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']);
 }
Esempio n. 3
0
 /**
  * @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')*/
?>
Esempio n. 6
0
    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]);
    }