Use SQL Query to Get Revised & New Orders

[mongoose00318]

I've been staring at this for a few hours and know there is a solution...I will post my code...sorry about it's length...I think that is one of the problems when I am trying to think through the logic of what I am trying to do.

//fetch production data
if( isset ( $action ) && $action == 'fetch_production_data' ) {
	
	$query = "
			SELECT p1.order_id, p1.dept_code, p1.status_id, p1.expected_complete_time
			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, 11, 12) 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
		 ";

	$stmt = $pdo->query( $query );

	//array to store order statuses
	$statuses = [];

	//build array to store order statuses for each order that has a manufacturing status
	while ( $row = $stmt->fetch() ) {
		$statuses[ $row[ "order_id" ] ][ $row[ "dept_code" ] ] = array(
			'status_id' => $row[ "status_id" ],
			'expected_complete_time' => $row[ "expected_complete_time" ]
		);
	}
	
	//filters are set
	if(isset($_GET['filters'])){
		
		//check field for manipulation
		$field = $_GET['filters'][0]['field'];
		if ( $field == 'Job Number' )
			$field = 'job_number';
		else if ( $field == 'Enterprise' )
			$field = 'enterprise';
		else 
			exit();
		
		//check type for manipulation
		$type = $_GET['filters'][0]['type'];
		if ( $type == '=' )
			$type = '=';
		else if ( $type == 'like' )
			$type = 'LIKE';
		else 
			exit();
		
		//check value for job_number as numeric
		$value = $_GET['filters'][0]['value'];
		if ( $field == 'job_number' && trim( !is_numeric ( $value ) ) )
			exit();
		else if ( $type == 'LIKE' )
			$value = "'%" . $value . "%'";
		
		$filter_data = TRUE;
	}
	
	//pagination
	if( isset( $field ) && isset( $type ) && isset( $value ) )
		$total_pages_sql = "SELECT COUNT(*) FROM production_data WHERE ". $field ." ". $type ." ". $value;
	else
		$total_pages_sql = "SELECT COUNT(*) FROM production_data";
	
	$stmt = $pdo->query($total_pages_sql);
	$stmt->execute();
	
	$records_per_page = 14;
	$page = $_GET['page'];
	$offset = ($page-1) * $records_per_page; 
	$total_rows = $stmt->fetchColumn();
	$total_pages = ceil($total_rows / $records_per_page);
	
	/*fetch production data*/
	if( isset( $field ) && isset( $type ) && isset( $value ) ) {
		
		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time
				FROM production_data
				WHERE '. $field .' '. $type .' '. $value .' 
				ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page;

	} else {

		$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time 
				FROM production_data
				ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page;

	}

	//prepare SQL statement & execute
	$stmt = $pdo->query($sql);

	// define departments
	$dept_codes = [5,6,7,8,10,11,12];

	//output data into spreadsheet view
	while(list($id, $job_number, $enterprise, $description, $line_item, $as400_ship_date, $insert_time) = $stmt->fetch(PDO::FETCH_NUM))
	{

		// get/produce status values
		$status = [];
		foreach($dept_codes as $key)
		{
			$status[$key] = $statuses[$id][$key] ?? 0;
		}
		
		//set format for dates
		$date_format = 'm/d/Y';
		
		//get crating shipping expected ship date and assign to expected ship date
		$expected_ship_date = ( $status[8]['expected_complete_time'] != '' ) ? $status[8]['expected_complete_time'] : 'N/A';
		
		//format expected dates
		$expected_ship_date = ( $expected_ship_date != 'N/A' ) ? date( $date_format, strtotime( $expected_ship_date ) ) : $expected_ship_date;
		$as400_ship_date = date( $date_format, strtotime( $as400_ship_date ) );
		
		// build the buttons, once
		$btns = build_change_order_status_btns($id, $user_dept_code, $job_number, $enterprise, $status);
		
		//determine if order is new or revised
		$latestRevisedsql = $pdo->query('SELECT insert_time FROM production_data_archive WHERE order_id = "'.$id.'" ORDER BY insert_time desc LIMIT 1');
		$latestRevised = $latestRevisedsql->fetch();
		$latestRevisedcnt = $latestRevisedsql->rowCount();
		
		//setup dates for comparison
		$currentDate = strtotime(date('Y-m-d H:i:s'));
		$revisedDate = strtotime($latestRevised['insert_time']);
		$insertDate = strtotime($insert_time);
		
		//add 24hrs to revised date
		$revisedDate = $revisedDate + 86400;
		$insertDate = $insertDate + 86400;
		
		//see if the order is new or was revised in the last 24 hours
		$is_revised = ( $revisedDate > $currentDate ) ? TRUE : FALSE;
		$is_new = ( $insertDate > $currentDate && $latestRevisedcnt == 0 ) ? TRUE : FALSE;

		//build JSON response
		$json['data'][] = array(
			'Enterprise' => $enterprise,
			'Job Number' => $job_number,
			'LN #' => '<a href="order_details.php?order=' . $id . '">' . $line_item . '</a>',
			'Description' => $description,
			'AS400 Ship Date' => $as400_ship_date,
			'Est Ship' => $expected_ship_date,
			'QC' => $btns[10],
			'Pole Barn' => $btns[12],
			'Thermoforming' => $btns[6],
			'Vinyl/Paint' => $btns[5],
			'Fnl Asmb 1' => $btns[7],
			'Fnl Asmb 2' => $btns[11],
			'Crating/Shipping' => $btns[8],
			'New' => $is_new,
			'Revised' => $is_revised,
		);
		
		$json['new_orders'][] = [ $id => $latestRevised, 'cdate' => $currentDate, 'revised_date' => $revisedDate, 'is revised' => $is_revised ];
		
	}
	
	$json['last_page'] = $total_pages;
	
	$json['current_page'] = $page;
	
	header('Content-type: text/javascript');
	print(json_encode($json, JSON_PRETTY_PRINT));
}

If a $_GET var is set (say $_GET['new'] or $_GET['revised']) I'd like to pull all of the orders in the system which were created or revised in the last 24hours. Between all of the other features of Tabulator I am using (pagination, filtering, and I need to setup AJAX sorting) my head is just spinning...the criteria that I am using to figure out if an order was revised or is new is as so:

• New Order: Created within the last 24 hours and doesn't have any matches in the production_data_archive table
• Revised Order: Revised in the last 24 hours

Maybe I need to clean up my code a bit...maybe I'm doing too much there. I don't like the way I am handling the pagination and when it comes to the AJAX sorting I'll likely have to handle it in a very similar way. Everything works...I just know I could probably be doing several things better.

[requinix]

I'm not sure exactly what the problem you're facing is, but if you want ideas for how to clean up the code...

Instead of trying to build the whole query at once, build the pieces of it and then assemble it at the end.

In your case it seems the only concern is the WHERE conditions. Use an array to track all the conditions you want, then join them together with ANDs to get the full clause, then add that in to your main query. Goes like

$conditions = [];

if ($some_condition) {
	$conditions[] = "some condition";
}
if ($other_condition) {
	$conditions[] = "other condition";
}
// ...

if ($conditions) {
	$where = "WHERE " . implode(" AND ", $conditions);
} else {
	$where = "";
}

$query = "
	SELECT fields
	FROM table
	{$where}
	ORDER BY fields
	LIMIT whatever
";

 

[mongoose00318]

@requinix

I wrote out the structure of the array I intend to try to create to handle the pagination, sorting, and filtering. I also wrote down some key points to help me mentally. Here are the key points:

1. Paginate will always exist
2. Filter is optional
3. Sort is optional
4. Sort will always be "ORDER BY"
5. Filter will always have a field & value

Here is my proposed array structure:

Array([
  'pagination' = [
    'offset' = ?,
    'records_per_page' = ?,
    'current_page' = ?,
  ],
  'sorters' = [
    'direction' = ASC or DESC,
    'field' ?,
  ],
  'filter' = [
    'field' = ?,
    'type' = LIKE (I want to make this the only option for the user),
    'value' = ?,
  ],
]);

What do you think?

[mongoose00318]

One thing that frustrates me after I finish a script is something like this:

$status[$key] = $statuses[$id][$key] ?? 0;

I wish I knew more ways to write in a short hand way where the code isn't so long like the above example (that line is courtesy of phdr). Also, I wish I was better at not repeating my code. I'm getting better at it but I am still fascinated with how a lot of you guys do in 1 or 2 lines what seems to take me 25-35. 

Like ternary operators..I love to use those when I can.

Edited May 19, 2020 by mongoose00318

[Barand]

1 hour ago, mongoose00318 said:

'type' = LIKE (I want to make this the only option for the user),

Why? There is very little data in your query results that is LIKEable.

LIKE is for matching string type columns against partial string values using wildcards

E.G. Find all users with last name beginning with "J" who live in a location ending with "ham"

SELECT … FROM user WHERE lastname LIKE 'j%' AND location LIKE '%ham';

Jones, Oldham
Johnstone, Birmingham
Joker, Gotham

 

Edited May 19, 2020 by Barand

[requinix]

2 hours ago, mongoose00318 said:

What do you think?

I think you're creating something far more complex than you actually need.

[mongoose00318]

15 hours ago, Barand said:

Why? There is very little data in your query results that is LIKEable.

LIKE is for matching string type columns against partial string values using wildcards

E.G. Find all users with last name beginning with "J" who live in a location ending with "ham"

SELECT … FROM user WHERE lastname LIKE 'j%' AND location LIKE '%ham';

Jones, Oldham
Johnstone, Birmingham
Joker, Gotham

 

Because the users who are searching the orders sometimes only type in a partial value for the order number. Normally the middle 5 digits of the order number. There is a reason...I think the first 2 numbers represent a location and the last digits are always a 1 and 0. I guess they do it to find the order faster.

[Barand]

I would recommend you split the order number in that case. Put the location (digits 1 and 2) in a location code column and the rest in the order number column.

Aside from it being a poor design decision to have "structured" numbers in a single column, your users will have to use search expressions "LIKE '%x%' ". That will prevent any indexes from being used and greatly slow down any searches.

[mongoose00318]

Here is what I came up with...thank you for the array suggestion.

        //handle filters $_GET vars
	if ( isset ( $_GET['filters'][0] ) ) {
		
		//set fields
		if ( $_GET['filters'][0]['field'] == 'Job Number' )	
			$tabulator_get['filters'] = ['field' => 'job_number']; //set to job number
		elseif ( $_GET['filters'][0]['field'] == 'Enterprise' )
			$tabulator_get['filters'] = ['field' => 'enterprise']; //set to enterprise
		
		$tabulator_get['filters']['type'] = 'LIKE'; //set type
		$tabulator_get['filters']['value'] = "'%" . $_GET['filters'][0]['value'] . "%'"; //set value
		
	}
	
	//handle sorter $_GET vars
	if ( isset ( $_GET['sorters'] ) ) {
		$tabulator_get['sorters'] = [
			'field' => ( isset ($_GET[ 'sorters' ][0]['field']) && $_GET[ 'sorters' ][0]['field'] == 'AS400 Ship Date' ) ? 'as400_ship_date' : '',
			'direction' => ( isset ($_GET[ 'sorters' ][0]['dir']) ) ? strtoupper( $_GET[ 'sorters' ][0]['dir'] ) : '',
		];
	}
	
	//query db for necessary pagination data
	if( isset ( $tabulator_get[ 'filters' ] ) ) {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data WHERE " . $tabulator_get['filters']['field'] . " LIKE " . $tabulator_get['filters']['value'];
	} else {
		$total_pages_sql = "SELECT COUNT(*) FROM production_data";
	}

@Barand I have to work within a very badly designed IT infrastructure that is unchanging (for now at least). So, I totally agree with you about separating the location code from the order number. But, just like the issue I mentioned to you about orders being entered into the AS400 wrong...nothing gets done to fix these things. But I am working on ways to get it done. It's as much a culture issue as an IT issue.

One good thing though is working through all these unique challenges has been very beneficial to trying to get my development skill set back.

Edited May 20, 2020 by mongoose00318

[mongoose00318]

Oops I left out the query construction:

        //begin structuring query to get order data
	$sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date, insert_time
			FROM production_data';
	
	//build where
	if ( isset ( $tabulator_get[ 'filters' ]) ) {
		$sql .= ' WHERE ' . $tabulator_get[ 'filters' ][ 'field' ] . ' ' . $tabulator_get[ 'filters' ][ 'type' ] . ' ' . $tabulator_get[ 'filters' ][ 'value' ];
	}
	
	//build order by
	if ( isset( $tabulator_get[ 'sorters' ] ) ) {
		$sql .= ' ORDER BY ' . $tabulator_get[ 'sorters' ][ 'field' ] . ' ' . $tabulator_get[ 'sorters' ][ 'direction' ];
	} else {
		$sql .= ' ORDER BY enterprise, job_number, line_item';
	}
	
	//build limit
	if ( isset ( $tabulator_get[ 'pagination' ] ) ) {
		$sql .= ' LIMIT ' . $tabulator_get[ 'pagination' ][ 'offset' ] . ', ' . $tabulator_get[ 'pagination' ][ 'records_per_page' ];
	}