public string getGenType(Qualified_Student student_data) { Qualified_Student qualifiedObj = new Qualified_Student(); ConnectDB db = new ConnectDB(); SqlDataSource oracleObj = db.ConnectionOracle(); string gen_type = ""; string studentid1_2 = student_data.Year_Admission.Substring(2, 2); string studentid3_4 = student_data.Faculty_Code; string studentid5_6 = student_data.Department_Code_StudentID; string studentid7 = student_data.Major_Code_StudentID; string studentid8 = student_data.Degree_Admission; string studentid9 = student_data.Admission_Type; string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9; string table = qualifiedObj.getTableName(student_data.Degree_Char); string sql = "Select GEN_GROUP From " + table + " Where STUDENT_ID Like '" + tmp_studentid + "%' group by GEN_GROUP"; oracleObj.SelectCommand = sql; DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty); foreach (DataRowView rowData in allData) { gen_type = rowData["GEN_GROUP"].ToString(); } return(gen_type); }
// V 1.0.0 public bool exitsRoundUse(string academic_year, string semester, string faculty_code, int round_paid, string degree_char) { bool result = false; string table_name = ""; List <Qualified_Student> stdData = new List <Qualified_Student>(); try { table_name = getQuadlifiedStdTableName(degree_char); string sql = ""; if (degree_char == "U") { sql = "Select * From " + table_name + " Where (degree_admission_code='1' Or degree_admission_code='2' Or degree_admission_code='3') And YEAR_ADMISSION='" + academic_year + "' AND SEMESTER_ADMISSION='" + semester + "' And faculty_code='" + faculty_code + "' And round_paid=" + round_paid; } else if (degree_char == "B") { sql = "Select * From " + table_name + " Where (degree_admission_code='4' Or degree_admission_code='5' Or degree_admission_code='6') And YEAR_ADMISSION='" + academic_year + "' AND SEMESTER_ADMISSION='" + semester + "' And faculty_code='" + faculty_code + "' And round_paid=" + round_paid; } else if (degree_char == "M") { sql = "Select * From " + table_name + " Where degree_admission_code='8' And YEAR_ADMISSION='" + academic_year + "' AND SEMESTER_ADMISSION='" + semester + "' And faculty_code='" + faculty_code + "' And round_paid=" + round_paid; } else if (degree_char == "D") { sql = "Select * From " + table_name + " Where degree_admission_code='9' And YEAR_ADMISSION='" + academic_year + "' AND SEMESTER_ADMISSION='" + semester + "' And faculty_code='" + faculty_code + "' And round_paid=" + round_paid; } else if (degree_char == "P") { sql = "Select * From " + table_name + " Where degree_admission_code='7' And YEAR_ADMISSION='" + academic_year + "' AND SEMESTER_ADMISSION='" + semester + "' And faculty_code='" + faculty_code + "' And round_paid=" + round_paid; } stdData = new Qualified_Student().getManualQualifiedStudent(sql); if (stdData.Count > 0) { result = true; } else { result = false; } } catch { HttpContext.Current.Session["response"] = "Unit Test:RoundPaid:exitsRoundUse" + " ไม่สามารถดำเนินการได้"; HttpContext.Current.Response.Redirect("err_response.aspx"); } return(result); }
// V 1.0.0 public string delRoundPaid(RoundPaidData delData) { string response = ""; Qualified_Student qualified_student = new Qualified_Student(); ConnectDB db = new ConnectDB(); SqlDataSource oracleObj = db.ConnectionOracle(); //string qualified_std_table = new Qualified_Student().getTableName(delData.Degree_Char); //string sql_chk = "Select * From " + qualified_std_table + " Where YEAR_ADMISSION = '" + new SystemConfig().getConfig().AcademicYear + "' AND SEMESTER_ADMISSION = '" + new SystemConfig().getConfig().Semester + "' AND FACULTY_CODE='" + delData.Faculty_Code + "' AND ROUND_PAID=" + delData.RoundPaid; //qualified_student = new Qualified_Student().getManualOneQualifiedStudent(sql_chk); //if (qualified_student.Faculty_Code == null) //{ string sql = "Delete From Round_Paid Where ACADEMIC_YEAR='" + delData.Academic_Year + "' And SEMESTER='" + delData.Semester + "' And FACULTY_CODE='" + delData.Faculty_Code + "' And DEGREE_CHAR='" + delData.Degree_Char + "' And ROUND=" + delData.RoundPaid; oracleObj.DeleteCommand = sql; try { if (oracleObj.Delete() > 0) { response = "OK"; } } catch (Exception e) { response = e.Message.ToString(); } //} //else //{ // response = "ไม่สามารถลบรอบการชำระเงินได้"; // } return(response); }
// V 1.0.0 public string[] insertQualifiedSTD_BankData(BankHead bank_head, List <BankData> bankData) { string TableName = ""; string[] response = new string[bankData.Count]; string comp_account = new string(bank_head.CompAccount); string comp_name = new string(bank_head.CompName).Replace("'", "`"); string summary_date = bank_head.Bank_SummaryDate; Status_Variable status_var = new Status_Variable(); SystemConfigData system_config = new SystemConfigData(); system_config = new SystemConfig().getConfig(); int i = 0; ConnectDB db = new ConnectDB(); SqlDataSource oracleObj = db.ConnectionOracle(); foreach (BankData field in bankData) { TableName = getTableName(field.Degree_Char); int bank_amount = Convert.ToInt32(new string(field.Amount)); bank_amount = bank_amount / 100; string regis_type = new Student_Payment().getPayment(field.Paymenty_ID, field.Degree_Char).Reg_Type; if (regis_type == "NW") { oracleObj.InsertCommand = "Insert Into " + TableName + " (PAYMENT_ID,PAYMENT_DATE,COMPANY_AC,COMPANY_NAME,BANK_CODE,BRANCH_CODE,TELLER_NO,NATION_PASSPORT_ID,STUDENT_NAME,ACADEMIC_YEAR,SEMESTER,AMOUNT,KIND_TRANSACTION,TRANSACTION_CODE,DUE_DATE,IMPORT_DATE,STATUS,PAYMENT_TIME,BANK_SUMDATE) Values('" + field.Paymenty_ID + "','" + field.Payment_Date + "','" + comp_account + "','" + comp_name + "','" + new string(field.BankCode) + "','" + new string(field.BranNo) + "','" + new string(field.TellerNo) + "','" + field.Student_ID + "','" + new string(field.CustomerName) + "','" + field.AcademicYear + "','" + field.Semester + "'," + Convert.ToSingle(bank_amount) + ",'" + new string(field.KindTranc) + "','" + new string(field.TrancCode) + "','" + field.DueDate + "','" + field.ImportDate + "','" + field.Status + "','" + new string(field.PayTime) + "','" + summary_date + "')"; } else { oracleObj.InsertCommand = "Insert Into " + TableName + " (PAYMENT_ID,PAYMENT_DATE,COMPANY_AC,COMPANY_NAME,BANK_CODE,BRANCH_CODE,TELLER_NO,STUDENT_ID,STUDENT_NAME,ACADEMIC_YEAR,SEMESTER,AMOUNT,KIND_TRANSACTION,TRANSACTION_CODE,DUE_DATE,IMPORT_DATE,STATUS,PAYMENT_TIME,BANK_SUMDATE) Values('" + field.Paymenty_ID + "','" + field.Payment_Date + "','" + comp_account + "','" + comp_name + "','" + new string(field.BankCode) + "','" + new string(field.BranNo) + "','" + new string(field.TellerNo) + "','" + field.Student_ID + "','" + new string(field.CustomerName) + "','" + field.AcademicYear + "','" + field.Semester + "'," + Convert.ToSingle(bank_amount) + ",'" + new string(field.KindTranc) + "','" + new string(field.TrancCode) + "','" + field.DueDate + "','" + field.ImportDate + "','" + field.Status + "','" + new string(field.PayTime) + "','" + summary_date + "')"; } try { if (oracleObj.Insert() == 1) { response[i] = "OK"; // อัพเดทตาราง Payment if ((field.Status == status_var.BankData_Accurate) || (field.Status == status_var.BankData_Accurate_OverPaid)) { //นักศึกษาใหม่ if (regis_type == "NW") { Qualified_Student_Payment std_payment = new Qualified_Student_Payment(); string res_update = std_payment.updatePayment(field.Paymenty_ID, field.Payment_Date, status_var.QualifiedStudent_PaymentComplete, field.Degree_Char); // Generate Student_ID if (res_update == "OK") { GenStudentID gen_student_id = new GenStudentID(); PaymentData payment_data = std_payment.getPayment(field.Paymenty_ID, field.Degree_Char); // List<StudentIDConfigData> configData = new List<StudentIDConfigData>(); Qualified_Student student_data = new Qualified_Student().getQualifiedStudent(payment_data.Academic_Year, payment_data.Semester, payment_data.Nation_ID, payment_data.Passport_ID, payment_data.Curr_Code, payment_data.Round_Paid, field.Degree_Char); if (payment_data.Reg_Type == "NW") { string academic_year = student_data.Year_Admission.Substring(2, 2); string faculty_code = student_data.Faculty_Code; string department_code = student_data.Department_Code_StudentID; string major_code = student_data.Major_Code_StudentID; string degree_code = student_data.Degree_Admission; string admission_type = student_data.Admission_Type; string project_code = student_data.Project_Code; string new_student_id = gen_student_id.getStudentID(student_data); if (new_student_id.Length == 13) { // Update Student ID ตารางนักศึกษาใหม่ string update_status = new Qualified_Student().updateQualifiedStudent_ID(student_data.Year_Admission, student_data.Semester_Admission, student_data.Nation_ID, student_data.Passport_ID, student_data.Curri_Code, student_data.Round_Paid, new_student_id, field.Degree_Char); // Update ตารางลงทะเบียน new Qualified_Student_Registration().updateRegistrationStudentID(system_config.AcademicYear, system_config.Semester, student_data.Nation_ID, student_data.Passport_ID, student_data.Curri_Code, student_data.Project_Code, new_student_id, field.Degree_Char); // Update ตารางประวัติการลงทะเบียน new HistoryUpdate().update_HisRegistrationStudentID(system_config.AcademicYear, system_config.Semester, student_data.Nation_ID, student_data.Passport_ID, new_student_id, field.Degree_Char); // Update ตารางใบชำระเงิน std_payment.updatePayment_StudentID(field.Paymenty_ID, new_student_id, field.Degree_Char); } } } } // นักศึกษาปัจจุบันท์ else { Student_Payment std_payment = new Student_Payment(); string res_update = std_payment.updatePayment(field.Paymenty_ID, field.Payment_Date, status_var.PaymentComplete, field.Degree_Char); } } } } catch (Exception e) { response[i] = e.Message.ToString(); } i++; } // end foreach return(response); }
//public List<string> getStudentID(string academic_year, string faculty_code, string department_code, string major_code, string degree_code, string admissiontype_code, string gen_type, string sec_number, int amount) //{ // ConnectDB db = new ConnectDB(); // SqlDataSource oracleObj = db.ConnectionOracle(); // string sql = ""; // if (gen_type == "0") // ใช้ Sec // { // oracleObj.SelectCommand = "Select * From Run_Student_ID Where ACADEMIC_YEAR='" + academic_year + "' AND FACULTY_CODE='" + faculty_code + "' AND DEPARTMENT_CODE='" + department_code + "' AND MAJOR_CODE='" + major_code + "' AND DEGREE_CODE='" + degree_code + "' AND ADMISSION_TYPE='" + admissiontype_code + "' AND NUMBER1='" + sec_number + "' AND GEN_TYPE='" + gen_type + "'"; // DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty); // string studentid1_2 = academic_year; // string studentid3_4 = faculty_code; // string studentid5_6 = department_code; // string studentid7 = major_code; // string studentid8 = degree_code; // string studentid9 = admissiontype_code; // string studentid10 = sec_number; // if (allData.Count == 0) // { // for (int i = 1; i <= amount; i++) // { // string runNum = i.ToString("00"); // string studentid11 = runNum.Substring(0, 1); // string studentid12 = runNum.Substring(1, 1); // string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9 + studentid10 + studentid11 + studentid12; // gen_student_id.Add(getCheckDigit(tmp_studentid)); // } // } // else // { // int start_num = 0; // foreach (DataRowView rowData in allData) // { // int studentid11 = Convert.ToInt16(rowData["NUMBER2"]) * 10; // int studentid12 = Convert.ToInt16(rowData["NUMBER3"]); // start_num = studentid11 + studentid12; // } // for (int i = 1; i <= amount; i++) // { // int j = start_num + i; // string runNum = j.ToString("00"); // string studentid11 = runNum.Substring(0, 1); // string studentid12 = runNum.Substring(1, 1); // string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9 + studentid10 + studentid11 + studentid12; // gen_student_id.Add(getCheckDigit(tmp_studentid)); // } // } // } // else if (gen_type == "1") // ไม่ใช้ Sec // { // oracleObj.SelectCommand = "Select * From Run_Student_ID Where ACADEMIC_YEAR='" + academic_year + "' AND FACULTY_CODE='" + faculty_code + "' AND DEPARTMENT_CODE='" + department_code + "' AND MAJOR_CODE='" + major_code + "' AND DEGREE_CODE='" + degree_code + "' AND ADMISSION_TYPE='" + admissiontype_code + "' AND GEN_TYPE='" + gen_type + "'"; // DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty); // string studentid1_2 = academic_year; // string studentid3_4 = faculty_code; // string studentid5_6 = department_code; // string studentid7 = major_code; // string studentid8 = degree_code; // string studentid9 = admissiontype_code; // if (allData.Count == 0) // { // for (int i = 1; i <= amount; i++) // { // string runNum = i.ToString("000"); // string studentid10 = runNum.Substring(0, 1); // string studentid11 = runNum.Substring(1, 1); // string studentid12 = runNum.Substring(2, 1); // string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9 + studentid10 + studentid11 + studentid12; // gen_student_id.Add(getCheckDigit(tmp_studentid)); // } // } // else // { // int start_num = 0; // foreach (DataRowView rowData in allData) // { // int studentid10 = Convert.ToInt16(rowData["NUMBER1"]) * 100; // int studentid11 = Convert.ToInt16(rowData["NUMBER2"]) * 10; // int studentid12 = Convert.ToInt16(rowData["NUMBER3"]); // start_num = studentid10 + studentid11 + studentid12; // } // for (int i = 1; i <= amount; i++) // { // int j = start_num + i; // string runNum = j.ToString("000"); // string studentid10 = runNum.Substring(0, 1); // string studentid11 = runNum.Substring(1, 1); // string studentid12 = runNum.Substring(2, 1); // string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9 + studentid10 + studentid11 + studentid12; // gen_student_id.Add(getCheckDigit(tmp_studentid)); // } // } // } // //gen_student_id.Add(sql); // return gen_student_id; //} //public string getStudentID(string academic_year, string faculty_code, string department_code, string major_code, string degree_code, string admissiontype_code, string gen_type, string sec_number, string project_code) public string getStudentID(Qualified_Student student_data) { Qualified_Student qualifiedObj = new Qualified_Student(); ConnectDB db = new ConnectDB(); SqlDataSource oracleObj = db.ConnectionOracle(); string student_id = ""; if (student_data.Gen_Gruop == "y") // ใช้ Sec { string studentid1_2 = student_data.Year_Admission.Substring(2, 2); string studentid3_4 = student_data.Faculty_Code; string studentid5_6 = student_data.Department_Code_StudentID; string studentid7 = student_data.Major_Code_StudentID; string studentid8 = student_data.Degree_Admission; string studentid9 = student_data.Admission_Type; string studentid10 = ""; if (student_data.StudentGruop == "001") { studentid10 = "0"; } else if (student_data.StudentGruop == "002") { studentid10 = "1"; } else if (student_data.StudentGruop == "003") { studentid10 = "2"; } else if (student_data.StudentGruop == "004") { studentid10 = "3"; } else if (student_data.StudentGruop == "005") { studentid10 = "4"; } else if (student_data.StudentGruop == "006") { studentid10 = "5"; } else if (student_data.StudentGruop == "007") { studentid10 = "6"; } else if (student_data.StudentGruop == "008") { studentid10 = "7"; } else if (student_data.StudentGruop == "009") { studentid10 = "8"; } else if (student_data.StudentGruop == "010") { studentid10 = "9"; } else { studentid10 = "x"; } string max_student_id = ""; string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9 + studentid10; string table = qualifiedObj.getTableName(student_data.Degree_Char); string sql = "Select Max(TO_NUMBER(student_id)) AS MAX_ID From " + table + " Where STUDENT_ID Like '" + tmp_studentid + "%'"; oracleObj.SelectCommand = sql; DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty); foreach (DataRowView rowData in allData) { max_student_id = rowData["MAX_ID"].ToString(); } if (max_student_id == "") { string studentid11 = "0"; string studentid12 = "1"; string tmp_studentid2 = tmp_studentid + studentid11 + studentid12; student_id = getCheckDigit(tmp_studentid2); } else if (max_student_id.Length == 13) { int run_num = Convert.ToInt16(max_student_id.Substring(10, 2)); run_num++; if (run_num <= 99) { string tmp_studentid2 = tmp_studentid + run_num.ToString("00"); student_id = getCheckDigit(tmp_studentid2); } else { student_id = "max"; } } else { student_id = "err"; } } else if (student_data.Gen_Gruop == "n") // ไม่ใช้ Sec { string studentid1_2 = student_data.Year_Admission.Substring(2, 2); string studentid3_4 = student_data.Faculty_Code; string studentid5_6 = student_data.Department_Code_StudentID; string studentid7 = student_data.Major_Code_StudentID; string studentid8 = student_data.Degree_Admission; string studentid9 = student_data.Admission_Type; string max_student_id = ""; string tmp_studentid = studentid1_2 + studentid3_4 + studentid5_6 + studentid7 + studentid8 + studentid9; string table = qualifiedObj.getTableName(student_data.Degree_Char); string sql = "Select Max(TO_NUMBER(student_id)) AS MAX_ID From " + table + " Where STUDENT_ID Like '" + tmp_studentid + "%'"; oracleObj.SelectCommand = sql; DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty); foreach (DataRowView rowData in allData) { max_student_id = rowData["MAX_ID"].ToString(); } if (max_student_id == "") { string studentid10 = "0"; string studentid11 = "0"; string studentid12 = "1"; string tmp_studentid2 = tmp_studentid + studentid10 + studentid11 + studentid12; student_id = getCheckDigit(tmp_studentid2); } else if (max_student_id.Length == 13) { int run_num = Convert.ToInt16(max_student_id.Substring(9, 3)); run_num++; if (run_num <= 999) { string tmp_studentid2 = tmp_studentid + run_num.ToString("000"); student_id = getCheckDigit(tmp_studentid2); } else { student_id = "max"; } } else { student_id = "err"; } } if (student_id.Length == 13) { return(student_id); } else { return("ID_Error" + student_id); } }