PHP EnmasseHelper::escapeSqlLikeSpecialChar方法代码示例

 /**
  * 
  * 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;
 }