<?php 
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputArgument;
use TopClient;
use DB;
use App\Order;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Reader_Excel5;
use PHPExcel;
use PHPExcel_Writer_Excel2007;
class OrderExcelAdd extends Command {

    protected $signature = 'OrderExcelAdd';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '导入订单';


    public function handle()
    {
        $this->OrderExcelAdd();
    }
    public function OrderExcelAdd(){

        $excelFile = './order_history.xlsx';
        //实例化Excel读取类
        $objReader = new PHPExcel_Reader_Excel2007();

        if(!$objReader->canRead($excelFile)){
            $objReader = new PHPExcel_Reader_Excel5();
            if(!$objReader->canRead($excelFile)){
                echo "\n".'无法识别的Excel文件！';
                return false;
            }
        }
        $objPHPExcel=$objReader->load($excelFile);
        $worksheet=$objPHPExcel->getSheet(0);//获取第一个工作表
        $highestRow=$worksheet->getHighestRow();//取得总行数
        $highestColumn=$worksheet->getHighestColumn(); //取得总列数

        $lines = $highestRow - 1;
        if ($lines <= 0) {
            //'Excel表格中没有数据';
            return false;
        }

        $errorArr=[];
        $okStr = '';

        for ($row = 2; $row <= $highestRow; ++$row) {
            $t = trim($worksheet->getCellByColumnAndRow(0, $row)->getValue()); 
	    $n = intval(($t - 25569) * 3600 * 24);
	    $createTime = gmdate('Y-m-d',$n);
            $receiverName = trim($worksheet->getCellByColumnAndRow(1, $row)->getValue());
            $receiverMobile = trim($worksheet->getCellByColumnAndRow(2, $row)->getValue());
            $receiverAddress = trim($worksheet->getCellByColumnAndRow(3, $row)->getValue());
            $goods_note = trim($worksheet->getCellByColumnAndRow(4, $row)->getValue());
            $receivedAmount = trim($worksheet->getCellByColumnAndRow(5, $row)->getValue());
            $f = trim($worksheet->getCellByColumnAndRow(7, $row)->getValue());
	    if(!empty($f)){
            	$n = intval(($f - 25569) * 3600 * 24);
            	$fanTime = gmdate('Y-m-d',$n);
	    }else{
		$fanTime = '';
	    }
            $is_fugou = trim($worksheet->getCellByColumnAndRow(8, $row)->getValue());

            //数据库-新增数据
            $order = array();
            if( empty($receiverMobile) ){
                continue;
            }
            if( !empty($createTime) ){
                $order['createTime'] = date('Y-m-d H:i:s', strtotime($createTime));
            }
            if( !empty($receiverName) ){
                $order['receiverName'] = $receiverName;
            }
            if( !empty($receiverMobile) ){
                $order['receiverMobile'] = $receiverMobile;
            }
            if( !empty($receiverAddress) ){
                $order['receiverAddress'] = $receiverAddress;
            }
            if( !empty($goods_note) ){
                $order['goods_note'] = $goods_note;
            }
            if( !empty($receivedAmount) ){
                $order['receivedAmount'] = $receivedAmount;
            }
            if( empty($fanTime) ){
                $fanTime = $createTime;
            }
            if( !empty($is_fugou) ){
                $order['is_fugou'] = 1;
            }else{
                $order['is_fugou'] = 0;
            }
            
            $order['eshopCode'] = '99|34881'; //默认
            $order['outerCode'] = Order::createOuterCode();//随机生成
            $order['status'] = 0;
            $order['customerName'] = $order['receiverName']; //买家名称
            $order['shippingType'] = '卖家包邮';
            $order['expressCompanyCode'] = '10084';
            $order['expressCompanyName'] = '顺丰快递';
            
            $order['buyerPostageFee'] = 0; //买家邮费
            $order['discountFee'] = 0; //订单优惠
            $order['adjustFee'] = 0; //订单调价
            $order['serviceFee'] = 0; //服务费
            $order['paymentMethod'] = '在线付款';
            $order['paymentType'] = '网银';
            $order['paymentAccount'] = '18322225555'; // 付款账号，暂时默认写死
            $order['paymentTime'] = $order['createTime']; // 付款时间
            $order['warehouse'] = 0; //仓库
            $order['delivery_date'] = $createTime; //发货日期

            #更新客户表
            $customer = array();
            $customers['name'] = $receiverName;
            $customers['receiverAddress'] = $receiverAddress;

            $if_e = DB::table('customers')->select('id')->where('phone', $order['receiverMobile'])->first();
            if(isset($if_e->id)){               
                //$up_c = DB::table('customers')->where('id', $if_e->id)->update($customers);
            }else{
                $customers['phone'] = $order['receiverMobile'];
                $customers['fanTime'] = $fanTime;
                $customers['is_fugou'] = $order['is_fugou'];
                $in_c = DB::table('customers')->insert($customers);
            }
            $order['admin_id'] = 3;
            $order['admin_name'] = '测试账号';
            $order['team_id'] = 4;
            $res = DB::table('order')->insert($order);
echo "\n行数：".$row. " 结果：".$res;

        }
    }

}