IMPORT CSV

<?php
namespace App\Http\Controllers;

use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Input;

use App\Http\Controllers\Controller;
use App\Models\DailyStockControlHeader;
use Illuminate\Http\Request;
use App\Models\SpecialOrder;

//solehin add
use App\Models\OutletsModel;
use App\Models\OutletGroupModel;
use App\Models\ProductModel;
use App\Models\StockCategoryModel;
use App\Models\RoutesModel;
use App\Models\PriceGroup;
use App\Models\PriceList;
use App\Models\DVS_Header;
use App\Models\DvsDetailsModel as DVS_Detail;
use App\Models\SpecialOrderItem;
use Rap2hpoutre\FastExcel\FastExcel;
use App\Jobs\ProcessCsvUpload;
use App\Models\GlobalSettings;

class DespatchController extends Controller
{


    public function fileStoreExcel(Request $request)
    {

        $folderPath = GlobalSettings::get();

        $fileTblOutlets                    = resource_path($folderPath[0]->import_folder_path.'/SBIntCustTableARV.csv');
        $fileTblOutletGroup                = resource_path($folderPath[0]->import_folder_path.'/SBIntCustGrpARV.csv');
        $fileTblCustPriceCode              = resource_path($folderPath[0]->import_folder_path.'/SBIntCustPriceCodeARV.csv');

        $fileTblProduct                    = resource_path($folderPath[0]->import_folder_path.'/SBIntInventTableARV.csv');
        $fileTblProd_ActualTray            = resource_path($folderPath[0]->import_folder_path.'/SBIntInventTableActualTrayARV.csv');
        $fileTblProductUnitPrice           = resource_path($folderPath[0]->import_folder_path.'/SBIntInventPriceARV.csv');
        $fileTblProductCat                 = resource_path($folderPath[0]->import_folder_path.'/SBIntInventCatARV.csv');

        $fileTblPriceCode                  = resource_path($folderPath[0]->import_folder_path.'/SBIntPriceCodeMasterARV.csv');
        $fileTblPriceGroup                 = resource_path($folderPath[0]->import_folder_path.'/SBIntInventPriceGroupARV.csv');

        $fileTblSalesRoute                 = resource_path($folderPath[0]->import_folder_path.'/SBIntSalesRouteARV.csv');

        $fileTblForecast                   = resource_path($folderPath[0]->import_folder_path.'/t-customer_recomqty.csv');
        $fileTblDVSLine                    = resource_path($folderPath[0]->import_folder_path.'/SBCustItemForecastLineARV.csv');
        $fileTblDVSHeader                  = resource_path($folderPath[0]->import_folder_path.'/SBCustItemForecastTableARV.csv');

        if(!empty($fileTblOutlets))
        {
            $this->readInsertTableOutlets($fileTblOutlets);
        }

        if(!empty($fileTblOutletGroup))
        {
            $this->readInsertTableOutletGroup($fileTblOutletGroup);
        }

        if(!empty($fileTblCustPriceCode))
        {
            $this->readInsertTableCustPriceCode($fileTblCustPriceCode);
        }

        if(!empty($fileTblProduct))
        {
            $this->readInsertTableProduct($fileTblProduct);
        }

        if(!empty($fileTblProd_ActualTray))
        {
            $this->readInsertTableProduct_ActualTray($fileTblProd_ActualTray);
        }

        if(!empty($fileTblProductUnitPrice))
        {
            $this->readInsertTableProductUnitPrice($fileTblProductUnitPrice);
        }

        if(!empty($fileTblProductCat))
        {
            $this->readInsertTableProductCat($fileTblProductCat);
        }

        if(!empty($fileTblPriceCode))
        {
            $this->readInsertTablePriceCode($fileTblPriceCode);
        }

        if(!empty($fileTblPriceGroup))
        {
            $this->readInsertTablePriceGroup($fileTblPriceGroup);
        }

        if(!empty($fileTblSalesRoute))
        {
            $this->readInsertTableSalesRoute($fileTblSalesRoute);
        }

        if(!empty($fileTblDVSHeader))
        {
            $this->readInsertTableDVSHeader($fileTblDVSHeader);
        }

        if(!empty($fileTblDVSLine))
        {
            $this->readInsertTableDVSLine($fileTblDVSLine);
        }

        // if(!empty($fileTblForecast))
        // {
        //     $this->readInsertTableForecast($fileTblForecast);
        // }

    }

    public function readInsertTableOutlets($file)
    {
        // insert in table outlets
       (new FastExcel)->import($file, function ($line) {

            if($line['PAYMTERMID'] == '1')
            {
                $payment_method = 'C';
            }else{
                $payment_method = 'R';
            }

            OutletsModel::UpdateOrCreate([
                'outlet_code'    => $line['ACCOUNTNUM']
             ],
             [
                'outlet_name'    => $line['NAME'],
                'outlet_group'   => $line['CUSTGROUP'],
                'segment'        => $line['SEGMENTID'],
                'subsegment'     => $line['SUBSEGMENTID'],
                'address1'       => $line['ADDR1'],
                'address2'       => $line['ADDR2'],
                'address3'       => $line['ADDR3'],
                'address4'       => $line['ADDR4'],
                'phone'          => $line['PHONE'],
                'fax'            => $line['TELEFAX'],
                'route_code'     => $line['AREA'],
                'payment_method' => $payment_method,
                'short_name'     => $line['ShortName'],
            ]);

        });
    }


    public function readInsertTableOutletGroup($file)
    {
         // insert in table outlets group
        (new FastExcel)->import($file, function ($line) {

            OutletGroupModel::UpdateOrCreate([
                'outlet_group'   => $line['CHAINID'],
                'group_name'     => $line['DESCRIPTION']
             ],
             [
                'created_at'                  => $line['STARTDATETIME'],
                'invoice_by_product_brand'    => '1',
            ]);


            });
    }

    public function readInsertTableCustPriceCode($file)
    {
      // update table outlets - price group code
        (new FastExcel)->import($file, function ($row) {

            OutletsModel::where('outlet_code' , $row['CUSTOMERCODE'])
                 ->update([
                     'price_group_code' => $row['PRICECODE']
                     ]);

        });
    }

    public function readInsertTableProduct($file)
    {
       // insert in table product
        $product = (new FastExcel)->import($file, function ($row) {

            ProductModel::UpdateOrCreate([
                'product_code' => $row['ITEMCODE']
             ],
             [
                'description'           => $row['DESCRIPTION'],
                'name'                  => $row['NAME'],
                'brand'                 => 'AOQI',
                'uom'                   => $row['UOM'],
                'stock_category_code'   => $row['CATEGORY'],
                'parent_product_code'   => $row['ITEMCODE'],
                'subcategory'           => $row['SUBCATEGORY'],
            ]);

        });
    }

    public function readInsertTableProduct_ActualTray($file)
    {
        // update table product - update actual tray
        (new FastExcel)->import($file, function ($row) {

        ProductModel::where('product_code' , $row['ItemCode'])
                ->update([
                    'actual_tray' => $row['Conv_Pack']
                    ]);
        });
    }

    public function readInsertTableProductUnitPrice($file)
    {
        // update table product - update unit price
        (new FastExcel)->import($file, function ($row) {

            ProductModel::where('product_code',$row['ITEMCODE'])
                 ->update([
                     'unit_price'=> $row['UNITPRICE']
                     ]);

        });
    }

    public function readInsertTableProductCat($file)
    {
        //need to insert in table stock category
        (new FastExcel)->import($file, function ($row) {

        StockCategoryModel::UpdateOrCreate([
            'code' => $row['CATEGORY']
            ],
            [
            'description'      => $row['DESCRIPTION'],
            'is_active'        => '1',
        ]);

        });
    }

    public function readInsertTablePriceCode($file)
    {
         // need to insert in table price group
         (new FastExcel)->import($file, function ($row) {

            PriceGroup::UpdateOrCreate([
                'code' => $row['PRICECODE']
             ],
             [
                'description'      => $row['DESCRIPTION'],
                'is_active'        => '1',
            ]);

         });
    }

    public function readInsertTablePriceGroup($file)
    {
         //fifth , need to insert in table price list
         (new FastExcel)->import($file, function ($row) {
            return PriceList::create([
                'product_code'            => $row['ITEMCODE'],
                'unit_price'              => $row['UNITPRICE'],
                'code'                    => $row['PRICECODE']
                ]);
            });
    }

    public function readInsertTableSalesRoute($file)
    {
        //then , need to insert in table route
        (new FastExcel)->import($file, function ($row) {

            RoutesModel::UpdateOrCreate([
                'route_code' => $row['ROUTEID']
             ],
             [
                'route_description'             => $row['DESCRIPTION'],
                'created_at'                    => $row['STARTDATETIME'],
                'activity_type'                 => '1',
            ]);

            });
    }

    public function readInsertTableDVSHeader($file)
    {

       (new FastExcel)->import($file, function ($row) {

            if($row['DOCTYPE'] == 'DIR' || $row['DOCTYPE'] == 'DIM')
            {

                SpecialOrder::UpdateOrCreate([
                    'order_no' => $row['CUSTORDERNUM']
                ],
                [
                    'order_date'         => $row['ORDERDATE'],
                    'delivery_date'      => $row['DELIVERYDATE'],
                    'route_code'         => $row['SALESROUTEID'],
                    'outlet_code'        => $row['CUSTACCOUNT'],
                    'order_amount'       => $row['SALESAMOUNT'],
                ]);

            }elseif ($row['DOCTYPE'] == "DVS") {

                DVS_Header::UpdateOrCreate([
                    'order_no' => $row['CUSTORDERNUM']
                ],
                [
                    'order_date'         => $row['ORDERDATE'],
                    'delivery_date'      => $row['DELIVERYDATE'],
                    'route_code'         => $row['SALESROUTEID'],
                    'outlet_code'        => $row['CUSTACCOUNT'],
                    'order_amount'       => $row['SALESAMOUNT'],
                ]);

            }

         });
    }

    public function readInsertTableDVSLine($file)
    {

        (new FastExcel)->import($file, function ($row) {

            //checking if order no is same as dvs header (order no)
            //then can insert in table dvs detail

            if($row['DOCTYPE'] == 'DVS')
            {

                $data = DVS_Header::select('id' , 'order_no')
                ->where('order_no', '=', $row['SALESID'])
                ->get();

                if( $data != '' )
                {
                    $record = DVS_Detail::create([
                        'order_no'           => $row['SALESID'],
                        'order_quantity'     => $row['SALESQTY'],
                        'product_code'       => $row['ITEMID'],
                        'dvs_id'             => $data[0]->id
                        ]);

                }

            }elseif ( $row['DOCTYPE'] == 'DIR' || $row['DOCTYPE'] == 'DIM') {

                    $data = SpecialOrder::select('id' , 'order_no')
                    ->where('order_no', '=', $row['SALESID'])
                    ->get();

                    if($data != '')
                    {
                        $record = SpecialOrderItem::create([
                         //   'order_no'   => $row['SALESID'],
                            'order_quantity'     => $row['SALESQTY'],
                            'product_code'       => $row['ITEMID'],
                            'special_order_id'   => $data[0]->id
                            ]);
                    }
            }

            });
    }

    public function testing()
    {
        $a = DB::connection()->getPDO();
        $path = file($request->file('file')->getRealPath());

           Excel::filter('chunk')->load($path)->chunk(500, function($rows){
             foreach($rows as $row) {
                 $data = collect([]);
             }
         });
    }

    public function readInsertTableForecast($file)
    {

         Excel::load($file, function($reader) {
          foreach ($reader->skipRows(0)->toArray() as $row) {

                $dataSet[] = [
                    'parent_route_code'        => $row['route'],
                    'outlet_code'              => $row['pptid'],
                    'product_code'             => $row['productid'],
                    'plan_issue_date'          => $row['orderdeliverydate'],
                    'final_recom_issue_qty'    => $row['planload'],
                ];

        }

        if(!empty($dataSet))
        {
           DB::table('forecast_issue')->insert($dataSet);
        }

      });

    }

}