/** * Creates data provider instance with search query applied * * @param array $params * * @return ActiveDataProvider */ public function search($params) { $query = FinanceRecord::find(); $dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['EventTime' => SORT_DESC, 'ID' => SORT_DESC], 'attributes' => ['EventTime' => ['asc' => ['EventTime' => SORT_ASC, 'ID' => SORT_ASC], 'desc' => ['EventTime' => SORT_DESC, 'ID' => SORT_DESC]], 'ID', 'ClassID', 'CashSum', 'ObjectID', 'CancelTime', 'Comment']], 'pagination' => ['pageSize' => 50]]); $this->load($params); if (!$this->validate()) { // uncomment the following line if you do not want to return any records when validation fails // $query->where('0=1'); return $dataProvider; } if (!empty($this->dateRange)) { list($this->dateFrom, $this->dateTo) = preg_split('/\\s+-\\s+/', $this->dateRange); $formatter = Yii::$app->formatter; $this->dateFrom = $formatter->asDatetime($this->dateFrom, 'yyyy-MM-dd HH:mm:ss'); $this->dateTo = $formatter->asDatetime($this->dateTo, 'yyyy-MM-dd HH:mm:ss'); $query->andWhere(['and', ['>=', 'EventTime', $this->dateFrom], ['<=', 'EventTime', $this->dateTo]]); } $query->andFilterWhere(['ID' => $this->ID, 'ClassID' => $this->ClassID, 'AccountantID' => $this->AccountantID, 'ContractorID' => $this->ContractorID, 'ObjectID' => $this->ObjectID, 'FunctionID' => $this->FunctionID, 'CancelAccountID' => $this->CancelAccountID, 'CancelTime' => $this->CancelTime, 'Coins' => $this->Coins, 'Safe' => $this->Safe, 'RoomID' => $this->RoomID]); $query->andFilterWhere(['like', 'Comment', $this->Comment]); if (!empty($this->CashSum)) { if (preg_match('/^(?:\\s*(<>|<=|>=|<|>|=)){1}(.*)$/', $this->CashSum, $matches)) { $value = $matches[2]; $op = $matches[1]; } else { $value = $this->CashSum; $op = '='; } if (!empty($value)) { $query->andWhere('`CashSum`' . $op . $value); } } return $dataProvider; }
public function actionIndex() { $formatter = Yii::$app->formatter; $query = FinanceRecord::find()->today()->exceptCancelled(); $data = $query->asArray()->orderBy(['ID' => SORT_ASC, 'EventTime' => SORT_ASC])->all(); $usersPerHour = []; if (count($data)) { /** @var FinanceRecord $finance */ $usersPerHourData = ArrayHelper::map($data, function ($finance) use($formatter) { return $finance['ID']; }, function ($finance) { return $finance; }, function ($finance) use($formatter) { return $formatter->asDate($finance['EventTime'], 'php:H:00'); }); if (count($usersPerHourData) > 0) { $usersPerHour = ['labels' => array_keys($usersPerHourData), 'datasets' => [['label' => 'Количество уникальных посетителей в час', 'fillColor' => "rgba(250,100,0,0.8)", 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#0f0", 'data' => []]]]; foreach ($usersPerHourData as $day => $hFin) { $objectUses = []; foreach ($hFin as $id => $f) { if ($f['ClassID'] == 1) { $objectUses[] = $f['ContractorID']; } } $usersPerHour['datasets'][0]['data'][] = count(array_unique($objectUses)); unset($objectUses); } } } $dataProvider = (new ActionsSearch(['attributes' => ['deleted' => 0]]))->search(Yii::$app->request->queryParams); return $this->render('index', ['usersPerHour' => $usersPerHour, 'dataProvider' => $dataProvider, 'cashBoxCash' => \common\models\cf\FinanceRecord::todayStats()]); }
/** * @param $from * @param $to * @param $personId * @param $allowCancelled * @return string * @throws \yii\base\InvalidConfigException */ public function actionGenerate($from, $to, $personId = null, $allowCancelled = false) { $formatter = Yii::$app->formatter; /** @var ReportDataModel $data */ $data = Yii::createObject(ReportDataModel::className()); $data->fromDate = $from; $data->toDate = $to; //приход $_income = 0; //расход $_expense = 0; $data->orderCount = 0; $charges = []; $chargesWithRegistration = []; $ordersCash = []; /** @var array $objects */ $objects = ObjectRecord::find()->indexBy('ID')->orderBy('Name')->asArray()->all(); foreach ($objects as $oID => $obj) { $objects[$oID]['cash'] = 0; } $objectsStat = $objects; $objectsByDay = []; $data->actions = FinanceClassRecord::find()->indexBy('ID')->orderBy('Type')->where(['Deleted' => '0'])->asArray()->all(); foreach ($data->actions as $aID => $act) { $data->actions[$aID]['count'] = 0; $data->actions[$aID]['cash'] = 0; } $query = FinanceRecord::find()->with('action')->leftJoin('tpersons', 'tfinance.ContractorID=tpersons.ID')->andWhere(['tpersons.Deleted' => 0])->andWhere(['tpersons.ServiceCard' => 0])->andWhere(['>=', 'EventTime', $from])->andWhere(['<=', 'EventTime', $to])->orderBy('EventTime'); //Включить отмененные if (!$allowCancelled) { $query->andFilterWhere(['CancelAccountID' => 0]); } $query->asArray(); foreach ($query->each() as $row) { $isAccountMatched = $personId != null && $personId == $row['AccountantID']; $EventTime = $row['EventTime']; $EventDay = $formatter->asDate($EventTime, 'php:D d-m-Y'); //Отменено ? $cancelled = $row['CancelAccountID'] != 0; //if($cancelled && !$allowCancelled) continue; $type = $row['action']['Type']; $action = $row['action']; $cash = $row['CashSum']; if (!isset($data->actions[$action['ID']])) { $data->actions[$action['ID']] = $action; $data->actions[$action['ID']]['count'] = 0; $data->actions[$action['ID']]['cash'] = 0; } $data->actions[$action['ID']]['count']++; $data->actions[$action['ID']]['cash'] += $cash; if ($type == FinanceClassRecord::TYPE_IN) { if ($personId != null) { if ($isAccountMatched) { $_income += $cash; if ($row['ClassID'] != 14 && $row['ClassID'] != 2) { if ($cash > 0) { $data->orderCount++; $ordersCash[] = $cash; } } } } else { $_income += $cash; if ($row['ClassID'] != 14 && $row['ClassID'] != 2) { if ($cash > 0) { $data->orderCount++; $ordersCash[] = $cash; } } } //Регистрация карты } else { if ($type == FinanceClassRecord::TYPE_OUT) { if ($personId != null) { if ($isAccountMatched) { $_expense += $cash; } } else { $_expense += $cash; } } else { } } switch ($row['ClassID']) { //Использование объектов case 1: //total money $data->totalSpend += $cash; $data->totalSpendWithCard += $cash; if (!isset($objects[$row['ObjectID']])) { throw new Exception('объект не найден: №' . $row['ObjectID']); } $object = $objects[$row['ObjectID']]; //Total objects stat // if(!isset($objectsStat[$object['ID']]['cash'])) { // $objectsStat[$object['ID']]['cash'] = 0; // } $objectsStat[$object['ID']]['cash'] += $cash; //Objects stat by days if (!isset($objectsByDay[$EventDay])) { $objectsByDay[$EventDay] = $objects; } // if(!isset($objectsByDay[$EventDay][$object['ID']])) { // $objectsByDay[$EventDay][$object['ID']] = $object; // $objectsByDay[$EventDay][$object['ID']]['cash'] = 0; // $objectsByDay[$EventDay][$object['ID']]['EventTime'] = $EventTime; // } $objectsByDay[$EventDay][$object['ID']]['cash'] += $cash; break; //Покупка услуги|акции //Покупка услуги|акции case 22: if ($personId != null) { if ($isAccountMatched) { $data->soldService[$row['ID']] = $row; $data->soldService[$row['ID']]['cash'] = $cash; $data->totalSpend += $cash; } } else { $data->soldService[$row['ID']] = $row; $data->soldService[$row['ID']]['cash'] = $cash; $data->totalSpend += $cash; } break; //Регистрация карты //Регистрация карты case 2: if ($personId != null) { if ($isAccountMatched) { $data->totalIncomeFromCardRegistration += $cash; } } else { $data->totalIncomeFromCardRegistration += $cash; } break; //Залог //Залог case 14: //идет в доход от регистрации if ($personId != null) { if ($isAccountMatched) { $data->totalIncomeFromCardRegistration += $cash; } } else { $data->totalIncomeFromCardRegistration += $cash; } break; //Пополнение простое //Пополнение простое case 6: if ($personId != null) { if ($isAccountMatched) { $data->chargeCount++; $data->totalCharge += $cash; $charges[] = $cash; $data->currentCardDebt += $cash; } } else { $data->chargeCount++; $data->totalCharge += $cash; $charges[] = $cash; $data->currentCardDebt += $cash; } break; //Пополнение при регистрации //Пополнение при регистрации case 12: if ($personId != null) { if ($isAccountMatched) { $data->cardRegistrationCount++; //в доход от регистрации $data->totalIncomeFromCardRegistration += $cash; //либо в пополнения //$data->totalCharge += $cash; $chargesWithRegistration[] = $cash; $data->currentCardDebt += $cash; } } else { $data->cardRegistrationCount++; //в доход от регистрации $data->totalIncomeFromCardRegistration += $cash; //либо в пополнения //$data->totalCharge += $cash; $chargesWithRegistration[] = $cash; $data->currentCardDebt += $cash; } break; //Пополнение при регистрации подарочной карты //Пополнение при регистрации подарочной карты case 15: if ($personId != null) { if ($isAccountMatched) { $data->cardGiftRegistrationCount++; $data->totalIncomeFromGiftCardRegistration += $cash; $chargesWithRegistration[] = $cash; } } else { $data->cardGiftRegistrationCount++; $data->totalIncomeFromGiftCardRegistration += $cash; $chargesWithRegistration[] = $cash; } break; //Снятие денег с карты //Снятие денег с карты case 7: if ($personId != null) { if ($isAccountMatched) { $data->totalMoneyBackFromCard += $cash; } } else { $data->totalMoneyBackFromCard += $cash; } break; //Билеты на карту //Билеты на карту case 3: $data->currentTicketsEmitted += $row['Coins']; break; //Обмен билетов на товар //Обмен билетов на товар case 4: $data->currentTicketsSpend += $row['Coins']; break; //Возврат залога //Возврат залога case 13: //возвращается на счет поэтому не имеет фин значения (type = 0) break; //Акционное попоплнение //Акционное попоплнение case 18: if ($personId != null) { if ($isAccountMatched) { $data->currentBonusesEmitted += $cash; } } else { $data->currentBonusesEmitted += $cash; } break; //плата за замену карты //плата за замену карты case 19: break; //оплата бонусами //оплата бонусами case 20: $data->currentBonusesSpend += $cash; break; //начисление бонусов //начисление бонусов case 21: $data->currentBonusesEmitted += $cash; break; } } $data->objects = $objectsStat; $data->objectsByDays = $objectsByDay; $data->sumIncome = $_income; $data->sumExpense = $_expense; $data->income = $data->sumIncome - $data->sumExpense; if (count($charges) > 0) { $data->averageChargeAmount = array_sum($charges) / count($charges); } if (count($chargesWithRegistration) > 0) { $data->averageRegistrationChargeAmount = array_sum($chargesWithRegistration) / count($chargesWithRegistration); } if (count($ordersCash) > 0) { $ordersCash = array_filter($ordersCash); $data->orderCount = count($ordersCash); $data->orderAvg = array_sum($ordersCash) / count($ordersCash); $data->orderMax = max($ordersCash); $data->orderMin = min($ordersCash); } /* $data->currentCardDebtPlusBonus = AccountRecord::find() ->select('Money') ->leftJoin('tpersons', 'taccount.PersonID=tpersons.ID') ->where(['tpersons.ServiceCard'=>0]) ->andWhere(['>=', 'taccount.RegisterDate', $from]) ->andWhere(['<=', 'taccount.RegisterDate', $to]) ->andWhere(['taccount.Deleted'=>0]) ->sum('Money'); */ //Текущий дебет на карточках за выбранный период // $data->currentCardDebt = FinanceRecord::find() // ->andWhere(['CancelAccountID'=>0]) // ->andWhere(['>=', 'EventTime', $from]) // ->andWhere(['<=', 'EventTime', $to]) // ->andWhere(['in', 'ClassID', [6, 12]]) // ->sum('CashSum'); //Дебет без бонусов $data->currentCardDebt = $data->currentCardDebt - $data->totalSpend; $data->currentCardDebtPlusBonus = $data->currentCardDebt + $data->currentBonusesEmitted; //Всего начислено бонусов //по журналу $data->totalBonusesEmitted = FinanceRecord::find()->leftJoin('tpersons', 'tfinance.ContractorID=tpersons.ID')->andWhere(['<=', 'tpersons.RegisterDate', $to])->andWhere(['in', 'tfinance.ClassID', [18, 21]])->andWhere(['tfinance.CancelAccountID' => 0])->andWhere(['tpersons.Deleted' => 0, 'tpersons.ServiceCard' => 0])->sum('tfinance.CashSum'); //фактический бонусный дебет на аккаунтах $data->totalCardBonusDebt = AccountRecord::find()->leftJoin(PersonsRecord::tableName() . ' person', 'person.ID=taccount.PersonID')->andWhere(['<=', 'taccount.RegisterDate', $to])->select('taccount.Bonuses')->andWhere(['person.ServiceCard' => 0])->andWhere(['taccount.Deleted' => 0])->andWhere(['person.Deleted' => 0])->sum('taccount.Bonuses'); //Фактически денег на счетах $data->totalCardDebt = AccountRecord::find()->leftJoin(PersonsRecord::tableName() . ' person', 'person.ID=taccount.PersonID')->andWhere(['<=', 'taccount.RegisterDate', $to])->select('taccount.Money')->andWhere(['person.ServiceCard' => 0])->andWhere(['taccount.Deleted' => 0])->sum('taccount.Money'); //Общий дебет на карточках с бонусами $data->totalCardDebtPlusBonus = $data->totalCardDebt + $data->totalCardBonusDebt; //Билетов на картах $data->totalTickets = AccountRecord::find()->andWhere(['<=', 'RegisterDate', $to])->andWhere(['Deleted' => 0])->sum('Tickets'); //Количество зарег сервисных карт $data->countServiceCardWasCreated = PersonsRecord::find()->andWhere(['>=', 'RegisterDate', $from])->andWhere(['<', 'RegisterDate', $to])->andWhere(['Deleted' => 0])->andWhere(['ServiceCard' => 1])->count(); $file = $this->_create($data, PersonsRecord::findOne($personId)); Yii::$app->response->sendFile($file, 'Report.' . $from . '-' . $to . '.xlsx'); }
/** * Общая статистика * @return string * @throws \yii\base\InvalidConfigException */ public function actionIndex() { $formatter = Yii::$app->formatter; $query = FinanceRecord::find()->with('action', 'contractor.account.level')->andFilterWhere(['CancelAccountID' => 0])->orderBy(['EventTime' => SORT_ASC]); /** @var StatisticForm $model */ $model = Yii::createObject(StatisticForm::className()); if ($model->load(Yii::$app->request->post()) && $model->validate()) { $query->andWhere(['>=', 'EventTime', $model->dateFrom])->andWhere(['<=', 'EventTime', $model->dateTo]); } else { $model->dateFrom = $model->formatDateTime(time() - 3600 * 24 * 7); //$model->dateTo = $model->formatDateTime(time()); $query->andWhere(['>=', 'EventTime', $model->dateFrom]); //->andWhere(['<=', 'EventTime', $model->dateTo]); } $data = $query->asArray()->all(); /** @var FinanceRecord $finance */ $usersPerDayData = ArrayHelper::map($data, function ($finance) use($formatter) { return $finance['ID']; }, function ($finance) { return $finance; }, function ($finance) use($formatter) { return $formatter->asDate($finance['EventTime'], 'php:l (d-M-Y)'); }); $usersPerHourData = ArrayHelper::map($data, function ($finance) use($formatter) { return $finance['ID']; }, function ($finance) { return $finance; }, function ($finance) use($formatter) { return $formatter->asDate($finance['EventTime'], 'php:H:00 (d-M-Y)'); }); if (count($usersPerHourData) > 0) { $usersPerHour = ['labels' => array_keys($usersPerHourData), 'datasets' => [['label' => 'Количество уникальных посетителей в час', 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#0f0", 'borderWidth' => 0, 'data' => []]]]; $incomePerHour = ['labels' => array_keys($usersPerHourData), 'datasets' => [['label' => 'Доход в час', 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#0f0", 'borderWidth' => 0, 'data' => []]]]; foreach ($usersPerHourData as $day => $hFin) { $objectUses = []; $incomsPerHour = 0; foreach ($hFin as $id => $f) { if ($f['ClassID'] == 1) { $objectUses[] = $f['ContractorID']; } $type = $f['action']['Type']; if ($type == FinanceClassRecord::TYPE_IN) { $incomsPerHour += $f['CashSum']; } else { if ($type == FinanceClassRecord::TYPE_OUT) { $incomsPerHour = $incomsPerHour - $f['CashSum']; } } } $usersPerHour['datasets'][0]['data'][] = count(array_unique($objectUses)); unset($objectUses); $incomePerHour['datasets'][0]['data'][] = $incomsPerHour; } } else { $usersPerHour = []; $incomePerHour = []; } if (count($usersPerDayData) > 0) { $dayLabels = array_keys($usersPerDayData); //Columns define $usersPerDay = ['labels' => $dayLabels, 'datasets' => [['label' => 'Количество уникальных посетителей по дням недели', 'fillColor' => "rgba(0,152,250,0.8)", 'strokeColor' => "rgba(220,220,220,1)", 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#fff", 'data' => []]]]; $incomePerDay = ['labels' => $dayLabels, 'datasets' => [['label' => 'Доход по дням недели', 'fillColor' => "rgba(0,250,120,0.8)", 'strokeColor' => "rgba(220,220,220,1)", 'pointColor' => "rgba(220,220,220,1)", 'pointStrokeColor' => "#fff", 'data' => []]]]; $usersByLevel = []; $levels = []; foreach ($usersPerDayData as $day => $fin) { //Использование объектов // и //Общий доход $incomsPerDay = 0; $objectUses = []; foreach ($fin as $id => $f) { $type = $f['action']['Type']; if ($type == FinanceClassRecord::TYPE_IN) { $incomsPerDay += $f['CashSum']; } else { if ($type == FinanceClassRecord::TYPE_OUT) { $incomsPerDay = $incomsPerDay - $f['CashSum']; } } if ($f['ClassID'] == 1) { $objectUses[] = $f['ContractorID']; } if (!isset($levels[$f['contractor']['account']['level']['Name']])) { $levels[$f['contractor']['account']['level']['Name']] = []; } $levels[$f['contractor']['account']['level']['Name']][] = $f['ContractorID']; } $usersPerDay['datasets'][0]['data'][] = count(array_unique($objectUses)); unset($objectUses); $incomePerDay['datasets'][0]['data'][] = $incomsPerDay; } $levelSectorsColours = [['color' => "#FDB45C", 'highlight' => "#FFC870"], ['color' => "#F7464A", 'highlight' => "#FF5A5E"], ['color' => "#CCC", 'highlight' => "#46BFBD"]]; $usersByLevel['labels'] = array_keys($levels); $usersByLevel['datasets'] = []; foreach ($levels as $levelName => $userIds) { $colors = array_pop($levelSectorsColours); $usersByLevel['datasets'][0]['data'][] = count(array_unique($userIds)); $usersByLevel['datasets'][0]['backgroundColor'][] = $colors['color']; $usersByLevel['datasets'][0]['hoverBackgroundColor'][] = $colors['highlight']; } } else { $usersPerHour = []; $usersPerDay = []; $incomePerDay = []; $usersByLevel = []; } return $this->render('index', ['model' => $model, 'usersPerHour' => $usersPerHour, 'usersPerDay' => $usersPerDay, 'incomePerDay' => $incomePerDay, 'incomePerHour' => $incomePerHour, 'usersByLevel' => $usersByLevel]); }