|
|
马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？注册账号
×
【问题现象】升级脚本出错，错误信息：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
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
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
AND ISNULL(FundAmountForCost, 0) != 0
group by Inventory
order by sum(FundAmountForCost) desc
end
WHILE @date
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
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
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
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
--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
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
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
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
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 isPeriodBeginning1
and sourceVoucherAuditor is not null
group by M.sourceVoucherDate) b
ON a.VoucherDate = b.sourceVoucherDate
END
if(@EnableModule0)
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
--同比上期的销售成本
select
@xscbtb=sum(isnull(acc_XSCostAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(year,-1,@BeginDate)
and VoucherDate
--环比上期的销售成本
select
@xscbhb=sum(isnull(acc_XSCostAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(MONTH,-1,@BeginDate)
and VoucherDate
--本期的销售收入
select
@xssr=sum(isnull(acc_SAAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=@BeginDate
and VoucherDate
--同比
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
--同比上期的结存成本
select
@jccbtb=sum(isnull(acc_STBalance,0))
from
RCHK_SeveralBalance
where
VoucherDate
--环比上期的结存成本
select
@jccbhb=sum(isnull(acc_STBalance,0))
from
RCHK_SeveralBalance
where
VoucherDate
----资金占压利率-日利率
--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
--当前时点仓库中有结存的存货种类数
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
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 c30
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
--同比上期的销售成本
select
@xscbtb=sum(isnull(acc_XSCostAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(year,-1,@BeginDate)
and VoucherDate
--环比上期的销售成本
select
@xscbhb=sum(isnull(acc_XSCostAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(MONTH,-1,@BeginDate)
and VoucherDate
--本期的销售收入
select
@xssr=sum(isnull(acc_SAAmount,0))
from
RCHK_SeveralBalance
where
VoucherDate>=@BeginDate
and VoucherDate
--同比
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
--同比上期的结存成本
select
@jccbtb=sum(isnull(acc_STBalance,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(year,-1,@BeginDate)
and VoucherDate
--环比上期的结存成本
select
@jccbhb=sum(isnull(acc_STBalance,0))
from
RCHK_SeveralBalance
where
VoucherDate>=dateadd(MONTH,-1,@BeginDate)
and VoucherDate
--资金占压利率-日利率
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
--当前时点仓库中有结存的存货种类数
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
--动销率
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
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
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
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
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
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
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
) 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
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
) detail
) mData
where ranking=1
end
--本期成本金额All
declare @allCost decimal
select
@allCost=sum(SACostForCost)
from
rchk_CostAnalysis
where
VoucherDate>=@BeginDate
and VoucherDate
and SACostForCost is not null
--本期收入金额All
declare @allIncome decimal
select
@allIncome=sum(SAAMountForCost)
from
rchk_CostAnalysis
where
VoucherDate>=@BeginDate
and VoucherDate
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
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
) 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
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
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
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
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
) d1
left join
(
select
st.idinventory as idDimension,
count(distinct(idinventory)) as jcCount
from
ST_SubsidiaryBook st
where
voucherdate
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
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
group by iddimensioin
)d3
on d1.idDimension=d3.iddimensioin
where
d1.jcCost0 or d3.costAmount0
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
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
group by iddimensioin
)d3
on d1.idDimension=d3.iddimensioin
where
d1.jcCost0 or d3.costAmount0
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
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
group by iddimensioin
)d3
on d1.idDimension=d3.iddimensioin
where
d1.jcCost0 or d3.costAmount0
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}
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 |
|
|站长微信|Archiver|手机版|小黑屋|用友之家
( 蜀ICP备07505338号|
51072502110008 )
IP卡
狗仔卡
发表于 2023-3-23 21:31:00
提升卡
置顶卡
变色卡
千斤顶
显身卡