CREATE TABLE [dbo].[MaterialTransApprove](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Branch] [nvarchar](15) NULL,
	[EntryDate] [datetime] NULL,
	[InvNo] [nvarchar](500) NULL,
	[ReturnInvoiceNo] [nvarchar](500) NULL,
	[BillType] [nvarchar](500) NULL,
	[RefNo] [nvarchar](500) NULL,
	[PONo] [nvarchar](500) NULL,
	[CustomerCode] [nvarchar](500) NULL,
	[CustomerName] [nvarchar](2000) NULL,
	[Address] [nvarchar](2000) NULL,
	[Phone] [nvarchar](500) NULL,
	[Fax] [nvarchar](500) NULL,
	[Attended] [nvarchar](2000) NULL,
	[GrossAmount] [float] NULL,
	[LessPerc] [float] NULL,
	[LessAmount] [float] NULL,
	[NetAmount] [float] NULL,
	[SR] [nvarchar](500) NULL,
	[Received] [float] NULL,
	[DnNo] [nvarchar](500) NULL,
	[QuotNo] [nvarchar](500) NULL,
	[Flag] [char](1) NULL,
	[CreditPeriod] [nvarchar](500) NULL,
	[ReceivedAmount] [float] NULL,
	[UserName] [nvarchar](500) NULL,
	[InvoiceRetNo] [float] NULL,
	[ReceivedAmt] [float] NULL,
	[ChangeAmount] [float] NULL,
	[InvoiceDate] [datetime] NULL,
	[Narration] [nvarchar](2000) NULL,
	[voucherNo] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[Remarks] [nvarchar](500) NULL,
	[AddLess] [float] NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL,
	[MatOutNo] [nvarchar](100) NULL,
	[AccCode] [nvarchar](500) NULL,
	[AccName] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL,
	[Total] [float] NULL,
	[EnteredBy] [nvarchar](500) NULL,
	[CreatedDateTime] [datetime] NULL,
 CONSTRAINT [PK_MaterialTransApprove] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[MaterialTransApproveDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Branch] [nvarchar](15) NULL,
	[EntryDate] [datetime] NULL,
	[ItemCode] [nvarchar](500) NULL,
	[ItemName] [nvarchar](2000) NULL,
	[Damage] [numeric](18, 0) NULL,
	[Unit] [nvarchar](500) NULL,
	[Qty] [float] NULL,
	[Rate] [float] NULL,
	[Amount] [float] NULL,
	[SR] [nvarchar](500) NULL,
	[SlNo] [numeric](18, 0) NULL,
	[Cost] [float] NULL,
	[InvoiceRetNo] [nvarchar](500) NULL,
	[InvoiceDate] [datetime] NULL,
	[DocumentNo] [nvarchar](500) NULL,
	[InvNo] [nvarchar](500) NULL,
	[SQTY] [float] NULL,
	[Sales] [float] NULL,
	[Stock] [float] NULL,
	[purrate] [float] NULL,
	[SALESRATE] [float] NULL,
	[MatOutNo] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[ArabicName] [nvarchar](2000) NULL,
	[QtyPUnit] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL,
	[Status] [nvarchar](500) NULL,
	[SubId] [int] NULL,
	[AppQty] [nvarchar](500) NULL,
 CONSTRAINT [PK_MaterialTransApproveDetails] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[MaterialTransReq](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Branch] [nvarchar](15) NULL,
	[EntryDate] [datetime] NULL,
	[InvNo] [nvarchar](500) NULL,
	[ReturnInvoiceNo] [nvarchar](500) NULL,
	[BillType] [nvarchar](500) NULL,
	[RefNo] [nvarchar](500) NULL,
	[PONo] [nvarchar](500) NULL,
	[CustomerCode] [nvarchar](500) NULL,
	[CustomerName] [nvarchar](2000) NULL,
	[Address] [nvarchar](2000) NULL,
	[Phone] [nvarchar](500) NULL,
	[Fax] [nvarchar](500) NULL,
	[Attended] [nvarchar](2000) NULL,
	[GrossAmount] [float] NULL,
	[LessPerc] [float] NULL,
	[LessAmount] [float] NULL,
	[NetAmount] [float] NULL,
	[SR] [nvarchar](500) NULL,
	[Received] [float] NULL,
	[DnNo] [nvarchar](500) NULL,
	[QuotNo] [nvarchar](500) NULL,
	[Flag] [char](1) NULL,
	[CreditPeriod] [nvarchar](500) NULL,
	[ReceivedAmount] [float] NULL,
	[UserName] [nvarchar](500) NULL,
	[InvoiceRetNo] [nvarchar](500) NULL,
	[ReceivedAmt] [float] NULL,
	[ChangeAmount] [float] NULL,
	[InvoiceDate] [datetime] NULL,
	[Narration] [nvarchar](2000) NULL,
	[voucherNo] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[Remarks] [nvarchar](2000) NULL,
	[AddLess] [float] NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL,
	[recno] [nvarchar](100) NULL,
	[smcode] [nvarchar](50) NULL,
	[sm] [nvarchar](100) NULL,
	[drcode] [nvarchar](50) NULL,
	[dr] [nvarchar](100) NULL,
	[AccCode] [nvarchar](500) NULL,
	[AccName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[Total] [float] NULL,
	[EnteredBy] [nvarchar](500) NULL,
	[CreatedDateTime] [datetime] NULL,
 CONSTRAINT [PK_MaterialTransReq] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[MaterialTransReqDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Branch] [nvarchar](15) NULL,
	[EntryDate] [datetime] NULL,
	[ItemCode] [nvarchar](50) NULL,
	[ItemName] [nvarchar](2000) NULL,
	[Damage] [numeric](18, 0) NULL,
	[Unit] [nvarchar](500) NULL,
	[Qty] [float] NULL,
	[Rate] [float] NULL,
	[Amount] [float] NULL,
	[SR] [nvarchar](500) NULL,
	[SlNo] [numeric](18, 0) NULL,
	[Cost] [float] NULL,
	[InvoiceRetNo] [nvarchar](500) NULL,
	[InvoiceDate] [datetime] NULL,
	[DocumentNo] [nvarchar](500) NULL,
	[InvNo] [nvarchar](500) NULL,
	[SQTY] [float] NULL,
	[Sales] [float] NULL,
	[Stock] [float] NULL,
	[purrate] [float] NULL,
	[BranchCode] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[ArabicName] [nvarchar](2000) NULL,
	[QtyPUnit] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL,
 CONSTRAINT [PK_MaterialTransReqDetails] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


alter table materialtrans add [AccCode] [nvarchar](500) NULL,
	[AccName] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL,
	[Total] [float] NULL,
	[EnteredBy] [nvarchar](500) NULL,
	[CreatedDateTime] [datetime] NULL;

	alter table materialtransout add [AccCode] [nvarchar](500) NULL,
	[AccName] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL,
	[Total] [float] NULL,
	[EnteredBy] [nvarchar](500) NULL,
	[CreatedDateTime] [datetime] NULL;


	alter table MaterialTransOutDetails add [BranchCode] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[ArabicName] [nvarchar](2000) NULL,
	[QtyPUnit] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL;

	alter table MaterialTransDetails add [BranchCode] [nvarchar](500) NULL,
	[BranchName] [nvarchar](500) NULL,
	[ArabicName] [nvarchar](2000) NULL,
	[QtyPUnit] [nvarchar](500) NULL,
	[fromstorecode] [nvarchar](500) NULL,
	[fromstore] [nvarchar](500) NULL,
	[tostorecode] [nvarchar](500) NULL,
	[tostore] [nvarchar](500) NULL;

alter table FinalProductItem add [BranchName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL;

	alter table FinalProduct add [BranchName] [nvarchar](500) NULL,
	[BranchCode] [nvarchar](500) NULL;

ALTER PROCEDURE [dbo].[SP_stockupdate]           
@todate datetime,
@BranchName varchar(500)
AS        
BEGIN       
DECLARE @stockstartdate AS datetime
set @stockstartdate=(select isnull(stockdate,'01/01/1900')'date' from stockstartdate)  
   
    Update ITEM 
SET Item.STOCK = a.QTY
FROM 
(select sum(qty)as qty,itemcode from (select isnull(sum(isnull(opstock,0)),0) as qty,ITEMCODE from OPStockEntryDetails where BranchName=@BranchName Group by ItemCode

UNION all
  select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseItem where PurchaseItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Purchase'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode
  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from InvoiceReturnItem where InvoiceReturnItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Returns'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode
    
	  UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from GoodsReceiptItem where GoodsReceiptItem.entrydate>= @stockstartdate and  entryDate <= @todate  and isnull(deleted,'N')='N' and BranchName=@BranchName   Group by ItemCode

    UNION all
   select isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE from MaterialTransApproveDetails where MaterialTransApproveDetails.entrydate>= @stockstartdate and  entryDate <= @todate  and tostore=@BranchName   Group by ItemCode


	UNION all
	select isnull(sum(qty),0) as qty,ITEMCODE from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.INVNO=MaterialTrans.INVNO  WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entrydate<=@todate and MaterialTransDetails.BranchName=@BranchName    Group by ItemCode
   UNION all
   select isnull(sum(qty),0) as qty,ITEMCODE from FinalProduct where FinalProduct.entrydate>= @stockstartdate and  entryDate <= @todate and BranchName=@BranchName  Group by ItemCode

   UNION all
   select -1*isnull(sum(isnull(qty,0)*isnull(qtypunit,1)),0) as qty,ITEMCODE from MaterialTransApproveDetails where MaterialTransApproveDetails.entrydate>= @stockstartdate and  entryDate <= @todate  and fromstore=@BranchName   Group by ItemCode


   UNION all
   select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from PurchaseReturnItem where PurchaseReturnItem.entrydate>= @stockstartdate and  entryDate <= @todate and PR='Returns'  and isnull(deleted,'N')='N' and BranchName=@BranchName Group by ItemCode 
  UNION all
    select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from InvoiceItem where InvoiceItem.entrydate>= @stockstartdate and  entryDate <= @todate and SR='Sales'  and isnull(deleted,'N')='N' and BranchName=@BranchName  Group by ItemCode
  UNION all
  select -1* isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as qty,ITEMCODE from DeliveryNoteItem where DeliveryNoteItem.entrydate>= @stockstartdate and  entryDate <= @todate and  Status=0  and isnull(deleted,'N')='N' and BranchName=@BranchName Group by ItemCode 
  UNION all
  select -1* isnull(sum(qty),0) as qty,ITEMCODE from FinalProductItem where FinalProductItem.entrydate>= @stockstartdate and  entryDate <= @todate and BranchName=@BranchName Group by ItemCode  
 UNION all
  select -1* isnull(sum(qty),0) as qty,ITEMCODE from damageItem where damageItem.Date>= @stockstartdate and  Date <= @todate and BranchName=@BranchName  Group by ItemCode
  UNION all
  select -1* isnull(sum(qty),0)as qty,ITEMCODE from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
  WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate<= @todate  AND MaterialTransOutDetails.fromstore IN (SELECT NAME FROM BRANCHMASTER ) and MaterialTransOutDetails.BranchName=@BranchName
    Group by ItemCode
   UNION all
   select  isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Qty,ITEMCODE from QtyAdjustmentDetails Where entryDate <= @todate and entryDate>=@stockstartdate and BranchName=@BranchName  Group by ItemCode) t 

    

 group by  t.itemcode )a



   WHERE item.itemcode = a.itemcode 


END







create PROCEDURE [dbo].[sp_SaveMatTransferIn]
@voucherNo varchar(50)  
AS  
BEGIN 
select  MaterialTrans.voucherNo as InvNo,MaterialTrans.EntryDate,MaterialTrans.PONo,MaterialTrans.RefNo,MaterialTrans.fromstore as StoreName,MaterialTrans.tostore as StoreName1,MaterialTrans.Attended,MaterialTrans.NetAmount,
MaterialTrans.Remarks,MaterialTransDetails.SlNo,MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,MaterialTransDetails.Unit,MaterialTransDetails.Amount,MaterialTransDetails.ItemCode,
MaterialTransDetails.ItemName,MaterialTransDetails.Qty,MaterialTransDetails.Rate,
MaterialTransDetails.SalesRate
from  MaterialTrans inner join MaterialTransDetails on MaterialTrans.voucherNo=MaterialTransDetails.DocumentNo  
 where  MaterialTrans.voucherNo=@voucherNo  
END


create PROCEDURE [dbo].[sp_SaveMatTransferOut]
@voucherNo varchar(50)  
AS  
BEGIN 
select  MaterialTransOut.voucherNo as InvNo,MaterialTransOut.EntryDate,MaterialTransOut.PONo,MaterialTransOut.RefNo,MaterialTransOut.fromstore as StoreName,MaterialTransOut.tostore as StoreName1,MaterialTransOut.Attended,MaterialTransOut.NetAmount,
MaterialTransOut.Remarks,MaterialTransOutDetails.SlNo,MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Unit,MaterialTransOutDetails.Qty * MaterialTransOutDetails.Cost as Amount,MaterialTransOutDetails.ItemCode,
MaterialTransOutDetails.ItemName,MaterialTransOutDetails.Qty,MaterialTransOutDetails.Cost as Rate

from  MaterialTransOut inner join MaterialTransOutDetails on MaterialTransOut.voucherNo=MaterialTransOutDetails.DocumentNo  
 where  MaterialTransOut.voucherNo=@voucherNo  
END




create PROCEDURE [dbo].[sp_SaveMatTransferReq]
@voucherNo varchar(50)  
AS  
BEGIN 
select  MaterialTransReq.voucherNo as InvNo,MaterialTransReq.EntryDate,MaterialTransReq.PONo,MaterialTransReq.RefNo,MaterialTransReq.fromstore as StoreName,MaterialTransReq.tostore as StoreName1,MaterialTransReq.Attended,MaterialTransReq.NetAmount,
MaterialTransReq.Remarks,MaterialTransReqDetails.SlNo,MaterialTransReqDetails.ItemName,MaterialTransReqDetails.Qty,MaterialTransReqDetails.Unit,MaterialTransReqDetails.Qty * MaterialTransReqDetails.Cost as Amount,MaterialTransReqDetails.ItemCode,
MaterialTransReqDetails.ItemName,MaterialTransReqDetails.Qty,MaterialTransReqDetails.Cost as Rate

from  MaterialTransReq inner join MaterialTransReqDetails on MaterialTransReq.voucherNo=MaterialTransReqDetails.DocumentNo  
 where  MaterialTransReq.voucherNo=@voucherNo  
END