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

<?php
require_once 'trdLibrary/PHPExcel/PHPExcel.php';
require_once 'library/mdb.php';
require_once 'library/common.php';

class WsoCheckCtrl{
    public function __construct(){
        $this->db = new MdbLib();
        $this->common = new CommonLib();
    }
    //show
    public function getOrders(){
        if(!isset($_GET['now']) || !isset($_GET['startDate']) || !isset($_GET['endDate'])){
            return false;
        }else{
            $now = $this->common->my_filter_input($_GET['now']);
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
            $pnsn = isset($_GET['pnsn'])?$this->common->my_filter_input($_GET['pnsn']):50;
        }
        
        $offset = ($now-1)*$pnsn;
        $sql = 'SELECT `id`,`order_barcode`,`goods_no`,`goods_info`,`out_time`,`transport`';
        $sql .= ' FROM `app_wsocheck` '; 
        $sql .= " WHERE `out_time` BETWEEN '{$startDate}' AND '{$endDate}'";
        $sql .= " ORDER BY `out_time` DESC LIMIT {$offset},$pnsn;";
        $query = $this->db->querySql($sql);
        
        $line = "";
        while($item = $this->db->queryRs($query)) { 
            $line .= '<div class="list_block">';
            $line .= "<span class=\"order_id\">{$item['id']}</span>";
            $line .= "<span class=\"order_barcode\" title=\"{$item['order_barcode']}\">{$item['order_barcode']}</span>";
            $line .= "<span class=\"goods_no\">{$item['goods_no']}</span>";
            $line .= "<span class=\"goods_info\" title=\"{$item['goods_info']}\">{$item['goods_info']}</span>";
            $line .= "<span class=\"transport\">{$item['transport']}</span>";
            $line .= "<span class=\"out_time\">{$item['out_time']}</span>";
            $line .= '<span class="options">';
            $line .= "<input type=\"button\" class=\"delete_order\" data-id=\"{$item['id']}\" value=\"删除\">";
            $line .= '</span></div>';
        }
        return $line;
    }
    
    public function searchOrders(){
    	$sql = 'SELECT `id`,`order_barcode`,`goods_no`,`goods_info`,`out_time`,`transport` FROM `app_wsocheck`';
        if(isset($_GET['code'])){
            $code = $this->common->my_filter_input($_GET['code']);
            $sql .= " WHERE `order_barcode`='{$code}' OR `goods_no`='{$code}';";
        }
        $query = $this->db->querySql($sql);
        $line = "";
        while($item = $this->db->queryRs($query)) {
            $line .= '<div class="list_block">';
            $line .= "<span class=\"order_id\">{$item['id']}</span>";
            $line .= "<span class=\"order_barcode\" title=\"{$item['order_barcode']}\">{$item['order_barcode']}</span>";
            $line .= "<span class=\"goods_no\">{$item['goods_no']}</span>";
            $line .= "<span class=\"goods_info\" title=\"{$item['goods_info']}\">{$item['goods_info']}</span>";
            $line .= "<span class=\"transport\">{$item['transport']}</span>";
            $line .= "<span class=\"out_time\">{$item['out_time']}</span>";
            $line .= '<span class="options">';
            $line .= "<input type=\"button\" class=\"delete_order\" data-id=\"{$item['id']}\" value=\"删除\">";
            $line .= '</span></div>';
        }
        if(!empty($line)){
            return $line;
        }else{
            return -1;
        }
    }
    
    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';
        }
        
        $sql_total = "SELECT COUNT(`id`) AS `total_num` FROM `app_wsocheck` ";
        $sql_total .= "WHERE `out_time` BETWEEN '{$startDate}' AND '{$endDate}';";
        $query_total = $this->db->querySql($sql_total);
        $rs_total = $this->db->queryRs($query_total);
        
        $rs = array();
        $rs['total'] = $rs_total['total_num'];
        return json_encode($rs);
    }
    
    public function exportExcel(){
        set_time_limit(120);
        
        if(!isset($_GET['startDate']) || !isset($_GET['endDate'])){
            return false;
        }else{
            $startDate = $_GET['startDate'] . ' 00:00:00';
            $endDate = $_GET['endDate'].' 23:59:59';
        }
        
//         $sql = 'SELECT `id`,`order_barcode`,`goods_no`,`goods_info`,`out_time`,`transport` FROM `app_wsocheck`';
        $sql = 'SELECT `id`,`order_barcode`,`goods_info` FROM `app_wsocheck`';
        $sql .= " WHERE `out_time` BETWEEN '{$startDate}' AND '{$endDate}' ";
        $query = $this->db->querySql($sql);
        
        $PHPExcel = new \PHPExcel();
        $PHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '序号')
                ->setCellValue('B1', '条码编号')   
                ->setCellValue('C1', '货物明细');
// 				->setCellValue('D1', '订单编号')
// 				->setCellValue('E1', '物流方式')
//              ->setCellValue('F1', '出库日期');
        $k = 2;
        while($item = $this->db->queryRs($query)) {
            $PHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$k, $k-1)
            ->setCellValue('B'.$k, $item['order_barcode'])
            ->setCellValue('C'.$k, $item['goods_info']);
//             ->setCellValue('D'.$k, $item['goods_no'])
//             ->setCellValue('E'.$k, $item['transport'])
//             ->setCellValue('F'.$k, $item['out_time']);
            $k++;
        }
        
        // set wdith
        $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(70);
//         $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
//         $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
//         $PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        
        // set align
        $PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(TRUE);
        
        $filename = 'wsocheck.'.date('His',time()).'.xls';
        $PHPExcel->getActiveSheet()->setTitle('wsocheck');
        $PHPExcel->setActiveSheetIndex(0);
        
        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_Excel5($PHPExcel, 'Excel5');
        $PHPWriter->save('php://output');
    }
    
    public function pagination(){
        if(!isset($_GET['startDate']) || !isset($_GET['endDate']) || !isset($_GET['wh'])){
            return false;
        }else{
            $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']):50;
        }
        
        $sql = "SELECT `id` FROM `app_wsocheck`";
        $sql .= " WHERE `out_time` BETWEEN '{$startDate}' AND '{$endDate}';";
        $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';
                    }
                    $start = substr($startDate, 0, 10);
                    $end = substr($endDate, 0, 10);
                    $param = "&now={$i}&startDate={$start}&endDate={$end}&pnsn=50";
                    $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('50', '100', '250', '500');
        for($i=0; $i<4; $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 deleteOrder(){
        if(isset($_GET['id']) && !empty($_GET['id'])){
        	$id = $this->common->my_filter_input($_GET['id']);
        }else{
            return false;
        }
        
        $sql = "DELETE FROM  `app_wsocheck` WHERE `id`='{$id}';";
        if($this->db->querySql($sql)){
            return 1;
        }else{
            return -1;
        }
    }
    
    public function addNewOrder(){
    	if(!isset($_POST['barcode']) || empty($_POST['barcode'])){
    		return false;
    	}else{
    		$barcode = $_POST['barcode'];
    	}
    	
    	$sql = "SELECT `goods_no`,`goods_info`,`transport` FROM `app_wigsout` WHERE `order_barcode`='{$barcode}';";
    	$query = $this->db->querySql($sql);
    	$rs = $this->db->queryRs($query);
    	
    	if(empty($rs)){
    		return -1;
    	}
    	$sql = "INSERT INTO `app_wsocheck`(`order_barcode`,`goods_no`,`goods_info`,`transport`) VALUES ('{$barcode}','{$rs['goods_no']}','{$rs['goods_info']}','{$rs['transport']}');";
    	if(!($this->db->querySql($sql))){
    		return -2;
    	}else{
    		return 1;
    	}
    }
    
    public function __destruct(){
        //TODO
    }
}