public int UpdateUserPassword(string username, string oldPassword, string newpassword, out string mesg)
{
mesg = string.Empty;
result_affected = 0;
string encrypted_password = new Common().Encrypt(oldPassword, TDM_KEY);
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var qry = context.tb_userApps.SingleOrDefault(x => x.UserName == username && x.Password == encrypted_password);
if (qry != null)
{
qry.Password = new Common().Encrypt(newpassword, TDM_KEY);
result_affected = context.SaveChanges();
}
else
{
mesg = "username and oldpassword not found";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result_affected);
}
public IEnumerable <WorkflowSettingHeader> GetHeader()
{
List <WorkflowSettingHeader> ls = new List <WorkflowSettingHeader>();
using (TDMDBEntities context = new TDMDBEntities())
{
var qry = context.tb_workflowSettingHdr
.Join(context.tb_Master, w => w.TypeID, m => m.Id, (w, m) => new { w, m })
.Where(x => x.w.IsActive == true && x.m.Category == MyEnums.enumMaster.DocumentType.ToString())
.Select(n => new
{
n.w.Id,
n.w.TypeID,
n.w.Version,
n.w.IsActive,
n.m.Value,
n.w.ApprovalLevel
});
foreach (var item in qry)
{
ls.Add(new WorkflowSettingHeader
{
Id = item.Id,
TypeID = item.TypeID,
TypeName = item.Value,
Version = item.Version,
IsActive = (bool)item.IsActive,
ApprovalLevel = item.ApprovalLevel
});
}
}
return(ls);
}
public int Update(RoleModel model, out string errMsg)
{
errMsg = string.Empty;
result_affected = 0;
try
{
using (TDMDBEntities context = new TDMDBEntities())
{
var detil = context.tb_role.SingleOrDefault(x => x.Id == model.Id);
if (detil != null)
{
detil.RoleName = model.RoleName;
detil.ModifiedBy = model.ModifiedBy;
detil.ModofiedDate = DateTime.Now;
result_affected = context.SaveChanges();
}
}
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(result_affected);
}
public IEnumerable <UserAppsModel> ListActiveUser()
{
List <UserAppsModel> ls = new List <UserAppsModel>();
using (TDMDBEntities context = new TDMDBEntities())
{
var qry = context.tb_userApps
.Join(context.tb_employee, u => u.EmployeeId, e => e.EmpID, (u, e) => new { u, e })
.Where(x => x.u.IsExpired == false)
.Select(s => new
{
s.u.Id,
s.u.UserName,
s.u.Password,
s.u.IsExpired,
s.u.EmployeeId,
s.e.EmpName
});
foreach (var item in qry)
{
ls.Add(new UserAppsModel
{
Id = item.Id,
UserName = item.UserName,
Password = item.Password,
IsExpired = item.IsExpired,
EmployeeId = item.EmployeeId,
EmployeeName = item.EmpName
});
}
}
return(ls);
}
public int Update(EmployeeModel model, out string errMsg)
{
errMsg = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var exist = context.tb_employee.FirstOrDefault(x => x.EmpID == model.Id);
exist.EmpName = model.EmpName;
exist.EmpNo = model.EmpNo;
exist.Dept = model.Dept;
exist.IsActive = model.IsActive;
exist.RoleApps = model.RoleApps;
exist.ModifiedBy = model.ModifiedBy;
exist.ModifiedDate = model.ModifiedDate;
result_affected = context.SaveChanges();
}
catch (Exception ex)
{
errMsg = ex.Message == null ? ex.InnerException.Message : ex.Message;
}
}
return(result_affected);
}
public int Update(UserAppsModel model, out string errMsg)
{
errMsg = MyEnums.enumStatus.SUCCESS.ToString();
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var detil = context.tb_userApps.FirstOrDefault(x => x.Id == model.Id);
if (detil != null)
{
detil.UserName = model.UserName;
detil.EmployeeId = model.EmployeeId;
detil.IsExpired = model.IsExpired;
detil.ModifiedBy = model.ModifiedBy;
detil.ModifiedDate = model.ModifiedDate;
result_affected = context.SaveChanges();
}
}
catch (Exception ex)
{
errMsg = ex.Message;
}
}
return(result_affected);
}
public List <WorklistArchiveModel> GetCompleteSPK(string creator)
{
List <WorklistArchiveModel> ls = new List <WorklistArchiveModel>();
using (TDMDBEntities context = new TDMDBEntities())
{
var qry = context.tb_Worklist_Archive
.Include("tb_Master")
.Include("tb_employee")
.Include("tb_role")
.Where(x => x.IsCompleted == true && x.tb_Master.Category == MyEnums.enumMaster.DocumentType.ToString() && x.CreatedBy == creator);
foreach (var item in qry)
{
ls.Add(new WorklistArchiveModel
{
Id = (Int32)item.Id,
DocType = item.DocType,
DocTypeDesc = item.tb_Master.Value,
DocId = item.DocId,
Status = item.Status,
StartDate = item.StartDate,
RespondDate = item.RespondDate,
LastActorDesc = item.tb_employee.EmpName,
Actioner = item.Actioner,
LastRoleDesc = item.tb_role.RoleName,
LastLevel = (int)item.LastLevel
});
}
}
return(ls);
}
public List <WorklistModel> GetSPKStatus(string creator)
{
List <WorklistModel> colls = new List <WorklistModel>();
using (TDMDBEntities context = new TDMDBEntities())
{
var _qryPending = context.tb_Worklist
.Include("tb_role")
.Include("tb_Master")
.Where(x => x.CreatedBy == creator && x.tb_Master.Category == MyEnums.enumMaster.DocumentType.ToString());
foreach (var item in _qryPending)
{
colls.Add(new WorklistModel
{
Id = (Int32)item.Id,
DocType = item.DocType,
DocTypeDesc = item.tb_Master.Value,
Status = item.Status,
StartDate = item.StartDate,
RespondDate = item.RespondDate,
NextApprover = item.NextApprover == null ? 0 : (int)item.NextApprover,
NextApproverDesc = item.tb_role.RoleName,
CurrLevel = item.CurrLevel,
DocId = item.DocId
});
}
}
return(colls);
}
public SPKHeaderModel Detail(int SPKId)
{
SPKHeaderModel _spk = new SPKHeaderModel();
using (TDMDBEntities context = new TDMDBEntities())
{
var _qrySPK = context.tb_spkHdr.Find(SPKId);
if (_qrySPK != null)
{
imap = config.CreateMapper();
_spk = imap.Map <tb_spkHdr, SPKHeaderModel>(_qrySPK);
// var _qryEquipment = context.tb_KetTambahan.Where(x => x.SPKId == SPKId).FirstOrDefault();
var _qryAdditional = context.tb_PerlTambahan.Where(x => x.SPKId == SPKId);
var _qryAttachment = context.tb_Attachment.Where(x => x.DocId == SPKId);
//if (_qryEquipment!=null)
//{
// _spk.spkequipment = new SPKEquipmentModel();
// _spk.spkequipment.Id = _qryEquipment.Id;
// _spk.spkequipment.SPKId = _qryEquipment.SPKId;
// _spk.spkequipment.IsKaroseri = _qryEquipment.IsKaroseri;
// _spk.spkequipment.Karoseri = _qryEquipment.Karoseri;
// _spk.spkequipment.IsOnTheRoad = _qryEquipment.IsOnTheRoad;
// _spk.spkequipment.IsOffTheRoad = _qryEquipment.IsOffTheRoad;
// _spk.spkequipment.IsChooseNo = _qryEquipment.IsChooseNo;
// _spk.spkequipment.PlatNo = _qryEquipment.PlatNo;
//}
_spk.lsadditional = new List <SPKAdditionalModel>();
foreach (var item in _qryAdditional)
{
_spk.lsadditional.Add(new SPKAdditionalModel
{
Id = item.Id
,
SPKId = item.SPKId
,
No = (int)item.No
,
Additional = item.AdditionalItem
});
}
_spk.lsspkattachment = new List <SPKAttachmentModel>();
foreach (var att in _qryAttachment)
{
_spk.lsspkattachment.Add(new SPKAttachmentModel
{
Id = (Int32)att.Id,
DocId = att.DocId,
AttachmentName = att.AttachmentName,
DocType = (int)att.DocType
});
}
}
}
return(_spk);
}
public static string GetRoleAppsName(int id)
{
string roleName = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
roleName = context.tb_Master.SingleOrDefault(x => x.Id == id && x.Category == MyEnums.enumMaster.RoleApps.ToString()).Value;
}
return(roleName);
}
public int GetEmployeeIDByUserName(string userName)
{
int _empId = 0;
using (TDMDBEntities context = new TDMDBEntities())
{
var qry = context.tb_userApps.Single(x => x.UserName == userName);
_empId = qry.EmployeeId;
}
return(_empId);
}
public RoleModel FindById(int Id)
{
RoleModel model = new RoleModel();
using (TDMDBEntities context = new TDMDBEntities())
{
IMapper imap = config.CreateMapper();
tb_role role = context.tb_role.FirstOrDefault(x => x.Id == Id);
model = imap.Map <tb_role, RoleModel>(role);
}
return(model);
}
public EmployeeModel FindById(int Id)
{
EmployeeModel emp = new EmployeeModel();
using (TDMDBEntities context = new TDMDBEntities())
{
var ent = context.tb_employee.SingleOrDefault(x => x.EmpID == Id);
if (ent != null)
{
emp = MapToModel(ent);
}
}
return(emp);
}
public int FindByValue(string _val, string _category)
{
int _retId = 0;
using (TDMDBEntities context = new TDMDBEntities())
{
var _qry = context.tb_Master.Where(x => x.Value == _val && x.Category == _category).SingleOrDefault();
if (_qry != null)
{
_retId = _qry.Id;
}
}
return(_retId);
}
public int Update(WorkflowSettingHeader wfHdr, List <WorkflowSettingModel> lsdetails, out string errMsg)
{
errMsg = string.Empty;
tb_workflowSetting wfdetil = new tb_workflowSetting();
imap = config.CreateMapper();
using (TDMDBEntities context = new TDMDBEntities())
{
var _exist = context.tb_workflowSettingHdr.SingleOrDefault(x => x.Id == wfHdr.Id);
using (var transaction = context.Database.BeginTransaction())
{
try
{
_exist.TypeID = wfHdr.TypeID;
_exist.Version = wfHdr.Version;
_exist.ApprovalLevel = wfHdr.ApprovalLevel;
_exist.ModifiedBy = "SYSTEM";
_exist.ModifiedDate = DateTime.Now;
result_affected += context.SaveChanges();
var toBeDelete = context.tb_workflowSetting.Where(x => x.HeaderID == wfHdr.Id);
if (toBeDelete != null)
{
context.tb_workflowSetting.RemoveRange(toBeDelete);
context.SaveChanges();
}
foreach (var item in lsdetails)
{
wfdetil = imap.Map <WorkflowSettingModel, tb_workflowSetting>(item);
wfdetil.HeaderID = wfHdr.Id;
wfdetil.CreatedBy = "SYSTEM";
wfdetil.CreatedDate = DateTime.Now;
context.tb_workflowSetting.Add(wfdetil);
result_affected += context.SaveChanges();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
errMsg = ex.Message;
}
}
}
return(result_affected);
}
public int Update(UserAppsModel model, out string errMsg)
{
StringBuilder sb = new StringBuilder();
int errctr = 0;
errMsg = MyEnums.enumStatus.SUCCESS.ToString();
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
//cek is any same username
var _u = context.tb_userApps.SingleOrDefault(x => x.UserName == model.UserName);
if (_u != null)
{
if (model.Id != _u.Id)
{
sb.Append("User Name sudah terpakai, silahkan mengganti dengan yang lain");
errctr += 1;
}
}
if (errctr <= 0)
{
var detil = context.tb_userApps.FirstOrDefault(x => x.Id == model.Id);
if (detil != null && ValidateBeforeUpdate(model, out errMsg))
{
detil.UserName = model.UserName;
detil.EmployeeId = model.EmployeeId;
detil.IsExpired = model.IsExpired;
detil.ModifiedBy = model.ModifiedBy;
detil.ModifiedDate = model.ModifiedDate;
result_affected = context.SaveChanges();
}
}
else
{
errMsg = sb.ToString();
}
}
catch (Exception ex)
{
errMsg = ex.Message;
}
}
return(result_affected);
}
public IEnumerable <RoleModel> List()
{
List <RoleModel> ls = new List <RoleModel>();
using (TDMDBEntities context = new TDMDBEntities())
{
var entity = context.tb_role;
foreach (var item in entity)
{
IMapper imap = config.CreateMapper();
var mapped = imap.Map <tb_role, RoleModel>(item);
ls.Add(mapped);
}
}
return(ls);
}
public int DeleteAttachment(int id)
{
result_affected = 0;
using (TDMDBEntities context = new TDMDBEntities())
{
var _remove = context.tb_Attachment.SingleOrDefault(x => x.Id == id);
try
{
context.tb_Attachment.Remove(_remove);
result_affected = context.SaveChanges();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(result_affected);
}
public int Insert(EmployeeModel model, out string errMsg)
{
errMsg = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
tb_employee tbEmp = MapToEntity(model);
context.tb_employee.Add(tbEmp);
result_affected = context.SaveChanges();
}
catch (Exception ex)
{
errMsg = ex.Message == null ? ex.InnerException.Message : ex.Message;
}
}
return(result_affected);
}
public SPKAttachmentModel GetDocAttachment(int id)
{
SPKAttachmentModel att = new SPKAttachmentModel();
using (TDMDBEntities context = new TDMDBEntities())
{
var _qry = context.tb_Attachment.SingleOrDefault(x => x.Id == id);
if (_qry != null)
{
att.Id = (Int32)_qry.Id;
att.AttachmentName = _qry.AttachmentName;
att.Attachment = _qry.Attachment;
att.DocId = _qry.DocId;
att.DocType = _qry.DocType == null ? 0 : (int)_qry.DocType;
}
}
return(att);
}
public WorkflowSettingHeader Detail(int id)
{
WorkflowSettingHeader detil = new WorkflowSettingHeader();
IMapper imap = config.CreateMapper();
try
{
using (TDMDBEntities context = new TDMDBEntities())
{
var _details = context.tb_workflowSettingHdr.SingleOrDefault(x => x.Id == id);
detil = imap.Map <tb_workflowSettingHdr, WorkflowSettingHeader>(_details);
var qry_setting_detail = context.tb_workflowSetting.Where(x => x.HeaderID == id);
if (qry_setting_detail.Count() > 0)
{
detil.ls_details = new List <WorkflowSettingModel>();
}
foreach (var item in qry_setting_detail)
{
WorkflowSettingModel md = new WorkflowSettingModel();
md.HeaderID = item.HeaderID;
md.ApprovalLevel = md.Order = item.ApprovalLevel;
md.Actor = item.Actor;
md.ActorID = item.ActorID;
md.Action = item.ActorID;
md.action_desc = item.action_desc;
if (item.Actor == "ROLE")
{
int _actorid = Convert.ToInt32(item.ActorID);
md.RoleDesc = context.tb_role.SingleOrDefault(x => x.Id == _actorid).RoleName;
}
detil.ls_details.Add(md);
}
}
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
return(detil);
}
public string GetNextActorId(int doctype, int level)
{
string _actorId = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
var _qryHdrId = context.tb_workflowSettingHdr.SingleOrDefault(x => x.TypeID == doctype && x.IsActive == true);
if (_qryHdrId != null)
{
int _hdrId = _qryHdrId.Id;
var _qryActor = context.tb_workflowSetting.SingleOrDefault(x => x.HeaderID == _hdrId && x.ApprovalLevel == level);
if (_qryActor != null)
{
_actorId = _qryActor.ActorID;
}
}
}
return(_actorId);
}
public int Insert(UserAppsModel model, out string errMsg)
{
errMsg = MyEnums.enumStatus.SUCCESS.ToString();
tb_userApps tentity = MapToEntity(model);
try
{
using (TDMDBEntities context = new TDMDBEntities())
{
context.tb_userApps.Add(tentity);
result_affected = context.SaveChanges();
}
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(result_affected);
}
public bool CheckIsWorkflowCompleted(int doctype, int level)
{
bool _IsCompleted = false;
level = level + 1;
using (TDMDBEntities context = new TDMDBEntities())
{
var _qryHdrId = context.tb_workflowSettingHdr.SingleOrDefault(x => x.TypeID == doctype && x.IsActive == true);
if (_qryHdrId != null)
{
int _hdrId = _qryHdrId.Id;
var _qryActor = context.tb_workflowSetting.SingleOrDefault(x => x.HeaderID == _hdrId && x.ApprovalLevel == level);
if (_qryActor == null)
{
_IsCompleted = true;
}
}
}
return(_IsCompleted);
}
public int Delete(int Id, out string mesg)
{
mesg = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var remove = context.tb_userRole.SingleOrDefault(x => x.Id == Id);
if (remove != null)
{
context.tb_userRole.Remove(remove);
result_Affected = context.SaveChanges();
}
}
catch (Exception ex)
{
mesg = ex.Message;
}
}
return(result_Affected);
}
public int Delete(int Id, out string errMsg)
{
errMsg = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var exist = context.tb_employee.FirstOrDefault(x => x.EmpID == Id);
if (exist != null)
{
context.tb_employee.Remove(exist);
result_affected = context.SaveChanges();
}
}
catch (Exception ex)
{
errMsg = ex.Message == null ? ex.InnerException.Message : ex.Message;
}
}
return(result_affected);
}
public int ValidateBeforSave(UserAppsModel usr, out string mesg)
{
int result_affected = 0;
mesg = string.Empty;
StringBuilder sb = new StringBuilder();
if (usr.UserName == string.Empty)
{
sb.Append("User Name tidak boleh kosong /n");
}
if (usr.EmployeeId == 0)
{
sb.Append("Employee ID tidak boleh kosong /n");
}
if (usr.Password == string.Empty)
{
sb.Append("Password tidak boleh kosong /n");
}
//cek is any same username
using (TDMDBEntities context = new TDMDBEntities())
{
var _u = context.tb_userApps.SingleOrDefault(x => x.UserName == usr.UserName);
if (_u != null)
{
sb.Append("User Name sudah terpakai, silahkan mengganti dengan yang lain");
}
}
mesg = sb.ToString();
if (mesg == string.Empty)
{
usr.Password = new Common().Encrypt(usr.Password, TDM_KEY);
string sSql = "Insert INTO tb_UserApps (UserName, Password, IsExpired, EmployeeId, CreatedBy)"
+ "VALUES (@UserName, @Password, @IsExpired, @EmployeeId, @CreatedBy)";
result_affected = InsertOrUpdate <UserAppsModel>(sSql, usr);
}
return(result_affected);
}
public UserAppsModel Authenticate(string username, string password, out string mesg)
{
UserAppsModel model = new UserAppsModel();
mesg = string.Empty;
using (TDMDBEntities context = new TDMDBEntities())
{
var _q = context.tb_userApps.SingleOrDefault(x => x.UserName == username);
if (_q != null)
{
if (password == new Common().Decrypt(_q.Password, TDM_KEY))
{
var qry = context.tb_userApps.Join(context.tb_employee, u => u.EmployeeId, e => e.EmpID, (u, e) => new { u, e }).Select(s => new
{
s.u.UserName,
s.u.Id,
s.u.EmployeeId,
s.e.EmpName,
s.e.RoleApps,
})
.SingleOrDefault(x => x.UserName == username);
model.Id = qry.Id;
model.UserName = qry.UserName;
model.EmployeeId = qry.EmployeeId;
model.EmployeeName = qry.EmpName;
model.Role = (int)qry.RoleApps;
}
else
{
mesg = "Password Salah";
}
}
else
{
mesg = "UserName tidak ditemukan";
}
}
return(model);
}
public UserRoleModel FindByID(int Id)
{
UserRoleModel urm = new UserRoleModel();
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var entity = context.tb_userRole.SingleOrDefault(x => x.Id == Id);
if (entity != null)
{
IMapper imap = config.CreateMapper();
urm = imap.Map <tb_userRole, UserRoleModel>(entity);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(urm);
}
public UserAppsModel FindById(int Id)
{
UserAppsModel usr = new UserAppsModel();
using (TDMDBEntities context = new TDMDBEntities())
{
try
{
var q = context.tb_userApps.SingleOrDefault(x => x.Id == Id);
if (q != null)
{
usr = MapToModel(q);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
return(usr);
}
