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]);
}