public function getValues()
{
parent::getValues();
$query = $this->db->newStatement("\n SELECT COUNT(category_id) cnt, user_id, DATE_FORMAT(date_creation,'%Y-%m') date FROM log_article\n WHERE DATE_FORMAT(date_creation,'%Y-%m')>=:f: AND DATE_FORMAT(date_creation,'%Y-%m')<=:t: AND user_id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) AND type=0\n GROUP BY user_id, DATE_FORMAT(date_creation,'%Y-%m')\n ");
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->conn->newStatement("\n SELECT SUM(t.daily) cnt, employee_id user_id, DATE_FORMAT(date,'%Y-%m') date FROM (\n SELECT ROUND(IF(SUM(TIME_TO_SEC(finish)-TIME_TO_SEC(start))>4,14400,SUM(TIME_TO_SEC(finish)-TIME_TO_SEC(start)))/3600) daily, employee_id, DATE_FORMAT(start,'%Y-%m-%d') date \n FROM timesheet \n WHERE DATE_FORMAT(start,'%Y-%m')>=:f: AND DATE_FORMAT(start,'%Y-%m')<=:t: AND employee_id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) AND DATE_FORMAT(start,'%Y-%m-%d')=DATE_FORMAT(finish,'%Y-%m-%d')\n GROUP BY employee_id, DATE_FORMAT(start,'%Y-%m-%d')\n ) t\n GROUP BY employee_id, DATE_FORMAT(date,'%Y-%m') \n ");
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->conn->newStatement("\n SELECT ROUND(SUM(a.profit)) cnt, DATE_FORMAT(a.status_date,'%Y-%m') date, a.manager_id user_id\n FROM (\n (SELECT op1.manager_id, op1.creator_id, o1.owner_id, op1.status_date, op1.warehouse_id,\n op1.quantity*(op1.price-op1.purchase_price-op1.transport_charges)*IF(o1.user_id=3199,0.5,1) profit\n FROM order_position op1\n INNER JOIN `order` o1\n ON o1.id=op1.order_id\n WHERE op1.status_id=(SELECT id FROM order_status WHERE code='IS_TRANSFERED'))\n UNION ALL\n (SELECT op2.manager_id manager_id, IF(pctp.manager_id=0,op2.creator_id,pctp.manager_id) creator_id, o2.owner_id, op2.status_date, op2.warehouse_id,\n IF(pctp.change_type_id IN (11,61),0,pctp.amount) profit\n FROM order_position op2\n INNER JOIN price_change_to_position pctp ON pctp.order_position_id=op2.id AND !pctp.deactivated AND !op2.client_price\n INNER JOIN price_change_type pct ON pctp.change_type_id=pct.id\n INNER JOIN `order` o2 ON o2.id=op2.order_id\n WHERE op2.status_id=(SELECT id FROM order_status WHERE code='IS_TRANSFERED'))\n ) a\n WHERE a.owner_id=:oid: AND DATE_FORMAT(a.status_date,'%Y-%m')>=:f:\n AND DATE_FORMAT(a.status_date,'%Y-%m')<=:t: AND a.manager_id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) \n GROUP BY a.manager_id, DATE_FORMAT(a.status_date,'%Y-%m') \n \n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->conn->newStatement("\n SELECT ROUND(SUM(IF(\n !bop.amount,\n IF(b.payment_type_id=3,IF(cag.with_vat,0.984,0.98),1)*bo.amount,\n IF(bop.change_id,-(1-pct.profit)*bop.amount,IF(bo.type='cancel',-1,1)*(bop.amount*IF(b.payment_type_id=3,IF(cag.with_vat,0.984,0.98),1)-(op.purchase_price+op.transport_charges)*IF(bop.quantity,bop.quantity,op.quantity)))\n ))*0.1) cnt, DATE_FORMAT(b.datetime,'%Y-%m') date, u.id user_id\n FROM balance_operation bo\n INNER JOIN balance_operation_position bop on bop.operation_id=bo.id\n INNER JOIN balance b on b.id=bo.balance_id\n INNER JOIN order_position op on op.id=bop.position_id\n INNER JOIN `order` o ON o.id=op.order_id\n INNER JOIN client_address ca ON ca.id=o.address_id\n INNER JOIN client c ON c.id=ca.client_id\n INNER JOIN pm_employee pe ON pe.user_id=c.manager_id\n LEFT OUTER JOIN user u ON u.id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":)\n LEFT OUTER JOIN counteragent cag ON cag.id=bo.seller_id\n LEFT OUTER JOIN price_change_type pct ON pct.id=bop.change_id\n WHERE bo.type IN ('cancel','payment','forfeit','markdown') AND c.manager_id=pe.user_id AND o.owner_id=:oid: AND DATE_FORMAT(b.datetime,'%Y-%m')>=:t: AND DATE_FORMAT(b.datetime,'%Y-%m')<=:t:\n GROUP BY u.id, DATE_FORMAT(b.datetime,'%Y-%m')\n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->conn->newStatement("\n SELECT ROUND(SUM(a.profit*0.1)) cnt, DATE_FORMAT(a.status_date,'%Y-%m') date, u.id user_id\n FROM (\n (SELECT op1.manager_id, op1.creator_id, o1.owner_id, op1.status_date, op1.warehouse_id,\n op1.quantity*(op1.price-op1.purchase_price-op1.transport_charges)*IF(o1.user_id=3199,0.5,1) profit\n FROM order_position op1\n INNER JOIN `order` o1 ON o1.id=op1.order_id\n WHERE op1.status_id=(SELECT id FROM order_status WHERE code='IS_TRANSFERED'))\n UNION ALL\n (SELECT op2.manager_id manager_id, IF(pctp.manager_id=0,op2.creator_id,pctp.manager_id) creator_id, o2.owner_id, op2.status_date, op2.warehouse_id,\n pctp.amount profit\n FROM order_position op2\n INNER JOIN price_change_to_position pctp ON pctp.order_position_id=op2.id AND !pctp.deactivated AND !op2.client_price\n INNER JOIN price_change_type pct ON pctp.change_type_id=pct.id\n INNER JOIN `order` o2 ON o2.id=op2.order_id\n WHERE op2.status_id=(SELECT id FROM order_status WHERE code='IS_TRANSFERED'))\n ) a\n LEFT OUTER JOIN user u ON u.id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":)\n WHERE a.owner_id=:oid: AND DATE_FORMAT(a.status_date,'%Y-%m')>=:f: AND DATE_FORMAT(a.status_date,'%Y-%m')<=:t: AND (\n \ta.creator_id IN (1937,4868,4864,6976)\n \tOR a.creator_id=2544 AND DATE_FORMAT(a.status_date,'%Y-%m')='2013-02'\n \tOR a.creator_id IN (8049,8038,8161,7061) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-08-01'\n \tOR a.creator_id IN (8407,8599) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-09-01'\n \tOR a.creator_id IN (8567) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-09-01' AND DATE_FORMAT(a.status_date,'%Y-%m-%d')<'2014-10-01'\n \tOR a.creator_id IN (8230) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-09-01' AND DATE_FORMAT(a.status_date,'%Y-%m-%d')<'2014-06-01'\n \tOR a.creator_id IN (8724,8744,8942) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-10-01'\n \tOR a.creator_id IN (8989) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-11-01'\n \tOR a.creator_id IN (9358) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-12-01'\n \tOR a.creator_id IN (9469) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2013-12-01' AND DATE_FORMAT(a.status_date,'%Y-%m-%d')<'2014-06-01'\n \tOR a.creator_id IN (9970) AND (DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-01-01' AND DATE_FORMAT(a.status_date,'%Y-%m-%d')<'2014-06-01' OR DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-07-01')\n \tOR a.creator_id IN (10798,10898) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-04-01'\n \tOR a.creator_id IN (10455) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-04-10'\n \tOR a.creator_id IN (126) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-04-15'\n \tOR a.creator_id IN (11217) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-05-01'\n \tOR a.creator_id IN (11300,11217) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-05-01'\n \tOR a.creator_id IN (11564) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-07-01'\n \tOR a.creator_id IN (12690) AND DATE_FORMAT(a.status_date,'%Y-%m-%d')>='2014-10-01'\n \t)\n GROUP BY u.id, DATE_FORMAT(a.status_date,'%Y-%m')\n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->conn->newStatement("\n SELECT ROUND(SUM(a.profit)) cnt, DATE_FORMAT(a.status_date,'%Y-%m') date, u.id user_id\n FROM (\n (SELECT op1.manager_id, op1.creator_id, o1.owner_id, op1.status_date, op1.warehouse_id,\n op1.quantity*IF(os.code='IS_RETURNED',-op1.price,op1.price)*IF(o1.user_id=3199,0.5,1) profit, op1.operator_status_id\n FROM order_position op1\n INNER JOIN `order` o1 ON o1.id=op1.order_id\n INNER JOIN order_status os ON os.id=op1.status_id\n WHERE os.code IN ('IS_RETURNED','IS_TRANSFERED'))\n UNION ALL\n (SELECT op2.manager_id manager_id, IF(pctp.manager_id=0,op2.creator_id,pctp.manager_id) creator_id, o2.owner_id, op2.status_date, op2.warehouse_id,\n IF(os.code='IS_RETURNED',-pctp.amount,pctp.amount) profit, op2.operator_status_id\n FROM order_position op2\n INNER JOIN price_change_to_position pctp ON pctp.order_position_id=op2.id AND !pctp.deactivated AND !op2.client_price\n INNER JOIN price_change_type pct ON pctp.change_type_id=pct.id\n INNER JOIN `order` o2 ON o2.id=op2.order_id\n INNER JOIN order_status os ON os.id=op2.status_id\n WHERE os.code IN ('IS_RETURNED','IS_TRANSFERED'))\n ) a\n LEFT OUTER JOIN user u ON u.id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) \n LEFT OUTER JOIN employee_data ed ON ed.user_id=a.operator_status_id\n LEFT OUTER JOIN warehouse w ON w.id=ed.point_id\n WHERE a.owner_id=:oid: AND DATE_FORMAT(a.status_date,'%Y-%m')>=:f:\n AND DATE_FORMAT(a.status_date,'%Y-%m')<=:t: AND (\n a.creator_id IN (4980,7586) OR\n w.city_id=63 AND DATE_FORMAT(a.status_date,'%Y-%m')>='2013-10'\n )\n GROUP BY u.id, DATE_FORMAT(a.status_date,'%Y-%m') \n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$query = $this->db->newStatement("\n SELECT ROUND(0.02*SUM(op.price*op.quantity*IF(o.user_id=3199,0.5,1))) cnt, DATE_FORMAT(op.status_date,'%Y-%m') date, u.id user_id\n FROM order_position op\n INNER JOIN `order` o ON o.id=op.order_id\n INNER JOIN user u ON u.id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) \n WHERE o.owner_id=:oid: AND DATE_FORMAT(op.status_date,'%Y-%m')>=:f: \n AND op.status_id=(SELECT id FROM order_status WHERE code='IS_TRANSFERED')\n AND DATE_FORMAT(op.status_date,'%Y-%m')<=:t: AND o.payment_type_id=3\n GROUP BY u.id, DATE_FORMAT(op.status_date,'%Y-%m') \n \n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
return $this->processResult($query->getAllRecords());
}
public function getValues()
{
parent::getValues();
$date = $this->start;
$results = array();
while ($date <= $this->end) {
$query = $this->conn->newStatement("\n SELECT SUM(IF(d.position_id,op.purchase_price* op.quantity,d.price_purchase*d.quantity)) cnt, u.id user_id, :t: date\n FROM defect d\n LEFT OUTER JOIN order_position op ON op.id=d.position_id\n LEFT OUTER JOIN `order` o ON o.id=op.order_id\n LEFT OUTER JOIN product p ON p.id=d.product_id\n LEFT OUTER JOIN user u ON u.id IN (:uid" . implode(':,:uid', array_keys($this->users)) . ":) \n WHERE (d.position_id AND o.owner_id=:oid: OR !d.position_id AND p.owner_id=:oid:) AND DATE_FORMAT(d.date_creation,'%Y-%m')<=:t: AND (\n DATE_FORMAT(d.product_solved,'%Y-%m')>:t: OR d.product_solved IS NULL\n OR d.supposed_compensation AND (DATE_FORMAT(d.supplier_solved,'%Y-%m')>:t: OR d.supplier_solved IS NULL)\n OR d.position_id AND (DATE_FORMAT(d.client_solved,'%Y-%m')>:t: OR d.client_solved IS NULL)\n )\n GROUP BY u.id, :t: \n ");
$query->setVarChar('t', $date);
$query->setInteger('oid', OWNER_ID);
foreach ($this->users as $key => $curr) {
$query->setInteger('uid' . $key, $curr);
}
$results = array_merge($results, $query->getAllRecords());
$date = date('Y-m', mktime(0, 0, 0, date('m', strtotime($date . '-01')) + 1, date("01"), date('Y', strtotime($date . '-01'))));
}
return $this->processResult($results);
}
