存储过程新方法萌芽 *——*

存储过程新方法萌芽 ——

  1 alter procedure [LC0019999].[pro_zlzjzxzb]
  2 
  3 @dw   varchar(88),
  4 @ksrq varchar(8),
  5 @jsrq varchar(8)
  6 
  7 as 
  8 begin
  9 create table #T_01
 10 (
 11     F_01 varchar(8),    /*单位编号*/
 12     F_02 varchar(8),    /*单据类型*/
 13     F_03 varchar(60),    /*账户编号*/
 14     F_04 varchar(8),    /*账户类型*/
 15     F_05 varchar(8),    /*账户组*/
 16     F_06 varchar(60),    /*对方账户*/
 17     F_0601 varchar(8),    /*对方单位*/
 18     F_07 DECIMAL(18,4),    /*增加额*/
 19     F_08 DECIMAL(18,4),    /*减少额*/
 20     F_09 DECIMAL(18,4),    /*调拨额*/
 21 
 22     F_0101 varchar(80),    /*单位名称*/
 23     F_10 DECIMAL(18,4),    /*上周账户余额*/
 24     F_11 DECIMAL(18,4), /*内部户*/
 25     F_12 DECIMAL(18,4), /*归集行*/
 26     F_13 DECIMAL(18,4),    /*其他账户*/
 27     F_14 DECIMAL(18,4),    /*合计*/
 28     F_15  VARCHAR(4),    /*货币编号*/
 29     F_16  VARCHAR(8),    /*货币名称*/
 30     F_17  DECIMAL(18,4),/*汇率*/
 31 
 32 
 33     F_20 varchar(8)
 34 )
 35 (SELECT  LSBZDW.LSBZDW_DWBH hsdw into #hsdw FROM LSBZDW LSBZDW WHERE  
 36         ( LSBZDW.LSBZDW_DWBH IN (SELECT  DISTINCT AO_DATA FROM GSPAURESULT WHERE BIZOBJID='GlobalId' AND BIZOPID='GlobalId' AND AO_ID='GSP_SCF_LSBZDW' AND 
 37         OWNERID=@dw) ) )
 38 
 39 INSERT INTO #T_01
 40 select 
 41 zjzh_dwbh F_01,'' F_02,zjzh_zhbh F_03,zjzh_lxbh F_04,zjzh_zhz F_05,'' F_06,'' F_0601,0 F_07,0 F_08,0 F_09,lsbzdw_dwmc F_0101,
 42 0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'000' F_20
 43 from zjzh 
 44 left join lsbzdw on zjzh_dwbh=lsbzdw_dwbh
 45 left join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
 46 where   ZJZH_DWBH in (select hsdw from #hsdw )
 47 
 48 INSERT INTO #T_01
 49 select 
 50 zjzh_DWBH F_01,rq F_02,zjzh_zhbh F_03,zjzh_nwbz F_04,zjzh_zhz F_05,zjzh_lxbh F_06,'' F_0601,0 F_07,0 F_08,dqye F_09,lsbzdw_dwmc F_0101,
 51 0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,bzbh F_15,'' F_16,0 F_17,'YE00' F_20
 52 from(
 53 select zjyhzhyeb_rq rq,zjyhzhyeb_zhnm,zjzh_zhbh,zjzh_mc,zjzh_nwbz,zjzh_XZBH,ZJZHXZ_XZMC,zjzh_DWBH,LSBZDW_DWMC,zjzh_lxbh,zjzh_zhz,
 54 zjzhz_mc,zjyhzhyeb_bz bzbh,lswbzd_bzmc,zjyhzhyeb_dqye dqye  
 55 from  zjzh
 56 inner join zjyhzhyeb on zjzh_zhnm=zjyhzhyeb_zhnm
 57 inner join LSBZDW ON zjzh_DWBH=LSBZDW_DWBH
 58 inner join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
 59 inner join zjzhz  on zjzhz_zhzbh=zjzh_zhz 
 60 inner join lswbzd  on   lswbzd_bzbh=zjyhzhyeb_bz
 61 where 1=1
 62 and zjyhzhyeb_rq between dateadd(d,-1,@ksrq) and  @jsrq 
 63 and ZJZH_DWBH in (select hsdw from #hsdw )
 64 union all
 65 select zjzhryeb_rq rq,zjzhryeb_zhnm,zjzh_zhbh,zjzh_mc,zjzh_nwbz,zjzh_XZBH,ZJZHXZ_XZMC,zjzh_DWBH,LSBZDW_DWMC,zjzh_lxbh,zjzh_zhz,
 66 zjzhz_mc,zjzhryeb_bzbh bzbh,lswbzd_bzmc,abs(zjzhryeb_qcye) dqye 
 67 from  zjzh
 68 inner join zjzhryeb on zjzh_zhnm=zjzhryeb_zhnm and zjzh_nwbz='0'
 69 inner join LSBZDW ON zjzh_DWBH=LSBZDW_DWBH
 70 inner join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
 71 inner join lswbzd  on   lswbzd_bzbh=zjzhryeb_bzbh
 72 inner join zjzhz  on zjzhz_zhzbh=zjzh_zhz  
 73 where 1=1
 74 and zjzhryeb_rq between dateadd(d,-1,@ksrq) and @jsrq
 75 and ZJZH_DWBH in (select hsdw from #hsdw )
 76 ) yeb
 77 
 78 
 79 INSERT INTO #T_01
 80 SELECT 
 81 zh.zjzh_dwbh F1,zjjsdj_djlx F2,zh.ZJZH_ZHBH F3,zh.zjzh_lxbh F4,zh.zjzh_zhz F5,ZJJSDJ_DFZH F6,df.zjzh_dwbh F61,
 82 isnull(SUM(CASE  WHEN ZJJSDJ_DJLX IN ('01','06','07','11','15') THEN ZJJSDJ_JE  END),0) F7,
 83 isnull(SUM(CASE  WHEN ZJJSDJ_DJLX IN ('02','05','09','12','15') THEN ZJJSDJ_JE  END),0) F8,
 84 isnull(SUM(CASE  WHEN ZJJSDJ_DJLX IN ('15') THEN ZJJSDJ_JE  END),0)     F9,
 85 LSBZDW_DWmc F_0101,0 F_10,0 F11,0 F_12,0 F_13,0 F_14,zjjsdj_yhbz F_15,'' F_16,1 F_17,
 86 '0' F20 
 87 FROM ZJZH zh
 88 LEFT JOIN ZJJSDJ ON ZJZH_ZHNM=ZJJSDJ_YHZH  OR ZJZH_ZHNM=ZJJSDJ_NBZH
 89 left join zjzh DF on df.zjzh_zhbh=zjjsdj.zjjsdj_dfzh
 90 left join LSBZDW ON zh.zjzh_DWBH=LSBZDW_DWBH
 91 left join zjzhxz ON zh.zjzh_XZBH=ZJZHXZ_XZBH
 92 WHERE 1=1
 93 and ZJJSDJ_DJZT='11'    
 94 AND zjjsdj_ywrq>=@ksrq   
 95 and zjjsdj_ywrq<=@jsrq
 96 and zh.ZJZH_DWBH in (select hsdw from #hsdw )
 97 GROUP BY zh.zjzh_dwbh,zjjsdj_djlx,zh.ZJZH_ZHBH,ZJJSDJ_DFZH,zh.zjzh_lxbh,zh.zjzh_zhz,df.zjzh_dwbh,LSBZDW_DWmc,zjjsdj_yhbz,zh.zjzh_XZBH,ZJZHXZ_XZMC
 98 
 99 /*---汇率------------------------------------------------------*/
100 insert into #T_01
101 select 
102 '' F_01,'' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,0 F_07,0 F_08,0 F_09,'' F_0101,
103 0 F_10,0 F_11,0 F_12,0 F_13,isnull(hl.lshl_hl,1) F_14,LSWBZD_BZbh F_15,LSWBZD_BZMC F_16,isnull(hl.lshl_qmhl,1) F_17,'HL00' F_20
104 from LSWBZD zd
105 left join LSHLLX2015 lx on zd.LSWBZD_BZbh= lx.lshllx_ybz
106 left join  LSHL2015 hl on lx.LSHLLX_BH=hl.lshl_bh
107 WHERE LSHL_QJXH='08' or zd.LSWBZD_BZbh='01'
108 
109 update #T_01 set F_14=HL.F_14,F_16=HL.F_16,F_17=HL.F_17 
110 from  #T_01 A,(select * from #T_01 B  where  B.F_20='HL00') HL  where  A.F_20 in ('0','YE00') and A.F_15=HL.F_15
111 
112 --select top 1 * from #T_01 where F_20 in ('0','YE00')  and F_15<>'01' and F_01='huaqi' and F_03='3602000109200173293'
113 update #T_01 set F_07=F_07*F_17,F_08=F_08*F_17,F_09=F_09*F_17 where F_20 in ('0','YE00')
114 --select top 1 * from #T_01 where F_20 in ('0','YE00')  and F_15<>'01'  and F_01='huaqi'   and F_03='3602000109200173293'
115 
116     /*---上周账户余额-----*/
117 insert into #T_01
118 select F_01,'','','','','','',0,0,sum(F_09),F_0101,sum(F_09) F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE01' F_20
119 from #T_01 where F_02=dateadd(d,-1,'20150810') and F_20='YE00'  and F_01<>'jszx' group by F_01,F_0101
120     /*---内部户-----*/
121 insert into #T_01
122 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,sum(F_09) F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE02' F_20
123 from #T_01 where F_02='20150816' and F_04='0' and F_20='YE00'  and F_01<>'jszx' group by F_01,F_0101
124     /*---归集行-----*/
125 insert into #T_01
126 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,0 F_11,sum(F_09) F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE03' F_20 
127 from #T_01 where F_02='20150816' and F_05 in ('01','02','06','07') and F_20='YE00'  and F_01<>'jszx' group by F_01,F_0101
128     /*---其他户-----*/
129 insert into #T_01
130 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,sum(F_09) F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE04' F_20
131 from #T_01 where F_02='20150816' and F_05='04' and F_20='YE00'  and F_01<>'jszx' group by F_01,F_0101
132 
133 
134 
135     /*---1. +调拨-----------------------------------*/
136 insert into #T_01
137 select F_01,'','','','','','',sum(F_07),sum(F_08),sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'1' F_20
138     from #T_01 where F_20='0'  and F_01<>'jszx'  group by F_01,F_0101
139 union all
140 select F_01,'','','','','','',sum(F_07),sum(F_08),sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'20' F_20 
141     from #T_01  where F_20='0' and F_04='08' and F_05='03'  and F_01<>'jszx'  group by F_01,F_0101  /**/
142 
143     /*---2.本周增+减---------------------*/
144 insert into #T_01
145 select F_01,'','','','','','',-abs(sum(F_07+F_08)),-abs(sum(F_07+F_08)),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'2' F_20
146 from #T_01 where F_20='20'  and F_01<>'jszx' group by F_01,F_0101
147 
148     /*---3.本企业内部银行账户转账-----------------*/
149 insert into #T_01
150 select F_01,'','','','','','',sum(F_07),0,0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'31' F_20
151 from #T_01 where F_02 in ('01','11','15') and F_0601=F_01 and F_20='0'  and F_01<>'jszx' group by F_01,F_0101
152 union all
153 select F_01,'','','','','','',0,sum(F_08),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'31' F_20 
154 from #T_01 where F_02 in ('02','12','15') and F_0601=F_01 and F_20='0'  and F_01<>'jszx' group by F_01,F_0101
155 insert into #T_01
156 select F_01,'','','','','','',-abs(sum(F_07)),-abs(sum(F_08)),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'3' F_20 
157 from #T_01 where F_20='31' group by F_01,F_0101
158 
159     /*---4.汇总  4---------------------------------*/
160 insert into #T_01
161 select F_01,'','','','','','',sum(F_07),sum(F_08),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'4' F_20 
162 from #T_01 where F_20 in ('1','2','3') group by F_01,F_0101
163 
164     /*---5.汇总 增加、减少额、上周余额、内部... 5---------------------------------*/
165 insert into #T_01
166 select F_01,'','','','','','',sum(F_07),sum(F_08),0,F_0101,sum(F_10),sum(F_11),SUM(F_12),sum(F_13),Sum(F_14),'' F_15,'' F_16,0 F_17,'5' F_20 
167 from #T_01 where F_20 in ('4','YE01','YE02','YE03','YE04')  group by F_01,F_0101
168 
169 
170 /*==========JSZX=====================*/
171 
172 INSERT INTO #T_01
173 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZXYE' F_20 
174 from #T_01 where F_01 ='jszx' and F_06='03'  and F_20 in ('YE00')
175 UNION ALL
176 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,-ABS(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZXYE' F_20 
177 from #T_01 where F_01 <>'jszx' and F_06='08'  and F_05='03'  and F_20 in ('YE00')
178 
179     /*---上周账户余额-----*/
180 INSERT INTO #T_01
181 select F_01,'','','','','','',0,0,0, F_0101,sum(F_09) F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX01' F_20
182 from #T_01 where F_02=dateadd(d,-1,'20150810') and F_20='JSZXYE' group by F_01,F_0101
183 
184 
185     /*---内部户-----*/
186 INSERT INTO #T_01
187 select F_01,'','','','','','',0,0,0,  F_0101,0 F_10,sum(F_09) F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX02' F_20
188 from #T_01 where F_02='20150816' AND F_04='0' and F_20='JSZXYE' group by F_01,F_0101
189 
190     /*---归集行-----没数据*/
191 INSERT INTO #T_01
192 select F_01,'','','','','','',0,0,0, F_0101,0 F_10,0 F_11,sum(F_09) F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX03' F_20
193 from #T_01 where F_02='20150816' and F_05 in ('01','02','06','07') and F_20='JSZXYE' group by F_01,F_0101
194 
195     /*---其他户-----*/
196 insert into #T_01
197 select F_01,'','','','','','',0,0,0, F_0101,0 F_10,0 F_11,0 F_12,sum(F_09)  F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX04' F_20
198 from #T_01 where F_02='20150816' and F_05='04' and F_20='JSZXYE' group by F_01,F_0101
199 
200 insert into #T_01
201 SELECT  '','','','','','','',0,0,0,'' F_0101,sum(F_10),sum(F_11),sum(F_12),sum(F_13),0 F_14,'' F_15,'' F_16,0 F_17,'JSZX05' F_20
202 FROM #T_01 WHERE F_20 IN ('JSZX01','JSZX02','jszx03','jszx04') group by F_01,F_0101
203 
204     /*---1.本周增减额 -----*/
205 insert into #T_01
206 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'6' F_20 
207 from #T_01 where F_01 ='jszx' and F_04='03'  and F_20 in ('0')
208 union all
209 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,-ABS(F_07),-ABS(F_08),F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'6' F_20 
210 from #T_01 where F_01<>'jszx' and F_04='08'  and F_05='03' and F_20 in ('0')
211 
212     /*---2.汇总账户金额--------------------*/
213 insert into #T_01
214 select '','',F_03,'','','','',sum(F_07),sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'7' F_20  
215 from #T_01 where F_20='6' GROUP BY F_03
216 
217     /*---3.本企业内部银行账户转账-------------*/
218 INSERT INTO #T_01
219 select '','','','','','','',sum(F_07),0,0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'30' F_20   
220 from #T_01 where F_02 in ('01','11','15') and F_0601='JSZX' and F_20='6'
221 union all
222 select '','','','','','','',0,sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'30' F_20   
223 from #T_01 where F_02 in ('02','12','15') and F_0601='JSZX' and F_20='6'
224 INSERT INTO #T_01
225 select F_01,'','','','','','',-abs(sum(F_07)),-abs(sum(F_08)),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'8' F_20   
226 from #T_01 where F_20='30' group by F_01
227 delete #T_01 where F_20='30'
228 
229     /*---4.JSZX 9 -----------------------------*/
230 insert into #T_01
231 select '','','','','','','',sum(F_07),sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'9' F_20   
232 from #T_01 where F_20 IN ('7','8')
233 
234     /*---5.JSZX 9 -----------------------------*/
235 insert into #T_01
236 select 'JSZX','','','','','','',sum(F_07),sum(F_08),0, '广东省旅游控股集团有限公司资金结算中心' F_0101,sum(F_10),sum(F_11),SUM(F_12),sum(F_13),Sum(F_14),'' F_15,'' F_16,0 F_17,'jszx00' F_20 
237 from #T_01 where F_20 in ('9','jszx05')
238 
239 
240     /*---  -----------------------------*/
241 update #T_01 set F_02=zjzh_xzBH from #T_01 left join zjzh on F_01=zjzh_dwbh where F_20 in ('5','jszx00') 
242 update #T_01 set F_03=ZJZHXZ_XZmc from #T_01 left join zjzhxz ON F_02=ZJZHXZ_XZBH  where F_20 in ('5','jszx00') 
243 
244 
245 insert into #T_01
246 select '▉小计' F_01,F_02+'01' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07),sum(F_08),sum(F_09),F_03,sum(F_10),sum(F_11),sum(F_12),sum(F_13),sum            (F_14), '' F_15,'' F_16,0 F_17,'jszxxj' F_20 
247 from #T_01 where F_20 in ('5','jszx00') group by F_02,F_03
248 
249 insert into #T_01
250 select '' F_01,'9000' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07),sum(F_08),sum(F_09),'▉合计',sum(F_10),sum(F_11),sum(F_12),sum(F_13),sum            (F_14), '' F_15,'' F_16,0 F_17,'jszxhj' F_20 
251 from #T_01 where F_20 in ('5','jszxxj')
252 
253 insert into #T_01
254 SELECT '' F_01,'9010' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,-abs(sum(zjjsdj_je)) F_07,0 F_08,0 F_09,'▉减：内部单位往来' F_0101,0 F_10,0 F_11,
255 0 F_12,0 F_13,0 F_14, '' F_15,'' F_16,0 F_17,'nbwl' F_20 
256 FROM ZJZH zh
257 LEFT JOIN ZJJSDJ ON ZJZH_ZHNM=ZJJSDJ_YHZH  OR ZJZH_ZHNM=ZJJSDJ_NBZH
258 WHERE 1=1
259 and ZJJSDJ_DJZT='11'    
260 AND zjjsdj_ywrq>=@ksrq   
261 and zjjsdj_ywrq<=@jsrq
262 and zh.ZJZH_DWBH in (select hsdw from #hsdw )
263 and zjjsdj_wldwid in (select hsdw from #hsdw )
264 
265 insert into #T_01
266 SELECT 
267 '' F_01,'9020' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07) F_07,0 F_08,0 F_09,'▉合计' F_0101,0 F_10,0 F_11,
268 0 F_12,0 F_13,0 F_14, '' F_15,'' F_16,0 F_17,'nbwlhj' F_20 
269 from #T_01 where F_20 in ('jszxhj','nbwl')
270 
271 update #T_01 set F_14=F_10+F_07-F_08  where F_20 in ('jszx00','5','jszxxj','jszxhj','nbwl','nbwlhj')
272 
273 select F_01,F_0101,F_10,F_07,F_08,F_11,F_12,F_13,F_14 from #T_01 where F_20 in ('jszx00','5','jszxxj','jszxhj','nbwl','nbwlhj') order by F_02
274 
275 
276 
277 end
278 
279 
280 go
281 [pro_zlzjzxzb] '406fa89b-e1d2-4869-9380-2793b581aa0a','20150810','20150816'
posted on 2015-08-22 20:43 陸九淵阅读(311) 评论(1) 收藏举报
刷新页面返回顶部
陸九淵

导航

存储过程新方法萌芽 ——
