SQL Extracting timetable data

本文由 P 在 2012-07-24 發表於 "WebSAMS 討論區" 討論區

  1. 55078230

    P
    Expand Collapse

    文章:
    1
    讚:
    0
    Dear Sir,

    I got a query a few years ago for extracting timetable data from WebSAMS. It worked properly for the last few years. This time, there is an error message saying that the query is a malicious code. Could you help find out what the problem is? Please. The query is attached.
     

    附件文件:

  2. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    Pls try the below SQL:

    select z.LAYOUTID 'LayoutID', g.ENAME 'Class',z.DAY 'Day',z.PERIOD 'Period',z.LESSON_LEN 'Length_Len',
    convert(integer,hour(z.DAY_START)+(minute(z.DAY_START)+z.offset)/60) Start_Hour,
    mod(minute(z.DAY_START)+z.offset,60) Start_Minute,
    convert(integer,hour(z.DAY_START)+(minute(z.DAY_START)+z.offset+z.DURATION)/60) End_Hour,
    mod(minute(z.DAY_START)+z.offset+z.DURATION,60) End_Minute,z.DURATION 'Duration',
    h.ENAME 'Subject_E',h.CNAME 'Subject_C',h.SNAME 'Subject_Short_E',h.SNAME2 'Subject_Short_C',
    d.ENAME 'Room', t.SNAME 'Teacher_S', t.ENAME 'Teacher_E', t.CNAME 'Teacher_C'
    from
    (select k.LAYOUTID,b.DAYID+1 'Day',b.SESSIONID+k.NRECESS+1 'Slot',b.SESSIONID+1 'Period',
    b.LESSONID,b.SUBID,b.LESSON_LEN,b.ROOMID,k.DURATION Duration,k.START_TIME DAY_START,
    isnull(k.assembly,0)+isnull(k.recess,0)+isnull(k.Total,0) Offset,a.INSTID,a.RESULTID
    from RESULT a
    join RESULT_SESSION b
    on b.INSTID=a.INSTID and b.RESULTID=a.RESULTID
    join
    (select a.INSTID,a.LAYOUTID,a.DAYID,a.SESSIONID,b.START_TIME,a.DURATION,c.DURATION assembly,
    (select sum(duration)
    from LAYOUT_SESSION
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID) total,
    (select sum(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID) recess,
    (select count(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID) nrecess,
    (select max(duration)
    from LAYOUT_ASSEMBLY
    where instid=a.INSTID and layoutid=a.LAYOUTID) dassembly
    from LAYOUT_SESSION a
    join LAYOUT_START b
    on b.INSTID=a.INSTID and b.LAYOUTID=a.LAYOUTID and b.DAYID=a.DAYID
    left outer join LAYOUT_ASSEMBLY c
    on c.INSTID=a.INSTID and c.LAYOUTID=a.LAYOUTID and c.DAYID=a.DAYID
    ) k
    on k.INSTID=b.INSTID and k.DAYID=b.DAYID and k.SESSIONID=b.SESSIONID
    union
    select k.LAYOUTID,b.DAYID+1,b.SESSIONID+k.NRECESS+2,b.SESSIONID+2,b.LESSONID,b.SUBID,
    b.LESSON_LEN,b.ROOMID,k.DURATION,k.START_TIME,
    isnull(k.assembly,0)+isnull(k.recess,0)+isnull(k.Total,0),a.INSTID,a.RESULTID
    from RESULT a
    join RESULT_SESSION b
    on b.INSTID=a.INSTID and b.RESULTID=a.RESULTID and b.LESSON_LEN>1
    join
    (select a.INSTID,a.LAYOUTID,a.DAYID,a.SESSIONID,b.START_TIME,a.DURATION,c.DURATION assembly,
    (select sum(duration)
    from LAYOUT_SESSION
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+1) total,
    (select sum(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+1) recess,
    (select count(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+1) nrecess,
    (select max(duration)
    from LAYOUT_ASSEMBLY
    where instid=a.INSTID and layoutid=a.LAYOUTID) dassembly
    from LAYOUT_SESSION a
    join LAYOUT_START b
    on b.INSTID=a.INSTID and b.LAYOUTID=a.LAYOUTID and b.DAYID=a.DAYID
    left outer join LAYOUT_ASSEMBLY c
    on c.INSTID=a.INSTID and c.LAYOUTID=a.LAYOUTID and c.DAYID=a.DAYID
    ) k
    on k.INSTID=b.INSTID and k.DAYID=b.DAYID and k.SESSIONID=b.SESSIONID
    union
    select k.LAYOUTID,b.DAYID+1,b.SESSIONID+k.NRECESS+3,b.SESSIONID+3,b.LESSONID,b.SUBID,
    b.LESSON_LEN,b.ROOMID,k.DURATION,k.START_TIME,
    isnull(k.assembly,0)+isnull(k.recess,0)+isnull(k.Total,0),a.INSTID,a.RESULTID
    from RESULT a
    join RESULT_SESSION b
    on b.INSTID=a.INSTID and b.RESULTID=a.RESULTID and b.LESSON_LEN>2
    join
    (select a.INSTID,a.LAYOUTID,a.DAYID,a.SESSIONID,b.START_TIME,a.DURATION,c.DURATION assembly,
    (select sum(duration)
    from LAYOUT_SESSION
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+2) total,
    (select sum(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+2) recess,
    (select count(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+2) nrecess,
    (select max(duration)
    from LAYOUT_ASSEMBLY where instid=a.INSTID and layoutid=a.LAYOUTID) dassembly
    from LAYOUT_SESSION a
    join LAYOUT_START b
    on b.INSTID=a.INSTID and b.LAYOUTID=a.LAYOUTID and b.DAYID=a.DAYID
    left outer join LAYOUT_ASSEMBLY c
    on c.INSTID=a.INSTID and c.LAYOUTID=a.LAYOUTID and c.DAYID=a.DAYID
    ) k
    on k.INSTID=b.INSTID and k.DAYID=b.DAYID and k.SESSIONID=b.SESSIONID
    union
    select k.LAYOUTID,b.DAYID+1,b.SESSIONID+k.NRECESS+4,b.SESSIONID+4,b.LESSONID,b.SUBID,
    b.LESSON_LEN,b.ROOMID,k.DURATION,k.START_TIME,
    isnull(k.assembly,0)+isnull(k.recess,0)+isnull(k.Total,0),a.INSTID,a.RESULTID
    from RESULT a
    join RESULT_SESSION b
    on b.INSTID=a.INSTID and b.RESULTID=a.RESULTID and b.LESSON_LEN>3
    join
    (select a.INSTID,a.LAYOUTID,a.DAYID,a.SESSIONID,b.START_TIME,a.DURATION,c.DURATION assembly,
    (select sum(duration)
    from LAYOUT_SESSION
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+3) total,
    (select sum(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+3) recess,
    (select count(duration)
    from LAYOUT_BREAK
    where instid=a.INSTID and layoutid=a.LAYOUTID and dayid=a.DAYID and sessionid<a.SESSIONID+3) nrecess,
    (select max(duration)
    from LAYOUT_ASSEMBLY
    where instid=a.INSTID and layoutid=a.LAYOUTID) dassembly
    from LAYOUT_SESSION a
    join LAYOUT_START b
    on b.INSTID=a.INSTID and b.LAYOUTID=a.LAYOUTID and b.DAYID=a.DAYID
    left outer join LAYOUT_ASSEMBLY c
    on c.INSTID=a.INSTID and c.LAYOUTID=a.LAYOUTID and c.DAYID=a.DAYID
    ) k
    on k.INSTID=b.INSTID and k.DAYID=b.DAYID and k.SESSIONID=b.SESSIONID
    ) z
    left outer join
    (select i.INSTID, i.RESULTID, i.LESSONID, i.SUBID, i.TEACHERID, j.SNAME, j.ENAME, j.CNAME
    from RESULT_TEACHER i
    left outer join TEACHER j
    on i.INSTID=j.INSTID and i.TEACHERID=j.TEACHERID
    ) t
    on t.INSTID=z.INSTID and t.RESULTID=z.RESULTID and t.LESSONID=z.LESSONID and t.SUBID=z.SUBID
    left outer join room d
    on d.INSTID=z.INSTID and d.ROOMID=z.ROOMID
    left outer join lesson e
    on e.INSTID=z.INSTID and e.LESSONID=z.LESSONID
    left outer join klassgroup_link f
    on f.INSTID=z.INSTID and f.klassgpid=e.klassgpid
    left outer join klass g
    on g.INSTID=z.INSTID and g.klassid=f.klassid
    left outer join subject h
    on h.INSTID=z.INSTID and h.subjectid=e.subjectid
    where z.INSTID=? and z.RESULTID=?
    order by z.LAYOUTID,g.ENAME,z.DAY,z.PERIOD
     
    #2 edb-石頭, 2012-07-25
  3. Expand Collapse

    What should I input for parameter "z.INSTID" and "z.RESULTID"?
     
  4. 56953375

    edb-石頭
    Expand Collapse

    文章:
    1,160
    讚:
    0
    Pls refer to this
     
    #4 edb-石頭, 2012-08-23