1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
select row_number()over(partition by t.divisionname, t.objname order by t.sv_sort, t.objname, t.tw_time, t.worktypename, decode(t.postname,'п/машиниста',1,0), t.driver) as obj_rn
, count(*)over(partition by t.divisionname, t.objname) as obj_count
, t.divisionname, t.objname, t.worktypename, t.qty, t.driver, to_char(t.postname) as postname
, row_number()over(partition by t.divisionname, t.objname, pt.prevention_name order by t.sv_sort, t.objname, t.tw_time, t.worktypename, decode(t.postname,'п/машиниста',1,0), t.driver) as prev_rn
, count(*)over(partition by t.divisionname, t.objname, pt.prevention_name) as prev_count
, pt.prevention_name
, t.sv_sort
from (-- Наряды экскаваторов
select os.divisionid
, os.divisionname
, nvl(t1.objname, t2.objname) as objname
, t1.worktypeid, t1.worktypename
, t1.qty
, t1.tw_time
, t2.driver, t2.postname
, nvl(t1.sv_sort, t2.sv_sort) as sv_sort
from (select row_number()over(partition by t.shovid order by t.tw_time, t.worktypename) rn
, t.shovid
, t.modelname||'<br>№ '||t.shovname as objname
, t.worktypeid, t.worktypename, t.qty
, t.tw_time
, case when sv.usebynothing = 1 then 0
when sv.usebylocos_unload = 1 then 2
else 1 end as sv_sort
from (select sv.shovid, sv.shovname, sm.modelname, wt.worktypeid
, decode(wc.worktypecategoryname,'Добыча',wt.worktypename||' - добыча',wt.worktypename) as worktypename
, tvw.volume || decode(wt.isvolume, 1, ' м.куб',' т.') as qty
, null as tw_time
from task.tasks t
join shov.shovels sv on sv.shovid = t.objectid
join shov.models sm on sm.modelid = sv.modelid
join task.taskdetails td on td.taskid = t.taskid
join task.taskvolumeworks tvw on tvw.taskdetailsid = td.taskdetailid
join wt.worktypes wt on wt.worktypeid = tvw.worktypeid
left join wt.worktypecategories wc on wc.worktypecategoryid = wt.worktypecategoryid
where t.gmttimebegin = p_date_beg
and wt.worktypeid != 108189 --Автопрочие
-- union all
-- select sv.shovid, sv.shovname, sm.modelname
-- , t_t.id, t_t.name as worktypename
-- , to_char(base.times.GetLocalTime(stw.gmtbegintime,7981), 'hh24:mi') ||' - '|| to_char(base.times.GetLocalTime(stw.gmtendtime,7981), 'hh24:mi') ||' ('||
-- round((stw.gmtendtime - stw.gmtbegintime)*24,2) || ' ч.)' as qty
-- , stw.gmtbegintime as tw_time
-- from task.shovtimeworks stw
-- join task.tasks t on stw.taskid = t.taskid
-- join shov.shovels sv on sv.shovid = t.objectid
-- join shov.models sm on sm.modelid = sv.modelid
-- join task.timework_types t_t on stw.timeworkid = t_t.id
-- join base.objecttypes ot on ot.objecttypeid=t_t.objecttypeid and ot.stringcode='shov'
-- where t.gmttimebegin = p_date_beg
) t
join shov.shovels sv on sv.shovid = t.shovid
) t1
full join (
select row_number()over(partition by sv.shovid order by decode(tp.timemark,'mash',0,1), initcap(p.familyname)) rn
, sv.shovid
, sm.modelname||'<br>№ '||sv.shovname as objname
, initcap(p.familyname)||' '||substr(p.firstname, 1, 1)||'.'||substr(p.secname, 1, 1)||'.' as driver
, decode(tp.timemark,'mash','машинист','п/машиниста') as postname -----------------------------
, case when sv.usebynothing = 1 then 0
when sv.usebylocos_unload = 1 then 2
else 1 end as sv_sort
from task.tasks t
join shov.shovels sv on sv.shovid = t.objectid
join shov.models sm on sm.modelid = sv.modelid
join task.taskpersonnel tp on tp.taskid = t.taskid
join base.personnel p on p.personnelid = tp.personnelid
where t.gmttimebegin = p_date_beg
) t2 on t1.shovid = t2.shovid and t1.rn = t2.rn
left join ( select os.objectid
, max(os.divisionid)keep(dense_rank last order by os.gmtbegintime) as divisionid
, max(d.divisionname)keep(dense_rank last order by os.gmtbegintime) as divisionname
from base.objectassignment os
join base.divisions d on d.divisionid = os.divisionid
where os.gmtendtime is null
group by os.objectid
) os on os.objectid = nvl(t1.shovid, t2.shovid)
union all
-- Наряды МНУ! ЭТОТ ЗАПРОС ДОБАВИЛ Я
select dr.divisionid as DIVISIONID
, d.divisionname as DIVISIONNAME
, dr.drain_name as OBJNAME
, 103361 as WORKTYPEID
, 'Водоотлив' as WORKTYPENAME
, '08:00 - 20:00 (12 ч.)' as QTY
, null as TW_TIME
, initcap(p.familyname)||' '||SUBSTR(p.firstname,1,1)||'.'||SUBSTR(p.secname,1,1)||'.' as DRIVER
, ps.shortname as POSTNAME -- ЕСЛИ ЭТОТ СТОЛБЕЦ СДЕЛАТЬ КОСТАНТОЙ, БУДЕТ ОШИБКА ORA-06502
, 5 as SV_SORT
from mjdr_ababkov.task_people tp
join mjdr_ababkov.drainage dr on dr.drain_id = tp.drain_id
join BASE.divisions d on d.divisionid = dr.divisionid
join BASE.personnel p on p.personnelid = tp.personnelid
join BASE.posts ps on ps.postid = p.postid
where tp.gmtbegintime = p_date_beg
union all
-- Наряды бульдозеров
select nvl(t1.divisionid,
lag(t1.divisionid, t2.rn-1, 0)over(partition by t2.objname order by t2.rn)
) as divisionid
, nvl(t1.divisionname,
lag(t1.divisionname, t2.rn-1, 0)over(partition by t2.objname order by t2.rn)
) as divisionname
, nvl(t1.objname, t2.objname) as objname
, t1.id, t1.name, t1.qty
, t1.tw_time
, t2.driver
, t2.postname
, 4 as sv_sort
from (
select t.objectid
, row_number()over(partition by t.objectid order by td.gmttimebegin, twt.name) rn
, d.divisionid, d.divisionname
, bm.modelname||'<br>№ '||b.bulldozername as objname
, twt.id, twt.name
, to_char(base.times.GetLocalTime(td.gmttimebegin,7981), 'hh24:mi') ||' - '|| to_char(base.times.GetLocalTime(td.gmttimeend,7981), 'hh24:mi') ||' ('||
round((td.gmttimeend - td.gmttimebegin)*24,2) || ' ч.)' as qty
, td.gmttimebegin as tw_time
from task.tasks t
join bull.bulldozers b on b.bulldozerid = t.objectid
join bull.models bm on bm.modelid = b.modelid
join task.taskdetails td on td.taskid = t.taskid
join task.tasktimeworks tw on tw.taskdetailsid = td.taskdetailid
join task.timework_types twt on twt.id = tw.timeworkid
join base.divisions d on d.divisionid = tw.divisionid
where t.gmttimebegin = p_date_beg
) t1
full join (
select t.objectid
, row_number()over(partition by t.objectid order by decode(tp.timemark,'mash',0,1), initcap(p.familyname)) rn
, bm.modelname||'<br>№ '||b.bulldozername as objname
, nullif(initcap(p.familyname)||' '||substr(p.firstname, 1, 1)||'.'||substr(p.secname, 1, 1)||'.', ' ..') as driver
, decode(tp.timemark,'mash','машинист','п/машиниста') as postname
from task.tasks t
join bull.bulldozers b on b.bulldozerid = t.objectid
join bull.models bm on bm.modelid = b.modelid
join task.taskpersonnel tp on tp.taskid = t.taskid
join base.personnel p on p.personnelid = tp.personnelid
where t.gmttimebegin = p_date_beg
) t2 on t1.objectid = t2.objectid and t1.rn = t2.rn
union all
-- Наряды буровых
select nvl(t1.divisionid,
lag(t1.divisionid, t2.rn-1, 0)over(partition by t2.objname order by t2.rn)
) as divisionid
, nvl(t1.divisionname,
lag(t1.divisionname, t2.rn-1, 0)over(partition by t2.objname order by t2.rn)
) as divisionname
, nvl(t1.objname, t2.objname) as objname
, t1.worktypeid, t1.worktypename, t1.qty
, t1.tw_time
, t2.driver
, t2.postname
, 3 as sv_sort
from (select row_number()over(partition by t.objectid order by t.tw_time, t.worktypename) rn
, t.objectid
, t.objname
, t.worktypeid, t.worktypename, t.qty
, t.tw_time
, t.divisionid, t.divisionname
from (select t.objectid
, d.divisionid, d.divisionname
, rm.modelname||'<br>№ '||r.rigname as objname
, wt.worktypeid, wt.worktypename
, to_char(tv.volume) as qty
, td.gmttimebegin as tw_time
from task.tasks t
join rigs.rigs r on r.rigid = t.objectid
join rigs.models rm on rm.modelid = r.modelid
join task.taskdetails td on td.taskid = t.taskid
join task.taskvolumeworks tv on tv.taskdetailsid = td.taskdetailid
join wt.worktypes wt on wt.worktypeid = tv.worktypeid
join wt.worktypecategories wc on wc.worktypecategoryid = wt.worktypecategoryid
and wc.worktypecategoryname = 'Работа буровых станков'
join base.divisions d on d.divisionname = 'Участок 10'
where t.gmttimebegin = p_date_beg
union all
select t.objectid
, d.divisionid, d.divisionname
, rm.modelname||'<br>№ '||r.rigname as objname
, twt.id, twt.name
, to_char(base.times.GetLocalTime(td.gmttimebegin,7981), 'hh24:mi') ||' - '|| to_char(base.times.GetLocalTime(td.gmttimeend,7981), 'hh24:mi') ||' ('||
round((td.gmttimeend - td.gmttimebegin)*24,2) || ' ч.)' as qty
, td.gmttimebegin as tw_time
from task.tasks t
join rigs.rigs r on r.rigid = t.objectid
join rigs.models rm on rm.modelid = r.modelid
join task.taskdetails td on td.taskid = t.taskid
join task.tasktimeworks tw on tw.taskdetailsid = td.taskdetailid
join task.timework_types twt on twt.id = tw.timeworkid
join base.divisions d on d.divisionname = 'Участок 10'
where t.gmttimebegin = p_date_beg
) t
) t1
full join (
select t.objectid
, row_number()over(partition by t.objectid order by decode(tp.timemark,'rig_mash',0,1), initcap(p.familyname)) rn
, rm.modelname||'<br>№ '||r.rigname as objname
, nullif(initcap(p.familyname)||' '||substr(p.firstname, 1, 1)||'.'||substr(p.secname, 1, 1)||'.', ' ..') as driver
, decode(tp.timemark,'rig_mash','машинист','п/машиниста') as postname
from task.tasks t
join rigs.rigs r on r.rigid = t.objectid
join rigs.models rm on rm.modelid = r.modelid
join task.taskpersonnel tp on tp.taskid = t.taskid
join base.personnel p on p.personnelid = tp.personnelid
where t.gmttimebegin = p_date_beg
) t2 on t1.objectid = t2.objectid and t1.rn = t2.rn
) t
left join (select pw.worktypeid
, listagg(p.name,' ') within group (order by name)
over(partition by pw.worktypeid) as prevention_name
from mjdr_moskalev.preventions p
join mjdr_moskalev.prevent_worktypes pw on pw.preventionid = p.id
union
select wt.worktypeid
, listagg(p.name,' ') within group (order by name)
over(partition by wt.worktypeid) as prevention_name
from mjdr_moskalev.preventions p
join mjdr_moskalev.prevent_worktypes pw on pw.preventionid = p.id
join wt.worktypes wt on wt.worktypecategoryid = pw.worktypeid
) pt on pt.worktypeid = t.worktypeid
where INSTR(p_divisionid, t.divisionid) > 0 --t.divisionid = 105132
-- union all
-- select 1 as obj_rn, 1, null, null as objname, null, null, null, null, 1, 1, null, 3 as sv_sort from dual connect by level <= p_erows
-- union all
-- select 1 as obj_rn, 1, null, null as objname, null, null, null, null, 1, 1, null, 4 as sv_sort from dual connect by level <= p_erows
-- union all
-- select 1 as obj_rn, 1, null, null as objname, null, null, null, null, 1, 1, null, 5 as sv_sort from dual connect by level <= p_erows2
order by sv_sort desc
, objname desc
, obj_rn desc
