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