维护手册

show databases;
use lawyerapp;
desc apptool;
select * from apptool where toolType='T5' and p1='江苏';

平均工资
#select p4,p7 from apptool where toolType='T5' and p1='江苏';
#update apptool set p4=60061,p7=60061 where toolType='T5' and p1='江苏';
#select info where toolType='T5' and p1='江苏';
#update info='' where toolType='T5' and p1='江苏';


select info from apptool toolType where toolType='T5' and p1='江苏';


#update apptool set p4=78267,p7=78267 where toolType='T5' and p1='江苏';

select p4,p7 from apptool where toolType='T5' and p1='山西';


2、关联公司主账号
#update ssdb_company set companyGroup_id='zhongguoyongan' where loginName in('福建永安','永安上海','东莞永安','广州永安');
#select companyGroup_id from ssdb_company where loginName in('福建永安','永安上海','东莞永安','广州永安')

3、可以帮忙统一设置下这些账号的出函时长为4个小时吗，即下图的时长
湖北太平洋（成都温江区）、湖北太平洋（山东）、湖北太平洋（宁夏）、湖北太平洋（厦门）、湖北太平洋（东莞）、湖北太平洋（天津）、湖北太平洋（吉林）、湖北太平洋（青岛）、湖北太平洋（温州）、湖北太平洋（河北）、北京太平洋、湖北太平洋（山西）、湖北太平洋（黑龙江）、湖北太保（湖北）、湖北太平洋、湖北太平洋（海南）、湖北太平洋（宁波）、湖北太平洋（大连）、湖北太平洋（上海）、湖北太平洋（广州）
可以帮忙统一设置下这些账号的出函时长为4个小时吗，即下图的时长

############
维护图标
select * from ssdb_dwlabel;
insert into `ssdb_dwlabel`(`id`,`isValid`,`labelContent`,`labelIcon`,`labelType`,`sortNum`,`quality`) values
('37','1','付款后3小时出函','https://duowen-pub.oss-cn-shenzhen.aliyuncs.com/ssdb/ssdbIcon/icon_payment_send_letter.png','0','37','工作日15点前付款，3小时后出函；15点后付款，次日中午12点前出函');

insert into `ssdb_dwlabel`(`id`,`isValid`,`labelContent`,`labelIcon`,`labelType`,`sortNum`,`quality`) values
('38','1','付款后4小时出函','https://duowen-pub.oss-cn-shenzhen.aliyuncs.com/ssdb/ssdbIcon/icon_payment_send_letter.png','0','38','工作日14点前付款，4小时后出函；14点后付款，次日中午12点前出函');

insert into `ssdb_dwlabel`(`id`,`isValid`,`labelContent`,`labelIcon`,`labelType`,`sortNum`,`quality`) values
('39','1','付款后1天内出函','https://duowen-pub.oss-cn-shenzhen.aliyuncs.com/ssdb/ssdbIcon/icon_payment_send_letter.png','0','39','工作日中午12点前付款，次日中午12点出函；12点后付款，次日17点出函');

insert into `ssdb_dwlabel`(`id`,`isValid`,`labelContent`,`labelIcon`,`labelType`,`sortNum`,`quality`) values
('40','1','专人全程帮您跟进','https://duowen-pub.oss-cn-shenzhen.aliyuncs.com/ssdb/ssdbIcon/icon_customer_service.png','0','40','提交资料后，将有专人全程跟进出函情况，并微信反馈给您');

#update ssdb_dwlabel set sortNum=2 where id in(37,38,39);

insert into `ssdb_dwlabel`(`id`,`isValid`,`labelContent`,`labelIcon`,`labelType`,`sortNum`,`quality`) values ('41','1','4小时审核','https://duowen-pub.oss-cn-shenzhen.aliyuncs.com/ssdb/ssdbIcon/icon_zhuan.png','0','1','提交资料后，保险公司在4小时内出审核结果');


不退保证金的律师：
update lawyer set depositStatus=0,stateJoin=1 where phone='13009210208';
update lawyer set depositStatus=-1,stateJoin=0,depositBackDate=null where phone='13580946712';


# 18122937164
delete from adv_relation_statistics where userId in (select id from adv_user where phone='18122937164');
delete from adv_voucher where userId in (select id from adv_user where phone='18122937164');

yly@170165
#常用的统计SQL：
//律师姓名	律师手机号	执业证号	省代码	城市代码	详细地址	加盟状态
select real_name as 律师姓名,phone as 律师手机号,certNum as 执业证号,province_id as 省代码,city_id as 城市代码,address as 详细地址,
case stateJoin
       when '1' then '已加盟' else '未加盟' end as 加盟状态,
case status
       when '0' then '注册' when '1' then '已完善' when '2' then '认证审核中' when '3' then '认证失败' when '4' then '认证成功'  else '未知' end as 认证状态
from lawyer;

select count(1) as 查档_数量,real_name as 律师姓名,phone as 律师手机号 from cd_order left join lawyer on lawyerA_id=id group by phone,real_name order by 查档_数量 desc;
select count(1) as 查档_接单数量,real_name as 律师姓名,phone as 律师手机号 from cd_order left join lawyer on lawyerB_id=id group by phone,real_name order by 查档_接单数量 desc;
select count(1) as 案件合作_数量,real_name as 律师姓名,phone as 律师手机号 from ajhz_order left join lawyer on lawyerA_id=id group by phone,real_name order by 案件合作_数量 desc;
select count(1) as 案件合作_接单数量,real_name as 律师姓名,phone as 律师手机号 from ajhz_order left join lawyer on lawyerB_id=id group by phone,real_name order by 案件合作_接单数量 desc;
select count(1) as 完成订单数量,real_name as 律师姓名,phone as 律师手机号 from ssdb_order left join lawyer on lawyer_id=id where orderState in(31,32,40,41) group by real_name,phone order by 完成订单数量 desc;

######增加消息提醒#######
INSERT INTO message_notification (message_id,create_date,json_context,msgType,read_state,receiver_id,type,update_date)
SELECT UUID(),now(), CONCAT('{"title":"新消息提醒","msg":"客服电话暂时受限，无法拨打；有疑问请加客服微信：duowen99，客服电话会尽快恢复，感谢您的支持！","extraFieldMap":{"messageId":"',id,'","type":"20"}}'),'XTTZ',0,id,20,now()
FROM lawyer;

# 新增红包提醒
INSERT INTO message_notification (message_id,create_date,json_context,msgType,read_state,receiver_id,type,update_date)
SELECT UUID(),now(), CONCAT('{"title":"国庆红包送不停","msg":"送你一个100元红包，已经放入我的钱包中，等你来提现哦！","extraFieldMap":{"messageId":"',id,'","type":"22"}}'),'XTTZ',0,id,20,now()
FROM lawyer;

#审核失败的，加黑名单：
select isBlackList from adv_lawyer  where id in(
	select id from lawyer where status not in(4)
) and isBlackList !=1;
update adv_lawyer set isBlackList=1 where id in(
	select id from lawyer where status not in(4)
) and isBlackList !=1;

update ads_lawyer set isBlackList=1 where id in(
	select id from lawyer where status not in(4)
) and isBlackList !=1;


#查看保证金：
select * from lawyer where phone='13009210208'
select * from ajhz_payrecord where lawyer_id in(select id from lawyer where phone='13009210208');


#重新解冻脚本
select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1

update ssdb_order set pointState=0 WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1


select lawyer_id,count(1) from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1
group by lawyer_id;

#解冻记录，需要删除此表
select a.* from ssdb_pointjdlog a where a.lawyer_id in (
	select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1
group by lawyer_id
) and left(createDate,10)='2018-08-01'

delete from ssdb_pointjdlog  where lawyer_id in (
	select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1
group by lawyer_id
) and left(createDate,10)='2018-08-01'


delete from ssdb_pointjdlog  where lawyer_id in (
	select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1
group by lawyer_id
) and left(createDate,10)='2018-08-01'


select pointsSum-monthPoints,a.id from lawyer a left join ssdb_pointjdlog b on a.id=b.lawyer_id where a.id in(
	select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1 group by lawyer_id
) and left(createDate,10)='2018-08-01';

select * from lawyer a left join ssdb_pointjdlog b on a.id=b.lawyer_id where a.id in(
	select lawyer_id from ssdb_order WHERE payedDate>='2018-07-01 00:00:00' AND payedDate<='2018-07-31 23:59:59' AND orderState='41' AND pointState=1 group by lawyer_id
) and left(createDate,10)='2018-08-01';

#加一个红包

select id from lawyer where phone in(15845666885,18156914982,15155115612);
| 8af40d17535977d601537e4e9f9509a2 |
| 8af40d1752ee2ca5015310b877c90354 |
| 8af40d17535977d601536f739a5d0600 |
+----------------------------------+
insert into `ssdb_redpacket`(`id`,`amount`,`createDate`,`lawyerId`,`outDate`,`packetId`,`remark`,`type`,`updateDate`,`useDate`,`withdraw`) values
('201808010000001','100',now(),'8af40d17535977d601537e4e9f9509a2',date_add(now(), interval 2 month),'2','申请并完成诉保订单即可提现','0',null,null,'0');
insert into `ssdb_redpacket`(`id`,`amount`,`createDate`,`lawyerId`,`outDate`,`packetId`,`remark`,`type`,`updateDate`,`useDate`,`withdraw`) values
('201808010000002','100',now(),'8af40d1752ee2ca5015310b877c90354',date_add(now(), interval 2 month),'2','申请并完成诉保订单即可提现','0',null,null,'0');
insert into `ssdb_redpacket`(`id`,`amount`,`createDate`,`lawyerId`,`outDate`,`packetId`,`remark`,`type`,`updateDate`,`useDate`,`withdraw`) values
('201808010000003','100',now(),'8af40d17535977d601536f739a5d0600',date_add(now(), interval 2 month),'2','申请并完成诉保订单即可提现','0',null,null,'0');

select b.username,b.phone as 用户手机号,orderId as 投诉订单,complaintText as 投诉内容,a.createDate as 投诉时间 from adv_complaint a left join adv_user b on b.id=a.userId;

#git增加仓库：
git push -u origin master
这是由于git默认拒绝了push操作，需要进行设置，修改.git/config添加如下代码：
    [receive]
    denyCurrentBranch = ignore

2018-08-28-17:32以后的

# 2018-09-19生产部署

INSERT INTO ads_lawyer (id,address,areaCode,averageLevel,casenum,certYear,cityCode,createDate,deviceType,idCard,isBlackList,isLive,logo,name,phone,provinceCode,realname,serviceTimes,updateDate)
SELECT c.id,c.address,c.areaCode,c.averageLevel,0,9999,c.cityCode,c.createDate,c.deviceType,c.idCard,c.isBlackList,c.isLive,c.logo,c.name,c.phone,c.provinceCode,c.realname,c.serviceTimes,c.updateDate
FROM adv_lawyer c where not exists(select 1 from ads_lawyer d where c.id=d.id);

update ads_lawyer a left join  lawyer b on a.id=b.id set a.certYear=convert(substring(b.certNum,6, 4),signed INTEGER) where  substring(b.certNum,6, 4)!='' and cast(substring(b.certNum,6, 4) as signed INTEGER) >= 1949 and cast(substring(b.certNum,6, 4) as signed INTEGER)+0<=2018