--
-- Since : 5.8.0
--
-- File    : whathappened.sql
-- Purpose : this file contains the needed SQL commands to 
--           retreive pilot jobs from DB
-- Usage   : mysql < whathappened.sql
--


-- select "create a temporary table for completed jobs"

create temporary table completedjobs (
       thedate datetime,
       name char(100) default "",
       completeds int(10) default 0,
       avgexec int(10) default 0,
       proxieds int(10) default 0
);

-- select "fill temporary table completedjobs";

insert into completedjobs (thedate,name, completeds, avgexec, proxieds)
       select date(completeddate),
       	      apps.name,
	      count(*) as completed,
       	      format(avg(UNIX_TIMESTAMP(completeddate)-UNIX_TIMESTAMP(laststartdate)),0),
       	      count(works.userproxy) as proxieds
       from apps,works,tasks,hosts
       where works.appuid=apps.uid
       	     and works.uid=tasks.uid
             and tasks.hostuid=hosts.uid
 	     and date(completeddate)>"2009-07-14" 
 	     and not isnull(completeddate)
 	     and works.status="COMPLETED"
       group by apps.name, date(completeddate)
       order by apps.name, date(completeddate);

-- select "create a temporary table for error jobs"

create temporary table errorjobs (
       thedate datetime,
       name char(100) default "",
       errors int(10) default 0
);

-- select "fill temporary table errorjobs";

insert into errorjobs (thedate,name,errors)
       select date(arrivaldate),
       	      apps.name,
       	      count(*) as errors
       from apps,works
       where works.appuid=apps.uid
 	 and date(arrivaldate)>"2009-07-14" 
 	 and not isnull(arrivaldate)
 	 and works.status="ERROR"
       group by apps.name, date(arrivaldate)
       order by apps.name, date(arrivaldate);


-- select "create temporary table for piloted jobs (jobs run on EGEE ressources)";

create temporary table pilotedjobs (
       thedate datetime,
       name char(100) default "",
       piloteds int(10) default 0
);

-- select "fill temporary table pilotedjobs";

insert into pilotedjobs (thedate,name, piloteds)
       select date(completeddate),
       	      apps.name,
	      count(*) 
       from apps, works , tasks, hosts 
       where tasks.uid=works.uid 
       	 and works.appuid=apps.uid
       	 and works.status="COMPLETED" 
	 and not isnull(works.completeddate)
	 and date(works.completeddate)>"2009-07-14" 
	 and tasks.hostuid=hosts.uid 
	 and hosts.pilotjob="true" 
       group by apps.name, date(completeddate);

-- select "create temporary table for pilot jobs (EGEE ressources)";

create temporary table pilotjobs (
       thedate datetime,
       pilotjobs int(10) default 0
);

-- select "fill temporary table pilotedjobs";


-- select date(completedjobs.thedate),
--        completejobs.name,
--        completeds,
--        errors,
--        piloteds,
--        avgexec,
--        proxieds,
--        pilotjobs
--        from completedjobs
--        join errorjobs,pilotedjobs,pilotjobs
--        on date(completedjobs.thedate)=date(errorjobs.thedate),
--        	  date(completedjobs.thedate)=date(pilotedjobs.thedate), 
--        	  date(completedjobs.thedate)=date(pilotjobs.thedate)
--       group by date(completedjobs.thedate)
--       order by date(completedjobs.thedate);

select date(completedjobs.thedate),
       completedjobs.name,
       completeds,
       errors,
       avgexec,
       proxieds
	      from completedjobs
	      join errorjobs 
	      on date(completedjobs.thedate)=date(errorjobs.thedate) 
      group by date(completedjobs.thedate)
      order by date(completedjobs.thedate);
