public string[] setStudentAid(CreateStudentAid StudentTracked)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE FinancialAidApplications SET ContactName=@ContactName, ContactTel_home=@ContactTel_home, ContactPhone=@ContactPhone, " +
"AddressZip=@AddressZip, AddressCity=@AddressCity, AddressOther=@AddressOther, AidCategory=@AidCategory, AidCategory_other=@AidCategory_other, " +
"DataPayment=@DataPayment, DisabilityCategory=@DisabilityCategory, DisabilityGrade=@DisabilityGrade, OtherNotes=@OtherNotes, Grants=@Grants, " +
"GrantsTimeSince=@GrantsTimeSince, GrantsTimeUntil=@GrantsTimeUntil, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
"WHERE ID=@ID AND isDeleted=0";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(StudentTracked.ID);
cmd.Parameters.Add("@ContactName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentName);
cmd.Parameters.Add("@ContactTel_home", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentTel);
cmd.Parameters.Add("@ContactPhone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentPhone);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.address);
cmd.Parameters.Add("@AidCategory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.subsidyitem);
cmd.Parameters.Add("@AidCategory_other", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.subsidytext);
cmd.Parameters.Add("@DataPayment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.payitem);
cmd.Parameters.Add("@DisabilityCategory", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory);
cmd.Parameters.Add("@DisabilityGrade", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade);
cmd.Parameters.Add("@OtherNotes", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.othertext);
cmd.Parameters.Add("@Grants", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.subsidymoney);
cmd.Parameters.Add("@GrantsTimeSince", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.subsidydate1);
cmd.Parameters.Add("@GrantsTimeUntil", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.subsidydate2);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] createStudentAid(CreateStudentAid StudentTracked)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO FinancialAidApplications(WriteDate, Unit, StudentID, AidCategory, AidCategory_other, DataPayment, DisabilityCategory, " +
"DisabilityGrade, OtherNotes, Grants, GrantsTimeSince, GrantsTimeUntil , ContactName, ContactTel_home, ContactPhone, AddressZip, " +
"AddressCity, AddressOther, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@WriteDate, @Unit, @StudentID, @AidCategory, @AidCategory_other, @DataPayment, @DisabilityCategory, @DisabilityGrade, " +
"@OtherNotes, @Grants, @GrantsTimeSince, @GrantsTimeUntil, @ContactName, @ContactTel_home, @ContactPhone, @AddressZip, @AddressCity, " +
"@AddressOther, @CreateFileBy, @UpFileBy, (getDate()) )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.fillInDate);
cmd.Parameters.Add("@StudentID", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.studentID);
cmd.Parameters.Add("@ContactName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentName);
cmd.Parameters.Add("@ContactTel_home", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentTel);
cmd.Parameters.Add("@ContactPhone", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.ParentPhone);
cmd.Parameters.Add("@AddressZip", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.addressZip);
cmd.Parameters.Add("@AddressCity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.addressCity);
cmd.Parameters.Add("@AddressOther", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.address);
cmd.Parameters.Add("@AidCategory", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StudentTracked.subsidyitem);
cmd.Parameters.Add("@AidCategory_other", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.subsidytext);
cmd.Parameters.Add("@DataPayment", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.payitem);
cmd.Parameters.Add("@DisabilityCategory", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualCategory);
cmd.Parameters.Add("@DisabilityGrade", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.manualGrade);
cmd.Parameters.Add("@OtherNotes", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StudentTracked.othertext);
cmd.Parameters.Add("@Grants", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StudentTracked.subsidymoney);
cmd.Parameters.Add("@GrantsTimeSince", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.subsidydate1);
cmd.Parameters.Add("@GrantsTimeUntil", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StudentTracked.subsidydate2);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
sql = "select IDENT_CURRENT('FinancialAidApplications') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue[1] = dr["cID"].ToString();
}
dr.Close();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setStudentAidData(CreateStudentAid StructData)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setStudentAid(StructData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public CreateStudentAid getStudentAidData(string aID)
{
CreateStudentAid returnValue = new CreateStudentAid();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT FinancialAidApplications.*, StudentDatabase.StudentName, StudentDatabase.StudentBirthday, StudentDatabase.StudentIdentity " +
"FROM FinancialAidApplications INNER JOIN StudentDatabase ON FinancialAidApplications.StudentID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"WHERE FinancialAidApplications.isDeleted=0 AND FinancialAidApplications.ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(aID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.studentID = dr["StudentID"].ToString();
returnValue.studentName = dr["StudentName"].ToString();
returnValue.studentbirthday = DateTime.Parse(dr["StudentBirthday"].ToString()).ToString("yyyy-MM-dd");
returnValue.addressZip = dr["AddressZip"].ToString();
returnValue.addressCity = dr["AddressCity"].ToString();
returnValue.address = dr["AddressOther"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.sUnit = dr["Unit"].ToString();
returnValue.subsidyitem = dr["AidCategory"].ToString();
returnValue.subsidytext = dr["AidCategory_other"].ToString();
returnValue.payitem = dr["DataPayment"].ToString();
returnValue.manualCategory = dr["DisabilityCategory"].ToString();
returnValue.manualGrade = dr["DisabilityGrade"].ToString();
returnValue.othertext = dr["OtherNotes"].ToString();
returnValue.subsidymoney = dr["Grants"].ToString();
returnValue.subsidydate1 = DateTime.Parse(dr["GrantsTimeSince"].ToString()).ToString("yyyy-MM-dd");
returnValue.subsidydate2 = DateTime.Parse(dr["GrantsTimeUntil"].ToString()).ToString("yyyy-MM-dd");
returnValue.ParentName = dr["ContactName"].ToString();
returnValue.ParentTel = dr["ContactTel_home"].ToString();
returnValue.ParentPhone = dr["ContactPhone"].ToString();
returnValue.studentTWID = dr["StudentIdentity"].ToString();
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
public CreateStudentAid getStudentAidData(string aID)
{
CaseDataBase SDB = new CaseDataBase();
CreateStudentAid returnValue = new CreateStudentAid();
if (int.Parse(SDB._StaffhaveRoles[3]) == 1)
{
returnValue=SDB.getStudentAidData(aID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.sUnit != UserFile[2] && int.Parse(SDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
