function top10() { $this->layout = 'defaultlayout'; $periods = __getPeriods(); $sites = $this->Site->find('list', array('fields' => array('id', 'sitename'))); $rs = array(); if (!empty($this->request->data)) { $conds = array(); $selsitenum = $this->request->data['Top10']['selsitenum']; $start = $this->request->data['Top10']['start']; $end = $this->request->data['Top10']['end']; $groupby = $this->request->data['Top10']['groupby']; $conds['startdate'] = $start; $conds['enddate'] = $end; if ($selsitenum == 1) { $conds['siteids'] = array(12, 13); } else { $conds['siteids'] = array_keys($sites); } $rs = $this->__top10($conds, $groupby); } $this->set(compact('rs')); $this->set(compact('start')); $this->set(compact('end')); $this->set(compact('periods')); $this->set(compact('sites')); $this->set(compact('conds')); $this->set(compact('groupby')); }
/** * $bywhat: 0 means group by office, etc. * $periods: should be 2 arrays in an array like the following * "[[2016-01-01,2016-01-07], [2016-01-08,2016-01-14]]" */ function progresses() { if ($this->Auth->user('Account.role') != 0) { $this->Session->setFlash('Sorry, the page you try to reach is not allowed.'); $this->redirect(array('controller' => 'accounts', 'action' => 'index')); return; } $this->layout = "defaultlayout"; $bywhat = array_key_exists('bywhat', $this->passedArgs) ? $this->passedArgs['bywhat'] : null; $periods = array_key_exists('periods', $this->passedArgs) ? $this->passedArgs['periods'] : null; $status = array_key_exists('status', $this->passedArgs) ? $this->passedArgs['status'] : null; $this->set(compact("bywhat")); $periods = explode(",", $periods); $this->set(compact("periods")); $this->set(compact("status")); $sel_periods = __getPeriods(); $last_period = array_keys($sel_periods); $last_period = array_pop($last_period); $last_period = array($last_period => "THIS YEAR"); array_pop($sel_periods); $sel_periods += $last_period; $this->set(compact("sel_periods")); $start0 = count($periods) > 0 ? $periods[0] : null; $end0 = count($periods) > 1 ? $periods[1] : null; $start1 = count($periods) > 2 ? $periods[2] : null; $end1 = count($periods) > 3 ? $periods[3] : null; $sites = $this->Site->find('list', array('fields' => array('id', 'sitename'))); $statuses = $this->ViewCompany->find('all', array('fields' => array('status'), 'distinct' => 'status', 'order' => 'status desc')); $offi_statuses = array(); foreach ($statuses as $s) { $offi_statuses[$s['ViewCompany']['status']] = $this->Account->status[$s['ViewCompany']['status']]; } $this->set(compact('offi_statuses')); $conn = new zmysqlConn(); switch ($bywhat) { /* * means group by office */ case 0: if ($start0 != 'y') { /* * weekly or monthly, one by one only once */ $sql = "select d.companyid, c.officename, sum(d.total) as total\n\t\t\t\t\t\tfrom daily_stats d, companies c, accounts a\n\t\t\t\t\t\twhere d.day >= '{$start0}' and d.day <= '{$end0}'\n\t\t\t\t\t\t\tand d.companyid = c.id and c.id = a.id and a.status = {$status}\n\t\t\t\t\t\tgroup by d.companyid\n\t\t\t\t\t\torder by c.officename\n\t\t\t\t\t"; $rs0 = mysql_query($sql, $conn->dblink); $sql = "select d.companyid, c.officename, sum(d.total) as total\n\t\t\t\t\t\tfrom daily_stats d, companies c, accounts a\n\t\t\t\t\t\twhere d.day >= '{$start1}' and d.day <= '{$end1}'\n\t\t\t\t\t\t\tand d.companyid = c.id and c.id = a.id and a.status = {$status}\n\t\t\t\t\t\tgroup by d.companyid\n\t\t\t\t\t\torder by c.officename\n\t\t\t\t\t"; $rs1 = mysql_query($sql, $conn->dblink); $ra0 = $ra1 = array(); while ($r = mysql_fetch_assoc($rs0)) { $ra0[$r['companyid']] = array($r['officename'], $r['total']); } while ($r = mysql_fetch_assoc($rs1)) { $ra1[$r['companyid']] = array($r['officename'], $r['total']); } $rd0 = array_diff_key($ra0, $ra1); $rd1 = array_diff_key($ra1, $ra0); foreach ($rd1 as $k => $v) { $ra0 += array($k => array($v[0], 0)); } foreach ($rd0 as $k => $v) { $ra1 += array($k => array($v[0], 0)); } $this->set(compact("ra0")); $this->set(compact("ra1")); } else { /* * 12 month (if exists 12 ones, otherwise the actual months there) * one by one in the whole year */ $comd = array(); $thisyear = date("Y"); $sql = "\n\t\t\t\t\t\tselect d.companyid, c.officename, \n\t\t\t\t\t\t\tsubstring(d.day, 1, 7) as mon, sum(d.total) as total\n\t\t\t\t\t\tfrom daily_stats d, companies c, accounts a\n\t\t\t\t\t\twhere d.companyid = c.id and c.id = a.id and a.status = {$status}\n\t\t\t\t\t\t\tand substring(day, 1, 4) = '{$thisyear}'\n\t\t\t\t\t\tgroup by d.companyid, mon\n\t\t\t\t\t\tORDER by c.officename, mon\n\t\t\t\t\t"; $rs = mysql_query($sql, $conn->dblink); $rprev = mysql_fetch_assoc($rs); if ($rprev !== false) { $comd[$rprev['officename']][$rprev['mon']] = array($rprev['total'], 0); while ($r = mysql_fetch_assoc($rs)) { if ($r['companyid'] == $rprev['companyid']) { $totprev = end($comd[$rprev['officename']])[0]; $comd[$rprev['officename']][$r['mon']] = array($r['total'], sprintf("%.0f", ($r['total'] - $totprev) / $totprev * 100)); } else { $rprev = $r; $comd[$rprev['officename']][$rprev['mon']] = array($rprev['total'], 0); } } } $this->set(compact("comd")); $this->set(compact("sql")); } break; default: break; } }