using System;
using edu.jhu.Cas.Util;
using System.Text;
/**
 * Administrative back end for jobs. This updates and amends the job table according to the status of a given job. The Job executor calls the vairous routines to update the status. The various SQL statements are encapsulated in this one class. 
  $Id: JobAdmin.cs,v 1.8 2007/05/10 02:51:55 nli Exp $
  $Revision: 1.8 $
  $Date: 2007/05/10 02:51:55 $
  */
namespace edu.jhu.Cas.Jobs{
	public class JobAdmin:JobPub{
		//CreateJobStatement is in Job, not JobAdmin
	
		public const string StartJobStatement = @"
update jobs
set timestart = "+TIME_START+@"
,status = "+STATUS+@"
,hostip  = " + HOST_IP + WhereJobWSID;

        public const string FailJobStatement = @"
update jobs set status = " + STATUS + @"
, timeend = " + TIME_END + @"
, message = " + MESSAGE + WhereJobWSID + @"
update jobs set vtimesubmit = "+TIME_END+@" where status = 0 and 
webservicesid = " + WEB_SERVICES_ID + @"
and jobid <> " + JOB_ID;
		public const string FinishedJobStatement = @"
update jobs set status = "+STATUS+@"
, timeend = "+TIME_END+@"
, message = "+MESSAGE+@"
, rows = "+ROWS+WhereJobWSID+@"
update jobs set vtimesubmit = getdate() where status = 0 and 
webservicesid = "+WEB_SERVICES_ID+@"
and jobid <> "+JOB_ID;
		public const string FinishedOutputJobStatement = @"
update jobs set status = "+STATUS+@"
, timeend = "+TIME_END+@"
, message = "+MESSAGE+@"
, rows = "+ROWS+@"
, outputloc = "+OUTPUT_LOC+WhereJobWSID;
		public const string FindJobAdminStatement  = @"
select * from jobs ";
		public const string ResubmitTaskStatement = JobPub.CreateJobSelectStatement+@"
and taskname = "+TASK_NAME;
		public const string FindNextQueueStatement = @" 
select min(queue) from servers where queue > "+ESTIMATE+@"			
and target = "+TARGET;
		/// <summary>
		/// Lowest queue jobs execute immediatly so one needs to know if a given job
		/// is in the lowest queue. This means the queue with te lowest ESTIMATE 
		/// for the given target. This builds the sql query for that question.
		/// </summary>
		public const string IsInLowestQueueStatement = @"
select name, queue,host,cat,usr
from servers, ( select  min(queue) from servers where target = "+TARGET+@")
as result (minQ) where target = "+TARGET+@" and
queue = result.minQ and "+ESTIMATE+@" <= queue";			
		protected const string WhereJobWSID = @"
where webservicesid = "+WEB_SERVICES_ID+@"
and jobid = "+JOB_ID;
	}
}
/**
 * $Log: JobAdmin.cs,v $
 * Revision 1.8  2007/05/10 02:51:55  nli
 * vtimesubmit functionality should now work properly again
 *
 * Revision 1.7  2007/04/25 18:51:05  nli
 * removed redundant information from servers and mydbhosts table
 * code modified to support this
 * schema changes:
 * servers ->
 *   --add CStringExtra column, varchar(200)
 *   --add Password column, varchar(200)
 *   --rename owner column to usr
 *   --fill in appropriate values
 *   --remove ConnectionString column
 *
 * mydbhosts ->
 *   -- rename 'user' to usr
 *   -- add CStringExtraColumn
 *   -- add admin_usr varchar column
 *   -- add admin_pw varchar column
 *
 * Revision 1.6  2007/04/25 17:03:27  nli
 * optimized sql generation in JobXXX classes
 *
 * Revision 1.5  2006/09/14 16:34:32  nli
 * minor cosmetic changes
 *
 * Revision 1.4  2005/11/14 18:07:49  nli
 * --new 'fair' scheduling
 * --jobs table requires new column, vTimeSubmit, default = getdate()
 *
 * Revision 1.3  2005/11/14 17:38:12  nli
 * --new 'fair' scheduling
 * --jobs table requires new column, vTimeSubmit, default = getdate()
 *
 * Revision 1.2  2005/05/31 17:57:20  womullan
 *  Added some general comments. Fixed The Admin page resetting users password SPR6843. Added New Password box to admin page to allow admin to change users password.
 *
 * Revision 1.1.1.1  2004/04/27 16:38:29  cvsuser
 * import CasJobs to FERMI
 *
 * Revision 1.1.1.1  2004/02/18 16:38:26  nli
 * new version
 *
 * Revision 1.1.1.1  2004/01/25 18:16:57  nli
 * first
 *
 * Revision 1.1.1.1  2004/01/20 20:59:56  nli
 * new
 *
 * Revision 1.10  2003/10/21 17:13:51  nli
 * virtualhost implemented, all targets are virtual
 *
 * Revision 1.9  2003/08/27 18:15:23  nli
 * debug
 *
 * Revision 1.8  2003/07/23 21:11:06  nli
 * -outputloc arg removed from CasJobs.StartJob
 *     this is set only by the output proceesor
 * -hostip is correctly set in submitshortjob and queuethreadaction
 * -various changes in other stuff to make the above 2 thigns work
 *
 * Revision 1.7  2003/07/21 19:57:58  nli
 * untested outputcore
 *
 * Revision 1.6  2003/06/26 20:31:08  nli
 * IsInLowestQueueStatement made more robust
 *
 * Revision 1.5  2003/06/20 18:33:40  nli
 * added submitShortJob method in CasJobs
 *
 * Revision 1.4  2003/06/19 19:37:19  womullan
 *  users added
 *
 * Revision 1.3  2003/06/19 18:52:08  nli
 *
 * blah blah cvs
 *
 */
