public override void Delete() { DeleteFromRoot(); DatabaseConnection connection = new DatabaseConnection( @"delete CCEx_Group where intID = @groupid;"); connection.AddParameter("@groupid", ID).Execute(); }
public override void Update() { DatabaseConnection connection = new DatabaseConnection( @"update CCEx_ProspectContact set strContactNumber = @contact where intID = @id and strContactNumber like @oldNumber;"); connection.AddParameter("@id", Parent.ID).AddParameter("@contact", ContactNumber).AddParameter("@oldNumber", OldNumber) .Execute(); }
public override void Delete() { DatabaseConnection connection = new DatabaseConnection( @"delete from CCEx_ProspectContact where strContactNumber = @contact and intID = @id;"); connection.AddParameter("@id", Parent.ID).AddParameter("@contact", ContactNumber) .Execute(); }
public override void Insert() { DatabaseConnection connection = new DatabaseConnection( @"insert into CCEx_GroupMember (intGroupID, intStaffID, intStudentID, dtmListed) values (@groupid, @staffid, @studentid, @date);"); connection.AddParameter("@groupid", ID).AddParameter("@staffid", StaffID).AddParameter("@studentid", MemberID).AddParameter("@date", DateListed) .Execute(); }
public static Prospect ImportByMXV3Guardian(int id) { DatabaseConnection connection = new DatabaseConnection( @" declare @studentID as int; declare @prospectID as int; declare @currentTable as table(id int); insert into CCEx_Prospect (strAddress, strFullName) output inserted.intID into @currentTable select strMailingAddressGuardian1, strNameGuardian from Guardian where intIdGuardian = @guardianID; select @prospectID = id from @currentTable; -- We Get the Student Info -- declare studentscur cursor for select intIDStudent from Student where intidGuardian = @guardianID declare @studentList as table (id int); fetch next from studentscur into @studentID while (@@FETCH_STATUS = 1) begin declare @prospectStudentID as int; insert into CCEx_Prospect (strEmail, strFullName) output inserted.intID into @currentTable select strEmailUser, strFullNameUser from AspenUser where intIdUser = @studentID select @prospectStudentID = id from @currentTable; insert into CCEx_ProspectStudent (intID) values (@prospectStudentID) insert into @studentList (id) values (@prospectStudentID) fetch next from studentscur into @studentID end close studentscur deallocate studentscur -- We Link the Two Together -- declare prostudentcur cursor for select id from @studentList declare @currentID as int; open prostudentcur fetch next from prostudentcur into @currentID while (@@FETCH_STATUS = 0) begin insert into CCEx_ProspectRelation (intProspectID, intRelationID, intStudentID) values (@prospectID, 3, @currentID); fetch next from prostudentcur into @currentID end select @prospectID "); DataTable result = connection.AddParameter("@guardianID", id).Query; return new Prospect((int)result.Rows[0][0], null); }
public string GetStaffName() { DatabaseConnection connection = new DatabaseConnection( @"select strFullNameUser from AspenUser where intIDUser = @id"); DataTable table = connection.AddParameter("@id", StaffID).Query; return table.Rows[0][0].ToString(); }
public string GetAvailabilityCompiledRemarks() { DatabaseConnection connection = new DatabaseConnection( @"select strday from CCEx_Day where intID = @id"); DataTable result = connection.AddParameter("@id", DayID).Query; return result.Rows[0][0].ToString() + " (" + Remark + ") "; }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection( @"select * from CCex_ProspectCaseAvailability where intID = @id;"); DataTable table = connection.AddParameter("@id", ID).Query; DayID = (int)table.Rows[0]["intDayID"]; Remark = table.Rows[0]["strAvailabilityRemarks"].ToString(); }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection (@"select intStaffID, dtmListed from CCEx_GroupMember where intGroupID = @groupid and intStudentID = @studentid;"); DataTable result = connection.AddParameter("@groupid", ID).AddParameter("@studentid", MemberID).Query; StaffID = (int)result.Rows[0]["intStaffID"]; DateListed = (DateTime)result.Rows[0]["dtmListed"]; }
public override void Insert() { DatabaseConnection connection = new DatabaseConnection( @"declare @availinfot as table(id int); insert into CCEx_ProspectCaseAvailability (intCaseID, intDayID, strAvailabilityRemarks) output inserted.intID into @availinfot values (@caseid, @dayid, @remarks); select id from @availinfot;"); DataTable result = connection.AddParameter("@caseid", Parent.ID).AddParameter("@dayid", DayID).AddParameter("@remarks", Remark).Query; ID = (int)result.Rows[0][0]; }
public override void Insert() { DatabaseConnection connection = new DatabaseConnection( @"declare @temp as table(id int); insert into CCEx_ProspectContact (intID, strContactNumber) output inserted.intContactID into @temp values (@id, @contact); select id from @temp"); DataTable table = connection.AddParameter("@id", Parent.ID).AddParameter("@contact", ContactNumber) .Query; ID = (int)table.Rows[0][0]; }
private void login(string username, string password) { DatabaseConnection connection = new DatabaseConnection( @"select CCEx_User.intID, strFullNameUser, strRoleName from AspenUser inner join CCEx_User on intIdUser = CCEx_User.intID inner join CCEx_UserRole on CCEx_User.intStaffRole = CCEx_UserRole.intID where strUserNameUser like @user and strPasswordUser = @password"); DataTable result = connection.AddParameter("@user", username).AddParameter( "@password", password).Query; if (result.Rows.Count == 0) throw new InvalidCredentialException(); StaffName = result.Rows[0]["strFullNameUser"].ToString(); AccessRole = result.Rows[0]["strRoleName"].ToString(); StaffID = (int)result.Rows[0]["intID"]; }
private void Search(ProspectStudentCase obj) { DatabaseConnection connection = new DatabaseConnection( @" declare @levelName as varchar(30); select @levelName = strNameLevel from [Level] where intIdLevel = @levelid; if (SUBSTRING(@levelName, 0, 3) like 'IP') select intID from CCEx_ProspectCase where intSubjectID = @subjectid and intLevelID = @levelid and intSchoolID = @schoolid and intID <> @id; else select intID from CCEx_ProspectCase where intSubjectID = @subjectid and intLevelID = @levelid and intID <> @id; "); DataTable table = connection.AddParameter("@id", obj.ID).AddParameter("@subjectid", obj.SubjectID).AddParameter("@levelid", obj.LevelID).AddParameter("@schoolid", obj.SchoolID).Query; foreach (DataRow row in table.Rows) _cases.Add(new ProspectStudentCase((int)row[0], null)); }
private void Populate(ProspectStudentCase scase) { DatabaseConnection connection = new DatabaseConnection( @" select convert(varchar(100),dtmTimeStartRegular, 108) + ' to ' + convert(varchar(100),dtmTimeEndRegular, 108) as 'Session', strDayRegular as 'Day', strNameBranch as 'Branch' from ClassSchedule inner join Regular on ClassSchedule.intIdClassSchedule = Regular.intIdClassScheduleRegular inner join Branch on intIdBranch = intIdClassroomRegular where intIdLevelClassSchedule = @levelid and intIdSubjectClassSchedule = @subjectid and intIdClassroomRegular = @branchid and strYearClassSchedule like DATEPART(year, getdate()); " ); DataTable table = connection.AddParameter("@levelid", scase.LevelID).AddParameter("@branchid", scase.BranchID).AddParameter("@subjectid", scase.SubjectID).Query; foreach (DataRow row in table.Rows) _entries.Add(new CaseSimilarRegularClassEntry(row["session"].ToString(), row["branch"].ToString(), row["day"].ToString())); }
public override void Insert() { if (Conversation.Trim() != "") { DatabaseConnection connection = new DatabaseConnection( @"declare @convinfot as table(id int); insert into CCex_ProspectCaseConversation (dtmConverse, intCaseID, intStaffID, strConversation) output inserted.intID into @convinfot values (@date, @caseid, @staffid, @conversation); select id from @convinfot;"); DataTable result = connection.AddParameter("@date", Date).AddParameter("@caseid", Parent.ID).AddParameter("@staffid", StaffID).AddParameter("@conversation", Conversation).Query; ID = (int)result.Rows[0][0]; connection = new DatabaseConnection( @"update CCEx_CallReminder set dtmCalled = GETDATE(), intStaffID = @staffid where intCaseID = @id and dtmCalled = null; if (@next >= 0) insert into CCEx_CallReminder (intCaseID, intStaffID, dtmDate) values (@id, @nextstaff, DATEADD(day, @next, getdate()));"); connection.AddParameter("@next", NextCallDay) .AddParameter("@id", Parent.ID) .AddParameter("@staffid", StaffID) .AddParameter("@nextstaff", ((ProspectStudentCase)Parent).StaffID) .Execute(); } }
public override void Update() { DatabaseConnection connection = new DatabaseConnection( @"update CCEx_ProspectCaseAvailability set intDayID = @dayid, strAvailabilityRemarks = @remarks where intID = @id;"); connection.AddParameter("@id", ID).AddParameter("@dayid", DayID).AddParameter("@remarks", Remark).Execute(); }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection (@"select * from CCEx_Prospect where intID = @id"); DataTable result = connection.AddParameter("@id", ID).Query; Name = result.Rows[0]["strFullName"].ToString(); Address = result.Rows[0]["strAddress"].ToString(); Email = result.Rows[0]["strEmail"].ToString(); // Populate Prospect Student connection = new DatabaseConnection (@"select CCEx_ProspectRelation.intStudentID from CCEx_ProspectRelation where intProspectID = @prospectid"); foreach (DataRow row in connection.AddParameter("@prospectid", ID).Query.Rows) AddStudent(new Prospect((int)row[0], this)); // Populate Prospect Contact connection = new DatabaseConnection (@"select intContactID, strContactNumber from CCEx_ProspectContact where intID = @id;"); foreach (DataRow row in connection.AddParameter("@id", ID).Query.Rows) AddContact(new ProspectContact((int)row[0] , row[1].ToString(), this)); if (this.IsInstanceStudent()) { connection = new DatabaseConnection( @"select intID from CCEx_ProspectCase where intStudentID = @studentid; "); foreach (DataRow row in connection.AddParameter("@studentid", ID).Query.Rows) { ProspectStudentCase pscase = new ProspectStudentCase( (int)row[0], this); } } }
public override void Update() { DatabaseConnection connection = new DatabaseConnection( @"update CCEx_Prospect set strAddress = @address, strEmail = @email, strFullName = @name where intID = @id;"); connection.AddParameter("@address", Address).AddParameter("@email", Email).AddParameter("@name", Name).AddParameter("@id", ID).Execute(); }
private void Populate(int month, int year) { DatabaseConnection connection = new DatabaseConnection( @" /********************************************************* Staff Performance Report *********************************************************/ declare @resulttable as table(staffid int, staffname varchar(100), called int, confirm int, cancel int) -- We get all staff that has the role of a leader or admin -- declare staffcur cursor for select intid, strFullNameuser from CCEx_User inner join AspenUser on intid = intIDuser where intStaffRole = 1 or intStaffRole = 2; open staffcur declare @currentid as int, @currentname as varchar(100); fetch next from staffcur into @currentid, @currentname while (@@FETCH_STATUS = 0) begin declare @called as int, @confirm as int, @cancel as int select @called = COUNT(*) from CCex_ProspectCaseConversation where datepart(month, dtmConverse) = @month and DATEPART(year, dtmConverse) = @year and intStaffID = @currentid; select @confirm = COUNT(*) from CCEx_ProspectCase where intStatusID = 3 and DATEPART(MONTH, dtmFinalised) = @month and DATEPART(year, dtmFinalised) = @year and intOwnerID = @currentid; select @cancel = COUNT(*) from CCEx_ProspectCase where intStatusID = 4 and DATEPART(MONTH, dtmFinalised) = @month and DATEPART(year, dtmFinalised) = @year and intOwnerID = @currentid; insert into @resulttable (staffid, staffname, called, confirm, cancel) values (@currentid, @currentname, @called, @confirm, @cancel); fetch next from staffcur into @currentid end close staffcur deallocate staffcur select staffid, staffname, called, confirm, cancel from @resulttable; "); DataTable table = connection.AddParameter("@month", month).AddParameter("@year", year).Query; foreach (DataRow rows in table.Rows) _entry.Add(new PerformanceReportEntry((int)rows[0], rows[1].ToString(), (int)rows[2], (int)rows[3], (int)rows[4])); }
public override void Delete() { DatabaseConnection connection = new DatabaseConnection( @"delete from CCEx_ProspectCaseAvailability where intID = @id;"); connection.AddParameter("@id", ID).Execute(); }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection( @"select * from CCex_ProspectCaseConversation where intID = @id;"); DataTable result = connection.AddParameter("@id", ID).Query; Date = (DateTime)result.Rows[0]["dtmConverse"]; Conversation = result.Rows[0]["strConversation"].ToString(); StaffID = (int)result.Rows[0]["intStaffID"]; }
public string GetCaseStandardName() { string standardName = ""; DatabaseConnection connection = new DatabaseConnection( @"select strNameSubject from Subject where intIDSubject = @id;"); standardName = connection.AddParameter("@id", SubjectID).Query.Rows[0][0].ToString(); connection = new DatabaseConnection( @"select strStatusDesc from CCEx_Status where intID = @id "); string statusName = connection.AddParameter("@id", CaseStatusID).Query.Rows[0][0].ToString(); connection = new DatabaseConnection( @"select strNameLevel from Level where intIdLevel = @id;"); string levelName = connection.AddParameter("@id", LevelID).Query.Rows[0][0].ToString(); if (levelName.ToLower().StartsWith("ip")) { connection = new DatabaseConnection( @"select strNameSchool from School where intSchool = @id;"); string schoolName = connection.AddParameter("@id", SchoolID).Query.Rows[0][0].ToString(); return standardName + " " + levelName + " (" + schoolName + ") - " + statusName; } else { return standardName + " " + levelName + " (General) - " + statusName; } }
public Prospect GetProspectStudent() { DatabaseConnection connection = new DatabaseConnection( @"select intStudentID from CCEx_ProspectCase where intID = @id;"); return new Prospect((int)connection.AddParameter("@id", ID).Query.Rows[0][0], null); }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection( "select * from CCEx_Group where intID = @id"); DataTable result = connection.AddParameter("@id", ID).Query; if (result.Rows.Count > 0) { Name = result.Rows[0]["strName"].ToString(); connection = new DatabaseConnection( "select * from CCEx_GroupMember where intGroupID = @id"); result = connection.AddParameter("@id", ID).Query; foreach (DataRow row in result.Rows) AddMember((int)row["intStudentID"]); } }
public override void Insert() { DatabaseConnection connection = new DatabaseConnection( @"declare @groupinfot as table(id int); insert into CCEx_Group (strName) output inserted.intID into @groupinfot values (@name); declare @groupidt as int; select @groupidt = id from @groupinfot; select @groupidt;"); DataTable result = connection.AddParameter("@name", Name).Query; ID = (int)result.Rows[0][0]; }
public override void Insert() { DatabaseConnection connection = new DatabaseConnection( @"declare @caseinfot as table(id int); insert into CCEx_ProspectCase (intBranchID, intLevelID, intOwnerID, intSchoolID, intStatusID, intStudentID, intSubjectID) output inserted.intID into @caseinfot values (@branchid, @levelid, @staffid, @schoolid, @statusid, @studentid, @subjectid); select id from @caseinfot; "); DataTable result = connection .AddParameter("@branchid", BranchID) .AddParameter("@levelid", LevelID) .AddParameter("@staffid", StaffID) .AddParameter("@schoolid", SchoolID) .AddParameter("@statusid", CaseStatusID) .AddParameter("@studentid", Parent.ID) .AddParameter("@subjectid", SubjectID) .Query; ID = (int)result.Rows[0][0]; }
public override void Populate() { DatabaseConnection connection = new DatabaseConnection( @"select * from CCEx_ProspectCase where intID = @id;"); DataTable result = connection.AddParameter("@id", ID).Query; SubjectID = (int)result.Rows[0]["intSubjectID"]; BranchID = (int)result.Rows[0]["intBranchID"]; LevelID = (int)result.Rows[0]["intLevelID"]; SchoolID = (int)result.Rows[0]["intSchoolID"]; CaseStatusID = (int)result.Rows[0]["intStatusID"]; StaffID = (int)result.Rows[0]["intOwnerID"]; WorkspaceBufferEngine engine = CurrentSession.BufferEngine; ProspectStudentCaseRemarks newremarks = new ProspectStudentCaseRemarks(engine.GetNextValidID(), this); newremarks.StaffID = CurrentSession.CredentialManager.ValidStaffID(1); AddRemark(newremarks); connection = new DatabaseConnection( @"select CCex_ProspectCaseConversation.intID from CCex_ProspectCaseConversation where intCaseID = @caseid order by dtmConverse desc"); foreach (DataRow row in connection.AddParameter("@caseid", ID).Query.Rows) AddRemark(new ProspectStudentCaseRemarks((int)row[0], this)); connection = new DatabaseConnection( @"select intID from CCEx_ProspectCaseAvailability where intCaseID = @caseid;"); foreach (DataRow row in connection.AddParameter("@caseid", ID).Query.Rows) AddAvailability(new ProspectStudentCaseAvailability((int)row[0], this)); }
public bool IsInstanceStudent() { DatabaseConnection connection = new DatabaseConnection( @"select count(*) from CCEx_ProspectStudent where intID = @id"); return (int)connection.AddParameter("@id", ID).Query.Rows[0][0] > 0; }
public override void Update() { DatabaseConnection connection = new DatabaseConnection( @"update CCEx_Group set strName = @name where intID = @id;"); connection.AddParameter("@name", Name).Execute(); }
public override void Update() { DatabaseConnection connection = new DatabaseConnection( @"update CCEx_ProspectCase set intBranchID = @branchid, intLevelID = @levelid, intSchoolID = @schoolid, intStatusID = @statusid, intSubjectID = @subjectid where intID = @id; "); connection .AddParameter("@branchid", BranchID) .AddParameter("@levelid", LevelID) .AddParameter("@staffid", StaffID) .AddParameter("@schoolid", SchoolID) .AddParameter("@statusid", CaseStatusID) .AddParameter("@studentid", Parent.ID) .AddParameter("@subjectid", SubjectID) .AddParameter("@id", ID) .Execute(); }