public function actionIndex($user = null, $date = null, $trimestr = null, $scroll = null) { if (\Yii::$app->user->identity->status == 3) { $user = \Yii::$app->user->identity->id; } //$model = Preparats::find()->where(['region_id' => $region])->orderBy('name')->all(); if ($user) { //$apteki = Apteki::find()->where(['pi_id' => $user, 'farmopeka' => '1'])->orderBy('address')->all(); $query = new Query(); $query->select(['ur_l.name', 'apteki.address', 'apteki.id']); $query->from('apteki'); $query->LeftJoin('ur_l', 'ur_l.id = apteki.ur_l_id'); $query->andFilterWhere(['=', 'apteki.farmopeka', '1']); $query->andFilterWhere(['=', 'apteki.pi_id', $user]); //$query->orderBy('ur_l.name,apteki.address'); $query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]); $apteki = $query->all(); } if (!$date) { $date = date('Y-m-01'); } // if (!trimestr) $trimestr = Visitors::getTrimestr($date); if ($user) { $table = Visitors::getTable($user, $apteki, $date, $trimestr, $scroll); } // print \Yii::$app->params['infoEmail']; //Количество привязвнных ир.лиц и розничных точек $count['ur'] = Ur::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count(); $count['apteki'] = Apteki::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count(); return $this->render('index', ['user' => $user, 'table' => $table, 'date' => $date, 'trimestr' => $trimestr, 'count' => $count, 'scroll' => $scroll]); }
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; }
/** *Отчеты по препаратам в XML * */ public function ListAptekiAnsverXml($region_id, $date = null) { $query = new Query(); $query->select(['preparats_ansver.nal', 'preparats_ansver.zakupka', 'preparats_ansver.prodaja', 'preparats.name', 'preparats.manuf', 'ur_l.name as uname', 'users.username', 'region.name as rname']); $query->from('preparats_ansver'); $query->innerJoin('preparats', 'preparats.id = preparats_ansver.id_o'); $query->innerJoin('ur_l', 'ur_l.id = preparats_ansver.id_apteka'); $query->LeftJoin('users', 'users.id = ur_l.pi_id'); // $query->LeftJoin('users as pi', 'users.id = ur_l.pi_id'); $query->LeftJoin('region', 'region.id = preparats.region_id'); // $query->innerJoin('region', 'region.id = ur_l.region_id'); $query->where(['=', 'preparats.region_id', $region_id]); 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]); } if ($date) { $query->andFilterWhere(['like', 'preparats_ansver.date', $date]); } // $query->andFilterWhere(['like', 'preparats_ansver.date', '2016-%']); return $query->all(); /** * $date_like=date("Y-m"); * $date_like2=" and pl_pi_o_ansver.date like '".$date_like."%' "; * * SELECT * pl_region.name AS rname, * pl_apteka.name AS aname, * pl_apteka.address, * pl_apteka.site_name, * pl_pi_o.name, * pl_pi_o.manuf, * pl_pi_o_ansver.nal, * pl_pi_o_ansver.zakupka, * pl_pi_o_ansver.prodaja, * pl_pi_o_ansver.date, * pl_pi.`fio` * FROM * pl_pi_o_ansver * INNER JOIN pl_pi_o ON * pl_pi_o_ansver.id_o = pl_pi_o.id * INNER JOIN pl_apteka ON * pl_pi_o_ansver.id_apteka = pl_apteka.wabc * INNER JOIN pl_region ON * pl_region.id = pl_apteka.region * LEFT JOIN pl_pi_apteki ON * pl_pi_apteki.apt_id = pl_apteka.id * LEFT JOIN pl_pi ON * pl_pi_apteki.pi_id = pl_pi.id * WHERE * pl_pi_o.is_active = 1 * $and_pr * $date_like2 * $order * * */ }
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; }
apteki.`address`, users.username, visitors.date FROM apteki INNER JOIN users ON users.`id` = apteki.pi_id LEFT JOIN visitors ON visitors.apteki_id = apteki.id WHERE apteki.id = 4581 AND visitors.date LIKE '2016%' */ //тут новый запрос $query = new Query(); $query->select(['apteki.address']); $query->from('apteki'); $query->LeftJoin('users', 'users.id = apteki.pi_id'); $query->andFilterWhere(['=', 'apteki.farmopeka', '1']); $query->andFilterWhere(['=', 'users.id', 1]); $query->orderBy('apteki.address'); ?> <br><br> <?php echo Html::SubmitButton("Сохранить", ['class' => 'btn btn-success']); ?>
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; }
public function actionModalapteki($regional_id = null, $region_id) { $regional_id = (int) $regional_id; $region_id = (int) $region_id; $query = new Query(); $query->select(['ur_l.name', 'apteki.address', 'apteki.id']); $query->from('apteki'); $query->LeftJoin('ur_l', 'ur_l.id = apteki.ur_l_id'); if ($regional_id) { $query->andFilterWhere(['=', 'apteki.regional_id', $regional_id]); } $query->andFilterWhere(['=', 'apteki.region_id', $region_id]); //$query->orderBy('ur_l.name,apteki.address'); $query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]); $return = $query->all(); $this->layout = 'modal_400'; return $this->render('modalapteki', compact(['return'])); // return $this->render('index'); }