· 4 years ago · Feb 19, 2021, 09:42 AM
1if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
2begin
3 INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10020 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase CC Charge', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
4 INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10021 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase Adjustment', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
5end
6Go
7if not exists (select * from tbl_AccountLedger where RTRIM(Ltrim(ledgerName)) = 'Purchase CC Charge')
8begin
9 INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10020 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase CC Charge', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
10 INSERT [dbo].[tbl_AccountLedger] ([ledgerId], [accountGroupId], [ledgerName], [openingBalance], [isDefault], [crOrDr], [narration], [mailingName], [address], [phone], [mobile], [email], [creditPeriod], [creditLimit], [pricinglevelId], [billByBill], [registrationNo], [licenseNo], [pan], [routeId], [bankAccountNumber], [branchName], [branchCode], [extraDate], [extra1], [extra2], [areaId], [isfree], [isimport], [isfixedpurchased], [iseffectonvat], [isvat], [AccRefId], [userid], [ledgertype]) VALUES (CAST(10021 AS Numeric(18, 0)), CAST(15 AS Numeric(18, 0)), N'Purchase Adjustment', CAST(0.00000 AS Decimal(18, 5)), 1, N'Cr', NULL, NULL, N'banasthali', NULL, NULL, NULL, 0, NULL, CAST(1 AS Numeric(18, 0)), 0, NULL, NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, CAST(N'2013-06-20T00:00:00.000' AS DateTime), NULL, NULL, CAST(1 AS Numeric(18, 0)), NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL)
11end
12go
13CREATE proc [dbo].[TrialBalance_Groupwise]
14 @fromdate datetime,
15 @todate datetime,
16 @branchid int,
17 @financialYearid int,
18 @reportType nvarchar(max)
19as
20DECLARE @noOfDecimalPlace INT = 5;
21
22if @reportType = 'SubGroupwise'
23Begin
24 declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;
25 Declare @temp table(accountGroupId int, accountGroupName nvarchar(max), groupUnder int)
26
27 --insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId = 0
28 Declare datainsert cursor for
29 select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup
30 open datainsert
31 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
32 WHILE @@FETCH_STATUS=0
33 Begin
34 set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId = @accountGroupId)
35 if @groupUnder = 0
36 begin
37 insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where accountGroupId = @accountGroupId
38 insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder = @accountGroupId
39 insert into @temp select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup where groupUnder in (select accountGroupId from tbl_accountGroup where groupUnder = @accountGroupId)
40 end
41 if @accountGroupId = 0
42 begin
43 insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where accountGroupId = @accountGroupId
44 --insert into @temp select accountGroupId, accountGroupName, 0 from tbl_accountGroup where groupUnder = @accountGroupId
45 end
46 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
47 End
48
49 CLOSE datainsert
50 DEALLOCATE datainsert
51
52
53 ;With
54 PlGroups(accountGroupId) as(
55 select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
56 union all
57 select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)
58 ),
59 trial1 as
60 (
61 select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening
62 from tbl_ledgerposting TL
63 inner join tbl_accountledger AL ON AL.ledgerid=TL.LedgerID
64 inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
65 where convert(date, TL.date) < convert(date, @fromdate ) and
66 ag.accountGroupId not in (select accountGroupId from PlGroups)
67 GROUP BY AG.accountGroupID,AG.AccountGroupName
68 ),
69 trial2 as (
70 select AG.accountGroupID, Ag.AccountGroupName, sum(TL.Debit) as Debit, Sum(TL.Credit) as credit
71 from tbl_ledgerposting TL
72 inner join tbl_accountledger AL ON AL.ledgerid=TL.LedgerID
73 inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
74 where convert(date, TL.date) between convert(date, @fromdate) and convert(date, @toDate)
75 and yearId = @financialyearid
76 GROUP BY AG.accountGroupID,AG.AccountGroupName
77 )
78 select
79 ag.accountGroupID, ag.accountGroupName, ag.groupUnder,
80 isnull(t1.opening,0) as OpeningBalance,
81 concat (
82 (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),
83 case when isnull(t1.opening,0) > 0 then ' Dr' else
84 case when isnull(t1.opening,0) < 0 then ' Cr' else '' end
85 end
86 )
87 as OpeningBalanceStr,
88 convert(decimal(24, 3), isnull( t2.debit, 0) ) as Debit,
89 convert(decimal(24, 3), isnull( t2.credit, 0) ) as Credit,
90 convert(decimal(24, 3), isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,
91 concat (
92 (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),
93 case when isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr' else
94 case when isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr' else '' end
95 end
96 ) as BalanceStr
97 from @temp ag
98 left join trial1 t1 on t1.accountGroupId = ag.accountGroupId
99 left join trial2 t2 on t2.accountGroupId = ag.accountGroupId
100 WHERE opening<>0 or Debit<>0 or Credit<>0
101End
102
103else if @reportType = 'Groupwise'
104Begin
105 ;With
106 PlGroups(accountGroupId) as(
107 select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
108 union all
109 select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)
110 ),
111 mainGroups(accountGroupId) as (
112 select accountGroupId from tbl_accountGroup where groupUnder in (0)
113 ),
114 trial1 as
115 (
116 select AG.accountGroupID, sum(TL.Debit)-Sum(TL.Credit) as opening
117 from tbl_ledgerposting TL
118 inner join tbl_accountledger AL ON AL.ledgerid=TL.LedgerID
119 inner join tbl_accountGroup AG ON AG.AccountGroupID=AL.AccountGroupID
120 where
121 convert(date, TL.date) < convert(date, @fromdate ) and
122 ag.accountGroupId not in (select accountGroupId from PlGroups)
123 GROUP BY AG.accountGroupID,AG.AccountGroupName
124 ),
125 trial2 as (
126 SELECT ag.accountGroupId, ag.accountGroupName,
127 ISNULL(
128 (
129 SELECT SUM(ISNULL(lp.debit, 0))
130 FROM tbl_AccountLedger al
131 LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
132 WHERE convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
133 and yearId = @financialyearid and
134 (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
135 , 0)AS Debit,
136 ISNULL(
137 (
138 SELECT SUM(ISNULL(lp.credit, 0))
139 FROM tbl_AccountLedger al
140 LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
141 WHERE convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
142 and yearId = @financialyearid and
143 (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
144 , 0)AS credit
145 FROM tbl_AccountGroup ag
146 LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId
147 AND al1.accountGroupId IN (
148 SELECT accountGroupId
149 FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))
150 WHERE (ag.accountGroupId IN (
151 SELECT accountGroupId FROM tbl_AccountGroup ag1
152 WHERE (nature = 'Assets') AND (groupUnder = 0))
153 )
154 group by ag.accountGroupId, ag.accountGroupName
155 union all
156
157 SELECT ag.accountGroupId, ag.accountGroupName,
158 ISNULL(
159 (
160 SELECT SUM(ISNULL(lp.debit, 0))
161 FROM tbl_AccountLedger al
162 LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
163 WHERE convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
164 and yearId = @financialyearid and
165 (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
166 , 0)AS Debit,
167 ISNULL(
168 (
169 SELECT SUM(ISNULL(lp.credit, 0))
170 FROM tbl_AccountLedger al
171 LEFT OUTER JOIN tbl_LedgerPosting lp ON al.ledgerId = lp.ledgerId
172 WHERE convert(date, lp.date) between convert(date, @fromdate) and convert(date, @toDate)
173 and yearId = @financialyearid and
174 (al.accountGroupId IN(SELECT accountGroupId FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId) AS AccountLedgerGetGroupUnderFunction_1)))
175 , 0)AS credit
176 FROM tbl_AccountGroup ag
177 LEFT OUTER JOIN tbl_AccountLedger al1 ON ag.accountGroupId = al1.accountGroupId
178 AND al1.accountGroupId IN (
179 SELECT accountGroupId
180 FROM dbo.AccountLedgerGetGroupUnderFunction(ag.accountGroupId))
181 WHERE (ag.accountGroupId IN (
182 SELECT accountGroupId FROM tbl_AccountGroup ag1
183 WHERE (nature = 'Liabilities') AND (groupUnder = 0))
184 )
185 group by ag.accountGroupId, ag.accountGroupName
186 )
187
188 select DISTINCT convert(int,ROW_NUMBER() OVER (ORDER BY ag.accountGroupId)) AS [Sl No],
189 ag.accountGroupID, ag.accountGroupName,
190 isnull(t1.opening,0) as OpeningBalance,
191 concat (
192 (convert(decimal(24, 3), abs(isnull(t1.opening,0) ))),
193 case when isnull(t1.opening,0) > 0 then ' Dr' else
194 case when isnull(t1.opening,0) < 0 then ' Cr' else '' end
195 end
196 )
197 as OpeningBalanceStr,
198 convert(decimal(24, 3), isnull( t2.debit, 0) ) as Debit,
199 convert(decimal(24, 3), isnull( t2.credit, 0) ) as Credit,
200 convert(decimal(24, 3), isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0) ) as Balance,
201 concat (
202 (convert(decimal(24, 3), abs(isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)))),
203 case when isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0)> 0 then ' Dr' else
204 case when isnull(t1.opening,0) + isnull( t2.debit, 0) - isnull( t2.credit, 0) < 0 then ' Cr' else '' end
205 end
206 ) as BalanceStr
207 from tbl_accountGroup ag
208 --inner join mainGroups m on m.accountGroupId = ag.accountGroupId
209 left join trial1 t1 on t1.accountGroupId = ag.accountGroupId
210 left join trial2 t2 on t2.accountGroupId = ag.accountGroupId
211 WHERE opening<>0 or Debit<>0 or Credit<>0
212
213End
214go
215CREATE proc [dbo].[TrialBalance_LedgerWise]
216@fromDate date, @toDate date, @branchid int, @financialYearid int
217as
218declare @accountGroupId int, @accountGroupName nvarchar(max), @groupUnder int;
219Declare @temp table(ledgerId int, ledgerName nvarchar(max))
220Declare datainsert cursor for
221 select accountGroupId, accountGroupName, groupUnder from tbl_accountGroup
222 open datainsert
223 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
224 WHILE @@FETCH_STATUS=0
225 Begin
226 set @groupUnder = (select groupUnder from tbl_accountGroup where accountGroupId = @accountGroupId)
227 if @groupUnder = 0
228 begin
229 insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId = @accountGroupId
230 insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId = @accountGroupId order by ledgerId
231 end
232 else
233 Begin
234 insert into @temp select 0, accountGroupName from tbl_accountGroup where accountGroupId = @accountGroupId
235 insert into @temp select ledgerId, ledgerName from tbl_AccountLedger where accountGroupId = @accountGroupId order by ledgerId
236 End
237
238 Fetch next from datainsert into @accountGroupId, @accountGroupName, @groupUnder
239 End
240
241CLOSE datainsert
242DEALLOCATE datainsert
243
244
245create table #PlGroups (accountGroupId int)
246insert into #PlGroups
247 select accountGroupId from tbl_accountGroup where accountGroupId in (10,11,12,13,14,15)
248 union all
249 select accountGroupId from tbl_accountGroup where groupUnder in (10,11,12,13,14,15)
250
251Create table #AccountGroups (accountGroupId int)
252insert into #AccountGroups
253 select accountGroupId from tbl_AccountGroup
254create table #ledgerData (ledgerId int, accountGroupId int, ledgerName nvarchar(max), Opening nvarchar(max), op decimal(18,5),
255 Debit decimal(18,5), Credit decimal(18,5), Closing nvarchar(max), Closing1 decimal(18,5), Total_Closing nvarchar(max))
256insert into #ledgerData
257 SELECT
258 ledgerId, accountGroupId, ledgerName,
259 CASE WHEN CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2))<0 THEN
260 CAST(CAST(ROUND(isnull(SUM(-Opening) ,0),2e)AS DECIMAL(24,2)) AS VARCHAR(50))+'Cr'
261 ELSE
262 CAST(CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS VARCHAR(50))+'Dr'
263 END AS Opening,
264 CAST(ROUND(isnull(SUM(Opening) ,0),2)AS DECIMAL(24,2)) AS op,
265 CAST( ROUND(isnull(SUM(Debit) ,0),2)AS DECIMAL(24,2)) AS Debit,
266 CAST( ROUND(isnull(SUM(Credit) ,0),2)AS DECIMAL(24,2)) AS Credit,
267 CASE WHEN (Opening+debit-credit)>0 then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2)))+'Dr' else
268 CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round( (Opening+debit-credit), 2) ) )+'Cr' end AS Closing,
269 convert(decimal(18, 2), (Round((Opening+debit-credit), 2))) AS Closing1,
270 CASE WHEN (Opening+debit-credit)>0 then CONVERT(VARCHAR(MAX),convert(decimal(18,2),Round( (Opening+debit-credit), 2) ))+'Dr' else
271 CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),Round((Opening+debit-credit), 2)) )+'Cr' end AS Total_Closing
272 --CASE WHEN (totalopening)>0 then CONVERT(VARCHAR(MAX),convert(decimal(18,2),(totalopening)))+'Dr'
273 --else CONVERT(VARCHAR(MAX),-1*convert(decimal(18,2),(totalopening)) )+'Cr' end AS Total_Closing
274 FROM
275 (
276 SELECT B.ledgerId, B.ledgerName, B.accountGroupId,
277 (SELECT
278 ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP
279 FROM tbl_LedgerPosting AS LP
280 inner join tbl_AccountLedger al on al.ledgerId = lp .ledgerId and al.accountGroupId not in (select accountGroupId from #PlGroups)
281 WHERE (LP.ledgerId=B.ledgerId
282 AND (LP.date<@fromDate)
283 ))
284 AS Opening,
285 (SELECT ISNULL(SUM(debit),0) -ISNULL(SUM(credit), 0) AS OP
286 FROM tbl_LedgerPosting AS LP
287 WHERE
288 LP.ledgerId=B.ledgerId and yearId = @financialyearid
289 and lp.voucherTypeId !=1
290 and LP.date BETWEEN @fromDate AND @toDate
291 )
292 AS totalopening,
293 (select
294 ISNULL(SUM(debit),0)AS OP
295 FROM tbl_LedgerPosting AS LP
296 WHERE
297 LP.ledgerId=B.ledgerId and yearId = @financialyearid
298 and lp.voucherTypeId !=1
299 and LP.date BETWEEN @fromDate AND @toDate
300 ) AS Debit,
301 (
302 SELECT
303 ISNULL(SUM(credit),0) AS OP
304 FROM tbl_LedgerPosting AS LP
305 WHERE
306 Lp.ledgerId=B.ledgerId and yearId = @financialyearid and
307 lp.voucherTypeId !=1
308 and LP.date BETWEEN @fromDate AND @toDate
309 )AS Credit
310
311 FROM tbl_AccountLedger AS B
312 WHERE accountGroupId in( SELECT accountGroupId From #AccountGroups )
313 )AS TEMP
314 WHERE(TEMP.Debit<>0 OR TEMP.Credit<>0 OR opening<>0)
315 GROUP BY ledgerId,ledgerName,opening,credit,debit,totalopening,accountGroupId
316
317declare @ledgerId int, @ledgerName nvarchar(max), @OpeningBalance decimal(18, 5),
318 @OpeningBalanceStr nvarchar(max), @Debit decimal(18, 5), @Credit decimal(18, 5),
319 @Balance decimal(18, 5), @BalanceStr nvarchar(max);
320Declare @tempData table(
321 ledgerId int, ledgerName nvarchar(max), accountGroupId int,
322 OpeningBalance decimal(18, 5),
323 OpeningBalanceStr nvarchar(max), Debit decimal(18, 5), Credit decimal(18, 5),
324 Balance decimal(18, 5), BalanceStr nvarchar(max))
325
326Declare datainsert cursor for
327 select t.ledgerId, t.ledgerName, d.accountGroupId,
328 isnull(d.op,0) OpeningBalance, isnull(d.Opening,0) OpeningBalanceStr,
329 isnull(d.Debit,0) Debit, isnull(d.Credit,0) Credit,
330 isnull(d.Closing1,0) Balance, isnull(d.Total_Closing,0) BalanceStr
331 from @temp t
332 left join #ledgerData d on d.ledgerId = t.ledgerId
333 open datainsert
334 Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
335 WHILE @@FETCH_STATUS=0
336 Begin
337 if @ledgerId = 0
338 begin
339 insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
340 end
341 else
342 Begin
343 if @Debit <> 0 or @Credit <> 0 or @OpeningBalance <>0
344 insert into @tempData select @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
345 End
346
347 Fetch next from datainsert into @ledgerId, @ledgerName, @accountGroupId, @OpeningBalance, @OpeningBalanceStr, @Debit, @Credit, @Balance, @BalanceStr
348 End
349
350CLOSE datainsert
351DEALLOCATE datainsert
352
353 select ledgerId, ledgerName, accountGroupId,
354 OpeningBalance,
355 OpeningBalanceStr, Debit, Credit,
356 Balance, BalanceStr from @tempData
357
358Drop table #PlGroups;
359Drop table #AccountGroups;
360Drop table #ledgerData;
361Go
362
363GO
364truncate table [tblDashboardSetting]
365go
366SET IDENTITY_INSERT [dbo].[tblDashboardSetting] ON
367GO
368INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (1, N'Patient Record')
369GO
370INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (2, N'Departmentwise Admitted Record')
371GO
372INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (3, N'Doctorwise Admitted Record')
373GO
374INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (4, N'Vistor Record In Percentage')
375GO
376INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (5, N'Weekly Total Revenue')
377GO
378INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (6, N'Book Appointment')
379GO
380INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (7, N'Departmentwise Discharged Record')
381GO
382INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (8, N'Total Bed')
383GO
384INSERT [dbo].[tblDashboardSetting] ([DBId], [ActionName]) VALUES (9, N'Inpatient Morbidity')
385GO
386SET IDENTITY_INSERT [dbo].[tblDashboardSetting] OFF
387GO
388
389Go
390Create proc AccountFixedAssetsPurchasegetByBillNo
391@fromBillNo nvarchar(max),
392@toBillNo nvarchar(max)
393as
394declare @frombillId int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @fromBillNo),0));
395declare @toBillID int = (isnull((select AssetMasterID from tblFixedAssetsPurchasemaster Where ReferenceNo = @toBillNo),0));
396if @frombillId<>0 and @toBillID<>0
397BEGIN
398 select 'Purchase A/c' as heading,11 as ledgerid,sum(BillAmt) as Amount,'Dr'
399 DrCR from tblFixedAssetsPurchasemaster
400 where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
401 Union all
402 select 'VAT A/c' as heading,10022 as ledgerid,sum(taxAmt) as Amount,'Dr' DrCR from tblFixedAssetsPurchasemaster
403 where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
404 Union all
405 select 'Discount Allow A/c' as heading,8 as ledgerid,sum(DIscAmt) as Amount,
406 'Cr' DrCR from tblFixedAssetsPurchasemaster
407 where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
408 --Union all
409 --Purchase CC Charge --group under 15
410 --select 'Purchase CC Charge' as heading,10020 as ledgerid, ABS(sum(ccamt)) as Amount,'Dr' DrCR
411 --from tblFixedAssetsPurchasemaster
412 --where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
413 union all
414 --Purchase Adjustment --group under 15
415 select 'Purchase Adjustment' as heading,10021 as ledgerid, abs(sum(AdjustmentAmt)) as Amount,
416 case when sum(AdjustmentAmt) > 0 then 'Dr' else 'Cr' end DrCR
417 from tblFixedAssetsPurchasemaster
418 where AssetMasterID between @frombillId and @toBillID and type in ('Purchases','Purchases Return')
419 union all
420 select acl.ledgername as heading, AccRefId as ledgerid,sum(NettotalAmt) as Amount,'Cr' DrCR
421 from tblFixedAssetsPurchasemaster TPM
422 Inner join tbl_accountLedger acl ON acl.ledgerid=TPM.AccountLedgerID
423 where TPM.AssetMasterID between @frombillId and @toBillID and TPM.type in ('Purchases','Purchases Return')
424 group by AccRefId,acl.LedgerName
425END
426GO
427
428
429go
430
431
432
433