错误信息：SQL 脚本:15.000.000.0234 DATA_DSTR_RCHK_Mix_NL-43012 出错：列名 'acc_XSCostAmount' 无效。 列名 'acc_XSCostAmount' 无效。 列名 'acc_XSCostAmount' 无效。

【问题现象】

升级脚本出错，错误信息：SQL 脚本: 15.000.000.0234 DATA_DSTR_RCHK_Mix_NL-43012 出错：列名 'acc_XSCostAmount' 无效。

列名 'acc_XSCostAmount' 无效。

列名 'acc_XSCostAmount' 无效。   在 Ufida.T.SM.Upgrade.Service.UpgradeHelper.ExecuteScript(TongVersion version, Queue`1 upgardeItems, VersionTypeEnum product, String currentDBName, Boolean isSaas)

   在 Ufida.T.SM.Upgrade.Service.UpgradeService.VersionsUpgrade(UpgradeContext context)

   在 Ufida.T.SM.Upgrade.Service.UpgradeService.ExecUpgradeScript(UpgradeContext context)

   在 Ufida.T.SM.Upgrade.Service.UpgradeService.Upgrade(UpgradeContext context)![具体出错信息请参见:C:\Chanjet\TPlusStd\Appserver\server\..\logs\UFTData803541_000001_Upgrade.log![具体出错信息请参见:C:\Chanjet\TPlusStd\Appserver\server\..\logs\UFTData803541_000001_Upgrade.log

【解决方案】

备份数据后，执行下面的脚本。

---补丁脚本----

--资金成本数据集:for成本分析

IF EXISTS

(

SELECT *

FROM sys.sysobjects

WHERE name = N'RCHK_func_FundAmountTableForCost'

)

BEGIN

DROP function RCHK_func_FundAmountTableForCost;

END;

GO

CREATE function [dbo].[RCHK_func_FundAmountTableForCost]

(

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@GroupBy tinyint --分组依据：1、仓库 2、存货分类 3、存货

)

returns @table TABLE

(

Id INT IDENTITY(1, 1),

iddimension int,

VoucherDatet DATETIME,

FundAmount DECIMAL(18, 5)

DEFAULT 0, --库存资金

CostAmount DECIMAL(18, 5)

DEFAULT 0 --资金成本

)

as

BEGIN

--DECLARE @table TABLE

--(

-- Id INT IDENTITY(1, 1),

-- VoucherDate DATETIME,

-- FundAmount DECIMAL(18, 5)

-- DEFAULT 0, --库存资金

-- CostAmount DECIMAL(18, 5)

-- DEFAULT 0 --资金成本

--);

DECLARE @date DATETIME;

SET @date = CONVERT(DATETIME, @BeginDate, 120);

declare @iTop int

set @iTop=12

DECLARE @tableDimension TABLE

(

idDimension int --维度id：仓库、存货分类、存货

);

if(@GroupBy=1)

begin

insert into @tableDimension

select

top 12 Warehouse

from

RCHK_CostAnalysis

where

VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

group by Warehouse

order by sum(FundAmountForCost) desc

end

else if(@GroupBy=2)

begin

insert into @tableDimension

select

top 12 InventoryClass

from

RCHK_CostAnalysis

where

VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

group by InventoryClass

order by sum(FundAmountForCost) desc

end

else if(@GroupBy=3)

begin

insert into @tableDimension

select

top 12 Inventory

from

RCHK_CostAnalysis

where

VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

group by Inventory

order by sum(FundAmountForCost) desc

end

WHILE @date <= CONVERT(DATETIME, @EndDate, 120)

BEGIN

if(@GroupBy=1)

begin

INSERT INTO @table

(

iddimension,

VoucherDatet

)

SELECT idDimension,@date from @tableDimension

end

else if(@GroupBy=2)

begin

INSERT INTO @table

(

iddimension,

VoucherDatet

)

SELECT idDimension,@date from @tableDimension

end

else if(@GroupBy=3)

begin

INSERT INTO @table

(

iddimension,

VoucherDatet

)

SELECT idDimension,@date from @tableDimension

end

SET @date = DATEADD(DAY, 1, @date);

END;

if(@GroupBy=1)

begin

UPDATE a

SET a.FundAmount = b.FundAmount

FROM @table a

INNER JOIN

(

SELECT

Warehouse,

VoucherDate,

SUM(FundAmountForCost) AS FundAmount

FROM dbo.RCHK_CostAnalysis,@tableDimension

WHERE VoucherDate >= CONVERT(DATETIME, @BeginDate, 120)

AND VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

and Warehouse=idDimension

GROUP BY VoucherDate,Warehouse

) b

ON a.VoucherDatet = b.VoucherDate

and a.iddimension=b.Warehouse

end

else if(@GroupBy=2)

begin

UPDATE a

SET a.FundAmount = b.FundAmount

FROM @table a

INNER JOIN

(

SELECT

InventoryClass,

VoucherDate,

SUM(FundAmountForCost) AS FundAmount

FROM dbo.RCHK_CostAnalysis,@tableDimension

WHERE VoucherDate >= CONVERT(DATETIME, @BeginDate, 120)

AND VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

and InventoryClass=idDimension

GROUP BY VoucherDate,InventoryClass

) b

ON a.VoucherDatet = b.VoucherDate

and a.iddimension=b.InventoryClass

end

else if(@GroupBy=3)

begin

UPDATE a

SET a.FundAmount = b.FundAmount

FROM @table a

INNER JOIN

(

SELECT

Inventory,

VoucherDate,

SUM(FundAmountForCost) AS FundAmount

FROM dbo.RCHK_CostAnalysis,@tableDimension

WHERE VoucherDate >= CONVERT(DATETIME, @BeginDate, 120)

AND VoucherDate <= CONVERT(DATETIME, @EndDate, 120)

AND ISNULL(FundAmountForCost, 0) != 0

and Inventory=idDimension

GROUP BY VoucherDate,Inventory

) b

ON a.VoucherDatet = b.VoucherDate

and a.iddimension=b.Inventory

end

----增加期初成本金额

--DECLARE @periodBeginCostAmount DECIMAL(18, 5);

--SELECT @periodBeginCostAmount = SUM(FundAmountForCost)

--FROM dbo.RCHK_CostAnalysis

--WHERE VoucherDate < CONVERT(DATETIME, @BeginDate, 120)

--UPDATE @table

--SET FundAmount = FundAmount + ISNULL(@periodBeginCostAmount,0)

--WHERE VoucherDatet = CONVERT(DATETIME, @BeginDate, 120)

if(@GroupBy=1)

begin

update

@table

set

FundAmount=FundAmount+m.FundAmountForCost

from(

select

Warehouse,

sum(FundAmountForCost) FundAmountForCost

from

RCHK_CostAnalysis,@tableDimension

where

VoucherDate<=CONVERT(DATETIME, @BeginDate, 120)

and Warehouse=idDimension

group by Warehouse

)m

where

VoucherDatet=CONVERT(DATETIME, @BeginDate, 120)

and iddimension=m.Warehouse

end

else if(@GroupBy=2)

begin

update

@table

set

FundAmount=FundAmount+m.FundAmountForCost

from(

select

InventoryClass,

sum(FundAmountForCost) FundAmountForCost

from

RCHK_CostAnalysis,@tableDimension

where

VoucherDate<=CONVERT(DATETIME, @BeginDate, 120)

and InventoryClass=idDimension

group by

InventoryClass

)m

where

VoucherDatet=CONVERT(DATETIME, @BeginDate, 120)

and iddimension=m.InventoryClass

end

else if(@GroupBy=3)

begin

update

@table

set

FundAmount=FundAmount+m.FundAmountForCost

from(

select

Inventory,

sum(FundAmountForCost) FundAmountForCost

from

RCHK_CostAnalysis,@tableDimension

where

VoucherDate<=CONVERT(DATETIME, @BeginDate, 120)

and Inventory=idDimension

group by

Inventory

)m

where

VoucherDatet=CONVERT(DATETIME, @BeginDate, 120)

and iddimension=m.Inventory

end

DECLARE @preCostAmount DECIMAL(18, 5);

SET @preCostAmount = 0;

declare @iddimension int

UPDATE M

SET @preCostAmount=m.CostAmount = (case when @iddimension=m.iddimension then @preCostAmount + m.FundAmount else m.FundAmount end),

@iddimension=m.iddimension

from

(

select top 99.9999 percent iddimension,FundAmount,CostAmount

from @table

order by iddimension,VoucherDatet

)m

DECLARE @dayRate DECIMAL(18, 5);

SELECT @dayRate = dValue / 100

FROM dbo.RCHK_Setting

WHERE settingType = 'zjzyll'

AND settingCode = '3';

UPDATE @table

SET CostAmount = ROUND(CostAmount * @dayRate, 2);

return ;

--PRINT @FundCost

END;

GO

--好几种余额表新增销售成本列

GO

/****** Object: Table [dbo].[RCHK_SeveralBalance] Script Date: 2019/12/13 9:47:23 ******/

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'RCHK_SeveralBalance')

begin

DROP TABLE [dbo].[RCHK_SeveralBalance]

end

GO

/****** Object: Table [dbo].[RCHK_SeveralBalance] Script Date: 2019/12/13 9:47:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[RCHK_SeveralBalance](

[id] [int] IDENTITY(1,1) NOT NULL,

[VoucherDate] [datetime] NULL,

[acc_ApAmount] [decimal](28, 2) NULL,

[acc_ApBalance] [decimal](28, 2) NULL,

[acc_ArAmount] [decimal](28, 2) NULL,

[acc_ArBalance] [decimal](28, 2) NULL,

[acc_BankBalance] [decimal](28, 2) NULL,

[acc_OTExpenses] [decimal](28, 2) NULL,

[acc_OTIncome] [decimal](28, 2) NULL,

[acc_PayAmount] [decimal](28, 2) NULL,

[acc_ProductClass] [nvarchar](200) NULL,

[acc_PUAmount] [decimal](28, 2) NULL,

[acc_RevAmount] [decimal](28, 2) NULL,

[acc_SAAmount] [decimal](28, 2) NULL,--销售收入

[acc_STBalance] [decimal](28, 2) NULL,

[acc_XSCostAmount] [decimal](28, 2) NULL,--销售成本

[acc_ZJCostAmount] [decimal](28, 2) NULL,--资金成本

[acc_JCCostAmount] [decimal](28, 2) NULL,--结存成本

[createdTime] [datetime] NULL,

CONSTRAINT [RCHK_SeveralBalance_PK] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

--人财货客好几种余额表前期数据准备及插数

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_SeveralBalance_DataPool'

)

BEGIN

DROP PROCEDURE RCHK_SP_SeveralBalance_DataPool;

END;

GO

--好几种余额表的数据池结构创建及数据填充

CREATE PROCEDURE [dbo].[RCHK_SP_SeveralBalance_DataPool]

-- Add the parameters for the stored procedure here

@ReportModelCode nvarchar(100) --指标Code

AS

BEGIN

-- 呆滞存货取数----

exec SP_RCHK_ObsoleteInventory

-------临期存货取数---

exec SP_RCHK_expiryInventory

------收付款金额类型取数------------

exec [RCHK_SP_RevPayAmountType_Data]

--需要预置的截至到当前日的总天数(建账前一天到当天)

declare @iDate int

declare @minDate datetime

declare @maxDate datetime

select @minDate=min(begindate) from SM_Period

select @maxDate=max(enddate) from SM_Period

select

@iDate=datediff(day,@minDate,@maxDate)+2

--先清除表数据

truncate table rchk_SeveralBalance

--插入从建账头一天到当前日的预置数据，各指标只需按日期更新即可

insert into

rchk_SeveralBalance(voucherdate,createdtime)

select

distinct dateadd(day,-number+1,@maxDate) ,dbo.getdate()

from

(select ROW_NUMBER() over(order by id) number from Eap_ColumnSet) tmp

where

number>=0 and number<=@iDate

update

rchk_SeveralBalance

set

voucherdate=convert(varchar(10),voucherdate,120)

--应收指标更新 ArBanlance

UPDATE a

SET a.acc_ArBalance = b.banlance

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT Detail.registerDate AS VoucherDate,

SUM(ISNULL(Detail.amount, 0) - ISNULL(Detail.settleAmount, 0)) AS banlance

FROM dbo.ARAP_Detail Detail

WHERE Detail.isArFlag = 1

AND Detail.auditFlag = 1

GROUP BY Detail.registerDate

) b

ON a.VoucherDate = b.VoucherDate;

INSERT INTO RCHK_SeveralBalance

(

VoucherDate,

acc_ArBalance,

createdTime

)

SELECT registerDate,

SUM(ISNULL(Detail.amount, 0) - ISNULL(Detail.settleAmount, 0)) AS banlance,

getdate()

FROM ARAP_Detail Detail

WHERE Detail.isArFlag = 1

AND Detail.auditFlag = 1

AND registerDate NOT IN

(

SELECT VoucherDate FROM RCHK_SeveralBalance

)

GROUP BY registerDate;

--应付指标更新 ApBanlance

UPDATE a

SET a.acc_ApBalance = b.banlance

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT Detail.registerDate AS VoucherDate,

SUM(ISNULL(Detail.amount, 0) - ISNULL(Detail.settleAmount, 0)) AS banlance

FROM dbo.ARAP_Detail Detail

WHERE Detail.isArFlag = 0

AND Detail.auditFlag = 1

GROUP BY Detail.registerDate

) b

ON a.VoucherDate = b.VoucherDate;

Insert INTO RCHK_SeveralBalance

(

VoucherDate,

acc_ApBalance,

createdTime

)

SELECT registerDate,

SUM(ISNULL(Detail.amount, 0) - ISNULL(Detail.settleAmount, 0)) AS banlance,

getdate()

FROM ARAP_Detail Detail

WHERE Detail.isArFlag = 0

AND Detail.auditFlag = 1

AND registerDate NOT IN

(

SELECT VoucherDate FROM RCHK_SeveralBalance

)

GROUP BY registerDate;

--收款指标更新 ArBanlance

UPDATE a

SET a.acc_RevAmount = b.banlance

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT Detail.voucherDate,

SUM(ISNULL(Detail.amount, 0)) AS banlance

FROM dbo.ARAP_ReceivePayment Detail

WHERE Detail.isReceiveFlag = 1

AND Detail.voucherstate = 189

GROUP BY Detail.voucherDate

) b

ON a.VoucherDate = b.voucherDate;

--付款指标更新 ApBanlance

UPDATE a

SET a.acc_PayAmount = b.banlance

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT Detail.voucherDate,

SUM(ISNULL(Detail.amount, 0)) AS banlance

FROM dbo.ARAP_ReceivePayment Detail

WHERE Detail.isReceiveFlag = 0

AND Detail.voucherstate = 189

GROUP BY Detail.voucherDate

) b

ON a.VoucherDate = b.voucherDate;

--下周应收款 acc_ArAmount

UPDATE a

SET a.acc_ArAmount = b.aramount

FROM dbo.rchk_SeveralBalance a

INNER JOIN

(SELECT Detail.arrivalDate, SUM(ISNULL(amount, 0) - ISNULL(settleAmount, 0)) as aramount

FROM ARAP_Detail Detail

WHERE arrivalDate IS NOT NULL

AND isArFlag = 1

AND auditFlag = 1

AND prepayFlag = 0

GROUP BY Detail.arrivalDate) b

ON a.VoucherDate = b.arrivalDate

--下周应付款 acc_ApAmount

UPDATE a

SET a.acc_ApAmount = b.aramount

FROM dbo.rchk_SeveralBalance a

INNER JOIN

(SELECT Detail.arrivalDate, SUM(ISNULL(amount, 0) - ISNULL(settleAmount, 0)) as aramount

FROM ARAP_Detail Detail

WHERE arrivalDate IS NOT NULL

AND isArFlag = 0

AND auditFlag = 1

AND prepayFlag = 0

GROUP BY Detail.arrivalDate) b

ON a.VoucherDate = b.arrivalDate

--acc_OTExpenses 其他费用

UPDATE a

SET a.acc_OTExpenses = b.taxAmountSum

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT SUM(taxAmountSum) AS taxAmountSum,

voucherdate

FROM CS_ExpenseVoucher

WHERE voucherstate = 189

GROUP BY voucherdate

) b

ON a.VoucherDate = b.VoucherDate;

INSERT INTO RCHK_SeveralBalance

(

VoucherDate,

acc_OTExpenses,

createdTime

)

SELECT voucherdate,

SUM(taxAmountSum) AS Amount,

GETDATE()

FROM CS_ExpenseVoucher

WHERE voucherstate = 189

AND voucherdate NOT IN

(

SELECT VoucherDate FROM RCHK_SeveralBalance

)

GROUP BY voucherdate;

---acc_OTIncome 其他收入

UPDATE a

SET a.acc_OTIncome = b.taxAmountSum

FROM dbo.RCHK_SeveralBalance a

INNER JOIN

(

SELECT SUM(taxAmountSum) AS taxAmountSum,

voucherdate

FROM CS_IncomeVoucher

WHERE voucherstate = 189

GROUP BY voucherdate

) b

ON a.VoucherDate = b.VoucherDate;

INSERT INTO RCHK_SeveralBalance

(

VoucherDate,

acc_OTIncome,

createdTime

)

SELECT voucherdate,

SUM(taxAmountSum) AS Amount,

GETDATE()

FROM CS_IncomeVoucher

WHERE voucherstate = 189

AND voucherdate NOT IN

(

SELECT VoucherDate FROM RCHK_SeveralBalance

)

GROUP BY voucherdate;

--- acc_SAAmount 采购货款

UPDATE a

SET a.acc_PUAmount = b.amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (SELECT Detail.registerDate AS VoucherDate, SUM(ISNULL(Detail.amount, 0) + ISNULL(Detail.cashAmount, 0)+ ISNULL(Detail.SaleAllowance, 0)) AS amount

FROM dbo.ARAP_Detail Detail WHERE auditFlag=1 and idarapvouchertype in(select id from SM_VoucherType where code in('PU03','PU04')) GROUP BY Detail.registerDate) b

ON a.VoucherDate = b.VoucherDate

--- acc_SAAmount 销售收入

UPDATE a

SET a.acc_SAAmount = b.amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (SELECT Detail.registerDate AS VoucherDate, SUM(ISNULL(Detail.amount, 0) + ISNULL(Detail.cashAmount, 0)+ ISNULL(Detail.SaleAllowance, 0)) AS amount

FROM dbo.ARAP_Detail Detail WHERE auditFlag=1 and idarapvouchertype in(select id from SM_VoucherType where code in('SA04','SA05','RE20')) GROUP BY Detail.registerDate) b

ON a.VoucherDate = b.VoucherDate

Insert into rchk_SeveralBalance(VoucherDate,acc_SAAmount,createdTime)

Select voucherdate,SUM(ISNULL(Detail.amount, 0) + ISNULL(Detail.cashAmount, 0)+ ISNULL(Detail.SaleAllowance, 0)) as Amount,getdate()

from ARAP_Detail Detail where auditFlag=1 and idarapvouchertype in(select id from SM_VoucherType where code in('SA04','SA05','RE20'))

and voucherdate not in (select voucherdate from rchk_SeveralBalance) group by voucherdate

--- acc_XSCostAmount 销售成本

UPDATE a

SET a.acc_XSCostAmount = b.amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (SELECT RDRecord.voucherdate AS VoucherDate, sum(isnull(RDRecord.Amount,0)) AS amount

FROM dbo.ST_RDRecord RDRecord WHERE RDRecord.voucherstate=189 and idvouchertype in(select id from SM_VoucherType where code in('ST1021','ST1025')) GROUP BY RDRecord.voucherdate) b

ON a.VoucherDate = b.VoucherDate

Insert into rchk_SeveralBalance(VoucherDate,acc_XSCostAmount,createdTime)

Select voucherdate,sum(isnull(RDRecord.Amount,0)) as Amount,getdate()

from ST_RDRecord RDRecord where RDRecord.voucherstate=189 and idvouchertype in(select id from SM_VoucherType where code in('ST1021','ST1025'))

and voucherdate not in (select voucherdate from rchk_SeveralBalance) group by voucherdate

-- --结存成本

--UPDATE a

--SET a.[acc_JCCostAmount] = b.Amount

--FROM dbo.rchk_SeveralBalance a

--INNER JOIN (Select sum(isnull(ST_SubDTO.inAmount,0)-isnull(ST_SubDTO.outAmount,0)) as Amount,ST_SubDTO.voucherdate from ST_SubsidiaryBook ST_SubDTO where isPeriodInit=1 group by ST_SubDTO.voucherdate) b

--ON a.VoucherDate = b.VoucherDate

--Insert into rchk_SeveralBalance(VoucherDate,acc_STBalance,createdTime)

--Select ST_SubDTO.voucherdate,sum(isnull(ST_SubDTO.inAmount,0)-isnull(ST_SubDTO.outAmount,0)) as Amount,getdate()

-- from ST_SubsidiaryBook ST_SubDTO where isPeriodInit=1 and voucherdate not in (select voucherdate from rchk_SeveralBalance) group by ST_SubDTO.voucherdate

--库存资金指标更新 STBanlance

UPDATE a

SET a.acc_STBalance = b.Amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (Select sum(isnull(ST_SubDTO.inAmount,0)-isnull(ST_SubDTO.outAmount,0)) as Amount,ST_SubDTO.voucherdate from ST_SubsidiaryBook ST_SubDTO group by ST_SubDTO.voucherdate) b

ON a.VoucherDate = b.VoucherDate

Insert into rchk_SeveralBalance(VoucherDate,acc_STBalance,createdTime)

Select ST_SubDTO.voucherdate,sum(isnull(ST_SubDTO.inAmount,0)-isnull(ST_SubDTO.outAmount,0)) as Amount,getdate()

from ST_SubsidiaryBook ST_SubDTO where voucherdate not in (select voucherdate from rchk_SeveralBalance) group by ST_SubDTO.voucherdate

declare @dValue decimal(18,4)

select @dValue=dValue from RCHK_Setting where settingType='zjzyll' and settingCode='3'

--资金成本=结存成本*资金占压利率的日利率

update

RCHK_SeveralBalance

set

[acc_ZJCostAmount]=acc_STBalance*@dValue

--账号余额指标更新 BankBanlance

DECLARE @EnableModule INT

SET @EnableModule=0 --1:以出纳计算方式为准 2：以钱流计算方式为准

--获取计算依据，是根据出纳计算还是钱流计算

IF EXISTS (SELECT [VALUE] FROM EAP_AccInformation WHERE [NAME]='CS' AND [Value]=1 )

SET @EnableModule=1

ELSE IF EXISTS (SELECT [VALUE] FROM EAP_AccInformation WHERE [NAME]='CashBankMoneyFlow' AND [Value]=1 )

SET @EnableModule=2

ELSE IF EXISTS (SELECT [VALUE] FROM EAP_AccInformation WHERE [NAME]='StdIERP' AND [Value]=1 )

SET @EnableModule=2

IF(@EnableModule=0) --如果都未启用,置空余额

BEGIN

update dbo.rchk_SeveralBalance set acc_bankBalance=0

END

ELSE IF(@EnableModule=1)--出纳

BEGIN

UPDATE a

SET a.acc_BankBalance = b.Amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (Select sum(isnull(M.amountDr,0)-isnull(M.amountCr,0)) as Amount,M.voucherdate from CS_CashAccountDaily M group by M.voucherdate) b

ON a.VoucherDate = b.VoucherDate

END

ELSE

BEGIN --钱流

UPDATE a

SET a.acc_BankBalance = b.Amount

FROM dbo.rchk_SeveralBalance a

INNER JOIN (Select sum(isnull(M.inamount,0)-isnull(M.outamount,0)) as Amount,M.sourceVoucherDate

from CS_CashAccount M

where isPeriodBeginning<>1

and sourceVoucherAuditor is not null

group by M.sourceVoucherDate) b

ON a.VoucherDate = b.sourceVoucherDate

END

if(@EnableModule<>0)

begin

declare @BeginDate datetime

IF(@EnableModule=1)

BEGIN

SET @BeginDate=(SELECT TOP 1 Dateadd(day, -1, b.begindate)

FROM EAP_AccInformation a

LEFT JOIN sm_period b

ON a.idEnablePeriod = b.id

WHERE idEnablePeriod IS NOT NULL

AND name = 'CS'

AND sysid = 'SM'

AND value = 1)

END

ELSE IF(@EnableModule=2)

BEGIN

SET @BeginDate=(SELECT TOP 1 Dateadd(day, -1, b.begindate)

FROM EAP_AccInformation a

LEFT JOIN sm_period b

ON a.idEnablePeriod = b.id

WHERE idEnablePeriod IS NOT NULL

AND name = 'CashBankMoneyFlow'

AND sysid = 'SM'

AND value = 1)

END

--UPDATE a

--SET a.acc_BankBalance = (Select sum(isnull(M.amount,0)) as Amount from CS_CashAccount M where isPeriodBeginning=1)

--FROM dbo.rchk_SeveralBalance a

--where a.id=(select min(id) from dbo.rchk_SeveralBalance)

UPDATE a

SET a.acc_BankBalance = (SELECT Sum(Isnull(M.amount, 0)) AS Amount

FROM CS_CashAccount M

WHERE isPeriodBeginning = 1)

FROM dbo.rchk_SeveralBalance a

WHERE a.VoucherDate = @BeginDate

INSERT INTO RCHK_SeveralBalance

(VoucherDate,

acc_BankBalance,

createdTime)

SELECT @BeginDate,

Sum(Isnull(M.amount, 0)) AS banlance,

Getdate()

FROM CS_CashAccount M

WHERE M.isPeriodBeginning = 1

AND @BeginDate NOT IN (SELECT VoucherDate

FROM RCHK_SeveralBalance)

--GROUP BY sourceVoucherDate;

end

END ;

GO

--盈利分析-成本分析

--成本分析模型

DECLARE @IDReportModel_CostAnalysis INT;

select

@IDReportModel_CostAnalysis=id

FROM

RCHK_ReportModel

WHERE

code = 'CostAnalysis'

--删除模型对应维度明细

DELETE FROM RCHK_Detail_Dimension

WHERE idReportModel =@IDReportModel_CostAnalysis

--删除模型对应指标明细

DELETE FROM RCHK_Detail_Member

WHERE idReportModel =@IDReportModel_CostAnalysis

DELETE FROM dbo.RCHK_ReportModel

WHERE code = 'CostAnalysis';

INSERT INTO dbo.RCHK_ReportModel

(

idParent,

code,

name,

reportModelType,

visible,

filters,

members,

description,

makerId,

createdTime,

reviser

)

VALUES

(NULL,

'CostAnalysis',

'成本分析',

'EChart',

1 ,

NULL,

NULL,

'成本分析',

NULL,

dbo.getdate(),

NULL

);

select @IDReportModel_CostAnalysis=@@IDENTITY

--存货分类维度ID

DECLARE

@IDDimensionInventoryClass INT

SELECT @IDDimensionInventoryClass = id

FROM rchk_dimension

WHERE code = 'InventoryClass'

--存货维度ID

DECLARE

@IDDimensionInventory INT

SELECT @IDDimensionInventory = id

FROM rchk_dimension

WHERE code = 'Inventory'

--仓库维度ID

DECLARE

@IDDimensionWarehouse INT

SELECT @IDDimensionWarehouse = id

FROM rchk_dimension

WHERE code = 'Warehouse'

INSERT INTO RCHK_Detail_Dimension

(

idReportModel,

idDimension,

visible,

createdTime

)

SELECT @IDReportModel_CostAnalysis,

id,

1,

getdate()

FROM dbo.RCHK_Dimension

WHERE code IN ( 'VoucherDate', 'InventoryClass', 'Inventory','Warehouse');

--日期维度id

DECLARE

@IDDimensionVoucherDate INT

select @IDDimensionVoucherDate=id

from RCHK_Dimension

where code='VoucherDate'

--删除指标明细

delete

RCHK_Member_Dimension

where

idMember in(

select id from RCHK_Member where code in('FundAmountForCost','SAAMountForCost','SACostForCost','CGCostForCost','CGQtyForCost')

)

--删除指标

delete

RCHK_Member

where

code in('FundAmountForCost','SAAMountForCost','SACostForCost','CGCostForCost','CGQtyForCost')

--结存成本金额指标For成本

DECLARE

@IDMember_FundAmountForCost INT

INSERT INTO rchk_member(code,

[name],

membertype,

isatomexpression,

valueType,

isValid,

ExpressionName,

tablename,

alias,

selectField,

datasource,

fixedwhereexpression,

[description],

createdtime)

VALUES ('FundAmountForCost',

'结存成本金额',

'Sum',

1,

'AMOUNT',

1,

NULL,

'ST_SubsidiaryBook',

'M',

'isnull(M.inAmount,0)-isnull(M.outAmount,0) ',

'DataPool',

null,

'库存结存成本金额',

Getdate())

set @IDMember_FundAmountForCost=@@IDENTITY

INSERT INTO rchk_detail_member(idreportmodel,

idmember,

visible,

createdtime)

VALUES (@IDReportModel_CostAnalysis,

@IDMember_FundAmountForCost,

1,

Getdate())

INSERT INTO RCHK_Member_Dimension(

idMember,

idDimension,

selectField,

alias,

selectFieldName,

fieldType,

leftJoinRightTable,

leftJoinRightField,

leftJoinLeftField

)

values

(

@IDMember_FundAmountForCost,

@IDDimensionVoucherDate,

'M.VoucherDate',

'VoucherDate',

'日期',

'Date',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventory,

'Inventory.id',

'Inventory',

'存货id',

'int',

'AA_Inventory Inventory',

'Inventory.id',

'M.idinventory'

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventory,

'Inventory.Code',

'Inventory_Code',

'存货编码',

'String',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventory,

'Inventory.Name',

'Inventory_Name',

'存货名称',

'String',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.id',

'InventoryClass',

'存货分类id',

'int',

'AA_InventoryClass InventoryClass',

'Inventory.idinventoryclass',

'InventoryClass.id'

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.Code',

'InventoryClass_Code',

'存货分类编码',

'String',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.Name',

'InventoryClass_Name',

'存货分类名称',

'String',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionWarehouse,

'Warehouse.id',

'Warehouse',

'仓库id',

'int',

'AA_Warehouse Warehouse',

'Warehouse.id',

'M.idwarehouse'

),

(

@IDMember_FundAmountForCost,

@IDDimensionWarehouse,

'Warehouse.Code',

'Warehouse_Code',

'仓库编码',

'String',

null,

null,

null

),

(

@IDMember_FundAmountForCost,

@IDDimensionWarehouse,

'Warehouse.Name',

'Warehouse_Name',

'仓库名称',

'String',

null,

null,

null

)

--销售收入金额指标For成本

DECLARE

@IDMember_SAAmountForCost INT

INSERT INTO rchk_member(code,

[name],

membertype,

isatomexpression,

valueType,

isValid,

ExpressionName,

tablename,

alias,

selectField,

datasource,

fixedwhereexpression,

[description],

createdtime)

VALUES ('SAAMountForCost',

'销售收入金额For成本',

'Sum',

1,

'AMOUNT',

1,

NULL,

'ARAP_Detail',

'M',

'ISNULL(M.amount, 0) + ISNULL(M.cashAmount, 0)+ ISNULL(M.SaleAllowance, 0) ',

'DataPool',

'AND M.isArFlag = 1 AND M.AuditFlag = 1 AND M.idarapvouchertype IN (20,104,67)',

'销售收入金额For成本',

Getdate())

set @IDMember_SAAmountForCost=@@IDENTITY

INSERT INTO rchk_detail_member(idreportmodel,

idmember,

visible,

createdtime)

VALUES (@IDReportModel_CostAnalysis,

@IDMember_SAAmountForCost,

1,

Getdate())

INSERT INTO RCHK_Member_Dimension(

idMember,

idDimension,

selectField,

alias,

selectFieldName,

fieldType,

leftJoinRightTable,

leftJoinRightField,

leftJoinLeftField

)

values

(

@IDMember_SAAmountForCost,

@IDDimensionVoucherDate,

'M.RegisterDate',

'VoucherDate',

'日期',

'Date',

null,

null,

null

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventory,

'Inventory.id',

'Inventory',

'存货id',

'int',

'AA_Inventory Inventory',

'Inventory.id',

'M.detailID'

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventory,

'Inventory.Code',

'Inventory_Code',

'存货编码',

'String',

null,

null,

null

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventory,

'Inventory.Name',

'Inventory_Name',

'存货名称',

'String',

null,

null,

null

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.id',

'InventoryClass',

'存货分类id',

'int',

'AA_InventoryClass InventoryClass',

'Inventory.idinventoryclass',

'InventoryClass.id'

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.Code',

'InventoryClass_Code',

'存货分类编码',

'String',

null,

null,

null

),

(

@IDMember_SAAmountForCost,

@IDDimensionInventoryClass,

'InventoryClass.Name',

'InventoryClass_Name',

'存货分类名称',

'String',

null,

null,

null

)

--销售成本金额指标For成本

DECLARE

@IDMember_SACostForCost INT

INSERT INTO rchk_member(code,

[name],

membertype,

isatomexpression,

valueType,

isValid,

ExpressionName,

tablename,

alias,

selectField,

datasource,

fixedwhereexpression,

[description],

createdtime)

VALUES ('SACostForCost',

'销售成本金额',

'Sum',

1,

'AMOUNT',

1,

NULL,

'ST_RDRecord_b',

'M',

'isnull(M.Amount,0) ',

'DataPool',

'and idvouchertype in(select id from sm_vouchertype where code in(''ST1021'',''ST1025''))',

'销售成本金额',

Getdate())

set @IDMember_SACostForCost=@@IDENTITY

INSERT INTO rchk_detail_member(idreportmodel,

idmember,

visible,

createdtime)

VALUES (@IDReportModel_CostAnalysis,

@IDMember_SACostForCost,

1,

Getdate())

INSERT INTO RCHK_Member_Dimension(

idMember,

idDimension,

selectField,

alias,

selectFieldName,

fieldType,

leftJoinRightTable,

leftJoinRightField,

leftJoinLeftField

)

values

(

@IDMember_SACostForCost,

@IDDimensionVoucherDate,

null,

null,

null,

'Date',

'ST_RDRecord D',

'D.id',

'M.idRDRecordDTO'

),

(

@IDMember_SACostForCost,

@IDDimensionVoucherDate,

'D.VoucherDate',

'VoucherDate',

'日期',

'Date',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionInventory,

'Inventory.id',

'Inventory',

'存货id',

'int',

'AA_Inventory Inventory',

'Inventory.id',

'M.idinventory'

),

(

@IDMember_SACostForCost,

@IDDimensionInventory,

'Inventory.Code',

'Inventory_Code',

'存货编码',

'String',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionInventory,

'Inventory.Name',

'Inventory_Name',

'存货名称',

'String',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionInventoryClass,

'InventoryClass.id',

'InventoryClass',

'存货分类id',

'int',

'AA_InventoryClass InventoryClass',

'Inventory.idinventoryclass',

'InventoryClass.id'

),

(

@IDMember_SACostForCost,

@IDDimensionInventoryClass,

'InventoryClass.Code',

'InventoryClass_Code',

'存货分类编码',

'String',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionInventoryClass,

'InventoryClass.Name',

'InventoryClass_Name',

'存货分类名称',

'String',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionWarehouse,

'Warehouse.id',

'Warehouse',

'仓库id',

'int',

'AA_Warehouse Warehouse',

'Warehouse.id',

'M.idwarehouse'

),

(

@IDMember_SACostForCost,

@IDDimensionWarehouse,

'Warehouse.Code',

'Warehouse_Code',

'仓库编码',

'String',

null,

null,

null

),

(

@IDMember_SACostForCost,

@IDDimensionWarehouse,

'Warehouse.Name',

'Warehouse_Name',

'仓库名称',

'String',

null,

null,

null

)

--采购成本金额指标For成本

DECLARE

@IDMember_CGCostForCost INT

INSERT INTO rchk_member(code,

[name],

membertype,

isatomexpression,

valueType,

isValid,

ExpressionName,

tablename,

alias,

selectField,

datasource,

fixedwhereexpression,

[description],

createdtime)

VALUES ('CGCostForCost',

'采购成本金额',

'Sum',

1,

'AMOUNT',

1,

NULL,

'ST_RDRecord_b',

'M',

'isnull(M.Amount,0) ',

'DataPool',

'and idvouchertype in(select id from sm_vouchertype where code =''ST1001'')',

'采购成本金额',

Getdate())

set @IDMember_CGCostForCost=@@IDENTITY

INSERT INTO rchk_detail_member(idreportmodel,

idmember,

visible,

createdtime)

VALUES (@IDReportModel_CostAnalysis,

@IDMember_CGCostForCost,

1,

Getdate())

INSERT INTO RCHK_Member_Dimension(

idMember,

idDimension,

selectField,

alias,

selectFieldName,

fieldType,

leftJoinRightTable,

leftJoinRightField,

leftJoinLeftField

)

values

(

@IDMember_CGCostForCost,

@IDDimensionVoucherDate,

null,

null,

null,

'Date',

'ST_RDRecord D',

'D.id',

'M.idRDRecordDTO'

),

(

@IDMember_CGCostForCost,

@IDDimensionVoucherDate,

'D.VoucherDate',

'VoucherDate',

'日期',

'Date',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionInventory,

'Inventory.id',

'Inventory',

'存货id',

'int',

'AA_Inventory Inventory',

'Inventory.id',

'M.idinventory'

),

(

@IDMember_CGCostForCost,

@IDDimensionInventory,

'Inventory.Code',

'Inventory_Code',

'存货编码',

'String',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionInventory,

'Inventory.Name',

'Inventory_Name',

'存货名称',

'String',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionInventoryClass,

'InventoryClass.id',

'InventoryClass',

'存货分类id',

'int',

'AA_InventoryClass InventoryClass',

'Inventory.idinventoryclass',

'InventoryClass.id'

),

(

@IDMember_CGCostForCost,

@IDDimensionInventoryClass,

'InventoryClass.Code',

'InventoryClass_Code',

'存货分类编码',

'String',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionInventoryClass,

'InventoryClass.Name',

'InventoryClass_Name',

'存货分类名称',

'String',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionWarehouse,

'Warehouse.id',

'Warehouse',

'仓库id',

'int',

'AA_Warehouse Warehouse',

'Warehouse.id',

'M.idwarehouse'

),

(

@IDMember_CGCostForCost,

@IDDimensionWarehouse,

'Warehouse.Code',

'Warehouse_Code',

'仓库编码',

'String',

null,

null,

null

),

(

@IDMember_CGCostForCost,

@IDDimensionWarehouse,

'Warehouse.Name',

'Warehouse_Name',

'仓库名称',

'String',

null,

null,

null

)

--采购成本金额指标For成本

DECLARE

@IDMember_CGQtyForCost INT

INSERT INTO rchk_member(code,

[name],

membertype,

isatomexpression,

valueType,

isValid,

ExpressionName,

tablename,

alias,

selectField,

datasource,

fixedwhereexpression,

[description],

createdtime)

VALUES ('CGQtyForCost',

'采购数量',

'Sum',

1,

'AMOUNT',

1,

NULL,

'ST_RDRecord_b',

'M',

'isnull(M.quantity,0) ',

'DataPool',

'and idvouchertype in(select id from sm_vouchertype where code =''ST1001'')',

'采购数量',

Getdate())

set @IDMember_CGQtyForCost=@@IDENTITY

INSERT INTO rchk_detail_member(idreportmodel,

idmember,

visible,

createdtime)

VALUES (@IDReportModel_CostAnalysis,

@IDMember_CGQtyForCost,

1,

Getdate())

INSERT INTO RCHK_Member_Dimension(

idMember,

idDimension,

selectField,

alias,

selectFieldName,

fieldType,

leftJoinRightTable,

leftJoinRightField,

leftJoinLeftField

)

values

(

@IDMember_CGQtyForCost,

@IDDimensionVoucherDate,

null,

null,

null,

'Date',

'ST_RDRecord D',

'D.id',

'M.idRDRecordDTO'

),

(

@IDMember_CGQtyForCost,

@IDDimensionVoucherDate,

'D.VoucherDate',

'VoucherDate',

'日期',

'Date',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventory,

'Inventory.id',

'Inventory',

'存货id',

'int',

'AA_Inventory Inventory',

'Inventory.id',

'M.idinventory'

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventory,

'Inventory.Code',

'Inventory_Code',

'存货编码',

'String',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventory,

'Inventory.Name',

'Inventory_Name',

'存货名称',

'String',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventoryClass,

'InventoryClass.id',

'InventoryClass',

'存货分类id',

'int',

'AA_InventoryClass InventoryClass',

'Inventory.idinventoryclass',

'InventoryClass.id'

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventoryClass,

'InventoryClass.Code',

'InventoryClass_Code',

'存货分类编码',

'String',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionInventoryClass,

'InventoryClass.Name',

'InventoryClass_Name',

'存货分类名称',

'String',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionWarehouse,

'Warehouse.id',

'Warehouse',

'仓库id',

'int',

'AA_Warehouse Warehouse',

'Warehouse.id',

'M.idwarehouse'

),

(

@IDMember_CGQtyForCost,

@IDDimensionWarehouse,

'Warehouse.Code',

'Warehouse_Code',

'仓库编码',

'String',

null,

null,

null

),

(

@IDMember_CGQtyForCost,

@IDDimensionWarehouse,

'Warehouse.Name',

'Warehouse_Name',

'仓库名称',

'String',

null,

null,

null

)

GO

--创建成本分析数据池结构

exec RCHK_SP_CreateTalbe_ForDataPool 'CostAnalysis'

GO

--成本分析-成本总览-采购成本

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisCGCB'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisCGCB;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisCGCB]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100) --结束日期

--查询条件

AS

BEGIN

DECLARE @table TABLE

(

expenseCode NVARCHAR(20), --费用档案Code

expenseName NVARCHAR(20), --费用档案Name

totalAmount DECIMAL(18, 2), --总金额

feeAmount DECIMAL(18, 2) --费用金额

);

insert into @table

(expenseCode,expenseName,totalAmount,feeAmount)

select

aa.code,

aa.name,

sum(isnull(d.totalAmount,0)),

sum(isnull(feeAmount,0))

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

left join ST_ShareExpenseVoucherExpenseRdDetail sve on d.ID=sve.idrDRecordDetail

left join CS_ExpenseVoucher_b cs on sve.IdexpenseVoucherDetail=cs.ID

left join AA_ExpenseItem aa on cs.idexpenseitem=aa.id

where

m.idvouchertype=(select id from SM_VoucherType where code='ST1001')

and m.voucherState=189

group by

aa.code,

aa.name

--货值成本

declare @hzcb decimal(18,2)

--总采购费用

declare @cgfy decimal(18,2)

--采购费用:其他费用

declare @qtfy decimal(18,2)

--计算货值成本

select

@hzcb=sum(totalAmount-feeAmount)

from

@table

--计算总采购费用

select

@cgfy=sum(feeAmount)

from

@table

--计算其他费用(总采购费用-前三的费用)

select

@qtfy=sum(feeAmount)

from

(

select top 3 feeAmount

from @table

order by feeAmount

) m

--成本率begin---------------------------

--本期的销售成本

declare @xscb decimal(18,2)

--同比上期的销售成本

declare @xscbtb decimal(18,2)

--环比上期的销售成本

declare @xscbhb decimal(18,2)

--本期的销售收入

declare @xssr decimal(18,2)

--销售成本同比率

declare @tb decimal(18,4)

--销售成本环比率

declare @hb decimal(18,4)

--成本率

declare @cbl decimal(18,4)

--本期的销售成本

select

@xscb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

--同比上期的销售成本

select

@xscbtb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(year,-1,@BeginDate)

and VoucherDate<=dateadd(year,-1,@EndDate)

--环比上期的销售成本

select

@xscbhb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(MONTH,-1,@BeginDate)

and VoucherDate<=dateadd(MONTH,-1,@EndDate)

--本期的销售收入

select

@xssr=sum(isnull(acc_SAAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

--同比

if(@xscbtb=0)

begin

set @tb=1;

end

else

begin

set @tb=@xscb/@xscbtb

end

--环比

if(@xscbhb=0)

begin

set @hb=1;

end

else

begin

set @hb=@xscb/@xscbhb

end

--计算本期的成本率

if(@xssr=0)

begin

set @cbl=1;

end

else

begin

set @cbl=@xscb/@xssr

end

--成本率end------------------------------------

--结存成本begin--------------------------------

--结存成本

declare @jccb decimal(18,2)

--同比上期的结存成本

declare @jccbtb decimal(18,2)

--环比上期的结存成本

declare @jccbhb decimal(18,2)

--本期的资金成本

declare @zjcb decimal(18,2)

--结存成本同比率

declare @tb2 decimal(18,4)

--结存成本环比率

declare @hb2 decimal(18,4)

--动销率

declare @dxl decimal(18,4)

--本期的结存成本

select

@jccb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate<=@EndDate

--同比上期的结存成本

select

@jccbtb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate<=dateadd(year,-1,@EndDate)

--环比上期的结存成本

select

@jccbhb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate<=dateadd(MONTH,-1,@EndDate)

----资金占压利率-日利率

--declare @zjzyll decimal(18,4)

--select @zjzyll=dValue from RCHK_Setting where settingType='zjzyll' and settingCode='3'

DECLARE @fundTable TABLE

(

Id INT,

VoucherDate DATETIME,

FundAmount DECIMAL(18, 5)

DEFAULT 0, --库存资金

CostAmount DECIMAL(18, 5)

DEFAULT 0 --资金成本

);

--将存储过程执行的结果放入表变量中

INSERT INTO @fundTable

select * from RCHK_func_FundAmountTable (@BeginDate,@EndDate,null)

--本期的资金成本

SELECT @zjcb = SUM(CostAmount)

FROM @fundTable

--同比

if(@jccbtb=0)

begin

set @tb2=1;

end

else

begin

set @tb2=@jccb/@jccbtb

end

--环比

if(@jccbhb=0)

begin

set @hb2=1;

end

else

begin

set @hb2=@jccb/@jccbhb

end

--查询期间内销售出库的存货种类数

declare @InventoryCountForSale int

select

@InventoryCountForSale=count(distinct(d.idinventory))

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

where

m.idvouchertype in(select id from sm_vouchertype where code in('ST1021','ST1025'))

and m.voucherState=189

and m.voucherdate>=@BeginDate

and m.voucherdate<=@EndDate

--当前时点仓库中有结存的存货种类数

declare @InventoryCountForWarehouse int

select @InventoryCountForWarehouse=count(idinventory) from(

select

idinventory

from

ST_SubsidiaryBook st

inner join

AA_Inventory aa on st.idinventory=aa.id

where

voucherdate <= @EndDate

group by

idinventory

having

sum(isnull(inQuantity,0))>sum(isnull(outQuantity,0))

)m

--动销率

if(@InventoryCountForWarehouse=0)

begin

set @dxl=1

end

else

begin

set @dxl=@InventoryCountForSale/cast(@InventoryCountForWarehouse as decimal(18,2))

end

--结存成本end-------------------------------------

select c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12 from

(

select 1 as c1,'Single' c2,@cbl c3,@tb c4,@hb c5,@xscb c6,@xssr c7,@jccb c8,@tb2 c9,@hb2 c10,@zjcb c11,@dxl c12

union

select 2,'货值成本',@hzcb,null,null,null,null,null,null,null,null ,null

union

select top 3 3,expenseName,feeAmount,null,null,null,null,null,null,null,null,null from @table

union

select 6,'其他费用',@cgfy-@qtfy,null,null,null,null,null,null,null,null ,null

)m

where c1=1 or c3<>0

order by c1,c3 desc

END;

GO

--成本分析-成本总览-成本率

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisCBL'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisCBL;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisCBL]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@Where NVARCHAR(2000)

--查询条件

AS

BEGIN

--本期的销售成本

declare @xscb decimal

--同比上期的销售成本

declare @xscbtb decimal

--环比上期的销售成本

declare @xscbhb decimal

--本期的销售收入

declare @xssr decimal

--销售成本同比率

declare @tb decimal

--销售成本环比率

declare @hb decimal

--成本率

declare @cbl decimal

--本期的销售成本

select

@xscb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

--同比上期的销售成本

select

@xscbtb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(year,-1,@BeginDate)

and VoucherDate<=dateadd(year,-1,@EndDate)

--环比上期的销售成本

select

@xscbhb=sum(isnull(acc_XSCostAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(MONTH,-1,@BeginDate)

and VoucherDate<=dateadd(MONTH,-1,@EndDate)

--本期的销售收入

select

@xssr=sum(isnull(acc_SAAmount,0))

from

RCHK_SeveralBalance

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

--同比

if(@xscbtb=0)

begin

set @tb=1;

end

else

begin

set @tb=@xscb/@xscbtb

end

--环比

if(@xscbhb=0)

begin

set @tb=1;

end

else

begin

set @tb=@xscb/@xscbhb

end

--计算本期的成本率

if(@xssr=0)

begin

set @cbl=1;

end

else

begin

set @cbl=@xscb/@xssr

end

select '销售成本',@xscb

union

select '环比',@hb

union

select '同比',@tb

union

select '销售收入',@xssr

union

select '成本率',@cbl

END;

GO

--成本分析-成本总览-结存成本

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisJCCB'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisJCCB;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisJCCB]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@Where NVARCHAR(2000)

--查询条件

AS

BEGIN

--结存成本

declare @jccb decimal

--同比上期的结存成本

declare @jccbtb decimal

--环比上期的结存成本

declare @jccbhb decimal

--本期的资金成本

declare @zjcb decimal

--结存成本同比率

declare @tb decimal

--结存成本环比率

declare @hb decimal

--动销率

declare @dxl decimal

--本期的结存成本

select

@jccb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

--同比上期的结存成本

select

@jccbtb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(year,-1,@BeginDate)

and VoucherDate<=dateadd(year,-1,@EndDate)

--环比上期的结存成本

select

@jccbhb=sum(isnull(acc_STBalance,0))

from

RCHK_SeveralBalance

where

VoucherDate>=dateadd(MONTH,-1,@BeginDate)

and VoucherDate<=dateadd(MONTH,-1,@EndDate)

--资金占压利率-日利率

declare @zjzyll decimal

select @zjzyll=dValue from RCHK_Setting where settingType='zjzyll' and settingCode='3'

--本期的资金成本

set @zjcb=@jccb*@zjzyll

--同比

if(@jccbtb=0)

begin

set @tb=1;

end

else

begin

set @tb=@jccb/@jccbtb

end

--环比

if(@jccbhb=0)

begin

set @tb=1;

end

else

begin

set @tb=@jccb/@jccbhb

end

--查询期间内销售出库的存货种类数

declare @InventoryCountForSale int

select

@InventoryCountForSale=count(distinct(d.idinventory))

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

where

m.idvouchertype in(select id from sm_vouchertype where code in('ST1021','ST1025'))

and m.voucherState=189

and m.voucherdate>=@BeginDate

and m.voucherdate<=@EndDate

--当前时点仓库中有结存的存货种类数

declare @InventoryCountForWarehouse int

select

@InventoryCountForWarehouse=count(distinct(idinventory))

from

ST_SubsidiaryBook st

inner join AA_Inventory aa on st.idinventory=aa.id

where

isPeriodInit = 1

or (voucherdate>=@BeginDate and voucherdate <= @EndDate)

--动销率

if(@InventoryCountForWarehouse=0)

begin

set @dxl=1

end

else

begin

set @dxl=@InventoryCountForSale/@InventoryCountForWarehouse

end

select '结存成本',@jccb

union

select '环比',@hb

union

select '同比',@tb

union

select '资金成本',@zjcb

union

select '动销率',@dxl

END;

GO

--成本分析-采购成本分析

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisCGCBFX'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisCGCBFX;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisCGCBFX]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@Where NVARCHAR(2000)

--查询条件

AS

BEGIN

--分组方式 1：存货分类+存货 2：存货分类

declare @groupFlag tinyint

set @groupFlag=@Where

--存货按存货分类id维度下的临时数据

DECLARE @table TABLE

(

idInventory int , --存货id

costAMount DECIMAL(18, 2), --本期金额

avgCost DECIMAL(18, 2), --平均成本

avgCostTB DECIMAL(18, 4), --平均成本同比增长

avgCostHB DECIMAL(18, 4), --平均成本环比增长

--ckCost DECIMAL(18, 2), --参考成本

zxCost DECIMAL(18, 2) --最新成本

);

--查询期间中所有存货本期金额合计

declare @allCost decimal

select

@allCost=sum(CGCostForCost)

from

rchk_CostAnalysis

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

and CGCostForCost is not null

insert into @table(idInventory,costAMount,avgCost,avgCostTB,avgCostHB,zxCost)

select

a.Inventory,

a.CGCostForCost,

a.avgCost,

case isnull(b.avgCost,0) when 0 then 1.00 else (a.avgCost-b.avgCost)/b.avgCost end as avgCostTB ,

case isnull(c.avgCost,0) when 0 then 1.00 else (a.avgCost-c.avgCost)/c.avgCost end as avgCostHB ,

d.taxPrice as zxCost

from

(

--本期数据

select

Inventory,

sum(CGCostForCost) CGCostForCost,

case sum(CGQTYForCost) when 0 then 1.00 else sum(CGCostForCost)/sum(CGQTYForCost) end as avgCost--平均成本

from

rchk_CostAnalysis

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

and CGCostForCost is not null

group by

Inventory

) a

left join

(

--上期同比数据

select

Inventory,

case sum(CGQTYForCost) when 0 then 1.00 else sum(CGCostForCost)/sum(CGQTYForCost) end as avgCost--平均成本

from

rchk_CostAnalysis

where

VoucherDate>=dateadd(year,-1,@BeginDate)

and VoucherDate<=dateadd(year,-1,@EndDate)

and CGCostForCost is not null

group by

Inventory

) b on a.Inventory=b.Inventory

left join

(

--上期环比比数据

select

Inventory,

case sum(CGQTYForCost) when 0 then 0 else sum(CGCostForCost)/sum(CGQTYForCost) end as avgCost--平均成本

from

rchk_CostAnalysis

where

VoucherDate>=dateadd(MONTH,-1,@BeginDate)

and VoucherDate<=dateadd(MONTH,-1,@EndDate)

and CGCostForCost is not null

group by

Inventory

) c on a.Inventory=c.Inventory

inner join

(

--最新成本

select

idinventory,taxPrice

from

ST_RDRecord_b

where

id in

(

select

max(main.ID) id

from

ST_RDRecord_b main

inner join

ST_RDRecord mMain on main.idRDRecordDTO=mMain.id

inner join(

select idinventory,max(voucherdate) voucherdate

from ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

where

idvouchertype =(select id from SM_VoucherType where code='ST1001')

and VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

group by idinventory

) dmain on main.idinventory=dmain.idinventory and mMain.voucherdate=dmain.voucherdate

where

mMain.idvouchertype =(select id from SM_VoucherType where code='ST1001')

group by

main.idinventory

)

) d on a.Inventory=d.idinventory

--按存货分类+存货

if(@groupFlag=1)

begin

select

--inventoryClass.id as InventoryClass,

inventoryClass.name as InventoryClass_Name,

--m.idInventory as Inventory,

inventory.name as Inventory_Name,

costAMount,

case @allCost when 0 then 0 else m.costAMount/@allCost end as avgCostPer,--总成本占比

m.avgCost,

m.avgCostHB,

m.avgCostTB,

inventory.invSCost as ckCost,

m.zxCost,

case isnull(inventory.invSCost,0) when 0 then 1 else (m.zxCost-inventory.invSCost)/inventory.invSCost end as overCost--超出参考成本

from

@table m

inner join

AA_Inventory inventory on m.idInventory=inventory.id

inner join

AA_InventoryClass inventoryClass on inventory.idinventoryclass=inventoryClass.id

order by

costAMount desc

end

--按存货分类

else

begin

select

--inventoryClass.id as InventoryClass,

inventoryClass.name as InventoryClass_Name,

sum(costAMount) as costAmount,

case @allCost when 0 then 1 else sum(m.costAMount)/@allCost end as avgCostPer,--总成本占比

sum(m.avgCost) as avgCost,

sum(m.avgCostHB) as avgCostHB,

sum(m.avgCostTB) as avgCostTB,

avg(inventory.invSCost) as ckCost,

avg(m.zxCost) as zxCost,

case avg(isnull(inventory.invSCost,0)) when 0 then 1 else avg(m.zxCost-inventory.invSCost)/avg(inventory.invSCost) end as overCost--超出参考成本

from

@table m

inner join

AA_Inventory inventory on m.idInventory=inventory.id

inner join

AA_InventoryClass inventoryClass on inventory.idinventoryclass=inventoryClass.id

group by

inventoryClass.id,

inventoryClass.name

order by

sum(costAMount) desc

end

END;

GO

--成本分析-销售成本分析

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisXSCBFX'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisXSCBFX;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisXSCBFX]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@Where NVARCHAR(2000)

--查询条件

AS

BEGIN

--分组方式 1：存货分类+存货 2：存货分类

declare @groupFlag tinyint

set @groupFlag=@Where

--存货按存货分类id维度下的临时数据

DECLARE @table TABLE

(

idInventory int , --存货id

costAmount DECIMAL(18, 2), --本期成本金额

incomeAmount DECIMAL(18, 2), --本期收入金额

zxCost DECIMAL(18, 2), --最新成本

zxPrice DECIMAL(18, 2) --最新售价

);

--最新售价

declare @tablePrice table

(

idInventory int, --存货id

taxPrice decimal(18,2) --最新售价

)

--单据立账

if exists(select * from EAP_AccInformation where Name='SAAccount' and Value=0)

begin

insert into @tablePrice

select idinventory,taxPrice from (

select rank()

over (partition by idinventory order by detail.voucherdate desc,detail.ts desc) as ranking,detail.idinventory ,detail.taxPrice

from

(

select m.voucherdate,d.idinventory,d.taxPrice,d.ts from SA_SaleDelivery m

inner join SA_SaleDelivery_b d on m.ID=d.idSaleDeliveryDTO

where

voucherdate>=@BeginDate

and voucherdate<=@EndDate

union ALL

select m.voucherdate,d.idinventory,d.taxPrice,d.ts from RE_RetailSettle m

inner join RE_RetailSettle_b d on m.ID=d.idRetailSettleDTO

where

voucherdate>=@BeginDate

and voucherdate<=@EndDate

) detail

) mData

where ranking=1

end

else

begin

insert into @tablePrice

select idinventory,taxPrice from (

select rank()

over (partition by idinventory order by detail.voucherdate desc,detail.ts desc) as ranking,detail.idinventory ,detail.taxPrice

from

(

select m.voucherdate,d.idinventory,d.taxPrice,d.ts from SA_SaleInvoice m

inner join SA_SaleInvoice_b d on m.ID=d.idSaleInvoiceDTO

where

voucherdate>=@BeginDate

and voucherdate<=@EndDate

union ALL

select m.voucherdate,d.idinventory,d.taxPrice,d.ts from RE_RetailSettle m

inner join RE_RetailSettle_b d on m.ID=d.idRetailSettleDTO

where

voucherdate>=@BeginDate

and voucherdate<=@EndDate

) detail

) mData

where ranking=1

end

--本期成本金额All

declare @allCost decimal

select

@allCost=sum(SACostForCost)

from

rchk_CostAnalysis

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

and SACostForCost is not null

--本期收入金额All

declare @allIncome decimal

select

@allIncome=sum(SAAMountForCost)

from

rchk_CostAnalysis

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

and SAAMountForCost is not null

insert into @table(idInventory,costAmount,incomeAmount,zxCost,zxPrice)

select

a.Inventory,

a.SACostForCost,

a.SAAMountForCost,

b.taxPrice as zxCost,

c.taxPrice as zxPrice

from

(

--本期数据:销售成本、销售收入

select

Inventory,

sum(isnull(SACostForCost,0)) SACostForCost,

sum(isnull(SAAMountForCost,0)) SAAMountForCost

from

rchk_CostAnalysis

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

group by

Inventory

having

sum(isnull(SACostForCost,0))<>0

) a

left join

(

--最新成本:采购入库单

select idinventory,taxPrice from (

select rank()

over (partition by idinventory order by detail.voucherdate desc,detail.ts desc) as ranking,detail.idinventory ,detail.taxPrice

from

(

select m.voucherdate,d.idinventory,d.taxPrice,d.ts from ST_RDRecord m

inner join ST_RDRecord_b d on m.ID=d.idRDRecordDTO

where

idvouchertype in(select id from sm_vouchertype where code ='ST1001')

and voucherdate>=@BeginDate

and voucherdate<=@EndDate

) detail

) mData

where ranking=1

) b on a.Inventory=b.idinventory

left join

@tablePrice c on a.Inventory=c.idInventory

--按存货分类+存货

if(@groupFlag=1)

begin

select

--inventoryClass.id as InventoryClass,

inventoryClass.name as InventoryClass_Name,

--m.idInventory as Inventory,

inventory.name as Inventory_Name,

m.costAmount,

case @allCost when 0 then 1 else m.costAmount/@allCost end as avgCostPer,--总成本占比

case @allIncome when 0 then 1 else m.incomeAmount/@allIncome end as avgIncomePer,--总收入占比

case m.incomeAmount when 0 then 1 else m.costAmount/m.incomeAmount end as avgCostIncomePer,--成本率

m.zxCost,

m.zxPrice,

case isnull(m.zxPrice,0) when 0 then 1 else m.zxCost/m.zxPrice end as avgCostPricePer--最新成本率

from

@table m

inner join

AA_Inventory inventory on m.idInventory=inventory.id

inner join

AA_InventoryClass inventoryClass on inventory.idinventoryclass=inventoryClass.id

order by

costAmount desc

end

--按存货分类

else

begin

select

--inventoryClass.id as InventoryClass,

inventoryClass.name as InventoryClass_Name,

sum(m.costAmount) as costAmount,

case @allCost when 0 then 1 else sum(m.costAmount)/@allCost end as avgCostPer,--总成本占比

case @allIncome when 0 then 1 else sum(m.incomeAmount)/@allIncome end as avgIncomePer,--总收入占比

case sum(m.incomeAmount) when 0 then 1 else sum(m.costAmount)/sum(m.incomeAmount) end as avgCostIncomePer,--成本率

avg(isnull(m.zxCost,0)) zxCost,

avg(isnull(m.zxPrice,0)) zxPrice,

case avg(isnull(m.zxPrice,0)) when 0 then 1 else avg(isnull(m.zxCost,0))/avg(isnull(m.zxPrice,0)) end as avgCostPricePer--最新成本率

from

@table m

inner join

AA_Inventory inventory on m.idInventory=inventory.id

inner join

AA_Inventory_Cost inventoryCost on inventory.id=inventoryCost.idInventory

inner join

AA_InventoryClass inventoryClass on inventory.idinventoryclass=inventoryClass.id

group by

inventoryClass.id,

inventoryClass.name

order by

sum(costAmount) desc

end

END;

GO

--成本分析-结存成本分析

IF EXISTS

(

SELECT *

FROM sys.procedures

WHERE name = N'RCHK_SP_CostAnalysisJCCBFX'

)

BEGIN

DROP PROCEDURE RCHK_SP_CostAnalysisJCCBFX;

END;

GO

CREATE PROCEDURE [dbo].[RCHK_SP_CostAnalysisJCCBFX]

@MemberCode NVARCHAR(100) = NULL, --指标Code

@DimensionCode NVARCHAR(100) = NULL, --维度Code

@BeginDate NVARCHAR(100), --开始日期

@EndDate NVARCHAR(100), --结束日期

@Where NVARCHAR(2000)

--查询条件

AS

BEGIN

--分组方式 1：仓库 2：存货分类 3：存货

declare @groupFlag tinyint

set @groupFlag=@Where

--存货按存货分类id维度下的临时数据

DECLARE @table TABLE

(

idDimension int , --维度id：仓库、存货分类、存货

currentCount int, --本期销售种类数

jcCount int --本期结存种类数

);

--按仓库

if(@groupFlag=1)

begin

insert into @table(idDimension,currentCount,jcCount)

select d1.idwarehouse,d1.currentCounst,d2.jcCount from

(

select

d.idwarehouse,

count(distinct(inventory.id)) currentCounst

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

inner join AA_Inventory inventory on d.idinventory=inventory.id

where

m.idvouchertype in(select id from sm_vouchertype where code in('ST1021','ST1025'))

and m.voucherState=189

and m.voucherdate>=@BeginDate

and m.voucherdate<=@EndDate

group by d.idwarehouse

) d1

left join

(

select

st.idwarehouse,

count(distinct(aa.id)) as jcCount

from

ST_SubsidiaryBook st

inner join AA_Inventory aa on st.idinventory=aa.id

where

voucherdate <= @EndDate

group by

st.idwarehouse

having sum(isnull(inQuantity,0))>sum(isnull(outQuantity,0))

) d2

on d1.idwarehouse=d2.idwarehouse

end

--按存货分类

else if(@groupFlag=2)

begin

insert into @table(idDimension,currentCount,jcCount)

select d1.idDimension,d1.currentCounst,d2.jcCount from

(

select

inventory.idinventoryclass as idDimension,

count(distinct(inventory.id)) currentCounst

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

inner join AA_Inventory inventory on d.idinventory=inventory.id

where

m.idvouchertype in(select id from sm_vouchertype where code in('ST1021','ST1025'))

and m.voucherState=189

and m.voucherdate>=@BeginDate

and m.voucherdate<=@EndDate

group by inventory.idinventoryclass

) d1

left join

(

select

aa.idinventoryclass as idDimension,

count(distinct(idinventory)) as jcCount

from

ST_SubsidiaryBook st

inner join AA_Inventory aa on st.idinventory=aa.id

where

voucherdate <= @EndDate

group by

aa.idinventoryclass

having sum(isnull(inQuantity,0))>sum(isnull(outQuantity,0))

) d2

on d1.idDimension=d2.idDimension

end

--按存货

else if(@groupFlag=3)

begin

insert into @table(idDimension,currentCount,jcCount)

select d1.idDimension,d1.currentCounst,d2.jcCount from

(

select

distinct(d.idinventory) as idDimension,

1 currentCounst

from

ST_RDRecord_b d

inner join ST_RDRecord m on d.idRDRecordDTO=m.id

where

m.idvouchertype in(select id from sm_vouchertype where code in('ST1021','ST1025'))

and m.voucherState=189

and m.voucherdate>=@BeginDate

and m.voucherdate<=@EndDate

) d1

left join

(

select

st.idinventory as idDimension,

count(distinct(idinventory)) as jcCount

from

ST_SubsidiaryBook st

where

voucherdate <= @EndDate

group by

st.idinventory

having sum(isnull(inQuantity,0))>sum(isnull(outQuantity,0))

) d2

on d1.idDimension=d2.idDimension

end

--资金占压利率-日利率

declare @zjzyll decimal

select @zjzyll=dValue from RCHK_Setting where settingType='zjzyll' and settingCode='3'

DECLARE @fundTable TABLE

(

Id INT,

iddimensioin int,

VoucherDate DATETIME,

FundAmount DECIMAL(18, 5)

DEFAULT 0, --库存资金

CostAmount DECIMAL(18, 5)

DEFAULT 0 --资金成本

);

--将存储过程执行的结果放入表变量中

INSERT INTO @fundTable

select * from RCHK_func_FundAmountTableForCost(@BeginDate,@EndDate,@groupFlag)

--按仓库

if(@groupFlag=1)

begin

select

top 12

--d1.idDimension,

d1.dimensionName,

case when isnull(d2.currentCount,0)=0 then 0 when isnull(d2.jcCount,0)=0 then 1 else isnull(d2.currentCount,0)/cast(d2.jcCount as decimal(18,2)) end as dxl,

d1.jcCost, --结存成本

d3.costAmount --资金成本

from

(

select

Warehouse as idDimension,

Warehouse_Name as dimensionName,

sum(FundAmountForCost) as jcCost,

sum(FundAmountForCost)*@zjzyll as zjCost

from

RCHK_CostAnalysis

where

VoucherDate<=@EndDate

group by

Warehouse,Warehouse_Name

)d1

left join @table d2

on d1.idDimension=d2.idDimension

left join (

select

iddimensioin,sum(CostAmount) costAmount

from @fundTable

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

group by iddimensioin

)d3

on d1.idDimension=d3.iddimensioin

where

d1.jcCost<>0 or d3.costAmount<>0

order by

d1.jcCost desc

end

--按存货分类

else if(@groupFlag=2)

begin

select

top 12

--d1.idDimension,

d1.dimensionName,

case when isnull(d2.currentCount,0)=0 then 0 when isnull(d2.jcCount,0)=0 then 1 else isnull(d2.currentCount,0)/cast(d2.jcCount as decimal(18,2)) end as dxl,

d1.jcCost, --结存成本

d3.costAmount --资金成本

from

(

select

InventoryClass as idDimension,

InventoryClass_Name as dimensionName,

sum(FundAmountForCost) as jcCost,

sum(FundAmountForCost)*@zjzyll as zjCost

from

RCHK_CostAnalysis

where

VoucherDate<=@EndDate

group by

InventoryClass,InventoryClass_Name

)d1

left join @table d2

on d1.idDimension=d2.idDimension

left join (

select

iddimensioin,sum(CostAmount) costAmount

from @fundTable

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

group by iddimensioin

)d3

on d1.idDimension=d3.iddimensioin

where

d1.jcCost<>0 or d3.costAmount<>0

order by

d1.jcCost desc

end

--按存货

else if(@groupFlag=3)

begin

select

top 12

--d1.idDimension,

d1.dimensionName,

case d2.currentCount when 1 then 1 else 0 end as dxl,

d1.jcCost, --结存成本

d3.costAmount --资金成本

from

(

select

Inventory as idDimension,

Inventory_Name as dimensionName,

sum(FundAmountForCost) as jcCost,

sum(FundAmountForCost)*@zjzyll as zjCost

from

RCHK_CostAnalysis

where

VoucherDate<=@EndDate

group by

Inventory,Inventory_Name

)d1

left join @table d2

on d1.idDimension=d2.idDimension

left join (

select

iddimensioin,sum(CostAmount) costAmount

from @fundTable

where

VoucherDate>=@BeginDate

and VoucherDate<=@EndDate

group by iddimensioin

)d3

on d1.idDimension=d3.iddimensioin

where

d1.jcCost<>0 or d3.costAmount<>0

order by

d1.jcCost desc

end

END;

GO

--成本分析指标：采购成本分析，销售成本分析，结存成本分析,成本总览

DELETE FROM RCHK_Member where code in('cbfxcgcbfx','cbfxxscbfx','cbfxjccbfx','cbfxcbzl')

--成本分析报表：采购成本分析，销售成本分析，结存成本分析,成本总览

DELETE FROM dbo.RCHK_Report

WHERE code IN ('cbfxcgcbfx','cbfxxscbfx','cbfxjccbfx','cbfxcbzl');

--成本分析模型id

DECLARE @IDReportModel_CostAnalysis INT;

SELECT @IDReportModel_CostAnalysis=id from rchk_reportmodel where code='CostAnalysis'

--成本分析父报表

DECLARE @idReport_CostAnalysis INT;

SELECT @idReport_CostAnalysis = id

FROM dbo.RCHK_Report

WHERE code = 'CostAnalysis'

INSERT INTO dbo.RCHK_Member (code

, name

, memberType

, isValid

, isAtomExpression

, valueType

, ExpressionName

, content

, tablename

, alias

, selectField

, dataSource

, fixedWhereExpression

, description

, makerId

, createdTime

, reviser)

VALUES

('cbfxcgcbfx'

, '采购成本分析'

, 'Proc'

, 1

, 1

, 'INT'

, NULL

, 'RCHK_SP_CostAnalysisCGCBFX'

, ''

, ''

, ''

, 'Proc'

, NULL

, '采购成本分析'

, NULL

, dbo.getdate()

, NULL)

,

('cbfxxscbfx'

, '销售成本分析'

, 'Proc'

, 1

, 1

, 'INT'

, NULL

, 'RCHK_SP_CostAnalysisXSCBFX'

, ''

, ''

, ''

, 'Proc'

, NULL

, '销售成本分析'

, NULL

, dbo.getdate()

, NULL)

,

('cbfxjccbfx'

, '结存成本分析'

, 'Proc'

, 1

, 1

, 'INT'

, NULL

, 'RCHK_SP_CostAnalysisJCCBFX'

, ''

, ''

, ''

, 'Proc'

, NULL

, '结存成本分析'

, NULL

, dbo.getdate()

, NULL)

,

('cbfxcbzl'

, '成本总览'

, 'Proc'

, 1

, 1

, 'INT'

, NULL

, 'RCHK_SP_CostAnalysisCGCB'

, ''

, ''

, ''

, 'Proc'

, NULL

, '成本总览'

, NULL

, dbo.getdate()

, NULL)

--成本分析：采购成本分析

DECLARE @IDMember_cbfxcgcbfx INT

SELECT @IDMember_cbfxcgcbfx = id

FROM dbo.RCHK_Member

WHERE code = 'cbfxcgcbfx'

--成本分析：销售成本分析

DECLARE @IDMember_cbfxxscbfx INT

SELECT @IDMember_cbfxxscbfx = id

FROM dbo.RCHK_Member

WHERE code = 'cbfxxscbfx'

--成本分析：结存成本分析

DECLARE @IDMember_cbfxjccbfx INT

SELECT @IDMember_cbfxjccbfx = id

FROM dbo.RCHK_Member

WHERE code = 'cbfxjccbfx'

--成本分析：成本总览

DECLARE @IDMember_cbfxcbzl INT

SELECT @IDMember_cbfxcbzl = id

FROM dbo.RCHK_Member

WHERE code = 'cbfxcbzl'

INSERT INTO dbo.RCHK_Report (idReportModel

, idDimension

, code

, name

, showIndex

, visible

, reportType

, description

, makerId

, createdTime

, idMembers

, orderby

, reviser

, idparent)

VALUES

(@IDReportModel_CostAnalysis

, NULL

, 'cbfxcgcbfx'

, '采购成本分析'

, 300

, 1

, 'grid'

, '采购成本分析'

, NULL

, dbo.getdate ()

, @IDMember_cbfxcgcbfx

, 'DESC'

, NULL

, @idReport_CostAnalysis)

,

(@IDReportModel_CostAnalysis

, NULL

, 'cbfxxscbfx'

, '销售成本分析'

, 300

, 1

, 'grid'

, '销售成本分析'

, NULL

, dbo.getdate ()

, @IDMember_cbfxxscbfx

, 'DESC'

, NULL

, @idReport_CostAnalysis)

,

(@IDReportModel_CostAnalysis

, NULL

, 'cbfxjccbfx'

, '结存成本分析'

, 300

, 1

, 'grid'

, '结存成本分析'

, NULL

, dbo.getdate ()

, @IDMember_cbfxjccbfx

, 'DESC'

, NULL

, @idReport_CostAnalysis)

,

(@IDReportModel_CostAnalysis

, NULL

, 'cbfxcbzl'

, '成本总览'

, 300

, 1

, 'grid'

, '成本总览'

, NULL

, dbo.getdate ()

, @IDMember_cbfxcbzl

, 'DESC'

, NULL

, @idReport_CostAnalysis)

GO

--成本分析话术

--成本分析模型

DECLARE @IDReportModel_CostAnalysis INT;

select

@IDReportModel_CostAnalysis=id

FROM

RCHK_ReportModel

WHERE

code = 'CostAnalysis'

DELETE FROM [RCHK_Info]

WHERE reportName = 'cbfxcbzl'

INSERT INTO [dbo] .[RCHK_Info] ([idReportModel]

, [code]

, [name]

, [reportName]

, [infoType]

, [content])

VALUES

(@IDReportModel_CostAnalysis

, 'cbfxCbzl-yj1'

, '查询期间内成本率>'

, 'cbfxcbzl'

, ''

, '90')

DELETE FROM RCHK_Member

WHERE code IN ( 'CostRate');

INSERT INTO dbo.RCHK_Member (code

, name

, memberType

, isValid

, isAtomExpression

, valueType

, ExpressionName

, content

, tablename

, alias

, selectField

, dataSource

, fixedWhereExpression

, description

, makerId

, createdTime

, reviser)

VALUES

('CostRate'

, '成本率'

, 'Single'

, 1

, 0

, 'PERCENTAGE'

, NULL

, ' CASE WHEN sum(SAAMountForCost) = 0 THEN

100

ELSE

(sum(SACostForCost) * 100) / sum(SAAMountForCost)

END '

, 'RCHK_CostAnalysis'

, 'Cost'

, ''

, 'Table'

, NULL

, '查询期间内的销售成本/销售收入*100%'

, NULL

, dbo.getdate()

, NULL)

DELETE FROM [RCHK_AnalysisModel]

WHERE reportName IN ('cbfxcbzl','cbfxcgcbfx','cbfxxscbfx','cbfxjccbfx')

INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

, [title]

, [reportName]

, [content]

, [showIndex]

, [showType]

, [reportModelCode]

, [color]

, [expressionValue])

VALUES

('CostOverview0'

, '成本总览分析0'

, 'cbfxcbzl'

, '当前成本率大于[cbfxCbzl-yj1]%预警值，请及时控制成本以保证利润'

, 0

, 51862

, NULL

, 'red'

, '{CostRate} > [cbfxCbzl-yj1]')

INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

, [title]

, [reportName]

, [content]

, [showIndex]

, [showType]

, [reportModelCode]

, [color]

, [expressionValue])

VALUES

('CostOverview1'

, '成本总览分析1'

, 'cbfxcbzl'

, '降低成本可从采购成本、销售成本、结存成本着手'

, 0

, 51862

, NULL

, 'blue'

, '{CostRate} <= [cbfxCbzl-yj1]')

INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

, [title]

, [reportName]

, [content]

, [showIndex]

, [showType]

, [reportModelCode]

, [color]

, [expressionValue])

VALUES

('PUCostAnalysis1'

, '采购成本分析1'

, 'cbfxcgcbfx'

, '总成本占比较高，成本环同比增长较快或超出参考成本%较大的存货需要及时控制采购成本，可尝试优化供应商或大批量采购等形式'

, 0

, 51862

, NULL

, 'blue'

, '1=1')

--INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

-- , [title]

-- , [reportName]

-- , [content]

-- , [showIndex]

-- , [showType]

-- , [reportModelCode]

-- , [color]

-- , [expressionValue])

--VALUES

-- ('PUCostAnalysis2'

-- , '采购成本分析2'

-- , 'cbfxcgcbfx'

-- , '总成本占比较高，成本环同比增长较快或超出参考成本%较大的存货需要及时控制采购成本，可尝试优化供应商或大批量采购等形式'

-- , 0

-- , 51862

-- , NULL

-- , 'green'

-- , '1=1')

INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

, [title]

, [reportName]

, [content]

, [showIndex]

, [showType]

, [reportModelCode]

, [color]

, [expressionValue])

VALUES

('cbfxXscbfx0'

, '销售成本分析0'

, 'cbfxxscbfx'

, '总成本占比较高的存货成本率不达标时需要控制其成本或提高售价以保证利润'

, 0

, 51862

, NULL

, 'blue'

, '1=1')

--INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

-- , [title]

-- , [reportName]

-- , [content]

-- , [showIndex]

-- , [showType]

-- , [reportModelCode]

-- , [color]

-- , [expressionValue])

--VALUES

-- ('cbfxXscbfxChfl0'

-- , '销售成本分析存货分类0'

-- , 'cbfxxscbfx'

-- , '总成本占比较高的存货成本率不达标时需要控制其成本或提高售价以保证利润'

-- , 0

-- , 51862

-- , NULL

-- , 'blue'

-- , '1=1')

INSERT INTO [dbo] .[RCHK_AnalysisModel] ([name]

, [title]

, [reportName]

, [content]

, [showIndex]

, [showType]

, [reportModelCode]

, [color]

, [expressionValue])

VALUES

('cbfxCkcbdb0'

, '结存成本分析0'

, 'cbfxjccbfx'

, '查询期间内仓库的结存金额、资金成本均高的仓库可结合动销率判断是否需要降低库存'

, 0

, 51862

, NULL

, 'blue'

, '1=1')

GO