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 Delete()
{
DeleteFromRoot();
DatabaseConnection connection = new DatabaseConnection(
@"delete CCEx_Group where intID = @groupid;");
connection.AddParameter("@groupid", ID).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 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 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];
}
public static List<Prospect> GetAllProspect()
{
List<Prospect> collection = new List<Prospect>();
DatabaseConnection connection = new DatabaseConnection(
@"select intID from CCEx_Prospect;");
foreach (DataRow row in connection.Query.Rows)
collection.Add(new Prospect((int)row[0], null));
return collection;
}
public static List<Group> GetAllGroups()
{
List<Group> groupList = new List<Group>();
DatabaseConnection connection = new DatabaseConnection(
"select intID from CCEx_Group;");
DataTable result = connection.Query;
foreach (DataRow row in result.Rows)
groupList.Add(new Group((int)row[0], null));
return groupList;
}
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]));
}
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 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 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;
}
}
private void Populate()
{
DatabaseConnection connection = new DatabaseConnection(
@"
/******************************************************
This is a Demand Report
******************************************************/
-- We select all subjects --
declare @expressTable as table(subjectid int, levelid int, counts int);
declare @iptable as table(subjectid int, levelid int, schoolid int, counts int);
declare @currentSubject as int, @currentLevel as int, @currentSchool as int;
declare subcur cursor for
select intSubjectID from [Subject]
open subcur
fetch next from subcur into @currentsubject
while (@@FETCH_STATUS = 0)
begin
-- Against all Level --
declare levcur cursor for
select intLevelID from [Level]
open levcur
fetch next from levcur into @currentlevel
while (@@FETCH_STATUS = 0)
begin
declare @levelname as varchar(30);
select @levelname = strNameLevel from [Level] where intIdLevel = @currentLevel;
if (SUBSTRING(@levelname, 0, 3) like 'IP')
begin
declare schoolcur cursor for
select intSchool from [School]
open schoolcur
fetch next from schoolcur into @currentschool
while (@@FETCH_STATUS = 0)
begin
declare @count as int;
select @count = COUNT(*) from CCEx_ProspectCase where intSubjectID = @currentsubject and
intLevelID = @currentLevel and intSchoolID = @currentSchool and intStatusID = 1
if (@count > 0)
insert into @ipTable(subjectid, levelid, schoolid, counts) values (@currentSubject, @currentLevel, @currentschool, @count);
fetch next from schoolcur into @currentschool
end
close schoolcur
deallocate schoolcur
end
else
begin
declare @count1 as int;
select @count1 = COUNT(*) from CCEx_ProspectCase where intSubjectID = @currentsubject and
intLevelID = @currentLevel and intStatusID = 1
if (@count1 > 0)
insert into @expressTable(subjectid, levelid, counts) values (@currentSubject, @currentLevel, @count1);
end
fetch next from levcur into @currentlevel
end
close levcur
deallocate levcur
fetch next from subcur into @currentsubject
end
close subcur
deallocate subcur
declare @result as table (subjectid int, levelid int, schoolid int, counts int);
insert into @result (subjectid, levelid, counts) select subjectid, levelid, counts from @expresstable;
insert into @result (subjectid, levelid, schoolid, counts) select subjectid, levelid, schoolid, counts from @iptable;
select subjectid, levelid, schoolid, counts from @result;
");
DataTable table = connection.Query;
foreach (DataRow row in table.Rows)
_entries.Add(new DemandReportEntry(
(int)row[0], (int)row[1], (int)row[2], (int)row[3]));
}
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 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 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 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 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();
}
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 Update()
{
DatabaseConnection connection = new DatabaseConnection(
@"update CCEx_Group set strName = @name where intID = @id;");
connection.AddParameter("@name", Name).Execute();
}
