--CreateProjectDB.sql--------------------------------------------------------------------------
use master
create database [DBKeyword_ProjectDbName] collate Chinese_PRC_CI_AS;
go

USE [DBKeyword_ProjectDbName]
GO

--****************************start repalce holder part*************************
--start v1.0.0.0--------------------------------------------------------------------------

IF NOT EXISTS (select 1 from sys.tables where name = 'VoucherMapping' and type = 'U')
BEGIN
create table dbo.VoucherMapping
(	
	ID VARCHAR(128) NOT NULL,
	[TranCode] [nvarchar](50) NOT NULL,
	[VID] [nvarchar](50) NOT NULL,
	[Period] [int] NOT NULL,
	[Group] [nvarchar](50) NULL,
	[ItemID] [nvarchar](50) NULL,
	[CreatorID] VARCHAR(128) NULL,
	[CreateTime] [datetime] NOT NULL 
	PRIMARY KEY(ID)
);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'InvoiceMapping' and type = 'U')
BEGIN
create table dbo.InvoiceMapping
(	
	ID VARCHAR(128) NOT NULL,
	[TranCode] [nvarchar](50) NOT NULL,	
	[InvoiceCode] [nvarchar](50) NOT NULL,
	[InvoiceNumber] [nvarchar](50) NOT NULL,
	[Period] [int] NOT NULL,
	[CreatorID] VARCHAR(128) NULL,
	[CreateTime] [datetime] NOT NULL
	PRIMARY KEY(ID)
);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'EnterpriseAccount' and type = 'U')
BEGIN
CREATE TABLE dbo.EnterpriseAccount(
	ID varchar(128) NOT NULL DEFAULT (CONVERT([varchar](128),newid())),
	[AcctCode] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](200) NOT NULL,
	[ParentCode] [nvarchar](50) NULL,
	[FullName] [nvarchar](200) NULL,
	[AcctProp] [int] NULL,
	[SubProp] [int] NULL,
	[AcctLevel] [int] NULL,
	[Direction] [int] NOT NULL,
	[IsLeaf] [smallint] NULL,
	[IsActive] [smallint] NOT NULL DEFAULT 1,
	[EnglishName] [nvarchar](200) NULL,
	[StdCode] [nvarchar](50) NULL,
	[EnterpriseAccountSetID] [varchar](128) NOT NULL,
	[IsDummy] [int] NULL,
	[IsInVoucher] [int] NULL,
	[CreatorID] [VARCHAR](128) NOT NULL,
	[UpdatorID] [VARCHAR](128) NOT NULL,
	[CreateTime] [DATETIME] NOT NULL,
	[UpdateTime] [DATETIME] NOT NULL,
	 PRIMARY KEY(ID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'ImportFile' and type = 'U')
BEGIN
CREATE TABLE dbo.ImportFile(
	FileID varchar(128) NOT NULL,
	[FilePath] [nvarchar](500) NOT NULL,
	[FileName] [nvarchar](500) NULL,
	[PeriodID] [int] NULL,
	[FileType] [varchar](50) NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[FileImportType] [int] NULL,
	[Comments] [nvarchar](max) NULL,
	 PRIMARY KEY(FileID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'OutputVATInvoice' and type = 'U')
BEGIN
CREATE TABLE dbo.OutputVATInvoice(
	InvoiceID varchar(128) NOT NULL,
	[InvoiceType] [int] NOT NULL,
	[ClassCode] [nvarchar](50) NOT NULL,
	[InvoiceNumber] [nvarchar](50) NOT NULL,
	[BuyerName] [nvarchar](200) NULL,
	[BuyerTaxNumber] [nvarchar](200) NULL,
	[BankAccount] [nvarchar](200) NULL,
	[PhoneNum] [nvarchar](200) NULL,
	[InvoiceDate] [datetime] NOT NULL,
	[SeqNo] [int] NOT NULL,
	[PeriodID] [int] NOT NULL,
	[Status] [int] NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[PartAccntedPeriod] [int] NULL,
	[AccntedPeriod] [int] NULL,
	 PRIMARY KEY(InvoiceID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'OutputVATInvoiceItem' and type = 'U')
BEGIN
CREATE TABLE dbo.OutputVATInvoiceItem(
	ItemID varchar(128) NOT NULL,
	[InvoiceID] [varchar](128) NOT NULL,
	[CodeVersion] [nvarchar](50) NULL,
	[ProductName] [nvarchar](200) NULL,
	[DocumentNum] [nvarchar](100) NULL,
	[ProductStandard] [nvarchar](100) NULL,
	[Unit] [nvarchar](100) NULL,
	[Quantity] [int] NULL,
	[UnitPrice] [float] NULL,
	[Amount] [decimal](18, 4) NOT NULL,
	[TaxRate] [decimal](18, 4) NOT NULL,
	[TaxAmount] [decimal](18, 4) NOT NULL,
	[SeqNo] [int] NOT NULL,
	[TaxClassCode] [nvarchar](100) NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[PeriodID] [int] NOT NULL,
	 PRIMARY KEY(ItemID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'InputVATInvoice' and type = 'U')
BEGIN
CREATE TABLE dbo.InputVATInvoice(
	[ID] [varchar](128) NOT NULL,
	[PeriodID] [int] NOT NULL,
	[InvoiceCode] [nvarchar](50) NOT NULL,
	[InvoiceNumber] [nvarchar](50) NOT NULL,
	[InvoiceDate] [datetime] NOT NULL,
	[SellerTaxNumber] [nvarchar](50) NOT NULL,
	[Amount] [decimal](18, 4) NULL,
	[TaxAmount] [decimal](18, 4) NULL,
	[InvoiceType] [int] NOT NULL,
	[CertificationResult] [nvarchar](50) NULL,
	[CertificationDate] [datetime] NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Status] [int] NULL,
	[PartAccntedPeriod] [int] NULL,
	[AccntedPeriod] [int] NULL,
	 PRIMARY KEY(ID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'InputVATInvoiceItem' and type = 'U')
BEGIN
CREATE TABLE dbo.InputVATInvoiceItem(
	[ID] [varchar](128) NOT NULL,
	[InputVATInvoiceID] [varchar](128) NOT NULL,
	[PeriodID] [int] NOT NULL,
	[InvoiceCode] [nvarchar](50) NOT NULL,
	[InvoiceNumber] [nvarchar](50) NOT NULL,
	[Amount] [decimal](18, 4) NULL,
	[TaxAmount] [decimal](18, 4) NULL,
	[TaxRate] [decimal](18, 4) NULL,
	[ProductionName] [nvarchar](100) NOT NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[ProductionCode] [nvarchar](50) NULL,
	 PRIMARY KEY(ID)
	);
END


IF NOT EXISTS (select 1 from sys.tables where name = 'GoodsMapping' and type = 'U')
BEGIN
CREATE TABLE dbo.GoodsMapping(
	ID [varchar](128) NOT NULL,
	[CustGoodsName] [nvarchar](50) NULL,
	[StdGoodsName] [nvarchar](50) NULL,
	[CreatorID] [varchar](128) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	 PRIMARY KEY(ID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'OperationLog' and type = 'U')
BEGIN
CREATE TABLE dbo.OperationLog(
	[ID] [varchar](128) NOT NULL,
	[OperationName] [nvarchar](200) NOT NULL,
	[ModuleID] [int] NOT NULL,
	[OperationObject] [nvarchar](max) NULL,
	[OperationType] [int] NOT NULL,
	[OperationContent] [nvarchar](max) NULL,
	[OriginalState] [nvarchar](max) NULL,
	[UpdateState] [nvarchar](max) NULL,
	[CreatorID] [varchar](128) NOT NULL,
	[Comment] [nvarchar](max) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[IP] [varchar](100) NULL,
	[Period] [int] NULL,
	[ServiceType] [int] NULL,
	 PRIMARY KEY(ID)
	);
END


IF NOT EXISTS (select 1 from sys.tables where name = 'ValidationInfo' and type = 'U')
BEGIN
	CREATE TABLE dbo.ValidationInfo(
	ID varchar(128) NOT NULL ,
	[ImportTypeId] [int] NOT NULL,
	[ValidationResult] [nvarchar](max) NULL,
	[ValidationDetails] [nvarchar](max) NULL,
	[ValidationTips] [nvarchar](max) NULL,
	[Number] [int] NULL,
	[CreatorId] [nvarchar](50) NULL,
	[CreateTime] [datetime] NULL,
	[PeriodId] [int] NULL,
	[ErpCheckTypeId] [int] NULL,
	[ErrorLevel] [int] NULL,
	[IsShowDetail] [smallint] NULL, 
	PRIMARY KEY(ID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'StandardAccount' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[StandardAccount](
	[ID] [varchar](128) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ParentCode] [nvarchar](50) NULL,
	[FullName] [nvarchar](200) NULL,
	[AcctProp] [int] NULL,
	[SubProp] [int] NULL,
	[AcctLevel] [int] NULL,
	[Direction] [int] NOT NULL,
	[IsLeaf] [smallint] NOT NULL,
	[RuleType] [int] NOT NULL,
	[IsActive] [smallint] NOT NULL,
	[EnglishName] [nvarchar](200) NULL,
	[IndustryID] [varchar](128) NOT NULL, 
	PRIMARY KEY(ID)
	);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'BalanceStd' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[BalanceStd](
	[BalanceId] [varchar](128) NOT NULL  DEFAULT (CONVERT([varchar](128),newid())),
	[PeriodId] [int] NULL,
	[AcctCode] [nvarchar](50) NULL,
	[BegDebitBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[BegCreditBal] [numeric](18, 3) NULL  DEFAULT ((0)),
	[BegBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[EndBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[EndDebitBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[EndCreditBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[DebitBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[CreditBal] [numeric](18, 3) NULL DEFAULT ((0)),
	[YearDebitBal] [numeric](18, 3) NULL,
	[YearCreditBal] [numeric](18, 3) NULL,
	[CarryoverDebit] [numeric](18, 3) NULL,
	[CarryoverCredit] [numeric](18, 3) NULL,
	[YearCarryoverDebit] [numeric](18, 3) NULL,
	[YearCarryoverCredit] [numeric](18, 3) NULL,
	[DebitNet] [numeric](18, 3) NULL,
	[CreditNet] [numeric](18, 3) NULL,
	[YearDebitNet] [numeric](18, 3) NULL,
	[YearCreditNet] [numeric](18, 3) NULL,
	[CreateTime] [datetime] NULL DEFAULT (getdate()),
	[UpdateTime] [datetime] NULL DEFAULT (getdate()),
	[SubProp] INT NULL,
	PRIMARY KEY(BalanceId)
	);
END

-- report begin ----------

IF NOT EXISTS (select 1 from sys.tables where name = 'CellData' and type = 'U')
BEGIN
    CREATE TABLE [dbo].[CellData](
    [ID] [varchar](128) NOT NULL,
	[ReportID] [varchar](128) NOT NULL,
	[CellTemplateID] [varchar](128) NOT NULL,
	[Data] [nvarchar](200) NULL,
	[FormulaExp] [nvarchar](max) NULL,
	[Creator] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Updater] [varchar](128) NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
    PRIMARY KEY(ID)
    );
END
IF NOT EXISTS (select 1 from sys.tables where name = 'CellDataSource' and type = 'U')
BEGIN
    CREATE TABLE [dbo].[CellDataSource](
	[ID] [varchar](128) NOT NULL,
	[CellTemplateID] [varchar](128) NOT NULL,
	[CellDataID] [varchar](128) NOT NULL,
	[DataSourceID] [varchar](128) NOT NULL,
	[OperationType] [int] NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	PRIMARY KEY(ID)
    );
END
IF NOT EXISTS (select 1 from sys.tables where name = 'DataSource' and type = 'U')
BEGIN
    CREATE TABLE [dbo].[DataSource](
	[ID] [varchar](128) NOT NULL,
	[Type] [int] NOT NULL,
	[Name] [nvarchar](500) NULL,
	[Amount] [decimal](20, 4) NULL,
	[Description] [nvarchar](max) NULL,	
	[Creator] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Updater] [varchar](128) NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[KeyValueDataID] [varchar](128) NULL,
	[RowName] [nvarchar](2000) NULL,
	[ColumnName] [nvarchar](2000) NULL,
	[RowIndex] int NULL,
	[ColumnIndex] int NULL,
	PRIMARY KEY(ID)
    );
END
IF NOT EXISTS (select 1 from sys.tables where name = 'DataSourceDetail' and type = 'U')
BEGIN
    CREATE TABLE [dbo].[DataSourceDetail](
	[ID] [varchar](128) NOT NULL,
	[DataSourceID] [varchar](128) NOT NULL,
	[DataSourceType] [int] NOT NULL,
	[ItemValue] [varchar](128) NOT NULL,
	PRIMARY KEY(ID)
);
END
IF NOT EXISTS (select 1 from sys.tables where name = 'Report' and type = 'U')
BEGIN
    CREATE TABLE [dbo].[Report](
	[ID] [varchar](128) NOT NULL,
	[TemplateID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[ProjectID] [varchar](128) NULL,
	[Creator] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Updater] [varchar](128) NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	PRIMARY KEY(ID)
);
END
IF NOT EXISTS (select 1 from sys.tables where name = 'FormulaCache' and type = 'U')
BEGIN
CREATE TABLE [dbo].[FormulaCache](
	[ID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
    [Year] [int] NOT NULL,
	[Formula] [nvarchar](2000) NOT NULL,
	[FormulaValue] [nvarchar](200) NULL,
	[TargetReportTemplateID] [varchar](128) NULL,
	[TargetRowIndex] [int] NULL,
	[TargetRowName] [nvarchar](200) NULL,
	[TargetColumnIndex] [int] NULL,
	[TargetColumnName] [nvarchar](200) NULL,
	[TargetPeriod] [int] NULL,
	[TargetCellDataID] [varchar](128) NULL,
	[TargetCellTemplateID] [varchar](128) NULL,
	[ResultType] [int] NULL,
	[CreateTime] [datetime] NOT NULL,
    PRIMARY KEY(ID,Period)
);
END

--update 2017-6-29


IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellTemplate' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodCellTemplate](
	[ID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[ReportTemplateID] [varchar](128) NOT NULL,
	[RowIndex] [int] NOT NULL,
	[RowName] [nvarchar](2000) NULL,
	[ColumnIndex] [int] NOT NULL,
	[ColumnName] [nvarchar](2000) NULL,
	[Comment] [nvarchar](2000) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[CopyFromID] [varchar](128) NULL,
	[DataType] [int] NULL,
	[IsReadOnly] [int] NULL,
    PRIMARY KEY(ID,Period)
);
END


IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellTemplateConfig' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodCellTemplateConfig](
	[ID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[CellTemplateID] [varchar](128) NOT NULL,
	[ReportTemplateID] [varchar](128) NULL,
	[DataSourceType] [int] NOT NULL,
	[Formula] [nvarchar](4000) NULL,
	[ParsedFormula] [nvarchar](4000) NULL,
	[FormulaDescription] [nvarchar](2000) NULL,
	[AccountCodes] [nvarchar](2000) NULL,
	[InvoiceType] [int] NULL,
	[TaxRate] [varchar](50) NULL,
	[InvoiceAmountType] [int] NULL,
	[ModelIDs] [varchar](4000) NULL,
	[Creator] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Updater] [varchar](128) NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
	[InvoiceCategory] [varchar](50) NULL,
	[FormulaDataSource] [nvarchar](500) NULL,
	[Validation] [nvarchar](4000) NULL,
	[ParsedValidation] [nvarchar](4000) NULL,
	[ValidationDescription] [nvarchar](2000) NULL,
	[VoucherKeyword] [nvarchar](500) NULL,
    PRIMARY KEY(ID,Period)
);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodFormulaBlock' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodFormulaBlock](
	[ID] [nvarchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[FormulaBlock] [nvarchar](50) NOT NULL,
	[Index] [int] NOT NULL,
	[Data] [nvarchar](200) NULL,
	[ResultType] [int] NULL,
	[DataSourceInfo] [nvarchar](max) NULL,
	[IsCalculated] [smallint] NULL,
	[Creator] [varchar](128) NULL,
	[CreateTime] [datetime] NULL,
	[Updater] [varchar](128) NULL,
	[UpdateTime] [datetime] NULL,
    PRIMARY KEY(ID,Period)
);
END


IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxPayerReportRule' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodTaxPayerReportRule](
	[ID] [varchar](128) NOT NULL,
	[IsDefault] [int] NOT NULL,
	[Period] [int] NOT NULL,
	[TaxPayerType] [int] NOT NULL,
	[TemplateGroupID] [varchar](128) NOT NULL,
	[OrganizationID] [varchar](max) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
    PRIMARY KEY(ID,Period)
);
END
IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxRuleSetting' and type = 'U')
BEGIN

CREATE TABLE [dbo].[PeriodTaxRuleSetting](
	[ID] [varchar](128) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[IsDefault] [int] NOT NULL,
	[Period] [int] NOT NULL,
	[GroupName] [nvarchar](50) NULL,
	[TaxBase] [varchar](500) NOT NULL,
	[TaxRate] [decimal](20, 4) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
    PRIMARY KEY(ID,Period)
);
END
IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTaxRuleSettingOrganization' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodTaxRuleSettingOrganization](
	[ID] [varchar](128) NOT NULL,
	[TaxSettingID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[OrganizationID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
    PRIMARY KEY(ID,Period)
);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodTemplate' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodTemplate](
	[ID] [varchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[Code] [varchar](500) NULL,
	[Path] [nvarchar](2000) NOT NULL,
	[ReportType] [int] NULL,
	[TemplateGroupID] [varchar](128) NOT NULL,
	[OrderIndex] [int] NULL,
	[CreateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[UpdateTime] [datetime] NOT NULL DEFAULT (getdate()),
	[IsSystemType] [int] NOT NULL,
	[IsActiveAssociation] [int] NOT NULL,
	[ParentId] [varchar](128) NULL,
    PRIMARY KEY(ID,Period)
);
END

IF NOT EXISTS (select 1 from sys.tables where name = 'PeriodCellReference' and type = 'U')
BEGIN
CREATE TABLE [dbo].[PeriodCellReference](
	[ID] [nvarchar](128) NOT NULL,
	[Period] [int] NOT NULL,
	[FormulaBlockIndex] [int] NOT NULL,
	[ReferenceFromCellID] [nvarchar](128) NULL,
	[ReferenceFromTemplateID] [nvarchar](128) NULL,
	[ReferenceToCellID] [nvarchar](128) NULL,
	[ReferenceToTemplateID] [nvarchar](128) NULL,
	[ReferenceFromKeyValueID] [nvarchar](128) NULL,
	[ReferenceToKeyValueID] [nvarchar](128) NULL,
	[IsValidation] [int] NOT NULL,
    PRIMARY KEY(ID,Period)
);
END

GO

-- report end ----------

--end v1.0.0.0-------------------------------------------------------------------------------

GO
IF (TYPE_ID(N'AcctValidTab') IS NULL)
BEGIN
  CREATE TYPE [dbo].[AcctValidTab] AS TABLE(
	[AcctCode] [nvarchar](50) NOT NULL,
	[AcctSum] [numeric](18, 3) NULL,
	[SubSum] [numeric](18, 3) NULL,
	PRIMARY KEY CLUSTERED 
	(
		[AcctCode] ASC
	)WITH (IGNORE_DUP_KEY = OFF)
	)
END


GO
IF NOT EXISTS(SELECT 1 from sys.tables where name = 'Balance' and type = 'U')
BEGIN 
	CREATE TABLE [dbo].[Balance](
	[BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF_Balance_BalanceId]  DEFAULT (CONVERT([varchar](128),newid())),
	[PeriodId] [int] NULL,
	[AcctCode] [nvarchar](50) NULL,
	[CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_Balance_CustomerCode]  DEFAULT (''),
	[BegDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegDebitBal]  DEFAULT ((0)),
	[BegCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegCreditBal]  DEFAULT ((0)),
	[BegBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_BegBal]  DEFAULT ((0)),
	[EndBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndBal]  DEFAULT ((0)),
	[EndDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndDebitBal]  DEFAULT ((0)),
	[EndCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_EndCreditBal]  DEFAULT ((0)),
	[DebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_DebitBal]  DEFAULT ((0)),
	[CreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_Balance_CreditBal]  DEFAULT ((0)),
	[MonthId] [int] NULL,
	[YearDebitBal] [numeric](18, 3) NULL,
	[YearCreditBal] [numeric](18, 3) NULL,
	[CarryoverDebit] NUMERIC(18,3) NULL,
	[CarryoverCredit] NUMERIC(18,3) NULL,
	[YearCarryoverDebit] NUMERIC(18,3) NULL,
	[YearCarryoverCredit] NUMERIC(18,3) NULL,
	[DebitNet] NUMERIC(18,3) NULL,
	[CreditNet] NUMERIC(18,3) NULL,
	[YearDebitNet] NUMERIC(18,3) NULL,
	[YearCreditNet] NUMERIC(18,3) NULL,
	[StdCode] NVARCHAR(50) NULL,
	[MappingReason] NVARCHAR(500) NULL,
	[MappingUser] NVARCHAR(128) NULL,
	[MappingTime] DATETIME NULL,
	[SubProp] INT NULL,
	[IsDummy] [int] NULL DEFAULT(0),
 CONSTRAINT [PK_Balance] PRIMARY KEY CLUSTERED 
(
	[BalanceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 

END


GO
IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CompanyBalance' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[CompanyBalance](
	[BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF__CompanyBa__Balan__70DDC3D8]  DEFAULT (CONVERT([varchar](128),newid())),
	[PeriodId] [int] NULL,
	[AcctCode] [nvarchar](50) NULL,
	[CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_CompanyBalance_CustomerCode]  DEFAULT (''),
	[BegDebitBal] [numeric](18, 3) NULL,
	[BegCreditBal] [numeric](18, 3) NULL,
	[BegBal] [numeric](18, 3) NULL,
	[EndBal] [numeric](18, 3) NULL,
	[EndDebitBal] [numeric](18, 3) NULL,
	[EndCreditBal] [numeric](18, 3) NULL,
	[DebitBal] [numeric](18, 3) NULL,
	[CreditBal] [numeric](18, 3) NULL,
	[MonthId] [int] NULL,
 CONSTRAINT [PK_CompanyBalance] PRIMARY KEY CLUSTERED 
(
	[BalanceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END


GO
IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CustBalance' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[CustBalance](
	[BalanceId] [varchar](128) NOT NULL CONSTRAINT [DF_CustBalance_BalanceId]  DEFAULT (CONVERT([varchar](50),newid())),
	[PeriodId] [bigint] NOT NULL CONSTRAINT [DF_CustBalance_PeriodID]  DEFAULT ((1)),
	[AcctCode] [nvarchar](50) NOT NULL,
	[CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_CustBalance_CustomerCode]  DEFAULT (''),
	[BegBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegBal]  DEFAULT ((0)),
	[BegDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegDebitBal]  DEFAULT ((0)),
	[BegCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_BegCreditBal]  DEFAULT ((0)),
	[DebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_DebitBal]  DEFAULT ((0)),
	[CreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_CreditBal]  DEFAULT ((0)),
	[YearDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_YearDebitBal]  DEFAULT ((0)),
	[YearCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_YearCreditBal]  DEFAULT ((0)),
	[EndBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndBal]  DEFAULT ((0)),
	[EndDebitBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndDebitBal]  DEFAULT ((0)),
	[EndCreditBal] [numeric](18, 3) NULL CONSTRAINT [DF_CustBalance_EndCreditBal]  DEFAULT ((0)),
	[MonthId] [int] NULL,
	[IsDummy] [int] NULL DEFAULT(0),
 CONSTRAINT [PK_CustBalance] PRIMARY KEY CLUSTERED 
(
	[BalanceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END


GO
IF NOT EXISTS(SELECT 1 from sys.tables where name = 'CustomsInvoice' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[CustomsInvoice](
	[CustomsId] [varchar](128) NOT NULL,
	[PayNum] [nvarchar](200) NULL,
	[IssueDate] [datetime] NULL,
	[InvoiceTaxAmount] [numeric](18, 3) NULL,
	[InvoiceAmount] [numeric](18, 3) NULL,
	[AuditResult] [nvarchar](500) NULL,
	[CreateTime] [datetime] NULL,
	[PeriodId] INT NULL,
	[CreatorID] [varchar](128) NULL,
	[Status] [int] NULL,
	[PartAccntedPeriod] [int] NULL,
	[AccntedPeriod] [int] NULL,
 CONSTRAINT [PK_CustomsInvoice] PRIMARY KEY CLUSTERED 
(
	[CustomsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END

GO
IF NOT EXISTS(SELECT 1 from sys.tables where name = 'Voucher' and type = 'U')
BEGIN
	CREATE TABLE [dbo].[Voucher](
	[VoucherID] [varchar](128) NOT NULL CONSTRAINT [DF_Voucher_VoucherID]  DEFAULT (CONVERT([varchar](50),newid())),
	[VID] [nvarchar](256) NOT NULL,
	[Date] [date] NOT NULL,
	[Group] [nvarchar](50) NULL,
	[Period] [int] NULL,
	[CustomerCode] [nvarchar](50) NULL CONSTRAINT [DF_Voucher_CustomerCode]  DEFAULT (''),
	[CustomerName] [nvarchar](100) NULL CONSTRAINT [DF_Voucher_CustomerName]  DEFAULT (''),
	[Summary] [nvarchar](500) NULL,
	[AcctCode] [nvarchar](50) NULL,
	[Debit] [numeric](18, 3) NULL CONSTRAINT [DF_Voucher_Debit]  DEFAULT ((0)),
	[Credit] [numeric](18, 3) NULL CONSTRAINT [DF_Voucher_Credit]  DEFAULT ((0)),
	[Attach] [bigint] NULL,
	[ItemID] [nvarchar](256) NULL,
	[CreateTime] [datetime] NULL CONSTRAINT [DF__Voucher__Creat__1DE57479]  DEFAULT (getdate()),
	[ImportType] [int] NULL,
	[CreatorID] [varchar](128) NULL,
	[MonthID] [int] NULL,
	[OriginalPeriod] INT NULL,
	[Status] [int] NULL,
	[PartBilledPeriod] [int] NULL,
	[BilledPeriod] [int] NULL,
	[StdCode] [nvarchar](50) NULL,
	[MappingReason] NVARCHAR(500) NULL,
	[MappingUser] NVARCHAR(128) NULL,
	[MappingTime] DATETIME NULL,
	[RemapId] VARCHAR(128) NULL,
 CONSTRAINT [PK_Voucher] PRIMARY KEY NONCLUSTERED 
(
	[VoucherID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

END


GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes  WHERE name='IX_Voucher_AcctCode' AND object_id = OBJECT_ID('dbo.Voucher'))
BEGIN
	CREATE CLUSTERED INDEX [IX_Voucher_AcctCode] ON [dbo].[Voucher]
(
	[AcctCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END


GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes  WHERE name='IX_Voucher_CustomerCode' AND object_id = OBJECT_ID('dbo.Voucher'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_Voucher_CustomerCode] ON [dbo].[Voucher]
(
	[CustomerCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END



GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes  WHERE name='IX_Voucher_Summary' AND object_id = OBJECT_ID('dbo.Voucher'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_Voucher_Summary] ON [dbo].[Voucher]
(
	[Summary] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END


GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes  WHERE name='IX_Voucher_VID_ItemID_PeriodID' AND object_id = OBJECT_ID('dbo.Voucher'))
BEGIN
	CREATE NONCLUSTERED INDEX [IX_Voucher_VID_ItemID_PeriodID] ON [dbo].[Voucher]
(
	[VID] ASC,
	[ItemID] ASC,
	[Period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END


GO
IF NOT EXISTS(SELECT 1 FROM sys.indexes  WHERE name='IX_Voucher_ImportType' AND object_id = OBJECT_ID('dbo.Voucher'))
BEGIN
	
CREATE NONCLUSTERED INDEX [IX_Voucher_ImportType] ON [dbo].[Voucher]
(
	[ImportType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END





GO
ALTER TABLE [dbo].[CustomsInvoice] ADD  CONSTRAINT [DF_CustomsInvoice_CustomsId]  DEFAULT (CONVERT([varchar](50),newid())) FOR [CustomsId]
GO
ALTER TABLE [dbo].[ValidationInfo] ADD  CONSTRAINT [DF_ValidationInfo_ID]  DEFAULT (CONVERT([varchar](50),newid())) FOR [ID]

GO 
IF NOT EXISTS (select 1 from sys.tables where name = 'CustBalanceStd' and type = 'U')
BEGIN
  CREATE TABLE [dbo].[CustBalanceStd](
	[BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())),
	[PeriodID] [INT] NOT NULL,
	[AcctCode] [NVARCHAR](50) NOT NULL,
	[CustomerCode] [NVARCHAR](50) NULL,
	[BegBal] [NUMERIC](18, 3) NULL,
	[DebitBal] [NUMERIC](18, 3) NULL,
	[CreditBal] [NUMERIC](18, 3) NULL,
	[YearDebitBal] [NUMERIC](18, 3) NULL,
	[YearCreditBal] [NUMERIC](18, 3) NULL,
	[EndBal] [NUMERIC](18, 3) NULL,
	[BegDebitBal] [NUMERIC](18, 3) NULL,
	[BegCreditBal] [NUMERIC](18, 3) NULL,
	[EndDebitBal] [NUMERIC](18, 3) NULL,
	[EndCreditBal] [NUMERIC](18, 3) NULL,
 CONSTRAINT [PK_CustBalanceStd] PRIMARY KEY CLUSTERED 
(
	[BalanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END 


GO
IF NOT EXISTS (select 1 from sys.tables where name = 'CustBalanceStdManual' and type = 'U')
BEGIN
CREATE TABLE [dbo].[CustBalanceStdManual](
	[BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())),
	[PeriodID] [BIGINT] NOT NULL,
	[AcctCode] [NVARCHAR](50) NOT NULL,
	[CustomerCode] [NVARCHAR](50) NULL,
	[BegBal] [NUMERIC](18, 3) NULL,
	[BegDebitBal] [NUMERIC](18, 3) NULL,
	[BegCreditBal] [NUMERIC](18, 3) NULL,
	[DebitBal] [NUMERIC](18, 3) NULL,
	[CreditBal] [NUMERIC](18, 3) NULL,
	[YearDebitBal] [NUMERIC](18, 3) NULL,
	[YearCreditBal] [NUMERIC](18, 3) NULL,
	[EndBal] [NUMERIC](18, 3) NULL,
	[EndDebitBal] [NUMERIC](18, 3) NULL,
	[EndCreditBal] [NUMERIC](18, 3) NULL,
 CONSTRAINT [PK_CustBalanceStdManual] PRIMARY KEY CLUSTERED 
(
	[BalanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END 

GO 
IF NOT EXISTS (select 1 from sys.tables where name = 'BalanceStdManual' and type = 'U')
BEGIN
  CREATE TABLE [dbo].[BalanceStdManual](
	[BalanceID] [VARCHAR](128) NOT NULL DEFAULT (CONVERT([VARCHAR](50),NEWID())),
	[PeriodID] [BIGINT] NOT NULL,
	[AcctCode] [NVARCHAR](50) NOT NULL,
	[BegBal] [NUMERIC](18, 3) NULL,
	[BegDebitBal] [NUMERIC](18, 3) NULL,
	[BegCreditBal] [NUMERIC](18, 3) NULL,
	[DebitBal] [NUMERIC](18, 3) NULL,
	[CreditBal] [NUMERIC](18, 3) NULL,
	[YearDebitBal] [NUMERIC](18, 3) NULL,
	[YearCreditBal] [NUMERIC](18, 3) NULL,
	[EndBal] [NUMERIC](18, 3) NULL,
	[EndDebitBal] [NUMERIC](18, 3) NULL,
	[EndCreditBal] [NUMERIC](18, 3) NULL,
	[SubProp] [INT] NULL,
	[CarryoverDebit] [NUMERIC](18, 3) NULL,
	[CarryoverCredit] [NUMERIC](18, 3) NULL,
	[YearCarryoverDebit] [NUMERIC](18, 3) NULL,
	[YearCarryoverCredit] [NUMERIC](18, 3) NULL,
	[DebitNet] [NUMERIC](18, 3) NULL,
	[CreditNet] [NUMERIC](18, 3) NULL,
	[YearDebitNet] [NUMERIC](18, 3) NULL,
	[YearCreditNet] [NUMERIC](18, 3) NULL,
CONSTRAINT [PK_BalanceStdManual] PRIMARY KEY CLUSTERED 
(
[BalanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END 

GO 
IF NOT EXISTS(SELECT 1 from sys.objects where name = 'VoucherMain' and type_desc = 'USER_TABLE')
BEGIN
	CREATE TABLE [dbo].[VoucherMain](
	[VoucherMainID] VARCHAR(128) NOT NULL DEFAULT (CONVERT([varchar](50),newid())),
	[Period] [int] NULL,
	[Group] [nvarchar](50) NULL,
	[VID] [nvarchar](256) NOT NULL,
	[VoucherCount] [int] NULL,
	[AcctCodeCount] [int] NULL,
	[Amount] [decimal](18, 3) NULL,
	[AmountCredit] [decimal](18, 3) NULL,
	[CreateTime] DATETIME NULL,
	[UpdateTime] DATETIME NULL,
	[CreatorID] VARCHAR(128) NULL
	PRIMARY KEY CLUSTERED 
	(
		[VoucherMainID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

END

GO 
IF NOT EXISTS (select 1 from sys.tables where name = 'AccountRemap' and type = 'USER_TABLE')
BEGIN
   CREATE TABLE [dbo].[AccountRemap](
	[RemapId] [VARCHAR](128) NOT NULL CONSTRAINT [DF_AccountRemap_RemapId]  DEFAULT (CONVERT([VARCHAR](50),NEWID())),
	[Period] [INT] NOT NULL,
	[AcctCode] [NVARCHAR](50) NULL,
	[CustomerCode] [NVARCHAR](50) NULL,
	[RemapDebit] [NUMERIC](18, 3) NULL,
	[RemapCredit] [NUMERIC](18, 3) NULL,
	[StdCode] [NVARCHAR](50) NULL,
	[ReMappName] [NVARCHAR](200) NOT NULL,
	[ReMappReason] [NVARCHAR](500) NULL,
	[ReMappTypeId] [INT] NOT NULL,
	[CreateTime] [DATETIME] NULL,
	[UpdateTime] [DATETIME] NULL,
	[CreatorID] [NVARCHAR](128) NULL,
	[RemapBatchId] [VARCHAR](128) NULL,
 CONSTRAINT [PK_C_AccountRemap] PRIMARY KEY CLUSTERED 
(
	[RemapId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END

GO 
IF NOT EXISTS (select 1 from sys.tables where name = 'AccountRemapSum' and type = 'USER_TABLE')
BEGIN
	  CREATE TABLE [dbo].[AccountRemapSum](
		[AccountRemapSumId] [VARCHAR](128) NOT NULL CONSTRAINT [DF_AccountRemapSum_AccountRemapSumId]  DEFAULT (CONVERT([VARCHAR](50),NEWID())),
		[Period] [INT] NULL,
		[AcctCode] [NVARCHAR](50) NULL,
		[Parent] [NVARCHAR](50) NULL,
		[AcctLevel] [INT] NULL,
		[RemapDebit] [NUMERIC](18, 3) NULL,
		[RemapCredit] [NUMERIC](18, 3) NULL,
		[CreateTime] [DATETIME] NULL,
		[UpdateTime] [DATETIME] NULL,
		[CreatorID] [NVARCHAR](128) NULL,
	 CONSTRAINT [PK_AccountRemapSum] PRIMARY KEY CLUSTERED 
	(
		[AccountRemapSumId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END 



GO
IF EXISTS(SELECT 1 from sys.objects where name = 'AcctValid' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].AcctValid
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'ImportPackageCheckDuplicate' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].ImportPackageCheckDuplicate
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceByConditions' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].InitBalanceByConditions
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'UpdateNonLeafVocuhers' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].UpdateNonLeafVocuhers
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitVoucherMain' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitVoucherMain]
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccountIsInVoucher' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitAccountIsInVoucher]
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccount' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitAccount]
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitCustBalanceStd' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitCustBalanceStd]
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitCarryover' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitCarryover]
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStd' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitBalanceStd]
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitCustBalanceStdRemap' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitCustBalanceStdRemap]
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStdRemap' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitBalanceStdRemap]
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitBalanceStdRemapCarryover' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitBalanceStdRemapCarryover]
END


GO
IF EXISTS(SELECT 1 from sys.objects where name = 'InitAccountRemapSum' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[InitAccountRemapSum]
END

GO
IF EXISTS(SELECT 1 from sys.objects where name = 'UpdateParentCalValues' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
  DROP PROCEDURE [dbo].[UpdateParentCalValues]
END



GO
CREATE PROCEDURE [dbo].[AcctValid]
	-- Add the parameters for the stored procedure here
	 --@avt [dbo].[AcctValidTab]  READONLY
	 @periodId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @temp AS [dbo].[AcctValidTab];
	DECLARE @AcctLevel int;
	DECLARE @Parent nvarchar(50);
	DECLARE @ParentBegBal numeric(18,3);
	DECLARE @SumBegBal numeric(18,3);      
	DECLARE @T_RealBegBal Table (BegBal numeric(18, 3) null); 
	       
	select @AcctLevel = Max(AcctLevel) from  [dbo].[EnterpriseAccount]

	while @AcctLevel >1
	begin
		DECLARE Parent_Cursor CURSOR FOR
		select distinct ParentCode from [dbo].[EnterpriseAccount] where AcctLevel = @AcctLevel;
		OPEN Parent_Cursor;
		FETCH NEXT FROM Parent_Cursor INTO @Parent
		WHILE @@FETCH_STATUS = 0
		BEGIN	

				--select @ParentBegBal= BegBal from [dbo].[C_CustBalance] where AcctCode=@Parent and PeriodID = @periodId and CustomerCode = '';

			    --select @SumBegBal = sum(c.BegBal) 
				--from [dbo].[C_CustBalance] c 
				--join [dbo].[C_Account] a on c.AcctCode = a.Code 
				--where c.AcctCode like @Parent+'%' and a.AcctLevel= @AcctLevel and c.CustomerCode='' and PeriodID = @periodId;
				
				select @ParentBegBal= (c.BegBal * a.Direction) 
				from [dbo].[CustBalance] c
				inner join [dbo].[EnterpriseAccount] a on c.AcctCode = a.AcctCode
				where c.AcctCode=@Parent and c.PeriodID = @periodId and c.CustomerCode = '';


				insert into @T_RealBegBal(BegBal)
				select t.RealBegBal
				from 
				(select (c.BegBal * a.Direction) as RealBegBal
				from [dbo].[CustBalance] c 
				join [dbo].[EnterpriseAccount] a on c.AcctCode = a.AcctCode 
				where c.AcctCode like @Parent+'%' and a.AcctLevel= @AcctLevel and c.CustomerCode='' and PeriodID = @periodId) t;

				select @SumBegBal = sum(BegBal)  from @T_RealBegBal			
				
				if round(@ParentBegBal,2) <>round(@SumBegBal,2) 
				begin
					insert @temp values (@Parent,round(@ParentBegBal,2),round(@SumBegBal,2));
				end;

				delete from @T_RealBegBal

				FETCH NEXT FROM Parent_Cursor INTO @Parent
		END;

		CLOSE Parent_Cursor;
		DEALLOCATE Parent_Cursor;
		set @AcctLevel = @AcctLevel -1;
	end;

	select AcctCode,AcctSum,SubSum from @temp;
END



GO
CREATE PROCEDURE [dbo].[ImportPackageCheckDuplicate]

AS
BEGIN
	
	SET NOCOUNT ON;
	Create Table #dupTable
	(
	  Num int,
	  TableName nvarchar(50),
	  DuplicateCount int
	)

	declare @acctDupCount as int,
	        @cusBalDupCount as int,
			@cutDupCount as int,
			@periodDupCount as int,
			@voucherDupCount as int,
			@invoiceDupCount as int
   /*
    --C_Account duplicate check:Code
	SELECT @acctDupCount = COUNT(*)
	FROM(
	SELECT 
	ROW_NUMBER() OVER(PARTITION BY Code ORDER BY AccountID ) AS rn
	FROM dbo.C_Account) as d
	where d.rn > 1
	insert into #dupTable(Num,TableName,DuplicateCount) values (1,'Account',@acctDupCount)
	 	*/

   --C_CustBalance duplicate check:PeriodId, AcctCode,CustomerCode
   select @cusBalDupCount = Count(*)
   from(
    SELECT 
	 ROW_NUMBER() OVER(PARTITION BY PeriodID,AcctCode,CustomerCode ORDER BY BalanceID ) AS rn
	 FROM dbo.CustBalance
   ) as d
   where d.rn > 1
   insert into #dupTable(Num,TableName,DuplicateCount) values (1,'CustBalance',@cusBalDupCount)

     /*
  --C_Period duplicate check:BegDate,EndData
  select @periodDupCount = count(*)
  from
  (
	  select ROW_NUMBER() over(partition by BegDate,EndDate order by PeriodID) as rn
	  from dbo.C_Period
  ) as d
  where d.rn > 1
   insert into #dupTable(Num,TableName,DuplicateCount) values (3,'Period',@periodDupCount)

  --C_Customer duplicate check:CustomerCode
  select @cutDupCount = count(*)
  from
  (
     SELECT 
	 ROW_NUMBER() OVER(PARTITION BY CustomerCode ORDER BY CustomerID ) AS rn
	 FROM dbo.C_Customer
  ) as d
  where d.rn > 1
  insert into #dupTable(Num,TableName,DuplicateCount) values (4,'Customer',@cutDupCount)
  	*/


  --C_Voucher duplicate check:VID,ItemID,VID,Period,Group
    select @voucherDupCount = count(*)
	from 
	(
	select ROW_NUMBER() over (partition by VID,ItemID,Period,[Group] order by VoucherID) as rn
	from dbo.Voucher
	where ItemID is not null
	) as d
	where d.rn > 1

	insert into #dupTable(Num,TableName,DuplicateCount) values (2,'Voucher',@voucherDupCount)
  
  /*
  --V_VoucherInvoice duplicate check:Period, VID, ItemID, TransactionCode, InvoiceNumber,Group
  select @invoiceDupCount = count(*)
  from
  (
	  select ROW_NUMBER() over(partition by Period, VID, ItemID, TransactionCode, InvoiceNumber,[Group] order by VoucherInvoiceID) as rn
	  from dbo.V_VoucherInvoice
  ) as d
  where d.rn > 1
  insert into #dupTable(Num,TableName,DuplicateCount) values (6,'VoucherInvoice',@invoiceDupCount)
  */
  
  select Num,TableName,DuplicateCount from #dupTable

  drop table #dupTable
    


END

GO
/****** Object:  StoredProcedure [dbo].[InitBalanceByConditions]    Script Date: 08/05/2017 17:15:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Bin Li
-- Create date: 2016.06.12
-- Parameters:  1. @PeriodIdParam:
--                 @PeriodIdParam < 0: calculate entire year; @StartPeriod > 0: calculate from period @PeriodIdParam
-- Description:	1. manage data by conditions:  1) BegBal imported   2) BegDebitBal,BegCreditBal
--              2. voucher is imported by JournalEntry(ImportType = 3) will be filtered
-- =============================================

CREATE PROCEDURE [dbo].[InitBalanceByConditions]
     @PeriodIdParam INT
AS
    BEGIN
	
        SET NOCOUNT ON;

        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @sumDebit FLOAT;
        DECLARE @sumCredit FLOAT;
        DECLARE @BegBal FLOAT;
        DECLARE @EndBal FLOAT;
        DECLARE @sumYearDebit FLOAT;
        DECLARE @sumYearCredit FLOAT;
        DECLARE @isLeaf INT;
        DECLARE @Direction INT;
        DECLARE @BegDebitBal FLOAT;
        DECLARE @BegCreditBal FLOAT;
        DECLARE @EndDebitBal FLOAT;
        DECLARE @EndCreditBal FLOAT;
        DECLARE @PeriodID INT;
        DECLARE @PeriodIDMax INT;
		DECLARE @StartPeriodID INT;
		DECLARE @IsDummy INT;
		DECLARE @DummyAcctChildrenTb TABLE(AcctCode NVARCHAR(50));
	
		IF(@PeriodIdParam < 0)
		BEGIN
			SET @StartPeriodID = 1;
		END
		ELSE
        BEGIN
			SET @StartPeriodID = @PeriodIdParam;
		END
               
	    --Step 1: Delete the data of CustBalance except the first period and truncate Balance		
        DELETE  FROM [dbo].[CustBalance] WHERE PeriodId > @StartPeriodID      
        TRUNCATE TABLE [dbo].[Balance];

	    --Step 2: Get AcctCode + CustomerCode from Voucher that does not exist in CustBalance, then insert them into CustBalance
        DECLARE Customer_Cursor CURSOR FOR 
        SELECT  AcctCode,CustomerCode
        FROM    [dbo].[Voucher] WHERE Period = @StartPeriodID
        GROUP BY AcctCode,CustomerCode
        EXCEPT
        SELECT  AcctCode ,CustomerCode
        FROM    [dbo].CustBalance WHERE PeriodId = @StartPeriodID
        GROUP BY AcctCode,CustomerCode;
        OPEN Customer_Cursor;
        FETCH NEXT FROM Customer_Cursor INTO @AcctCode, @CustomerCode;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode)
				BEGIN 
				    SELECT @BegBal= ISNULL(EndBal,0) FROM dbo.CustBalance 
					WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID - 1

					INSERT [dbo].[CustBalance](PeriodId,AcctCode,CustomerCode,BegBal)
                    VALUES  (@StartPeriodID,@AcctCode,@CustomerCode,@BegBal);                                                                       
				END              				
                FETCH NEXT FROM Customer_Cursor INTO @AcctCode, @CustomerCode;
            END;

        CLOSE Customer_Cursor;
        DEALLOCATE Customer_Cursor;


	   --Step 3: Get AcctCode + CustomerCode(always empty) from EnterpriseAccount that does not exist in CustBalance, then insert them into CustBalance  
		DECLARE AcctCode_Cursor CURSOR
        FOR
            SELECT  AcctCode, ''
            FROM    [dbo].EnterpriseAccount
            EXCEPT
            SELECT  AcctCode,CustomerCode
            FROM    [dbo].[CustBalance]
            WHERE   PeriodId = @StartPeriodID
            GROUP BY AcctCode,CustomerCode;
        OPEN AcctCode_Cursor;
        FETCH NEXT FROM AcctCode_Cursor INTO @AcctCode, @CustomerCode;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			    IF NOT EXISTS(SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode)
				BEGIN
				     -- if missing account is not leaf AND it has any level children account in CompanyBalance, then set IsDummy = 1		
					 SELECT @isLeaf = IsLeaf FROM dbo.EnterpriseAccount WHERE AcctCode = @AcctCode 	
					 SET @IsDummy = 0;
					 DELETE FROM  @DummyAcctChildrenTb
					 IF(@isLeaf = 0)
				     BEGIN
						WITH tblChild AS -- get all level children for @AcctCode
						(
						   SELECT * FROM dbo.EnterpriseAccount WHERE AcctCode  = @AcctCode
						   UNION ALL
						   SELECT a1.* FROM dbo.EnterpriseAccount a1 JOIN tblChild  ON a1.ParentCode = tblChild.AcctCode
						)
						INSERT INTO @DummyAcctChildrenTb ( AcctCode )
						SELECT AcctCode FROM tblChild WHERE tblChild.AcctCode != @AcctCode
					 
						IF EXISTS(SELECT 1 FROM dbo.CompanyBalance WHERE AcctCode IN (SELECT AcctCode FROM @DummyAcctChildrenTb))
						BEGIN					    
							SET @IsDummy = 1;
						END
					 END 
					 											 				 					 																								 									 
					 SELECT @BegBal= ISNULL(EndBal,0) FROM dbo.CustBalance 
					 WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode AND PeriodId = @StartPeriodID - 1

					 INSERT  [dbo].[CustBalance](PeriodId, AcctCode, CustomerCode,BegBal,IsDummy)                           
                     VALUES  (@StartPeriodID,@AcctCode ,'' ,@BegBal, @IsDummy);                                                                      
				END                            
                FETCH NEXT FROM AcctCode_Cursor INTO @AcctCode, @CustomerCode;
            END;
        CLOSE AcctCode_Cursor;
        DEALLOCATE AcctCode_Cursor;
		
	
        IF EXISTS ( SELECT 1 FROM  dbo.[CustBalance] WHERE BegBal != 0 AND PeriodId = @StartPeriodID) -- import BegBal
         BEGIN
         	 UPDATE dbo.CustBalance SET BegBal = 0 WHERE PeriodId = @StartPeriodID AND BegBal IS NULL;
	       --Step 4: Update the @StartPeriodID period of each CustBalance record
                DECLARE CustBal_Cursor CURSOR
                FOR
                    SELECT   cb.AcctCode, 
					         CustomerCode, 
							 SUM(BegBal), 
							 IsLeaf, Direction
                    FROM    [dbo].[CustBalance] cb
                            INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
                    WHERE   PeriodId = @StartPeriodID
                    GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction;
                OPEN CustBal_Cursor;
                FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode,@BegBal, @isLeaf, @Direction;
                
                WHILE @@FETCH_STATUS = 0
                    BEGIN
					   -- if account + customer was calculated in (@StartPeriodID - 1), then use its EndBal @StartPeriodID's BegBal. 
					   -- otherwise, stay default
					   IF EXISTS (SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID - 1)
					   BEGIN
					       SELECT @BegBal = SUM(cb.EndBal)
						   FROM    [dbo].[CustBalance] cb
                           INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
						   WHERE cb.PeriodId = @StartPeriodID - 1 
						         AND cb.AcctCode = @AcctCode 
								 AND cb.CustomerCode = @CustomerCode	
						  
						   UPDATE dbo.CustBalance SET BegBal = @BegBal 
						   		  WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode		
					   END
                    					                  
                       IF ( @isLeaf = 1 AND @CustomerCode <> '')
                            OR ( @isLeaf = 0
                                 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3))
                                 AND @CustomerCode <> ''
                               )
                            BEGIN
                                SELECT  @sumDebit = SUM(Debit) ,
                                        @sumCredit = SUM(Credit)
                                FROM    [dbo].[Voucher]
                                WHERE   AcctCode LIKE @AcctCode + '%'
                                        AND CustomerCode = @CustomerCode
                                        AND Period = @StartPeriodID
										AND (ImportType IS NULL OR ImportType != 3);
                            END;
                        ELSE
                            BEGIN
                                SELECT  @sumDebit = SUM(Debit) ,
                                        @sumCredit = SUM(Credit)
                                FROM    [dbo].[Voucher]
                                WHERE   AcctCode LIKE @AcctCode + '%'
                                        AND Period = @StartPeriodID
										AND (ImportType IS NULL OR ImportType != 3);
                            END;
                       
                        --GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal 					    
                        SELECT  @sumYearDebit = ISNULL(SUM(DebitBal),0)  ,
                                 @sumYearCredit = ISNULL(SUM(CreditBal),0)
                        FROM    [dbo].[CustBalance]
                        WHERE   AcctCode = @AcctCode
                                AND CustomerCode = @CustomerCode
                                AND PeriodId <= @StartPeriodID - 1;  
						        
					 					                				                     				  
                       IF @sumDebit IS NOT NULL
                            BEGIN
                                UPDATE  [dbo].[CustBalance]
                                SET     DebitBal = @sumDebit ,
                                        CreditBal = @sumCredit ,
                                        YearDebitBal = @sumDebit + @sumYearDebit ,
                                        YearCreditBal = @sumCredit + @sumYearCredit,
                                        EndBal = ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ,                                      
                                        BegDebitBal = CASE WHEN @Direction = 1
                                                           THEN @BegBal
                                                           ELSE 0
                                                      END ,
                                        BegCreditBal = CASE WHEN @Direction = -1
                                                            THEN @BegBal
                                                            ELSE 0
                                                       END ,
                                        EndDebitBal = CASE WHEN @Direction = 1
                                                           THEN ( @BegBal
                                                              * @Direction
                                                              + @sumDebit
                                                              - @sumCredit )
                                                              * @Direction
                                                           ELSE 0
                                                      END ,
                                        EndCreditBal = CASE WHEN @Direction = -1
                                                            THEN ( @BegBal
                                                              * @Direction
                                                              + @sumDebit
                                                              - @sumCredit )
                                                              * @Direction
                                                            ELSE 0
                                                       END
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
										AND PeriodId = @StartPeriodID;
                            END;
                        ELSE
                            BEGIN
                                UPDATE  [dbo].[CustBalance]
                                SET     DebitBal = 0 ,
                                        CreditBal = 0 ,
                                        YearDebitBal = 0 ,
                                        YearCreditBal = 0 ,
                                        EndBal = @BegBal ,                                      
                                        BegDebitBal = CASE WHEN @Direction = 1
                                                           THEN @BegBal
                                                           ELSE 0
                                                      END ,
                                        BegCreditBal = CASE WHEN @Direction = -1
                                                            THEN @BegBal
                                                            ELSE 0
                                                       END ,
                                        EndDebitBal = CASE WHEN @Direction = 1
                                                           THEN @BegBal
                                                           ELSE 0
                                                      END ,
                                        EndCreditBal = CASE WHEN @Direction = -1
                                                            THEN @BegBal
                                                            ELSE 0
                                                       END
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
										AND PeriodId = @StartPeriodID;
									
                            END;		
                            									
                        FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction;
                    END;

                CLOSE CustBal_Cursor;
                DEALLOCATE CustBal_Cursor;
			 
			   --update BegBal for dummy account		  
			   EXECUTE dbo.UpdateParentCalValues @CalPeriodId = @StartPeriodID, @IsImportBegBal = 1;
			   
			    
			  
		    --Step 5: Insert the rest 11 periods of each CustBalance record		
		        SET @PeriodID = @StartPeriodID + 1;	
				SELECT @PeriodIDMax = MAX(Period) FROM dbo.Voucher WHERE ImportType IS NULL OR ImportType != 3;		
				IF ISNULL(@PeriodIDMax,0) < 12 
				BEGIN
				   SET @PeriodIDMax = 12
				END	
														                                             				
                WHILE @PeriodID <= @PeriodIDMax                 																				                                                                                               
                    BEGIN	
                        DECLARE CustBal_Cursor CURSOR
                        FOR
                            SELECT  cb.AcctCode ,
                                    CustomerCode ,
                                    SUM(EndBal) ,
                                    IsLeaf ,
                                    Direction
                            FROM    [dbo].[CustBalance] cb
                                    INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
                            WHERE   PeriodId = @PeriodID - 1 -- Get last period EndBal as current period BegBal column value
                            GROUP BY cb.AcctCode ,
                                    CustomerCode ,
                                    IsLeaf ,
                                    Direction;
                        OPEN CustBal_Cursor;
                        FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @EndBal, @isLeaf, @Direction;
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                
                                --1. Cal Debit and Credit of current period 
                                IF ( @isLeaf = 1 AND @CustomerCode <> '')
                                    OR ( @isLeaf = 0
                                         AND EXISTS ( SELECT  1
                                                      FROM    dbo.Voucher
                                                      WHERE   AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3))
                                         AND @CustomerCode <> ''										 
                                       )
                                    BEGIN
                                        SELECT  @sumDebit = SUM(Debit) ,
                                                @sumCredit = SUM(Credit)
                                        FROM    [dbo].[Voucher]
                                        WHERE   AcctCode LIKE @AcctCode + '%'
                                                AND CustomerCode = @CustomerCode
                                                AND Period = @PeriodID
												AND (ImportType IS NULL OR ImportType != 3);
                                    END;
                                ELSE
                                    BEGIN
                                        SELECT  @sumDebit = SUM(Debit) ,
                                                @sumCredit = SUM(Credit)
                                        FROM    [dbo].[Voucher]
                                        WHERE   AcctCode LIKE @AcctCode + '%'
                                                AND Period = @PeriodID
												AND (ImportType IS NULL OR ImportType != 3);
                                    END;
 
                                --2. GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal
                                SELECT  @sumYearDebit = SUM(DebitBal) ,
                                        @sumYearCredit = SUM(CreditBal)
                                FROM    [dbo].[CustBalance]
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
                                        AND PeriodId <= @PeriodID - 1;  
						        
							
                                -- set dummy value for current period                              						        																												        																								 ;
                                IF EXISTS(SELECT 1 FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode 
								              AND PeriodId = @PeriodID -1 AND IsDummy = 1)
								BEGIN
								     SET @IsDummy = 1;
								END
								ELSE
								BEGIN
								    SET @IsDummy = 0;
								END
                                
                                                             
                                --4. Insert value																		   				        																												        																								 
                                IF @sumDebit IS NOT NULL
                                    BEGIN
                                        INSERT  [dbo].[CustBalance]
                                                ( PeriodId ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal ,
                                                  BegDebitBal ,
                                                  BegCreditBal ,
                                                  EndDebitBal ,
                                                  EndCreditBal,
												  IsDummy
                                                )
                                        VALUES  ( @PeriodID ,
                                                  @AcctCode ,
                                                  @CustomerCode ,
                                                  @EndBal ,
                                                  @sumDebit ,
                                                  @sumCredit ,
                                                  @sumYearDebit + @sumDebit ,
                                                  @sumYearCredit + @sumCredit ,
                                                  ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ,
                                                  CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END ,
                                                  CASE WHEN @Direction = -1 THEN @EndBal ELSE 0
                                                  END ,
                                                  CASE WHEN @Direction = 1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction
                                                       ELSE 0 END ,
                                                  CASE WHEN @Direction = -1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction
                                                       ELSE 0 END,
												  @IsDummy
                                                );
                                    END;
                                ELSE
                                    BEGIN
                                        INSERT  [dbo].[CustBalance]
                                                ( PeriodId ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal ,
                                                  BegDebitBal ,
                                                  BegCreditBal ,
                                                  EndDebitBal ,
                                                  EndCreditBal,
												  IsDummy
                                                )
                                        VALUES  ( @PeriodID ,
                                                  @AcctCode ,
                                                  @CustomerCode ,
                                                  @EndBal ,
                                                  0 ,
                                                  0 ,
                                                  @sumYearDebit ,
                                                  @sumYearCredit ,
                                                  @EndBal ,
                                                  CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END ,
                                                  CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END ,
                                                  CASE WHEN @Direction = 1 THEN @EndBal ELSE 0 END ,
                                                  CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END,
												  @IsDummy
                                                );										
                                    END;
                                
                                FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf,@Direction;
                            END;

                        CLOSE CustBal_Cursor;
                        DEALLOCATE CustBal_Cursor;
								
                        SET @PeriodID = @PeriodID + 1;
                    END;
            		
            END;
        ELSE -- import BegDebitBal and BegCreditBal
            BEGIN        	            
	            -- set BegDebitBal/BegCreditBal as 0 if it is null
	            UPDATE dbo.CustBalance SET BegDebitBal = 0 WHERE PeriodId = @StartPeriodID AND BegDebitBal IS NULL
	            UPDATE dbo.CustBalance SET BegCreditBal = 0 WHERE PeriodId = @StartPeriodID AND BegCreditBal IS NULL
	            --Step 4: Update the first period of each CustBalance record
                DECLARE CustBal_Cursor CURSOR
                FOR
                    SELECT  cb.AcctCode ,
                            CustomerCode ,
                            SUM(BegBal) ,
                            IsLeaf ,
                            Direction ,
                            SUM(BegDebitBal) ,
                            SUM(BegCreditBal)
                    FROM    [dbo].[CustBalance] cb
                            INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
                    WHERE   PeriodId = @StartPeriodID
                    GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction;
                OPEN CustBal_Cursor;
                FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction, @BegDebitBal, @BegCreditBal;
                WHILE @@FETCH_STATUS = 0
                    BEGIN				
					    -- if account + customer was calculated in (@StartPeriodID - 1), then use its EndDebitBal/EndCreditBal as @StartPeriodID's BegDebitBal/BegCreditBal
					    -- otherwise, stay default
					    IF EXISTS (SELECT 1 FROM dbo.CustBalance WHERE PeriodId = @StartPeriodID - 1)
					    BEGIN
					       SELECT @BegDebitBal = SUM(cb.EndDebitBal), @BegCreditBal = SUM(cb.EndCreditBal)
						   FROM    [dbo].[CustBalance] cb
                           INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
						   WHERE cb.PeriodId = @StartPeriodID - 1 
						         AND cb.AcctCode = @AcctCode 
								 AND cb.CustomerCode = @CustomerCode
								 								 
						   UPDATE dbo.CustBalance SET BegDebitBal = @BegDebitBal 
						   		  WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode	
						   		  
						   UPDATE dbo.CustBalance SET BegCreditBal = @BegCreditBal
						   	      WHERE PeriodId = @StartPeriodID AND AcctCode = @AcctCode AND CustomerCode = @CustomerCode	 								 																 	
								 								 								 								 																 						
					    END
						
						--set BegBal value
                        SET @BegBal = ( @BegDebitBal - @BegCreditBal ) * @Direction;
			
                        IF ( @isLeaf = 1 AND @CustomerCode <> '')
                            OR ( @isLeaf = 0
                                 AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3))
                                 AND @CustomerCode <> ''
                               )
                            BEGIN
                                SELECT  @sumDebit = CASE WHEN SUM(Debit) IS NULL
                                                         THEN 0
                                                         ELSE SUM(Debit)
                                                    END ,
                                        @sumCredit = CASE WHEN SUM(Credit) IS NULL
                                                          THEN 0
                                                          ELSE SUM(Credit)
                                                     END
                                FROM    [dbo].[Voucher]
                                WHERE   AcctCode LIKE @AcctCode + '%'
                                        AND CustomerCode = @CustomerCode
                                        AND Period = @StartPeriodID
										AND (ImportType IS NULL OR ImportType != 3);
                            END;
                        ELSE
                            BEGIN
                                SELECT  @sumDebit = CASE WHEN SUM(Debit) IS NULL
                                                         THEN 0
                                                         ELSE SUM(Debit)
                                                    END ,
                                        @sumCredit = CASE WHEN SUM(Credit) IS NULL
                                                          THEN 0
                                                          ELSE SUM(Credit)
                                                     END
                                FROM    [dbo].[Voucher]
                                WHERE   AcctCode LIKE @AcctCode + '%'
                                        AND Period = @StartPeriodID
										AND (ImportType IS NULL OR ImportType != 3);
                            END;
                                                        
                        
                         --GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal 
                        SELECT  @sumYearDebit = ISNULL(SUM(DebitBal),0)  ,
                                @sumYearCredit = ISNULL(SUM(CreditBal),0)
                        FROM    [dbo].[CustBalance]
                        WHERE   AcctCode = @AcctCode
                                AND CustomerCode = @CustomerCode
                                AND PeriodId <= @StartPeriodID - 1;                                  														                             

				  
                        IF @sumDebit IS NOT NULL
                            BEGIN
                                UPDATE  [dbo].[CustBalance]
                                SET     DebitBal = @sumDebit ,
                                        CreditBal = @sumCredit ,
                                        YearDebitBal = @sumDebit + @sumYearDebit ,
                                        YearCreditBal = @sumCredit + @sumYearCredit,
                                        EndBal = ( @BegBal * @Direction + @sumDebit - @sumCredit ) * @Direction ,
                                        BegBal = @BegBal ,
                                        EndDebitBal = CASE WHEN @Direction = 1
                                                           THEN ( @BegBal
                                                              * @Direction
                                                              + @sumDebit
                                                              - @sumCredit )
                                                              * @Direction
                                                           ELSE 0
                                                      END ,
                                        EndCreditBal = CASE WHEN @Direction = -1
                                                            THEN ( @BegBal
                                                              * @Direction
                                                              + @sumDebit
                                                              - @sumCredit )
                                                              * @Direction
                                                            ELSE 0
                                                       END
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
                                        AND PeriodId = @StartPeriodID;                                       										
                            END;
                        ELSE
                            BEGIN
                                UPDATE  [dbo].[CustBalance]
                                SET     DebitBal = 0 ,
                                        CreditBal = 0 ,
                                        YearDebitBal = 0 ,
                                        YearCreditBal = 0 ,
                                        EndBal = @BegBal ,
                                        BegBal = @BegBal ,
                                        EndDebitBal = CASE WHEN @Direction = 1
                                                           THEN @BegBal
                                                           ELSE 0
                                                      END ,
                                        EndCreditBal = CASE WHEN @Direction = -1
                                                            THEN @BegBal
                                                            ELSE 0
                                                       END
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
                                        AND PeriodId = @StartPeriodID;
                                                                               
                            END;
				
                        FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @CustomerCode, @BegBal, @isLeaf, @Direction,@BegDebitBal, @BegCreditBal;
                    END;
                    
                CLOSE CustBal_Cursor;
                DEALLOCATE CustBal_Cursor;


				--update BegBal for dummy account		  
			    EXECUTE dbo.UpdateParentCalValues @CalPeriodId = @StartPeriodID, @IsImportBegBal = 0;

		        --Step 5: Insert the rest 11 periods of each CustBalance record	
                SET @PeriodID = @StartPeriodID + 1;
                SELECT @PeriodIDMax = MAX(Period) FROM dbo.Voucher WHERE ImportType IS NULL OR ImportType != 3;
                
                IF ISNULL(@PeriodIDMax, 0) < 12
                BEGIN
                    SET @PeriodIDMax = 12;
                END;

                WHILE @PeriodID <= @PeriodIDMax
                    BEGIN	
                        DECLARE CustBal_Cursor CURSOR
                        FOR
                            SELECT  cb.AcctCode ,
                                    CustomerCode ,
                                    SUM(EndBal) ,
                                    IsLeaf ,
                                    Direction ,
                                    SUM(EndDebitBal) ,
                                    SUM(EndCreditBal)
                            FROM    [dbo].[CustBalance] cb
                                    INNER JOIN [dbo].[EnterpriseAccount] a ON cb.AcctCode = a.AcctCode
                            WHERE   PeriodId = @PeriodID - 1 -- get last period EndBal, EndDebitBal,EndCreditBal as current period Beg columns value
                            GROUP BY cb.AcctCode, CustomerCode, IsLeaf, Direction;
                        OPEN CustBal_Cursor;
                        FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf, @Direction,@EndDebitBal, @EndCreditBal;
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                --1. Cal Debit and Credit of current period 
                                IF ( @isLeaf = 1 AND @CustomerCode <> '')
                                    OR ( @isLeaf = 0 
                                         AND EXISTS ( SELECT 1 FROM dbo.Voucher WHERE AcctCode LIKE @AcctCode + '@%' AND (ImportType IS NULL OR ImportType != 3))
                                         AND @CustomerCode <> ''
                                       )
                                    BEGIN
                                        SELECT  @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END ,
                                                @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END
                                        FROM    [dbo].[Voucher]
                                        WHERE   AcctCode LIKE @AcctCode + '%'
                                                AND CustomerCode = @CustomerCode
                                                AND Period = @PeriodID
												AND (ImportType IS NULL OR ImportType != 3);
                                    END;
                                ELSE
                                    BEGIN
                                        SELECT  @sumDebit = CASE WHEN SUM(Debit) IS NULL THEN 0 ELSE SUM(Debit) END ,
                                                @sumCredit = CASE WHEN SUM(Credit) IS NULL THEN 0 ELSE SUM(Credit) END
                                        FROM    [dbo].[Voucher]
                                        WHERE   AcctCode LIKE @AcctCode + '%'
                                                AND Period = @PeriodID
												AND (ImportType IS NULL OR ImportType != 3);
                                    END;
                                
                                --2. GET SUM(DebitBal) and SUM(CreditBal) of previous periods to cal YearDebitBal and YearCreditBal
                                SELECT  @sumYearDebit = CASE WHEN SUM(DebitBal) IS NULL THEN 0 ELSE SUM(DebitBal) END ,
                                        @sumYearCredit = CASE WHEN SUM(CreditBal) IS NULL THEN 0 ELSE SUM(CreditBal) END
                                FROM    [dbo].[CustBalance]
                                WHERE   AcctCode = @AcctCode
                                        AND CustomerCode = @CustomerCode
                                        AND PeriodId <= @PeriodID - 1; 
						 						   						                						   		
						   	    
						   	    -- set dummy value for current period                              						        																												        																								 ;
                                IF EXISTS(SELECT 1 FROM dbo.CustBalance WHERE AcctCode = @AcctCode AND CustomerCode = @CustomerCode 
								              AND PeriodId = @PeriodID - 1 AND IsDummy = 1)
								BEGIN
								     SET @IsDummy = 1;
								END
								ELSE
								BEGIN
								    SET @IsDummy = 0;
								END															
						   	     																													                 																        
                                --4. Insert value
                                IF @sumDebit IS NOT NULL
                                    BEGIN
                                        INSERT  [dbo].[CustBalance]
                                                ( PeriodId ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal ,
                                                  BegDebitBal ,
                                                  BegCreditBal ,
                                                  EndDebitBal ,
                                                  EndCreditBal,
                                                  IsDummy
                                                )
                                        VALUES  ( @PeriodID ,
                                                  @AcctCode ,
                                                  @CustomerCode ,
                                                  @EndBal ,
                                                  @sumDebit ,
                                                  @sumCredit ,
                                                  @sumYearDebit + @sumDebit ,
                                                  @sumYearCredit + @sumCredit ,
                                                  ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction ,
                                                  @EndDebitBal ,
                                                  @EndCreditBal ,
                                                  CASE WHEN @Direction = 1 THEN ( @EndBal * @Direction  + @sumDebit - @sumCredit ) * @Direction
                                                       ELSE 0 END ,
                                                  CASE WHEN @Direction = -1 THEN ( @EndBal * @Direction + @sumDebit - @sumCredit ) * @Direction
                                                       ELSE 0 END,
                                                  @IsDummy     
                                                );
                                    END;
                                ELSE
                                    BEGIN
                                        INSERT  [dbo].[CustBalance]
                                                ( PeriodId ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal ,
                                                  BegDebitBal ,
                                                  BegCreditBal ,
                                                  EndDebitBal ,
                                                  EndCreditBal,
                                                  IsDummy
                                                )
                                        VALUES  ( @PeriodID ,
                                                  @AcctCode ,
                                                  @CustomerCode ,
                                                  @EndBal ,
                                                  0 ,
                                                  0 ,
                                                  @sumYearDebit ,
                                                  @sumYearCredit ,
                                                  @EndBal ,
                                                  @EndDebitBal ,
                                                  @EndCreditBal ,
                                                  CASE WHEN @Direction = 1 THEN @EndBal  ELSE 0  END ,
                                                  CASE WHEN @Direction = -1 THEN @EndBal ELSE 0 END,
                                                  @IsDummy
                                                );
                                    END;

                                FETCH NEXT FROM CustBal_Cursor INTO @AcctCode,@CustomerCode, @EndBal, @isLeaf, @Direction, @EndDebitBal, @EndCreditBal;
                            END;

                        CLOSE CustBal_Cursor;
                        DEALLOCATE CustBal_Cursor;

                        SET @PeriodID = @PeriodID + 1;
                    END;
            END;
       

	--step6: append records which period is greate than 12 to records period is 12
	--IF EXISTS (select 1 from CustBalance where PeriodID > 12)			
	--BEGIN				
	--	update dbo.CustBalance
	--	set DebitBal = DebitBal + sumcs.SumDebitBal, CreditBal = CreditBal + sumcs.SumCreditBal,
	--		YearDebitBal = YearDebitBal + sumcs.SumDebitBal,YearCreditBal = YearCreditBal + sumcs.SumCreditBal,
	--		EndBal = (EndBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction,
	--		EndDebitBal = case when a.Direction = 1 then (EndDebitBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction else 0 end,
	--		EndCreditBal = case when a.Direction = -1 then (EndCreditBal + sumcs.SumDebitBal - sumcs.SumCreditBal) * a.Direction else 0 end 
	--	from dbo.CustBalance cs
	--	inner join
	--	(
	--	select AcctCode,CustomerCode,Sum(DebitBal)SumDebitBal,Sum(CreditBal)SumCreditBal
	--	from CustBalance where PeriodID > 12 
	--	group by AcctCode,CustomerCode
	--	) sumcs 
	--	on cs.AcctCode = sumcs.AcctCode and cs.CustomerCode = sumcs.CustomerCode 			 
	--	inner join dbo.EnterpriseAccount a on cs.AcctCode = a.Code 
	--	where PeriodID = 12			   		   			 
	--END;

	--delete dbo.CustBalance where PeriodID > 12


	--Step 7: Insert Balance by CustBalance
        SELECT @PeriodID  = MIN(PeriodId) FROM dbo.CustBalance;
		
        WHILE @PeriodID <= 12
            BEGIN
                DECLARE CustBal_Cursor CURSOR
                FOR
                    SELECT  b.AcctCode ,
                            BegBal ,
                            EndBal ,
                            DebitBal ,
                            CreditBal ,
                            BegDebitBal ,
                            BegCreditBal ,
                            EndDebitBal ,
                            EndCreditBal,
							b.IsDummy
                    FROM    [dbo].[CustBalance] b
                            INNER JOIN [dbo].[EnterpriseAccount] a ON b.AcctCode = a.AcctCode
                    WHERE   PeriodId = @PeriodID
                            AND CustomerCode = '';

                OPEN CustBal_Cursor;
                FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @BegBal,
                    @EndBal, @sumDebit, @sumCredit, @BegDebitBal,
                    @BegCreditBal, @EndDebitBal, @EndCreditBal,@IsDummy;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
					                      
						SELECT  @sumYearDebit = ISNULL(SUM(DebitBal),0),
                                @sumYearCredit = ISNULL(SUM(CreditBal),0) 
                        FROM    [dbo].[CustBalance]
                        WHERE   AcctCode = @AcctCode
                                AND CustomerCode = ''
                                AND PeriodId <= @PeriodID - 1;

                        IF @sumDebit IS NOT NULL
                            BEGIN
                                INSERT  [dbo].[Balance]
                                        ( PeriodId ,
                                          AcctCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal ,
                                          BegDebitBal ,
                                          BegCreditBal ,
                                          EndDebitBal ,
                                          EndCreditBal,
										  IsDummy
                                        )
                                VALUES  ( @PeriodID ,
                                          @AcctCode ,
                                          @BegBal ,
                                          @sumDebit ,
                                          @sumCredit ,
                                          @sumYearDebit + @sumDebit ,
                                          @sumYearCredit + @sumCredit ,
                                          @EndBal ,
                                          @BegDebitBal ,
                                          @BegCreditBal ,
                                          @EndDebitBal ,
                                          @EndCreditBal,
										  @IsDummy
                                        );
                            END;
                        ELSE
                            BEGIN
                                INSERT  [dbo].[Balance]
                                        ( PeriodId ,
                                          AcctCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal ,
                                          BegDebitBal ,
                                          BegCreditBal ,
                                          EndDebitBal ,
                                          EndCreditBal,
										  IsDummy
                                        )
                                VALUES  ( @PeriodID ,
                                          @AcctCode ,
                                          @BegBal ,
                                          0 ,
                                          0 ,
                                          @sumYearDebit ,
                                          @sumYearCredit ,
                                          @EndBal ,
                                          @BegDebitBal ,
                                          @BegCreditBal ,
                                          @EndDebitBal ,
                                          @EndCreditBal,
										  @IsDummy
                                        );
                            END;

                        FETCH NEXT FROM CustBal_Cursor INTO @AcctCode, @BegBal,
                            @EndBal, @sumDebit, @sumCredit, @BegDebitBal,
                            @BegCreditBal, @EndDebitBal, @EndCreditBal,@IsDummy;
                    END;

                CLOSE CustBal_Cursor;
                DEALLOCATE CustBal_Cursor;

                SET @PeriodID = @PeriodID + 1;
            END;
    
    END;


GO
/****** Object:  StoredProcedure [dbo].[UpdateParentCalValues]    Script Date: 14/06/2017 11:01:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Bin LI>
-- Description:	<CustBalance�и���Ŀ��dummy�Ļ�������BegBal���������ӿ�Ŀ�ۼӡ������μ���������ۼƷ�������ĩ������>
-- Parmeters:   1. @CalPeriodId: ������ڼ�
--              2. @IsImportBegBal �Ƿ�ֻ����BegBal��1���ǣ�0������
-- =============================================
CREATE PROCEDURE [dbo].[UpdateParentCalValues] 
    @CalPeriodId INT,
	@IsImportBegBal INT  
AS
    BEGIN

        SET NOCOUNT ON;
        DECLARE @AcctCode NVARCHAR(50);
		DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @BegBal FLOAT;
		DECLARE @BegDebitBal FLOAT;
	    DECLARE @BegCreditBal FLOAT;
        DECLARE @Direction INT;
		DECLARE @sumYearDebit FLOAT;
        DECLARE @sumYearCredit FLOAT;
		DECLARE @MaxAcctLevle INT;
		DECLARE @PeriodId INT;
        DECLARE @ChildTbValues TABLE (
		  AcctCode NVARCHAR(50),
		  ParentCode NVARCHAR(50),
		  CustomerCode NVARCHAR(50),
		  AcctLevel INT,
		  BegBal FLOAT,
		  BegDebitBal FLOAT,
		  BegCreditBal FLOAT,
		  PeriodId INT 
		)
           
		  --1. atemp: children accounts of dummy acount
          INSERT  INTO @ChildTbValues
                    ( AcctCode ,
                      ParentCode ,
                      CustomerCode ,
                      AcctLevel ,
                      BegBal ,
                      BegDebitBal ,
                      BegCreditBal,
					  PeriodId
		            )
                    SELECT  atemp.AcctCode ,
                            atemp.ParentCode ,
                            atemp.CustomerCode ,
                            atemp.AcctLevel ,
                            b1.BegBal ,
                            b1.BegDebitBal ,
                            b1.BegCreditBal,
							b1.PeriodId
                    FROM    dbo.CustBalance b1
                            INNER JOIN ( SELECT a1.AcctCode ,
                                                a1.ParentCode ,
                                                b2.CustomerCode ,
                                                a1.AcctLevel
                                         FROM   dbo.EnterpriseAccount a1
                                                INNER JOIN dbo.CustBalance b2 ON a1.ParentCode = b2.AcctCode
                                         WHERE  b2.IsDummy = 1
                                       ) atemp ON b1.AcctCode = atemp.AcctCode
                    WHERE   b1.PeriodId = @CalPeriodId;  

          SELECT    @MaxAcctLevle = MAX(AcctLevel)
          FROM      @ChildTbValues;

          WHILE @MaxAcctLevle > 1
            BEGIN
                DECLARE DummyParent_Cursor CURSOR
                FOR
                    SELECT  ParentCode ,                       
                            ISNULL(SUM(BegBal), 0) AS SumBegBal ,
                            ISNULL(SUM(BegDebitBal), 0) AS SumBegDebitBal ,
                            ISNULL(SUM(BegCreditBal), 0) AS SumBegCreditBal,
							CustomerCode,
						    PeriodId
                    FROM    @ChildTbValues
                    WHERE   AcctLevel = @MaxAcctLevle
                    GROUP BY ParentCode ,
                            CustomerCode,
							PeriodId; 

                OPEN DummyParent_Cursor;
                FETCH NEXT FROM DummyParent_Cursor INTO @AcctCode, @BegBal,
                    @BegDebitBal, @BegCreditBal, @CustomerCode,@PeriodId;
                WHILE @@FETCH_STATUS = 0
                    BEGIN

                        SELECT  @Direction = Direction
                        FROM    dbo.EnterpriseAccount
                        WHERE   AcctCode = @AcctCode;
                        IF @IsImportBegBal = 0
                            BEGIN
                                SET @BegBal = ( @BegDebitBal - @BegCreditBal )  * @Direction;
                            END; 


                        SELECT  @sumYearDebit = ISNULL(SUM(DebitBal), 0) ,
                                @sumYearCredit = ISNULL(SUM(CreditBal), 0)
                        FROM    [dbo].[CustBalance]
                        WHERE   AcctCode = @AcctCode
                                AND CustomerCode = @CustomerCode
                                AND PeriodId <= @CalPeriodId;  


                        UPDATE  dbo.CustBalance
                        SET     BegBal = @BegBal ,
                                BegDebitBal = CASE WHEN @Direction = 1
                                                        AND @IsImportBegBal = 1
                                                   THEN @BegBal
                                                   WHEN @IsImportBegBal = 0
                                                   THEN @BegDebitBal
                                                   ELSE 0
                                              END ,
                                BegCreditBal = CASE WHEN @Direction = -1
                                                         AND @IsImportBegBal = 1
                                                    THEN @BegBal
                                                    WHEN @IsImportBegBal = 0
                                                    THEN @BegCreditBal
                                                    ELSE 0
                                               END ,
                                YearDebitBal = @sumYearDebit ,
                                YearCreditBal = @sumYearCredit ,
                                EndBal = ( @BegBal * @Direction + DebitBal
                                           - CreditBal ) * @Direction ,
                                EndDebitBal = CASE WHEN @Direction = 1
                                                   THEN ( @BegBal * @Direction
                                                          + DebitBal
                                                          - CreditBal )
                                                        * @Direction
                                                   ELSE 0
                                              END ,
                                EndCreditBal = CASE WHEN @Direction = -1
                                                    THEN ( @BegBal
                                                           * @Direction
                                                           + DebitBal
                                                           - CreditBal )
                                                         * @Direction
                                                    ELSE 0
                                               END
                        FROM    dbo.CustBalance
                        WHERE   AcctCode = @AcctCode
                                AND CustomerCode = @CustomerCode
                                AND PeriodId = @CalPeriodId;
                        

						UPDATE @ChildTbValues SET
                        BegBal = @BegBal ,
                        BegDebitBal = CASE WHEN @Direction = 1
                                                AND @IsImportBegBal = 1
                                            THEN @BegBal
                                            WHEN @IsImportBegBal = 0
                                            THEN @BegDebitBal
                                            ELSE 0
                                        END ,
                        BegCreditBal = CASE WHEN @Direction = -1
                                                    AND @IsImportBegBal = 1
                                            THEN @BegBal
                                            WHEN @IsImportBegBal = 0
                                            THEN @BegCreditBal
                                            ELSE 0
                                        END 
					    FROM @ChildTbValues
						WHERE AcctCode = @AcctCode
                              AND CustomerCode = @CustomerCode
                              AND PeriodId = @CalPeriodId;

                        FETCH NEXT FROM DummyParent_Cursor INTO @AcctCode,
                            @BegBal, @BegDebitBal, @BegCreditBal,
                            @CustomerCode,@PeriodId; 
                    END; 
			  
                CLOSE DummyParent_Cursor;
                DEALLOCATE DummyParent_Cursor;
                SET @MaxAcctLevle = @MaxAcctLevle - 1;

            END;
          
    END;
GO

CREATE PROCEDURE [dbo].[UpdateNonLeafVocuhers] 
     @PeriodIdParam INT,
	 @UserId VARCHAR(128)
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @NewAccountCode NVARCHAR(50);
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @AcctLevel INT;
        DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @BegBal NUMERIC(18, 3);
        DECLARE @BegBalChildren NUMERIC(18, 3);
        DECLARE @Direction INT;
        DECLARE @Symbol NVARCHAR(50);
		DECLARE @AccountSetId VARCHAR(128);		

        SET @Symbol = '@';
		SELECT TOP(1) @AccountSetId = EnterpriseAccountSetID FROM dbo.EnterpriseAccount WHERE EnterpriseAccountSetID IS NOT NULL
	--Step 1: Update CustBalance table to add balance record for the new-added accounts
        DECLARE Account_Cursor CURSOR
        FOR
            SELECT DISTINCT
                    v.AcctCode ,
                    a.AcctLevel
            FROM    dbo.Voucher v
                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                    INNER JOIN dbo.CustBalance cb ON a.AcctCode = cb.AcctCode
            WHERE   a.IsLeaf = 0 AND cb.PeriodId = @PeriodIdParam
            ORDER BY a.AcctLevel DESC ,
                    v.AcctCode;
        OPEN Account_Cursor;
        FETCH NEXT FROM Account_Cursor INTO @AcctCode, @AcctLevel;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                DECLARE CustomerCode_Cursor CURSOR
                FOR
                    SELECT  CustomerCode ,
                            BegBal
                    FROM    dbo.CustBalance
                    WHERE   AcctCode = @AcctCode AND PeriodId = @PeriodIdParam;
                OPEN CustomerCode_Cursor;
                FETCH NEXT FROM CustomerCode_Cursor INTO @CustomerCode,
                    @BegBal;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        SELECT TOP 1 @BegBalChildren = ISNULL(SUM(a.Direction * cb.BegBal), 0)
                        FROM    CustBalance cb
                                INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode
                        WHERE   PeriodId = @PeriodIdParam
                                AND a.ParentCode = @AcctCode
                                AND CustomerCode = @CustomerCode;

                        SELECT  @Direction = a.Direction
                        FROM    dbo.CustBalance cb
                                INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode
                        WHERE   PeriodId = @PeriodIdParam
                                AND a.AcctCode = @AcctCode
                                AND CustomerCode = @CustomerCode;
                        
                        IF NOT EXISTS ( SELECT  1
                                        FROM    dbo.CustBalance
                                        WHERE   PeriodId = @PeriodIdParam
                                                AND AcctCode = @AcctCode + @Symbol
                                                AND CustomerCode = @CustomerCode )
                            BEGIN
                                INSERT  dbo.CustBalance
                                        ( PeriodId ,
                                          AcctCode ,
                                          CustomerCode ,
                                          BegBal
							            )
                                VALUES  ( @PeriodIdParam ,
                                          @AcctCode + @Symbol ,
                                          @CustomerCode ,
                                          @BegBal - @Direction
                                          * @BegBalChildren
							            );
                            END;
                        										                                              
                        FETCH NEXT FROM CustomerCode_Cursor INTO @CustomerCode,  @BegBal;
                    END;
                CLOSE CustomerCode_Cursor;
                DEALLOCATE CustomerCode_Cursor;

                FETCH NEXT FROM Account_Cursor INTO @AcctCode, @AcctLevel;
            END;
        CLOSE Account_Cursor;
        DEALLOCATE Account_Cursor;

	--Step 2: Update EnterpriseAccount/Voucher table with special symbol
        DECLARE NewAccount_Cursor CURSOR
        FOR
            SELECT DISTINCT
                    v.AcctCode
            FROM    dbo.Voucher v
                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode					
            WHERE   a.IsLeaf = 0 AND v.Period = @PeriodIdParam;
        OPEN NewAccount_Cursor;
        FETCH NEXT FROM NewAccount_Cursor INTO @NewAccountCode;
        WHILE @@FETCH_STATUS = 0
            BEGIN		        			              
                IF NOT EXISTS ( SELECT  1 FROM dbo.EnterpriseAccount WHERE   AcctCode = @NewAccountCode + @Symbol )
                    BEGIN
				    UPDATE dbo.EnterpriseAccount SET StdCode = NULL WHERE AcctCode = @NewAccountCode

				  --Step 2.1: Update EnterpriseAccount table to add new accounts with special symbol
                        INSERT  dbo.EnterpriseAccount
                                ( AcctCode ,
                                  Name ,
                                  FullName ,
                                  ParentCode ,
                                  AcctProp ,
                                  Direction ,
                                  StdCode ,
                                  IsLeaf ,
                                  AcctLevel,
                                  IsActive,
								  EnterpriseAccountSetID,
								  CreatorID,
								  UpdatorID,
								  CreateTime,
								  UpdateTime
							    )
                                SELECT  AcctCode + @Symbol ,
                                        Name ,
                                        FullName ,
                                        AcctCode ,
                                        AcctProp ,
                                        Direction ,
                                        NULL,  -- set stdcode value of new enterprise as NULL
                                        1 ,
                                        AcctLevel + 1,
                                        1,
										@AccountSetId,
										@UserId,
										@UserId,
										GETDATE(),
										GETDATE()
                                FROM    dbo.EnterpriseAccount
                                WHERE   AcctCode = @NewAccountCode;
                    END;
				--Step 2.2: Update Voucher table to add special symbol on vouchers of non-leaf accounts
                UPDATE  dbo.Voucher
                SET     AcctCode = AcctCode + @Symbol
                WHERE   AcctCode = @NewAccountCode;
			     			  
                FETCH NEXT FROM NewAccount_Cursor INTO @NewAccountCode;				       			
            END; 
        CLOSE NewAccount_Cursor;
        DEALLOCATE NewAccount_Cursor;

    END;




GO
CREATE PROCEDURE [dbo].[InitVoucherMain] 
 @creatorId VARCHAR(128)
AS 
BEGIN
	truncate table VoucherMain
	
	insert into VoucherMain (Period, [Group], VID, VoucherCount, AcctCodeCount, Amount, AmountCredit,CreateTime,UpdateTime,CreatorID)
	select [Period], [Group], [VID], count(1) as vc, count(distinct acctcode) as ac, sum(Debit) Amount, sum(Credit) AmountCredit,GETDATE(),GETDATE(),@creatorId
	from [Voucher] group by [Period], [Group], [VID] 
END


GO
CREATE PROCEDURE [dbo].[InitAccountIsInVoucher]
AS
BEGIN
	
	SET NOCOUNT ON;

	--Set IsInVoucher = 1 if account code is in C_Voucher,otherwise set it as 0
	--If C_Accout has any record which IsInVoucher is null, means the project is old version before admin module v2.1
	update dbo.EnterpriseAccount set IsInVoucher = 0
    update dbo.EnterpriseAccount set IsInVoucher = 1 
	where AcctCode in
	(
	  select distinct v.AcctCode from dbo.Voucher v where v.AcctCode is not null
	)

    
END
GO
/* ******************************************************************************************************************************* */
/* CREATE NEW TABLE CALLED "Account" */
IF NOT EXISTS (select 1 from sys.tables where name = 'Account' and type = 'U')
BEGIN
CREATE TABLE [dbo].[Account](
	[ID] [varchar](128) NOT NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ParentCode] [nvarchar](50) NULL,
	[FullName] [nvarchar](200) NULL,
	[AcctProp] [int] NULL,
	[SubProp] [int] NULL,
	[AcctLevel] [int] NULL,
	[Direction] [int] NOT NULL,
	[IsLeaf] [smallint] NOT NULL DEFAULT ((1)),
	[RuleType] [int] NOT NULL,
	[IsActive] [smallint] NOT NULL DEFAULT ((1)),
	[EnglishName] [nvarchar](200) NULL,
	[IndustryID] [varchar](128) NOT NULL
	PRIMARY KEY (ID)
	);
END
GO
---begin Rita 2017-05-05 MSSql V1.0.0.0 Model--------------------------------------
IF NOT EXISTS (select 1 from sys.tables where name = 'EntriesCheckResult')
BEGIN
CREATE TABLE [dbo].EntriesCheckResult(
	[ID] [varchar](128) NOT NULL,
	[ModelID] [varchar](128) NOT NULL,
	[Code] [varchar](20) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](300) NULL,
	[VoucherNum] [int] NOT NULL default 0,
	[EntriesNum] [int] NOT NULL default 0,
	[DebitRelevantAmt] [numeric](18,3) NOT NULL default 0,
	[CreditRelevantAmt] [numeric](18,3) NOT NULL default 0,
	[DebitTotalAmt] [numeric](18,3) NOT NULL default 0,
	[CreditTotalAmt] [numeric](18,3) NOT NULL default 0,
	[BigVoucherNum] [int] NOT NULL default 0,
	[BigEntriesNum] [int] NOT NULL default 0,
	[BigDebitRelevantAmt] [numeric](18,3) NOT NULL default 0,
	[BigCreditRelevantAmt] [numeric](18,3) NOT NULL default 0,
	[BigDebitTotalAmt] [numeric](18,3) NOT NULL default 0,
	[BigCreditTotalAmt] [numeric](18,3) NOT NULL default 0,
	[EntryDoubts] int NOT NULL default 0,
	[BigEntryDoubts] int NOT NULL default 0,
	[VoucherDoubts] int NOT NULL default 0,
	[BigVoucherDoubts] int NOT NULL default 0,
	[IsFiltered] [int] NOT NULL default 0,
	[ServiceTypeID] [varchar](128) NOT NULL,
	[CategoryID] [varchar](128) NOT NULL,
	[Type] [int] NOT NULL,
	[IsFilter] [int] NOT NULL default 0,
	[IsShow] [int] NOT NULL default 1,
	[Period] [int] NOT NULL,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
 );
END
GO

IF NOT EXISTS (select 1 from sys.tables where name = 'EntriesCheckDetailResult')
BEGIN
CREATE TABLE [dbo].EntriesCheckDetailResult(
	[ID] [varchar](128) NOT NULL,
	[ResultID] [varchar](128) NOT NULL,
	[IsDoubt] [int] NOT NULL default 0,
	[IsBigVoucher] [int] NOT NULL default 0,
	[VoucherID] [varchar](128) NOT NULL,
	[VID] [nvarchar](256) NOT NULL,
	[Date] [date] NOT NULL,
	[Group] [nvarchar](50) NULL,
	[Period] [int] NULL,
	[CustomerCode] [nvarchar](50) NULL,
	[CustomerName] [nvarchar](100) NULL,
	[Summary] [nvarchar](500) NULL,
	[AcctCode] [nvarchar](50) NULL,
	[StdCode] [nvarchar](50) NULL,
	[Debit] [numeric](18,3) NULL,
	[Credit] [numeric](18,3) NULL,
	[Attach] [bigint] NULL,
	[ItemID] [nvarchar](256) NULL,
	[ImportType] [int] NULL,
	[IsFiltered] [int] NOT NULL default 0,
	[IsRelevantAmt] [int] NOT NULL default 0,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
 );
END
GO

IF NOT EXISTS (select 1 from sys.tables where name = 'IndexAnalysisResult')
BEGIN
CREATE TABLE [dbo].IndexAnalysisResult(
	[ID] [varchar](128) NOT NULL,
	[ModelID] [varchar](128) NOT NULL,
	[Code] [varchar](20) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](300) NULL,
	[MonthExplain] [nvarchar](200) NULL,
	[YearExplain] [nvarchar](200) NULL,
	[ServiceTypeID] [varchar](128) NOT NULL,
	[CategoryID] [varchar](128) NOT NULL,	
	[Type] [int] NOT NULL,
	[IndexCodeTotal] [varchar](8000) NULL,
	[IsShow] [int] NOT NULL default 1,
	[IsReport] [int] NOT NULL default 0,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
 );
END
GO


IF NOT EXISTS (select 1 from sys.tables where name = 'IndexAnalysisDetailResult')
BEGIN
CREATE TABLE [dbo].IndexAnalysisDetailResult(
	[ID] [varchar](128) NOT NULL,
	[ResultID] [varchar](128) NOT NULL,
	[Year] [int] NOT NULL,
	[Period] [int] NOT NULL,
	[IndexName] [nvarchar](50) NULL,
	[Sequence] [int] NULL,
	[DisplayType] [int] NULL,
	[IsVisible] [int] NOT NULL default 1,
	[IsMainValue] [int] NOT NULL default 0,
	[IsYearValue] [int] NOT NULL default 1,
	[IsMonExp] [int] NOT NULL default 0,
	[IsYearExp] [int] NOT NULL default 0,
	[IndexResult] [varchar](50) NULL,
	[IndexCode] [varchar](8000) NULL,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
 );
END
GO

IF NOT EXISTS (select 1 from sys.tables where name = 'ModelLog')
BEGIN
CREATE TABLE [dbo].ModelLog(
	[ID] [varchar](128) NOT NULL,
	[OrganizationID] [varchar](128) NOT NULL,
	[RunNumber] [int] NOT NULL,
	[Description] [nvarchar](500) NULL,
	[SuccessNumber] [int] NOT NULL,
	[FailureNumber] [int] NOT NULL,
	[ServiceTypeID] [varchar](128) NOT NULL,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
);
END
GO

IF NOT EXISTS (select 1 from sys.tables where name = 'ModelDetailLog')
BEGIN
CREATE TABLE [dbo].ModelDetailLog(
	[ID] [varchar](128) NOT NULL,
	[ModelLogID] [varchar](128) NOT NULL,
	[Feature] [int] not null,
	[ModelID] [varchar](128) NOT NULL,
	[Code] [varchar](20) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[RunningResult] [int] NOT NULL,
	[Period] [int] NOT NULL,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	PRIMARY KEY(ID)
);
END
GO

IF NOT EXISTS (select 1 from sys.tables where name = 'ModelCategoryResult')
BEGIN
CREATE TABLE [dbo].ModelCategoryResult(
	[ID] [varchar](128) NOT NULL,
	[CategoryID] [varchar](128) NOT NULL,
	[ClientCode] [nvarchar](50) NOT NULL,
	[OrganizationID] [varchar](128) NOT NULL,
	[Category] [nvarchar](10) NOT NULL,
	[ParentID] [varchar](128) NOT NULL,
	[Sort] [int] NOT NULL default 0,
	[Quantity] [int] NOT NULL default 0,
	[CreateTime] [datetime] NOT NULL default getDate(),
	[UpdateTime] [datetime] NOT NULL default getDate(),
	[IndustryID] [varchar](128) NULL,
	[ServiceTypeID] [varchar](128) NOT NULL,
	PRIMARY KEY( ID )
 );
END
GO
---end Rita 2017-05-05 MSSql V1.0.0.0----------------------------------------------
GO
CREATE PROCEDURE [dbo].[InitAccount]
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

	
        DECLARE @Code NVARCHAR(50);
        DECLARE @Code2 NVARCHAR(50);
        DECLARE @Parent NVARCHAR(50);
        DECLARE @Name NVARCHAR(50);
        DECLARE @AccountID VARCHAR(128);
	

        UPDATE  dbo.EnterpriseAccount
        SET     ParentCode = NULL ,
                FullName = NULL ,
                AcctLevel = NULL ,
                IsLeaf = NULL

    -- Insert statements for procedure here
        DECLARE Code_Cursor CURSOR
        FOR
            SELECT  AcctCode
            FROM    [dbo].[EnterpriseAccount]
            ORDER BY AcctCode;
        OPEN Code_Cursor;
        FETCH NEXT FROM Code_Cursor INTO @Code;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			 
                UPDATE  [dbo].[EnterpriseAccount]
                SET     ParentCode = @Code
                WHERE   AcctCode LIKE @Code + '%'
                        AND AcctCode <> @Code;
			
                FETCH NEXT FROM Code_Cursor INTO @Code;
            END;

        CLOSE Code_Cursor;
        DEALLOCATE Code_Cursor;

        DECLARE @count1 INT;
        DECLARE @count2 INT;
        DECLARE @num INT;

        SET @num = 1;

	--update [dbo].[C_Account] set Parent = '';--ǧ���ƻ���ʹ��ϵͳĬ�Ϲ�������Ŀ���¼���ϵ����Ҫ�Ȱ�����Parent����Է��û������ļ�����Parent
        UPDATE  [dbo].[EnterpriseAccount]
        SET     ParentCode = NULL
        WHERE   ParentCode = '';
        UPDATE  [dbo].[EnterpriseAccount]
        SET     AcctLevel = 1
        WHERE   ParentCode IS NULL;

        SELECT  @count1 = COUNT(1)
        FROM    [dbo].[EnterpriseAccount]
        WHERE   AcctLevel IS NULL; 

        WHILE @count1 <> 0
            BEGIN 
		
                DECLARE Code1_Cursor CURSOR
                FOR
                    SELECT  AcctCode
                    FROM    [dbo].[EnterpriseAccount]
                    WHERE   AcctLevel = @num; 
                OPEN Code1_Cursor;
                FETCH NEXT FROM Code1_Cursor INTO @Code;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
			
                        UPDATE  [dbo].[EnterpriseAccount]
                        SET     AcctLevel = @num + 1
                        WHERE   ParentCode = @Code;

                        SELECT  @count2 = COUNT(1)
                        FROM    [dbo].[EnterpriseAccount]
                        WHERE   AcctCode LIKE @Code + '%'
                                AND AcctCode <> @Code; 
                        IF @count2 = 0
                            BEGIN
					-- is leaf
                                UPDATE  [dbo].[EnterpriseAccount]
                                SET     IsLeaf = 1
                                WHERE   AcctCode = @Code;
                            END;
                        ELSE
                            BEGIN
					-- is not leaf
                                UPDATE  [dbo].[EnterpriseAccount]
                                SET     IsLeaf = 0
                                WHERE   AcctCode = @Code;
                            END;


                        FETCH NEXT FROM Code1_Cursor INTO @Code;
                    END;

                CLOSE Code1_Cursor;
                DEALLOCATE Code1_Cursor;

                SET @num = @num + 1;
                SELECT  @count1 = COUNT(1)
                FROM    [dbo].[EnterpriseAccount]
                WHERE   AcctLevel IS NULL; 	 
            END;	 
			  
        UPDATE  [dbo].[EnterpriseAccount]
        SET     IsLeaf = 1
        WHERE   IsLeaf IS NULL;

        DECLARE FullName_Cursor CURSOR
        FOR
            SELECT  AcctCode ,
                    Name ,
                    ParentCode ,
                    ID
            FROM    [dbo].[EnterpriseAccount] ORDER BY AcctLevel,AcctCode;
        OPEN FullName_Cursor;
        FETCH NEXT FROM FullName_Cursor INTO @Code, @Name, @Parent, @AccountID;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			 
                IF @Parent IS NULL
                    BEGIN
                        UPDATE  [dbo].[EnterpriseAccount]
                        SET     FullName = @Name
                        WHERE   AcctCode = @Code
                                AND ID = @AccountID;
                    END;
                ELSE
                    BEGIN			
                        UPDATE  [dbo].[EnterpriseAccount]
                        SET     FullName = ( SELECT MAX(FullName)
                                             FROM   [dbo].[EnterpriseAccount]
                                             WHERE  AcctCode = @Parent
                                           ) + '-' + @Name
                        WHERE   AcctCode = @Code;
                    END;
			
                FETCH NEXT FROM FullName_Cursor INTO @Code, @Name, @Parent,
                    @AccountID;
            END;

        CLOSE FullName_Cursor;
        DEALLOCATE FullName_Cursor;

    END;

GO
CREATE PROCEDURE [dbo].[InitCustBalanceStd] 
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @PeriodID INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @BegBal FLOAT;
        DECLARE @sumDebit FLOAT;
        DECLARE @sumCredit FLOAT;
        DECLARE @sumYearDebit FLOAT;
        DECLARE @sumYearCredit FLOAT;
        DECLARE @EndBal FLOAT;
        DECLARE @CurrAcctLevel INT;
        DECLARE @AcctDirectionDiff INT;
        DECLARE @StdDirection INT;

	--Step 1: clean up C_CustBalanceStd
        TRUNCATE TABLE dbo.CustBalanceStd;

	--Step 2: calculate the trial balance for leaf node
        DECLARE LeafNode_Cursor CURSOR
        FOR
            SELECT  cb.PeriodId ,
                    a.StdCode ,
                    cb.CustomerCode ,
                    SUM(BegBal * sa.Direction * a.Direction) BegBal ,
                    SUM(DebitBal) DebitBal ,
                    SUM(CreditBal) CreditBal ,
                    SUM(YearDebitBal) YearDebitBal ,
                    SUM(YearCreditBal) YearCreditBal ,
                    SUM(EndBal * sa.Direction * a.Direction) EndBal
            FROM    dbo.CustBalance cb
                    INNER JOIN dbo.EnterpriseAccount a ON cb.AcctCode = a.AcctCode
                    INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code
            WHERE   a.StdCode IS NOT NULL
                    AND a.IsLeaf = 1
            GROUP BY cb.PeriodId ,
                    a.StdCode ,
                    cb.CustomerCode
            ORDER BY cb.PeriodId ,
                    a.StdCode ,
                    cb.CustomerCode;
        OPEN LeafNode_Cursor;
        FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode,
            @CustomerCode, @BegBal, @sumDebit, @sumCredit, @sumYearDebit,
            @sumYearCredit, @EndBal;
        WHILE @@FETCH_STATUS = 0
            BEGIN                            								
                INSERT  CustBalanceStd
                        ( PeriodID ,
                          AcctCode ,
                          CustomerCode ,
                          BegBal ,
                          DebitBal ,
                          CreditBal ,
                          YearDebitBal ,
                          YearCreditBal ,
                          EndBal                     
                        )
                VALUES  ( @PeriodID ,
                          @AcctCode ,
                          @CustomerCode ,
                          @BegBal ,
                          @sumDebit ,
                          @sumCredit ,
                          @sumYearDebit ,
                          @sumYearCredit ,
                          @EndBal					                                                      
                        );
                FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode,
                    @CustomerCode, @BegBal, @sumDebit, @sumCredit,
                    @sumYearDebit, @sumYearCredit, @EndBal;
            END;

        CLOSE LeafNode_Cursor;
        DEALLOCATE LeafNode_Cursor;

	--Step 3: get the maxium level of the standard account based trial balance
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.CustBalanceStd cbs
                INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code;

	--Step 4: calculate trial balance for all the parent standard account based on the value of leaf node
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodID ,
                            sa.ParentCode ,
                            CustomerCode ,
                            SUM(sa.Direction * BegBal) BegBal ,
                            SUM(DebitBal) DebitBal ,
                            SUM(CreditBal) CreditBal ,
                            SUM(YearDebitBal) YearDebitBal ,
                            SUM(YearCreditBal) YearCreditBal ,
                            SUM(sa.Direction * EndBal) EndBal
                    FROM    dbo.CustBalanceStd cbs
                            INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code
                    WHERE   sa.ParentCode IS NOT NULL
                            AND sa.AcctLevel = @CurrAcctLevel
                    GROUP BY PeriodID ,
                            sa.ParentCode ,
                            CustomerCode
                    ORDER BY PeriodID ,
                            sa.ParentCode ,
                            CustomerCode;

                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode,
                    @CustomerCode, @BegBal, @sumDebit, @sumCredit,
                    @sumYearDebit, @sumYearCredit, @EndBal;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        IF @CustomerCode = ''
                            BEGIN
                                SELECT  @StdDirection = Direction
                                FROM    dbo.StandardAccount
                                WHERE   Code = @AcctCode;
                                INSERT  dbo.CustBalanceStd
                                        ( PeriodID ,
                                          AcctCode ,
                                          CustomerCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal
                                        )
                                VALUES  ( @PeriodID ,
                                          @AcctCode ,
                                          @CustomerCode ,
                                          @StdDirection * @BegBal ,
                                          @sumDebit ,
                                          @sumCredit ,
                                          @sumYearDebit ,
                                          @sumYearCredit ,
                                          @StdDirection * @EndBal
                                        );
                            END;
                        FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID,
                            @AcctCode, @CustomerCode, @BegBal, @sumDebit,
                            @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal;
                    END;

                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;
        
      
     --step 5: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal
        UPDATE cbt
		SET cbt.BegDebitBal = CASE WHEN sta.Direction = 1 THEN cbt.BegBal ELSE 0 END,
			cbt.BegCreditBal = CASE WHEN sta.Direction = -1 THEN cbt.BegBal ELSE 0 END,
			cbt.EndDebitBal = CASE WHEN sta.Direction = 1 THEN cbt.EndBal ELSE 0 END,    
			cbt.EndCreditBal = CASE WHEN sta.Direction = -1 THEN cbt.EndBal ELSE 0 END
		FROM dbo.CustBalanceStd cbt
		INNER JOIN dbo.StandardAccount sta ON cbt.AcctCode = sta.Code             

    END;      

GO
CREATE PROCEDURE [dbo].[InitCarryover]
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @MaxAcctProp INT;
        DECLARE @GainLossAcctProp INT; --AcctProp of Gain & Loss accounts
        DECLARE @ProfitStdCode NVARCHAR(50); --Standard account code of Current Year Profit
        DECLARE @ProfitAcctCode NVARCHAR(50); --Account code of Current Year Profit
        DECLARE @Period INT;
        DECLARE @Group NVARCHAR(50); --Voucher type
        DECLARE @VID NVARCHAR(256);	--Voucher number
        DECLARE @PeriodDetails INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CarryoverDebit NUMERIC(18, 3);
        DECLARE @CarryoverCredit NUMERIC(18, 3);
        DECLARE @YearCarryoverDebit NUMERIC(18, 3);
        DECLARE @YearCarryoverCredit NUMERIC(18, 3);
        DECLARE @CurrAcctLevel INT;

	--Step 1: Reset Carryover column of C_Balance table
        UPDATE  dbo.Balance
        SET     CarryoverDebit = 0 ,
                CarryoverCredit = 0 ,
                YearCarryoverDebit = 0 ,
                YearCarryoverCredit = 0;

	--Step 2: Get the accounting rule (old 2001/new 2007) in current project
        SELECT  @MaxAcctProp = MAX(AcctProp)
        FROM    dbo.StandardAccount; 
        IF @MaxAcctProp = 5
            BEGIN
			--old rule 2001
                SET @GainLossAcctProp = 5;
                SET @ProfitStdCode = '3131';
            END;
        ELSE
            BEGIN
			--new rule 2007
                SET @GainLossAcctProp = 6;
                SET @ProfitStdCode = '4103';
            END;

	--Step 3: Get all the accounts per standard account code of current year profits
        SELECT  @ProfitAcctCode = AcctCode
        FROM    dbo.EnterpriseAccount
        WHERE   StdCode = @ProfitStdCode;

        DECLARE ProfitAcct_Cursor CURSOR
        FOR
            SELECT  AcctCode
            FROM    dbo.EnterpriseAccount
            WHERE   StdCode = @ProfitStdCode;
        OPEN ProfitAcct_Cursor;
        FETCH NEXT FROM ProfitAcct_Cursor INTO @ProfitAcctCode;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			--Step 3.1: Get Vouchers by AcctCode
                DECLARE Voucher_Cursor CURSOR
                FOR
                    SELECT DISTINCT
                            Period ,
                            [Group] ,
                            VID
                    FROM    dbo.Voucher
                    WHERE   AcctCode = @ProfitAcctCode
                    ORDER BY Period ,
                            [Group] ,
                            VID;
                OPEN Voucher_Cursor;
                FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
					--Step 3.1.1: Get Voucher Details by Voucher Period, Group, VID
					--Maybe gaint voucher
                        IF EXISTS ( SELECT  1
                                    FROM    dbo.Voucher v
                                            INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                            INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,
                                                              a.StdCode) = sa.Code
                                    WHERE   v.Period = @Period
                                            AND v.[Group] = @Group
                                            AND v.VID = @VID
                                            AND sa.Code <> @ProfitStdCode
                                            AND sa.AcctProp <> @GainLossAcctProp )
                            BEGIN
                                DECLARE VoucherDetails_Cursor CURSOR
                                FOR
                                    SELECT  v.Period ,
                                            v.AcctCode ,
                                            SUM(Debit) CarryoverDebit ,
                                            SUM(Credit) CarryoverCredit
                                    FROM    dbo.Voucher v
                                            INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                            INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,
                                                              a.StdCode) = sa.Code
                                    WHERE   v.Period = @Period
                                            AND v.[Group] = @Group
                                            AND v.VID = @VID
                                            AND sa.AcctProp = @GainLossAcctProp
                                            AND ( CASE a.Direction
                                                    WHEN 1 THEN Debit
                                                    ELSE Credit
                                                  END ) = 0
                                    GROUP BY v.Period ,
                                            v.AcctCode ,
                                            a.Direction;
                                OPEN VoucherDetails_Cursor;
                                FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                    @AcctCode, @CarryoverDebit,
                                    @CarryoverCredit;
                                WHILE @@FETCH_STATUS = 0
                                    BEGIN
                                        UPDATE  dbo.Balance
                                        SET     CarryoverDebit = CarryoverDebit + @CarryoverDebit ,
                                                CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                                YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit ,
                                                YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit
                                        WHERE   PeriodID = @PeriodDetails
                                                AND AcctCode = @AcctCode;
                                        FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                            @AcctCode, @CarryoverDebit,
                                            @CarryoverCredit;
                                    END;
                                CLOSE VoucherDetails_Cursor;
                                DEALLOCATE VoucherDetails_Cursor;
                            END;
					--Maybe normal voucher
                        ELSE
                            BEGIN
                                DECLARE VoucherDetails_Cursor CURSOR
                                FOR
                                    SELECT  v.Period ,
                                            v.AcctCode ,
                                            CASE a.Direction
                                              WHEN 1 THEN 0
                                              ELSE SUM(Debit) - SUM(Credit)
                                            END CarryoverDebit ,
                                            CASE a.Direction
                                              WHEN 1
                                              THEN SUM(Credit) - SUM(Debit)
                                              ELSE 0
                                            END CarryoverCredit
                                    FROM    dbo.Voucher v
                                            INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                            INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code
                                    WHERE   v.Period = @Period
                                            AND v.[Group] = @Group
                                            AND v.VID = @VID
                                            AND sa.AcctProp = @GainLossAcctProp
                                    GROUP BY v.Period ,
                                            v.AcctCode ,
                                            a.Direction;
                                OPEN VoucherDetails_Cursor;
                                FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                    @AcctCode, @CarryoverDebit,
                                    @CarryoverCredit;
                                WHILE @@FETCH_STATUS = 0
                                    BEGIN
                                        UPDATE  dbo.Balance
                                        SET     CarryoverDebit = CarryoverDebit + @CarryoverDebit ,
                                                CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                                YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit ,
                                                YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit
                                        WHERE   PeriodID = @PeriodDetails
                                                AND AcctCode = @AcctCode;

                                        FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                            @AcctCode, @CarryoverDebit,
                                            @CarryoverCredit;
                                    END;
                                CLOSE VoucherDetails_Cursor;
                                DEALLOCATE VoucherDetails_Cursor;
                            END;
                        FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group,
                            @VID;
                    END;
                CLOSE Voucher_Cursor;
                DEALLOCATE Voucher_Cursor;

                FETCH NEXT FROM ProfitAcct_Cursor INTO @ProfitAcctCode;
            END;

        CLOSE ProfitAcct_Cursor;
        DEALLOCATE ProfitAcct_Cursor;

	--Step 4: Calculate the accumulated amount of current year profits
        DECLARE AccumulatedProfit_Cursor CURSOR
        FOR
            SELECT  PeriodID ,
                    b.AcctCode
            FROM    dbo.Balance b
                    INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
                    INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code
            WHERE   sa.AcctProp = @GainLossAcctProp;
        OPEN AccumulatedProfit_Cursor;
        FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; 
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT  @YearCarryoverDebit = SUM(CarryoverDebit) ,
                        @YearCarryoverCredit = SUM(CarryoverCredit)
                FROM    dbo.Balance
                WHERE   PeriodID <= @Period
                        AND AcctCode = @AcctCode;
                UPDATE  dbo.Balance
                SET     YearCarryoverDebit = @YearCarryoverDebit ,
                        YearCarryoverCredit = @YearCarryoverCredit
                WHERE   PeriodID = @Period
                        AND AcctCode = @AcctCode;

                FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period,
                    @AcctCode;
            END;

        CLOSE AccumulatedProfit_Cursor;
        DEALLOCATE AccumulatedProfit_Cursor;

	--Step 5: Calculate the amount of all parent accounts
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.EnterpriseAccount;
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodID ,
                            a.ParentCode ,
                            SUM(CarryoverDebit) CarryoverDebit ,
                            SUM(CarryoverCredit) CarryoverCredit ,
                            SUM(YearCarryoverDebit) YearCarryoverDebit ,
                            SUM(YearCarryoverCredit) YearCarryoverCredit
                    FROM    dbo.Balance b
                            INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
                    WHERE   a.ParentCode IS NOT NULL
                            AND a.AcctLevel = @CurrAcctLevel
                    GROUP BY PeriodID ,
                            a.ParentCode
                    ORDER BY PeriodID ,
                            a.ParentCode;
                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode,
                    @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit,
                    @YearCarryoverCredit;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        UPDATE  dbo.Balance
                        SET     CarryoverDebit = CarryoverDebit + @CarryoverDebit ,
                                CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                YearCarryoverDebit = YearCarryoverDebit + @YearCarryoverDebit ,
                                YearCarryoverCredit = YearCarryoverCredit + @YearCarryoverCredit
                        WHERE   PeriodID = @Period
                                AND AcctCode = @AcctCode;

                        FETCH NEXT FROM ParentNode_Cursor INTO @Period,
                            @AcctCode, @CarryoverDebit, @CarryoverCredit,
                            @YearCarryoverDebit, @YearCarryoverCredit;
                    END;
                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;

	--Step 6: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit
        UPDATE  dbo.Balance
        SET     DebitNet = a.Direction * ( DebitBal - CreditBal  + CarryoverCredit - CarryoverDebit ) ,
                CreditNet = 0 ,
                YearDebitNet = a.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) ,
                YearCreditNet = 0
        FROM    dbo.Balance b
                INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
        WHERE   a.Direction = 1;

        UPDATE  dbo.Balance
        SET     DebitNet = 0 ,
                CreditNet = a.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) ,                                            
                YearDebitNet = 0 ,
                YearCreditNet = a.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit )                                                                                               
        FROM    dbo.Balance b
                INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
        WHERE   a.Direction = -1;

    END;

GO
CREATE PROCEDURE [dbo].[InitBalanceStd] 
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE @PeriodID INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @BegBal FLOAT;
        DECLARE @sumDebit FLOAT;
        DECLARE @sumCredit FLOAT;
        DECLARE @sumYearDebit FLOAT;
        DECLARE @sumYearCredit FLOAT;
        DECLARE @EndBal FLOAT;
        DECLARE @CarryoverDebit NUMERIC(18, 3);
        DECLARE @CarryoverCredit NUMERIC(18, 3);
        DECLARE @YearCarryoverDebit NUMERIC(18, 3);
        DECLARE @YearCarryoverCredit NUMERIC(18, 3);
        DECLARE @CurrAcctLevel INT;
        DECLARE @AcctDirectionDiff INT;
        DECLARE @StdDirection INT;
        DECLARE @StdCode NVARCHAR(50);
        DECLARE @SubProp INT;
        DECLARE @Count INT;

	--Step 1: clean up C_BalanceStd
        TRUNCATE TABLE dbo.BalanceStd;

	--Step 2: calculate the trial balance for leaf node
        DECLARE LeafNode_Cursor CURSOR
        FOR
            SELECT  b.PeriodId ,
                    a.StdCode ,
                    SUM(BegBal * sa.Direction * a.Direction) BegBal ,
                    SUM(DebitBal) DebitBal ,
                    SUM(CreditBal) CreditBal ,
                    SUM(YearDebitBal) YearDebitBal ,
                    SUM(YearCreditBal) YearCreditBal ,
                    SUM(EndBal * sa.Direction * a.Direction) EndBal ,
                    SUM(CarryoverDebit) CarryoverDebit ,
                    SUM(CarryoverCredit) CarryoverCredit ,
                    SUM(YearCarryoverDebit) YearCarryoverDebit ,
                    SUM(YearCarryoverCredit) YearCarryoverCredit
            FROM    dbo.Balance b
                    INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
                    INNER JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code
            WHERE   a.StdCode IS NOT NULL
                    AND a.IsLeaf = 1
            GROUP BY b.PeriodId ,
                    a.StdCode
            ORDER BY b.PeriodId ,
                    a.StdCode;

        OPEN LeafNode_Cursor;
        FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode, @BegBal,
            @sumDebit, @sumCredit, @sumYearDebit, @sumYearCredit, @EndBal,
            @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit,
            @YearCarryoverCredit;
        WHILE @@FETCH_STATUS = 0
            BEGIN
            
                INSERT  dbo.BalanceStd
                        ( PeriodId ,
                          AcctCode ,
                          BegBal ,
                          DebitBal ,
                          CreditBal ,
                          YearDebitBal ,
                          YearCreditBal ,
                          EndBal ,
                          CarryoverDebit ,
                          CarryoverCredit ,
                          YearCarryoverDebit ,
                          YearCarryoverCredit
                        )
                VALUES  ( @PeriodID ,
                          @AcctCode ,
                          @BegBal ,
                          @sumDebit ,
                          @sumCredit ,
                          @sumYearDebit ,
                          @sumYearCredit ,
                          @EndBal ,
                          @CarryoverDebit ,
                          @CarryoverCredit ,
                          @YearCarryoverDebit ,
                          @YearCarryoverCredit
                        );
                FETCH NEXT FROM LeafNode_Cursor INTO @PeriodID, @AcctCode,
                    @BegBal, @sumDebit, @sumCredit, @sumYearDebit,
                    @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit,
                    @YearCarryoverDebit, @YearCarryoverCredit;
            END;

        CLOSE LeafNode_Cursor;
        DEALLOCATE LeafNode_Cursor;

	--Step 3: get the maxium level of the standard account based trial balance
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.BalanceStd bs
                INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code;

	--Step 4: calculate trial balance for all the parent standard account based on the value of leaf node
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodId ,
                            sa.ParentCode ,
                            SUM(sa.Direction * BegBal) BegBal ,
                            SUM(DebitBal) DebitBal ,
                            SUM(CreditBal) CreditBal ,
                            SUM(YearDebitBal) YearDebitBal ,
                            SUM(YearCreditBal) YearCreditBal ,
                            SUM(sa.Direction * EndBal) EndBal ,
                            SUM(CarryoverDebit) CarryoverDebit ,
                            SUM(CarryoverCredit) CarryoverCredit ,
                            SUM(YearCarryoverDebit) YearCarryoverDebit ,
                            SUM(YearCarryoverCredit) YearCarryoverCredit
                    FROM    dbo.BalanceStd bs
                            INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code
                    WHERE   sa.ParentCode IS NOT NULL
                            AND sa.AcctLevel = @CurrAcctLevel
                    GROUP BY PeriodId ,
                            sa.ParentCode
                    ORDER BY PeriodId ,
                            sa.ParentCode;

                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID, @AcctCode,
                    @BegBal, @sumDebit, @sumCredit, @sumYearDebit,
                    @sumYearCredit, @EndBal, @CarryoverDebit, @CarryoverCredit,
                    @YearCarryoverDebit, @YearCarryoverCredit;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        SELECT  @StdDirection = Direction
                        FROM    dbo.StandardAccount
                        WHERE   Code = @AcctCode;
                        
                        INSERT  dbo.BalanceStd
                                ( PeriodId ,
                                  AcctCode ,
                                  BegBal ,
                                  DebitBal ,
                                  CreditBal ,
                                  YearDebitBal ,
                                  YearCreditBal ,
                                  EndBal ,
                                  CarryoverDebit ,
                                  CarryoverCredit ,
                                  YearCarryoverDebit ,
                                  YearCarryoverCredit
                                )
                        VALUES  ( @PeriodID ,
                                  @AcctCode ,
                                  @StdDirection * @BegBal ,
                                  @sumDebit ,
                                  @sumCredit ,
                                  @sumYearDebit ,
                                  @sumYearCredit ,
                                  @StdDirection * @EndBal ,
                                  @CarryoverDebit ,
                                  @CarryoverCredit ,
                                  @YearCarryoverDebit ,
                                  @YearCarryoverCredit
                                );
                        FETCH NEXT FROM ParentNode_Cursor INTO @PeriodID,
                            @AcctCode, @BegBal, @sumDebit, @sumCredit,
                            @sumYearDebit, @sumYearCredit, @EndBal,
                            @CarryoverDebit, @CarryoverCredit,
                            @YearCarryoverDebit, @YearCarryoverCredit;
                    END;

                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
    END;

	--Step 5: update SubProp column of C_BalanceStd
        BEGIN
            UPDATE  dbo.BalanceStd
            SET     SubProp = bssa.SubProp
            FROM    ( SELECT    bs.PeriodId ,
                                bs.AcctCode ,
                                sa.SubProp
                      FROM      dbo.BalanceStd bs
                                INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code
                    ) bssa
            WHERE   dbo.BalanceStd.PeriodId = bssa.PeriodId
                    AND dbo.BalanceStd.AcctCode = bssa.AcctCode;
        END;

	--Step 6: update SubProp column of C_Balance
        BEGIN
            UPDATE  dbo.Balance
            SET     SubProp = bsa.SubProp
            FROM    ( SELECT    b.PeriodId ,
                                b.AcctCode ,
                                sa.SubProp
                      FROM      dbo.Balance b
                                INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
                                LEFT JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code
                    ) bsa
            WHERE   dbo.Balance.PeriodId = bsa.PeriodId
                    AND dbo.Balance.AcctCode = bsa.AcctCode;

            DECLARE Balance_Cursor CURSOR
            FOR
                SELECT  b.PeriodId ,
                        b.AcctCode                        					
                FROM    dbo.Balance b
                        INNER JOIN dbo.EnterpriseAccount a ON b.AcctCode = a.AcctCode
                        LEFT JOIN dbo.StandardAccount sa ON a.StdCode = sa.Code
                WHERE   a.StdCode = '0000';
	
            OPEN Balance_Cursor;
            FETCH NEXT FROM Balance_Cursor INTO @PeriodID, @AcctCode;
            WHILE @@FETCH_STATUS = 0
                BEGIN
				--select @Count = count(1) from C_Account where Code like @AcctCode + '%' and IsLeaf = 1 and StdCode is not null
				--IF @Count > 0
                    BEGIN
                        SELECT TOP 1
                                @StdCode = StdCode
                        FROM    dbo.EnterpriseAccount
                        WHERE   AcctCode LIKE @AcctCode + '%'
                                AND IsLeaf = 1
                                AND StdCode IS NOT NULL;

                        SELECT  @SubProp = SubProp
                        FROM    dbo.StandardAccount
                        WHERE   Code = @StdCode;
                        UPDATE  dbo.Balance
                        SET     SubProp = @SubProp
                        WHERE   PeriodID = @PeriodID
                                AND AcctCode = @AcctCode;
                    END;

                    FETCH NEXT FROM Balance_Cursor INTO @PeriodID, @AcctCode;
                END;

            CLOSE Balance_Cursor;
            DEALLOCATE Balance_Cursor;
        END;

	--Step 7: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit
        UPDATE  dbo.BalanceStd
        SET     DebitNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) ,
                CreditNet = 0 ,
                YearDebitNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) ,
                YearCreditNet = 0
        FROM    dbo.BalanceStd bs
                INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code
        WHERE   sa.Direction = 1;


        UPDATE  dbo.BalanceStd
        SET     DebitNet = 0 ,
                CreditNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) ,
                YearDebitNet = 0 ,
                YearCreditNet = sa.Direction * ( YearDebitBal - YearCreditBal  + YearCarryoverCredit - YearCarryoverDebit )
        FROM    dbo.BalanceStd bs
                INNER JOIN dbo.StandardAccount sa ON bs.AcctCode = sa.Code
        WHERE   sa.Direction = -1;

    
     --Step 8: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal
        UPDATE bs
		SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END,
			bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END,
			bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END,    
			bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END
		FROM dbo.BalanceStd bs
		INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code           


    END;      

GO
/******============================================= 
Description: �����ض�Ӧ�����������ı�׼��ĿTB
Parameters: @VoucherRemapId = 1, @CustRemapId = -1: ֻ��ȡƾ֤�ض�Ӧ������
            @VoucherRemapId = -1, @CustRemapId = 2��ֻ��ȡ�ֶ��ض�Ӧ������
			@VoucherRemapId = 1, @CustRemapId = 2�� ��ȡƾ֤�ض�Ӧ���ֶ��ض�Ӧ����
			@VoucherRemapId = -1, @CustRemapId = -1��������ȡƾ֤�ض�Ӧ��Ҳ����ȡ�ֶ��ض�Ӧ����
=============================================******/
CREATE PROCEDURE [dbo].[InitCustBalanceStdRemap]
@VoucherRemapId INT, @CustRemapId INT

AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Period INT;
        DECLARE @Periods INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @RemapDebit NUMERIC(18, 3);
        DECLARE @RemapCredit NUMERIC(18, 3);
        DECLARE @StdCodeAccount NVARCHAR(50); --ת������std account code
        DECLARE @StdCodeVoucher NVARCHAR(50); --ת������std account code
        DECLARE @CurrAcctLevel INT;
        DECLARE @StdCode NVARCHAR(50);
        DECLARE @Direction INT;
        DECLARE @YearDebitBal NUMERIC(18, 3);
        DECLARE @YearCreditBal NUMERIC(18, 3);
        DECLARE @BegBal NUMERIC(18, 3);
        DECLARE @EndBal NUMERIC(18, 3);
        DECLARE @DebitBal NUMERIC(18, 3);
        DECLARE @CreditBal NUMERIC(18, 3);
                                   
	--Step 1: Clean up CustBalanceStdManual and copy CustBalanceStd to CustBalanceStdManual
        TRUNCATE TABLE dbo.CustBalanceStdManual
        INSERT  dbo.CustBalanceStdManual
                ( PeriodID ,
                  AcctCode ,
                  CustomerCode ,
                  BegBal ,
                  DebitBal ,
                  CreditBal ,
                  YearDebitBal ,
                  YearCreditBal ,
                  EndBal
                )
                SELECT  PeriodID ,
                        AcctCode ,
                        CustomerCode ,
                        BegBal ,
                        DebitBal ,
                        CreditBal ,
                        YearDebitBal ,
                        YearCreditBal ,
                        EndBal
                FROM    dbo.CustBalanceStd;

	--Step 2: Get remapping result from C_AccountRemap and calculate new debit/credit movements
	--�����ض�Ӧ���������C_CustBalanceStdManual�б�׼��Ŀ�ġ����������
			  
        DECLARE AccountRemap_Cursor CURSOR
        FOR
            SELECT t.Period,t.AcctCode,t.CustomerCode,t.RemapDebit,t.RemapCredit,outa.StdCode AS StdCodeAccount, t.StdCodeVoucher
            FROM dbo.EnterpriseAccount outa
            INNER JOIN
            (			           									
				SELECT  ar.Period ,
				ar.AcctCode ,
				ar.CustomerCode ,
				SUM(ar.RemapDebit) AS  RemapDebit,
				SUM(ar.RemapCredit) AS RemapCredit,              
				ar.StdCode AS StdCodeVoucher
				FROM    dbo.AccountRemap ar
				INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
				WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				      OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
				GROUP BY ar.Period,ar.AcctCode, ar.CustomerCode,ar.StdCode 	-- һ��coa + stdaccont ���Լ���ƾ֤�ض�Ӧ�����ֹ��ض�Ӧ,����ҪGroup��ȡSum
            ) t
           ON outa.AcctCode = t.AcctCode 	
        OPEN AccountRemap_Cursor;
        FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode,
        		 @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount,@StdCodeVoucher;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			--1. ���ת���std account��������C_CustBalanceStdManual�еĻ���insert��
                IF NOT EXISTS ( SELECT  1
                                FROM    dbo.CustBalanceStdManual
                                WHERE   PeriodId = @Period
                                        AND AcctCode = @StdCodeVoucher
                                        AND CustomerCode = @CustomerCode )
                    BEGIN
                        SET @Periods = 1;
                        WHILE @Periods < = 12
                            BEGIN
                                INSERT  dbo.CustBalanceStdManual
                                        ( PeriodID ,
                                          AcctCode ,
                                          CustomerCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal
                                        )
                                VALUES  ( @Periods ,
                                          @StdCodeVoucher ,
                                          @CustomerCode ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0
                                        );
                                SET @Periods = @Periods + 1;
                            END;
                    END;
                    
                    
			--2. ����ת������std account�ķ�����
                UPDATE  dbo.CustBalanceStdManual
                SET     DebitBal = DebitBal + @RemapDebit ,
                        CreditBal = CreditBal + @RemapCredit
                WHERE   PeriodId = @Period
                        AND AcctCode = @StdCodeVoucher
                        AND CustomerCode = @CustomerCode;	
                       
                       					    								
			--3. ����ת������std account�ķ�����
                UPDATE  dbo.CustBalanceStdManual
                SET     DebitBal = DebitBal - @RemapDebit ,
                        CreditBal = CreditBal - @RemapCredit
                WHERE   PeriodId = @Period
                        AND AcctCode = @StdCodeAccount
                        AND CustomerCode = @CustomerCode;
			
                IF @CustomerCode <> ''
                    BEGIN
                        IF NOT EXISTS ( SELECT  1
                                        FROM    dbo.CustBalanceStdManual
                                        WHERE   PeriodId = @Period
                                                AND AcctCode = @StdCodeVoucher
                                                AND CustomerCode = '' )
                            BEGIN
                                SET @Periods = 1;
                                WHILE @Periods < = 12
                                    BEGIN
                                        INSERT  dbo.CustBalanceStdManual
                                                ( PeriodID ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal
                                                )
                                        VALUES  ( @Periods ,
                                                  @StdCodeVoucher ,
                                                  '' ,
                                                  0 ,
                                                  0 ,
                                                  0 ,
                                                  0 ,
                                                  0 ,
                                                  0
                                                );
                                        SET @Periods = @Periods + 1;
                                    END;
                            END;
                        UPDATE  dbo.CustBalanceStdManual
                        SET     DebitBal = DebitBal + @RemapDebit ,
                                CreditBal = CreditBal + @RemapCredit
                        WHERE   PeriodId = @Period
                                AND AcctCode = @StdCodeVoucher
                                AND CustomerCode = '';
                                
                        UPDATE  dbo.CustBalanceStdManual
                        SET     DebitBal = DebitBal - @RemapDebit ,
                                CreditBal = CreditBal - @RemapCredit
                        WHERE   PeriodId = @Period
                                AND AcctCode = @StdCodeAccount
                                AND CustomerCode = '';
                                
                    END;

                FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode,
                    @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount,
                    @StdCodeVoucher;
            END;
        CLOSE AccountRemap_Cursor;
        DEALLOCATE AccountRemap_Cursor;




	--Step 3: Calculate the accumulated movements for remapped standard accounts
	--�������ض�Ӧ�ķ�����󣬼������±����ۼơ���ĩ�������ڳ�
        DECLARE UpdateEndBal_Cursor CURSOR
        FOR
        --union�ĵ�һ���֣���ҵ��Ŀԭ����Ӧ�ı�׼��Ŀ��ת������ֻ����һ�����ݣ� union�ĵڶ����֣���ҵ��Ŀ�ض�Ӧ��ı�׼��Ŀ��ת��������ܻ��ж���
		SELECT  MIN(Period) Period ,
				StdCode ,
				Direction
		FROM    ( SELECT    ar.Period ,
							a.StdCode
				  FROM      dbo.AccountRemap ar
							INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
						    WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				               OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
				  UNION
				  SELECT    ar.Period ,
							ar.StdCode
				  FROM      dbo.AccountRemap ar
							INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
					        WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				               OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
				) b
				INNER JOIN dbo.StandardAccount sa ON b.StdCode = sa.Code
		GROUP BY StdCode ,
				Direction;

        OPEN UpdateEndBal_Cursor;
        FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                WHILE @Period <= 12
                    BEGIN
					--Update by CustomerCode
                        DECLARE UpdateEndBalCustomer_Cursor CURSOR
                        FOR
                            SELECT  CustomerCode ,
                                    SUM(DebitBal) ,
                                    SUM(CreditBal)
                            FROM    dbo.CustBalanceStdManual
                            WHERE   PeriodID <= @Period
                                    AND AcctCode = @StdCode
                            GROUP BY CustomerCode;
                        OPEN UpdateEndBalCustomer_Cursor;
                        FETCH NEXT FROM UpdateEndBalCustomer_Cursor INTO @CustomerCode,
                            @YearDebitBal, @YearCreditBal;
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                UPDATE  dbo.CustBalanceStdManual
                                SET     YearDebitBal = @YearDebitBal ,
                                        YearCreditBal = @YearCreditBal ,
                                        EndBal = BegBal + ( DebitBal - CreditBal ) * @Direction
                                WHERE   PeriodID = @Period
                                        AND AcctCode = @StdCode
                                        AND CustomerCode = @CustomerCode;
							
                                SELECT  @EndBal = EndBal
                                FROM    dbo.CustBalanceStdManual
                                WHERE   PeriodID = @Period
                                        AND AcctCode = @StdCode
                                        AND CustomerCode = @CustomerCode;

                                UPDATE  dbo.CustBalanceStdManual
                                SET     BegBal = @EndBal
                                WHERE   PeriodID = @Period + 1
                                        AND AcctCode = @StdCode
                                        AND CustomerCode = @CustomerCode;
                                FETCH NEXT FROM UpdateEndBalCustomer_Cursor INTO @CustomerCode,
                                    @YearDebitBal, @YearCreditBal;
                            END;
                        CLOSE UpdateEndBalCustomer_Cursor;
                        DEALLOCATE UpdateEndBalCustomer_Cursor;

                        SET @Period = @Period + 1;
                    END;
                FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode,
                    @Direction;
            END;

        CLOSE UpdateEndBal_Cursor;
        DEALLOCATE UpdateEndBal_Cursor;

	--Step 4: calculate trial balance for all the parent standard account based on the value of leaf node
	--���¼���ParentCode������
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.CustBalanceStdManual cbsm
                INNER JOIN dbo.StandardAccount sa ON cbsm.AcctCode = sa.Code;
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodID ,
                            sa.ParentCode ,
                            CustomerCode ,
                            SUM(sa.Direction * BegBal) BegBal ,
                            SUM(DebitBal) DebitBal ,
                            SUM(CreditBal) CreditBal ,
                            SUM(YearDebitBal) YearDebitBal ,
                            SUM(YearCreditBal) YearCreditBal ,
                            SUM(sa.Direction * EndBal) EndBal
                    FROM    dbo.CustBalanceStdManual cbs
                            INNER JOIN dbo.StandardAccount sa ON cbs.AcctCode = sa.Code
                    WHERE   sa.ParentCode IS NOT NULL
                            AND sa.AcctLevel = @CurrAcctLevel
                    GROUP BY PeriodID ,
                            sa.ParentCode ,
                            CustomerCode
                    ORDER BY PeriodID ,
                            sa.ParentCode ,
                            CustomerCode;

                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode,
                    @CustomerCode, @BegBal, @DebitBal, @CreditBal,
                    @YearDebitBal, @YearCreditBal, @EndBal;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        IF @CustomerCode = ''
                            BEGIN
                                SELECT  @Direction = Direction
                                FROM    dbo.StandardAccount
                                WHERE   Code = @AcctCode;
							-- ��������ڵĻ���insert
                                IF NOT EXISTS ( SELECT  1
                                                FROM    dbo.CustBalanceStdManual
                                                WHERE   PeriodID = @Period
                                                        AND AcctCode = @AcctCode
                                                        AND @CustomerCode = '' )
                                    BEGIN
                                        INSERT  dbo.CustBalanceStdManual
                                                ( PeriodID ,
                                                  AcctCode ,
                                                  CustomerCode ,
                                                  BegBal ,
                                                  DebitBal ,
                                                  CreditBal ,
                                                  YearDebitBal ,
                                                  YearCreditBal ,
                                                  EndBal
                                                )
                                        VALUES  ( @Period ,
                                                  @AcctCode ,
                                                  @CustomerCode ,
                                                  @Direction * @BegBal ,
                                                  @DebitBal ,
                                                  @CreditBal ,
                                                  @YearDebitBal ,
                                                  @YearCreditBal ,
                                                  @Direction * @EndBal
                                                );
                                    END;
                                ELSE
                                    BEGIN
                                        UPDATE  dbo.CustBalanceStdManual
                                        SET     BegBal = @Direction * @BegBal ,
                                                DebitBal = @DebitBal ,
                                                CreditBal = @CreditBal ,
                                                YearDebitBal = @YearDebitBal ,
                                                YearCreditBal = @YearCreditBal ,
                                                EndBal = @Direction * @EndBal
                                        WHERE   PeriodID = @Period
                                                AND AcctCode = @AcctCode
                                                AND CustomerCode = @CustomerCode;
                                    END;
                            END;
                        FETCH NEXT FROM ParentNode_Cursor INTO @Period,
                            @AcctCode, @CustomerCode, @BegBal, @DebitBal,
                            @CreditBal, @YearDebitBal, @YearCreditBal, @EndBal;
                    END;

                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;
     
     
    --Step 5: update BegDebitBal/BegCreditBal; EndDebitBal/EndCreditBal
    	UPDATE bs
        SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END,
                bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END,
                bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END,   
                bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END
        FROM dbo.CustBalanceStdManual bs
        INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code  
            

    END;

GO
CREATE PROCEDURE [dbo].[InitBalanceStdRemap]
    @VoucherRemapId INT, @CustRemapId INT
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Period INT;
        DECLARE @Periods INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CustomerCode NVARCHAR(50);
        DECLARE @RemapDebit NUMERIC(18, 3);
        DECLARE @RemapCredit NUMERIC(18, 3);
        DECLARE @StdCodeAccount NVARCHAR(50);
        DECLARE @StdCodeVoucher NVARCHAR(50);
        DECLARE @CurrAcctLevel INT;
        DECLARE @StdCode NVARCHAR(50);
        DECLARE @Direction INT;
        DECLARE @YearDebitBal NUMERIC(18, 3);
        DECLARE @YearCreditBal NUMERIC(18, 3);
        DECLARE @BegBal NUMERIC(18, 3);
        DECLARE @EndBal NUMERIC(18, 3);
        DECLARE @DebitBal NUMERIC(18, 3);
        DECLARE @CreditBal NUMERIC(18, 3);
        DECLARE @SubProp INT;

	--Step 1: Clean up C_BalanceStdManual and copy C_BalanceStd to C_BalanceStdManual
        TRUNCATE TABLE dbo.BalanceStdManual;
        INSERT  dbo.BalanceStdManual
                ( PeriodID ,
                  AcctCode ,
                  BegBal ,
                  DebitBal ,
                  CreditBal ,
                  YearDebitBal ,
                  YearCreditBal ,
                  EndBal ,
                  SubProp ,
                  CarryoverDebit ,
                  CarryoverCredit ,
                  YearCarryoverDebit ,
                  YearCarryoverCredit ,
                  DebitNet ,
                  CreditNet ,
                  YearDebitNet ,
                  YearCreditNet
                )
                SELECT  PeriodID ,
                        AcctCode ,
                        BegBal ,
                        DebitBal ,
                        CreditBal ,
                        YearDebitBal ,
                        YearCreditBal ,
                        EndBal ,
                        SubProp ,
                        0 ,
                        0 ,
                        0 ,
                        0 ,
                        0 ,
                        0 ,
                        0 ,
                        0
                FROM    dbo.BalanceStd;

	--Step 2: Get remapping result from C_AccountRemap and calculate new debit/credit movements
        DECLARE AccountRemap_Cursor CURSOR
        FOR
           SELECT t.Period,t.AcctCode,t.CustomerCode,t.RemapDebit,t.RemapCredit,outa.StdCode AS StdCodeAccount, t.StdCodeVoucher
           FROM dbo.EnterpriseAccount outa 
           INNER JOIN  
           (			
           SELECT   ar.Period ,
                    ar.AcctCode ,
                    ar.CustomerCode ,
                    SUM(ar.RemapDebit) AS RemapDebit ,
                    SUM(ar.RemapCredit) AS RemapCredit ,
                    ar.StdCode AS StdCodeVoucher
            FROM    dbo.AccountRemap ar
                    INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
                     WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				           OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
				     GROUP BY ar.Period,ar.AcctCode, ar.CustomerCode,ar.StdCode) t
	       ON outa.AcctCode = t.AcctCode

        OPEN AccountRemap_Cursor;
        FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode,
            @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount,
            @StdCodeVoucher;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF NOT EXISTS ( SELECT  1
                                FROM    dbo.BalanceStdManual
                                WHERE   PeriodId = @Period
                                        AND AcctCode = @StdCodeVoucher )
                    BEGIN
                        SET @Periods = 1;
                        WHILE @Periods < = 12
                            BEGIN
                                SELECT  @SubProp = SubProp
                                FROM    dbo.StandardAccount
                                WHERE   Code = @StdCodeVoucher;
                                INSERT  dbo.BalanceStdManual
                                        ( PeriodID ,
                                          AcctCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal ,
                                          SubProp ,
                                          CarryoverDebit ,
                                          CarryoverCredit ,
                                          YearCarryoverDebit ,
                                          YearCarryoverCredit ,
                                          DebitNet ,
                                          CreditNet ,
                                          YearDebitNet ,
                                          YearCreditNet
                                        )
                                VALUES  ( @Periods ,
                                          @StdCodeVoucher ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          @SubProp ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0
                                        );
                                SET @Periods = @Periods + 1;
                            END;
                    END;
                UPDATE  dbo.BalanceStdManual
                SET     DebitBal = DebitBal + @RemapDebit ,
                        CreditBal = CreditBal + @RemapCredit
                WHERE   PeriodId = @Period
                        AND AcctCode = @StdCodeVoucher;
                        
                UPDATE  dbo.BalanceStdManual
                SET     DebitBal = DebitBal - @RemapDebit ,
                        CreditBal = CreditBal - @RemapCredit
                WHERE   PeriodId = @Period
                        AND AcctCode = @StdCodeAccount;
                        
                FETCH NEXT FROM AccountRemap_Cursor INTO @Period, @AcctCode,
                    @CustomerCode, @RemapDebit, @RemapCredit, @StdCodeAccount,
                    @StdCodeVoucher;
            END;

        CLOSE AccountRemap_Cursor;
        DEALLOCATE AccountRemap_Cursor;

	--Step 3: Calculate the accumulated movements for remapped standard accounts
        DECLARE UpdateEndBal_Cursor CURSOR
        FOR
            SELECT  MIN(Period) Period ,
                    StdCode ,
                    Direction
            FROM    ( SELECT    ar.Period ,
                                a.StdCode
                      FROM      dbo.AccountRemap ar
                                INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
                                WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				                    OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
                      UNION
                      SELECT    ar.Period ,
                                ar.StdCode
                      FROM      dbo.AccountRemap ar
                                INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
                                WHERE (@VoucherRemapId > 0 AND ar.ReMappTypeId = @VoucherRemapId)
				                     OR (@CustRemapId > 0 AND ar.ReMappTypeId = @CustRemapId)	
                    ) b
                    INNER JOIN dbo.StandardAccount sa ON b.StdCode = sa.Code
            GROUP BY StdCode ,
                    Direction;

        OPEN UpdateEndBal_Cursor;
        FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode, @Direction;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                WHILE @Period <= 12
                    BEGIN
                        SELECT  @YearDebitBal = SUM(DebitBal) ,
                                @YearCreditBal = SUM(CreditBal)
                        FROM    dbo.BalanceStdManual
                        WHERE   PeriodID <= @Period
                                AND AcctCode = @StdCode;
                                
                        UPDATE  dbo.BalanceStdManual
                        SET     YearDebitBal = @YearDebitBal ,
                                YearCreditBal = @YearCreditBal ,
                                EndBal = BegBal + ( DebitBal - CreditBal ) * @Direction
                        WHERE   PeriodID = @Period
                                AND AcctCode = @StdCode;
                                
                        SELECT  @EndBal = EndBal
                        FROM    dbo.BalanceStdManual
                        WHERE   PeriodID = @Period
                                AND AcctCode = @StdCode;
                                
                        UPDATE  dbo.BalanceStdManual
                        SET     BegBal = @EndBal
                        WHERE   PeriodID = @Period + 1
                                AND AcctCode = @StdCode;
                        SET @Period = @Period + 1;
                    END;
                FETCH NEXT FROM UpdateEndBal_Cursor INTO @Period, @StdCode,
                    @Direction;
            END;

        CLOSE UpdateEndBal_Cursor;
        DEALLOCATE UpdateEndBal_Cursor;


	--Step 4: calculate trial balance for all the parent standard account based on the value of leaf node
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.BalanceStdManual bsm
                INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code;

        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodID ,
                            sa.ParentCode ,
                            SUM(sa.Direction * BegBal) BegBal ,
                            SUM(DebitBal) DebitBal ,
                            SUM(CreditBal) CreditBal ,
                            SUM(YearDebitBal) YearDebitBal ,
                            SUM(YearCreditBal) YearCreditBal ,
                            SUM(sa.Direction * EndBal) EndBal
                    FROM    dbo.BalanceStdManual bsm
                            INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code
                    WHERE   sa.AcctLevel = @CurrAcctLevel
                            AND sa.ParentCode IS NOT NULL
                    GROUP BY PeriodID ,
                            sa.ParentCode
                    ORDER BY PeriodID ,
                            sa.ParentCode;

                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode,
                    @BegBal, @DebitBal, @CreditBal, @YearDebitBal,
                    @YearCreditBal, @EndBal;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        SELECT  @Direction = Direction
                        FROM    dbo.StandardAccount
                        WHERE   Code = @AcctCode;
                        
                        SELECT  @SubProp = SubProp
                        FROM    dbo.StandardAccount
                        WHERE   Code = @AcctCode;
                        IF NOT EXISTS ( SELECT  1
                                        FROM    dbo.BalanceStdManual
                                        WHERE   PeriodID = @Period
                                                AND AcctCode = @AcctCode )
                            BEGIN
                                INSERT  dbo.BalanceStdManual
                                        ( PeriodID ,
                                          AcctCode ,
                                          BegBal ,
                                          DebitBal ,
                                          CreditBal ,
                                          YearDebitBal ,
                                          YearCreditBal ,
                                          EndBal ,
                                          SubProp ,
                                          CarryoverDebit ,
                                          CarryoverCredit ,
                                          YearCarryoverDebit ,
                                          YearCarryoverCredit ,
                                          DebitNet ,
                                          CreditNet ,
                                          YearDebitNet ,
                                          YearCreditNet
                                        )
                                VALUES  ( @Period ,
                                          @AcctCode ,
                                          @Direction * @BegBal ,
                                          @DebitBal ,
                                          @CreditBal ,
                                          @YearDebitBal ,
                                          @YearCreditBal ,
                                          @Direction * @EndBal ,
                                          @SubProp ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0 ,
                                          0
                                        );
                            END;
                        ELSE
                            BEGIN
                                UPDATE  dbo.BalanceStdManual
                                SET     BegBal = @Direction * @BegBal ,
                                        DebitBal = @DebitBal ,
                                        CreditBal = @CreditBal ,
                                        YearDebitBal = @YearDebitBal ,
                                        YearCreditBal = @YearCreditBal ,
                                        EndBal = @Direction * @EndBal
                                WHERE   PeriodID = @Period
                                        AND AcctCode = @AcctCode;
                            END;
                        FETCH NEXT FROM ParentNode_Cursor INTO @Period,
                            @AcctCode, @BegBal, @DebitBal, @CreditBal,
                            @YearDebitBal, @YearCreditBal, @EndBal;
                    END;

                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;
    
    --Step 5: 
        UPDATE bs
        SET bs.BegDebitBal = CASE WHEN sta.Direction = 1 THEN bs.BegBal ELSE 0 END,
                bs.BegCreditBal = CASE WHEN sta.Direction = -1 THEN bs.BegBal ELSE 0 END,
                bs.EndDebitBal = CASE WHEN sta.Direction = 1 THEN bs.EndBal ELSE 0 END,   
                bs.EndCreditBal = CASE WHEN sta.Direction = -1 THEN bs.EndBal ELSE 0 END
        FROM dbo.BalanceStdManual bs
        INNER JOIN dbo.StandardAccount sta ON bs.AcctCode = sta.Code      

	--Step 6: Calculate the carryover and net value (in another stored procedure InitBalanceStdRemapCarryover)
    END;

GO
CREATE PROCEDURE [dbo].[InitBalanceStdRemapCarryover]
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @MaxAcctProp INT;
        DECLARE @GainLossAcctProp INT; --AcctProp of Gain & Loss accounts
        DECLARE @ProfitStdCode NVARCHAR(50); --Standard account code of Current Year Profit
        DECLARE @ProfitAcctCode NVARCHAR(50); --Account code of Current Year Profit
        DECLARE @Period INT;
        DECLARE @Group NVARCHAR(50); --Voucher type
        DECLARE @VID NVARCHAR(256);	--Voucher number
        DECLARE @PeriodDetails INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @CarryoverDebit NUMERIC(18, 3);
        DECLARE @CarryoverCredit NUMERIC(18, 3);
        DECLARE @YearCarryoverDebit NUMERIC(18, 3);
        DECLARE @YearCarryoverCredit NUMERIC(18, 3);
        DECLARE @CurrAcctLevel INT;

	--Step 1: Reset Carryover column of C_BalanceStdManual table
        UPDATE  dbo.BalanceStdManual
        SET     CarryoverDebit = 0 ,
                CarryoverCredit = 0 ,
                YearCarryoverDebit = 0 ,
                YearCarryoverCredit = 0;

	--Step 2: Get the accounting rule (old 2001/new 2007) in current project
        SELECT  @MaxAcctProp = MAX(AcctProp)
        FROM    dbo.StandardAccount; 
        IF @MaxAcctProp = 5
            BEGIN
			--old rule 2001
                SET @GainLossAcctProp = 5;
                SET @ProfitStdCode = '3131';
            END;
        ELSE
            BEGIN
			--new rule 2007
                SET @GainLossAcctProp = 6;
                SET @ProfitStdCode = '4103';
            END;

	--Step 3: Get Vouchers by StdCode (combined from account level and voucher level mapping)
        DECLARE Voucher_Cursor CURSOR
        FOR
            SELECT DISTINCT
                    v.Period ,
                    v.[Group] ,
                    v.VID
            FROM    dbo.Voucher v
                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
            WHERE   ISNULL(v.StdCode, a.StdCode) = @ProfitStdCode;
        OPEN Voucher_Cursor;
        FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID;
        WHILE @@FETCH_STATUS = 0
            BEGIN
			--Step 3.1: Get Voucher Details by Voucher Period, Group, VID
			--Maybe gaint voucher
                IF EXISTS ( SELECT  1
                            FROM    dbo.Voucher v
                                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                    INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code
                            WHERE   v.Period = @Period
                                    AND v.[Group] = @Group
                                    AND v.VID = @VID
                                    AND sa.Code <> @ProfitStdCode
                                    AND sa.AcctProp <> @GainLossAcctProp )
                    BEGIN
                        DECLARE VoucherDetails_Cursor CURSOR
                        FOR
                            SELECT  v.Period ,
                                    ISNULL(v.StdCode, a.StdCode) AS StdCode ,
                                    SUM(Debit) AS CarryoverDebit ,
                                    SUM(Credit) AS CarryoverCredit
                            FROM    dbo.Voucher v
                                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                    INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode,a.StdCode) = sa.Code
                            WHERE   v.Period = @Period
                                    AND v.[Group] = @Group
                                    AND v.VID = @VID
                                    AND sa.AcctProp = @GainLossAcctProp
                                    AND ( CASE sa.Direction WHEN 1 THEN Debit ELSE Credit END ) = 0
                            GROUP BY v.Period ,
                                    ISNULL(v.StdCode, a.StdCode) ,
                                    sa.Direction;
                        OPEN VoucherDetails_Cursor;
                        FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                            @AcctCode, @CarryoverDebit, @CarryoverCredit;
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                UPDATE  dbo.BalanceStdManual
                                SET     CarryoverDebit = CarryoverDebit + @CarryoverDebit ,
                                        CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                        YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit ,
                                        YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit
                                WHERE   PeriodID = @PeriodDetails
                                        AND AcctCode = @AcctCode;

                                FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                    @AcctCode, @CarryoverDebit,
                                    @CarryoverCredit;
                            END;
                        CLOSE VoucherDetails_Cursor;
                        DEALLOCATE VoucherDetails_Cursor;
                    END;
			--Maybe normal voucher
                ELSE
                    BEGIN
                        DECLARE VoucherDetails_Cursor CURSOR
                        FOR
                            SELECT  v.Period ,
                                    ISNULL(v.StdCode, a.StdCode) AS StdCode ,
                                    CASE sa.Direction WHEN 1 THEN 0 ELSE SUM(Debit) - SUM(Credit) END AS CarryoverDebit ,
                                    CASE sa.Direction WHEN 1 THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS CarryoverCredit
                            FROM    dbo.Voucher v
                                    INNER JOIN dbo.EnterpriseAccount a ON v.AcctCode = a.AcctCode
                                    INNER JOIN dbo.StandardAccount sa ON ISNULL(v.StdCode, a.StdCode) = sa.Code
                            WHERE   v.Period = @Period
                                    AND v.[Group] = @Group
                                    AND v.VID = @VID
                                    AND sa.AcctProp = @GainLossAcctProp
                            GROUP BY v.Period ,
                                    ISNULL(v.StdCode, a.StdCode) ,
                                    sa.Direction;
                        OPEN VoucherDetails_Cursor;
                        FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                            @AcctCode, @CarryoverDebit, @CarryoverCredit;
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                UPDATE  dbo.BalanceStdManual
                                SET     CarryoverDebit = CarryoverDebit  + @CarryoverDebit ,
                                        CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                        YearCarryoverDebit = YearCarryoverDebit + @CarryoverDebit ,
                                        YearCarryoverCredit = YearCarryoverCredit + @CarryoverDebit
                                WHERE   PeriodID = @PeriodDetails
                                        AND AcctCode = @AcctCode;

                                FETCH NEXT FROM VoucherDetails_Cursor INTO @PeriodDetails,
                                    @AcctCode, @CarryoverDebit,
                                    @CarryoverCredit;
                            END;
                        CLOSE VoucherDetails_Cursor;
                        DEALLOCATE VoucherDetails_Cursor;
                    END;
                FETCH NEXT FROM Voucher_Cursor INTO @Period, @Group, @VID;
            END;
        CLOSE Voucher_Cursor;
        DEALLOCATE Voucher_Cursor;

	--Step 4: Calculate the accumulated amount of current year profits
        DECLARE AccumulatedProfit_Cursor CURSOR
        FOR
            SELECT  PeriodID ,
                    AcctCode
            FROM    dbo.BalanceStdManual bsm
                    INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code
            WHERE   sa.AcctProp = @GainLossAcctProp;
        OPEN AccumulatedProfit_Cursor;
        FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period, @AcctCode; 
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT  @YearCarryoverDebit = SUM(CarryoverDebit) ,
                        @YearCarryoverCredit = SUM(CarryoverCredit)
                FROM    dbo.BalanceStdManual
                WHERE   PeriodID <= @Period
                        AND AcctCode = @AcctCode;
                        
                UPDATE  dbo.BalanceStdManual
                SET     YearCarryoverDebit = @YearCarryoverDebit ,
                        YearCarryoverCredit = @YearCarryoverCredit
                WHERE   PeriodID = @Period
                        AND AcctCode = @AcctCode;

                FETCH NEXT FROM AccumulatedProfit_Cursor INTO @Period,
                    @AcctCode;
            END;

        CLOSE AccumulatedProfit_Cursor;
        DEALLOCATE AccumulatedProfit_Cursor;

	--Step 5: Calculate the amount of all parent accounts
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.StandardAccount;
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  PeriodID ,
                            sa.ParentCode ,
                            SUM(CarryoverDebit) AS CarryoverDebit ,
                            SUM(CarryoverCredit) AS CarryoverCredit ,
                            SUM(YearCarryoverDebit) AS YearCarryoverDebit ,
                            SUM(YearCarryoverCredit) AS YearCarryoverCredit
                    FROM    dbo.BalanceStdManual bsm
                            INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code
                    WHERE   sa.ParentCode IS NOT NULL
                            AND sa.AcctLevel = @CurrAcctLevel
                    GROUP BY PeriodID , sa.ParentCode
                    ORDER BY PeriodID , sa.ParentCode;
                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @Period, @AcctCode,
                    @CarryoverDebit, @CarryoverCredit, @YearCarryoverDebit,
                    @YearCarryoverCredit;
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        UPDATE  dbo.BalanceStdManual
                        SET     CarryoverDebit = CarryoverDebit + @CarryoverDebit ,
                                CarryoverCredit = CarryoverCredit + @CarryoverCredit ,
                                YearCarryoverDebit = YearCarryoverDebit + @YearCarryoverDebit ,
                                YearCarryoverCredit = YearCarryoverCredit + @YearCarryoverCredit
                        WHERE   PeriodID = @Period
                                AND AcctCode = @AcctCode;

                        FETCH NEXT FROM ParentNode_Cursor INTO @Period,
                            @AcctCode, @CarryoverDebit, @CarryoverCredit,
                            @YearCarryoverDebit, @YearCarryoverCredit;
                    END;
                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;

	--Step 6: Calculate the net values of Debit/Credit/Accumulated Debit/Accumulated Credit
        UPDATE  dbo.BalanceStdManual
        SET     DebitNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) ,
                CreditNet = 0 ,
                YearDebitNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit ) ,
                YearCreditNet = 0
        FROM    dbo.BalanceStdManual bsm
                INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code
        WHERE   sa.Direction = 1;

        UPDATE  dbo.BalanceStdManual
        SET     DebitNet = 0 ,
                CreditNet = sa.Direction * ( DebitBal - CreditBal + CarryoverCredit - CarryoverDebit ) ,
                YearDebitNet = 0 ,
                YearCreditNet = sa.Direction * ( YearDebitBal - YearCreditBal + YearCarryoverCredit - YearCarryoverDebit )
        FROM    dbo.BalanceStdManual bsm
                INNER JOIN dbo.StandardAccount sa ON bsm.AcctCode = sa.Code
        WHERE   sa.Direction = -1;

    END;      

GO

GO
-- =============================================
-- Description:	�����ط����Ŀ���ط���total����������parent���ط���total���
-- Parameters: 1. @PeriodId > 0: ֻ��ȡAccountRemap����Period = @PeriodId������
--                @PeriodId < 0: ��ȡAccountRemap�������е�����
-- =============================================

CREATE PROCEDURE [dbo].[InitAccountRemapSum]
 @PeriodId INT
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @CurrAcctLevel INT;
        DECLARE @AcctCode NVARCHAR(50);
        DECLARE @Parent NVARCHAR(50);
        DECLARE @AcctLevel INT;
        DECLARE @RemapDebit NUMERIC(18, 3);
        DECLARE @RemapCredit NUMERIC(18, 3);

	--Step 1: Clean up AccountRemapSum and insert data from AccountRemap
        TRUNCATE TABLE dbo.AccountRemapSum;
        INSERT  dbo.AccountRemapSum
                ( AcctCode ,
                  Parent ,
				  Period,
                  AcctLevel ,
                  RemapDebit ,
                  RemapCredit
                )
                SELECT  ar.AcctCode ,
                        a.ParentCode ,
						@PeriodId,
                        a.AcctLevel ,
                        SUM(RemapDebit) AS RemapDebit ,
                        SUM(RemapCredit) AS RemapCredit
                FROM    dbo.AccountRemap ar
                        INNER JOIN dbo.EnterpriseAccount a ON ar.AcctCode = a.AcctCode
						WHERE (@PeriodId > 0 AND Period = @PeriodId)
                              OR(@PeriodId < 0 AND Period IS NOT NULL)
                GROUP BY ar.AcctCode ,
                        a.ParentCode ,
                        a.AcctLevel;

	--Step 2: Calculate the sum value of all parents
        SELECT  @CurrAcctLevel = MAX(AcctLevel)
        FROM    dbo.AccountRemapSum;
        WHILE @CurrAcctLevel > 1
            BEGIN
                DECLARE ParentNode_Cursor CURSOR
                FOR
                    SELECT  ars.Parent AS AcctCode ,
                            a.ParentCode ,
                            a.AcctLevel ,
                            SUM(RemapDebit) AS RemapDebit ,
                            SUM(RemapCredit) AS RemapCredit
                    FROM    dbo.AccountRemapSum ars
                            INNER JOIN dbo.EnterpriseAccount a ON ars.Parent = a.AcctCode
                    WHERE   ars.AcctLevel = @CurrAcctLevel
                    GROUP BY ars.Parent ,
                            a.ParentCode ,
                            a.AcctLevel;

                OPEN ParentNode_Cursor;
                FETCH NEXT FROM ParentNode_Cursor INTO @AcctCode, @Parent,
                    @AcctLevel, @RemapDebit, @RemapCredit;
                WHILE @@FETCH_STATUS = 0
                    BEGIN			
                        INSERT  dbo.AccountRemapSum
                                ( 								  
								  AcctCode ,
                                  Parent ,
								  Period,
                                  AcctLevel ,
                                  RemapDebit ,
                                  RemapCredit
                                )
                        VALUES  ( @AcctCode ,
                                  @Parent ,
								  @PeriodId,
                                  @AcctLevel ,
                                  @RemapDebit ,
                                  @RemapCredit
                                );
                        FETCH NEXT FROM ParentNode_Cursor INTO @AcctCode,
                            @Parent, @AcctLevel, @RemapDebit, @RemapCredit;
                    END;
                CLOSE ParentNode_Cursor;
                DEALLOCATE ParentNode_Cursor;
                SET @CurrAcctLevel = @CurrAcctLevel - 1;
            END;
    END;

GO
-----------------------------------------Rita 20170519��¼�ȶ��㷨������һ���Զ��ŷָ���ַ�������һ�ű�----------------------------------------------------------------
IF EXISTS(SELECT 1 from sys.objects where name = 'F_Splitstr' and type_desc = 'SQL_TABLE_VALUED_FUNCTION')
BEGIN
DROP FUNCTION [dbo].[F_Splitstr]
END
go

create function F_Splitstr(
	@str nvarchar(Max)
)returns @return table(id int identity(1,1),value nvarchar(Max))
as
begin
	declare @position int;
	set @position = CHARINDEX(',', @str);
	while @position>0
	begin
		insert @return(value) values(left(@str,@position-1));
		select
			@str = STUFF(@str,1,@position,''),
			@position = CHARINDEX(',', @str)
	end
	if @str>''
		insert @return(value) values(@str);
	return
end
go
------------------------------------------------��¼�ȶ��㷨�������ַ������ƴ��SQL-------------------------------------------------------------------------------
IF EXISTS(SELECT 1 from sys.objects where name = 'F_JointCondition' and type_desc = 'SQL_SCALAR_FUNCTION')
BEGIN
DROP FUNCTION [dbo].[F_JointCondition]
END
go

create function F_JointCondition(
	@sql nvarchar(Max),
	@condition nvarchar(Max)
)returns nvarchar(Max)
as
begin
	declare @i int;
	declare @j int;
	declare @str nvarchar(Max);
	declare @tempTable table(id int identity(1,1),value nvarchar(Max));
	declare @value nvarchar(Max);
	
	set @str = '';
	set @value = '';

	--������ʱ��
	insert into @tempTable (value) select value from dbo.F_Splitstr(@condition);
	select @i=count(1) from @tempTable;

	set @j=1;
	while(@i>=@j)
	begin
		select @value=rtrim(ltrim(value)) from @tempTable where id=@j;
		set @str = @str+@sql+@value+' ';
		set @j=@j+1;			
	end
	return(@str);
end
go
--------------------------------------------��¼�ȶ��㷨��AddEntriesCheckDetailResult�����߼�--------------------------------------------------------------------------

IF EXISTS(SELECT 1 from sys.objects where name = 'AddEntriesCheckDetailResult' and type_desc = 'SQL_STORED_PROCEDURE')
BEGIN
DROP PROCEDURE [dbo].[AddEntriesCheckDetailResult]
END
GO

create procedure [dbo].[AddEntriesCheckDetailResult]
	(
		@resultId varchar(128),
		@mainCondition nvarchar(Max),
		@parallelCondition nvarchar(Max),
		@exclusivesCondition nvarchar(Max),
		@entriesCondition nvarchar(Max),
		@isManual int=1,
		@mainConditionAtm nvarchar(Max),
		@parallelConditionAtm nvarchar(Max),
		@entriesConditionAtm nvarchar(Max),
		@isToAddResult int=1
	)	 
as
begin
		--��ȡ��¼�ȶ����ƾ֤
		declare @getVoucherSql nvarchar(Max);
		declare @temp nvarchar(Max);

		--��ȡ��¼�ɵ�
		declare @getDoubtSql nvarchar(Max);
		declare @mainTempTable table(id int identity(1,1),value nvarchar(Max));
		declare @entriesTempTable table(id int identity(1,1),value nvarchar(Max));
		declare @i int;
		declare @j int;
		declare @value nvarchar(Max);

		--���÷�¼��ؽ��
		declare @getAmtSql nvarchar(Max);
		declare @mainTempTableAtm table(id int identity(1,1),value nvarchar(Max));
		declare @entriesTempTableAtm table(id int identity(1,1),value nvarchar(Max));
		declare @k int;
		declare @g int;
		declare @valueAtm nvarchar(Max);

		--��¼�ȶԼ�����ʱ��
		declare @voucherTmplCalculator table(id int identity(1,1),Period int,VID nvarchar(256),[Group] nvarchar(50));

		--������ʱ��VoucherTmplCalculator
	    set @getVoucherSql = 'select Period, VID, [Group] from (select Period,VID,[Group] from Voucher ';
		if(@isManual = 0)
		begin
			set @getVoucherSql = @getVoucherSql+'where importType <> 7 ';
		end
		if(@mainCondition<>'')
		begin
			set @temp = dbo.F_JointCondition(' intersect select Period,VID,[Group] from Voucher where ',@mainCondition);
			set @getVoucherSql = @getVoucherSql+@temp;
		end
		if(@parallelCondition<>'')
		begin
			set @getVoucherSql = @getVoucherSql+' intersect select Period,VID,[Group] from Voucher where '+@parallelCondition;
		end
		if(@exclusivesCondition<>'')
		begin
			set @temp = dbo.F_JointCondition(' except select Period,VID,[Group] from Voucher where ',@exclusivesCondition);
			set @getVoucherSql = @getVoucherSql+@temp;
		end
		if(@entriesCondition<>'') 
		begin
			set @temp = dbo.F_JointCondition(' intersect select VoucherID,Period,VID,[Group] from Voucher where ',@entriesCondition);
			set @getVoucherSql = @getVoucherSql+' intersect select distinct Period,VID,[Group] from (select VoucherID,Period,VID,[Group] from Voucher '+@temp+')abc';
		end
		set @getVoucherSql = @getVoucherSql + ')tb';
		set @getVoucherSql = @getVoucherSql+' group by Period,VID,[Group]';
		--print @getVoucherSql;
		insert into @voucherTmplCalculator(Period, VID,[Group]) exec(@getVoucherSql);

		--����#temp��ʱ��
		select * into #temp from
		(select NEWID() as ID,@resultId as ResultId,0 as IsDoubt,0 as IsRelevantAmt,v.* from @voucherTmplCalculator t
		left join (select VoucherID,VID,[Date],[Group],Period,CustomerCode,CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode from Voucher) v 
		on v.Period = t.Period and v.[VID] = t.[VID] and v.[Group] = t.[Group])TB

		--�����ɵ��¼
		set @getDoubtSql = 'update #temp set IsDoubt=1 where ';
		if(@mainCondition<>'')
		begin
			--������ʱ��
			insert into @mainTempTable (value) select value from dbo.F_Splitstr(@mainCondition);
			select @i=count(1) from @mainTempTable;
			set @j=1;
			while(@i>=@j)
				begin
					select @value=rtrim(ltrim(value)) from @mainTempTable where id=@j;
					if(@j>1)
						set @getDoubtSql = @getDoubtSql+' or '+@value+' ';
					else
						set @getDoubtSql = @getDoubtSql+@value;
					set @j=@j+1;			
				end
		end
		if(@parallelCondition<>'')
		begin
			if(@mainCondition<>'')
			begin
				set @getDoubtSql = @getDoubtSql+' or ';
			end
			set @getDoubtSql = @getDoubtSql+@parallelCondition;
		end
		if(@entriesCondition<>'') 
		begin
			if(@mainCondition<>'' or @parallelCondition<>'')
			begin
				set @getDoubtSql = @getDoubtSql+' or ';
			end
			--������ʱ��
			insert into @entriesTempTable (value) select value from dbo.F_Splitstr(@entriesCondition);
			select @i=count(1) from @entriesTempTable;
			set @j=1;
			while(@i>=@j)
				begin
					select @value=rtrim(ltrim(value)) from @entriesTempTable where id=@j;
					if(@j>1)
						set @getDoubtSql = @getDoubtSql+' or '+@value+' ';
					else
						set @getDoubtSql = @getDoubtSql+@value+' ';
					set @j=@j+1;			
				end
		end
		if(@mainCondition = '' and @parallelCondition = '' and @entriesCondition='')
		begin
			set @getDoubtSql = @getDoubtSql + ' 0=1';
		end
		--print @getDoubtSql;
		exec(@getDoubtSql);

		--���÷�¼��ؽ��
		set @getAmtSql = 'update #temp set IsRelevantAmt=1 where ';
		if(@mainConditionAtm<>'')
		begin
			--������ʱ��
			insert into @mainTempTableAtm (value) select value from dbo.F_Splitstr(@mainConditionAtm);
			select @i=count(1) from @mainTempTableAtm;
			set @j=1;
			while(@i>=@j)
				begin
					select @valueAtm=rtrim(ltrim(value)) from @mainTempTableAtm where id=@j;
					if(@j>1)
						set @getAmtSql = @getAmtSql+' or '+@value+' ';
					else
						set @getAmtSql = @getAmtSql+@value;
					set @j=@j+1;			
				end
		end
		if(@parallelConditionAtm<>'')
		begin
			if(@mainConditionAtm<>'')
			begin
				set @getAmtSql = @getAmtSql+' or ';
			end
			set @getAmtSql = @getAmtSql+@parallelConditionAtm;
		end
		if(@entriesConditionAtm<>'') 
		begin
			if(@mainConditionAtm<>'' or @parallelConditionAtm<>'')
			begin
				set @getAmtSql = @getAmtSql+' or ';
			end
			--������ʱ��
			insert into @entriesTempTableAtm (value) select value from dbo.F_Splitstr(@entriesConditionAtm);
			select @i=count(1) from @entriesTempTableAtm;
			set @j=1;
			while(@i>=@j)
				begin
					select @valueAtm=rtrim(ltrim(value)) from @entriesTempTableAtm where id=@j;
					if(@j>1)
						set @getAmtSql = @getAmtSql+' or '+@value+' ';
					else
						set @getAmtSql = @getAmtSql+@value+' ';
					set @j=@j+1;			
				end
		end
		if(@mainConditionAtm = '' and @parallelConditionAtm = '' and @entriesConditionAtm = '')
		begin
			set @getAmtSql = @getAmtSql + ' 0=1';
		end
		--print @getAmtSql;
		exec(@getAmtSql);

		if(@isToAddResult = 1)
		begin
			insert into EntriesCheckDetailResult(ID,ResultID,IsDoubt,IsRelevantAmt,VoucherID,VID,[Date],
			[Group],Period,CustomerCode,CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode) 
			select ID,ResultID,IsDoubt,IsRelevantAmt,VoucherID,VID,[Date],[Group],Period,CustomerCode,
			CustomerName,Summary,StdCode,Debit,Credit,ItemID,ImportType,AcctCode from #temp;
		end

		--Return results of the model
		select * from #temp;
end
go
----------------------------------------------------------------------------------------------------------------------------------------------------------------
--***************************end replace holder part*************************





-----start---------from taxadmin db------------------------------------

INSERT INTO [dbo].[StandardAccount] SELECT *
FROM [TaxAdmin].[dbo].[StandardAccount] WHERE IsActive = 1 and IndustryId = 'DBKeyword_IndustryId'
GO
if not exists (select 1 from [dbo].[StandardAccount])
begin
INSERT INTO [dbo].[StandardAccount] SELECT *
FROM [TaxAdmin].[dbo].[StandardAccount] WHERE IsActive = 1 and IndustryId = '0'
end

Insert INTO [dbo].[EnterpriseAccount]
SELECT a.[ID],a.[Code],a.[Name],a.[ParentCode],a.[FullName],a.[AcctProp],a.[SubProp],a.[AcctLevel],a.[Direction],a.[IsLeaf],a.[IsActive],a.[EnglishName],
b.[StandardAccountCode] as StdCode,a.[EnterpriseAccountSetID],0,0,a.[CreatorID],a.[UpdatorID],a.[CreateTime],a.[UpdateTime] 
FROM [TaxAdmin].[dbo].[EnterpriseAccount] a 
left join [TaxAdmin].[dbo].[AccountMapping] b on a.EnterpriseAccountSetID = b.EnterpriseAccountSetID and a.Code = b.EnterpriseAccountCode and b.OrganizationID = 'DBKeyword_OrganizationID'
--left join [TaxAdmin].[dbo].[StandardAccount] c on b.StandardAccountCode = c.Code and b.IndustryID = c.IndustryID and b.IndustryId = 'DBKeyword_IndustryId' and c.IsActive = 1
WHERE a.IsActive = 1 and a.[EnterpriseAccountSetID] = 'DBKeyword_EnterpriseAccountSetID';
GO 

-----end---------from taxadmin db------------------------------------


---- start ModelBackFillAmount------
IF NOT EXISTS (select 1 from sys.tables where name = 'ModelBackFillAmount' and type = 'U')
BEGIN
create table dbo.ModelBackFillAmount
(	
	[ID] [nvarchar](128) NOT NULL,
	[DataSourceID] [nvarchar](128) NOT NULL,
	[EntriesCheckResultID] [nvarchar](128) NOT NULL,
	[Amount] [decimal](20, 4) NOT NULL,
	[CellDataID] [nvarchar](128) NOT NULL,
	PRIMARY KEY(ID)
);
END
GO 

---- end ModelBackFillAmount------

----CellComment----
IF NOT EXISTS (select 1 from sys.tables where name = 'CellComment')
BEGIN
CREATE TABLE [dbo].[CellComment](
	[ID] [varchar](128) NOT NULL,
	[CellDataId] [varchar](128) NOT NULL,
	[UserId] [varchar](128) NOT NULL,
	[UserName] [nvarchar](50) NULL,
	[Comment] [nvarchar](2048) NULL,
	[ReplyToUserName] [nvarchar](50) NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_CellComment] 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]
END
GO
----End of CellComment----

----ModifiedReportCell----
IF NOT EXISTS (select 1 from sys.tables where name = 'ModifiedReportCell')
BEGIN
CREATE TABLE [dbo].[ModifiedReportCell](
	[ID] [varchar](128) NOT NULL,
	[ReportId] [varchar](128) NOT NULL,
	[Row] [int] NOT NULL,
	[Col] [int] NOT NULL,
	[OriginalValue] [nvarchar](200) NULL,
	[Value] [nvarchar](200) NOT NULL,
	[RelatedCellDataId] [varchar](128) NULL,
	[Comment] [nvarchar](1024) NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[UpdateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_ModifiedReportCell] 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]
END
GO
----End of ModifiedReportCell----