using System;
using System.Data;
using System.Text;
using edu.jhu.Cas.Util;
namespace edu.jhu.Cas.Users {
	public class JobUsers : SqlJob {
		public JobUsers():base() {
			init();
		}
		public const string MyDBConnectionStatement = @"
select u.mydbname,m.connectionstring 
from mydbhosts m,users u 
where u.webservicesid = "+WEB_SERVICES_ID+@"
and u.mydbhost = m.mydbhost";
		public const string MyDBConnectionLoginStatement = @"
select m.[user],m.pass from mydbhosts m,users u
where u.webservicesid = "+WEB_SERVICES_ID+" and u.mydbhost = m.mydbhost";
		public const string MyDBServerStatement = @"
select server from mydbhosts m, users u
where u.webservicesid = "+WEB_SERVICES_ID+" and u.mydbhost = m.mydbhost";
		public const string HasPrivilegeStatement = @"
select webservicesid from users where
privileges like '%"+PRIVILEGES+"%' and webservicesid = "+WEB_SERVICES_ID;
		public const string MyDBHostStatement = @"
select mydbhost from users where webservicesid = "+WEB_SERVICES_ID;
		public const string MyDBNameStatement = @"
select mydbname from users where userid = "+USERID;
		public const string MyDBNameStatementWS = @"
select mydbname from users where webservicesid = "+WEB_SERVICES_ID;
		public string CreateAccountStatement {
			get {
				StringBuilder builder = new StringBuilder();
				builder.Append("insert into users (");
				string tmp;
				foreach(Object o in base.Keys) {
					tmp = o.ToString();
					if(tmp.CompareTo(MAX_SIZE) == 0)continue;
					tmp = tmp.Replace("@","");
					builder.Append(tmp);
					builder.Append(",");
				}
				//builder.Remove(builder.Length -1,1);
				builder.Append("lastmodified ");
				builder.Append(") VALUES (");
				foreach(Object o in base.Keys) {
					if(o.ToString().CompareTo(MAX_SIZE) == 0)continue;
					builder.Append(o);
					builder.Append(",");
				}
				//builder.Remove(builder.Length -1,1);
				builder.Append("getdate()");
				builder.Append(")");
				return builder.ToString();
			}
		}
		public const string GetWebServicesIdStatement = @"
Select webservicesid from users 
where PWEncrypted = "+PASSWORD+@"
and (userid = "+USERID+@"
or email like "+EMAIL+")";

		public string ReplicateAccountStatement{
			get{
				StringBuilder builder = new StringBuilder();
				builder.Append("insert into users (");
				string tmp;
				foreach(Object o in base.Keys) {
					tmp = o.ToString();
					tmp = tmp.Replace("@","");
					builder.Append(tmp);
					builder.Append(",");
				}
				//builder.Remove(builder.Length -1,1);
				builder.Append("lastmodified ");
				builder.Append(") VALUES (");
				foreach(Object o in base.Keys) {
					builder.Append(o);
					builder.Append(",");
				}
				//builder.Remove(builder.Length -1,1);
				builder.Append("getdate()");
				builder.Append(")");
				return builder.ToString();
			}
		}
	
		public const string FindUserStatement= "Select * from users ";
		public const string GetUniqueUserIdStatement = "select webservicesid from users where userid = "+USERID;
        public const string UpdateAccountAdminStatement = @"
update users
set userid =" + USERID + @"
,name =" + NAME + @"
,email =" + EMAIL + @"
,pwencrypted =" + PASSWORD + @"
, privileges =" + PRIVILEGES + @"
, emaillevel =" + EMAIL_LEVEL + @"
, gvisible = " + GVISIBLE + @"
, lastmodified =getdate()" + @"
 where webservicesid = " + WEB_SERVICES_ID;
        public const string UpdateAccountStatement =@"
update users
set userid ="+USERID+@"
,name ="+NAME+@"
,email ="+EMAIL+@"
,pwencrypted ="+PASSWORD+@"
, emaillevel ="+EMAIL_LEVEL+@"
, gvisible = " + GVISIBLE+@"
, lastmodified =getdate()"+@"
 where webservicesid = "+WEB_SERVICES_ID;
		public const string HasMyDBStatement = @"
select mydbname from users 
where mydbname is not null and webservicesid = "+ WEB_SERVICES_ID;

        public const string CreateMyDBStatement = @"
create database " + DB_NAME + @"
on (NAME = " + DB_NAME + @", 
FILENAME ='" + DB_FILENAME + @"',
SIZE = " + SIZE + @", 
FILEGROWTH = 5)";
				
		public void addCreateMyDBParams(string dbName, string dbFileName, int size, int max_size) {
			Add(SIZE, new JobInfo(SqlDbType.Int, 4,size));
			Add(MAX_SIZE, new JobInfo(SqlDbType.Int, 4,max_size));
			Add(DB_NAME, new JobInfo(SqlDbType.VarChar, 100,dbName));
			Add(DB_FILENAME, new JobInfo(SqlDbType.Int, 100,dbFileName));
		}
		public void init() {
			Add(USERID, new JobInfo(SqlDbType.VarChar, 32, ""));
			Add(NAME, new JobInfo(SqlDbType.VarChar, 80, ""));
			Add(EMAIL, new JobInfo(SqlDbType.VarChar, 80, ""));
			Add(PASSWORD, new JobInfo(SqlDbType.VarChar,50,""));
			Add(WEB_SERVICES_ID, new JobInfo(SqlDbType.BigInt, 0, 0));
			Add(PRIVILEGES, new JobInfo(SqlDbType.VarChar, 200, ""));
			Add(CREATED_BY, new JobInfo(SqlDbType.BigInt, 0, 0));
			Add(EMAIL_LEVEL, new JobInfo(SqlDbType.Int, 0, 0));
			Add(MAX_SIZE, new JobInfo(SqlDbType.Int, 0, 0));
            Add(GVISIBLE, new JobInfo(SqlDbType.Bit, 0, 0));
		}
	
		/** begin package keys **/
		public const string SIZE = "@size";
        public const string MAX_SIZE = "@max_size";
        public const string DB_NAME = "@dbname";
        public const string DB_FILENAME = "@DBfilename";
        public const string USERID = "@UserId";
        public const string NAME = "@Name";
        public const string EMAIL = "@Email";
        public const string PASSWORD = "@pwencrypted";
        public const string WEB_SERVICES_ID = "@WebServicesID";
        public const string PRIVILEGES = "@Privileges";
        public const string TIME_CREATED = "@TimeCreated";
        public const string CREATED_BY = "@CreatedBy";
        public const string EMAIL_LEVEL = "@EmailLevel";
        public const string GVISIBLE = "@gvisible";
		/**end package keys*/
	}
}
