<?php namespace App\Services;

use App\Exceptions\NoDetailDataForEpicorMultiKey;
use Doctrine\DBAL\Exception\DriverException;
use Illuminate\Support\Facades\Session;
use Log;
use PDO;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Savvior\Doctrine\EMContainer;
use stdClass;

class EpicorDatabase
{
    /** @var \Doctrine\DBAL\Connection  */
    private $connRO;
    private $wipDetailsCache;
    private $fgDetailsCache;
    private $processedOtherDeatilsCache;

    /**
     * @param EMContainer $EMs
     */
    public function __construct(EMContainer $EMs)
    {
        $this->connRO = $EMs->getEM('epicor_ro')->getConnection();
    }

    /**
     * @param string $CustID
     * @return array|bool
     */

    public function getCurrentDatabase()
    {
       /* switch(config('app.env'))
        {
            case "dev":
                return '[VorteqPortal-uat]';
            case "uat":
                return '[VorteqPortal-uat]';
            case "mirror":
                return '[VorteqPortal-mirror]';

            default: //production
                return '[VorteqPortal]';
        } */



        return "[".config("database.connections.ro.database")."]";


    }




    public function getCoilByCoil($job_num, $Customer)
    {
        /*        AND [JobHead].[JobNum] = :LotNumber
		AND [Customer].[CustID] = :Customer*/

        $sql = file_get_contents(__DIR__."/epicoreSQL/CoilByCoil.sql");

        //dd($sql);
      //  dd($job_num,$Customer);

        $query = $this->connRO->prepare($sql);


        //$job_num = '130735';
       // $Customer='SVP';

        $query->bindValue(':JobNum', $job_num);
        $query->bindValue(':JobNum2', $job_num);
        $query->bindValue(':Customer', $Customer);
        $query->bindValue(':Customer2', $Customer);

        try {
            $result = $query->executeQuery();
            $results = $result->fetchAllAssociative();
            if (sizeof($results) >0)
            {
                return $results;
            }
        } catch(DriverException) {}

        return false;
    }


    public function getJobNumberByInvoiceNumber($invoiceNumber)
    {
        $sql ='select TOP 1

	[JobProd].[JobNum] as JO

from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
	InvcHead.Company = InvcDtl.Company
And
	InvcHead.InvoiceNum = InvcDtl.InvoiceNum
 and ( InvcDtl.InvoiceLine = 1  )

inner join Erp.JobProd as JobProd on
	InvcDtl.Company = JobProd.Company
And
	InvcDtl.OrderNum = JobProd.OrderNum
And
	InvcDtl.OrderLine = JobProd.OrderLine
And
	InvcDtl.OrderRelNum = JobProd.OrderRelNum

 where (InvcHead.OrderNum > 0)
 AND InvcHead.InvoiceNum = :invoiceNumber;';

        $query = $this->connRO->prepare($sql);

        $query->bindValue(':invoiceNumber', $invoiceNumber);

        $result = $query->executeQuery();

        $return = $result->fetchAllAssociative();

        if (count($return) == 0)
            return null;
        else
            return $return[0]['JO'];




    }




    public function createGroupCSV($excelFilePath)
    {
        //echo $excelFilePath;

        $return = new stdClass();
        $return->error = false;
        $return->terminal = false;

        if (!file_exists($excelFilePath))
        {
            $return->error = true;
            $return->terminal = true;
            $return->errorCode = "NO_FILE_FOUND";
            $return->errorMessage = "The Excel File Was Not Found.. Please try again";
            return $return;
        }

        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $mimeType =trim(finfo_file($finfo, $excelFilePath));

       // echo $mimeType."\n";

        if (!($mimeType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            || $mimeType == "application/vnd.ms-excel")
        )
        {
            $return->error = true;
            $return->terminal = true;
            $return->errorCode = "BAD_EXCEL_FORMAT";
            $return->errorMessage = "The File provided was not a valid XLS or XLSX format";
            return $return;
        }

        $fileType = 'Xls';

        if ($mimeType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" )
        {
            $fileType = 'Xlsx';
        }

  //      $reader  =

        $reader = IOFactory::createReader($fileType);
       // $reader = \PHPExcel_IOFactory::createReader($fileType);
        if (!$reader->canRead($excelFilePath))
        {
            $return->error = true;
            $return->terminal = true;
            $return->errorCode = "EXCEL_READER_CAN_NOT_OPEN";
            $return->errorMessage = "The attached file can't be opened by the excel reader. It is possibly corrupted.";
            return $return;
        }

        $instReader = $reader->load($excelFilePath);

        $sheet = $instReader->getSheet(0);

        $cell = $sheet->getCell('AB9');

        if (!strstr($cell,'Group ID'))
        {
            $return->error = true;
            $return->terminal = true;
            $return->errorCode = "GROUP_ID_NOT_FOUND";
            $return->errorMessage = "Group ID was not found in expected cell location in the excel file (AB9)";
            return $return;
        }

        $groupID = trim(str_replace(["Group ID:",' ',' '],'',$cell));

        $return->group = $groupID;


        $sql = file_get_contents(__DIR__."/epicoreSQL/AP-CheckProcessing/CheckLines.sql");
        $query = $this->connRO->prepare($sql);

        $query->bindValue(':GROUPID', $groupID);


        try {
            $result = $query->executeQuery();
            $CheckResults = $result->fetchAllAssociative();
            if (sizeof($CheckResults) <1)
            {
                $return->error = true;
                $return->terminal = true;
                $return->errorCode = "NO_CHECK_LINES";
                $return->errorMessage = "The check lines query returned zero results for this gruop: $groupID";
                return $return;
            }
        } catch(DriverException) {
            $return->error = true;
            $return->terminal = true;
            $return->errorCode = "SQL_FAIL";
            $return->errorMessage = "The check lines query failed to execute";
            return $return;
        }


        //LOOKS LIKE WE ARE CLEAR TO MAKE A CSV!



        include(__DIR__."/APCheckProcessing/CreateCSV.php");



        $path = str_replace(basename($excelFilePath),'',$excelFilePath);
        $path = str_replace('excel_upload', 'csv_output', $path).'VORTEQ_IP_'.date("m-d-Y").'_001.CSV';
       ///echo "\nPATH: ";
        //echo $path."\n\n";

        $this->processCSVArray($output,$path );


        if (count($missingAddressRows) > 0)
        {
            $return->error = true;
            $return->terminal = false;
            $return->errorCode = "ADDRESS_ROWS";
            $return->errorMessage = "Address Lines are Missing";

        }
        $return->csvSerialized = $csvSerialized;
        $return->csvRows = $missingAddressRows;
        $return->csv = $path;



        return $return;


    }

    public function processCSVArray(array $ar, $path)
    {

        $newArray = [];

        foreach ($ar as $k => $v)
        {
            $newRow = $v;


            if ($v[0] == 'CHECK')
            {
                /** let's take care of country Codes first */

                //FOR
//Missing address Commonwealth of Pennsylvania
//address needs to be filled in... BUNDLE FIELDS NEED TO BE FILLED
//AN AP
                if ($v[20] != 'USA')
                {

                    //18 state 17 city
                  //  $newRow[17] = trim($v[18])." ".trim($v[17]);

              //      dd($v,$newRow);


                 //   $newRow[18]='FOR';
                    $newRow[20]='FOR';


                }

            }

            $newArray[]=$newRow;


        }

        $fp = fopen($path, 'w+');

        foreach ($newArray as $fields) {
            fputcsv($fp, $fields);
        }

        fclose($fp);

        $a = file_get_contents($path);

        $newCsv = str_replace("\r\n","\n", $a);
        $newCsv = str_replace("\n", "\r\n", $newCsv);



        // $a = explode("\n", $a);
       // $newCsv = implode("\r\n",$a);
        //$newCsv.="\r\n";

       // $newCsv = str_replace('CARRIAGERETURN', "\r\n", $newCsv);


        file_put_contents($path, $newCsv);

    }


    public function getAcknowledgement($customer, $poNumber = null, $orderNum = null )
    {
        $sql=file_get_contents(__DIR__."/epicoreSQL/SalesOrder.sql");





        if (strlen($poNumber) > 0)
            $sql ="$sql  [OrderHed].[PONum] = :PO ";
        else
            $sql ="$sql  [OrderHed].[OrderNum] = :ON ";


        $sql = $sql . " ORDER BY OrderRel_OrderLine;";

        /*	WHERE [OrderHed].[OrderNum] =47950 if clicking on an order number*/
        /*	WHERE  [OrderHed].[PONum] = '550853-OP'  if clicking on an PO number*/

        //		echo($sql);
        //    exit;


        $query = $this->connRO->prepare($sql);

        //dd($poNumber);


        if (strlen($poNumber) > 0)
            $query->bindValue(':PO', $poNumber);
        else
            $query->bindValue(':ON', $orderNum);


        $query->bindValue(":CUST", $customer);


        try {
            $result = $query->executeQuery();
            $results = $result->fetchAllAssociative();


            if (sizeof($results) >0)
            {
                return $results;
            }
        } catch (DriverException) {

        }




        return false;




    }

    public function getPaintCodeDataForPart($partNumber)
    {
        $query = $this->connRO->prepare('
            SELECT TOP 1
                [Part].[PartDescription] AS [Part_PartDescription],
                Part_UD.TopcoatPaintCode_c as [Top Finish],
                Part_UD.BackcoatPaintCode_c as [Bottom Finish]
            FROM Erp.Part AS Part
            INNER JOIN Erp.Part_UD ON Part_UD.ForeignSysRowID = Part.SysRowID
            WHERE Part.PartNum=:PartNumber
        ');

        $query->bindValue(':PartNumber', $partNumber);

        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            if(sizeof($results) > 0) {
                return $results[0];
            }
        } catch(DriverException) {}

        return null;
    }

    public function getLotSearchHistory($lotNum)
    {

        //  dd($lotNum);

        $query = $this->connRO->prepare("SELECT * FROM portal_LotHistory WHERE 	[LotNum]= :LotNum order by  [DateIssued] ASC;");
        $query->bindValue(':LotNum', $lotNum);
        $result = $query->executeQuery();
        $results = $result->fetchAllAssociative();

        //dd($results,$lotNum,"SELECT * FROM portal_LotHistory WHERE [LotNum]= :LotNum order by  [Date Issued] ASC;");




        return $results;
    }




    public function getJobStatusByPlanByCustomer($CustID)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/JobStatusByPlantByCustomer.sql");


     //   dd(config('db.database'));


        $currentDB = $this->getCurrentDatabase();

        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CUSTID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        //$query->execute();


        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            return $results;
        } catch(DriverException) {}

        return false;
    }


    public function storeCompanyInfo($Customers)
    {

        if (Session::has("cacheCustomers"))
            return;


        $custIDs=[];

        foreach ($Customers as $epicoreCustomerID => $v)
        {
            $custIDs[]="'$epicoreCustomerID'";
        }

        $query = $this->connRO->prepare("SELECT Name,CustID
                                         FROM Erp.Customer
                                         WHERE CustID IN (".implode(",",$custIDs).")");


        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            $ar = [];

            foreach ($results as $k => $v)
            {
                $ar[$v["CustID"]]=$v["Name"];
            }


            Session::put('cacheCustomers',$ar);
        } catch(DriverException) {}


    }
    public function reactivateCustomers()
    {
/*
        if (App::environment() === 'prod'):

            $query = $this->connRO->prepare('UPDATE COM
SET COM.IsDeactivated = 0
FROM VorteqPortal.dbo.quest_Company COM
    JOIN Epicor10Live.Erp.Customer CUST
        ON CUST.CustID = COM.EpicorCustID
WHERE COM.IsDeactivated = 1;');

            $query->execute();



        endif;
            */

    }


    public function getCustInfoIfExists($CustID)
    {
        if (Session::has("cacheCustomers"))
        {

            $results=Session::get('cacheCustomers');




            if (isset($results[$CustID]))
                return ["Name" => $results[$CustID]];




        }



        $query = $this->connRO->prepare("SELECT Name
                                     FROM Erp.Customer
                                     WHERE CustID = :custID");
        $query->bindValue(':custID', $CustID);


        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            if (sizeof($results) === 1) {
                return $results[0];
            }
        } catch(DriverException) {}

        return false;

    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryUnprocessedSummaryData($CustID)
    {
        if ($this->_getSubUser()) return [];
        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalUnprocessedInventorySummary] :CustID,:DBNAME");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);

        $result = $query->executeQuery();
        return $result->fetchAllAssociative();
    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryUnprocessedSummaryData_Details($CustID, $PartNumber = null, $Plant = null, $Warehouse = null)
    {
        return $this->getInventoryUnprocessedDetailsData($CustID, $PartNumber, $Plant, $Warehouse);




    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryUnprocessedRRSummaryData($CustID)
    {
        if ($this->_getSubUser()) return [];

        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalUnprocessedInventoryRejectsAndReturnsSummary] :CustID,:DBNAME");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative();
    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryUnprocessedRRSummaryData_Details($CustID, $PartNumber = null, $Plant = null, $Warehouse = null)
    {
        if ($this->_getSubUser()) return [];

        return $this->getInventoryUnprocessedDetailsData($CustID, $PartNumber, $Plant, $Warehouse);

    }

    public function getInventoryWorkInProgressSummaryData($CustID)
    {


        $sub=$this->_isSub();
        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalWorkInProgressInventorySummaryV6] :CustID,:DBNAME,:sub");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result = $query->executeQuery();
        $data =  $result->fetchAllAssociative();


        $sql = "dbo.PortalWorkInProgressInventoryDetailsALLV6 :CustID,:DBNAME,:sub,1";
        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result2 = $query->executeQuery();
        $countData =  $result2->fetchAllAssociative();
        $cs=[];
        foreach ($countData as $k =>$datum)
        {
            $cs[$datum['Plant']."|".$datum['CustID']."|".$datum['VorteqPartNum']."|".$datum['Warehouse']] = $datum['ROWCOUNT'];

        }



        foreach ($data as $k=>$v)
        {
            //do it here
            $key = $v['Plant']."|".$v['CustID']."|".$v['VorteqPartNum']."|".$v['Warehouse'];
            if (isset($cs[$key]))
                $data[$k]['NumResults'] = $cs[$key];
            else
                $data[$k]=null;

        }

        return $data;



    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryFinishedGoodsSummaryData($CustID)
    {


        $sub=$this->_isSub();
        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalFinishedGoodsInventorySummaryV6] :CustID,:DBNAME,:sub");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result = $query->executeQuery();
        $data =  $result->fetchAllAssociative();


        $sql = "dbo.PortalFinishedGoodsInventoryDetailsALLV6 :CustID,:DBNAME,:sub,1";
        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result2 = $query->executeQuery();
        $countData =  $result2->fetchAllAssociative();
        $cs=[];
        foreach ($countData as $k =>$datum)
        {
            $cs[$datum['Plant']."|".$datum['CustID']."|".$datum['VorteqPartNum']."|".$datum['Warehouse']] = $datum['ROWCOUNT'];

        }



        foreach ($data as $k=>$v)
        {
            //do it here
            $key = $v['Plant']."|".$v['CustID']."|".$v['VorteqPartNum']."|".$v['Warehouse'];
            if (isset($cs[$key]))
                $data[$k]['NumResults'] = $cs[$key];
            else
                $data[$k]=null;

        }

        return $data;



    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryProcessedOtherSummaryData($CustID)
    {


        $sub=$this->_isSub();
        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalProcessedOtherInventorySummaryV6] :CustID,:DBNAME,:sub");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result = $query->executeQuery();
        $data =  $result->fetchAllAssociative();


        $sql = "dbo.PortalProcessedOtherInventoryDetailsALLV6 :CustID,:DBNAME,:sub,1";
        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $query->bindValue(':sub', $sub);
        $result2 = $query->executeQuery();
        $countData =  $result2->fetchAllAssociative();
        $cs=[];
        foreach ($countData as $k =>$datum)
        {
            $cs[$datum['Plant']."|".$datum['CustID']."|".$datum['VorteqPartNum']."|".$datum['Warehouse']] = $datum['ROWCOUNT'];

        }



        foreach ($data as $k=>$v)
        {
            //do it here
            $key = $v['Plant']."|".$v['CustID']."|".$v['VorteqPartNum']."|".$v['Warehouse'];
            if (isset($cs[$key]))
                $data[$k]['NumResults'] = $cs[$key];
            else
                $data[$k]=null;

        }

        return $data;



    }


    private function _isSub()
    {
        /** @var \App\Services\QuestPageValues $qpv */
        $qpv = app(\App\Services\QuestPageValues::class);

        if (app()->runningInConsole())
            return 0;


        if ( $qpv->getIsSubUser())
            return 1;
        else
            return 0;

    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryWorkInProgressSummaryData_Details($CustID, $PartNumber, $Plant, $Warehouse)
    {

        return  $this->getInventoryWorkInProgressDetailsData($CustID, $PartNumber , $Plant, $Warehouse);


    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryFinishedGoodsSummaryData_Details($CustID, $PartNumber, $Plant, $Warehouse)
    {

        return  $this->getInventoryFinishedGoodsDetailsData($CustID, $PartNumber , $Plant, $Warehouse);


    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryProcessedOtherSummaryData_Details($CustID, $PartNumber, $Plant, $Warehouse)
    {

        return  $this->getInventoryProcessedOtherDetailsData($CustID, $PartNumber , $Plant, $Warehouse);


    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryProcessedRRSummaryData($CustID)
    {

        if ($this->_getSubUser()) return [];

        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[PortalProcessedInventoryRejectsAndReturnsSummary] :CustID,:DBNAME");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative();
    }

    /**
     * @param string $CustID
     * @return array
     */
    public function getInventoryProcessedRRSummaryData_Details($CustID, $PartNumber = null, $Plant = null, $Warehouse = null)
    {

        return $this->getInventoryProcessedRRDetailsData($CustID, $PartNumber, $Plant, $Warehouse);


    }

    private function _getSubUser()
    {
        /** @var QuestPageValues $QPV */
        $QPV = app(QuestPageValues::class);

        return $QPV->getIsSubUser();

    }


    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */



    public function getInventoryUnprocessedDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {
        if ($this->_getSubUser()) return [];

        $DBNAME = $this->getCurrentDatabase();

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));
        if ($GetAll)
            $sql = "EXEC [dbo].[PortalUnprocessedInventoryDetailsALL] :custID,:DBNAME";
        else
            $sql = "EXEC dbo.[PortalUnprocessedInventoryDetails] :custID, :plant, :part, :warehouse, :DBNAME;";


       // dd($CustID,$Part,$Plant,$Warehouse);


        $query = $this->connRO->prepare($sql);

        $query->bindValue(':custID', $CustID);
        $query->bindValue(':DBNAME', $DBNAME);

        if (!$GetAll) {
            $query->bindValue(':plant', $Plant);
            $query->bindValue(':part', $Part);
            $query->bindValue(':warehouse', $Warehouse);
        }
        $result = $query->executeQuery();

        return $result->fetchAllAssociative();

    }

    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */
    public function getInventoryUnprocessedRRDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {
        if ($this->_getSubUser()) return [];


        $DBNAME = $this->getCurrentDatabase();

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));
        if ($GetAll)
            $sql = "EXEC [dbo].[PortalUnprocessedInventoryRejectsAndReturnsDetailsALL] :custID,:DBNAME";
        else
            $sql = "EXEC dbo.[PortalUnprocessedInventoryRejectsAndReturnsDetails] :custID, :plant, :part, :warehouse, :DBNAME;";


        $query = $this->connRO->prepare($sql);

        $query->bindValue(':custID', $CustID);
        $query->bindValue(':DBNAME', $DBNAME);

        if (!$GetAll) {
            $query->bindValue(':plant', $Plant);
            $query->bindValue(':part', $Part);
            $query->bindValue(':warehouse', $Warehouse);
        }
        $result = $query->executeQuery();

        return $result->fetchAllAssociative();
    }

    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */
    public function getInventoryWorkInProgressDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {

        if (is_array($this->wipDetailsCache)) {
            return $this->wipDetailsCache;
        }

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));

            try {
               /* $sql = file_get_contents(__DIR__."/epicoreSQL/processedInventory.sql");
                $sql = str_replace('~DATABASE_NAME~', '['.\DB::getDatabaseName().']', $sql);


                $Part1 = 'AND ship_RequestDetail.Part = :PART
		                  AND ship_RequestDetail.Warehouse = :WAREHOUSE ';

                $Part2 = 'AND ship_Request.Plant = :PLANT ';

                $Part3 = 'AND PartBin.PartNum = :MAINPART
                          AND Plant.Name = :MAINPLANT
                          AND Warehse.Description = :MAINWH';*/


                $DBNAME = $this->getCurrentDatabase();

                if (!$GetAll)
                {
                    //$sql = str_replace('~~PART1~~', $Part1, $sql);
                    //$sql = str_replace('~~PART2~~', $Part2, $sql);
                    //$sql = str_replace('~~PART3~~', $Part3, $sql);

                    //EXEC dbo.[PortalProcessedInventoryDetails] 'HDM', 'Franklin Park', 'HDM140A14.375-S', 'FP BOP';

                    $sql = "EXEC dbo.[PortalWorkInProgressInventoryDetailsV6] :CUSTID, :PLANT, :PART, :WAREHOUSE, :DBNAME, :sub;";


                  // dd($sql,$CustID,$Plant,$Part,$Warehouse,$DBNAME );



                }
                else
                {

                    $sql = "EXEC dbo.[PortalWorkInProgressInventoryDetailsALLV6] :CUSTID, :DBNAME, :sub;";
                }



                $sub=$this->_isSub();


                $query = $this->connRO->prepare($sql);
                $query->bindValue(':CUSTID', $CustID);
                $query->bindValue(':DBNAME', $DBNAME);
                $query->bindValue(':sub', $sub);

                if(!$GetAll)
                {
                    $query->bindValue(':PLANT', $Plant);
                    $query->bindValue(':PART', $Part);
                    $query->bindValue(':WAREHOUSE', $Warehouse);

                }

                $result = $query->executeQuery();

                $res = $result->fetchAllAssociative();

              //  dd($res);



            } catch (DriverException $e) {
               // dd($e->getMessage());

                if ($GetAll)
                    return [];
                else
                    throw new NoDetailDataForEpicorMultiKey();
            }

        if(!sizeof($res) && !$GetAll)
            throw new NoDetailDataForEpicorMultiKey();



        $this->wipDetailsCache=$res;

        return $res;
    }

    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */
    public function getInventoryFinishedGoodsDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {

        if (is_array($this->fgDetailsCache)) {
            return $this->fgDetailsCache;
        }

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));

            try {
               /* $sql = file_get_contents(__DIR__."/epicoreSQL/processedInventory.sql");
                $sql = str_replace('~DATABASE_NAME~', '['.\DB::getDatabaseName().']', $sql);


                $Part1 = 'AND ship_RequestDetail.Part = :PART
		                  AND ship_RequestDetail.Warehouse = :WAREHOUSE ';

                $Part2 = 'AND ship_Request.Plant = :PLANT ';

                $Part3 = 'AND PartBin.PartNum = :MAINPART
                          AND Plant.Name = :MAINPLANT
                          AND Warehse.Description = :MAINWH';*/


                $DBNAME = $this->getCurrentDatabase();

                if (!$GetAll)
                {
                    //$sql = str_replace('~~PART1~~', $Part1, $sql);
                    //$sql = str_replace('~~PART2~~', $Part2, $sql);
                    //$sql = str_replace('~~PART3~~', $Part3, $sql);

                    //EXEC dbo.[PortalProcessedInventoryDetails] 'HDM', 'Franklin Park', 'HDM140A14.375-S', 'FP BOP';

                    $sql = "EXEC dbo.[PortalFinishedGoodsInventoryDetailsV6] :CUSTID, :PLANT, :PART, :WAREHOUSE, :DBNAME, :sub;";


                  // dd($sql,$CustID,$Plant,$Part,$Warehouse,$DBNAME );



                }
                else
                {

                    $sql = "EXEC dbo.[PortalFinishedGoodsInventoryDetailsALLV6] :CUSTID, :DBNAME, :sub;";
                }



                $sub=$this->_isSub();


                $query = $this->connRO->prepare($sql);
                $query->bindValue(':CUSTID', $CustID);
                $query->bindValue(':DBNAME', $DBNAME);
                $query->bindValue(':sub', $sub);

                if(!$GetAll)
                {
                    $query->bindValue(':PLANT', $Plant);
                    $query->bindValue(':PART', $Part);
                    $query->bindValue(':WAREHOUSE', $Warehouse);

                }

                $result = $query->executeQuery();

                $res = $result->fetchAllAssociative();

              //  dd($res);



            } catch (DriverException $e) {
               // dd($e->getMessage());

                if ($GetAll)
                    return [];
                else
                    throw new NoDetailDataForEpicorMultiKey();
            }

        if(!sizeof($res) && !$GetAll)
            throw new NoDetailDataForEpicorMultiKey();



        $this->fgDetailsCache=$res;

        return $res;
    }

    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */
    public function getInventoryProcessedOtherDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {

        if (is_array($this->processedOtherDeatilsCache)) {
            return $this->processedOtherDeatilsCache;
        }

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));

            try {
               /* $sql = file_get_contents(__DIR__."/epicoreSQL/processedInventory.sql");
                $sql = str_replace('~DATABASE_NAME~', '['.\DB::getDatabaseName().']', $sql);


                $Part1 = 'AND ship_RequestDetail.Part = :PART
		                  AND ship_RequestDetail.Warehouse = :WAREHOUSE ';

                $Part2 = 'AND ship_Request.Plant = :PLANT ';

                $Part3 = 'AND PartBin.PartNum = :MAINPART
                          AND Plant.Name = :MAINPLANT
                          AND Warehse.Description = :MAINWH';*/


                $DBNAME = $this->getCurrentDatabase();

                if (!$GetAll)
                {
                    //$sql = str_replace('~~PART1~~', $Part1, $sql);
                    //$sql = str_replace('~~PART2~~', $Part2, $sql);
                    //$sql = str_replace('~~PART3~~', $Part3, $sql);

                    //EXEC dbo.[PortalProcessedInventoryDetails] 'HDM', 'Franklin Park', 'HDM140A14.375-S', 'FP BOP';

                    $sql = "EXEC dbo.[PortalProcessedOtherInventoryDetailsV6] :CUSTID, :PLANT, :PART, :WAREHOUSE, :DBNAME, :sub;";


                  // dd($sql,$CustID,$Plant,$Part,$Warehouse,$DBNAME );



                }
                else
                {

                    $sql = "EXEC dbo.[PortalProcessedOtherInventoryDetailsALLV6] :CUSTID, :DBNAME, :sub;";
                }



                $sub=$this->_isSub();


                $query = $this->connRO->prepare($sql);
                $query->bindValue(':CUSTID', $CustID);
                $query->bindValue(':DBNAME', $DBNAME);
                $query->bindValue(':sub', $sub);

                if(!$GetAll)
                {
                    $query->bindValue(':PLANT', $Plant);
                    $query->bindValue(':PART', $Part);
                    $query->bindValue(':WAREHOUSE', $Warehouse);

                }

                $result = $query->executeQuery();

                $res = $result->fetchAllAssociative();

              //  dd($res);



            } catch (DriverException $e) {
               // dd($e->getMessage());

                if ($GetAll)
                    return [];
                else
                    throw new NoDetailDataForEpicorMultiKey();
            }

        if(!sizeof($res) && !$GetAll)
            throw new NoDetailDataForEpicorMultiKey();



        $this->processedOtherDeatilsCache=$res;

        return $res;
    }

    /**
     * @param string $CustID
     * @param string|null $Part
     * @param string|null $Plant
     * @param string|null $Warehouse
     * @return array
     * @throws NoDetailDataForEpicorMultiKey
     */
    public function getInventoryProcessedRRDetailsData($CustID, $Part = null, $Plant = null, $Warehouse = null)
    {
        if ($this->_getSubUser()) return [];

        $DBNAME = $this->getCurrentDatabase();

        $GetAll = (is_null($Part) && is_null($Plant) && is_null($Warehouse));
        if ($GetAll)
            $sql = "EXEC [dbo].[PortalProcessedInventoryRejectsAndReturnsDetailsALL] :custID,:DBNAME";
        else
            $sql = "EXEC dbo.[PortalProcessedInventoryRejectsAndReturnsDetails] :custID, :plant, :part, :warehouse, :DBNAME;";


        $query = $this->connRO->prepare($sql);

        $query->bindValue(':custID', $CustID);
        $query->bindValue(':DBNAME', $DBNAME);

        if (!$GetAll) {
            $query->bindValue(':plant', $Plant);
            $query->bindValue(':part', $Part);
            $query->bindValue(':warehouse', $Warehouse);
        }
        $result = $query->executeQuery();

        return $result->fetchAllAssociative();

    }


    public function getVorPartDescriptionFromPartNumberWithPaintCode($CustID, $Part)
    {
        $query = $this->connRO->prepare("SELECT TOP 1

        ISNULL(( SELECT TOP 1
                    CONCAT(Part.PartDescription, ISNULL(CONCAT(CHAR(13) + CHAR(10),
                           [Vendor].[VendorID], ' ',
                           Part_UD.TopcoatPaintCode_c, ' / ',
                           VendorBottomCoat.VendorID, ' ',
                           Part_UD.BackcoatPaintCode_c),'')) AS [Paint Code]
          FROM      Erp.Part AS Part
                    INNER JOIN Erp.Part_UD ON Part_UD.ForeignSysRowID = Part.SysRowID
                    INNER JOIN Erp.VendPart AS VendPart ON Part.Company = VendPart.Company
                                                           AND Part_UD.TopcoatPaintCode_c = VendPart.PartNum
                                                           AND ( VendPart.ExpirationDate IS NULL )
                    INNER JOIN Erp.Vendor AS Vendor ON VendPart.Company = Vendor.Company
                                                       AND VendPart.VendorNum = Vendor.VendorNum
                    INNER JOIN Erp.VendPart AS VendPart2 ON Part.Company = VendPart2.Company
                                                            AND Part_UD.BackcoatPaintCode_c = VendPart2.PartNum
                                                            AND ( VendPart2.ExpirationDate IS NULL )
                    INNER JOIN Erp.Vendor AS VendorBottomCoat ON VendPart2.Company = VendorBottomCoat.Company
                                                              AND VendPart2.VendorNum = VendorBottomCoat.VendorNum
          WHERE     ( Part.ClassID = 'FGS'
                      OR Part.ClassID = 'WIP'
                    )
                    AND Part.PartNum = PartBin.PartNum
        ),Part.PartDescription) AS PartDescription ,
        PartBin.PartNum AS VorteqPartNum

		FROM Erp.Part JOIN Erp.PartBin ON PartBin.PartNum = Part.PartNum
		INNER JOIN Erp.Customer AS Customer ON Part.Company = Customer.Company

		WHERE PartBin.PartNum LIKE :partNum AND Customer.CustID = :custID");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':partNum', $Part);

        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            if (sizeof($results) === 1)
            {
                return $results[0]['PartDescription'];
            }
        } catch(DriverException) {}

        return null;
    }




    /**
     * @param string $CustID
     * @param string $Part
     * @return null|string
     */
    public function getVorPartDescriptionFromPartNumber($CustID, $Part)
    {
        $query = $this->connRO->prepare("SELECT Part.PartDescription
                                         FROM Erp.Part AS Part
                                         INNER JOIN Erp.Customer AS Customer ON Part.Company = Customer.Company
                                         WHERE Part.PartNum = :partNum
                                           AND Customer.CustID = :custID");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':partNum', $Part);

        try {
            $result = $query->executeQuery();

            $results = $result->fetchAllAssociative();

            if (sizeof($results) === 1)
            {
                return $results[0]['PartDescription'];
            }
        } catch(DriverException) {}

        return null;
    }

    public function getInventoryUnprocessedQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {
        $query = $this->connRO->prepare("SELECT OnHandQty
                                         FROM dbo.portal_UnprocessedInventorySummary
                                         WHERE CustID = :custID
                                           AND VorteqPartNum = :part
                                           AND Plant = :plant
                                           AND Warehouse = :warehouse");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':part', $Part);
        $query->bindValue(':plant', $Plant);
        $query->bindValue(':warehouse', $Warehouse);
        $result = $query->executeQuery();
        return $result->fetchOne();
    }

    public function getInventoryUnprocessedRRQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {
        $query = $this->connRO->prepare("SELECT OnHandQty
                                         FROM dbo.portal_UnprocessedInventoryRejectsAndReturnsSummary
                                         WHERE CustID = :custID
                                           AND VorteqPartNum = :part
                                           AND Plant = :plant
                                           AND Warehouse = :warehouse");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':part', $Part);
        $query->bindValue(':plant', $Plant);
        $query->bindValue(':warehouse', $Warehouse);
        $result = $query->executeQuery();
        return $result->fetchOne();
    }

    public function getInventoryWorkInProgressQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {


        $this->getInventoryWorkInProgressDetailsData($CustID, $Part, $Plant, $Warehouse);



        return $this->_processOnHandQuantity($this->wipDetailsCache);


    }

    public function getInventoryFinishedGoodsQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {


        $this->getInventoryFinishedGoodsDetailsData($CustID, $Part, $Plant, $Warehouse);



        return $this->_processOnHandQuantity($this->fgDetailsCache);


    }

    public function getInventoryProcessedOtherQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {


        $this->getInventoryProcessedOtherDetailsData($CustID, $Part, $Plant, $Warehouse);



        return $this->_processOnHandQuantity($this->processedOtherDeatilsCache);


    }


    private function _processOnHandQuantity($detailsCache)
    {
        $sum =0;
        foreach ($detailsCache as $row)
        {
            $sum+=$row["OnHandQty"];
        }

        return $sum;

    }


    public function getCoilAllocationsByJobNumber($JobNum)
    {
       // dd(config('app.env'));




        $sql = file_get_contents(__DIR__ . "/epicoreSQL/CoilAllocation.sql");

        $CurrentDB = $this->getCurrentDatabase();
     //

       // dd($sql, $JobNum, $CurrentDB);


        try {
            $query = $this->connRO->prepare($sql);

         //   dd($sql, $JobNum);



            $query->bindValue(':JobNum', $JobNum);
            $query->bindValue(':DBNAME', $CurrentDB);
            $result = $query->executeQuery();
            return $result->fetchAllAssociative();
        } catch(\Exception $e) {
            dd($e, $sql, $JobNum, $CurrentDB);
        }
    }

    public function getInventoryProcessedPaintCodeDetailLine($CustID, $Part, $Plant, $Warehouse, $table)
    {

       // dd($CustID,$Part,$Plant,$Warehouse);

        $query = $this->connRO->prepare("SELECT PaintCode
                                         FROM dbo.{$table}
                                         WHERE CustID = :custID
                                           AND VorteqPartNum = :part
                                           AND Plant = :plant
                                           AND Warehouse = :warehouse");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':part', $Part);
        $query->bindValue(':plant', $Plant);
        $query->bindValue(':warehouse', $Warehouse);
        $result = $query->executeQuery();
        return $result->fetchOne();
    }



    public function getBOL($bolNum)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/getBOL.sql");

        $query = $this->connRO->prepare($sql);
        $query->bindParam(':BOL', $bolNum);

        /** @var SelectedCompanyProvider $customer */
        $customer = app(SelectedCompanyProvider::class);
        $customer = $customer->getSelectedCompanyEpicorCustID();
        $query->bindValue(':CUST', $customer);

        $result = $query->executeQuery();



        return $result->fetchAllAssociative();

    }



    /** job traveler */
    public function getJobTraveler($jobNum)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/jobTraveler.sql");

        $query = $this->connRO->prepare($sql);
        $query->bindValue(':JobNum', $jobNum);
        $query->bindValue(':JobNum1', $jobNum);


        //dd($jobNum);

       // dd($query->debugDumpParams());


        $result = $query->executeQuery();

        return $result->fetchAllAssociative();


    }



    public function getTotalCharges(array $JobNum)
    {
        foreach ($JobNum as $k => $num)
        {
            $JobNum[$k] = "'$num'";
        }
        $sql = file_get_contents(__DIR__."/epicoreSQL/getTotalCharges.sql");
        $sql = str_replace('$totalCharges', implode(",",$JobNum), $sql);
        $query = $this->connRO->prepare($sql);
        $result = $query->executeQuery();
        $ar = $result->fetchAllAssociative();

        $jobs = [];
        $grandTotal = 0;

        foreach ($ar as $k => $v)
        {
            $lineTotal = $v['TotalCharges'];
            $grandTotal += $lineTotal;
            $jobs[$v['JobNum']]='$'.number_format($lineTotal,0);

            //$ar[$k]=$v;
        }

        return [
            'jobs' => $jobs,
            'total' => number_format($grandTotal,0)
        ];


    }


    /** Paint line functions */

    public function getPaintLines()
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/paintlines.sql");

        $query = $this->connRO->prepare($sql);
        $result = $query->executeQuery();

        return $result->fetchAllAssociative();
    }

    // :JobNum AND CustID = :CustID  AND ProdGrup.ProdCode = :ProdCode
    public function getJobForPaintLine($JobNum, $ProdCode)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/getJobForPaintLine.sql");
        $query = $this->connRO->prepare($sql);
        //$query->bindParam(':CustID', $CustID);
        $JobNum = (string)$JobNum;
        $query->bindValue(':JobNum', $JobNum);
        $query->bindValue(':ProdCode', $ProdCode);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative();

    }

    public function getJob($JobNum)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/getJob.sql");
        $query = $this->connRO->prepare($sql);
        //$query->bindParam(':CustID', $CustID);
        $JobNum = (string)$JobNum;
        $query->bindValue(':JobNum', $JobNum);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative();

    }

    public function DoesPackingSlipExist($slip)
    {
        $sql ="SELECT
       PackingSlip

        FROM dbo.portal_CoilActivityReceipts
        where PackingSlip = :SLIP
        ";

        $query = $this->connRO->prepare($sql);
        $query->bindValue(':SLIP', $slip);

        $result = $query->executeQuery();
        $result= $result->fetchAllAssociative();

        // dd($lot,$result);

        if (count($result)>0)
            return true;
        else
            return false;

    }

    public function getPlantFromPackingSlip($slip)
    {
        $sql ="
        SELECT Erp.Plant.Plant
        FROM [Epicor10Live].[dbo].[portal_CoilActivityReceipts]
        JOIN Erp.Plant ON Erp.Plant.Name = dbo.portal_CoilActivityReceipts.PlantName
        WHERE PackingSlip = :SLIP
        GROUP BY Plant";

        $query = $this->connRO->prepare($sql);
        $query->bindValue(':SLIP', $slip);
        $result = $query->executeQuery();
        $result = $result->fetchAllAssociative();

        return $result;
    }

    public function getClosedJob($JobNum)
    {
        $sql = file_get_contents(__DIR__."/epicoreSQL/getClosedJob.sql");
        $query = $this->connRO->prepare($sql);
        //$query->bindParam(':CustID', $CustID);
        $JobNum = (string)$JobNum;
        $query->bindValue(':JobNum', $JobNum);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative(PDO::FETCH_ASSOC);

    }

    /** end paint line functions */



    public function getInventoryProcessedRRPaintCodeDetailLine($CustID, $Part, $Plant, $Warehouse)
    {




        $query = $this->connRO->prepare("SELECT PaintCode
                                         FROM dbo.[portal_ProcessedInventoryRejectsAndReturns]
                                         WHERE CustID = :custID
                                           AND VorteqPartNum = :part
                                           AND Plant = :plant
                                           AND Warehouse = :warehouse");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':part', $Part);
        $query->bindValue(':plant', $Plant);
        $query->bindValue(':warehouse', $Warehouse);
        $result = $query->executeQuery();
        return $result->fetchOne();
    }


    public function getInventoryProcessedRRQOHForSpecificDetailLine($CustID, $Part, $Plant, $Warehouse)
    {




        $query = $this->connRO->prepare("SELECT OnHandQty
                                         FROM dbo.portal_ProcessedInventoryRejectsAndReturnsSummary
                                         WHERE CustID = :custID
                                           AND VorteqPartNum = :part
                                           AND Plant = :plant
                                           AND Warehouse = :warehouse");
        $query->bindValue(':custID', $CustID);
        $query->bindValue(':part', $Part);
        $query->bindValue(':plant', $Plant);
        $query->bindValue(':warehouse', $Warehouse);
        $result = $query->executeQuery();
        return $result->fetchOne();
    }

    public function getTop100ShipmentsData($CustID)
    {

       // $sql = file_get_contents(__DIR__ . "/epicoreSQL/portal_Shipments.sql");


        $currentDB = $this->getCurrentDatabase();
        $query = $this->connRO->prepare(" [dbo].[portal_GetShipmentsV1] :CustID,:DBNAME");
        $query->bindValue(':CustID', $CustID);
        $query->bindValue(':DBNAME', $currentDB);

        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return false;
        }

        $results = $result->fetchAllAssociative();
        //format the ShipToLoc
        foreach($results as &$row) {
            $row['ShipToLoc'] = str_replace(', ', '<br />', $row['ShipToLoc']);
        }

        return $results;
    }

    public function getTop5ShipmentsData($CustID)
    {

        $sql = file_get_contents(__DIR__."/epicoreSQL/portal_ShipmentsTop5.sql");


        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CUST1', $CustID);
        $query->bindValue(':CUST2', $CustID);

        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return false;
        }

        $results = $result->fetchAllAssociative();
        //format the ShipToLoc
        foreach($results as &$row) {
            $row['ShipToLoc'] = str_replace(', ', '<br />', $row['ShipToLoc']);
        }

        return $results;
    }

    public function getTop100OrdersData($CustID)
    {
        //$viewName = 'portal_Orders';


        $Cust2 = $CustID;

        //echo($CustID);exit;
        /** exception case for HDC */
        if ($CustID == 'HDC')
        {
            //$viewName = 'portal_OrdersHDC';
            $Cust2 = 'BLARGH';
        }



        $sql = file_get_contents(__DIR__."/epicoreSQL/portal_Orders.sql");


        $query = $this->connRO->prepare($sql);
        $query->bindValue(':Cust1', $CustID);
        $query->bindValue(':Cust2', $Cust2);

        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return false;
        }


        return $result->fetchAllAssociative();
    }

    public function getTop5OrdersData($CustID)
    {
        $viewName = 'portal_Orders';

        /** exception case for HDC */
        if ($CustID == 'HDC')
        {
            $viewName = 'portal_OrdersHDC';
            $CustID = 'HDM';
            //WHERE [Customer].[CustID]='HDM' AND Part.UserChar2 = 'HDC'
        }

        $QueryBuilder = $this->connRO->createQueryBuilder();
        $QueryBuilder->select('*')
            ->from('dbo.'.$viewName)
            ->where('CustomerID = :custID')
            ->orderBy('CompletionDate', 'DESC')
            ->setMaxResults(5);
        $query = $this->connRO->prepare($QueryBuilder);
        $query->bindValue(':custID', $CustID);


        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return false;
        }

        return $result->fetchAllAssociative();
    }

    public function getCustomerNames($CustID)
    {

    }

    public function getCustomerShipToAddresses($CustID)
    {
        $subuser = $this->_isSub();

        try {
            if (!$subuser):

            $query = $this->connRO->prepare("SELECT ShipToNum, ShipToName, Address1 AS ShipToAddress1, Address2 AS ShipToAddress2,
                                                                        City AS ShipToCity, State AS ShipToState, Zip AS ShipToZip
                                             FROM portal_CustomerShipToAddresses
                                             WHERE CustID = :custID
                                             ORDER BY ShipToName");
            else:
                $query = $this->connRO->prepare("SELECT ShipToNum, ShipToName, Address1 AS ShipToAddress1, Address2 AS ShipToAddress2,
                                            City AS ShipToCity, State AS ShipToState, Zip AS ShipToZip
                                             FROM portal_CustomerShipToAddresses
                                             WHERE CustID = :custID
                                             and UPPER(ShipToName) like '%NB HANDY%'
                                             ORDER BY ShipToName");






            endif;


            $query->bindValue('custID', $CustID);
            $result = $query->executeQuery();
            return $result->fetchAllAssociative();
        } catch(DriverException $e) {
            Log::error('LOADING CUSTOMER SHIP TO ADDRESSES FOR '.$CustID.': '.$e->getMessage());
            return [];
        }
    }

    public function getCustomerSingleShipToAddressByNum($CustID, $ShipToNum)
    {
        try {
            $query = $this->connRO->prepare("SELECT ShipToName, Address1 AS ShipToAddress1, Address2 AS ShipToAddress2, City AS ShipToCity, State AS ShipToState, Zip AS ShipToZip
                                             FROM portal_CustomerShipToAddresses
                                             WHERE CustID = :custID
                                               AND ShipToNum = :shipToNum");
            $query->bindValue('custID', $CustID);
            $query->bindValue('shipToNum', $ShipToNum);
            $result = $query->executeQuery();
            $results = $result->fetchAllAssociative();
            return (sizeof($results) ? $results[0] : null);
        } catch(DriverException $e) {
            Log::error('LOADING SINGLE SHIP TO ADDRESS FOR '.$CustID.' '.$ShipToNum.': '.$e->getMessage());
            return null;
        }
    }

    /**
     * @param string $PlantName
     * @return string[]
     */
    public function getPlantUserEmailAddresses($PlantName)
    {
        // don't want to silently trap exceptions here since it's critical the plant get emailed the request if it's being completed
            //removed this function
        return [];


        $query = $this->connRO->prepare("SELECT UserEmailAddress
                                         FROM portal_PlantUserInfo
                                         WHERE PlantName = :plantName
                                           AND UserEmailAddress IS NOT NULL
                                           AND RTRIM(LTRIM(UserEmailAddress)) != ''");
        $query->bindValue('plantName', $PlantName);
        $result = $query->executeQuery();
        $results = $result->fetchAllAssociative();



        return array_column($results, 'UserEmailAddress');
    }


    /**
     * @param string $CustID
     * @param string $StartDate
     * @param string $EndDate
     * @return array
     */
    public function getCoilActivityUsageData($CustID, $StartDate, $EndDate)
    {
      /*  $query = $this->connRO->prepare("SELECT DateUsed, VorteqPartNum, CustomerPartNum, PartDesc, LotNum, Weight, PlantName, JobNum, CustomerPO, OnHandQty
                                         FROM dbo.portal_CoilActivityUsage
                                         WHERE CustID = :custID
                                           AND DateUsed >= :startDate
                                           AND DateUsed <= :endDate
                                         ORDER BY DateUsed, VorteqPartNum, JobNum, LotNum, Weight DESC"); */

        $sql = file_get_contents(__DIR__."/epicoreSQL/portal_CoilActivityUsage.sql");
        $query = $this->connRO->prepare($sql);


        $query->bindValue(':custID', $CustID);
        $query->bindValue(':startDate', $StartDate);
        $query->bindValue(':endDate', $EndDate);
        $result = $query->executeQuery();
        $data = $result->fetchAllAssociative();


        $foundRows = [];
        $foundLots=[];
        foreach ($data as $k => $row)
        {
            if ((int)$row["OnHandQty"] > 0)
            {
                if (isset($foundLots[$row["LotNum"]]))
                {
                    if (strtotime($row["DateUsed"]) < $foundRows[$row["LotNum"]]["date"])
                    {
                        $data[$k]["OnHandQty"]=null;
                    }
                    else
                    {
                        $data[$foundRows[$row["LotNum"]]["k"]]["OnHandQty"]=null;

                        $foundRows[$row["LotNum"]]["date"]=$row["DateUsed"];
                        $foundRows[$row["LotNum"]]["k"]=$k;
                    }
                }
                else
                {
                    $foundRows[$row["LotNum"]]["date"]=$row["DateUsed"];
                    $foundRows[$row["LotNum"]]["k"]=$k;
                    $foundLots[$row["LotNum"]]=1;
                }
            }

            if ($data[$k]["Weight"] == 0)
                $data[$k]["Weight"] = null;


        }

       // dd($data);

        return $data;
    }

    /**
     * @param string $CustID
     * @param string $StartDate
     * @param string $EndDate
     * @return array
     */
    public function getCoilActivityReceiptsData($CustID, $StartDate, $EndDate)
    {
        if ($CustID == "VGL") {
            $sql = file_get_contents(__DIR__."/epicoreSQL/VGLCoilReceiptsData.sql");
            $query = $this->connRO->prepare($sql);



        }
        else {
            $query = $this->connRO->prepare("SELECT DateReceived, VorteqPartNum, CustomerPartNum, PartDesc, ManufacturerLotNum, Weight, PlantName, PackingSlip, SupplierName, MillOrderNum, Temper, Alloy, CoilsPerSkid
                                         FROM dbo.portal_CoilActivityReceipts
                                         WHERE CustID = :custID
                                           AND DateReceived >= :startDate
                                           AND DateReceived <= :endDate
                                         ORDER BY DateReceived");
            $query->bindParam(':custID', $CustID);
        }

        $query->bindValue(':startDate', $StartDate);
        $query->bindValue(':endDate', $EndDate);
        $result = $query->executeQuery();
        return $result->fetchAllAssociative();
    }

    /**
     * @param array $InvoiceNumbers
     * @return string[]
     */
    public function getCustomerIDsForInvoiceNumbers(array $InvoiceNumbers)
    {
        if(!sizeof($InvoiceNumbers))
            return [];

        $InvoiceNumbers = array_unique($InvoiceNumbers);

        $stmt = $this->connRO->prepare("SELECT erp.InvcHead.InvoiceNum, erp.Customer.CustID
                                        FROM erp.InvcHead
                                        INNER JOIN erp.Customer ON Customer.CustNum = InvcHead.CustNum
                                        WHERE InvoiceNum IN (".implode(',',array_fill(0,sizeof($InvoiceNumbers),"?")).")");
        $ret = array_combine($InvoiceNumbers, array_fill(0,sizeof($InvoiceNumbers),null));
        $result = $stmt->executeQuery(array_values($InvoiceNumbers));
        $res = $result->fetchAllAssociative();
        return array_replace($ret, array_combine(array_column($res,'InvoiceNum'),array_column($res,'CustID')));
    }


    public function getOrdersForCustomerOnOrAfterDate($customerID, $date, $excludedOrderNumbers)
    {

        $sql = file_get_contents(__DIR__."/epicoreSQL/getOrdersForCustomerOnOrAfterDate.sql");



        if(!empty($excludedOrderNumbers)) {
            $sql .= str_replace(':OrderNumbers:', implode(',', $excludedOrderNumbers) ," AND Erp.OrderHed.OrderNum NOT IN (:OrderNumbers:);");
        } else {
            $sql .= ';';
        }

        $query = $this->connRO->prepare($sql);
        $query->bindValue(':CustomerID', $customerID);
        $query->bindValue(':Date', $date);

        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return [];
        }

        $orders = $result->fetchAllAssociative();

        return $orders;
    }

    /**
     * @return string[]
     */
    public function getAllCustomerNamesByID()
    {
        $stmt = $this->connRO->prepare("SELECT CustID, Name
                                        FROM erp.Customer
                                        ORDER BY CustID");
        $result = $stmt->executeQuery();
        $res = $result->fetchAllAssociative();
        return array_combine(array_column($res, 'CustID'),array_column($res,'Name'));
    }

    public function getShippingForMonth($month)
    {
        $sql = "exec [portal_GetShipmentsAllCustomersV1] 0, $month, '".$this->getCurrentDatabase()."';";
        $query = $this->connRO->prepare($sql);
        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return [];
        }
        $orders = $result->fetchAllAssociative();
        return $orders;
    }

    public function getShippingYTD()
    {

        $sql = "exec [portal_GetShipmentsAllCustomersV1] 1, 0, '".$this->getCurrentDatabase()."';";
        $query = $this->connRO->prepare($sql);

        try {
            $result = $query->executeQuery();
        } catch(DriverException) {
            return [];
        }

        $orders = $result->fetchAllAssociative();

        return $orders;
    }
}