ALTER TABLE `sb_person_grade`
ADD COLUMN `newest`  bit NULL COMMENT '是否最新';



#等级证报名导出视图
drop view if EXISTS v_person_grade;
CREATE
VIEW `v_person_grade` AS
SELECT
	t1.id,
	t1.active,
	t1.create_time,
	t1.update_time,
	t1.update_by,
	t1.updateflag,
	t1.userid,
	t1.areaCode,
	t1.orgId,
	t1.personId,
	t1.name,
	t1.phone,
	t1.idNum,
	t1.applyTime,
	t1.trainId,
	t1.trainName,
	t1.testTime,
	t1.idFrontImg,
	t1.idBackImg,
	t1.headImg,
	t1.comId,
	t1.comName,
	t1.gradeLevel,
	t1.period,
	t1.periodStatus,
	t1.practiceStatus,
	t1.practiceDay,
	t1.practiceHour,
	t1.censorStatus,
	t1.address,
	t1.liveAddress,
	t1.residentType,
	t1.residentPlaceType,
	t1.contractId,
	t1.classId,
	t1.testStateComplete,
	t1.writtenScoreComplete,
	t1.operateScoreComplete,
	t1.sumScoreComplete,
	t1.zgCerNo,
	t1.zgCerUrl,
	t1.zgOrg,
	t1.zgTrainName,
	t1.zgIssueDate,
	t1.payTime,
	t1.payState,
	t1.checkState,
	t1.checkDate,
	t1.classState,
	t1.writtenScore,
	t1.operateScore,
	t1.sumScore,
	t1.testState,
	t1.approvalReason,
	t1.approvalState,
	t1.gradePersonType,
	t1.mainHomeUrl,
	t1.selfHomeUrl,
	t1.regCerUrl,
	t1.gradCerUrl,
	t1.empCerUrl,
	t1.laborTranCerUrl,
	t1.dischargeCerUrl,
	t1.selfEmpCerUrl,
	t1.demobCerUrl,
	t1.conversionCerUrl,
	t1.confirmCerUrl,
	t2.`name` as 'className',
	IF(t2.startTime is null,'',CONCAT(DATE_FORMAT(t2.startTime,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t2.endTime,'%Y-%m-%d'))) as 'classStartTime',
	IF(t2.practiceStartDate is null,'',CONCAT(DATE_FORMAT(t2.practiceStartDate,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t2.practiceEndDate,'%Y-%m-%d'))) as 'trainTime'
	FROM sb_person_grade t1
	LEFT JOIN sb_grade_class t2 ON t1.classId = t2.id and t2.active = true
	where
	  t1.active =true
	  and t1.newest = true;

ALTER TABLE `security_certificate`
MODIFY COLUMN `id`  bigint(20) NOT NULL FIRST ;


DROP PROCEDURE IF EXISTS `prc_batchImportCertificate_query`;

CREATE PROCEDURE `prc_batchImportCertificate_query`(IN   prm_batchId   INT(11),
  IN   prm_policeId  BIGINT(20),
  IN   prm_areaCode  VARCHAR(20))
BEGIN


    	      UPDATE tmp_security_certificate
               SET errorMsg = IF(errorMsg IS NULL  ,'身份证重复',CONCAT(errorMsg,'||身份证重复')),
                   isSuccess = '0'
             WHERE batchId = prm_batchId
               AND idnum IN (SELECT m.idnum
                               FROM (SELECT idnum
                                       FROM tmp_security_certificate
                                      WHERE batchId = prm_batchId
                                      GROUP BY idnum HAVING COUNT(1) >1 )m) ;



    	   -- 2、校验导入的保安人员是否存在证书编号重复

    	    UPDATE tmp_security_certificate
               SET errorMsg = IF(errorMsg IS NULL  ,'证书编号重复',CONCAT(errorMsg,'||证书编号重复')),
                   isSuccess = '0'
             WHERE batchId = prm_batchId
               AND IFNULL(zsbh,'') != ''
               AND zsbh IN (SELECT m.zsbh
                               FROM (SELECT zsbh
                                       FROM tmp_security_certificate
                                      WHERE batchId = prm_batchId
                                        AND IFNULL(zsbh,'') != ''
                                      GROUP BY zsbh HAVING COUNT(1) >1 )m) ;


    	   -- 3、校验是否已经存在保安员的制证信息

    	     UPDATE tmp_security_certificate
              SET errorMsg = IF(errorMsg IS NULL  ,'已存在身份证相同的保安员制证信息',CONCAT(errorMsg,'||已存在身份证相同的保安员制证信息')),
                  isSuccess = '0'
            WHERE batchId = prm_batchId
              AND (EXISTS (SELECT 1
                             FROM security_certificate
                            WHERE active = 1
                              AND idnum = tmp_security_certificate.idnum )
                   OR
                   EXISTS (SELECT 1
                             FROM sb_personcertificate
                            WHERE active = 1
                              AND IFNULL(approvalState,'001') NOT IN ('001','004')
                              AND Cardnumber = tmp_security_certificate.idnum ) ) ;


        -- 4、校验保安员证编号是否存在

        UPDATE tmp_security_certificate
              SET errorMsg = IF(errorMsg IS NULL  ,'已存在相同的保安员证编号',CONCAT(errorMsg,'||已存在相同的保安员证编号')),
                  isSuccess = '0'
            WHERE batchId = prm_batchId
              AND IFNULL(zsbh,'')!= '' -- 排除证书编号为空
              AND (EXISTS (SELECT 1
                             FROM security_certificate
                            WHERE active = 1
                              AND zsbh = tmp_security_certificate.zsbh ));



		 SELECT 1 AS active, -- 是否有效
						create_time,
						create_time AS update_time,
						'1' AS isOldData, -- 是否历史导入数据
						IFNULL((SELECT a.realName
											FROM `user` a , security_person b
										 WHERE a.id = b.user_id
											 AND a.active = 1
											 AND b.id_number = a.idnum
											 LIMIT 1 ),xm) AS xm, -- 姓名
						IFNULL((SELECT c.name
											FROM security_person a , security_company_person b , security_company c
										 WHERE a.id = b.person_id
											 AND b.company_id = c.id
											 AND b.active = 1
											 AND c.active = 1
											 AND a.id_number = a.idnum
											 LIMIT 1 ),companyName) AS companyName, -- 公司名称
						NULL AS address, -- 地址
						CONCAT(SUBSTR(idnum,7,4),
									 '-',
									 SUBSTR(idnum,11,2),
									 '-',
									 SUBSTR(idnum,13,2)) AS csrq, -- 出生日期
						idnum, -- 公民身份证号
						IFNULL(zsbh,CONCAT(IF(LEFT(prm_areaCode,2) = '61','陕','桂'),
															 SUBSTR(prm_areaCode,3,2),YEAR(now()),RIGHT(CONCAT('00000',id),5))) AS zsbh , -- 证书编号(先拼接5位，然后再截取最后的5位)
						IF(IFNULL(fzrq,'') = '',
							 DATE(NOW()),
							 fzrq) AS fzrq, -- 发证日期
						NULL AS zzrq, -- 制作日期
						'2' AS zzzt, -- 制证状态 (已制证)
						NULL AS securitypersonid, -- 保安员资格证申报ID
						(SELECT b.id
								FROM security_person b , user c
							 WHERE b.user_id = c.id
								 AND c.active =1
								 AND b.id_number = a.idnum
								LIMIT 1 ) AS personId, -- 人员ID
						 (SELECT b.id
								FROM security_company b
							 WHERE b.active = 1
								 AND b.name = a.companyName
								 LIMIT 1 ) AS companyId, -- 公司ID
							a.companyName, -- 公司名称
						 CONCAT(LEFT(prm_areaCode, 2),'0000') AS province, -- 所在地省代码
						 CONCAT(LEFT(prm_areaCode, 4),'00') AS city, -- 所在地市代码
						 prm_areaCode AS district, -- 地区
						 (SELECT regionFullName
								FROM sysArea
							 WHERE code = prm_areaCode ) AS szss, -- 所在地区
						(SELECT orgName
							 FROM police
							WHERE areacode = CONCAT(SUBSTR(prm_areaCode,1,4),'00')
								AND active = 1
								and orgtype = '2') AS fzjgmc, -- 发证机关名称  (取市)
						CONCAT(SUBSTR(prm_areaCode,1,4),'00000000') AS fzjgbh, -- 发证机关编号
						CAST(SUBSTR(dyzt,1,1) AS  SIGNED ) AS isprint, -- 是否打印
						0 AS havephoto, -- 是否有照片
						IF(SUBSTR(dyzt,1,1) = '0',0,1) AS printcount, -- 打印次数
						NULL AS printTime, -- 打印时间
						1 AS updateflag -- 更新标识
			 FROM tmp_security_certificate a
			WHERE batchId  = prm_batchId
				AND isSuccess = '1';

END;