Subversion Repositories computer_asset_manager_v1

Rev

Rev 52 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 52 Rev 87
Line 105... Line 105...
105 
         join site on device.site_id = site.site_id join client on site.client_id = client.client_id
 105 
         join site on device.site_id = site.site_id join client on site.client_id = client.client_id
106 
      '
 106 
      '
107 
   );
 107 
   );
108 
//            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Due Date",
 108 
//            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Due Date",
109
109
110 
   define (SQL_GET_OUTSTANDING_MAINTENANCE, '
 110 
   define (SQL_GET_OUTSTANDING_MAINTENANCE, <<<EOD
111 
     select concat( client.name, \' Site \', site.name ) "Site",
 111 
        select concat( view_client_site_device.Client, ' Site ', view_client_site_device.Site ) Site,
112 
            device.device_id "Device ID",
 112 
            view_client_site_device.device_id "Device ID",
113 
            device.name "Device Name",
 113 
            Device "Device Name",
114 
            device.notes "Device Notes",
 114 
            device_notes "Device Notes",
115 
            device.restrictions "Restrictions",
 115 
            device_restrictions "Restrictions",
116 
            maintenance_task.description "Task",
 116 
            maintenance_task.description "Task",
117 
            maintenance_task.notes "Task Notes",
 117 
            maintenance_task.notes "Task Notes",
118 
            DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) "Due",
 118 
            DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) "Due",
119 
            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Date Due",
 119 
            DATE(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY)) "Date Due",
120 
            login.email "Technician",
 120 
            login.email "Technician",
121 
            maintenance_schedule.maintenance_schedule_id "ID"
 121 
            maintenance_schedule.maintenance_schedule_id "ID"
- 
 
 122 
      from
- 
 
 123 
         maintenance_schedule
122 
      from maintenance_schedule join maintenance_performed on
124 
         join maintenance_performed on
123 
                  maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
 125 
                  maintenance_performed.maintenance_task_id = maintenance_schedule.maintenance_task_id
124 
                  and maintenance_performed.device_id = maintenance_schedule.device_id
 126 
                  and maintenance_performed.device_id = maintenance_schedule.device_id
125 
         join login on login.login_id = maintenance_schedule.login_id
 127 
         join login on login.login_id = maintenance_schedule.login_id
126 
         join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
 128 
         join maintenance_task on maintenance_task.maintenance_task_id = maintenance_schedule.maintenance_task_id
127 
         join device on maintenance_schedule.device_id = device.device_id
 129 
         join view_client_site_device on (view_client_site_device.device_id = maintenance_schedule.device_id )
128 
         join site on device.site_id = site.site_id
 - 
 
129 
         join client on site.client_id = client.client_id
 - 
 
130 
         join (select max(maintenance_performed_id) last_maintenance from maintenance_performed group by device_id,maintenance_task_id) last_performed on maintenance_performed.maintenance_performed_id = last_performed.last_maintenance
 130 
         join (select max(maintenance_performed_id) last_maintenance from maintenance_performed group by device_id,maintenance_task_id) last_performed on maintenance_performed.maintenance_performed_id = last_performed.last_maintenance
131 
      where device.removed_date is null
 - 
 
132 
            and site.removed_date is null
 - 
 
133 
            and client.removed_date is null
 - 
 
134 
            and maintenance_schedule.removed_date is null
 131 
      where maintenance_schedule.removed_date is null
135 
            and <additionalWhere>
 132 
            and <additionalWhere>
- 
 
 133 
     order by
136 
     order by DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()),
 134 
         DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()),
- 
 
 135 
         Client,
- 
 
 136 
         Site,
- 
 
 137 
         Device,
137 
           client.name,site.name,device.name, maintenance_task.description;
 138 
         maintenance_task.description
138 
      '
 139 
EOD
139 
//            and DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) <= 7
 140 
//            and DATEDIFF(DATE_ADD(maintenance_performed.maintenance_date, INTERVAL schedule DAY), now()) <= 7
140 
   );
 141 
   );
141
142
142 
   define (SQL_MAINTENANCE_REPORT, '
 143 
   define (SQL_MAINTENANCE_REPORT, '
143 
      select concat( client.name, \' - \', site.name) "Site",
 144 
      select concat( client.name, \' - \', site.name) "Site",