public MdaGovernmentMapping GetMdaById(int mdaId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
return ents.MdaGovernmentMappings.Include("Government").FirstOrDefault(x => x.mdaID == mdaId);
}
}
public static int GetCurrentPayrollPeriodForMDAByEmployeeId(long empId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
return GetCurrentPayrollPeriodForMDAByMDAId(ents.EmployeeAssignments.FirstOrDefault(a => a.EmployeeID == empId).mdaID);
}
}
public List<MdaGovernmentMapping> GetALLMdaByGovtID(int govtID)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
return ents.MdaGovernmentMappings.Include("Government").Include("MdaStructure").Where(x=>x.governmentID==govtID).OrderBy(m => m.Government.governmentLevelID).ThenBy(m => m.Government.governmentName).ThenBy(x => x.mdaName).ToList();
}
}
public static int GetCurrentPayrollPeriodForMDAByMDAId(int mdaId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
MDA_CONFIG md = ents.MDA_CONFIG.FirstOrDefault(x => x.MDAID == mdaId);
return md == null ? 0 : int.Parse( md.CONFIG_VALUE);
}
}
public bool MdaIdsBelongToSameInstallation(List<int> mdaIds)
{
if (mdaIds == null || mdaIds.Count == 0) return false;
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
return ents.InstallationConfigs.Where(x => x.installation_key == Constant.MANAGED_SITE &&
mdaIds.Contains(x.mdaId)).Select(o => o.installation_value).Distinct().Count() == 1 ? true : false;
}
}
public List<int> ManagedMDAListBySiteId(int siteId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
string siteIdValue = siteId.ToString();
return ents.InstallationConfigs.Where(x =>
x.installation_key == Constant.MANAGED_SITE &&
x.installation_value == siteIdValue).Select(o => o.mdaId).ToList();
}
}
public static string GetListTextById(int listId)
{
using (var context = new SsepsIISynEntities())
{
var listItem = context.ListItems.FirstOrDefault(l => l.listID == listId);
if (listItem != null)
return listItem.listText;
else
return string.Empty;
}
}
public List<MdaGovernmentMapping> GetMDAsWithAssignments()
{
using (var context = new SsepsIISynEntities())
{
return (from mdas in context.MdaGovernmentMappings
join empAsgn in context.EmployeeAssignments on mdas.mdaID equals empAsgn.mdaID
where mdas.mdaID == empAsgn.mdaID
orderby mdas.mdaName
select mdas).Distinct().ToList<MdaGovernmentMapping>();
}
}
/// <summary>
/// If the employee already exists, then do an update.
/// </summary>
public void InsertEmployee()
{
bool exists = false;
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
//Skip if table doesn't exist
if (Tables.Where(x => x.TableName == Constant.EMPLOYEE_TABLE).Count() == 0) return;
XmlAttributeCollection attributes = Tables.Single(x => x.TableName == Constant.EMPLOYEE_TABLE).GetNewAttributesFromXml();
Employee emp;
if (ents.Employees.Where(x => x.EmployeeID == EmployeeId).Count() > 0)
{
emp = ents.Employees.Find(EmployeeId);
exists = true;
}
else
{
emp = new Employee();
emp.EmployeeID = EmployeeId;
}
if (attributes.GetNamedItem("prefix") != null) emp.prefix = attributes.GetNamedItem("prefix").Value;
if (attributes.GetNamedItem("gender") != null) emp.gender = attributes.GetNamedItem("gender").Value;
emp.surName = attributes.GetNamedItem("surName").Value;
emp.givenName = attributes.GetNamedItem("givenName").Value;
emp.otherNames = attributes.GetNamedItem("otherNames").Value;
emp.dateofBirth = DateTime.Parse(attributes.GetNamedItem("dateofBirth").Value);
if (attributes.GetNamedItem("maritalStatus") != null) emp.maritalStatus = int.Parse(attributes.GetNamedItem("maritalStatus").Value);
if (attributes.GetNamedItem("EmployeePIN") != null) emp.EmployeePIN = attributes.GetNamedItem("EmployeePIN").Value;
if (attributes.GetNamedItem("NationalID") != null) emp.NationalID = attributes.GetNamedItem("NationalID").Value;
emp.Nationality = attributes.GetNamedItem("Nationality").Value;
if (attributes.GetNamedItem("hrmisCode") != null) emp.hrmisCode = attributes.GetNamedItem("hrmisCode").Value;
emp.dateofAppointment = DateTime.Parse(attributes.GetNamedItem("dateofAppointment").Value);
emp.IsActive = int.Parse(attributes.GetNamedItem("IsActive").Value) == 1 ? true : false;
if (attributes.GetNamedItem("email") != null) emp.email = attributes.GetNamedItem("email").Value;
if (attributes.GetNamedItem("phoneMobile") != null) emp.phoneMobile = attributes.GetNamedItem("phoneMobile").Value;
if (attributes.GetNamedItem("phoneMobile2") != null) emp.phoneMobile2 = attributes.GetNamedItem("phoneMobile2").Value;
if (attributes.GetNamedItem("phoneMobile3") != null) emp.phoneMobile3 = attributes.GetNamedItem("phoneMobile3").Value;
if (attributes.GetNamedItem("home_StateID") != null) emp.home_StateID = int.Parse(attributes.GetNamedItem("home_StateID").Value);
if (attributes.GetNamedItem("address") != null) emp.address = attributes.GetNamedItem("address").Value;
emp.dateCreated = DateTime.Parse(attributes.GetNamedItem("dateCreated").Value);
if (attributes.GetNamedItem("dateUpdated") != null) emp.dateUpdated = DateTime.Parse(attributes.GetNamedItem("dateUpdated").Value);
emp.whoCreated = attributes.GetNamedItem("whoCreated").Value;
if (attributes.GetNamedItem("whoUpdated") != null) emp.whoUpdated = attributes.GetNamedItem("whoUpdated").Value;
if (attributes.GetNamedItem("LogRefID") != null) emp.LogRefID = attributes.GetNamedItem("LogRefID").Value;
if (attributes.GetNamedItem("lastPromotionDate") != null) emp.lastPromotionDate = DateTime.Parse(attributes.GetNamedItem("lastPromotionDate").Value);
if (attributes.GetNamedItem("lastIncrementDate") != null) emp.lastIncrementDate = DateTime.Parse(attributes.GetNamedItem("lastIncrementDate").Value);
ents.Database.ExecuteSqlCommand("DISABLE TRIGGER dbo.EmployeeEventLog ON dbo.Employee");
if (!exists) ents.Employees.Add(emp);
ents.SaveChanges();
ents.Database.ExecuteSqlCommand("ENABLE TRIGGER dbo.EmployeeEventLog ON dbo.Employee");
}
}
public int GetSiteIdFromMDAIdOnServer(int mdaId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
if (ents.InstallationConfigs.Where(x => x.installation_key == Constant.MANAGED_SITE && x.mdaId == mdaId).Count() > 0)
{
string installationValue = ents.InstallationConfigs.First(x => x.installation_key == Constant.MANAGED_SITE && x.mdaId == mdaId).installation_value;
int siteId;
return int.TryParse(installationValue, out siteId) ? siteId : 0;
}
else return 0;
}
}
public string InstallationTokenByMDAId(int mdaId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
if (ents.InstallationConfigs.Where(x => x.installation_key == Constant.MANAGED_SITE && x.mdaId == mdaId).Count() > 0)
{
return ents.InstallationConfigs.First(x =>
x.installation_key == Constant.MANAGED_SITE &&
x.mdaId == mdaId).installation_token;
}
else return string.Empty;
}
}
private static void Log(string message, string username, int level = 3, string exception = null)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
//Log log = new Log();
//log.Message = message;
//log.Level = level;
//log.Exception = exception;
//log.WhoCreated = username;
//log.DateCreated = DateTime.Now;
//ents.Logs.Add(log);
//ents.SaveChanges();
}
}
public string InstallationTokenBySiteId(int siteId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
string siteIdValue = siteId.ToString();
if (ents.InstallationConfigs.Where(x => x.installation_key == Constant.MANAGED_SITE && x.installation_value == siteIdValue).Count() > 0)
{
return ents.InstallationConfigs.First(x =>
x.installation_key == Constant.MANAGED_SITE &&
x.installation_value == siteIdValue).installation_token;
}
else return string.Empty;
}
}
public SystemUser LogIn(string username, string password)
{//MyConnectionString.ConnectionString
if (username.IsNotNullOrWhiteSpace() && password.IsNotNullOrWhiteSpace())
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
if (ents.SystemUsers.Where(x => x.Username.Equals(username, StringComparison.CurrentCultureIgnoreCase)).Count() == 1)
{
SystemUser systemUser = ents.SystemUsers
.Include("SystemUserMdas.MdaGovernmentMapping.Government")
.Include("SystemUserSupervisedMdas.MdaGovernmentMapping.Government")
.Include("SystemUserRoles.SystemRole")
.Include("Employee")
.Single(x => x.Username.Equals(username, StringComparison.CurrentCultureIgnoreCase));
if (systemUser != null && systemUser.Locked == false)
{
var passwordHash = new EncryptionService().GetSHA256(password, systemUser.PasswordSalt, true);
bool isValid = systemUser.Password.IsNotNullOrWhiteSpace() && systemUser.Password == passwordHash && systemUser.Active;
if (isValid)
{
return systemUser;
}
else
{
return null;
}
}
else
{
return null;
}
}
else
{
return null;
}
}
}
else
{
return null;
}
}
public List<MdaGovernmentBO> GetMyMDAGovtWithAssignments()
{
List<MdaGovernmentBO> temp = new List<MdaGovernmentBO>();
using (var context = new SsepsIISynEntities())
{
var mds = (from mdas in context.MdaGovernmentMappings.Include("MdaGovernmentMapping.Government")
join empAsgn in context.EmployeeAssignments on mdas.mdaID equals empAsgn.mdaID
where mdas.mdaID == empAsgn.mdaID
orderby mdas.mdaName
select mdas).Distinct().ToList<MdaGovernmentMapping>();
foreach (MdaGovernmentMapping mgm in mds)
{
temp.Add(new MdaGovernmentBO()
{
governmentName=mgm.Government.governmentName,
mdaID=mgm.mdaID,
mdaName=mgm.mdaName
});
}
}
return temp;
}
public List<MdaGovernmentMapping> GetGovtMDAs(int govtId, bool IncludeCountyMDAs=false)
{
using (var context = new SsepsIISynEntities())
{
return IncludeCountyMDAs ? context.MdaGovernmentMappings.Include("Government").Where(m => (m.Government.governmentID == govtId || m.Government.linkToID == govtId)).OrderBy(m => m.mdaName).ToList() : context.MdaGovernmentMappings.Include("Government").Where(m => (m.Government.governmentID == govtId)).OrderBy(m => m.mdaName).ToList();
}
}
public List<DirectorateListing> GetMdaDirectorates(List<int> mdaIds)
{
using (var context = new SsepsIISynEntities())
{
return context.DirectorateListings.Include("MdaGovernmentMapping").Where(d => mdaIds.Contains(d.mdaID)).OrderBy(d => d.directorateName).ToList();
}
}
public Government GetGovernmentById(int governmentId)
{
using (var context = new SsepsIISynEntities())
{
return context.Governments.FirstOrDefault(g => g.governmentID == governmentId);
}
}
public MdaStructure GetMdaStructureById(int mdaStructureId)
{
using (var context = new SsepsIISynEntities())
{
return context.MdaStructures.FirstOrDefault(m => m.mdaStructureID == mdaStructureId);
}
}
public void ConsolidateTransfers(XElement records)
{
//TODO clean up.
if (records == null) return;
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
List<TransferredEmployee> transferedEmployees = new List<TransferredEmployee>();
TransferredEmployee tempTransferedEmployee;
foreach (XElement transferedEmpElement in records.Elements("Transfer").ToList())
{
tempTransferedEmployee = new TransferredEmployee()
{
EmployeeID = long.Parse(transferedEmpElement.Element("EmployeeID").Value),
TransferReason = transferedEmpElement.Element("TransferReason").Value,
TransferXML = transferedEmpElement.Element("TransferXML").Value,
TransferDirection = transferedEmpElement.Element("TransferDirection").Value,
FromDirectorateId = int.Parse(transferedEmpElement.Element("FromDirectorateId").Value),
FromGrade = int.Parse(transferedEmpElement.Element("FromGrade").Value),
StartPeriodDestination = int.Parse(transferedEmpElement.Element("StartPeriodDestination").Value),
TransferState = int.Parse(transferedEmpElement.Element("TransferState").Value),
ToGrade = int.Parse(transferedEmpElement.Element("ToGrade").Value),
ToMDAID = int.Parse(transferedEmpElement.Element("ToMDAID").Value),
FromMDAID = int.Parse(transferedEmpElement.Element("FromMDAID").Value),
AuthorizedBy = transferedEmpElement.Element("AuthorizedBy").Value,
AuthorizedOn = DateTime.Parse(transferedEmpElement.Element("AuthorizedOn").Value),
dateCreated = DateTime.Parse(transferedEmpElement.Element("dateCreated").Value),
whoCreated = transferedEmpElement.Element("whoCreated").Value
};
if (transferedEmpElement.Element("dateUpdated") != null) tempTransferedEmployee.dateUpdated = DateTime.Parse(transferedEmpElement.Element("dateUpdated").Value);
//TODO why is this varbinary?
//if (transferedEmpElement.Element("whoCreated") != null) tempTransferedEmployee.whoUpdated = transferedEmpElement.Element("TransferReason").Value
transferedEmployees.Add(tempTransferedEmployee);
}
//SqlConnection dbConn = ents.Database.Connection as SqlConnection;
//dbConn.Open();
//SqlBulkCopy bulkInsert = new SqlBulkCopy(dbConn);
//bulkInsert.DestinationTableName = "TransferredEmployee";
//bulkInsert.WriteToServer(transferedEmployees.AsDataReader());
//dbConn.Close();
}
}
public void ImportApprovedData(XDocument xDoc)
{
List<string> pkApprovedData = new List<string>();
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
using (TransactionScope scope = new TransactionScope())
{
List<TransData> dataToSync = new List<TransData>();
foreach (XElement rowElement in xDoc.Element("Approved").Element("TransData").Elements("Row").ToList())
{
string logRefID = string.Empty;
switch ((int)rowElement.Element("state"))
{
case (int)SyncState.Rejected:
if (rowElement.Element("LogRefID") == null) new ArgumentNullException("LogRefID");
logRefID = (string)rowElement.Element("LogRefID");
TransData approvalStatusRejected = ents.TransDatas.Where(o => o.LogRefID == logRefID).FirstOrDefault();
if (approvalStatusRejected == null) new InvalidDataException("Sync Row not found on client machine");
//If it was rejected and not edited further on the client machine, then reject.
//However, if it was edited by the user then assume new changes to be valid ones.
if (approvalStatusRejected.state == (int)SyncState.Exported)
{
approvalStatusRejected.state = (int)SyncState.Rejected;
pkApprovedData.Add(logRefID);
}
break;
case (int)SyncState.Approved:
if (rowElement.Element("LogRefID") == null) new ArgumentNullException("LogRefID");
logRefID = (string)rowElement.Element("LogRefID");
TransData approvalStatusAccepted = ents.TransDatas.Where(o => o.LogRefID == logRefID).FirstOrDefault();
if (approvalStatusAccepted == null) new InvalidDataException("Sync Row not found on client machine");
approvalStatusAccepted.state = (int)SyncState.Approved;
pkApprovedData.Add(logRefID);
approvalStatusAccepted.NewValues = rowElement.Element("newValue").FirstNode.ToString();
break;
case (int)SyncState.SyncedConsolidateOnly:
int mdaId = 0, syncPeriod = 0;
if (rowElement.Element("SyncPeriod") == null) new ArgumentNullException("SyncPeriod");
else syncPeriod = (int)rowElement.Element("SyncPeriod");
if (rowElement.Element("MdaId") == null) new ArgumentNullException("MdaId");
else mdaId = (int)rowElement.Element("MdaId");
TransData approvalStatusConsolidated = ents.TransDatas.Where(o => o.MdaId == mdaId && o.SyncPeriod == syncPeriod).FirstOrDefault();
approvalStatusConsolidated.state = (int)SyncState.SyncedConsolidateOnly;
break;
}
ents.SaveChanges();
}
foreach (XElement rowElement in xDoc.Element("Approved").Element("Transfer-status").Elements("Row").ToList())
{
long employeeId = (long)rowElement.Element("EmployeeId");
TransferredEmployee transferredEmployee = ents.TransferredEmployees.Find(employeeId);
if (transferredEmployee != null)
{
transferredEmployee.TransferState = (int)rowElement.Element("state");
ents.SaveChanges();
}
}
List<long> empIds = ents.TransDatas.Where(x => pkApprovedData.Contains(x.LogRefID)).Select(o => o.EmployeeID).Distinct().ToList();
foreach (long empId in empIds)
{
EffectApprovalsByEmployeeID(empId);
}
#region Transfers
ConsolidateTransfers(xDoc.Element("Approved").Element("Transfers"));
#endregion
scope.Complete();
}
}
}
public List<TransData> GetDataToSync(SystemUser user)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
//Mark them as exported
foreach (TransData row in ents.TransDatas.Where(x => user.AssignedMdaIdList.Contains(x.MdaId) && x.state == (int)SyncState.Normal).ToList())
{
row.state = (int)SyncState.Exported;
}
ents.SaveChanges();
return ents.TransDatas.Where(x => user.AssignedMdaIdList.Contains(x.MdaId) &&
(x.state == (int)SyncState.Normal || x.state == (int)SyncState.Exported || x.state == (int)SyncState.ConsolidateOnly)).OrderBy(o => o.MdaId).ToList();
}
}
public List<MdaGovernmentMapping> GetGovtMDAsWithAssignments(int govtId)
{
using (var context = new SsepsIISynEntities())
{
return (from mdas in context.MdaGovernmentMappings
join assgn in context.EmployeeAssignments on mdas.mdaID equals assgn.mdaID
where (mdas.mdaID == assgn.mdaID && mdas.Government.governmentID == govtId)
select mdas).Distinct().OrderBy(m => m.mdaName).ToList<MdaGovernmentMapping>();
}
}
public List<MdaGovernmentMapping> GetGovtLevelMDAs(int govtLevelId)
{
using (var context = new SsepsIISynEntities())
{
return context.MdaGovernmentMappings.Where(m => m.Government.governmentLevelID == govtLevelId).OrderBy(m => m.mdaName).ToList();
}
}
private void GetEmployeePayrollHistoryData(XElement mdaConsolidationXElement, TransData data)
{
if (!mdaConsolidationXElement.Elements(Constant.EMPLOYEE_PAYROLLHISTORY_TABLE).Any())
mdaConsolidationXElement.Add(new XElement(Constant.EMPLOYEE_PAYROLLHISTORY_TABLE, new XAttribute("SyncPeriod", data.SyncPeriod)));
if (mdaConsolidationXElement.Elements(Constant.EMPLOYEE_PAYROLLHISTORY_TABLE)
.Where(x => x.Attribute("SyncPeriod").Value == data.SyncPeriod.ToString()).Count() > 0) new ArgumentException(string.Format("Duplicate payroll periods for mda {0}", data.MdaId));
XElement payrollHistoryXElement = mdaConsolidationXElement.Elements(Constant.EMPLOYEE_PAYROLLHISTORY_TABLE).FirstOrDefault(x => x.Attribute("SyncPeriod").Value == data.SyncPeriod.ToString());
if (payrollHistoryXElement == null) mdaConsolidationXElement.Add(payrollHistoryXElement = new XElement(Constant.EMPLOYEE_PAYROLLHISTORY_TABLE, new XAttribute("SyncPeriod", data.SyncPeriod)));
payrollHistoryXElement.Add(GetSyncDataRow(data));
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
payrollHistoryXElement.Add(new XElement("History-data"));
foreach (EmployeePayrollHistory empPH in ents.EmployeePayrollHistories.Where(x => x.mdaID == data.MdaId && x.payrollPeriod == data.SyncPeriod).ToList())
{
payrollHistoryXElement.Element("History-data").Add(new XElement(empPH.AsXmlRow));
}
}
}
public List<Government> GetGovernmentsByLevel(int levelId)
{
using (var context = new SsepsIISynEntities())
{
return context.Governments.Where(g => g.governmentLevelID == levelId).OrderBy(g => g.governmentName).ToList();
}
}
public void EffectApprovalsByEmployeeID(long empId)
{
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
SyncData syncData = SyncFactory.GetSyncObject(ents.TransDatas.Where(x => x.EmployeeID == empId).ToList());
syncData.Save(SyncType.Approval);
}
}
public List<MdaGovernmentMapping> GetMDAsByGovernmentForAssignment(int govtId, int systemUserId)
{
using (var context = new SsepsIISynEntities())
{
var assignedMDAs = context.SystemUserMdas.Where(s => s.SystemUserId == systemUserId).Select(s => s.MdaId).ToList();
var supervisedMDAs = context.SystemUserSupervisedMdas.Where(s => s.SystemUserId == systemUserId).Select(s => s.MdaId).ToList();
var mdas = context.MdaGovernmentMappings.Where(m => m.governmentID == govtId).OrderBy(m => m.mdaName).ToList();
foreach (var mda in mdas)
{
if (assignedMDAs.Contains(mda.mdaID))
mda.IsAssigned = true;
if (supervisedMDAs.Contains(mda.mdaID))
mda.IsSupervised = true;
}
return mdas;
}
}
/// <summary>
/// Get's all pending transfers that need to be synced with the server for all mda's on this site
/// </summary>
/// <returns></returns>
public XElement GetEmployeesToTransferToServer()
{
XElement transfers = new XElement("Transfers");
using (SsepsIISynEntities ents = new SsepsIISynEntities())
{
int sourceCreatedTransfer = (int)TransferState.SourceCreatedTransfer;
foreach (TransferredEmployee transferredEmp in ents.TransferredEmployees.Where(x => x.TransferState == sourceCreatedTransfer).ToList())
{
XElement transfer = new XElement("Transfer",
new XElement("EmployeeID", transferredEmp.EmployeeID),
new XElement("TransferReason", transferredEmp.TransferReason),
new XElement("TransferXML", transferredEmp.TransferXML),
new XElement("TransferDirection", transferredEmp.TransferDirection),
new XElement("FromDirectorateId", transferredEmp.FromDirectorateId),
new XElement("FromGrade", transferredEmp.FromGrade),
new XElement("StartPeriodDestination", transferredEmp.StartPeriodDestination),
new XElement("TransferState", transferredEmp.TransferState),
new XElement("ToGrade", transferredEmp.ToGrade),
new XElement("ToMDAID", transferredEmp.ToMDAID),
new XElement("FromMDAID", transferredEmp.FromMDAID),
new XElement("AuthorizedBy", transferredEmp.AuthorizedBy),
new XElement("AuthorizedOn", transferredEmp.AuthorizedOn),
new XElement("dateCreated", transferredEmp.dateCreated),
new XElement("whoCreated", transferredEmp.whoCreated));
if (transferredEmp.dateUpdated.HasValue) transfer.Add(new XElement("dateUpdated", transferredEmp.dateUpdated));
//transfers.Add(transfer);
}
}
return transfers;
}
public List<MdaStructure> GetMdaStructures()
{
using (var context = new SsepsIISynEntities())
{
return context.MdaStructures.OrderBy(m => m.mdaStructureName).ToList();
}
}