protected function loadData()
{
$dbh = Project_DB::get();
$filter = array();
$values = array();
if ($this->id !== null) {
$filter[] = ' M.ID = :id';
$values['id'] = (int) $this->id;
}
if ($this->name !== null) {
$filter[] = ' M.Name = :name';
$values['name'] = $this->name;
}
$where = implode(' AND', $filter);
$query = "SELECT M.ID, M.Type, M.Name, X.Content, M.LastChange, M.Active FROM modules AS M LEFT OUTER JOIN mod_text AS X ON M.ID=X.ID WHERE{$where}";
$stmt = $dbh->prepare($query);
foreach ($values as $key => $value) {
$stmt->bindValue(":{$key}", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if ($row['Type'] != $this->type) {
throw new Kiwi_Bad_Module_Type_Exception($row['Type'], $this->type);
}
$this->id = $row['ID'];
$this->name = $row['Name'];
$this->content = $row['Content'];
$this->lastchange = $row['LastChange'];
$this->active = $row['Active'];
} else {
$idstr = $this->id !== null ? "id = {$this->id}" : "name = {$this->name}";
throw new Kiwi_No_Such_Module_Exception($idstr);
}
}
}
protected static function createDBCM()
{
$config = new Project_Config();
self::$_dbcm = new DB_Connection_Manager();
$dbs = $config->db;
foreach ($dbs as $name => $connection_data) {
self::$_dbcm->addConnection($name, $connection_data);
}
}
public static function getModuleActiveStatus($mid)
{
$dbh = Project_DB::get();
$query = "SELECT Active FROM modules WHERE ID=?";
$stmt = $dbh->prepare($query);
if ($stmt->execute(array($mid))) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
return $row['Active'];
}
}
return false;
}
protected function loadData()
{
$dbh = Project_DB::get();
$query = "SELECT PID, GID FROM prodbinds WHERE ID=:ID AND Active=1";
$stmt = $dbh->prepare($query);
$stmt->bindValue(":ID", $this->id, PDO::PARAM_INT);
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$this->pid = $row['PID'];
$this->gid = $row['GID'];
}
}
}
public static function startPending()
{
$dbh = Project_DB::get();
$sql_params['waiting'] = self::STATUS_WAITING;
$sql_params['started'] = self::STATUS_STARTED;
$sql_params['active'] = 1;
$sql_params['now'] = date('Y-m-d H:i:s');
$query = 'UPDATE newsletters SET Status=:started, Started=NOW() WHERE Status=:waiting AND Active=:active AND Start<=:now';
$stmt = $dbh->prepare($query);
foreach ($sql_params as $spk => $spv) {
$stmt->bindValue(":{$spk}", $spv, is_int($spv) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmt->execute();
return $stmt->rowCount();
}
protected function loadData()
{
if ($this->_data === null) {
$this->_data = array();
$this->checkActive();
if ($this->_active) {
$dbh = Project_DB::get();
$query = "SELECT ID, Title, Description, Picture, Link FROM eshopactions WHERE AGID=:agid AND Active=1 ORDER BY Priority";
$stmt = $dbh->prepare($query);
$stmt->bindValue(":agid", $this->_group, PDO::PARAM_INT);
if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$this->_data[] = new Var_Pool($row);
}
}
}
}
}
protected function populateMenu(&$menu)
{
$dbh = Project_DB::get();
$query = "SELECT ID, Name, URL, Subgroup FROM eshop WHERE Active=1 AND Parent=:Parent ORDER BY Priority";
$stmt = $dbh->prepare($query);
foreach ($menu as &$mi) {
if ($mi['Contents'] === false) {
continue;
}
$stmt->bindValue(":Parent", $mi['ID'], PDO::PARAM_INT);
if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$ar = array('ID' => $row['ID'], 'Name' => $row['Name'], 'URL' => $row['URL']);
$ar['Contents'] = $row['Subgroup'] ? array() : false;
$mi['Contents'][] = $ar;
}
}
$this->populateMenu($mi['Contents']);
}
}
protected function populatePath($gid)
{
$rpath = array();
$dbh = Project_DB::get();
$query = "SELECT Name, URL, Subgroup, Parent FROM eshop WHERE ID=:gid AND Active=1";
$stmt = $dbh->prepare($query);
$max_path_length = 32;
while ($gid > 1 && $max_path_length-- > 0) {
$stmt->bindValue(":gid", $gid, PDO::PARAM_INT);
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$rpath[] = array('ID' => $gid, 'Name' => $row['Name'], 'URL' => $row['URL'], 'Subgroup' => $row['Subgroup']);
} else {
$rpath = array();
return;
}
}
$stmt->closeCursor();
$gid = $row['Parent'];
}
$this->_data = array_reverse($rpath);
}
public function loadGalleryPictures(&$gallery, $page = 1, $size = null)
{
$dbh = Project_DB::get();
$values = array();
$values['gid'] = (int) $gallery->GID;
$query = "SELECT SQL_CALC_FOUND_ROWS P.ID AS ID, GP.FileName AS GPFileName, P.FileName AS FileName, GP.Title AS GPTitle, P.Title AS Title, GP.ShortDesc AS GPShortDesc, P.ShortDesc AS ShortDesc, GP.LongDesc AS GPLongDesc, P.LongDesc AS LongDesc, GP.Author AS GPAuthor, P.Author AS Author, P.`When` AS `When` FROM gallpics AS P JOIN gallpbinds AS GP ON P.ID=GP.PID WHERE GP.GID=:gid AND GP.Active=1 AND P.Active=1 ORDER BY GP.Priority ASC LIMIT :from, :to";
if ($size === null) {
$size = (int) $this->gsize;
}
$values['from'] = ($page - 1) * $size;
$values['to'] = $size;
$stmt = $dbh->prepare($query);
foreach ($values as $key => $value) {
$stmt->bindValue(":{$key}", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$pictures = array();
if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$picture = new Var_Pool($row);
$pictures[$row['ID']] = $picture;
}
}
$gallery->Pictures = $pictures;
$stmt = $dbh->query('SELECT FOUND_ROWS()');
if ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$gallery->PicturesTotal = $row[0];
}
}
public function login()
{
$cu = Current_User::getInstance();
$cu->setUserID($this->ID);
$dbh = Project_DB::get();
$query = "UPDATE users SET LastLogin=CURRENT_TIMESTAMP";
$dbh->exec($query);
}
public static function getLines($filters, $columns = null)
{
if ($columns === null) {
$columns = array('ID');
} elseif (!is_array($columns)) {
$columns = array($columns);
}
$dbh = Project_DB::get();
$sql_filters = array();
$sql_params = array();
foreach ($filters as $fkey => $fval) {
switch ($fkey) {
case 'name':
$sql_filters[] = "Name=:name";
$sql_params['name'] = $fval;
break;
case 'name_like':
$sql_filters[] = "Name LIKE :name_like";
$sql_params['name_like'] = $fval;
break;
case 'parent':
$sql_filters[] = "Parent=:parent";
$sql_params['parent'] = $fval;
break;
default:
throw new Invalid_Argument_Value_Exception("filters/{$fkey}", $fkey);
}
}
if (!empty($sql_filters)) {
$fsql = implode(' AND ', $sql_filters);
} else {
$fsql = 1;
}
$query = "SELECT ID FROM eshop WHERE " . $fsql;
$stmt = $dbh->prepare($query);
foreach ($sql_params as $spk => $spv) {
$stmt->bindValue(":{$spk}", $spv, is_int($spv) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$lines = array();
if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$lines[] = new Var_Pool($row);
}
}
return $lines;
}
protected function getClientFromEmail($email)
{
$dbh = Project_DB::get();
$lookupSql = 'SELECT ID, FirstName, SurName, Salutation, Title, Email, FirmEmail, BusinessName FROM eshopclients WHERE Email=:email OR FirmEmail=:firmEmail';
$stmt = $dbh->prepare($lookupSql);
$stmt->bindValue(':email', $email, PDO::PARAM_STR);
$stmt->bindValue(':firmEmail', $email, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row;
}
public static function loadProductLineID($url, $catalogRoot = NULL)
{
if ($catalogRoot === NULL) {
$catalogRoot = self::DEFAULT_CATALOG_ROOT;
}
$sub_groups = self::getSubGroups($catalogRoot);
if (!empty($sub_groups)) {
$sub_groups_str = implode(',', $sub_groups);
$search_pmi_sql = " AND ID IN ({$sub_groups_str})";
} else {
$search_pmi_sql = "";
}
$dbh = Project_DB::get();
$query = "SELECT ID FROM eshop WHERE Active=1 AND URL=:url{$search_pmi_sql}";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':url', $url, PDO::PARAM_STR);
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
return $row['ID'];
}
}
}
protected function loadAttachments()
{
if ($this->_attachments === null) {
$this->_attachments = array();
$dbh = Project_DB::get();
$query = "SELECT ID, FileName, Title FROM prodattach WHERE PID=:pid ORDER BY ID";
$stmt = $dbh->prepare($query);
$stmt->bindValue(":pid", $this->_id, PDO::PARAM_INT);
if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$this->_attachments[] = array('ID' => $row['ID'], 'FileName' => $row['FileName'], 'Title' => $row['Title']);
}
}
}
}
public function save()
{
parent::save();
$dbh = Project_DB::get();
switch ($this->_type) {
case self::PERSON:
$data = $this->_person->toArray();
$table = 'clientsp';
$table_d = 'clientsc';
break;
case self::COMPANY:
$data = $this->_company->toArray();
$table = 'clientsc';
$table_d = 'clientsp';
break;
default:
throw new Kiwi_Exception('Kiwi_Client type unknown');
}
foreach ($data as $key => $value) {
if ($value === null) {
unset($data[$key]);
}
}
$data['ID'] = $this->_data->ID;
$columns = implode(', ', array_keys($data));
$column_pdo_hooks = ':' . implode(', :', array_keys($data));
$query = "REPLACE {$table} ({$columns}) VALUES ({$column_pdo_hooks})";
$stmt = $dbh->prepare($query);
foreach ($data as $key => $value) {
$stmt->bindValue(":{$key}", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$query2 = "DELETE FROM {$table_d} WHERE ID=:ID";
$stmt2 = $dbh->prepare($query2);
$stmt2->bindValue(':ID', $this->_data->ID, PDO::PARAM_INT);
$dbh->exec("LOCK TABLES {$table} WRITE, {$table_d} WRITE");
$locked = true;
try {
$stmt->execute();
$stmt2->execute();
} catch (PDOException $e) {
$dbh->exec('UNLOCK TABLES');
$locked = false;
$ei = $e->errorInfo;
throw Kiwi_Exception("Failed to save client - {$ei[2]} ({$ei[0]})");
}
if ($locked) {
$dbh->exec('UNLOCK TABLES');
}
}
protected function resolveClient($emailRecord)
{
// @TODO will need update if new client scheme (one supported by Kiwi_Client class) is used
if ($emailRecord['ClientID']) {
$dbh = Project_DB::get();
$query = 'SELECT ID, FirstName, SurName, Salutation, Title, Email, FirmEmail, BusinessName FROM eshopclients WHERE ID=:id';
$stmt = $dbh->prepare($query);
$stmt->bindValue(":id", $emailRecord['ClientID'], PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
} else {
$row = array();
}
$row['Code'] = $emailRecord['Code'];
return $row;
}
public function getSearchSQL()
{
$this->_sql_params = array();
$dbh = Project_DB::get();
$query = "SELECT Contained FROM eshoph WHERE Container=:pmi";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':pmi', $this->_pmi, PDO::PARAM_INT);
$stmt->execute();
$sub_groups = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$sub_groups[] = $row['Contained'];
}
$stmt->closeCursor();
if (!empty($sub_groups)) {
$sub_groups_str = implode(',', $sub_groups);
$search_pmi_sql = " AND PB.GID IN ({$sub_groups_str})";
}
if (!isset($search_pmi_sql)) {
$search_pmi_sql = "";
}
$search_flags_sql = "";
if ($this->_novelty) {
$search_flags_sql .= " AND P.Novelty=1";
}
if ($this->_action) {
$search_flags_sql .= " AND P.Action=1";
}
if ($this->_discount) {
$search_flags_sql .= " AND P.Discount=1";
}
if ($this->_title !== null && $this->_title !== '') {
$search_title_sql = " AND P.Title LIKE :title";
$this->_sql_params['title'] = '%' . $this->_title . '%';
} else {
$search_title_sql = "";
}
$search_cost_sql = '';
if ($this->_cost_min !== null) {
$search_cost_sql .= " AND P.NewCost>=:cmin";
$this->_sql_params['cmin'] = (int) $this->_cost_min;
}
if ($this->_cost_max !== null) {
$search_cost_sql .= " AND P.NewCost<=:cmax";
$this->_sql_params['cmax'] = (int) $this->_cost_max;
}
$search_properties_sql = '';
if ($this->_properties !== null) {
$prop_filters = array('==' => array(), '<=' => array(), '>=' => array(), '<>' => array(), '?s' => array());
$matches = array();
// parse the property filter string:
$properties = explode(',', $this->_properties);
foreach ($properties as $prop_filter) {
/*
* $prop_filter can be either:
* 1. number - id of property value
* 2. interval in format of number-number
* 2.1 0-number
* 2.2 number-0
* 2.3 non_zero_number-non_zero_number
* 3. number:string - id of property and string to search for in property values
*/
if (preg_match('/^[0-9]+$/', $prop_filter)) {
$prop_filters['=='][] = (int) $prop_filter;
} elseif (preg_match('/^([0-9]+)-([0-9]+)$/', $prop_filter, $matches)) {
$lbound = (int) $matches[1];
$rbound = (int) $matches[2];
if ($lbound == 0 && $rbound > 0) {
$prop_filters['<='][] = $rbound;
} elseif ($lbound > 0 && $rbound == 0) {
$prop_filters['>='][] = $lbound;
} elseif ($lbound > 0 && $lbound == $rbound) {
$prop_filters['=='][] = $lbound;
} elseif ($lbound > 0 && $rbound > 0) {
$prop_filters['<>'][] = array('low' => $lbound, 'high' => $rbound);
}
} elseif (preg_match('/^([0-9]+):(.+)$/', $prop_filter, $matches)) {
$prop_id = (int) $matches[1];
$pv_str = $matches[2];
$prop_filters['?s'][] = array('pid' => $prop_id, 'string' => $pv_str);
}
}
$filter_selects = array();
$si = 1;
foreach ($prop_filters['=='] as $ppvid) {
$filter_selects[$si] = "SELECT PID AS PID{$si} FROM prodpbinds WHERE PPVID=:ppvid{$si}";
$this->_sql_params['ppvid' . $si] = $ppvid;
$si++;
}
foreach ($prop_filters['<='] as $ppvid) {
$filter_selects[$si] = "SELECT B{$si}.PID AS PID{$si} FROM prodpbinds AS B{$si} JOIN prodpvals AS V{$si} ON B{$si}.PPVID=V{$si}.ID AND V{$si}.Active=1 JOIN prodpvals AS V{$si}H ON V{$si}.PID=V{$si}H.PID WHERE V{$si}H.ID=:ppvid{$si} AND V{$si}.Priority<=V{$si}H.Priority";
$this->_sql_params['ppvid' . $si] = $ppvid;
$si++;
}
foreach ($prop_filters['>='] as $ppvid) {
$filter_selects[$si] = "SELECT B{$si}.PID AS PID{$si} FROM prodpbinds AS B{$si} JOIN prodpvals AS V{$si} ON B{$si}.PPVID=V{$si}.ID AND V{$si}.Active=1 JOIN prodpvals AS V{$si}L ON V{$si}.PID=V{$si}L.PID WHERE V{$si}L.ID=:ppvid{$si} AND V{$si}.Priority>=V{$si}L.Priority";
$this->_sql_params['ppvid' . $si] = $ppvid;
$si++;
}
foreach ($prop_filters['<>'] as $ppvids) {
$filter_selects[$si] = "SELECT B{$si}.PID AS PID{$si} FROM prodpbinds AS B{$si} JOIN prodpvals AS V{$si} ON B{$si}.PPVID=V{$si}.ID AND V{$si}.Active=1 JOIN prodpvals AS V{$si}L ON V{$si}.PID=V{$si}L.PID JOIN prodpvals AS V{$si}H ON V{$si}.PID=V{$si}H.PID WHERE V{$si}L.ID=:ppvidlow{$si} AND V{$si}H.ID=:ppvidhigh{$si} AND V{$si}.Priority BETWEEN V{$si}L.Priority AND V{$si}H.Priority";
$this->_sql_params['ppvidlow' . $si] = $ppvids['low'];
$this->_sql_params['ppvidhigh' . $si] = $ppvids['high'];
$si++;
}
foreach ($prop_filters['?s'] as $pp_search) {
$filter_selects[$si] = "SELECT B{$si}.PID AS PID{$si} FROM prodpbinds AS B{$si} JOIN prodpvals AS V{$si} ON B{$si}.PPVID=V{$si}.ID AND V{$si}.Active=1 AND V{$si}.PID=:ppsearch{$si} AND V{$si}.Value LIKE :substr{$si}";
$this->_sql_params['ppsearch' . $si] = $pp_search['pid'];
$this->_sql_params['substr' . $si] = '%' . $pp_search['string'] . '%';
$si++;
}
if (!empty($filter_selects)) {
// combine the selects
$filter_sql = 'SELECT F1.PID1';
foreach ($filter_selects as $si => $select) {
if ($si == 1) {
$filter_sql .= " FROM ({$select}) AS F1";
$si++;
} else {
$filter_sql .= " JOIN ({$select}) AS F{$si} ON F1.PID1=F{$si}.PID{$si}";
$si++;
}
}
$search_properties_sql = " AND P.ID IN ({$filter_sql})";
} else {
$search_properties_sql = '';
}
}
$search_sql = $search_pmi_sql . $search_flags_sql . $search_title_sql . $search_cost_sql . $search_properties_sql;
return $search_sql;
}
public function loadNewsItem(&$items, $item)
{
$dbh = Project_DB::get();
$filter = array();
$values = array();
if ($this->ngid !== null) {
$filter[] = ' N.NGID = :ngid';
$values['ngid'] = (int) $this->ngid;
}
if ($this->newsgroup !== null) {
$filter[] = ' G.Title = :newsgroup';
$values['newsgroup'] = $this->newsgroup;
}
$filter[] = ' N.ID = :item';
$values['item'] = (int) $item;
$where = implode(' AND', $filter);
$query = "SELECT N.ID, N.Name, N.Author, N.Sample, N.Content, N.`When`, N.Start, N.End FROM news AS N JOIN newsgroups AS G ON N.NGID=G.ID WHERE{$where}";
$stmt = $dbh->prepare($query);
foreach ($values as $key => $value) {
$stmt->bindValue(":{$key}", $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$items = array();
if ($stmt->execute()) {
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$items = array(new Var_Pool($row));
}
}
}
