Jump to content

mongoose00318

Members
  • Posts

    253
  • Joined

  • Last visited

Posts posted by mongoose00318

  1. I'm trying to figure out how to select job numbers and their line items by searching for a job number but only their latest entry in the table. This table is an archive table. As changes are made to rows in the main production_data table, the row's current values are backed up in this archive table. Eventually an order falls off completely and only exists in this archive table.

    Here is the table schema

    --
    -- Table structure for table `production_data_archive`
    --
    
    DROP TABLE IF EXISTS `production_data_archive`;
    CREATE TABLE IF NOT EXISTS `production_data_archive` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_id` int(11) NOT NULL,
      `job_number` int(8) NOT NULL,
      `enterprise` tinytext NOT NULL,
      `part_number` text NOT NULL,
      `description` text NOT NULL,
      `psm` tinytext NOT NULL,
      `qty` int(11) NOT NULL,
      `line_item` varchar(11) NOT NULL,
      `as400_ship_date` date DEFAULT NULL,
      `hold_reason` text NOT NULL DEFAULT '0',
      `hold_date` date DEFAULT NULL,
      `insert_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time order was inserted',
      `archive_time` timestamp NULL DEFAULT NULL COMMENT 'this column was added 4-19-21',
      PRIMARY KEY (`id`),
      KEY `job_line` (`job_number`,`line_item`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    COMMIT;

    Here is a sample of data when I do a search for a specific job_number:

    image.thumb.png.e08838dcf3ad5dec66036740cdbb60f6.png

    In the image above I search for a job_number and then highlighted the most recent entries for that job_number. Those are the records I would have wanted to come out of the query.

  2. Here is a screenshot of the U/I:

    image.thumb.png.7d8fefda5044636596371001f7c5f0e8.png

    The request gets put in (status of 0) then the reviewer can approve the request (status of 1) and the request moves to the "Approved & Not Pulled" tab. When the physical product is pulled from the shelf another user marks who they gave it to and then it has a pulled status (status of 2). Does that help any? Also, here is a query that I wrote which pulls the "Approved & Not Pulled" requests.

    function get_not_pulled( $pdo ) {
    
    	$query = "
    				SELECT
    					lr.*,
    					l.make,
    					l.part_number,
    					l.description,
    					pd.job_number,
    					pd.as400_ship_date,
    					pd.line_item,
    					pd.enterprise,
    					pd.description AS order_description,
    					pd.qty AS order_qty,
    					log.name AS user_full_name
    				FROM
    					leds_requests lr
    				LEFT JOIN
    					leds l
    				ON
    					l.id = lr.product_id
    				LEFT JOIN
    					production_data pd
    				ON
    					pd.id = lr.order_id
    				LEFT JOIN
    					login log
    				ON
    					log.user_id = lr.user_id
    				WHERE
    					lr.id IN(
    					SELECT
    						MAX(id)
    					FROM
    						leds_requests
    					WHERE
    						status_id = 1
    					GROUP BY
    						order_id,
    						product_id
    				) AND lr.id NOT IN(
    						SELECT
    					        ref_request_id
    					    FROM
    					        leds_requests
    					    WHERE
    					        status_id IN(2,3,4)
    				)
    				GROUP BY
    					order_id,
    					product_id,
    					job_number
    	";
    	$statement = $pdo->prepare($query);
    	$statement->execute();
    	$results = $statement->fetchAll();
    	
    	//loop through requests and add additonal information to the array before passing it to the browser
    	foreach ( $results as $k => $v ) {
    		$time = strtotime($results[$k]['submit_time']);
    		$results[$k]['submit_time'] = date('n/d @ h:iA', $time);
    
    		//get original requester information
    		if ( $results[$k]['status_id'] > 0 )
    			$results[$k]['original_request_info'] = get_original_requester_info($results[$k]['original_request_id'], $pdo);
    
    		//determine if the request is tied to an archived order
    		$results[$k]['archived'] = ( is_null( $results[$k]['job_number'] ) ) ? TRUE : FALSE;
    		
    		//deal with requests which are tied to archived orders
    		if ( $results[$k]['archived'] == TRUE ) {
    
    			//retrieve the archived order information by using order_id
    			$archived_data = get_archived_order_data_by_order_id( $results[$k]['order_id'], $pdo )[0];
    
    			//set the order data
    			$results[$k]['as400_ship_date'] 	= $archived_data['as400_ship_date'];
    			$results[$k]['enterprise'] 			= $archived_data['enterprise'];
    			$results[$k]['job_number'] 			= $archived_data['job_number'];
    			$results[$k]['line_item'] 			= $archived_data['line_item'];
    			$results[$k]['order_description'] 	= $archived_data['description'];
    			$results[$k]['order_qty'] 			= $archived_data['qty'];
    
    		}
    
    	}
    	
    	return $results;
    
    }

    Does any of this help?

  3. 11 minutes ago, Barand said:

    What are these fields?

    • `original_request_id` int(11) NOT NULL,
    • `ref_request_id` int(11) DEFAULT NULL,

    original_request_id is what I would refer to as the "base" id of when the request was made (when it had a status of 0)

    ref_request_id is the id of the previous state of the request. for example:

    //status of 2 means the leds have been pulled, so 238 references the id of when it was approved (status of 1)
    (243, 235, 238, 17380, 54, 68, 2, 2, 44, '2021-04-21 19:11:56'),
    
    //status of 1 means approved and 235 references the original request (status of 0)
    (238, 235, 235, 17380, 55, 68, 2, 1, NULL, '2021-04-21 17:24:53'),

    Hopefully, I explained that well!

  4. --
    -- Table structure for table `leds_requests`
    --
    
    DROP TABLE IF EXISTS `leds_requests`;
    CREATE TABLE IF NOT EXISTS `leds_requests` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `original_request_id` int(11) NOT NULL,
      `ref_request_id` int(11) DEFAULT NULL,
      `order_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `product_id` int(11) DEFAULT NULL,
      `qty` int(11) NOT NULL,
      `status_id` int(11) NOT NULL COMMENT '0: Not Approved; 1: Approved & Not Pulled; 2: Approved & Pulled; 3: Denied; 4: Pulled & Returned',
      `issued_to` int(11) DEFAULT NULL,
      `submit_time` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    COMMIT;

    Here is some sample data as well of a request going through it's lifecycle. I had to manually correct parts of it which I showed in the screenshot above.

    --
    -- Dumping data for table `leds_requests`
    --
    
    INSERT INTO `leds_requests` (`id`, `original_request_id`, `ref_request_id`, `order_id`, `user_id`, `product_id`, `qty`, `status_id`, `issued_to`, `submit_time`) VALUES
    (243, 235, 238, 17380, 54, 68, 2, 2, 44, '2021-04-21 19:11:56'),
    (242, 236, 237, 17380, 54, 67, 2, 2, 44, '2021-04-21 19:11:47'),
    (238, 235, 235, 17380, 55, 68, 2, 1, NULL, '2021-04-21 17:24:53'),
    (237, 236, 236, 17380, 55, 67, 2, 1, NULL, '2021-04-21 17:24:37'),
    (236, 236, NULL, 17380, 44, 67, 2, 0, NULL, '2021-04-21 17:23:40'),
    (235, 235, NULL, 17380, 44, 68, 2, 0, NULL, '2021-04-21 17:23:40');
    COMMIT;

     

  5. Here's another function I'm having issues with that I suspect is primary key related:

    //send system message
    function send_system_message( $message, $users, $order_id, $pdo ) {
    	
    	//insert the chat message from the automated system
    	$query = "INSERT INTO chat_message(order_id, chat_message) VALUES(:order_id, :chat_message)";
    	$statement = $pdo->prepare($query);
    	$statement->execute([
    		'order_id' => $order_id,
    		'chat_message' => $message,
    	]);
    
    	//get message id
    	$last_insert_id = $pdo->lastInsertId();
    	
    	//determine highest chat message id in system and increment by 1
    	$chat_message_id = $pdo->query("SELECT MAX(chat_message_id) FROM chat_message")->fetch(PDO::FETCH_NUM)[0];
    	
    	//insert the chat_to_users records for this message
    	foreach ( $users as $user ) {
    
    		$query = "INSERT INTO chat_to_users(message_id, order_id, to_user_id, from_user_id) VALUES(:message_id, :order_id, :to_user_id, :from_user_id)";
    		$statement = $pdo->prepare($query);
    		$statement->execute([
    			'message_id' => $chat_message_id,
    			'order_id' => $order_id,
    			'to_user_id' => $user['user_id'],
    			'from_user_id' => 53,
    		]);
    	}
    
    	return $last_insert_id;
    	
    }

     

  6. I can't figure out where this is going wrong. But it obviously has to do with the primary key for the table. Here is the code:

    //set led request
    if ( isset ( $action ) && $action == 'submit_led_request' ) {
    
    	$order_id = $_GET['order_id'];
    	$products = explode(',', $_GET['product']);
    	$qty = explode(',', $_GET['qty']);
    
    	//make sure the same of number of records exist in each array
    	if ( count ( $products ) == count ( $qty ) ) {
    		
    		$original_request_id = $pdo->query("SELECT MAX(id) FROM leds_requests LIMIT 1")->fetch(PDO::FETCH_NUM)[0] + 1;
    
    		for ( $i = 0; $i <= count ( $products ) - 1; $i++ ) {
    
    			$query = "INSERT INTO leds_requests (original_request_id, order_id, user_id, product_id, qty, status_id) VALUES (:original_id, :order_id, :user_id, :product_id, :qty, 0)";
    			$statement = $pdo->prepare($query);
    			$statement->execute([
    				'original_id' => $original_request_id,
    				'order_id' => $order_id,
    				'user_id' => $_SESSION['user_id'],
    				'product_id' => $products[$i],
    				'qty' => $qty[$i],
    			]);
    
    		}
    
    		//setup variables for system wide message function
    		$send_to_users[] = ['user_id' => 54]; //r
    		$send_to_users[] = ['user_id' => 49]; //b
    		$send_to_users[] = ['user_id' => 55]; //g
    		$send_to_users[] = ['user_id' => 1]; //j
    		$message = 'An LED pull request has been made by: ' . get_user_full_name($_SESSION['user_id'], $pdo) . '. Please go to the LED Request Manager to review the request.';
    		
    		//send the messages
    		$chat_message_id = send_system_message( $message, $send_to_users, $order_id, $pdo );
    
    		//establish relationship for messages
    		$pdo->query('
    				INSERT INTO
    					chat_message_rel_leds(
    						chat_message_id,
    						original_request_id
    					) VALUES (
    						'. $chat_message_id .',
    						'. $original_request_id .'
    					)
    			');
    		
    	}
    	else {
    		exit();
    	}
    
    }

     

    Here is a screenshot of the problem:

    image.thumb.png.62008e70ef9561d550aa182942b00938.png

     

    If you look at row 235 and 236, they have a value of 233 and 234 in the 'original_request_id' column. If you look below 235, the last key was 232 so that should have been correct (original_request_id should be the initial key assigned to the record from the table). 

    Is my method for determining what that key is going to be fault in some way? IE: 

    $original_request_id = $pdo->query("SELECT MAX(id) FROM leds_requests LIMIT 1")->fetch(PDO::FETCH_NUM)[0] + 1;

    From what I understand, there isn't a way in PDO to get the current max primary key except after an insert function is performed. Is this correct and am I doing something wrong here?

  7. 18 minutes ago, Barand said:

    If we do a slightly different query

    
    SELECT 
          pd.id
        , pd.enterprise
        , pd.as400_ship_date
        , pd.hold_date
        , ps.order_id
        , ps.dept_code
        , ps.status_id 
    FROM
        production_data pd
        LEFT JOIN
        production_status ps ON ps.order_id = pd.id
                            AND ps.dept_code = 13
                            AND ps.status_id = 3
    WHERE pd.enterprise = "EXXON" 
          AND pd.as400_ship_date = '2021-03-02' 
          AND pd.hold_date = "0000-00-00";
          
    +-------+------------+-----------------+------------+----------+-----------+-----------+
    | id    | enterprise | as400_ship_date | hold_date  | order_id | dept_code | status_id |
    +-------+------------+-----------------+------------+----------+-----------+-----------+
    | 15298 | EXXON      | 2021-03-02      | 0000-00-00 |    15298 |        13 |         3 |
    | 15154 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15156 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15157 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15158 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15290 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15291 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15292 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15293 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15294 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15296 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15297 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15299 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15300 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15301 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15302 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15303 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 15304 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16589 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16590 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16593 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16594 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16597 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16598 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16601 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16602 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16605 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16606 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16609 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16610 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16613 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    | 16614 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
    +-------+------------+-----------------+------------+----------+-----------+-----------+
    32 rows in set (0.00 sec)

    Becasue it uses a LEFT JOIN, we get nulls where there was no matching record meeting the join criteria. These are the ones we want to count hence the ps.order_id IS NULL

    Awesome man. You're level of SQL knowledge is impressive. Thanks for the thorough explanation. Very cool indeed.

  8. -- phpMyAdmin SQL Dump
    -- version 4.9.2
    -- https://www.phpmyadmin.net/
    --
    -- Host: 127.0.0.1:3306
    -- Generation Time: Mar 24, 2021 at 12:21 PM
    -- Server version: 10.4.10-MariaDB
    -- PHP Version: 7.3.12
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SET time_zone = "+00:00";
    
    --
    -- Database: `fedheath_production`
    --
    
    -- --------------------------------------------------------
    
    --
    -- 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,
      `psm` tinytext NOT NULL,
      `qty` int(11) NOT NULL,
      `line_item` varchar(11) NOT NULL,
      `as400_ship_date` date DEFAULT NULL,
      `hold_reason` text NOT NULL DEFAULT '0',
      `hold_date` date DEFAULT NULL,
      `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
      PRIMARY KEY (`id`),
      KEY `job_line` (`job_number`,`line_item`)
    ) ENGINE=MyISAM AUTO_INCREMENT=16615 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `production_data`
    --
    
    INSERT INTO `production_data` (`id`, `job_number`, `enterprise`, `part_number`, `description`, `psm`, `qty`, `line_item`, `as400_ship_date`, `hold_reason`, `hold_date`, `insert_time`) VALUES
    (15290, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15291, 22113902, 'EXXON', 'LD0008RM.ABEX', 'LED RF REMOTE CONTROLLER KIT ABLE||EIGHT BUTTON WIRELESS REMOTE||DIRECT||EXXON MOBIL ONLY||', 'S', 1, 'J', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15292, 22113902, 'EXXON', 'LD0120GR.2ABEX', 'LED 12\" GRN 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT DOES NOT INCLUDE REMOTE||EXXON MOBIL ONLY||', 'S', 1, 'I', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15293, 22113902, 'EXXON', 'LD0120RD.2ABEX', 'LED 12\" RED 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT REMOTE NOT INCLUDED||EXXON MOBIL ONLY||', 'S', 1, 'H', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15294, 22113902, 'EXXON', 'LD0180RD.2ABEX', 'LED 18\" RED 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT REMOTE NOT INCLUDED||EXXON MOBIL ONLY||', 'S', 1, 'G', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15296, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||7 1/2\" X 53 1/2\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||* REGULAR SELECT MOBIL CARDS - DWG 210801.PP*||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 2, 'N', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15297, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15298, 22113902, 'EXXON', 'XM0135FM.0VNFPR', 'EXXONMOBIL 13\'4x5 VC DF FM SGN 9PNL 24V||(NO FACES)||PRINCIPAL PINN 24V ILLUMINATION||** LOAD ORDER: 2X NOURIA, 2X REGULAR||SELECT MOBIL CARDS, 1X REGULAR, 1X||DIESEL EFFICIENT, 1X SYNERGY, 1X DUNKIN,||1X SUBWAY **||', 'M', 1, 'C', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15299, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** SYNERGY - DWG 150805 **||', 'M', 2, 'K', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15300, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** DUNKIN - DWG 207346 **||', 'M', 2, 'L', '2021-03-02', 'FF', '0000-00-00', '2021-03-08 12:05:20'),
    (15301, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** SUBWAY - DWG 187262 **||', 'M', 2, 'M', '2021-03-02', 'FF', '0000-00-00', '2021-03-08 12:05:20'),
    (15302, 22113902, 'EXXON', 'XM3035FA.NOURIA', 'EXXONMOBIL 3x5 NOURIA FACE FOR TYPE B||ID 30\"CTS PSH THRU SF SGN ON FLAT ALUM||FACE. NEED DIGITAL PRINT* GRA EX210804||DWG EX3035FM_NOURIA||** NOURIA - DWG 210804 **||', 'M', 2, 'D', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
    (15303, 22113902, 'EXXON', 'XM9005FA.1NLDAB', 'EXXONMOBIL 1\'6X5 1PR ABLE FACE FOR SIGNS||(TO USE 12\" ABLE LED) FOR RED OR GREEN||LEDS OR REMOTE NOT INCLUDED||INCLUDES LABOR AND AL ANG TO INSTALL LED||** (2) REGULAR, (2) DIESEL EFFICIENT||(BLUE)(SLIP IN PRODUCT PANELS ALL GRADES||) - DWG 210802 **||', 'M', 4, 'F', '2021-03-02', 'FF', '0000-00-00', '2021-03-01 14:35:19'),
    (15304, 22113902, 'EXXON', 'XM9005FA.1NLDAB2X', 'EXXONMOBIL 3X5 1-PR ABLE FACE FOR SIGNS||(TO USE 18\" ABLE LED) FOR RED OR GREEN||LEDS OR REMOTE NOT INCLUDED||INCLUDES LABOR AND AL ANG TO INSTALL LED||** REGULAR SELECT MOBIL CARDS (SLIP IN||PRODUCT PANELS ALL GRADES) - DWG||210801 **||', 'M', 2, 'E', '2021-03-02', 'FF', '0000-00-00', '2021-03-19 11:35:20'),
    (16601, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 13:35:28'),
    (15154, 22111012, 'EXXON', 'DP3000MS.2130', 'DIGITAL PRINT 21SF-30SF||** NOURIA - DWG 210294.DP **||', 'S', 2, 'D', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16'),
    (15157, 22111012, 'EXXON', 'TOOLING', 'TOOLING||** TO EMBOSS MOBIL ID **||', 'M', 543, 'B', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16'),
    (15158, 22111012, 'EXXON', 'XM9100CP.CLSP', 'XOM 11X10 CP SIGN BASE CLADDING(CSTM)||ALUMINUM WRAP||EX0100CP_3LDNOURIAVPN||** DWG 210294 **||', 'M', 1, 'C', '2021-03-02', 'WO', '0000-00-00', '2021-02-19 03:05:21'),
    (16614, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 15:05:22'),
    (16613, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 15:05:22'),
    (16610, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:35:25'),
    (16609, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:35:25'),
    (16602, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 13:35:28'),
    (16605, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:05:23'),
    (16589, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 16:05:28'),
    (16598, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 11:35:28'),
    (16594, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 21:05:30'),
    (16593, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 21:05:30'),
    (16590, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 16:05:28'),
    (16606, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:05:23'),
    (16597, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 11:35:28'),
    (15156, 22111012, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:      65.00||5\" X 44 1/2\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR SELECT MOBIL CARDS,||(2) REGULAR, (2) DIESEL EFFICIENT - DWG||210294 **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 6, 'E', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16');
    COMMIT;

     

    Here ya go. Sorry about that.

  9. @Barand Yes I'm sure about the Maria problem. I may just have an older version. It has said this to me before.

    Here is some sample data:

    -- phpMyAdmin SQL Dump
    -- version 4.9.2
    -- https://www.phpmyadmin.net/
    --
    -- Host: 127.0.0.1:3306
    -- Generation Time: Mar 24, 2021 at 12:02 PM
    -- Server version: 10.4.10-MariaDB
    -- PHP Version: 7.3.12
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SET time_zone = "+00:00";
    
    --
    -- Database: `fedheath_production`
    --
    
    -- --------------------------------------------------------
    
    --
    -- 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,
      `psm` tinytext NOT NULL,
      `qty` int(11) NOT NULL,
      `line_item` varchar(11) NOT NULL,
      `as400_ship_date` date DEFAULT NULL,
      `hold_reason` text NOT NULL DEFAULT '0',
      `hold_date` date DEFAULT NULL,
      `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
      PRIMARY KEY (`id`),
      KEY `job_line` (`job_number`,`line_item`)
    ) ENGINE=MyISAM AUTO_INCREMENT=16615 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `production_data`
    --
    
    INSERT INTO `production_data` (`id`, `enterprise`, `job_number`) VALUES
    (15290, 'EXXON', 22113902),
    (15291, 'EXXON', 22113902),
    (15292, 'EXXON', 22113902),
    (15293, 'EXXON', 22113902),
    (15294, 'EXXON', 22113902),
    (15296, 'EXXON', 22113902),
    (15297, 'EXXON', 22113902),
    (15298, 'EXXON', 22113902),
    (15299, 'EXXON', 22113902),
    (15300, 'EXXON', 22113902),
    (15301, 'EXXON', 22113902),
    (15302, 'EXXON', 22113902),
    (15303, 'EXXON', 22113902),
    (15304, 'EXXON', 22113902),
    (16601, 'EXXON', 22113902),
    (15154, 'EXXON', 22111012),
    (15157, 'EXXON', 22111012),
    (15158, 'EXXON', 22111012),
    (16614, 'EXXON', 22113902),
    (16613, 'EXXON', 22113902),
    (16610, 'EXXON', 22113902),
    (16609, 'EXXON', 22113902),
    (16602, 'EXXON', 22113902),
    (16605, 'EXXON', 22113902),
    (16589, 'EXXON', 22113902),
    (16598, 'EXXON', 22113902),
    (16594, 'EXXON', 22113902),
    (16593, 'EXXON', 22113902),
    (16590, 'EXXON', 22113902),
    (16606, 'EXXON', 22113902),
    (16597, 'EXXON', 22113902),
    (15156, 'EXXON', 22111012);
    COMMIT;

    Here is the data for the production_status table:

    -- phpMyAdmin SQL Dump
    -- version 4.9.2
    -- https://www.phpmyadmin.net/
    --
    -- Host: 127.0.0.1:3306
    -- Generation Time: Mar 24, 2021 at 12:03 PM
    -- Server version: 10.4.10-MariaDB
    -- PHP Version: 7.3.12
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SET time_zone = "+00:00";
    
    --
    -- Database: `fedheath_production`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `production_status`
    --
    
    DROP TABLE IF EXISTS `production_status`;
    CREATE TABLE IF NOT EXISTS `production_status` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `status_id` int(11) NOT NULL,
      `submit_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time user submits status change',
      `expected_complete_time` date DEFAULT NULL COMMENT 'expected completion time which user enters',
      `dept_code` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=49402 DEFAULT CHARSET=latin1 COMMENT='Status Types [ 0: Not Started, 1: In Progress, 2: Delayed, 3: Finished ]';
    
    --
    -- Dumping data for table `production_status`
    --
    
    INSERT INTO `production_status` (`id`, `order_id`, `user_id`, `status_id`, `submit_time`, `expected_complete_time`, `dept_code`) VALUES
    (49285, 15298, 30, 3, '2021-03-23 19:03:59', NULL, 13),
    (49192, 15298, 43, 3, '2021-03-23 12:53:52', NULL, 11),
    (49068, 15298, 43, 1, '2021-03-22 16:17:00', '2021-03-24', 11),
    (47659, 15298, 23, 3, '2021-03-08 16:42:17', NULL, 7),
    (46873, 15298, 23, 1, '2021-03-01 19:13:17', '2021-03-03', 7);
    COMMIT;

     

    Will this be enough? 

  10. SELECT
        pd.enterprise,
        COUNT(*) AS total
    FROM
        production_data pd
    WHERE
        enterprise = "EXXON" AND as400_ship_date = CAST("2021-03-02" AS DATE) AND hold_date = "0000-00-00" AND pd.id NOT IN(
        SELECT
            order_id
        FROM
            (
            SELECT
                MAX(id),
                order_id
            FROM
                production_status
            WHERE
                order_id = pd.id AND dept_code = 13 AND status_id = 3
        )
    )

    Okay I changed it to this but am still getting a syntax error.

  11. I am trying to use this to reference another table to determine if an item has shipped or not; if it has don't include it. But I'm having some issues with my SQL.

    SELECT
        pd.enterprise,
        COUNT(*) AS total
    FROM
        production_data pd
    WHERE
        enterprise = "EXXON" AND as400_ship_date = CAST("2021-03-02" AS DATE) AND hold_date = "0000-00-00" AND pd.id NOT IN(
        SELECT
            order_id
        FROM
            production_status
        WHERE
            order_id = pd.id AND dept_code = 13 AND status_id = 3
        ORDER BY
            id
        DESC
    LIMIT 1
    )

    I think the problem is where I have order_id = pd.id

    Do I need to provide table structures or is this some type of syntax error?

  12. Well I think I've found a solution. I knew there was a built in tooltip for HighCharts but I wasn't sure it would do exactly what I was trying to do with it. After researching more of it's settings I think I have it doing what I want but I am getting a JS error on my returned JSON.

    'Uncaught SyntaxError: Unexpected token u in JSON at position 0'

    Here is a sample of my returned JSON:

    0: {id: 14671, job_number: 22094872}
    1: {id: 14739, job_number: 22102672}
    2: {id: 14740, job_number: 22102682}
    3: {id: 14673, job_number: 22102712}
    4: {id: 14675, job_number: 22102722}
    5: {id: 14678, job_number: 22102742}
    6: {id: 14683, job_number: 22102782}
    7: {id: 14686, job_number: 22102902}
    8: {id: 14689, job_number: 22102912}
    9: {id: 14746, job_number: 22102922}
    10: {id: 14837, job_number: 22111742}

    And here is my updated code:

    const chart = Highcharts.chart('container', {
    		    chart: {
    		        type: 'column'
    		    },
    		    title: {
    		        text: '3 Week Forecast'
    		    },
    		    xAxis: {
    		        categories: <?php echo $formatted_dates; ?>
    		    },
    		    yAxis: {
    		        min: 0,
    		        title: {
    		            text: 'Total Line Items Due To Ship'
    		        },
    		        stackLabels: {
    		            enabled: true,
    		            style: {
    		                fontWeight: 'bold',
    		                color: ( // theme
    		                    Highcharts.defaultOptions.title.style &&
    		                    Highcharts.defaultOptions.title.style.color
    		                ) || 'gray'
    		            }
    		        }
    		    },
    		    legend: {
    		        align: 'right',
    		        x: -30,
    		        verticalAlign: 'top',
    		        y: 25,
    		        floating: true,
    		        backgroundColor:
    		            Highcharts.defaultOptions.legend.backgroundColor || 'white',
    		        borderColor: '#CCC',
    		        borderWidth: 1,
    		        shadow: false
    		    },
    		    tooltip: {
    		        useHTML: true,
    		        formatter: function() {
    		        	var json = $.getJSON('scripts/order_status.php?action=highchart_tooltip&date=' + this.point.category + '&enterprise=' + this.series.name);
    
    		        	$.each(json, function(i, item) {
    		        		console.log(item.job_number);
    		        	});
    		        },
    		        style: {
    		        	pointerEvents: 'auto'
    		        }
    		    },
    		    plotOptions: {
    		        column: {
    		            stacking: 'normal',
    		            dataLabels: {
    		                enabled: true
    		            }
    		        }
    		    },
    		    series: <?php echo $json; ?>
    		});

     

  13. Hello Everyone,

    I've started using HighCharts JS library to make dynamic charts and so far it is pretty impressive.

    Here is a screenshot of my chart that I'd like to make a custom behavior for:

    image.thumb.png.73ff724991efbc24b11be0b203238334.png

     

    Basically what I would like to do is when a user clicks on a point in a series within a certain category (for example on category 2021-02-15 the user clicks the series with a total of 35 line items) a box would pop out to the side of the series point the user has clicked on and would be populated with an ajax request. Here is my code so far:

            const chart = Highcharts.chart('container', {
    		    chart: {
    		        type: 'column'
    		    },
    		    title: {
    		        text: '3 Week Forecast'
    		    },
    		    xAxis: {
    		        categories: <?php echo $formatted_dates; ?>
    		    },
    		    yAxis: {
    		        min: 0,
    		        title: {
    		            text: 'Total Line Items Due To Ship'
    		        },
    		        stackLabels: {
    		            enabled: true,
    		            style: {
    		                fontWeight: 'bold',
    		                color: ( // theme
    		                    Highcharts.defaultOptions.title.style &&
    		                    Highcharts.defaultOptions.title.style.color
    		                ) || 'gray'
    		            }
    		        }
    		    },
    		    legend: {
    		        align: 'right',
    		        x: -30,
    		        verticalAlign: 'top',
    		        y: 25,
    		        floating: true,
    		        backgroundColor:
    		            Highcharts.defaultOptions.legend.backgroundColor || 'white',
    		        borderColor: '#CCC',
    		        borderWidth: 1,
    		        shadow: false
    		    },
    		    tooltip: {
    		        headerFormat: '<b>{point.x}</b><br/>',
    		        pointFormat: '{series.name}: {point.y}<br/>Total: {point.stackTotal}'
    		    },
    		    plotOptions: {
    		        column: {
    		            stacking: 'normal',
    		            dataLabels: {
    		                enabled: true
    		            }
    		        },
    		        series: {
    		        	cursor: 'pointer',
    		        	events: {
    		        		click: function ( event ) {
    							//this.name is the enterprise the user has clicked and even.point.category is the date
                              	//  this are the only two parameters I need to retrieve the necessary data from the database to populate the 'popout box'
    		        			alert(
    		        				'Enterprise: ' + this.name + ' for ' + event.point.category + ' clicked'
    		        			);
    		        		}
    		        	}
    		        }
    		    },
    		    series: <?php echo $json; ?>
    		});

     

    At the bottom of the code where 'series: { ' starts I've put some comments in. I am already getting the data I need from the click event; the only thing I'm stumped on is how to make some type of box popout when the user clicks it; the position of the box would have to be relative to the position of where the user clicked I would think...

  14. image.thumb.png.2199fb35a35388bfff825de9e7b0a9c8.png

     

    I got it working with this:

    		function updateTableAfterSubmit() {
    			table.ajax.reload( function (json){
    				auto_btn_height_width();
    			}, false );
    		}
    
    		function auto_btn_height_width() {
    			$('td.mfg-view-cols').each(function() {
    				tdWidth = $(this).width();
    				tdHeight = $(this).height();
    
    				$(this).find('button').css({'width': tdWidth, 'height': tdHeight});
    
    				console.log(tdHeight);
    			});
    			console.log($('td.mfg-view-cols').length);
    		}

    Then just had to make sure I identified the right events to do call backs on. Works great now.

×
×
  • 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.