public function actionDashboard() { $title['between'] = ""; $connection = \Yii::$app->db; $model = new OrderInvoiceReportForm(); $saleGroup = ResGroups::findOne(['name' => 'All Sales User']); $saleUsers = ArrayHelper::map($saleGroup->users, 'id', 'name'); $aiSearch = new AccountInvoiceSearch(); $aiSearch->start_date = '2014-07-01'; #DEFAULT START DATE FROM JULLY 2014 CAUSE ERP START LIVE IN JULY 2014 $aiSearch->end_date = date('Y-m-d'); $submited = false; $sales_ids = []; #sales ids if empty then show all sales man data if ($model->load(Yii::$app->request->get())) { $model->sales = Yii::$app->request->get('sales'); $submited = true; $aiSearch->start_date = $model->date_from; $aiSearch->end_date = $model->date_to; $getSalesUsers = []; // if($model->validate()){ // Sales ids $getSalesUsers = Yii::$app->request->get('sales'); // }else{ // Yii::$app->session->setFlash('danger','SalesMan is not valid'); $salesError = true; // } // check if has sear for group // FIND BY GROUP $group_ids = []; #sale group ids if ($getSalesUsers) { // echo 'AAAAAAAAA'; foreach ($getSalesUsers as $searchFor) { if (preg_match('/group\\:/', $searchFor)) { // search for group $expl = explode(':', $searchFor); $groupQ = GroupSales::find()->where(['is_main_group' => true, 'name' => $expl[1]]); // var_dump($groupQ->createCommand()->sql); $group = $groupQ->one(); /*foreach($group->groupSalesLines as $gLine): $sales_ids[]=$gLine->name; endforeach;*/ $group_ids[] = $group->id; } else { $sales_ids[] = $searchFor; } } } $aiSearch->group_ids = $group_ids; $aiSearch->sales_ids = $sales_ids; } // var_dump($aiSearch->group_ids); $ai = $aiSearch->getSum(); #result from query->all() // \yii\helpers\VarDumper::dump($aiPie); $resGrid['dataProvider'] = new \yii\data\ArrayDataProvider(['allModels' => $ai, 'pagination' => ['pageSize' => 100]]); $aiPie = $aiSearch->getSumGroup(); /*var_dump($ai); die();*/ if (!$ai) { throw new NotFoundHttpException('Data not found.'); } $fields = array_keys($ai[0]); $uidF = array_search('user_id', $fields); # SEARCH USER ID INDEX unset($fields[$uidF]); # UNSET FIELD WITH SEARCHED INDEX KEY REMOVE USER_ID $fields = array_values($fields); # RE GENERATE ARRAY KEY $totalSummaryFields = count($fields) - 1; # INIT GRID COLUMNS FORMAT foreach ($fields as $fieldName) { $summary = false; $format = 'html'; $header = ucwords(str_replace('_', ' ', $fieldName)); if (preg_match('/summary_/', $fieldName)) { $summary = true; $format = 'currency'; $expl = explode('_', $fieldName); $monthName = \DateTime::createFromFormat('m', $expl[2]); $header = $expl[1] . '-' . $monthName->format('F'); $resGrid['columns'][] = ['attribute' => $fieldName, 'header' => $header, 'format' => $format, 'pageSummary' => $summary]; } else { $resGrid['columns'][] = ['attribute' => $fieldName, 'header' => $header, 'format' => $format, 'pageSummary' => $summary, 'value' => function ($model, $key, $index, $grid) use($fieldName, $aiSearch) { return \yii\helpers\Html::a($model[$fieldName], ['account-invoice/index', 'uid' => $model['user_id'], 'type' => 'out', 'start_date' => $aiSearch->start_date, 'end_date' => $aiSearch->end_date]); }]; } } $resGrid['columns'][] = ['class' => '\\kartik\\grid\\FormulaColumn', 'format' => ['currency'], 'header' => 'Subtotal', 'pageSummary' => true, 'value' => function ($model, $key, $index, $widget) use($totalSummaryFields) { $p = compact('model', 'key', 'index'); $res = 0; for ($c = 1; $c <= $totalSummaryFields; $c++) { $res += $widget->col($c, $p); } return $res; }]; // ACTION COLUMN /*$resGrid['columns'][]=[ 'class'=>'\yii\grid\ActionColumn', 'template'=>'{view}', 'buttons'=>[ 'view'=>function($url,$model,$key){ return 'Viewsss'; } ] ];*/ $pie = []; $y = []; foreach ($aiPie as $idx => $inv) { foreach ($fields as $fieldName) { if ($fieldName != 'sales_name') { if (isset($y[$idx])) { $y[$idx] += $inv[$fieldName]; } else { if ($fieldName != 'group_name') { $y[$idx] = $inv[$fieldName]; } } } } $pie['series'][] = ['name' => $inv['sales_name'], 'y' => $y[$idx]]; } $title['between'] = ' Between ' . \DateTime::createFromFormat('Y-m-d', $aiSearch->start_date)->format('d-F-Y') . ' and ' . \DateTime::createFromFormat('Y-m-d', $aiSearch->end_date)->format('d-F-Y'); return $this->render('order_invoice_dashboard', ['title' => $title, 'model' => $model, 'saleUsers' => $saleUsers, 'resGrid' => $resGrid, 'pie' => $pie]); }
public function actionSalesAchievement() { $connection = \Yii::$app->db; $model = new SaleAnnualReportForm(); $saleGroup = ResGroups::findOne(['name' => 'All Sales User']); $saleUsers = ArrayHelper::map($saleGroup->users, 'id', 'name'); $allOrderTitle = "Globally Orders Received"; $dateQuery = "so.date_order > '2014-07-01'"; $submited = false; if ($model->load(Yii::$app->request->get())) { $submited = true; if ($model->date_from == $model->date_to) { $dateQuery = "so.date_order = '{$model->date_from}'"; } else { $dateQuery = "so.date_order BETWEEN '{$model->date_from}' AND '{$model->date_to}'"; } } $queryAllOrder = <<<EOQ SELECT \tCAST(EXTRACT(YEAR FROM "date_order") AS INTEGER) AS period_year, \tCAST(EXTRACT(MONTH FROM "date_order") AS INTEGER) AS period_month, \tCONCAT(TO_CHAR(TO_TIMESTAMP (CAST(EXTRACT(MONTH FROM "date_order") AS TEXT), 'MM'), 'TMmon'), '-',CAST(EXTRACT(YEAR FROM "date_order") AS TEXT)) as month_name, \tSUM( CASE WHEN week=1 THEN so_rates.rates ELSE 0 END) AS subtotal_week_1, \tSUM( CASE WHEN week=2 THEN so_rates.rates ELSE 0 END) AS subtotal_week_2, \tSUM( CASE WHEN week=3 THEN so_rates.rates ELSE 0 END) AS subtotal_week_3, \tSUM( CASE WHEN week=4 THEN so_rates.rates ELSE 0 END) AS subtotal_week_4, \tSUM( CASE WHEN week=5 THEN so_rates.rates ELSE 0 END) AS subtotal_week_5, \tSUM(so_rates.rates) AS subtotal \tFROM( \tselect \t\tso.*, \t\t(case when rcr.rating is null then( \t\t\t( \t\t\t\tcase when \t\t\t\t\t(case when rcr.rating is null and rc.id=13 then 1 else case when rcr.rating is null then 0 end end) = 0 \t\t\t\tthen \t\t\t\t\t(select rating from res_currency_rate where currency_id=rc.id and name < so.date_order order by name desc limit 1) * amount_total \t\t\t\t\t \t\t\t\telse \t\t\t\t\t(1*amount_total) \t\t\t\tend \t\t\t) \t\t) \t\telse \t\t\t(rcr.rating*amount_total) \t\tend) as rates \tfrom \t\tsale_order as so \tjoin product_pricelist as ppr on so.pricelist_id = ppr.id \tjoin res_currency as rc on ppr.currency_id=rc.id \tleft outer join res_currency_rate as rcr on rcr.currency_id=rc.id and rcr.name = so.date_order \twhere \t\t{$dateQuery} \t\tand \t\tso.state not in ('draft','cancel') \torder by so.date_order asc) AS so_rates GROUP BY period_year, period_month, month_name ORDER BY period_year ASC, period_month ASC EOQ; // echo '<pre>'.$queryAllOrder.'</pre>'; $commandAllOrders = $connection->createCommand($queryAllOrder); $resultAllOrders = $commandAllOrders->queryAll(); $allOrderDataProvider = new \yii\data\ArrayDataProvider(['allModels' => $resultAllOrders, 'pagination' => ['pageSize' => 80], 'sort' => ['attributes' => [['name' => 'month_name'], 'subtotal']]]); $xCategories = []; $series = [['name' => 'All Sales']]; $seriesIdx = 0; // use for indexing period variant // using in rendering sales man grid search result $xCatIndex = []; foreach ($resultAllOrders as $row => $monthlyOrder) { $xCategories[] = $monthlyOrder['month_name']; $xCatIndex[$monthlyOrder['period_year'] . '_' . $monthlyOrder['period_month']] = $row; $series[$seriesIdx]['data'][] = (double) $monthlyOrder['subtotal']; } /*var_dump($xCatIndex); die();*/ $seriesIdx++; // IF SEARCH FORM SUBMITTED if ($submited) { $getSalesUsers = Yii::$app->request->get('sales'); // check if has sear for group $sales_ids = []; #sales ids $group_ids = []; #sale group ids if ($getSalesUsers) { if (!is_array($getSalesUsers)) { $dec = urldecode($getSalesUsers); $getSalesUsers = explode(',', $dec); } foreach ($getSalesUsers as $searchFor) { if (preg_match('/group\\:/', $searchFor)) { // search for group $expl = explode(':', $searchFor); $group = GroupSales::find()->where(['name' => $expl[1]])->one(); foreach ($group->groupSalesLines as $gLine) { $sales_ids[] = $gLine->name; } $group_ids[] = $group->id; } else { $sales_ids[] = $searchFor; } } } // var_dump($sales_ids); // die(); if ($model->date_from == $model->date_to) { $dateQuery = "so.date_order = '{$model->date_from}'"; $allOrderTitle .= "On " . Yii::$app->formatter->asDate($model->date_from); } else { $dateQuery = "so.date_order BETWEEN '{$model->date_from}' AND '{$model->date_to}'"; $allOrderTitle .= " Between " . Yii::$app->formatter->asDate($model->date_from) . " to " . Yii::$app->formatter->asDate($model->date_to); } // GET RESULT MONTHLY ORDER RECEIVED FOR SALES $salesMonthlyOrderReceive = $this->getMonthlyOrderReceive($sales_ids, $model->date_from, $model->date_to); // FOR DATA PROVIDER $salesManSearchGrid['dataProvider'] = new \yii\data\ArrayDataProvider(['allModels' => $salesMonthlyOrderReceive, 'pagination' => ['pageSize' => 100]]); $groups = ArrayHelper::map(GroupSales::find()->select('id,desc')->where(['is_main_group' => true])->asArray()->all(), 'id', 'desc'); $salesManSearchGrid['columns'] = [['attribute' => 'sales_name', 'header' => 'User(s)', 'format' => 'html', 'value' => function ($data) use($model) { // return var_dump($data); return \yii\helpers\Html::a($data['sales_name'], ['tree', 'where' => \yii\helpers\Json::encode(['date_order' => [Yii::$app->formatter->asDate($model->date_from, 'php:Y-m-d H:i:s'), Yii::$app->formatter->asDate($model->date_to, 'php:Y-m-d H:i:s')], 'user_id' => [$data['user_id']]])]); }], ['attribute' => 'group_id', 'header' => 'Group', 'format' => 'html', 'value' => function ($data, $key, $col, $grid) use($groups, $model) { return \yii\helpers\Html::a($groups[$data['group_id']], ['tree', 'where' => \yii\helpers\Json::encode(['date_order' => [Yii::$app->formatter->asDate($model->date_from, 'php:Y-m-d H:i:s'), Yii::$app->formatter->asDate($model->date_to, 'php:Y-m-d H:i:s')], 'group_id' => [$data['group_id']]])]); }]]; $countCurrColumn = 0; // EACH SALESMAN foreach ($salesMonthlyOrderReceive as $row => $saleMonthly) { $series[$seriesIdx] = ['type' => 'line', 'name' => $saleMonthly['sales_name'], 'data' => []]; // var_dump($saleMonthly); $countCurrColumn = 0; $total[$row]['value'] = 0; foreach ($saleMonthly as $fieldName => $fieldValue) { switch ($fieldName) { case 'user_id': # do nothing # dont render break; case 'sales_name': # dont render break; case 'group_id': # dont render break; default: # code... # add to series # # $periodIdx = str_replace('subtotal_', '', $fieldName); if (isset($xCatIndex[$periodIdx])) { $total[$row]['value'] += $fieldValue; $series[$seriesIdx]['data'][] = (double) $fieldValue; #add to salesMan Grid $getIdx = $xCatIndex[$periodIdx]; #get ex: 2014_1 means period on 2014 on january $headerName = ""; $explodeName = explode('_', $periodIdx); $headerName = Yii::$app->formatter->asDate($explodeName[0] . '-' . $explodeName[1] . '-01', 'MMM-yyyy'); if ($row == 0) { $salesManSearchGrid['columns'][] = ['attribute' => $fieldName, 'header' => $headerName, 'format' => ['currency'], 'pageSummary' => true]; } $pieSeries[$seriesIdx] = ['name' => $saleMonthly['sales_name'], 'y' => $total[$row]['value']]; } else { // do nothing } $countCurrColumn++; break; } } $seriesIdx++; } $salesManSearchGrid['columns'][] = ['class' => '\\kartik\\grid\\FormulaColumn', 'format' => ['currency'], 'header' => 'Subtotal', 'pageSummary' => true, 'value' => function ($model, $key, $index, $widget) use($countCurrColumn) { $p = compact('model', 'key', 'index'); $res = 0; for ($c = 1; $c <= $countCurrColumn; $c++) { $res += $widget->col($c, $p); } return $res; }]; } return $this->render('achievement', ['model' => $model, 'saleUsers' => $saleUsers, 'chart' => ['xCategories' => $xCategories, 'series' => $series], 'allOrderDataProvider' => $allOrderDataProvider, 'allOrderTitle' => $allOrderTitle, 'submited' => $submited, 'salesManSearchGrid' => isset($salesManSearchGrid) ? $salesManSearchGrid : null, 'pieSeries' => isset($pieSeries) ? array_values($pieSeries) : null]); }