public function actionPartnerlist($search = null, $id = null)
{
$out = ['more' => false];
if (!is_null($search)) {
$query = new Query();
$query->select('id, name AS text')->from('res_partner')->where(['like', 'name', $search])->andWhere(['customer' => true])->andWhere(['is_company' => true])->limit(20);
$command = $query->createCommand();
$data = $command->queryAll();
$out['results'] = array_values($data);
} elseif ($id > 0) {
$out['results'] = ['id' => $id, 'text' => ResPartner::find($id)->name];
} else {
$out['results'] = ['id' => 0, 'text' => 'No matching records found'];
}
echo Json::encode($out);
}
public function actionSupplierlist($search = null, $id = null)
{
$out = ['more' => false];
if (!is_null($search)) {
$command = new Query();
$lowerchr = strtolower($search);
$command = Yii::$app->db->createCommand("SELECT DISTINCT id, name as text FROM res_partner WHERE lower(name) LIKE '%" . $lowerchr . "%' AND supplier=true AND is_company=true LIMIT 20");
$data = $command->queryAll();
$out['results'] = array_values($data);
} elseif ($id > 0) {
$ids = explode(',', $id);
foreach ($ids as $value) {
$data[] = ['id' => $value, 'text' => ResPartner::find()->where(['id' => $value])->one()->name];
}
$out['results'] = $data;
} else {
$out['results'] = ['id' => 0, 'text' => 'No matching records found'];
}
echo \yii\helpers\Json::encode($out);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getPartner()
{
return $this->hasOne(ResPartner::className(), ['id' => 'partner_id']);
}
public function actionGetPartnerImage($id)
{
$model = \app\models\ResPartner::find($id)->asArray()->one();
}
<div class="delivery-note-search">
<?php
$form = ActiveForm::begin(['action' => ['reportkpi'], 'method' => 'get']);
?>
<div class="col-md-12">
<?php
/*echo $form->field($model, 'partner_id')->widget(Select2::classname(), [
'data' => ArrayHelper::map(ResPartner::find()->all(),'id','display_name'),
'options' => ['placeholder' => 'Filter as you type ...'],
])->label('Address Name')*/
?>
<?php
$data = ResPartner::find()->select(['display_name as value', 'display_name as label', 'id as id'])->asArray()->all();
?>
<?php
echo $form->field($model, 'partner_id')->widget(\yii\jui\AutoComplete::classname(), ['clientOptions' => ['source' => $data]])->textInput()->label('Address Name');
?>
</div>
<div class="col-md-6">
<?php
echo $form->field($model, 'month_tanggal')->dropDownList(['1' => 'Januari', '2' => 'Februari', '3' => 'Maret', '4' => 'April', '5' => 'Mei', '6' => 'Juni', '7' => 'Juli', '8' => 'Agustus', '9' => 'September', '10' => 'Oktober', '11' => 'November', '12' => 'Desember'], ['prompt' => ''])->label('Bulan Kirim');
?>
</div>
<div class="col-md-6">
<?php
echo $form->field($model, 'year_tanggal')->textInput()->label('Tahun Kirim');
?>
</div>
/**
* @return \yii\db\ActiveQuery
*/
public function getResPartners()
{
return $this->hasMany(ResPartner::className(), ['state_id' => 'id']);
}
public function actionExportCsv($id)
{
$this->layout = 'export';
$partner = ResPartner::find()->where(['id' => $id])->one();
return $this->render('report/exportcsv', ['model' => $partner]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAddressAllotment()
{
return $this->hasOne(ResPartner::className(), ['id' => 'address_allotment_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getFakturAddress()
{
return $this->hasOne(ResPartner::className(), ['id' => 'faktur_address']);
}
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);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getName0()
{
return $this->hasOne(ResPartner::className(), ['id' => 'name']);
}
/**
* [getCustomerActivityCompositionByCustomer description]
* @param integer $partner_id [description]
* @param string $type [description]
* @return array [description]
*/
private function getCustomerActivityCompositionByCustomer($partner_id, $type = 'pie')
{
$plan = SalesActivityPlan::find();
$plan->select('res_partner.name, count(sales_activity_plan.user_id) as cout')->leftJoin(ResUsers::tableName(), 'res_users.id=sales_activity_plan.user_id')->leftJoin(ResPartner::tableName(), 'res_partner.id=res_users.partner_id')->where('sales_activity_plan.actual_partner_id = :actual_partner_id')->addParams([':actual_partner_id' => $partner_id])->groupBy(['res_partner.name']);
$series = [];
foreach ($plan->createCommand()->queryAll() as $idx => $act) {
$series[$idx] = ['name' => $act['name'], 'y' => floatval($act['cout'])];
}
return $series;
}
<div class="col-md-12">
<?php
/*$data = ResPartner::find()
->select(['display_name as value', 'display_name as label', 'id as id'])
->asArray()
->all();
echo $form->field($model, 'partner_id')->widget(\yii\jui\AutoComplete::classname(), [
'clientOptions' => [
'source' => $data,
],
])->textInput()->label('Costumer') */
?>
<?php
// $data = ArrayHelper::getColumn(ResPartner::find()->select('display_name')->distinct()->all(),'display_name');
echo $form->field($model, 'tag_partner')->widget(Select2::classname(), ['name' => 'tag_partner', 'data' => ArrayHelper::map(ResPartner::find()->select('display_name')->distinct()->all(), 'display_name', 'display_name'), 'options' => ['placeholder' => 'Cari Costumer...', 'class' => 'form-controler', 'multiple' => true]])->label('Costumer');
?>
</div>
<div class="form-group">
<?php
echo Html::submitButton('Search', ['class' => 'btn btn-primary']);
?>
<?php
echo Html::resetButton('Reset', ['class' => 'btn btn-default']);
?>
</div>
<?php
ActiveForm::end();
?>
/**
* @return \yii\db\ActiveQuery
*/
public function getAddressHome()
{
return $this->hasOne(ResPartner::className(), ['id' => 'address_home_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAttention0()
{
return $this->hasOne(ResPartner::className(), ['id' => 'attention']);
}
