ALTER TABLE PermanentSettings	ADD ZatcaPhases  int NOT NULL default 1
GO
IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[AccountOpeningEntry]') AND type = N'U'
)
CREATE TABLE AccountOpeningEntry
(
    AccountOpeningEntryId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FK_BranchId INT NULL,
	BranchCode nvarchar(20) NULL,
    FK_FinancialyearId INT NULL,
    FK_UserId INT NOT NULL,
    InvoiceDate DATETIME NOT NULL,
    InvoiceNo INT NOT NULL,
    EntryDate DATETIME NOT NULL,
    DebitTotal DECIMAL(24,4) NULL,
    CreditTotal DECIMAL(24,4) NULL,
    Remarks NVARCHAR(MAX) NULL,

   
)
go

IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[AccountOpeningEntryDetails]') AND type = N'U'
)
CREATE TABLE AccountOpeningEntryDetails
(
    Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FK_AccountOpeningEntryId INT NOT NULL,
    FK_AccountId INT NULL,
	AccountCode nvarchar(500) NOT NULL,
    Debit DECIMAL(24,4) NULL,
    Credit DECIMAL(24,4) NULL,

    -- Foreign Key Constraints
    CONSTRAINT FK_AOEntryDetails_AccountOpeningEntry FOREIGN KEY (FK_AccountOpeningEntryId) REFERENCES AccountOpeningEntry(AccountOpeningEntryId)
)

go
IF type_id('[dbo].[UT_AccountOpeningEntryDetails]') IS NOT  NULL
Drop TYPE  UT_AccountOpeningEntryDetails
GO
CREATE TYPE UT_AccountOpeningEntryDetails AS TABLE
(
Id int NULL, 
FK_AccountOpeningEntryId int NULL, 
FK_AccountId int NULL, 
AccountCode nvarchar(500) NULL,
Debit decimal(24,4) NULL, 
Credit decimal(24,4) NULL 
)
GO
IF type_id('[dbo].[UT_TblAccounting_ForAccOppEntry]') IS NOT  NULL
Drop TYPE  UT_TblAccounting_ForAccOppEntry
GO
CREATE TYPE UT_TblAccounting_ForAccOppEntry AS TABLE
(
EntryDate datetime NULL, 
VoucherNo nvarchar(500) NULL, 
AcCode nvarchar(500) NULL, 
Amount nvarchar(500) NULL, 
Narration nvarchar(2000) NULL, 
SalesMan nvarchar(2000) NULL, 
Type nvarchar(500) NULL, 
billtype nvarchar(500) NULL 
)
GO
CREATE or Alter FUNCTION dbo.fnGetMaxAccountOpeningEntryInvoiceNo
(
@BranchCode nvarchar(50) null
)
RETURNS INT
AS
BEGIN
    DECLARE @MaxInvoiceNo INT;
	if @BranchCode = ''
	begin
	set @BranchCode = null
	end
    SELECT @MaxInvoiceNo = ISNULL(MAX(InvoiceNo), 1000)+1
    FROM AccountOpeningEntry Where (BranchCode = @BranchCode or @BranchCode is null);

    RETURN @MaxInvoiceNo;
END
go
Create OR Alter Procedure spAccountOpeningEntry
(
@AccountOpeningEntryId int=NULL, 
@FK_BranchId int=NULL,
@BranchCode nvarchar(20) =null,
@FK_FinancialyearId int=NULL, 
@FK_UserId int=NULL, 
@InvoiceDate datetime=NULL, 
@InvoiceNo int=NULL, 
@EntryDate datetime=NULL, 
@DebitTotal decimal(24,4)=NULL, 
@CreditTotal decimal(24,4)=NULL, 
@Remarks nvarchar(max)=NULL, 
@TransactionType nvarchar(20)='insert',
@errMessage Varchar(max) = '' OUTPUT,
@return int = null OUTPUT,
@type dbo.UT_AccountOpeningEntryDetails READONLY,
@type1 dbo.UT_TblAccounting_ForAccOppEntry READONLY

)
 AS 
 BEGIN 
  BEGIN TRY
 BEGIN TRANSACTION insert_AccountOpeningEntry
if @TransactionType ='insert'
 BEGIN 
 select @InvoiceNo = dbo.fnGetMaxAccountOpeningEntryInvoiceNo(@BranchCode)
 insert into AccountOpeningEntry(FK_BranchId,BranchCode, FK_FinancialyearId, FK_UserId, InvoiceDate, InvoiceNo, EntryDate, DebitTotal, CreditTotal, Remarks )
 values(@FK_BranchId,@BranchCode, @FK_FinancialyearId, @FK_UserId, @InvoiceDate, @InvoiceNo, Getdate(), @DebitTotal, @CreditTotal, @Remarks )
 
 set @AccountOpeningEntryId = SCOPE_IDENTITY()
  
  insert into AccountOpeningEntryDetails(FK_AccountOpeningEntryId, FK_AccountId,AccountCode, Debit, Credit )
 select @AccountOpeningEntryId, FK_AccountId,AccountCode, Debit, Credit from @type
 
  insert into TblAccounting(EntryDate, VoucherNo, AcCode, Amount, Narration, SalesMan, Type, billtype )
 select @InvoiceDate, @InvoiceNo, AcCode, Amount, CONCAT('Accounts Opening Entry By:',@AccountOpeningEntryId), SalesMan, Type, billtype from @type1
 


 END 
else if @TransactionType ='update'
 BEGIN 

 delete from TblAccounting where VoucherNo = @InvoiceNo and Narration = CONCAT('Accounts Opening Entry By:',@AccountOpeningEntryId)
 delete from AccountOpeningEntryDetails where FK_AccountOpeningEntryId = @AccountOpeningEntryId
 update AccountOpeningEntry set FK_BranchId=@FK_BranchId,BranchCode=@BranchCode, FK_FinancialyearId=@FK_FinancialyearId, 
 FK_UserId=@FK_UserId, InvoiceDate=@InvoiceDate, InvoiceNo=@InvoiceNo, EntryDate=@EntryDate, 
 DebitTotal=@DebitTotal, CreditTotal=@CreditTotal, Remarks=@Remarks  
 where AccountOpeningEntryId=@AccountOpeningEntryId

 insert into AccountOpeningEntryDetails(FK_AccountOpeningEntryId, FK_AccountId,AccountCode, Debit, Credit )
 select @AccountOpeningEntryId, FK_AccountId,AccountCode, Debit, Credit from @type
 
  insert into TblAccounting(EntryDate,VoucherNo, AcCode, Amount, Narration, SalesMan, Type, billtype )
 select @InvoiceDate, @InvoiceNo, AcCode, Amount, CONCAT('Accounts Opening Entry By:',@AccountOpeningEntryId), SalesMan, Type, billtype from @type1
 
 END 
else if @TransactionType ='delete'
 BEGIN 
 delete from TblAccounting where VoucherNo = @InvoiceNo and Narration = CONCAT('Accounts Opening Entry By:',@AccountOpeningEntryId)
 delete from AccountOpeningEntryDetails where FK_AccountOpeningEntryId = @AccountOpeningEntryId
 Delete from AccountOpeningEntry where AccountOpeningEntryId= @AccountOpeningEntryId
 END 
  COMMIT TRANSACTION insert_AccountOpeningEntry
  END TRY
   BEGIN CATCH
    ROLLBACK TRANSACTION insert_AccountOpeningEntry;
    SELECT @errMessage = ERROR_MESSAGE()
   END CATCH 
 END  
go
Create OR Alter Procedure spGetAccountOpeningEntry
(
@AccountOpeningEntryId int=NULL, @FK_BranchId int=NULL,@BranchCode nvarchar(50)=NULL, @FK_FinancialyearId int=NULL, @FK_UserId int=NULL, @InvoiceDate datetime=NULL, @InvoiceNo int=NULL, @EntryDate datetime=NULL
)

 AS 
 BEGIN 

 Select AccountOpeningEntryId, FK_BranchId,BranchCode, FK_FinancialyearId, FK_UserId, InvoiceDate, InvoiceNo, EntryDate, DebitTotal, CreditTotal, Remarks 
 from AccountOpeningEntry where 
(AccountOpeningEntryId = @AccountOpeningEntryId or @AccountOpeningEntryId is null ) And 
(FK_BranchId = @FK_BranchId or @FK_BranchId is null ) And 
(BranchCode = @BranchCode or @BranchCode is null) and
(FK_FinancialyearId = @FK_FinancialyearId or @FK_FinancialyearId is null ) And 
(FK_UserId = @FK_UserId or @FK_UserId is null ) And 
(InvoiceDate = @InvoiceDate or @InvoiceDate is null ) And 
(InvoiceNo = @InvoiceNo or @InvoiceNo is null ) And 
(EntryDate = @EntryDate or @EntryDate is null )

 
 END 
go
Create OR Alter Procedure spGetAccountOpeningEntryDetails
(
@FK_AccountOpeningEntryId int=NULL, @FK_AccountId int=NULL,@AccountCode nvarchar(500)=null
)

 AS 
 BEGIN 

 Select Id, FK_AccountOpeningEntryId, FK_AccountId,AccountCode, Debit, Credit 
 from AccountOpeningEntryDetails where 
(FK_AccountOpeningEntryId = @FK_AccountOpeningEntryId or @FK_AccountOpeningEntryId is null ) And
(AccountCode =@AccountCode or @AccountCode is null) and
(FK_AccountId = @FK_AccountId or @FK_AccountId is null ) 

 
 END 
go
ALTER PROCEDURE [dbo].[SP_SUPPLIERLEDGER]
    @SupCode NVARCHAR(500),
    @FROM DATETIME,
    @TO DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    -- Get supplier opening balance
    WITH SupplierOpening AS (
        SELECT 
            s.Code,
            s.Name,
            Opening = 
                 ISNULL(dbo.udf_GenOpening(@FROM, s.Code), 0)
        FROM dbo.Supplier s
        WHERE s.Code = @SupCode
    )

    SELECT 
        so.Code,
        so.Name,
        so.Opening,
        '' AS VoucherNo,
        0 AS Amount,
        'OPENING' AS Type,
        '' AS Narration,
        CAST('2000-01-01' AS DATETIME) AS ENTRYDATE,
        '' AS SVNO
    FROM SupplierOpening so

    UNION ALL

    SELECT 
        s.Code,
        s.Name,
        so.Opening,
        a.VoucherNo,
        a.Amount,
        a.Type,
        a.Narration,
        a.ENTRYDATE,
        ISNULL(sp.rVNO, '') AS SVNO
    FROM dbo.Supplier s
    INNER JOIN SupplierOpening so ON s.Code = so.Code
    LEFT JOIN dbo.TBLACCOUNTING a ON s.Code = a.AcCode
    LEFT JOIN dbo.SupplierPayment sp 
        ON sp.VOUCHERNO = a.VOUCHERNO AND a.TYPE = 'SupplierPayment'
    WHERE a.EntryDate BETWEEN @FROM AND @TO;
END
go
IF NOT EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'TblAccounting' AND COLUMN_NAME = 'Id'
)
BEGIN
	   ALTER TABLE TblAccounting
	ADD Id  int IDENTITY(1, 1) NOT NULL primary key,
	BranchId int NULL,
	BranchCode nvarchar(50) NULL;
END
go
-- Step 1: Create new table with IDENTITY column as the first column
CREATE TABLE [dbo].[Company_New](
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
      [Code] [nvarchar](500) NULL,
	[Name] [nvarchar](2000) NULL,
	[Remarks] [nvarchar](2000) NULL,
	[NameAR] [nvarchar](500) NULL,
	[RemarksAR] [nvarchar](2000) NULL
);

-- Step 2: Copy data from old table to new table (excluding ID column)
INSERT INTO [dbo].[Company_New] ([Code], [Name], [Remarks], [NameAR], [RemarksAR])
SELECT [Code], [Name], [Remarks], [NameAR], [RemarksAR] FROM [dbo].[Company];

-- Step 3: Drop the old table
DROP TABLE [dbo].[Company];

-- Step 4: Rename new table to original name
EXEC sp_rename 'dbo.Company_New', 'Company';