Subversion Repositories SmartDukaan

<?php
App::uses('AppController', 'Controller');
/**
 * Exceptionalnlcs Controller
 *
 * @property Exceptionalnlc $Exceptionalnlc
 * @property PaginatorComponent $Paginator
 */
class ReportsController extends AppController {

/**
 * Components
 *
 * @var array
 */
        public $components = array('Paginator');

        public function beforeFilter() {
                parent::beforeFilter();
//              Configure::load('live');
                $this->apihost = Configure::read('pythonapihost');
        }

        public function admin_orders() {
                $page = $this->request->query('page');
                if(!isset($page)){
                        $page = 1;
                }
                $limit = Configure::read('admindashboardlimit');
                $params = array(
                        'fields' => array('_id','subOrders','storeId', 'userId', 'merchantOrderId'),
                        // 'conditions' => array('source_id' => array('$ne' => 0)),
                        'order' => array('_id' => -1),
                        'limit' => $limit,
                        'page' => $page,
                );
                $orders = $this->Report->find('all', $params);
                // $this->loadModel('Category');
                // $categories = $this->Category->find('list');
                // $storemapping = Configure::read('storemapping');
                $this->set(compact('orders'));
        }       

        public function admin_ordersjson() {    
                $this->layout = 'ajax';
                $page = $this->request->query('page');
                if(!isset($page)){
                        $page = 1;
                }
                $limit = $this->request->query('limit');
                $limit = Configure::read('admindashboardlimit');
                $params = array(
                        'fields' => array('_id','subOrders','storeId', 'userId', 'merchantOrderId'),
                        // 'conditions' => array('source_id' => array('$ne' => 0)),
                        'order' => array('_id' => -1),
                        'limit' => $limit,
                        'page' => $page,
                );
                $orders = $this->Report->find('all', $params);
                $result = array('orders'=>$orders);
                $this->set(array(
                    'result' => $result,
                    '_serialize' => array('result')
                ));
                $this->render('/Elements/json');
        }

        public function admin_activations() {
                $data = $this->request->data;
                if(!empty($data)){
                        $date = $data['Report']['date']['year'].'-'.$data['Report']['date']['month'].'-'.$data['Report']['date']['day'];
                        $otherSql="SELECT a.name, COUNT( r.id ) AS count FROM  `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = '$date' and r.user_id is not null and date(u.activation_time)='$date' GROUP BY a.id";
                        $sql = "SELECT DATE(activation_time ) AS date, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE DATE(activation_time) = '$date' AND  (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer";
                } else{
                        $otherSql="SELECT a.name, COUNT( r.id ) AS count FROM  `retailerlinks` r LEFT JOIN agents a ON r.agent_id = a.id join users u on r.user_id=u.id WHERE DATE( r.activated ) = CURDATE( ) and r.user_id is not null and date(u.activation_time)=curdate() GROUP BY a.id";
                        $sql = "SELECT DATE(activation_time) AS date, id, utm_campaign,referrer , COUNT( id ) AS count FROM users WHERE  DATE(activation_time) = CURDATE() AND  (utm_campaign IS NOT NULL OR referrer IS NOT NULL) GROUP BY DATE(activation_time) ,utm_campaign,referrer, id";
                }
                $this->loadModel('User');               
                $activations = $this->User->query($sql);
                foreach ($activations as $key => $value) {
                        if(isset($value['users']['utm_campaign'])){
                                #debug($value['users']['utm_campaign']);                
                                $val=$value['users']['id'];
                                $sql = "SELECT name from agents where id = (select agent_id from retailerlinks where user_id=$val);";
                                $this->loadModel('Agent');      
                                $agentname = $this->Agent->query($sql);
                                $citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where user_id=$val);";
                                $this->loadModel('Retailer');   
                                $retailerCity = $this->Retailer->query($citySql);
                                array_push($activations[$key], $agentname[0]['agents']['name']);
                                array_push($activations[$key], $retailerCity[0]['retailers']['city']);
                        }else{
                                #debug($value['users']['referrer']);                    
                                $val=$value['users']['id'];
                                $sql1 = "SELECT name from agents where id = (select agent_id from retailerlinks where user_id=$val);";
                                $this->loadModel('Agent');      
                                $agentname = $this->Agent->query($sql1);
                                $citySql = "SELECT city from retailers where id = (select retailer_id from retailerlinks where user_id=$val);";
                                $this->loadModel('Retailer');   
                                $retailerCity = $this->Retailer->query($citySql);
                                array_push($activations[$key], $agentname[0]['agents']['name']);
                                array_push($activations[$key], $retailerCity[0]['retailers']['city']);
                        }
                }
                $agentActivations = $this->User->query($otherSql);
                $this->set('agentActivations',$agentActivations);
                $this->set(compact('activations','date'));
        }

        public function admin_brandpreferences_report() {
                $this->loadModel('BrandPreference');
                $sql = "SELECT DATE( created ) date, brand, COUNT( id ) count FROM  `brand_preferences` WHERE STATUS =  'show' AND DATEDIFF( NOW( ) , created ) <= 7 GROUP BY  `brand_preferences`.`brand` , DATE( created ) ORDER BY DATE( created ) DESC,count desc";
                $brand_preferences = $this->BrandPreference->query($sql);
                $sql = "SELECT brand, COUNT( id ) count FROM  `brand_preferences` WHERE STATUS =  'show' AND DATEDIFF( NOW( ) , created ) <= 7 GROUP BY  `brand_preferences`.`brand` ORDER BY count desc";
                $count = $this->BrandPreference->query($sql);
                $this->set(compact('brand_preferences','count'));
        }

        public function admin_brandfilters_report() {           
                set_time_limit(180);
                $this->loadModel('UserFilter');         
                $sql = "select date(created) date,group_concat(filters separator '|') brands from user_filters where type = 'brand' group by date(created) ORDER BY id DESC";
                $brand_filters = $this->UserFilter->query($sql);
                $this->set(compact('brand_filters','all'));
        }

        public function admin_dailyupgrades_report() {
                $this->loadModel('Device');
                $data = $this->request->data;
                if(!empty($data)){
                        $date = $data['Report']['date']['year'].'-'.$data['Report']['date']['month'].'-'.$data['Report']['date']['day'];
                        $sql = "select count(id) as count,date(created) as date,user_id  from devices where imeinumber in (select imeinumber from devices where date(created) = '$date') group by imeinumber having count(id) > 1 order by id desc";
                } else{
                        $sql = "select count(id) as count,date(created) as date,user_id  from devices where imeinumber in (select imeinumber from devices where date(created) = curdate()) group by imeinumber having count(id) > 1 order by id desc";
                }
                $device_upgrades = $this->Device->query($sql);
                $this->set(compact('device_upgrades'));
        }

        public function admin_clicks_report($product=null){
                $this->loadModel('Click');
                $date = $this->request->query('date');
                if($product==null || $product=='search'){       
                        if(!empty($date)){
                                $sql = "select count(*) as count,product_name,brand,count(distinct user_id) as countUserId from clicks where length(product_name)>0 and store_product_id>0 and date(created)='$date' group by store_product_id order by count desc";
                        } else{
                                $sql = "select count(*) as count,product_name,brand,count(distinct user_id) as countUserId from clicks where length(product_name)>0 and store_product_id>0 and date(created)=curdate() group by store_product_id order by count desc";
                        }
                        $clicks_count = $this->Click->query($sql);
                        $this->set(compact('clicks_count'));
                }else{
                        $sql="select count(*) as count,date(created) date,count(distinct user_id) as countUserId from clicks where length(product_name)>0 and store_product_id>0 and product_name ='$product' group by date(created) order by date desc";
                        $product_count = $this->Click->query($sql);
                        $this->set(compact('product_count','product'));
                }
        }

        public function admin_productviews_report($product=null) {      
                set_time_limit(180);
                $this->loadModel('UserUrl');
                $this->loadModel('MasterData');
                $date = $this->request->query('date');

                if($product==null || $product=='search'){       
                        if(!empty($date)){
                                $sql = "SELECT DATE( created ) date, count(id) AS count,count(distinct user_id) as countUserId, SUBSTRING_INDEX(SUBSTRING_INDEX( url,  '/', -1 ),'?',1)  AS skuBundleId FROM  `user_urls` WHERE date(created) = '$date' AND url LIKE '%store_products/view%' GROUP BY skuBundleId ORDER BY countUserId DESC,count DESC";
                        } else{
                                $sql = "SELECT DATE( created ) date, count(id) AS count,count(distinct user_id) as countUserId, SUBSTRING_INDEX(SUBSTRING_INDEX( url,  '/', -1 ),'?',1)  AS skuBundleId FROM  `user_urls` WHERE date(created) = curdate() AND url LIKE '%store_products/view%' group by skuBundleId ORDER BY countUserId DESC,count DESC";
                        }
                        $clicks_count = $this->UserUrl->query($sql);
                        $this->set(compact('clicks_count'));
                } else{
                        $sql = "SELECT DATE( created ) date, count(id) AS count,count(distinct user_id) as countUserId, SUBSTRING_INDEX(SUBSTRING_INDEX( url,  '/', -1 ),  '/', -1 ) AS skuBundleId FROM  `user_urls` WHERE SUBSTRING_INDEX( url,  '/', -1 ) = $product group by date(created) ORDER BY id DESC ";
                        $product_count = $this->UserUrl->query($sql);
                        $this->set(compact('product_count','product'));
                }       
                $params = array(
                        'fields' => array('skuBundleId','brand','source_product_name'),
                        'conditions' => array('source_id' => array('$ne' => 0)),
                        'order' => array('_id' => 1),
                );
                $masterdata = $this->MasterData->find('all', $params);

                foreach ($masterdata as $key => $value) {
                        $map[$value['MasterData']['skuBundleId']] = array('name'=>$value['MasterData']['source_product_name'],'brand'=>$value['MasterData']['brand']);
                }
                $this->set(compact('map'));
        }

        public function admin_saledetail(){
                $date  = date("Y-m-d");
                $dt   = new DateTime($date);
                $url = $this->apihost."getSaleDetail/".$dt->getTimestamp();
                $result = $this->make_request($url,null);
                $this->set(compact('result'));
        }
        public  function getDetails($dateval)
        {
                $this->autoRender = false;      
                $this->request->onlyAllow('ajax');
                $dt   = new DateTime($dateval);
                $url = $this->apihost."getSaleDetail/".$dt->getTimestamp();
                $result = $this->make_request($url,null);
                return json_encode($result);
        }
        
        public function admin_pendingapproval($type = null,$id = null,$user_id = null,$amount = null,$rtype = null){
                $page = $this->request->query('page');
                if(!isset($page)){
                        $page = 1;
                }
                $limit = 20;
                $offset = ($page - 1)*$limit;
                $sendata = array();
                if($type != null){
                        $senddata['user_id'] = $user_id;
                        $senddata['_id'] = $id;
                        $senddata['amount'] = $amount;
                        $senddata['type'] = $rtype;
                        $senddata['approved_by'] = $this->Auth->user('email');
//                      $senddata = '{"user_id" : '.$user_id.' , "_id" : '.$id.'}';
                        $sendurl = $this->apihost."updateRefundStatus?status=".$type;
                        $this->make_request($sendurl, json_encode($senddata));
                        return $this->redirect(array('action' => 'admin_pendingapproval'));
                }               
                $url  = $this->apihost."refundToWallet?offset=".$offset."&limit=".$limit."&status=Pending";
                $pending = $this->make_request($url, null);;
                $this->set(compact('pending','page'));
        }

                public function admin_allstatus($type = null,$id = null,$user_id = null,$amount = null,$rtype = null){
                $page = $this->request->query('page');
                if(!isset($page)){
                        $page = 1;
                }
                $limit = 50;
                $offset = ($page - 1)*$limit;
                $url  = $this->apihost."refundToWallet?offset=".$offset."&limit=".$limit;
                $pending = $this->make_request($url, null);;
                $this->set(compact('pending','page'));
        }
}