Jump to content

mongoose00318

Members
  • Posts

    250
  • Joined

  • Last visited

Posts posted by mongoose00318

  1. I've trying something like this so far: 

    $sql = '
    		SELECT a.id
    		     , a.order_id
    		     , a.job_number
    		     , a.line_item
    		     , a.insert_time
    		     , a.as400_ship_date
    		FROM production_data_archive a 
    		     LEFT JOIN
    		     production_data_archive b
    		         ON a.job_number = b.job_number
    		         AND a.line_item = b.line_item   
    		         AND a.insert_time < b.insert_time
    		WHERE b.job_number IS NULL AND a.as400_ship_date >= now()-interval 2 month AND a.as400_ship_date <= now()
    		ORDER BY a.job_number, a.line_item 
    	';
    	$stmt = $pdo->query($sql);
    	$data = $stmt->fetchAll();
    
    	$i = 0;
    	$j = '';
    	$l = [];
    	foreach ( $data as $k => $v ) {
    		if( $v['job_number'] == $j ) {
    			$l[] = $v['line_item'];
    			
    		} else {
    			
    			echo $j . ' : ' . count($l).'<br>';
    			$j = $v['job_number'];
    			$l = Array();
    		}
    
    	}

    Its not going great though.

  2. On 7/8/2021 at 3:32 PM, Barand said:

    Try

    TABLE: production_data_archive;
    +----+----------+------------+-----------+---------------------+
    | id | order_id | job_number | line_item | insert_time         |
    +----+----------+------------+-----------+---------------------+
    |  1 |    16824 |   22000412 | A         | 2021-03-26 00:00:00 |
    |  2 |    16824 |   22000412 | A         | 2021-03-30 00:00:00 |
    |  3 |    16824 |   22000412 | A         | 2021-04-09 00:00:00 |
    |  4 |    16825 |   22000412 | B         | 2021-03-26 00:00:00 |
    |  5 |    16825 |   22000412 | B         | 2021-03-29 00:00:00 |
    |  6 |    16825 |   22000412 | B         | 2021-04-06 00:00:00 |
    +----+----------+------------+-----------+---------------------+
    
    
    SELECT a.id
         , a.order_id
         , a.job_number
         , a.line_item
         , a.insert_time
    FROM production_data_archive a 
         LEFT JOIN
         production_data_archive b
             ON a.job_number = b.job_number
             AND a.line_item = b.line_item   
             AND a.insert_time < b.insert_time
    WHERE b.job_number IS NULL;
    
    +----+----------+------------+-----------+---------------------+
    | id | order_id | job_number | line_item | insert_time         |
    +----+----------+------------+-----------+---------------------+
    |  3 |    16824 |   22000412 | A         | 2021-04-09 00:00:00 |
    |  6 |    16825 |   22000412 | B         | 2021-04-06 00:00:00 |
    +----+----------+------------+-----------+---------------------+

     

    @BarandHey Barand, I was curious is there a way I can use this query but exclude any jobs that have more than one line item? For example, above you returned line_item A and B for job 22000412. So because that job has more than one line item I would not want it returned in the query. Hopefully I worded this well...

  3. I'm sure some of us here are aware of this current threat to Apache servers.

    I have a WAMP installation of Apache on a dedicated server with Windows 10. I've been trying to determine if my version of of WAMP has this logging module installed with no success so far; but still researching.

    Has anyone else started patching their servers or have any input/feedback to this vulnerability?

     

  4. Okay awesome man. I've been playing around with it and I am going to continue to. I love learning more about SQL.

    I have found there is so much I've done in PHP when it comes to data handling that could be handled all with a proper SQL statement. I just didn't have the knowledge I have now but I still have more to learn. 

    I have two books on SQL that are helpful that I reference on a regular basis but sometimes I do get confused with combining certain parts of a SQL statement. 

    I greatly appreciate all that you have taught me! Thank you!

  5. @BarandSorry for the late response man, I haven't had the time to get back to this project this week until now. Yea man, that works great! 

    Can you explain a little more how the "HAVING COUNT(*) > 1" works in the first statement?

    I've always had trouble understanding how to properly use the count function in a SQL statement other than simple statements where all I'm doing is something like "SELECT COUNT(*).." to count rows, etc.

    I do understand the difference between a UNION and a JOIN...which is interesting because I really thought you were going to use a JOIN on the same table to accomplish this. Shows how much I know!

  6. @gizmola

    First off, thanks for the validation that this is a genuine and NORMAL part of server administration. 

    Second, I totally agree AWStats is old and not to mention clunky. It really doesn't do what I want. I have looked into some alternatives before but none of the ones you mentioned ring a bell so I am definitely going to look into it.

    All around, hats off to you! Thanks for the detailed post and all of the information...I am going to do some digging into some of your suggestions! 

    • Like 1
  7. Here is my table structure:

    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 DEFAULT CHARSET=latin1;
    COMMIT;

    And here is some sample data that matches what I am looking for:

    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
    (22277, 23669401, 'BURGER', 'BK9009FM.20CLDWG', 'BURGER KING 9\'OAH CLEARNCE BAR WOODGRAIN||20/20 DESIGN WOOD GRAIN VINYL BOTH SIDES||DWG||DOES NOT INCLUDE ANCHOR BOLTS||', 'S', 1, 'D', '2021-11-22', '', '0000-00-00', '2021-07-14 11:35:27'),
    (22276, 23669401, 'BURGER', 'BK7010BA.OCUWG', 'BURGER KING BOLTS FOR 9\'OAH OCU CANOPY||(4) 3/4\"DIAX48\" BOLTS||HIGH STRENGTH||W/HHN AND WASHERS AND TEMPLATE||', 'S', 1, 'E', '2021-11-22', '', '0000-00-00', '2021-07-14 11:35:27'),
    (22278, 23669401, 'BURGER', 'BK9010FM.OCUWG49LG', 'BURGER KING 9\'OAH OCU CANOPY 49\" LG||DISPLAY WOOD GRAIN VINYL BOTH SIDES||DWG BK7067CP_OCU_2019_3DR3  #195336||DWG BK7067CP_OCU_ADPTRKIT2019||', 'S', 1, 'C', '2021-11-22', '', '0000-00-00', '2021-07-14 11:35:27'),
    (22272, 23669401, 'BURGER', 'BK1072CS.20', 'BURGER KING 6\' DIA ID SF SGN||20/20 DESIGN||GE TETRA MAX LEDS WITH 3\" RETURN||', 'S', 1, 'A', '2021-11-22', '', '0000-00-00', '2021-07-14 11:35:27'),
    (38162, 23669401, 'BURGER', 'BK3023RF.0V', 'BURGER KING 1\'7x3 DIR FACE||(1) DRIVE THRU(ARROW RIGHT)/(ARROW LEFT)||DRIVE THRU||', 'M', 2, 'F', '2021-11-22', '', '0000-00-00', '2021-12-06 12:35:25'),
    (38163, 23669401, 'BURGER', 'BK7009BA.20CLWG', 'BURGER KING BOLTS FOR 9\'OAH CLEARANCE||(4) 1\"DIAX42\" BOLTS||HIGH STRENGTH||W/HHN AND WASHERS AND TEMPLATE||', 'S', 1, 'F', '2021-11-22', '', '0000-00-00', '2021-12-06 12:35:25');
    COMMIT;

    I'm trying to make a query that grabs items like 38162 and 38163 that have the same job_number but duplicate line_items as well as any job_numbers that have empty values for line_items.

  8. Yes I guess that sums it up..I was just tinkering with AWStats and I made a backup of the original access log and created a new one. It imported that information fine. The line it has a problem with in the original file looks like this:

    Quote

    This means each line in your web server log file need to have "common log format" like this:
    111.22.33.44 - - [10/Jan/2001:02:14:14 +0200] "GET / HTTP/1.1" 200 1234
    And this is an example of records AWStats found in your log file (the record number 50 in your log):
    ::1 - - [13/Jul/2020:06:47:04 -0500] "-" 408 -

    I would assume the ::1 was when I accessed the page internally from the server. I wonder if there is a way for it to ignore problem lines.

  9. Good morning everyone!

    I was just looking to pick everyone's mind on something I would like to do...

    So, Apache has it's access log already which essentially already does most of what I want but it stores it in a flat file that is always large and even if it isn't it still doesn't provide me with everything I want to be able to do.

    I would like to be able to store all of that type of information (url requests whether coming from front-end ajax or a backend script and any parameters within the URL) and the user_id that is stored within a $_SESSION.

    Does anyone have any thoughts on how this could be accomplished? I built this little function just for testing purposes:

    function log_uri ( $pdo ) {
    
    	//check that user is set
    	if ( isset ( $_SESSION['user_id'] ) ) {
    
    		$query = '
    			INSERT INTO log_uri (user_id, uri) VALUES (:user_id, :page) 
    		';
    		$statement = $pdo->prepare($query);
    		$statement->execute([
    			':user_id' => $_SESSION['user_id'],
    			':page' => $_SERVER['REQUEST_URI'],
    		]);
    
    	}
    
    }

    But, it does not do what I want. Mainly, because it is only catching backend requests and not any of multiple AJAX requests that are happening very often and are of more interest to me than the back end requests. Here is what it a sample of what it captures so far:

    image.png.37e0d026828ca1afe824d84bc55b9527.png

    Any thoughts? Maybe there is a way to detect any ajax response or something? Or maybe I can modify the Apache logging system to go into a database and hopefully pick up the user_id?

  10. Okay so I've modified the query to get the additional data I needed and it works fine until I add the 'AND a.job_number LIKE :search'

    It doesn't return any records no matter what value I put in for the :search. Am I doing something wrong here?

    SELECT
        lr.ref_request_id,
        a.job_number,
        a.enterprise,
        a.line_item,
        a.insert_time,
        a.description,
        l.make,
        l.description,
        lr.qty,
        log.name AS issued_to,
        lr.submit_time AS issue_date
    FROM
        production_data_archive a
    LEFT JOIN production_data_archive b ON
        a.job_number = b.job_number AND a.line_item = b.line_item AND a.insert_time < b.insert_time
    LEFT JOIN leds_requests lr ON
        lr.order_id = a.order_id AND lr.status_id = 2
    LEFT JOIN leds l ON
        l.id = lr.product_id
    LEFT JOIN login log ON
        lr.issued_to = LOG.user_id
    WHERE
        b.job_number IS NULL AND a.order_id IN(
        SELECT
            order_id
        FROM
            leds_requests
        WHERE
            status_id = 2
    ) AND a.job_number LIKE :search

     

  11. 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.

  12. 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?

  13. 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!

  14. --
    -- 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;

     

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