/** * 動態取得區域門市連動 * * 判斷條件為 區域id * * 可接受二種型態 ( 二種型態只能擇一) * 1. 一般查詢欄位 ( 比如: qry_area ) * url'=>CController::createUrl('tbsCom/dynamicstores',array('update'=>'qry_area')), * 2. MODEL欄位 ( 比如: TbaLog 的model, 欄位是 storecode ) * 'url'=>CController::createUrl('tbsCom/dynamicstores',array('model'=>'TbaLog','column'=>'storecode','empty'=>FALSE)), //url to call. * 3. 若結果第一列不想為空, 再傳入empty變數, 範例如下 * 'url'=>CController::createUrl('tbsCom/dynamicstores',array('empty'=>FALSE)) * 4. 若要加入權限判斷 * 'url'=>CController::createUrl('tbsCom/dynamicstores',array('right'=>TRUE)) */ public function actionDynamicstores() { // 第一列預設為空 $empty = TRUE; // 第一列是否為空 if (isset($_GET['empty'])) { $empty = $_GET['empty']; } // 由異動欄位來的值 $areaid = ''; if (isset($_GET['model']) && isset($_GET['column'])) { $areaid = $_POST[$_GET['model']][$_GET['column']]; } elseif (isset($_GET['update'])) { $areaid = $_POST[$_GET['update']]; } // 依傳入之areaid來查詢對應門市, 並且要已啟用 $stores = array(); if ($areaid == '') { $stores = TbsStore::model()->findAll(); } else { $stores = TbsStore::model()->findAllByAttributes(array(), $condition = "area_id = :id and opt1 = '1' order by storecode ", $params = array(':id' => (int) $areaid)); } // 取出店編號對應店名 $data = CHtml::listData($stores, 'storecode', 'storename'); // 回傳至畫面 // 連動結果第一筆為空 if ($empty) { echo CHtml::tag('option', array('value' => ''), '選擇門市', true); } foreach ($data as $value => $name) { echo CHtml::tag('option', array('value' => $value), CHtml::encode($name), true); } }
<table> <tr><td> 區域:<?php if (isset($area)) { echo $area->areaname; } ?> </td> <td> 門市: <?php $stores = array(); if (isset($qry_area) && $qry_area != '') { $stores = TbsStore::model()->findAllByAttributes(array(), $condition = "area_id = '{$qry_area}' AND opt1=1 ORDER BY id"); } else { $stores = TbsStore::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')); } echo CHtml::dropDownList('qry_store', '', CHtml::listData($stores, 'storecode', 'storename'), array('empty' => '選擇門市', 'options' => array($qry_store => array('selected' => 'selected')))); ?> </td> <td>員編:<input size="10" type="text" name="qry_empno" id="qry_empno" value="<?php echo $qry_empno; ?> " /></td> <td>姓名:<input size="10" type="text" name="qry_empname" id="qry_empname" value="<?php echo $qry_empname; ?> " /></td> <td>年月:
public function actionRpt14() { //用以計算開始/結束時間之變數 $time_start = microtime(true); //查詢的年月 $qry_date = date('Ym'); //區域 $qry_area = ""; //顯示銷售情報 $check_all = 0; if (isset($_POST['qry_date'])) { $qry_date = $_POST['qry_date']; } if (isset($_POST['qry_area'])) { $qry_area = $_POST['qry_area']; } if (isset($_POST['check_all'])) { $check_all = $_POST['check_all']; } // 取得欄位 $col = $this->getRpt14Col($check_all); // 取得表頭 $title = $this->getRpt14Title(); // 輸出在畫面上的陣列 $colAry = 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); //刪除區域重複值 $unique_result = array_unique($storeAreaId); //排序 sort($unique_result); //區域ID陣列轉字串,為了sql in查詢使用 $_str = " ' " . implode(" ',' ", $unique_result) . " ' "; //各區排名array $area_rank_Ary = array(); //各區業績array $area_perform_Ary = array(); //各區支出array $area_output_Ary = array(); $sql = ''; $sql = "SELECT c.area_id , c.total,c.output, COUNT(d.total) rank from (SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,sum(a.total) as total,sum(a.output) as output\n FROM tbp_perform AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n WHERE mid(a.pdate,1,6)={$qry_date}\n and b.area_id in ({$_str})\n\n GROUP BY b.area_id) c , (SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,sum(a.total) as total,sum(a.output) as output\n FROM tbp_perform AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n WHERE mid(a.pdate,1,6)={$qry_date}\n and b.area_id in ({$_str})\n\n GROUP BY b.area_id) d\n\n WHERE c.total< d.total OR (c.total=d.total AND c.area_id= d.area_id) \n GROUP BY c.area_id, c.total \n ORDER BY c.total DESC, c.area_id DESC;"; $all_result = Yii::app()->db->createCommand($sql)->queryAll(); if ($all_result > 0) { foreach ($all_result as $value) { $area_rank_Ary[$value['area_id']] = $value['rank']; $area_perform_Ary[$value['area_id']] = $value['total']; $area_output_Ary[$value['area_id']] = $value['output']; } unset($all_result); } //切割出年份 $tmp_year = substr($qry_date, 0, 4); //切割出月份 $tmp_mon = substr($qry_date, 4, 2); //所選擇當月的前一個月 $last_month = date("Ym", mktime(24, 0, 0, $tmp_mon - 1, 0, $tmp_year)); //所選擇當月的去年同個月 $ly_same_month = date("Ym", mktime(24, 0, 0, $tmp_mon - 12, 0, $tmp_year)); //前月區業績array $last_region_total = array(); //前月區支出array $last_region_output = array(); //上個月區業績及支出SQL $regin_sql = ''; $regin_sql = "SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,sum(a.total) as total,sum(a.output) as output\n FROM tbp_perform AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n WHERE mid(a.pdate,1,6)={$last_month}\n and b.area_id in ({$_str})\n GROUP BY b.area_id"; $last_region_result = Yii::app()->db->createCommand($regin_sql)->queryAll(); if ($last_region_result > 0) { foreach ($last_region_result as $value) { $last_region_total[$value['area_id']] = $value['total']; $last_region_output[$value['area_id']] = $value['output']; } unset($last_region_result); } //去年同月區業績array $ly_region_total = array(); //去年同月區支出array $ly_region_output = array(); //去年同月區業績及支出SQL $ly_regin_sql = ''; $ly_regin_sql = "SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,sum(a.total) as total,sum(a.output) as output\n FROM tbp_perform AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n WHERE mid(a.pdate,1,6)={$ly_same_month}\n and b.area_id in ({$_str})\n GROUP BY b.area_id"; $ly_region_result = Yii::app()->db->createCommand($ly_regin_sql)->queryAll(); if ($ly_region_result > 0) { foreach ($ly_region_result as $value) { $ly_region_total[$value['area_id']] = $value['total']; $ly_region_output[$value['area_id']] = $value['output']; } unset($ly_region_result); } // 查詢用的SQL $qryStr = ''; //如果只選區域,就選該區域 if ($qry_area != '') { $qryStr = "and b.area_id in({$qry_area})"; //如果都沒有選,就全部選出來 } else { $qryStr = "and b.area_id in({$_str})"; } $empAry = array(); $last_shampoo_Ary = array(); $sql2 = ''; $sql3 = ''; $sql4 = ''; $sql5 = ''; if (isset($_POST['qry'])) { //算區域剪、洗、助等.... tbp_perform_emp_log $sql2 = "(SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,a.serviceno,sum(a.num) as num\n FROM tbp_perform_emp_log AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n\n WHERE mid(a.pdate,1,6)={$qry_date}\n {$qryStr}\n GROUP BY b.area_id ,a.serviceno)"; //算區域剪、洗、助等.... tbp_perform_log $sql3 = "(SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,a.serviceno,sum(a.num) as num\n FROM tbp_perform_log AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n\n WHERE a.num!=0 and mid(a.pdate,1,6)={$qry_date}\n {$qryStr}\n GROUP BY b.area_id ,a.serviceno)"; $sql2 = $sql2 . "UNION" . $sql3; //合併 $sql2 = $sql2 . "order by area_id ,serviceno"; $result = Yii::app()->db->createCommand($sql2)->queryAll(); //前月各區=>算剪、洗、助等.... tbp_perform_emp_log $sql4 = "(SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,a.serviceno,sum(a.num) as num\n FROM tbp_perform_emp_log AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n\n WHERE mid(a.pdate,1,6)={$last_month}\n {$qryStr}\n GROUP BY b.area_id ,a.serviceno)"; //前月各區=>算剪、洗、助等.... tbp_perform_log $sql5 = "(SELECT mid(a.pdate,1,6) AS pdate,b.area_id ,a.serviceno,sum(a.num) as num\n FROM tbp_perform_emp_log AS a INNER JOIN tbs_store AS b \n ON a.storename=b.storename\n\n WHERE a.num!=0 and mid(a.pdate,1,6)={$last_month}\n {$qryStr}\n GROUP BY b.area_id ,a.serviceno)"; $sql4 = $sql4 . "UNION" . $sql5; //合併 $sql4 = $sql4 . "order by area_id ,serviceno"; $last_result = Yii::app()->db->createCommand($sql4)->queryAll(); if ($last_result > 0) { foreach ($last_result as $last_areaData) { $row = array(); // 區域編號 $row['area_id'] = $last_areaData['area_id']; // 服務項目 $row['serviceno'] = $last_areaData['serviceno']; //數量 $row['num'] = $last_areaData['num']; array_push($last_shampoo_Ary, $row); } } $last_shampoo_Ary = $this->setlastAreaAry($last_shampoo_Ary); unset($last_result); //紀錄前個月洗髮精數量 $shampoo = array(); //紀錄前個月髮油數量 $hair_oil = array(); //紀錄前個月髮雕數量 $lotion = array(); foreach ($last_shampoo_Ary as $key => $storeData) { $row = array(); // 洗髮精(含少收) = N041 + N042 + N043 + X12 + X13 + X14 $row['shampoo'] = 0; if (isset($storeData['N041'])) { $row['shampoo'] = $storeData['N041']; } if (isset($storeData['N042'])) { $row['shampoo'] = $row['shampoo'] + $storeData['N042']; } if (isset($storeData['N043'])) { $row['shampoo'] = $row['shampoo'] + $storeData['N043']; } if (isset($storeData['X12'])) { $row['shampoo'] = $row['shampoo'] + $storeData['X12']; } if (isset($storeData['X13'])) { $row['shampoo'] = $row['shampoo'] + $storeData['X13']; } if (isset($storeData['X14'])) { $row['shampoo'] = $row['shampoo'] + $storeData['X14']; } $shampoo[$storeData['area_id']] = $row['shampoo']; // 髮油 $row['hair_oil'] = 0; if (isset($storeData['N051'])) { $row['hair_oil'] = $storeData['N051']; } if (isset($storeData['X15'])) { $row['hair_oil'] = $row['hair_oil'] + $storeData['X15']; } $hair_oil[$storeData['area_id']] = $row['hair_oil']; // 髮雕 $row['lotion'] = 0; if (isset($storeData['N052'])) { $row['lotion'] = $storeData['N052']; } if (isset($storeData['X16'])) { $row['lotion'] = $row['lotion'] + $storeData['X16']; } $lotion[$storeData['area_id']] = $row['lotion']; } if ($result > 0) { foreach ($result as $areaData) { $row = array(); // 日期 $row['pdate'] = $areaData['pdate']; // 營業區ID $row['areaid'] = $areaData['area_id']; // 營業區 $row['area'] = $areaAry[$areaData['area_id']]; // 服務項目 $row['serviceno'] = $areaData['serviceno']; //數量 $row['num'] = $areaData['num']; array_push($empAry, $row); } } //if($result>0){ $empAry = $this->setAreaAry($empAry); unset($result); } foreach ($empAry as $key => $areaData) { $row = array(); // 年月 $row['ym'] = $areaData['pdate']; // 營業區ID $row['areaid'] = $areaData['areaid']; // 營業區 $row['area'] = $areaData['area']; // 設定各欄位業績算法及總金額、總支出 $row = $this->setRpt13RowData($row, $areaData); // 區業績 $row['perform'] = isset($area_perform_Ary[$areaData['areaid']]) ? (double) $area_perform_Ary[$areaData['areaid']] : ''; // 同期區業績 $row['same_period'] = isset($ly_region_total[$areaData['areaid']]) ? (double) $ly_region_total[$areaData['areaid']] : ''; // 支出 $row['output'] = isset($area_output_Ary[$areaData['areaid']]) ? (double) $area_output_Ary[$areaData['areaid']] : ''; //排名 $row['rank'] = isset($area_rank_Ary[$areaData['areaid']]) ? $area_rank_Ary[$areaData['areaid']] : ''; //業績成長 $row['perform_grow_up'] = isset($last_region_total[$areaData['areaid']]) ? $row['perform'] - $last_region_total[$areaData['areaid']] : ''; //支出成長 $row['output_grow_up'] = isset($last_region_output[$areaData['areaid']]) ? $row['output'] - $last_region_output[$areaData['areaid']] : ''; //洗髮精成長 $row['shampoo_grow_up'] = isset($shampoo[$areaData['areaid']]) ? $row['shampoo'] - $shampoo[$areaData['areaid']] : ''; //髮油成長 $row['hair_oil_grow_up'] = isset($hair_oil[$areaData['areaid']]) ? $row['hair_oil'] - $hair_oil[$areaData['areaid']] : ''; //髮雕成長 $row['lotion_grow_up'] = isset($lotion[$areaData['areaid']]) ? $row['lotion'] - $lotion[$areaData['areaid']] : ''; array_push($colAry, $row); } unset($empAry); if (isset($_POST['qry'])) { 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) . " 筆資料"); } } // CVarDumper::dump($empAry,10,true); $time_end = microtime(true); $computetime = round($time_end - $time_start, 3); $this->render('rpt14', array('qry_date' => $qry_date, 'qry_area' => $qry_area, 'check_all' => $check_all, 'col' => $col, 'title' => $title, 'colAry' => $colAry, 'computetime' => $computetime)); }
<?php echo $form->errorSummary($model); ?> <table> <tr> <td > 區域: <?php echo CHtml::dropDownList('qry_area', '', CHtml::listData(TbsArea::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'id', 'areaname'), array('prompt' => '選擇分區', 'options' => array($qry_area => array('selected' => 'selected')), 'ajax' => array('type' => 'POST', 'url' => CController::createUrl('tbsCom/dynamicstores', array('update' => 'qry_area')), 'update' => '#TbaLog_storecode'))); ?> <br> 門市: <?php echo $form->dropDownList($model, 'storecode', CHtml::listData(TbsStore::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'storecode', 'storename'), array('prompt' => '選擇門市', 'ajax' => array('type' => 'POST', 'url' => CController::createUrl('tbsCom/dynamicemps', array('model' => 'TbaLog', 'column' => 'storecode', 'empty' => FALSE)), 'update' => '#TbaLog_empno'))); ?> </td> <td> 員工 *: <?php echo $form->dropDownList($model, 'empno', CHtml::listData(TbsEmpMonth::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt3<>2')), 'empno', 'empname'), array('empty' => '選擇員工')); ?> <?php echo $form->error($model, 'empno'); ?> <br>
<?php echo $form->errorSummary($model); ?> <table> <tr> <td > 區域: <?php echo CHtml::dropDownList('qry_area', '', CHtml::listData(TbsArea::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'id', 'areaname'), array('prompt' => '選擇分區', 'options' => array($qry_area => array('selected' => 'selected')), 'ajax' => array('type' => 'POST', 'url' => CController::createUrl('tbsCom/dynamicstores', array('update' => 'qry_area')), 'update' => '#qry_store'))); ?> </td> <td> 門市: <?php echo CHtml::dropDownList('qry_store', '', CHtml::listData(TbsStore::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'storecode', 'storename'), array('prompt' => '選擇門市', 'options' => array($qry_store => array('selected' => 'selected')), 'ajax' => array('type' => 'POST', 'url' => CController::createUrl('tbaLog/dynamicemps', array('empty' => FALSE)), 'update' => '#TbaLog_empno'))); ?> </td> <td> <?php echo $form->labelEx($model, 'logday'); ?> <?php $this->widget('zii.widgets.jui.CJuiDatePicker', array('name' => 'logday', 'attribute' => 'logday', 'value' => "{$logday}", 'options' => array('showAnim' => 'slideDown', 'changeMonth' => true, 'changeYear' => true, 'dateFormat' => 'yymmdd'), 'htmlOptions' => array('style' => 'width:100px;'))); ?> <?php echo $form->error($model, 'logday'); ?>
<div class="tableBlue"> <div class="form"> <?php echo CHtml::beginForm(); ?> <table> <tr><td> 區域: <?php echo CHtml::dropDownList('qry_area', $qry_area, CHtml::listData(TbsArea::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'id', 'areaname'), array('prompt' => '選擇分區', 'ajax' => array('type' => 'POST', 'url' => CController::createUrl('tbpPerformRpt/dynamicstores'), 'update' => '#qry_store'))); ?> </td> <td> 門市: <?php echo CHtml::dropDownList('qry_store', $qry_store, CHtml::listData(TbsStore::model()->findAll(array('order' => 'id ASC', 'condition' => 'opt1=1')), 'storecode', 'storename'), array('empty' => '選擇門市', 'options' => array($qry_store => array('selected' => 'selected')))); ?> </td> <td> 區間:<?php $this->widget('zii.widgets.jui.CJuiDatePicker', array('name' => 'qry_dateS', 'attribute' => 'qry_dateS', 'value' => "{$qry_dateS}", 'options' => array('dateFormat' => 'yymmdd', 'altFormat' => 'yymmdd', 'changeMonth' => true, 'changeYear' => true), 'htmlOptions' => array('style' => 'width:80px;'))); ?> ~ <?php $this->widget('zii.widgets.jui.CJuiDatePicker', array('name' => 'qry_dateE', 'attribute' => 'qry_dateE', 'value' => "{$qry_dateE}", 'options' => array('dateFormat' => 'yymmdd', 'altFormat' => 'yymmdd', 'changeMonth' => true, 'changeYear' => true), 'htmlOptions' => array('style' => 'width:80px;'))); ?> </td>
/** * 取得門市名稱 * @param string $storecode - 門市編號 * @return string - 門市名稱 */ private function getStoreName($storecode) { $storename = ""; $store = TbsStore::model()->findByAttributes(array('storecode' => $storecode)); if (isset($store)) { $storename = $store->storename; } return $storename; }
/** * 全區業績支出 * 提供會計組人員查詢門市業績支出 */ public function actionOfficeperform() { $qry_area = ""; //區域 $qry_store = ""; // 門市 $qry_date = ""; // 每一列業績 $models = array(); // 年月下拉陣列 $dmAry = array(); if (isset($_POST['qry_area'])) { $qry_area = $_POST['qry_area']; } if (isset($_POST['qry_store'])) { $qry_store = $_POST['qry_store']; } if (isset($_POST['qry_date'])) { $qry_date = $_POST['qry_date']; } //得到起始 結束的年月日 $sqlDate = "SELECT MID( pdate, 1, 6 ) as daymonth \n FROM `tbp_perform` \n GROUP BY MID( pdate, 1, 6 ) \n ORDER BY daymonth DESC"; $dateSqlResult = Yii::app()->db->createCommand($sqlDate)->queryAll(); for ($i = 0; $i < count($dateSqlResult); $i++) { $daymonth = $dateSqlResult[$i]['daymonth']; $dmAry[$daymonth] = $daymonth; } //透過找尋對應門市 $store = TbsStore::model()->findByAttributes(array('storecode' => $qry_store)); // 門市零用金填寫日 $outputDates = array(); if (isset($_POST['qry_date'])) { $qry_date = $_POST['qry_date']; // 查詢門$models = $this->createPerformData($store, $qry_date);市業績及支出 if (isset($store)) { $models = $this->createPerformData($store, $qry_date); $outputDates = $this->getOutputDates($store->storecode, $qry_date); } else { Yii::app()->user->setFlash('error', '查無門市資料,請選擇門市!'); } } $this->render('officeperform', array('qry_area' => $qry_area, 'qry_store' => $qry_store, 'dmAry' => $dmAry, 'qry_date' => $qry_date, 'models' => $models, 'outputDates' => $outputDates)); }
/** * 取得門市陣列, array[0] 是 店編=>店名, array[1]是 店編=>區id, array[2]是 店編=>區名 * 回傳 array[0]=>array ( * '007001'=>高雄聯興, * '007002'=>高雄復興....) * 回傳 array[1]=>array ( * '007001'=>8, * '007002'=>8) * 回傳 array[2]=>array ( * '007001'=>高屏二區, * '007002'=>高屏二區) * @param boolean $inUse * @return array - array(門市名稱), array(門市區域id), array(門市區域名稱) */ public function getStoreArray($inUse) { $stores = array(); $storesArea = array(); $storesAreaName = array(); $TbsStore = array(); if ($inUse) { $TbsStore = TbsStore::model()->findAllByAttributes(array('opt1' => 1)); } else { $TbsStore = TbsStore::model()->findAll(); } $area = $this->getAreaArray($inUse); foreach ($TbsStore as $store) { $stores[$store->storecode] = $store->storename; $storesArea[$store->storecode] = $store->area_id; $storesAreaName[$store->storecode] = $area[0][$store->area_id]; } return array($stores, $storesArea, $storesAreaName); }
</td> <td width="25%"> 門市: <?php // $stores = array(); // if(isset($qry_area) && $qry_area!='') // $stores = TbsStore::model()->findAllByAttributes(array(), // $condition="area_id = '$qry_area' AND opt1=1 ORDER BY id"); // else // $stores = TbsStore::model()->findAll(array('order'=>'id ASC','condition'=>'opt1=1')); // // echo CHtml::dropDownList('qry_store','', CHtml::listData($stores,'storecode', 'storename'), // array('empty' => '選擇門市', 'options' => array($qry_store => array('selected' => 'selected'))) // ); //權限控制 echo CHtml::dropDownList('qry_store', $qry_store, TbsStore::model()->findByRight(TRUE), array('prompt' => '選擇門市', 'options' => array($qry_store => array('selected' => 'selected')))); ?> </td> <td width="10%">銷售情報:<?php echo CHtml::checkBox('check_area', $check_area); ?> </td> <td width="10%"> <input type="submit" name="qry" value="查詢"> </td> </tr> </table>
public function actionExport() { // 用以計算開始/結束時間之變數 $time_start = microtime(true); //ini_set('memory_limit', '256M'); //ini_set('max_execution_time', '300'); // Sleep for a while usleep(100); //得到起始 結束的年月日 $qry_dateS = date('Ymd'); $qry_dateE = date('Ymd'); $qry_area = ""; //區域 $qry_store = ""; // 門市 $qry_serviceno = array(); //服務項目 $serviceno_seq = array(); //服務項目順序 //預設取消匯出初始值 $cancel_export = false; //預設查詢異動初始值 $qry_move = false; 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['cancel_export'])) { $cancel_export = $_POST['cancel_export']; } if (isset($_POST['qry_move'])) { $qry_move = $_POST['qry_move']; } // 篩選條件初始的預設值 $default_serviceno = array(); $tbpParam = TbpPerformOut01::model()->findByAttributes(array('name' => 'ching_hang')); if ($tbpParam != NULL) { $default_serviceno = explode(",", $tbpParam->item); $serviceno_seq = explode(",", $tbpParam->sequence); } // 篩選條件初始的預設值 $qry_serviceno = $default_serviceno; unset($default_serviceno); // 畫面呈現的表格欄位, col是欄位名稱, title是欄位顯示的中文字 $col = array(); $title = array(); // $col = $qry_serviceno; // // // 取得預設報表抬頭 // $title = TbpPerformOut01::model()->getRptTitle($col); // // // 設定報表抬頭 // foreach ($serviceno as $row) { // $title[$row->serviceno] = $row->cname; // } //正航匯入title if (count($serviceno_seq) != 0) { // 將指定的順序放進去, 再重新排序 $col = array_combine($serviceno_seq, $qry_serviceno); ksort($col); } else { //models預設好的col $col = TbpPerformOut01::model()->getRemitCol(); } unset($serviceno_seq); unset($qry_serviceno); // 取得預設報表抬頭 $title = TbpPerformOut01::model()->getRemittitle(); // 輸出在畫面上的陣列 $colAry = array(); //店編對應區域名稱的陣列 $stores = array(); //儲存篩選出來的門市 $tbsStroes = array(); //儲存塞選出的門市的店編 $sqlStroe = array(); //如果有選門市,就只選出那一家門市 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 . ")"; } } $fileName = ''; //按下查詢 if (isset($_POST['qry']) or isset($_POST['export'])) { $sql = "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} AND num !=0 " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ,serviceno) "; $sql2 = "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_log " . "WHERE pdate BETWEEN '{$qry_dateS}' AND '{$qry_dateE}' {$qryStr} AND num !=0 " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ,serviceno) "; $sql = $sql . "UNION" . $sql2; //合併 $sql = $sql . "order by pdate, storecode, serviceno"; //一定要照pdate排序,因為loop要照日期去跑 $emplog = Yii::app()->db->createCommand($sql)->queryAll(); if ($emplog != NULL && count($emplog) > 0) { $colAry = $this->getDailyData($emplog); Yii::app()->user->setFlash('success', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查詢成功!合計' . count($colAry) . '筆資料'); } else { Yii::app()->user->setFlash('error', '以日期區間' . $qry_dateS . ' ~ ' . $qry_dateE . ' 查無資料'); } unset($emplog); if (isset($_POST['export'])) { $excelname = $qry_dateS . '-' . $qry_dateE; $fileName = $this->exportEXCEL($excelname, $col, $title, $colAry); $pdate = array(); $storecode = array(); //匯入成功 if (isset($fileName)) { if (count($colAry) > 0) { for ($i = 0; $i < count($colAry); $i++) { //給sql查詢條件 array_push($pdate, $colAry[$i]['pdate']); array_push($storecode, $colAry[$i]['storecode']); } $_pdatestr = "'" . implode("','", $pdate) . "'"; //每個日期加入單引號為了sql in查詢使用 $arr1 = array_unique(explode(',', $_pdatestr)); //將重複日期刪除只保留一個的array $_pdatestr = implode(',', $arr1); //再將不重複日期轉成字串 $_storecodestr = "'" . implode("','", $storecode) . "'"; //每個店編加入單引號為了sql in查詢使用 $arr2 = array_unique(explode(',', $_storecodestr)); //將重複店編刪除只保留一個的array $_storecodestr = implode(',', $arr2); //再將不重複店編轉成字串 //不只一筆所以用findAllByAttributes $isExist = TbpPerform::model()->findAllByAttributes(array(), $conditon = " pdate IN ({$_pdatestr}) and storecode IN ({$_storecodestr}) ORDER BY pdate DESC "); //將匯入日期、店編符合DB條件的,則opt2設定為1 if (isset($isExist) and count($isExist) > 0) { for ($i = 0; $i < count($isExist); $i++) { $isExist[$i]->opt2 = 1; $isExist[$i]->save(); } } } } unset($pdate); unset($storecode); // CVarDumper::dump($remitAry,10,true); // $col = array(); // $title = array(); $colAry = array(); // unset($colAry); $clickUrl = "<a href='" . Yii::app()->request->baseUrl . '/' . "protected" . '/' . "tmp" . '/' . $fileName . "'>點我下載</a>"; Yii::app()->user->setFlash('success', "正航匯入成功!請點擊下載 " . $clickUrl); } else { } //Yii::app()->user->setFlash('success', "查詢成功!共計 ".count($colAry)."筆資料!"); } //if(isset($_POST['qry']) OR isset($_POST['export']) //查詢異動array $qry_move_result = array(); if (isset($_POST['qry_move']) or isset($_POST['move_export'])) { $qry_move = true; //查詢日期區間被已被匯出資料 $isExist = TbpPerform::model()->findAllByAttributes(array(), $conditon = " opt2=1 AND uemp IS NOT NULL AND opt3 IS NULL ORDER BY pdate ,storecode "); foreach ($isExist as $value) { $temp = array('pdate' => $value->pdate, 'storecode' => $value->storecode, 'storename' => $value->storename, 'total' => $value->total, 'output' => $value->output, 'remit' => $value->remit, 'cemp' => $value->cemp, 'ctime' => $value->ctime, 'uemp' => $value->uemp, 'utime' => $value->utime); array_push($qry_move_result, $temp); } if (isset($qry_move_result) && count($qry_move_result) > 0) { Yii::app()->user->setFlash('success', "查出異動資料!共計 " . count($qry_move_result) . "筆資料。"); } else { Yii::app()->user->setFlash('error', "沒有任何異動資料!"); } // 查詢異動用的SQL $qrymoveStr = ''; //為了sql 使用 in(20140506,...) $dateStr = ''; //為了sql 使用 in(007002,...) $storecodeStr = ''; //日期array $sdate = array(); //店編array $sstorecode = array(); //先把$qry_move_result的日期與店編曲出來 if (count($qry_move_result) > 0) { for ($i = 0; $i < count($qry_move_result); $i++) { array_push($sdate, $qry_move_result[$i]['pdate']); array_push($sstorecode, $qry_move_result[$i]['storecode']); } //每個日期加入單引號為了sql in查詢使用 if (count($sdate) > 0) { $dateStr = "'" . implode("','", $sdate) . "'"; } //每個店編加入單引號為了sql in查詢使用 if (count($sstorecode) > 0) { $storecodeStr = "'" . implode("','", $sstorecode) . "'"; } } //if(count($qry_move_result)>0) //給sql查詢日期與店編 $qrymoveStr = $qrymoveStr . "pdate IN ({$dateStr}) AND storecode IN ({$storecodeStr})"; //sql字串清空 $sql = ''; if ($dateStr != '' and $storecodeStr != '') { $sql = "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_emp_log " . "WHERE {$qrymoveStr} AND num !=0 " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ,serviceno) "; $sql2 = "( SELECT pdate, storecode, storename, serviceno, sum(num) as num FROM tbp_perform_log " . "WHERE {$qrymoveStr} AND num !=0 " . "GROUP BY pdate, storecode, storename, serviceno ORDER BY pdate, storecode ,serviceno) "; $sql = $sql . "UNION" . $sql2; //合併 $sql = $sql . "order by pdate, storecode, serviceno"; //一定要照pdate排序,因為loop要照日期去跑 $emplog = Yii::app()->db->createCommand($sql)->queryAll(); if ($emplog != NULL && count($emplog) > 0) { $colAry = $this->getDailyData($emplog); } } unset($emplog); if (isset($_POST['move_export'])) { $excelname = "move_table"; $fileName = $this->exportEXCEL($excelname, $col, $title, $colAry); // CVarDumper::dump($remitAry,10,true); // $col = array(); // $title = array(); $colAry = array(); $clickUrl = "<a href='" . Yii::app()->request->baseUrl . '/' . "protected" . '/' . "tmp" . '/' . $fileName . "'>點我下載</a>"; Yii::app()->user->setFlash('success', "異動表匯入Excel成功!請點擊下載 " . $clickUrl); } } // if(isset($_POST['qry_move']) OR isset($_POST['move_export'])) //取消正航匯出array $cancel_result = array(); //取消匯出正航 if (isset($_POST['cancel_export'])) { $cancel_export = true; //查詢日期區間被已被匯出資料 $isExist = TbpPerform::model()->findAllByAttributes(array(), $conditon = " pdate BETWEEN {$qry_dateS} AND {$qry_dateE} AND opt2=1 ORDER BY pdate ,storecode "); foreach ($isExist as $value) { $temp = array('pdate' => $value->pdate, 'storename' => $value->storename, 'total' => $value->total, 'output' => $value->output, 'remit' => $value->remit, 'realremit' => $value->realremit, 'realmemo' => $value->realmemo, 'status' => '成功'); array_push($cancel_result, $temp); } //將日期區間DB條件的,則opt2設定為NULL if (isset($isExist) and count($isExist) > 0) { for ($i = 0; $i < count($isExist); $i++) { $isExist[$i]->opt2 = NULL; $isExist[$i]->save(); } Yii::app()->user->setFlash('success', "取消匯出成功!共計 " . count($isExist) . "筆資料!"); } else { Yii::app()->user->setFlash('error', "查無任何資料!"); } } //if(isset($_POST['cancel_export'])) // 用以計算開始/結束時間之變數 $time_end = microtime(true); $computetime = $time_end - $time_start; try { $this->render('export', array('qry_dateS' => $qry_dateS, 'qry_dateE' => $qry_dateE, 'qry_area' => $qry_area, 'qry_store' => $qry_store, 'col' => $col, 'title' => $title, 'colAry' => $colAry, 'fileName' => $fileName, 'computetime' => $computetime, 'cancel_export' => $cancel_export, 'cancel_result' => $cancel_result, 'qry_move' => $qry_move, 'qry_move_result' => $qry_move_result)); } catch (Exception $exc) { echo $exc->getTraceAsString(); } }
echo $form->error($model, 'logday'); ?> </td> <td><?php echo $form->labelEx($model, 'storename'); ?> *</td> <td> <?php // echo $form->textField($model,'storecode',array('size'=>6,'maxlength'=>6)); ?> <?php // echo $form->textField($model,'storename',array('size'=>10,'maxlength'=>10)); ?> <?php echo $form->dropDownList($model, 'storecode', CHtml::listData(TbsStore::model()->findAll(array('order' => 'storecode ASC', 'condition' => 'opt1=1')), 'storecode', 'storename')); ?> <?php echo $form->error($model, 'storecode'); ?> <?php echo $form->error($model, 'storename'); ?> </td> <td><?php echo $form->labelEx($model, 'empname'); ?> *</td> <td> <?php
public function actionInputcheck() { $qry_date = date('Ym'); if (isset($_POST['qry_date'])) { $qry_date = $_POST['qry_date']; } //得到起始 結束的年月日 $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; } // 區域 $areaAry = array(); $TbsAreas = TbsArea::model()->findAllByAttributes(array('opt1' => 1)); foreach ($TbsAreas as $area) { $areaAry[$area->id] = $area->areaname; } unset($TbsAreas); //門市區域ID $storeAreaId = array(); $TbsStores = TbsStore::model()->findAllByAttributes(array('opt1' => 1)); foreach ($TbsStores as $store) { $storeAreaId[$store->storecode] = $store->area_id; } unset($TbsStores); //紀錄資料 $colAry = array(); if (isset($_POST['fill_qry'])) { $sql = ''; //合併查詢(有支出,支出表沒填) $sql = "SELECT a.pdate,a.storecode,a.storename,a.output from\n (SELECT pdate,storecode,storename,output FROM tbp_perform \n WHERE mid(pdate,1,6)={$qry_date} and output>0 \n ORDER BY storecode ASC, pdate ASC) a\n LEFT JOIN\n (SELECT pdate,storename, sum(price) as output FROM tbp_output_log \n WHERE mid(pdate,1,6)={$qry_date} and price>0 \n GROUP BY pdate,storecode \n ORDER BY storecode ASC, pdate ASC) b\n ON a.storename=b.storename and a.pdate=b.pdate\n WHERE b.storename is null and b.pdate is null\n ORDER BY a.storecode , a.pdate "; $result = Yii::app()->db->createCommand($sql)->queryAll(); if ($result > 0) { foreach ($result as $nofill_Data) { $row = array(); // 日期 $row['pdate'] = $nofill_Data['pdate']; //區域 $row['area'] = isset($nofill_Data['storecode']) ? $areaAry[$storeAreaId[$nofill_Data['storecode']]] : ''; // 門市名稱 $row['storename'] = $nofill_Data['storename']; //支出金額 $row['output'] = $nofill_Data['output']; array_push($colAry, $row); } } //if($result>0){ if (count($colAry) > 0) { Yii::app()->user->setFlash('success', '以下為零用支出明細未填入的門市清單!'); } else { Yii::app()->user->setFlash('error', '查詢不到未輸入支出明細門市!'); } } elseif (isset($_POST['conform_qry'])) { //金額不符合查詢 $sql = ''; //合併查詢 (金額不符查詢) $sql = "SELECT a.pdate,a.storecode,a.storename,a.output from\n (SELECT pdate,storecode,storename,output FROM tbp_perform \n WHERE mid(pdate,1,6)={$qry_date} and output>0 \n ORDER BY storecode ASC, pdate ASC) a\n INNER JOIN\n (SELECT pdate,storename, sum(price) as output FROM tbp_output_log \n WHERE mid(pdate,1,6)={$qry_date} and price>0 and opt1=1\n GROUP BY pdate,storecode \n ORDER BY storecode ASC, pdate ASC) b\n ON a.output<>b.output and a.storename=b.storename and a.pdate=b.pdate\n ORDER BY a.storecode , a.pdate"; $result = Yii::app()->db->createCommand($sql)->queryAll(); if ($result > 0) { foreach ($result as $conform_Data) { $row = array(); // 日期 $row['pdate'] = $conform_Data['pdate']; //區域 $row['area'] = isset($conform_Data['storecode']) ? $areaAry[$storeAreaId[$conform_Data['storecode']]] : ''; // 門市名稱 $row['storename'] = $conform_Data['storename']; //支出金額 $row['output'] = $conform_Data['output']; array_push($colAry, $row); } } //if($result>0){ if (count($colAry) > 0) { Yii::app()->user->setFlash('success', '以下為零用支出金額不符合門市清單!'); } else { Yii::app()->user->setFlash('error', '查詢不到金額不符合支出明細門市!'); } } // CVarDumper::dump($colAry,10,true); $this->render('inputcheck', array('qry_date' => $qry_date, 'dmAry' => $dmAry, 'colAry' => $colAry)); }