Jump to content

Trying to compress and make my code faster


mongoose00318

Recommended Posts

I apologize for the length of my script. Is there a better way to do some of what I am doing here? My script is working fine but it works off a lot of data and if I try to query all the results, the response is pretty slow (1500ms). 

I'm concerned about that length of time when it comes to multiple users hitting the server at once. I may have to limit the query with pagination, etc. But, I just have a feeling there's a better way to do what I'm doing without over 1,000 lines....

 

//fetch production data
if( $action == 'fetch_production_data' ) {

	//setup query
	$sql = 'SELECT * FROM production_data ORDER BY enterprise, job_number, TRIM(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 );
		
		//$button = '';
		
		//output data into spreadsheet view
		while($row = $stmt->fetch(PDO::FETCH_BOUND)) {
			
			//setup vars to store button HTML
			$btn_qc 					= '';
			$btn_thermoforming 			= '';
			$btn_vinylpaint 			= '';
			$btn_finalassm 				= '';
			$btn_crateship 				= '';
			
			//store each dept status query for performance
			$status_qc					= isset( get_order_status( $id, 10, $pdo )[0]['status_id'] ) ? get_order_status( $id, 10, $pdo )[0]['status_id'] : '0';
			$status_thermoforming 		= isset( get_order_status( $id, 6, $pdo )[0]['status_id'] )  ? get_order_status( $id, 6, $pdo )[0]['status_id'] : '0';
			$status_vinylpaint 			= isset( get_order_status( $id, 5, $pdo )[0]['status_id'] ) ? get_order_status( $id, 5, $pdo )[0]['status_id'] : '0';
			$status_finalassm 			= isset( get_order_status( $id, 7, $pdo )[0]['status_id'] )  ? get_order_status( $id, 7, $pdo )[0]['status_id'] : '0';
			$status_crateship 			= isset( get_order_status( $id, 8, $pdo )[0]['status_id'] )  ? get_order_status( $id, 8, $pdo )[0]['status_id'] : '0';	
			
			$status_msg = 'Order ' . $id .' ';
			
			//echo $status_vinylpaint . '<br>';
			
			//build view based on user's department code
			switch ( $user_dept_code ) {
				
				//Design or AMS View
				case '4':
				case '9':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//Vinyl & Paint View
				case '5':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//Thermoforming View
				case '6':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//Derrick Donnel's View
				case '7,8':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order for qc dept
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for qc dept
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for qc dept
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Not Started</button>';
							break;
					}
					
					//determine status of order for qc dept
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//Final Assembly View
				case '7':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//Crating & Shipping View'
				case '8':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10" disabled>Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Not Started</button>';
							break;
					}
					
					//break parent switch
					break;
					
				//Quality Control View'
				case '10':
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7" disabled>Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8" disabled>Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				//SU/Op. Mngt/Prod. Mngt View
				case '1':
				case '2':
				case '3':
					
					//determine status of order for qc dept
					switch ( $status_qc ) {
						
						//order in progress
						case '1':
							
							$btn_qc = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_qc = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_qc = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_qc = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="10">Not Started</button>';
							break;
							
					}
					
					//determine status of order thermoforming
					switch ( $status_thermoforming ) {
						
						//order in progress
						case '1':
							
							$btn_thermoforming = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_thermoforming = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_thermoforming = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_thermoforming = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="6">Not Started</button>';
							break;
					}
					
					//determine status of order for vinyl & paint
					switch ( $status_vinylpaint ) {
						
						//order in progress
						case '1':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_vinylpaint = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_vinylpaint = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_vinylpaint = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="5">Not Started</button>';
							break;
					}
					
					//determine status of order final assembly
					switch ( $status_finalassm ) {
						
						//order in progress
						case '1':
							
							$btn_finalassm = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_finalassm = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_finalassm = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_finalassm = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="7">Not Started</button>';
							break;
					}
					
					//determine status of order for crating & shipping
					switch ( $status_crateship ) {
						
						case '1':
							
							$btn_crateship = '<button type="button" class="btn btn-warning btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">In Progress</button>';
							break;
						
						//order delayed
						case '2':
							
							$btn_crateship = '<button type="button" class="btn btn-danger btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Delayed</button>';
							break;
							
						//order finished
						case '3':
							
							$btn_crateship = '<button type="button" class="btn-success btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Finished</button>';
							break;
						
						//order not started
						case '0':
						default:
							
							$btn_crateship = '<button type="button" class="btn btn-secondary btn-sm btn_change_order_status_dialog" style="margin:0; width: 100px; height:100%; font-weight: bold;" data-order-id="'. $id . '" data-order-number="'. $job_number . '" data-order-enterprise="'. $enterprise . '" data-dept-code="8">Not Started</button>';
							break;
					}
					
					//break parent switch
					break;

				default:
					echo 'No case return';
					break;

			}
			
			
			//echo $btn_qc . $btn_thermoforming . $btn_vinylpaint . $btn_finalassm . $btn_crateship . '<br>';
			//$status_msg = 'Order ' . $id .' ';
			
			
			//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.' => trim($btn_qc),
					'Thermoforming' => trim($btn_thermoforming),
					'Vinyl/Paint' => trim($btn_vinylpaint),
					'Final Assm.' => trim($btn_finalassm),
					'Crating/Shipping' => trim($btn_crateship),
			);
			
		}

		// 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();
	}
}

 

Link to comment
Share on other sites

The source of the slowness is going to be database queries - nested switches won't be much of a problem (as far as the PHP engine is concerned).

I'm concerned about all those get_user_dept and get_order_status calls. Do they do database queries?

About cleaning up the code, I'm sorry but that's way too much for me personally to bother with. My advice is to improve it one step at a time: find out the differences between each switch case, pull them out into something more manageable (like an array), and build your HTML using data from it.
For example, the $user_dept_code=4/9 > $status_qc switch has all the buttons looking the same except for a CSS class or two and the inner text. Take those differences out into an array like

[
	1 => ["btn-warning", "In Progress"],
	2 => ["btn-danger",  "Delayed"],
	3 => ["btn-success", "Finished"],
	...
]

You can look up the CSS class and text using $status_qc. You may also discover that you messed up some of the markup.

And please, do yourself a favor and create your own CSS classes. All those inline styles are bad.

Link to comment
Share on other sites

@requinix Lol I know about the inline styles...sorry about that. Trying to get this to a more functional state and then planned to clean up a lot (queries, styles, etc.)

Yes the get_user_dept and get_order_status do database queries. That was my primary concern when looking it over. Is there a way to not have to query every time like I'm doing?

Link to comment
Share on other sites

4 minutes ago, mongoose00318 said:

Yes the get_user_dept and get_order_status do database queries. That was my primary concern when looking it over. Is there a way to not have to query every time like I'm doing?

Almost certainly. What's the code for those functions?

Link to comment
Share on other sites

//get order status
function get_order_status ( $order_id, $dept_code, $pdo ) {
	//setup query
	$query = "SELECT * FROM production_status WHERE order_id = $order_id AND dept_code = $dept_code ORDER BY submit_time DESC LIMIT 1";
	//prep, execute, & fetch SQL results
	$statement = $pdo->prepare( $query );
	$statement->execute();
	$result = $statement->fetchAll();
	return $result;
}

//get user's dept code(s)
function get_user_dept( $user_id, $pdo ) {
	$query = "SELECT dept_code FROM login WHERE user_id = '$user_id'";
	$statement = $pdo->prepare( $query );
	$statement->execute();
	$result = $statement->fetchAll();
	foreach ( $result as $row ) {
		return $row[ 'dept_code' ];
	}
}

 

Link to comment
Share on other sites

Here's the structure of how your queries are run:

foreach "SELECT * FROM production_data ORDER BY enterprise, job_number, TRIM(line_item) ASC" {
	$status_qc = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <10> ORDER BY submit_time DESC LIMIT 1";
	$status_thermoforming = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <6> ORDER BY submit_time DESC LIMIT 1";
	$status_vinylpaint = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <5> ORDER BY submit_time DESC LIMIT 1";
	$status_finalassm = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <7> ORDER BY submit_time DESC LIMIT 1";
	$status_crateship = "SELECT <status_id> FROM production_status WHERE order_id = <production_data.id> AND dept_code = <8> ORDER BY submit_time DESC LIMIT 1";
}

There are two complications in here: the fact that your departments are dynamic but your code cares about five specific ones, and that you want the most recent record for an order per department. This means that while all six of these queries could be combined into one, you'd end up with one query that uses 10 joins. That sucks.

So I think I would settle for two queries: getting everything you want from production_data, but before that getting the most recent status per department for all orders. With some quick preprocessing on that you could look up each status very quickly.

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
$statuses = [];
// foreach $row from that query {
	if (!isset($statuses[$row["order_id"]])) {
		$statuses[$row["order_id"]] = [];
	}
	$statuses[$row["order_id"]][$row["dept_code"]] = $row["status_id"];
// }

 

  • Great Answer 1
Link to comment
Share on other sites

The "ORDER BY submit_time DESC LIMIT 1" requires mySQL to do a full pass of the entire table just to get the most recent submit_time. You might try indexing the column(s) you require, and then use "select MAX(submit_time)" and benchmark it to see if it's faster. At the very minimum, a fun experiment.

Link to comment
Share on other sites

On 4/17/2020 at 5:15 PM, requinix said:

that uses 10 joins. That sucks.

...lol

@requinix Okay so I'm trying to make sense of the query you provided. I ran it in PHPMyAdmin and it seems to work. I'm confused about it though. At first I thought you were doing a join on the production_data table. While typing this question I realized you're doing the join on the same table? I'm confused about how the query works. It's awesome though. I've known for a while I really under utilize the power of fine tuned SQL queries.

How does this part work? 

p2.id IS NULL -- filter to keep only the p1 rows that didn't have later p2 rows

The ids are always set and are never null? I've worked my way through most of the logic but I'm still a little confused. I know you explained it in the SQL comments...but I'm still trying to grasp it...sorry...

Edited by mongoose00318
Link to comment
Share on other sites

30 minutes ago, mongoose00318 said:

How does this part work? 


p2.id IS NULL -- filter to keep only the p1 rows that didn't have later p2 rows

The ids are always set and are never null?

They can be NULL in that case because of the LEFT JOIN.

The join condition includes p2.submit_time > p1.submit_time, that means for each row in p1 it will attempt to find a row in p2 where p2's submit_time is greater than p1's submit_time (in addition to the other conditions).  If it can't find any row that matches, the all the p2 columns will be null.

So, since whatever the most recent entry in p1 is won't be able to find a matching row in p2, p2.id will be null and filtering on that condition in the where clause results in you only receiving the most recent rows from p1.

 

Link to comment
Share on other sites

I’m trying to do an in_array()…it’s not working.

Array
(
    [75333] => Array
        (
            [5] => 3
            [7] => 2
            [6] => 2
        )

    [75348] => Array
        (
            [8] => 2
            [6] => 2
        )

    [75340] => Array
        (
            [7] => 3
            [6] => 1
            [5] => 3
        )

    [75352] => Array
        (
            [5] => 1
        )

)

And I’m doing:

if ( in_array ( '75352', $statuses ) ) {
    echo 'TRUE<br>';
}

But I never get it to say true. The value is in the array though?

Link to comment
Share on other sites

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;
}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.