public static List <MissingStudent> GetAllStudents(string query)
{
Files.dbConnection con = new Files.dbConnection();
//List<Student> studentCollection = new List<Student>(100000);
List <MissingStudent> studentCollection = new List <MissingStudent>();
//var model = null;
DataTable tempTable = null;
string sclearsql = string.Concat(@"[dbo].[sp_getMissingStudents]");
//tempTable = con.executeSelectNoParameter(sclearsql);
SqlParameter[] parameter =
{
new SqlParameter("@query", SqlDbType.VarChar)
{
Value = query
}
};
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
}
else
{
foreach (DataRow dr in tempTable.Rows)
{
MissingStudent st = new MissingStudent();
st.crn = dr["crn"].ToString();
st.termCode = dr["termCode"].ToString();
st.heatId = dr["heatId"].ToString();
st.bannerId = dr["bannerId"].ToString();
st.login = dr["login"].ToString();
st.studentName = dr["studentName"].ToString();
st.ticketRefNumber = dr["ticketRefNumber"].ToString();
st.mobilePersonal = dr["mobilePersonal"].ToString();
//string mag = dr["Magcode"].ToString();
st.Magcode = dr["Magcode"].ToString();
//st.image = "http://latasha.com.au/wp-content/uploads/online-profile-picture-45x45.jpg";
if (dr["Magcode"].ToString() == "")
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + "NoImage" + ".jpg";
}
else
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + dr["bannerId"].ToString() + ".jpg";
}
//st.image = @"\\SMSODBPWV01\photos$\PROD\"+ dr["bannerId"].ToString() +".jpg";
st.checkBox = false;
studentCollection.Add(st);
}
}
//return model;
return(studentCollection);
}
public static List <String> GetStudents(string query)
{
Files.dbConnection con = new Files.dbConnection();
List <String> studentCollection = new List <String>();
//var model = null;
DataTable tempTable = null;
string sclearsql = string.Concat("[dbo].[sp_getAllStudentsInputParams]");
SqlParameter[] parameter =
{
new SqlParameter("@query", SqlDbType.VarChar)
{
Value = query
}
};
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
//mail.To.Add(WebConfigurationManager.AppSettings["ToEmail"]);
}
else
{
foreach (DataRow dr in tempTable.Rows)
{
studentCollection.Add(
"Name: " + dr["Name"].ToString() + dr["LastName"].ToString()
+ " , " + "MobilePersonal: " + dr["MobilePersonal"].ToString()
+ " , " + "Barcode: " + dr["Id"].ToString()
);
}
}
return(studentCollection);
}
public static List <AttendanceCrnTerm> GetAllCrn(string staffId)
{
string oradb = @"Data Source=(DESCRIPTION =" + "(ADDRESS = (PROTOCOL = TCP)(HOST = KISMSDB-DEV.kbtm.kangan.edu.au)(PORT = 1521))"
+ "(CONNECT_DATA =" + "(SERVER = DEDICATED)"
+ "(SERVICE_NAME = test.kbtm.KANGAN.EDU.AU)));"
+ "User Id=baninst1;Password=u_pick_it;";
List <AttendanceCrnTerm> crnTermColl = new List <AttendanceCrnTerm>();
Files.dbConnection con = new Files.dbConnection();
System.Data.DataTable tempTable = null;
string sclearsql = string.Concat(@"[dbo].[sp_getStaffBannerId]");
SqlParameter[] parameter =
{
new SqlParameter("@query", SqlDbType.VarChar)
{
Value = staffId
} //Akshay 220860
};
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
string pidm = "";
string staffName = "";
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
//worksheet.Cells[3, 1] = "Student 3";
}
else
{
//worksheet.Cells[4, 1] = "Student 4";
foreach (DataRow drr in tempTable.Rows)
{
pidm = drr["pidm"].ToString();
staffName = drr["name"].ToString();
}
}
OracleConnection OrCon = new OracleConnection(oradb);
OracleCommand cmd = new OracleCommand();
//worksheet.Cells[6, 1] = "Student 6";
cmd.CommandText = "select sirasgn_crn,sirasgn_term_code from sirasgn where sirasgn_pidm=" + pidm;
cmd.Connection = OrCon;
OrCon.Open();
///worksheet.Cells[7, 1] = "Student 7";
//DataSet dataSet = new DataSet();
//using (OracleDataAdapter dataAdapter = new OracleDataAdapter())
//{
// dataAdapter.SelectCommand = cmd;
// dataAdapter.Fill(dataSet);
//}
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//worksheet.Cells[8, 1] = "Student 8";
while (dr.Read())
{
AttendanceCrnTerm list = new AttendanceCrnTerm();
list.crn = dr["sirasgn_crn"].ToString();
list.termCode = dr["sirasgn_term_code"].ToString();
list.staffName = staffName;
crnTermColl.Add(list);
//worksheet.Cells[9, 1] = "Student 9";
}
}
OrCon.Close();
OrCon.Dispose();
//AttendanceCrnTerm list = new AttendanceCrnTerm();
//list.crn = "10050";
//list.termCode = "201710";
//crnTermColl.Add(list);
return(crnTermColl);
}
public static List <MissingStudent> GetAllStudents(string query)
{
Files.dbConnection con = new Files.dbConnection();
List <MissingStudent> studentCollection = new List <MissingStudent>();
#region SQL
DataTable tempTable = null;
string sclearsql = string.Concat(@"[dbo].[sp_getMissingStudentsDemo]");
SqlParameter[] parameter =
{
new SqlParameter("@query", SqlDbType.VarChar)
{
Value = query
}
};
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
}
else
{
foreach (DataRow dr in tempTable.Rows)
{
MissingStudent st = new MissingStudent();
st.crn = dr["crn"].ToString();
st.termCode = dr["termCode"].ToString();
st.heatId = dr["heatId"].ToString();
st.bannerId = dr["bannerId"].ToString();
st.login = dr["login"].ToString();
st.studentName = dr["studentName"].ToString();
st.ticketRefNumber = dr["ticketRefNumber"].ToString();
st.mobilePersonal = dr["mobilePersonal"].ToString();
//string mag = dr["Magcode"].ToString();
st.Magcode = dr["Magcode"].ToString();
//st.image = "http://latasha.com.au/wp-content/uploads/online-profile-picture-45x45.jpg";
if (dr["Magcode"].ToString() == "")
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + "NoImage" + ".jpg";
//st.image = @"\\webapp01d-doc\sam\11Data\NoImage1.jpg";
}
else
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + dr["bannerId"].ToString() + ".jpg";
}
//st.image = @"\\SMSODBPWV01\photos$\PROD\"+ dr["bannerId"].ToString() +".jpg";
st.checkBox = false;
//st.Tick1= @"\\webapp01d-doc\C$\inetpub\wwwroot\SAM\11Data\Tick1Copy.jpg";
st.Tick1 = "//webapp01d-doc/sam/11Data/Tick1Copy.jpg";
studentCollection.Add(st);
}
}
#endregion
#region Oracle
//OracleConnection OrCon = new OracleConnection(oradb);
//OracleCommand cmd = new OracleCommand();
//cmd.CommandText = "select sirasgn_crn,sirasgn_term_code from sirasgn where sirasgn_pidm=" + pidm;
//cmd.Connection = OrCon;
//OrCon.Open();
//
//OracleDataReader dr = cmd.ExecuteReader();
//if (dr.HasRows)
//{
// while (dr.Read())
// {
// //AttendanceCrnTerm list = new AttendanceCrnTerm();
// //list.crn = dr["sirasgn_crn"].ToString();
// //list.termCode = dr["sirasgn_term_code"].ToString();
// //crnTermColl.Add(list);
// }
//}
//
//OrCon.Close();
// OrCon.Dispose();
#endregion
//return model;
return(studentCollection);
}
public static string SaveApplication(StudentApplicationData[] ed)
{
//HeatData hd = new HeatData();
Files.dbConnection con = new Files.dbConnection();
//DateTime birthdate = DateTime.ParseExact(ed[0].birthdate.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
string sclearsql = string.Concat("[dbo].[sp_createApplication]");
SqlParameter[] parameter =
{
new SqlParameter("@Title", SqlDbType.VarChar)
{
Value = ed[0].vrt_title
},
new SqlParameter("@firstName", SqlDbType.VarChar)
{
Value = ed[0].firstName
},
new SqlParameter("@lastname", SqlDbType.VarChar)
{
Value = ed[0].lastname
},
new SqlParameter("@Email", SqlDbType.VarChar)
{
Value = ed[0].emailaddress1
},
new SqlParameter("@IsEmailValid", SqlDbType.VarChar)
{
Value = string.IsNullOrEmpty(ed[0].isEmailValid) ? "False" : ed[0].isEmailValid
}, //{ Value =ed[0].isEmailValid },
new SqlParameter("@mobilephone", SqlDbType.VarChar)
{
Value = ed[0].mobilephone
},
new SqlParameter("@IsNumberValid", SqlDbType.VarChar)
{
Value = string.IsNullOrEmpty(ed[0].isNumberValid) ? "False" : ed[0].isNumberValid
}, //ed[0].isNumberValid },
new SqlParameter("@birthdate", SqlDbType.VarChar)
{
Value = ed[0].birthdate
},
new SqlParameter("@Gender", SqlDbType.VarChar)
{
Value = ed[0].studentGender
},
new SqlParameter("@Postcode", SqlDbType.VarChar)
{
Value = ed[0].address1_postalcode
},
new SqlParameter("@ReasonToChooseBKIID", SqlDbType.VarChar)
{
Value = ed[0].vrt_whatbroughtyoutothekanganinstitutewebsite.reasonToChooseBKIID
},
new SqlParameter("@studiedHereBefore", SqlDbType.VarChar)
{
Value = ed[0].vrt_studiedatkanganinstitutebendigotafebefore
},
new SqlParameter("@prevStudentNumber", SqlDbType.VarChar)
{
Value = ed[0].vrt_kibtstudentidnumber
},
//new SqlParameter("@ResidencyID", SqlDbType.VarChar) { Value =ed[0].vrt_CountryofResidence.countryID },
new SqlParameter("@ResidencyID", SqlDbType.VarChar)
{
Value = ed[0].vrt_australiancitizenshipresidency.residencyId
},
new SqlParameter("@IndigenousStatusID", SqlDbType.VarChar)
{
Value = ed[0].vrt_aboriginalortorresstraitislander.statusId
},
new SqlParameter("@CompletedQualification", SqlDbType.VarChar)
{
Value = ed[0].vrt_successfullycompletedqualifications
},
new SqlParameter("@HasUSI", SqlDbType.VarChar)
{
Value = ed[0].hasUSI
},
new SqlParameter("@USI", SqlDbType.VarChar)
{
Value = ed[0].vrt_uniquestudentidentifier
},
new SqlParameter("@VerifyUSI", SqlDbType.VarChar)
{
Value = ed[0].vrt_permissiontocheckfororcreateausi
},
new SqlParameter("@ApplyUSI", SqlDbType.VarChar)
{
Value = ed[0].applyUSI
},
new SqlParameter("@StreetNumber", SqlDbType.VarChar)
{
Value = ed[0].streetName
},
new SqlParameter("@StreetName", SqlDbType.VarChar)
{
Value = ed[0].streetNumber
},
new SqlParameter("@CityOfResidence", SqlDbType.VarChar)
{
Value = ed[0].city
},
new SqlParameter("@StateOfResidenceId", SqlDbType.VarChar)
{
Value = ed[0].state.stateID
},
new SqlParameter("@CityOfBirth", SqlDbType.VarChar)
{
Value = ed[0].vrt_CityorTownofBirth
},
new SqlParameter("@IsAddressValid", SqlDbType.VarChar)
{
Value = string.IsNullOrEmpty(ed[0].isAddressValid) ? "True" : ed[0].isAddressValid
},
new SqlParameter("@CountryOfBirthID", SqlDbType.VarChar)
{
Value = ed[0].vrt_CountryofBirth.countryID
},
new SqlParameter("@CountryOfResidenceID", SqlDbType.VarChar)
{
Value = ed[0].vrt_CountryofResidence.countryID
},
new SqlParameter("@ProofTypeID", SqlDbType.VarChar)
{
Value = ed[0].idProof.proofId
},
new SqlParameter("@ProofNumber", SqlDbType.VarChar)
{
Value = ed[0].idProofText
},
new SqlParameter("@PartialSave", SqlDbType.VarChar)
{
Value = ed[0].partialSave
},
//new SqlParameter("@CourseCampusID", SqlDbType.VarChar) { Value = ed[0].courseNCampus.courseCampusID },
new SqlParameter("@LastModifiedBy", SqlDbType.VarChar)
{
Value = "svc_StudentHub"
},
new SqlParameter("@CreatedBy", SqlDbType.VarChar)
{
Value = "svc_StudentHub"
}
};
DataTable tempTable = null;
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
string a = tempTable.Rows[0][0].ToString();
}
if ((ed[0].txtQualification.Count > 0) || (ed[0].txtQualification != null))
{
string qualificationInsertQry = string.Concat("[dbo].[sp_createApplicationQualifications]");
foreach (Qualification qual in ed[0].txtQualification)
{
//qualification: "", selected: false, internalName: "", qualificationID: 0
if (qual.qualificationID != 0)
{
SqlParameter[] qualificationInsertQryParameter =
{
new SqlParameter("@applicationID", SqlDbType.VarChar)
{
Value = tempTable.Rows[0][0]
},
new SqlParameter("@qualificationCode", SqlDbType.VarChar)
{
Value = qual.qualificationID
}
};
DataTable qualTable = null;
qualTable = con.executeSelectQueryWithSP(qualificationInsertQry, qualificationInsertQryParameter);
}
}
}
if ((ed[0].courseNCampus.Count > 0) || (ed[0].courseNCampus != null))
{
string ccInsertQry = string.Concat("[dbo].[sp_createApplicationCourseCampus]");
foreach (CourseNCampus cc in ed[0].courseNCampus)
{
if (cc.courseCampusID != 0)// || cc.vrt_course != 0 || cc.txtCampus != 0 ||)
{
SqlParameter[] qualificationInsertQryParameter =
{
new SqlParameter("@applicationID", SqlDbType.VarChar)
{
Value = tempTable.Rows[0][0]
},
new SqlParameter("@courseCampusID", SqlDbType.VarChar)
{
Value = Convert.ToInt32(cc.courseCampusID)
},
//new SqlParameter("@CourseValueID", SqlDbType.VarChar) { Value =Convert.ToInt32(cc.vrt_course)},
//new SqlParameter("@CampusValueID", SqlDbType.VarChar) { Value =cc.txtCampus}
};
DataTable ccTable = null;
ccTable = con.executeSelectQueryWithSP(ccInsertQry, qualificationInsertQryParameter);
}
}
}
if (Convert.ToInt32(ed[0].partialSave.ToString()) == 1)
{
try
{
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("mail-relay.kbtm.kangan.edu.au");
mail.From = new MailAddress(WebConfigurationManager.AppSettings["FromEmail"]);
mail.To.Add(WebConfigurationManager.AppSettings["FromEmail"]);
mail.Subject = "Complete Bendigo Kangan Course Application";
string url = WebConfigurationManager.AppSettings["Application"] + "?applicationId=" + tempTable.Rows[0][0].ToString() +
"&date=" + DateTime.Now.ToString("dd'/'MM'/'yyyy");
mail.Body = @" The URL is:- " + "\n" + url;
SmtpServer.Port = 25;
SmtpServer.Send(mail);
}
catch (Exception ex)
{
}
}
string returnValue = "none";
try
{
//hd.heatId = res.Message;
//hd.message = "Ticket Created";
}
catch (Exception e)
{
returnValue = e.Message;
throw e;
}
return(tempTable.Rows[0][0].ToString());
}
public static List <ResultingData> GetResulting(string crn, string termCode)
{
Files.dbConnection con = new Files.dbConnection();
//List<Student> studentCollection = new List<Student>(100000);
List <ResultingData> studentCollection = new List <ResultingData>();
//var model = null;
DataTable tempTable = null;
string sclearsql = string.Concat(@"[dbo].[sp_getAttendanceResulting]");
//tempTable = con.executeSelectNoParameter(sclearsql);
SqlParameter[] parameter =
{
new SqlParameter("@crn", SqlDbType.VarChar)
{
Value = crn
},
new SqlParameter("@termCode", SqlDbType.VarChar)
{
Value = termCode
},
};
tempTable = con.executeSelectQueryWithSP(sclearsql, parameter);
if ((tempTable == null) || (tempTable.Rows.Count == 0))
{
}
else
{
foreach (DataRow dr in tempTable.Rows)
{
ResultingData st = new ResultingData();
st.crn = dr["crn"].ToString();
st.termCode = dr["termCode"].ToString();
st.bannerId = dr["bannerId"].ToString();
st.login = dr["login"].ToString();
st.studentName = dr["studentName"].ToString();
st.minDate = dr["minDate"].ToString();
st.maxDate = dr["maxDate"].ToString();
st.attendancePercent = dr["attendancePercent"].ToString();
st.openCalendar = false;
string[] values = dr["disabledDates"].ToString().Split(',');
if (values.Length > 0)
{
List <string> sublist = new List <string>();
foreach (string s in values)
{
sublist.Add(s);
}
st.disabledDates = sublist;
}
else
{
}
st.Magcode = dr["Magcode"].ToString();
if (dr["Magcode"].ToString() == "")
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + "NoImage" + ".jpg";
}
else
{
st.image = @"\\SMSODBPWV01\photos$\PROD\" + dr["bannerId"].ToString() + ".jpg";
}
studentCollection.Add(st);
}
}
//return model;
return(studentCollection);
}