public function actionEmptyregional()
{
$db = new Query();
$db->from('ur_l');
$db->select(['ur_l.name', 'ur_l.id', 'GROUP_CONCAT(region.name) as rname']);
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
//$db->andWhere(['like', 'log_reestr.created_at', $date]);
$db->GroupBy('ur_l.id');
$db->orderBy('rname', 'ur_l.name');
$data = $db->all();
return $this->render('empty', ['data' => $data]);
}
public static function getRegionId($id)
{
$db = new Query();
$db->from('ur_l');
$db->select('region.id');
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$db->where(['=', 'ur_l.id', $id]);
$data = $db->all();
foreach ($data as $rid) {
$data2[] = $rid[id];
}
return $data2;
}
public function actionRegion($id = null)
{
if ($id) {
$model = Geo::getOne($id);
//получчаем количество розничных точек в регионе
$col_apteki = Apteki::find()->where(['region_id' => $id])->count();
//получаем количество Юр.лиц
$col_ur_l = RegionUrL::find()->where(['id_reg' => $id])->count();
$db = new Query();
$db->from('region_ur_l');
$db->InnerJoin('ur_l', 'id_ur = ur_l.id');
$db->andWhere(['=', 'id_reg', $id]);
$db->andWhere(['=', 'ur_l.regional_id', $model['regional_id']]);
$col_ur_l_regpred = $db->count();
// $col_ur_l_regpred = RegionUrL::find()->where(['id_reg' => $id])->andWhere(['$model'=>])->count();
} else {
$model = Geo::getAll();
}
$region = new Geo();
if ($_POST['Geo']) {
$model->attributes = $_POST['Geo'];
if ($model->validate() && $model->save()) {
return $this->render('/edit/region', ['model' => $model, 'region' => $region, 'ok' => 1, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]);
}
}
if ($id) {
return $this->render('/edit/region', ['model' => $model, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]);
} else {
return $this->render('region', ['model' => $model]);
}
}
public function ListAptekiAnsver()
{
$query = new Query();
$query->select(['GROUP_CONCAT(DISTINCT(region.name)) as rname', 'COUNT(preparats_ansver.id) AS count', 'ur_l.name AS uname', 'ur_l.id']);
$query->from('ur_l');
$query->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$query->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$query->leftJoin('preparats_ansver', 'ur_l.id = preparats_ansver.id_apteka');
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
}
$query->andFilterWhere(['=', 'farmopeka', '1']);
$query->groupBy('ur_l.id');
$query->orderBy('rname', 'ur_l.name');
// $query->InnerJoin('apteki', 'preparats_ansver.id_o=preparats_ansver.id');
return $query->all();
}
public static function generateTablePdf($id)
{
$mains = ValidationMain::find()->orderBy('npp')->all();
$html1 = "";
$html1 .= "\n<style>\nbody{\n\n \n font-size: 16px;\n font-family: 'open', sans-serif;\n\n\n \n\n}\n\nspan{\nfont-size: 17px;\nfont-family: 'roboto', sans-serif;\n}\n\ntd{\nbackground-color:#fff;\nfont-size: 14px;\nfont-family: 'open', sans-serif;\n\n}\n.main td{\nbackground-color:#64b5f6;\nfont-size: 15px;\nfont-family: 'roboto', sans-serif;\n}\n.main2 td{\nbackground-color:#87cefa;\n}\n.header{\nline-height:30px;\n}\nh1,h2,h3,h4{\nfont-family: 'roboto', sans-serif;\n color: #c62828;\n\n\n}\n\n</style>\n\n<body>\n<div id=\"background\">\n<div align='center'><img src=\"http://sojuzpharma.ru/images/logo_04.jpg\"></div><br>";
$table = "";
$priceAll = 0;
// $table.= "<pagebreak />";
foreach ($mains as $main) {
$params = ValidationParams::find()->where(['validation_main_id' => $main['id']])->all();
$table2 = '';
foreach ($params as $param) {
$err = null;
//$violations = Validationviolation::find()->where(['validation_params_id' => $param['id']])->all();
$query = new Query();
$query->select('validation_violation.name,validation_violation.price');
$query->InnerJoin('validation_report_violation', 'validation_report_violation.`violation_id`=validation_violation.id');
$query->where(['report_id' => $id]);
$query->andWhere(['validation_params_id' => $param['id']]);
$query->from('validation_violation');
$val = $query->all();
if (count($val) > 0) {
$table2 .= "";
$table2 .= "<tr>";
$table2 .= "<td rowspan='" . count($val) . "' width=10.5%>" . $param['name'] . "</td>";
$table2 .= "<td width='15%'>" . $val[0]['name'] . "</td>";
if ($val[0]['price'] == "") {
$table2 .= "<td rowspan='" . count($val) . "' align='center'><b>" . $param['price'] . "</b></td>";
$priceAll = $priceAll + $param['price'];
} else {
$table2 .= "<td align='center'><b>" . $val[0]['price'] . "<b></td>";
$priceAll = $priceAll + $val[0]['price'];
}
$table2 .= "<td rowspan='" . count($val) . "' valign=top>" . $param['nd'] . "</td>";
$table2 .= "<td rowspan='" . count($val) . "' valign=top width=45%>" . $param['sankcii'] . "</td>";
$table2 .= "</tr>\n";
$n = 0;
foreach ($val as $violation) {
if ($n != 0) {
$table2 .= "<tr nobr=\"false\">";
$table2 .= "<td>" . $violation['name'] . "</td>";
if ($violation['price'] != "") {
$table2 .= "<td align='center'><b>" . $violation['price'] . "</b></td>";
$priceAll = $priceAll + $violation['price'];
}
$table2 .= "</tr>\n";
}
$n++;
}
$err = 1;
}
}
if ($table2) {
$table .= "";
$table .= "<h4 align='center'>" . $main['name'] . "</h4>";
$table .= "<table border='1' cellpadding='3' cellspacing='0' width='100%'>";
$table .= "<thead><tr align='center' class='main'>";
$table .= "<td align='center'>Проверяемый параметр</td>";
$table .= "<td align='center'>Выявленные нарушения</td>";
$table .= "<td align='center'>Риск применения санкций для юр.лица (max)</td>";
$table .= "<td align='center'>п. НД, регламентирующий указанное в колонке 2 требование</td>";
$table .= "<td align='center'>Риск применения санкций возможный контролирующий орган, ст. КоАП и др…</td>";
$table .= "</tr></thead>\n\n";
$table .= "<tr align='center' class='main2'>";
//$table .= "<td colspan='5'>" . $main['name'] . "</td>";
$table .= "</tr>";
$table .= $table2;
$table .= "</table>";
}
}
$table .= "</div></body>";
$query = new Query();
$query->select('validation_report.`created_at`,ur_l.`name`,apteki.`address`,users.`username`');
$query->InnerJoin('apteki', 'apteki.id = validation_report.`apteka_id` ');
$query->InnerJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
$query->LeftJoin('users', 'users.id = validation_report.user_id');
$query->where(['validation_report.id' => $id]);
$query->from('validation_report');
$val = $query->one();
$dat = new \DateTime($val['created_at']);
$dat2 = $dat->format('d.m.Y');
$html2 = "";
$html2 .= "<h1 align='CENTER'>АКТ ОБСЛЕДОВАНИЯ АПТЕЧНОГО УЧРЕЖДЕНИЯ </h1>";
$html2 .= "<h4 align='CENTER'>В РАМКАХ ПРОЕКТА ФАРМОПЕКА (конфиденциально, только для внутреннего пользования) </h4>";
$html2 .= "<br><br><div class='header'>";
$html2 .= "<span>Дата проверки: </span>" . $dat2;
$html2 .= "<br><br>";
$html2 .= "<span>Юридическое лицо: </span>" . $val['name'];
$html2 .= "<br>";
$html2 .= "<span>Адрес объекта:</span> " . $val['address'];
$html2 .= "<br>";
$html2 .= "<span>ФИО инспектирующего лица: </span>" . $val['username'];
$html2 .= "<br>";
$html2 .= "<br><br><br><br>";
$html2 .= "<span>Выявлены несоответствия (графа 2) в соблюдение лицензионных условий и фармацевтического порядка на сумму <b>" . \number_format($priceAll, 0) . "</b> руб.</span>";
$html2 .= "</div>";
$html = $html1 . $html2 . $table;
return $html;
}
public static function getTable($users, $apteki, $date, $trimestr, $scroll = null)
{
$countMon = "";
//$nowdate = date('Y') . '-' . self::getFirstMonthTrimestr($trimestr) . '-01';
$now = new \DateTime($date);
$clone = $now;
// $table= $now->format( 'd-m-Y' ). "\n". $clone->format( 'd-m-Y' );
$table = "";
$table .= '<table class="table tableVisitors">
<thead>
<tr>
<th>Розничная точка</th>';
$clone->modify('-1 month');
for ($i = 0; $i < 3; $i++) {
$mon = $clone->format('n');
$table .= "<th align='center'>" . \Yii::$app->params['ruMonth'][$mon] . "</th>";
$dateSearch = $clone->format('Y-m');
//$visitors[$i] = self::find()->andFilterWhere(['LIKE', 'date', $dateSearch])->andFilterWhere(['=', 'user_id', $users])->all();
$query = new Query();
$query->select(['visitors.apteki_id', 'visitors.date_type', 'visitors.date']);
$query->from('apteki');
$query->InnerJoin('users', 'users.id = apteki.pi_id');
$query->LeftJoin('visitors', 'visitors.apteki_id = apteki.id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'users.id', $users]);
$query->andFilterWhere(['LIKE', 'visitors.date', $dateSearch]);
$query->orderBy('apteki.address');
$visitors[$i] = $query->all();
$mydate[$i] = $clone->format('Y-m');
$myMon[$i] = $clone->format('n');
$clone->modify('+1 month');
}
$table .= '</thead><tbody>';
foreach ($apteki as $apt) {
$style = "";
if ($scroll == $apt['id']) {
$style = "bgcolor='#fdf579'";
}
$table .= '<tr class="trr" id="' . $apt['id'] . '" ' . $style . '>
<td><a name="apt' . $apt['id'] . '" href="/apteki/update?id=' . $apt['id'] . '"><b>' . $apt['name'] . '</b> ' . $apt['address'] . '</a></td>';
for ($n = 0; $n < count($visitors); $n++) {
$m = null;
foreach ($visitors[$n] as $vis) {
if ($apt['id'] == $vis['apteki_id']) {
$monTemp = $myMon[$n];
if (!isset($countMon[$monTemp][1])) {
$countMon[$monTemp][1] = 0;
}
if (!isset($countMon[$monTemp][2])) {
$countMon[$monTemp][2] = 0;
}
if ($vis['date_type'] == 1) {
$alert = "warning";
$countMon[$monTemp]['1'] = $countMon[$monTemp]['1'] + 1;
}
if (date('dmY', strtotime($vis['date'])) == date('dmY')) {
//Подсетка текущей даты
$table .= '<script>
$("#' . $apt['id'] . '").css( "background", "#ddd" );
</script>';
}
if ($vis['date_type'] == 2) {
$alert = "success";
$countMon[$monTemp]['2'] = $countMon[$monTemp]['2'] + 1;
}
$table .= '<td align="center" class="' . $alert . '"><a href="/visitors/update/?date=' . $mydate[$n] . '-' . date('d', strtotime($vis['date'])) . '&apt=' . $apt['id'] . '"><span class="badge">' . date('d', strtotime($vis['date'])) . '</span></a></div>';
$m = 1;
}
}
if (!$m) {
$table .= '<td align="center" class="addnewdate">';
$table .= '<a href="/visitors/update/?date=' . $mydate[$n] . '&apt=' . $apt[id] . '"><span class="glyphicon glyphicon-flag"></span></td></a></td>';
}
}
$table .= '</tr>';
}
$table .= '</tbody>
</table><br>';
$table2 = "<br><table>";
if ($countMon) {
foreach ($countMon as $countMon2 => $val) {
$table2 .= "<tr><td><span class=\"label label-info\">" . \Yii::$app->params['ruMonth'][$countMon2] . "</span></td>";
$table2 .= "<td><span class=\"label label-warning\">" . $val[1] . "</span></td>";
$table2 .= "<td><span class=\"label label-success\">" . $val[2] . "</span></td>";
$table2 .= "</tr>";
}
}
$table2 .= "</table>";
return $table2 . $table;
}
public function actionUpdate($date = null, $apt)
{
// && $model->validate()
if (is_numeric($apt)) {
$model = Visitors::find()->andFilterWhere(['LIKE', 'date', $date])->andFilterWhere(['=', 'apteki_id', $apt])->one();
if (!$model) {
$model = new Visitors();
}
$model->apteki_id = $apt;
} else {
$model = new Visitors();
}
if (!$model->date) {
$model->date = $date;
}
if ($model->load(\Yii::$app->request->post())) {
$model->date = (int) \Yii::$app->request->post('date_y') . '-' . (int) \Yii::$app->request->post('date_m') . '-' . (int) \Yii::$app->request->post('date_d');
if (\Yii::$app->request->post('date_type')) {
$model->date_type = 2;
} else {
$model->date_type = 1;
}
if ($model->save()) {
$user_id = (int) \Yii::$app->request->post('user_id');
return $this->redirect(["/visitors/?user={$user_id}&scroll={$model->apteki_id}"]);
}
}
// $apteka = Apteki::find()->where(['id' => $apt, 'farmopeka' => '1'])->one();
$query = new Query();
$query->select(['apteki.*', 'ur_l.name']);
$query->from('apteki');
$query->InnerJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'apteki.id', $apt]);
$apteka = $query->one();
return $this->render('update', ['apteka' => $apteka, 'model' => $model]);
}
public static function All_excel($select, $where, $groupby, $count)
{
$db = new Query();
$db->from('apteki');
$select[] = 'ur_l.inn as inn2';
$select[] = 'ur_l.id as id2';
$select[] = 'region.id as rid';
$db->select($select);
$db->LeftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
//$db->Where('=', 'region.id',$where[region]);
if ($where['region']) {
$db->andWhere(['=', 'region.id', $where['region']]);
}
if ($where['regional']) {
$db->andWhere(['=', 'users.id', $where['regional']]);
}
if ($groupby['ur_l.id']) {
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$db->InnerJoin('ur_l as u2', 'ur_l.id=region_ur_l.id_ur');
$db->GroupBy('ur_l.id');
}
if (!$groupby['ur_l.id']) {
$db->LeftJoin('region', 'apteki.region_id = region.id');
}
//регионы Юр.лиц
//$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
//$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$db->LeftJoin('users', 'apteki.regional_id = users.id');
$db->LeftJoin('view_pi', 'apteki.pi_id = view_pi.id');
$db->LeftJoin('sb_site_users', 'sb_site_users.ur_l_id = ur_l.id');
// $db->GroupBy('apteki.id');
// $db->orderBy('region.name','ur_l.name','apteki.name');
$db->orderBy(['region.name' => SORT_ASC, 'ur_l.name' => SORT_ASC]);
$data = $db->all();
$countAll = $db->count();
for ($i = 0; $i < count($data); $i++) {
/*
$count = (new Query())
->from('apteki')
->where(['ur_l_id' => $data[$i]['id2']])
->count();
*/
if ($data[$i]['ur_l__farmopeka']) {
$data[$i]['ur_l__farmopeka'] = "Да";
} else {
$data[$i]['ur_l__farmopeka'] = "Нет";
}
if ($data[$i]['apteki__farmopeka']) {
$data[$i]['apteki__farmopeka'] = "Да";
} else {
$data[$i]['apteki__farmopeka'] = "Нет";
}
if ($data[$i]['ur_l__plat']) {
$data[$i]['ur_l__plat'] = "Да";
} else {
$data[$i]['ur_l__plat'] = "Нет";
}
if ($count['apteki']) {
$count2 = new Query();
$count2->from('apteki');
$count2->where(['ur_l_id' => $data[$i]['id2']]);
$count2->andWhere(['apteki.region_id' => $data[$i]['rid']]);
if (!isset($temp)) {
$temp = "";
}
if ($temp != $data[$i]['rid'] . $data[$i]['id2']) {
$counts = $count2->count();
$data[$i]['apteki'] = $counts;
$no = 1;
} else {
$data[$i]['apteki'] = "";
$data[$i]['ur_l'] = "";
$no = 2;
}
$temp = $data[$i]['rid'] . $data[$i]['id2'];
}
if ($no == 1) {
if ($count['ur_l']) {
$count2 = new Query();
$count2->from('ur_l');
$count2->where(['inn' => $data[$i]['inn2']]);
$counts = $count2->count();
$data[$i]['ur_l'] = $counts;
}
$data[$i]['count'] = $countAll;
}
}
return $data;
}