Jump to content

sbrinley

Members
 View Profile  See their activity

  • Posts

    21

  • Joined

  • Last visited

    Never

 Content Type 

Everything posted by sbrinley

  1. sbrinley
    For those of you that might need this information in the future I found out the following solutions: I was not able to convert the string directly so what I did instead was create a new table that allowed me to quickly look up a BPCS date into a regular date or week number. After doing that I was able to make the script work very easily. Also the answers to my 1 question is: Question: When you use a ODBC connection what is the SQL based on? In this case the AS400 or PHP's ODBC stuff? Answer: Yes it is the ODBC connection however the ODBC connection might have capabilities that are not supported by the connected database it is also limited by the database as well. in this case I could use most functions but not functions that had specific limitations such as the concat function in this case. Lastly here is the completed code for the final project: <?php require_once("includes/functions.inc.php"); ?> <!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN http://www.w3.org/TR/html401/loose.dtd> <html> <head> <title>13 Week Report Results</title> </head> <body> <?PHP $str_week = $_POST['str_week']; $end_week = $_POST['end_week']; $num_weeks = ($end_week + 1) - $str_week; $str_week_inc = $str_week +1; $str_date = weekDayToTime($str_week, date('Y')); $end_date = cleanTime(518400 + weekDayToTime($end_week, date('Y')));; $report_type = $_POST['report_type']; $bpcs_date = date("Ymd", (weekDayToTime(($end_week + 1), date('Y')))); //echo $str_week . " str week<br />"; //echo $end_week . " end week<br />"; //echo $num_weeks . " num weeks<br />"; //echo $str_week_inc . " str week inc<br />"; //echo date("n/j/y", $str_date) . " str date<br />"; //echo date("n/j/y", $end_date) . " end date<br />"; //echo $bpcs_date . " bpcs date<br />"; switch ($report_type) { case 1: $report_desc = 'Qty'; break; case 2: $report_desc = 'Machine Hours'; break; case 3: $report_desc = 'Sales Price'; break; case 4: $report_desc = 'Material Cost'; break; case 5: $report_desc = 'Labor'; break; } ///////////////////////////////////////////////////////// //Database connection //////////////////////////////////////////////////////// $server="XXXXXXXX"; #the name of the iSeries $user="XXXXXXX"; #a valid username that will connect to the DB $pass="XXXXXXX"; #a password for the username #Connect to the Database with ODBC - $conn is defined and loaded using the "odbc_connect" PHP #directive. $conn=odbc_connect("XXXXXX","XXXXXXX","XXXXXXXX"); #Check Connection if ($conn == false) { echo "Not able to connect to database...<br>"; } /////////////////////////////////////////////////////////////////////////// // Query selection based on which items is selected from the drop down menu /////////////////////////////////////////////////////////////////////////// switch ($report_type) { case 1: /////////////////////////////////////// // Based on qty /////////////////////////////////////// //MySQL data selection $query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, '; $query .= "sum(case when wk_num < {$str_week_inc} then (LQORD - LQSHP) else 0 end) AS Current, "; //Data selection based on number of weeks while($str_week_inc < $end_week){ $query .= "sum(case when wk_num = {$str_week_inc} then (LQORD - LQSHP) else 0 end) AS week_{$str_week_inc}, "; $str_week_inc++; } $query .= "sum(case when wk_num = {$end_week} then (LQORD - LQSHP) else 0 end) AS week_{$end_week} "; $query .= "FROM (bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod) LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} and bpcs405cdf.ecl.lprod not like 'P%' GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03 ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD"; break; case 2: /////////////////////////////////////// // Based on Run Hrs /////////////////////////////////////// //MySQL data selection $query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, '; $query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * rlab) else 0 end) AS Current, "; //Data selection based on number of weeks while($str_week_inc < $end_week){ $query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * rlab) else 0 end) AS week_{$str_week_inc}, "; $str_week_inc++; } $query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * rlab) else 0 end) AS week_{$end_week} "; $query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod) LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate) LEFT JOIN sblib.fg_run ON bpcs405cdf.ecl.lprod = sblib.fg_run.rprod WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} and bpcs405cdf.ecl.lprod not like 'P%' GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03 ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD"; break; case 3: /////////////////////////////////////// // Based on Price /////////////////////////////////////// //MySQL data selection $query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, '; $query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * LNET) else 0 end) AS Current, "; //Data selection based on number of weeks while($str_week_inc < $end_week){ $query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * LNET) else 0 end) AS week_{$str_week_inc}, "; $str_week_inc++; } $query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * LNET) else 0 end) AS week_{$end_week} "; $query .= "FROM (bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod) LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} and bpcs405cdf.ecl.lprod not like 'P%' GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03 ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD"; break; case 4: /////////////////////////////////////// // Based on Material Cost /////////////////////////////////////// //MySQL data selection $query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, '; $query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS Current, "; //Data selection based on number of weeks while($str_week_inc < $end_week){ $query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$str_week_inc}, "; $str_week_inc++; } $query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$end_week} "; $query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod) LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate) LEFT JOIN bpcs405cdf.cmf ON bpcs405cdf.ecl.lprod = bpcs405cdf.cmf.cfprod WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} and bpcs405cdf.ecl.lprod not like 'P%' and bpcs405cdf.cmf.cfcset = 2 and bpcs405cdf.cmf.cfcbkt = 1 GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03 ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD"; break; case 5: /////////////////////////////////////// // Based on Labor Cost /////////////////////////////////////// //MySQL data selection $query = 'SELECT bpcs405cdf.iim.iref03, bpcs405cdf.ecl.lprod, bpcs405cdf.iim.idesc, '; $query .= "sum(case when wk_num < {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS Current, "; //Data selection based on number of weeks while($str_week_inc < $end_week){ $query .= "sum(case when wk_num = {$str_week_inc} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$str_week_inc}, "; $str_week_inc++; } $query .= "sum(case when wk_num = {$end_week} then ((LQORD - LQSHP) * (cftlvl + cfplvl)) else 0 end) AS week_{$end_week} "; $query .= "FROM ((bpcs405cdf.ecl LEFT JOIN bpcs405cdf.iim ON bpcs405cdf.ecl.lprod = bpcs405cdf.iim.iprod) LEFT JOIN sblib.dte_conv ON bpcs405cdf.ecl.lsdte = sblib.dte_conv.bdate) LEFT JOIN bpcs405cdf.cmf ON bpcs405cdf.ecl.lprod = bpcs405cdf.cmf.cfprod WHERE bpcs405cdf.ecl.lid = 'CL' and bpcs405cdf.ecl.LQORD > bpcs405cdf.ecl.LQSHP and LSDTE < {$bpcs_date} and bpcs405cdf.ecl.lprod not like 'P%' and bpcs405cdf.cmf.cfcset = 2 and bpcs405cdf.cmf.cfcbkt = 3 GROUP BY bpcs405cdf.ecl.LPROD, bpcs405cdf.iim.IDESC, bpcs405cdf.iim.iref03 ORDER BY bpcs405cdf.iim.iref03, bpcs405cdf.ecl.LPROD"; break; } /////////////////////////////////////////////////////////////////////////// // End of Query selection based on which items is selected from the drop down menu /////////////////////////////////////////////////////////////////////////// // execute the data selection $result = odbc_exec($conn, $query); if (!$result) { print("Execution failed:\n"); print(" State: ".odbc_error($con)."\n"); print(" Error: ".odbc_errormsg($con)."\n"); } else { // print("Execution was successful.\n"); } // Data printed into a table echo "<h3>" . $num_weeks . " Weeks Requirements Based on " . $report_desc . "("; echo date("n/j/y", $str_date) . " - " . date("n/j/y", $end_date) . ")</h3>"; echo "<table border='1' cellspacing='0' bordercolor='#000000'><tr>"; echo "<tr> <td><b>Area</b></td> <td><b>Item</b></td> <td><b>Description</b></td> <td><b>Current</b></td>"; // While loop to display table headers for weeks $str_week_inc2 = $str_week +1; while($str_week_inc2 < $end_week +1){ // old header date //$header_date = date("n/j/y", (weekDayToTime($str_week_inc2, date('Y')))); //echo "<td><b>Week " . $str_week_inc2 . " (". $header_date . ")</b></td>"; // new header date $header_date = date("M-d", (weekDayToTime($str_week_inc2, date('Y')))); echo "<td><b>" .$header_date . "</b></td>"; $str_week_inc2++; } // Close table header echo "</tr><tr>"; // Pull in Data while ($row = odbc_fetch_array($result)) { while(list($fieldname, $fieldvalue) = each($row)) { if(is_numeric($fieldvalue)){ if($report_type > 2){ echo "<td>$" . number_format($fieldvalue, 2, '.', ',') . "</td>"; }else{ echo "<td>" . number_format($fieldvalue, 2, '.', ',') . "</td>"; } }else{ echo "<td>" . $fieldvalue . "</td>"; } } echo "</tr><tr>"; } // Close table echo "</tr></table>"; // Close ODBC connection odbc_close($conn); ?> </body> </html>
  2. sbrinley
    I'm trying to take a block of text that is formatted very poorly and put it into something that is more database friendly. Here is an example of the text: ---------------------------------------------------------------------------------------------------------------------------------- Albert, Kyle 050359 92501 Temp sunshade ID IN Dept ACTIVITY OUT ID IN Dept ACTIVITY OUT TOTALS Mon 01/04 553a 611p 12.00 12.00 Tue 01/05 552a 603p 12.00 24.00 Wed 01/06 558a 647p*L 12.00 36.00 Thu 01/07 632a*L 909a*E 2.60 38.60 Acct:92501 TempRg: 38.60 ---------------------------------------------------------------------------------------------------------------------------------- Aldridge,Jennie M 0001090916 026991 Plt 2 Shipping ID IN Dept ACTIVITY OUT ID IN Dept ACTIVITY OUT TOTALS Mon 01/04 538a 431p 10.00 10.00 Tue 01/05 541a 431p 10.00 20.00 Wed 01/06 541a 448p 10.00 30.00 Thu 01/07 547a 5.00 35.00 Acct:026991 REG: 35.00 ---------------------------------------------------------------------------------------------------------------------------------- The goal is to get it to look something like this: Name ID Workcenter Date hrs worked Albert, Kyle 050359 92501 01/04/10 12 Albert, Kyle 050359 92501 01/05/10 12 Albert, Kyle 050359 92501 01/06/10 12 Albert, Kyle 050359 92501 01/07/10 2 Aldridge,Jennie 0001090916 026991 01/04/10 10 Aldridge,Jennie 0001090916 026991 01/05/10 10 Aldridge,Jennie 0001090916 026991 01/06/10 10 Aldridge,Jennie 0001090916 026991 01/07/10 5 The data will always be in the same place in the file. I'm not even sure what method would work to go through the the file to make this happen. I'm not exactly looking for a complete answer but more of a nudge in the right direction.
  3. sbrinley
    I'm having a bit of trouble working with dates and the AS400. I'm writing a program with PHP that will allow users to see a customer requirements schedule based on different selections such as price, qty, labor, etc... Part of this program uses the week number (since users will select their date range based on weeks) My issue is this the current date format in field I'm using is 20100114 or YYYYMMDD which is stored as a numeric value and from this I would like to pull what week of the year it is. If I simply try using WEEK($DATA) where the $data is my actual column name, it won't work. The only way I've gotten this to work is by using the strsql command on the AS400 itself and converting the date with the following command: WEEK(DATE(CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(DIGITS(LSDTE),5,2),'/'),SUBSTR(DIGITS(LSDTE),7,2)),'/'),SUBSTR(DIGITS(LSDTE),3,2)))) (the reason for so many concatenations is I can't concat more than 2 items at a time because of whatever reason on the AS400) However when I try to do this with the ODBC connection in PHP it simply laughs at me. This is the SQL I'm using in the PHP script that I'm simply testing with: $SQL = "select LPROD, LQORD, LQSHP, LSDTE, WEEK(DATE(CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(DIGITS(LSDTE),5,2),'/'),SUBSTR(DIGITS(LSDTE),7,2)),'/'),SUBSTR(DIGITS(LSDTE),3,2)))) FROM BPCS405CDF.ECL where LID = 'CL' and LPROD = 'D61120104GAT'"; This query returns all the values except the week number even though it returns the value on the AS400. So the answers I'm looking for are: When you use a ODBC connection what is the SQL based on? In this case the AS400 or PHP's ODBC stuff? If it is the AS400 any ideas why this won't work?Or evne a possible alternate solution An alternate solution I can think of is creating a table that simply has the date and the conversation date along with the week and week day. However that is not a very reliable way and I would not like to do that if possible.
×
×
  • 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.