« previous next »
Pages: [1]

Author Topic: Date / Week issues with AS400 DB2  (Read 316 times)

0 Members and 1 Guest are viewing this topic.

Offline sbrinleyTopic starter

  • Irregular
    • View Profile
Date / Week issues with AS400 DB2
« on: January 14, 2010, 03:12:14 PM »
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:

Code: [Select]
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.
Logged

Offline sbrinleyTopic starter

  • Irregular
    • View Profile
Re: Date / Week issues with AS400 DB2
« Reply #1 on: February 04, 2010, 11:18:48 PM »
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>




Logged

PHP Freaks Forums

« on: »

Tired of these ads? Purchase a supporter subscription to get rid of them.
Pages: [1]
« previous next »