GO
/****** Object:  StoredProcedure [dbo].[sp_SaveInvoice]    Script Date: 20-04-2025 12:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER procedure [dbo].[sp_SaveInvoice]
(
 @InvNo varchar(50), @BranchCode varchar(10)
 )
AS
begin
SELECT Invoice.DELDate,Invoice.Branch,Invoice.CreditPeriod,Invoice.Bank1,Invoice.Bank2,Invoice.Bank3,Invoice.Bank4,
Invoice.Del1,Invoice.Del2,Invoice.Del3,Invoice.Del4,Invoice.PODate,Invoice.Freight,Invoice.Retention, UPPER(LEFT(Invoice.BillType, 1)) + LOWER(SUBSTRING(Invoice.BillType, 2, LEN(Invoice.BillType))) AS BillType,
Invoice.InvNo,Invoice.EntryDate,Invoice.PONo,Invoice.DnNo,Invoice.RefNo,Invoice.CustomerCode,Invoice.CustomerName,
Invoice.Address,Invoice.Phone,Invoice.Fax,Invoice.Attended,Invoice.Narration,Invoice.GrossAmount,Invoice.NetAmount,
Invoice.LessAmount  AS less,InvoiceItem.SlNo,InvoiceItem.ItemCode,InvoiceItem.ItemName,Invoice.PlateNo,invoice.modelno as Model,JobCard.EntryDate as JobOrderDate,
InvoiceItem.PartNo,InvoiceItem.Rate,InvoiceItem.Unit,InvoiceItem.Qty,InvoiceItem.Amount,Item.CategoryName,InvoiceItem.ArabicName as ItemNameAr,
CONVERT(nchar, isnull(HijriDate,'01/01/1900'), 131) as HijriDate,isnull(invoice.QuotNo,'') as QuotNo
,ISNULL(INVOICEITEM.VATPER,0) AS VATPER,ISNULL(INVOICEITEM.VATAMT,0)AS VATAMT,ISNULL(INVOICE.VATTOTAL,0)AS VATTOTAL
,ISNULL(INVOICE.Vatno,'')AS Vatno,isnull(invoiceitem.rateafterdisc,0) as Rateafterdisc,ISNULL(INVOICE.REMARKS1,'') AS REMARKSS,INVOICE.MOBILE
,ISNULL(CONVERT(VARCHAR(25),D.ENTRYDATE,103),CONVERT(VARCHAR(25),INVOICE.ENTRYDATE,103)) AS DNDATE,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress 
 ,CONVERT(VARCHAR(25),Invoice.EntryDate,103) 'DATE' ,
 dbo.currency_conversion(Invoice.NetAmount) as ARABICNETAMOUNT,isnull(pinvno,'')as pinvno,isnull(bankcode,'')as bankcode,isnull(bankname,'')as bankname,isnull(bankamount,'')as bankamount 
 ,isnull(vat,0)As vat,isnull(EINVOICE.qrcode,'')as qrcode

 ,isnull(C.ContactArabic,'')as ContactArabic,isnull(C.phoneArabic,'')as phoneArabic,isnull(C.FaxArabic,'')as FaxArabic,isnull(C.emailArabic,'')as emailArabic,isnull(C.mobArabic,'')as mobArabic,
 isnull(C.vatnoArabic,'')as vatnoArabic,isnull(C.BuildingNo,'')as BuildingNo,isnull(C.StreetName,'')as StreetName,isnull(C.District,'')as District,isnull(C.City,'')as City,isnull(C.Country,'')as Country,
 isnull(C.PostalCode,'')as PostalCode,isnull(C.AdditionalNo,'')as AdditionalNo,isnull(C.OtherSellerNo,'')as OtherSellerNo,isnull(C.BuildingNoArabic,'')as BuildingNoArabic,isnull(C.StreetNameArabic,'')as StreetNameArabic,
 isnull(C.DistrictArabic,'')as DistrictArabic,isnull(C.CityArabic,'')as CityArabic,isnull(C.CountryArabic,'')as CountryArabic,isnull(C.PostalCodeArabic,'')as PostalCodeArabic,isnull(C.AdditionalNoArabic,'')as AdditionalNoArabic 
 ,isnull(C.OtherSellerNoArabic,'')as OtherSellerNoArabic,isnull(cd.vatno,'')as cdvatno
  ,ISNULL(CD.Name,'')AS CDNAME,ISNULL(CD.ADDRESS1,'')AS CDADDRESS,ISNULL(CD.namear,'') as CDArabicname,isnull(CD.address1ar,'') as CDArabicAddress ,isnull(CD.ContactArabic,'')as CDContactArabic,isnull(CD.phoneArabic,'')as CDphoneArabic
 ,isnull(CD.FaxArabic,'')as CDFaxArabic,isnull(CD.emailArabic,'')as CDemailArabic,isnull(CD.mobArabic,'')as CDmobArabic,
 isnull(CD.vatnoArabic,'')as CDvatnoArabic,isnull(CD.Address1,'')as CDBuildingNo,isnull(CD.Address2,'')as CDStreetName,isnull(CD.District,'')as CDDistrict,isnull(CD.Address3,'')as CDCity,isnull(CD.Country,'')as CDCountry,
 isnull(CD.PostalCode,'')as CDPostalCode,isnull(CD.AdditionalNo,'')as CDAdditionalNo,isnull(CD.OtherSellerNo,'')as CDOtherSellerNo,isnull(CD.Address1AR,'')as CDBuildingNoArabic,isnull(CD.Address2AR,'')as CDStreetNameArabic,
 isnull(CD.DistrictArabic,'')as CDDistrictArabic,isnull(CD.Address3AR,'')as CDCityArabic,isnull(CD.CountryArabic,'')as CDCountryArabic,isnull(CD.PostalCodeArabic,'')as CDPostalCodeArabic,isnull(CD.AdditionalNoArabic,'')as CDAdditionalNoArabic 
 ,isnull(CD.OtherSellerNoArabic,'')as CDOtherSellerNoArabic,isnull(CD.VATNO,'')as CDVATNO
 ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
 FROM Invoice INNER JOIN InvoiceItem ON Invoice.InvNo = InvoiceItem.InvNo AND Invoice.SR = 'Sales'and  InvoiceItem.SR = 'Sales' 
LEFT JOIN DELIVERYNOTE D ON D.DNNO=INVOICEITEM.DNNO LEFT JOIN CUSTOMER C ON C.CODE=INVOICE.CUSTOMERCODE 
LEFT JOIN EINVOICE ON EINVOICE.EINVNO=INVOICE.INVNO
LEFT JOIN Item ON InvoiceItem.ItemCode = Item.ItemCode 
LEFT JOIN JobCard ON Invoice.JobCardNo=JobCard.JobCardNo

JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
where Invoice.InvNo=@InvNo



end