导出guide_data.sh数据库脚本内的内容整理

/home/weihu1/guide_data.sh脚本内容如下：
###############################################################################

请选择以下项目：
1.导物流信息

mysql -h172.18.136.74 -ubss_reader -pzF4GfdKV9FfsQ7Jv -P3306 -Ne "use bss; select o.system_num as systemNum, o.status_code as statusCode,o.status,d.delivery_express as deliveryExpress,d.delivery_no as deliveryNo
from
order_base_info o, delivery_info_dt d
where o.id = d.order_base_id and o.create_time >= '2023-01-01' and o.create_time < '2023-02-01'
and d.delivery_no != '' and d.delivery_no is not null; " > /home/weihu/haoka_tuisongnew.txttest
;;
2.导管理员信息

mysql -A -h172.18.136.74 -ujiangsu -pHj+2vbgOEw0= -P3306 -Ne "use open_jiangsu;select loginId,loginName,mobilePhone,email,domain,createTime,groupName from open_admininfo" >/home/weihu1/data_export/guanliperson.txt
;;
3.导部分充值金额

mysql -h172.18.136.74 -ubss_reader -pzF4GfdKV9FfsQ7Jv -P3306 -Ne "use bss; select i.offer_name,i.status,i.create_time,i.offer_id,i.site_name,i.user_id,b.city,b.acc_nbr,i.history_balance/100,i.delivery_type from order_base_info i left join (select d.acc_nbr,d.cust_name,d.active_time,p.city,d.order_base_id from biz_info_dt d left join open_phone_region p on left(d.acc_nbr,7) = p.numberPrefix) b on i.id = b.order_base_id where i.create_time between '2023-01-01 00:00:00' and '2023-01-31 23:59:59' and i.delivery_type='SFPS' and i.status = '交易成功' group by i.system_num;" > /home/weihu1/haoka_202301new.txttest
;;
4.输出号卡订单的商品编码、用户ID、收获省份、对应号码详情

mysql -A -h172.18.136.74 -ubss_reader -pzF4GfdKV9FfsQ7Jv -P3306 -Ne "use bss;select b.acc_nbr,o.activate_time,o.good_code,o.user_id,d.delivery_provice from order_base_info o ,biz_info_dt b ,delivery_info_dt d where o.id = b.order_base_id and o.id = d.order_base_id and o.status_code = 5 and o.activate_time > '2023-01-01 00:00:00' and o.activate_time <= '2023-12-31 23:59:59' and b.acc_nbr in
('18351140207',
'15850746816') order by b.acc_nbr;" >/home/weihu1/qudao.txt

;;
5.输出用户ID和渠道名称的对应信息表

mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu;select a.userId , a.userName , b.companyName,a.channelId from open_user a , open_verification b where a.userId = b.userId ;" >/home/weihu1/company.name

;;
6.导出pany信息

mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu;select a.userId,a.userName,b.companyName,a.createTime,d.eaName
from open_user a, open_verification b,open_eaapply c ,open_eainfo d
where a.userId = b.userId and b.devType = 2 and a.userId=c.userId and c.eaId=d.eaId and a.status= 1;" >/home/weihu1/data_export/pany.txt
;;

7.导出pany2信息

mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu;select u.userName,a.id as appId,a.appName,d.eaName from open_appinfo a,open_app_billstrategy b ,open_user u,open_eainfo d where a.userId = u.userId and b.eaId in (52,56,60) and a.id = b.appId and b.eaId = d.eaId and a.isDelete = 0 ORDER BY a.id;" >/home/weihu1/data_export/pany2.txt
;;
8.导出南通地市充值信息

mysql -h172.18.136.74 -uhmm -P3306 -pzF4GfdKV9FfsQ7Jv -Ne "use hmm;select acc_nbr, system_num, card_system_num, promote_contact from hmm_operator_order where create_time >= '2023-08-01' and create_time < '2023-08-24' and city_id = 20 and order_type = 2 and status_code not in ('9') and delete_flag = 1;" >/home/weihu1/nantong.txt
;;
9.导出代理商管理表信息

echo "得到MySQL终端里面去导出，使用limit固定行语句"
mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu;select a.userId,a.channelId,a.userName,b.companyName,cast(AES_DECRYPT(from_base64(realName),'cmccheli') as char) as realName,a.createTime,a.status from open_user a, open_verification b where a.userId = b.userId and b.devType = 2 ;" > /home/weihu1/kaifashang.txt
;;

12.把应用管理拉个清单出来  ，包括ip地址和绑定能力，开发者的门户ip地址也导出

mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu;select oa.appName as '应用名称' ,oi.ipAddress as '绑定IP地址',oe.eaName as '绑定能力' ,ou.userId as '开发者账号id',ou.userName as '开发者账号',
(case when oa.status = 2 then '审核通过'
when oa.status = 3 then '审核不通过'
when oa.status = 4 then '待审核'
when oa.status = 5 then '冻结'
when oa.status = 6 then '待审核' end) as '应用状态',
ov.realName as '联系人',l.devportal_ip as '门户IP地址'
from open_appinfo oa ,open_ipstrategy oi ,open_app_billstrategy oab ,open_user ou,open_user_ip_white_list l,open_eainfo oe,open_verification ov
where oa.id = oi.appId and oa.id = oab.appId and oab.eaId =oe.eaId and oa.userId = ou.userId and ou.userId =l.user_id and ou.userId =ov.userId ;" >/home/weihu1/data_export/app.txt

第二种写法：：：

select oa.appName as '应用名称' ,oi.ipAddress as '绑定IP地址',oe.eaName as '绑定能力' ,ou.userId as '开发者账号id',ou.userName as '开发者账号',
(case when oa.status = 2 then '审核通过'
when oa.status = 3 then '审核不通过'
when oa.status = 4 then '待审核'
when oa.status = 5 then '冻结'
when oa.status = 6 then '待审核' end) as '应用状态',

################对联系人姓名进行解密##############################
cast(AES_DECRYPT(from_base64(ov.realName),'cmccheli') as char) as '联系人',l.devportal_ip as '门户IP地址'
from open_appinfo oa ,open_ipstrategy oi ,open_app_billstrategy oab ,open_user ou,open_user_ip_white_list l,open_eainfo oe,open_verification ov
where oa.id = oi.appId and oa.id = oab.appId and oab.eaId =oe.eaId and oa.userId = ou.userId and ou.userId =l.user_id and ou.userId =ov.userId ;

EOF


10)
mysql -h172.18.136.74 -ubss_reader -pzF4GfdKV9FfsQ7Jv -P3306 -Ne "use bss;select b.acc_nbr,d.delivery_provice,d.delivery_city,d.delivery_country,AES_DECRYPT(from_base64(d.delivery_addr),'cmccheli') from order_base_info o ,biz_info_dt b ,delivery_info_dt d where o.id = b.order_base_id and o.id = d.order_base_id and o.status_code = 5 and o.activate_time >'2023-03'and b.acc_nbr in ('13914655184',
'15295171945',
'18360462235',
'18261832286',
'19816019165');" >/home/weihu1/new.txt
;;
11)
mysql -h172.18.136.74 -ujiangsu -P3306 -pHj+2vbgOEw0= -Ne "use open_jiangsu; select ou.userId,oa.appName ,oi.ipAddress from open_user ou ,open_appinfo oa ,open_ipstrategy oi where oa.id = oi.appId and ou.userId =oa.userId and oi.ipAddress is not null and ou.userId in (70, 276, 496, 531, 661, 781, 1246, 1366, 1426, 1541, 1566, 1621, 1626, 1636, 1646, 1676, 1711, 1681, 1776, 1771, 1871, 1881, 1886, 1891, 1906, 1931, 1951, 1941, 2041, 2046, 2101, 2096, 2091, 2157, 2197, 2266, 2271, 2276, 2301, 2311, 2326, 2341, 2351, 2366, 2371, 2376, 2401, 2391, 2421, 2446, 2461, 2486, 2476, 2491, 2511, 2501, 2521, 2546, 2561, 2566, 2571, 2611, 2626, 2631, 2636, 2666, 2676, 2701, 2736, 2741, 2746, 2751, 2761, 2766, 2791, 2796, 2826, 2831, 511);" >/home/weihu1/data_export/kafadata.txt
;;

###############################################################################