private void InitNonSSAttend()
{
this.dgvData.Rows.Clear();
if (string.IsNullOrEmpty(this.lblSchoolYear.Text) || string.IsNullOrEmpty(this.lblSemester.Text) || string.IsNullOrEmpty(cboIdentity.Text))
{
return;
}
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
if (item.Text == "所有學生")
{
InitNonSSAttend_AllStudent();
return;
}
IEnumerable <UDT.Identity> identities = Access.Select <UDT.Identity>();
IEnumerable <UDT.SIRelation> sirelations = Access.Select <UDT.SIRelation>();
var qry = from cfr in identities
join cfmtr in sirelations on int.Parse(cfr.UID) equals cfmtr.IdentityID
select new { DeptID = cfr.DeptID, GradeYear = cfr.GradeYear };
UDT.Identity identity_Record = item.Tag as UDT.Identity;
int dept_id = identity_Record.DeptID;
int grade_year = identity_Record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
string SQL = string.Format(@"select class_name, seat_no, student_number, student.name, student.ref_dept_id as student_dept_id, class.ref_dept_id as class_dept_id, class.grade_year from student
left join class on class.id=student.ref_class_id
where student.status in (1, 2) and class.status=1 and student.id not in
(
select sa.ref_student_id from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id
where subject.school_year={0} and subject.semester={1}
)
order by class_name, seat_no, student_number, student.name;", this.lblSchoolYear.Text, this.lblSemester.Text);
DataTable dataTable = queryHelper.Select(SQL);
foreach (DataRow row in dataTable.Rows)
{
string selected_dept_id = !string.IsNullOrEmpty(row["student_dept_id"] + "") ? (row["student_dept_id"] + "") : (row["class_dept_id"] + "");
if ((selected_dept_id != dept_id.ToString()) || (row["grade_year"] + "" != grade_year.ToString()))
{
continue;
}
if (qry.Where(x => x.DeptID.ToString() == selected_dept_id).Where(x => (x.GradeYear - (this.CurrentSchoolYear - this.DefaultSchoolYear)).ToString() == (row["grade_year"] + "")).Count() == 0)
{
continue;
}
object[] rowData = new object[] { "", "", "", "", row["class_name"] + "", row["seat_no"] + "", row["student_number"] + "", row["name"] + "" };
int rowIndex = this.dgvData.Rows.Add(rowData);
}
}
private void cboIdentity_SelectedIndexChanged(object sender, EventArgs e)
{
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
UDT.Identity identity_Record = item.Tag as UDT.Identity;
InitSelectableSubject(identity_Record);
}
private string SetQueryString()
{
int school_year = this.CurrentSchoolYear;
int semester = this.CurrentSemester;
int grade_year = 0;
int dept_id = 0;
string querySQL = string.Empty;
ComboItem item = this.cboIdentity.SelectedItem as ComboItem;
UDT.Identity record = (item == null ? null : item.Tag as UDT.Identity);
if (record != null)
{
grade_year = record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
dept_id = record.DeptID;
querySQL = string.Format(@"select student.id as 學生系統編號, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 學生姓名 from student
left join class on class.id=student.ref_class_id
where student.status in (1, 2) and class.status=1 and student.id not in
(
select sa.ref_student_id from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id
where subject.school_year={0} and subject.semester={1}
)
and class.grade_year={2} and case when student.ref_dept_id is NULL then class.ref_dept_id else student.ref_dept_id end = {3}
order by class_name, seat_no, student_number, student.name", school_year, semester, grade_year, dept_id);
}
else
{
if (item == null || string.IsNullOrEmpty(item.Text))
{
querySQL = string.Format(@"select '' as 學生系統編號, '' as 班級, '' as 座號, '' as 學號, '' as 學生姓名");
}
else
{
string condition_dept_id = @"select ref_dept_id from $ischool.course_selection.identity join $ischool.course_selection.si_relation on $ischool.course_selection.si_relation.ref_identity_id=$ischool.course_selection.identity.uid
group by ref_dept_id";
string condition_grade_year = @"select grade_year from $ischool.course_selection.identity join $ischool.course_selection.si_relation on $ischool.course_selection.si_relation.ref_identity_id=$ischool.course_selection.identity.uid
group by grade_year";
grade_year = (this.CurrentSchoolYear - this.DefaultSchoolYear);
querySQL = string.Format(@"select student.id as 學生系統編號, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 學生姓名 from student left join class on class.id=student.ref_class_id
where student.status in (1, 2) and class.status=1 and student.id not in
(
select sa.ref_student_id from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id
where subject.school_year={0} and subject.semester={1}
)
and (class.grade_year + {2}) in ({3}) and case when student.ref_dept_id is NULL then class.ref_dept_id else student.ref_dept_id end in ({4})
order by class_name, seat_no, student_number, student.name", school_year, semester, grade_year, condition_grade_year, condition_dept_id);
}
}
return(querySQL);
}
private void InitSelectableSubject(UDT.Identity identity)
{
this.cboSelectableSubject.Items.Clear();
this.cboSelectableSubject.Text = string.Empty;
if (identity == null)
{
return;
}
if (string.IsNullOrEmpty(this.lblSchoolYear.Text) || string.IsNullOrEmpty(this.lblSemester.Text))
{
return;
}
List <UDT.SIRelation> records = Access.Select <UDT.SIRelation>(string.Format("ref_identity_id = {0}", identity.UID));
if (records.Count == 0)
{
return;
}
List <UDT.Subject> subjects = Access.Select <UDT.Subject>(string.Format("uid in ({0}) and school_year={1} and semester={2}", string.Join(",", records.Select(x => x.SubjectID)), this.lblSchoolYear.Text, this.lblSemester.Text));
Dictionary <string, UDT.Subject> dicSubjects = new Dictionary <string, UDT.Subject>();
if (subjects.Count == 0)
{
return;
}
dicSubjects = subjects.ToDictionary(x => x.UID);
ComboItem comboItem1 = new ComboItem("");
comboItem1.Tag = 0;
this.cboSelectableSubject.Items.Add(comboItem1);
foreach (UDT.SIRelation record in records)
{
if (!dicSubjects.ContainsKey(record.SubjectID.ToString()))
{
continue;
}
string subject_name = dicSubjects[record.SubjectID.ToString()].SubjectName;
string subject_level = dicSubjects[record.SubjectID.ToString()].Level.HasValue ? RomanChar(dicSubjects[record.SubjectID.ToString()].Level.Value.ToString()) : string.Empty;
ComboItem item = new ComboItem(subject_name + subject_level);
item.Tag = record.SubjectID;
this.cboSelectableSubject.Items.Add(item);
}
this.cboSelectableSubject.SelectedItem = comboItem1;
}
private void InitNonSSAttend(int subject_id)
{
this.dgvSelectNoneStudent.Rows.Clear();
this.lblNoneSelectedAmount.Text = "未選修學生";
if (string.IsNullOrEmpty(this.lblSchoolYear.Text) || string.IsNullOrEmpty(this.lblSemester.Text) || string.IsNullOrEmpty(cboIdentity.Text) || string.IsNullOrEmpty(this.cboSelectableSubject.Text))
{
return;
}
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
UDT.Identity identity_Record = item.Tag as UDT.Identity;
int grade_year = identity_Record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
StringBuilder sb = new StringBuilder();
sb.Append("select class_name, seat_no, student_number, student.name, class.grade_year, student.id as student_id ");
sb.Append("from student left join class on class.id=student.ref_class_id ");
sb.Append("where student.status in (1, 2) and class.status=1 ");
sb.Append("and student.id not in "); //學生不在...以下條件
sb.Append("(select sa.ref_student_id from $ischool.course_selection.ss_attend as sa ");
sb.Append("join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id ");
sb.Append(string.Format("where subject.uid = {0}) ", subject_id));
sb.Append("order by class_name, seat_no, student_number, student.name");
DataTable dataTable = queryHelper.Select(sb.ToString());
foreach (DataRow row in dataTable.Rows)
{
if (row["grade_year"] + "" != grade_year.ToString())
{
continue;
}
object[] rowData = new object[] { row["class_name"] + "", row["seat_no"] + "", row["student_number"] + "", row["name"] + "" };
int rowIndex = this.dgvSelectNoneStudent.Rows.Add(rowData);
this.dgvSelectNoneStudent.Rows[rowIndex].Tag = row["student_id"] + "";
}
if (this.dgvSelectNoneStudent.Rows.Count == 0)
{
return;
}
this.lblNoneSelectedAmount.Text = "未選修學生：" + this.dgvSelectNoneStudent.Rows.Count + "人";
this.dgvSelectNoneStudent.SelectedRows.Cast <DataGridViewRow>().ToList().ForEach(x => x.Selected = false);
}
public frmIdentityCreator(string UID)
{
InitializeComponent();
this.Load += new EventHandler(frmIdentityCreator_Load);
this.Access = new AccessHelper();
if (string.IsNullOrEmpty(UID))
{
mRecord = null;
}
else
{
mRecord = Access.Select <UDT.Identity>(string.Format("uid = {0}", UID)).ElementAt(0);
}
}
private void InitSSAttend()
{
this.dgvData.Rows.Clear();
if (string.IsNullOrEmpty(this.lblSchoolYear.Text) || string.IsNullOrEmpty(this.lblSemester.Text) || string.IsNullOrEmpty(cboIdentity.Text))
{
return;
}
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
if (item.Text == "所有學生")
{
InitSSAttend_AllStudent();
return;
}
UDT.Identity identity_Record = item.Tag as UDT.Identity;
int dept_id = identity_Record.DeptID;
int grade_year = identity_Record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
string SQL = string.Format(@"select subject.subject_name, subject.level, subject.credit, subject.type, class_name, seat_no, student_number, student.name, student.ref_dept_id as student_dept_id, class.ref_dept_id as class_dept_id, class.grade_year from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on sa.ref_subject_id=subject.uid
join student on student.id=sa.ref_student_id
left join class on class.id=student.ref_class_id
where subject.school_year={0} and subject.semester={1} and student.status in (1, 2)
order by subject.subject_name, subject.level, class_name, seat_no, student_number, student.name;", this.lblSchoolYear.Text, this.lblSemester.Text);
DataTable dataTable = queryHelper.Select(SQL);
foreach (DataRow row in dataTable.Rows)
{
string selected_dept_id = !string.IsNullOrEmpty(row["student_dept_id"] + "") ? (row["student_dept_id"] + "") : (row["class_dept_id"] + "");
if ((selected_dept_id != dept_id.ToString()) || (row["grade_year"] + "" != grade_year.ToString()))
{
continue;
}
object[] rowData = new object[] { row["subject_name"] + "", row["level"] + "", row["credit"] + "", row["type"] + "", row["class_name"] + "", row["seat_no"] + "", row["student_number"] + "", row["name"] + "" };
int rowIndex = this.dgvData.Rows.Add(rowData);
}
}
private string SetQueryString()
{
int school_year = this.CurrentSchoolYear;
int semester = this.CurrentSemester;
int grade_year = 0;
int dept_id = 0;
string querySQL = string.Empty;
ComboItem item = this.cboIdentity.SelectedItem as ComboItem;
UDT.Identity record = (item == null ? null : item.Tag as UDT.Identity);
if (record != null)
{
grade_year = record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
dept_id = record.DeptID;
querySQL = string.Format(@"select sa.uid as 科目選課結果系統編號, subject.school_year as 學年度, subject.semester as 學期, subject.subject_name as 科目名稱, subject.level as 級別, subject.credit as 學分, subject.type as 課程時段, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 姓名
from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on sa.ref_subject_id=subject.uid
join student on student.id=sa.ref_student_id
left join class on class.id=student.ref_class_id
where subject.school_year={0} and subject.semester={1} and class.grade_year={2} and case when student.ref_dept_id is NULL then class.ref_dept_id else student.ref_dept_id end = {3} and student.status in (1, 2)
order by subject.subject_name, subject.level, class_name, seat_no, student_number, student.name", school_year, semester, grade_year, dept_id);
}
else
{
if (item == null || string.IsNullOrEmpty(item.Text))
{
querySQL = string.Format(@"select '' as 科目選課結果系統編號, '' as 學年度, '' as 學期, '' as 科目名稱, '' as 級別, '' as 學分, '' as 課程時段, '' as 班級, '' as 座號, '' as 學號, '' as 姓名");
}
else
{
querySQL = string.Format(@"select sa.uid as 科目選課結果系統編號, subject.school_year as 學年度, subject.semester as 學期, subject.subject_name as 科目名稱, subject.level as 級別, subject.credit as 學分, subject.type as 課程時段, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 姓名
from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on sa.ref_subject_id=subject.uid
join student on student.id=sa.ref_student_id
left join class on class.id=student.ref_class_id
where subject.school_year={0} and subject.semester={1} and student.status in (1, 2)
order by subject.subject_name, subject.level, class_name, seat_no, student_number, student.name", school_year, semester);
}
}
return(querySQL);
}
private void InitSSAttend(int subject_id)
{
this.lblSelectedAmount.Text = "已選修學生";
this.lblCurrentSSAttendAmount.Text = "";
this.dgvSelectedStudent.Rows.Clear();
if (string.IsNullOrEmpty(this.lblSchoolYear.Text) || string.IsNullOrEmpty(this.lblSemester.Text) || string.IsNullOrEmpty(cboIdentity.Text) || string.IsNullOrEmpty(cboSelectableSubject.Text))
{
return;
}
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
UDT.Identity identity_Record = item.Tag as UDT.Identity;
int grade_year = identity_Record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
string SQL = string.Format(@"select class_name, seat_no, student_number, student.name, Student.id as student_id from $ischool.course_selection.ss_attend as sa
join $ischool.course_selection.subject as subject on sa.ref_subject_id=subject.uid
join student on student.id=sa.ref_student_id
left join class on class.id=student.ref_class_id
where subject.school_year={0} and subject.semester={1} and subject.uid={2} and student.status in (1, 2)
order by class_name, seat_no, student_number, student.name;", this.lblSchoolYear.Text, this.lblSemester.Text, subject_id);
DataTable dataTable = queryHelper.Select(SQL);
foreach (DataRow row in dataTable.Rows)
{
object[] rowData = new object[] { row["class_name"] + "", row["seat_no"] + "", row["student_number"] + "", row["name"] };
int rowIndex = this.dgvSelectedStudent.Rows.Add(rowData);
this.dgvSelectedStudent.Rows[rowIndex].Tag = row["student_id"] + "";
}
if (this.dgvSelectedStudent.Rows.Count == 0)
{
return;
}
this.lblSelectedAmount.Text = "已選修學生：" + this.dgvSelectedStudent.Rows.Count + "人";
this.lblCurrentSSAttendAmount.Text = this.dgvSelectedStudent.Rows.Count.ToString();
this.dgvSelectedStudent.SelectedRows.Cast <DataGridViewRow>().ToList().ForEach(x => x.Selected = false);
}
private void SaveData()
{
int grade_year = 0;
bool result = int.TryParse(this.GradeYear.Text, out grade_year);
UDT.Identity record;
if (mRecord == null)
{
record = new UDT.Identity();
}
else
{
record = mRecord;
}
record.GradeYear = grade_year;
record.Save();
}
private void SaveData()
{
ComboItem item = (ComboItem)this.cboDepart.SelectedItem;
SHDepartmentRecord dept = (SHDepartmentRecord)item.Tag;
int grade_year = 0;
bool result = int.TryParse(this.GradeYear.Text, out grade_year);
UDT.Identity record;
if (mRecord == null)
{
record = new UDT.Identity();
}
else
{
record = mRecord;
}
record.DeptID = int.Parse(dept.ID);
record.GradeYear = grade_year;
record.Save();
}
private void InitSelectableSubectInfo(int subject_id)
{
this.lblGroup.Text = string.Empty;
this.lblGroupLimit.Text = string.Empty;
ComboItem item = (ComboItem)this.cboIdentity.SelectedItem;
UDT.Identity identity_Record = item.Tag as UDT.Identity;
if (identity_Record == null)
{
return;
}
List <UDT.SIRelation> records = Access.Select <UDT.SIRelation>(string.Format("ref_subject_id = ({0}) and ref_identity_id = ({1})", subject_id, identity_Record.UID));
if (records.Count == 0)
{
return;
}
this.lblGroup.Text = records[0].Group;
this.lblGroupLimit.Text = records[0].CountLimit.ToString();
}
private string SetQueryString()
{
int school_year = this.CurrentSchoolYear;
int semester = this.CurrentSemester;
int grade_year = 0;
ComboItem item = this.cboIdentity.SelectedItem as ComboItem;
UDT.Identity record = (item == null ? null : item.Tag as UDT.Identity);
string querySQL = string.Empty;
//身份是否有選(一年級/二年級/三年級)
if (record != null)
{
StringBuilder sb = new StringBuilder();
grade_year = record.GradeYear;
grade_year -= (this.CurrentSchoolYear - this.DefaultSchoolYear);
sb.Append("select student.id as 學生系統編號, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 學生姓名 ");
sb.Append("from student ");
sb.Append("left join class on class.id=student.ref_class_id ");
sb.Append("where student.status in (1, 2) and student.id not in ");
sb.Append("(");
sb.Append("select sa.ref_student_id from $ischool.course_selection.ss_attend as sa ");
sb.Append("join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id ");
sb.Append("join student on student.id = sa.ref_student_id ");
sb.Append("left join class on class.id = student.ref_class_id ");
sb.Append(string.Format("where subject.school_year={0} and subject.semester={1} ", school_year, semester));
sb.Append(string.Format("and class.grade_year={0}", grade_year));
sb.Append(string.Format(") and class.grade_year='{0}'", grade_year));
sb.Append("order by class_name, seat_no, student_number, student.name");
//
querySQL = sb.ToString();
}
else
{
StringBuilder sb = new StringBuilder();
StringBuilder sb_1 = new StringBuilder();
StringBuilder sb_2 = new StringBuilder();
if (item == null || string.IsNullOrEmpty(item.Text))
{
querySQL = string.Format(@"select '' as 學生系統編號, '' as 班級, '' as 座號, '' as 學號, '' as 學生姓名");
}
else
{
sb_2.Append("select grade_year from $ischool.course_selection.identity ");
sb_2.Append("join $ischool.course_selection.si_relation on $ischool.course_selection.si_relation.ref_identity_id=$ischool.course_selection.identity.uid group by grade_year");
grade_year = (this.CurrentSchoolYear - this.DefaultSchoolYear);
sb.Append("select student.id as 學生系統編號, class_name as 班級, seat_no as 座號, student_number as 學號, student.name as 學生姓名 ");
sb.Append("from student left join class on class.id=student.ref_class_id ");
sb.Append("where student.status in (1, 2) ");
sb.Append("and student.id not in ");
sb.Append("(");
sb.Append("select sa.ref_student_id from $ischool.course_selection.ss_attend as sa ");
sb.Append("join $ischool.course_selection.subject as subject on subject.uid=sa.ref_subject_id ");
sb.Append(string.Format("where subject.school_year={0} and subject.semester={1} )", school_year, semester));
sb.Append(string.Format("order by class_name, seat_no, student_number, student.name"));
//sb.Append(string.Format("and (class.grade_year + {0}) in ({1}) ", grade_year, sb_2.ToString()));
//sb.Append(string.Format("case when student.ref_dept_id is NULL then class.ref_dept_id else student.ref_dept_id end in ({0})", sb_2.ToString()));
querySQL = sb.ToString();
}
}
return(querySQL);
}