Function Terbilang untuk Rupiah (MS-SQL Server)

Function  Terbilang untuk Rupiah (MS-SQL Server)

Source : http://mamadh.wordpress.com/2007/03/05/fungsi-terbilang-di-sql-server/

CREATE FUNCTION [DBO].[TerbilangDetil] (@Nilai as decimal)  
RETURNS varchar(200) AS  
BEGIN 
declare @TS varchar(200), @ST varchar(200), @S integer, @p integer, @r1 integer, @Bil varchar(200), @TP varchar(200), @hasil varchar(200)
    set @ST = Right(rTrim(Str(@Nilai + 1000)), 3)
    set @S = cast(Right(@ST, 1) as integer)
    set @p = cast(substring(@ST, 2, 1) as integer)
    set @r1 = cast(Left(@ST, 1) as integer)
    set @Bil = 'Satu    Dua     Tiga    Empat   Lima    Enam    Tujuh   Delapan Sembilan'
  
    If @S = 0 
        set @TS = ''
    Else
        set @TS = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ' '
        
    If @p = 0 
        set @TP = @TS
    Else
        If @p = 1 
            If @S = 0 
                set @TP = 'Sepuluh '
            Else
                If @S = 1 
                    set @TP = 'Sebelas '
                Else
                    set @TP = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ' Belas '
        Else
            set @TP = rTrim(substring(@Bil, @p * 8 - 7, 8)) + ' Puluh ' + @TS
        
    If @r1 = 0 
        set @hasil = @TP
    Else
        If @r1 = 1 
            set @hasil = 'Seratus ' + @TP
        Else
            set @hasil = rTrim(substring(@Bil, @r1 * 8 - 7, 8)) + ' Ratus ' + @TP
return @hasil
END
GO

CREATE FUNCTION [dbo].[terbilang] (@angka as decimal(19,2))  
RETURNS varchar (200) AS  
BEGIN 
Declare @b Decimal, @r Decimal, @J Decimal, @M Decimal, @T1 Decimal, @koma1 Decimal, @koma2 Integer, @hasil varchar(200) 
Declare @s_t varchar(200), @z varchar(200), @ribu varchar(200), @Juta varchar(200), @Miliar varchar(200), @tx varchar(200), @angka1 varchar(200), @tx1 varchar(200)
set @s_t = case when Len(rTrim(cast(cast(@Angka as bigint) as varchar(20)))) < 15 
        then replicate('0',15 - Len(rTrim(cast(cast(@Angka as bigint) as varchar(20))))) + rTrim(cast(cast(@Angka as bigint) as varchar(20))) 
        else rTrim(cast(cast(@Angka as bigint) as varchar(20))) 
         end
set @z = cast(15 - Len(rTrim(cast(cast(@Angka as integer) as varchar(20)))) as varchar(20)) + rTrim(cast(cast(@Angka as integer) as varchar(20))) 
set @r = cast(Right(@s_t, 3) as integer)
set @b = cast(substring(@s_t, 10, 3)  as integer)
set @J = cast(substring(@s_t, 7, 3)  as integer)
set @M = cast(substring(@s_t, 4, 3)  as integer)
set @T1 = cast(Left(@s_t, 3)  as integer)
set @ribu = case when @b = 0 then ' ' else case when @b = 1 then 'Seribu' else [dbo].TerbilangDetil(@b) + 'Ribu' end end
set @Juta = case when @J = 0 then ' ' else [dbo].TerbilangDetil(@J) + 'Juta ' end
set @Miliar = case when @M = 0 then ' ' else [dbo].TerbilangDetil(@M) + 'Miliar' end
set @tx = Case 
    when @Angka < 1000 -- Ratusan
                then [dbo].TerbilangDetil(@r)
        when @Angka < 1000000 -- Ribuan
                then @ribu + ' ' + [dbo].TerbilangDetil(@r)
        when @Angka < 1000000000 -- Jutaan
                then [dbo].TerbilangDetil(@J) + '' + rTrim('Juta ' + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
    when @Angka < 1000000000000 -- Miliaran
                then [dbo].TerbilangDetil(@M) + '' + rTrim('Miliar ' + @Juta + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
        when @Angka >= 1000000000000 -- Trilliun
                then [dbo].TerbilangDetil(@T1) + '' + rTrim('Trilliun ' + @Miliar + @Juta + @ribu) + ' ' + [dbo].TerbilangDetil(@r)
End
set @angka1 = rTrim(cast(@Angka as varchar(20)))
set @koma1 = case when charindex('.', @angka1) = 0 then 0 else cast(rTrim(substring(@angka1, charindex('.',@angka1) + 1, 2)) as integer) end
set @koma2 = Len(case when charindex('.',@angka1) = 0 then '0' else rTrim(substring(@angka1, charindex('.',@angka1) + 1, 2)) end)
If @koma1 > 0
begin
    If @koma1 < 10 And @koma2 = 1
    set @koma1 = @koma1 * 10
    set @tx1 = [dbo].TerbilangDetil(@koma1)
    set @hasil = @tx + 'Rupiah ' + @tx1 + 'Sen'
end
Else
    set @hasil = @tx + 'Rupiah'
return (@hasil)
END
GO


Usage :

Select dbo.terbilang(1250000) as Terbilang
GO

Printing Directly to LPT port

'Open the port

Open "LPT1" For Output As #1

'Once opened, you can print directly to it
Print #1, "Your text"

'then, you have to close the port:

Close #1

for printing italics, bolds, etc, you must send special characters to the printer, according to the printer's ascii code. For example:

'To print small caps

print #1, chr(15) & "the text"

'to print Bold caps:

print #1, chr(27) & "E"

YOU CAN USE THE NEXT CODES FOR IBM PRINTERS:

Bold_on=27,69|27,71|27,73,1|27,73,2|27,95,1|27,95,49
Bold_off=27,70|27,72|27,120,0|27,120,48|27,95,0|27,95,48
Italics_on=27,52|27,73,11
Italics_off=27,53
Underline_on=27,45,1|27,45,49
Underline_off=27,45,0|27,45,48
Normal_Width=18|27,18|27,73,0|27,87,0|27,87,48|20
Condensed=15|27,15
Double_Width=27,87,1|27,87,49|14
Double_Height=27,119,1|27,119,49
Normal_Height=27,84|27,119,0|27,119,48
Ignore_Chars=27,120,0|27,120,48|27,80,1|27,80,0
Cpi12=27,58|27,73,1
Cpi14=27,16,2
Cpi15=27,103
Cpi20=27,16,4
Superscript=27,83,0|27,83,48
Subscript=27,83,1|27,83,49