Untitled

 public function actionImport()
    {

        // Yii::error("Lakukan import product bulks");
        $connection = \Yii::$app->db;
        $out = ['success' => 0, 'data' => "", 'message' => ""];
        $post = Yii::$app->request->post();
        $file = $_FILES['file'];
        // var_dump($file);
        // die;
        $file = $file['tmp_name'];

        $filesize =  filesize($file) / 1000;


        $session = Yii::$app->session;

        $accountId =  $session->get("accountId");





        // Yii::error("nilai storePdp");
        // Yii::error($storePdp);
        // Yii::error("end nilai storePdp");


        if ($filesize > 5000) {
            echo json_encode([
                'filesize' => $filesize,
                'data' => 0,
                'cek_count' => 0,
                'total_data' => 0,
                'total_sukses' => 0,
                'total_error' => 0
            ]);
            die;
        }

        $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);
        $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        $objReader2 = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
        $objPHPExcel2 = $objReader2->load($file);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $rowData = [];
        $images = [];

        $transaction = $connection->beginTransaction();
        $cek_count = true;
        $total_data = 0;
        $total_sukses = 0;
        $total_error = 0;
        try {
            $num = $objPHPExcel->getSheetCount(); //get number of sheets
            $sheetnames = $objPHPExcel->getSheetNames(); //get sheet names
            for ($i = 0; $i < 1; $i++) { //0
                $sheet = $objPHPExcel->getSheet($i);
                $highestRow = $sheet->getHighestRow();
                $highestColumn = $sheet->getHighestColumn();

                // $total_data=$highestRow;
                // break;

                if ($highestRow > 1003) {
                    $cek_count = false;
                    $total_data = $highestRow - 3;
                    break;
                }

                // return $num;
                // die;

                //$row is start 2 because first row assigned for heading.  



                for ($row = 4; $row <= $highestRow; $row++) {
                    // Yii::error("highestRow");
                    // Yii::error($highestRow);
                    // Yii::error("end highestRow");
                    $array_excel = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                    $data_tamp = $array_excel[0];
                    $count = 0;
                    $error = "";

                    $images = [];
                    if ($data_tamp[1] == "") {
                        $error .= "Barcode kosong,";
                        $count++;
                    }




                    if ($data_tamp[2] == "") {
                        $error .= "PDP StartDate Kosong,";
                        $count++;
                    }

                    if ($data_tamp[3] == "") {
                        $error .= "PDP End Date Kosong,";
                        $count++;
                    }

                    if ($data_tamp[4] == "") {
                        $error .= "Store Wajib di isi,";
                        $count++;
                    }

                    $new_site_id = [];

                    $store = $data_tamp[4];
                    if ($store == null) {
                        $error .= "Pilih Store!, ";
                    } else if (strtoupper($store) == "SEMUA") {
                        $select_store = Store::find()->where(['accountId' => $accountId])->andWhere(['active' => 1])->all();
                        foreach ($select_store as  $item) {
                            array_push($new_site_id, $item['id']);
                        }
                    } else if ($store != null && strtoupper($store) != "SEMUA") {
                        $data_store_arr = explode("-", $store);
                        foreach ($data_store_arr as $item) {
                            if ($item != "" && $item != null) {
                                array_push($new_site_id, $item);
                            }
                        }
                    }

                    if (count($new_site_id) == 0) {
                        $error .= "Store Not Found";
                        $count++;
                    }


                    $pdpStartDate = @$data_tamp[2]; // Nilai baru yang akan dimasukkan
                    $pdpEndDate    = @$data_tamp[3]; // Nilai baru yang akan dimasukkan


                    // Konversi nilai Excel ke format tanggal
                    $date1 = DateTime::createFromFormat('Y-m-d', '1900-01-01')->modify('+' . ($pdpStartDate - 2) . ' days');

                    // Ubah format ke format yang diinginkan, misalnya 'Y-m-d'
                    $formattedDate1 = $date1->format('Y-m-d');

                    $date2 = DateTime::createFromFormat('Y-m-d', '1900-01-01')->modify('+' . ($pdpEndDate - 2) . ' days');

                    // Ubah format ke format yang diinginkan, misalnya 'Y-m-d'
                    $formattedDate2 = $date2->format('Y-m-d');


                    if ($formattedDate1 > $formattedDate2) {
                        $error .= "PDP EndDate Harus Lebih Besar,";
                        $count++;
                    }

                    // validasi barcode
                    $product_check_barcode = (new \yii\db\Query())
                        ->select([
                            'p.name',
                            'p.barcode',
                            's.location',
                            'ps.pdpStartDate',
                            'ps.pdpEndDate',
                            'ps.productId',
                        ])
                        ->from('product_store ps')
                        ->leftJoin('product p', 'p.id = ps.productId')
                        ->leftJoin('store s', 's.id = ps.storeId')
                        ->where(['p.accountId' => $accountId, 'p.barcode' => str_replace("'","",$data_tamp[1])])
                        ->andWhere(['IN', 's.id', $new_site_id]);

                    // if ($storePdp == "all") {
                    // $product_check_barcode->andWhere(['IN', 's.id', $new_site_id]);
                    // } else {
                    //     $product_check_barcode->andWhere(['s.id' => $storePdp]);
                    // }

                    $productStore = $product_check_barcode->one();
                    if ($productStore == null) {
                        $barcode = $data_tamp[1];
                        $error .= "Barcode $barcode Not Found ";
                        $count++;
                    }



                    // Tampilkan tanggal dalam format yang sudah diubah
                    // echo $formattedDate1;

                    // Yii::error("start pdpEndDate");
                    // Yii::error($formattedDate1);
                    // Yii::error("end pdpEndDate");

                    // Yii::error("start pdpEndDate");
                    // Yii::error($formattedDate2);
                    // Yii::error("end pdpEndDate");

                    // Yii::error("nilai error ");
                    // Yii::error($error);
                    // Yii::error("end error");

                    if ($error != "") {
                        $total_data++;
                        $total_error++;

                        // upate nilai
                        $objPHPExcel2->getSheetByName('ISI Template di sini')->setCellValueByColumnAndRow(1, $row, $error);
                        continue;
                    } else {
                        // Buat query untuk melakukan pembaruan

                        // if ($storePdp == "all") {
                        Yii::$app->db->createCommand()
                            ->update('product_store', ['pdpStartDate' => "$formattedDate1", 'pdpEndDate' => "$formattedDate2"], ['AND', ['IN', 'storeId', $new_site_id], ['productId' => $productStore['productId']]])
                            ->execute();
                        // } else {
                        // Yii::$app->db->createCommand()
                        //     ->update('product_store', ['pdpStartDate' => "$formattedDate1", 'pdpEndDate' => "$formattedDate2"], ['storeId' => $storePdp, 'productId' => $productStore['productId']])
                        //     ->execute();
                        // }
                        $total_data++;
                        $total_sukses++;
                        $objPHPExcel2->getSheetByName('ISI Template di sini')->setCellValueByColumnAndRow(1, $row, "Sukses");
                    }
                }
            }


            $transaction->commit();
        } catch (Exception $e) {
            $transaction->rollBack();
            Yii::error("start error import pdp");
            Yii::error($e->getMessage());
            Yii::error("end error import pdp");
        }

        unset($transaction);
        $connection->close();
        $connection = null;
        unset($connection);

        // unlink($file);
        // echo json_encode($data_tamp[0][1]);
        $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objPHPExcel2, 'Xlsx');
        $objWriter->save(\Yii::$app->basePath . "/web/template/TemplatePromoPDP_" . $post['accountId'] . ".xlsx");
        echo json_encode([
            'filesize' => $filesize,
            'cek_count' => $cek_count,
            'total_data' => $total_data,
            'total_sukses' => $total_sukses,
            'total_error' => $total_error,
        ]);

        die;
        // echo json_encode($storeId);
    }