-- 状态记录表 select * from CollectDataStatus c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
-- 基本信息 select * from pcs2309_tph_jbxx c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
-- 收入情况 select * from pcs2309_tph_srqk c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
-- 家庭成员 select * from pcs2309_tph_jtcy c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
-- 帮扶责任人 select * from pcs2309_tph_bfzrr c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
--生产生活条件 select * from pcs2309_tph_scshtj c where c.hid='77c4321e-0f92-45eb-a934-4d2d464431c5'
--- 户主信息 select * from pcs2309_householdcode c where c.id='77c4321e-0f92-45eb-a934-4d2d464431c5'
update pcs2309_tph_srqk
set e9 =0 where e9 is null;
update pcs2309_tph_srqk
set e8 =0 where e8 is null;
update pcs2309_tph_srqk
set e4 =0 where e4 is null;
update pcs2309_tph_srqk
set e30 =0 where e30 is null;
update pcs2309_tph_srqk
set e2 =0 where e2 is null;
update pcs2309_tph_srqk
set e1 =0 where e1 is null;
update pcs2309_tph_srqk
set e =0 where e is null;
update pcs2309_tph_srqk
set d9 =0 where d9 is null;
update pcs2309_tph_srqk
set d8 =0 where d8 is null;
update pcs2309_tph_srqk
set d6p=0 where d6p is null;
update pcs2309_tph_srqk
set d6o=0 where d6o is null;
update pcs2309_tph_srqk
set d6n=0 where d6n is null;
update pcs2309_tph_srqk
set d6m=0 where d6m is null;
update pcs2309_tph_srqk
set d6l=0 where d6l is null;
update pcs2309_tph_srqk
set d6k=0 where d6k is null;
update pcs2309_tph_srqk
set d6j=0 where d6j is null;
update pcs2309_tph_srqk
set d6i=0 where d6i is null;
update pcs2309_tph_srqk
set d6h=0 where d6h is null;
update pcs2309_tph_srqk
set d6g=0 where d6g is null;
update pcs2309_tph_srqk
set d6f=0 where d6f is null;
update pcs2309_tph_srqk
set d6e=0 where d6e is null;
update pcs2309_tph_srqk
set d6d=0 where d6d is null;
update pcs2309_tph_srqk
set d6c=0 where d6c is null;
update pcs2309_tph_srqk
set d6b=0 where d6b is null;
update pcs2309_tph_srqk
set d6a=0 where d6a is null;
update pcs2309_tph_srqk
set d6=0 where d6 is null;
update pcs2309_tph_srqk
set d5e=0 where d5e is null;
update pcs2309_tph_srqk
set d5d=0 where d5d is null;
update pcs2309_tph_srqk
set d5c=0 where d5c is null;
update pcs2309_tph_srqk
set d5b=0 where d5b is null;
update pcs2309_tph_srqk
set d5a=0 where d5a is null;
update pcs2309_tph_srqk
set d5=0 where d5 is null;
update pcs2309_tph_srqk
set d4=0 where d4 is null;
update pcs2309_tph_srqk
set d3=0 where d3 is null;
update pcs2309_tph_srqk
set d2=0 where d2 is null;
update pcs2309_tph_srqk
set d1=0 where d1 is null;
update pcs2309_tph_srqk
set d=0 where d is null;
update pcs2309_tph_srqk
set lpsr=0 where lpsr is null;
update pcs2309_tph_srqk
set hgzfzc=0 where hgzfzc is null;
update pcs2309_tph_srqk
set a=0 where a is null;
update pcs2309_tph_srqk
set a1=0 where a1 is null;
update pcs2309_tph_srqk
set a2=0 where a2 is null;
update pcs2309_tph_srqk
set a3=0 where a3 is null;
update pcs2309_tph_srqk
set a4=0 where a4 is null;
update pcs2309_tph_srqk
set a5=0 where a5 is null;
update pcs2309_tph_srqk
set a8=0 where a8 is null;
update pcs2309_tph_srqk
set a9=0 where a9 is null;
update pcs2309_tph_srqk
set a10=0 where a10 is null;
update pcs2309_tph_srqk
set b=0 where b is null;
update pcs2309_tph_srqk
set b1=0 where b1 is null;
update pcs2309_tph_srqk
set b2=0 where b2 is null;
update pcs2309_tph_srqk
set b3=0 where b3 is null;
update pcs2309_tph_srqk
set b4=0 where b4 is null;
update pcs2309_tph_srqk
set c=0 where c is null;
update pcs2309_tph_srqk
set c1=0 where c1 is null;
update pcs2309_tph_srqk
set c2=0 where c2 is null;
update pcs2309_tph_srqk
set c3=0 where c3 is null;
update pcs2309_tph_srqk
set c4=0 where c4 is null;
update pcs2309_tph_srqk
set c5=0 where c5 is null;
select * from pcs2309_tph_srqk
CREATE DEFINER=`root`@`%` PROCEDURE `ffpfun_calculateincome_2309`(in p_hid varchar(200) CHARSET utf8)
BEGIN
#定义一个变量用于计录户总人数
DECLARE d_zrs DECIMAL(10,2) DEFAULT 0;
#定义一个变量用于计录户类型
DECLARE d_hlx VARCHAR(255);
#定义一个错误的变量,类型是整形,默认是0
DECLARE t_error INTEGER DEFAULT 0;
#捕获到sql的错误,就设置t_error为1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
#开启事务
START TRANSACTION;
# 取此户的总人数...
select IFNULL(ndrks,1),hlx into d_zrs,d_hlx from pcs2309_householdcode where id=p_hid ;
# 为保证算人均时不出错,如果户总人口数小于1,则改为1
IF d_zrs<1 THEN
SET d_zrs=1;
END IF;
# 计算收入情况
# 1.计算D5,D6
update pcs2309_tph_srqk t set t.D5 = IFNULL(t.D5a,0)+IFNULL(t.D5b,0)+IFNULL(t.D5c,0)+IFNULL(t.D5d,0)+IFNULL(t.D5e,0),
t.D6 = IFNULL(t.D6a,0)+IFNULL(t.D6b,0)+IFNULL(t.D6c,0)+IFNULL(t.D6d,0)+IFNULL(t.D6e,0)
+IFNULL(t.D6f,0)+IFNULL(t.D6g,0)+IFNULL(t.D6h,0)+IFNULL(t.D6i,0)+IFNULL(t.D6j,0)
+IFNULL(t.D6k,0)+IFNULL(t.D6l,0)+IFNULL(t.D6m,0)+IFNULL(t.D6n,0)+IFNULL(t.D6o,0)+IFNULL(t.D6p,0)
where t.hid=p_hid and t.nf='202309';
# 2.计算各项收入和支出大项
update pcs2309_tph_srqk t set t.A=IFNULL(t.A1,0)+IFNULL(t.A2,0)+IFNULL(t.A3,0)+IFNULL(t.A4,0)+IFNULL(t.A5,0),
t.B=IFNULL(t.B1,0)+IFNULL(t.B2,0)+IFNULL(t.B3,0)+IFNULL(t.B4,0),
t.C=IFNULL(t.C1,0)+IFNULL(t.C2,0)+IFNULL(t.C3,0)+IFNULL(t.C4,0)+IFNULL(t.C5,0),
t.D=IFNULL(t.D1,0)+IFNULL(t.D2,0)+IFNULL(t.D3,0)+IFNULL(t.D4,0)+IFNULL(t.D5,0)+IFNULL(t.D6,0),
t.E=IFNULL(t.E1,0)+IFNULL(t.E2,0)+IFNULL(t.E3,0)+IFNULL(t.E4,0)
where t.hid=p_hid and t.nf='202309';
# 3.计算总计
update pcs2309_tph_srqk t set t.nzsr = IFNULL(t.A,0)+IFNULL(t.B,0)+IFNULL(t.C,0)+IFNULL(t.D,0),
t.ncsr = IFNULL(t.A,0)+IFNULL(t.B,0)+IFNULL(t.C,0)+IFNULL(t.D,0) - IFNULL(t.E,0),
t.rjcsr = (IFNULL(t.A,0)+IFNULL(t.B,0)+IFNULL(t.C,0)+IFNULL(t.D,0) - IFNULL(t.E,0)) /d_zrs
where t.hid=p_hid and t.nf='202309';
# 4.如果是监测户,计算纳入监测收入范围
IF d_hlx='100B02002' THEN
update pcs2309_tph_srqk t set t.zsrckfw = IFNULL(t.ncsr,0) + IFNULL(t.lpsr,0) - IFNULL(t.hgzfzc,0),
t.rjsrckfw = (IFNULL(t.ncsr,0) + IFNULL(t.lpsr,0) - IFNULL(t.hgzfzc,0)) /d_zrs
where t.hid=p_hid and t.nf='202309';
END IF;
#如果捕获到错误
IF t_error=1 THEN
#回滚
ROLLBACK;
ELSE
#提交
COMMIT;
END IF;
END