通过shell查询数据信息并保存在excel中，并记录日志

#!/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#####"