//Amended By Jason 03/07/2019
public List <CommonDropDown> GetCRMProfileByTypeEntity(string profileType, string entityType, string IndividualCorporate, string textFilter = null)
{
using (var db = new OrixDBEntities())
{
var data = from profile in db.crmtb_client_entity_profile
join profileC in db.crmtb_client_mas on profile.ep_client_cod equals profileC.cm_client_cod
join profileD in db.crmtb_client_id_mas on profileC.cm_client_cod equals profileD.im_client_cod
where profile.ep_profiletype_cod == profileType && profile.ep_entity_cod == entityType &&
profileC.cm_sta_ind == "O" &&
(string.IsNullOrEmpty(textFilter) ? true : profileC.cm_client_nam.StartsWith(textFilter)) &&
(IndividualCorporate == clsVariables.Individual ? profileC.cm_typ_cod == "C0001" :
IndividualCorporate == clsVariables.Corporate ? new string[] { "C0002", "C0003" }.Contains(profileC.cm_typ_cod) :
new string[] { "C0001", "C0002", "C0003" }.Contains(profileC.cm_typ_cod)
)
select new { profileC.cm_client_nam, profileC.cm_client_cod, profileD.im_id_num };
List <CommonDropDown> result = new List <CommonDropDown>();
result = data.Select(x => new CommonDropDown()
{
label = x.cm_client_nam.Replace("\"", """).Replace("\'", "'").Replace("<", "〈").Replace(">", "〉") + " (" + x.im_id_num + ")",
value = x.cm_client_cod
}).ToList();
return(result);
}
}
public List <DepartmentViewModel> getDepartmentList(string selectedAddress)
{
glog.Debug("getDepartmentList: Entry");
using (var db = new OrixDBEntities())
{
try
{
var departmentList = db.crmtb_client_dept.Where(x =>
x.cd_add_cod == x.crmtb_client_address_mas.am_add_cod &&
x.crmtb_client_address_mas.am_add_cod == selectedAddress &&
x.cd_sta_ind == "O").ToList().OrderBy(x => x.cd_ref_num);
List <DepartmentViewModel> deptModel = new List <DepartmentViewModel>();
deptModel = departmentList.Select(x => new DepartmentViewModel()
{
cd_ref_num = x.cd_ref_num,
cd_dept_desc = x.cd_dept_desc
}).ToList();
glog.Debug("getDepartmentList: Exit");
return(deptModel);
}
catch (Exception ex)
{
glog.Error("getDepartmentList Exception: " + ex.Message);
return(new List <DepartmentViewModel>());
}
}
}
//temp
public IEnumerable <CommonDropDown> GetSupplierFromCRM()
{
using (var db = new OrixDBEntities())
{
var supplier = db.crmtb_client_entity_profile.AsNoTracking().Where(f => f.ep_sta_ind == "O" && f.ep_profiletype_cod == "PT002" && f.ep_entity_cod == "EN002" &&
f.crmtb_client_mas.cm_sta_ind == "O").OrderBy(f => f.crmtb_client_mas.cm_client_nam).Select(f => new CommonDropDown()
{
value = f.ep_client_cod,
label = f.crmtb_client_mas.cm_client_nam.Replace("\"", """).Replace("\'", "'").Replace("<", "〈").Replace(">", "〉")
}).ToList();
return(supplier);
}
}
public List <Security_ConstructionEquipModel> GetIndustrial(string cm_client_cod)
{
using (var mdb = new MainDbContext())
using (var db = new OrixDBEntities())
{
try
{
var res = (from s in mdb.Security_IndustrialEquip // outer sequence
join st in mdb.Security_IndustrialEquipCustomer //inner sequence
on s.ID equals st.MasterID // key selector
where s.Status == "O" && st.Status == "O" &&
st.Customer == cm_client_cod
select s).ToList();
var result = (from s in res
join cabm in db.cfstb_asset_brand_mas
on s.EquipBrand equals cabm.cfs_brand_code
join camm in db.cfstb_asset_model_mas
on s.EquipModel equals camm.cfs_model_code
where cabm.cfs_brand_sta_ind != "X" && camm.cfs_model_sta_ind != "X"
orderby s.EquipBrand, s.EquipModel
select new { s, cabm, camm }).ToList();
result.ForEach(x =>
{
x.s.EquipBrand = x.cabm.cfs_brand_name;
x.s.EquipModel = x.camm.cfs_model_name;
});
var finalres = result.Select(x => new Security_ConstructionEquipModel()
{
ChargeDate = x.s.ChargeDate != null ? x.s.ChargeDate.Value.ToShortDateString() : "",
ChargeNumber = x.s.ChargeNumber,
EngineNumber = x.s.EngineNumber,
EquipBrand = x.s.EquipBrand,
EquipDesc = x.s.EquipDesc,
EquipModel = x.s.EquipModel,
ID = x.s.ID,
SecuredAmount = x.s.SecuredAmount != null ? x.s.SecuredAmount.Value.ToString("0.00") : "0.00",
SerialNumber = x.s.SerialNumber,
YearOfManufacture = x.s.YearOfManufacture
}).ToList();
return(finalres);
}
catch (Exception e)
{
return(new List <Security_ConstructionEquipModel>());
}
}
}
public string GetCRMProfileByClientId(string Id)
{
using (var db = new OrixDBEntities())
{
var data = db.crmtb_client_mas.AsNoTracking().Where(w => w.cm_sta_ind == "O" &&
w.cm_client_cod == Id
).OrderBy(o => o.cm_client_nam).ToList();
var result = string.Empty;
if (data != null)
{
result = data.Select(s => s.cm_client_nam).FirstOrDefault().ToString();
}
return(result);
}
}
public string getNricFinPassportType(string selected)
{
glog.Debug("getNricFinPassportType: Entry");
string NricType = null;
using (var db = new OrixDBEntities())
{
try
{
IEnumerable <crmtb_client_id_mas> model = db.crmtb_client_id_mas.Where(x =>
x.crmtb_client_mas.cm_client_cod == selected &&
x.im_sta_ind == "O" &&
(x.im_id_typ == "NRIC" || x.im_id_typ == "FIN" || x.im_id_typ == "PASSPORT")).ToList();
if (model != null)
{
if (model.Any(x => x.im_id_typ == "NRIC"))
{
NricType = model.Where(x => x.im_id_typ == "NRIC").Select(x => x.im_id_num).FirstOrDefault();
}
else if (model.Any(x => x.im_id_typ == "FIN" || x.im_id_typ == "PASSPORT"))
{
NricType = model.Where(x => x.im_id_typ == "FIN").Select(x => x.im_id_num).FirstOrDefault();
}
else
{
NricType = model.Where(x => x.im_id_typ == "PASSPORT").Select(x => x.im_id_num).FirstOrDefault();
}
}
else
{
NricType = "";
}
glog.Debug("getNricFinPassportType: Exit");
return(NricType);
}
catch (Exception ex)
{
NricType = string.Empty;
glog.Error("getNricFinPassportType Exception: " + ex.Message);
return(NricType);
}
}
}
public ClientContactMasViewModel GetClientContactMasByCustomerConPerson(string strCustomerConPerson)
{
glog.Debug("GetClientContactMasByCustomerConPerson: Entry");
using (var db = new OrixDBEntities())
{
try
{
var result = db.Database.SqlQuery <ClientContactMasViewModel>(
"exec GetCrmtbClientContactMasByCustomerConPerson @CustomerConPerson",
new SqlParameter("@CustomerConPerson", string.IsNullOrWhiteSpace(strCustomerConPerson) ? "" : strCustomerConPerson)).FirstOrDefault();
glog.Debug("GetClientContactMasByCustomerConPerson: Exit");
return(result);
}
catch (Exception ex)
{
glog.Error("GetClientContactMasByCustomerConPerson Exception: " + ex.Message);
throw;
}
}
}
public string getClientNameByCode(string code)
{
glog.Debug("getClientNameByCode: Entry");
using (var db = new OrixDBEntities())
{
try
{
var clientname = db.crmtb_client_mas.Where(x => x.cm_sta_ind == "O" &&
x.cm_client_cod == code).Select(x => x.cm_client_nam).FirstOrDefault();
glog.Debug("getClientNameByCode: Exit");
return(clientname);
}
catch (Exception ex)
{
glog.Error("getClientNameByCode Exception: " + ex.Message);
return("");
}
}
}
public List <AddressViewModel> getAddress(string selected, string IndividualCorporate)
{
glog.Debug("getAddress: Entry");
using (var db = new OrixDBEntities())
{
try
{
var address = db.crmtb_client_address_mas.Where(x => x.am_ref_cod == x.crmtb_client_mas.cm_client_cod &&
x.crmtb_client_mas.cm_client_cod == selected &&
x.am_sta_ind == "O"
).ToList();
//var sortAddress = (IndividualCorporate == "Corporate" ? address.OrderBy(o => o.am_add_ind == "R") : address.OrderBy(o => o.am_bill_ind == "Y")).Select(x => new AddressViewModel()
var sortAddress = (IndividualCorporate == clsVariables.Corporate ?
address.Where(x => x.am_add_ind == "R").Concat(address.Where(x => x.am_add_ind != "R")).ToList() :
address = address.Where(x => x.am_bill_ind == "Y").Concat(address.Where(x => x.am_bill_ind != "Y")).ToList())
.Select(x => new AddressViewModel()
{
am_add_ind = x.am_add_ind,
am_bill_ind = x.am_bill_ind,
AddressId = x.am_add_cod,
Address = (x.am_blk_typ == "BLK" ? (x.am_blk_typ + " ") : string.Empty) +
(!string.IsNullOrEmpty(x.am_blk_num) ? (x.am_blk_num + " ") : string.Empty) +
(!string.IsNullOrEmpty(x.am_street_nam) ? (x.am_street_nam + " ") : string.Empty) +
(!string.IsNullOrEmpty(x.am_floor_num) ? ("#" + x.am_floor_num + " ") : " ") +
(!string.IsNullOrEmpty(x.am_unit_num) ? ("-" + x.am_unit_num + " ") : " ") +
(!string.IsNullOrEmpty(x.am_build_nam) ? x.am_build_nam : string.Empty) +
(!string.IsNullOrEmpty(x.am_postal_cod) ? ("," + x.am_postal_cod + " ") : string.Empty) +
(!string.IsNullOrEmpty(x.am_country_cod) ? (db.gentb_country_mas.Where(c => c.cm_country_cod == x.am_country_cod && c.cm_sta_ind == "O").Select(c => c.cm_country_nam).FirstOrDefault()) : string.Empty)
}).ToList();
glog.Debug("getAddress: Exit");
return(sortAddress);
}
catch (Exception ex)
{
glog.Error("getAddress Exception: " + ex.Message);
return(new List <AddressViewModel>());
}
}
}
public List <SelectListItem> GetCountry()
{
glog.Debug("GetCountry: Entry");
using (var db = new OrixDBEntities())
{
try
{
var country = db.gentb_country_mas.Where(x => x.cm_sta_ind == "O").Select(p => new SelectListItem()
{
Value = p.cm_country_cod,
Text = p.cm_country_nam
}).ToList();
glog.Debug("GetCountry: Exit");
return(country);
}
catch (Exception ex)
{
glog.Error("GetCountry Exception: " + ex.Message);
return(new List <SelectListItem>());
}
}
}
public string getContactPager(string selectedContactPerson)
{
glog.Debug("getContactPager: Entry");
using (var db = new OrixDBEntities())
{
try
{
var contactPagerList = db.crmtb_client_contact_det.Where(x => x.cd_con_cod == selectedContactPerson &&
x.cd_sta_ind == "O" && x.cd_typ_cod == "PAGER").Select(x =>
x.cd_typ_val
);
var contactPager = string.Join(";", contactPagerList);
glog.Debug("getContactPager: Exit");
return(contactPager);
}
catch (Exception ex)
{
glog.Error("getContactPager Exception: " + ex.Message);
return("");
}
}
}
public string getRocUenType(string selected)
{
glog.Debug("getRocUenType: Entry");
string rocUenType = string.Empty;
using (var db = new OrixDBEntities())
{
try
{
IEnumerable <crmtb_client_id_mas> model = db.crmtb_client_id_mas.Where(x =>
x.im_client_cod == selected &&
x.im_sta_ind == "O" && (x.im_id_typ == "ROC" || x.im_id_typ == "UEN")).ToList();
if (model.Any())
{
if (model.Any(x => x.im_id_typ == "ROC"))
{
rocUenType = model.Where(x => x.im_id_typ == "ROC").Select(x => x.im_id_num).FirstOrDefault();
}
else
{
rocUenType = model.Where(x => x.im_id_typ == "UEN").Select(x => x.im_id_num).FirstOrDefault();
}
}
else
{
rocUenType = "";
}
glog.Debug("getRocUenType: Exit");
return(rocUenType);
}
catch (Exception ex)
{
rocUenType = string.Empty;
glog.Error("getRocUenType Exception: " + ex.Message);
return(rocUenType);
}
}
}
//TableFieldInd -> is the field in a table? If yes, then replace special characters "<" and ">"
public IEnumerable <CommonDropDown> GetVehicleModelByVehicleMake(string code, Boolean TableFieldInd)
{
using (var db = new OrixDBEntities())
{
var vehModel = db.cfstb_asset_model_chd.AsNoTracking().Where(f => f.cfstb_asset_model_mas.cfs_model_sta_ind == "O" && f.cfs_chd_model_sta_ind == "O" && f.cfs_chd_brand_code == code).OrderBy(f => f.cfstb_asset_model_mas.cfs_model_name).ToList();
var result = vehModel.Select(f => new CommonDropDown()
{
value = f.cfstb_asset_model_mas.cfs_model_code,
label = f.cfstb_asset_model_mas.cfs_model_name
}).ToList();
if (TableFieldInd)
{
result = vehModel.Select(f => new CommonDropDown()
{
value = f.cfstb_asset_model_mas.cfs_model_code,
label = f.cfstb_asset_model_mas.cfs_model_name.Replace("\"", """).Replace("\'", "'").Replace("<", "〈").Replace(">", "〉")
}).ToList();
}
return(result);
}
}
public List <ContactPersonModel> getContactPerson(string selectedDepartment)
{
glog.Debug("getContactPerson: Entry");
using (var db = new OrixDBEntities())
{
try
{
var contactPerson = db.crmtb_client_contact_mas.Where(x => x.cm_ref_cod == x.crmtb_client_dept.cd_ref_num &&
x.crmtb_client_dept.cd_ref_num == selectedDepartment &&
x.cm_sta_ind == "O").OrderBy(x => x.cm_con_typ == "M").Select(x => new ContactPersonModel()
{
Contact = (!string.IsNullOrEmpty(x.cm_title) ? (x.cm_title + " ") : string.Empty) + x.cm_full_nam,
Value = x.cm_con_cod
}).ToList();
glog.Debug("getContactPerson: Exit");
return(contactPerson);
}
catch (Exception ex)
{
glog.Error("getContactPerson Exception: " + ex.Message);
return(new List <ContactPersonModel>());
}
}
}
//TableFieldInd -> is the field in a table? If yes, then replace special characters "<" and ">"
public IEnumerable <CommonDropDown> GetBrand(Boolean TableFieldInd)
{
using (var db = new OrixDBEntities())
{
var brand = db.cfstb_asset_brand_mas.AsNoTracking().Where(f => f.cfs_brand_sta_ind != "X" && f.cfs_brand_vehicle_ind == "N" && f.cfs_brand_code != "").OrderBy(f => f.cfs_brand_name).ToList();
var result = brand.Select(f => new CommonDropDown()
{
value = f.cfs_brand_code,
label = f.cfs_brand_name
}).ToList();
if (TableFieldInd)
{
result = brand.Select(f => new CommonDropDown()
{
value = f.cfs_brand_code,
label = f.cfs_brand_name.Replace("\"", """).Replace("\'", "'").Replace("<", "〈").Replace(">", "〉")
}).ToList();
}
return(result);
}
}
public ActionResult Login(Sys_Users model)
{
glog.Debug("HttpPost Login: Entry");
if (!ModelState.IsValid) //Checks if input fields have the correct format
{
glog.Error("Invalid UserName/Password. Please try again.");
return(Json(new
{
NotificationTitle = clsGlobal.SwalTitle_Fail,
NotificationContent = "Invalid UserName/Password. Please try again.",
NotificationType = clsGlobal.SwalType_Warning
}));
}
try
{
using (var db = new MainDbContext())
{
////Store username and password
string getEmpCode = "";
clsGlobal.LoginID = model.Email;
clsGlobal.Password = model.Password;
////Store username and password
//glog.Debug(db);
//glog.Debug("emailCheck: Entry");
//var emailCheck = db.Sys_Users.FirstOrDefault(u => u.Email == clsGlobal.LoginID.ToString().Trim()); // change to clscommon
//glog.Debug("emailCheck: Exit");
if (EthozCapital.CustomLibraries.clsGlobal.GetConnection() == "")
{
return(Json(new
{
NotificationTitle = clsGlobal.SwalTitle_Error,
NotificationContent = "Please contact MIS, error: Invalid User",
NotificationType = clsGlobal.SwalType_Error
}));
}
;
//Retrieve em_emp_cod from CRM OrixDB using em_sybase_id
//string getEmpCode = EthozCapital.CustomLibraries.clsUserAccessLogin.GetEmpCode(clsGlobal.LoginID.ToString().Trim());
using (var OrixDB = new OrixDBEntities())
{
//getEmpCode = "S0497";
var allowedStatus = new[] { "N", "E" };
var EmpCode = OrixDB.hr_emp_mas.FirstOrDefault(u => u.em_sybase_id == clsGlobal.LoginID.ToString().Trim() && allowedStatus.Contains(u.em_staff_status));
if (EmpCode == null)
{
return(Json(new
{
NotificationTitle = clsGlobal.SwalTitle_Error,
NotificationContent = "Please contact MIS, error: Invalid User",
NotificationType = clsGlobal.SwalType_Error
}));
}
else
{
getEmpCode = Convert.ToString(EmpCode.em_emp_cod);
}
}
//Pass em_emp_cod to here
//Check User exist in Orix DB or user group not assigned
//if (!EthozCapital.CustomLibraries.clsUserAccessLogin.ChkUserInfor(getEmpCode.ToString().Trim()))
//{
// return Json(new
// {
// NotificationTitle = clsGlobal.SwalTitle_Error,
// NotificationContent = "Please contact MIS, error: Invalid User",
// NotificationType = clsGlobal.SwalType_Error
// });
//}
//Get group after check user exists in Orix DB
var getGroupCode = db.Sys_UserGroupMembers.FirstOrDefault(u => u.EmployeeCode == getEmpCode.ToString().Trim());
clsGlobal.UserGroupLogin = getGroupCode.GroupCode.ToString().Trim();
//User Exist
//if (emailCheck != null)
//Check Password Pending
if (getEmpCode.ToString().Trim() != null)
{
//var getPassword = db.Sys_Users.Where(u => u.Email == model.Email).Select(u => u.Password);
var getPassword = db.Sys_Users.Where(u => u.EmployeeCode == getEmpCode.ToString().Trim()).Select(u => u.Password);
var materializePassword = getPassword.ToList();
var password = materializePassword[0];
var decryptedPassword = clsDecrypt.Decrypt(password);
if (clsGlobal.LoginID.ToString().Trim() != null)
{
var getName = db.Sys_Users.Where(u => u.Email == clsGlobal.LoginID.ToString().Trim()).Select(u => u.Name);
var materializeName = getName.ToList();
var name = materializeName[0];
var getEmail = db.Sys_Users.Where(u => u.EmployeeCode == getEmpCode.ToString().Trim()).Select(u => u.Email);
var materializeEmail = getEmail.ToList();
var email = materializeEmail[0];
var getId = db.Sys_Users.Where(u => u.EmployeeCode == getEmpCode.ToString().Trim()).Select(u => u.Id);
var materializeId = getId.ToList();
var id = materializeId[0];
var identity = new ClaimsIdentity(new[] {
new Claim(ClaimTypes.Name, name),
new Claim(ClaimTypes.Email, email),
new Claim("JobTitle", "ADMIN"),
new Claim("UserGroupCode", clsGlobal.UserGroupLogin),
new Claim(ClaimTypes.NameIdentifier, id.ToString())
}, "ApplicationCookie");
var ctx = Request.GetOwinContext();
var authManager = ctx.Authentication;
authManager.SignIn(identity);
return(Json(new
{
NotificationType = clsGlobal.SwalType_Success,
redirectUrl = Url.Action("Default", "Home"),
isRedirect = true,
JsonRequestBehavior.AllowGet
}));
}
}
//Check Password Pending
}
glog.Error("Invalid UserName/Password. Please try again.");
return(Json(new
{
NotificationTitle = clsGlobal.SwalTitle_Fail,
NotificationContent = "Invalid UserName/Password. Please try again.",
NotificationType = clsGlobal.SwalType_Warning
}));
}
catch (Exception ex)
{
glog.Error("Please contact MIS, error:" + ex.Message);
return(Json(new
{
NotificationTitle = clsGlobal.SwalTitle_Error,
NotificationContent = "Please contact MIS, error:" + ex.Message,
NotificationType = clsGlobal.SwalType_Error
}));
}
glog.Debug("HttpPost Login: Exit");
}