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 .= '';
$line .= "{$item['oid']}";
$line .= "{$item['order_barcode']}";
$line .= "{$item['order_sn']}";
$line .= "{$item['goods_no']}";
$line .= "{$item['goods_info']}";
$line .= "{$item['create_time']}";
$line .= "{$order_status}";
$line .= "{$out_time}";
if(!empty($key) && $key==$this->skey){
if($item['order_status'] == '1'){
$line .= "";
}else{
$line .= "暂无";
}
}
$line .= '
';
}
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 .= '';
$line .= "{$item['oid']}";
$line .= "{$item['order_barcode']}";
$line .= "{$item['order_sn']}";
$line .= "{$item['goods_no']}";
$line .= "{$item['goods_info']}";
$line .= "{$item['create_time']}";
$line .= "{$order_status}";
$line .= "{$out_time}";
if(!empty($key) && $key==$this->skey){
if($item['order_status'] == '1'){
$line .= "";
}else{
$line .= "暂无";
}
}
$line .= '
';
}
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, '"') . '",';
}
}
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 = '共 '.$num.' 页';
if($num > 1){
$pagination .= ($num>10)?"<<":'';
$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 .= "".$i."";
}
}
$pagination .= ($num>10)?">>":'';
}
$pagination .= "展示 记录";
return $pagination;
}
public function __destruct(){
//TODO
}
}