#会员信息导入 #user.money做索引,一定要做索引，否则就要挂机 insert into sys_member select u.id,"",u.会员号,u.繁殖登记编号,u.会员类别,u.交费详情,u.会员开始时间,u.会员截止时间,u.qslz_tag,u.qslz_time,u.mrUserType,u.mrBeiZhu,u.mrUserTime,u.推荐人,u.电话联络,u.SS_jstime,u.qydUserType,u.qydUserTime,u.登记日期,1,1,case when u.修改日期 = '' then null else u.修改日期 end 修改日期,u.备注,0,u.cku_club,case when m.money_amount is NULL then 0.00 else m.money_amount end,u.姓名,u.Email,u.固定电话,u.移动电话,u.姓名拼音,u.cku_cardtype,u.身份证号,u.性别,u.出生日期,u.Province,u.City,u.Street,"",u.邮政编码,u.QQ,"",u.传真电话,u.配送方式,u.证书配送方式,u.通知配送方式,1,"系统管理员",null,1,登记人员 from dbo.cku_user u left join dbo.cku_user_money m on u.会员号 = m.user_id; update sys_member set member_type = "basic" where member_type = "基础"; update sys_member set member_type = "professional" where member_type = "专业"; update sys_member set member_type = "basic" where member_type = "基础(赠送)"; update sys_member set member_type = "basic" where member_type = "基础会员"; update sys_member set member_type = "basic",remarks="普通" where member_type = "普通"; update sys_member set member_type = "basic",remarks="已交" where member_type = "已交"; update sys_member set member_type = "basic",remarks="杂志" where member_type = "杂志"; update sys_member set member_type = "basic",remarks="赞助" where member_type = "赞助"; update sys_member set member_type = "basic" where member_type like "%遮%"; update sys_member set member_type = "basic",remarks="合作医院" where member_type like "合作医院"; update sys_member set member_type = "professional" where member_type = "专业"; update sys_member set member_type = "professional" where member_type = "专业会员"; update sys_member set gender = "male" where gender = "男"; update sys_member set gender = "female" where gender = "女"; update sys_member set groomer_type="reserve" where groomer_type="预备会员"; update sys_member set groomer_type="formal" where groomer_type="正式会员"; #用户信息的导入 DELETE from jeesite_sys_user where id != '1'; insert into jeesite_sys_user select UserID,"","",UserName,UserPassword,UserID,UserName,UserEmail,"",UserMobile,2,UserLastIP,LastLogin,1,'1',JoinDate,'1',LastLogin,"",0,"",mailCheck,weixin_id,NULL,NULL,NULL,NULL,0,0 from dbo.Dv_User where UserID <> 1 ; update jeesite_sys_user set user_type = "1" where login_name in (select userID from dbo.oa_qxUser ); update jeesite_sys_user a set name = (select userName from dbo.oa_qxUser where userID = a.login_name ) where login_name in (select userID from dbo.oa_qxUser ); update jeesite_sys_user set mail_check = 1 where mail_check is NULL; #导入用户与登录帐号的关系 #这个功能用java代码实现，用sql实现效率较低 参考com.cku.sqlImport包 #导入合作机构与合作医院 insert into sys_org select id,cku_state,cku_hospital,cku_contact,null,cku_endtime,cku_yajin,cku_jftime,cku_hospital_type,leftSize,topSize,cku_jftime,'1','1',cku_jftime,'',0,cku_hospitalid,concat('HZ',id),cku_sm,cku_address,cku_tel from dbo.cku_hospital_user; update sys_org set org_type = 'club' where org_type = '合作机构'; update sys_org set org_type = 'hospital' where org_type = '合作医院'; #导入合作机构登录帐号 insert into jeesite_sys_user(id,login_name,password,name,create_by,update_by,user_type) select concat('HZ',id),cku_username,cku_password,cku_hospital,'1','1',3 from dbo.cku_hospital_user; #转换城市与省关系 #insert into jeesite_sys_area select (id+2),"1","0,1,",cku_province,30,id,2,1,"2016-05-27 08:00:00",1,"2016-05-27 08:00:00","",0 from province; #insert into jeesite_sys_area select (c.id+100),(p.id+2),CONCAT("0,1,",(p.id+2),","),c.cku_city,30,(c.id+100),3,1,"2016-05-27 08:00:00",1,"2016-05-27 08:00:00","",0 from city c LEFT JOIN province p on c.cku_province = p.cku_province; #导入用户的收件记录信息 insert into sys_express_receive select id,ckuid,ckuName,receiveInfo,receiveType,receiveExpress,receiveNumber,receiveTime,null,null,addTime,'1','1',editTime,receiveRemarks,0,"" from dbo.cku_post2; update sys_express_receive set express_type = '3' where express_type = '传真'; update sys_express_receive set express_type = '1' where express_type = '快递'; update sys_express_receive set express_type = '2' where express_type = '信件'; #导入发件记录信息 添加了打印字段 已加入sql中 默认已打印 insert into sys_express_send select id,会员号,会员姓名,发件内容,发件方式,case when 快递公司 is null then '' else 快递公司 end,发件单号,收件地址,邮政编码,收件人,领取时间,添加日期,移动电话,添加日期,'1','1',修改日期,'',0,备注,'1' from dbo.cku_post; update sys_express_send set express_type = '1' where express_type = '到付快递'; update sys_express_send set express_type = '2' where express_type = '月付快递'; update sys_express_send set express_type = '3' where express_type = '挂号信'; update sys_express_send set express_type = '4' where express_type = '自取'; #导入芯片记录 insert into sys_org_chip select id,cku_date,cku_chip,cku_address,userid,1,1,null,removeCause,0,null,remove from dbo.cku_hospital_chip; #全犬种成绩表 INSERT INTO show_results ( id, dog_id, show_code, referee, pedigree_certified, other_certified, race_lap, dog_breed_group, age_group, dog_gender, fci_code, dog_owner_name_cn, dog_owner_name_en, dog_owner_member_code, show_result, order_code, result_cc, remarks_cc, result_cacib, remarks_cacib, result_bob, result_bis, result_big, result_bow, result_wb, create_by, create_date, update_by, update_date, remarks, del_flag ) SELECT id, '' dog_id, 赛事编号, 裁判, 血统证书号, 其他证书号, 赛圈, 犬种组, 年龄组别, 性别, FCI代码, 犬主中文名, 犬主英文名, 会员号, 本场积分, 秩序号, CC, CC_Remarks, CACIB, CACIB_Remarks, BOB, BIS, BIG, BOW, WB, 1 create_by, SYSDATE() create_date, 1 update_by, SYSDATE() update_date, '' remarks, 0 del_flag FROM dbo.tab_grade; #牵犬师资格证书 update dbo.cku_handler_Certificate set 签发日期 = "2015-05-27" where id = 290; update dbo.cku_handler_Certificate set 签发日期 = "2015-11-11" where id = 450; INSERT INTO handler_qualification_certificate SELECT id, 会员号, 取证日期, 证书编号, null, 英文级别, 签发日期, judge, 0, 1, NULL, 2, null, 1, null, NULL, NULL, NULL, `添加日期`, NULL, 修改日期, NULL, del_tag, null FROM dbo.cku_handler_Certificate; #培训报名表 INSERT INTO handler_register SELECT id, 报名序号, 会员号, 姓名, NULL, NULL, 参赛级别, 培训机构, 报名费, '0', '0', order_price, pay_ckuid, NULL, grade, NULL, pay_Pid, zmDogClass, dmDogClass, anyDogClass, mrDogClass, '1', SYSDATE(), '2', SYSDATE(), '0', SYSDATE(), running_number, NULL, 1 create_by, add_time, 1 update_by, edit_time, '' remarks, 0 del_flag, '' avatar, 赛事编号, SYSDATE(), NULL, NULL FROM dbo.ckupxBaoming; update handler_register set show_level='MASTER' WHERE show_level='J' #更改请假状态 update handler_register set leave_cancel='1' where leave_cancel='是'; update handler_register set leave_cancel='0' where leave_cancel is null or leave_cancel='否'; update handler_register set show_leave='1' where show_leave='是'; update handler_register set show_leave='0' where show_leave is null or show_leave='否'; #更改支付状态 update handler_register set payment_state='2' where payment_state='1'; update handler_register set payment_state='1' where payment_state is null; #美容师结业证书 INSERT INTO groomer_diploma (SELECT id, 会员号, 姓名, 开始日期, 结束日期, NULL, 签发日期, NULL, 证书级别, 证书编号, NULL, NULL, '1', '1', NULL, NULL, NULL, '2', NULL, NULL, '1', NULL, 添加日期, NULL, 修改日期, NULL, del_tag, 学校, 学校英文 FROM dbo.CKU美容师培训结业证书 WHERE substring_index(证书编号,'-',1)= 'G') ; #美容结业证书级别变更**** UPDATE groomer_diploma a SET a.certificate_level = 'A' WHERE SUBSTRING_INDEX( ( SUBSTRING_INDEX(a.certificate_code, '-' ,- 2) ), '-', 1 ) = 'A'; UPDATE groomer_diploma a SET a.certificate_level = 'B' WHERE SUBSTRING_INDEX( ( SUBSTRING_INDEX(a.certificate_code, '-' ,- 2) ), '-', 1 ) = 'B'; UPDATE groomer_diploma a SET a.certificate_level = 'C' WHERE SUBSTRING_INDEX( ( SUBSTRING_INDEX(a.certificate_code, '-' ,- 2) ), '-', 1 ) = 'C'; update groomer_diploma set school_name_cn='西安时尚宠物美容学校' where school_name_cn='2015-9-30' ; #美容、培训合作学校 INSERT INTO training_institution SELECT id, NULL, cku_title, NULL, NULL, NULL, cku_tel, cku_address, NULL, NULL, cku_t, '系统管理员', SYSDATE(), NULL, NULL, NULL, '0', cku_content, cku_web, cku_picture FROM dbo.cku_peixun; #培训结业证书 INSERT INTO handler_diploma (SELECT id, 会员号, 姓名, 开始日期, 结束日期, NULL, NULL, 证书级别, 证书编号, 签发日期, NULL, NULL, '1', '1', NULL, '2', NULL, '0', NULL, NULL, NULL, '系统管理员', 添加日期, NULL, 修改日期, NULL, del_tag, NULL, 学校英文, 学校 FROM dbo.CKU美容师培训结业证书 WHERE substring_index(证书编号,'-',1)!= 'G'); update handler_diploma SET handler_type='0' where certificate_level='牵犬师C级' ; update handler_diploma SET handler_type='0' where certificate_level='牵犬师B级' ; update handler_diploma SET handler_type='0' where certificate_level='牵犬师A级' ; update handler_diploma SET handler_type='0' where certificate_level='精英培训班' ; update handler_diploma SET handler_type='1' where handler_type is NULL; #犬舍表 INSERT INTO kennel SELECT id, 会员号, 犬舍编号, CASE WHEN 犬舍中文名 IS NULL THEN '' ELSE 犬舍中文名 END 犬舍中文名, 犬舍英文及拼音, fci_kennel, standby_fci_kennel, 犬舍犬种, dogClass, 犬舍电话, 犬舍网址, 犬舍地址, 犬舍邮编, 犬舍类型, 犬舍截止时间, 登记人员, 操作人员, info_Confirm, info_Confirm_why, standby_fci_kennel_info_Confirm, standby_fci_kennel_info_Confirm_why, dy_num, dy_num, fci_kennel_id, fci_kennel_address, 犬舍开始时间, 1, 1, 修改日期, 备注, 0 FROM dbo.cku_kennel; #更改犬舍审核状态 UPDATE kennel SET review_state ='1' WHERE review_state='2'; update kennel SET fci_review_state='1' WHERE fci_review_state='2'; #在册牵犬师(功能已废除,数据要保留可查询) INSERT INTO `handler` SELECT id, 会员姓名, 网站会员, 会员号, 性别, 证件号码, 详细地址, 联系电话, 证书号, 邮政编码, 申请资格, 资格说明, 审核人, '系统管理员', SYSDATE(), null, null, 备注, '0', del_tag FROM dbo.cku_handler; #美容活动导入 INSERT INTO groomer_show SELECT id, showsID, startTime, endTime, showsName, showsAddress, showsType, cku_endtime, null, null, null, null, cku_caipanid, null, null, null, null, null, null, null, null, null, bmFei, mr_level, '系统管理员', addTime, null, editTime, null, del_tag FROM dbo.ckumrShows update groomer_show SET del_flag='0' WHERE del_flag is NULL UPDATE groomer_show SET show_type='exam' where show_type='考试'; UPDATE groomer_show SET show_type='match' where show_type='比赛'; UPDATE groomer_show SET show_type='cathedra' where show_type='讲座'; #美容报名表 INSERT INTO groomer_register SELECT id, 赛事编号, 报名序号, 会员号, 参赛人员, 请假及取消, 赛场请假, 姓名, 参赛级别, 培训机构, 报名费, 请假及取消, 赛场请假, 比赛犬种, order_price, payout, 操作人员, grade, pay_Pid, NULL, '1', NULL, NULL, running_number, payout, NULL, NULL, '0', '系统管理员', add_time, null, edit_time, 备注, del_tag, testNo, cku_caipanid, null, NULL, null from dbo.ckumrBaoming #更改删除标记 UPDATE groomer_register SET del_flag='0' WHERE del_flag is NULL; #更改报名级别 update groomer_register set show_level='MASTER' WHERE show_level='教师'; update groomer_register set show_level='none' WHERE show_level='无'; update groomer_register set show_level='none' WHERE show_level='提供模特（免费）'; update groomer_register set show_level='C' WHERE show_level='C 第一天考试 '; update groomer_register set show_level='none' WHERE show_level='精英组'; update groomer_register set show_level='none' WHERE show_level='9日精英班'; update groomer_register set show_level='none' WHERE show_level='赛 '; update groomer_register set show_level='none' WHERE show_level='精英 '; update groomer_register set show_level='none' WHERE show_level='无 '; update groomer_register set show_level='none' WHERE show_level='创意美容 '; #更改成绩类别 update groomer_register set grade='0' where grade='未通过'; update groomer_register set grade='2' where grade='通过'; update groomer_register set grade='3' where grade='优秀奖'; update groomer_register set grade='1' where grade='缺考'; update groomer_register set grade='4' where grade='特别优秀奖'; #更改请假状态 update groomer_register set leave_cancel='1' where leave_cancel='是'; update groomer_register set leave_cancel='0' where leave_cancel is null or leave_cancel='否'; update groomer_register set show_leave='1' where show_leave='是'; update groomer_register set show_leave='0' where show_leave is null or show_leave='否'; #更改支付状态 update groomer_register set payment_state='2' where payment_state='1'; update groomer_register set payment_state='1' where payment_state is null; #证书换发配种证明表 UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20060815" where id =10671; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090511" where id =11292; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090429" where id =6211; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090415" where id =10071; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20020228" where id =7768; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-06-30" where id =16750; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2013-11-30" where id =15989; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2012-11-30" where id =14134; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20061130" where id =6344; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090630" where id =6362; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20081130" where id =6407; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20050630" where id =6660; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080201" where id =6696; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080824" where id =6716; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090609" where id =6767; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20070429" where id =7640; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080131" where id =8494; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100428" where id =8648; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20060328" where id =10391; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2012-06-30" where id =20335; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20031220" where id =10266; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090504" where id =10149; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20040919" where id =12494; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-02-28" where id =17460; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-02-28" where id =17391; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2011-02-28" where id =13849; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-02-28" where id =17414; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2011-11-30" where id =14135; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20061218" where id =9767; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090218" where id =9820; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-02-28" where id =18942; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100313" where id =10542; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080817" where id =11289; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100324" where id =11291; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110101" where id =10792; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20040501" where id =10530; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100131" where id =9863; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20081120" where id =9963; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20101013" where id =9839; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20071024" where id =9841; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20101030" where id =10572; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080107" where id =9985; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20091130" where 配犬日期 = '2009-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20131130" where 配犬日期 = '2013-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20141130" where 配犬日期 = '2014-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20151130" where 配犬日期 = '2015-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20111130" where 配犬日期 = '2011-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20121130" where 配犬日期 = '2012-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20101130" where 配犬日期 = '2010-11-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20140630" where 配犬日期 = '2014-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20050630" where 配犬日期 = '2005-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100630" where 配犬日期 = '2010-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080630" where 配犬日期 = '2008-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20130630" where 配犬日期 = '2013-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20150630" where 配犬日期 = '2015-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20120630" where 配犬日期 = '2012-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110630" where 配犬日期 = '2011-06-31'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110228" where 配犬日期 = '2011-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20150228" where 配犬日期 = '2015-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20140228" where 配犬日期 = '2014-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20120228" where 配犬日期 = '2012-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20130228" where 配犬日期 = '2013-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100228" where 配犬日期 = '2010-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080228" where 配犬日期 = '2008-02-30'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20130228" where 配犬日期 = '2013-02-29'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20140228" where 配犬日期 = '2014-02-29'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20150228" where 配犬日期 = '2015-02-29'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20120228" where 配犬日期 = '2012-02-29'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110228" where 配犬日期 = '2011-02-29'; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110312" where 配犬日期 = '20110312BC' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110611" where 配犬日期 = '20110611CKU-' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20091231" where 配犬日期 = '20091231FN' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-02-28" where 配犬日期 = '2015-02-29' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20130602" where 配犬日期 = '2513962' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-06-30" where 配犬日期 = '2-14-06-30' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100630" where 配犬日期 = '20100630CC' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110720" where 配犬日期 = '201100720' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20070108" where 配犬日期 = '20070108MS' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080526" where 配犬日期 = '20080526EE' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20060517" where 配犬日期 = '220060517' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-06-01" where 配犬日期 = '201506-01' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20050104" where 配犬日期 = '20050104KB' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20020304" where 配犬日期 = '20020304NB' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2011-02-28" where 配犬日期 = '2011-2-29' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2011-02-28" where 配犬日期 = '2011-02-29' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20080806" where 配犬日期 = '20080806L' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100102" where 配犬日期 = '20100102NK' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20060323" where 配犬日期 = '20060323SG' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100726" where 配犬日期 = '20100726BB' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110925" where 配犬日期 = '20110925CKU' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2009-02-20" where 配犬日期 = '20096-02-20' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2013-02-28" where 配犬日期 = '2013-2-30' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100514" where 配犬日期 = '20100514AC' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20090815" where 配犬日期 = '20090815TS' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20110620" where 配犬日期 = '2011620' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100810" where 配犬日期 = '20100810XL' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "20100626" where 配犬日期 = '20100626TD' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2013-07-08" where 配犬日期 = '2013-07-081' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2011-08-18" where 配犬日期 = '20111-08-18' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-07-26" where 配犬日期 = '2014-07026' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-07-26" where 配犬日期 = '2014-0702' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2010-11-29" where 配犬日期 = '201011-29' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2014-12-08" where 配犬日期 = '2014-128' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2012-02-16" where 配犬日期 = '2012-02-163' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-04-20" where 配犬日期 = '2-15-04-20' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-02-28" where 配犬日期 = '2015-2-29' ; UPDATE dbo.tab_amphimixis_prove set `配犬日期` = "2015-02-28" where 配犬日期 = '2015-2-30' ; INSERT INTO dog_amphimixis_prove SELECT id, 父母犬配犬证明号, 种类, case when 配犬日期 = '' then null else 配犬日期 end 配犬日期, 父犬血统证号, 母犬血统证号, 犬舍名称, 本胎数量, 死胎犬数, 夭折犬数, 死亡幼犬数, 寄养犬数, 存拦犬数, 登记犬数, 同胎犬, 繁殖人, 繁殖人地址, 会员号, '系统管理员', 添加日期, NULL, 修改日期, NULL, del_tag FROM dbo.tab_amphimixis_prove; update dbo.dog set running_number = "" where id=666; #犬只信息表 insert into dog( id, logout, birth_certificate_level_code, pedigree_certified_code, dog_owner, member_code, second_dog_owner, second_owner_member_num, breeder, name_cn, name_en, gender, dog_breed, birthdate, identification_flag, wool, color_flag, special_flag, hip_qualification_result, elbow_qualification_result, DNA, breed_qualification, inbreeding, referee_evaluation, show_level, show_grade, test_level, test_grade, archives_code, fdog_pedigree_certified, mdog_pedigree_certified, fmdog_breed_certified_code, inbreeding_print, print_state, pedigree_certified_old, pedigree_certified_en, operator, DNA_pay_state, breed_pay_state, running_number, breeder_member_code, dog_generation, breed_reviewe, disqualification_number, dog_state, create_by, create_date, update_by, update_date, remarks, del_flag ) select id, logout, 出生证明登记编号, 血统证书号, 犬主人, 会员号, 第二犬主, 第二犬主会员号, 繁殖人, 中文姓名, 英文姓名, 性别, 种类, case when 出生日期 = '' then null else 出生日期 end 出生日期, 身份识别标志, 毛质, 颜色及标志, 特殊标记, 髋关节鉴定结果, 肘关节鉴定结果, DNA, 繁殖资格, 近亲繁殖, 裁判评价, 比赛级别, 比赛成绩, 考试级别, 考试成绩, 档号, 父犬血统证号, 母犬血统证号, 父母犬配犬证明号, 近亲繁殖打印, print_type, 旧血统证书号, 国外登记证书号, 操作人员, pay_DNA, pay_fy, running_number, 繁殖人会员号, dogGenerations, fanyu_Confirm, case when loseNum = '' then 0 else loseNum end loseNum, del_tag, 1 create_by, 添加日期 create_date, 1 update_by, SYSDATE() update_date, '' remarks, 0 del_flag from `dbo`.dog; update dog set gender = '1' where gender = '公'; update dog set gender = '2' where gender = '母'; update dog set del_flag = '1' where dog_state = '1'; #犬只种类表 insert into dog_type( id, dog_breed_code, breed_en_name, breed_en_alias, breed_cn_name, breed_cn_alias, dog_breed, countries, CACIB, standard_time, type_group, node, figures, dog_breed_photo, now_max_number, breed_certified_number, dna_register_number, hd_certified_number, short_name, hot, create_by, create_date, update_by, update_date, remarks, del_flag ) select id, 犬种类代码, 犬种类英文名称, 犬种类英文别名, 犬种类中文名称, 犬种类中文别名, 犬种, 国别, CACIB, 标准时间, 组, 节, 位, 犬种类照片, case when 当前最大序号 = '' then null else 当前最大序号 end 当前最大序号, case when 繁育证书序号 = '' then null else 繁育证书序号 end 繁育证书序号, case when DNA登记序号 = '' then null else DNA登记序号 end DNA登记序号, case when HD证书序号 = '' then null else HD证书序号 end HD证书序号, short_name, hot, 1 create_by, case when 添加日期 ='' then null else 添加日期 end 添加日期, 1 update_by, case when 修改日期='' then null else 修改日期 end 修改日期, '' remarks, del_tag from `dbo`.tab_dog_class_code; update dog_type set del_flag = '0' where del_flag is null or del_flag = ''; #新生犬登记表 insert into dog_chip( id, birth_code, payment_member_code, operator, price, hospital_name, hospital_code, del_time, review_state, review_time, review_remarks, running_number, payment_state, payment_time, process_time, process_state, create_by, create_date, update_by, update_date, remarks, del_flag, is_canceled ) select id, cszID, pay_ckuid, operater, order_price, hospital, hospital_id, del_tag_time, info_Confirm, info_Confirm_time, info_Confirm_why, running_number, payout, payout_time, operate_time, operate, 1 create_by, add_time, 1 update_by, SYSDATE(), '' remarks, del_tag as del_flag, del_tag as is_canceled from `dbo`.dog_NewDogChip; update dog_chip set del_flag = '0'; update dog_chip set payment_state = '2' where payment_state = '1'; update dog_chip set payment_state = '1' where payment_state = '0'; update dog_chip set payment_state = '1' where payment_state != '2'; #配种证明表 insert into dog_newborn( id, create_date, payment_state, running_number, breed_certified_code, area, male_dog_owner, mdog_owner_code, female_dog_owner, fdog_owner_code, male_dog_name, male_pedigree_certified, female_dog_name, female_pedigree_certified, breed_date, birthday, puppy_birth_num, puppy_register_num, review_state, review_remarks, male_dog_owner_topup, female_dog_owner_topup, batch_fee, # implanted_num, register, remark, dog_register_num, dog_breed, operator, kin_breed, price, birth_certificate, print_type, kin_get_dna, batch_num, urgent, dog_wo_time, create_by, update_date, update_by, del_flag ) select id, timestr, payout, running_number, dog_peinum, dog_place, dog_head_gong, dog_gong_userid, dog_head_mu, dog_mu_userid, dog_name_gong, dog_gong_card, dog_name_mu, dog_mu_card, dog_pei_date, dog_birthday, dog_new_num, dog_dj_num, dog_check, dog_check_why, dog_gong_xufei, dog_mu_xufei, dog_wo_fei, # dog_inf_num, recorder, dog_remark, dog_ckudj_num, dog_class, optioner, dog_jinqin, order_price, dogBirthCertificate, dog_dy_type, dog_qu_dna, dog_wo_num, cku_jiaji, dog_wo_time, 1 'create_by', sysdate() update_date, 1 'update_by', 0 'del_flag' from `dbo`.dog_new; update dog_newborn set payment_state = 2 where payment_state = 1;# 支付状态修改 update dog_newborn set payment_state = 1 where payment_state = 0; update dog_newborn set review_state = 1 where review_state = '通过';# 审核状态修改 update dog_newborn set review_state = 2 where review_state = '未通过'; update dog_newborn set review_state = 3 where review_state = '注销'; update dog_newborn set review_state = 0 where review_state = '待审核'; update dog_newborn set urgent = 0 where urgent = '否'; update dog_newborn set urgent = 1 where urgent = '是'; update dog_newborn set kin_breed = '1' where kin_breed = '1-2';# 近亲结果修改 update dog_newborn set kin_breed = '2' where kin_breed = '2-2'; update dog_newborn set kin_breed = '3' where kin_breed = '1-3'; update dog_newborn set kin_breed = '4' where kin_breed = '1-1'; # 犬主变更表 insert into dog_owner_change( id, dog_id, origin_owner_member_code, new_owner_member_code, origin_owner_name, new_owner_name, origin_second_owner_member_code, new_second_owner_member_code, origin_second_owner_name, new_second_owner_name, payment_member_code, review_state, review_time, payment_state, payment_time, running_number, process_state, process_time, create_date, update_date, review_remarks, price, reviewer ) select id, dogid, ckuid, new_ckuid, ckuname, new_ckuname, ckuid2, new_ckuid2, ckuname2, new_ckuname2, pay_ckuid, info_Confirm, info_Confirm_time, payout, payout_time, running_number, operate, operate_time, add_time, edit_time, info_Confirm_why, order_price, info_Confirm_User from `dbo`.dog_OwnerChange; update dog_owner_change set del_flag = '0'; update dog_owner_change set payment_state = 2 where payment_state = 1;# 将原支付状态进行修改 update dog_owner_change set payment_state = 1 where payment_state = 0; update dog_owner_change set review_state = 4 where review_state = 3;# 将原锁定状态值3改为4 # 繁育证书 insert into dog_breed_certified( id, dog_id, breed_deadline, breed_grade, shoulder, body_length, chest_depth, weight, testicles, appraises, dog_fur_color, disposition, attention, nerve_situation, natural_degree, instinct_confidence_suffer, sex_typical, physique, expressive, trunk, bones, muscles, forelimbs, hindlimbs, back, elbow_joint, strength_of_pasterns, front, croup, strength_of_hocks, gait, steps_forward, steps_backward, nails, feet, head, colour_of_teeth, upper_jaw, underjaw, teeth, faults_of_teeth, other_strengths_wcaknesses, suggestions_on_breeding, breed_identify_time, breed_identify_location, breed_certified_code, breed_identify_referee, create_date, update_date, del_flag, kin_breed, breed_results, create_by, update_by, remarks, review_state, review_time, review_remarks, running_number, payment_state, payment_time, process_time, process_state ) select id, dog_id, 繁育期限, 繁育级别, 肩高, 体长, 胸深, 体重, 睾丸, 总体评价, 毛色与毛质, 性情, 注意力, 神经状况, 自然程度, 本能自信和承受力, 性别特性, 体格, 表现力, 躯干情况, 骨骼, 肌肉, 前肢, 后肢, 背部, 肘关节连接, 前系强度, 正面, 尻部, 飞节强度, 步态, 步幅_前展, 步幅_后蹬, 趾甲, 趾, 头部, 眼睛颜色, 上颚, 下颚, 牙齿, 牙齿缺陷, 其他优缺点, 种犬繁育的意见, 繁育鉴定日期, 繁育鉴定地点, 繁育证书编号, 繁育鉴定裁判, 添加日期, 修改日期, del_tag, 近亲繁殖, 繁殖成绩, '1' create_by, '1' update_by, '' remarks, '1'review_state, SYSDATE() review_time, '' review_remarks, '' running_number, '2' payment_state, SYSDATE() payment_time, NULL process_time, '0' process_state from `dbo`.dog_Breed; # 冠军登录 insert into show_champion_login( id, pedigree_certified, login_type, login_code, dog_breed, membership_code, cku_state, running_number, payment_state, create_date, create_by, update_date, update_by, del_flag, price, review_state, review_time, review_remarks, payment_time, proccess_state, proccess_time, payment_membership_code, post_type, post_address, print_state, remarks, print_time ) select id, cku_zsid, cku_dltype, cku_dlid, cku_qz, cku_userid, cku_state, running_number, payout, add_time, 1 create_by, edit_time, 1 update_by, del_tag, order_price, info_Confirm, info_Confirm_time, '' info_Confirm_why, payout_time, operate, operate_time, pay_ckuid, cku_yjType, cku_address, dynum, remark, cku_dytime from `dbo`.cku_gjdl; update show_champion_login set login_type = '1' where login_type = '普通登录'; update show_champion_login set login_type = '2' where login_type = '铜质登录'; update show_champion_login set login_type = '3' where login_type = '银质登录'; update show_champion_login set login_type = '4' where login_type = '金质登录'; update show_champion_login set login_type = '5' where login_type = '超级登录'; update show_champion_login set login_type = '6' where login_type = '世界登录'; update show_champion_login set login_type = '7' where login_type = '青年登录'; update show_champion_login set payment_state = 2 where payment_state = 1; update show_champion_login set payment_state = 1 where payment_state = 0; # 将图片表修改为主键自增############ # 修改赛事裁判中文逗号的问题 update show_main set referee = REPLACE(referee,'，',',') where referee is not null and referee != ''; #更改错误字段 UPDATE dbo.`美容师资格证书` set 签发日期='2008-12-20' where 签发日期='12/20/2008'; UPDATE dbo.`美容师资格证书` set 签发日期='2009-3-10' where 签发日期='3/10/2009'; UPDATE dbo.`美容师资格证书` set 签发日期='2008-4-7' where 签发日期='4/7/2008'; UPDATE dbo.`美容师资格证书` set 签发日期='2009-6-3' where 签发日期='6/3/2009'; UPDATE dbo.`美容师资格证书` set 签发日期='2009-7-23' where 签发日期='7/23/2009'; UPDATE dbo.`美容师资格证书` set 签发日期='2009-11-13' where 签发日期='11/13/2009'; UPDATE dbo.`美容师资格证书` set 签发日期='2015-08-03' where 签发日期='20154-08-03'; UPDATE dbo.`美容师资格证书` set 签发日期='2014-01-11' where 签发日期='2014-01-113'; UPDATE dbo.`美容师资格证书` set 签发日期='2014-04-16' where 签发日期='21014-04-16'; UPDATE dbo.`美容师资格证书` set 签发日期='2014-05-10' where 签发日期='2014-05-104'; UPDATE dbo.`美容师资格证书` set 签发日期='2012-11-3' where 签发日期='2012-11-'; UPDATE dbo.`美容师资格证书` set 签发日期='2012-11-3' where 签发日期='B'; UPDATE dbo.`美容师资格证书` set 签发日期='2015-06-12' where 签发日期='CKU-GC-20150405' ; UPDATE dbo.`美容师资格证书` set 签发日期='2012-11-3' where 签发日期='B'; UPDATE dbo.`美容师资格证书` set 签发日期='2014-08-10' where 签发日期='20145-08-10'; #美容资格证书导入sql INSERT INTO groomer_qualification_certificate SELECT id, 会员号, 取证日期, 级别, 英文级别, 证书编号, 签发日期, 审查员, xiongHua, xiongHuaTime, '1', '1', NULL, NULL, NULL, '2', NULL, NULL, '1', '系统管理员', 添加日期, NULL, 修改日期, NULL, del_tag, NULL FROM dbo.美容师资格证书; #培训活动表 insert INTO handler_show SELECT id, showsID, startTime, endTime, showsName, showsAddress, showsType, cku_endtime, NULL, NULL, NULL, NULL, cku_caipanid, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, bmyouhui, bmFei, NULL, '系统管理员', addTime, NULL, editTime, NULL, 0 from dbo.ckupxShows; update handler_show SET show_type='0' where show_type='考试'; update handler_show SET show_type='1' where show_type='比赛'; update handler_show SET test_level='A' where substring_index(show_code,'-',-1)= 'A'; update handler_show SET test_level='B' where substring_index(show_code,'-',-1)= 'B'; update handler_show SET test_level='C' where substring_index(show_code,'-',-1)= 'C'; update handler_show SET test_level='none' where substring_index(show_code,'-',-1)= 'J'; #犬舍重命名表 INSERT INTO kennel_rename SELECT id, ckuid, kennelid, kennel_name_cn, kennel_name_en, kennel_name_cn_new, kennel_name_en_new, fci_kennel, standby_fci_kennel, order_price, operater, add_time, '系统管理员', NULL, edit_time, null, '0', info_Confirm, info_Confirm_time, payout, payout_time, operate, operate_time, running_number, info_Confirm_why FROM dbo.cku_kennel_NameChange; #转换支付状态 update kennel_rename set payment_state='2' where payment_state='1'; #犬舍所有人变更 INSERT INTO kennel_owner_change SELECT id, ckuid, kennelid, new_ckuid, order_price, add_time, '系统管理员', null, edit_time, NULL, '0', info_Confirm, info_Confirm_time, payout, payout_time, operate, operate_time, running_number, info_Confirm_why, operater FROM dbo.cku_kennel_OwnerChange; #更改审核状态 原数据0是未通过 UPDATE kennel_owner_change set review_state='2' WHERE review_state='0'; #更改状态 UPDATE kennel_owner_change set payment_state='2' WHERE payment_state='1'; #更改犬舍名字的字符 UPDATE kennel_rename SET original_name_en =REPLACE(original_name_en, "’", "'"); UPDATE kennel_rename SET new_name_en =REPLACE(new_name_en, "’", "'"); UPDATE kennel SET name_en =REPLACE(name_en, "’", "'"); #更改犬舍类别 UPDATE kennel SET type='year' WHERE type='年度'; UPDATE kennel SET type='permanent' WHERE type='永久'; INSERT INTO kennel_alliance( id, member_code, project, combo, operator, pay_amount, start_date, end_date, create_by, create_date, update_by, update_date, remarks, del_flag ) SELECT id, userid, cku_project, cku_package, cku_service, cku_payamount, cku_lmstarttime, cku_lmendtime, 1, SYSDATE(), 1, SYSDATE(), cku_lmbz, 0 FROM dbo.cku_kennel_qslm;