echo $this->render('_search', ['model' => $searchModel]);
?>
<?php
echo \kartik\grid\GridView::widget(['dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'filterSelector' => '#finance-record-search input, #finance-record-search select', 'columns' => [['visible' => Yii::$app->user->can('/finances/revert-toggle'), 'header' => Yii::t('app', 'Finance row state'), 'value' => function ($model) {
if ($model->CancelAccountID > 0) {
return Html::a('<span class="glyphicon glyphicon-ok-circle"></span> ' . Yii::t('app', 'Apply'), ['revert-toggle', 'id' => $model->id], ['class' => 'btn btn-xs btn-danger btn-block', 'data-method' => 'post', 'data-confirm' => Yii::t('app', 'Are you sure you want to apply this record?')]);
} else {
return Html::a('<span class="glyphicon glyphicon-remove-circle"></span> ' . Yii::t('app', 'Revert'), ['revert-toggle', 'id' => $model->id], ['class' => 'btn btn-xs btn-success btn-block', 'data-method' => 'post']);
}
}, 'format' => 'raw'], ['attribute' => 'EventTime', 'format' => 'datetime', 'group' => true], 'CashSum:currency', ['attribute' => 'ObjectID', 'format' => 'raw', 'value' => function ($model, $key, $index, $column) {
$object = \yii\helpers\ArrayHelper::getValue($model, 'object.Name');
if ($object) {
return Html::a($object . "<br>" . \yii\helpers\ArrayHelper::getValue($model, 'objectFunction.Name'), ['/objects/view', 'id' => $model->object->id]);
}
}], ['attribute' => 'ClassID', 'filter' => \common\models\cf\FinanceClassRecord::find()->select(['Name', 'ID'])->indexBy('ID')->column(), 'value' => function ($model, $key, $index, $column) {
$name = $model->action->Name;
if ($model->ClassID == 22) {
$name = $name . '<br>' . $model->Comment;
}
switch ($model->action->Type) {
case \common\models\cf\FinanceClassRecord::TYPE_DEFAULT:
return $name;
case \common\models\cf\FinanceClassRecord::TYPE_IN:
return '<span class="glyphicon glyphicon-plus"></span> ' . $name;
case \common\models\cf\FinanceClassRecord::TYPE_OUT:
return '<span class="glyphicon glyphicon-minus"></span> ' . $name;
}
}, 'format' => 'raw'], 'Comment:raw', 'ID', ['class' => 'yii\\grid\\ActionColumn', 'template' => '{print}', 'visibleButtons' => ['print' => function ($model, $key, $index) {
/** @var \common\models\cf\FinanceRecord $model */
return $model->action->Type === \common\models\cf\FinanceClassRecord::TYPE_IN;
/**
* Returns Finance action's {Class} name/description class
* @return FinanceClassRecord
*/
public function getClass()
{
return $this->hasOne(FinanceClassRecord::className(), ['ID' => 'ClassID']);
}
/**
* Fix 19 finance class for correct card change
*/
public function actionFinanceclasses()
{
FinanceClassRecord::findOne(19)->updateAttributes(['Type' => 1]);
}
/**
* @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');
}