Purchase Edit changes

alter table tblPurchaseEditEntry add soldQTY decimal(18,2) null, isSold int null;
go
go
Alter proc [dbo].[PurchaseEdit_getPurchaseDetailsbyRefno]
@ReferenceNo nvarchar(500)
as
begin
 with checkSoldQty as (
    select pd.ItemID, pd.Itemtype, pd.Batch, isnull(sum( st.outward_qnty),0) soldQTY from tblpurchaseDetails pd
     left join tblstock st on st.batch = pd.Batch and st.item_id = pd.ItemID and st.stock_type = 'Pharmacy Sale'
    where PurchaseMasterID = (select PurchaseMasterID from tblPurchaseMaster where ReferenceNo = @ReferenceNo)
    group by pd.ItemID, pd.Itemtype, pd.Batch
 )

 select PD.PurchaseDetailsID,PM.PurchaseMasterID,Pm.ReferenceNo,Pm.VendorBillNo,TPT.typeid,TPT.purchaseType as Itemtype,PD.ItemID,
 (case when PD.Itemtype=1 then  (select brandname from tblBrand where bid=pd.ItemID)
 when PD.Itemtype=2 then  (select brandname from tblSurgicalbrand where SurgBrandId=pd.ItemID)
 else (select brandname from tblExtra where EId=pd.ItemID) end) as 'BrandName',
 PD.StoreDepartmentID,TSD.Department,PD.ledgerID as AccountledgerID,ta.ledgerName,PD.Batch,PD.mode,convert(date,pd.ExpireDate) as 'ExpireDate',
 PD.TaxAmt as  'TaxAmt',
 PD.Taxper as 'Taxper',
 PD.TotalCost as 'TotalCost',
 PD.profitPer as 'profitPer',
 PD.TotalQty as 'TotalQty',
 PD.rate as 'rate',
 PD.MRP as 'MRP',
 PD.CashDiscPer as 'CashDiscPer',
 PD.CashDisc as 'CashDisc',
 PD.QtyBonus as 'QtyBonus',
 PD.CCPer as 'CCPer',
 PD.NetUnitCost as 'NetUnitCost',
 PD.DiscUnitCost as 'DiscUnitCost',
 PD.CurentSellPrice as 'CurentSellPrice',
 PD.newSellproce as 'newSellproce',
 PD.TotalCost as 'TotalCost' ,
 cs.soldQTY,
 case when cs.soldQTY > 0 then 1 else 0 end isSold
 from tblPurchaseMaster PM
 inner join tblpurchaseDetails PD on PM.PurchaseMasterID=PD.PurchaseMasterID  and PM.Type='Purchases'
 inner join tbl_AccountLedger ta on PM.AccountledgerID = ta.ledgerId
 inner join tblStoreDepartment TSD on TSD.StoreDepartmentID=PD.StoreDepartmentID
 inner join tblPurchaseType TPT on TPT.typeid=PD.Itemtype
 left join checkSoldQty cs on cs.ItemID = pd.ItemID and cs.Batch = pd.Batch and cs.Itemtype = pd.Itemtype
 where ltrim(rtrim(PM.ReferenceNo))=ltrim(rtrim(@ReferenceNo))

end
go
Alter proc [dbo].[PurchaseEdit_Insert]
@PurchaseMasterID int,
@PurchaseDetailsID int ,
@VendorBillNo nvarchar(MAX),
@ReferenceNo nvarchar(MAX),
@AccountledgerID int ,
@mode nvarchar(MAX) ,
@StoreDepartmentID int ,
@Itemtype nvarchar(MAX) ,
@ItemID int ,
@BrandName nvarchar(MAX),
@Batch nvarchar(MAX) ,
@ExpireDate datetime ,
@TotalQty decimal(18, 2),
@rate decimal(18, 2) ,
@CashDiscPer decimal(18, 2),
@CashDisc decimal(18, 2),
@Taxper decimal(18, 2) ,
@TaxAmt decimal(18, 2) ,
@MRP decimal(18, 2) ,
@profitPer decimal(18, 2) ,
@QtyBonus decimal(18, 2) ,
@CCPer decimal(18, 2) ,
@NetUnitCost decimal(18, 2),
@DiscUnitCost decimal(18, 2),
@TotalCost decimal(18, 2),
@CurentSellPrice decimal(18, 2) ,
@newSellproce decimal(18, 2),
@Action nvarchar(MAX),
@userid int  ,
@soldQTY decimal(18,2),
@isSold int
as
BEGIN
IF not exists(select * from tblPurchaseEditEntry where PurchaseMasterID=@PurchaseMasterID and ItemID=@ItemID and Itemtype=@Itemtype
and Batch=@Batch  and StoreDepartmentID=@StoreDepartmentID)
BEGIN
 insert into tblPurchaseEditEntry(PurchaseMasterID,PurchaseDetailsID,VendorBillNo,ReferenceNo,AccountledgerID,mode,StoreDepartmentID,Itemtype,ItemID,BrandName,Batch,
 ExpireDate,TotalQty,rate,CashDiscPer,CashDisc,Taxper,TaxAmt,MRP,profitPer,QtyBonus,CCPer,NetUnitCost,DiscUnitCost,TotalCost,CurentSellPrice,newSellproce,[Action],userid,createdate,
 soldQTY, isSold)
 values (@PurchaseMasterID,@PurchaseDetailsID,@VendorBillNo,@ReferenceNo,@AccountledgerID,@mode,@StoreDepartmentID,@Itemtype,@ItemID,@BrandName,@Batch,@ExpireDate,
 @TotalQty,@rate,@CashDiscPer,@CashDisc,@Taxper,@TaxAmt,@MRP,@profitPer,@QtyBonus,@CCPer,@NetUnitCost,@DiscUnitCost,@TotalCost,@CurentSellPrice,@newSellproce,@Action,@userid,GETDATE(),
 @soldQTY, @isSold)
END
END
go
go
Alter proc [dbo].[PurchaseEditEntry_getEditEntry]
@VenderInvoiceNum nvarchar(500)
as
begin
 select Pm.Id,PM.PurchaseDetailsID,PM.PurchaseMasterID,PM.VendorBillNo,Pm.ReferenceNo,PM.StoreDepartmentID,TSD.Department,PM.AccountledgerID,ta.ledgerName,
 TPT.typeid,TPT.purchaseType as Itemtype,PM.Batch,PM.mode,convert(date,PM.ExpireDate) as 'ExpireDate',PM.ItemID,PM.BrandName,
 convert(decimal(18,5),PM.TaxAmt)as 'TaxAmt',
 convert(decimal(18,5),PM.Taxper)as 'Taxper',
 convert(decimal(18,5),PM.TotalCost)as 'TotalCost',
 convert(decimal(18,5),PM.profitPer)as 'profitPer',
 convert(decimal(18,5),PM.TotalQty)as 'TotalQty',
 convert(decimal(18,5),PM.rate)as 'rate',
 convert(decimal(18,5),PM.MRP)as 'MRP',
 convert(decimal(18,5),PM.CashDiscPer)as 'CashDiscPer',
 convert(decimal(18,5),PM.CashDisc)as 'CashDisc',
 convert(decimal(18,5),PM.QtyBonus)as 'QtyBonus',
 convert(decimal(18,5),PM.CCPer)as 'CCPer',
 convert(decimal(18,5),PM.NetUnitCost)as 'NetUnitCost',
 convert(decimal(18,5),PM.DiscUnitCost)as 'DiscUnitCost',
 convert(decimal(18,5),PM.CurentSellPrice)as 'CurentSellPrice',
 convert(decimal(18,5),PM.newSellproce)as 'newSellproce',
 (select isnull(qnty,0)  from tblstockSummary where itemid=PM.ItemID and itemtype=PM.Itemtype and batch=pm.Batch and  StoreDepartmentID=pm.StoreDepartmentID) as StockQty,
 PM.[Action]  ,
 pm.soldQTY, pm.isSold
 from tblPurchaseEditEntry PM
 inner join tbl_AccountLedger ta on PM.AccountledgerID = ta.ledgerId
 inner join tblStoreDepartment TSD on TSD.StoreDepartmentID=PM.StoreDepartmentID
 inner join tblPurchaseType TPT on TPT.typeid=PM.Itemtype
 where ltrim(rtrim(PM.ReferenceNo))=ltrim(rtrim(@VenderInvoiceNum))  --and Action<>'Delete'
end
go
go
Alter proc [dbo].[PurchaseEdit_UpdatePurchaseDetails]
@PurchaseMasterId int,
@PurchaseDetailsID int,
@VenderInvoiceNum nvarchar(500),
@ledgerID int,
@StoreDepartmentID int,
@Itemtype nvarchar(500),
@Batch nvarchar(500),
@ExpireDate datetime,
@TotalCost decimal(18,2),
@profitPer decimal(18,2),
@TotalQty  decimal(18,2),
@MRP decimal(18,2),
@CashDiscPer  decimal(18,2),
@CashDisc decimal(18,2),
@QtyBonus decimal(18,2),
@CCPer decimal(18,2),
@NetUnitCost decimal(18,2),
@DiscUnitCost decimal(18,2),
@CurentSellPrice decimal(18,2),
@newSellproce decimal(18,2),
@ItemID int,
@rate decimal(18,2),
@Taxper decimal(18,2),
@TaxAmt decimal(18,2),
@mode nvarchar(500),
@invoicedate datetime
as
begin
declare @preqty decimal(18,2)
declare @stockqty decimal(18,2)
declare @diffqty decimal(18,2)
declare @remainstock decimal(18,2)

--previous  qty
set @preqty=(select (TotalQty+isnull(QtyBonus,0)) as TotalQty from tblpurchaseDetails  where PurchaseDetailsID=@PurchaseDetailsID)

--stock  qty
if exists( select * from tblstockSummary where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID)
 begin
 set @stockqty=(select isnull(qnty,0) as Qty from tblstockSummary where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID)
 end
else
 begin
 set @stockqty=0
 end


set @diffqty=(isnUll(@TotalQty+@QtyBonus,0))-@preqty --qty  diff
set @remainstock=@stockqty+(@diffqty) --remain qty

--check qty
  if (@remainstock >= 0)
 begin
    --update in purchasedetails table
  update tblpurchasedetails set   VenderInvoiceNum=@VenderInvoiceNum,ledgerID=@ledgerID,StoreDepartmentID=@StoreDepartmentID,Itemtype=@Itemtype,
  Batch=@Batch,ExpireDate=@ExpireDate,TotalCost=@TotalCost,profitPer=@profitPer,TotalQty=@TotalQty,MRP=@MRP,CashDiscPer=@CashDiscPer,
  CashDisc=@CashDisc,QtyBonus=@QtyBonus,CCPer=@CCPer,NetUnitCost=@NetUnitCost,DiscUnitCost=@DiscUnitCost,CurentSellPrice=@CurentSellPrice,newSellproce=@newSellproce,
  ItemID=@ItemID,rate=@rate,Taxper=@Taxper,TaxAmt=@TaxAmt,mode=@mode where  PurchaseDetailsID=@PurchaseDetailsID and PurchaseMasterID=@PurchaseMasterId
  update tblpurchasedetails set VenderInvoiceNum=@VenderInvoiceNum,ledgerID=@ledgerID,mode=@mode where PurchaseMasterID=@PurchaseMasterId

  --delete from purchase edit table
  delete  from tblPurchaseEditEntry  where PurchaseDetailsID=@PurchaseDetailsID
  if (select count(PurchaseDetailsID) from tblPurchaseEditEntry) > 0 begin truncate table tblPurchaseEditEntry end

  declare @Itemname nvarchar(500)
  declare @UnitCost decimal(18,2)
  declare @Dosageform nvarchar(max)
  declare @strengthlabeling nvarchar(max)

  IF(@Itemtype='1')
   BEGIN
   SET @Itemname =(select brandname from tblBrand where bid=@ItemID)
   SET @Dosageform=(select Dosageform from tblBrand where bid=@ItemID)
   SET @strengthlabeling=(select Saltwithstrength  from tblBrand where  bid=@ItemID)
   END

  ELSE IF(@Itemtype='2')
   BEGIN
   SET @Itemname =(select brandname from tblSurgicalbrand where SurgBrandId=@ItemID)
   SET @Dosageform=(select Unit from tblSurgicalbrand where SurgBrandId=@ItemID)
   SET @strengthlabeling=(select Surgicalname from tblSurgicalbrand where SurgBrandId=@ItemID)
   END

  ELSE IF(@Itemtype='3')
   BEGIN
   SET @Itemname =(select brandname from tblExtra where EId=@ItemID)
   SET @Dosageform=(select unit from tblExtra where EId=@ItemID)
   SET @strengthlabeling=(select Groupname from tblExtra where EId=@ItemID)
   END
              ---update in stock table
   update tblstock set [date]=@invoicedate,item_id=@ItemID,item_type=@Itemtype,batch=@batch,[expiry_date]=@ExpireDate,netunit_cost=@NetUnitCost,
   inward_qnty=inward_qnty+@diffqty,outward_qnty=0,newsellPrice=@newSellproce,StoreDepartmentID=@StoreDepartmentID
   where stock_type='Purchases' and details_id=@PurchaseDetailsID  and master_id=@PurchaseMasterId

   ---update in stocksummary table
   update tblstockSummary set qnty=qnty+@diffqty,lastupdatedate=getdate(),salesprice=@newSellproce,UnitCost=@NetUnitCost
   where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID

   select 'Success'
  END
ELSE
  BEGIN
  select 'Failed'
  END
END
go
go