MichaelMcDonald
Posts: 640
|
| Posted: 06/02/2013, 8:14 AM |
|
*** Notice the declaration of some variables as "%" for use with LIKE to provide wildcard function when there is no value that can be retrieved for it using CCGetParam...
*** Date searches where the date is retrieved from the form, converted to integer and then into DB format are on (date) fields only, not on (datetime) fields
$siteid = CCGetFromGet("siteid");
$sitename = CCGetFromGet("sitename");
$ordertypeid = CCGetParam("s_ordertypeid");
if ($ordertypeid > 0)
$ordertypeid = CCGetParam("s_ordertypeid");
else
$ordertypeid = "%";
$supplierid = CCGetParam("s_supplierid");
if ($supplierid > 0)
$supplierid = CCGetParam("s_supplierid");
else
$supplierid = "%";
$accountcodeid = CCGetParam("s_accountcodeid");
if ($accountcodeid > 0)
$accountcodeid = CCGetParam("s_accountcodeid");
else
$accountcodeid = "%";
$duedate1 = CCGetParam("s_duedate1");
$duedate2 = CCGetParam("s_duedate2");
$duedate1 = strtotime($duedate1);
$duedate2 = strtotime($duedate2);
$duedate1= date("Y/m/d",$duedate1);
$duedate2= date("Y/m/d",$duedate2);
$added1 = CCGetParam("s_added1");
$added2 = CCGetParam("s_added2");
$completed1 = CCGetParam("s_completed1");
$completed2 = CCGetParam("s_completed2");
$constaxincex= CCGetSession("constaxincex");
$constaxcode = CCGetSession("constaxcode");
$constax= $constaxincex."-".$constaxcode;
if($completed1 != NULL and $added1 != NULL){
//echo "1";
//looks ok
$added1 = strtotime($added1);
$added1= date("Y/m/d",$added1);
$added2 = strtotime($added2);
$added2= date("Y/m/d",$added2);
$completed1 = strtotime($completed1);
$completed1 = date("Y/m/d",$completed1);
$completed2 = strtotime($completed2);
$completed2 = date("Y/m/d",$completed2);
$db = new clsDBFM();
$sql = "SELECT sitename AS 'Site Name', idorder as 'Order ID', added AS 'Added', duedate AS 'Due Date',
inprogress AS 'In Progress', completed AS 'Completed', displayname AS 'Supplier', description AS 'Description',
accountcode AS 'Account Code', cost AS 'Cost ($constax)', invoicenumber AS 'Invoice Number', invoicecost AS 'Invoice Cost ($constax)',
ordertype AS 'Order Type'"
." FROM (((orders LEFT JOIN ordertype ON
orders.ordertypeid = ordertype.idordertype) LEFT JOIN supplier ON
orders.supplierid = supplier.idsupplier) LEFT JOIN accountcode ON
orders.accountcodeid = accountcode.idaccountcode) LEFT JOIN site ON orders.siteid = site.idsite"
." WHERE orders.siteid = '$siteid' AND ordertype.idordertype LIKE '$ordertypeid'
AND accountcode.idaccountcode LIKE '$accountcodeid'
AND orders.supplierid LIKE '$supplierid'
AND (duedate >= '$duedate1' AND duedate <= '$duedate2')
AND (added >= '$added1' AND added <= '$added2')
AND (completed1 >= '$completed1' AND completed1 <= '$completed2')
ORDER BY duedate desc";
$db->query($sql);
}
_________________
Central Coast, NSW, Australia.
|
