/** * Creates data provider instance with search query applied * * @param array $params * * @return ActiveDataProvider */ public function search($params) { $query = AccountRecord::find(); $dataProvider = new ActiveDataProvider(['query' => $query]); $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->andFilterWhere(['ID' => $this->ID, 'RegisterDate' => $this->RegisterDate, 'Money' => $this->Money, 'PersonID' => $this->PersonID, 'Spend' => $this->Spend, 'Blocked' => $this->Blocked, 'InPawn' => $this->InPawn, 'LevelID' => $this->LevelID, 'Deleted' => $this->Deleted, 'Charged' => $this->Charged]); return $dataProvider; }
/** * @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'); }