#!/bin/bash
#通过shell查询数据信息并保存在excel中,并记录日志
#Data:2018-06-14
#Name:Zhang
#数据库连接地址
DBServer='ip'
#数据库用户名
DBUserName='XXXX'
#数据库密码
DBPasswd='XXXX'
###########################################################
#选择数据库
Use_Cmd="use u"
#查询数据库信息Sql语
Select_Cmd="SELECT
farmer_deal_record.order_id AS 订单ID,
order_info.create_time AS 时间,
vendor.name AS 中心,
vendor.real_name AS 中心姓名,
vendor.phone AS 中心联系电话,
operator.name AS 姓名,
operator.phone AS 联系电话,
farmer_deal_record.farmer_id AS 农,
farmer.name AS 姓名,
farmer.phone AS 电话,
order_info.contact_name AS 联系人,
order_info.contact_phone AS 系人电话,
order_info.crop AS 农作物,
order_info.fee_real AS 实费用,
FROM
farmer_deal_record,
order_info,
vendor,
farmer,
operator_order,
operator
WHERE
NOT deal_type = 102
AND farmer_deal_record.order_id = order_info.id
AND order_info.vendor_id = vendor.id
AND farmer_deal_record.farmer_id = farmer.id
AND order_info.id = operator_order.order_id
AND operator_order.operator_id = operator.id
AND farmer_deal_record.deal_time > '2018-05-31 17:36:21'
ORDER BY farmer_deal_record.deal_time,vendor.name DESC;"
###########################################################
#后息保存路径
Payment_DataDir='/opt/select_back'
#日志保存路径
LogDir=/opt/select_back/logs
#数据导出时间
backtime=`date +%Y%m%d%H%M`
#保存文件名称
DataName="支付订单信息"
###########################################################
#邮件收件人
#邮件主题
#Email_Subject="单详细信息$backtime"
##########################################################
MKDIR='/bin/mkdir'
#########################################################
echo "##################判断备份路径 #############################"
test ! -d $Payment_DataDir && $MKDIR -p $Payment_DataDir
test ! -w $Payment_DataDir && echo "Error: $Payment_DataDir is un-writeable." && exit 0
test ! -d $LogDir && $MKDIR -p $LogDir
test ! -w $LogDir && echo "Error: $LogDir is un-writeable." && exit 0
echo "######################备份开始 #############################"
echo "" >> $LogDir/$DataName.log
echo -e "\033[44;32m-------------------------分割线-----------------------\033[0m \n" >> $LogDir/$DataName.log
echo "导出时间为$backtime,导出$文件开始" >> $LogDir/$DataName.log
/usr/local/mysql/bin/mysql -u$DBUserName -h$DBServer -p$DBPasswd -e "$Use_Cmd;$Select_Cmd" > $Payment_DataDir/$DataName-$backtime.xls
#sed -i 's/^/`&/g' $Payment_DataDir/$DataName-$backtime.xls
#转换编码,因Linux的编码是utf-8的编码需要转换成windows的gbk编码
iconv -f "utf-8" -t "gbk" $Payment_DataDir/$DataName-$backtime.xls > $Payment_DataDir/Orders.xls
#因数据列表中第一列的数据位数超过了18位,excel中使用了科学计数法格式,需要把格式改成文本格式故在第一列前面加上了“·”
sed -i 's/^/`&/g' $Payment_DataDir/Orders.xls
if [ "$?" == 0 ];then
#把导出的结果使用邮件发送到指定邮箱
/opt/py3/bin/python3 /opt/select_back/select_mail.py
echo "导出时间为$backtime,导出$DataName文件结束!!!" >> $LogDir/$DataName.log
echo "Mysql-$DataName数据导出成功!!!" >> $LogDir/$DataName.log
else
echo "导出时间为$backtime,导出$DataName文件结束!!!" >> $LogDir/$DataName.log
echo "Mysql-$DataName数据导失败!!!" >> $LogDir/$DataName.log
fi
echo "######################导出数据结束,时间为:$backtime#####"
领取专属 10元无门槛券
私享最新 技术干货