PHP LMSDB类代码示例

function GetReceipt($id)
{
    $db = LMSDB::getInstance();
    if ($receipt = $db->GetRow('SELECT d.*, u.name AS user, n.template,
					ds.name AS d_name, ds.address AS d_address,
					ds.zip AS d_zip, ds.city AS d_city
				FROM documents d
				LEFT JOIN vusers u ON (d.userid = u.id)
				LEFT JOIN numberplans n ON (d.numberplanid = n.id)
				LEFT JOIN customers c ON (d.customerid = c.id)
				LEFT JOIN divisions ds ON (ds.id = c.divisionid)
				WHERE d.type = 2 AND d.id = ?', array($id))) {
        $receipt['contents'] = $db->GetAll('SELECT * FROM receiptcontents WHERE docid = ? ORDER BY itemid', array($id));
        $receipt['total'] = 0;
        foreach ($receipt['contents'] as $row) {
            $receipt['total'] += $row['value'];
        }
        $receipt['number'] = docnumber(array('number' => $receipt['number'], 'template' => $receipt['template'], 'cdate' => $receipt['cdate'], 'ext_num' => $receipt['extnumber'], 'customerid' => $receipt['customerid']));
        if ($receipt['total'] < 0) {
            $receipt['type'] = 'out';
            // change values sign
            foreach ($receipt['contents'] as $idx => $row) {
                $receipt['contents'][$idx]['value'] *= -1;
            }
            $receipt['total'] *= -1;
        } else {
            $receipt['type'] = 'in';
        }
        $receipt['totalg'] = round($receipt['total'] * 100 - (int) $receipt['total'] * 100);
        return $receipt;
    }
}

function IncomePerMonth($only_year)
{
    global $LMS, $SMARTY, $SESSION;
    $db = LMSDB::getInstance();
    $income = $db->GetAll('SELECT MONTH(FROM_UNIXTIME(time)) as month, SUM(value) AS suma FROM cash WHERE value>0 AND YEAR(FROM_UNIXTIME(time))=' . $only_year . ' GROUP BY month(FROM_UNIXTIME(time))');
    return $income;
}

function select_customer($id)
{
    $JSResponse = new xajaxResponse();
    $nodes_location = LMSDB::getInstance()->GetAll('SELECT n.id, n.name, location FROM vnodes n WHERE ownerid = ? ORDER BY n.name ASC', array($id));
    $JSResponse->call('update_nodes_location', (array) $nodes_location);
    return $JSResponse;
}

 public function __construct($load_policy = null)
 {
     $this->DB = LMSDB::getInstance();
     if ($load_policy !== null) {
         $this->setLoadPolicy($load_policy);
     }
 }

function getEventsToClose($userid)
{
    global $LMS, $SMARTY, $SESSION;
    $db = LMSDB::getInstance();
    $events = $db->GetAll('SELECT from_unixtime(e.date, \'%Y-%m-%d\') AS startdate, e.title, e.id
			FROM events e, eventassignments ea WHERE e.closed=0 AND ea.userid=' . $userid . ' AND e.id=ea.eventid');
    return $events;
}

function GetEventList($year = NULL, $month = NULL, $day = NULL, $forward = 0, $customerid = 0, $userid = 0, $type = 0, $privacy = 0, $closed = '')
{
    global $AUTH;
    $DB = LMSDB::getInstance();
    if (!$year) {
        $year = date('Y', time());
    }
    if (!$month) {
        $month = date('n', time());
    }
    if (!$day) {
        $day = date('j', time());
    }
    $startdate = mktime(0, 0, 0, $month, $day, $year);
    $enddate = mktime(0, 0, 0, $month, $day + $forward, $year);
    $list = $DB->GetAll('SELECT events.id AS id, title, note, description, date, begintime, enddate, endtime, customerid, closed, events.type, ' . $DB->Concat('UPPER(c.lastname)', "' '", 'c.name') . ' AS customername,
		userid, users.name AS username, ' . $DB->Concat('c.city', "', '", 'c.address') . ' AS customerlocation, nodeid, nodes.location AS location 
		FROM events 
		LEFT JOIN nodes ON (nodeid = nodes.id)
		LEFT JOIN customerview c ON (customerid = c.id)
		LEFT JOIN users ON (userid = users.id)
		WHERE ((date >= ? AND date < ?) OR (enddate <> 0 AND date < ? AND enddate >= ?))
			AND (private = 0 OR (private = 1 AND userid = ?)) ' . ($customerid ? ' AND customerid = ' . intval($customerid) : '') . ($userid ? ' AND EXISTS (
			SELECT 1 FROM eventassignments 
			WHERE eventid = events.id AND userid = ' . intval($userid) . '
			)' : '') . ($type ? ' AND events.type = ' . intval($type) : '') . ($privacy == 1 ? ' AND private = 0' : ($privacy == 2 ? ' AND private = 1' : '')) . ($closed != '' ? ' AND closed = ' . intval($closed) : '') . ' ORDER BY date, begintime', array($startdate, $enddate, $enddate, $startdate, $AUTH->id));
    $list2 = array();
    if ($list) {
        foreach ($list as $idx => $row) {
            $row['userlist'] = $DB->GetAll('SELECT userid AS id, users.name
					FROM eventassignments, users
					WHERE userid = users.id AND eventid = ? ', array($row['id']));
            $endtime = $row['endtime'];
            if ($row['enddate'] && $row['enddate'] - $row['date']) {
                $days = round(($row['enddate'] - $row['date']) / 86400);
                $row['enddate'] = $row['date'] + 86400;
                $row['endtime'] = 0;
                $list2[] = $row;
                while ($days) {
                    if ($days == 1) {
                        $row['endtime'] = $endtime;
                    }
                    $row['date'] += 86400;
                    list($year, $month, $day) = explode('/', date('Y/n/j', $row['date']));
                    $row['date'] = mktime(0, 0, 0, $month, $day, $year);
                    $row['enddate'] = $row['date'] + 86400;
                    if ($days > 1 || $endtime) {
                        $list2[] = $row;
                    }
                    $days--;
                }
            } else {
                $list2[] = $row;
            }
        }
    }
    return $list2;
}

 /**
  * Loads plugin database schema updates
  */
 protected function upgradeDb()
 {
     $constant = get_class($this) . '::PLUGIN_DBVERSION';
     if (defined($constant)) {
         $libdir = $this->dirname . DIRECTORY_SEPARATOR . 'lib';
         $docdir = $this->dirname . DIRECTORY_SEPARATOR . 'doc';
         $this->dbschversion = LMSDB::getInstance()->UpgradeDb(constant($constant), get_class($this), $libdir, $docdir);
     }
 }

 /**
  * Return user rights mask
  * 
  * @param array $options Associative array of options
  * @return array
  */
 public function load(array $options = array())
 {
     if (!isset($options['user_id'])) {
         throw new Exception('User id not set. Cannot find user rights config!');
     }
     $id = $options['user_id'];
     $db = LMSDB::getInstance();
     return array($db->GetOne('SELECT rights FROM users WHERE id = ?', array($id)));
 }

function select_producer($id)
{
    $JSResponse = new xajaxResponse();
    $models = LMSDB::getInstance()->GetAll('
        SELECT id, name
        FROM netdevicemodels 
        WHERE netdeviceproducerid = ?
        ORDER BY name', array($id));
    $JSResponse->call('update_models', (array) $models);
    return $JSResponse;
}

function deletedCustomers($year)
{
    global $LMS, $SMARTY, $SESSION;
    $db = LMSDB::getInstance();
    $deletedcustomers = $db->GetAll('
    SELECT COUNT(id) AS customers, MONTH(FROM_UNIXTIME(moddate)) as month
    FROM customers
    WHERE deleted=1 AND YEAR(FROM_UNIXTIME(moddate))=' . $year . ' 
    GROUP BY MONTH(FROM_UNIXTIME(moddate))
    ');
    return $deletedcustomers;
}

function getSummary()
{
    global $LMS, $SMARTY, $SESSION;
    $db = LMSDB::getInstance();
    $summary = $db->GetAll('SELECT u.id, u.name, COUNT(e.closed) AS wszystkie, SUM(IF(e.closed, 1, 0)) AS zamkniete, SUM(IF(e.closed, 0, 1)) AS otwarte
			    FROM eventassignments ea
			    LEFT JOIN users u ON (u.id = ea.userid)
			    LEFT JOIN events e ON (e.id = ea.eventid)
			    WHERE u.deleted=0
			    GROUP BY ea.userid');
    return $summary;
}

function findAlreadyExists(&$prefixList)
{
    $DB = LMSDB::getInstance();
    $prefixHelperArray = $DB->GetAllByKey("SELECT id, prefix FROM voip_prefixes", "prefix");
    $exists = array();
    foreach ($prefixList as $v => $k) {
        if (isset($prefixHelperArray[$v])) {
            $exists[] = $v;
        }
    }
    return $exists ? $exists : NULL;
}

function getUsersForGroup($groupid)
{
    $JSResponse = new xajaxResponse();
    if (empty($groupid)) {
        $users = null;
    } else {
        $users = LMSDB::getInstance()->GetCol('SELECT u.id FROM users u
			JOIN userassignments ua ON ua.userid = u.id
			WHERE u.deleted = 0 AND u.access = 1 AND ua.usergroupid = ?', array($groupid));
    }
    $JSResponse->call('update_user_selection', $users);
    return $JSResponse;
}

function GetEvents($date = NULL, $userid = 0, $customerid = 0, $privacy = 0, $closed = '')
{
    global $AUTH;
    $DB = LMSDB::getInstance();
    switch ($privacy) {
        case 0:
            $privacy_condition = '(private = 0 OR (private = 1 AND userid = ' . intval($AUTH->id) . '))';
            break;
        case 1:
            $privacy_condition = 'private = 0';
            break;
        case 2:
            $privacy_condition = 'private = 1 AND userid = ' . intval($AUTH->id);
            break;
    }
    $enddate = $date + 86400;
    $list = $DB->GetAll('SELECT events.id AS id, title, note, description, date, begintime, enddate, endtime, closed, events.type,' . $DB->Concat('UPPER(c.lastname)', "' '", 'c.name') . ' AS customername, ' . $DB->Concat('c.city', "', '", 'c.address') . ' AS customerlocation,
		 nodes.location AS nodelocation,
		 (SELECT contact FROM customercontacts WHERE customerid = c.id
			AND (customercontacts.type & ?) > 0 AND (customercontacts.type & ?) <> ?  ORDER BY id LIMIT 1) AS customerphone
		 FROM events LEFT JOIN customerview c ON (customerid = c.id) LEFT JOIN nodes ON (nodeid = nodes.id)
		 WHERE ((date >= ? AND date < ?) OR (enddate <> 0 AND date < ? AND enddate >= ?)) AND ' . $privacy_condition . ($customerid ? 'AND customerid = ' . intval($customerid) : '') . ($closed != '' ? ' AND closed = ' . intval($closed) : '') . ' ORDER BY date, begintime', array(CONTACT_MOBILE | CONTACT_FAX | CONTACT_LANDLINE, CONTACT_DISABLED, CONTACT_DISABLED, $date, $enddate, $enddate, $date));
    $list2 = array();
    if ($list) {
        foreach ($list as $idx => $row) {
            $row['userlist'] = $DB->GetAll('SELECT userid AS id, vusers.name
				FROM eventassignments, vusers
				WHERE userid = vusers.id AND eventid = ? ', array($row['id']));
            $endtime = $row['endtime'];
            if ($row['enddate'] && $row['enddate'] - $row['date']) {
                $days = round(($row['enddate'] - $row['date']) / 86400);
                $row['enddate'] = $row['date'] + 86400;
                $row['endtime'] = 0;
                $list2[] = $row;
            } else {
                $list2[] = $row;
            }
        }
    }
    return $list2;
}

 public function __construct()
 {
     $this->DB = LMSDB::getInstance();
 }