АлександЕР21
27-02-2014, 16:38
Коллеги прошу помощи ))) Не могу создать SQL запрос имеется мед отчет (скрин прилагаю) Надо добавить туда услугу )) Добавить то по ангиологии получается, но в следствии чего отчет начинает выдавать ошибку ,а услуги перестают подсчитываться))
Следовательно вопрос как добавить запрос SQL??( код и скрин кода прилагаю ) Если дело в нем конечно. Я просто в этих базах полный ноль а сделать оч нада. Написал суть проблемы как мог)).
вот сам код))
<div cmptype="Form" oncreate="base().onCreate();" class="report_main_div" window_size="210mmx297mm">
<style>
div.report_main_div {
line-height:4.6mm;
}
div.report_main_div table.rell
{
border: 1px solid black;
border-collapse: collapse;
font-size:8pt;
}
div.report_main_div td.mell
{
padding: 3px;
border: 1px solid black;
}
div.report_main_div td.pell
{
padding: 3px;
border: 1px solid black;
text-align: center;
}
div.report_main_div td.vell
{
-moz-transform: translateY(100px) rotate(270deg);
-webkit-transform: translateY(100px) rotate(270deg);
-o-transform: translateY(100px) rotate(270deg);
padding: 3px;
border: 1px solid black;
height: 300px;
width: 25px;
}
div.report_main_div td.vellone
{
-moz-transform: translateY(80px) rotate(270deg);
-webkit-transform: translateY(80px) rotate(270deg);
-o-transform: translateY(80px) rotate(270deg);
padding: 3px;
border: 1px solid black;
height: 300px;
width: 100px;
font-size:10pt;
}
div.report_main_div td.vrell
{
-moz-transform: translateY(80px) rotate(270deg);
-webkit-transform: translateY(80px) rotate(270deg);
-o-transform: translateY(80px) rotate(270deg);
border: 1px solid black;
height: 250px;
width: 15px;
white-space:nowrap;
}
</style>
<component cmptype="Script"><![CDATA[
Form.onCreate = function()
{
setVar('START_DATE', $_GET['START_DATE']);
setVar('END_DATE', $_GET['END_DATE']);
if (empty(getVar('END_DATE'))) setVar('END_DATE',getVar('START_DATE'));
};
]]>
</component>
<component cmptype="DataSet" name="DS_MAIN">
<![CDATA[
select t1.DDATES,
nvl(t2.elektro,0) elektro,
nvl(t2.galvan,0) galvan,
nvl(t2.magnito,0) magnito,
nvl(t2.mdm,0) mdm,
nvl(t2.darson,0) darson,
nvl(t2.vibro,0) vibro,
nvl(t2.ufo,0) ufo,
nvl(t2.limfomat,0) limfomat,
nvl(t2.lazerot,0) lazerot,
nvl(t2.himavat,0) himavat,
nvl(t2.alfa,0) alfa,
nvl(t2.smt,0) smt,
nvl(t2.uzt,0) uzt,
nvl(t2.gidromas,0) gidromas,
nvl(t2.parafin,0) parafin,
nvl(t2.cryo,0) cryo,
nvl(t2.lekarst,0) lekarst,
nvl(t2.pearl,0) pearl,
nvl(t2.galvanna,0) galvanna,
nvl(t2.dush,0) dush,
nvl(t2.halo,0) halo,
nvl(t2.cnt,0) cnt,
nvl(t2.uet_count,0) uet_count
from table (cast (d_pkg_dat_tools.DATES_IN_RANGE(:START_DATE,:END_DATE,1) as D_CL_DATE)) t1,
(select trunc(vwd.visit_date) visit_date,
sum(case when ser.cid = 75863455 then 1 else 0 end) elektro,
sum(case when ser.cid = 75862906 then 1 else 0 end) galvan,
sum(case when ser.cid = 75863720 then 1 else 0 end) magnito,
sum(case when ser.cid = 75863845 then 1 else 0 end) mdm,
sum(case when ser.cid = 75863972 then 1 else 0 end) darson,
sum(case when ser.cid = 75864097 then 1 else 0 end) vibro,
sum(case when ser.cid = 75864236 then 1 else 0 end) ufo,
sum(case when ser.cid = 75864362 then 1 else 0 end) limfomat,
sum(case when ser.cid = 75864492 then 1 else 0 end) lazerot,
sum(case when ser.cid = 75864617 then 1 else 0 end) himavat,
sum(case when ser.cid = 75864787 then 1 else 0 end) alfa,
sum(case when ser.cid = 75863595 then 1 else 0 end) smt,
sum(case when ser.cid = 75864912 then 1 else 0 end) uzt,
sum(case when ser.cid = 75865057 then 1 else 0 end) gidromas,
sum(case when ser.cid = 75865199 then 1 else 0 end) parafin,
sum(case when ser.cid = 75865324 then 1 else 0 end) cryo,
sum(case when ser.cid = 75865455 then 1 else 0 end) lekarst,
sum(case when ser.cid = 75865581 then 1 else 0 end) pearl,
sum(case when ser.cid = 75865712 then 1 else 0 end) galvanna,
sum(case when ser.cid = 75865837 then 1 else 0 end) dush,
sum(case when ser.cid = 75865962 then 1 else 0 end) halo,
count(vis.uet_count) cnt,
sum(vis.uet_count) uet_count
from d_v_visit_with_direction vwd,
d_v_services ser,
d_v_visits vis
where ser.id = vwd.service
and vis.id = vwd.id
and (select pid from d_v_catalogs where id = ser.cid) = 75862766
group by trunc(vwd.visit_date)) t2
where t1.ddates = t2.visit_date(+)
order by t1.ddates
]]>
<component cmptype="Variable" name="START_DATE" src="START_DATE" srctype="var" get="var1"/>
<component cmptype="Variable" name="END_DATE" src="END_DATE" srctype="var" get="var2"/>
</component>
<table class="rell" style="table-layout:fixed; width: 100%;">
<tr>
<td class="vellone" rowspan="3" style="width: 100px; white-space:nowrap;">число</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">первичные больные</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">повторные больные</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">всего процедур</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">всего процедурных единиц</td>
<td class="pell" colspan="21">Число отпущенных процедур</td>
</tr>
<tr>
<td class="vrell">электрофорез</td>
<td class="vrell">гальванизация</td>
<td class="vrell">магнитотерапия</td>
<td class="vrell">МДМ</td>
<td class="vrell">дарсонвализация</td>
<td class="vrell">вибромассаж</td>
<td class="vrell">УФО</td>
<td class="vrell">лимфомат</td>
<td class="vrell">лазеротерапия</td>
<td class="vrell">химават</td>
<td class="vrell">альфа-капсула</td>
<td class="vrell">СМТ, интерференция, э/стимуляция</td>
<td class="vrell">УЗТ, фонофорез</td>
<td class="vrell">бесконтактный гидромассаж</td>
<td class="vrell">парафин</td>
<td class="vrell">криотерапия</td>
<td class="vrell">ванна лекарственная</td>
<td class="vrell">ванна жемчужная</td>
<td class="vrell">гальваническая ванна</td>
<td class="vrell">подводный душ-массаж</td>
<td class="vrell">галотерапия</td>
</tr>
<tr>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">4</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">1,5</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">2,5</td>
<td class="pell">4</td>
<td class="pell">3</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">2,5</td>
<td class="pell">3</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">5</td>
<td class="pell">4</td>
</tr>
<tr dataset="DS_MAIN" repeate="0">
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DDATES"/></td>
<td class="pell"></td>
<td class="pell"></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="CNT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UET_COUNT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="ELEKTRO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GALVAN"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="MAGNITO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="MDM"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DARSON"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="VIBRO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UFO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LIMFOMAT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LAZEROT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="HIMAVAT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="ALFA"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="SMT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UZT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GIDROMAS"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="PARAFIN"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="CRYO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LEKARST"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="PEARL"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GALVANNA"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DUSH"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="HALO"/></td>
</tr>
</table>
</div>
Следовательно вопрос как добавить запрос SQL??( код и скрин кода прилагаю ) Если дело в нем конечно. Я просто в этих базах полный ноль а сделать оч нада. Написал суть проблемы как мог)).
вот сам код))
<div cmptype="Form" oncreate="base().onCreate();" class="report_main_div" window_size="210mmx297mm">
<style>
div.report_main_div {
line-height:4.6mm;
}
div.report_main_div table.rell
{
border: 1px solid black;
border-collapse: collapse;
font-size:8pt;
}
div.report_main_div td.mell
{
padding: 3px;
border: 1px solid black;
}
div.report_main_div td.pell
{
padding: 3px;
border: 1px solid black;
text-align: center;
}
div.report_main_div td.vell
{
-moz-transform: translateY(100px) rotate(270deg);
-webkit-transform: translateY(100px) rotate(270deg);
-o-transform: translateY(100px) rotate(270deg);
padding: 3px;
border: 1px solid black;
height: 300px;
width: 25px;
}
div.report_main_div td.vellone
{
-moz-transform: translateY(80px) rotate(270deg);
-webkit-transform: translateY(80px) rotate(270deg);
-o-transform: translateY(80px) rotate(270deg);
padding: 3px;
border: 1px solid black;
height: 300px;
width: 100px;
font-size:10pt;
}
div.report_main_div td.vrell
{
-moz-transform: translateY(80px) rotate(270deg);
-webkit-transform: translateY(80px) rotate(270deg);
-o-transform: translateY(80px) rotate(270deg);
border: 1px solid black;
height: 250px;
width: 15px;
white-space:nowrap;
}
</style>
<component cmptype="Script"><![CDATA[
Form.onCreate = function()
{
setVar('START_DATE', $_GET['START_DATE']);
setVar('END_DATE', $_GET['END_DATE']);
if (empty(getVar('END_DATE'))) setVar('END_DATE',getVar('START_DATE'));
};
]]>
</component>
<component cmptype="DataSet" name="DS_MAIN">
<![CDATA[
select t1.DDATES,
nvl(t2.elektro,0) elektro,
nvl(t2.galvan,0) galvan,
nvl(t2.magnito,0) magnito,
nvl(t2.mdm,0) mdm,
nvl(t2.darson,0) darson,
nvl(t2.vibro,0) vibro,
nvl(t2.ufo,0) ufo,
nvl(t2.limfomat,0) limfomat,
nvl(t2.lazerot,0) lazerot,
nvl(t2.himavat,0) himavat,
nvl(t2.alfa,0) alfa,
nvl(t2.smt,0) smt,
nvl(t2.uzt,0) uzt,
nvl(t2.gidromas,0) gidromas,
nvl(t2.parafin,0) parafin,
nvl(t2.cryo,0) cryo,
nvl(t2.lekarst,0) lekarst,
nvl(t2.pearl,0) pearl,
nvl(t2.galvanna,0) galvanna,
nvl(t2.dush,0) dush,
nvl(t2.halo,0) halo,
nvl(t2.cnt,0) cnt,
nvl(t2.uet_count,0) uet_count
from table (cast (d_pkg_dat_tools.DATES_IN_RANGE(:START_DATE,:END_DATE,1) as D_CL_DATE)) t1,
(select trunc(vwd.visit_date) visit_date,
sum(case when ser.cid = 75863455 then 1 else 0 end) elektro,
sum(case when ser.cid = 75862906 then 1 else 0 end) galvan,
sum(case when ser.cid = 75863720 then 1 else 0 end) magnito,
sum(case when ser.cid = 75863845 then 1 else 0 end) mdm,
sum(case when ser.cid = 75863972 then 1 else 0 end) darson,
sum(case when ser.cid = 75864097 then 1 else 0 end) vibro,
sum(case when ser.cid = 75864236 then 1 else 0 end) ufo,
sum(case when ser.cid = 75864362 then 1 else 0 end) limfomat,
sum(case when ser.cid = 75864492 then 1 else 0 end) lazerot,
sum(case when ser.cid = 75864617 then 1 else 0 end) himavat,
sum(case when ser.cid = 75864787 then 1 else 0 end) alfa,
sum(case when ser.cid = 75863595 then 1 else 0 end) smt,
sum(case when ser.cid = 75864912 then 1 else 0 end) uzt,
sum(case when ser.cid = 75865057 then 1 else 0 end) gidromas,
sum(case when ser.cid = 75865199 then 1 else 0 end) parafin,
sum(case when ser.cid = 75865324 then 1 else 0 end) cryo,
sum(case when ser.cid = 75865455 then 1 else 0 end) lekarst,
sum(case when ser.cid = 75865581 then 1 else 0 end) pearl,
sum(case when ser.cid = 75865712 then 1 else 0 end) galvanna,
sum(case when ser.cid = 75865837 then 1 else 0 end) dush,
sum(case when ser.cid = 75865962 then 1 else 0 end) halo,
count(vis.uet_count) cnt,
sum(vis.uet_count) uet_count
from d_v_visit_with_direction vwd,
d_v_services ser,
d_v_visits vis
where ser.id = vwd.service
and vis.id = vwd.id
and (select pid from d_v_catalogs where id = ser.cid) = 75862766
group by trunc(vwd.visit_date)) t2
where t1.ddates = t2.visit_date(+)
order by t1.ddates
]]>
<component cmptype="Variable" name="START_DATE" src="START_DATE" srctype="var" get="var1"/>
<component cmptype="Variable" name="END_DATE" src="END_DATE" srctype="var" get="var2"/>
</component>
<table class="rell" style="table-layout:fixed; width: 100%;">
<tr>
<td class="vellone" rowspan="3" style="width: 100px; white-space:nowrap;">число</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">первичные больные</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">повторные больные</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">всего процедур</td>
<td class="vell" rowspan="3" style="width: 25px; white-space:nowrap;">всего процедурных единиц</td>
<td class="pell" colspan="21">Число отпущенных процедур</td>
</tr>
<tr>
<td class="vrell">электрофорез</td>
<td class="vrell">гальванизация</td>
<td class="vrell">магнитотерапия</td>
<td class="vrell">МДМ</td>
<td class="vrell">дарсонвализация</td>
<td class="vrell">вибромассаж</td>
<td class="vrell">УФО</td>
<td class="vrell">лимфомат</td>
<td class="vrell">лазеротерапия</td>
<td class="vrell">химават</td>
<td class="vrell">альфа-капсула</td>
<td class="vrell">СМТ, интерференция, э/стимуляция</td>
<td class="vrell">УЗТ, фонофорез</td>
<td class="vrell">бесконтактный гидромассаж</td>
<td class="vrell">парафин</td>
<td class="vrell">криотерапия</td>
<td class="vrell">ванна лекарственная</td>
<td class="vrell">ванна жемчужная</td>
<td class="vrell">гальваническая ванна</td>
<td class="vrell">подводный душ-массаж</td>
<td class="vrell">галотерапия</td>
</tr>
<tr>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">4</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">1,5</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">2,5</td>
<td class="pell">4</td>
<td class="pell">3</td>
<td class="pell">2,5</td>
<td class="pell">2</td>
<td class="pell">2,5</td>
<td class="pell">3</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">2</td>
<td class="pell">5</td>
<td class="pell">4</td>
</tr>
<tr dataset="DS_MAIN" repeate="0">
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DDATES"/></td>
<td class="pell"></td>
<td class="pell"></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="CNT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UET_COUNT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="ELEKTRO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GALVAN"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="MAGNITO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="MDM"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DARSON"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="VIBRO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UFO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LIMFOMAT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LAZEROT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="HIMAVAT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="ALFA"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="SMT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="UZT"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GIDROMAS"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="PARAFIN"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="CRYO"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="LEKARST"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="PEARL"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="GALVANNA"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="DUSH"/></td>
<td class="pell"><component cmptype="Label" style="color: black;" captionfield="HALO"/></td>
</tr>
</table>
</div>