/** * 管理部報表 */ public function actionRpt08() { // 用以計算開始/結束時間之變數 $time_start = microtime(true); // Sleep for a while usleep(100); //得到起始 結束的年月日 $qry_dateS = date('Ymd'); $qry_dateE = date('Ymd'); $qry_area = ""; //區域 $qry_store = ""; // 門市 $qry_empno = ""; //員編 $qry_empname = ""; //姓名 $qry_serviceno = array(); //服務項目 $serviceno_seq = array(); //服務項目順序 $rpttype = ""; // 報表類型 $rptname = ""; // 報表名稱 if (isset($_POST['qry_dateS'])) { $qry_dateS = $_POST['qry_dateS']; } if (isset($_POST['qry_dateE'])) { $qry_dateE = $_POST['qry_dateE']; } if (isset($_POST['qry_area'])) { $qry_area = $_POST['qry_area']; } if (isset($_POST['qry_store'])) { $qry_store = $_POST['qry_store']; } if (isset($_POST['rpttype'])) { $rpttype = $_POST['rpttype']; } if (isset($_POST['rptname'])) { $rptname = $_POST['rptname']; } if (isset($_POST['qry_empno'])) { $qry_empno = $_POST['qry_empno']; } if (isset($_POST['qry_empname'])) { $qry_empname = $_POST['qry_empname']; } if (isset($_POST['qry_serviceno'])) { $qry_serviceno = $_POST['qry_serviceno']; } // 服務項目陣列(上面的篩選條件) $serviceary = array(); // 報表預設之欄位名稱 $defaultAry = TbpPerformParamRpt08::model()->getRptCol(); // 報表預設之中文名稱 $servicearyname = TbpPerformParamRpt08::model()->getRptTitle(); // 產生畫面用的篩選checkboxlist // 先取得預設. 再查詢目前已設定之服務項目 foreach ($defaultAry as $i => $value) { $serviceary[$value] = $servicearyname[$value]; } // 查目前系統已設定之服務項目 $servicedata = CHtml::listData(TbsService::model()->findAll(array('order' => 'serviceno ASC', 'condition' => 'opt1=1')), 'serviceno', 'cname'); foreach ($servicedata as $i => $data) { $serviceary[$i] = $data; } // 篩選條件初始的預設值 $default_serviceno = array(); if (count($qry_serviceno) == 0) { $tbpParam = TbpParam::model()->findByAttributes(array('param' => 'performRpt08')); if ($tbpParam != NULL) { $default_serviceno = explode(",", $tbpParam->pvalue); } else { $default_serviceno = $qry_serviceno; } } else { $default_serviceno = $qry_serviceno; } // 若有指定報表, 則以報表設定作為勾選值 if ($rptname != '') { $tbpParamRpt08 = TbpPerformParamRpt08::model()->findByAttributes(array('rptname' => $rptname)); if ($tbpParamRpt08 != NULL) { //切割字串轉成陣列 $qry_serviceno = explode(",", $tbpParamRpt08->check); //順序陣列 $serviceno_seq = explode(",", $tbpParamRpt08->sequence); } else { $qry_serviceno = $default_serviceno; } } else { // 篩選條件初始的預設值 $qry_serviceno = $default_serviceno; } unset($default_serviceno); // 畫面呈現的表格欄位, col是欄位名稱, title是欄位顯示的中文字 $col = array(); $title = array(); // 取得選取的服務項目產生欄位 $serviceno = TbsService::model()->findAllByAttributes(array(), $condition = "serviceno IN ('" . implode("','", $qry_serviceno) . "') AND opt1 = '1' ORDER BY serviceno "); // 若有報表. 而且有指定順序 if ($rptname != '' && count($serviceno_seq) != 0) { // 將指定的順序放進去, 再重新排序 $col = array_combine($serviceno_seq, $qry_serviceno); ksort($col); } else { $col = $qry_serviceno; } unset($serviceno_seq); // 取得預設報表抬頭 $title = $this->getRpt08Title($col); // 設定報表抬頭 foreach ($serviceno as $row) { $title[$row->serviceno] = $row->cname; } // 輸出在畫面上的陣列 $colAry = array(); //店編對應區域名稱的陣列 $stores = array(); //儲存篩選出來的門市 $tbsStroes = array(); //儲存塞選出的門市的店編 $sqlStroe = array(); //合計陣列 $sum = array(); foreach ($title as $key => $value) { $sum[$key] = 0; } //如果有選門市,就只選出那一家門市 if ($qry_store != '') { $tbsStroes = TbsStore::model()->findAllByAttributes(array('storecode' => $qry_store)); } elseif ($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 . ")"; } } // 員編查詢SQL $qryStrno = ''; $check_emp = TRUE; //如果有打員編 if ($qry_empno != '') { $emp = TbsEmp::model()->findByAttributes(array('empno' => $qry_empno)); if ($emp == NULL) { $check_emp = FALSE; Yii::app()->user->setFlash('error', '查無此人,請重新輸入!'); } else { $qryStrno = " AND empno = '{$qry_empno}' "; $qry_empname = $emp->empname; } } elseif ($qry_empname != '') { //先去tbsemp找出員編 $emp = TbsEmp::model()->findByAttributes(array('empname' => $qry_empname)); if ($emp == NULL) { $check_emp = FALSE; Yii::app()->user->setFlash('error', '查無此人,請重新輸入!'); } else { $qryStrno = "AND empno = '{$emp->empno}'"; $qry_empno = $emp->empno; } } //按下查詢 if (isset($_POST['qry']) && $check_emp) { $sql = "SELECT * FROM ( " . "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} {$qryStrno} " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ) "; if ($qry_empno == '' && $qry_empname == '') { $sql = $sql . "UNION ALL " . "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ) "; } $sql = $sql . " ) a order by pdate, storecode, serviceno"; //一定要照pdate排序,因為loop要照日期去跑 $emplog = Yii::app()->db->createCommand($sql)->queryAll(); if ($emplog != NULL && count($emplog) > 0) { $colAry = $this->getDailyData($emplog, $stores, $qry_empname, $qry_dateS, $qry_dateE, $sum); Yii::app()->user->setFlash('success', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查詢成功!合計' . (count($colAry) - 1) . '筆資料'); } else { Yii::app()->user->setFlash('error', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查無資料'); } unset($emplog); //按下合併查詢,則顯示日期區間各門市業績的總和 } elseif (isset($_POST['qry2']) && $check_emp) { $sql = "SELECT * FROM ( " . "( SELECT storecode, storename, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} {$qryStrno} " . "GROUP BY storecode, storename, serviceno ORDER BY storecode ) "; if ($qry_empno == '' && $qry_empname == '') { $sql = $sql . "UNION ALL " . "( SELECT storecode, storename, serviceno, sum(num) as num FROM tbp_perform_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} " . "GROUP BY storecode, storename, serviceno ORDER BY storecode ) "; } $sql = $sql . " ) a order by storecode, serviceno"; $emplog = Yii::app()->db->createCommand($sql)->queryAll(); if ($emplog != NULL && count($emplog) > 0) { $colAry = $this->getSumData($emplog, $stores, $qry_empname, $qry_dateS, $qry_dateE, $sum); Yii::app()->user->setFlash('success', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查詢成功!合計' . (count($colAry) - 1) . '筆資料'); } else { Yii::app()->user->setFlash('error', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查無資料'); } unset($emplog); } else { Yii::app()->user->setFlash('notice', '日期區間裡,起始日期不可大於結束日期!'); } // 將輸出陣列做瘦身 // $resultAry = array(); // for ($i = 0; $i < count($colAry); $i++) { // $result = array(); // for ($j = 0; $j < count($col); $j++) { // $result[$col[$j]] = $colAry[$i][$col[$j]]; // } // array_push($resultAry, $result); // } // CVarDumper::dump($resultAry,10,true); // 用以計算開始/結束時間之變數 $time_end = microtime(true); $computetime = $time_end - $time_start; try { $this->render('rpt08', array('qry_dateS' => $qry_dateS, 'qry_dateE' => $qry_dateE, 'qry_area' => $qry_area, 'qry_store' => $qry_store, 'qry_empno' => $qry_empno, 'qry_empname' => $qry_empname, 'qry_serviceno' => $qry_serviceno, 'serviceary' => $serviceary, 'rpttype' => $rpttype, 'rptname' => $rptname, 'col' => $col, 'title' => $title, 'colAry' => $colAry, 'computetime' => $computetime)); } catch (Exception $exc) { echo $exc->getTraceAsString(); } }
/** * 新增修改 門市支出明細 */ public function actionCreateandupdate() { $storecode = ''; $pdate = ''; if (isset($_GET['storecode'])) { $storecode = $_GET['storecode']; } //門市編號 if (isset($_GET['pdate'])) { $pdate = $_GET['pdate']; } //門市日期 // 是否存在(合法) $isExist = TRUE; // 門市 $store = ''; // 門市當天業績 $perform = ''; // 錯誤訊息 $errorMsg = ''; // 畫面顯示 $result = array(); //如果有門市編號及日期不為空, 就查出那一家門市資料 if ($storecode != '' && $pdate != '') { $store = TbsStore::model()->findByAttributes(array('storecode' => $storecode)); if (isset($store)) { //取得目前IP $myip = $this->getMyip(); //$myip='211.75.115.206'; //假ip,預設高雄聯興 // $myip='60.249.143.210'; // 比較登入者門市的IP,是否合法 if (!strstr($store->storeip1, $myip)) { $isExist = FALSE; $errorMsg = "錯誤!所查詢之資料不屬於貴門市 [ IP: {$myip} ] ~請洽資訊組, 分機306<br>"; } $perform = TbpPerform::model()->findByAttributes(array('pdate' => $pdate, 'storecode' => $storecode, 'opt1' => 1)); if (!isset($perform)) { $isExist = FALSE; $errorMsg = $errorMsg . "錯誤!所查詢之門市[{$store->storename}]、日期[{$pdate}]無業績資料!"; } elseif ($perform->output < 1) { $isExist = FALSE; $errorMsg = $errorMsg . "錯誤!所查詢之門市[{$store->storename}]、日期[{$pdate}]當天支出為 0 !"; } } else { $isExist = FALSE; $errorMsg = "錯誤!所查詢之門市 [ {$storecode} ] 不存在~請洽資訊組, 分機306"; } } else { $isExist = FALSE; $errorMsg = '錯誤!不正確的進入方式~請重新開啟EIP~若一直出現此情況,請洽資訊組, 分機306'; } // 檢核日期是否合乎規範 if ($isExist) { $isExist = $this->checkDate($pdate); if (!$isExist) { $errorMsg = $errorMsg . '已超過門市可修改門市支出明細時間,請聯絡會計組進行處理!!'; } } // 預設筆數, 由公用變數取得 $default_num = 0; if (isset($_POST['default_num']) && $_POST['default_num'] > 0) { $default_num = $_POST['default_num']; Yii::app()->user->setFlash('success', '修改顯示筆數成功,請依照主、次、細項點選,金額必須填入!!'); } else { $tbpParam = TbpParam::model()->findByAttributes(array('param' => 'output_num')); if ($tbpParam != NULL) { $default_num = $tbpParam->pvalue; } } //針對F5重複刷新做解決 //The second parameter on print_r returns the result to a variable rather than displaying it $RequestSignature = md5($_SERVER['REQUEST_URI'] . $_SERVER['QUERY_STRING'] . print_r($_POST, true)); if (Yii::app()->session['LastRequest'] == $RequestSignature) { //echo $RequestSignature ; // 畫面顯示 $result = TbpOutputLog::model()->findAllByAttributes(array('pdate' => $perform->pdate, 'storecode' => $perform->storecode)); } else { // 若當天有業績, 且業績大於0 if (!isset($_POST['reload']) && isset($_POST['TbpOutputLog']) && isset($perform) && $perform->output > 0) { $result = $this->createOutputData($result, $perform); } else { // 畫面顯示 $result = TbpOutputLog::model()->findAllByAttributes(array('pdate' => $perform->pdate, 'storecode' => $perform->storecode)); } Yii::app()->session['LastRequest'] = $RequestSignature; } $result_num = isset($result) ? count($result) : 0; if (isset($_POST)) { if ($result_num > 0) { Yii::app()->user->setFlash('notice', '修改細項時,仍需按照主、次、細項點選,金額必須大於0才會紀錄!!'); } else { Yii::app()->user->setFlash('notice', '請按照主、次、細項點選,金額必須大於0才會紀錄!!'); } } // 員工列數, 利用預設值產出列數 if ($result_num < $default_num) { for ($i = 0; $i < $default_num - $result_num; $i++) { // 零用金支出表紀錄 $model = new TbpOutputLog(); array_push($result, $model); } } $this->render('createandupdate', array('pdate' => $pdate, 'store' => $store, 'perform' => $perform, 'isExist' => $isExist, 'default_num' => $default_num, 'array' => $result, 'errorMsg' => $errorMsg)); }