public static function fetchDistrictManagerShops($idManager) { $idShops = new Default_Model_Shops(); $select = $idShops->getMapper()->getDbTable()->select()->from(array('s' => 'shops'), array('*'))->joinLeft(array('dms' => 'district_manager_shops'), 'dms.idShop = s.id', array('dmsid' => 'dms.id'))->where('dms.idUser = ? ', $idManager)->setIntegrityCheck(false); $result = $idShops->fetchAll($select); return $result; }
public function statusDailySalesAction() { $curdate = date('Y-m-d'); $sixMonths = strtotime($curdate . ' - 6 months'); $startDate = strtotime(START_DATE); if ($startDate >= $sixMonths) { $finalDate = date('Y-m-d', $startDate); } else { $finalDate = date('Y-m-d', $sixMonths); } $select = $this->db->query("select aa.Date,dss.date,dss.shopIds,dss.count,s.count as nr\n\t\t\t\t\tfrom (\n\t\t\t\t\t\tselect curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n\t\t\t\t\t\tfrom (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n\t\t\t\t\t\tcross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n\t\t\t\t\t\tcross join (select 0 as a union all select 1) as c\n\t\t\t\t\t) aa\n\t\t\t\t\tLEFT JOIN(SELECT ds.date,GROUP_CONCAT(s.id) as shopIds,COUNT(s.id) as count\n\t\t\t\t\t FROM `daily_sales` as ds\n\t\t\t\t\t LEFT JOIN( shops as s) ON(ds.idShop=s.id)\n\t\t\t\t\t WHERE s.deleted = 0\n\t\t\t\t\t\t\t\tGROUP BY ds.date) as dss ON(dss.date=aa.Date)\n\t\t\t\t\tCROSS JOIN(select COUNT(shops.id) as count FROM shops WHERE shops.deleted = 0) as s\n\t\t\t\t\twhere TO_DAYS(aa.Date) > TO_DAYS('{$finalDate}') \n\t\t\t\t\tHAVING (IFNULL(dss.count,0)=0 OR IFNULL(dss.count,0)<nr)\n\t\t\t\t\tORDER BY aa.Date DESC"); $result = $select->fetchAll(); $shop = new Default_Model_Shops(); $selectShops = $shop->getMapper()->getDbTable()->select()->from(array('s' => 'shops'), array('id' => 's.id', 'name' => 's.name'))->where('NOT s.deleted'); $selectShops->order(array('id ASC')); $resultShops = $shop->fetchAll($selectShops); $shopIds = array(); $shopNames = array(); foreach ($resultShops as $value) { $shopIds[] = $value->getId(); $shopNames[$value->getId()] = $value->getName(); } $this->view->arrayShopIds = $shopIds; $this->view->arrayShopNames = $shopNames; if (NULL != $result) { $paginator = Zend_Paginator::factory($result); $paginator->setItemCountPerPage(10); $paginator->setCurrentPageNumber($this->_getParam('page')); $paginator->setPageRange(5); $this->view->result = $paginator; $this->view->itemCountPerPage = $paginator->getItemCountPerPage(); $this->view->totalItemCount = $paginator->getTotalItemCount(); Zend_Paginator::setDefaultScrollingStyle('Sliding'); Zend_View_Helper_PaginationControl::setDefaultViewPartial('_pagination.phtml'); } }