BEGIN TRANSACTION

IF @@TRANCOUNT = 1 EXEC('CREATE SCHEMA [acc]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE FUNCTION [acc].[AccDocRemain]
(@ID INT,
@Debtor BIT
)
RETURNS DECIMAL(18,2)
BEGIN

DECLARE @Res DECIMAL(18,2)
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(ISNULL(A.Debtor, 0))
			FROM acc.tbl_AccountingDocDetails A
			WHERE A.AccountingDocMaster_ID=@ID
		END
	ELSE
		BEGIN
			 SELECT @Res= SUM(ISNULL(A.Creditor, 0))
			FROM acc.tbl_AccountingDocDetails A
			WHERE A.AccountingDocMaster_ID=@ID
		END

RETURN
@Res
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE FUNCTION [acc].[FirstRemainAcc]
(@ID INT,
@InDate NVARCHAR(10),
@Acctype INT,
@Debtor BIT
)
RETURNS DECIMAL(18,2)
BEGIN

---AccType =1 =>TotalAcc
--AccType =2 =>AppointmentAcc
--AccType =3 =>DetailedAcc
--AccType =4 =>Detailed2Acc
--AccType =5 =>Detailed3Acc

--DebTor =1 =>Debtor
--Debtor =0 =>Creditor

-----in Function be Taraz haye 6 sotouni hesabha Vabaste mi bashad

DECLARE @Res DECIMAL(18,2)
IF @Acctype=1
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Debtor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.TotalAccount_ID=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
	ELSE
		BEGIN
			SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Creditor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.TotalAccount_ID=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) 
		END
END

IF @Acctype=2
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Debtor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.AppointmentAccount_ID=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
	ELSE
		BEGIN
			SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Creditor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.AppointmentAccount_ID=@ID 
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
END

IF @Acctype=3
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Debtor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
	ELSE
		BEGIN
			SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Creditor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID=@ID 
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
END

IF @Acctype=4
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Debtor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID2=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
	ELSE
		BEGIN
			SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Creditor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID2=@ID 
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
END

IF @Acctype=5
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Debtor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID3=@ID
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
	ELSE
		BEGIN
			SELECT @Res= SUM(CASE WHEN DocDate< @InDate THEN ISNULL(A.Creditor, 0)ELSE 0 END)
			FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			WHERE A.DetailedAccount_ID3=@ID 
			AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )
		END
END

RETURN
@Res
END
')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE FUNCTION [acc].[RemaindAccDocItems]
(@ID INT,
@DocMasterID INT,
@Acctype INT,
@Debtor BIT
)
RETURNS DECIMAL(18,2)
BEGIN

---AccType =1 =>TotalAcc
--AccType =2 =>AppointmentAcc
--AccType =3 =>DetailedAcc

--DebTor =1 =>Debtor
--Debtor =0 =>Creditor



DECLARE @Res DECIMAL(18,2)
IF @Acctype=1
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= ISNULL(SUM(A.Debtor),0)
			FROM acc.tbl_AccountingDocDetails A
			WHERE A.AccountingDocMaster_ID=@DocMasterID
				AND A.TotalAccount_ID=@ID
		END
	ELSE
		BEGIN
			SELECT @Res= ISNULL(SUM(A.Creditor), 0)
			FROM acc.tbl_AccountingDocDetails A
			WHERE A.AccountingDocMaster_ID=@DocMasterID
			AND A.TotalAccount_ID=@ID
		END
END

IF @Acctype=2
BEGIN
	IF @Debtor=1
		BEGIN
			 SELECT @Res= ISNULL(SUM(A.Debtor),0)
			FROM acc.tbl_AccountingDocDetails A
			 WHERE A.AccountingDocMaster_ID=@DocMasterID
			  AND A.AppointmentAccount_ID=@ID
		END
	ELSE
		BEGIN
			SELECT @Res= ISNULL(SUM(A.Creditor), 0)
			FROM acc.tbl_AccountingDocDetails A
			 WHERE A.AccountingDocMaster_ID=@DocMasterID
			  AND A.AppointmentAccount_ID=@ID
		END
END

RETURN
@Res
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_AccountGroups](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](1500) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Nature] [smallint] NULL,
	[EnCode] [int] NULL,
	[IsFixed] [bit] NULL,
 CONSTRAINT [PK_tbl_AccountGroups] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_AccountingDocDetails](
	[AccountingDocMaster_ID] [int] NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RowNum] [int] NOT NULL,
	[TotalAccount_ID] [int] NULL,
	[AppointmentAccount_ID] [int] NULL,
	[DetailedAccount_ID] [int] NULL,
	[DetailedAccount_ID2] [int] NULL,
	[DetailedAccount_ID3] [int] NULL,
	[RowDescription] [nvarchar](max) NULL,
	[Creditor] [decimal](18, 2) NULL,
	[Debtor] [decimal](18, 2) NULL,
	[OldID] [int] NULL,
	[CreateDate] [nvarchar](10) NULL,
	[CreateBy] [nvarchar](1500) NULL,
	[ModifiedDate] [nvarchar](10) NULL,
	[ModifiedBy] [nvarchar](1500) NULL,
	[ReferenceNo] [int] NULL,
	[Related_ID] [int] NULL,
	[Related_Type] [int] NULL,
 CONSTRAINT [PK_tbl_AccountingDocDetails] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('ALTER TABLE [acc].[tbl_AccountingDocDetails] ADD  CONSTRAINT [DF_tbl_AccountingDocDetails_ReferenceNo]  DEFAULT ((0)) FOR [ReferenceNo]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_AccountingDocMaster](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ReferenceNo] [int] NULL,
	[DocCode] [int] NULL,
	[DocDate] [nvarchar](10) NOT NULL,
	[DocState] [smallint] NOT NULL,
	[OldDocCode] [int] NULL,
	[DocDescription] [nvarchar](300) NULL,
	[OldID] [int] NULL,
	[CreateDate] [nvarchar](10) NULL,
	[CreateBy] [int] NULL,
	[ModifiedDate] [nvarchar](10) NULL,
	[ModifiedBy] [int] NULL,
	[ConfirmedBy] [int] NULL,
	[PermanentBy] [int] NULL,
	[Amendment_ID] [int] NULL,
	[SubSystem] [int] NULL,
	[DocType] [tinyint] NULL,
	[Deleted] [bit] NULL,
	[tmp] [bit] NULL,
	[Reserve] [bit] NULL,
 CONSTRAINT [PK_tbl_AccountingDocMaster] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_AccountingDocStatus](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_tbl_AccountingDocStatus] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_AppointmentAccounts](
	[TotalAccount_ID] [int] NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](1500) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[EnCode] [int] NULL,
	[IsFixed] [bit] NULL,
 CONSTRAINT [PK_tbl_AppointmentAccounts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO


IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_BalanceProfitDetails](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MasterID] [int] NULL,
	[TotalID] [int] NULL,
	[ProfitID] [int] NULL,
	[CalcType] [smallint] NULL,
 CONSTRAINT [PK_tbl_BalanceProfitDetails] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_BalanceProfitMaster](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Caption] [nvarchar](150) NULL,
	[Row] [int] NULL,
	[Resultant] [decimal](18, 2) NULL,
 CONSTRAINT [PK_tbl_ProfitMaster] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_DetailedAccGroupToAppointmentRel](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[DetailedAccGroup_ID] [int] NULL,
	[Appointment_ID] [int] NULL,
	[Level] [smallint] NULL,
 CONSTRAINT [PK_tbl_DetailedAccGroupToAppointmentRel] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_DetailedAccountGroups](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [int] NOT NULL,
	[Title] [nvarchar](500) NOT NULL,
	[Level] [smallint] NULL,
 CONSTRAINT [PK_tbl_DetailedAccountGroups] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_DetailedAccounts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](1500) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[EnCode] [int] NULL,
	[Level] [smallint] NULL,
 CONSTRAINT [PK_tbl_DetailedAccounts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_DetailedAccountsRelation](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DetailedAccount_ID] [int] NULL,
	[DetailedAccountGroup_ID] [int] NULL,
	[AccountGroup_ID] [int] NULL,
	[TotalAccount_ID] [int] NULL,
	[AppointmentAccount_ID] [int] NULL,
	[Related_Type] [int] NULL,
	[Related_ID] [int] NULL,
 CONSTRAINT [PK_tbl_DetailedAccountsRelation] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_DocDescription](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](300) NULL,
 CONSTRAINT [PK_acc.tbl_DocDescription] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_ReferenceHolder](
	[Holder] [int] NULL
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_Reports](
	[ID] [int] NOT NULL,
	[Title] [nvarchar](500) NULL,
	[Query] [nvarchar](max) NULL,
	[Fields] [nvarchar](max) NULL,
	[VisibleFields] [nvarchar](max) NULL,
	[FieldsTitle] [nvarchar](max) NULL,
	[ReportFile] [nvarchar](max) NULL,
	[QueryType] [smallint] NULL,
 CONSTRAINT [PK_tbl_Reports] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_RowDescription](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](300) NULL,
 CONSTRAINT [PK_acc.tbl_RowDescription] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE TABLE [acc].[tbl_TotalAccounts](
	[AccountGroup_ID] [int] NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](1500) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Nature] [smallint] NULL,
	[EnCode] [int] NULL,
	[IsFixed] [bit] NULL,
 CONSTRAINT [PK_tbl_TotalAccounts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('ALTER TABLE [acc].[tbl_ReferenceHolder] ADD  DEFAULT ((1)) FOR [Holder]')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AccDocContrast]
@MasterID INT
AS
BEGIN

DECLARE @mTable TABLE 
(
	ID INT,
	[Debtor] DECIMAL(18,2),
	[Creditor] DECIMAL(18,2),
	[sumColDeb] DECIMAL(18,2),
	[sumColcre] DECIMAL(18,2),
	[ContrastCol] DECIMAL(18,2),
	[SumRowDeb] DECIMAL(18,2),
	[SumRowcre] DECIMAL(18,2),
	[ContrastRowDeb] DECIMAL(18,2),
	[ContrastRowcre] DECIMAL(18,2),
	[State] SMALLINT
	
)

INSERT INTO @mTable
(ID,Debtor,Creditor)
SELECT ID, ISNULL(Debtor,0) ,ISNULL(Creditor,0) FROM acc.tbl_AccountingDocDetails WHERE AccountingDocMaster_ID=@MasterID

 DECLARE @Debtor DECIMAL(18,2)
 DECLARE @Creditor DECIMAL(18,2)
 DECLARE @ContrastCol DECIMAL(18,2)
 DECLARE	@SumRowDeb DECIMAL(18,2)=0
 DECLARE	@SumRowCre DECIMAL(18,2)=0
 DECLARE @ContrastRow DECIMAL(18,2)=0
 
 
 SELECT @Debtor=sum(Debtor),@Creditor=sum(Creditor) FROM @mTable AS mt

SET @ContrastCol= @Debtor-@Creditor

UPDATE @mTable
SET
	[sumColDeb] = @Debtor,
	[sumColcre] = @Creditor,
	[ContrastCol]=abs(@ContrastCol),
	@SumRowDeb=@SumRowDeb+Debtor,
	[SumRowDeb]=@SumRowDeb,
	@SumRowCre=@SumRowCre+Creditor,
	[SumRowCre]=@SumRowCre,
	@ContrastRow=(@ContrastRow-[Debtor]+[Creditor]),
	[ContrastRowDeb] = CASE WHEN @ContrastRow<0
	THEN ABS(@ContrastRow)
	ELSE 0
	END,
	[ContrastRowCre]=CASE WHEN @ContrastRow>0
	THEN ABS(@ContrastRow)
	ELSE 0
	END,
	[State]=CASE WHEN @ContrastCol=0 THEN 0
	WHEN @ContrastCol>0 THEN 1
	 ELSE 2 END
SELECT * FROM @mTable AS mt
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddAccountDocDetails]
			@AccountingDocMaster_ID INT, 
			@RowNum INT, 
			@TotalAccount_ID INT, 
			@AppointmentAccount_ID INT, 
			@DetailedAccount_ID INT,
			@DetailedAccount_ID2 INT,
			@DetailedAccount_ID3 INT, 
			@RowDescription NVARCHAR(MAX), 
			@Creditor DECIMAL(18,2), 
			@Debtor DECIMAL(18,2), 
			@CreateDate NVARCHAR(10), 
			@CreateBy NVARCHAR(1500)
		AS
		DECLARE @ID  INT
		INSERT INTO acc.tbl_AccountingDocDetails (AccountingDocMaster_ID, RowNum, TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID,DetailedAccount_ID2,DetailedAccount_ID3, RowDescription, Creditor, Debtor, CreateDate, CreateBy)
		VALUES (@AccountingDocMaster_ID, @RowNum, @TotalAccount_ID, @AppointmentAccount_ID, @DetailedAccount_ID,@DetailedAccount_ID2,@DetailedAccount_ID3, @RowDescription, @Creditor, @Debtor, @CreateDate, @CreateBy)
		
		SELECT SCOPE_IDENTITY()
		
		UPDATE acc.tbl_AccountingDocDetails SET RowNum=RowNum+1 WHERE AccountingDocMaster_ID=@AccountingDocMaster_ID AND RowNum>@RowNum')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddAccountGroup]
			@Code NVARCHAR(50),
			@Title NVARCHAR(1500),
			@Description NVARCHAR(MAX),
			@Nature SMALLINT
		AS
			INSERT INTO acc.tbl_AccountGroups (Code, Title, [Description], Nature, EnCode)
			VALUES (@Code, @Title, @Description, @Nature, CAST(@Code AS INT))')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddAppointmentAccount]
			@TotalAccount_ID INT, 
			@Code NVARCHAR(50), 
			@Title NVARCHAR(1500), 
			@Description NVARCHAR(MAX)  
		AS
			INSERT INTO acc.tbl_AppointmentAccounts(TotalAccount_ID, Code, Title, [Description], EnCode)
			VALUES (@TotalAccount_ID, @Code, @Title, @Description, CAST(@Code AS INT))')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO


IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddBalanceProfitMaster]
@Caption NVARCHAR(150)='''',
@Row INT=0,
@Resultant DECIMAL(18,2)=0
AS
	INSERT INTO [acc].[tbl_BalanceProfitMaster]
           ([Caption],[Row],[Resultant])
     VALUES
           (@Caption,@Row,@Resultant)
   RETURN  scope_Identity()')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddDetailedAccount]
			@AccountGroup_ID INT, 
			@TotalAccount_ID INT, 
			@AppointmentAccount_ID INT, 
			@Code NVARCHAR(50), 
			@Title NVARCHAR(1500), 
			@Related_ID INT,
			@Related_Type SMALLINT,
			@DAGroup_ID INT,
			@Level SMALLINT
		AS
			DECLARE @DA_ID INT
	
			INSERT INTO acc.tbl_DetailedAccounts (Code, Title, EnCode,[Level])

			VALUES (@Code, @Title, CAST(@Code AS INT) ,@Level)

			SET @DA_ID = (SELECT IDENT_CURRENT(''acc.tbl_DetailedAccounts''))
	
			DECLARE @TA INT = (SELECT TotalAccount_ID FROM acc.tbl_AppointmentAccounts WHERE (@AppointmentAccount_ID IS NOT NULL) AND ID = @AppointmentAccount_ID)
			DECLARE @AGA INT = (SELECT AccountGroup_ID FROM acc.tbl_TotalAccounts WHERE (@TA IS NOT NULL) AND ID = @TA)
	
			INSERT INTO acc.tbl_DetailedAccountsRelation (DetailedAccount_ID, DetailedAccountGroup_ID, AccountGroup_ID, TotalAccount_ID, AppointmentAccount_ID, Related_Type, Related_ID)
			VALUES (@DA_ID, @DAGroup_ID, @AGA, @TA, @AppointmentAccount_ID, @Related_Type, @Related_ID)')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddDetailedAccountGroup]
			@Code NVARCHAR(50),
			@Title NVARCHAR(500),
			@Level SMALLINT
		AS

			INSERT INTO acc.tbl_DetailedAccountGroups (Code, Title, [LEVEL])
			VALUES (@Code, @Title,@Level)')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddTotalAccount]
			@AccountGroup_ID INT, 
			@Code NVARCHAR(50), 
			@Title NVARCHAR(1500), 
			@Description NVARCHAR(MAX), 
			@Nature SMALLINT
		AS
		INSERT INTO acc.tbl_TotalAccounts (AccountGroup_ID, Code, Title, [Description], Nature, EnCode)
		VALUES (@AccountGroup_ID, @Code, @Title, @Description, @Nature, CAST(@Code AS INT))')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetLastRefNo]
			@SEQVAL INT OUTPUT
		AS
		BEGIN
			 IF (SELECT COUNT(HOLDER) FROM acc.tbl_ReferenceHolder)=0 
				 BEGIN
					INSERT INTO  acc.tbl_ReferenceHolder(Holder) VALUES (1)
				 END
			 ELSE
		 		BEGIN
			UPDATE acc.tbl_ReferenceHolder SET Holder=ISNULL(Holder,0)+1
		 		END	
		 SET @SEQVAL = (SELECT  Holder FROM acc.tbl_ReferenceHolder)
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AggregateAccDoc]
			@AccDocList NVARCHAR(3000),
			@AccDocIDList NVARCHAR(3000),
			@AccDocDate NVARCHAR(10),
			@CreateBy INT,
			@SumRow BIT =FALSE,
			@UpdateFiles BIT,
			@DocNo INT=0
		AS
		BEGIN
			DECLARE @Query NVARCHAR(MAX)
			DECLARE @ItemFilesQuery NVARCHAR(MAX)
			DECLARE @ReferenceNo INT
			DECLARE @DocCode TABLE (DocCode INT)
			DECLARE @RowDescription NVARCHAR(100)
			DECLARE @CreateDate NVARCHAR(10)
			SELECT @CreateDate= dbo.MiladiToShamsi(GETDATE())	
			EXEC Acc.[sp_GetLastRefNo] @ReferenceNo OUTPUT
			IF @DocNo=0
				BEGIN
					
					INSERT INTO @DocCode (DocCode)
					EXEC acc.sp_GetNewAccDocCode
				END	
			ELSE
				BEGIN
					INSERT INTO @DocCode (DocCode) VALUES (CAST (@DocNo AS INT))
				END
				
				
			INSERT INTO acc.tbl_AccountingDocMaster (ReferenceNo, DocCode, DocDate, DocState,DocType, DocDescription,CreateBy,CreateDate, Amendment_ID, SubSystem)
			VALUES (@ReferenceNo, (SELECT DocCode FROM @DocCode), @AccDocDate, 1,1, N''سند ادغام شده از اسناد '' + @AccDocList,@CreateBy,@CreateDate ,NULL, 101)
			DECLARE @AccDocMaster_ID INT =(SELECT SCOPE_IDENTITY())
			
			
			IF @SumRow=1 
				BEGIN
				SET @RowDescription=N''''''بابت ادغام اسناد ''+@AccDocList+''''''''	
				SET @Query = 
			''INSERT INTO acc.tbl_AccountingDocDetails (AccountingDocMaster_ID,RowNum, TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID ,DetailedAccount_ID2 ,DetailedAccount_ID3, RowDescription, Creditor, Debtor,CreateDate,CreateBy)''+	
			''SELECT '' + CAST(@AccDocMaster_ID AS NVARCHAR(20)) + '',1,TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID,DetailedAccount_ID2,DetailedAccount_ID3,''+@RowDescription+'',0,SUM(CASE WHEN ISNULL(Debtor,0)>0 THEN Debtor ELSE 0 END),''''''+@CreateDate+'''''',''+CONVERT(CHAR(2), @CreateBy)+'' FROM acc.tbl_AccountingDocDetails''+
			'' WHERE AccountingDocMaster_ID IN '' + @AccDocIDList +'' AND ISNULL(Debtor,0)>0'' +
			'' GROUP BY TotalAccount_ID,AppointmentAccount_ID,DetailedAccount_ID,DetailedAccount_ID2,DetailedAccount_ID3''+
		
			'' UNION ALL ''+
	
			
			''SELECT '' + CAST(@AccDocMaster_ID AS NVARCHAR(20)) + '',1,TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID,DetailedAccount_ID2,DetailedAccount_ID3,''+@RowDescription+'',SUM(CASE WHEN ISNULL(Creditor,0)>0  THEN Creditor ELSE 0 END)  ,0,''''''+@CreateDate+'''''',''+CONVERT(CHAR(2), @CreateBy)+'' FROM acc.tbl_AccountingDocDetails''+
			'' WHERE AccountingDocMaster_ID IN '' + @AccDocIDList + '' AND ISNULL(Creditor,0)>0 ''+
			'' GROUP BY TotalAccount_ID,AppointmentAccount_ID,DetailedAccount_ID,DetailedAccount_ID2,DetailedAccount_ID3''
				END	
			ELSE
				BEGIN
					SET @Query  = ''
					INSERT INTO acc.tbl_AccountingDocDetails (AccountingDocMaster_ID, RowNum, TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID, DetailedAccount_ID2, DetailedAccount_ID3, RowDescription, Creditor, Debtor,CreateDate,CreateBy,ReferenceNo)
					SELECT '' + CAST(@AccDocMaster_ID AS NVARCHAR(20)) + '', (SELECT ROW_NUMBER() OVER(ORDER BY ID)), TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID, DetailedAccount_ID2, DetailedAccount_ID3, RowDescription, Creditor, Debtor ,''''''+@CreateDate+'''''',''+CONVERT(CHAR(2), @CreateBy)+'',ReferenceNo FROM acc.tbl_AccountingDocDetails	
					WHERE AccountingDocMaster_ID IN '' + @AccDocIDList + ''''
				END

			EXEC sp_executesql @Query
			
			DECLARE @Rownum INT
			SET @RowNum=0	
			
			UPDATE acc.tbl_AccountingDocDetails
			SET @Rownum= RowNum =@Rownum+1
			WHERE acc.tbl_AccountingDocDetails.AccountingDocMaster_ID=@AccDocMaster_ID
			
			IF @UpdateFiles=1
				SET @ItemFilesQuery =''UPDATE ItemFiles	SET	ItemID =''+ CAST(@AccDocMaster_ID AS NVARCHAR(20))+ '' WHERE ItemType=2 AND ItemID IN ''+@AccDocIDList
			ELSE
				 SET @ItemFilesQuery =''INSERT INTO ItemFiles   ([ItemID],[ItemType],[Title],[Info],[blob],[ShowType],[Ext],[dbStorage],[path],[Height],[Width],[Size])''+
									'' SELECT ''+ CAST(@AccDocMaster_ID AS NVARCHAR(20))+'',[ItemType],[Title],[Info],[blob],[ShowType],[Ext],[dbStorage],[path],[Height],[Width],[Size] FROM ItemFiles  WHERE ItemType=2 AND ItemID IN ''+@AccDocIDList
			EXEC sp_executesql @ItemFilesQuery
	
			SELECT @AccDocMaster_ID,(SELECT DocCode FROM @DocCode)
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AppointedAccountBalance4Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM (
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				0 FDebtor,0 FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID'' +
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND''+@DocDate +		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title) T ''+
			'' WHERE (1=1)''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AppointedAccountBalance6Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				acc.FirstRemainAcc(D.ID,''''''+@DateStart+'''''',2,1) FDebtor,acc.FirstRemainAcc(D.ID,''''''+@DateStart+'''''',2,0) FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID'' +
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title )T '' +
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
			
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AppointedAccountBalance8Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
			SELECT
				 D.ID AppointedAccount_ID ,C.Code TotalAccount_Code,C.Title TotalAccount_Title,D.Code AppointedAccount_Code , C.Title AppointedAccount_Title ,
				acc.FirstRemainAcc(D.ID,''''''+@DateStart+'''''',2,1) FDebtor,acc.FirstRemainAcc(D.ID,''''''+@DateStart+'''''',2,0) FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				acc.FirstRemainAcc(D.ID,''''''+@DateStart+'''''',2,1)+SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) SDebtor,
				acc.FirstRemainAcc(D.ID,+''''''+@DateStart+'''''',2,0)+SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID'' +
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )''+	
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title )T '' +
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
			
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AppointedAccountBalanceSummary2Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				0 FDebtor,0 FCreditor
				,0 DebtorFlow,0 CreditorFlow
				,0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID'' +
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND'' +@DocDate+		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title) T ''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE  PROCEDURE [acc].[sp_BalanceUniversal] @DocState VARCHAR(10),@GroupNature VARCHAR(10),@DocDate VARCHAR(30)=''''
AS
BEGIN
	IF @DocDate<>''''
	BEGIN
		SET @DocDate='' AND M.DocDate<=''''''+@DocDate+''''''''
	END
	DECLARE @qry NVARCHAR(MAX)
	SET @qry=''SELECT * FROM
(SELECT G.title,ISNULL(SUM(DD1.AvalDore) ,0) AS AvalDore ,ISNULL(SUM(DD2.PayanDore),0) AS PayanDore,G.Code, (CAST(0 AS bit)) AS IsTotal,0 AS TotalID,0 as TCode FROM acc.tbl_AccountGroups G
	LEFT OUTER JOIN 
			(SELECT G.ID,((ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))) AS AvalDore FROM
				 acc.tbl_AccountingDocDetails AS D
			INNER JOIN acc.tbl_AccountingDocMaster AS M ON m.ID=d.AccountingDocMaster_ID AND m.DocType=0 AND ISNULL(m.[Deleted],0)<>1 AND ISNULL(m.tmp,0)<>1
			INNER JOIN acc.tbl_TotalAccounts AS T ON t.ID=d.TotalAccount_ID
			INNER JOIN acc.tbl_AccountGroups G ON G.ID=T.AccountGroup_ID
			 WHERE M.DocState IN (''+@DocState+'')''+@DocDate+''	 
			 GROUP BY G.ID
			) AS DD1 ON DD1.ID=G.ID
	LEFT OUTER JOIN 
			(SELECT G.ID,((ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))) AS PayanDore FROM
				 acc.tbl_AccountingDocDetails AS D
				 INNER JOIN acc.tbl_AccountingDocMaster AS M ON m.ID=d.AccountingDocMaster_ID  AND ISNULL(m.[Deleted],0)<>1 AND ISNULL(m.tmp,0)<>1
				 INNER JOIN acc.tbl_TotalAccounts AS T ON t.ID=d.TotalAccount_ID
				 INNER JOIN acc.tbl_AccountGroups G ON G.ID=T.AccountGroup_ID
				WHERE M.DocState IN (''+@DocState+'')''+@DocDate+''	 
			 GROUP BY G.ID
			) AS DD2 ON DD2.ID=G.ID
 WHERE G.Nature IN(''+@GroupNature+'')			
GROUP BY g.Title,g.Code
 UNION ALL
 
 SELECT T.title,ISNULL(SUM(DD1.AvalDore) ,0) AS AvalDore ,ISNULL(SUM(DD2.PayanDore),0) AS PayanDore,G.Code, (CAST(1 AS bit)) AS IsTotal,T.ID AS TotalID,T.Code as TCode FROM acc.tbl_TotalAccounts T
	INNER JOIN acc.tbl_AccountGroups AS G ON T.AccountGroup_ID=G.ID
	LEFT OUTER JOIN 
	(SELECT TotalAccount_ID,(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0)) AS AvalDore FROM
		 acc.tbl_AccountingDocDetails AS D
	INNER JOIN acc.tbl_AccountingDocMaster AS M ON m.ID=d.AccountingDocMaster_ID AND m.DocType=0 AND ISNULL(m.[Deleted],0)<>1 AND ISNULL(m.tmp,0)<>1
	WHERE M.DocState IN (''+@DocState+'')''+@DocDate+''
	 GROUP BY TotalAccount_ID
	) AS DD1 ON DD1.TotalAccount_ID=T.ID
		LEFT OUTER JOIN 
	(SELECT TotalAccount_ID,(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0)) AS PayanDore FROM
		 acc.tbl_AccountingDocDetails AS D
		 INNER JOIN acc.tbl_AccountingDocMaster AS M ON m.ID=d.AccountingDocMaster_ID  AND ISNULL(m.[Deleted],0)<>1 AND ISNULL(m.tmp,0)<>1
	WHERE M.DocState IN (''+@DocState+'')''+@DocDate+''	 
	 GROUP BY TotalAccount_ID
	) AS DD2 ON DD2.TotalAccount_ID=T.ID
 WHERE G.Nature IN(''+@GroupNature+'')	
 GROUP BY T.Title,G.Code,T.Code,T.ID
) tbl
ORDER BY Code,tbl.IStotal,TCode'' 
EXEC sp_executesql @qry	
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_ChangeDocState]
@IDList NVARCHAR(3000),
@DocState INT,
@UserID INT

AS
BEGIN

	DECLARE @Query NVARCHAR(MAX)
	SET @Query=''UPDATE acc.tbl_AccountingDocMaster SET DocState=''+CAST(@DocState AS NVARCHAR(1))
	
		IF @DocState=1
		SET @Query=@Query+'',  ConfirmedBy=NULL,PermanentBy=NULL''
		IF @DocState=2
		SET @Query=@Query+'',  ConfirmedBy=''+CAST(@UserID AS NVARCHAR(2))
		IF @DocState=3
		SET @Query=@Query+'',  PermanentBy=''+CAST(@UserID AS NVARCHAR(2))
	SET @Query=@Query+'' WHERE ISNULL([Deleted],0)<>1 AND ISNULL(tmp,0)<>1''
	IF @IDList<>''''	
	SET @Query=@Query+'' AND ID IN ''+@IDList
				
	EXEC sp_executesql @Query	
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_CheckAccCodingValue]
			@TableName NVARCHAR(500),
			@FieldName NVARCHAR(500),
			@FieldValue NVARCHAR(500),
			@ParentID NVARCHAR(50),
			@Related BIT =FALSE
		AS
			DECLARE @Query NVARCHAR(MAX);
	
			-- بررسی گروه حساب
			IF (@TableName = ''AccGroup'')
				SET @Query = ''SELECT 1 FROM acc.tbl_AccountGroups WHERE '' + @FieldName + '' = '' + @FieldValue

			-- بررسی حساب کل
			IF (@TableName = ''AccTotal'')
				SET @Query = ''SELECT 1 FROM acc.tbl_TotalAccounts WHERE '' + @FieldName + '' = '' + @FieldValue 
			-- بررسی حساب معین
			IF (@TableName = ''AccAppointment'')
				SET @Query = ''SELECT 1 FROM acc.tbl_AppointmentAccounts WHERE '' + @FieldName + '' = '' + @FieldValue+'' AND TotalAccount_ID=''+@ParentID
			--بررسی حساب تفصیلی سطح1
			IF (@TableName = ''AccDetailed1'')
				BEGIN
					IF @Related=1
						BEGIN
							SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccounts A ''
							+'' INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID =A.ID''+
							+'' WHERE A.'' + @FieldName + '' = '' + @FieldValue+
							+'' AND AppointmentAccount_ID=''+@ParentID
						END	
					ELSE
						BEGIN
							--SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccounts WHERE '' + @FieldName + '' = '' + @FieldValue+'' AND Level=''+@ParentID
							SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccounts A ''
							+'' INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID =A.ID''+
							+'' WHERE A.'' + @FieldName + '' = '' + @FieldValue+
							+'' AND Level=1 AND  DetailedAccountGroup_ID=''+@ParentID
						END	
				END
			--بررسی حساب تفصیلی سطح2	
			IF (@TableName = ''AccDetailed2'')
				BEGIN
							SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccounts A ''
							+'' INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID =A.ID''+
							+'' WHERE A.'' + @FieldName + '' = '' + @FieldValue+
							+'' AND Level=2 AND  DetailedAccountGroup_ID=''+@ParentID
				END
			--بررسی حساب تفصیلی سطح3	
			IF (@TableName = ''AccDetailed3'')
				BEGIN
							SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccounts A ''
							+'' INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID =A.ID''+
							+'' WHERE A.'' + @FieldName + '' = '' + @FieldValue+
							+'' AND Level=3 AND  DetailedAccountGroup_ID=''+@ParentID
				END
				

			IF (@TableName = ''AccDetailGroup'')
				SET @Query = ''SELECT 1 FROM acc.tbl_DetailedAccountGroups WHERE '' + @FieldName + '' = '' + @FieldValue +'' AND Level=''+@ParentID

			EXEC sp_executesql @Query')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_ClosePermanentAccounts]
@DocDate NVARCHAR(10),
@DocDescription NVARCHAR(300),
@CreateBy INT,
@ResultID INT OUTPUT

AS
BEGIN

DECLARE @CreateDate NVARCHAR(10)	
DECLARE @AccountDocMaster_ID INT
DECLARE @Debtor DECIMAL(18,2)
DECLARE @Creditor DECIMAL(18,2)
DECLARE @DebtorRemain DECIMAL(18,2)
DECLARE @CreditorRemain DECIMAL(18,2)
DECLARE @DocCode INT
DECLARE @ReferenceNo INT
DECLARE @RowNum INT
DECLARE @TotalID INT
DECLARE @AppointmentID INT
DECLARE @AccumulatedTotalID INT
DECLARE @AccumulatedAppointmentID INT

SET @CreateDate= (SELECT dbo.MiladiToShamsi(GETDATE()))

SET @AccumulatedTotalID=(SELECT ID FROM acc.tbl_TotalAccounts WHERE Title=N''سود و زیان انباشته'')
SET @AccumulatedAppointmentID=(SELECT ID FROM acc.tbl_AppointmentAccounts AS aa WHERE Title=N''سود و زیان انباشته'')

SET @TotalID=(SELECT ID FROM acc.tbl_TotalAccounts WHERE Title=N''خلاصه سود و زیان'')
SET @AppointmentID=(SELECT ID FROM acc.tbl_AppointmentAccounts AS aa WHERE Title=N''خلاصه سود و زیان'')

SELECT @DebtorRemain=Creditor FROM acc.tbl_AccountingDocDetails AS add1 
WHERE TotalAccount_ID=@TotalID AND AppointmentAccount_ID=@AppointmentID

SELECT @CreditorRemain=Debtor FROM acc.tbl_AccountingDocDetails AS add1 
WHERE TotalAccount_ID=@TotalID AND AppointmentAccount_ID=@AppointmentID 
 
 
SET @AccountDocMaster_ID=(SELECT ID FROM acc.tbl_AccountingDocMaster AS adm WHERE adm.DocType=2)
IF ISNULL(@AccountDocMaster_ID,0)=0
BEGIN
	IF (SELECT COUNT(HOLDER) FROM acc.tbl_ReferenceHolder)=0 
				 BEGIN
		 			INSERT INTO  acc.tbl_ReferenceHolder(Holder) VALUES (1)
				 END
			 ELSE
		 		BEGIN
		 			UPDATE acc.tbl_ReferenceHolder SET Holder=ISNULL(Holder,0)+1
		 		END	
		 		SET @ReferenceNo=(SELECT  Holder FROM acc.tbl_ReferenceHolder)
	SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE  (ISNULL(Deleted,0)<>1))
	
		INSERT INTO acc.tbl_AccountingDocMaster (ReferenceNo, DocCode, DocDate, DocType, DocDescription, CreateDate, CreateBy,tmp,DocState,SubSystem)
										VALUES (@ReferenceNo, @DocCode, @DocDate, 2, @DocDescription, @CreateDate, @CreateBy,1,1,101)
		SET @AccountDocMaster_ID= @@IDENTITY
		 		
END
	
	
--SET @Debtor=
--(
--	SELECT SUM (ISNULL(Creditor,0)) FROM acc.tbl_AccountingDocDetails dt
--	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
--	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
--	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
--	WHERE ag.Nature<>2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
--)
--SET @Creditor=
--(
--	SELECT SUM (ISNULL(Debtor,0)) FROM acc.tbl_AccountingDocDetails dt
--	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
--	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
--	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
--	WHERE ag.Nature<>2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
--)


INSERT INTO acc.tbl_AccountingDocDetails
	SELECT @AccountDocMaster_ID,1 RowNum,
	 @AccumulatedTotalID, @AccumulatedAppointmentID, dt.DetailedAccount_ID ,dt.DetailedAccount_ID2,
	 dt.DetailedAccount_ID3  ,N''بابت بستن حسابهای دائم'',
	(ISNULL(dt.debtor,0)) ,
	 (ISNULL(dt.Creditor,0)),
	 NULL,
	@CreateDate,@CreateBy,NULL ,NULL,@ReferenceNo,NULL ,NULL
	  FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AppointmentAccounts AS ap ON ap.ID=dt.AppointmentAccount_ID  
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	WHERE dt.TotalAccount_ID=@TotalID AND dt.AppointmentAccount_ID=@AppointmentID AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1 

	
INSERT INTO acc.tbl_AccountingDocDetails
	SELECT @AccountDocMaster_ID,ROW_NUMBER() OVER (ORDER BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3)+1 RowNum,
	 tl.ID TotalAccount_ID, ap.ID AppointmentAccount_ID, dt.DetailedAccount_ID ,dt.DetailedAccount_ID2,
	 dt.DetailedAccount_ID3  ,N''بابت بستن حسابهای دائم'',
	SUM(ISNULL(dt.debtor,0)) ,
	 SUM(ISNULL(dt.Creditor,0)),
	 NULL,
	@CreateDate,@CreateBy,NULL ,NULL,@ReferenceNo,NULL ,NULL
	  FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AppointmentAccounts AS ap ON ap.ID=dt.AppointmentAccount_ID  
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
	WHERE ag.Nature<>2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
	GROUP BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3
	
	--IF @Creditor<>0 OR @Debtor<>0
	--BEGIN
	--		SET @RowNum=(SELECT MAX([RowNum])+1 FROM acc.tbl_AccountingDocDetails WHERE AccountingDocMaster_ID=@AccountDocMaster_ID)
	--	INSERT INTO acc.tbl_AccountingDocDetails
	--	   ([AccountingDocMaster_ID],[RowNum],[TotalAccount_ID],[AppointmentAccount_ID],[RowDescription],[Creditor],[Debtor],[CreateDate],[CreateBy],[ReferenceNo])
	--	   VALUES
	--	   (@AccountDocMaster_ID,@RowNum,@TotalID,@AppointmentID,''خلاصه سود و زیان'',CASE WHEN @Debtor-@Creditor>0 THEN @Debtor-@Creditor ELSE 0 END, CASE WHEN @Creditor-@Debtor>0 THEN @Creditor-@Debtor ELSE 0 END ,@CreateDate,@CreateBy,@ReferenceNo)		
	--END
SET @ResultID=@AccountDocMaster_ID
	
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_CloseTemporaryAccounts]
@DocDate NVARCHAR(10),
@DocDescription NVARCHAR(300),
@CreateBy INT,
@ResultID INT OUTPUT

AS
BEGIN

DECLARE @CreateDate NVARCHAR(10)	
DECLARE @AccountDocMaster_ID INT
DECLARE @Debtor DECIMAL(18,2)
DECLARE @Creditor DECIMAL(18,2)
DECLARE @DocCode INT
DECLARE @ReferenceNo INT
DECLARE @RowNum INT
DECLARE @TotalID INT
DECLARE @AppointmentID INT

SET @CreateDate= (SELECT dbo.MiladiToShamsi(GETDATE()))

SET @TotalID=(SELECT ID FROM acc.tbl_TotalAccounts WHERE Title=N''خلاصه سود و زیان'')
SET @AppointmentID=(SELECT ID FROM acc.tbl_AppointmentAccounts AS aa WHERE Title=N''خلاصه سود و زیان'')
 
SET @AccountDocMaster_ID=ISNULL((SELECT ID FROM acc.tbl_AccountingDocMaster AS adm WHERE adm.DocType=3),0)
IF @AccountDocMaster_ID=0
BEGIN
	IF (SELECT COUNT(HOLDER) FROM acc.tbl_ReferenceHolder)=0 
				 BEGIN
		 			INSERT INTO  acc.tbl_ReferenceHolder(Holder) VALUES (1)
				 END
			 ELSE
		 		BEGIN
		 			UPDATE acc.tbl_ReferenceHolder SET Holder=ISNULL(Holder,0)+1
		 		END	
		 		SET @ReferenceNo=(SELECT  Holder FROM acc.tbl_ReferenceHolder)
	SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE  (ISNULL(Deleted,0)<>1 AND ISNULL(tmp,0)<>1) )
	
		INSERT INTO acc.tbl_AccountingDocMaster (ReferenceNo, DocCode, DocDate, DocType, DocDescription, CreateDate, CreateBy,tmp,DocState,SubSystem)
										VALUES (@ReferenceNo, @DocCode, @DocDate, 3, @DocDescription, @CreateDate, @CreateBy,1,1,101)
		SET @AccountDocMaster_ID= @@IDENTITY
		 		
END
	
	
SET @Debtor=
(
	SELECT SUM (ISNULL(Creditor,0)) FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
	WHERE ag.Nature=2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
)
SET @Creditor=
(
	SELECT SUM (ISNULL(Debtor,0)) FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
	WHERE ag.Nature=2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
)

INSERT INTO acc.tbl_AccountingDocDetails

	SELECT @AccountDocMaster_ID,
	ROW_NUMBER() OVER (ORDER BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3) RowNum,
	 tl.ID TotalAccount_ID, ap.ID AppointmentAccount_ID, dt.DetailedAccount_ID ,dt.DetailedAccount_ID2,
	 dt.DetailedAccount_ID3  ,N''بابت بستن حسابهای موقت'',SUM(ISNULL(dt.debtor,0)),SUM(ISNULL(dt.Creditor,0)) ,NULL,
	@CreateDate,@CreateBy,NULL ,NULL,@ReferenceNo,NULL ,NULL
	  FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AppointmentAccounts AS ap ON ap.ID=dt.AppointmentAccount_ID  
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
	WHERE ag.Nature=2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
	GROUP BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3
	
INSERT INTO acc.tbl_AccountingDocDetails
	
	SELECT @AccountDocMaster_ID,ROW_NUMBER() OVER (ORDER BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3) RowNum,
	 tl.ID TotalAccount_ID, ap.ID AppointmentAccount_ID, dt.DetailedAccount_ID ,dt.DetailedAccount_ID2,
	 dt.DetailedAccount_ID3  ,N''بابت بستن حسابهای موقت'',SUM(ISNULL(dt.Creditor,0)),SUM(ISNULL(dt.debtor,0)) ,NULL,
	@CreateDate,@CreateBy,NULL ,NULL,@ReferenceNo,NULL ,NULL
	  FROM acc.tbl_AccountingDocDetails dt
	INNER JOIN acc.tbl_AppointmentAccounts AS ap ON ap.ID=dt.AppointmentAccount_ID  
	INNER JOIN acc.tbl_AccountingDocMaster mc ON mc.ID=dt.AccountingDocMaster_ID
	INNER JOIN acc.tbl_TotalAccounts tl ON tl.ID=dt.TotalAccount_ID
	INNER JOIN acc.tbl_AccountGroups AS ag ON ag.ID=tl.AccountGroup_ID
	WHERE ag.Nature=2 AND ISNULL(Mc.[Deleted],0)<>1 AND ISNULL(mc.tmp,0)<>1
	GROUP BY tl.ID,ap.ID,dt.DetailedAccount_ID,dt.DetailedAccount_ID2,dt.DetailedAccount_ID3	
	

	
	
	SET @RowNum=(SELECT MAX([RowNum])+1 FROM acc.tbl_AccountingDocDetails WHERE AccountingDocMaster_ID=@AccountDocMaster_ID)
	
	INSERT INTO acc.tbl_AccountingDocDetails
	   ([AccountingDocMaster_ID],[RowNum],[TotalAccount_ID],[AppointmentAccount_ID],[RowDescription],[Creditor],[Debtor],[CreateDate],[CreateBy],[ReferenceNo])
	   VALUES
	   (@AccountDocMaster_ID,@RowNum,@TotalID,@AppointmentID,N''خلاصه سود و زیان'',CASE WHEN @Debtor-@Creditor>0 THEN @Debtor-@Creditor ELSE 0 END,CASE WHEN @Creditor-@Debtor>0 THEN @Creditor-@Debtor ELSE 0 END ,@CreateDate,@CreateBy,@ReferenceNo)

	
SET @ResultID=@AccountDocMaster_ID
	
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_CopyAccDoc]
			@AccDocMaster_ID INT,
			@CreateBy INT
		AS
		BEGIN
			--DECLARE @Query NVARCHAR(3000) = 	
			DECLARE @ReferenceNo INT
			DECLARE	@DocCode INT		
			DECLARE @NewAccDocMaster_ID INT
			DECLARE @DocDate NVARCHAR(10) = (SELECT dbo.MiladiTOShamsi(GETDATE()))	
			DECLARE @CreateDate NVARCHAR(10)= (SELECT dbo.MiladiTOShamsi(GETDATE()))
		 IF (SELECT COUNT(HOLDER) FROM acc.tbl_ReferenceHolder)=0 
			 BEGIN
		 		INSERT INTO  acc.tbl_ReferenceHolder(Holder) VALUES (1)
			 END
		 ELSE
		 	BEGIN
		 		UPDATE acc.tbl_ReferenceHolder SET Holder=ISNULL(Holder,0)+1
		 	END	
		 	
		 	SET @ReferenceNo=(SELECT  Holder FROM acc.tbl_ReferenceHolder)
		 	
			SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE  (ISNULL(Deleted,0)<>1)) 
				
			INSERT INTO acc.tbl_AccountingDocMaster (ReferenceNo, DocCode, DocDate, DocState, DocDescription, Amendment_ID, SubSystem,DocType,CreateBy,CreateDate)
			SELECT 	
				@ReferenceNo, @DocCode, @DocDate, 1,+N'' كپی شده از سند شماره '' + CAST(DocCode AS NVARCHAR), NULL, 101,1,@CreateBy,@CreateDate
			FROM acc.tbl_AccountingDocMaster WHERE ID=@AccDocMaster_ID
	
			SET @NewAccDocMaster_ID = (SELECT SCOPE_IDENTITY())
	
			INSERT INTO acc.tbl_AccountingDocDetails (AccountingDocMaster_ID, RowNum, TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID, DetailedAccount_ID2, DetailedAccount_ID3, RowDescription, Creditor, Debtor)
			SELECT
				@NewAccDocMaster_ID, (ROW_NUMBER() OVER (ORDER BY ID)), 
				TotalAccount_ID, AppointmentAccount_ID, DetailedAccount_ID, DetailedAccount_ID2, DetailedAccount_ID3, 
				RowDescription, Creditor, Debtor
			FROM acc.tbl_AccountingDocDetails
			WHERE AccountingDocMaster_ID =@AccDocMaster_ID
			
			SELECT DocCode FROM acc.tbl_AccountingDocMaster
			WHERE ID=@NewAccDocMaster_ID
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_CopyDocRow]
			@ID INT,
			@Rownum INT,	
			@CreateDate NVARCHAR(10),
			@CreateBy INT
		AS
		INSERT INTO [acc].[tbl_AccountingDocDetails]
           ([AccountingDocMaster_ID],[RowNum],[TotalAccount_ID],[AppointmentAccount_ID],[DetailedAccount_ID],[DetailedAccount_ID2],[DetailedAccount_ID3],[RowDescription],[Creditor],[Debtor],[CreateDate],[CreateBy])
           SELECT AccountingDocMaster_ID,@Rownum,[TotalAccount_ID],[AppointmentAccount_ID],[DetailedAccount_ID],[DetailedAccount_ID2],[DetailedAccount_ID3],[RowDescription],[Creditor],[Debtor],@CreateDate,@CreateBy
           FROM acc.tbl_AccountingDocDetails AS add1
           WHERE add1.ID=@ID ')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DayBook]
@Level INT,
@StartDate NVARCHAR(10),
@EndDate NVARCHAR(10),
@DocState SMALLINT 
AS
BEGIN
	
DECLARE @StartDocSate INT
DECLARE @EndDocSate INT

IF @DocState=1
begin
	SET @StartDocSate=1
	SET @EndDocSate=3
 END 
IF @DocState=2
begin
	SET @StartDocSate=1
	SET @EndDocSate=2
 END  		
 IF @DocState=3
begin
	SET @StartDocSate=3
	SET @EndDocSate=3
 END 	
	
	
	IF @Level=1
		begin
			SELECT M.ID,ROW_NUMBER()OVER (ORDER BY M.ID,B.ID,DocDate)AS RowNo,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code TotalAccount_Code,'''' AppointedAccount_Code,'''' DetailedAccount_Code ,B.Title Account_Title ,A.Debtor,A.Creditor 
			FROM tbl_AccountingDocMaster M 
			INNER JOIN tbl_AccountingDocDetails A ON A.AccountingDocMaster_ID=M.ID
			INNER JOIN tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
			WHERE DocDate BETWEEN @StartDate AND @EndDate 
			AND DocState BETWEEN @StartDocSate AND @EndDocSate
			AND ISNULL(M.Deleted,0)<>1 AND ISNULL(M.tmp,0)<>1
			GROUP BY M.ID,B.ID,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code ,B.Title  ,A.Debtor,A.Creditor
		END
	IF @Level=2
	BEGIN
			SELECT M.ID,ROW_NUMBER()OVER (ORDER BY M.ID,C.ID,DocDate)AS RowNo,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code TotalAccount_Code,C.Code AppointedAccount_Code,'''' DetailedAccount_Code ,C.Title Account_Title ,A.Debtor,A.Creditor 
			FROM tbl_AccountingDocMaster M 
			INNER JOIN tbl_AccountingDocDetails A ON A.AccountingDocMaster_ID=M.ID
			INNER JOIN tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
			INNER JOIN tbl_AppointmentAccounts C ON C.ID=A.AppointmentAccount_ID
			WHERE DocDate BETWEEN @StartDate AND @EndDate
			AND DocState BETWEEN @StartDocSate AND @EndDocSate	
			AND ISNULL(M.Deleted,0)<>1 AND ISNULL(M.tmp,0)<>1
			GROUP BY M.ID,C.ID,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code,C.Code,C.Title ,A.Debtor,A.Creditor			
	END	
	
	IF @Level=3
	BEGIN
			SELECT M.ID,ROW_NUMBER()OVER (ORDER BY M.ID,D.ID,DocDate)AS RowNo,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code TotalAccount_Code,C.Code AppointedAccount_Code ,D.Code DetailedAccount_Code,D.Title Account_Title,A.Debtor,A.Creditor 
			FROM tbl_AccountingDocMaster M 
			INNER JOIN tbl_AccountingDocDetails A ON A.AccountingDocMaster_ID=M.ID
			INNER JOIN tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
			INNER JOIN tbl_AppointmentAccounts C ON C.ID=A.AppointmentAccount_ID
			INNER JOIN tbl_DetailedAccounts D ON D.ID=A.DetailedAccount_ID
			WHERE DocDate BETWEEN @StartDate AND @EndDate
			AND DocState BETWEEN @StartDocSate AND @EndDocSate
			AND ISNULL(M.Deleted,0)<>1 AND ISNULL(M.tmp,0)<>1	
			GROUP BY M.ID,D.ID,DocDate,M.ReferenceNo,DocCode,DocDescription,B.Code,C.Code,D.Code ,D.Title  ,A.Debtor,A.Creditor
	END	
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DeleteAccDoc]
			@AccDocMaster_ID INT
		AS
		BEGIN
			IF (SELECT DocState FROM acc.tbl_AccountingDocMaster WHERE ID = @AccDocMaster_ID) <> 1
			BEGIN
				SELECT 0 AS Result
			END
			ELSE
			BEGIN

				UPDATE acc.tbl_AccountingDocMaster SET oldDocCode=DocCode WHERE ID = @AccDocMaster_ID
				UPDATE acc.tbl_AccountingDocMaster SET Deleted=1,DocCode=NULL WHERE ID = @AccDocMaster_ID
				SELECT 1 AS Result	
			END
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed2AccountBalance4Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID2 = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title ) T ''+
			'' WHERE (1=1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed2AccountBalance6Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',4,1) AS  FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',4,0) AS  FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID2 = E.ID''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND''+@DocDate+ 		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title,DocDate) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed2AccountBalanceSummary2Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor
				,0 DebtorFlow,0 CreditorFlow
				,0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID2 = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+ 		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title) T ''+
			'' WHERE (1=1)'' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed3AccountBalance4Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID3 = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title ) T ''+
			'' WHERE (1=1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed3AccountBalance6Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',5,1) AS  FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',5,0) AS  FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID3 = E.ID''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND''+@DocDate+ 		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title,DocDate) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_Detailed3AccountBalanceSummary2Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor
				,0 DebtorFlow,0 CreditorFlow
				,0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID3 = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+ 		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title) T ''+
			'' WHERE (1=1)'' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccount2Balance8Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				E.ID DetailedAccount_ID,C.Code TotalAccount_Code,
				D.Code AppointedAccount_Code,
				E.Code DetailedAccount_Code,
				E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',4,1) AS FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',4,0) AS  FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
								acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',4,1)+SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) SDebtor,
				acc.FirstRemainAcc(E.ID,+''''''+@DateStart+'''''',4,0)+SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) SCreditor,
				CASE WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor,0)) - SUM(ISNULL(A.Creditor,0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID2 = E.ID
				''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )''+	
			'' GROUP BY C.Code, C.Title,D.Code, D.Title,E.ID,E.Code,E.Title) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccount3Balance8Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				E.ID DetailedAccount_ID,C.Code TotalAccount_Code,
				D.Code AppointedAccount_Code,
				E.Code DetailedAccount_Code,
				E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',5,1) AS FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',5,0) AS  FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',5,1)+SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) SDebtor,
				acc.FirstRemainAcc(E.ID,+''''''+@DateStart+'''''',5,0)+SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) SCreditor,
				CASE WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor,0)) - SUM(ISNULL(A.Creditor,0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID3 = E.ID
				''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )''+	
			'' GROUP BY C.Code, C.Title,D.Code, D.Title,E.ID,E.Code,E.Title) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccountBalance4Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, C.Code+D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor,
				SUM(ISNULL(A.Debtor, 0)) DebtorFlow, SUM(ISNULL(A.Creditor, 0)) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title ) T ''+
			'' WHERE (1=1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccountBalance6Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, C.Code+D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',3,1) AS  FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',3,0) AS  FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )  GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title,DocDate) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccountBalance8Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT
				E.ID DetailedAccount_ID,C.Code TotalAccount_Code,
				C.Code+D.Code AppointedAccount_Code,
				E.Code DetailedAccount_Code,
				E.Title DetailedAccount_Title,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',3,1) AS FDebtor,acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',3,0) AS  FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				acc.FirstRemainAcc(E.ID,''''''+@DateStart+'''''',3,1)+SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) SDebtor,
				acc.FirstRemainAcc(E.ID,+''''''+@DateStart+'''''',3,0)+SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) SCreditor,
				CASE WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor,0)) - SUM(ISNULL(A.Creditor,0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
				''+
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )''+	
			'' GROUP BY C.Code, C.Title,D.Code, D.Title,E.ID,E.Code,E.Title) T''+
			'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_DetailedAccountBalanceSummary2Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = ''
			SELECT * FROM(
			SELECT 
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				D.ID AppointedAccount_ID, C.Code+D.Code AppointedAccount_Code, D.Title AppointedAccount_Title,
				E.ID DetailedAccount_ID,E.Code DetailedAccount_Code,E.Title DetailedAccount_Title,
				0 FDebtor,0 FCreditor
				,0 DebtorFlow,0 CreditorFlow
				,0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
				INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
				INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
				'' +
				
			'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND ''+@DocDate+ 		
			'' GROUP BY C.ID, C.Code, C.Title, D.ID, D.Code, D.Title,E.ID,E.Code,E.Title) T ''+
			'' WHERE (1=1)'' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditAccountDocDetails]
			@TotalAccount_ID INT,
			@AppointmentAccount_ID INT,
			@DetailedAccount_ID INT,
			@DetailedAccount_ID2 INT,
			@DetailedAccount_ID3 INT,
			@RowDescription NVARCHAR(MAX),
			@Creditor DECIMAL(18,2),
			@Debtor DECIMAL(18,2),
			@ModifiedDate NVARCHAR(10),
			@ModifiedBy NVARCHAR(1500),
			@AccountingDocMaster_ID INT,
			@ID INT
		AS
		UPDATE acc.tbl_AccountingDocDetails SET
			TotalAccount_ID = @TotalAccount_ID,
			AppointmentAccount_ID = @AppointmentAccount_ID,
			DetailedAccount_ID = @DetailedAccount_ID,
			DetailedAccount_ID2 = @DetailedAccount_ID2,
			DetailedAccount_ID3 = @DetailedAccount_ID3,
			RowDescription = @RowDescription,
			Creditor = @Creditor,
			Debtor = @Debtor,
			ModifiedDate = @ModifiedDate,
			ModifiedBy = @ModifiedBy
		WHERE
			AccountingDocMaster_ID = @AccountingDocMaster_ID AND ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditAccountDocMaster]
			@DocCode INT,
			@DocDate NVARCHAR(10),
			@DocType SMALLINT,
			@DocDescription NVARCHAR(300),
			@ModifiedDate NVARCHAR(10),
			@ModifiedBy NVARCHAR(1500),
			@ID INT,
			@ReferenceNo INT
		AS
		UPDATE acc.tbl_AccountingDocMaster SET
			DocCode = @DocCode,
			DocDate = @DocDate,
			DocType = @DocType,
			DocDescription = @DocDescription,
			ModifiedDate = @ModifiedDate,
			ModifiedBy = @ModifiedBy
		WHERE
			ID = @ID AND ReferenceNo = @ReferenceNo')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditAccountGroup]
	@Code NVARCHAR(50),
	@Title NVARCHAR(1500),
	@Description NVARCHAR(MAX),	
	@Nature SMALLINT,
	@ID INT
AS
	UPDATE acc.tbl_AccountGroups SET
		Code = @Code,
		Title = @Title,
		[Description] = @Description,	
		Nature = @Nature
	WHERE
		ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditAppointmentAccount]
			@Code NVARCHAR(50),
			@Title NVARCHAR(1500),
			@Description NVARCHAR(MAX),
			@TotalAccount_ID INT,
			@ID INT
		AS
		UPDATE acc.tbl_AppointmentAccounts SET
			Code = @Code,
			Title = @Title,
			[Description] = @Description,
			EnCode = CAST(@Code AS INT)
		WHERE
			TotalAccount_ID = @TotalAccount_ID AND ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditDetailedAccount]
			@AccountGroup_ID INT,
			@TotalAccount_ID INT,
			@AppointmentAccount_ID INT,
			@Code NVARCHAR(50),
			@Title NVARCHAR(1500),
			@Related_ID INT,
			@Related_Type SMALLINT,
			@ID INT,
			@DARelation_ID INT,
			@DAGroup_ID INT
		AS
			UPDATE acc.tbl_DetailedAccounts SET
				Code = @Code,
				Title = @Title,
				EnCode = CAST(@Code AS INT)
			WHERE
				ID = @ID
		
			UPDATE acc.tbl_DetailedAccountsRelation SET
				AccountGroup_ID = @AccountGroup_ID,
				AppointmentAccount_ID = @AppointmentAccount_ID,
				DetailedAccountGroup_ID = @DAGroup_ID,
				DetailedAccount_ID = @ID,
				Related_ID = @Related_ID,
				Related_Type = @Related_Type,
				TotalAccount_ID = @TotalAccount_ID
			WHERE
				ID = @DARelation_ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AutoUpdateAccountDocMaster]
			@DocDate NVARCHAR(10),
			@DocType SMALLINT,
			@DocDescription NVARCHAR(300),
			@ID INT
		AS
		UPDATE acc.tbl_AccountingDocMaster SET
			DocDate = @DocDate,
			DocType = @DocType,
			DocDescription = @DocDescription
		WHERE
			ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditDetailedAccountGroup]
			@Code INT,
			@Title NVARCHAR(500),
			@Level SMALLINT,
			@ID INT
		AS
	
			UPDATE acc.tbl_DetailedAccountGroups SET
				Code = @Code,
				Title = @Title,
				[LEVEL] =@Level
				WHERE
				ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_EditTotalAccount]
			@Code NVARCHAR(50),
			@Title NVARCHAR(1500),
			@Description NVARCHAR(MAX),
			@Nature SMALLINT,
			@AccountGroup_ID INT,
			@ID INT
		AS

		UPDATE acc.tbl_TotalAccounts SET
			Code = @Code,
			Title = @Title,
			[Description] = @Description,
			Nature = @Nature,
			EnCode = CAST(@Code AS INT)	
		WHERE
			AccountGroup_ID = @AccountGroup_ID AND ID = @ID')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAccCodingInfoByCode]
			@TableName NVARCHAR(500),
			@CodeValue NVARCHAR(500),
			@Level SMALLINT
		AS
			IF (@TableName = ''AccTotal'')
				SELECT AccountGroup_ID, ID, Code, Title FROM acc.tbl_TotalAccounts WHERE Code = @CodeValue

			IF (@TableName = ''AccAppointment'')
			BEGIN
				SELECT B.ID,(A.Code+B.Code) Code, B.Title FROM acc.tbl_AppointmentAccounts AS B 
				INNER JOIN [acc].[tbl_TotalAccounts] AS A ON A.ID=B.TotalAccount_ID
				WHERE (A.Code+B.Code) = @CodeValue
			END
			

			IF (@TableName = ''AccDetailed'')
			BEGIN
				SELECT ID, Code, Title FROM acc.tbl_DetailedAccounts WHERE Code = @CodeValue AND [Level]=@Level
				
				UNION ALL
				SELECT da.ID,(C.Code+B.Code+da.Code) AS Code,da.Title FROM acc.tbl_DetailedAccounts AS da 
				INNER JOIN acc.tbl_DetailedAccountsRelation AS dr ON dr.DetailedAccount_ID=da.ID
				INNER JOIN [acc].[tbl_AppointmentAccounts] AS B ON B.ID=dr.AppointmentAccount_ID
				INNER JOIN [acc].[tbl_TotalAccounts] AS C ON C.ID=dr.TotalAccount_ID
				WHERE C.Code+B.Code+da.Code= @CodeValue AND @Level=1
			END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAccNewCode]
			@AccountLevel NVARCHAR(100),
			@Parent_ID INT,
			@Related BIT,
			@DeltailedLevel INT
		AS
		BEGIN
		DECLARE @NewCode INT
		DECLARE @ParentCode INT
			
			IF (@AccountLevel = ''AccGroup'')
				SELECT ISNULL(MAX(EnCode),0) + 1 NewCode FROM acc.tbl_AccountGroups
	
			IF (@AccountLevel = ''AccTotal'')
			BEGIN
				SET @NewCode=(SELECT ISNULL(MAX(EnCode),0)+ 1 NewCode FROM acc.tbl_TotalAccounts WHERE AccountGroup_ID=@Parent_ID)
				SET @ParentCode=(SELECT Code FROM acc.tbl_AccountGroups WHERE ID=@Parent_ID)
				SELECT @NewCode,@ParentCode
			END	
				
		
			IF (@AccountLevel = ''AccAppointed'')
				SELECT ISNULL(MAX(EnCode),0)+ 1 NewCode FROM acc.tbl_AppointmentAccounts WHERE TotalAccount_ID = @Parent_ID
		
			IF (@AccountLevel = ''AccDetailGroup'')
				SELECT ISNULL(MAX(Code),0) + 1 NewCode FROM acc.tbl_DetailedAccountGroups WHERE [Level]=@Parent_ID
				
			IF (@AccountLevel = ''AccDetailed'')
			BEGIN
				IF @Related=1
					BEGIN
						SELECT ISNULL(MAX(EnCode),0) + 1 NewCode FROM acc.tbl_DetailedAccounts AS A
						INNER JOIN acc.tbl_DetailedAccountsRelation AS B ON B.DetailedAccount_ID=A.ID 
						 WHERE B.AppointmentAccount_ID=@Parent_ID
					END
				ELSE
					BEGIN
						SELECT ISNULL(MAX(EnCode),0) + 1 NewCode FROM acc.tbl_DetailedAccounts AS A
						INNER JOIN acc.tbl_DetailedAccountsRelation AS B ON B.DetailedAccount_ID=A.ID 
						 WHERE B.DetailedAccountGroup_ID=@Parent_ID AND A.[Level]=@DeltailedLevel
					END
			END	
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAccountDocDetails]
			@AccountDocMaster_ID INT
		AS	
		SELECT
			A.AccountingDocMaster_ID, A.ID, A.RowNum,
			A.TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
			A.AppointmentAccount_ID,(C.Code+D.Code) AppointmentAccount_Code, D.Title AppointmentAccount_Title,
			A.DetailedAccount_ID,CASE WHEN ISNULL(E.TotalAccount_ID,0)<>0 THEN (C.Code+D.Code+E.Code) ELSE E.Code END  DetailedAccount_Code, E.Title DetailedAccount_Title,
			A.DetailedAccount_ID2, F.Code  DetailedAccount_Code2, F.Title DetailedAccount_Title2,
			A.DetailedAccount_ID3, G.Code  DetailedAccount_Code3, G.Title DetailedAccount_Title3,
			A.RowDescription, ISNULL(A.Creditor,0) Creditor , ISNULL(A.Debtor,0) Debtor ,A.ReferenceNo, 
			A.CreateDate, A.CreateBy, A.ModifiedDate, A.ModifiedBy
		FROM acc.tbl_AccountingDocDetails A
			INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
			INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
			INNER JOIN acc.tbl_AppointmentAccounts D ON A.AppointmentAccount_ID = D.ID AND A.TotalAccount_ID = D.TotalAccount_ID
			LEFT OUTER JOIN (
				SELECT B.ID, B.Code, B.Title, A.AccountGroup_ID, A.AppointmentAccount_ID, A.TotalAccount_ID FROM acc.tbl_DetailedAccountsRelation A
					INNER JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
			) E ON A.DetailedAccount_ID = E.ID
			LEFT OUTER JOIN (
				SELECT B.ID, B.Code, B.Title, A.AccountGroup_ID, A.AppointmentAccount_ID, A.TotalAccount_ID FROM acc.tbl_DetailedAccountsRelation A
					INNER JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
			) F ON A.DetailedAccount_ID2 = F.ID
			LEFT OUTER JOIN (
				SELECT B.ID, B.Code, B.Title, A.AccountGroup_ID, A.AppointmentAccount_ID, A.TotalAccount_ID FROM acc.tbl_DetailedAccountsRelation A
					INNER JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
			) G ON A.DetailedAccount_ID3 = G.ID
								
		WHERE
			(A.AccountingDocMaster_ID = @AccountDocMaster_ID)

		ORDER BY Rownum')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAccountDocMaster]
			@AccountDocMaster_ID INT
		AS
		SELECT 
			A.ID, A.ReferenceNo, A.DocCode, A.DocDate, 
			A.DocState, B.Title DocState_Title,
			A.DocDescription,
			A.CreateDate, A.CreateBy, A.ModifiedDate, A.ModifiedBy,
			A.OldID, A.Amendment_ID,A.tmp,A.ConfirmedBy,A.permanentBy,
			 DocType,A.[Deleted],
			 A.Reserve
		FROM acc.tbl_AccountingDocMaster A
			INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
		WHERE
			(@AccountDocMaster_ID IS NULL OR A.ID = @AccountDocMaster_ID)')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAccountDocStatusList]
		AS
		SELECT ID, Title FROM acc.tbl_AccountingDocStatus')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[Sp_GetAccReport]
			@AccLevel SMALLINT,
			@DetailedLevel SMALLINT,
			@AccID INT,
			@ADateFrom VARCHAR(10),
	        @ADateTo VARCHAR(10),
	        @MandehAzGhabl BIT
	AS
	BEGIN
			DECLARE @AccReport TABLE (
				[AccountingDocMaster_ID] INT NULL,
				[DocCode] INT NULL,
				[DocDate] NVARCHAR(10) NULL,
				[DocDescription] NVARCHAR(300) NULL,
	            [Bed] Decimal (18,2) NULL,
	            [Bes] Decimal (18,2) NULL,
	            [Tashkhis] [nvarchar](50) NULL,
	            [Mandeh] Decimal (18,2) NULL
			)
	DECLARE @RemainValue DECIMAL(18,2)
		
--------------------------------------------------------Mandeh Az Ghabl
IF @MandehAzGhabl=1
BEGIN
	
		IF @AccLevel=1
	BEGIN
		
			INSERT INTO @AccReport
			(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
			SELECT 0,NULL,NULL,N''مانده از قبل'',CASE WHEN (ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))>0 THEN
											(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))
											ELSE
											NULL END,
										CASE WHEN (ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))>0 THEN
											(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))
											ELSE
											NULL END,
										NULL,NULL
											
			FROM acc.tbl_AccountingDocMaster A
			INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
			
			WHERE 
			(A.DocDate<@ADateFrom) AND (B.TotalAccount_ID=@AccID) AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			HAVING 
			(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))<>0
		END	
IF @AccLevel=2
	BEGIN
		
			INSERT INTO @AccReport
			(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
			SELECT 0,0,NULL,N''مانده از قبل'',CASE WHEN (ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))>0 THEN
											(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))
											ELSE
											0 END,
										CASE WHEN (ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))>0 THEN
											(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))
											ELSE
											0 END,
										NULL,NULL
											
			FROM acc.tbl_AccountingDocMaster A
			INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
			
			WHERE 
			(A.DocDate<@ADateFrom) AND (B.AppointmentAccount_ID=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			HAVING 
			(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))<>0
		END	
		IF @AccLevel=3
	BEGIN
		IF @DetailedLevel=1
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT 0,0,NULL,N''مانده از قبل'',CASE WHEN (ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))>0 THEN
												(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))
												ELSE
												0 END,
											CASE WHEN (ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))>0 THEN
												(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))
												ELSE
												0 END,
											NULL,NULL
												
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
			(A.DocDate<@ADateFrom) AND (B.DetailedAccount_ID=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			HAVING 
			(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))<>0
		END	
		
				IF @DetailedLevel=2
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT 0,0,NULL,N''مانده از قبل'',CASE WHEN (ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))>0 THEN
												(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))
												ELSE
												0 END,
											CASE WHEN (ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))>0 THEN
												(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))
												ELSE
												0 END,
											NULL,NULL
												
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
			(A.DocDate<@ADateFrom) AND (B.DetailedAccount_ID2=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			HAVING 
			(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))<>0
		END	
		
				IF @DetailedLevel=3
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT 0,0,NULL,N''مانده از قبل'',CASE WHEN (ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))>0 THEN
												(ISNULL(SUM(Debtor),0)-ISNULL(SUM(Creditor),0))
												ELSE
												0 END,
											CASE WHEN (ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))>0 THEN
												(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))
												ELSE
												0 END,
											NULL,NULL
												
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
			(A.DocDate<@ADateFrom) AND (B.DetailedAccount_ID3=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			HAVING 
			(ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0))<>0
		END	
	END		
		
END	
---------------------------------------------------------------------------------------------------		

		IF @AccLevel=1
	BEGIN
		
			INSERT INTO @AccReport
			(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
			SELECT A.ID,A.DocCode,A.DocDate,DocDescription,ISNULL(SUM(Debtor),0),ISNULL(SUM(Creditor),0),NULL,NULL							
			FROM acc.tbl_AccountingDocMaster A
			INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
			WHERE 
			(A.DocDate BETWEEN @ADateFrom AND @ADateTo) AND (B.TotalAccount_ID=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			GROUP BY A.ID,A.DocCode,A.DocDate,A.DocDescription
		END	
IF @AccLevel=2
	BEGIN
		
	INSERT INTO @AccReport
			(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
			SELECT A.ID,A.DocCode,A.DocDate,DocDescription,ISNULL(SUM(Debtor),0),ISNULL(SUM(Creditor),0),NULL,NULL							
			FROM acc.tbl_AccountingDocMaster A
			INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
			WHERE 
			(A.DocDate BETWEEN @ADateFrom AND @ADateTo) AND (B.AppointmentAccount_ID=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
			GROUP BY A.ID,A.DocCode,A.DocDate,A.DocDescription
		END	
		IF @AccLevel=3
	BEGIN
		IF @DetailedLevel=1
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT A.ID,A.DocCode,A.DocDate,DocDescription,ISNULL(SUM(Debtor),0),ISNULL(SUM(Creditor),0),NULL,NULL							
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
				(A.DocDate BETWEEN @ADateFrom AND @ADateTo)  AND (B.DetailedAccount_ID=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
				GROUP BY A.ID,A.DocCode,A.DocDate,A.DocDescription
			END
			IF @DetailedLevel=2
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT A.ID,A.DocCode,A.DocDate,DocDescription,ISNULL(SUM(Debtor),0),ISNULL(SUM(Creditor),0),NULL,NULL							
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN acc.tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
				(A.DocDate BETWEEN @ADateFrom AND @ADateTo)  AND (B.DetailedAccount_ID2=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
				GROUP BY A.ID,A.DocCode,A.DocDate,A.DocDescription
			END
			IF @DetailedLevel=3
			begin
				INSERT INTO @AccReport
				(AccountingDocMaster_ID,DocCode,DocDate,DocDescription,Bed,Bes,Tashkhis,Mandeh)
				SELECT A.ID,A.DocCode,A.DocDate,DocDescription,ISNULL(SUM(Debtor),0),ISNULL(SUM(Creditor),0),NULL,NULL							
				FROM acc.tbl_AccountingDocMaster A
				INNER JOIN acc.tbl_AccountingDocDetails B ON A.ID=B.AccountingDocMaster_ID
				
				WHERE 
				(A.DocDate BETWEEN @ADateFrom AND @ADateTo)  AND (B.DetailedAccount_ID3=@AccID)AND (ISNULL(A.DELETED,0)=0 AND ISNULL(A.Tmp,0)=0)
				GROUP BY A.ID,A.DocCode,A.DocDate,A.DocDescription
			END
		END
	
SET @RemainValue=0	
UPDATE @AccReport
SET
	@RemainValue=(ISNULL(@RemainValue,0)-ISNULL(Bed,0)+ISNULL(Bes,0))
	,Mandeh =ABS(@RemainValue)
	,Tashkhis =CASE
	  WHEN @RemainValue<0 THEN N''بدهکار''
	  WHEN @RemainValue>0 THEN N''بستانکار''
	  ELSE N''بی حساب''
	 END--EndCase
	    --
SELECT * FROM @AccReport 
ORDER BY DocDate																									
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAllAccountingDocList]
			@WClause NVARCHAR(MAX) = NULL
		AS
		BEGIN
			DECLARE @Query NVARCHAR(MAX) = N''
				WITH Q AS (
					SELECT
						A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,1 as GroupDoc,
						Amendment_ID, 
						CASE
							WHEN A.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''
							WHEN A.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
							WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
							WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
							WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
							WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
						END Amendment_Title, A.SubSystem
						,ISNULL(acc.AccDocRemain(A.ID,1),0) TotalDebtor
						,ISNULL(acc.AccDocRemain(A.ID,0),0) TotalCreditor 
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE (ISNULL(A.DELETED,0)<>1) AND (ISNULL(A.tmp,0)<>1) 
					
				UNION ALL
					SELECT
						A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,2 as GroupDoc,
						Amendment_ID,
						 N''''حسابداری''''
						AS Amendment_Title, A.SubSystem
						,ISNULL(acc.AccDocRemain(A.ID,1),0) TotalDebtor
						,ISNULL(acc.AccDocRemain(A.ID,0),0) TotalCreditor 
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE (ISNULL(A.DELETED,0)<>1) AND (ISNULL(A.tmp,0)<>1) AND A.SubSystem =101
					
			UNION ALL
			
					SELECT
						A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,3 as GroupDoc,
						Amendment_ID, 
						CASE
							WHEN A.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
							WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
							WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
							WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
							WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
						END Amendment_Title, A.SubSystem
						,ISNULL(acc.AccDocRemain(A.ID,1),0) TotalDebtor
						,ISNULL(acc.AccDocRemain(A.ID,0),0) TotalCreditor 
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE (ISNULL(A.DELETED,0)<>1) AND (ISNULL(A.tmp,0)<>1) AND A.SubSystem<>101
					
			UNION ALL
					SELECT
						A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,4 as GroupDoc,
						Amendment_ID, 
						CASE
							WHEN A.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''	
							WHEN A.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
							WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
							WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
							WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
							WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
						END Amendment_Title, A.SubSystem
						,ISNULL(acc.AccDocRemain(A.ID,1),0) TotalDebtor
						,ISNULL(acc.AccDocRemain(A.ID,0),0) TotalCreditor 
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE (ISNULL(A.DELETED,0)<>1) AND (ISNULL(A.tmp,0)<>1) AND A.Reserve=1
						
			UNION ALL
			
					SELECT
						A.ID, A.ReferenceNo,CASE WHEN A.DocCode IS NOT NULL THEN A.DocCode ELSE A.OldDocCode END as DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,5 as GroupDoc,
						Amendment_ID, 
						CASE
							WHEN A.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''	
							WHEN A.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
							WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
							WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
							WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
							WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
						END Amendment_Title, A.SubSystem

						,ISNULL(acc.AccDocRemain(A.ID,1),0) TotalDebtor
						,ISNULL(acc.AccDocRemain(A.ID,0),0) TotalCreditor 
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE ISNULL(A.DELETED,0)=1	OR 	ISNULL(A.tmp,0)=1 
							 
				)
					SELECT Q.* FROM Q''+
			'' WHERE (1 = 1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END +
			'' GROUP BY Q.Amendment_ID, Q.Amendment_Title, Q.DocCode, Q.DocDate, Q.DocDescription,Q.DocState,
					   Q.DocState_Title,Q.GroupDoc, Q.ID, Q.ReferenceNo, Q.SubSystem,Q.TotalDebtor,Q.TotalCreditor
					ORDER BY DocDate,DocCode''

			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAllDetailedAccountsType]
			@TotalAccount_ID INT,
			@AppointmentAccount_ID INT,
			@Level SMALLINT
		AS
			SELECT B.ID, B.Code, B.Title,D.Code+E.Code+B.Code AS VCode  FROM acc.tbl_DetailedAccountsRelation A
				INNER JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts D ON A.TotalAccount_ID = D.ID
				INNER JOIN acc.tbl_AppointmentAccounts E ON A.AppointmentAccount_ID = E.ID AND E.TotalAccount_ID = A.TotalAccount_ID
			WHERE
				(A.TotalAccount_ID = @TotalAccount_ID AND @TotalAccount_ID <> 0) AND (A.AppointmentAccount_ID = @AppointmentAccount_ID AND @AppointmentAccount_ID <> 0 )
		
			UNION ALL
			
				SELECT B.ID, B.Code, B.Title,D.Code+E.Code+B.Code AS VCode FROM acc.tbl_DetailedAccountsRelation A
				INNER JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
				INNER JOIN acc.tbl_TotalAccounts D ON A.TotalAccount_ID = D.ID
				INNER JOIN acc.tbl_AppointmentAccounts E ON A.AppointmentAccount_ID = E.ID AND E.TotalAccount_ID = A.TotalAccount_ID
			WHERE
				@TotalAccount_ID =0  AND  @AppointmentAccount_ID =0 AND @LEVEL=1
		
			UNION ALL
			
			SELECT B.ID, B.Code, B.Title,B.Code AS VCode FROM acc.tbl_DetailedAccountsRelation A
				LEFT JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
			WHERE
				A.TotalAccount_ID IS NULL AND A.AppointmentAccount_ID IS NULL AND LEVEL=@Level 
				AND @TotalAccount_ID=0 AND @AppointmentAccount_ID=0
				
			UNION ALL
			
			SELECT B.ID, B.Code, B.Title,B.Code AS VCode FROM acc.tbl_DetailedAccountsRelation A
				LEFT JOIN acc.tbl_DetailedAccounts B ON A.DetailedAccount_ID = B.ID
				INNER JOIN acc.tbl_DetailedAccountGroups C ON c.ID=A.DetailedAccountGroup_ID AND C.[Level]=@Level
				INNER JOIN acc.tbl_DetailedAccGroupToAppointmentRel D ON D.DetailedAccGroup_ID=A.DetailedAccountGroup_ID AND D.Appointment_ID=@AppointmentAccount_ID
				
			WHERE
				A.TotalAccount_ID IS NULL AND A.AppointmentAccount_ID IS NULL AND B.LEVEL=@Level 

	ORDER BY b.Code	')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetAllReservedAccDocs]
			@WClause NVARCHAR(MAX) = NULL
		AS
		BEGIN
			DECLARE @Query NVARCHAR(MAX) = N''
			SELECT CAST(0 AS BIT) SelRow, 
				A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription, A.Amendment_ID, 
				CASE
					WHEN A.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''
					WHEN A.SubSYstem LIKE ''''102%'''' THEN N''''فروش''''
					WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
					WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
					WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
					WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
				END Amendment_Title,
				B.Title DocState_Title, 
				CAST(0 AS DECIMAL(18, 2)) TotalDebtor, 
				CAST(0 AS DECIMAL(18, 2)) TotalCreditor, A.DocState, CAST(0 AS INT) TotalAccount_ID, CAST(0 AS INT) AppointmentAccount_ID, CAST(0 AS INT) DetailedAccount_ID, A.SubSystem
			FROM acc.tbl_AccountingDocMaster A
				INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
			 WHERE A.ID NOT IN (SELECT AccountingDocMaster_ID FROM acc.tbl_AccountingDocDetails) AND (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0)''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END +
			'' ORDER BY DocDate''

	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetDeletedAccountingDocList]
			@WClause NVARCHAR(MAX) = NULL
		AS
		BEGIN
			DECLARE @Query NVARCHAR(MAX) = N''
				WITH Q AS (
					SELECT
						CAST(0 AS BIT) SelRow,
						A.ID, A.ReferenceNo, A.DocCode, A.DocDate, A.DocDescription,A.DocState, 
						B.Title DocState_Title,
						Amendment_ID, 
						CASE
							WHEN A.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''
							WHEN A.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
							WHEN A.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
							WHEN A.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
							WHEN A.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
							WHEN A.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
						END Amendment_Title, A.SubSystem
					FROM acc.tbl_AccountingDocMaster A
						INNER JOIN acc.tbl_AccountingDocStatus B ON A.DocState = B.ID
					WHERE ISNULL(A.DELETED,0)=1	OR 	ISNULL(A.tmp,0)=1 
				)
					SELECT Q.*, SUM(ISNULL(X.Debtor, 0)) TotalDebtor, SUM(ISNULL(X.Creditor, 0)) TotalCreditor FROM Q
						LEFT JOIN acc.tbl_AccountingDocDetails X ON X.AccountingDocMaster_ID = Q.ID'' +
			'' WHERE (1 = 1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE '' AND '' + @WClause END +
			'' GROUP BY Q.Amendment_ID, Q.Amendment_Title, Q.DocCode, Q.DocDate, Q.DocDescription,Q.DocState, 
					   Q.DocState_Title, Q.ID, Q.ReferenceNo, Q.SubSystem, Q.SelRow
			 ORDER BY DocDate''

			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetNewAccDocCode]
		AS
		SELECT 
			ISNULL(MAX(DocCode),0)+1
		FROM acc.tbl_AccountingDocMaster
		WHERE  (ISNULL(Deleted,0)<>1)')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetNonRelatedDetailAccounts] @Level SMALLINT
AS
SELECT 
	A.ID,
	A.Code,
	A.Title,
	A.EnCode,
	A.[Level],
	Rel.DetailedAccountGroup_ID AS DAGroup_ID,
	Rel.Related_ID,
	Rel.ID DARelation_ID
FROM acc.tbl_DetailedAccounts AS A
INNER JOIN 
	acc.tbl_DetailedAccountsRelation AS rel ON rel.DetailedAccount_ID=A.ID
WHERE	(AccountGroup_ID IS NULL) AND (TotalAccount_ID IS NULL) AND (AppointmentAccount_ID IS NULL) AND [LEVEL]=@Level
ORDER BY A.EnCode')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetRelatedDetailAccounts]
AS
SELECT 
	Rel.Related_ID , Rel.AccountGroup_ID,Rel.DetailedAccountGroup_ID DAGroup_ID,Rel.ID DARelation_ID, B.Code AccountGroup_Code, B.Title AccountGroup_Title,
	Rel.TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
	Rel.AppointmentAccount_ID, (C.Code+D.Code) AppointmentAccount_Code, D.Title AppointmentAccount_Title,
	A.ID, A.Code, A.Title,A.EnCode
FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_DetailedAccountsRelation Rel ON Rel.DetailedAccount_ID=A.ID
	INNER JOIN acc.tbl_AccountGroups B ON Rel.AccountGroup_ID = B.ID
	INNER JOIN acc.tbl_TotalAccounts C ON Rel.TotalAccount_ID = C.ID AND Rel.AccountGroup_ID = C.AccountGroup_ID
	INNER JOIN acc.tbl_AppointmentAccounts D ON Rel.AppointmentAccount_ID = D.ID AND Rel.TotalAccount_ID = D.TotalAccount_ID
WHERE
	(Rel.AccountGroup_ID IS NOT NULL) AND (Rel.TotalAccount_ID IS NOT NULL) AND (Rel.AppointmentAccount_ID IS NOT NULL)
ORDER BY c.code,D.Code, A.EnCode')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetRemaindAccCoding]
@ID INT,
@Level NVARCHAR(50)
AS
DECLARE @Remaind DECIMAL(18,2)
DECLARE @Creditor DECIMAL(18,2)
DECLARE @Debtor DECIMAL(18,2)
DECLARE @VCode NVARCHAR(50)
DECLARE @Title NVARCHAR(100)
DECLARE @NatureTitle NVARCHAR(50)



IF @Level=''almAccTotal''
BEGIN

 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0)FROM acc.tbl_AccountingDocDetails A
 INNER JOIN acc.tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE A.TotalAccount_ID=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
  GROUP BY B.Code,B.Title
  
SELECT  @VCode=A.Code,@Title=A.Title FROM acc.tbl_TotalAccounts AS A
	
WHERE A.ID=@ID 

 
END

IF @Level=''almAccAppointment''
BEGIN

 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails A
 INNER JOIN acc.tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
 INNER JOIN [acc].[tbl_AppointmentAccounts] AS C ON C.TotalAccount_ID=B.ID
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE A.AppointmentAccount_ID=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
 
 
 SELECT @VCode=(B.Code+A.Code),@Title=B.Title+'' - ''+A.Title FROM acc.[tbl_AppointmentAccounts] A
 INNER JOIN acc.tbl_TotalAccounts B ON B.ID=A.TotalAccount_ID
  WHERE A.ID=@ID 

END

IF @Level=''almDetailedRel''
BEGIN
 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0)FROM acc.tbl_AccountingDocDetails A
 INNER JOIN	acc.tbl_DetailedAccounts AS da ON da.ID=A.DetailedAccount_ID
 INNER JOIN acc.tbl_DetailedAccountsRelation AS dr ON dr.DetailedAccount_ID=da.ID
 INNER JOIN [acc].[tbl_AppointmentAccounts] AS C ON C.ID=dr.AppointmentAccount_ID
 INNER JOIN [acc].[tbl_TotalAccounts] AS B ON B.ID=dr.TotalAccount_ID
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE da.ID=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
 
 SELECT @VCode=(B.Code+C.Code+da.Code),@Title=B.Title+'' - ''+C.Title+'' - ''+da.Title
   FROM acc.tbl_DetailedAccounts da
 INNER JOIN acc.tbl_DetailedAccountsRelation AS dr ON dr.DetailedAccount_ID=da.ID
 INNER JOIN [acc].[tbl_AppointmentAccounts] AS C ON C.ID=dr.AppointmentAccount_ID
 INNER JOIN [acc].[tbl_TotalAccounts] AS B ON B.ID=dr.TotalAccount_ID
 
  WHERE da.ID=@ID
END

IF @Level=''almDetailedNoRel1''
BEGIN
 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0)FROM acc.tbl_AccountingDocDetails A
 INNER JOIN	acc.tbl_DetailedAccounts AS da ON da.ID=A.DetailedAccount_ID
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE A.DetailedAccount_ID=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
 
 SELECT @VCode=(da.Code),@Title=da.Title FROM acc.tbl_DetailedAccounts da 
  WHERE da.ID=@ID
END

IF @Level=''almDetailedNoRel2''
BEGIN
 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0)FROM acc.tbl_AccountingDocDetails A
 INNER JOIN	acc.tbl_DetailedAccounts AS da ON da.ID=A.DetailedAccount_ID2
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE A.DetailedAccount_ID2=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
 
 SELECT @VCode=(da.Code),@Title=da.Title FROM acc.tbl_DetailedAccounts da 
  WHERE da.ID=@ID
END

IF @Level=''almDetailedNoRel3''
BEGIN
 SELECT @Creditor= ISNULL(SUM(Creditor),0),@Debtor=ISNULL(SUM(Debtor),0)FROM acc.tbl_AccountingDocDetails A
 INNER JOIN	acc.tbl_DetailedAccounts AS da ON da.ID=A.DetailedAccount_ID3
 INNER JOIN acc.tbl_AccountingDocMaster AS Ma ON Ma.ID=A.AccountingDocMaster_ID
 WHERE A.DetailedAccount_ID3=@ID AND (ISNULL(Ma.Deleted,0)=0 AND ISNULL(Ma.tmp,0)=0)
 
 SELECT @VCode=(da.Code),@Title=da.Title FROM acc.tbl_DetailedAccounts da 
  WHERE da.ID=@ID
END

SET @Remaind=@Creditor-@Debtor

IF @Remaind=0
SET @NatureTitle=N''بی حساب''
IF @Remaind>0
SET @NatureTitle=N''بستانکار''
IF @Remaind<0
SET @NatureTitle=N''بدهکار''

SELECT @Creditor Creditor,@Debtor Debtor,abs(@Remaind) AS Remaind , @NatureTitle AS NatureTitle,@VCode AS VCode,@Title AS Title ')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetRemaindAccDoc]
@T_ID INT,
@A_ID INT,
@D_ID INT,
@D_ID2 INT,
@D_ID3 INT
AS
DECLARE @T_Remaind DECIMAL(18,2)
DECLARE @A_Remaind DECIMAL(18,2)
DECLARE @D_Remaind DECIMAL(18,2)
DECLARE @D_Remaind2 DECIMAL(18,2)
DECLARE @D_Remaind3 DECIMAL(18,2)

DECLARE @T_NatureTitle NVARCHAR(50)
DECLARE @A_NatureTitle NVARCHAR(50)
DECLARE @D_NatureTitle NVARCHAR(50)
DECLARE @D_NatureTitle2 NVARCHAR(50)
DECLARE @D_NatureTitle3 NVARCHAR(50)



IF @T_ID<>0
BEGIN

SET @T_Remaind=( SELECT ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails AS A INNER JOIN acc.tbl_AccountingDocMaster AS B ON B.ID=A.AccountingDocMaster_ID WHERE TotalAccount_ID=@T_ID AND (ISNULL(b.[Deleted],0)=0  AND ISNULL(B.tmp,0)=0 ))

IF @T_Remaind=0
SET @T_NatureTitle=N''بی حساب''

IF @T_Remaind>0
SET @T_NatureTitle=N''بستانکار''

IF @T_Remaind<0
BEGIN
SET @T_NatureTitle=N''بدهکار''	
END

END

IF @A_ID<>0
BEGIN

SET @A_Remaind=( SELECT ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails AS A INNER JOIN acc.tbl_AccountingDocMaster AS B ON B.ID=A.AccountingDocMaster_ID WHERE AppointmentAccount_ID=@A_ID AND (ISNULL(b.[Deleted],0)=0  AND ISNULL(B.tmp,0)=0 ))

IF @A_Remaind=0
SET @A_NatureTitle=N''بی حساب''

IF @A_Remaind>0
SET @A_NatureTitle=N''بستانکار''

IF @A_Remaind<0
SET @A_NatureTitle=N''بدهکار''


END

IF @D_ID<>0
BEGIN

SET @D_Remaind=( SELECT ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails AS A INNER JOIN acc.tbl_AccountingDocMaster AS B ON B.ID=A.AccountingDocMaster_ID WHERE DetailedAccount_ID=@D_ID AND (ISNULL(b.[Deleted],0)=0  AND ISNULL(B.tmp,0)=0 ))

IF @D_Remaind=0
SET @D_NatureTitle=N''بی حساب''

IF @D_Remaind>0
SET @D_NatureTitle=N''بستانکار''

IF @D_Remaind<0
SET @D_NatureTitle=N''بدهکار''

END

IF @D_ID2<>0
BEGIN

SET @D_Remaind2=( SELECT ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails AS A INNER JOIN acc.tbl_AccountingDocMaster AS B ON B.ID=A.AccountingDocMaster_ID WHERE DetailedAccount_ID2=@D_ID2 AND (ISNULL(b.[Deleted],0)=0  AND ISNULL(B.tmp,0)=0 ))

IF @D_Remaind2=0
SET @D_NatureTitle2=N''بی حساب''

IF @D_Remaind2>0
SET @D_NatureTitle2=N''بستانکار''

IF @D_Remaind2<0
SET @D_NatureTitle2=N''بدهکار''

END

IF @D_ID3<>0
BEGIN

SET @D_Remaind3=( SELECT ISNULL(SUM(Creditor),0)-ISNULL(SUM(Debtor),0) FROM acc.tbl_AccountingDocDetails AS A INNER JOIN acc.tbl_AccountingDocMaster AS B ON B.ID=A.AccountingDocMaster_ID WHERE DetailedAccount_ID3=@D_ID3 AND (ISNULL(b.[Deleted],0)=0  AND ISNULL(B.tmp,0)=0 ))

IF @D_Remaind3=0
SET @D_NatureTitle3=N''بی حساب''

IF @D_Remaind3>0
SET @D_NatureTitle3=N''بستانکار''

IF @D_Remaind3<0
SET @D_NatureTitle3=N''بدهکار''

END



SELECT abs(@T_Remaind) AS T_Remaind, @T_NatureTitle AS T_NatureTitle,abs(@A_Remaind) AS A_Remaind , @A_NatureTitle AS A_NatureTitle,abs(@D_Remaind) AS D_Remaind, @D_NatureTitle AS D_NatureTitle,abs(@D_Remaind2) AS D_Remaind2, @D_NatureTitle2 AS D_NatureTitle2,abs(@D_Remaind3) AS D_Remaind3, @D_NatureTitle3 AS D_NatureTitle3   
')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO


IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_GetSourceDataList]
			@TableName NVARCHAR(MAX),
			@Fields NVARCHAR(MAX),
			@Related_Type NVARCHAR(10),
			@DetailedGroup_ID NVARCHAR(10)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(MAX)

			SET @Query = N''
			SELECT CAST(0 AS BIT) Checked, * FROM (
			SELECT '' + @Fields + '', 0 Exist, N''''غیر قابل انتقال'''' PTDState FROM '' + @TableName + ''
			WHERE ID IN (SELECT A.Related_ID FROM acc.tbl_DetailedAccounts A 
								INNER JOIN acc.tbl_DetailedAndGroupRelation B ON B.DetailedAccount_ID = A.ID
							 WHERE A.Related_Type = '' + @Related_Type + '' AND B.DetailedAccountGroup_ID = '' + @DetailedGroup_ID + '')
			UNION ALL
			SELECT '' + @Fields + '', -1 Exist, N''''قابل انتقال'''' PTDState FROM '' + @TableName + ''
			WHERE ID NOT IN (SELECT A.Related_ID FROM acc.tbl_DetailedAccounts A 
								INNER JOIN acc.tbl_DetailedAndGroupRelation B ON B.DetailedAccount_ID = A.ID
							 WHERE A.Related_Type = '' + @Related_Type + '' AND B.DetailedAccountGroup_ID = '' + @DetailedGroup_ID + '')) AA''

			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO


IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_PrintAccDoc] @AccountDocMaster_ID INT
AS


SELECT * FROM
(
SELECT * FROM(
SELECT  dc.ID,dc.TotalAccount_ID, [acc].[RemaindAccDocItems]( dc.TotalAccount_ID,@AccountDocMaster_ID,1,1)bed,0 as Bes,0 AS joz,Code AS VCode,N'''' as RowDescription , Title,N''حساب کل''AS typ,1 AS AccType ,1 AS SortBy FROM
acc.tbl_TotalAccounts A
INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.TotalAccount_ID=A.ID
WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID  AND ISNULL(dc.Debtor,0)>0 AND dc.ID=(SELECT TOP 1(ID) FROM acc.tbl_AccountingDocDetails WHERE AccountingDocMaster_ID = @AccountDocMaster_ID  AND ISNULL(Debtor,0)>0 AND TotalAccount_ID=A.ID )



UNION ALL

SELECT  dc.ID,dc.TotalAccount_ID,0 AS bed ,0 as Bes,dc.Debtor AS joz,(B.Code+A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID IS NULL AND dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription , A.Title,N''حساب معین'' AS typ,2 AS AccType, 1 AS SortBy FROM acc.tbl_AppointmentAccounts A
INNER JOIN acc.tbl_TotalAccounts AS B ON B.ID=A.TotalAccount_ID
INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.AppointmentAccount_ID=A.ID
WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND ISNULL(dc.Debtor,0)>0

UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Debtor joz, (D.Code+E.Code+A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription, A.Title,N''حساب تفضیلی'' AS typ,3 AS AccType,1 AS SortBy FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID 
	INNER JOIN acc.tbl_TotalAccounts D ON B.TotalAccount_ID = D.ID 
	INNER JOIN acc.tbl_AppointmentAccounts E ON B.AppointmentAccount_ID = E.ID AND E.TotalAccount_ID = B.TotalAccount_ID
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID=A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND ISNULL(dc.Debtor,0)>0

UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Debtor joz, (A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription, A.Title,N''حساب تفضیلی'' AS typ,3 AS AccType,1 AS SortBy
  FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND  B.TotalAccount_ID IS NULL  AND ISNULL(dc.Debtor,0)>0
	
UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Debtor joz, (A.Code) AS Vcode,CASE WHEN  dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription, A.Title,N''حساب تفضیلی2'' AS typ,4 AS AccType,1 AS SortBy
  FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID2=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND  B.TotalAccount_ID IS NULL  AND ISNULL(dc.Debtor,0)>0		
	
UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Debtor joz, (A.Code) AS Vcode, dc.RowDescription as RowDescription, A.Title,N''حساب تفضیلی3'' AS typ,5 AS AccType,1 AS SortBy
  FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID3=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND  B.TotalAccount_ID IS NULL  AND ISNULL(dc.Debtor,0)>0	
	
	)T 
	

UNION ALL

SELECT * FROM(
	
SELECT  dc.ID,dc.TotalAccount_ID,0 bed,[acc].[RemaindAccDocItems](dc.TotalAccount_ID,@AccountDocMaster_ID,1,0) bes ,0 AS joz,Code AS VCode,N'''' as RowDescription ,Title,N''حساب کل''AS typ,1 AS AccType ,2 AS SortBy FROM
acc.tbl_TotalAccounts A
INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.TotalAccount_ID=A.ID
WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID  AND ISNULL(dc.Creditor,0)>0 AND dc.ID=(SELECT TOP 1(ID) FROM acc.tbl_AccountingDocDetails WHERE AccountingDocMaster_ID = @AccountDocMaster_ID  AND ISNULL(Creditor,0)>0 AND TotalAccount_ID=A.ID )


UNION ALL

SELECT  dc.ID,dc.TotalAccount_ID,0 AS bed ,0 as Bes ,dc.Creditor joz,(B.Code+A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID IS NULL AND dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription,A.Title,N''حساب معین'' AS typ,2 AS AccType,2 AS SortBy FROM acc.tbl_AppointmentAccounts A
INNER JOIN acc.tbl_TotalAccounts AS B ON B.ID=A.TotalAccount_ID
INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.AppointmentAccount_ID=A.ID
WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND ISNULL(dc.Creditor,0)>0

UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 AS Bes ,dc.Creditor joz, (D.Code+E.Code+A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription,A.Title,N''حساب تفضیلی'' AS typ,3 AS AccType,2 AS SortBy FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	INNER JOIN acc.tbl_TotalAccounts D ON B.TotalAccount_ID = D.ID
	INNER JOIN acc.tbl_AppointmentAccounts E ON B.AppointmentAccount_ID = E.ID AND E.TotalAccount_ID = B.TotalAccount_ID
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID=A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND ISNULL(dc.Creditor,0)>0
UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Creditor joz, (A.Code) AS Vcode,CASE WHEN dc.DetailedAccount_ID2 IS NULL AND dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE '''' END as RowDescription, A.Title,N''حساب تفضیلی'' AS typ,3 AS AccType,2 AS SortBy FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND B.TotalAccount_ID IS NULL AND ISNULL(dc.Creditor,0)>0
	
UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Creditor joz, (A.Code) AS Vcode,CASE WHEN  dc.DetailedAccount_ID3 IS NULL  THEN dc.RowDescription ELSE N'''' END as RowDescription, A.Title,N''حساب تفضیلی2'' AS typ,4 AS AccType,2 AS SortBy
  FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID2=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND  B.TotalAccount_ID IS NULL  AND ISNULL(dc.Creditor,0)>0		
	
UNION ALL

SELECT dc.ID,dc.TotalAccount_ID,0 AS bed,0 as Bes ,dc.Creditor joz, (A.Code) AS Vcode, dc.RowDescription as RowDescription, A.Title,N''حساب تفضیلی3'' AS typ,5 AS AccType,2 AS SortBy
  FROM acc.tbl_DetailedAccounts A
	INNER JOIN acc.tbl_AccountingDocDetails dc ON dc.DetailedAccount_ID3=A.ID
	INNER JOIN acc.tbl_DetailedAccountsRelation B ON B.DetailedAccount_ID = A.ID
	WHERE dc.AccountingDocMaster_ID = @AccountDocMaster_ID AND  B.TotalAccount_ID IS NULL  AND ISNULL(dc.Creditor,0)>0		
	
	)T2 
)Main	
ORDER BY Main.SortBy,Main.TotalAccount_ID,ID,Main.AccType')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[Sp_ReportAccountAnalyz]
@TotalID INT,
@AppointID INT,
@Level INT,
@DocState INT
AS
BEGIN
DECLARE @StartDocSate INT
DECLARE @EndDocSate INT

IF @DocState=1
begin
	SET @StartDocSate=1
	SET @EndDocSate=3
 END 
IF @DocState=2
begin
	SET @StartDocSate=1
	SET @EndDocSate=2
 END  		
 IF @DocState=3
begin
	SET @StartDocSate=3
	SET @EndDocSate=3
 END 
	IF @Level=1
	BEGIN
		SELECT C.ID ,C.Code TotalAccount_Code ,C.Title,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		  WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		 N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND
		B.DocState BETWEEN @StartDocSate AND @EndDocSate		   
		GROUP BY C.ID,C.Code,C.Title
	END
	IF @Level=2
	BEGIN
		IF @TotalID<>0
			BEGIN
		SELECT D.ID AppointedAccount_ID,C.ID TotalAccount_ID, C.Code TotalAccount_Code ,C.Code AppointedAccount_Code , D.Title ,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		 WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND A.TotalAccount_ID=@TotalID AND B.DocState BETWEEN @StartDocSate AND @EndDocSate
		GROUP BY D.ID,C.ID,D.Code,C.Code,D.Title

			END
		ELSE
			BEGIN
		SELECT D.ID AppointedAccount_ID,C.ID TotalAccount_ID, C.Code TotalAccount_Code ,C.Code  AppointedAccount_Code , D.Title ,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		 WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		 N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND B.DocState BETWEEN @StartDocSate AND @EndDocSate
		GROUP BY D.ID,C.ID,D.Code,C.Code,D.Title
			END
	END
	IF @Level=3
	BEGIN
	IF @TotalID<>0
			BEGIN
		SELECT E.ID DetailedAccount_ID,A.AppointmentAccount_ID AppointedAccount_ID,C.ID TotalAccount_ID,C.Code TotalAccount_Code , C.Code AppointedAccount_Code , E.Code Code , E.Title ,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		 WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		 N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
		INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
		LEFT OUTER JOIN acc.tbl_DetailedAccountsRelation Rel ON Rel.DetailedAccount_ID=A.DetailedAccount_ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND A.TotalAccount_ID =@TotalID AND B.DocState BETWEEN @StartDocSate AND @EndDocSate
		GROUP BY E.ID,E.Code,E.Title,D.Code,C.Code,C.ID,Rel.TotalAccount_ID,A.AppointmentAccount_ID

	END
			
	IF @AppointID<>0
			BEGIN
		SELECT E.ID DetailedAccount_ID,A.AppointmentAccount_ID AppointedAccount_ID,C.ID TotalAccount_ID,C.Code TotalAccount_Code ,D.Code AppointedAccount_Code, E.Code Code , E.Title ,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		 WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		 N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
		INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
		LEFT OUTER JOIN acc.tbl_DetailedAccountsRelation Rel ON Rel.DetailedAccount_ID=A.DetailedAccount_ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND A.AppointmentAccount_ID =@AppointID AND B.DocState BETWEEN @StartDocSate AND @EndDocSate
		GROUP BY E.ID,E.Code,E.Title,D.Code,C.Code,C.ID,Rel.TotalAccount_ID,A.AppointmentAccount_ID

			END
		IF @AppointID = 0 AND @TotalID=0
			BEGIN
		SELECT E.ID DetailedAccount_ID,A.AppointmentAccount_ID AppointedAccount_ID,C.ID TotalAccount_ID,C.Code TotalAccount_Code ,D.Code AppointedAccount_Code, E.Code Code , E.Title ,
		SUM(ISNULL(A.Debtor, 0)) DebTor,
		SUM(ISNULL(A.Creditor, 0)) Creditor,
		CASE WHEN
		 SUM(ISNULL(A.Debtor, 0)) > SUM(ISNULL(A.Creditor, 0)) THEN
		 N''بدهکار''
		 WHEN
		 SUM(ISNULL(A.Debtor, 0)) < SUM(ISNULL(A.Creditor, 0)) THEN	
		 N''بستانکار''
		 ELSE N''بی حساب''
		END Tashkhis,
		ABS(SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
		Remain
		FROM acc.tbl_AccountingDocDetails A
		INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
		INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID
		INNER JOIN acc.tbl_AppointmentAccounts D ON A.TotalAccount_ID = D.TotalAccount_ID AND A.AppointmentAccount_ID = D.ID
		INNER JOIN acc.tbl_DetailedAccounts E ON A.DetailedAccount_ID = E.ID
		LEFT OUTER JOIN acc.tbl_DetailedAccountsRelation Rel ON Rel.DetailedAccount_ID=A.DetailedAccount_ID
		WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND B.DocState BETWEEN @StartDocSate AND @EndDocSate  
		GROUP BY E.ID,E.Code,E.Title,D.Code,C.Code,C.ID,Rel.TotalAccount_ID,A.AppointmentAccount_ID

			END	
	END
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_RestoreDoc]
@MasterID Int
		AS
DECLARE @DocCode INT
SET @DocCode=(SELECT ISNULL(DocCode,0) FROM acc.tbl_AccountingDocMaster WHERE ID=@MasterID)

IF @DocCode=0
		SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE (ISNULL(Deleted,0)<>1))
UPDATE acc.tbl_AccountingDocMaster SET DocCode = @DocCode,[Deleted] = 0,tmp = 0 WHERE ID=@MasterID
RETURN @DocCode')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_SaveAccountDocMaster]
			@ID INT,
			@DocCode INT,
			@tmp BIT,	
			@ReferenceNo INT,
			@UpdateMode BIT,
			@ModifiedBy INT,
			@Reserve BIT=0
		AS
		IF @ReferenceNo=0 --اگر سند شماره عطف نداشت برایش ایجاد میشود
		BEGIN
			 IF (SELECT COUNT(HOLDER) FROM acc.tbl_ReferenceHolder)=0 
				 BEGIN
		 			INSERT INTO  acc.tbl_ReferenceHolder(Holder) VALUES (1)
				 END
			 ELSE
		 		BEGIN
		 			UPDATE acc.tbl_ReferenceHolder SET Holder=ISNULL(Holder,0)+1
		 		END	
			SET @ReferenceNo=(SELECT  Holder FROM acc.tbl_ReferenceHolder)
		END
		IF @DocCode=0-- اگر سند شماره سند نداشت برایش ایجاد میشود
		BEGIN	
		SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE  (ISNULL(Deleted,0)<>1))
		END
		IF @UpdateMode=1
		UPDATE acc.tbl_AccountingDocMaster SET ReferenceNo=@ReferenceNo ,tmp=@tmp,DocCode=@DocCode,ModifiedBy =@ModifiedBy,ModifiedDate = dbo.MiladiToShamsi(GETDATE()) , Reserve =@Reserve WHERE ID=@ID
		ELSE
		UPDATE acc.tbl_AccountingDocMaster SET ReferenceNo=@ReferenceNo ,tmp=@tmp,DocCode=@DocCode, Reserve =@Reserve WHERE ID=@ID
		UPDATE acc.tbl_AccountingDocDetails
		SET ReferenceNo=@ReferenceNo 
		WHERE AccountingDocMaster_ID=@ID AND ISNULL(ReferenceNo,0)=0')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[Sp_SortDoc] AS
		DECLARE @SanadList TABLE (
			[Idx] INT identity(1, 1)
			,[ID] [int] NOT NULL UNIQUE CLUSTERED ([Idx])
			)
		DECLARE @d1 CHAR(10),@d2 CHAR(10)

		SELECT @d1 = startdate,@d2 = enddate FROM MMyCompany

		INSERT INTO @SanadList ([ID])
		SELECT [ID] FROM acc.tbl_AccountingDocMaster
		WHERE (DocDate BETWEEN @d1 AND @d2) AND (ISNULL(DELETED,0)<>1)AND (ISNULL(Tmp,0)<>1)
		ORDER BY DocDate,DocCode

		
		UPDATE acc.tbl_AccountingDocMaster
		SET OldDocCode =DocCode
		WHERE DocDate BETWEEN @d1 AND @d2
		
		UPDATE acc.tbl_AccountingDocMaster
		SET DocCode = (
				SELECT Idx FROM @SanadList WHERE [ID] = acc.tbl_AccountingDocMaster.[ID]
				)
		WHERE DocDate BETWEEN @d1 AND @d2')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_SubSystemAccDocs]
			@WClause NVARCHAR(4000)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT 
				B.ID, B.ReferenceNo, B.DocCode, B.DocDate, B.DocDescription, ISNULL(B.Amendment_ID, 0) Amendment_ID,
				CASE
					WHEN B.SubSystem LIKE ''''101%'''' THEN N''''حسابداری''''
					WHEN B.SubSystem LIKE ''''102%'''' THEN N''''فروش''''
					WHEN B.SubSystem LIKE ''''103%'''' THEN N''''انبار''''
					WHEN B.SubSystem LIKE ''''104%'''' THEN N''''خزانه''''
					WHEN B.SubSystem LIKE ''''105%'''' THEN N''''دارایی ثابت''''
					WHEN B.SubSystem LIKE ''''106%'''' THEN N''''حقوق و دستمزد''''
				END Amendment_Title,
				C.Title DocState_Title,
				SUM(ISNULL(A.Debtor, 0)) TotalDebtor,
				SUM(ISNULL(A.Creditor, 0)) TotalCreditor,
				B.DocState
			FROM acc.tbl_AccountingDocDetails A
				LEFT JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
				LEFT JOIN acc.tbl_AccountingDocStatus C ON B.DocState = C.ID
			GROUP BY 
				B.ID, B.ReferenceNo, B.DocCode, B.DocDate, B.DocDescription, B.Amendment_ID,
				B.SubSystem, C.Title, B.DocState
			WHERE (1 = 1) '' + CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE '' AND '' + @WClause END +
			''ORDER BY B.ID, B.RefrenceNo, B.DocDate''
			EXEC sp_executesql @Query
		END
			')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TotalAccountBalance4Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
			
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
				SELECT
					C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
					0 FDebtor,0 FCreditor,
					SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
					0 SDebtor,0 SCreditor,
					CASE
						WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
						ELSE 0
					END Debtor_Remain,
					CASE	
						WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
						ELSE 0
					END Creditor_Remain
				FROM acc.tbl_AccountingDocDetails A
					INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
					INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID'' +
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )  GROUP BY C.ID, C.Code, C.Title) T''+
				'' WHERE (1=1)''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END 
				
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TotalAccountBalance6Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title,
				acc.FirstRemainAcc(C.ID,''''''+@DateStart+'''''',1,1) AS  FDebtor,acc.FirstRemainAcc(C.ID,+''''''+@DateStart+'''''',1,0) FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				0 SDebtor,0 SCreditor,
				CASE
					WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
					INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID'' +
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )  GROUP BY C.ID, C.Code, C.Title) T''+
		'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TotalAccountBalance8Col]
			@WClause NVARCHAR(4000),
			@DateStart NVARCHAR(10),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM(
			SELECT
				C.ID TotalAccount_ID, C.Code TotalAccount_Code , C.Title TotalAccount_Title,
				acc.FirstRemainAcc(C.ID,''''''+@DateStart+'''''',1,1) AS  FDebtor,acc.FirstRemainAcc(C.ID,+''''''+@DateStart+'''''',1,0) FCreditor,
				SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) DebtorFlow, SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) CreditorFlow,
				acc.FirstRemainAcc(C.ID,''''''+@DateStart+'''''',1,1)+SUM(CASE WHEN ''+@DocDate+'' THEN ISNULL(A.Debtor, 0) ELSE 0 END) SDebtor,
				acc.FirstRemainAcc(C.ID,+''''''+@DateStart+'''''',1,0)+SUM(CASE WHEN ''+@DocDate+'' THEN  ISNULL(A.Creditor, 0) ELSE 0 END) SCreditor,
				CASE
					WHEN (SUM (ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
					ELSE 0
				END Debtor_Remain,
				CASE	
					WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
					ELSE 0
				END Creditor_Remain
			FROM acc.tbl_AccountingDocDetails A
				INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
					INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID'' +
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 )  GROUP BY C.ID, C.Code, C.Title) T''+
		'' WHERE (1=1) ''+ CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE  @WClause END 
	
			EXEC sp_executesql @Query
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TotalAccountsBalance2Col]
			@WClause NVARCHAR(4000),
			@DocDate NVARCHAR(52)
		AS
		BEGIN
			DECLARE @Query NVARCHAR(4000) = N''
			SELECT * FROM (
				SELECT 
					C.ID TotalAccount_ID, C.Code TotalAccount_Code, C.Title TotalAccount_Title, 
					0 FDebtor,0 FCreditor
					,0 DebtorFlow,0 CreditorFlow
					,0 SDebtor,0 SCreditor,
					CASE
						WHEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0))) > 0 THEN (SUM(ISNULL(A.Debtor, 0)) - SUM(ISNULL(A.Creditor, 0)))
						ELSE 0
					END Debtor_Remain,
					CASE
						WHEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0))) > 0 THEN (SUM(ISNULL(A.Creditor, 0)) - SUM(ISNULL(A.Debtor, 0)))
						ELSE 0
					END Creditor_Remain
				FROM acc.tbl_AccountingDocDetails A
					INNER JOIN acc.tbl_AccountingDocMaster B ON A.AccountingDocMaster_ID = B.ID
					INNER JOIN acc.tbl_TotalAccounts C ON A.TotalAccount_ID = C.ID'' +
				'' WHERE (ISNULL(Deleted,0)=0 AND ISNULL(tmp,0)=0 ) AND '' +@DocDate+'' GROUP BY C.ID, C.Title, C.Code)T 
			WHERE (1=1)''+CASE WHEN ISNULL(@WClause, '''') = '''' THEN '''' ELSE @WClause END
			EXEC sp_executesql @Query
			
		END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[Sp_TreeView]
AS
BEGIN
SELECT * FROM
( SELECT Code,Title,0 AS parentID,1 AS Level FROM acc.tbl_AccountGroups
	UNION ALL
SELECT Code,Title, AccountGroup_ID AS parentID,2 AS Level FROM acc.tbl_TotalAccounts
	UNION ALL
SELECT Code,Title, TotalAccount_ID AS parentID,3 AS Level FROM acc.tbl_AppointmentAccounts
	UNION ALL
SELECT DA.Code,DA.Title, acc.tbl_DetailedAccountsRelation.AppointmentAccount_ID AS parentID,acc.tbl_DetailedAccountGroups.[Level] +3 AS Level FROM acc.tbl_DetailedAccounts as DA
INNER JOIN acc.tbl_DetailedAccountsRelation ON acc.tbl_DetailedAccountsRelation.DetailedAccount_ID=DA.ID
INNER JOIN acc.tbl_DetailedAccountGroups ON acc.tbl_DetailedAccountGroups.ID=acc.tbl_DetailedAccountsRelation.DetailedAccountGroup_ID
) tbl
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TruncateAccTables]
AS
BEGIN
Truncate Table acc.tbl_AccountGroups
Truncate Table acc.tbl_TotalAccounts
Truncate Table acc.tbl_AppointmentAccounts
Truncate Table acc.tbl_DetailedAccounts
Truncate Table acc.tbl_DetailedAccountsRelation
Truncate Table acc.tbl_DetailedAccountGroups
Truncate Table acc.tbl_DetailedAccGroupToAppointmentRel
Truncate Table acc.tbl_AccountingDocMaster
Truncate Table acc.tbl_AccountingDocDetails
Truncate TABLE tbl_BalanceProfitMaster
Truncate TABLE tbl_BalanceProfitDetails
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO


IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_TruncateCoding]
AS
BEGIN
	IF (SELECT COUNT(*) FROM acc.tbl_AccountingDocMaster)>0
	BEGIN
		RAISERROR(N''از حساب های تعریف شده در اسناد حسابداری استفاده شده و حذف حساب ها امکان پذیر نمیباشد'', 16, 1) 
		RETURN
	END

	IF (SELECT COUNT(*) FROM acc.tbl_DetailedAccountsRelation WHERE ISNULL(TotalAccount_ID,0)<>0 )>0
	BEGIN
		RAISERROR(N''از حساب های تعریف شده در معرفی حساب های تفصیلی استفاده شده و حذف حساب ها امکان پذیر نمیباشد'', 16, 1) 
		RETURN
	END
	
IF (SELECT COUNT(*) FROM acc.tbl_DetailedAccGroupToAppointmentRel WHERE ISNULL(Appointment_ID,0)<>0 )>0
	BEGIN
		RAISERROR(N''از حساب های تعریف شده در معرفی حساب های تفصیلی استفاده شده و حذف حساب ها امکان پذیر نمیباشد'', 16, 1) 
		RETURN
	END
	
IF (SELECT COUNT(*) FROM acc.tbl_BalanceProfitDetails WHERE ISNULL(TotalID,0)<>0 )>0
	BEGIN
		RAISERROR(N''از حساب کل های تعریف شده در صورت سود و زیان حسابداری استفاده شده است و حذف حساب ها امکان پذیر نمی باشد'', 16, 1) 
		RETURN
	END
	
	 TRUNCATE TABLE acc.tbl_AccountGroups
	 TRUNCATE TABLE acc.tbl_TotalAccounts
	 TRUNCATE TABLE acc.tbl_AppointmentAccounts
	 TRUNCATE TABLE acc.tbl_DetailedAccountsRelation
END')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('CREATE PROCEDURE [acc].[sp_AddAccountDocMaster]
			@ReferenceNo INT,
			@DocCode INT, 
			@DocDate NVARCHAR(10), 
			@DocType SMALLINT, 
			@DocDescription NVARCHAR(MAX), 
			@CreateDate NVARCHAR(10), 
			@CreateBy NVARCHAR(1500),
			@SubSystem INT, 
			@tmp BIT= 1	
		AS
		IF @DocCode=0
		SET @DocCode=(SELECT ISNULL(MAX(DocCode),0)+1 FROM acc.tbl_AccountingDocMaster WHERE (ISNULL(Deleted,0)<>1) AND (ISNULL(tmp,0)<>1))
		IF (@DocType=1 AND @DocCode=1)
			SET @DocCode=2
		INSERT INTO acc.tbl_AccountingDocMaster (ReferenceNo, DocCode, DocDate, DocType, DocDescription, CreateDate, CreateBy,tmp,DocState,SubSystem)
		VALUES (@ReferenceNo, @DocCode, @DocDate, @DocType, @DocDescription, @CreateDate, @CreateBy,ISNULL(@tmp,1),1,@SubSystem)
		RETURN  scope_Identity()')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100101, N''تراز آزمایشی کل - 2 ستونی'', N''EXEC acc.sp_TotalAccountsBalance2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,مانده_بدهکار,مانده_بستانکار'', N''rep_Total2CBalance.fr3'', 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100102, N''تراز آزمایشی کل - 4 ستونی'', N''EXEC acc.sp_TotalAccountBalance4Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,DebtorFlow,CreditorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,گردش_بدهکار,گردش_بستانکار,مانده_بدهکار,مانده_بستانکار'', N''rep_Total4CBalance.fr3'', 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100103, N''تراز آزمایشی معین - 2 ستونی'', N''EXEC acc.sp_AppointedAccountBalanceSummary2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,مانده_بدهکار,مانده_بستانکار'', N''rep_Appointment2CBalance.fr3'', 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100104, N''تراز آزمایشی معین - 4 ستونی'', N''EXEC acc.sp_AppointedAccountBalance4Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DebtorFlow,CreditorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,گردش_بدهکار,گردش_بستانکار,مانده_بدهکار,مانده_بستانکار'', N''rep_Appointment4CBalance.fr3'', 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100105, N''تراز آزمایشی خلاصه - معین'', N''EXEC acc.sp_AppointedAccountBalanceSummary2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Title,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,مانده_بدهکار,مانده_بستانکار'', N''rep_AppointmentSummaryBalance.fr3'', 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100107, N''تراز آزمایشی تفصیلی - 2 ستونی'', N''EXEC acc.sp_DetailedAccountBalanceSummary2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی,عنوان_تفصیلی,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100108, N''تراز آزمایشی تفصیلی - 4 ستونی'', N''EXEC acc.sp_DetailedAccountBalance4Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی,عنوان_تفصیلی,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100109, N''تراز آزمایشی کل - 6 ستونی'', N''EXEC acc.sp_TotalAccountBalance6Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100110, N''تراز آزمایشی معین - 6 ستونی'', N''EXEC acc.sp_AppointedAccountBalance6Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100111, N''تراز آزمایشی تفصیلی- 6 ستونی'', N''EXEC acc.sp_DetailedAccountBalance6Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,CreditorFlow,DebtorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی,عنوان_تفصیلی,بدهکار از قبل,بستانکار از قبل,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, NULL)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100112, N''تراز آزمایشی تفصیلی 2 - 2 ستونی'', N''EXEC acc.sp_Detailed2AccountBalanceSummary2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 2,عنوان_تفصیلی 2,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100113, N''تراز آزمایشی تفصیلی 2 - 4 ستونی'', N''EXEC acc.sp_Detailed2AccountBalance4Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 2,عنوان_تفصیلی 2,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100114, N''تراز آزمایشی تفصیلی 2- 6 ستونی'', N''EXEC acc.sp_Detailed2AccountBalance6Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 2,عنوان_تفصیلی 2,بدهکار از قبل,بستانکار از قبل,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100115, N''تراز آزمایشی تفصیلی 3 - 2 ستونی'', N''EXEC acc.sp_Detailed3AccountBalanceSummary2Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 3,عنوان_تفصیلی 3,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100116, N''تراز آزمایشی تفصیلی 3 - 4 ستونی'', N''EXEC acc.sp_Detailed3AccountBalance4Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 3,عنوان_تفصیلی 3,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100117, N''تراز آزمایشی تفصیلی 3- 6 ستونی'', N''EXEC acc.sp_Detailed2AccountBalance6Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_ID,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_ID,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,CreditorFlow,DebtorFlow,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,کد_تفصیلی 3,عنوان_تفصیلی 3,بدهکار از قبل,بستانکار از قبل,گردش بدهکار,گردش بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100118, N''تراز آزمایشی کل - 8 ستونی'', N''EXEC acc.sp_TotalAccountBalance8Col'', N''TotalAccount_ID,TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,جمع بدهکار,جمع بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100119, N''تراز آزمایشی معین - 8 ستونی'', N''EXEC acc.sp_AppointedAccountBalance8Col'', N''AppointedAccount_ID,TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,TotalAccount_Title,AppointedAccount_Code,AppointedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''کد_کل,عنوان_کل,کد_معین,عنوان_معین,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,جمع بدهکار,جمع بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100120, N''تراز آزمایشی تفصیلی- 8 ستونی'', N''EXEC acc.sp_DetailedAccountBalance8Col'', N''DetailedAccount_ID,TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''کد_کل,کد_معین,کد_تفصیلی,عنوان_تفصیلی,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,جمع بدهکار,جمع بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100121, N''تراز آزمایشی تفصیلی 2- 8 ستونی'', N''EXEC acc.sp_DetailedAccount2Balance8Col'', N''DetailedAccount_ID,TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''کد_کل,کد_معین,کد_تفصیلی2,عنوان_تفصیلی2,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,جمع بدهکار,جمع بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [acc].[tbl_Reports] ([ID], [Title], [Query], [Fields], [VisibleFields], [FieldsTitle], [ReportFile], [QueryType]) VALUES (100122, N''تراز آزمایشی تفصیلی 3- 8 ستونی'', N''EXEC acc.sp_DetailedAccount3Balance8Col'', N''DetailedAccount_ID,TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''TotalAccount_Code,AppointedAccount_Code,DetailedAccount_Code,DetailedAccount_Title,FDebtor,FCreditor,DebtorFlow,CreditorFlow,SDebtor,SCreditor,Debtor_Remain,Creditor_Remain'', N''کد_کل,کد_معین,کد_تفصیلی3,عنوان_تفصیلی3,بدهکار از قبل,بستانکار از قبل,گردش_بدهکار,گردش_بستانکار,جمع بدهکار,جمع بستانکار,مانده_بدهکار,مانده_بستانکار'', NULL, 3)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (537, N''تنظیمات  حساب ها'', 1, 1, 1, 1028)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (537, N''ثبت'', 1, 2, 2, 1029)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''گروه های تفصیلی'', 1, 1, 1, 1030)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''جدید'', 1, 2, 2, 1031)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''ویرایش'', 1, 3, 2, 1032)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''حذف'', 1, 4, 2, 1033)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''انتساب به معین'', 1, 5, 2, 1034)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''حساب های تفصیلی'', 1, 1, 1, 1036)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''جدید'', 1, 2, 2, 1037)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''ویرایش'', 1, 3, 2, 1038)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''حذف'', 1, 4, 2, 1039)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''چاپ'', 1, 5, 2, 1040)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (540, N''کدهای حسابداری'', 1, 1, 1, 1041)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (540, N''جدید'', 1, 2, 2, 1042)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (540, N''ویرایش'', 1, 3, 2, 1043)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (540, N''حذف'', 1, 4, 2, 1044)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (540, N''چاپ'', 1, 5, 2, 1045)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''جدید'', 1, 2, 2, 1047)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''ویرایش'', 1, 3, 2, 1048)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''حذف'', 1, 5, 2, 1049)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''چاپ'', 1, 6, 2, 1050)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''امکانات'', 1, 7, 2, 1051)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''تهیه گزارش'', 1, 8, 2, 1052)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''مرتب سازی اسناد'', 1, 9, 3, 1053)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''خروجی اکسل'', 1, 10, 3, 1054)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''کپی سند'', 1, 11, 3, 1055)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''ادغام اسناد'', 1, 12, 3, 1056)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''تایید اسناد'', 1, 13, 3, 1057)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''دائمی کردن اسناد'', 1, 14, 3, 1058)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''تبدیل اسناد به موقت'', 1, 15, 3, 1059)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''سند حسابداری'', 1, 1, 1, 1060)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''ردیف جدید'', 1, 2, 2, 1061)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''حذف ردیف'', 1, 3, 2, 1062)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''کپی ردیف'', 1, 5, 2, 1063)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''ویرایش ردیف'', 1, 4, 2, 1064)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''خواندن ردیف'', 1, 6, 2, 1065)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''جدید'', 1, 7, 2, 1066)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''تایید سند'', 1, 8, 2, 1067)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''حذف'', 1, 9, 2, 1068)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''چاپ'', 1, 10, 2, 1069)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''ضمائم جدید'', 1, 11, 2, 1070)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''ویرایش ضمائم'', 1, 12, 2, 1071)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''حذف ضمائم'', 1, 13, 2, 1072)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''نمایش فایل'', 1, 14, 2, 1073)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (542, N''نمایش تصویر'', 1, 15, 2, 1074)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (543, N''سند افتتاحیه'', 1, 1, 1, 1075)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (544, N''سند اختتامیه'', 1, 1, 1, 1076)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''صورت سود و زیان'', 1, 1, 1, 1077)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (546, N''ترازهای آزمایشی'', 1, 1, 1, 1078)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (546, N''چاپ'', 1, 3, 2, 1080)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (547, N''دفتر روزنامه'', 1, 1, 1, 1081)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (547, N''نمایش سند'', 1, 3, 2, 1083)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (547, N''چاپ'', 1, 4, 2, 1084)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (539, N''خروجی اکسل'', 1, 6, 2, 1085)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (546, N''خروجی اکسل'', 1, 4, 2, 1086)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (547, N''خروجی اکسل'', 1, 5, 2, 1087)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (548, N''سند بستن حساب های موقت'', 1, 1, 1, 1088)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (549, N''آنالیز حساب ها'', 1, 1, 1, 1089)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (549, N''چاپ'', 1, 2, 2, 1090)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (549, N''خروجی اکسل'', 1, 3, 2, 1091)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (536, N''حسابداری'', 1, 1, 1, 1092)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''اسناد حسابداری'', 1, 1, 1, 1093)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (550, N''گزارش گردش حساب'', 1, 1, 1, 1094)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''انتساب به معین جدید'', 1, 6, 2, 1095)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (538, N''حذف  انتساب به معین'', 1, 7, 2, 1096)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (551, N''ترازنامه'', 1, 1, 1, 1097)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (551, N''چاپ'', 1, 2, 2, 1098)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (551, N''خروجی اکسل'', 1, 3, 2, 1099)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''جدید'', 1, 2, 2, 1100)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''ویرایش'', 1, 3, 2, 1101)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''حذف'', 1, 4, 2, 1102)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''چاپ'', 1, 5, 2, 1103)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (545, N''خروجی اکسل'', 1, 6, 2, 1104)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (550, N''چاپ'', 1, 2, 2, 1105)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (550, N''خروجی اکسل'', 1, 3, 2, 1106)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (552, N''شرح سند حسابداری'', 1, 1, 1, 1107)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (552, N''جدید'', 1, 2, 2, 1108)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (552, N''ویرایش'', 1, 3, 2, 1109)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (552, N''حذف'', 1, 4, 2, 1110)
INSERT [dbo].[FormButtons] ([IDForm], [Name], [Active], [ButtonOrder], [Type], [ID]) VALUES (541, N''بازبینی'', 1, 4, 3, 1111)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''حسابداری'', 0, 5, 536)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''تنظیمات  حساب ها'', 536, 1, 537)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''گروه های تفصیلی'', 536, 2, 538)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''حساب های تفصیلی'', 536, 3, 539)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''کدهای حسابداری'', 536, 4, 540)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''اسناد حسابداری'', 536, 5, 541)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''سند حسابداری'', 536, 6, 542)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''سند افتتاحیه'', 536, 7, 543)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''سند اختتامیه'', 536, 9, 544)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''صورت سود و زیان'', 536, 11, 545)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''ترازهای آزمایشی'', 536, 12, 546)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''دفتر روزنامه'', 536, 13, 547)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''سند بستن حساب های موقت'', 536, 8, 548)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''آنالیز حساب ها'', 536, 14, 549)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''گزارش گردش حساب'', 536, 10, 550)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''ترازنامه'', 536, 15, 551)
INSERT [dbo].[Forms] ([Name], [ParentId], [MenuOrder], [ID]) VALUES (N''شرح ردیف سند'', 542, 1, 552)')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1 EXEC('SET IDENTITY_INSERT [acc].[tbl_AccountingDocStatus] ON 

INSERT [acc].[tbl_AccountingDocStatus] ([ID], [Title]) VALUES (1, N''موقت'')
INSERT [acc].[tbl_AccountingDocStatus] ([ID], [Title]) VALUES (2, N''تایید'')
INSERT [acc].[tbl_AccountingDocStatus] ([ID], [Title]) VALUES (3, N''دائم'')
SET IDENTITY_INSERT [acc].[tbl_AccountingDocStatus] OFF')
GO
IF @@ERROR <> 0 IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'Successfull'
   COMMIT TRANSACTION
END ELSE PRINT 'Failed'