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

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

class BarcodeCtrl{
    public function __construct(){
        $this->db = new MdbLib();
        $this->common = new CommonLib();
    }
    
    //show
    public function showProduct(){
        $now = isset($_GET['now'])?$this->common->my_filter_input($_GET['now']):'1';
        $type = isset($_GET['type'])?$this->common->my_filter_input($_GET['type']):'1';

        $offset = ($now-1)*15;
        if($type == 1){
            $sql = "SELECT `bc_id`,`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`,`bc_print_status`,`create_time` FROM `barcode_amazon` ORDER BY `bc_id` DESC LIMIT {$offset},15";
        }elseif($type == 2){
            $sql = "SELECT `bc_id`,`bc_product`,`bc_sku`,`bc_tag`,`bc_amount`,`bc_print_status`,`create_time` FROM `barcode_oversea` ORDER BY `bc_id` DESC LIMIT {$offset},15";
        }elseif($type == 3){
            $sql = "SELECT `bc_id`,`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`,`bc_print_status`,`create_time` FROM `barcode_alie` ORDER BY `bc_id` DESC LIMIT {$offset},15";
        }elseif($type == 4){
            $sql = "SELECT `bc_id`,`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`,`bc_print_status`,`create_time` FROM `barcode_gzc` ORDER BY `bc_id` DESC LIMIT {$offset},15";
        }
        $query = $this->db->querySql($sql);
        $line = "";
        while($item = $this->db->queryRs($query)) {
            if($item['bc_print_status']){
                $print_status = "已打印";
                $class = 'ok';
            }else{
                $print_status = "未打印";
                $class = 'no';
            }
            $line .= '<div class="list_block">';
            $line .= "<span class=\"id\">{$item['bc_id']}</span>";
            $line .= "<span class=\"product\">{$item['bc_product']}</span>";
            if($type == 1 || $type == 3 || $type == 4){
                $line .= "<span class=\"code\">{$item['bc_code']}</span>";
            }elseif($type == 2){
                $line .= "<span class=\"code\">{$item['bc_sku']}</span>";
            }
            $line .= "<span class=\"tag\">{$item['bc_tag']}</span>";
            if($type == 1 || $type == 3 || $type == 4){
                $line .= "<span class=\"tag2\">{$item['bc_tag2']}</span>";
            }
            $line .= "<span class=\"amount la\">{$item['bc_amount']}</span>";
            $line .= "<span class=\"create_time\">{$item['create_time']}</span>";
            $line .= "<span class=\"print_status {$class}\">{$print_status}</span>";
            $line .= '<span class="options"><input type="button" value="Print" class="print_bc" onclick="myAjax(\'barcode_updatePrintStatus\',\''.$item['bc_id'].'_'.$type.'\');" /></span>';
            $line .= '</div>';
        }
        return $line;
    }
    
    //search
    public function searchProduct(){
        if(!isset($_POST['code'])){
            return -1;
        }else{
            $code = $this->common->my_filter_input($_POST['code']);
        }
        $type = isset($_POST['type'])?$this->common->my_filter_input($_POST['type']):'1';
        $sql = "SELECT `bc_id`,`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`,`bc_print_status`,`create_time` FROM `barcode_amazon` WHERE `bc_code`='{$code}' ORDER BY `create_time` DESC";
        $query = $this->db->querySql($sql);
        $line = "";
        while($item = $this->db->queryRs($query)) {
            if($item['bc_print_status']){
                $print_status = "已打印";
                $class = 'ok';
            }else{
                $print_status = "未打印";
                $class = 'no';
            }
            $line .= '<div class="list_block">';
            $line .= "<span class=\"id\">{$item['bc_id']}</span>";
            $line .= "<span class=\"product\">{$item['bc_product']}</span>";
            $line .= "<span class=\"code\">{$item['bc_code']}</span>";
            $line .= "<span class=\"tag\">{$item['bc_tag']}</span>";
            $line .= "<span class=\"tag2\">{$item['bc_tag2']}</span>";
            $line .= "<span class=\"amount la\">{$item['bc_amount']}</span>";
            $line .= "<span class=\"create_time\">{$item['create_time']}</span>";
            $line .= "<span class=\"print_status {$class}\">{$print_status}</span>";
            $line .= '<span class="options"><input type="button" value="Print" class="print_bc" onclick="myAjax(\'barcode_updatePrintStatus\',\''.$item['bc_id'].'_'.$type.'\');" /></span>';
            $line .= '</div>';
        }
        return $line;
    }
    
    //upload
    public function uploadExcel(){
        if(isset($_POST['type']) && !empty($_POST['type'])){
            $type = $this->common->my_filter_input($_POST['type']);
            if($type == 1){
                $parentFolder = 'AMAZON';
            }elseif($type == 2){
                $parentFolder = 'OVERSEA';
            }elseif($type == 3){
                $parentFolder = 'ALIE';
            }elseif($type == 4){
                $parentFolder = 'GZC';
            }
        }else{
            return false;
        }
        
//         if(($_FILES['excel']['type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') && ($_FILES['excel']['size'] < 5000000)){
        if($_FILES['excel']['size'] < 5000000){
            if($_FILES['excel']['error'] > 0){
                echo "error on upload file";
                return -2;
            }else{
                $tmp = pathinfo($_FILES['excel']['name']);
                $newName = date('YmdHis').'.'.$tmp['extension'];
                $newPath = "../public/upload/BARCODE/{$parentFolder}/{$newName}";
                if(!move_uploaded_file($_FILES['excel']['tmp_name'], $newPath)){
                    return -3;
                }else{
                    if($this->readExcel($newPath, $type) !== 1){
                        return -4;
                    }else{
                        return 1;
                    }
                }
            }
        } else {
            return -1;
        }
    }
    
    //read excel
    public function readExcel($file, $type){
        $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();
        if($type == 1 || $type == 3 || $type == 4){
            //$lastColumn = 'E';
            $startRow = 3;
            $startColumn = 'A';
            $endColumn = 'E';
        }elseif($type == 2){
            //$lastColumn = 'I';
            $startRow = 2;
            $startColumn = 'D';
            $endColumn = 'G';
        }
        //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)){
					if($currentColumn == 'C' || $currentColumn == 'D'){
						$cellStr .= '" ",';
					}else{
						break;
					}
                }else{
                    $cellStr .= '"' . $cellValue . '",';
                }
            }
            $cellStr = trim($cellStr, ',');
            if(!empty($cellStr)){
                $insertValues .= "({$cellStr}),";
            }
        }
        $insertValues = trim($insertValues, ',');
        if($type == 1){
            $sql = "INSERT INTO `barcode_amazon`(`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`) VALUES {$insertValues};";
        }elseif($type == 2){
            $sql = "INSERT INTO `barcode_oversea`(`bc_product`,`bc_sku`,`bc_tag`,`bc_amount`) VALUES {$insertValues};";
        }elseif($type == 3){
            $sql = "INSERT INTO `barcode_alie`(`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`) VALUES {$insertValues};";
        }elseif($type == 4){
            $sql = "INSERT INTO `barcode_gzc`(`bc_product`,`bc_code`,`bc_tag`,`bc_tag2`,`bc_amount`) VALUES {$insertValues};";
        }
        if($this->db->querySql($sql)){
            return 1;
        }else{
            return -3;
        }
    }
    
    //ups
    public function updatePrintStatus(){
        if(isset($_GET['bid']) && !empty($_GET['bid']) && isset($_GET['type']) && !empty($_GET['type'])){
            $bid = $this->common->my_filter_input($_GET['bid']);
            $type = $this->common->my_filter_input($_GET['type']);
        } else {
            return false;
        }
        
        if($type == 1){
            $db = 'barcode_amazon';
        }elseif($type == 2){
            $db = 'barcode_oversea';
        }elseif($type == 3){
            $db = 'barcode_alie';
        }elseif($type == 4){
            $db = 'barcode_gzc';
        }
        $sql = "UPDATE `{$db}` SET `bc_print_status`=`bc_print_status`+1 WHERE `bc_id`='{$bid}';";
        $this->db->querySql($sql);
        return 1;
    }
    
    //pagination
    public function pagination(){
        if(isset($_GET['type']) && !empty($_GET['type']) && isset($_GET['wh'])){
            $type = $this->common->my_filter_input($_GET['type']);
            $wh = $this->common->my_filter_input($_GET['wh']);
        } else {
            return false;
        }
        if($type == 1){
            $db = 'barcode_amazon';
        }elseif($type == 2){
            $db = 'barcode_oversea';
        }elseif($type == 3){
            $db = 'barcode_alie';
        }elseif($type == 4){
            $db = 'barcode_gzc';
        }
        
        $sql = "SELECT `bc_id` FROM `{$db}`";
        $query = $this->db->querySql($sql);
        $total = $this->db->queryNum($query);
        
        if($total == 0){
            $num = '0';
        }else{
            $num = ceil($total/15);
            $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 . '_' . $type;
                    $pagination .= "<span class=\"{$clazz}\" onclick=\"myAjax('barcode_showProduct','{$param}')\">".$i."</span>";
                }
            }
            $pagination .= ($num>10)?"<span class=\"gtlt tail\" data-wh=\"t{$wht}\">&gt;&gt;</span>":'';
        }
        return $pagination;
    }
    
    public function __destruct(){
        //TODO
    }
}