IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[VATCategoryDetails]') AND type in (N'U'))
Create Table VATCategoryDetails
(
Id int IDENTITY(1, 1) NOT NULL primary key,
Code nvarchar(20) NULL,
DescriptionEN nvarchar(2000) NULL,
DescriptionAR nvarchar(2000) NULL,
ExemptionCode nvarchar(500) NULL,
ExemptionTextEN nvarchar(2000) NULL,
ExemptionTextAR nvarchar(2000) NULL
);




go


SET IDENTITY_INSERT [dbo].[VATCategoryDetails] ON 
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (1, N'S', N'Standard rate', N'التوريدات الخاضعة للضريبة', N'', N'', N'')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (2, N'E', N'Exempt from Tax', N'التوريدات المعفاة', N'VATEX-SA-29', N'Financial services mentioned in Article 29 of the VAT Regulations', N'الخدمات المالية')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (3, N'E', N'Exempt from Tax', N'التوريدات المعفاة', N'VATEX-SA-29-7', N'Life insurance services mentioned in Article 29 of the VAT Regulations', N'عقد تأمين على الحياة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (4, N'E', N'Exempt from Tax', N'التوريدات المعفاة', N'VATEX-SA-30', N'Real estate transactions mentioned in Article 30 of the VAT Regulations', N'التوريدات العقارية المعفاة من الضريبة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (5, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-32', N'Export of goods', N'صادرات السلع من المملكة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (6, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-33', N'Export of services', N'صادرات الخدمات من المملكة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (7, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-34-1', N'The international transport of Goods ', N'النقل الدولي للسلع')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (8, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-34-2', N'international transport of passengers', N'النقل الدولي للركاب')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (9, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-34-3', N'services directly connected and incidental to a Supply of international passenger transport', N'الخدمات المرتبطة مباشرة أو عرضيًا بتوريد النقل الدولي للركاب')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (10, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-34-4', N'Supply of a qualifying means of transport', N'توريد وسائل النقل المؤهلة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (11, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-34-5', N'Any services relating to Goods or passenger transportation, as defined in article twenty five of these Regulations', N'الخدمات ذات الصلة بنقل السلع أو الركاب، وفقًا للتعريف الوارد بالمادة الخامسة والعشرين من الالئحة التنفيذية لنظام ضريبة القيامة المضافة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (12, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-35', N'Medicines and medical equipment', N'الأدوية والمعدات الطبية')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (13, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-36', N'Qualifying metals', N'المعادن المؤهلة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (14, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-EDU', N'Private education to citizen ', N'الخدمات التعليمية الخاصة للمواطنين')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (15, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-HEA', N'Private healthcare to citizen', N'الخدمات الصحية الخاصة للمواطنين')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (16, N'Z', N'Zero rated goods', N'التوريدات الخاضعة لنسبة الصفر', N'VATEX-SA-MLTRY', N'supply of qualified military goods ', N'توريد السلع العسكرية المؤهلة')
GO
INSERT [dbo].[VATCategoryDetails] ([Id], [Code], [DescriptionEN], [DescriptionAR], [ExemptionCode], [ExemptionTextEN], [ExemptionTextAR]) VALUES (17, N'O', N'Services outside scope of tax / Not subject to VAT', N'التوريدات الخاضعة للضريبة', N'VATEX-SA-OOS', N'', N'')
GO
SET IDENTITY_INSERT [dbo].[VATCategoryDetails] OFF
GO




IF NOT EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'item' AND COLUMN_NAME = 'ZatcaTypeCode'
)
BEGIN
	   ALTER TABLE item
	ADD ZatcaTypeCode nvarchar(2) null,
	ExemptionCode nvarchar(50) null,
	ExemptionTextEN nvarchar(max);
END
go




IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'MasterValue' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.MasterValue (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Name NVARCHAR(max),
        [Description] [nvarchar](max) NULL,
	[MasterType] [nvarchar](50) NULL,
	[FieldOne] [nvarchar](max) NULL,
	[FieldTwo] [nvarchar](max) NULL,
	[FieldThree] [nvarchar](max) NULL,
	[FieldFour] [nvarchar](max) NULL
    );
END
go

CREATE TABLE [dbo].[InvoiceICV](
	[InvIcv] [int] IDENTITY(1,1) Primary Key NOT NULL,
	[DocNo] [nvarchar](500) NOT NULL,
	[DOCTYPE] [nvarchar](500) NOT NULL,
	[DocDate] [datetime] not null,
	[invsubstatus] [nvarchar](400) NULL,
	[invsubdetails] [nvarchar](4000) NULL,
	[invsubWarningMsg] [nvarchar](4000) NULL,
	[invsubErrorMsg] [nvarchar](4000) NULL,
	[invsubClearanceStatus] [nvarchar](4000) NULL,
	[invsubValidationStatus] [nvarchar](4000) NULL,
	[invsubinfoMessagesType] [nvarchar](4000) NULL,
	[invsubinfoMessagesCode] [nvarchar](4000) NULL,
	[invsubinfoMessagesCATEGORY] [nvarchar](4000) NULL,
	[invsubinfoMessagesMESSAGE] [nvarchar](4000) NULL,
	[invsubinfoMessagesStatus] [nvarchar](4000) NULL,
	[invsubWarningMessagesType] [nvarchar](4000) NULL,
	[invsubWarningMessagesCode] [nvarchar](4000) NULL,
	[invsubWarningMessagesCATEGORY] [nvarchar](4000) NULL,
	[invsubWarningMessagesMESSAGE] [nvarchar](4000) NULL,
	[invsubWarningMessagesStatus] [nvarchar](4000) NULL,
	[invsubErrorMessagesType] [nvarchar](4000) NULL,
	[invsubErrorMessagesCode] [nvarchar](4000) NULL,
	[invsubErrorMessagesCATEGORY] [nvarchar](4000) NULL,
	[invsubErrorMessagesMESSAGE] [nvarchar](4000) NULL,
	[invsubErrorMessagesStatus] [nvarchar](4000) NULL,
	[INVOICEHASH] [nvarchar](4000) NULL,
	[PREVIOUSHASH] [nvarchar](4000) NULL,
	[Submitedxml] [varchar](max) NULL,
	[Finalxml] [varchar](max) NULL
	);
go

Create or Alter Proc spGetInvoiceICVData
(
@FromDate datetime,
@ToDate datetime,
@ReportType int = 0,
@InvType nvarchar(30)
)
As
Begin
declare  @StandardInvoiceCount int, @SimplifiedInvoiceCount int, @StandardCreditNoteCount int,  
@SimplifiedCreditNoteCount int, @StandardDebitNoteCount int , @SimplifiedDebitNoteCount int,
@SuccessfullCount int,@WarningCount int, @FailedCount int
select @StandardInvoiceCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Invoice' and DocDate between @FromDate and @ToDate
select @SimplifiedInvoiceCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Invoice' and DocDate between @FromDate and @ToDate
select @StandardCreditNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Credit Note' and DocDate between @FromDate and @ToDate
select @SimplifiedCreditNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Credit Note' and DocDate between @FromDate and @ToDate
select @StandardDebitNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Debit Note' and DocDate between @FromDate and @ToDate
select @SimplifiedDebitNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Debit Note' and DocDate between @FromDate and @ToDate
if @ReportType = 0
begin
select @StandardInvoiceCount + @SimplifiedInvoiceCount as InvoiceCount, @StandardCreditNoteCount + @SimplifiedCreditNoteCount as InvoiceRtnCount, @StandardDebitNoteCount + @SimplifiedDebitNoteCount as DebitInvoiceCount
end
else if @ReportType = 1
begin
select @StandardInvoiceCount as StandardInvoiceCount, @SimplifiedInvoiceCount as SimplifiedInvoiceCount
end
else if @ReportType = 2
begin
select @StandardCreditNoteCount as StandardCreditNoteCount, @SimplifiedCreditNoteCount as SimplifiedCreditNoteCount
end
else if @ReportType = 3
begin
select @StandardDebitNoteCount as StandardDebitNoteCount, @SimplifiedDebitNoteCount as SimplifiedDebitNoteCount
end
else if @ReportType > 3 and @ReportType < 10
begin
select @SuccessfullCount = count(*) from InvoiceICV Where DOCTYPE =@InvType  and invsubValidationStatus = 'PASS' and DocDate between @FromDate and @ToDate
select @WarningCount = count(*) from InvoiceICV Where DOCTYPE = @InvType and invsubValidationStatus = 'WARNING' and DocDate between @FromDate and @ToDate
select @FailedCount = count(*) from InvoiceICV Where DOCTYPE = @InvType and invsubValidationStatus = 'ERROR' and DocDate between @FromDate and @ToDate
select @SuccessfullCount as SuccessfullCount,@WarningCount as WarningCount,@FailedCount as FailedCount,@InvType +' Submitted Details' as CardTitle
end
else if @ReportType = 10
begin
SELECT 
        DocNo AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubClearanceStatus AS [Message]
    FROM InvoiceICV 
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'PASS' and  DocDate BETWEEN @FromDate AND @ToDate;
end
else if @ReportType = 11
begin
SELECT 
        DocNo AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubWarningMessagesMESSAGE AS [Message]
    FROM InvoiceICV 
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'WARNING' and  DocDate BETWEEN @FromDate AND @ToDate;
end
else if @ReportType = 12
begin
SELECT 
        DocNo AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubErrorMessagesMESSAGE AS [Message]
    FROM InvoiceICV 
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'ERROR' and  DocDate BETWEEN @FromDate AND @ToDate;
end
end

go


Create or ALTER proc 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,Invoice.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,
InvoiceItem.PartNo,InvoiceItem.Rate,InvoiceItem.Unit,InvoiceItem.Qty,InvoiceItem.Amount,
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.BuildingNo,'')as CDBuildingNo,isnull(CD.StreetName,'')as CDStreetName,isnull(CD.District,'')as CDDistrict,isnull(CD.City,'')as CDCity,isnull(CD.Country,'')as CDCountry,
 isnull(CD.PostalCode,'')as CDPostalCode,isnull(CD.AdditionalNo,'')as CDAdditionalNo,isnull(CD.OtherSellerNo,'')as CDOtherSellerNo,isnull(CD.BuildingNoArabic,'')as CDBuildingNoArabic,isnull(CD.StreetNameArabic,'')as CDStreetNameArabic,
 isnull(CD.DistrictArabic,'')as CDDistrictArabic,isnull(CD.CityArabic,'')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

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



end
go


Create or ALTER Proc sp_SaveInvoiceReturn
(
@InvoiceRetNo varchar(50),
@BranchCode varchar(10)
)
AS
begin

SELECT InvoiceReturn.InvoiceRetNo,InvoiceReturn.DELDate,InvoiceReturn.Branch,InvoiceReturn.CreditPeriod,InvoiceReturn.Bank1,InvoiceReturn.Bank2,InvoiceReturn.Bank3,InvoiceReturn.Bank4,
InvoiceReturn.Del1,InvoiceReturn.Del2,InvoiceReturn.Del3,InvoiceReturn.Del4,InvoiceReturn.PODate,InvoiceReturn.Freight,InvoiceReturn.Retention,InvoiceReturn.BillType,
InvoiceReturn.InvNo,InvoiceReturn.EntryDate,InvoiceReturn.PONo,InvoiceReturn.DnNo,InvoiceReturn.RefNo,InvoiceReturn.CustomerCode,InvoiceReturn.CustomerName,
InvoiceReturn.Address,InvoiceReturn.Phone,InvoiceReturn.Fax,InvoiceReturn.Attended,InvoiceReturn.Narration,InvoiceReturn.GrossAmount,InvoiceReturn.NetAmount,
InvoiceReturn.LessAmount  AS less,InvoiceReturnItem.SlNo,InvoiceReturnItem.ItemCode,InvoiceReturnItem.ItemName,
InvoiceReturnItem.PartNo,InvoiceReturnItem.Rate,InvoiceReturnItem.Unit,InvoiceReturnItem.Qty,InvoiceReturnItem.Amount,
CONVERT(nchar, isnull(HijriDate,'01/01/1900'), 131) as HijriDate,isnull(InvoiceReturn.QuotNo,'') as QuotNo
,ISNULL(InvoiceReturnItem.VATPER,0) AS VATPER,ISNULL(InvoiceReturnItem.VATAMT,0)AS VATAMT,ISNULL(InvoiceReturn.VATTOTAL,0)AS VATTOTAL
,ISNULL(InvoiceReturn.Vatno,'')AS Vatno,isnull(InvoiceReturnItem.rateafterdisc,0) as Rateafterdisc,ISNULL(InvoiceReturn.REMARKS1,'') AS REMARKSS,InvoiceReturn.MOBILE
,ISNULL(CONVERT(VARCHAR(25),D.ENTRYDATE,103),CONVERT(VARCHAR(25),InvoiceReturn.ENTRYDATE,103)) AS DNDATE,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress 
 ,CONVERT(VARCHAR(25),InvoiceReturn.EntryDate,103) 'DATE' ,
 dbo.currency_conversion(InvoiceReturn.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(EInvoiceReturn.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.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.BuildingNo,'')as CDBuildingNo,isnull(CD.StreetName,'')as CDStreetName,isnull(CD.District,'')as CDDistrict,isnull(CD.City,'')as CDCity,isnull(CD.Country,'')as CDCountry,
 isnull(CD.PostalCode,'')as CDPostalCode,isnull(CD.AdditionalNo,'')as CDAdditionalNo,isnull(CD.OtherSellerNo,'')as CDOtherSellerNo,isnull(CD.BuildingNoArabic,'')as CDBuildingNoArabic,isnull(CD.StreetNameArabic,'')as CDStreetNameArabic,
 isnull(CD.DistrictArabic,'')as CDDistrictArabic,isnull(CD.CityArabic,'')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 InvoiceReturn INNER JOIN InvoiceReturnItem ON  InvoiceReturn.InvoiceRetNo = InvoiceReturnItem.InvoiceRetNo AND  InvoiceReturn.SR = 'Returns' 
LEFT JOIN DELIVERYNOTE D ON D.DNNO=InvoiceReturnItem.DNNO LEFT JOIN CUSTOMER C ON C.CODE=InvoiceReturn.CUSTOMERCODE 
LEFT JOIN EInvoiceReturn ON EInvoiceReturn.EInvRetNO=InvoiceReturn.InvoiceRetNo AND  InvoiceReturn.SR = 'Returns'  

JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
where InvoiceReturn.InvoiceRetNo=@InvoiceRetNo


 
end


SET ANSI_NULLS ON
go


Create or ALTER Proc sp_SavePurchase
(
@DocNo varchar(50),
@BranchCode varchar(10)
)
AS  
BEGIN 
select  Purchase.DocumentNo,Purchase.EntryDate,Purchase.PONo,Purchase.RefNo,Purchase.SupplierName,Purchase.Address,Purchase.Attended,Purchase.NetAmount,
purchase.supinvno as  invoiceno,PurchaseItem.SlNo,PurchaseItem.ItemName,PurchaseItem.Qty,PurchaseItem.Rate,PurchaseItem.Unit,PurchaseItem.Amount,PurchaseItem.ItemCode,
PurchaseItem.SalesRate,ISNULL(PurchaseItem.VATPER,0) AS VATPER,ISNULL(PurchaseItem.VATAMT,0)AS VATAMT,ISNULL(Purchase.VATTOTAL,0)AS VATTOTAL,
ISNULL(Purchase.Vatno,'') AS Vatno,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress 
,isnull(fRate,0) as Frate,isnull(pcrate,0)as pcrate,isnull(Purchase.GrossAmount,0) as  Grossamount,isnull(Purchase.LESS,0) as  less,currency,exchangerate,purtype
,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
from  Purchase 
inner join PurchaseItem on Purchase.DocumentNo=PurchaseItem.DocumentNo  
LEFT JOIN SUPPLIER C ON C.CODE=Purchase.SUPPLIERCODE 

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

 where PurchaseItem.PR='Purchase' and Purchase.DocumentNo=@DocNo  
END

go

Create or ALTER PROCEDURE SP_CUSOTMELEDGER
(
@CUSTGROUP NVARCHAR(500),
@CustCode NVARCHAR(500),
@FROM DATETIME,
@TO DATETIME,
@Branchcode nvarchar(500) 
)
AS
BEGIN
SELECT ISNULL(CUSTOMER.CUSTGROUP,'')AS CUSTGROUP, dbo.Customer.Code,dbo.Customer.Name,
((SELECT ISNULL(SUM(ISNULL(Balance,0)),0) FROM CustomerOPEntryUpdated WHERE CustomerCode=Customer.Code AND  (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerOPEntryUpdated.BranchCode, '') = @BranchCode))
+isnull(dbo.udf_GenOpening(@FROM,Customer.Code,@Branchcode),0)) AS opening
,'' AS VoucherNo,0 AS Amount,'OPENING' AS Type,'' AS Narration,'01/01/2000' AS ENTRYDATE ,'' AS PONO
  , '' as   Branchname
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage

FROM dbo.customer
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
 WHERE ISNULL(Customer.CUSTGROUP,0)=ISNULL(@CUSTGROUP,ISNULL(Customer.CUSTGROUP,0)) AND ISNULL(dbo.Customer.Code,0)=ISNULL(@CustCode,ISNULL(Customer.Code,0))

 UNION ALL
SELECT ISNULL(CUSTOMER.CUSTGROUP,'')AS CUSTGROUP, dbo.Customer.Code,dbo.Customer.Name,
((SELECT ISNULL(SUM(ISNULL(Balance,0)),0) FROM CustomerOPEntryUpdated WHERE CustomerCode=Customer.Code AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(CustomerOPEntryUpdated.BranchCode, '') = @BranchCode))
+isnull(dbo.udf_GenOpening(@FROM,Customer.Code,@Branchcode),0)) AS opening
,dbo.TBLACCOUNTING.VoucherNo,Amount,dbo.TBLACCOUNTING.Type,dbo.TBLACCOUNTING.Narration,TBLACCOUNTING.ENTRYDATE,(CASE WHEN TYPE='Sales' then (SELECT TOP 1 ISNULL(PONO,'') FROM INVOICE WHERE INVNO=TBLACCOUNTING.VOUCHERNO) else '' end)  AS PONO
    ,(select top 1 [name] from branchmaster where code=TBLACCOUNTING.branchname) as Branchname 
	  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage

FROM dbo.CUSTOMER
LEFT  JOIN TBLACCOUNTING ON dbo.Customer.Code=dbo.TBLACCOUNTING.AcCode 
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode

 WHERE ISNULL(CUSTOMER.CUSTGROUP,0)=ISNULL(@CUSTGROUP,ISNULL(CUSTOMER.CUSTGROUP,0)) AND ISNULL(dbo.Customer.Code,0)=ISNULL(@CustCode,ISNULL(CUSTOMER.Code,0))
and TBLACCOUNTING.EntryDate between @FROM and @TO
AND (@BranchCode IS NULL OR @BranchCode = '' OR @BranchCode = '1' OR ISNULL(TBLACCOUNTING.BranchCode, '') = @BranchCode) ;
END;
go


Create Or ALTER PROCEDURE sp_SaveDeliveryNote
(
@DnNo nvarchar(50),
@BranchCode varchar(10)
)
AS  
begin  
select DeliveryNote.InvoiceDate as InVDate,DeliveryNote.PoDate,DeliveryNote.Del1,DeliveryNote.Del2,DeliveryNote.Del3,DeliveryNote.Del4,DeliveryNote. InvoiceNo ,
DeliveryNote.Attention,DeliveryNote.mobAttention,DeliveryNote.emailAttention,DeliveryNote.QuotationNo,DeliveryNote.DNNo,DeliveryNote.EntryDate,
DeliveryNote.PONo,DeliveryNote.RefNo,DeliveryNote.CustomerName,DeliveryNote.CustomerCode,DeliveryNote.Address,DeliveryNote.Telephone,DeliveryNote.Fax,
DeliveryNote.Attended,DeliveryNoteItem.SlNo,DeliveryNoteItem.ItemCode,DeliveryNoteItem.ItemName,DeliveryNoteItem.Unit,DeliveryNoteItem.Qty,
DeliveryNoteItem.Amount,ISNULL(DeliveryNoteItem.PartNo,'') AS PartNo,ISNULL(DeliveryNote.VATNO,'') AS VATNO,ISNULL(VATPER,0) AS VATPER,ISNULL(VATAMT,0) AS VATAMT 
,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress 
, dbo.currency_conversion(DeliveryNote.NetAmount) as ARABICNETAMOUNT
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
from DeliveryNote 
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
inner join DeliveryNoteItem on DeliveryNote.DnNo=DeliveryNoteItem.DnNo
 LEFT JOIN CUSTOMER C ON C.CODE=DeliveryNote.CUSTOMERCODE
 where DeliveryNote.DnNo=@DnNo ORDER BY DeliveryNoteItem.SlNo  
end  


go


Create or ALTER PROCEDURE sp_SavePO
(
@PONO nvarchar(50),
@BranchCode varchar(10)
)
AS
begin
select Freight,TransPort,PurchaseOrder.QuotDate,PurchaseOrder.Del1,PurchaseOrder.Del2,PurchaseOrder.Del3,PurchaseOrder.Del4,PurchaseOrder.PONo,
PurchaseOrder.RefNo,PurchaseOrder.EntryDate,PurchaseOrder.SupplierRefNo,PurchaseOrder.SupplierName,PurchaseOrder.Address,PurchaseOrder.Attention,
PurchaseOrder.Telephone,PurchaseOrder.Fax,PurchaseOrder.Attended,PurchaseOrder.PaymentsTerms,PurchaseOrder.DeliveryTime,PurchaseOrder.GrossAmount,
PurchaseOrder.NetAmount,(PurchaseOrder.Discount+PurchaseOrder.Less)as Discount,PurchaseOrderItem.ItemCode,PurchaseOrderItem.ItemName,PurchaseOrderItem.Unit,
PurchaseOrderItem.Qty,PurchaseOrderItem.Rate,PurchaseOrderItem.Amount,PurchaseOrderItem.SlNo,ISNULL(PurchaseOrder.Vatno,'') AS VATNO ,ISNULL(VATTOTAL,0) AS VATTOTAL,
ISNULL(VATPER,0) AS VATPER,ISNULL(VATAMT,0) AS VATAMT,ISNULL(PurchaseOrderItem.PARTNO,'') AS PARTNO
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage

from PurchaseOrder 
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
inner join PurchaseOrderItem on 
PurchaseOrder.PONo=PurchaseOrderItem.PONo where PurchaseOrder.PONo=@PONO
end


go

Create or ALTER PROCEDURE sp_SavePO
(
@PONO nvarchar(50),
@BranchCode varchar(10)
)
AS
begin
select Freight,TransPort,PurchaseOrder.QuotDate,PurchaseOrder.Del1,PurchaseOrder.Del2,PurchaseOrder.Del3,PurchaseOrder.Del4,PurchaseOrder.PONo,
PurchaseOrder.RefNo,PurchaseOrder.EntryDate,PurchaseOrder.SupplierRefNo,PurchaseOrder.SupplierName,PurchaseOrder.Address,PurchaseOrder.Attention,
PurchaseOrder.Telephone,PurchaseOrder.Fax,PurchaseOrder.Attended,PurchaseOrder.PaymentsTerms,PurchaseOrder.DeliveryTime,PurchaseOrder.GrossAmount,
PurchaseOrder.NetAmount,(PurchaseOrder.Discount+PurchaseOrder.Less)as Discount,PurchaseOrderItem.ItemCode,PurchaseOrderItem.ItemName,PurchaseOrderItem.Unit,
PurchaseOrderItem.Qty,PurchaseOrderItem.Rate,PurchaseOrderItem.Amount,PurchaseOrderItem.SlNo,ISNULL(PurchaseOrder.Vatno,'') AS VATNO ,ISNULL(VATTOTAL,0) AS VATTOTAL,
ISNULL(VATPER,0) AS VATPER,ISNULL(VATAMT,0) AS VATAMT,ISNULL(PurchaseOrderItem.PARTNO,'') AS PARTNO
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage

from PurchaseOrder 
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
inner join PurchaseOrderItem on 
PurchaseOrder.PONo=PurchaseOrderItem.PONo where PurchaseOrder.PONo=@PONO
end

go

Create or Alter PROCEDURE sp_SaveRFQReg
(
@RFQNo nvarchar(50),
@BranchCode varchar(10)
)
AS
begin


SELECT PayTerms,RFQDate,RFQToSupplier.Del1,RFQToSupplier.Del2,RFQToSupplier.Del3,RFQToSupplier.Del4,RFQToSupplier.RFQNo,RFQToSupplier.RefNo,RFQToSupplier.EntryDate,
RFQToSupplier.SupplierName,RFQToSupplier.Address,RFQToSupplier.Telephone,RFQToSupplier.Fax,RFQToSupplier.Attention,
RFQToSupplierDetails.ItemName,RFQToSupplierDetails.ItemCode,RFQToSupplierDetails.Qty,RFQToSupplierDetails.Unit,
RFQToSupplierDetails.Slno,ISNULL(RFQToSupplier.VATNO,'') AS VATNO 
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
FROM RFQToSupplier 
INNER JOIN RFQToSupplierDetails ON RFQToSupplier.rfqno = RFQToSupplierDetails.rfqno 
 JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
where RFQToSupplier.rfqno=@RFQNo

end


go

Create or ALTER PROCEDURE sp_SavePurchaseReturn
(
 @PurchaseRet varchar(50),
 @BranchCode varchar(10)
 )
AS
begin
SELECT PurchaseReturn.DocumentNo as PurchaseRet,
PurchaseReturn.InvoiceNo,PurchaseReturn.EntryDate, 
PurchaseReturn.RefNo,PurchaseReturn.SupplierName,PurchaseReturn.Address,
PurchaseReturn.Attended,PurchaseReturn.NetAmount,PurchaseReturnItem.SlNo,
PurchaseReturnItem.ItemName,PurchaseReturnItem.Unit,PurchaseReturnItem.Qty,PurchaseReturnItem.Rate,
PurchaseReturnItem.Amount,isnull(PurchaseReturn.vattotal,0)as vattotal,ISNULL(PurchaseReturn.Vatno,'') AS VATNO 
  ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
FROM PurchaseReturn 
INNER JOIN PurchaseReturnItem 
ON PurchaseReturn.DocumentNo = PurchaseReturnItem.DocumentNo 
JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
WHERE  
PurchaseReturn.PR = 'Returns' and PurchaseReturnItem.DocumentNo=@PurchaseRet 
end

go

IF NOT EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'PermanentSettings' AND COLUMN_NAME = 'ZatcaPhases'
)
BEGIN
	   ALTER TABLE PermanentSettings
	ADD ZatcaPhases varchar(50) null
END