DROP PROCEDURE IF EXISTS `prc_company_report`;

CREATE PROCEDURE `prc_company_report`()
begin
	TRUNCATE report_company;
	INSERT INTO `report_company` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `institutionType`, `totalNum`, `initNum`)
	select
		true,
		now(),
		now(),
		IFNULL(sc.orgId,''),
		min(p2.path),
		sc.areaCode,
		if(institutionType='0105','0102',institutionType) as institutionType,-- 服务公司与服务分公司统计到一起
		count(1),
		count(IF(u.init =true,1,null))
	FROM security_company sc
	INNER JOIN `user` u on sc.user_id=u.id
	LEFT JOIN police p2 on sc.orgId=p2.id and p2.active=true
	where  sc.active=true
	and u.active=true
	and (sc.`local` = true or sc.`local` is null)
	GROUP BY sc.institutionType,IFNULL(sc.orgId,''),sc.areaCode;

	update report_company set unInitNum = totalNum-initNum;

	INSERT INTO `report_company` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `institutionType`, `totalNum`, `initNum`, `unInitNum`)
	select
		true,
		now(),
		now(),
		`orgId`,
		min(`path`)as path,
		areaCode,
		'0000' as institutionType,
		sum(`totalNum`)as totalNum,
		sum(`initNum`) as initNum,
		sum(`unInitNum`) as unInitNum
	from report_company GROUP BY orgId,areaCode;


-- select * from report_company;
-- CALL prc_company_report();
END;

DROP PROCEDURE IF EXISTS `prc_person_report`;

CREATE PROCEDURE `prc_person_report`()
begin

	-- select * from report_person;
	TRUNCATE report_person;
	INSERT INTO `report_person` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `institutionType`, `totalNum`, `quitNum`, `authNum`, `certificateNum`)
	select
		true,
		now(),
		now(),
		IFNULL(s1.orgId,''),
		min(p2.path),
		s1.areaCode,
		if(institutionType='0105','0102',institutionType) as institutionType,-- 服务公司与服务分公司统计到一起
		count(if(scp.active=true,1,null)),
		count(if(scp.active=false,1,null)),
		count(if(scp.active=true and p1.identityAuthState =1,1,null)),
		count(if(scp.active=true and ifnull(p1.zgcredential,'')!='',1,null))
	from security_person p1
		INNER JOIN `user` u ON p1.user_id=u.id
		INNER JOIN security_company_person scp ON p1.id=scp.person_id
		INNER JOIN security_company s1 ON s1.id=scp.company_id
		LEFT JOIN police p2 ON p2.id=s1.orgId
	where u.active=TRUE
	AND p1.personType in ('2','3') -- 不统计内勤人员 统计普通保安员跟武装押运人员
	and institutionType !='0103'--  不统计培训单位
	group by s1.institutionType,IFNULL(s1.orgId,''),s1.areaCode;

	update report_person set unAuthNum=totalNum-authNum,unCerNum=totalNum-certificateNum;

	INSERT INTO `report_person` (`active`, `createTime`, `updateTime`, `orgId`, `path`, `areaCode`, `institutionType`, `totalNum`, `quitNum`, `authNum`, `unAuthNum`, `certificateNum`, `unCerNum`)
	select
		true,
		now(),
		now(),
		`orgId`,
		min(`path`) as path,
		areaCode,
		'0000' as institutionType,
		sum(`totalNum`)as totalNum,
		sum(`quitNum`)as quitNum,
		sum(`authNum`)as authNum,
		sum(`unAuthNum`)as unAuthNum,
		sum(`certificateNum`)as certificateNum,
		sum(`unCerNum`)as unCerNum
	from report_person
	GROUP BY orgId,areaCode;

-- call prc_person_report();

END;


DROP PROCEDURE IF EXISTS `prc_personcertificate_report`;

CREATE PROCEDURE `prc_personcertificate_report`()
begin

-- 	select * from report_personcertificate;
-- 	TRUNCATE report_personcertificate;
	set @today = NOW();
	set @thisWeek = YEARWEEK(@today ,2);
	set @thisMonth = date_format(@today,'%Y%m');
	set @thisYear = YEAR(@today);

	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,
			@thisWeek 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 YEARWEEK(p1.create_time ,2) = @thisWeek
		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',
		@thisMonth 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 date_format(p1.create_time,'%Y%m') = @thisMonth
	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,
		@thisYear 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 YEAR(p1.create_time) = @thisYear
	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;

	delete from report_personcertificate where dateType='1' and reportDate=@thisYear;
	delete from report_personcertificate where dateType='2' and reportDate=@thisMonth;
	delete from report_personcertificate where dateType='3' and reportDate=@thisWeek;


	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;
END;


DROP PROCEDURE IF EXISTS `prc_task_report`;

CREATE PROCEDURE `prc_task_report`()
begin
-- 	select * from report_task;
-- 	TRUNCATE report_task;
-- 	call prc_task_report();

	set @today = NOW();
	set @thisWeek = YEARWEEK(@today ,2);
	set @thisMonth = date_format(@today,'%Y%m');
	set @thisYear = YEAR(@today);

	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,
		@thisWeek 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 YEARWEEK(p1.createTime ,2) = @thisWeek
		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,
		@thisMonth 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 DATE_FORMAT(p1.createTime ,'%Y%m') = @thisMonth
		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,
		@thisYear 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 year(p1.createTime) = @thisYear
		and createTime <=@today
		and area is not null
		group by reportDate,area;

	update v_report_task set unRelatedNum= totalNum-relatedNum;


	delete from report_task where dateType='1' and reportDate=@thisYear;
	delete from report_task where dateType='2' and reportDate=@thisMonth;
	delete from report_task where dateType='3' and reportDate=@thisWeek;

	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;
END;

DROP PROCEDURE IF EXISTS `prc_problem_person_report`;

CREATE PROCEDURE `prc_problem_person_report`()
begin
-- 	TRUNCATE report_problem_person;
	set @today = NOW();
	set @thisWeek = YEARWEEK(@today ,2);
	set @thisMonth = date_format(@today,'%Y%m');
	set @thisYear = YEAR(@today);

	drop table if EXISTS v_report_problem_1;
	create table v_report_problem_1 as select * from (
		select
			2 as dateType,
			@thisMonth 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 date_format(create_time,'%Y%m') = @thisMonth
		and create_time <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;


	insert into v_report_problem_1
	select 3 as dateType,
		@thisWeek 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 YEARWEEK(create_time, 2) = @thisWeek
	and create_time <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;


	insert into v_report_problem_1
	select 1 as dateType,
		@thisYear 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 YEAR(create_time) = @thisYear
	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,
			@thisMonth 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 date_format(comHandleDate,'%Y%m') = @thisMonth
		and comHandleDate <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;


	insert into v_report_problem_2
	select
		3 as dateType,
		@thisWeek 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 YEARWEEK(comHandleDate, 2) = @thisWeek
	and comHandleDate <=@today
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;


	insert into v_report_problem_2
	select
		1 as dateType,
		@thisYear 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 YEAR(comHandleDate) = @thisYear
	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;

	delete from report_problem_person where dateType='1' and reportDate=@thisYear;
	delete from report_problem_person where dateType='2' and reportDate=@thisMonth;
	delete from report_problem_person where dateType='3' and reportDate=@thisWeek;

	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,
			@thisMonth 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 date_format(handleDate,'%Y%m') = 	@thisMonth
		and handleDate <=@today
		GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate
	) as sql_table;

	insert into v_report_problem_3
	select
		3 as dateType,
		@thisWeek 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
	  and YEARWEEK(handleDate, 2) = @thisWeek
	GROUP BY ifnull(areaCode,''),ifnull(orgId,''), reportDate;

	insert into v_report_problem_3
	select
		1 as dateType,
		@thisYear 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
	  and YEAR(handleDate) = @thisYear
	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;
END;