· 7 years ago · Oct 31, 2018, 09:22 AM
1
2ALTER PROCEDURE [dbo].[usp_beli_paket] --usp_beli_paket '1707180019'
3@MitraCode AS VARCHAR(50),
4@NoTransaksi AS VARCHAR(50),
5@NoNota AS VARCHAR(50),
6@SubsId AS VARCHAR(50),
7@PaketKode AS VARCHAR(50),
8@jumlahAktifasi AS INT,
9@Satuan AS VARCHAR(50),
10@PeriodeFrom AS VARCHAR(15)=NULL,
11@PeriodeTo AS VARCHAR(15),
12@Notes AS VARCHAR(100),
13@UserId AS VARCHAR(50),
14@Message AS VARCHAR(1000) OUTPUT
15AS
16BEGIN
17 SET NOCOUNT ON
18 BEGIN TRY
19 BEGIN TRAN
20 DECLARE @HargaDenomMitra AS DECIMAL(18,2)= (SELECT md.HargaDenom FROM Mst_Discount md WITH (NOLOCK) WHERE md.KodeMitra=@MitraCode)
21 DECLARE @DiscountMitra AS DECIMAL(18,2)= (SELECT md.Discount FROM Mst_Discount md WITH (NOLOCK) WHERE md.KodeMitra=@MitraCode)
22 DECLARE @NoSn AS VARCHAR(50)= (SELECT mp.NoSn FROM Mst_Pelanggan mp WITH (NOLOCK) WHERE mp.SubsId=@SubsId AND mp.MitraCode=@MitraCode)
23 DECLARE @MaxNo AS VARCHAR(50)= (SELECT (ISNULL(MAX(ia.SeqNo),0) + 1) FROM InstallmentAktifasi ia WITH (NOLOCK) WHERE ia.NoSn=@NoSn)
24 DECLARE @IsDownline AS VARCHAR(10) = (SELECT mm.TypeId FROM Mst_Mitra mm WITH (NOLOCK) WHERE mm.MitraCode=@MitraCode)
25 DECLARE @TotalSaldoMitra AS DECIMAL(18,2)
26 DECLARE @BalanceSaldo AS DECIMAL(18,2)
27 DECLARE @TotalPaketDiscount AS DECIMAL(18,2)
28
29 DECLARE @Price AS DECIMAL (18,2)
30 DECLARE @HargaDenom AS DECIMAL (18,2)
31 DECLARE @PeriodeFromNew AS VARCHAR(15)
32 DECLARE @PeriodeToNew AS VARCHAR(15) = dbo.convDate(@PeriodeTo) --(SELECT convert(varchar, convert(datetime, @PeriodeTo, 103), 101))
33 IF(@PeriodeFrom IS NULL OR @PeriodeFrom='')
34 BEGIN
35 SET @PeriodeFromNew = GETDATE();
36 END
37 ELSE
38 BEGIN
39 SET @PeriodeFromNew = dbo.convDate(@PeriodeFrom) --(SELECT convert(varchar, convert(datetime, @PeriodeFrom, 103), 101))
40 END
41
42 IF EXISTS (SELECT ms.Saldo FROM Mst_Saldo ms WITH (NOLOCK) WHERE ms.KodeMitra=@MitraCode)
43 BEGIN
44 SET @TotalSaldoMitra = (SELECT ms.Saldo FROM Mst_Saldo ms WITH (NOLOCK) WHERE ms.KodeMitra=@MitraCode)
45 SET @TotalPaketDiscount = (@HargaDenomMitra - ((@HargaDenomMitra * (CAST(ROUND(@DiscountMitra/100.0,2) as DECIMAL(16,2)))) * @jumlahAktifasi))
46 SET @BalanceSaldo = (@TotalSaldoMitra - @TotalPaketDiscount) --BALANCE
47 IF( @BalanceSaldo > @TotalSaldoMitra)
48 BEGIN
49 SELECT '100' AS MSG
50 SET @Message = 'Pembelian paket sudah melebihi jumlah saldo, silahkan top up saldo anda kembali'
51 PRINT @Message
52 END
53 ELSE
54 BEGIN
55 CREATE TABLE #TEMP(
56 MitraCode VARCHAR(50) NOT NULL
57 )
58
59 INSERT INTO #TEMP SELECT mm.MitraCode FROM Mst_Mitra mm WHERE mm.LevelPosition IN(SELECT * FROM dbo.FnGetRootLevel(@MitraCode))
60 SELECT @Price=md.SellingPrice,@HargaDenom=md.HargaDenom FROM Mst_Discount md WITH (NOLOCK) WHERE md.KodeMitra IN (SELECT * FROM #TEMP)
61
62 PRINT 'CREATE LOG'
63 INSERT LogTransaksi(Id,ParentCode,KodeMitra,TrxName,Amount,Discount,FeeAmountDiscount,FeeAmountMarkup,UsrCreated,DtmCreated)
64 SELECT NEWID(),md.KodeMitra,@SubsId,@PaketKode,md.SellingPrice,md.Discount,
65 (md.HargaDenom * (CAST(ROUND(md.Discount/100.0,2) as DECIMAL(16,2)))) AS fee,0.00,md.HargaDenom,GETDATE() as DtmCreated
66 FROM Mst_Discount md WITH (NOLOCK) WHERE md.KodeMitra IN (SELECT * FROM #TEMP)
67
68 PRINT 'CREATE INSTALLMENT'
69 INSERT INTO InstallmentAktifasi(Id,SeqNo,NoSn,SubsId,TglAktifasi,PeriodeStart,PeriodeTo,KodePaket,[Status],LamaAktifasi,SatuanAktifasi, Notes,UsrUpd,DtmUpd)
70 VALUES(NEWID(),@MaxNo,@NoSn,@SubsId,GETDATE(),@PeriodeFromNew,@PeriodeToNew,@PaketKode,1,@jumlahAktifasi,@Satuan, @Notes,@UserId,GETDATE())
71 PRINT 'END CREATE INSTALLMENT'
72 /* BELI PAKET */
73 PRINT 'BELI PAKET'
74 IF (LOWER(@IsDownline)='dp')
75 BEGIN
76 INSERT INTO LogTransaksi
77 SELECT
78 NEWID(),
79 md.ParentKodeMitra AS ParentCode,
80 md.KodeMitra AS KodeMitra,
81 'Pembelian paket COSMOMITRA ' AS trxName,
82 (CASE WHEN md.KodeMitra=@MitraCode
83 THEN ((@HargaDenomMitra * (CAST(ROUND(@DiscountMitra/100.0,2) as DECIMAL(16,2)))) * @jumlahAktifasi)
84 ELSE (((@HargaDenomMitra * (CAST(ROUND((md.Discount- @DiscountMitra)/100.0,2) as DECIMAL(16,2))) * @jumlahAktifasi))) END) as Amount,
85 @DiscountMitra AS Discount,
86 0.00 AS FeeAmountDiscount,
87 0.00 AS FeeAmountMarkup,
88 @UserId AS UsrCreated,
89 GETDATE() AS DtmCreated
90 FROM Mst_Discount md
91 INNER JOIN Mst_Saldo ms2 ON ms2.KodeMitra=md.KodeMitra
92 WHERE md.KodeMitra
93 IN(SELECT mm.MitraCode FROM Mst_Mitra mm WHERE mm.LevelPosition IN(SELECT * FROM dbo.FnGetRootLevel(@MitraCode)))
94 PRINT 'END CREATE LOG'
95
96 INSERT INTO Trx_Mutasi
97 SELECT
98 NEWID(),
99 'T003',
100 md.KodeMitra AS JurnalOwner,
101 md.ParentKodeMitra AS ParentCode,
102 (CASE WHEN md.KodeMitra=@MitraCode
103 THEN 'Beli Paket dari ' + md.ParentKodeMitra
104 ELSE 'Komisi dari ' + @MitraCode END) as Descriptions,
105 md.KodeMitra AS KodeMitra,
106 @NoTransaksi,
107 @NoNota,
108 (CASE WHEN md.KodeMitra=@MitraCode
109 THEN 0.00
110 ELSE ((@HargaDenomMitra * (CAST(ROUND((md.Discount- @DiscountMitra)/100.0,2) as DECIMAL(16,2))) * @jumlahAktifasi)) END) as Debet,
111 (CASE WHEN md.KodeMitra=@MitraCode
112 THEN ((@HargaDenomMitra * (CAST(ROUND(@DiscountMitra/100.0,2) as DECIMAL(16,2)))) * @jumlahAktifasi)
113 ELSE 0.00 END) as Kredit,
114 (CASE WHEN md.KodeMitra=@MitraCode
115 THEN 0.00
116 ELSE (ms2.Saldo) END) as [Before],
117 0.00 as [After],
118 (CASE WHEN md.KodeMitra=@MitraCode
119 THEN (ms2.Saldo + ((@HargaDenomMitra * (CAST(ROUND(@DiscountMitra/100.0,2) as DECIMAL(16,2)))) * @jumlahAktifasi))
120 ELSE (ms2.Saldo + ((@HargaDenomMitra * (CAST(ROUND((md.Discount- @DiscountMitra)/100.0,2) as DECIMAL(16,2))) * @jumlahAktifasi))) END) as Balance,
121 @UserId AS UsrCreated,
122 GETDATE() AS DtmCreated,
123 NULL,
124 NULL
125 FROM Mst_Discount md
126 INNER JOIN Mst_Saldo ms2 ON ms2.KodeMitra=md.KodeMitra
127 WHERE md.KodeMitra
128 IN(SELECT mm.MitraCode FROM Mst_Mitra mm WHERE mm.LevelPosition IN(SELECT * FROM dbo.FnGetRootLevel(@MitraCode)))
129
130 UPDATE Mst_Saldo SET Mst_Saldo.Saldo =
131 (CASE WHEN md.KodeMitra=@MitraCode
132 THEN (ms2.Saldo + ((@HargaDenomMitra * (CAST(ROUND(@DiscountMitra/100.0,2) as DECIMAL(16,2)))) * @jumlahAktifasi))
133 ELSE (ms2.Saldo + ((@HargaDenomMitra * (CAST(ROUND((md.Discount- @DiscountMitra)/100.0,2) as DECIMAL(16,2))) * @jumlahAktifasi))) END)
134 FROM Mst_Discount md
135 INNER JOIN Mst_Saldo ms2 ON ms2.KodeMitra=md.KodeMitra
136 WHERE md.KodeMitra
137 IN(SELECT mm.MitraCode FROM Mst_Mitra mm WHERE mm.LevelPosition IN(SELECT * FROM dbo.FnGetRootLevel(@MitraCode)))
138 END
139 ELSE
140 BEGIN
141 PRINT 'CREATE MUTASI'
142 INSERT INTO Trx_Mutasi(Id,TrxType,JurnalOwner, ParentCode,Descriptions,KodeMitra,NoTransaksi,NoNota,Debet,Kredit,[Before],Balance,UsrCreated,DtmCreated) VALUES
143 (NEWID(),'T003',@MitraCode, @MitraCode,'Pembelian paket COSMOMITRA ',@SubsId,@NoTransaksi,@NoNota,0.00,@TotalPaketDiscount,@TotalSaldoMitra,@BalanceSaldo,@UserId,GETDATE())
144 END
145 /* END */
146
147 INSERT INTO Trx_NotaHeader(Id,NoNota,SuppCusCode,KodeMitra,NotaName,TypeNota,TglCreated,Amount,UsrCreated,DtmCreated)
148 VALUES(NEWID(),@NoNota,@MitraCode,@SubsId,'COSMOMITRA','H',GETDATE(),@BalanceSaldo,@UserId,GETDATE()),
149 (NEWID(),@NoNota,@MitraCode,@SubsId,'COSMOMITRA','P',GETDATE(),@BalanceSaldo,@UserId,GETDATE())
150
151 INSERT INTO Trx_NotaDetail(Id,NoNota,ProductName,Descriptions,Qty,Amount,IsLunas,UsrCreated,DtmCreated)
152 VALUES(NEWID(),@NoNota,'COSMOMITRA',@Notes,1,@BalanceSaldo,1,@UserId,GETDATE())
153
154 PRINT (@TotalSaldoMitra - @BalanceSaldo)
155 UPDATE Mst_Saldo SET
156 Mst_Saldo.Saldo= (@BalanceSaldo),
157 Mst_Saldo.UsrUpd=@UserId,
158 Mst_Saldo.DtmUpd=GETDATE()
159 WHERE Mst_Saldo.KodeMitra=@MitraCode
160
161 DROP TABLE #TEMP
162 SELECT '200' AS MSG
163 SET @Message = 'Selamat Pembelian paket sukses periode sampe dengan ' + CAST(@PeriodeTo AS VARCHAR(20))
164 PRINT @Message
165 COMMIT TRAN
166 END
167 END
168 ELSE
169 BEGIN
170 SELECT '100' AS MSG
171 SET @Message = 'Anda saat ini tidak punya saldo'
172 PRINT @Message
173 ROLLBACK TRAN
174 END
175
176 END TRY
177 BEGIN CATCH
178 SELECT '100' AS MSG
179 SET @Message = 'Error Query'
180 PRINT N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();
181 ROLLBACK TRAN
182 END CATCH
183END