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