public override int Delete(long ID)
{
SqlParameterCollection prms = new SqlCommand().Parameters;
prms.AddWithValue("@ID", ID);
prms.AddWithValue("@controlID", mArchiveControl.ID);
return squtil.ExecuteNoResultSetSQLQuery(deleteSql, prms);
}
static public void AddUserAndRoles(string newStaffUserName)
{
var environmentParametersViewModel = new Thinkgate.Domain.Classes.EnvironmentParametersFactory(AppSettings.ConnectionStringName).GetEnvironmentParameters();
var clientId = environmentParametersViewModel.ClientId;
var kenticoEnabled = environmentParametersViewModel.IsKenticoEnabledSite;
if (kenticoEnabled)
{
SqlParameterCollection parmsUersInRoles = new SqlCommand().Parameters;
parmsUersInRoles.AddWithValue("ApplicationName", AppSettings.ApplicationName);
parmsUersInRoles.AddWithValue("UserName", newStaffUserName);
var findRolesForUser = ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString,
Thinkgate.Base.Classes.Data.StoredProcedures.ASPNET_USERS_IN_ROLES_GET_ROLES_FOR_USER,
System.Data.CommandType.StoredProcedure,
parmsUersInRoles);
var roleservice = new RoleServiceProxy();
var nameCalculator = new Thinkgate.Domain.Classes.KenticoNameCalculator();
for (int i = 0; i < findRolesForUser.Rows.Count; i++)
{
string newStaffRoleName = findRolesForUser.Rows[i]["RoleName"].ToString();
var kenticoUserName = nameCalculator.CalculateUserName(clientId, newStaffUserName);
var kenticoRoleName = nameCalculator.CalculateSystemGroupName(clientId, AdminUserName, newStaffRoleName);
var roleuser = new RoleUser();
roleuser.UserName = kenticoUserName;
roleservice.AddUsersToRole(kenticoRoleName, EnumerableExtensions.SingleItemAsEnumerable<RoleUser>(roleuser));
}
}
}
public override byte[] Read(long ID)
{
SqlParameterCollection prms = new SqlCommand().Parameters;
prms.AddWithValue("@ID", ID);
prms.AddWithValue("@controlID", mArchiveControl.ID);
return (byte[])squtil.ExecuteScalarResultSetSQLQuery(selectSql, prms);
}
public int InsertData2()
{
var arrayOfParameter = new SqlCommand().Parameters;
arrayOfParameter.AddWithValue("@rollno", _rollNo);
arrayOfParameter.AddWithValue("@name", _name);
arrayOfParameter.AddWithValue("@department", _department);
arrayOfParameter.AddWithValue("@section", _section);
return this.ModifyDataStoredProcedure(@"USP_StudentDetailsProc4", arrayOfParameter);
}
public int WithReturn3()
{
var arrayOfParameter = new SqlCommand().Parameters;
arrayOfParameter.AddWithValue("@rico", _rico);
return this.ModifyDataStoredProcedureWithReturn(@"USP_StudentDetailsProc6", arrayOfParameter);
}
public DataTable getLoginTeams()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@workdayid", _workdayid);
return dt = _dbi.FGetDataTable(@"EmployeeGetTeams", oParam);
}
public DataTable GetAllDetailsByName()
{
var arrayOfParameter = new SqlCommand().Parameters;
arrayOfParameter.AddWithValue("@name", _name);
return this.GetDataStoredProcedure(@"USP_StudentDetailsProc2", arrayOfParameter );
}
public DataSet getLoginDetails(string lanid)
{
DataSet ds = new DataSet();
lanid = lanid.Remove(0, lanid.IndexOf('\\') + 1);
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@lanid", lanid);
return ds = _dbi.FGetDataSet(@"EmployeeDetailsDisplay", oParam);
}
public override Dictionary<string, object> ReadHash(long ID)
{
//cant use GT cause GT calls archive to read data and that would create a recursive loop
SqlParameterCollection prms = new SqlCommand().Parameters;
prms.AddWithValue("@id", ID);
DataTable dt = squtil.ExecuteSingleResultSetSQLQuery(String.Format("select * from {0} where {1} = @id", dbTable, mArchiveControl.cfg.IDColumn), prms);
if (dt.Rows.Count == 0)
return null;
GenericTable gt = new GenericTable(squtil, dbTable, mArchiveControl.cfg.IDColumn);
gt.Load(dt.Rows[0]);
//this was not archived - it was just put there so we have the ability to archive that data
if (this.mArchiveControl.ArchiveType == ArchiveControl.ArchiveTypeEnum.Column)
gt.Data.Remove(mArchiveControl.cfg.AgeDeterminingColumn);
return gt.Data;
}
public DataTable displayKhronosLogs(int x)
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
//oParam.AddWithValue("@teamid", _teamid);
oParam.AddWithValue("@workdayid", _employeeid);
//oParam.AddWithValue("@month", _month);
oParam.AddWithValue("@monthyear", _monthYear);
//oParam.AddWithValue("@year", _year);
if (x == 1)
{
//return dt = this.FGetDataTable(@"KhronosTeamLogsBMDisplay", oParam);
return dt = this.FGetDataTable(@"GetEmployeeLogs", oParam);
}
else
{
return dt = this.FGetDataTable(@"KhronosEmployeeTardiness", oParam);
//return dt = this.FGetDataTable(@"KhronosTeamLogsDLDisplay", oParam);
}
}
public string cancelShift()
{
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@shiftempid", _shiftempid);
this.FExecuteQuery("EmployeeShiftCancel", oParam);
return "done";
}
public string cancelLeaves()
{
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@leaveid", _leaveid);
oParam.AddWithValue("@workdayid", _employeeid);
this.FExecuteQuery(@"KhronosEmployeeLeaveCancel", oParam);
return "done";
}
public string InsertForecast()
{
string msg;
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamid", _teamid);
oParam.AddWithValue("@position", _position);
oParam.AddWithValue("@year", _year);
oParam.AddWithValue("@month", _month);
oParam.AddWithValue("@teamname", _teamname);
//oParam.AddWithValue("@count", _count);
try
{
this.ExecuteInsert(@"HCInsertForecast", oParam);
msg = "Saved!";
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string insertLeaves()
{
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamempid", _teamempid);
oParam.AddWithValue("@leavetype", _leavetype);
oParam.AddWithValue("@leavedate", _leavedate);
oParam.AddWithValue("@leavevolume", _leavevolume);
oParam.AddWithValue("@leavereason", _leavereason);
oParam.AddWithValue("@workdayid", _employeeid);
this.ExecuteInsert(@"KhronosEmployeeLeavesInsert", oParam);
return "done";
}
public DataTable getEmployeeShift()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamempid", _teamempid);
oParam.AddWithValue("@shiftstartdate", _shiftstartdate);
oParam.AddWithValue("@shiftenddate", _shiftenddate);
return dt = this.FGetDataTable(@"EmployeeShiftGet", oParam);
}
public DataTable getEmployeeAIQualify()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@wdid", _employeeid);
oParam.AddWithValue("@monthyear1", _monthyear);
oParam.AddWithValue("@teamid", _teamid);
return dt = this.FGetDataTable(@"KhronosTeamAIQualification", oParam);
}
public DataTable displayTemp()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamid", _teamid);
return dt = this.FGetDataTable("TmpQryChippy", oParam);
}
public DataTable displayEmployeeShift()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamempid", _teamempid);
return dt = this.FGetDataTable("EmployeeShiftGetShift", oParam);
}
public DataSet displayTeamStaff()
{
DataSet ds = new DataSet();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamid", _teamid);
return ds = this.FGetDataSet("TeamStaffDisplay", oParam);
}
public string UpdateQuizbeeStatusI()
{
string msg;
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@questionid", _questionid);
try
{
this.ExecuteInsert(@"TBUpdateQuizbeeStatusI", oParam);
msg = "Saved!";
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public DataTable getAIExport()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@monthyear1", _monthyear);
oParam.AddWithValue("@teamid", _teamid);
return dt = this.FGetDataTable(@"ExportAIQualify", oParam);
}
public string UpdateUserSBStatus()
{
string msg;
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@eid", _eid);
try
{
this.ExecuteInsert(@"SBCheckUserSBStatus", oParam);
msg = "Saved!";
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public DataTable getEmployeeLeaves()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamid", _teamid);
oParam.AddWithValue("@leavedate", _leavedate);
return dt = this.FGetDataTable(@"KhronosEmployeeLeavesDisplay", oParam);
}
/// <summary>
/// Get Standard Levels By StandardList Data
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataTable GetStandardLevelbyStandardList(Thinkgate.Base.DataAccess.dtGeneric_Int ids,string StandardSet, int WorksheetId)
{
SqlParameterCollection parms = new SqlCommand().Parameters;
SqlParameter idCollections = new SqlParameter("StandardIds", SqlDbType.Structured);
idCollections.TypeName = "dbo.Generic_Int";
idCollections.Value = ids;
parms.Add(idCollections);
parms.AddWithValue("StandardSet", StandardSet);
parms.AddWithValue("WorksheetId", WorksheetId);
DataTable dt = ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString, StoredProcedures.E3_GET_STANDARDLEVEL_BY_STANDARDLIST, CommandType.StoredProcedure, parms);
return dt;
}
public DataTable getEmployeeTardiness()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@workdayid", _employeeid);
oParam.AddWithValue("@monthyear", _monthyear);
return dt = this.FGetDataTable(@"KhronosEmployeeTardiness", oParam);
}
public static E3InterventionDataObject getE3InterventionData(int userpage, bool usercrosscourses, int schoolid, string grade, string subject, int studentid, string type, string tier = "")
{
SqlParameterCollection sqlParms = new SqlCommand().Parameters;
sqlParms.AddWithValue("@UserPage", userpage);
sqlParms.AddWithValue("@UserCrossCourses", usercrosscourses);
sqlParms.AddWithValue("@SchoolID", schoolid);
sqlParms.AddWithValue("@Grade", grade);
sqlParms.AddWithValue("@Subject", subject);
sqlParms.AddWithValue("@StudentID", studentid);
sqlParms.AddWithValue("@Type", type);
sqlParms.AddWithValue("@Tier", tier);
DataSet ds = Thinkgate.Base.DataAccess.ThinkgateDataAccess.FetchDataSet(AppSettings.ConnectionString,
Thinkgate.Base.Classes.Data.StoredProcedures.E3_INTERVENTIONS_SELECT,
CommandType.StoredProcedure,
sqlParms);
//Build MTSS Objects
E3InterventionDataObject obj = new E3InterventionDataObject();
obj.InterventionsObject = (List<E3MTSSInterventions>)ds.Tables[0].ToList<E3MTSSInterventions>();
obj.StudentsObject = (List<E3MTSSInterventionStudents>)ds.Tables[1].ToList<E3MTSSInterventionStudents>();
return obj;
}
public string insertNewShift()
{
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamempid", _teamempid);
oParam.AddWithValue("@shiftid", _shiftid);
oParam.AddWithValue("@dayshiftid", _dayshiftid);
oParam.AddWithValue("@shiftstartdate", _shiftstartdate);
oParam.AddWithValue("@shiftenddate", _shiftenddate);
oParam.AddWithValue("@employeeid", _employeeid);
oParam.AddWithValue("@customstart", _customstart);
oParam.AddWithValue("@customend", _customend);
this.ExecuteInsert(@"EmployeeShiftInsert3", oParam);
return "done";
}
public static DataTable getE3InterventionStudents(int userpage, bool usercrosscourses, int schoolid, string grade, string subject, string type, string tier = "")
{
SqlParameterCollection sqlParms = new SqlCommand().Parameters;
sqlParms.AddWithValue("@UserPage", userpage);
sqlParms.AddWithValue("@UserCrossCourses", usercrosscourses);
sqlParms.AddWithValue("@SchoolID", schoolid);
sqlParms.AddWithValue("@Grade", grade);
sqlParms.AddWithValue("@Subject", subject);
sqlParms.AddWithValue("@Type", type);
sqlParms.AddWithValue("@Tier", tier);
DataTable dt = Thinkgate.Base.DataAccess.ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString,
Thinkgate.Base.Classes.Data.StoredProcedures.E3_STUDENTS_SELECT_IN_RTI,
CommandType.StoredProcedure,
sqlParms);
return dt;
}
public DataTable DisplayHeadcount()
{
DataTable dt = new DataTable();
var oParam = new SqlCommand().Parameters;
oParam.AddWithValue("@teamid", _teamid);
oParam.AddWithValue("@year", _year);
return dt = this.FGetDataTable(@"HCDisplayHeadcount", oParam);
}
public static List<E3Student> getE3StudentNames(List<KenticoMTSSFormData> studentList)
{
//Convert to DataTable for SP
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("StudentID");
dt.Columns.Add(dc);
foreach (var stid in studentList)
{
dt.Rows.Add(stid.StudentID.ToString());
}
//Call SP with DataTable list
SqlParameterCollection sqlParms = new SqlCommand().Parameters;
sqlParms.AddWithValue("@StudentIds", dt);
DataTable rs = Thinkgate.Base.DataAccess.ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString,
Thinkgate.Base.Classes.Data.StoredProcedures.E3_STUDENTS_BY_STUDENT_ID,
CommandType.StoredProcedure,
sqlParms);
//Convert to List using extension method (columns must match property names)
List<E3Student> ls = (List<E3Student>)rs.ToList<E3Student>();
return ls;
}