/**
* 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]);
}
