public function checkGroup($attribute, $params) { foreach ($this->{$attribute} as $gid) { $query = new \yii\db\Query(); $query->select('id')->from(GroupSales::tableName())->where('id = :gid')->addParams([':gid' => $gid]); if (!$query->exists()) { $this->addError($attribute, 'Sales Man did you search is not exist for group id ' . $gid); } } }
/** * @return \yii\db\ActiveQuery */ public function getSaleGroup() { return $this->hasOne(GroupSales::className(), ['id' => 'sale_group_id']); }
/** * @return \yii\db\ActiveQuery */ public function getKelompok() { return $this->hasOne(GroupSales::className(), ['id' => 'kelompok_id']); }
public function actionGetEssGroupDetail($group, $series = null, $year = null) { \Yii::$app->response->format = \yii\web\Response::FORMAT_JSON; $data = []; if (!$year) { $year = date('Y'); } $groupObj = GroupSales::find()->where('name like :search')->addParams([':search' => strtolower($group)])->one(); if ($groupObj) { $dataArr = ExecutiveSummaryGroup::find()->where('year_invoice = :year AND gid = :gid')->addParams([':year' => (int) $year, ':gid' => $groupObj->id])->orderBy('name ASC')->asArray()->all(); $ytdSales = array_map(function ($v) { return ['name' => $v['name'], 'y' => floatval($v['ytd_target'])]; }, $dataArr); $ytdAchievement = array_map(function ($v) { return ['name' => $v['name'], 'y' => floatval($v['ytd_sales_achievement'])]; }, $dataArr); $series = ['name' => 'Ytd Achievement', 'data' => preg_replace('/W+/', '', strtolower($series)) == 'ytdtarget' ? $ytdTarget : $ytdAchievement]; } return \yii\helpers\Json::encode($series); }
<div class="col-md-12"> <?php /*$data = GroupSales::find() ->select(['name as value', 'name as label', 'id as id']) ->asArray() ->all(); echo $form->field($model, 'kelompok_id')->widget(\yii\jui\AutoComplete::classname(), [ 'clientOptions' => [ 'source' => $data, ], ])->textInput()->label('Group')*/ ?> <?php // $data = ArrayHelper::getColumn(GroupSales::find()->select('name')->distinct()->all(),'name'); echo $form->field($model, 'tag_group')->widget(Select2::classname(), ['name' => 'tag_group', 'data' => ArrayHelper::map(GroupSales::find()->select('name')->distinct()->all(), 'name', 'name'), 'options' => ['placeholder' => 'Cari Group...', 'class' => 'form-controler', 'multiple' => true]])->label('Group'); ?> </div> <div class="col-md-12"> <?php /*$data = ResUsers::find() ->select(['login as value', 'login as label', 'id as id']) ->asArray() ->all(); echo $form->field($model, 'user_id')->widget(\yii\jui\AutoComplete::classname(), [ 'clientOptions' => [ 'source' => $data, ], ])->textInput()->label('Group')*/ ?>
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]); }