public function actionProcessreport()
{
$running = \backend\models\SysProcessRunning::find()->one();
if ($running->is_running == 'false') {
//$running->is_running = 'true';
//$running->update();
//ใส่ store;
$this->call("start_process", NULL);
$bdg = '2014-09-30';
$model = \backend\models\Sysconfigmain::find()->one();
if ($model) {
$bdg = $model->note2;
}
$bdg = "'" . $bdg . "'";
$this->call("merge_newborncare", NULL);
$this->call("clear_upload_error", NULL);
$this->call("clear_null_hospcode", NULL);
//$this->call("cal_chart_dial_1", $bdg);
$this->call("cal_chart_dial_2", $bdg);
$this->call("cal_chart_dial_3", $bdg);
$this->call("cal_chart_dial_4", NULL);
//$this->call("cal_chart_dial_5", NULL);
$this->call("cal_chart_dial_6", NULL);
$this->call("cal_ncd_cholesteral_colorchart", $bdg);
$this->call("cal_ncd_nocholesteral_colorchart", $bdg);
$this->call("cal_pyramid_level_1", $bdg);
$this->call("cal_pyramid_level_2");
$this->call("cal_pyramid_level_3");
$this->call("cal_sys_person_type");
$y = date('Y');
$this->call("cal_count_service", $y - 1);
$this->call("cal_count_service", $y);
$this->call("cal_rpt_visit_oldman", $y - 1);
$this->call("cal_rpt_visit_oldman", $y);
//รายงานแผนไทย-knott
$this->call("cal_rpt_panth_visit_ratio", $y - 1);
$this->call("cal_rpt_panth_visit_ratio", $y);
$this->call("cal_rpt_panth_drug_value", $y - 1);
$this->call("cal_rpt_panth_drug_value", $y);
$this->call("cal_rpt_cervical_cancer_screening", $y - 1);
$this->call("cal_rpt_cervical_cancer_screening", $y);
$this->call("cal_rpt_breast_cancer_screening", $y - 1);
$this->call("cal_rpt_breast_cancer_screening", $y);
// sos store
$this->call("cal_chart_dial_7", $bdg);
$this->call("cal_chart_dial_8", $bdg);
$this->call("cal_chart_dial_9", NULL);
$this->call("end_process", NULL);
//
//จบใส่ store
//$running->is_running = 'false';
// $running->update();
}
}
public function create_event()
{
$this->exec_sql("SET GLOBAL event_scheduler = ON;");
$this->exec_sql("DROP EVENT IF EXISTS event1;");
$this->exec_sql("DROP EVENT IF EXISTS event2;");
$this->exec_sql("DROP EVENT IF EXISTS event3;");
$this->exec_sql("DROP EVENT IF EXISTS event4;");
$bdg = '2014-09-30';
$model = \backend\models\Sysconfigmain::find()->one();
if ($model) {
$bdg = $model->note2;
}
$bdg = "'" . $bdg . "'";
$y = date('Y');
$t = SysSetTime::find()->one();
if (count($t) > 0) {
$date = date('Y-m-d');
$time = $t->event_time;
$days = $t->days;
$sql = "CREATE EVENT event1\r\n ON SCHEDULE EVERY '{$days}' DAY\r\n STARTS '{$date} {$time}'\r\n DO BEGIN\n\n";
$sql .= $this->call("start_process", NULL);
$sql .= $this->call("clear_import_not_success", NULL);
$sql .= $this->call("clear_null_hospcode", NULL);
$sql .= $this->call("clear_upload_error", NULL);
$sql .= $this->call("merge_newborncare", NULL);
$sql .= $this->call("cal_chart_dial_1", $bdg);
$sql .= $this->call("cal_chart_dial_2", $bdg);
$sql .= $this->call("cal_chart_dial_3", $bdg);
$sql .= $this->call("cal_chart_dial_4", NULL);
$sql .= $this->call("cal_chart_dial_5", NULL);
$sql .= $this->call("cal_chart_dial_6", NULL);
$sql .= $this->call("cal_ncd_cholesteral_colorchart", $bdg);
$sql .= $this->call("cal_ncd_nocholesteral_colorchart", $bdg);
$sql .= $this->call("cal_pyramid_level_1", $bdg);
$sql .= $this->call("cal_pyramid_level_2");
$sql .= $this->call("cal_pyramid_level_3");
$sql .= $this->call("cal_sys_person_type");
$sql .= $this->call("cal_count_service", $y - 1);
$sql .= $this->call("cal_count_service", $y);
$sql .= $this->call("cal_rpt_visit_oldman", $y - 1);
$sql .= $this->call("cal_rpt_visit_oldman", $y);
//รายงานแผนไทย-knott
$sql .= $this->call("cal_rpt_panth_visit_ratio", $y - 1);
$sql .= $this->call("cal_rpt_panth_visit_ratio", $y);
$sql .= $this->call("cal_rpt_panth_drug_value", $y - 1);
$sql .= $this->call("cal_rpt_panth_drug_value", $y);
$sql .= $this->call("cal_rpt_cervical_cancer_screening", $y - 1);
$sql .= $this->call("cal_rpt_cervical_cancer_screening", $y);
//จบ ใส่ store
$sql .= $this->call("end_process", NULL);
$sql .= "\nEND;";
$this->exec_sql($sql);
}
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Sysconfigmain::find();
$dataProvider = new ActiveDataProvider(['query' => $query]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id]);
$query->andFilterWhere(['like', 'provcode', $this->provcode])->andFilterWhere(['like', 'distcode', $this->distcode])->andFilterWhere(['like', 'district_code', $this->district_code])->andFilterWhere(['like', 'district_name', $this->district_name])->andFilterWhere(['like', 'note1', $this->note1])->andFilterWhere(['like', 'note2', $this->note2])->andFilterWhere(['like', 'note3', $this->note3])->andFilterWhere(['like', 'note4', $this->note4])->andFilterWhere(['like', 'note5', $this->note5]);
return $dataProvider;
}
public function actionReport1()
{
$model = \backend\models\Sysconfigmain::find()->one();
$bdg = $model->note2;
$date1 = "2014-10-01";
$date2 = date('Y-m-d');
if (Yii::$app->request->isPost) {
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];
}
$sql = "select h.hoscode as hospcode ,h.hosname as hospname,\r\n(SELECT hos_target from\r\n (select person.hospcode , count(distinct person.pid) as hos_target from person \r\n where person.discharge = '9' and person.typearea in ('1', '3') and person.nation ='099' \r\n and (TIMESTAMPDIFF(YEAR,person.birth,'{$bdg}') >= 35 ) group by person.hospcode ) as t\r\nwhere t.hospcode = h.hoscode\r\n) as target ,\r\n(SELECT hos_chronic from \r\n (select person.hospcode,count(distinct(person.pid)) as hos_chronic from chronic \r\n inner join person on chronic.hospcode = person.hospcode and chronic.pid = person.pid \r\n where person.discharge = '9' and person.typearea in ('1', '3') and person.nation ='099' and ((chronic.chronic between 'E10' and 'E149') or (chronic.chronic between 'I10' and 'I159')) \r\n and (TIMESTAMPDIFF(YEAR,person.birth,'{$bdg}') >= 35 ) group by person.hospcode) as c\r\nwhere c.hospcode = h.hoscode\r\n) as chronic,\r\n(SELECT hos_doit from\r\n (select person.hospcode,count(distinct(person.pid)) as hos_doit from ncdscreen \r\n inner join person on ncdscreen.hospcode = person.hospcode and ncdscreen.pid = person.pid \r\n where person.discharge = '9' and person.typearea in ('1', '3') and person.nation ='099' and date_serv between '{$date1}' and '{$date2}' \r\n and (TIMESTAMPDIFF(YEAR,person.birth,date_serv) >= 35 ) group by person.hospcode) as r\r\nwhere r.hospcode = h.hoscode\r\n) as result \r\nfrom chospital_amp h\r\norder by distcode,hoscode asc";
try {
$rawData = \Yii::$app->db->createCommand($sql)->queryAll();
} catch (\yii\db\Exception $e) {
throw new \yii\web\ConflictHttpException('sql error');
}
$dataProvider = new \yii\data\ArrayDataProvider(['allModels' => $rawData, 'pagination' => FALSE]);
return $this->render('report1', ['dataProvider' => $dataProvider, 'sql' => $sql, 'date1' => $date1, 'date2' => $date2]);
}
<?php
use yii\helpers\Html;
use backend\models\Sysconfigmain;
/* @var $this yii\web\View */
$this->title = 'เกียวกับ';
$this->params['breadcrumbs'][] = $this->title;
$sys = Sysconfigmain::find()->one();
?>
<div class="site-about">
<h1>ผู้ดูแลระบบ</h1>
<p>- <?php
echo $sys->note1;
?>
</p>
</div>
<hr>
<div class="site-about">
<h3>ทีมพัฒนาโปรแกรม และรายงาน</h3>
<p>- <?php
echo Html::a('คุณอุเทน จาดยางโทน', 'https://fb.com/tehnn', ['target' => '_blank']);
?>
(สสจ.พิษณุโลก) Project Manager </p>
<p>- <?php
echo Html::a('คุณสุพัฒนา ปิงเมือง', 'https://fb.com/kukks205', ['target' => '_blank']);
?>
(รพสต.ผักตบ อ.หนองหาน จ.อุดรธานี) Co-Programmer</p>
<p>- <?php
echo Html::a('คุณสุจินต์ สุกกล้า', 'https://fb.com/jub.wifi', ['target' => '_blank']);
?>
/**
* Finds the Sysconfigmain model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Sysconfigmain the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Sysconfigmain::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
public function actionReport10()
{
//hdc_ncd_ht_ควบคุมความดันได้ดี.sql
$model = \backend\models\Sysconfigmain::find()->one();
$bdg = $model->note2;
$date1 = "2014-10-01";
$date2 = date('Y-m-d');
if (Yii::$app->request->isPost) {
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];
}
$sql = "select h.hoscode as hospcode ,concat(provcode,distcode,subdistcode,mu) as areacode,h.hosname as hospname,\n(SELECT hos_chronic from \n (select person.hospcode,count(distinct(person.pid)) as hos_chronic from chronic \n inner join person on chronic.hospcode = person.hospcode and chronic.pid = person.pid \n where person.discharge = '9' and person.typearea in ('1', '3') and person.nation ='099' and (chronic.chronic between 'I10' and 'I15') \n and (TIMESTAMPDIFF(YEAR,person.birth,'{$bdg}') >= 35 ) group by person.hospcode) as c\nwhere c.hospcode = h.hoscode\n) as target,\n(SELECT hos_doit from\n (select person.hospcode,count(distinct(person.pid)) as hos_doit from chronic \n inner join person on chronic.hospcode = person.hospcode and chronic.pid = person.pid \n inner join chronicfu on person.hospcode = chronicfu.hospcode and person.pid = chronicfu.pid \n where person.discharge = '9' and person.typearea in ('1', '3') and person.nation ='099' and (chronic.chronic between 'I10' and 'I15')\n and (chronicfu.sbp <140 and chronicfu.dbp < 90) and (chronicfu.date_serv BETWEEN '{$date1}' and '{$date2}' ) \n and (TIMESTAMPDIFF(YEAR,person.birth,chronicfu.date_serv) >= 35 ) group by person.hospcode) as r\nwhere r.hospcode = h.hoscode\n) as result \n\nfrom chospital_amp h\norder by distcode,hoscode asc";
try {
$rawData = \Yii::$app->db->createCommand($sql)->queryAll();
} catch (\yii\db\Exception $e) {
throw new \yii\web\ConflictHttpException('sql error');
}
$dataProvider = new \yii\data\ArrayDataProvider(['allModels' => $rawData, 'pagination' => FALSE]);
for ($i = 0; $i < count($rawData); $i++) {
$hospname[] = $rawData[$i]['hospname'];
if ($rawData[$i]['result'] == NULL or $rawData[$i]['target'] == NULL) {
$persent1[] = 0;
} else {
$persent1[] = $rawData[$i]['result'] / $rawData[$i]['target'] * 100;
}
// $persent1[] = sum($rawData) ;
}
return $this->render('report10', ['dataProvider' => $dataProvider, 'sql' => $sql, 'date1' => $date1, 'date2' => $date2, 'hospname' => $hospname, 'persent1' => $persent1]);
}