Advertisement
Ranish666

Paged vaules

Mar 21st, 2021
870
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 5.62 KB | None | 0 0
  1. ALTER proc [dbo].[VoucherReport]  
  2. @type int,          
  3. @fromdate datetime,          
  4. @todate datetime,          
  5. @groupid int,          
  6. @ledgerid int,          
  7. @yearid int ,    
  8. @invoiceNo nvarchar(max),    
  9. @narration nvarchar(max),    
  10. @pagenumber int, @pagerow int, @export int          
  11. as      
  12.      
  13.      
  14. DECLARE @settingStatus INT = (select settingStatus   FROM  tbl_KhataSettings where settingId=8);  
  15.  
  16.      
  17. if @export = 0      
  18.  Begin        
  19.   declare @off int                                                
  20.   set @off = (@pagenumber - 1) * @pagerow                
  21.      
  22.    ;WITH CTE_TableName AS (      
  23.        SELECT entryMasterID, isnull(chequeNo,'') chequeNo      
  24.          FROM tbl_ledgerposting)  ,    
  25.  CTE_cheque as (  
  26.  SELECT t0.entryMasterID      
  27.    , STUFF((      
  28.      SELECT ',' + t1.chequeNo      
  29.     FROM CTE_TableName t1      
  30.    WHERE t1.entryMasterID = t0.entryMasterID      
  31.    and t1.chequeNo != ''  
  32.      FOR XML PATH('')), 1, LEN(', '), ' ') AS chequeNo      
  33.    FROM CTE_TableName t0      
  34.   GROUP BY t0.entryMasterID  )  
  35.  
  36.    select distinct MasterId,          
  37.    te.date,          
  38.    case when @settingStatus = 1  then          
  39.    (select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.date)) else          
  40.    (select CONVERT(VARCHAR(10), te.date, 111))          
  41.    end as strdate,        
  42.   case when @settingStatus = 1  then          
  43.    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          
  44.    CONCAT((select CONVERT(VARCHAR(10), te.EntryDate, 111)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt'))        
  45.    end as strEntryDate, te.EntryDate,        
  46.    tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,          
  47.    te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,  
  48.    isnull( c.chequeNo, '') chequeNo  
  49.     from tbl_EntryMaster te        
  50.  inner join CTE_cheque c on c.entryMasterID = te.MasterId  
  51.    inner join tbl_ledgerposting tlp on tlp.entryMasterID = te.MasterId          
  52.    inner join tbl_AccountLedger tal on tal.ledgerId = tlp.ledgerId          
  53.    where    
  54.    tlp.voucherTypeId = @type          
  55.    and tlp.ledgerId = case when @ledgerid = 0 then tal.ledgerId else @ledgerid end          
  56.    and tal.accountGroupId = case when @groupid = 0 then tal.accountGroupId else @groupid end          
  57.    and convert(date, te.date) between convert(date, @fromdate) and convert(date, @todate)          
  58.    and te.financialYearId = @yearid          
  59.    and te.invoiceNo like @invoiceNo+'%'    
  60.    and isnull(te.narration,'') like '%'+@narration+'%'    
  61.    group by MasterId, te.date, te.EntryDate, tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,          
  62.    te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,c.chequeNo        
  63.    order by MasterId      
  64.    offset @off rows        
  65.   fetch first @pagerow rows only      
  66.  END      
  67. else      
  68.  Begin    
  69.    
  70.   ;WITH CTE_TableName AS (      
  71.        SELECT entryMasterID, isnull(chequeNo,'') chequeNo      
  72.          FROM tbl_ledgerposting)  ,    
  73.  CTE_cheque as (  
  74.  SELECT t0.entryMasterID      
  75.    , STUFF((      
  76.      SELECT ',' + t1.chequeNo      
  77.     FROM CTE_TableName t1      
  78.    WHERE t1.entryMasterID = t0.entryMasterID      
  79.    and t1.chequeNo != ''  
  80.      FOR XML PATH('')), 1, LEN(', '), ' ') AS chequeNo      
  81.    FROM CTE_TableName t0      
  82.   GROUP BY t0.entryMasterID  )  
  83.  
  84.    select distinct MasterId,          
  85.    te.date,          
  86.    case when @settingStatus = 1  then          
  87.    (select top 1 dbo.StringSplit(m_date,'/',101) from datemiti where CONVERT(date,m_date)=CONVERT(date, te.date)) else          
  88.    (select CONVERT(VARCHAR(10), te.date, 111))          
  89.    end as strdate,        
  90.   case when @settingStatus = 1  then          
  91.    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          
  92.    CONCAT((select CONVERT(VARCHAR(10), te.EntryDate, 111)) , ' ' , FORMAT(te.EntryDate,'hh:mm tt'))        
  93.    end as strEntryDate, te.EntryDate,        
  94.    tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,          
  95.    te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID,          
  96.     isnull( c.chequeNo, '') chequeNo  
  97.     from tbl_EntryMaster te        
  98.  inner join CTE_cheque c on c.entryMasterID = te.MasterId  
  99.    inner join tbl_ledgerposting tlp on tlp.entryMasterID = te.MasterId          
  100.    inner join tbl_AccountLedger tal on tal.ledgerId = tlp.ledgerId          
  101.    where    
  102.    tlp.voucherTypeId = @type          
  103.    and tlp.ledgerId = case when @ledgerid = 0 then tal.ledgerId else @ledgerid end          
  104.    and tal.accountGroupId = case when @groupid = 0 then tal.accountGroupId else @groupid end          
  105.    and convert(date, te.date) between convert(date, @fromdate) and convert(date, @todate)          
  106.    and te.financialYearId = @yearid          
  107.    and te.invoiceNo like @invoiceNo+'%'    
  108.    and isnull(te.narration,'') like '%'+@narration+'%'        
  109.    group by MasterId, te.date, te.EntryDate, tlp.voucherTypeId, tlp.voucherNo, tlp.invoiceNo, totalAmount, te.narration, te.isImport,          
  110.    te.isfree, te.isvatPurchase, te.isfixedassests, te.userId, financialYearId, te.BranchID, c.chequeNo        
  111.    order by MasterId      
  112.  End  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement