public static string PostResulting(List <ResultingData> resultingPostData) { Files.dbConnection con = new Files.dbConnection(); DataTable tempTable = null; string sclearsql = string.Concat(@"[dbo].[sp_postAttendanceResulting]"); SqlParameter[] parameter = { new SqlParameter("@crn", SqlDbType.VarChar) { Value = "" } }; //tempTable = con.executeSelectQueryWithSP(sclearsql, parameter); // // //if ((tempTable == null) || (tempTable.Rows.Count == 0)) //{ //} //else //{ //} return("OK"); }
public static List <Campus> GetCampuses() { List <Campus> campuses = new List <Campus>(); Files.dbConnection con = new Files.dbConnection(); //[dbo].[sp_getCourses] string sqlCampuses = string.Concat("[dbo].[sp_getAllCampus]"); DataTable tempTable = null; tempTable = con.executeSelectNoParameter(sqlCampuses); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempTable == null) || (tempTable.Rows.Count == 0)) { //string a = tempTable.Rows[0][0].ToString(); } else { foreach (DataRow drr in tempTable.Rows) { Campus campus = new Campus(); campus.campusID = Convert.ToInt32(drr["CampusID"].ToString()); campus.campusName = drr["Campus"].ToString(); campuses.Add(campus); } } return(campuses); }
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 <Student> GetStudentsOnLoad() { ////////////////////////////////////////////////////// ////////////////////////////////////////////////////// // DO NOT WORK FOR MORE THAN 300 - 400 RECORDS // Failed under 500 records ////////////////////////////////////////////////////// ////////////////////////////////////////////////////// Files.dbConnection con = new Files.dbConnection(); //List<Student> studentCollection = new List<Student>(100000); List <Student> studentCollection = new List <Student>(); //var model = null; DataTable tempTable = null; string sclearsql = string.Concat(@"[dbo].[sp_getAllStudents]"); tempTable = con.executeSelectNoParameter(sclearsql); if ((tempTable == null) || (tempTable.Rows.Count == 0)) { } else { foreach (DataRow dr in tempTable.Rows) { Student st = new Student(); st.Login = dr["Login"].ToString(); st.Title = dr["Title"].ToString(); st.Initials = dr["Initials"].ToString(); st.FirstName = dr["FirstName"].ToString(); st.LastName = dr["LastName"].ToString(); st.PreferredName = dr["PreferredName"].ToString(); st.EmailInstitute = dr["EmailInstitute"].ToString(); st.EnrolmentDate = dr["EnrolmentDate"].ToString(); st.Gender = dr["Gender"].ToString(); st.DateOfBirth = dr["DateOfBirth"].ToString(); st.EmailPersonal = dr["EmailPersonal"].ToString(); st.TelephonePersonal = dr["TelephonePersonal"].ToString(); st.MobilePersonal = dr["MobilePersonal"].ToString(); st.USI = dr["USI"].ToString(); studentCollection.Add(st); } } var model = new List <Student>() { new Student { FirstName = "vaibhav", LastName = "sharma" }, new Student { FirstName = "Van", LastName = "phan" }, new Student { FirstName = "Akshay", LastName = "narang" } }; //return model; 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 List <HeatData> CreateTicket(string staffNumber, string crn, string termCode) { List <HeatData> heatData = new List <HeatData>(); string sentToEmail = WebConfigurationManager.AppSettings["ToEmail"]; try { UtilResultObject res = new UtilResultObject(); StudentAttendanceClient response = new StudentAttendanceClient(); Files.dbConnection con = new Files.dbConnection(); // 218720 VP // 22178 VS string sclearsql = string.Concat("SELECT * FROM [IntHub].[dbo].[StaffBKI] " + "WHERE BarCode = @staffNumber" + " and isActive = 1 and StaffType in ('Payroll BT','Payroll KI')" ); SqlParameter[] parameter = { new SqlParameter("@staffNumber", SqlDbType.VarChar) { Value = staffNumber } }; DataTable tempTable = null; tempTable = con.executeSelectQuery(sclearsql, parameter); string emailTofromDB = WebConfigurationManager.AppSettings["ToEmail"]; if ((tempTable == null) || (tempTable.Rows.Count == 0)) { emailTofromDB = WebConfigurationManager.AppSettings["ToEmailLogin"]; } else { foreach (DataRow dr in tempTable.Rows) { emailTofromDB = dr["Login"].ToString(); sentToEmail = dr["EmailInstitute"].ToString(); } } res = response.CreateHEATTicket(WebConfigurationManager.AppSettings["webServiceUsername"], WebConfigurationManager.AppSettings["webServicePassword"], emailTofromDB, staffNumber, crn, termCode); if (res.Status) { try { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("mail-relay.kbtm.kangan.edu.au"); mail.From = new MailAddress(WebConfigurationManager.AppSettings["FromEmail"]); //Files.dbConnection con = new Files.dbConnection(); // 218720 VP // 22178 VS //string sclearsql = string.Concat("SELECT * FROM [IntHub].[dbo].[StaffBKI] " + // "WHERE BarCode = @staffNumber" + // " and isActive = 1 and StaffType in ('Payroll BT','Payroll KI')" // ); //SqlParameter[] parameter = { // new SqlParameter("@staffNumber", SqlDbType.VarChar) { Value =staffNumber } // }; //DataTable tempTable = null; //tempTable = con.executeSelectQuery(sclearsql, parameter); //if ((tempTable == null) || (tempTable.Rows.Count == 0)) //{ // mail.To.Add(WebConfigurationManager.AppSettings["ToEmail"]); //} //else //{ // foreach (DataRow dr in tempTable.Rows) // { // //dr["EmailInstitute"].ToString(); // mail.To.Add(dr["EmailInstitute"].ToString()); // sentToEmail = dr["EmailInstitute"].ToString(); // } //} //where BarCode = CONVERT(varchar(50), 218720 ) mail.To.Add(sentToEmail); mail.Subject = "Student Attendance Missing"; string heatCloseUrl = WebConfigurationManager.AppSettings["AttendanceSubmission"]; //string heatCloseUrl = WebConfigurationManager.AppSettings["HeatTicketClose"]; string url = heatCloseUrl + "?heatId=" + res.Message + "&teacherId=" + staffNumber + "&crn=" + crn + "&termCode=" + termCode + "&classDate=" + DateTime.Now.ToString("dd'/'MM'/'yyyy"); mail.Body = @"Heat Ticket is created." + " The id is:- " + res.Message + "\n" + url; SmtpServer.Port = 25; //SmtpServer.Send(mail); HeatData hd = new HeatData(); hd.heatId = res.Message; hd.message = res.Status.ToString(); hd.sentToEmail = sentToEmail; hd.emailTofromDB = emailTofromDB; heatData.Add(hd); return(heatData); } catch (Exception ex) { return(heatData); throw ex; //MessageBox.Show(ex.ToString()); } } } catch (Exception) { throw; } //return message; return(heatData); }
public static StudentApplicationDataLookup GetApplicationAllLookups() { HttpResponse response = HttpContext.Current.Response; //response.AppendHeader("Access-Control-Allow-Origin", "*"); //response.AppendHeader("Access-Control-Allow-Methods", "*"); //headers.set('Access-Control-Allow-Headers', 'true'); //headers.set('Access-Control-Allow-Methods','GET, POST, PATCH, PUT, DELETE, OPTIONS'); //headers.set('Access-Control-Allow-Credentials', 'Origin, Content-Type, X-Auth-Token'); Files.dbConnection con = new Files.dbConnection(); StudentApplicationDataLookup lkp = new StudentApplicationDataLookup(); try { #region Course List List <Course> courses = new List <Course>(); string sqlCourses = string.Concat("[dbo].[sp_getCourses]"); DataTable tempTableCourse = null; tempTableCourse = con.executeSelectNoParameter(sqlCourses); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempTableCourse != null) || (tempTableCourse.Rows.Count >= 0)) { foreach (DataRow drr in tempTableCourse.Rows) { Course course = new Course(); course.CourseID = Convert.ToInt32(drr["CourseID"]); course.CourseName = drr["Course"].ToString(); course.CourseCode = drr["CourseCode"].ToString(); courses.Add(course); } } lkp.course = courses; #endregion #region Get Campus List <Campus> campuses = new List <Campus>(); string sqlCampuses = string.Concat("[dbo].[sp_getAllCampus]"); DataTable tempTableCampus = null; tempTableCampus = con.executeSelectNoParameter(sqlCampuses); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempTableCampus != null) || (tempTableCampus.Rows.Count >= 0)) { foreach (DataRow drr in tempTableCampus.Rows) { Campus campus = new Campus(); campus.campusID = Convert.ToInt32(drr["CampusID"].ToString()); campus.campusName = drr["Campus"].ToString(); campuses.Add(campus); } } lkp.campus = campuses; #endregion #region Get CourseCampus List <CourseCampus> ccCollection = new List <CourseCampus>(); string sqlCC = string.Concat("[dbo].[sp_getCourseCampus]"); DataTable tempTableCC = null; tempTableCC = con.executeSelectNoParameter(sqlCC); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempTableCC != null) || (tempTableCC.Rows.Count >= 0)) { foreach (DataRow drr in tempTableCC.Rows) { CourseCampus cc = new CourseCampus(); cc.campusID = Convert.ToInt32(drr["CampusID"].ToString()); cc.courseID = Convert.ToInt32(drr["CourseId"].ToString()); cc.courseCampusID = Convert.ToInt32(drr["CourseCampusID"].ToString()); cc.campusName = drr["Campus"].ToString(); ccCollection.Add(cc); } } lkp.courseCampus = ccCollection; #endregion #region Get Country List <Country> countries = new List <Country>(); string sqlCountry = string.Concat("[dbo].[sp_getCountry]"); DataTable tempTableCountry = null; tempTableCountry = con.executeSelectNoParameter(sqlCountry); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempTableCountry != null) || (tempTableCountry.Rows.Count >= 0)) { foreach (DataRow drr in tempTableCountry.Rows) { Country country = new Country(); country.countryID = Convert.ToInt32(drr["CountryID"].ToString()); country.country = drr["Country"].ToString(); countries.Add(country); } } lkp.country = countries; #endregion #region Get Residency List <Residency> residencyCollection = new List <Residency>(); string sqlResidency = string.Concat("[dbo].[sp_getResidency]"); DataTable tempResidency = null; tempResidency = con.executeSelectNoParameter(sqlResidency); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempResidency != null) || (tempResidency.Rows.Count >= 0)) { foreach (DataRow drr in tempResidency.Rows) { Residency residency = new Residency(); residency.residencyId = Convert.ToInt32(drr["ResidencyId"].ToString()); residency.vrt_australiancitizenshipresidency = drr["Residency"].ToString(); residency.type = drr["Type"].ToString(); residencyCollection.Add(residency); } } lkp.vrt_australiancitizenshipresidency = residencyCollection; #endregion #region Get Aboriginal List <Aboriginal> aboriginalCollection = new List <Aboriginal>(); string sqlAboriginal = string.Concat("[dbo].[sp_getIndigenousAustralians]"); DataTable tempAboriginal = null; tempAboriginal = con.executeSelectNoParameter(sqlAboriginal); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempAboriginal != null) || (tempAboriginal.Rows.Count >= 0)) { foreach (DataRow drr in tempAboriginal.Rows) { Aboriginal aboriginal = new Aboriginal(); aboriginal.statusId = Convert.ToInt32(drr["StatusId"].ToString()); aboriginal.vrt_aboriginalortorresstraitislander = drr["Status"].ToString(); aboriginalCollection.Add(aboriginal); } } lkp.vrt_aboriginalortorresstraitislander = aboriginalCollection; #endregion #region Get Qualification List <Qualification> qualificationCollection = new List <Qualification>(); string sqlQualification = string.Concat("[dbo].[sp_getQualification]"); DataTable tempsqlQualification = null; tempsqlQualification = con.executeSelectNoParameter(sqlQualification); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempsqlQualification != null) || (tempsqlQualification.Rows.Count >= 0)) { foreach (DataRow drr in tempsqlQualification.Rows) { Qualification qualification = new Qualification(); qualification.qualificationID = Convert.ToInt32(drr["QualificationID"].ToString()); qualification.qualification = drr["Qualification"].ToString(); qualification.internalName = drr["internalName"].ToString(); qualification.selected = false; qualification.qualificationCode = drr["qualificationCode"].ToString() ?? ""; qualificationCollection.Add(qualification); } } lkp.txtQualification = qualificationCollection; #endregion #region Get State List <State> stateCollection = new List <State>(); string sqlState = string.Concat("[dbo].[sp_getState]"); DataTable tempsqlState = null; tempsqlState = con.executeSelectNoParameter(sqlState); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempsqlState != null) || (tempsqlState.Rows.Count >= 0)) { foreach (DataRow drr in tempsqlState.Rows) { State state = new State(); state.stateID = Convert.ToInt32(drr["StateID"].ToString()); state.state = drr["State"].ToString(); stateCollection.Add(state); } } lkp.state = stateCollection; #endregion #region Get IdProof List <IdProof> idProofCollection = new List <IdProof>(); string idProofState = string.Concat("[dbo].[sp_getIdentityProof]"); DataTable tempsqlIdProof = null; tempsqlIdProof = con.executeSelectNoParameter(idProofState); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempsqlIdProof != null) || (tempsqlIdProof.Rows.Count >= 0)) { foreach (DataRow drr in tempsqlIdProof.Rows) { IdProof idProof = new IdProof(); idProof.proofId = Convert.ToInt32(drr["ProofId"].ToString()); idProof.idProofName = drr["IdentityProof"].ToString(); idProof.type = drr["Type"].ToString(); idProof.internalName = drr["InternalName"].ToString(); idProofCollection.Add(idProof); } } lkp.idProof = idProofCollection; #endregion #region Get WhatBroughtYouHere List <WhatBroughtYouHere> whatBroughtYouHereCollection = new List <WhatBroughtYouHere>(); string sqlwhatBroughtYouHereCollection = string.Concat("[dbo].[sp_getReasonToChooseBKI]"); DataTable tempsqlWhatBroughtYouHere = null; tempsqlWhatBroughtYouHere = con.executeSelectNoParameter(sqlwhatBroughtYouHereCollection); //con.executeSelectQueryWithSP(sclearsql, parameter); if ((tempsqlWhatBroughtYouHere != null) || (tempsqlWhatBroughtYouHere.Rows.Count >= 0)) { foreach (DataRow drr in tempsqlWhatBroughtYouHere.Rows) { WhatBroughtYouHere state = new WhatBroughtYouHere(); state.reasonToChooseBKIID = Convert.ToInt32(drr["ReasonToChooseBKIID"].ToString()); state.vrt_whatbroughtyoutothekanganinstitutewebsite = drr["ReasonToChooseBKI"].ToString(); whatBroughtYouHereCollection.Add(state); } } lkp.whatBroughtYouHere = whatBroughtYouHereCollection; #endregion } catch (Exception e) { } return(lkp); }
public static StudentApplicationData GetFormData(string applicationId) //string applicationId { Files.dbConnection con = new Files.dbConnection(); StudentApplicationData applicationDetails = new StudentApplicationData(); try { string sql_getApplicationDetails = string.Concat("[dbo].[sp_getApplicationDetails]"); DataTable tempTable_getApplicationDetails = null; SqlParameter[] parameter = { new SqlParameter("@applicationId", SqlDbType.VarChar) { Value = applicationId } }; tempTable_getApplicationDetails = con.executeSelectQuery(sql_getApplicationDetails, parameter); if ((tempTable_getApplicationDetails != null) || (tempTable_getApplicationDetails.Rows.Count >= 0)) { foreach (DataRow drr in tempTable_getApplicationDetails.Rows) { applicationDetails.vrt_title = drr["Title"].ToString(); applicationDetails.firstName = drr["FirstName"].ToString(); applicationDetails.lastname = drr["Lastname"].ToString(); applicationDetails.emailaddress1 = drr["Email"].ToString(); applicationDetails.isEmailValid = drr["IsEmailValid"].ToString(); applicationDetails.mobilephone = drr["MobilePhone"].ToString(); applicationDetails.isNumberValid = drr["IsNumberValid"].ToString(); applicationDetails.birthdate = drr["BirthdateFormatted"].ToString();//Birthdate #region Get course and campus lookup string sql_getApplicationCourseCampus = string.Concat("[dbo].[sp_getApplicationCourseCampus]"); DataTable tempTable_getApplicationCourseCampus = null; SqlParameter[] parameterApplicationCourseCampus = { new SqlParameter("@applicationId", SqlDbType.VarChar) { Value = applicationId } }; tempTable_getApplicationCourseCampus = con.executeSelectQuery(sql_getApplicationCourseCampus, parameterApplicationCourseCampus); applicationDetails.courseNCampus = new List <CourseNCampus>(); if ((tempTable_getApplicationCourseCampus != null) && (tempTable_getApplicationCourseCampus.Rows.Count > 0)) { foreach (DataRow dr in tempTable_getApplicationCourseCampus.Rows) { CourseNCampus cc = new CourseNCampus(); cc.vrt_course = dr["CourseID"].ToString(); cc.vrt_courseCode = dr["CourseCode"].ToString(); cc.vrt_courseName = dr["Course"].ToString(); cc.txtCampus = dr["CampusID"].ToString(); cc.txtCampusName = dr["Campus"].ToString(); cc.courseCampusID = Int32.Parse(dr["CourseCampusID"].ToString()); applicationDetails.courseNCampus.Add(cc); } } else { applicationDetails.courseNCampus.Add(new CourseNCampus()); } #endregion applicationDetails.studentGender = drr["Gender"].ToString(); applicationDetails.address1_postalcode = drr["Postcode"].ToString(); #region Get Qualification Lookup string sql_getApplicationQualifications = string.Concat("[dbo].[sp_getApplicationQualifications]"); DataTable tempTable_getApplicationQualifications = null; SqlParameter[] parameterApplicationQualifications = { new SqlParameter("@applicationId", SqlDbType.VarChar) { Value = applicationId } }; tempTable_getApplicationQualifications = con.executeSelectQuery(sql_getApplicationQualifications, parameterApplicationQualifications); applicationDetails.txtQualification = new List <Qualification>(); if ((tempTable_getApplicationQualifications != null) && (tempTable_getApplicationQualifications.Rows.Count > 0)) { foreach (DataRow dr in tempTable_getApplicationQualifications.Rows) { Qualification qual = new Qualification(); qual.qualificationID = Int32.Parse(dr["QualificationID"].ToString()); qual.qualification = dr["Qualification"].ToString(); qual.internalName = dr["InternalName"].ToString(); qual.qualificationCode = dr["QualificationCode"].ToString(); qual.selected = Boolean.Parse(dr["selected"].ToString()); applicationDetails.txtQualification.Add(qual); } } #endregion applicationDetails.vrt_successfullycompletedqualifications = drr["CompletedQualification"].ToString(); applicationDetails.vrt_whatbroughtyoutothekanganinstitutewebsite = new WhatBroughtYouHere(); applicationDetails.vrt_whatbroughtyoutothekanganinstitutewebsite.vrt_whatbroughtyoutothekanganinstitutewebsite = drr["ReasonToChooseBKI"].ToString(); applicationDetails.vrt_whatbroughtyoutothekanganinstitutewebsite.reasonToChooseBKIID = Int32.Parse(drr["ReasonToChooseBKIID"].ToString()); applicationDetails.vrt_studiedatkanganinstitutebendigotafebefore = drr["StudiedHereBefore"].ToString(); applicationDetails.vrt_kibtstudentidnumber = drr["PrevStudentNumber"].ToString(); applicationDetails.vrt_australiancitizenshipresidency = new Residency(); applicationDetails.vrt_australiancitizenshipresidency.vrt_australiancitizenshipresidency = drr["Residency"].ToString(); applicationDetails.vrt_australiancitizenshipresidency.residencyId = Int32.Parse(drr["ResidencyID"].ToString()); applicationDetails.vrt_australiancitizenshipresidency.type = drr["ResidencyType"].ToString(); applicationDetails.vrt_aboriginalortorresstraitislander = new Aboriginal(); applicationDetails.vrt_aboriginalortorresstraitislander.vrt_aboriginalortorresstraitislander = drr["Status"].ToString(); applicationDetails.vrt_aboriginalortorresstraitislander.statusId = Int32.Parse(drr["IndigenousStatusID"].ToString()); applicationDetails.hasUSI = drr["HasUSI"].ToString(); applicationDetails.vrt_uniquestudentidentifier = drr["USI"].ToString(); applicationDetails.vrt_permissiontocheckfororcreateausi = drr["VerifyUSI"].ToString(); applicationDetails.applyUSI = drr["ApplyUSI"].ToString(); applicationDetails.streetNumber = drr["StreetNumber"].ToString(); applicationDetails.streetName = drr["StreetName"].ToString(); applicationDetails.city = drr["CityOfResidence"].ToString(); applicationDetails.state = new State(); applicationDetails.state.state = drr["State"].ToString(); applicationDetails.state.stateID = Int32.Parse(drr["StateOfResidenceId"].ToString()); applicationDetails.vrt_CityorTownofBirth = drr["CityOfBirth"].ToString(); applicationDetails.isAddressValid = drr["IsAddressValid"].ToString(); // Accepts True or False applicationDetails.vrt_CountryofBirth = new Country(); applicationDetails.vrt_CountryofBirth.country = drr["CountryOfBirth"].ToString(); applicationDetails.vrt_CountryofBirth.countryID = Int32.Parse(drr["CountryOfBirthID"].ToString()); applicationDetails.vrt_CountryofResidence = new Country(); applicationDetails.vrt_CountryofResidence.country = drr["CountryOfResidence"].ToString(); applicationDetails.vrt_CountryofResidence.countryID = Int32.Parse(drr["CountryOfResidenceID"].ToString()); applicationDetails.idProof = new IdProof(); applicationDetails.idProof.internalName = drr["InternalName"].ToString(); applicationDetails.idProof.type = drr["Type"].ToString(); applicationDetails.idProof.proofId = Int32.Parse(drr["ProofTypeID"].ToString()); applicationDetails.idProof.idProofName = drr["IdentityProof"].ToString(); applicationDetails.idProofText = drr["ProofNumber"].ToString(); applicationDetails.partialSave = drr["PartialSave"].ToString(); } } } catch (Exception e) { } return(applicationDetails); }
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); }
public static List <AttendanceCrnTerm> GetAllCrn(string staffId) { List <AttendanceCrnTerm> crnTermColl = new List <AttendanceCrnTerm>(); Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook worKbooK; Microsoft.Office.Interop.Excel.Worksheet worksheet; Microsoft.Office.Interop.Excel.Range celLrangE; excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; worKbooK = excel.Workbooks.Add(Type.Missing); worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worksheet.Name = "StudentRepoertCard"; worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 8]].Merge(); worksheet.Cells[1, 1] = "Student 1"; try { Files.dbConnection con = new Files.dbConnection(); worksheet.Cells[2, 1] = "Student 2"; //celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowcount, ExportToExcel().Columns.Count]]; //celLrangE.EntireColumn.AutoFit(); //Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders; //border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //border.Weight = 2d; //celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]]; //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 = ""; //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(); // } //} //worksheet.Cells[5, 1] = "Student 5"; AttendanceCrnTerm list = new AttendanceCrnTerm(); list.crn = "10050"; list.termCode = "201710"; crnTermColl.Add(list); // 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(); // crnTermColl.Add(list); // worksheet.Cells[9, 1] = "Student 9"; // } // } // // OrCon.Close(); // OrCon.Dispose(); worksheet.Cells[10, 1] = "Student 10"; } catch (Exception ex) { worksheet.Cells[11, 1] = ex.Message; throw ex; } finally { worKbooK.SaveAs("c:\\DeleteIt\\csharp-Excel" + "1" + ".xls"); //"c:\\DeleteIt\\csharp-Excel" + new DateTime().ToString() + ".xls" worKbooK.Close(); excel.Quit(); } //worKbooK.SaveAs("c:\\DeleteIt\\csharp-Excel" + "1" + ".xls"); //"c:\\DeleteIt\\csharp-Excel" + new DateTime().ToString() + ".xls" //worKbooK.Close(); //excel.Quit(); return(crnTermColl); }