# 20201104 自招单位撤销

update security_company t1,`user` t2
set t1.init=t2.init,t1.companyState= t2.init
where t1.user_id= t2.id;


drop view if EXISTS v_security_company;
CREATE
VIEW `v_security_company` AS
SELECT
	`sc`.`id` AS `id`,
	`sc`.`native_code` AS `native_code`,
	`sc`.`user_id` AS `user_id`,
	`sc`.`update_time` AS `update_time`,
	`sc`.`update_by` AS `update_by`,
	`sc`.`active` AS `active`,
	`sc`.`name` AS `name`,
	`sc`.`legal_person` AS `legal_person`,
	`sc`.`social_code` AS `social_code`,
	`sc`.`central_office_name` AS `central_office_name`,
	`sc`.`central_office_legal_person` AS `central_office_legal_person`,
	`sc`.`central_office_tel` AS `central_office_tel`,
	`sc`.`tel` AS `tel`,
	`sc`.`security_amount` AS `security_amount`,
	`sc`.`isuredNumber` AS `isuredNumber`,
	`sc`.`onDutyPersons` AS `onDutyPersons`,
	`sc`.`task_amount` AS `task_amount`,
	`sc`.`province` AS `province`,
	`sc`.`city` AS `city`,
	`sc`.`area` AS `area`,
	`sc`.`areacode` AS `areacode`,
	`sc`.`legalPersonNo` AS `legalPersonNo`,
	`sc`.`exported` AS `exported`,
	`sc`.`insuranceAmount` AS `insuranceAmount`,
	`sc`.`insuranceEscortAmount` AS `insuranceEscortAmount`,
	`sc`.`postalcode` AS `postalcode`,
	`sc`.`policyLine` AS `policyLine`,
	`sc`.`operatorphone` AS `operatorphone`,
	`sc`.`operatorname` AS `operatorname`,
	`sc`.`securityScopeType` AS `securityScopeType`,
	`sc`.`orgId` AS `orgId`,
	`sc`.`orgName` AS `orgName`,
	`sc`.`updateflag` AS `updateflag`,
	`sc`.`location` AS `location`,
	`sc`.`address` AS `address`,
	`sc`.`insurance_number` AS `insurance_number`,
	`sc`.`emergency_agreement` AS `emergency_agreement`,
	`sc`.`business_license` AS `business_license`,
	`sc`.`public_security_license` AS `public_security_license`,
	`sc`.`init` AS `init`,
	`sc`.`longitude` AS `longitude`,
	`sc`.`latitude` AS `latitude`,
	`sc`.`orderNo` AS `orderNo`,
	`sc`.`business_license_number` AS `business_license_number`,
	`sc`.`principal` AS `principal`,
	`sc`.`principal_phone` AS `principal_phone`,
	`sc`.`principal_id` AS `principal_id`,
	`sc`.`registered_money` AS `registered_money`,
	`sc`.`fixed_assets` AS `fixed_assets`,
	`sc`.`turnover_year` AS `turnover_year`,
	`sc`.`annual_profits` AS `annual_profits`,
	`sc`.`public_security_license_number` AS `public_security_license_number`,
	`sc`.`give_license_name` AS `give_license_name`,
	`sc`.`give_license_date` AS `give_license_date`,
	`sc`.`national` AS `national`,
	`sc`.`economic_type` AS `economic_type`,
	`sc`.`company_type` AS `company_type`,
	`sc`.`human_defense_level` AS `human_defense_level`,
	`sc`.`risk_assessment_level` AS `risk_assessment_level`,
	`sc`.`security_association` AS `security_association`,
	`sc`.`economic_type_legal_person` AS `economic_type_legal_person`,
	ifnull(`sc`.`is_subsidiary`,0) AS `is_subsidiary`,
	`sc`.`armed_escort_level` AS `armed_escort_level`,
	`sc`.`security_technology_level` AS `security_technology_level`,
	`sc`.`otherSecScopeType` AS `otherSecScopeType`,
	`sc`.`rawid` AS `rawid`,
	`sc`.`inrawid` AS `inrawid`,
	`sc`.`institutionType` AS `institutionType`,
	`sc`.`sponsorlicenseType` AS `sponsorlicenseType`,
	`sc`.`comId` AS `comId`,
	`sc`.`companyCode` AS `companyCode`,
	`sc`.`seq` AS `seq`,
	`sc`.`trainContent` AS `trainContent`,
	`sc`.`parentId` AS `parentId`,
	`sc`.`local` AS `local`,
	`sc`.`officeAreaCode` AS `officeAreaCode`,
	`sc`.`officeOrgId` AS `officeOrgId`,
	`sc`.`officeOrgName` AS `officeOrgName`,
	ifnull(`u`.`init`, 0) AS `state`,
	sc.companyState AS `companyState`,
	`u`.`phone` AS `phone`
FROM
	(
		`security_company` `sc`
		JOIN `user` `u` ON (
			(
				(`sc`.`user_id` = `u`.`id`)
				AND (`u`.`active` = TRUE)
			)
		)
	);