Jump to content

sbrinley

Members
  • Posts

    21
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

sbrinley's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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. 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. 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.
  4. You are awesome that's exactly what I'm looking for! Also thanks roopurt18 that is very helpful as well. i'll do some checking into that.
  5. Whether its using &nbsp or html tags I still need some help adding the tags to the loop.
  6. Thanks for the replies. I think more in lines of what I meant about the spacing is to create <ul> items. I put in the spaces as just an example. Here is what I'm talking about: <ul>A00320002AXA <li><ul>R50210002AXA</li> <li>R50210002AXX</li></ul> <li><ul>R50230302AXA</li> <li><ul>R50230002AXA</li> <li>R50230002AXX</li></ul></ul> <li><ul>R50240302AXA</li> <li>R50240002AXA</li></ul> </ul> I'm wondering how to insert the unordered list item tags into the loop.
  7. About a week ago I posted something similar but I don't think I asked the question correctly. I've come very close to a solution but I need a little help to get the rest of the way (or maybe a different set of eyes) This is what I have so far: A table that looks like this: Parent Child A00030002AXA A00030002AXA R50210802AXA A00030002AXA R50231502AXA A00030002AXA R50241502AXA R50210802AXA R50210002AXA R50231502AXA R50230002AXA R50241502AXA R50240002AXA This is the PHP script I'm using. Which has been modified by search through various other solutions and take in part from a tutorial from another website. function display_children($parent, $level) { // retrieve all children of $parent $result = mysql_query('SELECT child FROM bom '. 'WHERE parent="'.$parent.'";'); // display each child while ($row = mysql_fetch_array($result)) { // indent and display the title of this child echo str_repeat(' ',$level).$row['child']."<br>"; // call this function again to display this // child's children display_children($row['child'], $level+1); } } display_children('',0); What is displayed is this A00320002AXA R50210002AXA R50210002AXX R50230302AXA R50230002AXA R50230002AXX R50240302AXA R50240002AXA R50240002AXX I would like to arrange the data so it looks like this A00320002AXA R50210002AXA R50210002AXX R50230302AXA R50230002AXA R50230002AXX R50240302AXA R50240002AXA Does anyone have any suggestions or can you point me in the right direction? Thanks !!!
  8. I am trying to take a list of data with two columns and separate it out into a structured table. Here is a sample list of the data Parent Child D61120901GAT R61120901GAT R61120901GAT R61120801GAT R61120801GAT R61120801GAA R61120801GAA R61120701GAT R61120701GAT R61120601GAT R61120601GAT R61120501GAT R61120501GAT R61120003GAT This is the list I want to turn into this D61120901GAT R61120901GAT R61120801GAT R61120801GAA R61120701GAT R61120601GAT R61120501GAT R61120003GAT The parent might have up to 10 children deep in total. Also the parent might have two or more direct children. for example: D61120901GAT R61120901GAT R61120801GAT R61121901GAT R61121801GAT Also even though the numbers seem to go into order here they don't in every part. A typical list size is about 20k lines long. I've tried a few different while loops without success. I fear I might be over complicating it or not looping correctly. If anyone has some ideas or a solutions I would appreciate it!
  9. There's not a problem if you go through windows explorer but it you go through internet explorer or firefox (which is what my users use) then it doesn't work. Also there's the slight issue of providing the full path instead of the c:/fakepath that populates. I did a calculation and all the files that would be uploaded total less then 1GB and I can't imagine even in the future going over 2GB so it should be that big of an issue. The users won't know the difference and the only real issue with going that way is inconsistency of data. If people don't update the file on the internal website as well as the local files then there could be issues.
  10. I decided to move on to the next part of the project assuming I could get this to work which is linking the files to a path. It seems IE7, IE8 and firefox have local access links disabled. I have found a few solutions to enable them but this opens a huge security risk and is somewhat time consuming. I'm going to mark this question as solved since there isn't a solution / even if this works it can't go anywhere. Thanks for your help.
  11. I decided to move on to the next part of the project assuming I could get this to work which is linking the files to a path. It seems IE7, IE8 and firefox have local access links disabled. I have found a few solutions to enable them but this opens a huge security risk and is somewhat time consuming. I'm going to mark this question as solved since there isn't a solution / even if this works it can't go anywhere. Thanks for your help.
  12. Here's a little update on what I've found so far This code <form> <textarea name='abc' rows=30 cols=50></textarea> <input type=file onchange="this.form.abc.value+=';' + this.value; return true;" multiple> </form> In firefox 3 I will get the name of the file just like the $_FILES however with IE8 I get this on the left side ;C:\fakepath\SNC00102.jpg here's a screen shot. The actually path of the file selected is c:\downloads\SNC00102.jpg as you can see from the screen shot...
  13. that is true but if you look at the global variable $_FILES it will only show the file name not the actual path. On a side note I've done some more digging and found that with this <form> <textarea name='abc' rows=30 cols=50></textarea> <input type=file onchange="this.form.abc.value+=';' + this.value; return true;" multiple> </form> In firefox 3 I will get the name of the file just like the $_FILES however with IE8 I get this on the left side ;C:\fakepath\SNC00102.jpg here's a screen shot. The actually path of the file selected is c:\downloads\SNC00102.jpg as you can see from the screen shot...
  14. Correct I posted here: * PHP Freaks Forums > Client Side > JavaScript Help Here is the link if anyone is interested. http://www.phpfreaks.com/forums/index.php/topic,252214.0.html
  15. Thanks for the help I appreciate you looking at this. None the less I've submitted the same question in the Java section maybe someone might have an idea. If I happen to find a solution I'll be sure to post in case someone else is looking for something similar.
×
×
  • 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.