ALTER PROCEDURE sp_Paged_Show_All_Items
	-- Add the parameters for the stored procedure here
	@CurrentPage as bigint,
	@RowsPerPage as bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

with PagedResult AS
(
		
	SELECT     ROW_NUMBER() over (order by productid) as RowNum,
			   p.productcode,
               itemname,
               description,
               packtype,
               itemtype,
               c.categoryname,
               s.suppliername,
               supplierprice,
               srp,
               discount,
               itemlocation,
               expdate,
               service_other_charge_amount_percent,
               vatable,
               COALESCE(BeginInventory,0) AS BeginInventory, 
			   COALESCE(Purchases,0) AS Purchases,
               COALESCE(TransfersIn,0) AS TransfersIn,
               COALESCE(TransfersOut,0) AS TransfersOut,
               COALESCE(SoldItem,0) AS SoldItem,
               COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(SoldItem,0) AS RunningOnhand
    FROM tbl_product_list p 
         
    LEFT JOIN  (SELECT prodcode,
				SUM(ISNULL(CASE WHEN memo='beginning' THEN qty ELSE 0 END,0)) AS BeginInventory,
                SUM(ISNULL(CASE WHEN memo='purchases' THEN qty ELSE 0 END,0)) AS Purchases
                FROM PO_detail
                GROUP BY prodcode)i
    ON i.prodcode = p.productcode
         
    LEFT JOIN (SELECT prodcode,
               SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
               SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
               FROM tbl_product_transfer_detail 
               GROUP BY prodcode)pts
    ON pts.prodcode= p.ProductCode
         
    LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
               FROM tbl_pos_sales_detail
               GROUP BY productcode)psd
    ON psd.productcode= p.productcode
         
	LEFT JOIN  tbl_supplier s
	ON  s.suppliername=(SELECT s.suppliername
	WHERE s.suppliercode=p.supplierid)
			
	LEFT JOIN  tbl_prod_category c
	ON  c.categoryname=(SELECT c.categoryname
	WHERE c.catgoryid=p.category)
	
	
	GROUP BY productid,p.productcode,itemname,description,packtype,itemtype,c.categoryname,
             s.suppliername,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,
             BeginInventory,Purchases,TransfersIn,TransfersOut,SoldItem
	--ORDER BY p.itemname ASC
)

	select * 
	from PagedResult
	where RowNum between @CurrentPage and @RowsPerPage
	ORDER BY itemname ASC
	
	END
	GO

  LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
               FROM tbl_pos_sales_detail
               GROUP BY productcode)psd
    ON psd.productcode= p.productcode