First let me start by saying I am soooo sorry. I am stupid, slow, and new to java, in fact this is the first I have ever used java except for a few mouse over menu image changing stuff that is very easy and well documented. I am fairly good at php but not that good... I have been working with it for about a year and it was kinda forced on me.... I would rater just fix hardware than write code.
Here is my problem. I have a report our sales reps would like (to know when some software expires so they know when to call) This data lives in our CRM software (SugarCRM) DB but is not easily sorted from there. It also has the unique problem of the date of expiration is NOT a DATE in sql it is a VARCHAR so I had to add lots of extra code to make sure what the sales people put in can actualy be displayed as a date when it pulls back out. The request form the sales people was a report that shows the number of days left until the software expires... so here is the code that displays the list. Then they wanted it sorted.. during my searching for sorting my list I found AW and thought THIS IS GREAT IT CAN SORT IT IN THIS GRID THINGY!!
The current list also has a hyperlink (url to an internal web server) in the customer name that links to their customer ID number that opens that customers record in the web interface of Sugar... I could just pull the id number but the sales reps wanted to see the name of the customer and not just some long id number.
Here is my code and it works fine to just display the report but again I would love to push this data into AW, once I figure this out this would be great for most of the reports I have, I just cant seem to get it to work after 4 days of trying.
There is one include file that just has my db settings, passwords and a few functions
------------START OF PHP CODE------------
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center"><table width="778" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="748"><table width="748" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="16" rowspan="2" bgcolor="#FFFFFF"> </td>
<td width="717" bgcolor="#FFFFFF"> </td>
<td width="15" rowspan="2" bgcolor="#FFFFFF"> </td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="center"><p class="title">AV Expiration report</p>
<?
include_once("../sugarreports/scripts/db.php");
//begging of date functions
function date_validate ($datefield) {
// First check to see if the input ($datefield) is in one of the accepted formats
// Check for delimiters ("-" or "/") and put three fields into an array
if (strpos($datefield, "-")) {
$datesplit = explode("-", $datefield);
} elseif (strpos($datefield, "/")) {
$datesplit = explode("/", $datefield);
} else {
$date_err="Error: Invalid date field. No proper delimiters (- or /) found";
return $date_err;
}
// Check for three input fields (month, day, year)
if (count($datesplit)>3) {
$date_err="Error: Invalid date field. Too many fields (".count($datesplit).") found";
return $date_err;
}
// Put date array into single format
if (strlen($datesplit[2])==4) { // The year is listed last - switch fields around
$newdatesplit[0]=$datesplit[2]; // Move Year to first field
$newdatesplit[1]=$datesplit[0]; // Move Month to second field
$newdatesplit[2]=$datesplit[1]; // Move Day to third field
$datesplit=$newdatesplit;
} elseif (strlen($datesplit[0])==4) { // The year is first listed - do nothing
// nothing to be done
} else { // Date entered is not valid; could not find year field
$date_err="Error: Date not valid. No Year field found (Year must be 4 digits)";
return $date_err;
}
// Main validation code
if ($datesplit[1]>12) { // No valid month field
$date_err="Error: Invalid Month field (".$datesplit[1].") ";
return $date_err;
} else {
switch ($datesplit[1]) { // Check number of days in a month
case 4:
case 6:
case 9:
case 11:
if ($datesplit[2]>30) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
break;
case 2: // February Check
if (($datesplit[0]/4)==(floor($datesplit[0]/4))) {
if (($datesplit[0]/100)==(floor($datesplit[0]/100))) {
if (($datesplit[0]==1600) or ($datesplit[0]==2000) or ($datesplit[0]==2400)) {
if ($datesplit[2]>29) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
} else {
if ($datesplit[2]>28) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
}
} else {
if ($datesplit[2]>29) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
}
} else {
if ($datesplit[2]>28) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
}
break;
default:
if ($datesplit[2]>31) {
$date_err="Error: Invalid # of days (".$datesplit[2].") for month ".$datesplit[1]." and year ".$datesplit[0];
return $date_err;
}
}
}
// Add leading zero if month or day field is only one character
if (strlen($datesplit[1])==1) {
$datesplit[1]="0".$datesplit[1];
}
if (strlen($datesplit[2])==1) {
$datesplit[2]="0".$datesplit[2];
}
// Create date field in MySQL format
$newdate=$datesplit[0]."-".$datesplit[1]."-".$datesplit[2];
return $newdate;
} // End date_validate function
$db = new DBMysql();
$sql = "SELECT id_c, av_exp_c FROM accounts_cstm WHERE av_exp_c >=1 ORDER BY av_exp_c DESC";
$alls = $db->FetchAll($sql);
if(count($alls) > 0) { ?>
<table width="75%" border="2" cellspacing="2" cellpadding="0" bgcolor="#ffffff">
<tr>
<td class="xsmallText"><strong>Customers</A></strong></td>
<td class="xsmallText"><strong>Experation data</strong></td>
</tr>
<? foreach($alls as $all) {
//get the variable for the customers link in sugar
$db = new DBMysql();
$sql = "SELECT name FROM accounts WHERE id = '$all[id_c]'";
$name = $db->GetOne($sql);
$cid = $all["id_c"];
$date = $all["av_exp_c"];
//validate the date to a mssql style date
$MySQLDate=date_validate($date);
if (substr($MySQLDate, 0, 5)=="Error") {
// Insert Error Code
} else {
// Insert Valid Date Code
}
//end date validation
//
//$datetime=strtotime( "$MySQLDate 00:00" );
//$date2=strtotime("NOW");
//if ($datetime < $date2) {
// print "The Customer has expired!<br>";
// } else {
//This gives days remaining
//$holdtotday=intval(($datetime-$date2)/86400);
//print "$holdtotday <br>";
// }
?>
<tr>
<td>
<A href="
http://srv.ourdomain.lan/crm/index.php?module=Accounts&action=DetailView&record=<?=$cid?>"target="_blank"><?=$name?></A>
</td>
<td>
<?
$datetime=strtotime($MySQLDate);
$date2=strtotime("NOW");
if ($datetime < $date2) {
print "The Customer's AV has expired! <br>";
} else {
//This gives days remaining
$holdtotday=intval(($datetime-$date2)/86400);
print "$holdtotday days until AV expires";
}?>
</td>
</tr>
<? } ?>
</table>
<?
}
else { ?>
<p align="center">No records found. This is probably an error. HaHaHa sorry guys... Contact me.</p>
<? } ?>
</td>
</tr>
</table></td>
------------END OF PHP CODE-----------------
So how do I get the link into grid and the other info that is printed within the foreach command?? Any help would be great!
I do have another report I was working on (this report but trying to add the AW Grid) and was able to get the last item in the loop into the grid but it only showed the last item and would not do the linking...
aaaaawwww Please help if possible and code examples would be great! (especially if I can just plug them right into my current code)
Thanks again!!!
I will post a small screen shot of what my current output looks like if that helps anyone Just let me know