--upd by ym on 2009.11.17
/*今后ECCS_WEB上接受用户上传数据到10.54. 自动写入10.15的存储过程已被禁止（因为财务不做，只好IT做）
以要导入200910 月的数据， IT要做如下代码
*/
SELECT yearmon,tid,km,qm,nc,leiji,je,jid,jname,
CONVERT(CHAR(4), substring(yearmon,0,5)) as ZYEAR,
CONVERT(CHAR(3), '0' + substring(yearmon,5,3)) as ZMON
,'CNY' AS HB,LRQJ =
CASE (CONVERT(CHAR(3), '0' + substring(yearmon,5,3)))
WHEN '001' THEN
case a.tid
when 1 then qm
when 2 then leiji
when 3 then je
when 4 then je
end
ELSE --其它月份
case a.tid
when 1 then
ISNULL(( A.QM - isnull((SELECT TOP 1 QM FROM Z_ECCS AS E WHERE E.YEARMON = cast(A.yearmon -1 as varchar(6)) AND E.TID = 1 AND E.KM = A.KM ),0) ),0)
when 2 then
ISNULL(( A.LEIJI - isnull((SELECT TOP 1 LEIJI FROM Z_ECCS AS B WHERE B.YEARMON = cast(A.yearmon -1 as varchar(6)) AND B.TID = 2 AND B.KM = A.KM ),0) ),0)
when 3 then
ISNULL(( A.JE - isnull((SELECT TOP 1 JE FROM Z_ECCS AS C WHERE C.YEARMON = cast(A.yearmon -1 as varchar(6)) AND C.TID = 3 AND C.KM = A.KM ),0) ),0)
when 4 then
ISNULL(( A.JE - isnull((SELECT TOP 1 JE FROM Z_ECCS AS D WHERE D.YEARMON = cast(A.yearmon -1 as varchar(6)) AND D.TID = 4 AND D.KM = A.KM ),0) ),0)
end
END
into Z_ECCS_200910
FROM Z_ECCS_from15 AS A
where yearmon = 200910
--drop table Z_ECCS_200910
-----
---10.15上上传 数据
INSERT INTO Z_ECCS (YEARMON,TID,KM,QM,NC,LEIJI,JE,JID,JNAME,zyear,zmon,hb,lrqj)
SELECT YEARMON,TID,KM,QM,NC,LEIJI,JE,JID,JNAME,zyear,zmon,hb,lrqj FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=172.16.10.54;User ID=s****;Password=*****'
).[aspnetdb].dbo.Z_ECCS_200910
---10.15上对 固定资产金额进行检查 看期间值是否为累计的月差
select * from z_eccs where yearmon in(200907,200908,200909, 200910)
and jid = '8307000000'
order by yearmon
ALTER VIEW [dbo].[V_ZECCS] as
select z_eccs.yearmon as YEARMON , tid as TID,KM AS KM ,QM AS QM,NC AS NC ,LEIJI AS LEIJI,JE AS JE,JID AS JID ,JNAME AS JNAME,ZYEAR,ZMON,HB,LRQJ
--,leiji+lrqj as newa
FROM Z_ECCS
where isup = 0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
select * from Z_ECCS
alter table Z_ECCS add isup int default 1
update Z_ECCS set isup = 1 where yearmon <> 200910
update Z_ECCS set isup = 0 where yearmon = 200910