/**
* 管理部報表
*/
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));
}
