修改订单的收货时间（MySQL）脚本

需求

下单时间为21：00-23:59，并且收货时间为第二天的订单，将这些订单的收货时间改为第三天；

#!/bin/bash
###time：2016-04-26
###This script is change "specify_receive_time"
###作用：修改下单时间为21:00:00-23:59:59并且收货时间为第二天的订单，将收货时间改为第三天

TIME="`date +%F`"
NEXT_DAY="`date -d next-day +%F`"
LAST_DAY="`date -d last-day +%F`"
MYSQL_HOST="ip地址"
MYSQL_DB="miracle"
MYSQL_CMD="/server/mysql5.5.39/bin/mysql"
MYSQL_USER="chtime"
MYSQL_PWD="密码"
###SELECT_MY="use miracle;"
###根据下单时间段：21:00:00-23:59:59(前一天),查询出收货时间
###SELECT_RETIME="SELECT specify_receive_time,order_sn FROM orders WHERE create_time>('"$LAST_DAY" 21:00:00') and create_time<('"$LAST_DAY" 23:59:59') and specify_receive_time>('"$TIME" 00:00:00') and specify_receive_time<('"$TIME" 23:59:59');"

ORDER_DIR="/data/order"
###ORDER_FILE="$ORDER_DIR/change_receive_time-`date +%F`.txt"
ORDER_FILE="$ORDER_DIR/change_receive_time-"$TIME".txt"
###生成log file
LOG_FILE="$ORDER_DIR/change_receive_time-"$TIME".log"

exec 1>> "$LOG_FILE"
exec 2>> "$LOG_FILE"



function receive_time(){
###取出指定时间内对应的收货时间和订单号
    [ ! -d "$ORDER_DIR" ] && mkdir "$ORDER_DIR" -p
    $MYSQL_CMD -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PWD $MYSQL_DB -e "SELECT specify_receive_time,order_sn FROM orders WHERE create_time>('"$LAST_DAY" 21:00:00') and create_time<('"$LAST_DAY" 23:59:59') and specify_receive_time>('"$TIME" 00:00:00') and specify_receive_time<('"$TIME" 23:59:59');"|grep -v "spe" > "$ORDER_DIR"/change_receive_time-"$TIME".txt
	if [ `ls -l "$ORDER_DIR"/change_receive_time-"$TIME".txt|wc -l` -eq 1 ]
	then
	    echo "collect specify_receive_time and order_sn success"
	else
	    exit 1
	fi
	
}


function change_receive_time(){
###将取出的收货时间修改为第二天
    ORDER_NUM="`wc -l $ORDER_FILE|awk '{print $1}'`"
	ORDER_FILE="$ORDER_DIR/change_receive_time-"$TIME".txt"
	if [ `wc -l $ORDER_FILE|awk '{print $1}'` -eq 0 ]
	then
	    echo "Do not have receive_time data"
		exit 2
	else 
	    echo "collect receive_time successful, Beginning change receive_time"
	fi
    for n in `seq 1 $ORDER_NUM`
    do
           RECEIVE_TIME="`cat $ORDER_FILE|awk '{print $2}'|sed -n "$n"p`"
           ORDER_SNN="`cat $ORDER_FILE|awk '{print $3}'|sed -n "$n"p`"
           $MYSQL_CMD -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PWD $MYSQL_DB -e "UPDATE orders SET specify_receive_time = '"$NEXT_DAY" "$RECEIVE_TIME"' WHERE  order_sn = '"$ORDER_SNN"';"
    done
	       if [ $? -eq 0 ]
		   then 
		       echo "change specify_receive_time success"
		   else
		       echo "change specify_receive_time failed"
		   fi
	
}

function main(){
   receive_time
   change_receive_time
}
main