#!/bin/bash
ip=$1
phone_no=$2
bill_num=3
bill_num_a=0
PORT="3306"
USERNAME="qa"
PASSWORD="qatest"
mysqld="-h${ip} -P${PORT} -u${USERNAME} -p${PASSWORD}"
date_now=`date "+%Y-%m-%d %H:%M:%S"`

 function baitiao_bill()
  {
  		len=`echo $phone_no|awk '{print length($0)}'`
  		if [ $len -ne 11 ] ; then
  				echo "请输入11位手机号 ！"
  		else 



                       #生成订单
                       
                      #白条订单表增加数据

                       select_user_id="SELECT id  from user where user.phone_no=${phone_no}"
                       xyqb_user_id=`mysql -N $mysqld xyqb_user -e "set names utf8;${select_user_id}"`
                     #  echo $select_user_id

                       order_no='xyqb'${phone_no}
                       order="INSERT INTO baitiao_order( "user_id", "merchant_id", "order_no", "order_name", "order_amount", "repay_term", "status", "created_at", "updated_at", "baitiao_order_type") VALUES (${xyqb_user_id}, 7, '${order_no}', '信用钱包订单', 2499.000000, 1,1, '$date_now', '$date_now', 0)"
                       mysql  $mysqld xyqb -e "set names utf8;${order}"
                      # echo " order=$order"


                       select_order_id="SELECT id  from baitiao_order where user_id=${xyqb_user_id}"
                       baitiao_order_id=`mysql -N $mysqld xyqb -e "set names utf8;${select_order_id}"`
                       #echo "select_order_id=$select_order_id"
                      # echo "baitiao_order_id=$baitiao_order_id"
                       insert_fund="INSERT INTO baitiao_fund_order ( "order_id", "status", "rate", "refund_status", "start_interest_date", "fund_id", "finance_record_id", "created_at", "updated_at") VALUES ( $baitiao_order_id, 0, 0.060200, 0, NULL, '3', NULL, '$date_now', '$date_now')"
                      # echo "insert_fund=$insert_fund"
                        mysql  $mysqld xyqb -e "set names utf8;${insert_fund}"
                       #白条还款计划表增加数据
                       repayment_plan1="INSERT INTO baitiao_bill_repayment_plan ("bill_id", "user_id", "user_phone", "order_id", "order_no", "order_name", "order_time", "merchant_id", "order_amount", "deadline", "term_no", "contract_term", "rate", "principal", "interest", "penalty_interest", "red_packet_amount", "collection_reduction", "repay_amount", "repay_amount_compute_time", "lock_time", "repaid_at", "repayment_received_at", "repayment_status", "pay_center_order_no", "enable", "created_at", "updated_at") VALUES (NULL, ${xyqb_user_id}, '${phone_no}', ${baitiao_order_id},'信用钱包订单', '手机', '$date_now', 7, 2499.000000, '2018-01-20 23:59:59', 1, 3, 0.030000, 833.000000, 74.970000, 0.000000, 0.000000, 0.000000, 907.970000, '$date_now', NULL, '$date_now', NULL, '0', NULL, 1, '$date_now', '$date_now')"
                       mysql  $mysqld xyqb -e "set names utf8;${repayment_plan1}"
                     # echo "plan1=$repayment_plan1"
                                                           

                       repayment_plan2="INSERT INTO baitiao_bill_repayment_plan ("bill_id", "user_id", "user_phone", "order_id", "order_no", "order_name", "order_time", "merchant_id", "order_amount", "deadline", "term_no", "contract_term", "rate", "principal", "interest", "penalty_interest", "red_packet_amount", "collection_reduction", "repay_amount", "repay_amount_compute_time", "lock_time", "repaid_at", "repayment_received_at", "repayment_status", "pay_center_order_no", "enable", "created_at", "updated_at") VALUES (NULL, ${xyqb_user_id}, '${phone_no}', ${baitiao_order_id}, '信用钱包订单', '手机','$date_now', 7, 2499.000000, '2018-02-20 23:59:59', 2, 3, 0.030000, 833.000000, 74.970000, 0.000000, 0.000000, 0.000000, 907.970000, '$date_now', NULL, '$date_now',NULL, '0', NULL, 1,'$date_now', '$date_now')"

                       mysql  $mysqld xyqb -e "set names utf8;${repayment_plan2}"
                      #echo "plan2=$repayment_plan2"
                       repayment_plan3="INSERT INTO baitiao_bill_repayment_plan ("bill_id", "user_id", "user_phone", "order_id", "order_no", "order_name", "order_time", "merchant_id", "order_amount", "deadline", "term_no", "contract_term", "rate", "principal", "interest", "penalty_interest", "red_packet_amount", "collection_reduction", "repay_amount", "repay_amount_compute_time", "lock_time", "repaid_at", "repayment_received_at", "repayment_status", "pay_center_order_no", "enable", "created_at", "updated_at") VALUES (NULL, ${xyqb_user_id}, ${phone_no}, ${baitiao_order_id}, '信用钱包订单', '手机', '$date_now', 7, '2499.000000', '2018-03-20 23:59:59', '3', '3', '0.030000', '833.000000', '74.970000', '0.000000', '0.000000', '0.000000', '907.970000', '$date_now', NULL,'$date_now', NULL, '0', NULL, '1', '$date_now','$date_now')"
                       mysql  $mysqld xyqb -e "set names utf8;${repayment_plan3}"

                       #echo "plan3=$repayment_plan3"
                       #白条额度表变化表增加数据

                       consume_seq1='ABCD201708051511458'${phone_no}

                       consume="INSERT INTO baitiao_consume_history("user_id", "order_id", "order_amount", "remaining_amount", "consume_type", "consume_seq", "created_at", "updated_at") VALUES(${xyqb_user_id}, ${baitiao_order_id}, '2499.00', '13501.00', '1', '${consume_seq1}', '$date_now','$date_now')"
           #            echo "consume=$consume"
                       mysql  $mysqld xyqb -e "set names utf8;${consume}"
          fi
         len=`echo $phone_no|awk '{print length($0)}'`
                if [ $len -ne 11 ] ; then
                                echo "请输入11位手机号 ！"
                else



                    #生成账单
      			user_id=`mysql -N $mysqld xyqb_user -e "SELECT id  from user where user.phone_no='${phone_no}'"`
  			#echo "user_id=$user_id"
                        order_id=`mysql -N $mysqld xyqb -e "select id from baitiao_order where baitiao_order.user_id=${user_id}"`
                    # echo "order_id=${order_id}"
  			SELECT_plan1="SELECT id from baitiao_bill_repayment_plan where baitiao_bill_repayment_plan.user_phone='${phone_no}' LIMIT 1" 
                       	plan_id1=`mysql -N $mysqld xyqb -e "${SELECT_plan1}"`
                    # echo "plan_id=$plan_id1"
                        
			select_fund_order_id1="SELECT id  from baitiao_fund_order where baitiao_fund_order.order_id=${order_id} limit 1"
  	             #echo ${select_fund_order_id1}
                        fund_order_id1=`mysql -N $mysqld xyqb -e "${select_fund_order_id1}"`
                    # echo "fund_order_id1=$fund_order_id1"
  			
			select_order_amount1="SELECT order_amount from baitiao_bill_repayment_plan where baitiao_bill_repayment_plan.user_phone='${phone_no}' LIMIT 1"
  			
                    # echo ${select_order_amount1}
                        order_amount1=`mysql -N $mysqld xyqb -e "${select_order_amount1}"`
                        
                    # echo "order_amount1=$order_amount1"
  			#更新baitiao_fund_order.status,start_interest_date
  			update_baitiao_fund_order="UPDATE baitiao_fund_order  as aaa set  aaa.status=3, aaa.start_interest_date='${date_now}' where aaa.id=${fund_order_id1}"
                    # echo "update_fund_order=${update_baitiao_fund_order}"
  			mysql  $mysqld xyqb -e "${update_baitiao_fund_order}"
                    # echo ${update_baitiao_fund_order}
  			#插入一条数据到baitiao_finance_record
  			insert_baitiao_finance_record="INSERT INTO baitiao_finance_record("third_no","total_amount","local_no","type", "remark", "created_at", "updated_at")VALUES ( 'test001', ${order_amount1}, NULL, '0', '笑脸资金方批量放款到量化派', '${date_now}','${date_now}')"
		        #echo ${insert_baitiao_finance_record}
                        mysql  $mysqld xyqb -e "${insert_baitiao_finance_record}"

                 
			while [ ${bill_num_a} -lt ${bill_num} ]; do
		#		echo ${bill_num_a}
				paymonth=`date -d "+${bill_num_a} month"  +%Y%m`
		 #              echo "paymonth=$paymonth"
                               

                              deadline=`date -d "+${bill_num_a} month"  +%Y-%m`
                               day1="-28 23:59:59"
                               deadlineTime=${deadline}${day1}
                 #            echo "deadlineTime=${deadlineTime}"

                               if [ $bill_num_a -eq 0 ] ; then
				   select_baitiao_bill="INSERT INTO baitiao_bill ("user_id", "user_phone", "month", "already_repay_amount", "bill_repay_status", "enable", "created_at", "updated_at")VALUES ( ${user_id},'${phone_no}', ${paymonth}, '0.00', '1', '1', '${date_now}', '${date_now}')"
				  
                  #             echo ${select_baitiao_bill}
                                mysql -N $mysqld xyqb -e "${select_baitiao_bill}"
                               else 

				  select_baitiao_bill1="INSERT INTO baitiao_bill ("user_id", "user_phone", "month", "already_repay_amount", "bill_repay_status", "enable", "created_at", "updated_at")VALUES ( ${user_id}, '${phone_no}', ${paymonth}, '0.00', '0', '1','${date_now}','${date_now}')"
					mysql -N $mysqld xyqb -e "${select_baitiao_bill1}"
                   #             echo ${select_baitiao_bill1}
				fi;
				
				#xyqb.baitiao_bill_repayment_plan插入一个bill_id
				id=$((${plan_id1}+${bill_num_a}))
                    #        echo "id=$id"
				update_repayment_plan="UPDATE baitiao_bill_repayment_plan  as a set a.bill_id=(SELECT id from baitiao_bill where  baitiao_bill.user_phone='${phone_no}' and baitiao_bill.month=${paymonth}) where a.id=${id}"
				
                     #        echo ${update_repayment_plan}
                                mysql -N $mysqld xyqb -e "${update_repayment_plan}"

				#插入一条笑脸还款计划
				insert_fund_repayment_plan="INSERT INTO baitiao_fund_repayment_plan ( "plan_id", "fund_order_id", "term_no", "principal", "interest", "discount", "finance_record_id", "status", "deadline", "created_at", "updated_at")VALUES(${id}, ${fund_order_id1}, $(( $bill_num_a + 1 )), 0.00, 0.00,0.00, NULL, '0','${deadlineTime}', '${date_now}','${date_now}')"
		#	    echo "dead1 = ${deadlineTime}"
                 #          echo ${insert_fund_repayment_plan}
                               mysql -N $mysqld xyqb -e "${insert_fund_repayment_plan}"

		#	   echo "deadlineTime=${deadlineTime}"   
				#更新baitiao_bill_repayment_plan deadline
                 
				
				select_deadline="SELECT deadline from baitiao_fund_repayment_plan where baitiao_fund_repayment_plan.plan_id=${id}"
				line1=`mysql -N $mysqld xyqb -e "${select_deadline}"`
                 #            echo ${select_deadline}
                                
                  #          echo "deadline1=${line1}"
				select_update_repayment_plan="UPDATE baitiao_bill_repayment_plan as  aa  set  aa.deadline='${line1}' WHERE  aa.id=${id}"
				
                   #        echo ${select_update_repayment_plan}
                               mysql -N $mysqld xyqb -e "${select_update_repayment_plan}"

				#更新笑脸还款计划金额baitiao_fund_repayment_plan
				select_fund_repayment_plan1="update baitiao_fund_repayment_plan as a,baitiao_bill_repayment_plan as  b set a.principal=b.principal,a.interest=b.interest WHERE a.plan_id=b.id and b.id=${id} and a.fund_order_id=${fund_order_id1}"
			
                    #        echo ${select_fund_repayment_plan1}
                             mysql -N $mysqld xyqb -e "${select_update_repayment_plan}"
				bill_num_a=$(( $bill_num_a + 1 ))
                     #         echo "bill_num_a=${bill_bum_a} jjj"

			done
                  echo "done!!! `date`"	
	
              fi;

  }
 baitiao_bill $ip $phone_no 
