How does this look? I’ve spent all day on it lol. It’s definitely faster and more responsive. 1300 lines of code cut down to roughly 200!
session_start();
include('dbconfig.php');
$query = "
SELECT p1.order_id, p1.dept_code, p1.status_id
FROM production_status p1
LEFT JOIN production_status p2 ON -- find similar records
p1.order_id = p2.order_id AND -- ...for the same order
p1.dept_code = p2.dept_code AND -- ...and the same department
p2.submit_time > p1.submit_time -- ...and that come after the row that p1 found
WHERE
p1.dept_code IN (5, 6, 7, 8, 10) AND -- limit to department we care about
p2.id IS NULL -- filter to keep only the p1 rows that didn't have later p2 rows
";
$statement = $pdo->prepare( $query );
$statement->execute();
//store results in array
$result = $statement->fetchAll();
//array to store order statuses
$statuses = [];
//build array to store order statuses for each order that has a manufacturing status
foreach ( $result as $row ) {
//check to see if the order id already exists in $statuses array, if not create it
if ( !isset( $statuses[ $row[ "order_id" ] ] ) ) {
$statuses[ $row[ "order_id" ] ] = [];
}
$statuses[ $row[ "order_id" ] ][ $row[ "dept_code" ] ] = $row[ "status_id" ];
}
/*fetch production data*/
//setup query
$sql = 'SELECT * FROM production_data ORDER BY enterprise, job_number, line_item ASC';
//execute SQL transaction
try {
//prepare SQL statement & execute
$stmt = $pdo->prepare($sql);
$stmt->execute();
//bind column names to variables
$stmt->bindColumn('id', $id);
$stmt->bindColumn('job_number', $job_number);
$stmt->bindColumn('enterprise', $enterprise);
$stmt->bindColumn('part_number', $part_number);
$stmt->bindColumn('description', $description);
$stmt->bindColumn('qty', $qty);
$stmt->bindColumn('line_item', $line_item);
$stmt->bindColumn('as400_ship_date', $as400_ship_date);
$stmt->bindColumn('date_showed_on_report', $date_showed_on_report);
$stmt->bindColumn('shipping_method', $shipping_method);
$stmt->bindColumn('notes', $notes);
$stmt->bindColumn('date_shown_complete', $date_shown_complete);
$stmt->bindColumn('actual_ship_date', $actual_ship_date);
$stmt->bindColumn('qty_shipped', $qty_shipped);
//store user dept code
$user_dept_code = get_user_dept( $_SESSION[ 'user_id' ], $pdo );
$test_results = [];
//output data into spreadsheet view
while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
$order_statuses = [];
//order has manufacturing status updates
if( isset ( $statuses[ $id ] ) ) {
if ( !isset ( $order_statuses[ $id ] ) ) {
$order_statuses[ $id ] = [];
}
//check vinyl & paint status
if ( isset ( $statuses[ $id ][ 5 ] ) ) {
$order_statuses[$id][ 5 ] = $statuses[ $id ][ 5 ];
} else {
$order_statuses[ $id ][ 5 ] = 0;
}
//check thermoforming status
if ( isset ( $statuses[ $id ][ 6 ] ) ) {
$order_statuses[$id][ 6 ] = $statuses[ $id ][ 6 ];
} else {
$order_statuses[ $id ][ 6 ] = 0;
}
//check final assembly status
if ( isset ( $statuses[ $id ][ 7 ] ) ) {
$order_statuses[$id][ 7 ] = $statuses[ $id ][ 7 ];
} else {
$order_statuses[ $id ][ 7 ] = 0;
}
//check crating & shipping status
if ( isset ( $statuses[ $id ][ 8 ] ) ) {
$order_statuses[$id][ 8 ] = $statuses[ $id ][ 8 ];
} else {
$order_statuses[ $id ][ 8 ] = 0;
}
//check quality control status
if ( isset ( $statuses[ $id ][ 10 ] ) ) {
$order_statuses[$id][ 10 ] = $statuses[ $id ][ 10 ];
} else {
$order_statuses[ $id ][ 10 ] = 0;
}
//order has no manufacturing status updates
} else {
if ( !isset ( $order_statuses[ $id ] ) ) {
$order_statuses[ $id ] = [];
}
$order_statuses[ $id ][ 5 ] = 0;
$order_statuses[ $id ][ 6 ] = 0;
$order_statuses[ $id ][ 7 ] = 0;
$order_statuses[ $id ][ 8 ] = 0;
$order_statuses[ $id ][ 10 ] = 0;
}
//build JSON response
if ( !isset($json[$job_number])) {
$json[$job_number] = array(
'Enterprise' => $enterprise,
'Job Number' => $job_number,
'LN #' => null,
'AS400 Ship' => null,
'Est. Ship' => null,
'Q.C.' => null,
'Thermoforming' => null,
'Vinyl/Paint' => null,
'Final Assembly' => null,
'Crating/Shipping' => null,
);
}
$json[$job_number]['__children'][] = array(
//'LN #' => $line_item,
'LN #' => '<a href="order_details.php?order=' . $id . '">' . $line_item . '</a>',
'Description' => $description,
'AS400 Ship Date' => $as400_ship_date,
'Est. Ship' => '12/12/1801',
'Q.C.' => build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $order_statuses[ $id ])[10],
'Thermoforming' => build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $order_statuses[ $id ])[6],
'Vinyl/Paint' => build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $order_statuses[ $id ])[5],
'Final Assm.' => build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $order_statuses[ $id ])[7],
'Crating/Shipping' => build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $order_statuses[ $id ])[8],
);
}
// Remove job number keys
$json = array_values($json);
//encode for JSON and output to screen
print(json_encode($json));
}
//failed to execute SQL transaction
catch (PDOException $e) {
print $e->getMessage();
}
function build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status) {
// define output button array
$btn = [];
// produce the buttons
foreach ( $status as $dept => $stat ) {
// enabled if user dept is 1,2,3 or the current dept matches the user's dept
$enabled = in_array( $user_dept_code, [ 1, 2, 3 ] ) || in_array( $dept, explode( ',', $user_dept_code ) ) ? '' : ' disabled';
// populate the dynamic values. you should actually use a template here with replaceable tags, then only produce the button that matches the current status value
$buttons[ 1 ] = "<button type='button' class='btn btn-warning btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>In Progress</button>";
$buttons[ 2 ] = "<button type='button' class='btn btn-danger btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Delayed</button>";
$buttons[ 3 ] = "<button type='button' class='btn-success btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Finished</button>";
$buttons[ 0 ] = "<button type='button' class='btn btn-secondary btn-sm btn_change_order_status_dialog' data-order-id='$id' data-order-number='$job_number' data-order-enterprise='$enterprise' data-dept-code='$dept'$enabled>Not Started</button>";
// get the button that matches the current status value for each dept
$btn[ $dept ] = $buttons[ $stat ];
}
// examine the results
return $btn;
}
