Jump to content

mongoose00318

Members
  • Posts

    235
  • Joined

  • Last visited

Everything posted by mongoose00318

  1. @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: Paginate will always exist Filter is optional Sort is optional Sort will always be "ORDER BY" 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?
  2. @Barand I haven't forgotten about this. I made my script and it has been running every 5 minutes and importing the data successfully. I did manage to get the LOAD DATA working after your suggestions. But, I ran into a few problems and figured I'd circle back to it after I don't have as much pressure to get this live (live meaning internal to the network)...but I wanted to get back to you so here is my code... <?php chdir(__DIR__); //allow more memory and execution time for script ini_set('memory_limit', '4096M'); ini_set('max_execution_time', '600'); //initialize db connection $host = '127.0.0.1'; $db = 'dbname'; $user = 'root'; $pass = 'password'; $charset = 'utf8mb4'; $options = [ \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, \PDO::ATTR_EMULATE_PREPARES => false, ]; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; try { $pdo = new \PDO($dsn, $user, $pass, $options); } catch (\PDOException $e) { throw new \PDOException($e->getMessage(), (int)$e->getCode()); } //set timezone date_default_timezone_set('America/Chicago'); //include composer autoload require 'vendor/autoload.php'; //setup use statements for PHPSpeadsheet use \PhpOffice\PhpSpreadsheet\Reader\Xls; use \PhpOffice\PhpSpreadsheet\Writer\Csv; ## ## Copy production log file ## $copied_file_timestamp = date("YmdHis"); $srcfile = '\\\directory\Daily Production\Production Schedule.xls'; //live production log $destfile = 'production_data/xls/Production Schedule ('. $copied_file_timestamp .').xls'; //copy of production log //last modifed date of network file $lastModifiedTimestamp = filemtime($srcfile); $lastModifiedDatetime = date("Y-m-d H:i:s", $lastModifiedTimestamp); //TEST DATE - REMOVE!!! //$lastModifiedDatetime = '2020-05-13 12:00:00'; //test date to make it appear production file is older than last insert time //get latest time when an import was performed $statement = $pdo->query("SELECT insert_time FROM production_data ORDER BY insert_time DESC LIMIT 1"); $statement->execute(); $get_last_insert_time = $statement->fetch(PDO::FETCH_NUM); //network file is older than last insert time; log msg and exit script if ( $get_last_insert_time[0] > $lastModifiedDatetime ) { $message = "Network file is older; exiting script..<br>$srcfile was last modified on $lastModifiedDatetime<br>Last data update was: $get_last_insert_time[0]"; echo $message; exit(); } //perform copy of network file to local system if (!copy($srcfile, $destfile)) { $message = "File cannot be copied! \n"; } else { $message = "File has been copied! \n"; } //store paths and file names $xls_prod_log = 'Production Schedule ('. $copied_file_timestamp .').xls'; $csv_prod_log = 'Production Schedule ('. $copied_file_timestamp .').csv'; $results_prod_log = 'Production Schedule ('. $copied_file_timestamp .').txt'; $path_xls = 'production_data/xls/'; $path_csv = 'production_data/csv/'; $path_results = 'production_data/results/'; //setup complete file path to newly copied xls file $xls_file = $path_xls . $xls_prod_log; //instantiate class and settings for PHPSpreadsheet class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { public function readCell($column, $row, $worksheetName = '') { // Read columns C:P if (in_array($column,range('C','P'))) { return true; } return false; } } $reader = new Xls(); $reader->setReadDataOnly(true); $reader->setReadFilter( new MyReadFilter() ); $reader->setLoadSheetsOnly( [ 'B. Current Report' ]); //load the sheet into memory $spreadsheet = $reader->load($xls_file); //write the csv file $writer = new Csv($spreadsheet); $writer->save($path_csv . $csv_prod_log); //unset all PHPSpreadsheet objects unset($reader); unset($writer); unset($spreadsheet); //open the new CSV file $file = fopen($path_csv . $csv_prod_log, "r"); //$arr_orders = []; $count = 1; $count_succesful = 0; ## Conditional Update //prep, execute, & fetch SQL results $query = "SELECT * FROM production_data"; $statement = $pdo->query( $query ); $statement->execute(); $old_orders = $statement->fetchAll(PDO::FETCH_ASSOC); //record update query results $update_query_results = [ 'accepted_change' => [], 'accepted_no_change' => [], 'rejected' => [], 'new' => [], ]; //loop through records of csv while( ( $column = fgetcsv($file, 10000, "," ) ) !== FALSE ) { //skip first 2 rows and make sure array isn't empty if ( $count > 2 & array_filter($column) ) { //setup update array for easy reference $update = [ 'job_number' => trim($column[7]), 'enterprise' => trim($column[2]), 'part_number' => trim($column[3]), 'description' => trim($column[4]), 'qty' => trim($column[5]), 'line_item' => trim($column[6]), 'as400_ship_date' => trim($column[11]), 'hold_reason' => trim($column[13]), 'hold_date' => trim($column[12]), ]; //format dates if ( !empty ( $update['as400_ship_date'] ) && is_numeric ( $update['as400_ship_date'] ) ) $tmp1 = ($update['as400_ship_date'] - 25569) * 86400; if ( !empty ( $update['hold_date'] ) && is_numeric ( $update['hold_date'] ) ) $tmp2 = ( trim ( $column[12] ) - 25569 ) * 86400; //set new values for dates $update['as400_ship_date'] = gmdate("Y-m-d", $tmp1); $update['hold_date'] = ( !empty ( $tmp2 ) ) ? gmdate("Y-m-d", $tmp2) : '0000-00-00'; if ($column[12] == '') $update['hold_date'] = '0000-00-00'; //check if order exists or not in old data $find_match = search_job_and_line_item( $update['job_number'], $update['line_item'], $old_orders ); //order exists without duplicate line items if ( is_array( $find_match ) ) { $current_old_order = $old_orders[ $find_match[0] ]; //store current old order array for easy reference $str1 = strtotime( $update[ 'as400_ship_date' ] ); //convert date to string $str2 = strtotime( $current_old_order[ 'as400_ship_date' ] ); //convert date to string $current_timestamp = date("Y-m-d H:i:s"); //count differences $no_change = 0; foreach ( $current_old_order as $k => $v ) if ( $k != 'id' && $k != 'insert_time' ) if (trim($v) !== trim($update[$k]) ) $no_change++; //proceed if there are differences if ( $no_change > 0 ) { //check for differences in fields $set_vals['part_number'] = ($update['part_number'] !== $current_old_order[ 'part_number' ]) ? $update['part_number'] : $current_old_order['part_number']; $set_vals['description'] = ($update['description'] !== $current_old_order[ 'description' ]) ? $update['description'] : $current_old_order['description']; $set_vals['qty'] = ($update['qty'] !== $current_old_order[ 'qty' ]) ? $update['qty'] : $current_old_order['qty']; $set_vals['as400_ship_date'] = ($str1 !== $str2) ? $update['as400_ship_date'] : $current_old_order['as400_ship_date']; $set_vals['hold_reason'] = ($update['hold_reason'] !== $current_old_order[ 'hold_reason' ]) ? $update['hold_reason'] : $current_old_order['hold_reason']; $set_vals['hold_date'] = ($update['hold_date'] !== $current_old_order[ 'hold_date' ]) ? $update['hold_date'] : $current_old_order['hold_date']; //perform backup of past data $query = " INSERT INTO production_data_archive (order_id, job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date, insert_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); "; $statement = $pdo->prepare($query); $statement->execute([ $current_old_order['id'], $current_old_order['job_number'], $current_old_order['enterprise'], $current_old_order['part_number'], $current_old_order['description'], $current_old_order['qty'], $current_old_order['line_item'], $current_old_order['as400_ship_date'], $current_old_order['hold_reason'], $current_old_order['hold_date'], $current_timestamp ]); //update old record $query = " UPDATE production_data SET part_number = :part_number, description = :description, qty = :qty, as400_ship_date = :as400_ship_date, hold_reason = :hold_reason, hold_date = :hold_date WHERE id = :order_id "; $statement = $pdo->prepare($query); $statement->execute([ 'part_number' => $set_vals['part_number'], 'description' => $set_vals['description'], 'qty' => $set_vals['qty'], 'as400_ship_date' => $set_vals['as400_ship_date'], 'hold_reason' => $set_vals['hold_reason'], 'hold_date' => $set_vals['hold_date'], 'order_id' => $current_old_order['id'], ]); //record result as accepted with a change $update_query_results['accepted_change'][] = $update; } else if ( $no_change == 0 ) { //record result as accepted no change $update_query_results['accepted_no_change'][] = $update; } } //order is new because it has no matches else if ( $find_match == "Error: No match detected!" ) { //insert new record $query = " INSERT INTO production_data (job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, hold_reason, hold_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "; $statement = $pdo->prepare($query); $statement->execute([ $update['job_number'], $update['enterprise'], $update['part_number'], $update['description'], $update['qty'], $update['line_item'], $update['as400_ship_date'], $update['hold_reason'], $update['hold_date'], ]); $update_query_results['new'][] = $update; } //order has duplicate line items else if ( $find_match == "Error: Duplicate line items detected!" ) { $update_query_results['rejected'][] = $update; } //increase count $count++; } else { //if array isn't empty; increase count if (array_filter($column)) $count++; } } //output update query results $s = serialize($update_query_results); //write results to file $fp = fopen($path_results . $results_prod_log, 'w'); fwrite($fp, serialize($update_query_results)); fclose($fp); function search_job_and_line_item ($job_number, $line_item, $result) { $return_key = []; //loop through result and check for matches foreach ( $result as $key => $value ) if ( $value['job_number'] == $job_number && $value['line_item'] == $line_item ) $return_key[] = $key; //more than one match; return error if ( count ( $return_key ) > 1 ) return "Error: Duplicate line items detected!"; //one match; return the match else if ( count ( $return_key ) == 1 ) return $return_key; //no matches; return error else return "Error: No match detected!"; } ?> I'm not super proud of it. But, it works.
  3. 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.
  4. Okay I think I understand. I'm going give it another shot.
  5. Yes I saw that documentation. Sorry I guess I misunderstand. I thought that line refers to the columns in the CSV file..so if I didn't want the last column wofc, I would do... (@dummy, @dummy, enterprise, part_num, desc, qty, line_item, job_num, work_order, psm, date_change_flag, scheduled_ship_date, on_hold, on_hold_reason, total_hours, @dummy) Or if the table column names were different I would just do: (@dummy, @dummy, diff_name_enterprise, diff_name_part_num, diff_name_desc, qty, line_item, job_num, work_order, psm, date_change_flag, scheduled_ship_date, on_hold, on_hold_reason, total_hours, worfc)
  6. @Barand Sorry for my delay. I've been diligently working on this and also trying to spell out some of the requirements I need to keep in mind. I will have an update for you later today. One question I have real quick about your query here: ## ## load the csv data into the table ## $db->exec("LOAD DATA LOCAL INFILE 'prod_data.csv' INTO TABLE production_csv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (@dummy, @dummy, enterprise, part_num, desc, qty, line_item, job_num, work_order, psm, date_change_flag, scheduled_ship_date, on_hold, on_hold_reason, total_hours, worfc) "); I've looked for a good example and haven't found one yet that shows how to specify which values go into what table columns if the columns in the CSV don't match the order of the table columns in MySQL. Do I list the fields after "FIELDS"? Also, I'm trying to learn more about triggers as you suggested instead of having the archive function. I put my script on the live server, made a .BAT file to call it in the console, and then setup a task in Windows Task Scheduler. When I tell it to "run" the task manually it works fine and as intended. But, when it fires the task automatically as scheduled it doesn't do anything at all. I have to look into this as well... 😑 But, it's getting there.
  7. Hmm...not through an automated method (such as an OBDC connection). They won't allow me to do that. But, maybe an email to the right person and someone could manually change it...that's possible..my biggest concern is when it goes to update any changes to orders (daily) that it could overwrite important information that's necessary for the order.. https://imgur.com/Lo8a2uT In that example, I tell it to find orders with that job # and line item C and look for any difference between the fields, if there is a difference, backup the previous line item, and overwrite with new changes. But in this case, it would overwrite both rows 843 and 845
  8. Lol our AS400 team is one guy and they never do much to improve it...so I'm thinking that's not going happen. Its only a percentage of .92% of all the orders (granted that information is based off of only 1 day of orders).... Do you see a way that it could be accounted for somehow? Assuming the AS400 tech won't do anything about it that is...
  9. Okay I get 12 entries out of 1293 rows Array ( [job_number] => 22149221 [enterprise] => SHRVIE [line_item] => E [total] => 2 ) Array ( [job_number] => 22992071 [enterprise] => SHRVIE [line_item] => E [total] => 2 ) Array ( [job_number] => 23518411 [enterprise] => LIDLUS [line_item] => C [total] => 2 ) Array ( [job_number] => 23518431 [enterprise] => LIDLUS [line_item] => C [total] => 2 ) Array ( [job_number] => 23565391 [enterprise] => LIDLUS [line_item] => B [total] => 2 ) Array ( [job_number] => 23586751 [enterprise] => SONIC [line_item] => [total] => 3 ) Array ( [job_number] => 23595111 [enterprise] => LIDLUS [line_item] => A [total] => 2 ) Array ( [job_number] => 23598551 [enterprise] => LIDLUS [line_item] => A [total] => 2 ) Array ( [job_number] => 23598551 [enterprise] => LIDLUS [line_item] => B [total] => 2 ) Array ( [job_number] => 23598561 [enterprise] => LIDLUS [line_item] => B [total] => 2 ) Array ( [job_number] => 23598561 [enterprise] => LIDLUS [line_item] => C [total] => 2 ) Array ( [job_number] => 23606661 [enterprise] => BURGER [line_item] => Q )
  10. I'm trying to figure out how common it is now. I just wrote this code but it's super slow. I'm trying to find out how many orders have more than one of the same line item: ## ## Figure out how many orders exist which have duplicate line items ## $records = $pdo->query("SELECT * FROM production_data"); $orders = []; foreach ($records as $r) { //is not in array so create it if ( !in_array( $r['job_number'], $orders ) ) $orders[ $r['job_number'] ] = []; foreach ( $orders as $key => $value ) { $get_lines = $pdo->query("SELECT line_item FROM production_data WHERE job_number = $key"); foreach ( $get_lines as $line ) { if ( !isset ( $orders[$key][$line['line_item']] ) ) $orders[$key][$line['line_item']] = 1; else $orders[$key][$line['line_item']] + 1; } } } echo '<pre>'; print_r($orders); exit();
  11. The same part number can be used in several orders and the part number can be revised on occasion if the order was entered with a bad part number...I know I'm not making this easy... Our system is ridiculous.
  12. See the entry on the bottom though? It's two entries with the same job number and same line item but a different description and quantity all together.
  13. Ugh...the way we store information in the AS400 is a nightmare. https://imgur.com/8gIpgg1 I have no real way to uniquely identify a job/line item...I'm trying to figure out a way to cross check the information in my database with what comes out of the AS400. I thought I could do that with the line item but see those orders in the screenshot? Order 23606661 has two line items of Q with completely different part numbers, qty, and description... Very cool tutorial. I look forward to doing it. Any ideas on how to cross check the info like I am trying to do? For example, today those orders could look like that but tomorrow a revision could have been done to one or more of those orders. I have other data tied to the order using it's id when I do the initial insert. I'm trying to post the revisions, archive the old info, and retain the ability to identify the order due to it's relationship with other data in the DB.
  14. @Barand Wow that's awesome. I'm going give it a try shortly. I've been doing the same thing with SQL as I have been with arrays. It's obvious to me that there are a lot of things I used to do years ago in PHP which I could have been handling with SQL statements themselves. I recently looked at some books on SQL on Amazon but didn't order one. Maybe I need to...often times when I'm looking online I don't find comprehensive examples. I'm sure they are out there but I just haven't come across them. One question I have about it: Is it just doing a mass update of the records in production_data? Here is what I programmed by the end of Friday: //loop through new orders array foreach ( $orders as $order ) { foreach ($result as $row) { //job already exists in DB if ( $order['job_number'] == $row['job_number'] && $order['line_item'] == $row['line_item'] ) { //check description field if ( $order['description'] != $row['description'] ) { //echo $order['job_number'] . ' : description is different. New: '. $order['description'] .' | Old: '. $row['description'] .'<br>'; } else { //echo $order['job_number'] . ' : description is same<br>'; } //check AS400 ship date if ( $order['scheduled_ship_date'] != $row['as400_ship_date'] ) { echo $order['scheduled_ship_date'] . ' : ship date is different. New: '. $order['scheduled_ship_date'] .' | Old: '. $row['as400_ship_date'] .'<br>'; } else { echo $order['scheduled_ship_date'] . ' : ship date is same New: '. $order['scheduled_ship_date'] .' | Old: '. $row['as400_ship_date'] .'<br>'; } $silly_count++; //job does not exist } else { //echo $row['as400_ship_date'].'<br>'; //echo $order['job_number'] . ' LN: ' . $order['line_item'] . ' has not match.<br>'; $silly_count++; } } $silly_count++; } I'm checking for two things before I archive/update a record; is the description or date from AS400 different than what is stored in MySQL. Then I use the archives to show the user past changes made to the order in the AS400. Sometimes those changes can be very small; like a dimensional change (ie 32 1/4" goes to 32 3/4"). So I built this to help the user more easily see changes to an order: https://imgur.com/yC4sNce Sorry I was more specific in my requirements. I look forward to trying this out and getting back to you with the results. Do you have any reading suggestions when it comes to learning how to better harness the power of SQL?
  15. @requinix Here is that code I mentioned earlier... //fetch production history data if( isset ( $action ) && $action == 'get_production_history' ) { $order_id = $_GET['order_id']; $production_history = []; $last_recorded = 0; $dept_codes = [5,6,7,8,10,11,12]; foreach ( $dept_codes as $d ) { $production_history[$d] = []; } //echo '<pre>'; //loop through depts foreach ( $dept_codes as $d ) { //loop through returned db rows foreach ( get_production_history( $order_id, $pdo ) as $row ) { if( $row['dept_code'] == $d ) { //set start time if ( !in_array ( $row[ 'id' ], $production_history[ $d ]) && $row[ 'status_id' ] === 1 ) { $production_history[$d][ $row['id'] ] = array( 'submit_time' => $row[ 'submit_time' ], 'finish_time' => '', ); //record id $last_recorded = $row['id']; //set finished time } elseif ( $row[ 'status_id' ] === 3 ) { $production_history[$d][ $last_recorded ]['finish_time'] = $row[ 'submit_time' ]; } } } } //find records without a finish time and unset them foreach ( $production_history as $dept => $value ) foreach ($value as $k => $v) if (empty($v['finish_time'])) unset($production_history[$dept][$k]); //find departments without records and unset them foreach ( $production_history as $dept => $value ) if (empty($value)) unset($production_history[$dept]); $json = []; $dept_arr_count = 0; //sort by dept foreach ( $production_history as $dept => $value ) { //get length of dept array $dept_arr_size = count($production_history[$dept]); //if on first entry for dept print parent if ( $dept_arr_count == 0 ) { //generate parent JSON entry $json[] = array( 'pID' => $dept, 'pName' => get_dept_name( $dept, $pdo ), 'pStart' => '', 'pEnd' => '', 'pClass' => 'ggroupblack', 'pLink' => '', 'pMile' => 0, 'pGroup' => 2, 'pParent' => 0, //need to set this for those that are children 'pOpen' => 1, 'pDepend' => '', 'pCaption' => '', 'pNotes' => '', ); } $submit_time = (isset($production_history[$dept][$k]['submit_time'])) ? $production_history[$dept][$k]['submit_time'] : ''; $finish_time = (isset($production_history[$dept][$k]['finish_time'])) ? $production_history[$dept][$k]['finish_time'] : ''; //print children foreach ($value as $k => $v) { $json[] = array( 'pID' => $dept .''. $dept_arr_count+1, 'pName' => '', 'pStart' => $production_history[$dept][$k]['submit_time'], 'pEnd' => $production_history[$dept][$k]['finish_time'], 'pClass' => 'ggroupblack', 'pLink' => '', 'pMile' => 0, 'pGroup' => 0, 'pParent' => $dept, //need to set this for those that are children 'pOpen' => 1, 'pDepend' => '', 'pCaption' => '', 'pNotes' => '', ); $dept_arr_count++; } //reached end of dept array if ( $dept_arr_size == $dept_arr_count ) { $dept_arr_count = 0; } } header('Content-type: text/javascript'); print(json_encode($json, JSON_PRETTY_PRINT)); } I was pretty proud of it haha. Here is what it generates... https://imgur.com/JW9Npz2
  16. Lol...just did a count on how many times that loop cycled through...I was off a bit from 40-50k.... It was more like 4,587,564.... Just a bit off...
  17. foreach ($result as $row) { if ( $order['job_number'] == $row['job_number'] && $order['line_item'] == $row['line_item'] ) { echo 'Found match on ' . $row['id'] . '<br><br>'; } else { echo $order['job_number'] . ' LN: ' . $order['line_item'] . ' has not match.<br>'; } } That loop does essentially what I was trying to do. It's just slow...looping through all records in the table for each record in the CSV. Okay, here is my table structure: -- -- Table structure for table `production_data` -- DROP TABLE IF EXISTS `production_data`; CREATE TABLE IF NOT EXISTS `production_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `job_number` int(8) NOT NULL, `enterprise` tinytext NOT NULL, `part_number` text NOT NULL, `description` text NOT NULL, `qty` int(11) NOT NULL, `line_item` varchar(11) NOT NULL, `as400_ship_date` date DEFAULT NULL, `hold_status` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; COMMIT; And here is a list of the columns I'm actually using from the CSV. It has other columns I'm not using though. //setup col vars for easier editing $enterprise = $column[2]; $part_num = $column[3]; $desc = $column[4]; $qty = $column[5]; $line_item = $column[6]; $job_num = $column[7]; $work_order = $column[8]; $psm = $column[9]; $date_change_flag = $column[10]; $scheduled_ship_date = $column[11]; $on_hold = $column[12]; $on_hold_reason = $column[13]; $total_hours = $column[14]; $worfc = $column[15]; After I find a match I'd like to compare certain fields in DB record against CSV row to see which ones have differences then make a backup of DB record and update the DB record with the new information from the CSV.
  18. Exactly what I'm trying to do. I've been learning about arrays and how much faster it is to do large complicated checks using them instead of hitting the database with repeated queries...which is why I'm trying to do it this way. If I wrote this with a query, it would probably result in 40,000-50,000 queries before it finished running...at least how I know to write it lol. So I did get the key from the matched array and the documentation mentions how array_search returns the key of it's first match. So on some matchs I am getting the wrong keys returned....maybe this is the wrong way to go about this... Basically, if the DB already has a job number and a line item which matches the new data; I want to check each field of that DB row against the new data to see if anything is different. If there is a difference I want to update those records and backup the old records in a different table. I am comparing a large CSV file against a table with almost 5,000 records.
  19. @Barand It's just a lot of values to compare against. I was trying to store all the rows of the particular table in an array and use the array to do the comparisons on; then when certain criteria is met, make an update on the specific record a match was found on.
  20. Hello, I'm trying to store the match I get in $result to a variable so that I can easily compare its other values against other values. Here is my code.. //loop through new orders array foreach ( $orders as $order ) { //compare job number and line item against database information for a match if ( array_search ( $order[ 'job_number' ], array_column ( $result, 'job_number' ) ) !== false && array_search ( $order[ 'line_item' ], array_column ( $result, 'line_item' ) ) !== false ) { $db_match = key($result); echo '<br><br>'.$db_match.'<br><br>'; echo 'Job: ' . $order['job_number'] . '/Line Item: ' . $order['line_item'] . ' was found<br>'; //new order which doesn't exist in database } else { echo 'Job: ' . $order['job_number'] . '/Line Item: ' . $order['line_item'] . ' was not found<br>'; } } When I do $db_match = key($result); I get 0 each time and when I do: $db_match = array_search ( $order[ 'job_number' ], array_column ( $result, 'job_number' ) ) !== false && array_search ( $order[ 'line_item' ], array_column ( $result, 'line_item' ) ) !== false; I get 1 each time...which I assume is the number of matches which meet that criteria instead of the array key I found a match on.
  21. I'll post some code later that I wrote yesterday building an array which is then passed as JSON to generate a Gannt chart on the fly. I'm proud of it for now...until you guys review it and tell me how repetitive my code is haha. It's cool learning to be more efficient with my code and really cool learning how to better build and interact with arrays. Back when I was still developing full time before I stepped away from it for several years; arrays was always something I had trouble with. I feel like I'm learning a lot about them now and it's making my life a lot easier.
  22. Okay very cool. Thanks a lot @requinix
  23. So $number is essentially being returned as an argument for the array_map() function?
  24. Also, what does the &$value do vs just $value in the below example? array_walk( $fruits, function( &$value, $key ) { $value = "$key is ".strtoupper($value); });
×
×
  • 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.