Ranish666

Paged vaules

Mar 21st, 2021
739
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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  
RAW Paste Data