Ejemplo n.º 1
0
    public function actionFirstAndLastScan($site = 1792, $year = null, $month = null, $department = null)
    {
        if (!$year) {
            $year = date('Y');
        }
        if (!$month) {
            $month = date('n');
        }
        $first = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
        $last = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
        $dataToRender = [];
        $where = ['employee' => '%%', 'department' => '%%', 'year' => "%%", 'month' => '%%', 'day' => '%%'];
        $dataToRender['attendanceLogForm'] = new \app\models\AttendanceLogForm();
        if ($dataToRender['attendanceLogForm']->load(Yii::$app->request->post())) {
            $where['employee'] = $dataToRender['attendanceLogForm']['employee'];
            $where['department'] = $dataToRender['attendanceLogForm']['department'];
            $where['year'] = $dataToRender['attendanceLogForm']['year'];
            $where['month'] = $dataToRender['attendanceLogForm']['month'];
            $where['day'] = $dataToRender['attendanceLogForm']['day'];
        }
        if ($department == 'All Department' || $department == '') {
            $department = 'All Department';
            $department_query = '%%';
        } else {
            $department_query = $department;
        }
        $query = <<<query
SELECT 
    date_series.i
    , r_p.name work_site
    , h_emp.name_related employee
    --, att_log_min_max_pure.employee_id
    , (CASE WHEN att_log_min_max_pure.y IS NULL THEN date_series.year_series ELSE att_log_min_max_pure.y END) "year"
    , (CASE WHEN att_log_min_max_pure.m IS NULL THEN date_series.month_series ELSE att_log_min_max_pure.m END) "month"
    , (CASE WHEN att_log_min_max_pure.d IS NULL THEN date_series.day_series ELSE att_log_min_max_pure.d END) "day"
    , att_log_min_max_pure.min_hour AS "hour_1"
    , att_log_min_max_pure.min_minutes AS "minute_1"
    , (
        CASE 
            WHEN 
                att_log_min_max_pure.min_date_time_log = att_log_min_max_pure.max_date_time_log
            THEN
                NULL
            ELSE
                att_log_min_max_pure.max_hour
        END
        
    ) AS "hour_2"
    , (
        CASE 
            WHEN 
                att_log_min_max_pure.min_date_time_log = att_log_min_max_pure.max_date_time_log
            THEN
                NULL
            ELSE
                att_log_min_max_pure.max_minutes
        END
        
    ) AS "minute_2"
    
    
FROM
(
    SELECT
        att_log_grouped2.employee_id,
        att_log_grouped2.y,
        att_log_grouped2.m,
        att_log_grouped2.d,
        att_log_grouped2.min_date_time_log,
        att_log_grouped2.max_date_time_log
        , EXTRACT(HOUR FROM TO_TIMESTAMP(min_date_time_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as min_hour
        , EXTRACT(MINUTE FROM TO_TIMESTAMP(min_date_time_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as min_minutes
        
        , EXTRACT(HOUR FROM TO_TIMESTAMP(max_date_time_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as max_hour
        , EXTRACT(MINUTE FROM TO_TIMESTAMP(max_date_time_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as max_minutes
        
    FROM (
        SELECT 
            att_log_grouped.employee_id,
            att_log_grouped.y,
            att_log_grouped.m,
            att_log_grouped.d
            , MIN(att_log_grouped.datetime_log) as min_date_time_log
            , MAX(att_log_grouped.datetime_log) as max_date_time_log
            
        FROM
        (
            SELECT
                att_log.employee_id, att_log.att_pin
                , datetime_log
                , TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0' AS timestamp_log
                , EXTRACT(YEAR FROM TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as y
                , EXTRACT(MONTH FROM TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as m
                , EXTRACT(DAY FROM TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as d
                , EXTRACT(HOUR FROM TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as hours
                , EXTRACT(MINUTE FROM TO_TIMESTAMP(datetime_log)::TIMESTAMP WITH TIME ZONE AT TIME ZONE 'gmt+0') as minutes
                
            FROM hr_attendance_log att_log
            -- WHERE h_emp.name_related ilike '%{$where['employee']}%'
            ) AS att_log_grouped
            GROUP BY
            att_log_grouped.employee_id, att_log_grouped.y, att_log_grouped.m, att_log_grouped.d
        ) AS att_log_grouped2
    ) AS att_log_min_max_pure

FULL JOIN
    (
        SELECT 
            date_series.*
            , EXTRACT(YEAR FROM i) as year_series
            , EXTRACT(MONTH FROM i) as month_series
            , EXTRACT(DAY FROM i) as day_series
            , eids.eid as emp_id
        FROM(
            SELECT i::DATE FROM generate_series('{$first}','{$last}', '1 day'::INTERVAL) AS i
        ) AS date_series
        JOIN (
            SELECT DISTINCT(employee_id) as eid, hrd.name as dept_name FROM hr_attendance_log hrl
            JOIN hr_employee hre ON hre.id = hrl.employee_id
            JOIN hr_department hrd ON hrd.id = hre.department_id
            JOIN resource_resource rs_rs ON hre.resource_id = rs_rs.id
            WHERE hrd.name like '{$department_query}'
                AND hre.address_id = {$site}
                AND rs_rs.active is True
        ) AS eids ON eids.eid > 0
        -- WHERE h_emp.eid ilike '%{$where['employee']}%'
        -- order by eids.eid asc, date_series.i asc
    ) AS date_series ON date_series.emp_id = att_log_min_max_pure.employee_id and date_series.year_series = att_log_min_max_pure.y and date_series.month_series = att_log_min_max_pure.m and date_series.day_series = att_log_min_max_pure.d
JOIN hr_employee AS h_emp ON h_emp.id = date_series.emp_id
LEFT JOIN res_partner r_p ON r_p.id = h_emp.address_id
ORDER BY h_emp.name_related ASC, date_series.i ASC, att_log_min_max_pure.y ASC, att_log_min_max_pure.m ASC, att_log_min_max_pure.d ASC
query;
        $connection = Yii::$app->db;
        $res = $connection->createCommand($query)->queryAll();
        $dataToRender['dataProvider'] = new \yii\data\ArrayDataProvider(['allModels' => $res, 'pagination' => false]);
        $dataToRender['year'] = $year;
        $dataToRender['month'] = $month;
        $dataToRender['department_active'] = $department;
        $site_active = \app\models\ResPartner::findOne($site);
        $dataToRender['site_active'] = $site_active;
        $dataToRender['sites'] = \app\models\ResPartner::find()->where(['id' => [1792, 2788, 1417, 5732]])->orderBy('name ASC')->asArray()->all();
        $depts = \app\models\HrDepartment::find()->select('name')->orderBy('name ASC');
        if ($department) {
            // $depts->where('name = :name',[':name'=>$department]);
        }
        $dataToRender['depts'] = $depts->asArray()->all();
        // var_dump($dataToRender['depts']);
        return $this->render('first_and_last_scan', $dataToRender);
    }
Ejemplo n.º 2
0
    /**
     * Displays a single SalesActivity model.
     * @param integer $uid instead user_id
     * @return mixed
     */
    public function actionViewTimeLine(integer $uid = null, $customer = null, $start = null, $year = null, $group = null)
    {
        if (!$year) {
            $year = date('Y');
        }
        $salesActivityForm = new SalesActivityForm();
        $charts = ['pie' => [], 'line' => []];
        #prepared for all chart
        $now = true;
        $plan = SalesActivityPlan::find();
        $pieType = 'customer';
        $salesName = "All Sales Man";
        $salesActivityForm->sales = $uid;
        $salesActivityForm->customer = $customer;
        $salesActivityForm->date_begin = $start;
        if ($group) {
            // jika ada group id
            // cari user nya
            $userIds = \app\models\GroupSalesLine::find()->select('name')->where(['kelompok_id' => $group])->column();
            // var_dump($userIds);
        }
        // untuk filter dropdown by year
        if ($year) {
            $plan->andWhere('EXTRACT(YEAR FROM sales_activity_plan.the_date) = ' . $year);
        }
        // untuk filter dropdown by group
        $connection = Yii::$app->db;
        $queryGroup = <<<query
SELECT DISTINCT(id), initcap(name) AS name FROM group_sales WHERE is_main_group = true AND parent_id IS NULL ORDER BY name ASC
query;
        $modelGroup = $connection->createCommand($queryGroup)->queryAll();
        // untuk filter dari form input
        if ($salesActivityForm->load(Yii::$app->request->get()) && $salesActivityForm->validate()) {
            /*var_dump($salesActivityForm->date_begin);
            		die();*/
            $uid = $salesActivityForm->sales;
            if ($uid) {
                // die();
                $salesName = ResUsers::findOne($uid)->partner->name;
                $plan->where('sales_activity_plan.user_id = :uid')->addParams([':uid' => $uid]);
                $pieType = 'customer';
            }
            if ($salesActivityForm->customer) {
                $plan->andWhere('(sales_activity_plan.partner_id = :partner OR sales_activity_plan.actual_partner_id = :partner)')->addParams([':partner' => $salesActivityForm->customer]);
                $pieType = 'sales';
                $custName = ResPartner::findOne($salesActivityForm->customer)->name;
            }
            $start = $salesActivityForm->date_begin;
        } else {
            $msg = [];
            foreach ($salesActivityForm->errors as $error) {
                foreach ($error as $err) {
                    $msg[] = $err;
                }
            }
            if ($msg) {
                Yii::$app->session->setFlash('danger', implode(' and ', $msg));
            }
        }
        // filter user berdasarkan group dropdown
        if ($group) {
            $plan->andWhere(['user_id' => $userIds]);
        }
        // if defined start and end date
        if ($start) {
            $now = false;
        }
        if ($now) {
            $plan->andWhere('sales_activity_plan.the_date <= :now')->addParams([':now' => date('Y-m-d')]);
        }
        // echo $plan->createCommand()->sql;
        // var_dump($plan->with(['partner','user','user.partner','actualPartner'])->orderBy('year_p DESC, week_no DESC, dow DESC, user_id, daylight, not_planned_actual')->asArray()->all());
        // die();
        $dataProvider = new ArrayDataProvider(['allModels' => $plan->with(['partner', 'user', 'user.partner', 'actualPartner'])->orderBy('year_p DESC, week_no DESC, dow DESC, user_id, daylight, not_planned_actual')->asArray()->all()]);
        $pies = [];
        // var_dump($pieType);
        if ($pieType && $pieType == 'customer') {
            $chartData = $this->getCustomerActivityCompositionByUser($uid);
            // var_dump($chartData);
            $series = $chartData['series'];
            $pies[] = ['title' => 'Customer Visit Activity By ' . $salesName, 'series' => $chartData['series'], 'drillDown' => $chartData['drillDown'], 'drillDownTitle' => 'Customer Visit Activity'];
        } elseif ($pieType && ($pieType = 'sales')) {
            $series = $this->getCustomerActivityCompositionByCustomer($salesActivityForm->customer);
            $pies[] = ['title' => 'Relationship Activities On ' . $custName, 'series' => $series, 'drillDownTitle' => 'User Visit Activity On ' . ResPartner::findOne($salesActivityForm->customer)->name, 'drillDown' => []];
            // var_dump($series);
        } else {
            $pieSeries = false;
        }
        $charts['pie'] = $pies;
        // var_dump($charts);
        return $this->render('viewTimeLine', ['dataProvider' => $dataProvider, 'salesActivityForm' => $salesActivityForm, 'series' => $series, 'charts' => $charts, 'year' => $year, 'group_active' => $group, 'modelGroup' => $modelGroup]);
    }