Example #1
0
<?php 
foreach (Yii::app()->user->getFlashes() as $key => $message) {
    echo '<div class="flash-' . $key . '">' . $message . "</div>\n";
}
?>
	

	<?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' => '選擇員工'));
?>
              
Example #2
0
<?php

/* @var $this TbpPerformController */
/* @var $dataProvider CActiveDataProvider */
$area = TbsArea::model()->findByPk($qry_area);
?>

<h1><?php 
if (isset($area)) {
    echo $area->areaname;
}
?>
 期中期末達成率</h1>

<div class="tableBlue">
    <?php 
echo CHtml::beginForm();
?>
    <table>
        <tr><td>
      區域:<?php 
if (isset($area)) {
    echo $area->areaname;
}
?>
      </td>
      <td>
      門市:
<?php 
$stores = array();
if (isset($qry_area) && $qry_area != '') {
Example #3
0
echo round(memory_get_usage() / 1024 / 1024, 2) . ' MB, ';
//CVarDumper::dump($cancel_result,10,true);
//        phpinfo();
?>
<br>

<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;')));
?>
    ~
 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));
 }
Example #5
0
$this->widget('zii.widgets.jui.CJuiDatePicker', array('name' => 'qry_date', 'attribute' => 'qry_date', 'language' => 'zh-tw', 'value' => "{$qry_date}", 'options' => array('dateFormat' => 'yymm', 'altFormat' => 'yymm', 'changeMonth' => true, 'changeYear' => true, 'yearRange' => '2013:2015'), 'htmlOptions' => array('style' => 'width:100px;')));
?>
    
        </td>
            
        <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'=>'選擇分區',));
//權限控制
echo CHtml::dropDownList('qry_area', $qry_area, TbsArea::model()->findByRight(TRUE), array('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  width="10%">銷售情報:<?php 
echo CHtml::checkBox('check_all', $check_all);
?>

        </td>
              
        <td>
       <input type="submit" name="qry" value="查詢">
       </td>
     
        </tr>
     </table>
Example #6
0
    echo isset($title[$col[$i]]) ? $title[$col[$i]] : '';
    echo "</th>";
}
?>
                <th ><?php 
echo isset($title[$col[count($col) - 1]]) ? $title[$col[$i]] : '';
?>
</th>
            </tr>         
        </thead>      
        
        <tbody>
            <?php 
// 分區, 每一區都顯示一下標題列
$area = '北一區';
$tbsArea = TbsArea::model()->findAll();
if ($tbsArea != null && count($tbsArea) > 0) {
    $area = $tbsArea[0]->areaname;
}
$chgColor = 1;
$chgBgColor = '#FDFF73';
foreach ($colAry as $key => $value) {
    if (isset($colAry[$key][$col[0]])) {
        if ($colAry[$key][$col[0]] == $area) {
            ?>
            <tr>              
                <?php 
            for ($i = 0; $i < count($col); $i++) {
                if (isset($colAry[$key][$col[$i]]) && $colAry[$key][$col[$i]] != '') {
                    if ($chgColor % 5 == 0) {
                        echo "<td bgcolor='{$chgBgColor}'>" . $colAry[$key][$col[$i]] . "</td>";
Example #7
0
 /**
  * 取得區域陣列, array[0] 是 id=>區域名,  array[1]是 id=>區域權限
  * 回傳 array[0]=>array (
  *          '1'=>北一區,
  *          '2'=>北二區....)
  * 回傳 array[1]=>array (
  *          '1'=>AreaManager01,
  *          '2'=>AreaManager02....)
  * @param boolean $inUse - 是否使用
  * @return array
  */
 public function getAreaArray($inUse)
 {
     $areas = array();
     $areaRight = array();
     $TbsArea = array();
     if ($inUse) {
         $TbsArea = TbsArea::model()->findAllByAttributes(array('opt1' => 1));
     } else {
         $TbsArea = TbsArea::model()->findAll();
     }
     foreach ($TbsArea as $area) {
         $areas[$area->id] = $area->areaname;
         $areaRight[$area->id] = $area->opt2;
     }
     return array($areas, $areaRight);
 }
 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();
     }
 }
 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));
 }