public function actionRpt11()
{
//用以計算開始/結束時間之變數
$time_start = microtime(true);
//決定是門市設計師按鈕功能，false則是每日業績按鈕功能
$isExist = true;
//查詢的年月
$qry_date = date('Ym');
//區域
$qry_area = "";
//門市
$qry_store = "";
//顯示銷售情報
$check_sale_Intelligence = 0;
if (isset($_GET['storecode']) && isset($_GET['pdate'])) {
Yii::app()->session['storecode'] = $_GET['storecode'];
Yii::app()->session['pdate'] = $_GET['pdate'];
$this->redirect(array('rpt11'));
//跳轉回rpt11頁面
} else {
if (isset($_POST['qry_date'])) {
$qry_date = $_POST['qry_date'];
}
if (isset($_POST['qry_area'])) {
$qry_area = $_POST['qry_area'];
}
if (isset($_POST['qry_store'])) {
$qry_store = $_POST['qry_store'];
}
if (isset($_POST['check_sale_Intelligence'])) {
$check_sale_Intelligence = $_POST['check_sale_Intelligence'];
}
if (Yii::app()->session['storecode'] != '' && Yii::app()->session['pdate'] != '') {
$qry_store = Yii::app()->session['storecode'];
$qry_date = Yii::app()->session['pdate'];
//沒使用到清空
unset(Yii::app()->session['storecode']);
unset(Yii::app()->session['pdate']);
}
}
//切割出年份
$tmp_year = substr($qry_date, 0, 4);
//切割出月份
$tmp_mon = substr($qry_date, 4, 2);
//月底
$dateS = date("Ymd", mktime(24, 0, 0, $tmp_mon, 0, $tmp_year));
//月初
$dateE = date("Ymd", mktime(0, 0, 0, $tmp_mon + 1, 0, $tmp_year));
// 取得欄位
$col = $this->getRpt11Col($check_sale_Intelligence);
// 取得表頭
$title = $this->getRpt11Title();
// 取得欄位2
$acol = $this->getRpt11_daily_Col();
// 取得表頭2
$atitle = $this->getRpt11_daily_Title();
// 取得欄位3
$acol2 = $this->getRpt11_daily2_Col();
// 取得表頭3
$atitle2 = $this->getRpt11_daily2_Title();
// 輸出在畫面上的陣列
$colAry = array();
$colAry2 = array();
$colAry3 = array();
//其他項目包含少收
$other = array();
// 門市. 門市ID
$storeAry = array();
$storeAreaId = array();
$TbsStores = TbsStore::model()->findAllByAttributes(array('opt1' => 1));
foreach ($TbsStores as $store) {
$storeAry[$store->storecode] = $store->storename;
$storeAreaId[$store->storecode] = $store->area_id;
}
unset($TbsStores);
// 區域
$areaAry = array();
$TbsAreas = TbsArea::model()->findAllByAttributes(array('opt1' => 1));
foreach ($TbsAreas as $area) {
$areaAry[$area->id] = $area->areaname;
}
unset($TbsAreas);
// 職位
$positionAry = array();
$TbsPositions = TbsPosition::model()->findAllByAttributes(array('opt1' => 1));
foreach ($TbsPositions as $pos) {
$positionAry[$pos->id] = $pos->pcname;
}
unset($TbsPositions);
// 薪資, 責任額
$salaryAry = array();
$dutyAry = array();
$TbsBaseSalary = TbsBasesalary::model()->findAllByAttributes(array('opt1' => 1));
foreach ($TbsBaseSalary as $salary) {
$salaryAry[$salary->id] = $salary->salary;
$dutyAry[$salary->id] = $salary->duty;
}
unset($TbsBaseSalary);
//員工SQL
$qryStrno = '';
$check_area = TRUE;
$check_store = TRUE;
$check_all = TRUE;
$check_query = TRUE;
//如果有選門市,就只選出那一家門市
if ($qry_store != '') {
$tbsStroes = TbsStore::model()->findAllByAttributes(array('storecode' => $qry_store));
if (count($tbsStroes) < 1) {
$check_store = FALSE;
}
//如果只選區域,就選出區域內的所有門市
} elseif ($qry_area != '') {
$tbsStroes = TbsStore::model()->findAllByAttributes(array('area_id' => $qry_area));
if (count($tbsStroes) < 1) {
$check_area = FALSE;
}
//如果都沒有選,就全部選出來
} else {
$tbsStroes = TbsStore::model()->findAll();
if (count($tbsStroes) < 1) {
$check_all = FALSE;
}
}
// 儲存篩選出的門市的店編
$sqlStroe = array();
foreach ($tbsStroes as $store) {
//找出篩選出的門市的區域代碼
$area = TbsArea::model()->findByPK($store->area_id);
if ($area != NULL) {
//push店編
array_push($sqlStroe, $store->storecode);
}
}
// 查詢用的SQL
$qryStr = '';
//如果有選門市或區域就需要sql=AND storecode in('007001',007002')
if ($qry_store != '' or $qry_area != '') {
//sql=sql.in('007001')因為只有一筆的時候沒有' , ' 所以直接把店編放進去
if (count($sqlStroe) > 0) {
// $qryStr = " AND storecode in ('$sqlStroe[0]'";
$qryStr = "WHERE a.storecode in ('{$sqlStroe['0']}'";
//如果門市>1,就需要' , '
if (count($sqlStroe) > 1) {
for ($i = 1; $i < count($sqlStroe); $i++) {
$qryStr = $qryStr . ",'{$sqlStroe[$i]}'";
}
}
$qryStr = $qryStr . ")";
}
}
$empSql = "SELECT a.empno, a.daymonth, a.storecode\n FROM (\n tbs_emp_month a\n INNER JOIN (\n SELECT id, empno, MAX( daymonth ) AS daymonth, storecode\n FROM tbs_emp_month\n WHERE depart = '3'\n AND daymonth <= '{$qry_date}'\n GROUP BY empno\n )b ON a.empno = b.empno\n AND a.daymonth = b.daymonth\n ) {$qryStr} ";
$result = Yii::app()->db->createCommand($empSql)->queryAll();
//sql=sql.in('007001')因為只有一筆的時候沒有' , ' 所以直接把店編放進去
if (count($result) > 0) {
$qryStrno = " AND empno in ('" . $result[0]['empno'] . "'";
//如果名單>1,就需要' , '
if (count($result) > 1) {
for ($i = 1; $i < count($result); $i++) {
$qryStrno = $qryStrno . ",'" . $result[$i]['empno'] . "'";
}
}
$qryStrno = $qryStrno . ")";
} else {
if ($check_store && $check_area) {
$check_query = FALSE;
}
if ($check_store) {
Yii::app()->user->setFlash('error', '查無門市所屬人員，請重新查詢！');
}
if ($check_area) {
Yii::app()->user->setFlash('error', '查無區域所屬門市及人員！');
}
}
unset($result);
$empAry = array();
$sql = '';
if (isset($_POST['qry_designer']) && $check_query or $qry_store != '' && $qry_date != '' && !isset($_POST['qry_daily']) && $check_query) {
//OR $qry_store!='' && $qry_date!='' && $check_query 為了GET
//查到15號的業績
$midday = date("Ymd", mktime(0, 0, 0, $tmp_mon, 15, $tmp_year));
$sql = "SELECT empno, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE pdate BETWEEN '{$dateS}' AND '{$dateE}' {$qryStrno} " . "GROUP BY empno, serviceno ORDER BY empno ";
$sql2 = "SELECT empno, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE pdate BETWEEN '{$dateS}' AND '{$midday}' {$qryStrno} " . "GROUP BY empno, serviceno ORDER BY empno ";
$result1 = Yii::app()->db->createCommand($sql)->queryAll();
$empAry = $this->setEmpAry($result1);
$result2 = Yii::app()->db->createCommand($sql2)->queryAll();
$empAry = $this->setMidEmpAry($empAry, $result2);
// 查出洗髮+10元的剪髮顆數跟洗頭數
$sql3 = "SELECT a.empno, sum(b.num) as cut, sum(a.num) as wash\n FROM \n (\n SELECT pdate, storecode, empno, num \n FROM `tbp_perform_emp_log` \n WHERE pdate BETWEEN '{$dateS}' AND '{$dateE}' {$qryStrno} AND serviceno = 'D01' \n ) a\n ,\n (\n SELECT pdate, storecode, empno, num \n FROM `tbp_perform_emp_log` \n WHERE pdate BETWEEN '{$dateS}' AND '{$dateE}' {$qryStrno} AND serviceno = 'A01'\n ) b \n WHERE a.pdate = b.pdate AND a.storecode = b.storecode AND a.empno = b.empno\n GROUP BY a.empno";
$result3 = Yii::app()->db->createCommand($sql3)->queryAll();
$empAry = $this->setWashEmpAry($empAry, $result3, 'col10', 'col12');
// 查出洗髮不加10元的剪髮顆數跟洗頭數
$sql4 = "SELECT a.empno, sum(b.num) as cut, sum(a.num) as wash\n FROM \n (\n SELECT pdate, storecode, empno, sum(num) as num\n FROM `tbp_perform_emp_log` \n WHERE pdate BETWEEN '{$dateS}' AND '{$dateE}' {$qryStrno} AND serviceno IN ('S02','S06')\n GROUP BY pdate, storecode, empno\n ) a\n ,\n (\n SELECT pdate, storecode, empno, sum(num) as num\n FROM `tbp_perform_emp_log` \n WHERE pdate BETWEEN '{$dateS}' AND '{$dateE}' {$qryStrno} AND serviceno IN ('A01','S01','S05')\n GROUP BY pdate, storecode, empno\n ) b \n WHERE a.pdate = b.pdate AND a.storecode = b.storecode AND a.empno = b.empno\n GROUP BY a.empno";
$result4 = Yii::app()->db->createCommand($sql4)->queryAll();
$empAry = $this->setWashEmpAry($empAry, $result4, 'col11', 'col13');
foreach ($empAry as $empno => $empData) {
// 查詢員工薪資福利
$empMonth = new TbsEmpMonth();
$empMonth = TbsEmpMonth::model()->findByDayMonth($empno, $qry_date);
$user = User::model()->findByPk($empno);
if ($empMonth != NULL) {
$row = array();
//員編
$row['empno'] = $empno;
// 年月
$row['ym'] = $qry_date;
// 營業區
$row['area'] = $areaAry[$empMonth->area];
// 門市
$row['store'] = $storeAry[$empMonth->storecode];
// 職稱
$row['position'] = $positionAry[$empMonth->position1];
// 姓名
$row['name'] = $user->emp ? $user->emp->empname : '';
// 到職日
$row['arrivedate'] = Yii::app()->dateFormatter->format("yyyy-MM-dd", $empMonth->arrivedate, "yyyy-MM-dd");
// 狀態
$row['status'] = TbsEmp::model()->getHireType($empMonth->hiretype);
// 底薪
$row['salary'] = $salaryAry[$empMonth->salary];
//已休
$row['vacation'] = ' ';
// 責任業績, 區店長需動態算, opt2欄位設成管理門市數
// if($empMonth->position1 == 9){
// $areaduty = TbsAreaduty::model()->findByAttributes(array('storenum'=>$empMonth->opt2));
// if(isset($areaduty))
// $row['duty'] = $areaduty->duty;
// }else
$row['duty'] = $dutyAry[$empMonth->salary];
// 設定各欄位業績及洗助算法
$row = $this->setRpt11RowData($row, $empData);
// 達成率
$rate = $row['duty'] == 0 ? 0 : round($row['perform'] / $row['duty'], 4);
$row['rate'] = $rate * 100 . "%";
// CVarDumper::dump($empData,10,true);
//
// 是否達成
$row['achi'] = $rate >= 1 ? '達成' : '未達成';
if ($row['perform'] + $row['assist'] > 0) {
// ================== 以下是期中 ==========================
// CVarDumper::dump($midasum,10,TRUE);
// 設定各欄位業績及洗助算法
$row = $this->setRpt11MidRowData($row, $empData);
}
// 期中業績(不含洗住)
$row['col8'] = $row['midperform'];
// 期中責任業績
$row['midduty'] = $row['duty'];
// 達成率
if ($row['midduty'] > 0) {
$rate = $row['midduty'] == 0 ? 0 : round($row['midperform'] / $row['midduty'], 4);
$row['midrate'] = $rate * 100 . "%";
} else {
$row['midrate'] = "0%";
}
// 是否達成
$row['midachi'] = $rate >= 0.5 ? '達成' : '未達成';
if ($row['midperform'] + $row['midassist'] > 0) {
//期末業績成長率
if ($row['midperform'] > 0) {
$ratecol14 = round(($row['perform'] - $row['midperform'] - $row['midperform']) / $row['midperform'], 4);
// print_r($ratecol14);
$row['col7'] = $ratecol14 * 100 . "%";
} else {
$row['col7'] = "0%";
}
}
array_push($colAry, $row);
} else {
Yii::app()->user->setFlash('error', "查無員工 {$empno} " . (isset($user->emp) ? $user->emp->empname : '') . " ,{$qry_date} 月的薪資福利資料");
break;
}
}
unset($empAry);
//統計結果
//$total=$this->getTotal($colAry);
} elseif (isset($_POST['qry_daily'])) {
$isExist = false;
/*--以下為每日業績--*/
//儲存所選門市當月總業績金額
$all_amount = array();
//儲存所選門市當月每天業績金額
$amount = array();
//每一行的陣列
$Arylog = array();
if (isset($_POST['qry_store']) && $qry_store != '') {
//門市總業績
$perform_sql = '';
$perform_sql = "SELECT mid(pdate,1,6) AS pdate , storecode ,storename , sum(total) AS total FROM tbp_perform \n WHERE mid(pdate,1,6)={$qry_date} and storecode='{$qry_store}' ";
$perform_result = Yii::app()->db->createCommand($perform_sql)->queryAll();
//先將該門市整個月的業績存在all_amount array()裡
if ($perform_result > 0) {
foreach ($perform_result as $value) {
$all_amount[$value['pdate']] = $value['total'];
}
unset($perform_result);
}
//該門市有業績的人員資料
$personsql = '';
$personsql = "SELECT mid(a.pdate,1,6) AS pdate , c.areaname ,d.storecode ,d.storename \n , b.position1 , a.empno , a.empname , b.arrivedate ,b.hiretype\n\n FROM tbp_perform_emp AS a ,tbs_emp_month AS b ,tbs_area AS c , tbs_store AS d\n\n where mid(a.pdate,1,6)='{$qry_date}' and a.storecode='{$qry_store}' and\n a.empno=b.empno and b.area=c.id and b.storecode=d.storecode\n\n and d.storecode=(SELECT storecode FROM tbs_emp_month\n where empno=a.empno and daymonth <='{$qry_date}'\n order by daymonth DESC LIMIT 1) ";
//group by a.empno
$person_result = Yii::app()->db->createCommand($personsql)->queryAll();
//撈出在該門市有業績的員編
$temp_arr = array();
if (count($person_result) > 0) {
for ($i = 0; $i < count($person_result); $i++) {
array_push($temp_arr, $person_result[$i]['empno']);
}
}
//if(count($person_result)>0){
$_str = "'" . implode("','", $temp_arr) . "'";
//每個員編加入單引號為了sql in查詢使用
//得到員工剪、染、洗等..
$personsql2 = '';
$personsql2 = "SELECT empno,empname ,serviceno, sum(num) as num FROM tbp_perform_emp_log \n WHERE mid(pdate,1,6)='{$qry_date}' and storecode='{$qry_store}' \n and empno in({$_str}) and num!=0\n GROUP BY empno, serviceno ORDER BY empno , serviceno";
$person_result2 = Yii::app()->db->createCommand($personsql2)->queryAll();
$person_result2 = $this->setEmpAry($person_result2);
//有業績的人員資料及員工剪、染、洗等..，放在一起
for ($i = 0; $i < count($person_result); $i++) {
$empno = $person_result[$i]['empno'];
if (isset($person_result2[$empno])) {
$emp = $person_result2[$empno];
$emp['pdate'] = $person_result[$i]['pdate'];
$emp['areaname'] = $person_result[$i]['areaname'];
$emp['storecode'] = $person_result[$i]['storecode'];
$emp['storename'] = $person_result[$i]['storename'];
$emp['position1'] = $person_result[$i]['position1'];
$emp['empno'] = $person_result[$i]['empno'];
$emp['empname'] = $person_result[$i]['empname'];
$emp['arrivedate'] = $person_result[$i]['arrivedate'];
$emp['hiretype'] = $person_result[$i]['hiretype'];
$person_result2[$empno] = $emp;
} else {
continue;
}
}
foreach ($person_result2 as $empno => $empData) {
$row = array();
// 年月
$row['ym'] = $empData['pdate'];
// 營業區
$row['areaname'] = $empData['areaname'];
// 門市編號(為了計算本店與別店的占比)
$row['storecode'] = $empData['storecode'];
// 門市
$row['storename'] = $empData['storename'];
// 職稱
$row['position'] = $positionAry[$empData['position1']];
// 姓名
$row['empname'] = $empData['empname'];
// 到職日期
$row['arrivedate'] = $empData['arrivedate'];
// 狀態
$row['hiretype'] = TbsEmp::model()->getHireType($empData['hiretype']);
// 設定各欄位業績及洗助算法
$row = $this->setRpt11Row_daily_Data2($row, $empData);
// 業績百分比
$total = (double) $all_amount[$empData['pdate']];
$rate = round($row['perform'] / $total, 4);
$row['rate'] = $rate * 100 . "%";
array_push($colAry3, $row);
}
//存業績陣列
$rank = array();
//存業績排名陣列
$rank2 = array();
for ($i = 0; $i < count($colAry3); $i++) {
array_push($rank, $colAry3[$i]['perform']);
}
//create a copy and rsort
$rank_copy = $rank;
rsort($rank_copy);
//reverses key and values
$rank_copy = array_flip($rank_copy);
//create result by using keys from sorted values + 1
foreach ($rank as $val) {
$rank2[] = $rank_copy[$val] + 1;
}
//插入排名到$colAry3
foreach ($rank2 as $key => $value) {
if (isset($colAry3[$key])) {
$temp = $colAry3[$key];
$temp['perform_rank'] = $value;
$colAry3[$key] = $temp;
} else {
continue;
}
}
// CVarDumper::dump($colAry3,10,true);
//門市服務項目SQL
$sql = '';
$sql = "(SELECT pdate , storecode , storename ,serviceno, sum(num) as amount FROM tbp_perform_emp_log\n WHERE mid(pdate,1,6)='{$qry_date}' and storecode='{$qry_store}' AND num !=0\n GROUP BY pdate , serviceno \n ORDER BY pdate DESC )";
$sql2 = '';
$sql2 = "(SELECT pdate , storecode , storename ,serviceno, sum(num) as amount FROM tbp_perform_log\n WHERE mid(pdate,1,6)='{$qry_date}' and storecode='{$qry_store}' AND num !=0\n GROUP BY pdate , serviceno \n ORDER BY pdate DESC )";
$sql = $sql . "UNION" . $sql2;
//合併
$sql = $sql . "order by pdate DESC, storecode, serviceno";
//一定要照pdate排序,因為loop要照日期去跑
$result = Yii::app()->db->createCommand($sql)->queryAll();
//門市業績
$sql3 = '';
$sql3 = "SELECT pdate , storecode ,storename , total FROM tbp_perform \n WHERE mid(pdate,1,6)='{$qry_date}' and storecode='{$qry_store}'\n GROUP BY pdate \n ORDER BY storecode ASC , pdate DESC";
$result2 = Yii::app()->db->createCommand($sql3)->queryAll();
//先將該門市整個月的業績存在amount array()裡
if ($result2 > 0) {
foreach ($result2 as $value) {
$amount[$value['pdate']] = $value['total'];
}
unset($result2);
}
//將門市所有服務項目放在同個日期及門市
if (count($result > 0)) {
if (isset($result[0]['pdate']) or isset($result[0]['storecode'])) {
$day = $result[0]['pdate'];
$storecode = $result[0]['storecode'];
// 每一行的陣列
$row = array();
for ($i = 0; $i < count($result); $i++) {
if ($day == $result[$i]['pdate'] && $storecode == $result[$i]['storecode']) {
$row['pdate'] = $result[$i]['pdate'];
$row['storecode'] = $result[$i]['storecode'];
$row['storename'] = $result[$i]['storename'];
$row[$result[$i]['serviceno']] = $result[$i]['amount'];
$row['total'] = $amount[$result[$i]['pdate']];
} else {
array_push($Arylog, $row);
$row = array();
$day = $result[$i]['pdate'];
$storecode = $result[$i]['storecode'];
/*------*/
$row['pdate'] = $result[$i]['pdate'];
$row['storecode'] = $result[$i]['storecode'];
$row['storename'] = $result[$i]['storename'];
$row[$result[$i]['serviceno']] = $result[$i]['amount'];
$row['total'] = $amount[$result[$i]['pdate']];
}
}
//for ($i = 0; $i < count($result); $i++) {
array_push($Arylog, $row);
}
//if(isset($result[0]['pdate']) OR isset($result[0]['storecode']) ){
}
//if(count($result>0)) {
foreach ($Arylog as $key => $storeData) {
$row = array();
//日期
$row['ymd'] = $storeData['pdate'];
// 設定各欄位業績及洗助算法
$row = $this->setRpt11Row_daily_Data($row, $storeData);
//每日業績金額
$row['perform_amount'] = (double) $storeData['total'];
array_push($colAry2, $row);
}
//計算其他項目
$other_sql = '';
$other_sql = "(SELECT mid(pdate,1,6) as pdate , storecode , storename ,serviceno, sum(num) as amount FROM tbp_perform_log\n WHERE mid(pdate,1,6)='{$qry_date}' and storecode='{$qry_store}' AND num !=0\n GROUP BY serviceno \n ORDER BY pdate DESC )";
$other_result = Yii::app()->db->createCommand($other_sql)->queryAll();
if (count($other_result > 0)) {
if (isset($other_result[0]['storecode'])) {
$storecode = $other_result[0]['storecode'];
// 每一行的陣列
$row = array();
for ($i = 0; $i < count($other_result); $i++) {
if ($storecode == $other_result[$i]['storecode']) {
$row['storecode'] = $other_result[$i]['storecode'];
$row['storename'] = $other_result[$i]['storename'];
$row[$other_result[$i]['serviceno']] = $other_result[$i]['amount'];
} else {
array_push($other, $row);
$row = array();
$storecode = $other_result[$i]['storecode'];
/*------*/
$row['storecode'] = $other_result[$i]['storecode'];
$row['storename'] = $other_result[$i]['storename'];
$row[$other_result[$i]['serviceno']] = $other_result[$i]['amount'];
}
}
//for ($i = 0; $i < count($other_result); $i++) {
array_push($other, $row);
}
//if(isset($result[0]['storecode']) ){
}
//if(count($other_result>0)) {
if (isset($other[0])) {
$other = $this->getRpt11MoneySellandReduce($other[0]);
//其他項目百分比
$other_amout = (double) $all_amount[$qry_date];
if ($other['sum'] != 0) {
$rate = round($other['sum'] / $other_amout, 4);
$other['rate'] = $rate * 100 . "%";
}
if ($other['sum2'] != 0) {
$rate = round($other['sum2'] / $other_amout, 4);
$other['rate2'] = $rate * 100 . "%";
}
}
}
}
//elseif(isset($_POST['qry_daily']))
// CVarDumper::dump($colAry,10,true);
$time_end = microtime(true);
$computetime = round($time_end - $time_start, 3);
if (isset($_POST['qry_designer']) or $qry_store != '' && $qry_date != '' && !isset($_POST['qry_daily'])) {
if (count($colAry) < 1) {
Yii::app()->user->setFlash('error', Yii::app()->user->getFlash('error') . "查無,{$qry_date} 月的業績資料");
} else {
Yii::app()->user->setFlash('success', Yii::app()->user->getFlash('success') . "查詢成功. 共計 " . count($colAry) . " 筆資料");
}
} elseif (isset($_POST['qry_daily'])) {
if ($qry_store != '') {
if (count($colAry2) < 1 and count($colAry3) < 1) {
Yii::app()->user->setFlash('error', Yii::app()->user->getFlash('error') . "查無,{$qry_date} 月的業績資料");
} else {
Yii::app()->user->setFlash('success', Yii::app()->user->getFlash('success') . "查詢成功.");
}
} else {
Yii::app()->user->setFlash('error', Yii::app()->user->getFlash('error') . "請選擇門市!");
}
}
$this->render('rpt11', array('isExist' => $isExist, 'qry_date' => $qry_date, 'qry_area' => $qry_area, 'qry_store' => $qry_store, 'tbsStroes' => $tbsStroes, 'check_sale_Intelligence' => $check_sale_Intelligence, 'col' => $col, 'title' => $title, 'colAry' => $colAry, 'acol' => $acol, 'atitle' => $atitle, 'colAry2' => $colAry2, 'acol2' => $acol2, 'atitle2' => $atitle2, 'colAry3' => $colAry3, 'other' => $other, 'computetime' => $computetime));
}
/**
* 取得員工姓名
* @param string $empno - 員工編號
* @return string - 員工姓名
*/
private function getEmpName($empno)
{
$empname = "";
$emp = TbsEmp::model()->findByEmpno($empno);
if (isset($emp)) {
$empname = $emp->empname;
}
return $empname;
}
/**
*
*/
public function actionSettle()
{
//年月, 預設上個月
$tmp = strtotime("-1 month", time());
$qry_YM = date('Ym', $tmp);
$qry_YM = '201405';
$com = new ComFunction();
//員編
$qry_empno = '';
//員工姓名
$qry_empname = '';
if (isset($_POST['qry_YM'])) {
$qry_YM = $_POST['qry_YM'];
}
if (isset($_POST['qry_empno'])) {
$qry_empno = $_POST['qry_empno'];
}
if (isset($_POST['qry_empname'])) {
$qry_empname = $_POST['qry_empname'];
}
// 標題列
$title = array();
// 欄位順序
$col = array();
// 畫面輸出陣列
$output = array();
// 顯示差勤細項紀錄
$showTotal = TRUE;
if ($_POST) {
// 若員工編號或姓名不為空
if ($qry_empno != '') {
$user = User::model()->findByPk($qry_empno);
if (isset($user->emp)) {
$qry_empname = $user->emp->empname;
}
} elseif ($qry_empname != '') {
$emp = TbsEmp::model()->findByAttributes(array('empname' => $qry_empname));
if (isset($emp)) {
$qry_empno = $emp->empno;
}
}
// 勾選顯示差勤
// if(isset($_POST['showTotal']) && $_POST['showTotal'] == 1 )
// $showTotal = TRUE;
// 差勤結算
// if(isset($_POST['settle'])) $showTotal = TRUE;
// 取得此月份的國定假日
$holidays = $com->getHolidayByYearMonth($qry_YM);
// 取得所有的差勤項目
$logitems = TbaLogItem::model()->findAllByAttributes(array('optshow' => 1));
$items = array();
// 所有差勤項目
$itemsettle = array();
// 差勤結算項目
$itemname = array();
// 差勤名稱
$itemdays = array();
// 以天計差勤項目 (公.婚.事.病.假.曠..)
$itemmins = array();
// 以分計差勤項目 ( 遲到早退開小差 )
$itemeach = array();
// 以支計差勤項目 ( 小過, 大過 )
// 將資料分類
foreach ($logitems as $item) {
array_push($items, $item->id);
$itemname[$item->id] = $item->logname;
if ($item->weight == 1) {
if ($item->logtype == 1) {
if ($item->opt2 == 0) {
array_push($itemdays, $item->id);
} else {
array_push($itemmins, $item->id);
}
} else {
array_push($itemeach, $item->id);
}
}
if ($item->salaryitem != '') {
$itemsettle[$item->id] = $item->salaryitem;
}
}
// 輸出的表頭
$title = $this->getWeightTitle($showTotal, $items, $itemdays, $itemmins, $itemeach, $itemname);
// 欄位順序
$col = array_keys($title);
// 取得差勤權重
$logweights = TbaWeight::model()->findAllByAttributes(array('opt1' => 1));
$nweight = array();
// 平日權重
$hweight = array();
// 假日權重
//
foreach ($logweights as $weight) {
$nweight[$weight->logitem] = $weight->nweight;
$hweight[$weight->logitem] = $weight->hweight;
if ($weight->opt2 == 1) {
$nweight['opt2'] = $weight->nweight;
}
}
// 計算權重
// 查出目前所有的差勤紀錄
// 每一天都丟進 $holidays 來判斷是否為假日
// 再逐項去判斷是否有權重. 若有則再做計算.
// 而權重要判斷成是以天計, 以分計, 還是以支計
$aStart = $com->getTheFirstDayByYearMonth($qry_YM, "Ymd");
$aEnd = $com->getTheLastDayByYearMonth($qry_YM, "Ymd");
// 查出區間內之差勤紀錄, 依員編. 差勤項目. 排序
$sql = "SELECT * \n FROM `tba_log` \n WHERE logday \n BETWEEN '{$aStart}'\n AND '{$aEnd}' ";
if ($qry_empno != '') {
$sql = $sql . "AND empno = '{$qry_empno}' ";
}
$sql = $sql . "ORDER BY empno, logitem, opt2";
$logs = TbaLog::model()->findAllBySql($sql);
// 針對每一筆. 先判斷是否有權重. 再判斷是那一種
// logtype=1 => 天 => 再判斷平日.假日
// logtype=1 => => opt2 = 1 ==> 分 , 該月累積超過30分, 則有權重
// 支 => 每一筆皆有
$emp = $logs[0]->empno;
$empAry = array();
$empAry['empname'] = $logs[0]->empname;
for ($i = 0; $i < count($logs); $i++) {
// 若符合要計算權重的項目才進行判斷
if (in_array($logs[$i]->logitem, $items)) {
// 同一個人一個 array
if ($logs[$i]->empno == $emp) {
if (isset($empAry[$logs[$i]->logitem])) {
$empAry[$logs[$i]->logitem] = $empAry[$logs[$i]->logitem] + $logs[$i]->num;
} else {
$empAry[$logs[$i]->logitem] = $logs[$i]->num;
}
$empAry = $this->setWeightValue($empAry, $logs[$i], $holidays, $nweight, $hweight);
} else {
// 寫入前, 把遲到分鐘數, 換算成權重
if (isset($empAry['opt2']) && isset($nweight['opt2'])) {
$empAry['opt2'] = $this->setLateWeight($empAry['opt2'], $nweight['opt2']);
} else {
$empAry['opt2'] = 0;
}
$empAry['sum'] = $this->setSumWeight($col, $empAry);
$output[$emp] = $empAry;
$emp = $logs[$i]->empno;
$empAry = array();
$empAry['empname'] = $logs[$i]->empname;
if (isset($empAry[$logs[$i]->logitem])) {
$empAry[$logs[$i]->logitem] = $empAry[$logs[$i]->logitem] + $logs[$i]->num;
} else {
$empAry[$logs[$i]->logitem] = $logs[$i]->num;
}
$empAry = $this->setWeightValue($empAry, $logs[$i], $holidays, $nweight, $hweight);
}
}
// if(in_array($logs[$i]->logitem, $items))
}
// for ($i = 0; $i < count($logs); $i++)
if (isset($empAry['opt2']) && isset($nweight['opt2'])) {
$empAry['opt2'] = $this->setLateWeight($empAry['opt2'], $nweight['opt2']);
} else {
$empAry['opt2'] = 0;
}
$empAry['sum'] = $this->setSumWeight($col, $empAry);
$output[$emp] = $empAry;
// 如果是結算, 則將結果寫入資料庫
if (isset($_POST['settle'])) {
// 先刪除該年月之資料
$deleteAry = array();
$param = TbmParam::model()->findByAttributes(array('param' => 'attend_settle'));
if ($param != NULL) {
$deleteAry = explode(",", $param->pvalue);
}
// 先刪除此年月之資料
if ($qry_empno != '' or $qry_empname != '') {
TbmEmpItem::model()->deleteAllByAttributes(array('daymonth' => $qry_YM, 'empno' => $qry_empno, 'itemno' => $deleteAry));
} else {
TbmEmpItem::model()->deleteAllByAttributes(array('daymonth' => $qry_YM, 'itemno' => $deleteAry));
}
$result = $this->createSettleItems($qry_YM, $itemsettle, $output);
if (isset($result)) {
$check = isset($result[0]) ? $result[0] : FALSE;
$errmsg = isset($result[1]) ? $result[1] : "差勤結算失敗！";
if ($check) {
Yii::app()->user->setFlash('success', "[差勤結算]成功！{$qry_YM} 共計有 " . count($output) . "筆 員工有差勤紀錄！");
} else {
Yii::app()->user->setFlash('error', "[差勤結算]失敗！{$errmsg！}");
}
}
} else {
Yii::app()->user->setFlash('success', "[畫面輸出]查詢成功！{$qry_YM} 共計有 " . count($output) . "筆 員工有差勤紀錄！");
}
} else {
Yii::app()->user->setFlash('notice', "[畫面輸出]可以只查看此年月之差勤權重, 不會結算至薪資。[差勤結算]會將此年月之差勤結果寫入薪資項目，供薪資計算用。");
}
$this->render('settle', array('qry_YM' => $qry_YM, 'qry_empno' => $qry_empno, 'qry_empname' => $qry_empname, 'title' => $title, 'col' => $col, 'colAry' => $output));
}
?>
</td>
<td><?php
echo $form->labelEx($model, 'empname');
?>
*</td>
<td>
<?php
// echo $form->textField($model,'empno',array('size'=>8,'maxlength'=>8));
?>
<?php
// echo $form->textField($model,'empname',array('size'=>10,'maxlength'=>20));
?>
<?php
echo $form->dropDownList($model, 'empno', CHtml::listData(TbsEmp::model()->findAll(array('order' => 'empno ASC', 'condition' => 'opt1=1')), 'empno', 'empname'));
?>
<?php
echo $form->error($model, 'empno');
?>
<?php
echo $form->error($model, 'empname');
?>
</td>
<td><?php
echo $form->labelEx($model, 'logitem');
?>
</td>
<td>
<?php
// echo $form->textField($model,'logtype',array('size'=>2,'maxlength'=>2));
