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');
}
