/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = PersonsRecord::find();
$dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['RegisterDate' => SORT_DESC]], 'pagination' => ['pagesize' => 50]]);
$query->joinWith(['account' => function ($query) {
$query->from(['account' => 'taccount']);
}, 'card' => function ($query) {
$query->from(['card' => 'tcard']);
}, 'group' => function ($query) {
$query->from(['group' => 'tpersonsgroup']);
}]);
$dataProvider->sort->attributes['account.Money'] = ['asc' => ['account.Money' => SORT_ASC], 'desc' => ['account.Money' => SORT_DESC]];
$dataProvider->sort->attributes['account.LevelID'] = $dataProvider->sort->attributes['accountLevelID'] = ['asc' => ['account.LevelID' => SORT_ASC], 'desc' => ['account.LevelID' => SORT_DESC]];
$dataProvider->sort->attributes['group.Name'] = ['asc' => ['group.Name' => SORT_ASC], 'desc' => ['group.Name' => SORT_DESC]];
$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;
}
$query->from(['person' => 'tpersons']);
$query->andFilterWhere(['person.ID' => $this->ID, 'CardID' => $this->CardID, 'AccountID' => $this->AccountID, 'person.Blocked' => $this->Blocked, 'GroupID' => $this->GroupID, 'person.Deleted' => $this->Deleted, 'ServiceCard' => $this->ServiceCard, 'IsRegularClient' => $this->IsRegularClient, 'RoomID' => $this->RoomID, 'account.LevelID' => $this->accountLevelID]);
if (!empty($this->searchName)) {
if (preg_match('/\\+?\\d+/', $this->searchName)) {
$query->andFilterWhere(['or', ['like', 'Phone1', $this->searchName], ['like', 'Phone2', $this->searchName], ['like', 'Phone3', $this->searchName]]);
} else {
$query->andFilterWhere(['or', ['like', 'FirstName', $this->searchName], ['like', 'LastName', $this->searchName], ['like', 'MidName', $this->searchName]]);
}
} else {
$query->andFilterWhere(['like', 'FirstName', $this->FirstName])->andFilterWhere(['like', 'LastName', $this->LastName])->andFilterWhere(['like', 'MidName', $this->MidName])->andFilterWhere(['like', 'Phone1', $this->Phone1])->andFilterWhere(['like', 'Phone2', $this->Phone2])->andFilterWhere(['like', 'Phone3', $this->Phone3]);
}
if (!empty($this->RegisterDate) && preg_match('/\\s+-\\s+/', $this->RegisterDate)) {
list($from, $to) = preg_split('/\\s+-\\s+/', $this->RegisterDate);
$query->andWhere("person.RegisterDate>='" . Yii::$app->formatter->asDate(trim($from), 'yyyy-MM-dd HH:mm:ss') . "'")->andWhere("person.RegisterDate<='" . Yii::$app->formatter->asDate(trim($to), 'yyyy-MM-dd HH:mm:ss') . "'");
}
$query->andFilterWhere(['like', 'City', $this->City])->andFilterWhere(['like', 'Address', $this->Address])->andFilterWhere(['like', 'Email1', $this->Email1])->andFilterWhere(['like', 'Additional', $this->Additional])->andFilterWhere(['like', 'Login', $this->Login])->andFilterWhere(['like', 'group.Name', $this->getAttribute('group.Name')])->andFilterWhere(['like', 'card.HardID', $this->getAttribute('card.HardID')]);
if ($this->getAttribute('account.Money') != '') {
$value = $this->getAttribute('account.Money');
if (preg_match('/^(?:\\s*(<>|<=|>=|<|>|=)){1}(.*)$/', $value, $matches)) {
$value = $matches[2];
$op = $matches[1];
} else {
$op = '=';
}
$query->andWhere('account.Money' . $op . $value);
}
return $dataProvider;
}
<?php
use yii\bootstrap\ActiveForm;
use yii\helpers\Html;
/* @var $this yii\web\View */
/* @var $model common\models\cf\FinanceSearch */
/* @var $form yii\widgets\ActiveForm */
$formatter = \Yii::$app->formatter;
if (!empty($model->dateFrom)) {
$model->dateFrom = $formatter->asDatetime($model->dateFrom, 'dd.MM.yyyy HH:mm');
$model->dateTo = $formatter->asDatetime($model->dateTo, 'dd.MM.yyyy HH:mm');
}
$persons = \common\models\cf\PersonsRecord::find()->select(['CONCAT(FirstName, " ", LastName)', 'ID'])->where(['GroupID' => \common\models\cf\PersonsGroupRecord::KASHIER])->indexBy('ID')->column();
?>
<div class="finance-record-search">
<?php
$form = ActiveForm::begin(['enableAjaxValidation' => false, 'options' => ['id' => 'finance-record-search']]);
?>
<?php
echo $form->field($model, 'dateRange')->widget(\kartik\daterange\DateRangePicker::className(), ['presetDropdown' => true, 'hideInput' => true, 'pluginOptions' => ['timePicker' => true, 'timePicker24Hour' => true, 'format' => 'DD.MM.YYYY HH:mm', 'timePickerIncrement' => 1, 'opens' => 'right', 'locale' => ['format' => 'DD.MM.YYYY HH:mm']], 'pluginEvents' => ['apply.daterangepicker' => "function(e) {\n\n }"]]);
?>
<?php
echo $form->field($model, 'PersonID')->dropdownList($persons, ['prompt' => 'Все']);
?>
<?php
echo $form->field($model, 'allowCancelled')->checkbox();
?>
/**
* Отмена всех сервисных операций
*/
public function actionServiceFinances()
{
$persons = PersonsRecord::find()->where(['ServiceCard' => 1])->indexBy('ID')->all();
/** @var PersonsRecord[] $persons */
echo "Found " . sizeof($persons) . " service persons. Updating finance records...\n";
$success = 0;
foreach ($persons as $id => $person) {
$success += FinanceRecord::updateAll(['CancelAccountID' => $id], ['ContractorID' => $id]);
}
echo " done updated: " . $success . "\n";
}
/** @inheritdoc */
public function beforeSave($insert)
{
if ($insert) {
// $this->EventTime = new Expression('NOW()');
// $this->CancelTime = new Expression('NOW()');
$this->AccountantID = Yii::$app->user->id;
// $this->RoomID = $session->RoomID;
$person = PersonsRecord::find()->select('ServiceCard')->where(['ID' => $this->ContractorID, 'ServiceCard' => 1])->one();
if (null !== $person && $person->ServiceCard) {
$this->CancelAccountID = $this->ContractorID;
}
}
return parent::beforeSave($insert);
}
/**
* @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');
}
