call prc_company_report();
call prc_person_report();



  TRUNCATE report_personcertificate;
	set @today = NOW();

	drop table if EXISTS v_report_personcertificate;
	create table v_report_personcertificate as select * from (
		select
			p1.orgId,
			min(p2.path) as path,
			min(p1.district) as areaCode,
			'3' as dateType,
			YEARWEEK(p1.create_time ,2) as reportDate,
			count(1) as totalNum,
			count(if(censor_status='003',1,null)) as passNum,
			count(if(censor_status='004',1,null)) as failNum,
			count(if(test_state='1' or test_state='0',1,null)) as testNum,
			count(if(test_state='1',1,null)) as testPassNum,
			count(if(test_state='0',1,null)) as testFailNum,
			0 as passRate
		from sb_personcertificate p1,police p2,sys_approval p3
		where p1.orgid = p2.id
		and p1.approvalstate !='001'
		and p1.id = p3.businessid
		and p3.businesstype='004'
		and p3.active=true
		and p1.active =true
		and p2.active=true
		and p1.create_time <=@today
		and not EXISTS(
			select 1 from security_certificate s1 where active=true and s1.securitypersonid=p1.id and isOldData=1
		)
		group by reportDate,p1.orgId
	) as sql_table;

	insert into v_report_personcertificate
	select
		p1.orgId,
		min(p2.path),
		min(p1.district),
		'2',
		date_format(p1.create_time,'%Y%m') as reportDate,
		count(1),
		count(if(censor_status='003',1,null)),
		count(if(censor_status='004',1,null)),
		count(if(test_state='1' or test_state='0',1,null)),
		count(if(test_state='1',1,null)),
		count(if(test_state='0',1,null)),
		0
	from sb_personcertificate p1,police p2,sys_approval p3
	where p1.orgid = p2.id
	and p1.approvalstate !='001'
	and p1.id = p3.businessid
	and p3.businesstype='004'
	and p3.active=true
	and p1.active =true
	and p2.active=true
	and p1.create_time <=@today
	and not EXISTS(
	select 1 from security_certificate s1 where active=true and s1.securitypersonid=p1.id and isOldData=1
	)
	group by reportDate,p1.orgId;


	insert into v_report_personcertificate
	select
		p1.orgId,
		min(p2.path) as path,
		min(p1.district) as areaCode,
		'1' as dateType,
		YEAR(p1.create_time) as reportDate,
		count(1) as totalNum,
		count(if(censor_status='003',1,null)) as passNum,
		count(if(censor_status='004',1,null)) as failNum,
		count(if(test_state='1' or test_state='0',1,null)) as testNum,
		count(if(test_state='1',1,null)) as testPassNum,
		count(if(test_state='0',1,null)) as testFailNum,
		0 as passRate
	from sb_personcertificate p1,police p2,sys_approval p3
	where p1.orgid = p2.id
	and p1.approvalstate !='001'
	and p1.id = p3.businessid
	and p3.businesstype='004'
	and p3.active=true
	and p1.active =true
	and p2.active=true
	and p1.create_time <=@today
	and not EXISTS(
		select 1 from security_certificate s1 where active=true and s1.securitypersonid=p1.id and isOldData=1
	)
	group by reportDate,p1.orgId;

	INSERT INTO `report_personcertificate` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `dateType`, `reportDate`, `totalNum`, `passNum`, `failNum`, `testNum`, `testPassNum`, `testFailNum`, `passRate`)
	select
		true,
		now(),
		now(),
		`orgId`,
		`path`,
		`areaCode`,
		`dateType`,
		`reportDate`,
		`totalNum`,
		`passNum`,
		`failNum`,
		`testNum`,
		`testPassNum`,
		`testFailNum`,
		`passRate`
	from v_report_personcertificate;

	DROP TABLE IF EXISTS v_report_personcertificate;

 	TRUNCATE report_task;

	DROP TABLE IF EXISTS v_report_task;
	create table v_report_task as select * from (
		select
		null as orgId,
		null as path,
		p1.area as areaCode,
		'3' as dateType,
		YEARWEEK(p1.createTime ,2) as reportDate,
		count(1) as totalNum,
		count(if(EXISTS(
		SELECT 1 from com_task_contract ctc where ctc.comTaskId= p1.comTaskId
		),1,null)) as relatedNum,
		0 as unRelatedNum,
		sum(p1.perNum) as personNum,
		0 as signNum,
		0 as renewaNum,
		count(if(taskStatus='2',1,null)) as completeNum,
		count(if(taskStatus='7',1,null)) as stopNum,
		count(if(taskStatus='1',1,null)) as underWayNum
		from com_task p1
		where isDelete=0
		and area is not null
		and createTime <=@today
		group by reportDate,area
	) as sql_table;

	insert into v_report_task
	select
		null as orgId,
		null as path,
		p1.area as areaCode,
		'2' as dateType,
		DATE_FORMAT(p1.createTime ,'%Y%m') as reportDate,
		count(1) as totalNum,
		count(if(EXISTS(
		SELECT 1 from com_task_contract ctc where ctc.comTaskId= p1.comTaskId
		),1,null)) as relatedNum,
		0 as unRelatedNum,
		sum(p1.perNum) as personNum,
		0 as signNum,
		0 as renewaNum,
		count(if(taskStatus='2',1,null)) as completeNum,
		count(if(taskStatus='7',1,null)) as stopNum,
		count(if(taskStatus='1',1,null)) as underWayNum
		from com_task p1
		where isDelete=0
		and createTime <=@today
		and area is not null
		group by reportDate,area;

	insert into v_report_task
	select
		null as orgId,
		null as path,
		p1.area as areaCode,
		'1' as dateType,
		year(p1.createTime) as reportDate,
		count(1) as totalNum,
		count(if(EXISTS(
		SELECT 1 from com_task_contract ctc where ctc.comTaskId= p1.comTaskId
		),1,null)) as relatedNum,
		0 as unRelatedNum,
		sum(p1.perNum) as personNum,
		0 as signNum,
		0 as renewaNum,
		count(if(taskStatus='2',1,null)) as completeNum,
		count(if(taskStatus='7',1,null)) as stopNum,
		count(if(taskStatus='1',1,null)) as underWayNum
		from com_task p1
		where isDelete=0
		and createTime <=@today
		and area is not null
		group by reportDate,area;

	update v_report_task set unRelatedNum= totalNum-relatedNum;

	INSERT INTO `report_task` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `dateType`, `reportDate`, `totalNum`, `relatedNum`, `unRelatedNum`, `personNum`, `signNum`, `renewaNum`, `completeNum`, `stopNum`, `underWayNum`)
	select
		true,
		now(),
		now(),
		orgId,
		path,
		areaCode,
		dateType,
		reportDate,
		totalNum,
		relatedNum,
		unRelatedNum,
		personNum,
		signNum,
		renewaNum,
		completeNum,
		stopNum,
		underWayNum
	from v_report_task;

	drop table if EXISTS v_report_task;

 	TRUNCATE report_problem_person;

	drop table if EXISTS v_report_problem_1;
	create table v_report_problem_1 as select * from (
		select
			2 as dateType,
			date_format(create_time,'%Y%m') as reportDate,
			ifnull(areaCode,'') as areaCode,
			ifnull(orgId,'') as orgId,
			count(IF(personType='3',1,null))as focalNum,
			count(IF(personType='2',1,null))as escapeNum,
			count(IF(personType='1',1,null))as errorNum,
			count(IF(personType = 2 AND ( catchState = 0 OR catchState IS NULL),1,null))as unCaptureNum,
			count(IF(comHandleDate is null,1,null))as unHandleNum
		from problemperson
		where active =true
		and create_time <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;


	insert into v_report_problem_1
	select 3 as dateType,
		YEARWEEK(create_time, 2) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
		count(IF(personType='3',1,null))as focalNum,
		count(IF(personType='2',1,null))as escapeNum,
		count(IF(personType='1',1,null))as errorNum,
		count(IF(personType = 2 AND ( catchState = 0 OR catchState IS NULL),1,null))as unCaptureNum,
		count(IF(comHandleDate is null,1,null))as unHandleNum
	from problemperson
	where active =true
	and create_time <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

	insert into v_report_problem_1
	select 1 as dateType,
		YEAR(create_time) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
		count(IF(personType='3',1,null))as focalNum,
		count(IF(personType='2',1,null))as escapeNum,
		count(IF(personType='1',1,null))as errorNum,
		count(IF(personType = 2 AND ( catchState = 0 OR catchState IS NULL),1,null))as unCaptureNum,
		count(IF(comHandleDate is null,1,null))as unHandleNum
	from problemperson
	where active =true
	and create_time <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

	drop table if EXISTS v_report_problem_2;
	create table v_report_problem_2 as select * from (
		select
			2 as dateType,
			date_format(comHandleDate,'%Y%m') as reportDate,
			ifnull(areaCode,'') as areaCode,
			ifnull(orgId,'') as orgId,
			count(1) as handleNum,
			count(IF(inform='2',1,null))as dismissNum,
			count(IF(inform='3',1,null))as retainNum
		from problemperson
		where active =true
		and comHandleDate is not null
		and comHandleDate <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;


	insert into v_report_problem_2
	select
		3 as dateType,
		YEARWEEK(comHandleDate, 2) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
		count(1) as handleNum,
		count(IF(inform='2',1,null))as dismissNum,
		count(IF(inform='3',1,null))as retainNum
	from problemperson
	where active =true
	and comHandleDate is not null
	and comHandleDate <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;


	insert into v_report_problem_2
	select
		1 as dateType,
		YEAR(comHandleDate) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
		count(1) as handleNum,
		count(IF(inform='2',1,null))as dismissNum,
		count(IF(inform='3',1,null))as retainNum
	from problemperson
	where active =true
	and comHandleDate is not null
	and comHandleDate <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

	drop table if EXISTS v_report_problem_person;
	create table v_report_problem_person as select * from (
		select
			tt.orgId,
			'' as path,
			tt.areaCode,
			tt.dateType,
			tt.reportDate,
			sum(tt.focalNum) as focalNum,
			sum(tt.escapeNum) as escapeNum,
			sum(tt.errorNum) as errorNum,
			sum(tt.unCaptureNum) as unCaptureNum,
			sum(tt.handleNum) as handleNum,
			sum(tt.unHandleNum) as unHandleNum,
			sum(tt.dismissNum) as dismissNum,
			sum(tt.retainNum) as retainNum
		from (
			select
				ifnull(v1.orgId,v2.orgId) as orgId,
				ifnull(v1.areaCode,v2.areaCode) as areaCode,
				ifnull(v1.dateType,v2.dateType) as dateType,
				ifnull(v1.reportDate,v2.reportDate) as reportDate,
				ifnull(focalNum,0) as focalNum,
				ifnull(escapeNum,0) as escapeNum,
				ifnull(errorNum,0) as errorNum,
				ifnull(unCaptureNum,0) as unCaptureNum,
				ifnull(handleNum,0) as handleNum,
				ifnull(unHandleNum,0) as unHandleNum,
				ifnull(dismissNum,0) as dismissNum,
				ifnull(retainNum,0) as retainNum
			from v_report_problem_1 v1
			LEFT JOIN v_report_problem_2 v2 on v1.dateType =v2.dateType
			and v1.orgId=v2.orgId and v1.areaCode =v2.areaCode and v1.reportDate=v2.reportDate
			union
			select
				ifnull(v1.orgId,v2.orgId),
				ifnull(v1.areaCode,v2.areaCode),
				ifnull(v1.dateType,v2.dateType),
				ifnull(v1.reportDate,v2.reportDate),
				ifnull(focalNum,0),
				ifnull(escapeNum,0),
				ifnull(errorNum,0),
				ifnull(unCaptureNum,0),
				ifnull(handleNum,0),
				ifnull(unHandleNum,0),
				ifnull(dismissNum,0),
				ifnull(retainNum,0)
			from v_report_problem_2 v1
			LEFT JOIN v_report_problem_1 v2 on v1.dateType =v2.dateType
			and v1.orgId=v2.orgId and v1.areaCode =v2.areaCode and v1.reportDate=v2.reportDate
		)tt group by tt.dateType,tt.orgId,tt.areaCode,tt.reportDate
	) as sql_table;

	ALTER TABLE `v_report_problem_person`
	MODIFY COLUMN `path`  varchar(500) NULL DEFAULT NULL;

	update v_report_problem_person p1,police p2
	set p1.path=p2.path
	where p1.orgId=p2.id;

	INSERT INTO `report_problem_person` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `dateType`, `reportDate`, `focalNum`, `escapeNum`, `errorNum`, `unCaptureNum`, `handleNum`, `unHandleNum`, `dismissNum`, `retainNum`)
	select
		true,
		now(),
		now(),
		`orgId`,
		`path`,
		`areaCode`,
		`dateType`,
		`reportDate`,
		`focalNum`,
		`escapeNum`,
		`errorNum`,
		`unCaptureNum`,
		`handleNum`,
		`unHandleNum`,
		`dismissNum`,
		`retainNum`
	from v_report_problem_person;

	drop table if EXISTS v_report_problem_3;
	create table v_report_problem_3 as select * from (
		select
			2 as dateType,
			date_format(handleDate,'%Y%m') as reportDate,
			ifnull(areaCode,'') as areaCode,
			ifnull(orgId,'') as orgId,
			null as path,
			count(1) as captureNum
		from problemperson
		where active =true
		and handleDate is not null
		and personType = 2 AND catchState =1
		and handleDate <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;

	insert into v_report_problem_3
	select
		3 as dateType,
		YEARWEEK(handleDate, 2) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
    '' as path,
		count(1) as captureNum
	from problemperson
	where active =true
		and handleDate is not null
		and handleDate <=@today
		and personType = 2 AND catchState =1
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

	insert into v_report_problem_3
	select
		1 as dateType,
		YEAR(handleDate) as reportDate,
    ifnull(areaCode,'') as areaCode,
    ifnull(orgId,'') as orgId,
    '' as path,
		count(1) as captureNum
	from problemperson
	where active =true
		and handleDate is not null
		and handleDate <=@today
		and personType = 2 AND catchState =1
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

  update v_report_problem_3 t1,report_problem_person t2
	set t2.captureNum = t1.captureNum,
			t2.updateTime = now()
	where t1.dateType=t2.dateType
	and t1.reportDate= t2.reportDate
	and t1.orgId=t2.orgId
	and t1.areaCode = t2.areaCode;

	ALTER TABLE `v_report_problem_3`
	MODIFY COLUMN `path`  varchar(500) NULL DEFAULT NULL;

	update v_report_problem_3 p1,police p2
	set p1.path=p2.path
	where p1.orgId=p2.id;

	INSERT INTO `report_problem_person` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `dateType`, `reportDate`, `focalNum`, `escapeNum`, `errorNum`, `captureNum`, `unCaptureNum`, `handleNum`, `unHandleNum`, `dismissNum`, `retainNum`)
	select
		true,
		now(),
		now(),
		`orgId`,
		path,
		`areaCode`,
		`dateType`,
		`reportDate`,
		0,
		0,
		0,
		`captureNum`,
		0,
		0,
		0,
		0,
		0
	from v_report_problem_3 t1 where not EXISTS(
		select 1 from report_problem_person t2
		where t1.dateType =t2.dateType
		and t1.reportDate = t2.reportDate
		and t1.orgId=t2.orgId
		and t1.areaCode = t2.areaCode
	);

	drop table if EXISTS v_report_problem_1;
	drop table if EXISTS v_report_problem_2;
	drop table if EXISTS v_report_problem_3;
	drop table if EXISTS v_report_problem_person;