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