/** * * Contruct the query for get list record by sale person id, * include filter and order condition. * @return string SQL query. */ private function _getListQuery() { $app = JFactory::getApplication(); $db = JFactory::getDbo(); $filter = JRequest::getVar('filter', '', '', 'array'); $query = "SELECT \n\t\t\t\t\t\t* \n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_merchant_branch\n\t\t\t\t\tWHERE sales_person_id = " . $this->getState('sales_person.id'); if ($filter != null) { //adding list filter condition if (isset($filter['id']) && $filter['id'] != "") { $query .= " AND id = " . intval($filter['id']); } if (isset($filter['name']) && $filter['name'] != "") { $query .= " AND name LIKE '%" . EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($filter['name'])) . "%' "; } if (isset($filter['published']) && $filter['published'] != "") { $query .= " AND published = " . $filter['published']; } } //adding order by condition $filter_order = $this->getState('filter_order'); $filter_order_dir = $this->getState('filter_order_dir'); if (!empty($filter_order)) { if ($filter_order == 'name' || $filter_order == 'user_name' || $filter_order == 'created_at') { $query .= ' ORDER BY ' . $filter_order . ' ' . $filter_order_dir; } } return $query; }
function search($name) { $db = JFactory::getDBO(); $query = "\tSELECT \n\t\t\t\t\t\t* \n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_merchant_branch"; if (!empty($name)) { $name = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($name)); $query .= " WHERE name LIKE '%{$name}%'"; } $query .= $this->_buildContentOrderBy(); $db->setQuery($query); $rows = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit')); if ($this->_db->getErrorNum()) { JError::raiseError(500, $this->_db->stderr()); return false; } return $rows; }
function searchBySaleReports($salesPersonId, $keyword, $merchantId, $fromdate, $todate, $sCode = null) { $db = JFactory::getDBO(); $query = "\tSELECT\n\t\t\t\t\t\t*\n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_deal as d, #__enmasse_order_item as oi \n\t\t\t\t\tWHERE\n\t\t\t\t\t\td.name = oi.description\n\t\t\t\t\t\tAND oi.status LIKE 'Delivered'\n\t\t\t\t\t\tAND d.sales_person_id = {$salesPersonId}"; if ($keyword != null) { $keyword = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($keyword)); $query .= "\tAND d.name like '%{$keyword}%'"; } if ($merchantId != null) { $query .= "\tAND d.merchant_id = {$merchantId}"; } $fromdate = $fromdate != null ? $fromdate : date('Y-m-d'); $todate = $todate != null ? $todate : date('Y-m-d'); $query .= "\tAND oi.updated_at BETWEEN '{$fromdate}' AND '{$todate}'"; //$query .= " AND oi.updated_at BETWEEN '2012-02-01' AND '2012-02-10'"; if ($sCode != null) { $sCode = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($sCode)); $query .= "\tAND d.deal_code like '%{$sCode}%'"; } //$query .= " ORDER BY created_at DESC"; $query .= " GROUP BY d.deal_code"; $db->setQuery($query); $dealList = $db->loadObjectList(); if ($db->getErrorNum()) { JError::raiseError(500, $db->getErrorMsg()); return false; } return $dealList; }
function search($code, $name, $sale, $mer, $fromdate, $todate) { //------------------------------------------------------------ // generate Query $db = JFactory::getDBO(); $query = "\tSELECT\n\t\t\t\t\t\t*\n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_deal as d, #__enmasse_order_item as oi \n\t\t\t\t\tWHERE\n\t\t\t\t\t\td.name = oi.description\n\t\t\t\t\t\tAND oi.status LIKE 'Delivered'\n\t\t\t\t"; if (!empty($code)) { $code = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($code)); $query .= " and d.deal_code like '%{$code}%'"; } if (!empty($name)) { $name = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($name)); $query .= " and d.name like '%{$name}%'"; } if (!empty($sale)) { $query .= " and d.sales_person_id = {$sale}"; } if (!empty($mer)) { $query .= " and d.merchant_id = {$mer}"; } $fromdate = $fromdate != null ? $fromdate : date('Y-m-d'); $todate = $todate != null ? $todate : date('Y-m-d'); $query .= "\tAND oi.updated_at BETWEEN '{$fromdate}' AND '{$todate}'"; $query .= " GROUP BY d.deal_code"; $rows = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit')); if ($this->_db->getErrorNum()) { JError::raiseError(500, $this->_db->stderr()); return false; } return $rows; }
function search($code, $name, $loc, $cat, $published, $status) { //------------------------------------------------------------------ // to create string of deal id base on category and location $ids = ''; if (count($loc) != 0 && count($cat) != 0) { for ($i = 0; $i < count($loc); $i++) { for ($x = 0; $x < count($cat); $x++) { if ($loc[$i] == $cat[$x]) { $idArr[] = $loc[$i]; } } } if (count($idArr) != 0) { $ids .= implode(",", $idArr); } else { $ids .= '0'; } } else { if (count($loc) != 0) { $ids .= implode(",", $loc); } else { if (count($cat) != 0) { $ids .= implode(",", $cat); } } } //------------------------------------------------------------ // generate Query $db = JFactory::getDBO(); $query = "\tSELECT\n\t\t*\n\t\tFROM\n\t\t#__enmasse_deal\n\t\tWHERE\n\t\t1=1"; if (!empty($code)) { $code = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($code)); $query .= " and deal_code like '%{$code}%'"; } if (!empty($name)) { $name = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($name)); $query .= " and name like '%{$name}%'"; } if ($published != null) { $query .= "\tAND published = {$published}"; } if (!empty($status) && trim($status) != '') { $query .= " and status = '{$status}'"; } if ($ids != '') { $query .= " AND id IN (" . $ids . ") "; } $query .= $this->_buildContentOrderBy(); $db->setQuery($query); $rows = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit')); if ($this->_db->getErrorNum()) { JError::raiseError(500, $this->_db->stderr()); return false; } return $rows; }
function search($status = null, $dealCode = null, $dealName = null, $orderBy = null, $orderType = null, $bPartial = false) { global $option; $app = JFactory::getApplication(); $filter = $app->getUserStateFromRequest($option . 'filter', 'filter', array(), 'array'); $db = JFactory::getDBO(); $query = "\tSELECT \n\t\t\t\t\t\to.*,\n\t\t\t\t\t\toi.description as deal_name,\n\t\t\t\t\t\toi.qty as qty,\n d.deal_code as deal_code\n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_order AS o,\n\t\t\t\t\t\t#__enmasse_order_item AS oi,\n #__enmasse_deal AS d\n\t\t\t\t\tWHERE \n\t\t\t\t\t\to.id = oi.order_id AND oi.pdt_id = d.id"; if (!empty($status)) { $query .= "\tAND o.status = '{$status}'"; } if (!empty($dealCode)) { $dealCode = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($dealCode)); $query .= " AND d.deal_code like '%{$dealCode}%'"; } if (!empty($dealName)) { $dealName = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($dealName)); $query .= " AND d.name like '%{$dealName}%'"; } if ($bPartial) { $query .= " AND d.prepay_percent < 100.0 "; } if (!empty($filter['year'])) { $query .= "\tAND YEAR(o.created_at) = " . intval($filter['year']); } if (!empty($filter['month'])) { $query .= "\tAND MONTH(o.created_at) = " . intval($filter['month']); } if (!empty($orderBy)) { $query .= " ORDER BY {$orderBy} "; } if (!empty($orderBy) && !empty($orderType)) { $query .= "\t{$orderType} "; } $db->setQuery($query); $rows = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit')); if ($this->_db->getErrorNum()) { JError::raiseError(500, $this->_db->stderr()); return false; } // Assign total records for pagination $this->_total = $this->_getListCount($query); return $rows; }
function getSearchTotal($name) { $name = str_replace("%", "!%", addslashes($name)); $db =& JFactory::getDBO(); $query = "\tSELECT \n\t\t\t\t\t\t* \n\t\t\t\t\tFROM \n\t\t\t\t\t\t#__enmasse_sales_person"; if (!empty($name)) { $name = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($name)); $query .= " where name like '%{$name}%' "; } $query .= $this->_buildContentOrderBy(); $db->setQuery($query); $rows = $this->_getList($query); if ($this->_db->getErrorNum()) { JError::raiseError(500, $this->_db->stderr()); return false; } return $rows; }