saoma.xchuicheng.com/application/controller/ScannerCtrl.php

<?php
/*
 * Scanner Class
 * by lijg 20181107
 */
require_once 'trdLibrary/PHPExcel/PHPExcel.php';
require_once 'library/mdb.php';
require_once 'library/common.php';

class ScannerCtrl{
    public function __construct(){
        $this->db = new MdbLib();
        $this->common = new CommonLib();
        $this->skey = '2019';
    }
    //show
    public function getOrders(){
        if(!isset($_GET['now']) || !isset($_GET['shop'])  || !isset($_GET['status']) || !isset($_GET['startDate']) || !isset($_GET['endDate'])){
            return false;
        }else{
            $now = $this->common->my_filter_input($_GET['now']);
            $shop = $this->common->my_filter_input($_GET['shop']);
            $status = $this->common->my_filter_input($_GET['status']);
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
            $key = $this->common->my_filter_input($_GET['key']);
            $pnsn = isset($_GET['pnsn'])?$this->common->my_filter_input($_GET['pnsn']):15;
        }
        
        $shop_type = '';
        switch($shop){
            case 1:
                $shop_type = 'AAASSS';
                break;
            case 2:
                $shop_type = 'Alipearl';
                break;
            case 3:
                $shop_type = 'QQ';
                break;
            case 4:
                $shop_type = 'SuperNova';
                break;
            case 5:
                $shop_type = 'West-Kiss';
                break;
            case 6:
                $shop_type = 'Yolissa';
                break;
            default:
                $shop_type = '';
        }
        
        $offset = ($now-1)*$pnsn;
        $sql = 'SELECT `oid`,`order_sn`,`order_barcode`,`order_status`,`goods_no`,`goods_info`,`create_time`,`out_time`,`gir` ';
        $sql .= 'FROM `orders` '; 
        $sql .= "WHERE `create_time` BETWEEN '{$startDate}' AND '{$endDate}' ";
        
        if($shop != 0){
            $sql .= " AND `order_barcode` LIKE '{$shop_type}%' ";
        }
        
        if($status != 0){
            $sql .= " AND `order_status`='{$status}' ";
        }
        
        //$sql .= "ORDER BY `create_time` DESC, `oid` DESC LIMIT {$offset},$pnsn;";
        $sql .= "ORDER BY `oid` DESC LIMIT {$offset},$pnsn;";
        $query = $this->db->querySql($sql);
        
        $line = "";
        while($item = $this->db->queryRs($query)) {
            if($item['order_status'] == '2'){
                $order_status = "已出库";
                $out_time = $item['out_time'];
                $class = 'ok';
            }elseif($item['order_status'] == '1'){
                $order_status = "未出库";
                $out_time = '暂无';
                $class = 'no';
            }elseif($item['order_status'] == '3'){
                $order_status = "已取消";
                $out_time = '暂无';
                $class = 'cancel';
            }
            if($item['gir'] == 1){
                $repeat = 'repeat';
            }else{
                $repeat = '';
            }
            
            $line .= '<div class="list_block">';
            $line .= "<span class=\"order_id\">{$item['oid']}</span>";
            $line .= "<span class=\"order_barcode\">{$item['order_barcode']}</span>";
            $line .= "<span class=\"order_no\">{$item['order_sn']}</span>";
            $line .= "<span class=\"goods_no {$repeat}\">{$item['goods_no']}</span>";
            $line .= "<span class=\"goods_info\" title=\"{$item['goods_info']}\">{$item['goods_info']}</span>";
            $line .= "<span class=\"create_time\">{$item['create_time']}</span>";
            $line .= "<span class=\"order_status {$class}\">{$order_status}</span>";
            $line .= "<span class=\"out_time\">{$out_time}</span>";
            if(!empty($key) && $key==$this->skey){
                if($item['order_status'] == '1'){
                    $line .= "<span class=\"options\"><input type=\"button\" class=\"cancel_order\" data-ordersn=\"{$item['order_sn']}\" value=\"取消\"></span>";
                }else{
                    $line .= "<span class=\"options\">暂无</span>";
                }
            }
            $line .= '</div>';
        }
        return $line;
    }
    
    public function searchOrders(){
        if(!isset($_GET['key'])){
            return false;
        }else{
            $key = $this->common->my_filter_input($_GET['key']);
        }
        
        $sql = 'SELECT `oid`,`order_sn`,`order_barcode`,`order_status`,`goods_no`,`goods_info`,`create_time`,`out_time`,`gir` ';
        $sql .= 'FROM `orders` ';
        if(isset($_GET['barcode'])){
            $barcode = $this->common->my_filter_input($_GET['barcode']);
            $sql .= "WHERE `order_barcode`='{$barcode}' LIMIT 1;";
        }elseif(isset($_GET['sn'])){
            $sn = $this->common->my_filter_input($_GET['sn']);
            $sql .= "WHERE `order_sn`='{$sn}' LIMIT 1;";
        }elseif(isset($_GET['gn'])){
            $gn = $this->common->my_filter_input($_GET['gn']);
            $sql .= "WHERE `goods_no`='{$gn}';";
        }
        
        $query = $this->db->querySql($sql);
        
        $line = "";
        while($item = $this->db->queryRs($query)) {
            if($item['order_status'] == '2'){
                $order_status = "已出库";
                $out_time = $item['out_time'];
                $class = 'ok';
            }elseif($item['order_status'] == '1'){
                $order_status = "未出库";
                $out_time = '暂无';
                $class = 'no';
            }elseif($item['order_status'] == '3'){
                $order_status = "已取消";
                $out_time = '暂无';
                $class = 'cancel';
            }
            
            if($item['gir'] == 1){
                $repeat = 'repeat';
            }else{
                $repeat = '';
            }
            
            $line .= '<div class="list_block">';
            $line .= "<span class=\"order_id\">{$item['oid']}</span>";
            $line .= "<span class=\"order_barcode\">{$item['order_barcode']}</span>";
            $line .= "<span class=\"order_no\">{$item['order_sn']}</span>";
            $line .= "<span class=\"goods_no {$repeat}\">{$item['goods_no']}</span>";
            $line .= "<span class=\"goods_info\" title=\"{$item['goods_info']}\">{$item['goods_info']}</span>";
            $line .= "<span class=\"create_time\">{$item['create_time']}</span>";
            $line .= "<span class=\"order_status {$class}\">{$order_status}</span>";
            $line .= "<span class=\"out_time\">{$out_time}</span>";
            if(!empty($key) && $key==$this->skey){
                if($item['order_status'] == '1'){
                    $line .= "<span class=\"options\"><input type=\"button\" class=\"cancel_order\" data-ordersn=\"{$item['order_sn']}\" value=\"取消\"></span>";
                }else{
                    $line .= "<span class=\"options\">暂无</span>";
                }
            }
            $line .= '</div>';
        }
        if(!empty($line)){
            return $line;
        }else{
            return -1;
        }
    }
    
    public function uploadExcel(){
        $tmp = pathinfo($_FILES['excel']['name']);
        if(($tmp['extension'] == 'xlsx') && ($_FILES['excel']['size'] < 5000000)){
            if($_FILES['excel']['error'] > 0){
                echo "error on upload file";
                return -2;
            }else{
                $newName = 'sc_'.date('YmdHis').'.'.$tmp['extension'];
                $newPath = "../public/upload/SCANNER/{$newName}";
                if(!move_uploaded_file($_FILES['excel']['tmp_name'], $newPath)){
                    return -3;
                }else{
                    if($this->readExcel($newPath) < 1){
                        return -4;
                    }else{
                        return 1;
                    }
                }
            }
        } else {
            return -1;
        }
    }
    
    public function readExcel($file){
        $PHPExcel = new \PHPExcel();
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if(!$PHPReader->canRead($file)){
            return -1;
        }
        $PHPExcel = $PHPReader->load($file);
        $currentSheet = $PHPExcel->getsheet(0);
        $allColumn = $currentSheet->getHighestColumn();
        $allRow = $currentSheet->getHighestRow();
        $lastColumn = 'E';
        $startRow = 2;
        $startColumn = 'A';
        $endColumn = 'E';
        if($allColumn != $lastColumn){
            return -2;
        }
        
        $insertValues = '';
        for($currentRow = $startRow; $currentRow <= $allRow; $currentRow++){
            $cellStr = "";
            for($currentColumn = $startColumn; $currentColumn <= $endColumn; $currentColumn++){
                $cellValue = $currentSheet->getCellByColumnAndRow(ord($currentColumn)-65, $currentRow)->getValue();
                $cellValue = $this->common->my_filter_input($cellValue);
                if(empty($cellValue)){
                    break;
                }else{
                    $cellValue = trim($cellValue, chr(194).chr(160));
					$cellStr .= '"' . trim($cellValue, '&quot;') . '",';
                }
            }
            if(!empty($cellStr)){
                $cellStr .= '"' . date('Y-m-d H:i:s', time()) . '"';
                $insertValues .= "({$cellStr}),";
            }
        }
        $insertValues = trim($insertValues, ',');
        $sql = "INSERT INTO `orders`(`create_time`,`order_sn`,`order_barcode`,`goods_no`,`goods_info`,`out_time`) VALUES {$insertValues};";
        $this->db->querySql("SET AUTOCOMMIT=0");
        $this->db->begin();
        if(!($this->db->querySql($sql))){
            $this->db->querySql("ROLLBACK");
            return -3;
        }else{
            $sql_gir = "UPDATE `orders` SET `gir`=1 WHERE `goods_no` IN (";
            $sql_gir .= "SELECT * FROM (SELECT `goods_no` FROM `orders` GROUP BY `goods_no` HAVING COUNT(`goods_no`)>1) AS `tmp`) AND `gir`!=1;";
            if($this->db->querySql($sql_gir)){
                $this->db->commit();
                return 1;
            }else{
                $this->db->querySql("ROLLBACK");
                return -4;
            }
        }
    }
    
    public function updateOrder(){
        if(isset($_GET['order_sn']) && !empty($_GET['order_sn']) && isset($_GET['status']) && !empty($_GET['status'])){
            $order_sn = $this->common->my_filter_input($_GET['order_sn']);
            $order_status = $this->common->my_filter_input($_GET['status']);
        } else {
            return false;
        }
        
        $sql_status = "SELECT `order_status`,`out_time` FROM `orders` WHERE `order_sn`='{$order_sn}';";
        $query = $this->db->querySql($sql_status);
        $arr_status = $this->db->queryRsAll($query);
        if(empty($arr_status)){
            return json_encode(array(
                'no' => '-10'
            ));
        }

        $now = date("Y-m-d H:i:s", time());
        $sql = "UPDATE `orders` SET `order_status`='{$order_status}',`out_time`='{$now}' WHERE `order_sn`='{$order_sn}';";
        if($arr_status[0]['order_status'] == 1 && $order_status == 2){
            $this->db->querySql($sql);
            if($this->db->queryAR() == 1){
                return json_encode(array(
                    'no' => '1'
                ));
            }else{
                return json_encode(array(
                    'no' => '-1'
                ));
            }
        }elseif($arr_status[0]['order_status'] == 1 && $order_status == 3){
            $this->db->querySql($sql);
            if($this->db->queryAR() == 1){
                return json_encode(array(
                    'no' => '2'
                ));
            }else{
                return json_encode(array(
                    'no' => '-2'
                ));
            }
        }elseif($arr_status[0]['order_status'] == 2 && $order_status == 2){
            return json_encode(array(
                'no' => '-3',
                'out_time' => $arr_status[0]['out_time'],
            ));
        }elseif($arr_status[0]['order_status'] == 2 && $order_status == 3){
            return json_encode(array(
                'no' => '-4',
                'out_time' => $arr_status[0]['out_time'],
            ));
        }elseif($arr_status[0]['order_status'] == 3 && $order_status == 2){
            return json_encode(array(
                'no' => '-5',
                'out_time' => $arr_status[0]['out_time'],
            ));
        }elseif($arr_status[0]['order_status'] == 3 && $order_status == 3){
            return json_encode(array(
                'no' => '-6',
                'out_time' => $arr_status[0]['out_time'],
            ));
        }else{
            return json_encode(array(
                'no' => '-7'
            ));
        }
    }
    
    public function getStatistics(){
        if(!isset($_GET['startDate']) || !isset($_GET['endDate'])){
            return false;
        } else {
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
        }
        
        $yesterday_start = date("Y-m-d", strtotime('-1 day')) . ' 00:00:00';
        $yesterday_end = date("Y-m-d", strtotime('-1 day')) . ' 23:59:59';
        $sql_yesterday = "SELECT COUNT(`oid`) AS `total_yesterday_out` FROM `orders`";
        $sql_yesterday .= " WHERE `out_time` BETWEEN '{$yesterday_start}' AND '{$yesterday_end}' AND `order_status`='2';";
        
        $sql_total = "SELECT COUNT(`oid`) AS `total_num` FROM `orders` ";
        $sql_total .= "WHERE `create_time` BETWEEN '{$startDate}' AND '{$endDate}';";
        
        $sql_out = "SELECT COUNT(`oid`) AS `total_out` FROM `orders` ";
        $sql_out .= "WHERE `create_time` BETWEEN '{$startDate}' AND '{$endDate}' AND `order_status`='2';";
        
        $sql_cancel = "SELECT COUNT(`oid`) AS `total_out` FROM `orders` ";
        $sql_cancel .= "WHERE `create_time` BETWEEN '{$startDate}' AND '{$endDate}' AND `order_status`='3';";
        
        $query_yesterday = $this->db->querySql($sql_yesterday);
        $rs_yesterday = $this->db->queryRs($query_yesterday);
        
        $query_total = $this->db->querySql($sql_total);
        $rs_total = $this->db->queryRs($query_total);
        
        $query_out = $this->db->querySql($sql_out);
        $rs_out = $this->db->queryRs($query_out);
        
        $query_cancel = $this->db->querySql($sql_cancel);
        $rs_cancel = $this->db->queryRs($query_cancel);
        
        $rs = array();
        $rs['yesterday'] = $rs_yesterday[0];
        $rs['total'] = $rs_total[0];
        $rs['out'] = $rs_out[0];
        $rs['cancel'] = $rs_cancel[0];
        $rs['in'] = $rs['total'] - $rs['out'] - $rs['cancel'];
        return json_encode($rs);
    }
    
    public function exportExcel(){
        set_time_limit(120);
        $PHPExcel = new \PHPExcel();
        if(!isset($_GET['shop'])  || !isset($_GET['status']) || !isset($_GET['startDate']) || !isset($_GET['endDate'])){
            return false;
        }else{
            $shop = $this->common->my_filter_input($_GET['shop']);
            $status = $this->common->my_filter_input($_GET['status']);
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
        }
        
        $shop_type = '';
        switch($shop){
            case 1:
                $shop_type = 'AAASSS';
                break;
            case 2:
                $shop_type = 'Alipearl';
                break;
            case 3:
                $shop_type = 'QQ';
                break;
            case 4:
                $shop_type = 'SuperNova';
                break;
            case 5:
                $shop_type = 'West-Kiss';
                break;
            case 6:
                $shop_type = 'Yolissa';
                break;
            default:
                $shop_type = '';
        }
        $sql = 'SELECT `oid`,`order_sn`,`order_barcode`,`order_status`,`goods_no`,`goods_info`,`create_time`,`out_time` ';
        $sql .= 'FROM `orders` ';
        $sql .= "WHERE `out_time` BETWEEN '{$startDate}' AND '{$endDate}' ";
        
        if($shop != 0){
            $sql .= " AND `order_barcode` LIKE '{$shop_type}%' ";
        }
        
        if($status != 0){
            $sql .= " AND `order_status`='{$status}' ";
        }
        $query = $this->db->querySql($sql);
        $line = "";
        $PHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '运单编号')
                ->setCellValue('B1', '条码编号')
                ->setCellValue('C1', '状态')
                ->setCellValue('D1', '订单编号')
                ->setCellValue('E1', '货物明细')
                ->setCellValue('F1', '创建日期')
                ->setCellValue('G1', '出库日期');
        $k = 2;
        while($item = $this->db->queryRs($query)) {
            if($item['order_status'] == '2'){
                $order_status = "已出库";
                $out_time = $item['out_time'];
            }elseif($item['order_status'] == '1'){
                $order_status = "未出库";
                $out_time = '暂无';
            }elseif($item['order_status'] == '3'){
                $order_status = "已取消";
                $out_time = '暂无';
            }
            $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$k, $item['order_sn'])
            ->setCellValue('B'.$k, $item['order_barcode'])
            ->setCellValue('C'.$k, $order_status)
            ->setCellValue('D'.$k, $item['goods_no'])
            ->setCellValue('E'.$k, $item['goods_info'])
            ->setCellValue('F'.$k, $item['create_time'])
            ->setCellValue('G'.$k, $out_time);
            $k++;
        }
        
        // set wdith
        $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
        $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        
        $filename = 'waybill.'.date('His',time()).'.xls';
        $PHPExcel->getActiveSheet()->setTitle('waybill');
        $PHPExcel->setActiveSheetIndex(0);
        
        //header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
        header("Content-Type: application/ms-excel; charset=utf-8");
        header("Content-Disposition: attachment; filename={$filename}");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: private",false);
        //$PHPWriter = new PHPExcel_Writer_Excel2007($PHPExcel, 'Excel2007');
        $PHPWriter = new PHPExcel_Writer_Excel5($PHPExcel, 'Excel5');
        $PHPWriter->save('php://output');
    }
    
    public function pagination(){
        if(!isset($_GET['shop']) || !isset($_GET['status']) || !isset($_GET['startDate']) || !isset($_GET['endDate']) || !isset($_GET['wh'])){
            return false;
        }else {
            $shop = $this->common->my_filter_input($_GET['shop']);
            $status = $this->common->my_filter_input($_GET['status']);
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
            $wh = $this->common->my_filter_input($_GET['wh']);
            $pnsn = isset($_GET['pnsn'])?$this->common->my_filter_input($_GET['pnsn']):15;
        }
        
        $shop_type = '';
        switch($shop){
            case 1:
                $shop_type = 'AAASSS';
                break;
            case 2:
                $shop_type = 'Alipearl';
                break;
            case 3:
                $shop_type = 'QQ';
                break;
            case 4:
                $shop_type = 'SuperNova';
                break;
            case 5:
                $shop_type = 'West-Kiss';
                break;
            case 6:
                $shop_type = 'Yolissa';
                break;
            default:
                $shop_type = '';
        }
        
        $sql = "SELECT `oid` FROM `orders` ";
        $sql .= "WHERE `create_time` BETWEEN '{$startDate}' AND '{$endDate}'";
        if($shop != 0){
            $sql .= " AND `order_barcode` LIKE '{$shop_type}%'";
        }
        if($status != 0){
            $sql .= " AND `order_status`='{$status}'";
        }
        $sql .= ';';
        $query = $this->db->querySql($sql);
        $total = $this->db->queryNum($query);
        
        if($total == 0){
            $num = '0';
        }else{
            $num = ceil($total/$pnsn);
            $wh_max = ceil($num/10);
        }
        
        $direction = substr($wh, 0, 1);
        $wh = substr($wh, 1);
        if($direction == 'h'){
            $whh = ($wh==0)?'0':$wh-1;
            $wht = ($wh==0)?'1':$wh;
        }elseif($direction == 't'){
            $whh = ($wh==$wh_max)?($wh-1):$wh;
            $wht = ($wh==$wh_max)?$wh:($wh+1);
        }else{
            $whh = '0';
            $wht = '1';
        }
                
        $pagination = '<span class="total_page" data-ttpg="'.$num.'">共&nbsp;'.$num.'&nbsp;页</span>';
        if($num > 1){
            $pagination .= ($num>10)?"<span class=\"gtlt head\" data-wh=\"h{$whh}\">&lt;&lt;</span>":'';
            $clazz = '';
            for($i=1+$whh*10; $i<=10+$whh*10; $i++){
                if($i > $num){
                    break;
                }else{
                    if($i != 1+$whh*10){
                        $clazz = 'page';
                    }else{
                        $clazz = 'page pn';
                    }
                    $param = $i . '_' . $shop .'_' . $status . '_' . substr($startDate, 0, 10) . '_' . substr($endDate, 0, 10);
                    $pagination .= "<span class=\"{$clazz}\" onclick=\"getOFP('{$param}')\">".$i."</span>";
                }
            }
            $pagination .= ($num>10)?"<span class=\"gtlt tail\" data-wh=\"t{$wht}\">&gt;&gt;</span>":'';
        }
        $pagination .= "<span>展示 <select class=\"pnsn\">";
        $pns = array('15', '50', '100', '250', '500');
        for($i=0; $i<5; $i++){
            if($pnsn == $pns[$i]){
                $s = 'selected="selected"';
            }else{
                $s = '';
            }
            $pagination .= "<option value=\"{$pns[$i]}\" {$s}>{$pns[$i]}条</option>";
        }
        $pagination .= "</select> 记录</span>";
        return $pagination;
    }
    
    public function __destruct(){
        //TODO
    }
}