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