public bool CalendarExists()
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try {
strSql = "Select Count(*) From [CalendarEvent] " +
"WHERE [EventID]=@EventID ";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.Connection = con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int));
com.Parameters["@EventID"].Value = EventID;
object o = com.ExecuteScalar();
if (Convert.ToInt32(o) > 0) {
return true;
}
return false;
} catch (SqlException ex) {
Message = ex.Message;
return false;
} finally {
if (com != null) {
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool UpdateData()
{
string sql="";
SqlCommand com=null;
Connection con=null;
try
{
sql = "Update [Contact] Set ";
sql += "LastName=@LastName, ";
sql += "LastNamePhonetic=@LastNamePhonetic, ";
sql += "LastNameRomaji=@LastNameRomaji, ";
sql += "FirstName=@FirstName, ";
sql += "FirstNamePhonetic=@FirstNamePhonetic, ";
sql += "FirstNameRomaji=@FirstNameRomaji, ";
sql += "NickName=@NickName, ";
sql += "CompanyName=@CompanyName, ";
sql += "CompanyNamePhonetic=@CompanyNamePhonetic, ";
sql += "CompanyNameRomaji=@CompanyNameRomaji, ";
sql += "TitleForName=@TitleForName, ";
sql += "TitleForJob=@TitleForJob, ";
sql += "Street1=@Street1, ";
sql += "Street2=@Street2, ";
sql += "Street3=@Street3, ";
sql += "City=@City, ";
sql += "State=@State, ";
sql += "PostalCode=@PostalCode, ";
sql += "Country=@Country, ";
sql += "ContactType=@ContactType, ";
sql += "BlockCode=@BlockCode, ";
sql += "Email1=@Email1, ";
sql += "Email2=@Email2, ";
sql += "AccountRepLastName=@AccountRepLastName, ";
sql += "AccountRepLastNamePhonetic=@AccountRepLastNamePhonetic, ";
sql += "AccountRepLastNameRomaji=@AccountRepLastNameRomaji, ";
sql += "AccountRepFirstName=@AccountRepFirstName, ";
sql += "AccountRepFirstNamePhonetic=@AccountRepFirstNamePhonetic, ";
sql += "AccountRepFirstNameRomaji=@AccountRepFirstNameRomaji, ";
sql += "Phone1=@Phone1, ";
sql += "Phone2=@Phone2, ";
sql += "PhoneMobile1=@PhoneMobile1, ";
sql += "PhoneMobile2=@PhoneMobile2, ";
sql += "PhoneBusiness1=@PhoneBusiness1, ";
sql += "PhoneBusiness2=@PhoneBusiness2, ";
sql += "PhoneFax1=@PhoneFax1, ";
sql += "PhoneFax2=@PhoneFax2, ";
sql += "PhoneOther=@PhoneOther, ";
sql += "Url=@Url, ";
sql += "DateBirth=@DateBirth, ";
sql += "DateJoined=@DateJoined, ";
sql += "DateEnded=@DateEnded, ";
sql += "TimeStatus=@TimeStatus, ";
sql += "Nationality=@Nationality, ";
sql += "Married=@Married, ";
sql += "NumberDependents=@NumberDependents, ";
sql += "VisaStatus=@VisaStatus, ";
sql += "VisaFromDate=@VisaFromDate, ";
sql += "VisaUntilDate=@VisaUntilDate, ";
sql += "ClosestStation1=@ClosestStation1, ";
sql += "ClosestLine1=@ClosestLine1, ";
sql += "MinutesToStation1=@MinutesToStation1, ";
sql += "ClosestStation2=@ClosestStation2, ";
sql += "ClosestLine2=@ClosestLine2, ";
sql += "MinutesToStation2=@MinutesToStation2, ";
sql += "ContactStatus=@ContactStatus, ";
sql += "DateLastModified=@DateLastModified, ";
sql += "LastModifiedByUserID=@LastModifiedByUserID, ";
sql += "BasePayField=@BasePayField ";
sql += "WHERE ContactID=@ContactID ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = sql;
com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@LastNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@LastNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TitleForName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TitleForJob", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street3", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Country", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@BlockCode", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Email1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Email2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Phone1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Phone2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneMobile1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneMobile2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneBusiness1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneBusiness2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneFax1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneFax2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneOther", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@DateBirth", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateJoined", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateEnded", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@TimeStatus", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Nationality", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Married", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@NumberDependents", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@VisaStatus", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@VisaFromDate", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@VisaUntilDate", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@ClosestStation1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ClosestLine1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@MinutesToStation1", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ClosestStation2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ClosestLine2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@MinutesToStation2", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ContactStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@BasePayField", SqlDbType.Decimal));
com.Parameters["@ContactID"].Value = _contactid;
com.Parameters["@LastName"].Value = LastName;
com.Parameters["@LastNamePhonetic"].Value = LastNamePhonetic;
com.Parameters["@LastNameRomaji"].Value = LastNameRomaji;
com.Parameters["@FirstName"].Value = FirstName;
com.Parameters["@FirstNamePhonetic"].Value = FirstNamePhonetic;
com.Parameters["@FirstNameRomaji"].Value = FirstNameRomaji;
com.Parameters["@NickName"].Value = NickName;
com.Parameters["@CompanyName"].Value = CompanyName;
com.Parameters["@CompanyNamePhonetic"].Value = CompanyNamePhonetic;
com.Parameters["@CompanyNameRomaji"].Value = CompanyNameRomaji;
com.Parameters["@TitleForName"].Value = TitleForName;
com.Parameters["@TitleForJob"].Value = TitleForJob;
com.Parameters["@Street1"].Value = Street1;
com.Parameters["@Street2"].Value = Street2;
com.Parameters["@Street3"].Value = Street3;
com.Parameters["@City"].Value = City;
com.Parameters["@State"].Value = State;
com.Parameters["@PostalCode"].Value = PostalCode;
com.Parameters["@Country"].Value = Country;
com.Parameters["@ContactType"].Value = ContactType;
com.Parameters["@BlockCode"].Value = BlockCode;
com.Parameters["@Email1"].Value = Email1;
com.Parameters["@Email2"].Value = Email2;
com.Parameters["@AccountRepLastName"].Value = AccountRepLastName;
com.Parameters["@AccountRepLastNamePhonetic"].Value = AccountRepLastNamePhonetic;
com.Parameters["@AccountRepLastNameRomaji"].Value = AccountRepLastNameRomaji;
com.Parameters["@AccountRepFirstName"].Value = AccountRepFirstName;
com.Parameters["@AccountRepFirstNamePhonetic"].Value = AccountRepFirstNamePhonetic;
com.Parameters["@AccountRepFirstNameRomaji"].Value = AccountRepFirstNameRomaji;
com.Parameters["@Phone1"].Value = Phone1;
com.Parameters["@Phone2"].Value = Phone2;
com.Parameters["@PhoneMobile1"].Value = PhoneMobile1;
com.Parameters["@PhoneMobile2"].Value = PhoneMobile2;
com.Parameters["@PhoneBusiness1"].Value = PhoneBusiness1;
com.Parameters["@PhoneBusiness2"].Value = PhoneBusiness2;
com.Parameters["@PhoneFax1"].Value = PhoneFax1;
com.Parameters["@PhoneFax2"].Value = PhoneFax2;
com.Parameters["@PhoneOther"].Value = PhoneOther;
com.Parameters["@Url"].Value = Url;
if(DateBirth==Convert.ToDateTime(null))
com.Parameters["@DateBirth"].Value = System.DBNull.Value;
else
com.Parameters["@DateBirth"].Value = DateBirth;
if(DateJoined==Convert.ToDateTime(null))
com.Parameters["@DateJoined"].Value = System.DBNull.Value;
else
com.Parameters["@DateJoined"].Value = DateJoined;
if(DateEnded==Convert.ToDateTime(null))
com.Parameters["@DateEnded"].Value = System.DBNull.Value;
else
com.Parameters["@DateEnded"].Value = DateEnded;
com.Parameters["@TimeStatus"].Value = TimeStatus;
com.Parameters["@Nationality"].Value = Nationality;
com.Parameters["@Married"].Value = Married;
com.Parameters["@NumberDependents"].Value = NumberDependents;
com.Parameters["@VisaStatus"].Value = VisaStatus;
if(VisaFromDate==Convert.ToDateTime(null))
com.Parameters["@VisaFromDate"].Value = System.DBNull.Value;
else
com.Parameters["@VisaFromDate"].Value = VisaFromDate;
if(VisaUntilDate==Convert.ToDateTime(null))
com.Parameters["@VisaUntilDate"].Value = System.DBNull.Value;
else
com.Parameters["@VisaUntilDate"].Value = VisaUntilDate;
com.Parameters["@ClosestStation1"].Value = ClosestStation1;
com.Parameters["@ClosestLine1"].Value = ClosestLine1;
com.Parameters["@MinutesToStation1"].Value =MinutesToStation1;
com.Parameters["@ClosestStation2"].Value = ClosestStation2;
com.Parameters["@ClosestLine2"].Value = ClosestLine2;
com.Parameters["@MinutesToStation2"].Value = MinutesToStation2;
com.Parameters["@ContactStatus"].Value = ContactStatus;
com.Parameters["@DateCreated"].Value = DateTime.Now;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@BasePayField"].Value = BaseRate;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public string getOccurrenceCount(int eventid)
{
SqlCommand com = null;
Connection con = null;
//SqlDataReader Reader = null;
string strSQL = string.Empty;
string strResult = string.Empty;
int result1;
int result2;
try
{
strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND StartDateTime < GetDate() AND CalendarEventStatus = 0;";
con = new Connection();
con.Connect();
com = new SqlCommand(strSQL, con.SQLCon);
com.Parameters.Add(new SqlParameter("@EventId", SqlDbType.Int));
com.Parameters["@EventId"].Value = eventid;
result1 = (int)com.ExecuteScalar();
//strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND EndDateTime >= GetDate() AND CalendarEventStatus = 0;";
strSQL = "Select Count(*) from [CalendarEvent] WHERE EventId=@EventId AND CalendarEventStatus = 0;";
com.CommandText = strSQL;
//com.Parameters["@EventId"].Value = eventid;
result2 = (int)com.ExecuteScalar();
//strResult = result1.ToString() + " / " + (result1 + result2).ToString();
strResult = result1.ToString() + " / " + (result2).ToString();
return strResult;
}
catch (SqlException ex)
{
Message = ex.Message;
return "Error!";
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public string getEventText(int eventid, ref string startdate, ref string enddate)
{
string Result="";
string strSql="";
SqlCommand com=null;
Connection con=null;
SqlDataReader Reader=null;
DateTime dtStart=Convert.ToDateTime(null);
DateTime dtEnd=Convert.ToDateTime(null);
try
{
strSql = "Select StartDateTime, EndDateTime From [CalendarEvent] ";
strSql += "WHERE EventID=@EventID";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.BigInt));
com.Parameters["@EventID"].Value = eventid;
Reader=com.ExecuteReader();
bool IsRecord=false;
if(Reader.Read())
{
IsRecord=true;
if(Reader["StartDateTime"]!=System.DBNull.Value)
{
dtStart = Convert.ToDateTime(Reader["StartDateTime"].ToString());
}
if(Reader["EndDateTime"]!=System.DBNull.Value)
{
dtEnd = Convert.ToDateTime(Reader["EndDateTime"].ToString());
}
}
Reader.Close();
if(IsRecord)
{
if(dtStart!=Convert.ToDateTime(null))
{
Result = dtStart.ToShortDateString() + " " + dtStart.ToShortTimeString();
if(Result.IndexOf("(")>0)
{
Result = Result.Substring(0, Result.IndexOf("(")+1);
}
startdate = Result;
}
if(dtEnd!=Convert.ToDateTime(null))
{
Result = dtEnd.ToShortDateString() + " " + dtEnd.ToShortTimeString();
if(Result.IndexOf("(")>0)
{
Result = Result.Substring(0, Result.IndexOf("(")+1);
}
enddate = Result;
}
}
else Result="None";
return Result;
}
catch(SqlException ex)
{
Message=ex.Message;
return "";
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool Exists()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Select Count(*) From [Course] " +
"WHERE [Name]=@Name and ProgramID=@ProgramID and CourseId<>" + _courseid + " ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
com.Parameters["@Name"].Value = _name;
com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int));
com.Parameters["@ProgramID"].Value = _programid;
object o = com.ExecuteScalar();
if(Convert.ToInt32(o)>0)
{
return true;
}
return false;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
/// <summary>
/// Checks whether Test Initial, Mid or Final are set for a Class or not.
/// </summary>
/// <returns>A boolean array with Test Event Ids or 0 if no events exist.</returns>
public bool[] CheckTestEvents()
{
string strSql = "";
SqlDataReader Reader = null;
SqlCommand com = null;
Connection con = null;
try
{
strSql = "Select TestInitialEventId,TestMidtermEventId,TestFinalEventId from [Course] WHERE CourseId=@CourseId;";
con = new Connection();
con.Connect();
com = new SqlCommand(strSql, con.SQLCon);
com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int));
com.Parameters["@CourseId"].Value = _courseid;
Reader = com.ExecuteReader();
string[] temp = new string[3];
bool[] boolArray = { false, false, false };
Reader.Read();
temp[0] = Reader[0].ToString();
temp[1] = Reader[1].ToString();
temp[2] = Reader[2].ToString();
Reader.Close();
IDataReader readerTemp = null;
if (temp[0] != null && temp[0] != "" && temp[0] != "0")
{
readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[0]);
if (readerTemp.Read())
{
boolArray[0] = true;
}
else
{
DAC.EXQuery("Update [Course] Set TestInitialEventId = 0 Where CourseId = " + _courseid);
boolArray[0] = false;
}
}
if (temp[1] != null && temp[1] != "" && temp[1] != "0")
{
readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[1]);
if (readerTemp.Read())
{
boolArray[1] = true;
}
else
{
DAC.EXQuery("Update [Course] Set TestMidtermEventId = 0 Where CourseId = " + _courseid);
boolArray[1] = false;
}
}
if (temp[2] != null && temp[2] != "" && temp[2] != "0")
{
readerTemp = DAC.SelectStatement("Select * From Event Where EventID = " + temp[2]);
if (readerTemp.Read())
{
boolArray[2] = true;
}
else
{
DAC.EXQuery("Update [Course] Set TestFinalEventId = 0 Where CourseId = " + _courseid);
boolArray[2] = false;
}
}
return boolArray;
}
catch (SqlException ex)
{
Message = ex.Message;
return null;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool UpdateData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Update [Course] Set " +
"Name=@Name, " +
"NamePhonetic=@NamePhonetic, " +
"NameRomaji=@NameRomaji, " +
"NickName=@NickName, " +
"ProgramID=@ProgramID, " +
"EventID=@EventID, " +
"Description=@Description, " +
"SpecialRemarks=@SpecialRemarks, " +
"CourseType=@CourseType, " +
"Curriculam=@Curriculam, " +
"NumberStudents=@NumberStudents, " +
"HomeWorkMinutes=@HomeWorkMinutes, " +
"TestInitialEventID=@TestInitialEventID, " +
"TestMidtermEventID=@TestMidtermEventID, " +
"TestFinalEventID=@TestFinalEventID, " +
"TestInitialForm=@TestInitialForm, " +
"TestMidtermForm=@TestMidtermForm, " +
"TestFinalForm=@TestFinalForm, " +
"CourseStatus=@CourseStatus, " +
"DateLastModified=@DateLastModified, " +
"LastModifiedByUserID=@LastModifiedByUserID, " +
"BreakDuration=@BreakDuration " +
"WHERE CourseId=@CourseId ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@SpecialRemarks", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CourseType", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Curriculam", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NumberStudents", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@HomeWorkMinutes", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestInitialEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestMidtermEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestFinalEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestInitialForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TestMidtermForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TestFinalForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CourseStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@BreakDuration", SqlDbType.Int));
com.Parameters["@CourseId"].Value = _courseid;
com.Parameters["@Name"].Value = _name;
com.Parameters["@NamePhonetic"].Value = _namephonetic;
com.Parameters["@NameRomaji"].Value = _nameromaji;
com.Parameters["@NickName"].Value = _nickname;
com.Parameters["@ProgramID"].Value = _programid;
com.Parameters["@EventID"].Value = _eventid;
com.Parameters["@Description"].Value = _description;
com.Parameters["@SpecialRemarks"].Value = _specialremarks;
com.Parameters["@CourseType"].Value = _coursetype;
com.Parameters["@Curriculam"].Value = _curriculam;
com.Parameters["@NumberStudents"].Value = _numberstudents;
com.Parameters["@HomeWorkMinutes"].Value = _homeworkminutes;
com.Parameters["@TestInitialEventID"].Value = _testinieventid;
com.Parameters["@TestMidtermEventID"].Value = _testmideventid;
com.Parameters["@TestFinalEventID"].Value = _testfinaleventid;
com.Parameters["@TestInitialForm"].Value = _testiniform;
com.Parameters["@TestMidtermForm"].Value = _testmidform;
com.Parameters["@TestFinalForm"].Value = _testfinalform;
com.Parameters["@CourseStatus"].Value = _coursestatus;
com.Parameters["@DateCreated"].Value = DateTime.Now;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@BreakDuration"].Value = _breakduration;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
//ViewClassEventsN
public bool LoadDataN()
{
string strSql = "select * From ViewClassEventsN ";
SqlDataReader Reader = null;
SqlCommand com = null;
Connection con = null;
try
{
/*
if (_courseid <= 0)
{
strSql += " Order By BrowseName ";
}
else
{
strSql += String.Format(" Where C.CourseId= {0} ", _courseid);
}
*/
BuildDataTable();
/*
con = new Connection();
con.Connect();
com = new SqlCommand();
com.Connection = con.SQLCon;
com.CommandText = strSql;
Reader = com.ExecuteReader();
*/
///*
strSql = "SpClassEventsN";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.CommandType = CommandType.StoredProcedure;
com.Connection = con.SQLCon;
com.CommandText = strSql;
Reader = com.ExecuteReader();
//*/
_dtbl.Load(Reader, LoadOption.OverwriteChanges);
return true;
}
catch (SqlException ex)
{
Message = ex.Message;
return false;
}
finally
{
if (!Reader.IsClosed)
{
Reader.Close();
}
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public DataTable LoadData(string option)
{
string strSql="";
int contacttypeid=0;
SqlCommand com=null;
Connection con=null;
SqlDataAdapter adpt=null;
if(option=="User") contacttypeid=0;
if(option=="Instructor") contacttypeid=1;
if(option=="Client") contacttypeid=2;
if(option=="Department") contacttypeid=3;
if(option=="ClientContact") contacttypeid=4;
if(option=="DepartmentContact") contacttypeid=5;
if(_dtbl==null)
{
_dtbl=new DataTable();
}
try
{
if(_contactid<=0)
{
strSql = "select BrowseName = CASE " +
"WHEN NickName IS NULL THEN CompanyName " +
"WHEN NickName = '' THEN CompanyName " +
"ELSE NickName " +
"END, " +
"*, " +
"Type = " +
"CASE ContactType " +
"When '0' Then 'User' " +
"When '1' Then 'Teacher' " +
"When '2' Then 'Client' " +
"When '3' Then 'Department' " +
"END, " +
"Status = " +
"CASE ContactStatus " +
"When '0' Then 'Active' " +
"When '1' Then 'Inactive' " +
"END, " +
"MaritalStatus = " +
"CASE Married " +
"When '0' Then 'Yes' " +
"When '1' Then 'No' " +
"ELSE '' " +
"END,Email1,Email2,Phone1,Phone2,PhoneMobile1,PhoneMobile2,PhoneBusiness1,PhoneBusiness2, CASE When (AccountRepLastName + ', ' + AccountRepFirstName) <> ', ' Then (AccountRepLastName + ', ' + AccountRepFirstName) Else '' End as AccRepName " +
"From Contact ";
if(option!="Contact")
{
strSql += "Where ContactType = " + contacttypeid.ToString() + " ";
if(RefID>0)
{
strSql += "and RefID = " + RefID.ToString() + " ";
}
}
if(option=="Instructor")
{
strSql += "Order By LastName, FirstName ";
}
else if(option=="Client")
{
strSql += "Order By BrowseName ";
}
else if(option=="DepartmentContact")
{
strSql += "Order By LastName, FirstName ";
}
else
{
strSql += "Order By ContactID";
}
}
else
{
strSql = "select BrowseName = CASE " +
"WHEN NickName IS NULL THEN CompanyName " +
"WHEN NickName = '' THEN CompanyName " +
"ELSE NickName " +
"END, " +
"*, " +
"Type = " +
"CASE ContactType " +
"When '0' Then 'User' " +
"When '1' Then 'Teacher' " +
"When '2' Then 'Client' " +
"When '3' Then 'Department' " +
"END, " +
"Status = " +
"CASE ContactStatus " +
"When '0' Then 'Active' " +
"When '1' Then 'Inactive' " +
"END, " +
"MaritalStatus = " +
"CASE Married " +
"When '0' Then 'Yes' " +
"When '1' Then 'No' " +
"ELSE '' " +
"END, CASE When (AccountRepLastName + ', ' + AccountRepFirstName) <> ', ' Then (AccountRepLastName + ', ' + AccountRepFirstName) Else '' End as AccRepName " +
"From Contact " +
"WHERE ContactID = " + _contactid + " ";
}
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
adpt=new SqlDataAdapter();
adpt.SelectCommand = com;
adpt.Fill(_dtbl);
if(contacttypeid==2)
{
_dtbl.Columns.Add("Contact1", Type.GetType("System.String"));
_dtbl.Columns.Add("Contact2", Type.GetType("System.String"));
_dtbl.Columns.Add("Contact1Phone", Type.GetType("System.String"));
_dtbl.Columns.Add("Contact2Phone", Type.GetType("System.String"));
//Get the contacts
int contid=0;
string contact1="";
string contact2="";
string phone1="";
string phone2="";
foreach(DataRow dr in _dtbl.Rows)
{
contid=0;
contact1="";
contact2="";
phone1="";
phone2="";
contid=Convert.ToInt32(dr["ContactID"].ToString());
GetContact(contid, ref contact1, ref contact2, ref phone1, ref phone2);
dr["Contact1"] = contact1;
dr["Contact2"] = contact2;
dr["Contact1Phone"] = phone1;
dr["Contact2Phone"] = phone2;
dr.AcceptChanges();
}
}
return _dtbl;
}
catch(SqlException ex)
{
Message=ex.Message;
return null;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
adpt.Dispose();
adpt=null;
}
}
}
public bool InsertData()
{
string sql="";
SqlCommand com=null;
Connection con=null;
SqlDataReader Reader=null;
try
{
sql = "Insert into [Contact] (";
sql += "RefID, ";
sql += "LastName, ";
sql += "LastNamePhonetic, ";
sql += "LastNameRomaji, ";
sql += "FirstName, ";
sql += "FirstNamePhonetic, ";
sql += "FirstNameRomaji, ";
sql += "NickName, ";
sql += "CompanyName, ";
sql += "CompanyNamePhonetic, ";
sql += "CompanyNameRomaji, ";
sql += "TitleForName, ";
sql += "TitleForJob, ";
sql += "Street1, ";
sql += "Street2, ";
sql += "Street3, ";
sql += "City, ";
sql += "State, ";
sql += "PostalCode, ";
sql += "Country, ";
sql += "ContactType, ";
sql += "BlockCode, ";
sql += "Email1, ";
sql += "Email2, ";
sql += "AccountRepLastName, ";
sql += "AccountRepLastNamePhonetic, ";
sql += "AccountRepLastNameRomaji, ";
sql += "AccountRepFirstName, ";
sql += "AccountRepFirstNamePhonetic, ";
sql += "AccountRepFirstNameRomaji, ";
sql += "Phone1, ";
sql += "Phone2, ";
sql += "PhoneMobile1, ";
sql += "PhoneMobile2, ";
sql += "PhoneBusiness1, ";
sql += "PhoneBusiness2, ";
sql += "PhoneFax1, ";
sql += "PhoneFax2, ";
sql += "PhoneOther, ";
sql += "Url, ";
sql += "DateBirth, ";
sql += "DateJoined, ";
sql += "DateEnded, ";
sql += "TimeStatus, ";
sql += "Nationality, ";
sql += "Married, ";
sql += "NumberDependents, ";
sql += "VisaStatus, ";
sql += "VisaFromDate, ";
sql += "VisaUntilDate, ";
sql += "ClosestStation1, ";
sql += "ClosestLine1, ";
sql += "MinutesToStation1, ";
sql += "ClosestStation2, ";
sql += "ClosestLine2, ";
sql += "MinutesToStation2, ";
sql += "ContactStatus, ";
sql += "CreatedByUserId, ";
sql += "DateCreated, ";
sql += "DateLastModified, ";
sql += "LastModifiedByUserID, ";
sql += "BasePayField ";
sql += ")";
sql += "Values( ";
sql += "@RefID, ";
sql += "@LastName, ";
sql += "@LastNamePhonetic, ";
sql += "@LastNameRomaji, ";
sql += "@FirstName, ";
sql += "@FirstNamePhonetic, ";
sql += "@FirstNameRomaji, ";
sql += "@NickName, ";
sql += "@CompanyName, ";
sql += "@CompanyNamePhonetic, ";
sql += "@CompanyNameRomaji, ";
sql += "@TitleForName, ";
sql += "@TitleForJob, ";
sql += "@Street1, ";
sql += "@Street2, ";
sql += "@Street3, ";
sql += "@City, ";
sql += "@State, ";
sql += "@PostalCode, ";
sql += "@Country, ";
sql += "@ContactType, ";
sql += "@BlockCode, ";
sql += "@Email1, ";
sql += "@Email2, ";
sql += "@AccountRepLastName, ";
sql += "@AccountRepLastNamePhonetic, ";
sql += "@AccountRepLastNameRomaji, ";
sql += "@AccountRepFirstName, ";
sql += "@AccountRepFirstNamePhonetic, ";
sql += "@AccountRepFirstNameRomaji, ";
sql += "@Phone1, ";
sql += "@Phone2, ";
sql += "@PhoneMobile1, ";
sql += "@PhoneMobile2, ";
sql += "@PhoneBusiness1, ";
sql += "@PhoneBusiness2, ";
sql += "@PhoneFax1, ";
sql += "@PhoneFax2, ";
sql += "@PhoneOther, ";
sql += "@Url, ";
sql += "@DateBirth, ";
sql += "@DateJoined, ";
sql += "@DateEnded, ";
sql += "@TimeStatus, ";
sql += "@Nationality, ";
sql += "@Married, ";
sql += "@NumberDependents, ";
sql += "@VisaStatus, ";
sql += "@VisaFromDate, ";
sql += "@VisaUntilDate, ";
sql += "@ClosestStation1, ";
sql += "@ClosestLine1, ";
sql += "@MinutesToStation1, ";
sql += "@ClosestStation2, ";
sql += "@ClosestLine2, ";
sql += "@MinutesToStation2, ";
sql += "@ContactStatus, ";
sql += "@CreatedByUserId, ";
sql += "@DateCreated, ";
sql += "@DateLastModified, ";
sql += "@LastModifiedByUserID, ";
sql += "@BasePayField ";
sql += ") ";
sql += "SELECT @@IDENTITY";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = sql;
com.Parameters.Add(new SqlParameter("@RefID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@LastNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@LastNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@FirstNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CompanyNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TitleForName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TitleForJob", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Street3", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@State", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Country", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@BlockCode", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Email1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Email2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepLastNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstNamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@AccountRepFirstNameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Phone1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Phone2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneMobile1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneMobile2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneBusiness1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneBusiness2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneFax1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneFax2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@PhoneOther", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@DateBirth", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateJoined", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateEnded", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@TimeStatus", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Nationality", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Married", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@NumberDependents", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@VisaStatus", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@VisaFromDate", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@VisaUntilDate", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@ClosestStation1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ClosestLine1", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@MinutesToStation1", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ClosestStation2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ClosestLine2", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@MinutesToStation2", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ContactStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@BasePayField", SqlDbType.Decimal));
com.Parameters["@RefID"].Value = RefID;
com.Parameters["@LastName"].Value = LastName;
com.Parameters["@LastNamePhonetic"].Value = LastNamePhonetic;
com.Parameters["@LastNameRomaji"].Value = LastNameRomaji;
com.Parameters["@FirstName"].Value = FirstName;
com.Parameters["@FirstNamePhonetic"].Value = FirstNamePhonetic;
com.Parameters["@FirstNameRomaji"].Value = FirstNameRomaji;
com.Parameters["@NickName"].Value = NickName;
com.Parameters["@CompanyName"].Value = CompanyName;
com.Parameters["@CompanyNamePhonetic"].Value = CompanyNamePhonetic;
com.Parameters["@CompanyNameRomaji"].Value = CompanyNameRomaji;
com.Parameters["@TitleForName"].Value = TitleForName;
com.Parameters["@TitleForJob"].Value = TitleForJob;
com.Parameters["@Street1"].Value = Street1;
com.Parameters["@Street2"].Value = Street2;
com.Parameters["@Street3"].Value = Street3;
com.Parameters["@City"].Value = City;
com.Parameters["@State"].Value = State;
com.Parameters["@PostalCode"].Value = PostalCode;
com.Parameters["@Country"].Value = Country;
com.Parameters["@ContactType"].Value = ContactType;
com.Parameters["@BlockCode"].Value = BlockCode;
com.Parameters["@Email1"].Value = Email1;
com.Parameters["@Email2"].Value = Email2;
com.Parameters["@AccountRepLastName"].Value = AccountRepLastName;
com.Parameters["@AccountRepLastNamePhonetic"].Value = AccountRepLastNamePhonetic;
com.Parameters["@AccountRepLastNameRomaji"].Value = AccountRepLastNameRomaji;
com.Parameters["@AccountRepFirstName"].Value = AccountRepFirstName;
com.Parameters["@AccountRepFirstNamePhonetic"].Value = AccountRepFirstNamePhonetic;
com.Parameters["@AccountRepFirstNameRomaji"].Value = AccountRepFirstNameRomaji;
com.Parameters["@Phone1"].Value = Phone1;
com.Parameters["@Phone2"].Value = Phone2;
com.Parameters["@PhoneMobile1"].Value = PhoneMobile1;
com.Parameters["@PhoneMobile2"].Value = PhoneMobile2;
com.Parameters["@PhoneBusiness1"].Value = PhoneBusiness1;
com.Parameters["@PhoneBusiness2"].Value = PhoneBusiness2;
com.Parameters["@PhoneFax1"].Value = PhoneFax1;
com.Parameters["@PhoneFax2"].Value = PhoneFax2;
com.Parameters["@PhoneOther"].Value = PhoneOther;
com.Parameters["@Url"].Value = Url;
if(DateBirth==Convert.ToDateTime(null))
com.Parameters["@DateBirth"].Value = DBNull.Value;
else
com.Parameters["@DateBirth"].Value = DateBirth;
if(DateJoined==Convert.ToDateTime(null))
com.Parameters["@DateJoined"].Value = DBNull.Value;
else
com.Parameters["@DateJoined"].Value = DateJoined;
if(DateEnded==Convert.ToDateTime(null))
com.Parameters["@DateEnded"].Value = DBNull.Value;
else
com.Parameters["@DateEnded"].Value = DateEnded;
com.Parameters["@TimeStatus"].Value = TimeStatus;
com.Parameters["@Nationality"].Value = Nationality;
com.Parameters["@Married"].Value = Married;
com.Parameters["@NumberDependents"].Value = NumberDependents;
com.Parameters["@VisaStatus"].Value = VisaStatus;
if(VisaFromDate==Convert.ToDateTime(null))
com.Parameters["@VisaFromDate"].Value = DBNull.Value;
else
com.Parameters["@VisaFromDate"].Value = VisaFromDate;
if(VisaUntilDate==Convert.ToDateTime(null))
com.Parameters["@VisaUntilDate"].Value = DBNull.Value;
else
com.Parameters["@VisaUntilDate"].Value = VisaUntilDate;
com.Parameters["@ClosestStation1"].Value = ClosestStation1;
com.Parameters["@ClosestLine1"].Value = ClosestLine1;
com.Parameters["@MinutesToStation1"].Value =MinutesToStation1;
com.Parameters["@ClosestStation2"].Value = ClosestStation2;
com.Parameters["@ClosestLine2"].Value = ClosestLine2;
com.Parameters["@MinutesToStation2"].Value = MinutesToStation2;
com.Parameters["@ContactStatus"].Value = ContactStatus;
com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@DateCreated"].Value = DateTime.Now;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@BasePayField"].Value = BaseRate;
Reader = com.ExecuteReader();
if(Reader.Read())
{
_contactid = Convert.ToInt32(Reader[0].ToString());
}
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(Reader!=null)
{
if(Reader.IsClosed==false)
{
Reader.Close();
Reader=null;
}
}
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool Exists(string strComp, int ClientID, int ContactType)
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Select Count(*) as cnt From [Contact] C, Department D " +
"Where C.ContactID=D.ContactID and " +
"C.ContactType=@ContactType and " +
"C.CompanyName=@CompanyName and " +
"D.ClientID=@ClientID ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.NVarChar));
com.Parameters["@CompanyName"].Value = strComp;
com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar));
com.Parameters["@ContactType"].Value = ContactType;
com.Parameters.Add(new SqlParameter("@ClientID", SqlDbType.BigInt));
com.Parameters["@ClientID"].Value = ClientID;
object o = com.ExecuteScalar();
if(Convert.ToInt32(o)>0)
{
return true;
}
return false;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool Exists(string FirstName, string LastName, int ContactType)
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Select Count(*) From [Contact] " +
"WHERE LastName=@LastName and FirstName=@FirstName And ContactType=@ContactType and ContactID<>" + _contactid + " ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar));
com.Parameters["@LastName"].Value = LastName;
com.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar));
com.Parameters["@FirstName"].Value = FirstName;
com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar));
com.Parameters["@ContactType"].Value = ContactType;
object o = com.ExecuteScalar();
if(Convert.ToInt32(o)>0)
{
return true;
}
return false;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public void DeleteContactFromProgram()
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try
{
strSql = "Update [Program] SET Contact1ID=0 WHERE Contact1ID=@ContactID;";
strSql += "Update [Program] SET Contact2ID=0 WHERE Contact2ID=@ContactID;";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.Connection = con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int));
com.Parameters["@ContactID"].Value = _contactid;
com.ExecuteNonQuery();
/*
strSql = "Delete from contact where RefID=@ContactID AND ContactType=4";
com.Parameters["@ContactID"].Value = _contactid;
com.CommandText = strSql;
com.ExecuteNonQuery();
*/
return;
}
catch (SqlException ex)
{
Message = ex.Message;
return;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool ContactExists(int refid, string fname, string lname)
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Select Count(*) From [Contact] " +
"WHERE FirstName=@FName AND LastName=@LName AND ContactType=@ContactType and RefID=@RefID ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@FName", SqlDbType.NVarChar));
com.Parameters["@FName"].Value = fname;
com.Parameters.Add(new SqlParameter("@LName", SqlDbType.NVarChar));
com.Parameters["@LName"].Value = lname;
com.Parameters.Add(new SqlParameter("@ContactType", SqlDbType.NVarChar));
com.Parameters["@ContactType"].Value = ContactType;
com.Parameters.Add(new SqlParameter("@RefID", SqlDbType.Int));
com.Parameters["@RefID"].Value = refid;
object o = com.ExecuteScalar();
if(Convert.ToInt32(o)>0)
{
return true;
}
return false;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool UpdateRefID(int randomno)
{
string sql="";
SqlCommand com=null;
Connection con=null;
try
{
sql = "Update [Contact] Set RefID=" + RefID.ToString() + " Where RefID=" + randomno.ToString() + " ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = sql;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
/// <summary>
/// Determines whether the class is a single event or a series of repeating events.
/// </summary>
/// <returns>A boolean indicating if a class repeats or not.</returns>
public bool IsRecurring()
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try
{
strSql = "Select Count(*) from [Event] WHERE RecurrenceText IS NOT NULL AND RecurrenceText<>'' AND EventId=" + _eventid + ";";
con = new Connection();
con.Connect();
com = new SqlCommand(strSql, con.SQLCon);
int result = (int)com.ExecuteScalar();
//Means the class reocurrs alright
if (result == 1)
return true;
else
return false;
}
catch (SqlException ex)
{
Message = ex.Message;
return false;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool LoadData()
{
string strSql="";
SqlDataReader Reader=null;
SqlCommand com=null;
Connection con=null;
try
{
if(_courseid<=0)
{
strSql += "Select C.*, CASE " +
"WHEN C.NickName IS NULL THEN C.Name " +
"WHEN C.NickName = '' THEN C.Name " +
"ELSE C.NickName " +
"END AS BrowseName, " +
"P.ProgramID, " +
"CASE " +
"WHEN P.NickName IS NULL THEN P.Name " +
"WHEN P.NickName = '' THEN P.Name " +
"ELSE P.NickName " +
"END AS Program, " +
"CASE " +
"WHEN CO.NickName IS NULL THEN CO.CompanyName " +
"WHEN CO.NickName = '' THEN CO.CompanyName " +
"ELSE CO.NickName " +
"END AS Department, " +
"CASE " +
"WHEN CO1.NickName IS NULL THEN CO1.CompanyName " +
"WHEN CO1.NickName = '' THEN CO1.CompanyName " +
"ELSE CO1.NickName " +
"END AS Client " +
"from Course C " +
"Left Join Program P on (C.ProgramID=P.ProgramID) " +
"Left Join Department D on (P.DepartmentID=D.DepartmentID) " +
"Left Join Contact CO on (D.ContactID=CO.ContactID) " +
"Left Join Contact CO1 on (D.ClientID=CO1.ContactID) " +
"Order By BrowseName ";
}
else
{
strSql += "Select C.*, CASE " +
"WHEN C.NickName IS NULL THEN C.Name " +
"WHEN C.NickName = '' THEN C.Name " +
"ELSE C.NickName " +
"END AS BrowseName, " +
"P.ProgramID, " +
"P.NickName, CASE " +
"WHEN P.NickName IS NULL THEN P.Name " +
"WHEN P.NickName = '' THEN P.Name " +
"ELSE P.NickName " +
"END AS Program, " +
"CASE " +
"WHEN CO.NickName IS NULL THEN CO.CompanyName " +
"WHEN CO.NickName = '' THEN CO.CompanyName " +
"ELSE CO.NickName " +
"END AS Department, " +
"CASE " +
"WHEN CO1.NickName IS NULL THEN CO1.CompanyName " +
"WHEN CO1.NickName = '' THEN CO1.CompanyName " +
"ELSE CO1.NickName " +
"END AS Client " +
"from Course C " +
"Left Join Program P on (C.ProgramID=P.ProgramID) " +
"Left Join Department D on (P.DepartmentID=D.DepartmentID) " +
"Left Join Contact CO on (D.ContactID=CO.ContactID) " +
"Left Join Contact CO1 on (D.ClientID=CO1.ContactID) " +
"Where C.CourseId=" + _courseid.ToString() + " ";
}
BuildDataTable();
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
Reader = com.ExecuteReader();
string strstatus="";
while(Reader.Read())
{
strstatus="";
if(Convert.ToInt16(Reader["CourseStatus"].ToString())==0)
{
strstatus = "Active";
}
else
{
strstatus = "Inactive";
}
_courseid = Convert.ToInt32(Reader["CourseId"].ToString());
_name = Reader["Name"].ToString();
_namephonetic = Reader["NamePhonetic"].ToString();
_nameromaji = Reader["NameRomaji"].ToString();
if(Reader["ProgramID"]!=System.DBNull.Value)
{
_programid = Convert.ToInt32(Reader["ProgramID"].ToString());
}
_program = Reader["Program"].ToString();
_nickname = Reader["NickName"].ToString();
_description = Reader["Description"].ToString();
_coursetype = Reader["CourseType"].ToString();
_specialremarks = Reader["SpecialRemarks"].ToString();
_curriculam = Reader["Curriculam"].ToString();
_numberstudents = Convert.ToInt32(Reader["NumberStudents"].ToString());
_homeworkminutes = Convert.ToInt32(Reader["HomeWorkMinutes"].ToString());
_eventid = Convert.ToInt32(Reader["EventID"].ToString());
_testinieventid = Convert.ToInt32(Reader["TestInitialEventID"].ToString());
_testmideventid = Convert.ToInt32(Reader["TestMidtermEventID"].ToString());
_testfinaleventid = Convert.ToInt32(Reader["TestFinalEventID"].ToString());
_testiniform = Reader["TestInitialForm"].ToString();
_testmidform = Reader["TestMidtermForm"].ToString();
_testfinalform = Reader["TestFinalForm"].ToString();
_coursestatus = Convert.ToInt32(Reader["CourseStatus"].ToString());
_breakduration = Convert.ToInt32(Reader["BreakDuration"].ToString());
_dtbl.Rows.Add(new object[]
{
_courseid,
Reader["BrowseName"].ToString(),
_name,
_namephonetic,
_nameromaji,
_nickname,
_programid,
_program,
Reader["Client"].ToString(),
Reader["Department"].ToString(),
_description,
_coursetype,
_specialremarks,
_curriculam,
_numberstudents,
_homeworkminutes,
_eventid,
_testinieventid,
_testmideventid,
_testfinaleventid,
Reader["TestInitialForm"].ToString(),
Reader["TestMidtermForm"].ToString(),
Reader["TestFinalForm"].ToString(),
strstatus,
_breakduration
});
}
Reader.Close();
int intEID=0;
string startdate="", enddate="";
string instructorName = "";
foreach(DataRow dr in _dtbl.Rows)
{
intEID = Convert.ToInt32(dr["EventID"].ToString());
//getEventText(intEID, ref startdate, ref enddate);
startdate = getEventText(intEID, true,true,ref instructorName);
enddate = getEventText(intEID, false);
// gad - Enable these comments 1-3 in order to allow a cloned Class that has no start or end date
// to be opened instead of generating an exception
//1 if (startdate.ToString() != "None")
//2 {
dr["EventStartDateTime"] = startdate;
dr["EventEndDateTime"] = enddate;
dr["OccurrenceCount"] = getOccurrenceCount(intEID);
dr["ScheduledInstructor"] = instructorName;
dr.AcceptChanges();
//3 }
}
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(!Reader.IsClosed)
{
Reader.Close();
}
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool DeleteData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Delete From [Department] " +
"WHERE DepartmentID=@DepartmentID ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@DepartmentID", SqlDbType.Int));
com.Parameters["@DepartmentID"].Value = _deptid;
com.ExecuteNonQuery();
strSql = "Delete from contact where RefID=@DepartmentID AND ContactType=5";
com.Parameters["@DepartmentID"].Value = _deptid;
com.CommandText=strSql;
com.ExecuteNonQuery();
com.CommandText = "Delete from [Contact] Where ContactID=@ContactID";
com.Parameters.Clear();
com.Parameters.Add(new SqlParameter("@ContactID", SqlDbType.Int));
com.Parameters["@ContactID"].Value = _contactid;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
//Loads Other Events (Events with 'EventType' fields set to anything but ZERO.
/* Event Type Field Values:
* 'Class' = Repeating or Single Occurence
* 'Test Initial', 'Test Mid', 'Test Final', 'Extra Class'
*/
public DataTable LoadOtherEvents(bool IsRecurring)
{
string strSql = "";
SqlDataReader Reader = null;
SqlCommand com = null;
Connection con = null;
try
{
//Here we need to make sure that we use different commands for selecting Tests and Extra Classes
//because extra classes only exist for repeating class events and NOT single occuring class events!
//Loading Positive Exceptions first
_dtbl = new DataTable();
/*
strSql = "Select Count(*) from Event WHERE RecurrenceText IS NOT NULL AND EventId=" + _eventid + ";";
con = new Connection();
con.Connect();
com = new SqlCommand(strSql, con.SQLCon);
int result = (int)com.ExecuteScalar();
*/
con = new Connection();
con.Connect();
//Means the class event exists and class reocurrs alright
if (_eventid > 0 && IsRecurring)
{
strSql = "Select * from [CalendarEvent] WHERE EventId=" + _eventid + " AND EventType='Extra Class';";
com = new SqlCommand(strSql, con.SQLCon);
Reader = com.ExecuteReader();
if (Reader.HasRows)
_dtbl.Load(Reader);
Reader.Close();
}
//Now, Loading the Test Events (if any)
strSql = "Select TestInitialEventId,TestMidtermEventId,TestFinalEventId from [Course] WHERE CourseId=@CourseId";
com = new SqlCommand(strSql, con.SQLCon);
com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int));
com.Parameters["@CourseId"].Value = _courseid;
Reader = com.ExecuteReader();
string[] temp = new string[3];
Reader.Read();
temp[0] = Reader[0].ToString();
temp[1] = Reader[1].ToString();
temp[2] = Reader[2].ToString();
Reader.Close();
string temp2 = "";
if (temp[0] != null && temp[0] != "")
temp2 = temp[0];
if (temp[1] != null && temp[1] != "")
temp2 += "," + temp[1];
if (temp[2] != null && temp[2] != "")
temp2 += "," + temp[2];
strSql = "SELECT * from [CalendarEvent] WHERE EventType LIKE 'Test%' AND EventId IN (" + temp2 + ");";
//com = new SqlCommand(strSql, con.SQLCon);
com.CommandText = strSql;
Reader = com.ExecuteReader();
if (Reader.HasRows)
{
if (_dtbl.Rows.Count==0) _dtbl.Load(Reader);
else
{
DataTable _temp = new DataTable();
_temp.Load(Reader);
_dtbl.Merge(_temp);
}
}
Reader.Close();
return _dtbl;
}
catch (SqlException ex)
{
Message = ex.Message;
return null;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool InsertData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Insert Into [Department] " +
"(" +
"ContactID, " +
"ClientID, " +
"DepartmentStatus, " +
"CreatedByUserId, " +
"DateCreated, " +
"DateLastModified, " +
"LastModifiedByUserID) " +
"Values( " +
"@contactid, " +
"@clientid, " +
"@DepartmentStatus, " +
"@CreatedByUserId, " +
"@DateCreated, " +
"@DateLastModified, " +
"@LastModifiedByUserID " +
") SELECT @@IDENTITY";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ContactId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DepartmentStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters["@ContactId"].Value = _contactid;
com.Parameters["@ClientId"].Value = _clientid;
com.Parameters["@DepartmentStatus"].Value = _statusid;
com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@DateCreated"].Value = DateTime.Now;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
SqlDataReader Reader = com.ExecuteReader();
if(Reader.Read())
{
_deptid = Convert.ToInt32(Reader[0].ToString());
}
Reader.Close();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public static int CloneData(int CourseId)
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try
{
strSql = "usp_CourseClone";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.CommandType = CommandType.StoredProcedure;
com.Connection = con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("CourseId", SqlDbType.Int));
com.Parameters["CourseId"].Value = CourseId;
com.Parameters.Add(new SqlParameter("creatorID", SqlDbType.Int));
com.Parameters["creatorID"].Value = Common.LogonID;
com.Parameters.Add(new SqlParameter("insertedID", SqlDbType.Int));
com.Parameters["insertedID"].Direction = ParameterDirection.Output;
com.ExecuteNonQuery();
return (int)com.Parameters["insertedID"].Value;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool LoadData()
{
string strSql="";
SqlDataReader Reader=null;
SqlCommand com=null;
Connection con=null;
try
{
if(_deptid<=0)
{
strSql = "select D.*, ";
strSql += "C.NickName, C.ContactID As ContactID, C.LastName + ', ' + C.FirstName As Contact, ";
strSql += "C.TitleForName, CASE ";
strSql += "WHEN C.NickName IS NULL THEN C.CompanyName ";
strSql += "WHEN C.NickName = '' THEN C.CompanyName ";
strSql += "ELSE C.NickName ";
strSql += "END AS CompanyName, ";
strSql += "C1.ContactID As ClientID, CASE ";
strSql += "WHEN C1.NickName IS NULL THEN C1.CompanyName ";
strSql += "WHEN C1.NickName = '' THEN C1.CompanyName ";
strSql += "ELSE C1.NickName ";
strSql += "END AS Client ";
strSql += "From Department D ";
strSql += "Left Join Contact C on(D.ContactID=C.ContactID) ";
strSql += "Left Join Contact C1 on(D.ClientID=C1.ContactID) ";
strSql += "Order By D.DepartmentID ";
}
else
{
strSql = "select D.*, ";
strSql += "C.NickName, C.ContactID As ContactID, C.LastName + ', ' + C.FirstName As Contact, ";
strSql += "C.TitleForName, CASE ";
strSql += "WHEN C.NickName IS NULL THEN C.CompanyName ";
strSql += "WHEN C.NickName = '' THEN C.CompanyName ";
strSql += "ELSE C.NickName ";
strSql += "END AS CompanyName, ";
strSql += "C1.ContactID As ClientID, CASE ";
strSql += "WHEN C1.NickName IS NULL THEN C1.CompanyName ";
strSql += "WHEN C1.NickName = '' THEN C1.CompanyName ";
strSql += "ELSE C1.NickName ";
strSql += "END AS Client ";
strSql += "From Department D ";
strSql += "Left Join Contact C on(D.ContactID=C.ContactID) ";
strSql += "Left Join Contact C1 on(D.ClientID=C1.ContactID) ";
strSql += "Where D.DepartmentID=" + _deptid.ToString() + " ";
}
BuildDataTable();
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
Reader = com.ExecuteReader();
string strstatus;
string strcompname;
while(Reader.Read())
{
strstatus="";
strcompname="";
//company
if(Reader["NickName"].ToString()!="")
{
strcompname = Reader["NickName"].ToString() + " ";
}
else
{
if(Reader["TitleForName"].ToString()!="")
strcompname = Reader["TitleForName"].ToString() + " ";
if(Reader["CompanyName"].ToString()!="")
strcompname += Reader["CompanyName"].ToString();
}
strcompname=strcompname.Trim();
//status
if(Convert.ToInt16(Reader["DepartmentStatus"].ToString())==0)
{
strstatus = "Active";
}
else
{
strstatus = "Inactive";
}
_deptid = Convert.ToInt32(Reader["DepartmentID"].ToString());
_contactid = Convert.ToInt32(Reader["ContactID"].ToString());
_contactname = Reader["Contact"].ToString();
_clientid = Convert.ToInt32(Reader["ClientID"].ToString());
_clientname = Reader["Client"].ToString();
_statusid = Convert.ToInt16(Reader["DepartmentStatus"].ToString());
_dtbl.Rows.Add(new object[]
{
_deptid,
strcompname,
_contactid,
_contactname,
_clientid,
_clientname,
"",
"",
strstatus
});
}
Reader.Close();
//Get the contacts
int deptid=0;
string contact1="",contact1Phone = "";
string contact2="",contact2Phone = "";
foreach(DataRow dr in _dtbl.Rows)
{
deptid=0;
contact1="";
contact2="";
contact1Phone = "";
contact2Phone = "";
deptid=Convert.ToInt32(dr["DepartmentID"].ToString());
GetContact(deptid, ref contact1, ref contact2,ref contact1Phone,ref contact2Phone);
dr["Contact1"] = contact1;
dr["Contact2"] = contact2;
dr["Contact1Phone"] = contact1Phone;
dr["Contact2Phone"] = contact2Phone;
dr.AcceptChanges();
}
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(!Reader.IsClosed)
{
Reader.Close();
}
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool DeleteData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.Parameters.Add(new SqlParameter("@CourseId", SqlDbType.Int));
com.Parameters["@CourseId"].Value = _courseid;
//Test Initial Event ID
strSql = "Delete From [Event] " +
"WHERE EventID IN (Select TestInitialEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
strSql = "Delete From [CalendarEvent] " +
"WHERE EventID IN (Select TestInitialEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
//Test MidTerm Event ID
strSql = "Delete From [Event] " +
"WHERE EventID IN (Select TestMidTermEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
strSql = "Delete From [CalendarEvent] " +
"WHERE EventID IN (Select TestMidTermEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
//Test Final Event ID
strSql = "Delete From [Event] " +
"WHERE EventID IN (Select TestFinalEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
strSql = "Delete From [CalendarEvent] " +
"WHERE EventID IN (Select TestFinalEventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
//Event ID
strSql = "Delete From [Event] " +
"WHERE EventID IN (Select EventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
strSql = "Delete From [CalendarEvent] " +
"WHERE EventID IN (Select EventID From [Course] " +
"WHERE CourseId=@CourseId) ";
com.CommandText=strSql;
com.ExecuteNonQuery();
strSql = "Delete From [Course] " +
"WHERE CourseId=@CourseId ";
com.CommandText=strSql;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool UpdateData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Update [Department] Set " +
"ContactId=@ContactId, " +
"ClientId=@ClientId, " +
"DepartmentStatus=@DepartmentStatus, " +
"DateLastModified=@DateLastModified, " +
"LastModifiedByUserID=@LastModifiedByUserID " +
"WHERE DepartmentId=@DepartmentId ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@DepartmentId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ContactId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@ClientId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DepartmentStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters["@DepartmentId"].Value = _deptid;
com.Parameters["@ContactId"].Value = _contactid;
com.Parameters["@ClientId"].Value = _clientid;
com.Parameters["@DepartmentStatus"].Value = _statusid;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.ExecuteNonQuery();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public string getEventText(int eventid, bool Start,bool getInstructor,ref string instructorName)
{
string startdate = "", enddate = "";
string Result = "";
string strSql = "";
SqlCommand com = null;
Connection con = null;
SqlDataReader Reader = null;
DateTime dtStart = Convert.ToDateTime(null);
DateTime dtEnd = Convert.ToDateTime(null);
if (Start)
dtStart = Convert.ToDateTime(null);
else
dtEnd = Convert.ToDateTime(null);
try
{
if (Start)
{
strSql = "Select Top 1 StartDateTime,ScheduledTeacherID From [CalendarEvent] ";
strSql += "WHERE EventID=@EventID Order By CalendarEventID";
}
else
{
strSql = "Select Top 1 EndDateTime From [CalendarEvent] ";
strSql += "WHERE EventID=@EventID Order by CalendarEventID DESC";
}
con = new Connection();
con.Connect();
com = new SqlCommand();
com.Connection = con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.BigInt));
com.Parameters["@EventID"].Value = eventid;
Reader = com.ExecuteReader();
string id = "";
bool IsRecord = false;
if (Reader.Read())
{
IsRecord = true;
if (Start)
{
if (Reader["StartDateTime"] != System.DBNull.Value)
{
dtStart = Convert.ToDateTime(Reader["StartDateTime"].ToString());
}
}
else
{
if (Reader["EndDateTime"] != System.DBNull.Value)
{
dtEnd = Convert.ToDateTime(Reader["EndDateTime"].ToString());
}
}
if(getInstructor)
{
if (Reader["ScheduledTeacherID"] != System.DBNull.Value)
{
id = Convert.ToString(Reader["ScheduledTeacherID"]);
//instructorName = getInstructorName(id);
}
else
{
instructorName = "None";
}
}
if (id == "0")
instructorName = "None";
}
Reader.Close();
if (IsRecord)
{
if (Start)
{
if (dtStart != Convert.ToDateTime(null))
{
Result = dtStart.ToShortDateString() + " " + dtStart.ToShortTimeString();
if (Result.IndexOf("(") > 0)
{
Result = Result.Substring(0, Result.IndexOf("(") + 1);
}
startdate = Result;
}
}
else
{
if (dtEnd != Convert.ToDateTime(null))
{
Result = dtEnd.ToShortDateString() + " " + dtEnd.ToShortTimeString();
if (Result.IndexOf("(") > 0)
{
Result = Result.Substring(0, Result.IndexOf("(") + 1);
}
enddate = Result;
}
}
if (id != "None" && id != "0")
{
instructorName = getInstructorName(id,con);
}
else
instructorName = "None";
}
else
{ Result = "None"; instructorName = "None"; }
return Result;
}
catch (SqlException ex)
{
Message = ex.Message;
return "";
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
private bool GetContact(int mdeptid, ref string contact1, ref string contact2,ref string contactPhone1,ref string contactPhone2)
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try
{
strSql = "Select Top 2 LastName + ', ' + FirstName As ContactName,Phone1 From Contact Where ContactType=5 AND RefID=" + mdeptid.ToString() + " Order by ContactID";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.Connection = con.SQLCon;
com.CommandText = strSql;
SqlDataReader Reader = com.ExecuteReader();
while (Reader.Read())
{
if (contact1 == "")
{
contact1 = Reader["ContactName"].ToString();
contactPhone1 = Reader["Phone1"].ToString();
}
else
{
contact2 = Reader["ContactName"].ToString();
contactPhone2 = Reader["Phone1"].ToString();
}
}
Reader.Close();
return true;
}
catch (SqlException ex)
{
Message = ex.Message;
return false;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public string getInstructorName(string id,Connection con)
{
string Result = "";
string strSql = "";
//SqlCommand com = null;
//Connection con = null;
SqlDataReader Reader = null;
SqlCommand com1 = null;
//con = new Connection();
//con.Connect();
strSql = "Select LastName,FirstName from Contact Where ContactID = " + id;
com1 = new SqlCommand();
com1.Connection = con.SQLCon;
com1.CommandText = strSql;
try
{
Reader = com1.ExecuteReader();
if (Reader.Read())
{
Result = Reader["LastName"].ToString() + ", " + Reader["FirstName"].ToString();
}
Reader.Close();
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
if (com1 != null)
{
com1.Dispose();
//com = null;
//con.DisConnect();
}
}
return Result;
}
public static int[] CloneData(int departmentID)
{
string strSql = "";
SqlCommand com = null;
Connection con = null;
try
{
strSql = "usp_DepartmentClone";
con = new Connection();
con.Connect();
com = new SqlCommand();
com.CommandType = CommandType.StoredProcedure;
com.Connection = con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("DepartmentID", SqlDbType.Int));
com.Parameters["DepartmentID"].Value = departmentID;
com.Parameters.Add(new SqlParameter("creatorID", SqlDbType.Int));
com.Parameters["creatorID"].Value = Common.LogonID;
com.Parameters.Add(new SqlParameter("insertedID", SqlDbType.Int));
com.Parameters["insertedID"].Direction = ParameterDirection.Output;
com.Parameters.Add(new SqlParameter("out_newcontactID", SqlDbType.Int));
com.Parameters["out_newcontactID"].Direction = ParameterDirection.Output;
com.ExecuteNonQuery();
int[] array = new int[2];
array[0]=(int)com.Parameters["insertedID"].Value;
array[1] = (int)com.Parameters["out_newcontactID"].Value;
return array;
}
finally
{
if (com != null)
{
com.Dispose();
com = null;
con.DisConnect();
}
}
}
public bool InsertData()
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Insert Into [Course] " +
"(" +
"Name, " +
"NamePhonetic, " +
"NameRomaji, " +
"NickName, " +
"ProgramID, " +
"EventID, " +
"Description, " +
"SpecialRemarks, " +
"CourseType, " +
"Curriculam, " +
"NumberStudents, " +
"HomeWorkMinutes, " +
"TestInitialEventID, " +
"TestMidtermEventID, " +
"TestFinalEventID, " +
"TestInitialForm, " +
"TestMidtermForm, " +
"TestFinalForm, " +
"CourseStatus, " +
"CreatedByUserId, " +
"DateCreated, " +
"DateLastModified, " +
"LastModifiedByUserID, " +
"BreakDuration) " +
"Values( " +
"@Name, " +
"@NamePhonetic, " +
"@NameRomaji, " +
"@NickName, " +
"@ProgramID, " +
"@EventID, " +
"@Description, " +
"@SpecialRemarks, " +
"@CourseType, " +
"@Curriculam, " +
"@NumberStudents, " +
"@HomeWorkMinutes, " +
"@TestInitialEventID, " +
"@TestMidtermEventID, " +
"@TestFinalEventID, " +
"@TestInitialForm, " +
"@TestMidtermForm, " +
"@TestFinalForm, " +
"@CourseStatus, " +
"@CreatedByUserId, " +
"@DateCreated, " +
"@DateLastModified, " +
"@LastModifiedByUserID, " +
"@BreakDuration " +
")";
strSql += "SELECT @@IDENTITY";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
com.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NamePhonetic", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NameRomaji", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NickName", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@ProgramID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@EventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@SpecialRemarks", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CourseType", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@Curriculam", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@NumberStudents", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@HomeWorkMinutes", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestInitialEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestMidtermEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestFinalEventID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@TestInitialForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TestMidtermForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@TestFinalForm", SqlDbType.NVarChar));
com.Parameters.Add(new SqlParameter("@CourseStatus", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@CreatedByUserId", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@DateCreated", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime));
com.Parameters.Add(new SqlParameter("@LastModifiedByUserID", SqlDbType.Int));
com.Parameters.Add(new SqlParameter("@BreakDuration", SqlDbType.Int));
com.Parameters["@Name"].Value = _name;
com.Parameters["@NamePhonetic"].Value = _namephonetic;
com.Parameters["@NameRomaji"].Value = _nameromaji;
com.Parameters["@NickName"].Value = _nickname;
com.Parameters["@ProgramID"].Value = _programid;
com.Parameters["@EventID"].Value = _eventid;
com.Parameters["@Description"].Value = _description;
com.Parameters["@SpecialRemarks"].Value = _specialremarks;
com.Parameters["@CourseType"].Value = _coursetype;
com.Parameters["@Curriculam"].Value = _curriculam;
com.Parameters["@NumberStudents"].Value = _numberstudents;
com.Parameters["@HomeWorkMinutes"].Value = _homeworkminutes;
com.Parameters["@TestInitialEventID"].Value = _testinieventid;
com.Parameters["@TestMidtermEventID"].Value = _testmideventid;
com.Parameters["@TestFinalEventID"].Value = _testfinaleventid;
com.Parameters["@TestInitialForm"].Value = _testiniform;
com.Parameters["@TestMidtermForm"].Value = _testmidform;
com.Parameters["@TestFinalForm"].Value = _testfinalform;
com.Parameters["@CourseStatus"].Value = _coursestatus;
com.Parameters["@CreatedByUserId"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@DateCreated"].Value = DateTime.Now;
com.Parameters["@DateLastModified"].Value = DateTime.Now;
com.Parameters["@LastModifiedByUserID"].Value = Scheduler.BusinessLayer.Common.LogonID;
com.Parameters["@BreakDuration"].Value = _breakduration;
SqlDataReader Reader = com.ExecuteReader();
if(Reader.Read())
{
CourseId = Convert.ToInt32(Reader[0].ToString());
}
Reader.Close();
return true;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
public bool IsEventExists(int evtid)
{
string strSql="";
SqlCommand com=null;
Connection con=null;
try
{
strSql = "Select Count(EventID) From [Event] " +
"WHERE EventID=" + evtid.ToString() + " ";
con=new Connection();
con.Connect();
com = new SqlCommand();
com.Connection=con.SQLCon;
com.CommandText = strSql;
object o = com.ExecuteScalar();
if(Convert.ToInt32(o)>0)
{
return true;
}
return false;
}
catch(SqlException ex)
{
Message=ex.Message;
return false;
}
finally
{
if(com!=null)
{
com.Dispose();
com=null;
con.DisConnect();
}
}
}
