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);
}
}
