2117 lines
62 KiB
PHP
2117 lines
62 KiB
PHP
<?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;
|
||
}
|
||
}
|