Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER proc [dbo].[VoucherReport]
- @type int,
- @fromdate datetime,
- @todate datetime,
- @groupid int,
- @ledgerid int,
- @yearid int ,
- @invoiceNo nvarchar(max),
- @narration nvarchar(max),
- @pagenumber int, @pagerow int, @export int
- as
- DECLARE @settingStatus INT = (select settingStatus FROM tbl_KhataSettings where settingId=8);
- if @export = 0
- Begin
- declare @off int
- set @off = (@pagenumber - 1) * @pagerow
- ;WITH CTE_TableName AS (
- SELECT entryMasterID, isnull(chequeNo,'') chequeNo
- FROM tbl_ledgerposting) ,
- CTE_cheque as (
- SELECT t0.entryMasterID
- , STUFF((
- SELECT ',' + t1.chequeNo
- FROM CTE_TableName t1
- WHERE t1.entryMasterID = t0.entryMasterID
- and t1.chequeNo != ''
- FOR XML PATH('')), 1, LEN(', '), ' ') AS chequeNo
- FROM CTE_TableName t0
- GROUP BY t0.entryMasterID )
- select distinct MasterId,
- te.date,
- case when @settingStatus = 1 then
- (select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.date)) else
- (select CONVERT(VARCHAR(10), te.date, 111))
- end as strdate,
- case when @settingStatus = 1 then
- CONCAT((select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.EntryDate)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt')) else
- CONCAT((select CONVERT(VARCHAR(10), te.EntryDate, 111)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt'))
- end as strEntryDate, te.EntryDate,
- tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,
- te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,
- isnull( c.chequeNo, '') chequeNo
- from tbl_EntryMaster te
- inner join CTE_cheque c on c.entryMasterID = te.MasterId
- inner join tbl_ledgerposting tlp on tlp.entryMasterID = te.MasterId
- inner join tbl_AccountLedger tal on tal.ledgerId = tlp.ledgerId
- where
- tlp.voucherTypeId = @type
- and tlp.ledgerId = case when @ledgerid = 0 then tal.ledgerId else @ledgerid end
- and tal.accountGroupId = case when @groupid = 0 then tal.accountGroupId else @groupid end
- and convert(date, te.date) between convert(date, @fromdate) and convert(date, @todate)
- and te.financialYearId = @yearid
- and te.invoiceNo like @invoiceNo+'%'
- and isnull(te.narration,'') like '%'+@narration+'%'
- group by MasterId, te.date, te.EntryDate, tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,
- te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,c.chequeNo
- order by MasterId
- offset @off rows
- fetch first @pagerow rows only
- END
- else
- Begin
- ;WITH CTE_TableName AS (
- SELECT entryMasterID, isnull(chequeNo,'') chequeNo
- FROM tbl_ledgerposting) ,
- CTE_cheque as (
- SELECT t0.entryMasterID
- , STUFF((
- SELECT ',' + t1.chequeNo
- FROM CTE_TableName t1
- WHERE t1.entryMasterID = t0.entryMasterID
- and t1.chequeNo != ''
- FOR XML PATH('')), 1, LEN(', '), ' ') AS chequeNo
- FROM CTE_TableName t0
- GROUP BY t0.entryMasterID )
- select distinct MasterId,
- te.date,
- case when @settingStatus = 1 then
- (select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.date)) else
- (select CONVERT(VARCHAR(10), te.date, 111))
- end as strdate,
- case when @settingStatus = 1 then
- CONCAT((select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.EntryDate)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt')) else
- CONCAT((select CONVERT(VARCHAR(10), te.EntryDate, 111)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt'))
- end as strEntryDate, te.EntryDate,
- tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,
- te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,
- isnull( c.chequeNo, '') chequeNo
- from tbl_EntryMaster te
- inner join CTE_cheque c on c.entryMasterID = te.MasterId
- inner join tbl_ledgerposting tlp on tlp.entryMasterID = te.MasterId
- inner join tbl_AccountLedger tal on tal.ledgerId = tlp.ledgerId
- where
- tlp.voucherTypeId = @type
- and tlp.ledgerId = case when @ledgerid = 0 then tal.ledgerId else @ledgerid end
- and tal.accountGroupId = case when @groupid = 0 then tal.accountGroupId else @groupid end
- and convert(date, te.date) between convert(date, @fromdate) and convert(date, @todate)
- and te.financialYearId = @yearid
- and te.invoiceNo like @invoiceNo+'%'
- and isnull(te.narration,'') like '%'+@narration+'%'
- group by MasterId, te.date, te.EntryDate, tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,
- te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID, c.chequeNo
- order by MasterId
- End
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement