供电局MIS管理信息系统.rar

--对报表内容进行初始化 use scmis go if object_id("p_tj_new_report_create") is not null drop proc p_tj_new_report_create go create procedure p_tj_new_report_create @u_send_month smalldatetime,@u_table_code numeric(10), @u_report_unit integer,@master_unit char(40),@u_filler char(10), @u_run_status char(1),@u_lib_info char(1) output --参数分别为统计月份、库表编号、报送单位、运行控制参数、库中数据情况 as begin /*************************定义变量********************/ declare @u_rowcount numeric(5),--确定库中是否有数据 @u_report_type char(1),--对于同一数据库表而不同报表的区别 @u_year integer,--统计年度 @u_quarter int,--统计季度 @u_number numeric(5)--临时使用字段 begin --确定库中情况 if @u_table_code = 102010 --线路情况统计 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_CIRCUIT_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_CIRC_STAT_DETAIL where YEAR_MONTH = @u_send_month delete from TJ_CIRCUIT_TABLE where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_CIRCUIT_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 7 begin insert into TJ_CIRC_STAT_DETAIL(YEAR_MONTH,ROW_SEQU) values(@u_send_month,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end --对线路进行统计,与生计进行接口 -- exec p_tj_circuit_dev_sta end --确定库中情况 else if @u_table_code = 101040 --县局及供电站人员年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_DEPT_LABOR_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_DEPT_LABOR_TABLE where YEAR_MONTH = @u_send_month delete from TJ_DEPT_LABOR where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_DEPT_LABOR_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_DEPT_LABOR(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 106010 --县局及供电站人员年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_LINE_LOSS_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_LINE_LOSS_TABLE where YEAR_MONTH = @u_send_month delete from TJ_LINE_LOSS where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_LINE_LOSS_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_LINE_LOSS(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 111050 --县局及供电站人员年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_LINE_LOSS_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_LINE_LOSS_TABLE where YEAR_MONTH = @u_send_month delete from TJ_LINE_LOSS where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_LINE_LOSS_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_LINE_LOSS(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 109020 --农村用电情况年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_COUNTRY_USE_ELEC_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_COUNTRY_USE_ELEC_TABLE where YEAR_MONTH = @u_send_month delete from TJ_COUNTRY_USE_ELEC where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_COUNTRY_USE_ELEC_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_COUNTRY_USE_ELEC(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 109030 --农村经济效益年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_COUNTRY_BENEFIT_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_COUNTRY_BENEFIT_TABLE where YEAR_MONTH = @u_send_month delete from TJ_COUNTRY_BENEFIT where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_COUNTRY_BENEFIT_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_COUNTRY_BENEFIT(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 109040 --农电技改情况年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_COUNTRY_TECH_CHANGE_TABLE where YEAR_MONTH = @u_send_month if @u_rowcount > 0 begin select @u_lib_info = 'a' return end end else if @u_run_status = 'b' begin delete from TJ_COUNTRY_TECH_CHANGE_TABLE where YEAR_MONTH = @u_send_month delete from TJ_COUNTRY_TECH_CHANGE where YEAR_MONTH = @u_send_month end --创建主表内容 insert into TJ_COUNTRY_TECH_CHANGE_TABLE(YEAR_MONTH,UNIT_CODE,FILLER,FILL_DATE) values(@u_send_month,@u_report_unit,@u_filler,getdate()) select @u_rowcount = 1 while @u_rowcount < 18 begin insert into TJ_COUNTRY_TECH_CHANGE(YEAR_MONTH,UNIT_CODE,ROW_SEQU) values(@u_send_month,1,@u_rowcount) select @u_rowcount = @u_rowcount + 1 end end else if @u_table_code = 109050 --农电设备年报 begin if @u_run_status = 'a' begin select @u_rowcount = count(1) from TJ_CO