create PROCEDURE sp_ProfInvoiceDetails  
    @fromdate DATE,  
    @todate DATE,  
    @CustCode NVARCHAR(50),  
    @BranchCode NVARCHAR(50),  
    @ItemCode NVARCHAR(50)  
AS  
BEGIN  
    SELECT 
        PI.Id,  
        PI.Branch,  
        PI.EntryDate,  
        PI.InvNo,  
        PI.ReturnInvoiceNo,  
        PI.BillType,  
        PI.RefNo,  
        PI.PONo,  
        PI.CustomerCode,  
        PI.CustomerName,  
        PI.Address,  
        PI.Phone,  
        PI.Fax,  
        PI.Attended,  
        PI.GrossAmount,  
        PI.LessPerc,  
        PI.LessAmount,  
        PI.NetAmount,  
        PI.SR,  
        PI.AddLess,  
        PI.Remarks,  
        PI.Received,  
        PI.DnNo,  
        PI.QuotNo,  
        PI.Flag,  
        PI.CreditPeriod,  
        PI.ReceivedAmount,  
        PI.UserName,  
        PI.InvoiceDate,  
        PI.Narration,  
        PI.voucherNo,  
        PI.Commision,  
        PI.Status,  
        PI.Bank1,  
        PI.Bank2,  
        PI.Bank3,  
        PI.Bank4,  
        PI.PODate,  
        PI.Freight,  
        PI.Retention,  
        PI.DelDate,  
        PI.ProjectNo,  
        PI.Deleted,  
        PI.AccountCode,  
        PI.AccountName,  
        PI.hijridate,  
        PI.Mobile,  
        PI.vattotal,  
        PI.Vatno,  
        PI.crdays,  
        PI.invdueDate,  
        PI.BranchName,  
        PI.BranchCode,  
        PI.TotalIncDiscAmt,  
        PII.ItemCode,  
        PII.ItemName,  
        PII.Damage,  
        PII.Unit,  
        PII.Qty,  
        PII.Rate,  
        PII.Amount,  
        PII.SR AS ItemSR,  
        PII.Cost,  
        PII.InvoiceDate AS ItemInvoiceDate,  
        PII.DocumentNo,  
        PII.InvNo AS ItemInvNo,  
        PII.SQTY,  
        PII.Sales,  
        PII.QtyPerUnit,  
        PII.TOTQty,  
        PII.PartNo,  
        PII.stdcost,  
        PII.RateAfterDisc,  
        PII.RetQty,  
        PII.DNNo,  
        PII.VATPER,  
        PII.VATAMT,  
        PII.DiscPer,  
        PII.DiscAmt,  
        PII.LineTotal  
    FROM 
        ProformaInvoice PI  
    LEFT JOIN 
        ProformaInvoiceItem PII ON PI.InvNo = PII.InvNo  
    WHERE  
        PI.EntryDate BETWEEN @fromdate AND @todate  
        AND (@CustCode IS NULL OR @CustCode = '' OR PI.CustomerCode = @CustCode)  
    AND (@BranchCode IS NULL OR @BranchCode = '' OR PI.BranchCode = @BranchCode)  
    AND (@ItemCode IS NULL OR @ItemCode = '' OR PII.ItemCode = @ItemCode);
END;

go

create PROCEDURE sp_ProfInvoiceRegister  
    @fromdate DATE,  
    @todate DATE,  
    @CustCode NVARCHAR(50),  
    @BranchCode NVARCHAR(50),  
    @BillType NVARCHAR(50)  
AS  
BEGIN  
    SELECT 
        PI.Id,  
        PI.Branch,  
        PI.EntryDate,  
        PI.InvNo,  
        PI.ReturnInvoiceNo,  
        PI.BillType,  
        PI.RefNo,  
        PI.PONo,  
        PI.CustomerCode,  
        PI.CustomerName,  
        PI.Address,  
        PI.Phone,  
        PI.Fax,  
        PI.Attended,  
        PI.GrossAmount,  
        PI.LessPerc,  
        PI.LessAmount,  
        PI.NetAmount,  
        PI.SR,  
        PI.AddLess,  
        PI.Remarks,  
        PI.Received,  
        PI.DnNo,  
        PI.QuotNo,  
        PI.Flag,  
        PI.CreditPeriod,  
        PI.ReceivedAmount,  
        PI.UserName,  
        PI.InvoiceDate,  
        PI.Narration,  
        PI.voucherNo,  
        PI.Commision,  
        PI.Status,  
        PI.Bank1,  
        PI.Bank2,  
        PI.Bank3,  
        PI.Bank4,  
        PI.PODate,  
        PI.Freight,  
        PI.Retention,  
        PI.DelDate,  
        PI.ProjectNo,  
        PI.Deleted,  
        PI.AccountCode,  
        PI.AccountName,  
        PI.hijridate,  
        PI.Mobile,  
        PI.vattotal,  
        PI.Vatno,  
        PI.crdays,  
        PI.invdueDate,  
        PI.BranchName,  
        PI.BranchCode,  
        PI.TotalIncDiscAmt,  
        PII.ItemCode,  
        PII.ItemName,  
        PII.Damage,  
        PII.Unit,  
        PII.Qty,  
        PII.Rate,  
        PII.Amount,  
        PII.SR AS ItemSR,  
        PII.Cost,  
        PII.InvoiceDate AS ItemInvoiceDate,  
        PII.DocumentNo,  
        PII.InvNo AS ItemInvNo,  
        PII.SQTY,  
        PII.Sales,  
        PII.QtyPerUnit,  
        PII.TOTQty,  
        PII.PartNo,  
        PII.stdcost,  
        PII.RateAfterDisc,  
        PII.RetQty,  
        PII.DNNo,  
        PII.VATPER,  
        PII.VATAMT,  
        PII.DiscPer,  
        PII.DiscAmt,  
        PII.LineTotal  
    FROM 
        ProformaInvoice PI  
    LEFT JOIN 
        ProformaInvoiceItem PII ON PI.InvNo = PII.InvNo  
    WHERE  
        PI.EntryDate BETWEEN @fromdate AND @todate  
        AND  (@CustCode IS NULL OR @CustCode = '' OR PI.CustomerCode = @CustCode)  
    AND (@BranchCode IS NULL OR @BranchCode = '' OR PI.BranchCode = @BranchCode)  
    AND (@BillType IS NULL OR @BillType = '' OR PI.BillType = @BillType);
END;

go 

create PROCEDURE [dbo].[sp_SaveProformaInvoice]  
 @InvNo varchar(50)  
  
AS  
begin  
SELECT ProformaInvoice.DELDate,ProformaInvoice.Branch,ProformaInvoice.CreditPeriod,ProformaInvoice.Bank1,ProformaInvoice.Bank2,ProformaInvoice.Bank3,ProformaInvoice.Bank4,  
ProformaInvoice.Del1,ProformaInvoice.Del2,ProformaInvoice.Del3,ProformaInvoice.Del4,ProformaInvoice.PODate,ProformaInvoice.Freight,ProformaInvoice.Retention,ProformaInvoice.BillType,  
ProformaInvoice.InvNo,ProformaInvoice.EntryDate,ProformaInvoice.PONo,ProformaInvoice.DnNo,ProformaInvoice.RefNo,ProformaInvoice.CustomerCode,ProformaInvoice.CustomerName,  
ProformaInvoice.Address,ProformaInvoice.Phone,ProformaInvoice.Fax,ProformaInvoice.Attended,ProformaInvoice.Narration,ProformaInvoice.GrossAmount,ProformaInvoice.NetAmount,  
ProformaInvoice.LessAmount + dbo.ProformaInvoice.AddLess AS less,ProformaInvoiceItem.SlNo,ProformaInvoiceItem.ItemCode,ProformaInvoiceItem.ItemName,  
ProformaInvoiceItem.PartNo,ProformaInvoiceItem.Rate,ProformaInvoiceItem.Unit,ProformaInvoiceItem.Qty,ProformaInvoiceItem.Amount,  
CONVERT(nchar, isnull(HijriDate,'01/01/1900'), 131) as HijriDate,isnull(ProformaInvoice.QuotNo,'') as QuotNo  
,ISNULL(ProformaInvoiceITEM.VATPER,0) AS VATPER,ISNULL(ProformaInvoiceITEM.VATAMT,0)AS VATAMT,ISNULL(ProformaInvoice.VATTOTAL,0)AS VATTOTAL  
,ISNULL(ProformaInvoice.Vatno,'')AS Vatno,isnull(ProformaInvoiceitem.rateafterdisc,0) as Rateafterdisc,ISNULL(ProformaInvoice.REMARKS1,'') AS REMARKSS,ProformaInvoice.MOBILE  
,ISNULL(CONVERT(VARCHAR(25),D.ENTRYDATE,103),CONVERT(VARCHAR(25),ProformaInvoice.ENTRYDATE,103)) AS DNDATE,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress   
 ,CONVERT(VARCHAR(25),ProformaInvoice.EntryDate,103) 'DATE' ,  
 dbo.currency_conversion(ProformaInvoice.NetAmount) as ARABICNETAMOUNT  
 FROM ProformaInvoice INNER JOIN ProformaInvoiceItem ON ProformaInvoice.InvNo = ProformaInvoiceItem.InvNo AND ProformaInvoice.SR = 'Sales'and  ProformaInvoiceItem.SR = 'Sales'   
LEFT JOIN DELIVERYNOTE D ON D.DNNO=ProformaInvoiceITEM.DNNO LEFT JOIN CUSTOMER C ON C.CODE=ProformaInvoice.CUSTOMERCODE where ProformaInvoice.InvNo=@InvNo  
  
  
end  

go

DISABLE TRIGGER ALL ON ProformaInvoice;