//Load the persons affected based on the incidentid
private void loadIncidents(string reportNumber, DateTime date, byte areaID, byte locationID)
{
var model = new EDM.DataSource();
var results = (from a in model.tbl_Op_IncidentPersonsAffected
join b in model.tbl_Ref_IncidentPersonAffectedType on a.AffectedTypeID equals b.AffectedTypeID
where a.IncidentID == incidentID
select new
{
personAffectedID = a.PersonAffectedID,
fullName = a.FirstName.Length > 0 ? a.LastName + ", " + a.FirstName : a.LastName,
phoneNumber = a.PhoneNumber,
address = a.Address,
description = a.Description,
affectedType = b.TypeName == "Other" ? b.TypeName + " - " + a.OtherTypeDescription : b.TypeName,
firstName = a.FirstName,
lastName = a.LastName,
affectedTypeID = a.AffectedTypeID,
otherTypeDescription = a.OtherTypeDescription
}).OrderBy(name => name).ToList();
if (results.Count > 0)
{
gvPersonsAffected.DataSource = results;
gvPersonsAffected.DataBind();
gvPersonsAffected.Visible = true;
}
else
{
gvPersonsAffected.Visible = false;
}
}
public bool duplicateCheck_WorkSchedule(string year, string month, byte areaID, byte shiftID)
{
bool isDuplicate = false;
var model = new EDM.DataSource();
var results = (from a in model.tbl_Mgr_WorkSchedule
where a.Year == year && a.Month == month && a.AreaID == areaID && a.ShiftID == shiftID
select new
{
a.WorkScheduleID
}).ToList();
if (results.Count > 0)
{
foreach (var val in results)
{
workScheduleID = val.WorkScheduleID;
}
isDuplicate = true;
}
else
{
isDuplicate = false;
}
return(isDuplicate);
}
public void insert_Shift(byte positionID, TimeSpan fromTime, TimeSpan toTime, string shiftName, string hrsInShift, bool isActive, byte userID)
{
int shiftID = 0;
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Insert header
EDM.tbl_Ref_Shifts a = new EDM.tbl_Ref_Shifts();
{
a.PositionID = positionID;
a.FromTime = fromTime;
a.ToTime = toTime;
a.HoursInShift = hrsInShift;
a.IsActive = isActive;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Ref_Shifts.Add(a);
model.SaveChanges();
//Get the id
shiftID = a.ShiftID;
if (shiftID != 0)
{
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = shiftID;
z.TableName = "tbl_Ref_Shifts";
z.AuditProcess = "Created Shift (" + shiftName + ")";
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
else
{
successfulCommit = false;
MISC.writetoAlertLog("No Shift ID returned after insert!!");
}
}
if (shiftID != 0)
{
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
public bool duplicateCheck_ShiftReport(DateTime date, byte areaID, byte shiftID, Int16 supervisorEmployeeID)
{
bool isDuplicate = false;
var model = new EDM.DataSource();
var results = (from a in model.tbl_Sup_ShiftReport
where a.Date == date && a.AreaID == areaID && a.ShiftID == shiftID && a.SupervisorID == supervisorEmployeeID
select new
{
a.ShiftReportID
}).ToList();
if (results.Count > 0)
{
foreach (var val in results)
{
shiftReportID = val.ShiftReportID;
}
isDuplicate = true;
}
else
{
isDuplicate = false;
}
return(isDuplicate);
}
public bool shiftDuplicateCheck(string fromTime, string toTime, byte positionID)
{
bool isDuplicate = false;
TimeSpan fTime = new TimeSpan();
TimeSpan tTime = new TimeSpan();
if ((TimeSpan.TryParse(fromTime, out fTime)) && (TimeSpan.TryParse(toTime, out tTime)))
{
tTime = TimeSpan.Parse(toTime);
var model = new EDM.DataSource();
var results = (from a in model.tbl_Ref_Shifts
where a.FromTime == fTime && a.ToTime == tTime && a.PositionID == positionID
select new
{
a.ShiftID
}).ToList();
if (results.Count > 0)
{
isDuplicate = true;
}
else
{
isDuplicate = false;
}
}
else
{
ShowMessage("Incorrect Time entered. Kindly verify.", MessageType.Warning);
}
return(isDuplicate);
}
//Insert new incidents
public int insert_Incident(byte areaID, Int16 locationID, DateTime dateOccured, TimeSpan timeOccured, byte incidentTypeID, string otherTypeDescription, string description, string incidentNumber, byte userID, byte shiftID, string actionTaken)
{
int incidentID = 0;
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Insert transaction
EDM.tbl_Op_Incidents a = new EDM.tbl_Op_Incidents();
{
a.AreaID = areaID;
a.LocationID = locationID;
a.DateOccured = dateOccured;
a.TimeOccured = timeOccured;
a.IncidentTypeID = incidentTypeID;
a.OtherTypeDescription = otherTypeDescription.Length > 0 ? firstCharToUpper(otherTypeDescription) : null;
a.Description = description.Length > 0 ? firstCharToUpper(description) : null;
a.ActionTaken = actionTaken.Length > 0 ? firstCharToUpper(actionTaken) : null;
a.IncidentNumber = incidentNumber;
a.ShiftID = shiftID == 0 ? null : (byte?)shiftID;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Op_Incidents.Add(a);
model.SaveChanges();
//Get the incidentID
incidentID = (from b in model.tbl_Op_Incidents
where b.IncidentNumber == incidentNumber
select b.IncidentID
).FirstOrDefault();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = incidentID;
z.TableName = "tbl_Op_Incidents";
z.AuditProcess = "Created Incident Report - " + incidentNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
incidentID = 0;
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
return(incidentID);
}
protected bool updateEmployee(bool status, bool reset, string fname, string lname, short empId, byte loggedIN)
{
bool success = false;
var model = new EDM.DataSource();
try
{
var query = (from a in model.tbl_Sup_Employees
where (a.EmployeeID == empId)
select a).FirstOrDefault();
if (query != null)
{
query.FirstName = fname;
query.LastName = lname;
query.IsActive = status;
query.ResetNeeded = reset;
query.LastModifiedBy = loggedIN;
query.LastModifiedOn = DateTime.Now;
model.SaveChanges();
success = true;
}
}
catch (Exception exec)
{
MISC.writetoAlertLog(exec.ToString());
}
return(success);
}
//create employee link
protected bool linkEmployee(short empID, bool resetneeded, bool status, byte UID, byte loggedIN)
{
bool success = false;
var model = new EDM.DataSource();
try
{
var query = (from a in model.tbl_Sup_Employees
where (a.EmployeeID == empID) &&
(a.UID == null)
select a).FirstOrDefault();
query.UID = UID;
query.LastModifiedBy = loggedIN;
query.LastModifiedOn = DateTime.Now;
model.SaveChanges();
success = true;
}
catch (Exception exec)
{
MISC.writetoAlertLog(exec.ToString());
// ShowMessage("Something went wrong and the employee link could not be created. Kindly try again and if the error persists, refer to the error log for details.", MessageType.Error);
}
return(success);
}
//save user that is not an employee
protected bool saveNonEmployee(string firstname, string lastname, bool resetneeded, bool status, byte UID)
{
bool success = false;
using (EDM.DataSource model = new EDM.DataSource())
{
EDM.tbl_Sup_Employees a = new EDM.tbl_Sup_Employees()
{
FirstName = firstname,
LastName = lastname,
IsActive = status,
ResetNeeded = resetneeded,
UID = UID,
IsEmployee = false,
CreatedBy = MISC.getUserID(),
CreatedOn = DateTime.Now
};
model.tbl_Sup_Employees.Add(a);
try
{
model.SaveChanges();
success = true;
}
catch (Exception exec)
{
MISC.writetoAlertLog(exec.ToString());
ShowMessage("Something went wrong and the user could not be created. Kindly try again and if the error persists, refer to the error log for details.", MessageType.Error);
}
}
return(success);
}
//save audit log
protected void AuditLog(int sourceID, string tableName, string AuditProcess, byte loggedInUser)
{
//Insert AuditTrail
using (EDM.DataSource model = new EDM.DataSource())
{
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = sourceID;
z.TableName = tableName;
z.AuditProcess = AuditProcess;
z.DateTime = DateTime.Now;
z.UserID = loggedInUser;
};
model.tbl_AuditLog.Add(z);
try
{
model.SaveChanges();
//success = true;
}
catch (Exception exec)
{
MISC.writetoAlertLog(exec.ToString());
ShowMessage("Something went wrong and the auditlog could not be created. Kindly try again and if the error persists, refer to the error log for details.", MessageType.Error);
}
}
}
public bool duplicateCheck(DateTime date, byte shiftID)
{
bool isDuplicate = false;
var model = new EDM.DataSource();
var results = (from a in model.tbl_Sup_DailyPostAssignment
where a.Date == date && a.ShiftID == shiftID
select new
{
a.PostAssignmentID
}).ToList();
if (results.Count > 0)
{
foreach (var val in results)
{
postAssignmentID = val.PostAssignmentID;
}
isDuplicate = true;
}
else
{
isDuplicate = false;
}
return(isDuplicate);
}
private void setSequence()
{
Int16 locationID = Convert.ToInt16(ddlLocation.SelectedValue);
var model = new EDM.DataSource();
var results = (from a in model.tbl_Sup_ShiftReportChecks
where a.ShiftReportID == shiftReportID && a.LocationID == locationID
select new
{
a.ChecksID
}).ToList();
int count = 0;
if (results.Count > 0)
{
foreach (var val in results)
{
count++;
}
count = count + 1;
}
else
{
count = 1;
}
lblSequence.Text = count.ToString();
}
public void insert_Location(byte areaID, string location, bool isActive, byte userID)
{
int locationID = 0;
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Insert header
EDM.tbl_Ref_Locations a = new EDM.tbl_Ref_Locations();
{
a.AreaID = areaID;
a.LocationName = firstCharToUpper(location);
a.IsActive = isActive;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Ref_Locations.Add(a);
model.SaveChanges();
//Get the id
locationID = a.LocationID;
if (locationID != 0)
{
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = locationID;
z.TableName = "tbl_Ref_Locations";
z.AuditProcess = "Created Location (" + location + ")";
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
else
{
successfulCommit = false;
MISC.writetoAlertLog("No location ID returned after insert!!");
}
}
if (locationID != 0)
{
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
public void insert_Grant(DateTime dtDateFrom, DateTime dtDateTo, string comments, byte userID)
{
int grantID = 0;
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
EDM.tbl_Mgr_DateTimeGrant a = new EDM.tbl_Mgr_DateTimeGrant();
{
a.DateTimeFrom = dtDateFrom;
a.DateTimeTo = dtDateTo;
a.Comments = comments;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Mgr_DateTimeGrant.Add(a);
model.SaveChanges();
//Get the id
grantID = a.GrantID;
if (grantID != 0)
{
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = grantID;
z.TableName = "tbl_Mgr_DateTimeGrant";
z.AuditProcess = "Created Date/Time Grant for period " + dtDateFrom + " to " + dtDateTo + ")";
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
else
{
successfulCommit = false;
MISC.writetoAlertLog("No grant ID returned after insert!!");
}
}
if (grantID != 0)
{
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
protected void bindEmployeeDDL()
{
var model = new EDM.DataSource();
var results = (model.sp_Employee_Sel4DDL_2()).ToList();
ddlEmployee.DataSource = results;
ddlEmployee.DataValueField = "EmployeeID";
ddlEmployee.DataTextField = "FullName";
ddlEmployee.DataBind();
}
public void update_dailyPostAssignment(int postAssignmentID, DateTime date, byte shiftID, Int16 lateArrivals, Int16 callsForAssistance, Int16 officersPresent, Int16 officersAbsent, Int16 officersAssigned, Int16 officersCalledOut, Int16 officersCallInForDayOff, Int16 officersOnDayOff, Int16 notDressedProperly, string shiftSummary, string shiftBriefingNotes, string PassedOnFrom, string passedOnTo, string summaryOfIncidents, string reportNumber, byte userID, Int16 sleeping)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Update header table
var dl = (from a in model.tbl_Sup_DailyPostAssignment
where a.PostAssignmentID == postAssignmentID
select a).First();
dl.LateArrivals = lateArrivals;
dl.CallsForAssistance = callsForAssistance;
dl.OfficersPresent = officersPresent;
dl.OfficersAbsent = officersAbsent;
dl.OfficersAssigned = officersAssigned;
dl.OfficersCalledOut = officersCalledOut;
dl.OfficersCallInForDayOff = officersCallInForDayOff;
dl.OfficersOnDayOff = officersOnDayOff;
dl.NotDressedProperly = notDressedProperly;
dl.ShiftSummary = shiftSummary;
dl.ShiftBriefingNotes = shiftBriefingNotes;
dl.PassedOnFrom = PassedOnFrom;
dl.PassedOnTo = passedOnTo;
dl.SummaryOfIncidents = summaryOfIncidents;
dl.ReportNumber = reportNumber;
dl.Sleeping = sleeping;
dl.LastModifiedBy = userID;
dl.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog x = new EDM.tbl_AuditLog();
{
x.SourceID = postAssignmentID;
x.TableName = "tbl_Sup_DailyPostAssignment";
x.AuditProcess = "Modified Daily Post Assignment " + reportNumber;
x.DateTime = DateTime.Now;
x.UserID = userID;
};
model.tbl_AuditLog.Add(x);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//Update complaint
public void update_Absence(int callInOffID, bool isCallIn, Int16 employeeID, DateTime date, TimeSpan time, Int16 callTakenByID, Int16 callForwardedToID, string workSchedule, string phoneNumber, byte absentType, string otherTypeDescription, byte sickLeaveForID, string otherSickLeaveForDescription, string comments, byte userID, string reportNumber, DateTime startDate, DateTime endDate)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Get the record to update
var recordToUpdate = (from a in model.tbl_Op_CallIn_Absent
where a.CallInOffID == callInOffID
select a).First();
recordToUpdate.IsCallIn = isCallIn;
recordToUpdate.EmployeeID = employeeID;
recordToUpdate.Date = date;
recordToUpdate.Time = time;
recordToUpdate.CallTakenByID = callTakenByID == 0 ? null : (Int16?)callTakenByID;
recordToUpdate.ForwardedToID = callForwardedToID == 0 ? null : (Int16?)callForwardedToID;
recordToUpdate.WorkSchedule = workSchedule == "" ? null : firstCharToUpper(workSchedule);
recordToUpdate.PhoneNumber = phoneNumber == "" ? null : phoneNumber;
recordToUpdate.AbsentTypeID = absentType;
recordToUpdate.OtherTypeDescription = otherTypeDescription == "" ? null : firstCharToUpper(otherTypeDescription);
recordToUpdate.SickLeaveForID = sickLeaveForID == 0 ? null : (byte?)sickLeaveForID;
recordToUpdate.OtherSickLeaveForTypeDescription = otherSickLeaveForDescription == "" ? null : firstCharToUpper(otherSickLeaveForDescription);
recordToUpdate.Comments = comments == "" ? null : firstCharToUpper(comments);
recordToUpdate.StartDate = startDate.Date;
recordToUpdate.EndDate = endDate.Date;
//recordToUpdate.ShiftID = shiftID;
recordToUpdate.LastModifiedBy = userID;
recordToUpdate.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = callInOffID;
z.TableName = "tbl_Op_CallIn_Absent";
z.AuditProcess = "Modified Call In/Absent Report - " + reportNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//insert doubles
public void insert_double(int dailyLogID, Int16 employeeID, DateTime date, string areaName, string locationName, string shiftTime, byte userID, string logNumber)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Update header table
var dl = (from t in model.tbl_Op_DailyLogs
where t.DailyLogID == dailyLogID
select t).First();
dl.LastModifiedBy = userID;
dl.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert doubles
EDM.tbl_Op_DailyLogsPersonWorking a = new EDM.tbl_Op_DailyLogsPersonWorking();
{
a.DailyLogID = dailyLogID;
a.EmployeeID = employeeID;
a.IsPresent = true;
a.TimeClocked = DateTime.Now;
a.IsDouble = true;
a.IsSwitch = false;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Op_DailyLogsPersonWorking.Add(a);
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = dailyLogID;
z.TableName = "tbl_Op_DailyLogs";
z.AuditProcess = "Added Double to Daily Log (" + logNumber + ") " + date.Date.ToString("dd/MM/yyyy") + ", " + areaName + " - " + locationName + " -- " + shiftTime;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//overload method
public byte getUserID(string username)
{
byte userID = 0;
var model = new EDM.DataSource();
userID = (from b in model.Users
where b.UserName == username
select b.UID
).FirstOrDefault();
return(userID);
}
public void insert_IncidentPersonAffected(byte affectedTypeID, string firstName, string lastName, string description, string address, string otherTypeDescription, int incidentID, string phoneNumber, byte userID)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Insert transaction
EDM.tbl_Op_IncidentPersonsAffected a = new EDM.tbl_Op_IncidentPersonsAffected();
{
a.AffectedTypeID = affectedTypeID;
a.FirstName = firstName.Length > 0 ? firstCharToUpper(firstName) : null;
a.LastName = firstCharToUpper(lastName);
a.Description = firstCharToUpper(description);
a.Address = address.Length > 0 ? firstCharToUpper(address) : null;
a.OtherTypeDescription = otherTypeDescription.Length > 0 ? firstCharToUpper(otherTypeDescription) : null;
a.IncidentID = incidentID;
a.PhoneNumber = phoneNumber.Length > 0 ? phoneNumber : null;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Op_IncidentPersonsAffected.Add(a);
model.SaveChanges();
//Get the incidentID
string incidentNumber = (from b in model.tbl_Op_Incidents
where b.IncidentID == incidentID
select b.IncidentNumber
).FirstOrDefault();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = incidentID;
z.TableName = "tbl_Op_IncidentPersonsAffected";
z.AuditProcess = "Created Person Affected - " + incidentNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//Update complaint
public void update_Complaint(int complaintID, byte complaintChannelID, string personMakingReport, string contactNumber, byte areaID, Int16 locationID, DateTime dateOfIncident, TimeSpan timeofIncident, Int16 receivedByID, DateTime dateReceived, byte acknowledgementID, string clientcomplaint, bool feedbackProvided, DateTime dateOfFeedback, string actionTaken, byte userID, string complaintNumber, byte shiftID)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Get the record to update
var recordToUpdate = (from a in model.tbl_Op_Complaints
where a.ComplaintID == complaintID
select a).First();
recordToUpdate.ComplaintChannelID = complaintChannelID;
recordToUpdate.DateOfIncident = dateOfIncident;
recordToUpdate.TimeOfIncident = timeofIncident;
recordToUpdate.AreaID = areaID;
recordToUpdate.LocationID = locationID;
recordToUpdate.PersonMakingReport = firstCharToUpper(personMakingReport);
recordToUpdate.ContactNumber = contactNumber.Length > 0 ? contactNumber : null;
recordToUpdate.ReceivedByID = receivedByID;
recordToUpdate.DateReceived = dateReceived;
recordToUpdate.ShiftID = shiftID;
recordToUpdate.AcknowledgementID = acknowledgementID == 0 ? null : (byte?)acknowledgementID;
recordToUpdate.ClientComplaint = clientcomplaint.Length > 0 ? firstCharToUpper(clientcomplaint) : null;
recordToUpdate.FeedbackProvided = feedbackProvided;
recordToUpdate.DateOfFeedback = dateOfFeedback.ToString() == "1/1/0001 12:00:00 AM" ? null : (DateTime?)dateOfFeedback;
recordToUpdate.ActionTaken = actionTaken.Length > 0 ? firstCharToUpper(actionTaken) : null;
recordToUpdate.LastModifiedBy = userID;
recordToUpdate.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = complaintID;
z.TableName = "tbl_Op_Complaints";
z.AuditProcess = "Modified Complaint Report - " + complaintNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
public int getFailCount(string username)
{
int count = 0;
var model = new EDM.DataSource();
count = (from m in model.Memberships
join u in model.Users on m.UserId equals u.UserId
where u.UserName == username
select m.FailedPasswordAttemptCount
).FirstOrDefault();
return(count);
}
/*Events*/
//buton click that performs a search based on the Filter criteria
protected void btnFilter_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("UserId", typeof(Byte));
dt.Columns.Add("EmployeeId", typeof(Int16));
dt.Columns.Add("Name", typeof(String));
dt.Columns.Add("UserName", typeof(String));
dt.Columns.Add("Status", typeof(String));
dt.Columns.Add("RoleName", typeof(String));
dt.Columns.Add("CreateDate", typeof(DateTime));
dt.Columns.Add("LastActivityDate", typeof(DateTime));
string name = tbName.Text == "" ? null : tbName.Text;
string role = ddlRole.SelectedValue == "" ? null : ddlRole.SelectedValue;
bool? status = (ddlStatus.SelectedValue == "" ? (bool?)null : (Convert.ToBoolean(ddlStatus.SelectedValue) == true ? true : false));
var model = new EDM.DataSource();
try
{
var query = model.sp_FilterUsers(role, name, status);
var result = query.Distinct().ToList();
if (result.Count > 0)
{
foreach (var row in result)
{
dt.LoadDataRow(new object[] { row.UID, row.EmployeeID, row.Name, row.UserName, (row.IsActive == true ? "Active" : "Inactive"), row.RoleName, row.CreateDate, row.LastActivityDate }, false);
}
}
//bind grid
if (dt.Rows.Count > 0)
{
users = dt;
}
else
{
users = null;
}
bindUsers();
lblUsersFound.Text = "Users Found: " + gvUsers.Rows.Count;
}
catch (System.Exception excec)
{
MISC.writetoAlertLog(excec.ToString());
ShowMessage("Something went wrong and the search could not be completed. Kindly try again and if the error persists, refer to the error log for details.", MessageType.Error);
}
}
//insert Call Log
public void insert_CallLog(int dailyLogID, TimeSpan time, string reports, bool supCheckLocation, DateTime date, string areaName, string locationName, string shiftTime, byte userID, string logNumber)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Update header table
var dl = (from t in model.tbl_Op_DailyLogs
where t.DailyLogID == dailyLogID
select t).First();
dl.LastModifiedBy = userID;
dl.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert call log header
EDM.tbl_Op_DailyLogsCallLog a = new EDM.tbl_Op_DailyLogsCallLog();
{
a.DailyLogID = dailyLogID;
a.Time = time;
a.Reports = reports;
a.SupCheckLocation = supCheckLocation;
a.CreatedBy = userID;
a.CreatedOn = DateTime.Now;
};
model.tbl_Op_DailyLogsCallLog.Add(a);
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = dailyLogID;
z.TableName = "tbl_Op_DailyLogs";
z.AuditProcess = "Created Call Log for Daily Log (" + logNumber + ") " + date.Date.ToString("dd/MM/yyyy") + ", " + areaName + " - " + locationName + " -- " + shiftTime;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//Update employee
public void update_Employee(Int16 employeeID, string firstName, string lastName, string otherName, DateTime dateHired, byte areaID, Int16 locationID, byte departmentID, byte positionID, byte shiftID, bool isStandbyEmployee, bool isActive, string employeeNumber, byte userID)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Get the record to update
var a = (from y in model.tbl_Sup_Employees
where y.EmployeeID == employeeID
select y).First();
a.FirstName = firstName == "" ? null : firstCharToUpper(firstName);
a.LastName = firstCharToUpper(lastName);
a.OtherName = otherName == "" ? null : firstCharToUpper(otherName);
a.DateHired = dateHired;
a.AreaID = areaID == 0 ? null : (byte?)areaID;
a.LocationID = locationID == 0 ? null : (Int16?)locationID;
a.DepartmentID = departmentID == 0 ? null : (byte?)departmentID;
a.PositionID = positionID == 0 ? null : (byte?)positionID;
a.ShiftID = shiftID == 0 ? null : (byte?)shiftID;
a.IsStandbyStaff = isStandbyEmployee;
a.IsActive = isActive;
a.EmployeeNumber = employeeNumber;
a.LastModifiedBy = userID;
a.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = employeeID;
z.TableName = "tbl_Sup_Employees";
z.AuditProcess = "Modified Employee Record - " + employeeNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//generate the incident number
private void generateEmployeeNumber()
{
DateTime dateHired = new DateTime();
if (DateTime.TryParse(tbDateHired.Text, out dateHired))
{
tbDateHired.BorderColor = System.Drawing.Color.LightGray;
string month = "";
string day = "";
if (dateHired.Month < 10)
{
month = "0" + dateHired.Month.ToString();
}
else
{
month = dateHired.Month.ToString();
}
if (dateHired.Day < 10)
{
day = "0" + dateHired.Day.ToString();
}
else
{
day = dateHired.Day.ToString();
}
var model = new EDM.DataSource();
var results = (from a in model.tbl_Sup_Employees
where a.DateHired == dateHired.Date
select new
{
a.EmployeeID
}).ToList();
int currentSequence = Convert.ToInt32(results.Count) + 1;
if (currentSequence < 10)
{
lblEmployeeNumber.Text = dateHired.ToString("yy") + month + day + "0" + currentSequence.ToString();
}
else
{
lblEmployeeNumber.Text = dateHired.ToString("yy") + month + day + currentSequence.ToString();
}
}
else
{
ShowMessage("Incorrect date format.", MessageType.Error);
tbDateHired.BorderColor = System.Drawing.Color.Red;
tbDateHired.Focus();
}
}
private void loadYear()
{
//If create, get current year only and next year if month is october
DataTable dt = new DataTable();
dt.Columns.Add("Year", typeof(string));
if (rbCreate.Checked == true)
{
int currentYear = DateTime.Now.Year;
int currentMonth = DateTime.Now.Month;
string sCurrentYear = currentYear.ToString();
DataRow dr = dt.NewRow();
dr["Year"] = sCurrentYear;
dt.Rows.Add(dr);
//ddlYear.Items.Add(sCurrentYear);
if (currentMonth > 10)
{
int nextYear = currentYear + 1;
DataRow dr2 = dt.NewRow();
dr2["Year"] = nextYear.ToString();
dt.Rows.Add(dr2);
//ddlYear.Items.Add(nextYear.ToString());
}
}
//if modify, get years from database
else
{
var model = new EDM.DataSource();
var results = (from a in model.tbl_Mgr_WorkSchedule
select new
{
a.Year
}).Distinct().ToList();
if (results.Count > 0)
{
foreach (var val in results)
{
// ddlYear.Items.Add(val.Year);
DataRow dr = dt.NewRow();
dr["Year"] = val.Year;
dt.Rows.Add(dr);
}
}
}
ddlYear.DataSource = dt;
ddlYear.DataTextField = "Year";
ddlYear.DataValueField = "Year";
ddlYear.DataBind();
}
//Update employee
public void update_SO(int specialOperationsID, byte areaID, Int16 locationID, DateTime date, bool escortService, bool cashInGT, bool cashOutGT, bool extraOfficers, byte amountOfficers, string reportNumber, string comments, byte userID, byte shiftID)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Get the record to update
var a = (from y in model.tbl_Mgr_SpecialOperations
where y.SpecialOperationsID == specialOperationsID
select y).First();
a.AreaID = areaID == 0 ? null : (byte?)areaID;
a.LocationID = locationID == 0 ? null : (byte?)locationID;
a.Date = date;
a.EscortService = escortService;
a.CashInGT = cashInGT;
a.CashOutGT = cashOutGT;
a.ExtraOfficers = extraOfficers;
a.AmountOfficers = amountOfficers == 0 ? null : (byte?)amountOfficers;
a.ReportNumber = reportNumber;
a.Comments = comments == "" ? null : comments;
a.ShiftID = shiftID == 0 ? null : (byte?)shiftID;
a.LastModifiedBy = userID;
a.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = specialOperationsID;
z.TableName = "tbl_Mgr_SpecialOperations";
z.AuditProcess = "Modified Special Operations Log - " + reportNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
public void update_Incident(int incidentID, byte areaID, Int16 locationID, DateTime dateOccured, TimeSpan timeOccured, byte incidentTypeID, string otherTypeDescription, string description, string incidentNumber, byte userID, byte shiftID, string actionTaken)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
using (EDM.DataSource model = new EDM.DataSource())
{
//Get the record to update
var recordToUpdate = (from a in model.tbl_Op_Incidents
where a.IncidentID == incidentID
select a).First();
recordToUpdate.AreaID = areaID;
recordToUpdate.LocationID = locationID;
recordToUpdate.DateOccured = dateOccured;
recordToUpdate.TimeOccured = timeOccured;
recordToUpdate.IncidentTypeID = incidentTypeID;
recordToUpdate.OtherTypeDescription = otherTypeDescription.Length > 0 ? firstCharToUpper(otherTypeDescription) : null;
recordToUpdate.Description = firstCharToUpper(description);
recordToUpdate.ActionTaken = firstCharToUpper(actionTaken);
recordToUpdate.IncidentNumber = incidentNumber;
recordToUpdate.ShiftID = shiftID == 0 ? null : (byte?)shiftID;
recordToUpdate.LastModifiedBy = userID;
recordToUpdate.LastModifiedOn = DateTime.Now;
model.SaveChanges();
//Insert AuditTrail
EDM.tbl_AuditLog z = new EDM.tbl_AuditLog();
{
z.SourceID = incidentID;
z.TableName = "tbl_Op_Incidents";
z.AuditProcess = "Modified Incident Report - " + incidentNumber;
z.DateTime = DateTime.Now;
z.UserID = userID;
};
model.tbl_AuditLog.Add(z);
model.SaveChanges();
}
//commit transaction
ts.Complete();
successfulCommit = true;
}
}
catch (Exception exec)
{
successfulCommit = false;
MISC.writetoAlertLog(exec.ToString());
}
}
//overload method
public byte getPositionID(string username)
{
byte positionID = 0;
var model = new EDM.DataSource();
var posID = (from b in model.Users
join c in model.tbl_Sup_Employees on b.UID equals c.UID
where b.UserName == username
select c.PositionID
).FirstOrDefault();
if (positionID != null)
{
positionID = Convert.ToByte(posID);
}
return(positionID);
}
