############保安服务公司

SELECT
	'保安服务许可证',
	c.public_security_license_number,
	c.`name`,
	'70',
	c.social_code,
	'北京市公安局',
	'1111000000002888XF',
	'110100',
	date_format(
		c.give_license_date,
		'%Y/%m/%d'
	),
	'',
	c.public_security_license_number,
	c.address,
c.legal_person,
	(
		SELECT
			GROUP_CONCAT(label)
		FROM
			sys_dict
		WHERE
			codeType = 'securityScopeType'
		AND codeValue IN (
			SELECT
				substring_index(
					substring_index(
						t.securityScopeType,
						',',
						b.help_topic_id + 1
					),
					',' ,- 1
				) shopid
			FROM
				security_company t
			JOIN help_topic b ON b.help_topic_id < (
				length(t.securityScopeType) - length(
					REPLACE (t.securityScopeType, ',', '')
				) + 1
			)
			WHERE
				id = c.id
		)
	) AS securityScopeType,
	c.registered_money,
	c.pzwh
FROM
	security_company c
LEFT JOIN company_certificate cc ON cc.gsmc = c. NAME
WHERE
	institutionType = '0102';



############### 保安培训机构 
SELECT
	'保安培训许可证',
	c.public_security_license_number,
	c.`name`,
	'70',
	c.social_code,
	'北京市公安局',
	'1111000000002888XF',
	'110100',
	date_format(
		c.give_license_date,
		'%Y/%m/%d'
	),
	'',
	c.public_security_license_number,
c.legal_person,
	pxnr,

	cc.pzwh,
	c.address

FROM
	security_company c
LEFT JOIN train_certificate cc ON cc.gsmc = c. NAME
WHERE
	institutionType = '0103';

 
########资格证

 Insert into bayzj
SELECT
	'保安员资格证',
	c.zsbh,
	c.xm,
	'10',
	c.idnum,
	'北京市公安局',
	'1111000000002888XF',
	'110100',
	date_format(c.fzrq, '%Y/%m/%d'),
	'',
	sp.nation,
	c.csrq,
	c.address,
	pb.imgblob

FROM
	security_certificate c
LEFT JOIN security_person sp on sp.id_number = c.idnum
LEFT JOIN person_base_info pb on pb.idnum =  c.idnum 
WHERE
	c.active = TRUE and c.state ='1' 
LIMIT 10