public IEnumerable<Advertisment> GetAdvertisments(string servicecode, DateTime? enddate)
{
var list = new List<Advertisment>();
using (var entities = new atmEntities())
{
var l = from a in entities.tblAdvertisments where a.ServiceCd == servicecode select a;
if (enddate.HasValue)
l = l.Where(a => a.EndDate <= enddate);
if (!l.Any()) return list;
foreach (var exist in l)
{
var adv = new Advertisment
{
Id = exist.Id,
AcquisitionId = exist.AcquisitionId ?? 0,
CreatedBy = exist.CreatedBy,
ServiceCode = exist.ServiceCd,
Description = exist.Description,
CreatedDate = DateTime.Now,
EndDate = exist.EndDate,
IntakeLocationId = exist.IntakeLocationId,
InterviewLocationId = exist.InterviewLocationId ?? 0,
IsActive = exist.IsActive,
ShortDescription = exist.ShortDescription,
StartDate = exist.StartDate,
Title = exist.Title
};
list.Add(adv);
}
}
return list;
}
public int Count(int acquisitionid, string[] birthstatecode, string gendercode, string highleveleducode, string racecode, string ethniccode, string religioncode)
{
if (acquisitionid != 0)
{
using (var entities = new atmEntities())
{
var app = from a in entities.tblApplicantSubmiteds where a.AcquisitionId == acquisitionid select a;
if (birthstatecode.Any())
app = app.Where(a => birthstatecode.Contains(a.BirthStateCd));
if (!string.IsNullOrWhiteSpace(gendercode))
app = app.Where(a => a.GenderCd == gendercode);
if (!string.IsNullOrWhiteSpace(highleveleducode))
app = app.Where(a => a.tblApplicantEduSubmitteds.Any(b => b.HighEduLevelCd == highleveleducode));
if (!string.IsNullOrWhiteSpace(racecode))
app = app.Where(a => a.RaceCd == racecode);
if (!string.IsNullOrWhiteSpace(ethniccode))
app = app.Where(a => a.EthnicCd == ethniccode);
if (!string.IsNullOrWhiteSpace(religioncode))
app = app.Where(a => a.ReligionCd == religioncode);
return app.Count();
}
}
return 0;
}
public int AddNew(ExistingMember existingMember)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblExistingAtmMembers where a.ICNOBaru == existingMember.IdNumber select a).SingleOrDefault();
if (null != exist)
{
exist.COID = exist.COID;
return Update(existingMember);
}
var atm = new tblExistingAtmMember()
{
COID = existingMember.CoId,
CONm = existingMember.Name,
ICNOBaru = existingMember.IdNumber,
NoTentera = existingMember.ArmyNo,
ExistingMemberStatusCd = existingMember.Status
};
entities.tblExistingAtmMembers.Add(atm);
if (entities.SaveChanges() > 0)
return atm.COID;
}
return 0;
}
public void Delete(int id)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblApplicants where a.ApplicantId == id select a);
if (exist.Any())
entities.tblApplicants.RemoveRange(exist);
}
}
public void Delete(int id)
{
if (id == 0) return;
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblAdvertisments where a.Id == id select a).SingleOrDefault();
if (null == exist) return;
entities.tblAdvertisments.Remove(exist);
entities.SaveChanges();
}
}
public bool CheckingExistingAtmMember(string idnumber)
{
if (!string.IsNullOrWhiteSpace(idnumber))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblExistingAtmMembers where a.ICNOBaru.ToLower() == idnumber.ToLower() select a).SingleOrDefault();
return null != exist;
}
}
return false;
}
public bool CheckingExistingAtmMemberByArmyNo(string armyno)
{
if (!string.IsNullOrWhiteSpace(armyno))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblExistingAtmMembers where a.NoTentera == armyno select a).SingleOrDefault();
return null != exist;
}
}
return false;
}
public ApplicantSubmitted GetApplicant(int applicantid)
{
if (applicantid != 0)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblApplicantSubmiteds where a.ApplicantId == applicantid select a).SingleOrDefault();
if (null != exist)
return BindingToClass(exist);
}
}
return null;
}
public bool Delete(int existingid)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblExistingAtmMembers where a.COID == existingid select a).SingleOrDefault();
if (null != exist)
{
entities.tblExistingAtmMembers.Remove(exist);
return entities.SaveChanges() > 0;
}
}
return false;
}
public bool ChangePasswordFirstTime(int loginid, bool firsttime, string newpassword)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.UserId == loginid select a).SingleOrDefault();
if (null != exist)
{
exist.Password = newpassword;
exist.FirstTime = firsttime;
exist.ModifiedDt = DateTime.Now;
return entities.SaveChanges() > 0;
}
}
return false;
}
public bool CheckUserIsInRole(string username, string role)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
var r = (from a in entities.tblUserRoles where a.UserId == exist.UserId select a).SingleOrDefault();
if (!string.IsNullOrWhiteSpace(r?.Roles))
return r.Roles.Contains(role);
if (r == null && role == RolesString.AWAM)
return true;
}
}
return false;
}
public bool DeleteApplicantSport(int applicantsportid)
{
if (applicantsportid != 0)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblApplicantSportAssocs where a.ApplicantSportAssocId == applicantsportid select a).SingleOrDefault();
if (null != exist)
{
entities.tblApplicantSportAssocs.Remove(exist);
return entities.SaveChanges() > 0;
}
}
}
return false;
}
public IEnumerable<Applicant> ExecuteQuery(string sql)
{
var list = new List<Applicant>();
if (!string.IsNullOrWhiteSpace(sql))
{
using (var entities = new atmEntities())
{
var l = entities.tblApplicants.SqlQuery(sql).ToList();
if (l.Any())
{
list.AddRange(l.Select(app => BindingToClass(app)));
}
}
}
return list;
}
public int AddNew(LoginUser loginUser)
{
if (null != loginUser)
{
using (var entities = new atmEntities())
{
// check existing
var exist = (from a in entities.tblUsers where a.LoginId == loginUser.LoginId select a).SingleOrDefault();
if (null != exist)
{
loginUser.UserId = exist.UserId;
return Update(loginUser);
}
var u = new tblUser
{
UserName = loginUser.UserName,
AlternativeEmail = loginUser.AlternativeEmail,
ApplicantId = loginUser.ApplicantId,
Email = loginUser.Email,
FullName = loginUser.FullName.ToUpper(),
Password = loginUser.Password,
Salt = loginUser.Salt,
LoginId = loginUser.LoginId,
FirstTime = loginUser.FirstTime,
IsLocked = loginUser.IsLocked,
LastLoginDt = loginUser.LastLoginDt,
LastLoginDt2 = loginUser.LastLoginDt2,
ServiceCd = loginUser.ServiceCd,
CreatedBy = loginUser.CreatedBy,
CreatedDt = DateTime.Now,
ModifiedBy = loginUser.ModifiedBy,
ModifiedDt = loginUser.ModifiedDt
};
entities.tblUsers.Add(u);
if (entities.SaveChanges() != 0)
return u.UserId;
}
}
return 0;
}
public ApplicantSubmitted GetApplicant(int applicantid, int acquisitionid)
{
if (applicantid != 0 && acquisitionid != 0)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblApplicantSubmiteds where a.ApplicantId == applicantid && a.AcquisitionId == acquisitionid select a).SingleOrDefault();
if (null != exist)
{
var app = BindingToClass(exist);
var edus = GetEducation(app.ApplicantId, acquisitionid);
foreach (var ed in edus)
app.ApplicantEducationSubmittedCollection.Add(ed);
return app;
}
}
}
return null;
}
public IEnumerable<Achievement> GetAchievements()
{
var list = new List<Achievement>();
using (var entities = new atmEntities())
{
var l = from a in entities.tblREFAchievements select a;
if (!l.Any()) return list;
foreach (var ac in l)
{
list.Add(new Achievement()
{
AchievementCd = ac.AchievementCd,
AchievementDescription = ac.Achievement,
ActiveInd = ac.ActiveInd,
CreatedBy = ac.CreatedBy,
CreatedDt = ac.CreatedDt,
LastModifiedBy = ac.LastModifiedBy,
LastModifiedDt = ac.LastModifiedDt
});
}
}
return list;
}
public IEnumerable<ApplicantSubmitted> GetApplicants(string icno)
{
var list = new List<ApplicantSubmitted>();
if (!string.IsNullOrWhiteSpace(icno))
{
using (var entities = new atmEntities())
{
var exist = from a in entities.tblApplicantSubmiteds where a.NewICNo == icno select a;
if (exist.Any())
foreach (var u in exist)
{
var app = BindingToClass(u);
var edus = GetEducation(app.ApplicantId, app.AcquisitionId);
foreach (var ed in edus)
app.ApplicantEducationSubmittedCollection.Add(ed);
list.Add(app);
}
}
}
return list;
}
public int AddRoles(int userid, string roles)
{
if (userid == 0 && string.IsNullOrWhiteSpace(roles)) return 0;
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUserRoles where a.UserId == userid select a).SingleOrDefault();
if (null != exist)
{
exist.Roles = roles;
entities.SaveChanges();
return 1;
}
// new record
var r = new tblUserRole
{
UserId = userid,
Roles = roles
};
entities.tblUserRoles.Add(r);
if (entities.SaveChanges() > 0)
return r.UserId;
}
return 0;
}
public IEnumerable<ExistingMember> Search(string status, string searchcriteria, string armyno)
{
var list = new List<ExistingMember>();
using (var entities = new atmEntities())
{
var l = from a in entities.tblExistingAtmMembers select a;
if (!string.IsNullOrWhiteSpace(status))
l = l.Where(a => a.ExistingMemberStatusCd == status);
if (!string.IsNullOrWhiteSpace(searchcriteria))
l = l.Where(a => a.CONm.Contains(searchcriteria) || a.ICNOBaru.Contains(searchcriteria));
if (!string.IsNullOrWhiteSpace(armyno))
l = l.Where(a => a.NoTentera == armyno);
foreach (var atm in l)
{
var ea = new ExistingMember()
{
CoId = atm.COID,
ArmyNo = atm.NoTentera,
IdNumber = atm.ICNOBaru,
Name = atm.CONm
};
if (!string.IsNullOrWhiteSpace(atm.ExistingMemberStatusCd))
{
ea.ExistingMemberStatus = new ExistingMemberStatus()
{
Code = atm.tblREFExistingMemberStatu.ExistingMemberStatusCD,
Status = atm.tblREFExistingMemberStatu.ExistingMemberStatus
};
}
list.Add(ea);
}
}
return list;
}
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
if (exist.Password != oldPassword)
return false;
exist.Password = newPassword;
exist.ModifiedDt = DateTime.Now;
if (entities.SaveChanges() > 0)
return true;
}
}
return false;
}
public bool VerifyUser(string username, string guid)
{
if (!string.IsNullOrWhiteSpace(username) && !string.IsNullOrWhiteSpace(guid))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
if (exist.Salt == guid) return true;
}
}
}
return false;
}
public override bool ValidateUser(string username, string password)
{
if (!string.IsNullOrWhiteSpace(username) && !string.IsNullOrWhiteSpace(password))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
var hasspassword = ObjectBuilder.GetObject<ICryptorService>("CryptorService").ComputeHashInString(exist.Salt, password);
if (exist.Password == hasspassword) return true;
}
}
}
return false;
}
public int Update(LoginUser user, List<string> messages)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.UserId == user.UserId select a).SingleOrDefault();
if (null == exist) return 0;
exist.AlternativeEmail = user.AlternativeEmail;
exist.ApplicantId = user.ApplicantId;
exist.Email = user.Email;
exist.ModifiedBy = user.ModifiedBy;
exist.ModifiedDt = DateTime.Now;
exist.ServiceCd = user.ServiceCd;
exist.IsLocked = user.IsLocked;
exist.LastLoginDt = user.LastLoginDt;
exist.FirstTime = user.FirstTime;
exist.FullName = user.FullName;
exist.Salt = user.Salt;
entities.SaveChanges();
return exist.UserId;
}
return 0;
}
public override bool UnlockUser(string userName)
{
if (!string.IsNullOrWhiteSpace(userName))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == userName select a).SingleOrDefault();
if (null != exist)
{
exist.IsLocked = false;
exist.ModifiedDt = DateTime.Now;
return entities.SaveChanges() > 0;
}
}
}
return false;
}
public LoginUser LoadByUserName(string username)
{
if (!string.IsNullOrWhiteSpace(username))
{
using (var entities = new atmEntities())
{
var user = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != user)
{
var usr = new LoginUser
{
AlternativeEmail = user.AlternativeEmail,
ApplicantId = user.ApplicantId,
CreatedBy = user.CreatedBy,
CreatedDt = user.CreatedDt,
LoginId = user.LoginId,
UserId = user.UserId,
ModifiedBy = user.ModifiedBy,
ModifiedDt = user.ModifiedDt,
Salt = user.Password,
ServiceCd = user.ServiceCd,
Status = user.IsLocked.HasValue ? user.IsLocked.Value ? "Aktif" : "Tidak Aktif" : "Aktif",
UserName = user.UserName,
Email = user.Email,
FirstTime = user.FirstTime ?? false,
FullName = user.FullName,
IsLocked = user.IsLocked ?? false,
LastLoginDt = user.LastLoginDt
};
if (!string.IsNullOrWhiteSpace(user.ServiceCd))
{
var svc = (from a in entities.tblREFServices where a.ServiceCd == user.ServiceCd select a).SingleOrDefault();
if (null != svc)
usr.ServiceName = svc.Service;
}
var role = (from a in entities.tblUserRoles where a.UserId == user.UserId select a).SingleOrDefault();
if (null != role)
{
usr.LoginRole = new LoginRole
{
UserId = usr.UserId,
Roles = role.Roles
};
}
return usr;
}
}
}
return null;
}
public IEnumerable<LoginUser> LoadAllByStatus(string status)
{
var list = new List<LoginUser>();
using (var entities = new atmEntities())
{
var islock = status == "Aktif";
var l = from a in entities.tblUsers where a.IsLocked == islock select a;
if (l.Any())
{
foreach (var user in l)
{
var usr = new LoginUser
{
AlternativeEmail = user.AlternativeEmail,
ApplicantId = user.ApplicantId,
CreatedBy = user.CreatedBy,
CreatedDt = user.CreatedDt,
LoginId = user.LoginId,
UserId = user.UserId,
ModifiedBy = user.ModifiedBy,
ModifiedDt = user.ModifiedDt,
Salt = user.Password,
ServiceCd = user.ServiceCd,
Status = user.IsLocked.HasValue ? user.IsLocked.Value ? "Aktif" : "Tidak Aktif" : "Aktif",
UserName = user.UserName,
Email = user.Email,
FirstTime = user.FirstTime ?? false,
FullName = user.FullName,
IsLocked = user.IsLocked ?? false,
LastLoginDt = user.LastLoginDt
};
if (!string.IsNullOrWhiteSpace(user.ServiceCd))
{
var svc = (from a in entities.tblREFServices where a.ServiceCd == user.ServiceCd select a).SingleOrDefault();
if (null != svc)
usr.ServiceName = svc.Service;
}
var role = (from a in entities.tblUserRoles where a.UserId == user.UserId select a).SingleOrDefault();
if (null != role)
{
usr.LoginRole = new LoginRole
{
UserId = usr.UserId,
Roles = role.Roles
};
}
list.Add(usr);
}
}
}
return list;
}
public bool IsExist(string username)
{
if (!string.IsNullOrWhiteSpace(username))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
return true;
}
}
return false;
}
public bool DeleteUser(LoginUser user)
{
if (!string.IsNullOrWhiteSpace(user.LoginId))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == user.LoginId select a).SingleOrDefault();
if (null != exist)
{
entities.tblUsers.Remove(exist);
return entities.SaveChanges() > 0;
}
}
}
return false;
}
public bool CustomResetPassword(string username, string password)
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
exist.Password = password;
exist.ModifiedDt = DateTime.Now;
if (entities.SaveChanges() > 0)
return true;
}
}
return false;
}
public int CreateNewUserVerification(string username, string guid)
{
if (!string.IsNullOrWhiteSpace(username) && !string.IsNullOrWhiteSpace(guid))
{
using (var entities = new atmEntities())
{
var exist = (from a in entities.tblUsers where a.LoginId == username select a).SingleOrDefault();
if (null != exist)
{
exist.Salt = guid;
exist.ModifiedDt = DateTime.Now;
entities.SaveChanges();
return exist.UserId;
}
}
}
return 0;
}
