public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
$selectWrapper = "\n ROUND(profit*0.1) cnt\n ";
$select = "\n o.client_manager_id user_id, DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$clause = "\n o.client_manager_id IN (:user_id:)\n ";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01'))), 'user_id' => $this->ids];
$group = "o.client_manager_id, DATE_FORMAT(b.datetime,'%Y-%m')";
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "clause", "params", "group")));
}
public function save()
{
$data = $this->getSafeData();
$insert = ['type_id' => $data['type_id'], 'text' => \Arr::get($this->types, $data['type_id'], 'ЗП'), 'amount' => -$data['amount'], 'payment_type_id' => $data['payment_type_id'], 'creation_date' => date('Y-m-d H:i:s'), 'creator_id' => \Model::factory('User')->get('id'), 'approval_date' => date('Y-m-d H:i:s'), 'approver_id' => \Model::factory('User')->get('id'), 'source_id' => $data['source_id'], 'point_id' => $data['point_id']];
$id = \Model::admin('Expense')->insert($insert);
//Insert
if ($this->getData('accomplish')) {
\Model::admin('Expense')->accomplish($id, $insert['source_id']);
//проведен
}
$this->model('Salary')->updateReady($id);
return ['amount' => $insert['amount']];
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
$selectWrapper = "\n ROUND(profit) cnt\n ";
$select = "\n DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$from = "\n INNER JOIN representative r ON r.city_id=o.city_id AND r.is_central\n ";
$clause = "\n r.partnership\n ";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01')))];
$group = "DATE_FORMAT(b.datetime,'%Y-%m')";
return ['cnt' => 180000, 'date' => '2015-12', 'department_id' => 22, 'mount' => 12, 'year' => 2015];
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "from", "clause", "params", "group")));
}
public function getByFact($date)
{
if (!$date) {
$date = date($this->format_date);
}
$period = Calendar::getPeriodMonth($date);
$select = "\n DATE_FORMAT(b.datetime,'%Y-%m') month\n ";
$params = ['since' => $period['start'] . ' 00:00:00', 'till' => $period['end'] . ' 23:59:59'];
$group = "DATE_FORMAT(b.datetime,'%Y-%m-01')";
$all = \Model::admin('Balance')->buildOrderStatistics(compact("select", "params", "group"));
if (!empty($all)) {
return array_pop($all);
}
return 0;
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
$selectWrapper = "\n ROUND(profit*0.1) cnt\n ";
$select = "\n pe.department_id, DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$from = "\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 AND pe.department_id IN (:department_id:) AND pe.`start`<=DATE_FORMAT(:till:,'%Y-%m-%d') AND (pe.`end`>=DATE_FORMAT(:since:,'%Y-%m-%d') OR pe.`end` IS NULL)\n ";
$clause = "\n c.manager_id=pe.user_id AND b.datetime BETWEEN CONCAT(pe.`start`,' 00:00:00') AND CONCAT(IFNULL(pe.`end`,DATE_FORMAT(NOW(),'%Y-%m-%d')),' 23:59:59')\n ";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01'))), 'department_id' => $this->departments];
foreach ($this->users as $key => $curr) {
$params['uid' . $key] = $curr;
}
$group = "pe.department_id, DATE_FORMAT(b.datetime,'%Y-%m')";
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "from", "clause", "params", "group")));
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
$selectWrapper = "\n ROUND(retailAmount) cnt\n ";
$select = "\n pd.id department_id, DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$from = "\n INNER JOIN representative r ON r.city_id=o.city_id\n INNER JOIN pm_department pd ON r.id=pd.point_id\n ";
$clause = "\n pd.id IN (:department_id:)\n ";
$params = ['since' => $this->start_day . ' 00:00:00', 'till' => $this->end_day . ' 23:59:59', 'department_id' => $this->departments];
$group = "pd.id, DATE_FORMAT(b.datetime,'%Y-%m')";
/*
return [15 => ['cnt' => 159900, 'date' => '2015-12', 'department_id' => 15],
23 => ['cnt' => 157900, 'date' => '2015-12', 'department_id' => 23],
24 => ['cnt' => 1349900, 'date' => '2015-12', 'department_id' => 24]
];*/
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "from", "clause", "params", "group")));
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
if ($this->start < '2015-11') {
$query = $this->db->newStatement("\n SELECT ROUND(SUM(a.profit)) cnt, DATE_FORMAT(a.status_date,'%Y-%m') date, a.creator_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.creator_id IN (:uids:) \n GROUP BY a.creator_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);
$query->setArray('uids', $this->ids);
return $this->PreSetCalculate($query->getAllRecords());
} else {
$selectWrapper = "\n ROUND(profit) cnt\n ";
$select = "\n op.creator_id user_id, DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$clause = "\n op.creator_id IN (:user_id:)\n ";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01'))), 'user_id' => $this->ids];
$group = "op.creator_id, DATE_FORMAT(b.datetime,'%Y-%m')";
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "from", "clause", "params", "group")));
}
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
if ($this->start < '2015-11') {
$query = $this->db->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 (:uids:) \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);
$query->setArray('uids', $this->ids);
return $this->PreSetCalculate($query->getAllRecords());
} else {
$selectWrapper = "\n ROUND(profit*0.1) cnt\n ";
$select = "\n DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$clause = "\n o.city_id=63\n ";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01')))];
$group = "DATE_FORMAT(b.datetime,'%Y-%m')";
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "clause", "params", "group")));
}
}
public function calculate()
{
if (empty($this->ids)) {
return FALSE;
}
if ($this->start < '2015-11') {
$query = $this->db->newStatement("\n SELECT ROUND(SUM(a.profit*0.1)) cnt, DATE_FORMAT(a.status_date,'%Y-%m') date, pe.department_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 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 INNER JOIN pm_employee pe ON pe.user_id=a.manager_id AND pe.department_id IN (:department_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.manager_id IN (2519,4688) OR\n a.manager_id=5239 AND DATE_FORMAT(a.status_date,'%Y-%m')<'2013-02' OR\n a.manager_id IN (3660) AND DATE_FORMAT(a.status_date,'%Y-%m')>='2013-10' OR\n a.manager_id IN (10529) AND DATE_FORMAT(a.status_date,'%Y-%m')>='2014-05'\n )\n GROUP BY pe.department_id, DATE_FORMAT(a.status_date,'%Y-%m')\n ");
$query->setInteger('oid', OWNER_ID);
$query->setVarChar('f', $this->start);
$query->setVarChar('t', $this->end);
$query->setArray('department_id', $this->departments);
return $this->PreSetCalculate($query->getAllRecords());
} else {
$selectWrapper = "\n ROUND(profit*0.1) cnt\n ";
$select = "\n pe.department_id, DATE_FORMAT(b.datetime,'%Y-%m') date\n ";
$from = "\n INNER JOIN pm_employee pe ON pe.user_id=op.manager_id AND pe.department_id IN (:department_id:) AND pe.`start`<=DATE_FORMAT(:till:,'%Y-%m-%d') AND (pe.`end`>=DATE_FORMAT(:since:,'%Y-%m-%d') OR pe.`end` IS NULL)\n ";
$clause = "b.datetime BETWEEN CONCAT(pe.`start`,' 00:00:00') AND CONCAT(IFNULL(pe.`end`,DATE_FORMAT(NOW(),'%Y-%m-%d')),' 23:59:59')";
$params = ['since' => date('Y-m-d 00:00:00', strtotime('first day of this month', strtotime($this->start . '-01'))), 'till' => date('Y-m-d 23:59:59', strtotime('last day of this month', strtotime($this->start . '-01'))), 'department_id' => $this->departments];
$group = "pe.department_id, DATE_FORMAT(b.datetime,'%Y-%m')";
return $this->PreSetCalculate(\Model::admin('Balance')->buildOrderStatistics(compact("selectWrapper", "select", "from", "clause", "params", "group")));
}
}
public function getByAll($date = null, $count_month = 24)
{
if (!$date) {
$date = date($this->format_date);
}
$count_month = $count_month ? $count_month : 24;
$end = $date;
$cdate = new \DateTime($end);
$cdate->sub(new \DateInterval('P' . $count_month . 'M'));
$start = $cdate->format($this->format_date);
$select = "\n DATE_FORMAT(b.datetime,'%Y-%m') month\n ";
$params = ['since' => $start . ' 00:00:00', 'till' => $end . ' 23:59:59'];
$group = "DATE_FORMAT(b.datetime,'%Y-%m-01')";
$all = \Model::admin('Balance')->buildOrderStatistics(compact("select", "params", "group"));
foreach ($all as $sheet) {
$return[$sheet['month']] = $sheet;
}
return $return;
}