#商城#用户钱
insert into shop_user_money (id,community,member_code,user_money,rebate_money,create_by,create_date,update_by,update_date,del_flag)
select id,community,userid,userMoney,fanli,1,NOW(),1,NOW(),0 from dbo.shopUserMoney;
#商城#会员充值记录
insert into shop_pay_record (id,member_code,shop_order_code,payment_money,out_trade_no,payment_type,payment_way,payment_time,payment_remarks,create_by,create_date,update_by,update_date,del_flag  )
select id,ckuid,orderid,total_fee,trade_no,pay_type,pay_way,add_time,'',1,NOW(),1,NOW(),0 from dbo.payRecord;

#商城#商品分类
insert into shop_goods_class (id,name,short_name,parent_id,parent_ids,sort,create_by,create_date,update_by,update_date,del_flag)
select id,className,'',parentID,'0,',0,1,NOW(),1,NOW(),0 from dbo.shopGoodsClass;
#商城#商品
INSERT INTO shop_goods (id, class_id, code, name, image, price, buy_gifts, is_rebate, is_freight, weight, title, tag, details, is_shelves, limits, num, sale_num, comment_num, add_time, create_by, create_date, update_by, update_date, remarks, del_flag)
select id,spClass,'',spName,'',unitPrice,buyGifts,discounts,freight,spWeight,spTitle,spTag,spDetails,onSale,limited,10000,salesVolume,reviewsVolume,addTime,1,NOW(),1,NOW(),'',0 from dbo.shopGoods;

#商城#订单
INSERT INTO shop_order (id, state, num, price, member_code, member_name, type, channel, post_price, post_type, shop_order_code, post_name, post_code, post_order_no, post_del, province, city, address,
 message, order_date, is_send, send_date, is_return, return_date, is_org, send_post_type, send_post_order_no, payment_date, payment_way, payment_state, create_by, create_date, update_by, update_date, remarks, del_flag)
select id,'',1,totalPrice,userid,username,0,0,NULL,
( CASE WHEN ExpressCompany = '速通物流' THEN 'sutong'
WHEN ExpressCompany = '中铁物流' THEN 'zhongtie'
WHEN ExpressCompany = '韵达快递' THEN 'yunda'
WHEN ExpressCompany = '协会配送' THEN 'cku'
ELSE '' END ) as a1,
OrderID,postName,'',ExpressNumber,postTel,'','',postAddress,OrderRemarks,add_time,ExpressConfirm,ExpressConfirmTime,'0',NULL,
( CASE WHEN userid like 'HZ%' THEN '1'
ELSE '0' END ) as a2,
( CASE WHEN ExpressCompany = '速通物流' THEN 'sutong'
WHEN ExpressCompany = '中铁物流' THEN 'zhongtie'
WHEN ExpressCompany = '韵达快递' THEN 'yunda'
WHEN ExpressCompany = '协会配送' THEN 'cku'
ELSE '' END ) as a3,
ExpressNumber,PayConfirmTime,payType,
( CASE WHEN PayConfirm = '0' THEN '1'
ELSE '2' END ) as a4,
1,NOW(),1,NOW(),'',0
from dbo.shopOrderInfo;
#商城#订单详细
insert into shop_order_detail (id,goods_id,goods_name,goods_price,post_price,goods_num,shop_order_code,member_code,add_date,total_price,is_buy_gifts,return_num,return_remarks,create_by,create_date,update_by,update_date,remarks,del_flag)
select o.id,pid,g.spName,o.unitPrice,NULL,pcount,OrderID,userid,add_time,totalPrice,0,0,'',1,NOW(),1,NOW(),'',0 from dbo.shopOrderDetails o left JOIN dbo.shopGoods g on o.pid = g.id;


#财务#资金挂账
INSERT INTO payment_on_account (id, member_code, name, card_no, payment_way, money, balance, log, add_time, create_date, create_by, update_by, update_date, remarks, del_flag, is_change)
select id,user_id,user_name,user_bank_number,money_way,money_amount,money_amount2,operater,add_time,NOW(),1,1,NOW(),'',0,del_tag from dbo.cku_user_money_credit;

#财务#订单

INSERT INTO payment_order (
	id,
	member_code,
	member_name,
	order_code,
	total_price,
	payment_way,
	payment_state,
	payment_time,
	add_time,
	receiver,
	receive_del,
	receive_province,
	receive_city,
	receive_address,
	receive_post_code,
	project_id,
	project_name,
	locked,
	create_by,
	create_date,
	update_by,
	update_date,
	remarks,
	del_flag
)
SELECT
	distinct s.id,
	cku_id,
	username,
	OrderID,
	SP_prices,
	c.money_way,
(case when PayConfirm=0 THEN '1' ELSE '2' end ) payConfirm,
	PayConfirmTime,
	s.add_time,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
(case when ISNULL(s.locked) THEN '0' ELSE s.locked end ) locked,
	1,
	s.add_time,
	1,
	NOW(),
	'',
	0
FROM
	dbo.SP_OrderInfo s left join ( SELECT	* FROM dbo.cku_user_payList
		WHERE id IN ( SELECT min(id) FROM dbo.cku_user_payList group by running_number )
) c on s.OrderID = c.running_number;



#财务#订单详情
INSERT INTO payment_order_detail (id, order_code, member_code, member_name, charging_item_id, charging_item_name, num, price, total_price, add_time, project_id, project_name,
voucher_code, proccess_state, proccess_time, create_by, create_date, update_by, update_date, remarks, del_flag)
select s.id,OrderID,cku_id,'',pid,'',pcount,sp_price,total_price,s.add_time,x.id,s.xmid,pz_id,operate,operate_time,1,NOW(),1,NOW(),'',0 from dbo.SP_carts s LEFT JOIN dbo.order_xmid x on s.xmid = x.xmid;

#更新订单详情中的收费项名称
update payment_order_detail d,payment_charging_item i set d.charging_item_name = i.name where d.charging_item_id = i.id;

#财务#流水
INSERT INTO payment_record (
	id,
	member_code,
	business_id,
	charging_item_id,
	charging_item_num,
	payment_amount,
	price,
	payment_way,
	running_number,
	payment_state,
	out_trade_no,
	payment_time,
	payment_remarks,
	update_by_name,
	alipay_s,
	create_date,
	create_by,
	update_by,
	update_date,
	remarks,
	del_flag
) SELECT
	id,
	user_id,
	table_id,
	( CASE WHEN money_type = '0' THEN 'chongzhi'
ELSE money_type END ),
	1,
	money_amount,
	money_amount,
	money_way,
	running_number,
	pay_state,
	out_trade_no,
	money_date,
	payout_Remarks,
	operater,
	alipay_s,
	add_time,
	1,
	1,
	add_time,
	'',
	( CASE WHEN  ISNULL(del_tag) THEN '0'
ELSE del_tag END )
FROM
	dbo.cku_user_payList;
#财务#项目
#收费项由于有新增的内容，无法导入
#insert into payment_order_project (id,name,start_date,address,is_top,is_valid,add_time,create_by,create_date,update_by,update_date,remarks,del_flag)
#select id,xmid,xm_time,'',top_tag,del_tag,add_time,1,NOW(),1,NOW(),'',0 from dbo.order_xmid;

#sys_code
INSERT INTO sys_code (
	id,
	member_code,
	hzmember_code,
	kennel_code,
	pedigree_certified_code,
	birth_certificate_code,
	YEAR,
	match_code,
	champion_login_cn_code,
	champion_login_w_code,
	groomer_diploma_code,
	groomer_qualification_certificate_code,
	groom_activity_code,
	groom_activity_sign_code,
	handler_diploma_code,
	handler_qualification_certificate_code,
	handler_activity_code,
	handler_activity_sign_code,
	dna_code,
	breed_certified_code,
	train_diploma_code,
	elite_diploma_code
) SELECT
	'1' AS id,
	会员号 AS member_code,
	'1' AS hzmember_code,
	'1' AS kennel_code,
	DogBirthCertificate AS pedigree_certified_code,
	'1' AS birth_certificate_code,
	'2016' AS YEAR,
	'1' AS match_code,
	普通登录 AS champion_login_cn_code,
	超级登录 AS champion_login_w_code,
	'1' AS groomer_diploma_code,
	'1' AS groomer_qualification_certificate_code,
	'1' AS groom_activity_code,
	'1' AS groom_activity_sign_code,
	在册牵犬师 AS handler_diploma_code,
	'1' AS handler_qualification_certificate_code,
	'1' AS handler_activity_code,
	'1' AS handler_activity_sign_code,
	'1' AS dna_code,
	繁殖登记编号 AS breed_certified_code,
	'1' AS train_diploma_code,
	'1' AS elite_diploma_code
FROM 
	dbo.tab_Auto;
	
	
	insert into payment_order_project (id,name,start_date,address,is_top,is_valid,add_time,create_by,create_date,update_by,update_date,remarks,del_flag) 
select id,xmid,xm_time,'',top_tag,del_tag,add_time,1,NOW(),1,NOW(),'',0 from dbo.order_xmid;