using System;
using edu.jhu.Cas.Util;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
/**
  $Id: JobPub.cs 147171 2013-06-25 18:32:16Z dmedv $
  $Revision: 147171 $
  $Date: 2013-06-25 14:32:16 -0400 (Tue, 25 Jun 2013) $
  */
namespace edu.jhu.Cas.Jobs
{
	public class JobPub:SqlJob
	{
		public JobPub():base()
		{
			init();
		}
		/** begin create job statements **/
		public string CreateJobStatement
		{
			get
			{
				StringBuilder builder = new StringBuilder();
				builder.Append("begin transaction ");
				builder.Append(CreateJobInsertStatement);
				builder.Append(" ; ");
				builder.Append(CreateJobSelectStatement);
				builder.Append(" commit transaction");
				return builder.ToString();
			}
		}
		public const string CreateJobSelectStatement = @"
select max(jobid) as jobid from jobs where webservicesid = "+WEB_SERVICES_ID;
		public string CreateJobInsertStatement
		{
			get
			{
				StringBuilder builder = new StringBuilder();
				string tmp;
				builder.Append("insert into jobs (");
				foreach(Object o in base.Keys)
				{
                    if (o.ToString() == TYPE) continue;
					tmp = o.ToString();
					tmp = tmp.Replace("@","");
					builder.Append(tmp);
					builder.Append(",");
                    
				}
				builder.Remove(builder.Length-1, 1);//remove extra comma
				builder.Append(")");
				builder.Append(" VALUES (");
				foreach(Object o in base.Keys)
				{
                    if (o.ToString() == TYPE) continue;
					builder.Append(o);
					builder.Append(",");
				}
				builder.Remove(builder.Length-1, 1);//remove extra comma
				builder.Append(")");
				return builder.ToString();
			}
		}
		/** end create job statements **/
		public const string StatusJobStatement = @"
select status from jobs where jobid = "+JOB_ID+@"
and webservicesid = "+WEB_SERVICES_ID;
		public const string CancelStatement = @"
update jobs set status = "+STATUS+@"
where jobid = "+JOB_ID;
		public void addJobId(Object value)
		{
			Add(JOB_ID, new JobInfo(SqlDbType.BigInt, 80, value));
		}
		public void addTimeStarted(DateTime time)
		{
			Add(TIME_START, new JobInfo(SqlDbType.DateTime, 8,time));
		}
		public void addTimeEnd(DateTime time)
		{
			Add(TIME_END, new JobInfo(SqlDbType.DateTime, 8,time));
		}
		public void addRows(Object value)
		{
			Add(ROWS, new JobInfo(SqlDbType.BigInt, 8,value));
		}
		public static string GetOutputDownloadsStatement = @"
select taskname, outputtype, max(TimeEnd) as timeend, outputloc from jobs where status = "+QUERY_STATUS.FINISHED+@"
and outputtype not like '%"+OUTPUT.QUERY+@"%' and visibility != "+VISIBILITY.NOT_USER_VISIBLE+@"
and webservicesid = "+WEB_SERVICES_ID+@" group by taskname,outputtype,outputloc order by timeend desc";
        public static string GetPendingOutputStatement = @"
select taskname,outputtype,outputloc,timesubmit from jobs where status < 2
and outputtype not like '%" +OUTPUT.QUERY+@"%' and visibility != "+VISIBILITY.NOT_USER_VISIBLE+@"
and webservicesid = "+WEB_SERVICES_ID+" order by timeend desc";
        public static string GetFailedOutputStatement = @"
select taskname,outputtype,timesubmit,message,
outputloc from jobs where status = " +QUERY_STATUS.FAILED+@"
and outputtype not like '%"+OUTPUT.QUERY+@"%' and visibility != "+VISIBILITY.NOT_USER_VISIBLE+@"
and webservicesid = "+WEB_SERVICES_ID+" order by timeend desc";
		
		public void init()
		{
			Add(WEB_SERVICES_ID, new JobInfo(SqlDbType.VarChar, 32,""));
			Add(TASK_NAME,new JobInfo(SqlDbType.VarChar,80,""));
			Add(OUTPUT_LOC,new JobInfo(SqlDbType.VarChar,200,""));
			Add(STATUS,new JobInfo(SqlDbType.Int,1,QUERY_STATUS.READY));
			Add(HOST_IP,new JobInfo(SqlDbType.Char,15,""));
			Add(MESSAGE,new JobInfo(SqlDbType.VarChar,-1,""));
			Add(QUERY,new JobInfo(SqlDbType.NText,99999999,""));
			Add(MODIFIED_QUERY,new JobInfo(SqlDbType.NText,99999999,""));
			Add(ESTIMATE,new JobInfo(SqlDbType.Int,2,0));
			Add(TARGET,new JobInfo(SqlDbType.VarChar,200,""));
			Add(AUTO_COMPLETE,new JobInfo(SqlDbType.Int,1,0));
            Add(TYPE, new JobInfo(SqlDbType.Int, 1, 0));
			Add(SEND_EMAIL,new JobInfo(SqlDbType.Int,1,EMAIL.ADMIN_ONLY));
			Add(OUT_TYPE,new JobInfo(SqlDbType.VarChar,50,"QUERY"));
			Add(IP,new JobInfo(SqlDbType.VarChar,50,""));
			Add(CREATED_TABLE,new JobInfo(SqlDbType.VarChar,200,""));
			Add(PARAMS,new JobInfo(SqlDbType.NText,99999999,""));
		}
		/** begin package keys **/
		public const string WEB_SERVICES_ID = "@WebServicesId";
		public const string TASK_NAME = "@TaskName";
		public const string OUTPUT_LOC = "@OutputLoc";
		public const string STATUS = "@Status";
		public const string HOST_IP = "@HostIP";
		public const string MESSAGE = "@Message";
		public const string QUERY = "@Query";
		public const string MODIFIED_QUERY = "@ModifiedQuery";
		public const string ESTIMATE = "@Estimate";
		public const string AUTO_COMPLETE = "@AutoComplete";
		public const string SEND_EMAIL = "@SendEmail";
		public const string JOB_ID = "@JobId";
		public const string CREATED_TABLE = "@Created_Table";
		public const string TIME_START = "@TimeStart";
		public const string TIME_END = "@TimeEnd";
		public const string ROWS = "@Rows";
		public const string TARGET = "@Target";
		public const string OUT_TYPE= "@OutputType";
		public const string IP= "@ip";
        public const string TYPE = "@type";
		public const string PARAMS= "@params";
		/** end package keys **/
		public string FindJobStatement
		{
			get
			{
				StringBuilder builder = new StringBuilder();
				builder.Append("select * from jobs where webservicesid = ");
				builder.Append(WEB_SERVICES_ID);
				return builder.ToString();
			}
		}
		public string StatusHTMLStatment
		{
			get
			{
				StringBuilder builder = new StringBuilder();
				builder.Append("select html from status where status =");
				builder.Append(STATUS);
				return builder.ToString();
			}
		}

	}
}
/**
 * $Log$
 * 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  2006/09/13 15:47:40  nli
 * back from summer
 *
 * Revision 1.3  2006/02/10 23:04:49  nli
 * --tableimport redone
 *
 * Revision 1.2  2005/09/16 15:02:19  nli
 * --bigplot tuning
 *
 * Revision 1.1.1.1  2004/04/27 16:38:29  cvsuser
 * import CasJobs to FERMI
 *
 * Revision 1.5  2004/04/15 21:26:40  nli
 * *** empty log message ***
 *
 * Revision 1.4  2004/03/15 14:30:48  nli
 * *** empty log message ***
 *
 * Revision 1.3  2004/03/12 21:15:32  nli
 * datareader, table link, etc
 *
 * Revision 1.2  2004/02/27 23:31:09  nli
 * job cancel fixed
 * lock level lowered
 *
 * Revision 1.1.1.1  2004/02/18 16:38:26  nli
 * new version
 *
 * Revision 1.3  2004/02/13 16:28:20  nli
 * plot images sort
 *
 * Revision 1.2  2004/01/30 18:42:18  nli
 * hey this might be stable!
 *
 * 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.13  2003/09/22 15:28:17  nli
 * qry char limit upped to 99999999
 *
 * Revision 1.12  2003/09/18 21:40:42  nli
 * ntext test
 *
 * Revision 1.11  2003/09/18 21:17:00  nli
 * query types 2 ntext
 *
 * Revision 1.10  2003/09/18 19:29:30  nli
 * upped query chars to 5000
 *
 * Revision 1.9  2003/07/17 18:46:25  nli
 * output type support
 *
 * Revision 1.8  2003/07/01 23:18:19  womullan
 * cancel job and re submit
 *
 * Revision 1.7  2003/06/26 20:08:40  nli
 * formatting stuff in casjobs
 *
 * Revision 1.6  2003/06/19 21:58:33  nli
 * casusers complete, but utterly untested
 *
 * Revision 1.5  2003/06/19 20:27:01  nli
 * arranging stuff
 *
 * 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
 *
 */
