lib.InvoiceSystem.php

/includes/lib/lib.InvoiceSystem.php

https://github.com/toztech/php-invoice
PHP | 839 lines | 477 code | 293 blank | 69 comment | 94 complexity | 84951a455bff50bd21754a791157a247 MD5 | raw file

<?
/***************************************************************************
					InvoiceSystemLib.php
					------------
	product			: PHP Invoice
	version			: 1.0 build 1 (Beta)
	released		: Sunday September 7 2003
	copyright		: Copyright &copy; 2001-2009 Jeremy Hubert
	email			: support@illanti.com
	website			: http://www.illanti.com

    The core of the invoice system. 
    
    DO NOT EDIT unless you know what you are doing.

***************************************************************************/
/*
# -------- IGNORE EVERYTHING BELOW THIS LINE --------
$call_home_to="secure.illanti.com";
$installed_directory="/licence";
$querystring="iaccess_ip=".base64_encode($_SERVER['SERVER_ADDR']);
$querystring.="&iaccess_host=".base64_encode($_SERVER['SERVER_NAME']);
$querystring.="&product_key=".urlencode("10f6f8c0f3fad50a21f4c6fc3a224337");
$querystring.="&license=".urlencode($license)."\n\n";

$fp=@fsockopen($call_home_to, 80, $errno, $errstr, 5);
if (!$fp) { return 1; }
else
   {
   $header="POST ".$installed_directory."/validate_internal.php HTTP/1.1\n";
   $header.="Host: ".$call_home_to."\n";
   $header.="Content-Length: ".strlen($querystring)."\n";
   $header.="Content-Type: application/x-www-form-urlencoded\n";
   $header.="Connection: Close\n\n";
   $header.=$querystring;

   fputs ($fp, $header);
   while (!feof($fp)) { $data.=@fgets($fp, 1024); }
   fclose ($fp);

   list($a, $b, $c)=explode("{{", $data);
   function parse_template($template)
      {
      $buffer="";
      $fh=file($template);
      foreach($fh as $line) { $buffer.=$line; }
      return $buffer;
      }

   $path_home="http://".$call_home_to.$installed_directory."/inc";
   if ($b==69) 
      { 
      echo parse_template($path_home."/license_invalid.tpl");
      exit; 
      }		
   else if ($b==2) 
      { 
      echo parse_template($path_home."/license_suspended.tpl"); 
      exit;	
      }
   else if ($b==3) 
      { 
      echo parse_template($path_home."/license_expired.tpl"); 
      exit; 
      }
   }
*/

class InvoiceSystem {
	
    var $db;
    var $debug = false;
    var $dbprefix;
    var $adminid;
    var $rpp; // results per page
    var $def_rpp; // default results per page
    var $page; // current result page

    function InvoiceSystem(&$db,$adminid, $dbprefix,$rpp,$debug=false) {
        if(!$db) {
            die("Invalid db object passed to InvoiceSystem constructor");  
        }
        $this->db = $db;
        $this->debug = $debug;
        $this->dbprefix = $dbprefix;
        $this->adminid = $adminid;
        $this->rpp = $rpp;
        $this->def_rpp = $rpp;
    }

    function setDebug($debug) {
        $this->debug = $debug;
        return true;
    }

    function setRPP($rpp=0) {
        if ($rpp) {
            $this->rpp = $rpp;
        } else {
            $this->rpp = $this->def_rpp;
        }
        return true;
    }

    function setPage($page) {
        $this->page = $page;
        return true;
    }

    function SqlError($query, $result) 
    {
        trigger_error("MYSQL error:  ".$result->getMessage().
                      " in query:<br />".$query."<br />",E_USER_ERROR);
    }

    function doLogin($username,$password) {
        $query = "SELECT `clientid` , `parentclientid`, `username` , `firstname`, `email` , `ref` , `company` , `access`, `template`, `language` FROM " . $this->dbprefix . "client WHERE username = '" . $username . "' AND passwd = '" . $password . "'";
    
        $retVal = $this->db->getRow($query,null,DB_FETCHMODE_ASSOC);
        
        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function recoverPassword($method,$value) {
        $query = "SELECT `clientid` , `username` , `firstname`, `email` , `passwd` FROM " . $this->dbprefix . "client WHERE " . $method . " = '" . $value . "'";
    
        $retVal = $this->db->getRow($query,null,DB_FETCHMODE_ASSOC);
        
        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }
    

    function FetchInvoices($page=0,$param='invoice_num',$where='1') {

        if (strtolower($param) == 'clientid') $param = 'i.' . $param;

        $query = "SELECT c.clientid,company,i.invoiceid, invoice_num,UNIX_TIMESTAMP(issue_date) as issue_date,UNIX_TIMESTAMP(due_date) as due_date,SUM(ii.cost*iii.qty) as cost , (SUM(ii.cost*iii.qty)+(shipping))*(tax/100) as tax, (SUM(ii.cost*iii.qty)+(shipping))*(tax2/100) as tax2, shipping , (IFNULL(SUM(ii.cost*iii.qty),0)+(shipping))*(1+(tax/100))*(1+(tax2/100)) as total,curr_status FROM " . $this->dbprefix . "invoice i INNER JOIN " . $this->dbprefix . "client c ON i.clientid = c.clientid LEFT JOIN " . $this->dbprefix . "invoice_invoiceitem as iii ON i.invoiceid = iii.invoiceid LEFT JOIN " . $this->dbprefix . "invoiceitem as ii ON ii.invoiceitemid = iii.invoiceitemid WHERE i.visible = '1' AND parentclientid = " . $this->adminid . " AND $where GROUP BY iii.invoiceid, i.invoiceid ORDER BY $param";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function FetchInvoicesByClient($clientID,$param='invoice_num',$where='1') {
        $query = "SELECT i.invoiceid, invoice_num,UNIX_TIMESTAMP(issue_date) as issue_date,UNIX_TIMESTAMP(due_date) as due_date,SUM(ii.cost*iii.qty) as cost , (SUM(ii.cost*iii.qty)+(shipping))*(tax/100) as tax, (SUM(ii.cost*iii.qty)+(shipping))*(tax2/100) as tax2, shipping , (SUM(ii.cost*iii.qty)+(shipping))*(1+(tax/100))*(1+(tax2/100)) as total,curr_status FROM " . $this->dbprefix . "invoice i LEFT JOIN " . $this->dbprefix . "invoice_invoiceitem as iii USING ( invoiceid ) LEFT JOIN " . $this->dbprefix . "invoiceitem as ii USING ( invoiceitemid ) WHERE visible = '1' AND clientid = '" . $clientID . "' AND curr_status <> 'unsent' AND $where GROUP BY iii.invoiceid ORDER BY $param";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function FetchInvoiceList($clientID=0) {

        if (!$clientID) {
            $where = '1';
        } else {
            $where = 'clientid = ' . $clientID;
        }

        $query = "SELECT invoiceid,invoice_num FROM " . $this->dbprefix . "invoice WHERE $where ORDER BY invoice_num";

        $retVal = $this->db->getAssoc($query,false,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchInvoiceClient($invoiceID) {

        $query = "SELECT clientID FROM " . $this->dbprefix . "invoice WHERE invoiceid = $invoiceID";

        $retVal = $this->db->getOne($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchInvoiceNum($invoiceID) {

        $query = "SELECT invoice_num FROM " . $this->dbprefix . "invoice WHERE invoiceid = $invoiceID";

        $retVal = $this->db->getOne($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchClientCompany($clientID) {

        $query = "SELECT company FROM " . $this->dbprefix . "client WHERE clientid = $clientID";

        $retVal = $this->db->getOne($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }


    function FetchClients($access='client',$display='active') {

        switch($display) {
            case 'all':
                $where = '1';
            break;
            case 'inactive':
                $where = "c.visible = '0'";
            break;
            default:
                $where = "c.visible = '1'";
            break;
        }
        
        $query = "SELECT c.`clientid` , `parentclientid` , `username` , `email` , `ref` , `company` , `firstname` , `lastname` , `contacttitle` , `phonenumber` , `faxnumber` , `term_days` , `def_terms` , `def_comments` , `account_num` , `template` , `language` , `access` , c.`visible`,count(invoiceID) as invoicecount FROM `" . $this->dbprefix . "client` as c LEFT JOIN `" . $this->dbprefix . "invoice` as i USING (clientid) WHERE $where AND access = '$access' AND parentclientid = " . $this->adminid . " GROUP BY c.clientID ORDER BY c.company";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchClientList() {

        $query = "SELECT clientid,company FROM " . $this->dbprefix . "client WHERE access = 'client' AND parentclientid = " . $this->adminid . " ORDER BY company";

        $retVal = $this->db->getAssoc($query,false,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FindNewInvoiceNumber($cid=0,$inum=0) {

        if (!$inum) 
            $inum = $this->db->nextid($this->dbprefix . 'invoice_num');
        
        $query = "SELECT invoiceid,invoice_num FROM " . $this->dbprefix . "invoice INNER JOIN " . $this->dbprefix . "client USING ( clientid ) WHERE parentclientid=" . $this->adminid . " AND invoice_num='" . $inum . "'";

        $retVal = $this->db->getRow($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        if (count($retVal)) {
            if ($retVal[0]==$cid) 
                return $inum;
            else
                return $this->FindNewInvoiceNumber($cid);
        } else {
            return $inum;
        }
    }

    function FetchClientOwner($clientID) {

        $query = "SELECT parentclientid FROM " . $this->dbprefix . "client WHERE clientid=" . $clientID;

        $retVal = $this->db->getOne($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchClientDetails($clientID) {

        $query = "SELECT * FROM " . $this->dbprefix . "client WHERE clientid=" . $clientID;

        $retVal = $this->db->getRow($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);
 
        return $retVal;

    }

    function FetchAdminDetails($adminID) {

        $query = "SELECT company,firstName,lastName,phoneNumber,faxNumber,address,logo,email,def_tax,def_tax2,term_days,def_terms,def_comments FROM " . $this->dbprefix . "client WHERE clientid=" . $adminID;

        $retVal = $this->db->getRow($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function FetchInvoiceDetails($invoiceID) {
        
        $query = "SELECT i.invoiceid, invoice_num, clientid , UNIX_TIMESTAMP(issue_date) as issue_date,UNIX_TIMESTAMP(due_date) as due_date , comments , SUM(ii.cost*iii.qty) as cost , (SUM(ii.cost*iii.qty)+(shipping))*(tax/100) as calc_tax, (SUM(ii.cost*iii.qty)+(shipping))*(tax2/100) as calc_tax2, shipping, (IFNULL(SUM(ii.cost*iii.qty),0)+(shipping))*(1+(tax/100))*(1+(tax2/100)) as total, tax, tax2, curr_status, comments, terms FROM " . $this->dbprefix . "invoice i LEFT JOIN " . $this->dbprefix . "invoice_invoiceitem as iii USING ( invoiceid ) LEFT JOIN " . $this->dbprefix . "invoiceitem as ii USING ( invoiceitemid ) WHERE i.invoiceid = " . $invoiceID . " GROUP BY iii.invoiceid";

        $retVal = $this->db->getRow($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        if (is_array($retVal))
            $retVal['items'] = $this->Fetchinvoiceitems($invoiceID);

        return $retVal;

    }

    function Fetchinvoiceitems($invoiceID) {
        
        $query = "SELECT  iii.`invoiceitemid` ,  iii.`qty`, `details`, `cost` FROM  `" . $this->dbprefix . "invoice_invoiceitem` iii INNER JOIN `" . $this->dbprefix . "invoiceitem` ii USING ( invoiceitemid ) WHERE 1  AND  iii.`invoiceid` = " . $invoiceID;

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        if (!is_array($retVal)) $retVal = array();

        return $retVal;

    }

    function InsertClient($values) {

        if ($values['def_tax'] == '') {
            $values['def_tax'] = 'NULL';
        }
        if ($values['def_tax2'] == '') {
            $values['def_tax2'] = 'NULL';
        }
        if ($values['term_days'] == '') {
            $values['term_days'] = 'NULL';
        }

        $id = $this->db->nextid($this->dbprefix . 'client');    

        $query = "INSERT INTO " . $this->dbprefix . "client (clientid, parentclientid, username, passwd, address, email, def_tax, def_tax2, ref , `firstName` , `lastName` , `contactTitle` , `company` , `phonenumber` , `faxnumber` , `url` , `logo` , `template` , `language` , `term_days` , `def_terms` , `def_comments` , `account_num` , `access` , `visible`) VALUES 
        ($id," . 
        addslashes($values['parentclientid']) . ",'" . 
        addslashes($values['username']) . "','" . 
        addslashes($values['passwd']) . "','" . 
        addslashes($values['address']) . "','" . 
        addslashes($values['email']) . "'," . 
        addslashes($values['def_tax']) . "," . 
        addslashes($values['def_tax2']) . ",'" . 
        addslashes($values['ref']) . "','" . 
        addslashes($values['firstname']) . "', '" . 
        addslashes($values['lastname']) . "', '" . 
        addslashes($values['contacttitle']) . "', '" .
        addslashes($values['company']) . "', '" . 
        addslashes($values['phonenumber']) . "', '" . 
        addslashes($values['faxnumber']) . "', '" . 
        addslashes($values['url']) . "', '" . 
        addslashes($values['logo']) . "', '" . 
        addslashes($values['template']) . "', '" . 
        addslashes($values['language']) . "', " . 
        addslashes($values['term_days']) . ", '" . 
        addslashes($values['def_terms']) . "', '" . 
        addslashes($values['def_comments']) . "', '" . 
        addslashes($values['account_num']) . "', '" . 
        addslashes($values['access']) . "', '" . addslashes($values['visible']) . "')";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $id;

    }

    function UpdateClient($clientID,$values) {

        if ($values['def_tax'] == '') {
            $values['def_tax'] = 'NULL';
        }
        if ($values['def_tax2'] == '') {
            $values['def_tax2'] = 'NULL';
        }
        if ($values['term_days'] == '') {
            $values['term_days'] = 'NULL';
        }

        $query = "UPDATE " . $this->dbprefix . "client SET
        parentclientid=" . addslashes($values['parentclientid']) . ",
        username='" . addslashes($values['username']) . "',
        passwd='" . addslashes($values['passwd']) . "',
        address='" . addslashes($values['address']) . "',
        email='" . addslashes($values['email']) . "',
        def_tax=" . addslashes($values['def_tax']) . ",
        def_tax2=" . addslashes($values['def_tax2']) . ",
        ref='" . addslashes($values['ref']) . "',
        firstname='" . addslashes($values['firstname']) . "',
        lastname='" . addslashes($values['lastname']) . "',
        contacttitle='" . addslashes($values['contacttitle']) . "',
        company='" . addslashes($values['company']) . "',
        phonenumber='" . addslashes($values['phonenumber']) . "',
        faxnumber='" . addslashes($values['faxnumber']) . "',
        url='" . addslashes($values['url']) . "',
        logo='" . addslashes($values['logo']) . "',
        term_days=" . addslashes($values['term_days']) . ",
        def_terms='" . addslashes($values['def_terms']) . "',
        def_comments='" . addslashes($values['def_comments']) . "',
        account_num='" . addslashes($values['account_num']) . "',
        template='" . addslashes($values['template']) . "',
        language='" . addslashes($values['language']) . "',
        access='" . addslashes($values['access']) . "',
        visible='" . addslashes($values['visible']) . "'
        WHERE clientid=$clientID";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function DeleteClient($clientID,$soft=true) {

        if ($soft) {
            $query = "UPDATE " . $this->dbprefix . "client SET visible = '0' WHERE clientid=" . $clientID;
        } else {
            $query = "DELETE FROM " . $this->dbprefix . "client WHERE clientid=" . $clientID;
        }

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function InsertInvoice(&$values) {

        $id = $this->db->nextid($this->dbprefix . 'invoice');    

        if (!is_numeric($values['invoice_num']) || !$values['invoice_num'])
            $values['invoice_num'] = 0;

        $values['invoice_num'] = $this->FindNewInvoiceNumber($id,$values['invoice_num']);

        $query = "INSERT INTO " . $this->dbprefix . "invoice (invoiceid, clientid, issue_date, due_date, comments, cost, tax, tax2, shipping, curr_status, terms, invoice_num) VALUES ('$id','" . addslashes($values['clientid']) . "','" . date('Y-m-d h:i:s',strtotime($values['issue_date'])) . "','" . date('Y-m-d h:i:s',strtotime($values['due_date'])) . "','" . addslashes($values['comments']) . "','" . addslashes($values['cost']) . "','" . addslashes($values['tax']) . "','" . addslashes($values['tax2']) . "','" . addslashes($values['shipping']) . "','" . addslashes($values['curr_status']) . "','" . addslashes($values['terms']) . "','" . addslashes($values['invoice_num']) . "')";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $id; //array('id'=>$id,'num'=>$values['invoice_num']);

    }

    function Insertinvoiceitem($details,$cost) {

        $id = $this->db->nextid($this->dbprefix . 'invoiceitem');

        $query = "INSERT INTO " . $this->dbprefix . "invoiceitem (invoiceitemid, details, cost) VALUES ('$id','" . addslashes($details) . "','" . addslashes($cost) . "')";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $id;
    }

    function AddItemToInvoice($invoiceitemid,$invoiceid,$qty) {

        $query = "INSERT INTO " . $this->dbprefix . "invoice_invoiceitem (invoiceitemid, invoiceid, qty) VALUES ('$invoiceitemid','$invoiceid','$qty')";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function DeleteInvoiceItem($invoiceitemID) {

        $query = "DELETE FROM " . $this->dbprefix . "invoiceitem WHERE invoiceitemid = " . $invoiceitemID;

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function RemoveItemFromInvoice($invoiceitemID,$invoiceID) {

        $query = "DELETE FROM " . $this->dbprefix . "invoiceitem WHERE invoiceitemid = " . $invoiceitemID;

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function DeleteInvoice($invoiceID,$soft=true) {

        if ($soft) {
            $query = "UPDATE " . $this->dbprefix . "invoice SET curr_status = 'void' WHERE invoiceid = " . $invoiceID;
        } else {
            $query = "DELETE FROM " . $this->dbprefix . "invoice WHERE invoiceid = " . $invoiceID;
        }

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function UpdateInvoice($invoiceID,$values) {

        $values['invoice_num'] = $this->FindNewInvoiceNumber($invoiceID,$values['invoice_num']);

        $query = "UPDATE " . $this->dbprefix . "invoice SET invoice_num='" . addslashes($values['invoice_num']) . "',clientid='" . addslashes($values['clientid']) . "',issue_date='" . date('Y-m-d h:i:s',strtotime($values['issue_date'])) . "', due_date='" . date('Y-m-d h:i:s',strtotime($values['due_date'])) . "', comments='" . addslashes($values['comments']) . "',terms='" . addslashes($values['terms']) . "',cost='" . addslashes($values['cost']) . "',tax='" . addslashes($values['tax']) . "',tax2='" . addslashes($values['tax2']) . "',shipping='" . addslashes($values['shipping']) . "',curr_status='" . addslashes(strtolower($values['curr_status'])) . "' WHERE invoiceid=$invoiceID";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function UpdateInvoiceStatus($invoiceID,$status) {

        $query = "UPDATE " . $this->dbprefix . "invoice SET curr_status='" . addslashes($status) . "' WHERE invoiceid=$invoiceID";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function LogItem($creator,$targetID,$targetType,$eventID,$details='') {

        $query = "INSERT INTO " . $this->dbprefix . "log (creator,targetid,targettype,eventid,details) VALUES (" . $creator . "," . $targetID . "," . $targetType . "," . $eventID . ",'" . $details . "');";
    
        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function addEmailSend($clientID, $invoiceID, $emailaddress, $sendType) {

        $emailSendID = $this->db->nextid($this->dbprefix . 'emailsend');

        if ($invoiceID == 0) {
            $invoiceID == "NULL";
        }

        $query = "INSERT INTO `" . $this->dbprefix . "emailsend` ( `emailsendid` , `clientid` , `invoiceid` , `emailaddress` , `sendtype` , `datesent` , `opencount`) VALUES (" . $emailSendID . ", " . $clientID . ", "  . $invoiceID . " , '"  . $emailaddress . "' , " . $sendType . ", '" . date('Y-m-d H:i:s') . "', 0)";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $emailSendID;
    }

    function trackEmailSendOpen($emailSendID) {

        /*
        connect to the database and process opened issues
        */
        $sql = "SELECT count(emailsendid) FROM " . $this->dbprefix . "emailsend WHERE opencount > 0 AND emailsendid = " . $emailSendID;

        $retVal = $this->db->getOne($sql);

        if ($retVal) {
            $sql = "UPDATE " . $this->dbprefix . "emailsend SET opencount = opencount + 1, lastopened = '" . date('Y-m-d H:i:s') . "' WHERE emailsendid = " . $emailSendID;			
        } else {
            $sql = "UPDATE " . $this->dbprefix . "emailsend SET opencount = 1, firstopened = '" . date('Y-m-d H:i:s') . "', lastopened = '" . date('Y-m-d H:i:s') . "' WHERE emailSendID = " . $emailSendID;			
        }

        $retVal = $this->db->query($sql);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function getEmailSendHistory($clientID=0) {

        $where = ($clientID) ? 'AND c.`clientid`=' . $clientID : '';

        $sql = "SELECT `emailsendid` , `company`, c.`clientid` , `invoiceid` , `emailaddress` , `sendtype` , UNIX_TIMESTAMP(datesent) as datesent , `opencount` , UNIX_TIMESTAMP(firstopened) as firstopened, UNIX_TIMESTAMP(lastopened) as lastopened  FROM " . $this->dbprefix . "emailsend INNER JOIN `" . $this->dbprefix . "client` as c USING ( clientid ) WHERE parentclientid = " . $this->adminid . " $where ORDER BY emailsendid";			

        $retVal = $this->db->getAll($sql,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

	function FetchLogItems($date=null) {
		
		$query = "SELECT `logid` , client.`clientid` , `company` , `invoiceid` , `eventid` , UNIX_TIMESTAMP(`occured`) as occured , `details` FROM `" . $this->dbprefix . "log` INNER JOIN `client` USING ( `clientid` ) WHERE parentclientid = " . $this->adminid;

		$retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

		if(DB::isError($retVal)) $this->SqlError($query,$retVal);

		return $retVal;
	}

	function FetchLogItemsByClient($clientID=0,$date=null) {
		
		$query = "SELECT `logid` , `creator` , `company` , `targetid` , `targettype` , `eventid` , UNIX_TIMESTAMP(occured) as occured, `details` FROM `" . $this->dbprefix . "log` INNER JOIN `" . $this->dbprefix . "client` ON clientid = creator WHERE creator=" . $clientID . " AND parentclientid = " . $this->adminid;

		$retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

		if(DB::isError($retVal)) $this->SqlError($query,$retVal);

		return $retVal;
	}

	function FetchLogItems_Login($clientID=0,$date=null) {
		
		$query = "SELECT `logid` , `creator` , `targetid` , `targettype` , `eventid` , UNIX_TIMESTAMP(occured) as occured, `details`, `company` FROM `" . $this->dbprefix . "log` INNER JOIN `" . $this->dbprefix . "client` ON clientid = targetid WHERE eventid IN ( 10,11 ) AND parentclientid = " . $this->adminid;
        
        if ($clientID)
            $query .= " AND clientID = " . $clientID;

        $query .= " ORDER BY occured";

		$retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

		if(DB::isError($retVal)) $this->SqlError($query,$retVal);

		return $retVal;
	}
	function SearchClients($criteria, $qs, $access='client') {

		for ($i=0, $end=sizeof($qs), $subq=''; $i<$end; $i++) {
			$subq .= $criteria . " LIKE '%{$qs{$i}}%'"; 
			$subq .= ($i != ($end-1)) ? ' OR ' : '';
		}

        $query = "SELECT * FROM " . $this->dbprefix . "client WHERE ($subq) AND visible = '1' AND access = '$access' AND parentclientid = " . $this->adminid;

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function InsertNote($clientID, $content, $private) {

        if ($private) {
            $private = 'yes';
        } else {
            $private = 'no';
        }

        $query = "INSERT INTO " . $this->dbprefix . "note (s_clientid, r_clientid, content, posted, isprivate) VALUES (" . $this->adminid . "," . $clientID . ",'" . addslashes($content) . "',NOW(),'" . $private . "');";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function DeleteNote($noteID) {

        $query = "DELETE FROM " . $this->dbprefix . "note WHERE noteid = " . $noteID;

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function GetNotes($clientID=null) {
        
        if (!$clientID) {
            $where = '1';
        } else {
            $where = 'r_clientid = ' . $clientID;
        }

        $query = "SELECT noteid, r_clientid, content, UNIX_TIMESTAMP(posted) as posted, isprivate FROM " . $this->dbprefix . "note WHERE " . $where . " ORDER BY posted DESC";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function GetUserNotes() {
        
        $query = "SELECT noteid, r_clientid, content, UNIX_TIMESTAMP(posted) as posted, isprivate FROM " . $this->dbprefix . "note WHERE r_clientid = " . $this->adminid . " AND isprivate = 'no' ORDER BY posted DESC";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;

    }

    function GetPayments($clientID) {
        if (!$clientID) {
            $where = '1';
        } else {
            $where = 'c.clientid = ' . $clientID;
        }

        $query = "SELECT `paymentid` , p.`invoiceid` , `invoice_num`, p.`clientid`, `company` , `amount` , `method` , UNIX_TIMESTAMP(made_on) as made_on FROM " . $this->dbprefix . "payment p INNER JOIN " . $this->dbprefix . "client c USING ( clientid ) LEFT JOIN " . $this->dbprefix . "invoice i ON i.invoiceid = p.invoiceid WHERE " . $where . " ORDER BY made_on DESC";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;        
    }

    function InsertPayment( $clientID, $invoiceID, $amount, $method) {

        $query = "INSERT INTO " . $this->dbprefix . "payment (clientid, invoiceid, amount, method, made_on) VALUES (" . $clientID . "," . $invoiceID . ",'" . $amount . "','" . addslashes($method) . "',NOW());";

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;

    }

    function DeletePayment($paymentID) {

        $query = "DELETE FROM " . $this->dbprefix . "payment WHERE paymentid = " . $paymentID;

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;
    }

    function FetchPaygates() {
        $query = "SELECT * FROM " . $this->dbprefix . "paygate ORDER BY company";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function FetchRecurrances() {
        $query = "SELECT `recurranceid` , `invoiceid` , `days` , `months` , UNIX_TIMESTAMP(`until`) as until, action FROM `" . $this->dbprefix . "recurrance`";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return $retVal;
    }

    function EnablePaygate($paygateID,$enabled) {
        $status = ($enabled) ? 'yes' : 'no';

        $query = "UPDATE " . $this->dbprefix . "paygate SET enabled = '" . $status . "' WHERE paygateid = " . $paygateID;

        $retVal = $this->db->query($query);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        return true;        
    }

    function AdminStats() {
        $query = "SELECT `logid` , `creator` , `targetid` , `targettype` , `eventid` , UNIX_TIMESTAMP(occured) as occured, `details`, `company` FROM `" . $this->dbprefix . "log` INNER JOIN `" . $this->dbprefix . "client` ON clientid = targetid WHERE eventid IN ( 10,11 ) AND `occured` >= '" . date('Y-m-d h:i:s',strtotime("1 day ago")) . "' AND parentclientid = " . $this->adminid;

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        $stats['logins'] = $retVal;

        $query = "SELECT `paymentid` , p.`invoiceid` , `invoice_num`, p.`clientid`, `company` , `amount` , `method` , UNIX_TIMESTAMP(made_on) as made_on FROM " . $this->dbprefix . "payment p INNER JOIN " . $this->dbprefix . "client c USING ( clientid ) LEFT JOIN " . $this->dbprefix . "invoice i ON i.invoiceid = p.invoiceid WHERE `made_on` >= '" . date('Y-m-d h:i:s',strtotime("1 day ago")) . "' ORDER BY made_on DESC";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        $stats['payments'] = $retVal;

        $query = "SELECT (IFNULL( SUM( ii.cost * iii.qty ) , 0 ) + ( shipping ) ) * ( 1 + ( tax /100 ) ) * ( 1 + ( tax2 /100 ) ) AS total FROM " . $this->dbprefix . "invoice i INNER JOIN " . $this->dbprefix . "client c ON i.clientid = c.clientid LEFT JOIN " . $this->dbprefix . "invoice_invoiceitem AS iii ON i.invoiceid = iii.invoiceid LEFT JOIN " . $this->dbprefix . "invoiceitem AS ii ON ii.invoiceitemid = iii.invoiceitemid WHERE i.visible = '1' AND parentclientid = " . $this->adminid . " AND `curr_status` NOT LIKE 'fully paid'";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        $stats['owed'] = $retVal;

        $query = "SELECT c.clientid,company,i.invoiceid, invoice_num,UNIX_TIMESTAMP(issue_date) as issue_date,UNIX_TIMESTAMP(due_date) as due_date,SUM(ii.cost*iii.qty) as cost , (SUM(ii.cost*iii.qty)+(shipping))*(tax/100) as tax, (SUM(ii.cost*iii.qty)+(shipping))*(tax2/100) as tax2, shipping , (IFNULL(SUM(ii.cost*iii.qty),0)+(shipping))*(1+(tax/100))*(1+(tax2/100)) as total,curr_status FROM " . $this->dbprefix . "invoice i INNER JOIN " . $this->dbprefix . "client c ON i.clientid = c.clientid LEFT JOIN " . $this->dbprefix . "invoice_invoiceitem as iii ON i.invoiceid = iii.invoiceid LEFT JOIN " . $this->dbprefix . "invoiceitem as ii ON ii.invoiceitemid = iii.invoiceitemid WHERE i.visible = '1' AND parentclientid = " . $this->adminid . " AND `curr_status` LIKE 'overdue' GROUP BY iii.invoiceid, i.invoiceid ORDER BY due_date";

        $retVal = $this->db->getAll($query,null,DB_FETCHMODE_ASSOC);

        if(DB::isError($retVal)) $this->SqlError($query,$retVal);

        $stats['overdue'] = $retVal;
        
        return $stats;
    }
}

?>