public static List<ReportResponseModelWithContacts> GetAttendanceReportWithContacts(ReportRequestModel requestModel) { DataSet ds = new DataSet("TimeRanges"); var reportResponseModel = new ReportResponseModel(); var reportResponseModelWithContactsList = new List<ReportResponseModelWithContacts>(); //var reportResponseModelWithContacts = new ReportResponseModelWithContacts(); using (var con = new SqlConnection(ConnectionString)) { con.Open(); var sqlComm = new SqlCommand("GetAttendanceReportWithContacts", con) { CommandType = CommandType.StoredProcedure }; sqlComm.Parameters.AddWithValue("@name", requestModel.Name); sqlComm.Parameters.AddWithValue("@class", requestModel.StudentClass); sqlComm.Parameters.AddWithValue("@section", requestModel.Section); sqlComm.Parameters.AddWithValue("@fromDate", requestModel.DtFrom); sqlComm.Parameters.AddWithValue("@toDate", requestModel.DtTo); sqlComm.Parameters.AddWithValue("@category", requestModel.Category); SqlDataAdapter da = new SqlDataAdapter { SelectCommand = sqlComm }; da.Fill(ds); //sqlComm.ExecuteReader(); sqlComm.ExecuteNonQuery(); con.Close(); if (ds.Tables.Count > 0) { reportResponseModel.Rows = new List<Row>(); foreach (DataRow dataRow in ds.Tables[0].Rows) { var reportResponseModelWithContacts = new ReportResponseModelWithContacts { Name = dataRow[0].ToString(), Class = dataRow[1].ToString(), Section = dataRow[2].ToString(), Gender = dataRow[3].ToString(), RFID = dataRow[4].ToString(), AttendanceDate = dataRow[5].ToString(), InTime = dataRow[6].ToString(), OutTime = dataRow[7].ToString(), Duration = dataRow[8].ToString(), UserName = dataRow[9].ToString(), PhoneNumber = dataRow[10].ToString() }; reportResponseModelWithContactsList.Add(reportResponseModelWithContacts); } } } return reportResponseModelWithContactsList; }
public static ReportResponseModel GetAttendanceReport(ReportRequestModel requestModel) { DataSet ds = new DataSet("TimeRanges"); var reportResponseModel = new ReportResponseModel(); using (var con = new SqlConnection(ConnectionString)) { con.Open(); var sqlComm = new SqlCommand("GetAttendanceReport", con) { CommandType = CommandType.StoredProcedure }; sqlComm.Parameters.AddWithValue("@name", requestModel.Name); sqlComm.Parameters.AddWithValue("@class", requestModel.StudentClass); sqlComm.Parameters.AddWithValue("@section", requestModel.Section); sqlComm.Parameters.AddWithValue("@fromDate", requestModel.DtFrom); sqlComm.Parameters.AddWithValue("@toDate", requestModel.DtTo); sqlComm.Parameters.AddWithValue("@category", requestModel.Category); SqlDataAdapter da = new SqlDataAdapter {SelectCommand = sqlComm}; da.Fill(ds); //sqlComm.ExecuteReader(); sqlComm.ExecuteNonQuery(); con.Close(); if (ds.Tables.Count > 0) { reportResponseModel.Columns = new List<string>(); foreach (DataColumn column in ds.Tables[0].Columns) { reportResponseModel.Columns.Add(column.ColumnName); } reportResponseModel.Rows = new List<Row>(); foreach (DataRow dataRow in ds.Tables[0].Rows) { var row = new Row(); row.RowCells = new List<string>(); foreach (var cells in dataRow.ItemArray) { row.RowCells.Add(cells.ToString()); } reportResponseModel.Rows.Add(row); } } } return reportResponseModel; }
public JsonResult GetReport(ReportRequestModel reportRequestModel) { var reportResponseModel = ReportService.GetAttendanceReport(reportRequestModel); //TODO pass ORgID to db var sessionUserName = User.Identity.GetUserName(); var filteredRows = new List<Row>(); if (User.IsInRole("Student") || ((User.IsInRole("Staff") && reportRequestModel.Category == "Staff"))) { //List<Row> filteredRows = (from row in reportResponseModel.Rows let rowUserName = row.RowCells[row.RowCells.Count - 1] where !String.IsNullOrEmpty(rowUserName) && !String.IsNullOrEmpty(sessionUserName) && (rowUserName.ToLower() == sessionUserName.ToLower()) select row).ToList(); foreach (var row in reportResponseModel.Rows) { var rowUserName = row.RowCells[row.RowCells.Count - 1]; if (!String.IsNullOrEmpty(rowUserName) && !String.IsNullOrEmpty(sessionUserName) && (rowUserName.ToLower() == sessionUserName.ToLower())) { filteredRows.Add(row); } } reportResponseModel.Rows = filteredRows; } return new JsonResult { Data = reportResponseModel, JsonRequestBehavior = JsonRequestBehavior.AllowGet }; }
public ActionResult Index() { //TODO check if request.queryString.count > 1 in any scenario if (Request.QueryString.Count > 0) { //sample URL http://localhost:62206/ams?$99999&99&5780786&15112015114300,5780786&15112015184800 //sample URL http://localhost:62206/ams?$99999&OutMessages //Date format is ddmmyyyy string queryString = Request.QueryString[0].ToString(CultureInfo.InvariantCulture); if (queryString.Contains("OutMessages")) { string[] qsParameters = queryString.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); string orgId = RemoveSpecialChars(qsParameters[0]); DateTime outReportDate = DateTime.Today; var reportRequestModel = new ReportRequestModel { DtFrom = outReportDate.AddDays(-2), DtTo = outReportDate.AddDays(-2) }; var reportResponseModelWithContactsList = ReportService.GetAttendanceReportWithContacts(reportRequestModel); foreach (var reportResponseModelWithContacts in reportResponseModelWithContactsList) { var phoneNumber = reportResponseModelWithContacts.PhoneNumber; //Sending SMS using Bulk Service if (!String.IsNullOrEmpty(phoneNumber)) { var smsResponseModel = SendSmsService.SendSmsDailyReport(reportResponseModelWithContacts); AttendanceService.InsertSmsResponse(smsResponseModel); } } } else { string[] qsParameters = queryString.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); //TODO check max how many sub-requests allowed in one request - change to list if (qsParameters.Length >= 4) { string orgId = RemoveSpecialChars(qsParameters[0]); string machineId = qsParameters[1]; var requestModelList = new List<AttendanceRequestModel>(); for (int index = 2; index < qsParameters.Length; index++) { string rfId = qsParameters[index].Trim(); index++; string dtAttendance = RemoveSpecialChars(qsParameters[index]); DateTime dateTimeAttendance = DateTime.ParseExact(dtAttendance, "ddMMyyyyHHmmss", CultureInfo.InvariantCulture); var requestModel = new AttendanceRequestModel { OrgId = orgId, MachineId = machineId, RfId = rfId, DtAttendance = dateTimeAttendance }; requestModelList.Add(requestModel); } foreach (var requestModel in requestModelList) { /*if (!String.IsNullOrEmpty(orgId) && !String.IsNullOrEmpty(machineId) && requestModel.RfId != null && !String.IsNullOrEmpty(requestModel.RfId) && requestModel.RfId.Length > 0 && requestModel.RfId.Length <= 16 && requestModel.DtAttendance != null)//TODO check default value of datetime.parseexact*/ //Insert student attendance record and get the student details to send the SMS var attendanceResponseModel = AttendanceService.InsertOrUpdateAttendanceRecord(requestModel); //Sending SMS using Bulk Service if (!String.IsNullOrEmpty(attendanceResponseModel.PhoneNumber) && attendanceResponseModel.IsInTime) { var smsResponseModel = SendSmsService.SendSms(attendanceResponseModel, requestModel.DtAttendance); AttendanceService.InsertSmsResponse(smsResponseModel); } //query To get the value from table tblregistration //string selectquery = "Select * from tblregistration where UserId='" + rfId + "' "; // TODO ==> Identify In and Out Timestamps. as of now, morning 6 AM to 10 AM ==> IN Time , evening 3 to 6 ==> OUT Time // TODO ==> think of correct data model to maintain this data // TODO ==> fix RFID datatype in database.10 digits. // TODO ==> Make sure RFID is assigned to every student during registration. registration page of UI. if (requestModel.RfId != null) { //For Successfull Insertion Of Data Into database We are giving response To the device //return View(requestModel); Response.Write("$RFID=0#"); } } } } } return View(); }