网销升级脚本 - 青春不止丶奋斗不息

1.修改客户配置的节点信息

UPDATE t_cus_tab_info t SET t.table_name_local = 'wx_fc_vehicle' WHERE t.table_name_local = 'wx_vehicle';
UPDATE t_cus_tab_info t SET t.table_name_local = 'wx_fc_brand' WHERE t.id in ('389837ad0f8f4686872c4c3018dc69a8','73d282cdf2ac4a20b58cf6710ab0e906','e0ecac94b35e4fb5a558b845f101ce9c');
UPDATE t_cus_tab_info t SET t.table_name_local = 'wx_fc_vehicle_famy' WHERE t.table_name_local = 'wx_fc_vehicle_famy_view';
UPDATE t_cus_tab_info t SET t.table_name_local = 'wx_fc_vehicle_parent' WHERE t.id in ('e3b0998264ea453b97a94e381ad9afa2','bc0d4e3ce6c04c2590edc86a5b4c876b','2c7667a364c040b0994025f64b77dc9f');
UPDATE t_cus_tab_info t SET t.table_name_local = 'wx_fc_vehicle_group' WHERE t.id in ('42c785ea37334241a96aab14b9aed5ac','5c592647a06b4228aadf5111791b57c7','9595ceac337a48ffbe7154f7bff48159');

3.网销视图重建

ALTER 
VIEW `wx_fc_vehicle` AS 
SELECT
	`fv`.`DATA_VERSION` AS `data_version`,
	`fv`.`VEHICLE_ID` AS `vehicle_id`,
	`fvf`.`FAMILY_ID` AS `family_id`,
	`fb`.`BRAND_ID` AS `brand_id`,
	`fv`.`PA_CODE` AS `pa_code`,
	`fv`.`RB_CODE` AS `rb_code`,
	`fv`.`STANDARD_NAME` AS `standard_name`,
	`fv`.`VEHICLE_ALIAS` AS `vehicle_alias`,
	`fv`.`GB_CODE` AS `gb_code`,
	`fdv1`.`NAME` AS `gearbox_type`,
	`fv`.`ANTI_THEFT` AS `ANTI_THEFT`,
	(
		CASE
		WHEN (`fv`.`ABS_FLAG` = '1') THEN
			'有'
		WHEN (`fv`.`ABS_FLAG` = '0') THEN
			'无'
		END
	) AS `ABS_FLAG`,
	`fv`.`IMPORT_FLAG` AS `IMPORT_FLAG`,
	`fv`.`VIN_CODE` AS `vin_code`,
	`fdv2`.`CODE` AS `RISK_FLAG`,
	`fv`.`STOP_FLAG` AS `STOP_FLAG`,
	`fv`.`REMARK` AS `remark`,
	`fv`.`FULL_WEIGHT` AS `full_weight`,
	`fv`.`DISPLACEMENT` AS `displacement`,
	`fv`.`SEAT` AS `seat`,
	`fv`.`SEAT_MAX` AS `seat_max`,
	`fv`.`POWER` AS `power`,
	`fv`.`SEAT_MIN` AS `seat_min`,
	`fv`.`AIRBAG_NUM` AS `airbag_num`,
	`fv`.`TONNAGE` AS `tonnage`,
	`foc`.`COM_CODE` AS `COMPANY_CODE`,
	`foc`.`COM_NAME` AS `COMPANY_NAME`,
	`fb`.`BRAND_CODE` AS `brand_code`,
	`fb`.`BRAND_NAME` AS `brand_name`,
	`fvf`.`FAMILY_CODE` AS `family_code`,
	`fvf`.`FAMILY_NAME` AS `family_name`,
	`fvf`.`FAMILY_ABBR` AS `family_abbr`,
	`fvf`.`FAMILY_ALIAS` AS `family_alias`,
	`fv`.`SEARCH_CODE` AS `search_code`,
	`fvp`.`PURCHASE_PRICE` AS `purchase_price`,
	`price_tax3` (
		`fvp`.`PURCHASE_PRICE`,
		`fv`.`RB_CODE`
	) AS `PURCHASE_PRICE_TAX`,
	`fvp`.`KINDRED_PRICE` AS `kindred_price`,
	`price_tax3` (
		`fvp`.`KINDRED_PRICE`,
		`fv`.`RB_CODE`
	) AS `KINDRED_PRICE_TAX`,
	`fdv4`.`CODE` AS `VEHICLE_CLASS_CODE`,
	`fdv4`.`NAME` AS `VEHICLE_CLASS`,
	`fv`.`SEARCH_CODE1` AS `search_code1`,
	`fieldvaluespilt_max` (`fv`.`FULL_WEIGHT`) AS `FULL_WEIGHT_MAX`,
	`fieldvaluespilt_min` (`fv`.`FULL_WEIGHT`) AS `FULL_WEIGHT_MIN`,
	`fv`.`INSURANCE_CLASS` AS `INSURANCE_CODE`,
	`fv`.`MARKET_DATE` AS `market_date`,
	`fv`.`ENGINE_DESC` AS `engine_desc`,
	`fvg`.`BATHOLITH` AS `batholith`,
	(
		CASE
		WHEN (`fv`.`BODY_TYPE` = '-1') THEN
			''
		WHEN (
			`fv`.`BODY_TYPE` = 'I0000000000000000190000000000118'
		) THEN
			'2厢'
		WHEN (
			`fv`.`BODY_TYPE` = 'I0000000000000000190000000000119'
		) THEN
			'3厢'
		END
	) AS `body_type`,
	`fv`.`YEAR_PATTERN` AS `YEAR_PATTERN`,
	`fv`.`GROUP_ID` AS `GROUP_ID`,
	`fv`.`STANDARDNAME1` AS `VEHICLE_FGW_NAME`,
	`fv`.`STANDARDNAME2` AS `VEHICLE_FGW_CODE`,
	ucase(
		REPLACE (
			`fv`.`STANDARDNAME2`,
			' ',
			''
		)
	) AS `STANDARDNAME_2`,
	`fps`.`PARENT_ID` AS `parent_id`,
	`fdv5`.`CODE` AS `POWER_CODE`,
	`fdv5`.`NAME` AS `POWER_NAME`,
	(
		CASE
		WHEN (
			(
				(
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000001'
				)
				OR (
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000002'
				)
				OR (
					(
						`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000003'
					)
					AND (`fv`.`SEAT` <= 9)
				)
			)
			AND (
				`fv`.`POWER_TYPE` = 'I0000000000000000190000000000080'
			)
		) THEN
			'X31'
		WHEN (
			(
				(
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000001'
				)
				OR (
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000002'
				)
				OR (
					(
						`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000003'
					)
					AND (`fv`.`SEAT` <= 9)
				)
			)
			AND (
				`fv`.`POWER_TYPE` = 'fbaeb0d2c057ffc800000000f2cf0c8a'
			)
		) THEN
			'X32'
		ELSE
			`m`.`VNE_NAME`
		END
	) AS `FUEL_CODE`,
	(
		CASE
		WHEN (
			(
				(
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000001'
				)
				OR (
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000002'
				)
				OR (
					(
						`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000003'
					)
					AND (`fv`.`SEAT` <= 9)
				)
			)
			AND (
				`fv`.`POWER_TYPE` = 'I0000000000000000190000000000080'
			)
		) THEN
			'纯电动乘用车'
		WHEN (
			(
				(
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000001'
				)
				OR (
					`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000002'
				)
				OR (
					(
						`fv`.`VEH_CLASS_ONE` = 'L0000000000000000270000000000003'
					)
					AND (`fv`.`SEAT` <= 9)
				)
			)
			AND (
				`fv`.`POWER_TYPE` = 'fbaeb0d2c057ffc800000000f2cf0c8a'
			)
		) THEN
			'燃料电池乘用车'
		ELSE
			`m`.`VNE_CODE`
		END
	) AS `FUEL_NAME`,
	`fun_fuelName` (`fv`.`RB_CODE`) AS `FUEL_NAME_NEW`,
	`fun_fuelCode` (`fv`.`RB_CODE`) AS `FUEL_CODE_NEW`,
	`fps`.`DELETED` AS `deleted`,
	`fv`.`APPROVE_DATE` AS `approve_date`,
	`fb`.`CREATED_DATE` AS `created_date`,
	`fb`.`NAME_DATE` AS `name_date`,
	`fvf`.`CREATED_DATE` AS `fvfcreated_date`,
	`fvf`.`APPROVED_DATE` AS `approved_date`,
	`fps`.`UPDATED_DATE` AS `updated_date`,
	`fv`.`VEH_CLASS` AS `veh_class`,
	`fv`.`CFG_LEVEL` AS `cfg_level`,
	`fv`.`VEHICLE_SIZE` AS `vehicle_size`,
	`fv`.`TRACK_FRONT` AS `track_front`,
	`fv`.`TRACK_REAR` AS `track_rear`,
	`fv`.`WHEEL_BASE` AS `wheel_base`,
	`fdv6`.`NAME` AS `gyfs`,
	`fdv7`.`NAME` AS `qdxs`,
	`fdv8`.`NAME` AS `value_num`,
	`fvp`.`LIST_PRICE` AS `list_price`,
	`price_tax3` (
		`fvp`.`LIST_PRICE`,
		`fv`.`RB_CODE`
	) AS `LIST_PRICE_TAX`,
	`fv`.`GEAR_NUM` AS `gear_num`,
	concat(
		ifnull(`fb`.`BRAND_NAME`, ''),
		'_',
		ifnull(`fvf`.`FAMILY_NAME`, ''),
		'_',
		ifnull(`foc`.`COM_NAME`, ''),
		'_',
		ifnull(`fv`.`VEHICLE_ALIAS`, ''),
		'_',
		ifnull(`fv`.`STANDARD_NAME`, ''),
		'_',
		ifnull(`fv`.`SEARCH_CODE1`, ''),
		'_',
		ifnull(`fv`.`SEARCH_CODE`, '')
	) AS `complex_search`,
	'' AS `NULLSTATUS`,
	'0' AS `hc_flag`,
	`fv`.`IA_DATE` AS `ia_date`,
	`fv`.`IA_FLAG` AS `ia_flag`,
	NULL AS `NUL`
FROM
	(
		`fc_parent_sub` `fps`
		JOIN (
			(
				(
					(
						(
							(
								(
									(
										(
											(
												(
													(
														(
															(
																`fc_vehicle` `fv`
																LEFT JOIN `fc_vehicle_famy` `fvf` ON (
																	(
																		`fv`.`FAMILY_ID` = `fvf`.`FAMILY_ID`
																	)
																)
															)
															LEFT JOIN `fc_brand` `fb` ON (
																(
																	`fv`.`BRAND_ID` = `fb`.`BRAND_ID`
																)
															)
														)
														LEFT JOIN `fc_old_company` `foc` ON (
															(
																`fv`.`FACTORY_ID` = `foc`.`COM_CODE`
															)
														)
													)
													LEFT JOIN `dic_vehicle` `fdv1` ON (
														(
															`fv`.`GEARBOX_TYPE` = `fdv1`.`ID`
														)
													)
												)
												LEFT JOIN `fc_vehicle_prc` `fvp` ON (
													(
														`fv`.`VEHICLE_ID` = `fvp`.`VEHICLE_ID`
													)
												)
											)
											LEFT JOIN `dic_vehicle` `fdv2` ON (
												(
													`fv`.`RISK_FLAG` = `fdv2`.`ID`
												)
											)
										)
										LEFT JOIN `dic_vehicle` `fdv6` ON (
											(
												`fv`.`FUEL_JET_TYPE` = `fdv6`.`ID`
											)
										)
									)
									LEFT JOIN `dic_vehicle` `fdv7` ON (
										(
											`fv`.`DRIVEN_TYPE` = `fdv7`.`ID`
										)
									)
								)
								LEFT JOIN `dic_vehicle` `fdv8` ON (
									(
										`fv`.`VALVE_NUM` = `fdv8`.`ID`
									)
								)
							)
							LEFT JOIN `dic_vehicle` `fdv4` ON (
								(
									`fv`.`VEHICLE_CLASS` = `fdv4`.`ID`
								)
							)
						)
						LEFT JOIN `dic_vehicle` `fdv5` ON (
							(
								`fv`.`POWER_TYPE` = `fdv5`.`ID`
							)
						)
					)
					LEFT JOIN `fc_vehicle_grp` `fvg` ON (
						(
							`fv`.`GROUP_ID` = `fvg`.`GROUP_ID`
						)
					)
				)
				LEFT JOIN `vne_pub_relation` `t` ON (
					(
						`fv`.`STANDARDNAME2` = `t`.`VNE_PUB_ID`
					)
				)
			)
			LEFT JOIN `vne_pub_item` `m` ON (
				(`t`.`MODEL_CODE` = `m`.`ID`)
			)
		)
	)
WHERE
	(
		(1 = 1)
		AND (
			`fv`.`VEHICLE_ID` = `fps`.`SUB_ID`
		)
		AND (
			(`fv`.`STATUS` = '3')
			OR (`fv`.`STATUS` = '2')
		)
		AND (`fv`.`DELETED` = '0')
		AND (`fvf`.`DELETED` = '0')
		AND (`fb`.`DELETED` = '0')
		AND (`foc`.`DELETED` = '0')
		AND (`fvg`.`DELETED` = '0')
		AND (`fps`.`DELETED` = '0')
	)

-- 网销品牌视图【wx_fc_brand】
ALTER 
VIEW `wx_fc_brand` AS 
SELECT
	`t`.`BRAND_PY` AS `brand_py`,
	`t`.`BRAND_PY` AS `brand_pyqp`,
	`t`.id AS `brand_id`,
	`t`.`BRAND_CODE` AS `brand_code`,
	`t`.`BRAND_NAME` AS `brand_name`,
	fb.BRAND_ABBR AS `brand_abbr`,
	`t`.brand_en AS `brand_en`,
	concat(`t`.`BRAND_CODE`, '.jpg') AS `brand_pic`,
	concat(`fbp`.`SCWZ`, 'small/') AS `scwz`,
	`fbp`.`TPMC` AS `tpmc`,
	`t`.`IMPORT_FLAG` AS `import_flag`,
	`t`.brand_hot AS `hot_spot`,
	`t`.`NET_FLAG` AS `net_flag`,
	t.brand_name_alias AS `brand_initial`,
	`t`.modified_date AS `UPDATED_DATE`,
	`fob`.`PPMC` AS `BRAND_PIC_OLD`,
	`fob`.`OLD_BRAND_CODE` AS `BRAND_CODE_OLD`,
	`fob`.`BRAND_NAME` AS `BRAND_NAME_OLD`,
	`fob`.`HOT` AS `HOT_SPOT_OLD`,
	`fob`.`SZM` AS `BRAND_INITIAL_OLD`,
	'' AS `approvedate`,
	`fob`.`BRAND_ABBR` AS `OLD_BRAND_ABBR`,
	`fob`.`BRAND_ALIAS` AS `OLD_BRAND_ALIAS`,
	`fob`.`BRAND_ENG` AS `OLD_BRAND_ENG`,
	(
		CASE
		WHEN (`fh`.`HOT_ORDER` = '') THEN
			'0'
		ELSE
			`fh`.`HOT_ORDER`
		END
	) AS `hot_order`,
	'0' AS `hc_flag`,
	fb.COMPANY_ID AS `company_id`,
	`fob`.`BRAND_PY` AS `OLD_BRAND_PY`,
	`fob`.`ID` AS `OLD_BRAND_ID`,
	'' AS `NUL`,
	`t`.`DATA_VERSION` AS `DATA_VERSION`
FROM
	(
		(
			(
				(`process_wx_brand` `t`)
				LEFT JOIN `fc_brand_pricture` `fbp` ON ((`t`.`id` = `fbp`.`PPID`))
			)
			LEFT JOIN `fc_old_brand` `fob` ON (
				(`t`.old_brand_id = `fob`.ID)
			)
		)
		LEFT JOIN `fc_brand_hot` `fh` ON (
			(
				`t`.`BRAND_CODE` = `fh`.`BRAND_CODE`
			)
		)
LEFT JOIN fc_brand fb on t.id = fb.BRAND_ID
	)
WHERE
	(
		(`fob`.`DELETED` = '0')
		AND (`t`.`DELETED` = '0')
		AND (`fbp`.`SCBZ` = '0')
		AND EXISTS (
			SELECT
				NULL
			FROM
				`fc_vehicle_parent` `p`
			WHERE
				(
					(`p`.`DELETED` = '0')
					AND (`p`.`BRAND_ID` = `t`.id)
				)
		)
	)

-- 网销车系视图【wx_fc_vehicle_famy】
ALTER 
VIEW `wx_fc_vehicle_famy` AS 
SELECT DISTINCT
	famy.BRAND_FAMILY_ALIAS_PY AS `BRAND_FAMILY_ALIAS_PY`,
	famy.BRAND_FAMILY_ALIAS AS `BRAND_FAMILY_ALIAS`,
	famy.FAMILY_PY AS `family_pyqp`,
	`fvf`.`FAMILY_CODE` AS `FAMILY_CODE`,
	`fvf`.`FAMILY_PY` AS `FAMILY_PY`,
	`fvf`.family_en AS `FAMILY_EN`,
	`fvf`.`id` AS `FAMILY_ID`,
	`fvf`.`FAMILY_NAME` AS `FAMILY_NAME`,
	`fvf`.family_short_name AS `FAMILY_ABBR`,
	`fvf`.`BRAND_ID` AS `BRAND_ID`,
	`fvf`.`DATA_VERSION` AS `data_version`,
	`fvf`.`NET_FLAG` AS `NET_FLAG`
FROM
	`process_wx_family` `fvf`
LEFT JOIN `process_wx_parent` `fvp` ON `fvp`.`FAMILY_ID` = `fvf`.`id`
LEFT JOIN fc_vehicle_famy famy ON fvf.id = famy.FAMILY_ID
WHERE
	fvp.approve_state = '2';

-- 网销父车型视图【wx_fc_vehicle_parent】
ALTER 
VIEW `wx_fc_vehicle_parent` AS 
SELECT
	`fvp`.parent_id AS `parent_veh_id`,
	`fvp`.`BRAND_ID` AS `brand_id`,
	`fvp`.`FAMILY_ID` AS `family_id`,
	`fvp`.`GROUP_ID` AS `group_id`,
	`fvp`.`PARENT_VEH_CODE` AS `parent_veh_code`,
	`fvp`.`PARENT_VEH_NAME` AS `parent_veh_name`,
	'' AS `pic_path`,
	`fvp`.`ENGINE_DESC` AS `engine_desc`,
	`fvp`.`GEARBOX_TYPE` AS `gearbox_type`,
	`fdv`.`NAME` AS `gearbox_name`,
	`fvp`.`IMPORT_FLAG` AS `import_flag`,
	`fvp`.`PRICE` AS `price`,
	`fvp`.price_tax AS `taxprice`,
	`fvp`.`REMARK` AS `remark`,
	`fvp`.`DELETED` AS `deleted`,
	`fvp`.`SEAT_FLAG` AS `seat_flag`,
	`fvp`.`SEAT` AS `seat`,
	`fvp`.`NET_FLAG` AS `NET_FLAG`,
	`fvp`.approve_date AS `APPROVEDATE`,
	'' AS `model_year_s`,
	'' AS `model_year_e`,
	`fvp`.config_name AS `config_level`,
	'' AS `special_field`,
	`fvp`.`CREATED_DATE` AS `CREATED_DATE`,
	`fvp`.`UPDATED_DATE` AS `UPDATED_DATE`,
	`fvp`.`DATA_VERSION` AS `data_version`,
	date_format(
		`fvp`.`CREATED_DATE`,
		'%Y-%m-%d %T'
	) AS `CREATED_DATE_CHAR`,
	date_format(
		`fvp`.`UPDATED_DATE`,
		'%Y-%m-%d %T'
	) AS `UPDATED_DATE_CHAR`,
	'JY' AS `CREATED_BY`,
	'0' AS `hc_flag`,
	'JY' AS `UPDATED_BY`
FROM
	(
		`process_wx_parent` `fvp`
		LEFT JOIN `dic_vehicle` `fdv` ON (
			(
				`fdv`.`CODE` = `fvp`.`GEARBOX_NAME`
			)
		)
	)
WHERE
	(
		(`fvp`.`DELETED` = '0')
		AND (`fvp`.approve_state = '2')
	)

-- 网销车款视图【wx_fc_vehicle_group】
ALTER 
VIEW `wx_fc_vehicle_group` AS 
SELECT
	`fvs`.`GROUP_ID` AS `group_id`,
	`fvs`.`FAMILY_ID` AS `family_id`,
	`fvs`.`GROUP_CODE` AS `group_code`,
	`fvs`.`GROUP_NAME` AS `group_name`,
	'' AS `group_py`,
	`fvs`.`DELETED` AS `deleted`,
	`fvs`.`NET_FLAG` AS `net_flag`,
	`fvs`.modified_date AS `UPDATED_DATE`,
	'' AS `PIC_PATH`,
	`fvs`.`CREATED_DATE` AS `CREATED_DATE`,
	`fvs`.`DATA_VERSION` AS `data_version`,
	date_format(
		`fvs`.`CREATED_DATE`,
		'%Y-%m-%d %T'
	) AS `CREATED_DATE_CHAR`,
	date_format(
		`fvs`.`modified_date`,
		'%Y-%m-%d %T'
	) AS `UPDATED_DATE_CHAR`,
	'JY' AS `CREATED_BY`,
	'0' AS `hc_flag`,
	'JY' AS `UPDATED_BY`,
	'0' AS `zeroSTATUS`
FROM
	(process_wx_style `fvs`)
WHERE
	(`fvs`.`DELETED` = '0')

null

posted on 2017-08-16 14:06 青春不止丶奋斗不息阅读(155) 评论(0) 编辑收藏举报