using System;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Xml.Serialization;
using System.Text.RegularExpressions;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Jobs;
using net.ivoa;
using net.ivoa.VOTable;
namespace edu.jhu.Cas.Jobs {  
	/// <summary>
	/// TableJobs class provides XML Web Services for extracting tables from mydb
	/// It also contains some of the logic for finding group tables to display to users
	/// and for finding the query which created the table - although that sometimes does not work.
	/// <pre>
	/// Current version:
	/// ================
	/// $Id: TableJobs.asmx.cs,v 1.32 2008/04/11 18:16:13 nli Exp $
	/// $Revision: 1.32 $
	/// $Date: 2008/04/11 18:16:13 $
	/// </pre>
	/// </summary>
	[WebService(Namespace="Jobs.Cas.jhu.edu",
		 Description="This is an <b>XML Web Services</b> interface to the <b>MYDB table extraction</b> (CAS)."
		 +"<br> It provides access to MyDb data and returns the result in different formats."
		 +"<br> Send comments and suggestions to <b>William O'Mullane</b> -- woullan[at]pha.jhu.edu")]
	public class TableJobs : System.Web.Services.WebService {
		
		public TableJobs(){}
		[WebMethod(Description="Return list of available Output Types & descriotios 2xn array"
			 )]
		public DataSet ListQuickOutputTypes() {	
			JobUsers js = new JobUsers();
			DataSet ds = SqlFactory.select(js,"select Outputtype,Description from Output where visible >= "+SYSTEM.QUICK_VISIBLE);
			return  ds;
		}
		public DataSet ListLongOutputTypes() {	
			   JobUsers js = new JobUsers();
			DataSet ds = SqlFactory.select(js,"select Outputtype,Description from Output where visible >= "+SYSTEM.LONG_VISIBLE);
			return  ds;
		}
		
        [WebMethod(Description = "Return list of group visible tables")]
        public Table[] ListGroupTables(long wsid) {
            JobGroups job = new JobGroups();
            job.SetValue(JobGroups.WEB_SERVICES_ID, wsid);
            return DataConverter.dataSet2Table(SqlFactory.select(job, JobGroups.ListGroupTablesStatement));
        }
		public ArrayList GetGroupTablesList(long wsid) {
            return new ArrayList(GetGroupTables(wsid));
		}
        public string[] GetGroupTables(long wsid) {
            Table[] tables = ListGroupTables(wsid);
            string[] rst = new string[tables.Length];
            for (int x = 0; x < rst.Length; x++)
                rst[x] = tables[x].GroupName + "." + tables[x].UserID.Trim() + "." + tables[x].Name.Trim();
            return rst;
        }
		/*
		 * returns an all-uppercase list of columns that have indices on them
		 * as well as the name of the index
		 */
		public StringDictionary ListIndices(long wsid, string tableName){
			StringDictionary sc = new StringDictionary();
			string qry = "sp_helpindex '"+tableName+"'";
			DataSet ds = (new CasJobs()).
				SubmitShortJob(wsid,qry,"MYDB",SYSTEM.TASKNAME+" listing indices",0);
			foreach(DataRow dr in ds.Tables[0].Rows)
				try{
					sc.Add(dr["index_keys"].ToString().ToUpper(),dr["index_name"].ToString());
				}catch(ArgumentException ae){ae=ae;continue;}
			return sc;
		}
		public Table[] ListPublishedTables(long wsid) {
			string publishedQuery = SYSTEM.QUERY_PREFIX+"select tableName from groupTables where webservicesid = "+wsid;
			DataSet ds = SqlFactory.select(publishedQuery);
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetPublishedTablesList(long wsid) {
			Table[] tables = ListPublishedTables(wsid);
			return Table2List(tables);
		}
		
        public const string PendingTablesQry = @"
select j.created_Table as tablename from jobs j, (
	select Created_Table as TableName, max(timestart) as timestart from jobs 
	where webservicesid = @WSID 
	and Created_Table is not null
	and Created_Table <> ''
    and timeend is null
	group by created_table
) b
where j.timestart = b.timestart
and status = 1
and Created_Table is not null
and Created_Table <> ''";
        public static string[] tParams = new string[1] { "@WSID" };
        public static SqlDbType[] tTypes = new SqlDbType[1] { SqlDbType.BigInt };
        public static int[] tSizes = new int[1] { 64 };
		public Table[] ListPendingTables(long wsid) {
			DataSet ds = SqlFactory.select_param(PendingTablesQry,tParams,tTypes,tSizes,new object[1]{wsid});
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetPendingTablesList(long wsid) {
			Table[] tables = ListPendingTables(wsid);
			return Table2List(tables);
		}
        public const string failedTablesQry = @"
select j.created_Table as tablename from jobs j, (
	select Created_Table as TableName, max(timeend) as timeend from jobs 
	where webservicesid = @WSID
	and Created_Table is not null
	and Created_Table <> ''
	group by created_table
) b
where j.timeend = b.timeend
and (status = 4 or status = 3 or status = 2)
and Created_Table is not null
and Created_Table <> ''";
		public Table[] ListFailedTables(long wsid) {
            DataSet ds = SqlFactory.select_param(failedTablesQry,tParams,tTypes,tSizes,new object[1]{wsid});
			return DataConverter.dataSet2Table(ds);
		}
		public ArrayList GetFailedTablesList(long wsid) {
			Table[] tables = ListFailedTables(wsid);
			return Table2List(tables);
		}
		public void ImportTable(long wsid, string tableName, string localFilePath) {
			StreamReader reader = new StreamReader(localFilePath);
		}

		[WebMethod(Description="Request  file to be put in download area</b>"
			 +"<br><b>Input:</b> TableName (string)"
			 +"<br><b>Input:</b> qry (string) - always select * from tablename"
			 +"<br><b>Input:</b> outputType (string) - get list from listOutputTypes"
			 +"<br><b>Output:</b> JobId (long)"
			 +"<br><b>Target:</b> currently, target must always be mydb"
			 )]
		public long QueryFileOutput(long wsid, string qry, string outputType, string target,string args,string taskName) {
			if(target == null)throw new Exception("You must specify a target.");
			target = target.ToUpper();
			if(target.IndexOf("MYDB") == -1)
				throw new Exception("Target must be a MyDB.");
			if(outputType == null || outputType.Length < 2)
				throw new Exception("You must specify an outputType.");
			
			CasJobs cj = new CasJobs();
			long jobid = cj.SubmitNonQueryJob(wsid,qry,target,taskName,500,0,outputType,args);
			return jobid;
		}
        /*
		/// <summary>
		/// for given tbale name try to find the query which CREATED it. That could be
		/// a create statement or an into. Also wht if anohter user has such a table
		/// what if its a group table .. we want to see the source if it is a group table
		/// as well but not a table of the same name owned by someone else !
		/// So this assumes you already found out the table owners wsid befor calling it.
        /// 
		/// </summary>
        /// <!--lol, classic wil style.  wtf is he talking about-->
		/// <param name="wsid"> the table owners wsid </param>
		/// <param name="tableName"> the table</param>
		/// <returns></returns>
		public string GetSourceQuery(long wsid,string tableName)
		{
			string qry2 = @"
select t.q from (
	select query q, created_table ct, timeend te  from jobs where
	webservicesid = "+wsid+@"
	and created_table <> '' 
) t where t.ct = '"+tableName+@"' 
or t.q like '%create%table "+tableName+@"'
order by t.te desc ";
			string qry1 = "sp_helptext ["+tableName+"]";
			try{
				DataSet ds = (new CasJobs()).SubmitShortJob(wsid,qry1,"MYDB",SYSTEM.TASKNAME+" table source check",0);
				string rst = "";
				foreach(DataRow r in ds.Tables[0].Rows)
					rst+=r[0].ToString();
				return rst;
			}catch{
                string s = SqlFactory.scalar(qry2) as string;
                return s;               
			}			
		}*/
		
		public long RequestFileOutput(long wsid, string tableName, string outputType, string target,string args){
			return RequestFileOutput(wsid,tableName,outputType,target,args,"select * from "+tableName);
		}
		public long RequestFileOutput(long wsid, string tableName, string outputType, string target,string args,string qry){
			return QueryFileOutput(wsid,qry,outputType,target,args,tableName);
		}
        /**
         * remove all casjobs references to a table
         */
        public void PurgeTable(long wsid, string tableName) {
            string[] tokens = tableName.Split('.');
            if (tokens.Length != 3) {
                SqlFactory.select_param(attemptDepublishQry, adparamNames, adparamTypes, adSizes, new object[2] { wsid, tableName });
            }
            else {
                User u = (new CasUsers()).FindUser(ID.SERVICE_ID, " userid = '" + tokens[1] + "'")[0];
                SqlFactory.nonselect_param(attemptDepublishQry, adparamNames, adparamTypes, adSizes, new object[2] { u.WebServicesID, tokens[2] });
            }
            CasNotes cnotes = new CasNotes();
            cnotes.PurgeObjNotes(wsid, "Mydb", tableName, CasDBObjType.TABLE);
        }
        public const string attemptDepublishQry = @"
delete from grouptables
where webservicesid = @WSID
and tableName = @TNAME

update jobs
set created_table = null
where webservicesid = @WSID
and created_table = @TNAME
";
        public static string[] adparamNames = new string[2] { "@WSID", "@TNAME" };
        public static SqlDbType[] adparamTypes = new SqlDbType[2] { SqlDbType.BigInt, SqlDbType.VarChar };
        public static int[] adSizes = new int[2] { 64, 100 };


        public void AssignTable(long wsid, long jobid, string tableName){
            SqlFactory.select("update jobs set Created_Table = '" + tableName + "' where jobid = " + jobid);
		}
        //called by jobs that have started, but not yet completed
        //only assign the table if there is not one already
        public void PreAssignTable(long wsid, long jobid, string tableName) {
            AssignTable(wsid, jobid, tableName);
        }
        public void RollBackTable(long wsid, long jobid, string tableName) {
            SqlFactory.select("update jobs set Created_Table = null where jobid = " + jobid);
        }   
		
		[WebMethod(Description="List tables in a users mydb")]
		public string[] MyDBTables(long wsid){
			return (new MyDBJobs()).Tables(wsid,"MYDB");
		}
		[WebMethod(Description="as dataset,csv,votable,fits?"
			 +"<br><b>Input:</b> WebServicesId to locat MyDB (long)")]
		public string QuickFileOutput(long wsid, string tableName, string format){
            throw new Exception("disabled");
			CasUsers cUsers = new CasUsers();
			edu.jhu.Cas.Jobs.CasJobs cJobs = new edu.jhu.Cas.Jobs.CasJobs();
			tableName = tableName.ToLower().Trim();
			if(!tableName.StartsWith("mydb") && !tableName.StartsWith("group"))
				tableName = "mydb."+tableName;
			long count = long.Parse(
				cJobs.SubmitShortJob(wsid,
					"select count(*) from "+tableName,"MYDB",
				SYSTEM.TASKNAME+" Extract count for "+tableName,-1).Tables[0].Rows[0][0].ToString());
			
			if(count > SYSTEM.QUICK_EXTRACT_ROW_LIMIT)
				throw new Exception("Table "+tableName+" has "+count+" rows, which exceeds quick extract limit of "+SYSTEM.QUICK_EXTRACT_ROW_LIMIT);
			DataSet ds = cJobs.SubmitShortJob(wsid,"select * from "+tableName,"MYDB",
				SYSTEM.TASKNAME+" extract job for "+tableName,-1);
			CasOutput cOut = new CasOutput();
			string path = cOut.path(format);
			string url = cOut.url(format);
			string name = tableName+"_"+wsid.ToString();
			
			//try{
			switch(format.ToLower()){
				case "csv":
					name+=".csv";
				    File.Delete(path+name);
					DataConverter.dataSet2CSVFile(ds, path+name);
					break;
				case "dataset":
					name+=".xml";
					File.Delete(path+name);
					FileStream fs = new FileStream(path+name,System.IO.FileMode.Create);
					XmlSerializer serializer = new XmlSerializer(typeof(DataSet));
					serializer.Serialize(fs,ds);
					fs.Close();
					break;
				case "votable":
					name+=".xml";
					File.Delete(path+name);
					VOTABLE votable = VOTableUtil.DataSet2VOTable(ds);
					FileStream fs2 = new FileStream(path+name,System.IO.FileMode.Create);
					XmlSerializer serializer2 = new XmlSerializer(typeof(VOTABLE));
					serializer2.Serialize(fs2,votable);
					fs2.Close();
					break;
				case "fits":
					break;
				default:
					throw new Exception(format+" format is not supported.");
			}
			return url+name;
		}
		private ArrayList Table2List(Table[] tables){
			string[] rst = new string[tables.Length];
			for(int x=0;x<rst.Length; x++)
				rst[x] = tables[x].Name;
			ArrayList list = new ArrayList();
			list.AddRange(rst);
			return list;
		}
		/// <summary>
		/// Revision from CVS
		/// </summary>
		public static string Revision {
			get {
				return "$Name $ $Revision: 1.32 $";
			}
		}
	}
}
#region CVS Revision History
/* Revision History
        $Log: TableJobs.asmx.cs,v $
        Revision 1.32  2008/04/11 18:16:13  nli
        *** empty log message ***

        Revision 1.31  2008/03/11 04:06:52  nli
        new web services added

        Revision 1.30  2007/05/10 00:59:24  nli
        tables that start with numbers handled correctly with mydbconent.aspx

        Revision 1.29  2007/05/05 00:27:33  nli
        --improved table to job tracking
        --removed 'tables' table

        Revision 1.28  2007/04/26 22:18:40  nli
        --server access is now tied to a users group[s]
        --'sight' and 'admin' are the only remaining user-level privs with any effect

        Revision 1.27  2007/02/02 17:23:34  thakar
        Changed "excede" to "exceed" (spelling).

        Revision 1.26  2007/01/02 20:33:17  nli
        mydbcontent.aspx now shows source queries for group tables.

        Revision 1.25  2006/09/14 16:34:32  nli
        minor cosmetic changes

        Revision 1.24  2006/09/13 15:47:40  nli
        back from summer
        Revision 1.23  2006/06/08 21:41:38  nli
        --select into now functions correctly
        --server is refered to as [server]
        --query to check mydb usage should work correctly when there are no allocated mydbs
        Revision 1.22  2006/03/09 19:47:49  nli
        --possibly fixed issue where output jobs were going to short queue
        Revision 1.21  2005/09/30 16:40:59  nli
        --plot conditions to mydb table page
        --mydb table page revamped.
        Revision 1.20  2005/08/25 20:25:58  nli
        --cannot drop/create index on a group table if not owner.
          fixes error where mydbcontent frame would not render a group table.
        Revision 1.19  2005/07/20 19:10:48  nli
        --sql case should be preserved.
        --mydbcontent page updated.
        ---link to created table
        --created table search optimized.
        Revision 1.18  2005/07/19 20:12:03  nli
        --drop index option shows on columns that have an index.
        --show x rows added to mydbcontent
        --preview removed.
        Revision 1.17  2005/06/01 18:15:58  womullan
         PR:6423 - you may now see the creating code of the group table you are viewing. Also some general comments added
        Revision 1.16  2005/02/28 18:15:22  womullan
         added getRev for updates
        Revision 1.15  2005/02/10 16:44:23  womullan
         MYdb Nolonger appears for Neighbors search - PR 6322
        Revision 1.14  2005/02/09 18:57:21  nli
        pr 6502
        added src table for views
        Revision 1.13  2005/02/08 23:00:04  womullan
         Now get correct Create staement for Table that was created PR 6321
        Revision 1.12  2005/02/08 22:15:53  womullan
         Fixed seeing others query for my table PR 6340
        Revision 1.11  2004/12/16 20:45:13  nli
        little bit o error handling in queryfileoutput
        Revision 1.10  2004/11/15 19:06:32  womullan
        qry
        Revision 1.9  2004/09/22 16:32:21  nli
        --another cjobscl update
        --some faq checks
        --some session over queries in mydbbrowser
        --fixed bug where cookies were overriding resubmits
        Revision 1.8  2004/07/09 01:04:51  nli
        -added omni browser
        -misc small stuff
        Revision 1.7  2004/06/23 23:39:16  nli
        subsitute ' target.'
        Revision 1.6  2004/06/22 15:19:48  nli
        oops
        Revision 1.5  2004/06/22 15:17:58  nli
        uncommented old output
        Revision 1.4  2004/06/21 13:24:48  womullan
        Fits back in
        Revision 1.3  2004/06/15 19:03:26  nli
        -cleaned up some query editing.
        -sps and functions might work
        -can do into mydb.table as just into table
        -new visibility setting for extracts
        -implemented intant extracts with row limiting
        -implented extract type availability based on table size
        Revision 1.2  2004/06/09 20:47:13  womullan
         DDL allowed in special cases
        Revision 1.1.1.1  2004/04/27 16:38:29  cvsuser
        import CasJobs to FERMI
        Revision 1.4  2004/03/16 18:31:11  nli
        a table entry is not created if that table already exists
        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/27 19:28:05  nli
        yup
        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.17  2003/11/26 22:06:41  womullan
         added name some places
        Revision 1.16  2003/09/22 14:33:45  nli
        service exception handling
        Revision 1.15  2003/09/17 18:21:47  nli
        *** empty log message ***
        Revision 1.14  2003/09/17 18:05:09  nli
        output stuff
        Revision 1.13  2003/09/03 17:38:47  nli
        estimate param removed from SubmitShortJob
        Revision 1.12  2003/08/29 18:08:00  nli

        Revision 1.11  2003/08/20 18:31:54  nli
        test
        Revision 1.10  2003/08/19 20:25:08  nli
        publish table is working.
        unpublish table is working.
        Revision 1.9  2003/08/19 19:10:56  nli
        test
        Revision 1.8  2003/08/18 21:24:01  nli
        some publish stuff, destroy, some service stuff
        Revision 1.7  2003/07/21 19:57:58  nli
        untested outputcore
        Revision 1.6  2003/07/18 21:41:13  womullan
        extract enters job - tided up somewhat
        Revision 1.5  2003/07/18 16:38:23  womullan
         closer
        Revision 1.4  2003/07/18 15:24:26  womullan
        Extraction page done
        Revision 1.3  2003/07/17 21:12:59  womullan
        extract table list works
        Revision 1.1  2003/07/16 21:38:18  womullan
         added extract table
     
*/
#endregion
