/** * 業績月報表 */ public function actionRpt10() { // 用以計算開始/結束時間之變數 $time_start = microtime(true); // Sleep for a while usleep(100); //得到起始 結束的年月日 $sqlDate = "SELECT MID( pdate, 1, 6 ) as daymonth FROM `tbp_perform` GROUP BY MID( pdate, 1, 6 ) ORDER BY daymonth DESC"; $dateSqlResult = Yii::app()->db->createCommand($sqlDate)->queryAll(); $dmAry = array(); for ($i = 0; $i < count($dateSqlResult); $i++) { $daymonth = $dateSqlResult[$i]['daymonth']; $dmAry[$daymonth] = $daymonth; } $qry_date = date('Ym'); $qry_area = ""; //區域 $qry_store = ""; // 門市 $qry_store = array(); //服務項目 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']; } // 服務項目陣列(上面的篩選條件) $storeary = array(); $tbsStroes = TbsStore::model()->findAll(); // 業績月報表 是 區域 門市 (月份天數) 合計 $qry_daymonth = $qry_date . '01'; $days = date('t', strtotime($qry_daymonth)); // 畫面呈現的表格欄位, col是欄位名稱, title是欄位顯示的中文字 $col = $this->getRpt10Col($days); $title = $this->getRpt10Title($days); // CVarDumper::dump($title,1,true); // 輸出在畫面上的陣列 $colAry = array(); //店編對應區域名稱的陣列 $stores = array(); //儲存篩選出來的門市 $tbsStroes = array(); //儲存塞選出的門市的店編 $sqlStroe = array(); //合計陣列 $sum = array(); foreach ($title as $key => $value) { $sum[$key] = 0; } //如果只選區域,就選出區域內的所有門市 if ($qry_area != '') { $tbsStroes = TbsStore::model()->findAllByAttributes(array('area_id' => $qry_area)); } else { $tbsStroes = TbsStore::model()->findAll(); } foreach ($tbsStroes as $store) { //找出篩選出的門市的區域代碼 $area = TbsArea::model()->findByPK($store->area_id); if ($area != NULL) { //push店編 array_push($sqlStroe, $store->storecode); //店編對應區域名稱 $stores[$store->storecode] = $area->areaname; } } // 查詢用的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']}'"; //如果門市>1,就需要' , ' if (count($sqlStroe) > 1) { for ($i = 1; $i < count($sqlStroe); $i++) { $qryStr = $qryStr . ",'{$sqlStroe[$i]}'"; } } $qryStr = $qryStr . ")"; } } //按下查詢 if (isset($_POST['qry'])) { $sql = "SELECT storecode, pdate, total \n FROM `tbp_perform` \n WHERE mid(pdate,1,6) = '{$qry_date}'\n ORDER BY storecode, pdate "; $result = Yii::app()->db->createCommand($sql)->queryAll(); if ($result != NULL && count($result) > 0) { // 設定資料 $colAry = $this->setRpt10Data($result, $title); Yii::app()->user->setFlash('success', '以日期區間' . $qry_date . ' 查詢成功!合計' . count($colAry) . '筆資料'); // 重新排序 $colAry = $this->reorderRpt10Data($colAry); } else { Yii::app()->user->setFlash('error', '以日期區間' . $qry_dateS . ' 查無資料'); } unset($result); } else { Yii::app()->user->setFlash('notice', '年月為資訊系統中有業績的日期, 本報表之業績為門市實際營業額!'); } $com = new ComFunction(); $holidays = array(); $result = $com->getHolidayByYearMonth($qry_date); if (isset($result)) { $holidays = $result; } // 用以計算開始/結束時間之變數 $time_end = microtime(true); $computetime = $time_end - $time_start; try { $this->render('rpt10', array('dmAry' => $dmAry, 'qry_date' => $qry_date, 'qry_area' => $qry_area, 'qry_store' => $qry_store, 'storeary' => $storeary, 'col' => $col, 'title' => $title, 'colAry' => $colAry, 'holidays' => $holidays, 'computetime' => $computetime)); } catch (Exception $exc) { echo $exc->getTraceAsString(); } }
/** * */ 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)); }