一.迁移站点：
（1） 看该站点下该站点物业是否有分润信息（ 更改物业）
SELECT
t2. NAME,
t4.*
FROM
chargerlink_charger.t_site t2
LEFT JOIN chargerlink_card_manager.t_site_share_rule t3 ON t2.id = t3.site_id
LEFT JOIN chargerlink_card_manager.t_site_share_proportion t4 on t3.id = t4.rule_id
WHERE
t4.merchant_id = ?
如果有分润信息，最好不前迁移，对分润有影响
------------------------------------------
2.更新站点物业和代理商（更新 t_site（站点表） 表中merchant_id （物业id） 和operate_id（代理商id）字段）
update chargerlink_charger.t_site set merchant_id='40583',operate_id='33989' where id=?;
------------------------------------------
3.更新盒子列表中的business_id（代理商id）
update chargerlink_charger.bs_box set business_id='33989' where station_code=?
-----------------------------------------------------------------------------------------------
二.迁移t_card 表中的代理商(修改t_card 中的comm_id（代理商id）如果修改物业（merchant_id）)
如果修改完代理商的没有卡对应的站点，还需要修改卡片的站点信息
修改t_card_site中的site_id
-----------------------------------------------------------------------------------------------
三。物业更改代理商
查询t_commercial_merchant_relevance 表（代理商与物业关系表）
UPDATE chargerlink_card_manager.t_merchant set pid='39891' where phone='13339080833'
---------------------------------------------------------------------------------------------------------------------------------------
三.
--------------------------删除离线订单------------------------------------------
UPDATE chargerlink_charger.t_charger_order SET `status` = '800',remark = '手动调整',pay_status = '0' WHERE order_id = '985771843578662912';
------------------------------------------------------
根据桩号查询订单
select * from chargerlink_charger.t_charger_order where box_out_factory_code='5580100066' and status not in('800' ,'2000')
-----------------------------------------------------------------------------
四.
（1）给虚拟账户充值
UPDATE chargerlink_card_manager.t_balance set balance = balance+500,free_gold=free_gold+2200 where user_id=(select id from chargerlink_card_manager.t_user where phone='?');
（2）给卡充值
UPDATE chargerlink_card_manager.t_card set card_denomination=card_denomination+0,card_gift_amount=card_gift_amount+5560 where card_no = '? ';
-------------------------------------------------------------------------------------------------------------
五.旧卡换新卡
（其中新卡中的set 内容全部为原旧卡信息内容）
update chargerlink_card_manager.t_card set card_status='10001',card_activation_date='2018-06-07 09:12:40',mobile='13876703992',user_id='75266981',card_gift_amount=card_gift_amount+4000 ,take_effect_time
='2018-06-01 00:00:00',past_due_time='2018-07-01 00:00:00',charger_times=38,last_modifier='33843',last_modifier_type=1 where card_no='新卡';
update chargerlink_card_manager.t_card set card_status='10000',card_activation_date=null,mobile=null,user_id=null,card_gift_amount=card_gift_amount-4000 ,take_effect_time
=null,past_due_time=null,charger_times=0,last_modifier=null,last_modifier_type=null where card_no='旧卡';
----------------------------------------------------------------------------------------------------------------------------------------
六.卡注销
UPDATE `chargerlink_card_manager`.`t_card` SET `card_type`='1', `card_chip_no`=NULL, `card_channel`='3', `card_denomination`='0.00', `card_activation_code`=NULL, `card_status`='10000', `card_activation_date`=NULL, `card_create_date`='2017-10-10 11:38:59', `card_update_date`='2017-10-20 14:49:43', `comm_id`='3565', `station_id`=NULL, `station_name`=NULL, `mobile`=NULL, `user_id`=NULL, `status`='0', `type`=NULL, `is_package`=NULL, `merchant_id`=NULL, `card_gift_amount`='0.00', `take_effect_time`=NULL, `past_due_time`=NULL, `charger_times`='0', `remark`='注销', `last_modifier`='4931', `last_modifier_type`='1' WHERE (`card_no`='?');
-----------------------------------------------------------------------------------------------------------------------------------------
七。设置站点按代理商分润
update chargerlink_charger.t_site set balance_method=1 where id ='站点id';
---------------------------------------------------------------------------------
八.查询站点下的全部卡片的本金
select sum(card_denomination/100) from chargerlink_card_manager.t_card where card_no in (
select card_no from chargerlink_card_manager.t_card_site where site_id='?'
)
----------------------------------------------------------------------------------------
九。卡更改手机号
//user_id 根据chargerlink_card_manager.t_user 表中的phone 查询
update chargerlink_card_manager.t_card set mobile='?',user_id='?' where card_no='?'
----------------------------------------------------
十。修改续充时长
update chargerlink_charger.t_bill_template set continue_time='10' where site_id='990139652878290945';
----------------------------------------------------------------------------------
十一。查询分润信息
SELECT
t4.id,
t2.id as '站点id',
t2. NAME as '站点名称',
t3.billing_method,
(case when t3.billing_method='0' then '代理商单独结算电费' else '扣除电费成本后分账' end ) as '计费方式',
(case when t4.type=100 then '充电网' when t4.type=101 then '上级代理商' when t4.type=102 then '当前商户' when t4.type=103 then '物业' else '代理商' end ) as '类别',
t5.merchant as '物业',
t6.comm_name as '代理商',
t3.`describe` as '描述',
-- t3.electricity_proportion as '电费结算比例',
-- t3.revenue_proportion as '收益分成比例',
t4.payee_proportion as '收款比例',
t4. `electricity_proportion` as '电费结算比例' ,
t4.`revenue_proportion` as '收益分成比例'
FROM
chargerlink_charger.t_site t2
JOIN chargerlink_card_manager.t_site_share_rule t3 ON t2.id = t3.site_id
JOIN chargerlink_card_manager.t_site_share_proportion t4 on t3.id = t4.rule_id
left JOIN chargerlink_card_manager.t_merchant t5 on t5.merchant_id= t4.merchant_id
LEFT JOIN chargerlink_card_manager.t_commercial t6 on t6.id=t4.comm_id
where t4.type ='102' and t2.id in ？
--------------------------------------------------------------------------------------------------------
物业更改代理商
修改t_merchant 的pid (pid 为物业代理商之间的关联表的merchant_id)
------------------------------------------------------------------------------------------------------
站点更改代理商
1.修改t_site 的 operate_id (代理商id) ,2.修改该代理商下的物业（t_merchant）表中的pid (pid 为t_commercial_merchant_relevance 的merchant_id),3,修改分润表的comm_id, 4.修改t_card 表中的merchant_id（t_commercial_merchant_relevance 的merchant_id） ，修改 t_merchant_ratio（根据merchant_id 查改 where merchant_id=？ 因为commercial_id 对应多个merchant_id）
---------------------------------------------------
修改续充有效时长
t_continuous_filling_record（表）
------------------------------------------------
--------------------------------------更改物业权限--------------------------
update chargerlink_card_manager.t_merchant a,
chargerlink_card_manager.t_merchant b
set a.extend_permission=1 where
a.merchant_id=b.merchant_id and
b.pid= '40767' and b.extend_permission not in (3,4,9);
-----------------------------------------------------------------------
1.t_commercial 中的 pid 改成上级代理商的 id(上级代理商的id 也在chargerlink_card_manager.t_commercial 表中)
2.
SELECT
t4.id,
t2.id as '站点id',
t2. NAME as '站点名称',
t3.billing_method,
(case when t3.billing_method='0' then '代理商单独结算电费' else '扣除电费成本后分账' end ) as '计费方式',
(case when t4.type=100 then '充电网' when t4.type=101 then '上级代理商' when t4.type=102 then '当前商户' when t4.type=103 then '物业' else '代理商' end ) as '类别',
t5.merchant as '物业',
t6.comm_name as '代理商',
t3.`describe` as '描述',
-- t3.electricity_proportion as '电费结算比例',
-- t3.revenue_proportion as '收益分成比例',
t4.payee_proportion as '收款比例',
t4. `electricity_proportion` as '电费结算比例' ,
t4.`revenue_proportion` as '收益分成比例'
FROM
chargerlink_charger.t_site t2
JOIN chargerlink_card_manager.t_site_share_rule t3 ON t2.id = t3.site_id
JOIN chargerlink_card_manager.t_site_share_proportion t4 on t3.id = t4.rule_id
left JOIN chargerlink_card_manager.t_merchant t5 on t5.merchant_id= t4.merchant_id
LEFT JOIN chargerlink_card_manager.t_commercial t6 on t6.id=t4.comm_id
where and t2.id in (站点id)
============================(站点id 根据opert_id 获取)
网友评论