INSERT INTO show_apply (
	id,
	apply_code,
	show_code,
	organizer,
	dog_owner_name_cn,
	dog_owner_name_en,
	dog_owner_member_code,
	dog_breed_name_cn,
	dog_breed_name_en,
	dog_breed_group,
	dog_name,
	dog_gender,
	dog_generation,
	dog_birthday,
	pedigree_certified,
	other_certified,
	fci_code,
	chip_code,
	fdog_pedigree_certified,
	fdog_name,
	mdog_pedigree_certified,
	mdog_name,
	breeder,
	age_group,
	show_apply_appraisal,
	fee,
	payment_charging_item_id,
	order_price,
	refund_flag,
	absence_flag,
	bbe_flag,
	phone_apply_flag,
	phone_apply_fee,
	handler_member_code,
	payment_member_code,
	review_state,
	review_time,
	review_remarks,
	running_number,
	payment_state,
	payment_time,
	proccess_time,
	proccess_state,
	create_by,
	create_date,
	update_by,
	update_date,
	remarks,
	del_flag
) SELECT
	id,
	报名序号,
	赛事编号,
	'',
	犬主人,
	犬主英文名,
	会员号,
	犬种中文名,
	犬种英文名,
	犬种组,
	犬名,
	性别,
	'',
	出生日期,
	CKU血统证书号,
	国外血统证书号,
	FCI代码,
	芯片号,
	父犬血统证书号,
	父犬名,
	母犬血统证书号,
	母犬名,
	繁殖人,
	年龄组别,
	鉴定,
	报名费,
	pay_Pid,
	order_price,
	请假及取消,
	赛场请假,
	BBE,
	serviceFee,
	serviceFeePrice,
	sponsor_detil,
	pay_ckuid,
	info_Confirm,
	info_Confirm_time,
	info_Confirm_why,
	running_number,
	payout,
	payout_time,
	operate_time,
	operate,
	1 create_by,
	添加日期,
	1 update_by,
	修改日期,
	备注,
	del_tag
FROM
	dbo.cku_shows_baoming

update show_apply set dog_gender = 1 where dog_gender in ('公','公犬');
update show_apply set dog_gender = 2 where dog_gender in ('母');


update show_apply set show_apply_appraisal = 0 where show_apply_appraisal in ('否');
update show_apply set show_apply_appraisal = 1 where show_apply_appraisal in ('是');

update show_apply set bbe_flag = 1 where bbe_flag in ('BBE');


update show_apply set refund_flag = 0 where refund_flag is null;
update show_apply set refund_flag = 0 where refund_flag = '否';
update show_apply set refund_flag = 1 where refund_flag = '是';

update show_apply set absence_flag = 0 where absence_flag is null;
update show_apply set absence_flag = 0 where absence_flag = '否';
update show_apply set absence_flag = 1 where absence_flag = '是';

update show_apply set payment_state = 2 where payment_state = 1;
update show_apply set payment_state = 1 where payment_state = 0;


#修改赛事报名中的organizer和赛事主表中一致
UPDATE show_apply,
 (
	SELECT
		s_c.id,
		s_m.organizer
	FROM
		(
			SELECT
				id,
				substring_index(show_apply.show_code, ",", 1) AS show_code
			FROM
				show_apply
		) AS s_c
	LEFT JOIN show_child AS s_ch ON s_c.show_code = s_ch.show_code
	LEFT JOIN show_main AS s_m ON s_ch.main_show_id = s_m.id
	WHERE
		s_c.show_code <> ''
) AS tmp_sc
SET show_apply.organizer = tmp_sc.organizer
WHERE
	show_apply.id = tmp_sc.id




UPDATE 
	show_apply,(SELECT
	sa.id,
	sc.main_show_id
FROM	
	(
	SELECT
		sa.id,
		substring_index(sa.show_code,",",1) as show_code
	FROM
		show_apply as sa
	) as sa
LEFT JOIN
	show_child as sc
ON
	sa.show_code = sc.show_code) as myTmp
SET
	show_apply.main_show_id = myTmp.main_show_id
WHERE
	show_apply.id = myTmp.id