this command works, and gives expected result, only problem is its performance, since there are multiple warehouses(companies) command has to job for all, so it takes a lot of time. I am looking to improve sql queries, and maybe avoid use looping companies somehow.
<?php
namespace AppConsoleCommands;
use IlluminateConsoleCommand;
use IlluminateSupportFacadesDB;
use AppServicesDatabaseConnectionService;
use GuzzleHttpClient;
use AppModelsCompanies;
class ReportPendingPackages extends Command
{
protected $signature = 'pendingpackages:run {company_id}';
protected $description = 'Process pending packages';
protected $databaseService;
public function __construct(DatabaseConnectionService $databaseService)
{
parent::__construct();
$this->databaseService = $databaseService;
}
public function handle()
{
$this->databaseService->configureDatabase($this->argument('company_id'));
$companies = Companies::get();
foreach ($companies as $company) {
$company_mara = $company->company_mara;
$company_id = $company->company_id;
$data = $this->pendingPackages($company_id, $company_mara);
foreach ($data as $date => $n) {
$totalLJ = $n['total_lj'] ?? 0;
$totalMB = $n['total_mb'] ?? 0;
$printed = $n['printed'] ?? 0;
$pickup_lj = $n['pickup_lj'] ?? 0;
$pickup_mb = $n['pickup_mb'] ?? 0;
$pickup_total = $n['pickup_total'] ?? 0;
$packed_lj = $n['packed_lj'] ?? 0;
$packed_mb = $n['packed_mb'] ?? 0;
$packed_total = $n['packed_total'] ?? 0;
$returns = $n['returns'] ?? 0;
$mpr = DB::table('mabreturns_prescan')
->whereDate('created_at', now())
->count();
$returns_prescan = $mpr;
$returns_mb_lj = $n['returns_mb_lj'] ?? 0;
$loaded = $n['loaded'] ?? 0;
$picked_per_people = $n['picked_per_people'] ?? 0;
$packed_per_people = $n['packed_per_people'] ?? 0;
$ppp_plus_pp = $picked_per_people + $packed_per_people;
$in_pickup_1 = DB::table('MARA')
->where('status', 'pickup_1')
->where('dynamic_row_2', $company_mara)
->distinct('tracking_id')
->count('tracking_id');
$waiting_for_pickup = DB::table('MARA')
->where('status', 'printed')
->where('dynamic_row_2', $company_mara)
->distinct('tracking_id')
->count('tracking_id');
$currentDate = now()->toDateString();
$client = new Client();
$response = $client->post('https://myurl/file.php', [
'form_params' => [
'api_key' => 'key'
]
]);
$omg = json_decode($response->getBody());
$plot = $this->plot($company_mara);
$omg->printed_invoices_unpacked = $plot->todoOrders;
$verify_invoices = $omg->verifyStatus;
$verified_invoices = $omg->verifiedStatus;
$backordered = $omg->backorderedStatus;
$nonprinted_invoices = $omg->invoicesCount + $omg->buffer;
$printed_invoices_unpacked = $omg->printed_invoices_unpacked;
$total = $verified_invoices + $verify_invoices + $backordered + $nonprinted_invoices + $printed_invoices_unpacked;
DB::table('report_pending_packages')
->where('date', $currentDate)
->where('company_id', $company_id)
->delete();
DB::table('report_pending_packages')->insert([
'date' => $date,
'company_id' => $company_id,
'lj_people' => $totalLJ,
'mb_people' => $totalMB,
'printed' => $printed,
'picked_lj_lj' => $pickup_lj,
'picked_lj_mb' => $pickup_mb,
'picked_total' => $pickup_total,
'packed_lj' => $packed_lj,
'packed_mb' => $packed_mb,
'packed_total' => $packed_total,
'returns_scanned' => $returns,
'returns_prescan' => $returns_prescan,
'returns_mb_lj' => $returns_mb_lj,
'loaded' => $loaded,
'picked_per_people' => $picked_per_people,
'packed_per_people' => $packed_per_people,
'ppp_plus_ppp' => $ppp_plus_pp,
'verify_invoices' => $verify_invoices,
'verified_nvoices' => $verified_invoices,
'backordered' => $backordered,
'nonprinted_invoices' => $nonprinted_invoices,
'printed_invoices_unpacked' => $printed_invoices_unpacked,
'in_pickup_1' => $in_pickup_1,
'waiting_for_pickup' => $waiting_for_pickup,
'total' => $total,
]);
}
}
}
private function pendingPackages($company_id, $company_mara)
{
$report = [];
$d = now()->toDateString();
$peopleSql = '
SELECT count(DISTINCT(t.p1)) as people, users.warehouse FROM (
SELECT DISTINCT(pickup_user) as p1 FROM MARA WHERE cast(pickup_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
UNION ALL
SELECT DISTINCT(packed_user) FROM MARA WHERE cast(packed_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
UNION ALL
SELECT DISTINCT(return_user) FROM MARA WHERE cast(return_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
) AS t
INNER JOIN users on users.username = t.p1
WHERE users.company_id = ' . $company_id . '
GROUP BY users.warehouse
';
$printedSql = '
SELECT count(DISTINCT(tracking_id)) printed FROM MARA where cast(insert_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
';
$pickupLjSql = '
SELECT count(DISTINCT(tracking_id)) as pickup_lj FROM MARA
INNER JOIN users on users.username = MARA.pickup_user
WHERE cast(pickup_time as date) = "' . $d . '" and warehouse="wh-si" AND dynamic_row_2 = "' . $company_mara . '"
';
$pickupMbSql = '
SELECT count(DISTINCT(tracking_id)) as pickup_mb FROM MARA
INNER JOIN users on users.username = MARA.pickup_user
WHERE cast(pickup_time as date) = "' . $d . '" and warehouse="wh-si2" AND dynamic_row_2 = "' . $company_mara . '"
';
$packedLjSql = '
SELECT count(DISTINCT(tracking_id)) as orders FROM MARA
INNER JOIN users on users.username = MARA.packed_user
WHERE cast(packed_time as date) = "' . $d . '" and users.warehouse="wh-si" AND dynamic_row_2 = "' . $company_mara . '"
';
$packedMbSql = '
SELECT count(DISTINCT(tracking_id)) as orders FROM MARA
INNER JOIN users on users.username = MARA.packed_user
WHERE cast(packed_time as date) = "' . $d . '" and users.warehouse="wh-si2" AND dynamic_row_2 = "' . $company_mara . '"
';
$returnsSql = '
SELECT count(id) as ret FROM MARA WHERE cast(return_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
GROUP BY tracking_id
';
$loadedSql = '
SELECT count(DISTINCT(tracking_id)) loaded FROM MARA WHERE cast(load_time as date) = "' . $d . '" AND dynamic_row_2 = "' . $company_mara . '"
';
$totalUserLjSql = '
SELECT count(DISTINCT(users_presence.user)) totaluser
FROM users_presence
INNER JOIN users on users.username = users_presence.user
WHERE cast(users_presence.start_time as date) = "' . $d . '"
AND users.company_id = ' . $company_id . '
';
$r1 = DB::select(DB::raw($printedSql));
$r2 = DB::select(DB::raw($pickupLjSql));
$r3 = DB::select(DB::raw($packedLjSql));
$r4 = DB::select(DB::raw($packedMbSql));
$r5 = DB::select(DB::raw($returnsSql));
$r6 = DB::select(DB::raw($loadedSql));
$r7 = DB::select(DB::raw($pickupMbSql));
$r8 = DB::select(DB::raw($totalUserLjSql));
$report[$d]['total_lj'] = $r8[0]->totaluser ?? 0;
$report[$d]['total_mb'] = 0;
$report[$d]['printed'] = $r1[0]->printed ?? 0;
$report[$d]['pickup_lj'] = $r2[0]->pickup_lj ?? 0;
$report[$d]['pickup_mb'] = $r7[0]->pickup_mb ?? 0;
$report[$d]['pickup_total'] = $report[$d]['pickup_lj'] + $report[$d]['pickup_mb'];
$report[$d]['packed_lj'] = $r3[0]->orders ?? 0;
$report[$d]['packed_mb'] = $r4[0]->orders ?? 0;
$report[$d]['packed_total'] = $report[$d]['packed_lj'] + $report[$d]['packed_mb'];
$report[$d]['returns'] = $r5[0]->ret ?? 0;
$report[$d]['returns_mb_lj'] = 0;
$report[$d]['loaded'] = $r6[0]->loaded ?? 0;
$report[$d]['picked_per_people'] = 0;
if ($report[$d]['pickup_total'] > 0) {
$report[$d]['picked_per_people'] = round($report[$d]['pickup_total'] / $r8[0]->totaluser, 0);
}
$report[$d]['packed_per_people'] = 0;
if ($report[$d]['packed_total'] > 0) {
$report[$d]['packed_per_people'] = round($report[$d]['packed_total'] / $r8[0]->totaluser, 0);
}
return $report;
}
private function plot($company_mara, $warehouse = 'wh-si')
{
$bigquery = '
SELECT count(DISTINCT(tracking_id)) as todoOrders FROM MARA
WHERE status in ("printed", "pickup_1")
AND dynamic_row_2 = "' . $company_mara . '"
';
return DB::select(DB::raw($bigquery))[0] ?? [];
}
}
Is it even possible to use group by (company_mara or company_id) in query, and somehow split each of those while inserting to report_pending_packages table(has company_id column which splits each company data)?