mgc.longyilive.com/controllers/SiteController.php

<?php

namespace app\controllers;

use Yii;
use app\models\Orderreturn;
use app\models\OrderreturnSearch;
use app\models\UploadExcel;
use app\models\ExcelFilter;
use yii\web\UploadedFile;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use app\models\LoginForm;

/**
 * SiteController implements the CRUD actions for Orderreturn model.
 */
class SiteController extends Controller
{
    /**
     * {@inheritdoc}
     */
    public function behaviors()
    {
        return [
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],
        ];
    }
    public function actionError()
{
    $exception = Yii::$app->errorHandler->exception;
    if ($exception !== null) {
        return $this->render('error', ['exception' => $exception,'url'=>'index']);
    }
}

    /**
     * Lists all Orderreturn models.
     * @return mixed
     */
    public function actionIndex()
    {
        
        $request = Yii::$app->request;
        $shop=$request->get('shop',1);
        $searchModel = new OrderreturnSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'shop'=>$shop,
            'dayStatis'=>$dayStatis,
            'dayCount'=>$dayCount,
            'monthStatis'=>$monthStatis,
            'monthCount'=>$monthCount
        ]);
    }
    public function actionStatis(){
        if (Yii::$app->user->isGuest) {
            return $this->redirect(['login']);
        }
        $request = Yii::$app->request;
        $shop=$request->get('shop',1);
        $dayStatis=Orderreturn::find()
                ->select([ 'usage','count(*) as num'])
                ->groupBy('usage')
                ->where(['>','created_at',strtotime(date('Y-m-d'))])
                ->where(['shop'=>$shop])
                ->asArray()
                ->all();
        $dayCount=array_sum(array_column($dayStatis,'num'));
        $monthStatis=Orderreturn::find()
            ->select(['count(*) as num', 'usage'])
            ->groupBy('usage')
            ->where(['>','created_at',strtotime(date('Y-m'))])
            ->where(['shop'=>$shop])
            ->asArray()
            ->all();
        $monthCount=array_sum(array_column($monthStatis,'num'));
        return $this->render('statis', [
            'shop'=>$shop,
            'dayStatis'=>$dayStatis,
            'dayCount'=>$dayCount,
            'monthStatis'=>$monthStatis,
            'monthCount'=>$monthCount
        ]);
    }
    public function actionStatisExport(){
        $request = Yii::$app->request;
        $shop=$request->get('shop',1);
        $dayStatis=Orderreturn::find()
                ->select([ 'usage','count(*) as num'])
                ->groupBy('usage')
                ->where(['>','created_at',strtotime(date('Y-m-d'))])
                ->where(['shop'=>$shop])
                ->asArray()
                ->all();
        $dayCount=array_sum(array_column($dayStatis,'num'));
        $monthStatis=Orderreturn::find()
            ->select(['count(*) as num', 'usage'])
            ->groupBy('usage')
            ->where(['>','created_at',strtotime(date('Y-m'))])
            ->where(['shop'=>$shop])
            ->asArray()
            ->all();
        $monthCount=array_sum(array_column($monthStatis,'num'));
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
        $spreadsheet->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30);
        //当日统计
        $spreadsheet->setActiveSheetIndex(0)->mergeCells('A1:B1');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1','当天退货数量统计 单位:个');
        $spreadsheet->setActiveSheetIndex(0)->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A2','退货包裹数');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('B2',$dayCount);
        $i=3;
        $usageType=['1' => "可用-请加库存",'2'=>'不可用-退回工厂','3'=>'精打理-退回国内','4'=>'精打理-留美仓','5'=>'泡发-发回国内'];
        foreach($dayStatis as $item){
            if(isset($usageType[$item['usage']])){
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.$i,$usageType[$item['usage']]);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('B'.$i,$item['num']);
            }
            $i++;
        }

        $i+=2;
        //当月统计
        $spreadsheet->setActiveSheetIndex(0)->mergeCells('A'.$i.':B'.$i);
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.$i,'当月退货数量统计 单位:个');
        $spreadsheet->setActiveSheetIndex(0)->getStyle('A'.$i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        
        $i++;
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.$i,'退货包裹数');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('B'.$i,$dayCount);
        $i++;
        foreach($monthStatis as $item){
            if(isset($usageType[$item['usage']])){
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.$i,$usageType[$item['usage']]);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('B'.$i,$item['num']);
            }
            $i++;
        }
        $spreadsheet->setActiveSheetIndex(0)->getStyle('B1:B'.$i)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('Simple');
        // 
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        $filename=($shop==1 ? "美国仓":"亚马逊")."退货订单".date("Ymd").".xlsx";
        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;







    }
    public function actionOrderExport(){
        $request = Yii::$app->request;
        $id = $request->post('id'); 
        if(!is_array($id)){
            throw new \yii\base\ErrorException('参数异常');
        }
        $ids=implode(',',$id);
        $orders=Orderreturn::find()
        ->select(['*'])
        ->where('id in (' . $ids . ')')
        ->asArray()
        ->all();

        $spreadsheet = new Spreadsheet();
        //表头
        $spreadsheet->setActiveSheetIndex(0)->mergeCells('A1:L1');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1','美国店退货收货详情统计表');

        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A2','序号');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('B2','收货日期');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('C2','退回货运');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('D2','收到单号');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('E2','网站');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('F2','订单号');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('G2','订单编号');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('H2','客户名');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('I2','原订单货物详情');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('J2','货品详情');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('K2','完好程度/使用情况');
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('L2','备注');
        $i=1;
        $usageType=['1' => "可用-请加库存",'2'=>'不可用-退回工厂','3'=>'精打理-退回国内','4'=>'精打理-留美仓','5'=>'泡发-发回国内'];
        $deliveryType=['1' =>'USPS','2'=>'UPS','3'=>'FedEx','4'=>'客人到店退货','5'=>'DHL' ];
        foreach($orders as $item){
            // if(isset($usageType[$item['usage']])){
                $delivery=isset($deliveryType[$item['delivery']])?$deliveryType[$item['delivery']]:$item['delivery'];
                $usage=isset($usageType[$item['usage']])?$usageType[$item['usage']]:$item['usage'];
                
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('A'.($i+2),$i);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('B'.($i+2),date('Y-m-d',$item['receiptdate']));
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('C'.($i+2),$delivery);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('D'.($i+2),$item['deliveryid']);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('E'.($i+2),$item['website']);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('F'.($i+2),'');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('G'.($i+2),'');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('H'.($i+2),$item['customer']);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('I'.($i+2),$item['origin_goodsinfo']);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('J'.($i+2),$item['goodsinfo']);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('K'.($i+2),$usage);
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('L'.($i+2),$item['mark']);
            // }
            $i++;
        }

       
        $spreadsheet->getActiveSheet()->setTitle('美国店退货');
        // 
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        $filename=($shop==1 ? "美国仓":"亚马逊")."退货订单".date("Ymd").".xlsx";
        // Redirect output to a client’s web browser (Xlsx)
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        // header('Content-Disposition: attachment;filename="'.$filename.'"');
        // header('Cache-Control: max-age=0');
        // // If you're serving to IE 9, then the following may be needed
        // header('Cache-Control: max-age=1');
        
        // // If you're serving to IE over SSL, then the following may be needed
        // header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        // header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        // header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        // header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        ob_start();
       $writer->save('php://output');
       $xlsData = ob_get_contents();
        ob_end_clean();
        $response =  array(
            'op' => 'ok',
            'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
        );
    
    die(json_encode($response));


    }

    /**
     * Displays a single Orderreturn model.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionView($id)
    {
        $is_create=false;
        $headers = Yii::$app->request->headers;
        $referer = $headers->get('Referer');
        if(strpos($referer,'site/create')>-1){
            $is_create=true;
        }
        return $this->render('view', [
            'model' => $this->findModel($id),
            'is_create'=>$is_create,
        ]);
    }

    /**
     * Creates a new Orderreturn model.
     * If creation is successful, the browser will be redirected to the 'view' page.
     * @return mixed
     */
    public function actionCreate()
    {
        $request = Yii::$app->request;
        $shop = $request->getBodyParam('shop')??1;
        $model = new Orderreturn();
        
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['view', 'id' => $model->id]);
        }
        return $this->render('create', [
            'model' => $model,
            'shop'=>$shop,
        ]);
    }

    /**
     * Updates an existing Orderreturn model.
     * If update is successful, the browser will be redirected to the 'view' page.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionUpdate($id)
    {
        $model = $this->findModel($id);

        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['view', 'id' => $model->id]);
        }
        return $this->render('update', [
            'model' => $model,
        ]);
    }

    /**
     * Deletes an existing Orderreturn model.
     * If deletion is successful, the browser will be redirected to the 'index' page.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionDelete($id)
    {
        $this->findModel($id)->delete();
      


        return $this->redirect(['index']);
    }

    public function actionDeleteAll()
    {
        $request = Yii::$app->request;
        $id = $request->post('id');   
        if(is_array($id)){
            $ids=implode(',',$id);
            $model = new Orderreturn(); 
            $model->deleteAll('id in (' . $ids . ')');
        }
        return $this->redirect(['index']);
    }
    public function actionImport(){
        
        $request = Yii::$app->request;
        $shop = $request->post('shop')??1;
        $model = new UploadExcel();
        if (Yii::$app->request->isPost) {
            $model->excel = UploadedFile::getInstance($model, 'excel');
            if ($inputFileName=$model->upload()) {
                $inputFileType = IOFactory::identify($inputFileName);
                $filterSubset = new ExcelFilter(3, 15, range('B', 'K'));
                $reader = IOFactory::createReader($inputFileType);
                $reader->setReadFilter($filterSubset);
                $spreadsheet = $reader->load($inputFileName);
                $spreadsheet->setActiveSheetIndex(0);
                $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
                $key=['receiptdate','num','delivery','deliveryid','website','customer','origin_goodsinfo','goodsinfo','usage','mark','created_at','shop'];
                $data=[];

                foreach($sheetData as $item){
                    if(!isset($item['E'])||empty($item['E'])){
                        continue;
                    }
                    $receiptdate=str_replace('.','-',$item['B'])??'';
                    $data[]=[
                        strtotime($receiptdate),
                        $item['C'],
                        $item['D'],
                        $item['E'],
                        $item['F'],
                        $item['G'],
                        $item['H'],
                        $item['I'],
                        $item['J'],
                        $item['k'],
                        time(),
                        $shop,
                    ];
                }
                if(empty($data)){
                    throw new \yii\base\ErrorException('未读取到数据');
                }

                $res=Yii::$app->db->createCommand()->batchInsert('order_return', $key, $data)->execute();
                if(!$res){
                    throw new \yii\base\ErrorException('数据库异常');
                    return;
                }else{
                    return $this->redirect(['index']);
                }
            }else{
                throw new \yii\base\ErrorException('上传文件失败');
                return;
            }
        }else{
            throw new \yii\web\NotFoundHttpException('错误请求');
        }
        return $this->render('upload', ['model' => $model]);
    }



    /**
     * Login action.
     *
     * @return Response|string
     */
    public function actionLogin()
    {
        if (!Yii::$app->user->isGuest) {
            return $this->goHome();
        }

        $model = new LoginForm();
        if ($model->load(Yii::$app->request->post()) && $model->login()) {
            return $this->goBack();
        }

        $model->password = '';
        return $this->render('login', [
            'model' => $model,
        ]);
    }
      /**
     * Logout action.
     *
     * @return Response
     */
    public function actionLogout()
    {
        Yii::$app->user->logout();

        return $this->goHome();
    }

    /**
     * Finds the Orderreturn model based on its primary key value.
     * If the model is not found, a 404 HTTP exception will be thrown.
     * @param integer $id
     * @return Orderreturn the loaded model
     * @throws NotFoundHttpException if the model cannot be found
     */
    protected function findModel($id)
    {
        if (($model = Orderreturn::findOne($id)) !== null) {
            return $model;
        }

        throw new NotFoundHttpException('The requested page does not exist.');
    }
}