92 lines
5.0 KiB
PHP
Executable File
92 lines
5.0 KiB
PHP
Executable File
<?php
|
|
|
|
namespace Modules\M3\Http\Controllers;
|
|
|
|
use Config;
|
|
use DB;
|
|
use Illuminate\Http\Request;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
|
|
class WhsePerfController extends Controller
|
|
{
|
|
public static function index(Request $request)
|
|
{
|
|
|
|
return view('m3::liberty-theme.pages.whsePerfRequest');
|
|
|
|
}
|
|
|
|
public static function displayTable(Request $request)
|
|
{
|
|
|
|
// **********************************************************************************
|
|
// **** Changes in this section must be replicated in function exportTable below ****
|
|
// **********************************************************************************
|
|
$date_in_yr = isset($request->date_in_yr) ? $request->date_in_yr : date('Y');
|
|
$date_in_mth = isset($request->date_in_mth) ? $request->date_in_mth : date('m');
|
|
$date_in = $date_in_yr . $date_in_mth . '01';
|
|
$sqlstmt = 'select AVDATE, AVANOTESCL,AVADLRCL,AVAWGTCL,AVANOTESCH,AVADLRCH,AVAWGTCH,AVANOTESOP,AVADLROP,AVAWGTOP,
|
|
AVLNOTESCL,AVLDLRCL,AVLWGTCL,AVLNOTESCH,AVLDLRCH,AVLWGTCH,AVLNOTESOP,AVLDLROP,AVLWGTOP,
|
|
AVPNOTESCL,AVPDLRCL,AVPWGTCL,AVPNOTESCH,AVPDLRCH,AVPWGTCH,AVPNOTESOP,AVPDLROP,AVPWGTOP,
|
|
AVINOTESCL,AVIDLRCL,AVIWGTCL,AVINOTESOP,AVIDLROP,AVIWGTOP
|
|
from ICM711M
|
|
where AVDATE between ' . date('Ym', strtotime($date_in)) . '00 and ' . date('Ym', strtotime($date_in)) . '32';
|
|
|
|
$iCM711M = DB::connection(config('m3.connection', 'pgsql'))->select($sqlstmt);
|
|
$uSER_INFO = DB::connection(config('m3.connection', 'pgsql'))->table('icm428m')->selectRaw('R428US as USERPRF, R428STS as STATUS, NULL as DATE_EXPIRED, NULL as EXPIRED')->get()->all();
|
|
$power_user = DB::connection(config('m3.connection', 'pgsql'))->select("select R428PWRUSR, R428CO, R428PL, R428WH from ICM428M where R428US='" . strtoupper(m3User()) . "'");
|
|
|
|
$pct_array = Config::get('arrays.whsePerf_targets');
|
|
|
|
return view('m3::liberty-theme.pages.whsePerf', ['ICM711M' => $iCM711M, 'power_user' => $power_user, 'USER_INFO' => $uSER_INFO, 'date_in' => $date_in, 'pct_array' => $pct_array]);
|
|
|
|
}
|
|
|
|
public function exportTable(Request $request)
|
|
{
|
|
|
|
$date_in_yr = isset($request->date_in_yr) ? $request->date_in_yr : date('Y');
|
|
$date_in_mth = isset($request->date_in_mth) ? $request->date_in_mth : date('m');
|
|
$date_in = $date_in_yr . $date_in_mth . '01';
|
|
$sqlstmt = 'select AVDATE, AVANOTESCL,AVADLRCL,AVAWGTCL,AVANOTESCH,AVADLRCH,AVAWGTCH,AVANOTESOP,AVADLROP,AVAWGTOP,
|
|
AVLNOTESCL,AVLDLRCL,AVLWGTCL,AVLNOTESCH,AVLDLRCH,AVLWGTCH,AVLNOTESOP,AVLDLROP,AVLWGTOP,
|
|
AVPNOTESCL,AVPDLRCL,AVPWGTCL,AVPNOTESCH,AVPDLRCH,AVPWGTCH,AVPNOTESOP,AVPDLROP,AVPWGTOP,
|
|
AVINOTESCL,AVIDLRCL,AVIWGTCL,AVINOTESOP,AVIDLROP,AVIWGTOP
|
|
from ICM711M
|
|
where AVDATE between ' . date('Ym', strtotime($date_in)) . '00 and ' . date('Ym', strtotime($date_in)) . '32';
|
|
|
|
$iCM711M = DB::connection(config('m3.connection', 'pgsql'))->select($sqlstmt);
|
|
$uSER_INFO = DB::connection(config('m3.connection', 'pgsql'))->table('icm428m')->selectRaw('R428US as USERPRF, R428STS as STATUS, NULL as DATE_EXPIRED, NULL as EXPIRED')->get()->all();
|
|
$power_user = DB::connection(config('m3.connection', 'pgsql'))->select("select R428PWRUSR, R428CO, R428PL, R428WH from ICM428M where R428US='" . strtoupper(m3User()) . "'");
|
|
|
|
$pct_array = Config::get('arrays.whsePerf_targets');
|
|
|
|
$html = view('m3::liberty-theme.pages.whsePerf', ['ICM711M' => $iCM711M, 'power_user' => $power_user, 'USER_INFO' => $uSER_INFO, 'date_in' => $date_in, 'pct_array' => $pct_array])->render();
|
|
|
|
libxml_use_internal_errors(true);
|
|
$doc = new \DOMDocument;
|
|
$doc->loadHTML(mb_convert_encoding($html, 'HTML-ENTITIES', 'UTF-8')); // Extract only table elements
|
|
$tables = $doc->getElementsByTagName('table');
|
|
if ($tables->length === 0) {
|
|
abort(400, 'No table found in HTML');
|
|
}
|
|
// Create new document with only the table
|
|
$newDoc = new \DOMDocument;
|
|
$newDoc->appendChild($newDoc->importNode($tables->item(0), true));
|
|
$cleanHtml = '<!DOCTYPE html><html><body>' . $newDoc->saveHTML() . '</body></html>';
|
|
libxml_clear_errors();
|
|
$tempFile = tempnam(sys_get_temp_dir(), 'html') . '.html';
|
|
file_put_contents($tempFile, $cleanHtml);
|
|
$reader = IOFactory::createReader('Html');
|
|
$spreadsheet = $reader->load($tempFile);
|
|
$writer = new Xlsx($spreadsheet);
|
|
$fileName = 'export.xlsx';
|
|
$tempExcel = tempnam(sys_get_temp_dir(), 'excel');
|
|
$writer->save($tempExcel);
|
|
unlink($tempFile);
|
|
|
|
return response()->download($tempExcel, $fileName)->deleteFileAfterSend(true);
|
|
}
|
|
}
|