public async Task <List <ClientListModel> > SelectClients() { using (var db = new InternalSystemEntities()) { return(await db.clients.Select(x => new ClientListModel() { client_id = x.client_id, client_name = x.client_name }).ToListAsync()); } }
public async Task <List <AssignedToWhomListModel> > SelectAssignedToWhoms() { using (var db = new InternalSystemEntities()) { return(await db.assigned_to_whom.Select(x => new AssignedToWhomListModel() { assigned_to_whom_id = x.assigned_to_whom_id, assigned_to_whom_name = x.assigned_to_whom_name }).ToListAsync()); } }
public async Task <List <PositionTypeListModel> > SelectPositionTypes() { using (var db = new InternalSystemEntities()) { return(await db.position_type.Select(x => new PositionTypeListModel() { position_type_id = x.position_type_id, position_type_name = x.position_type_name }).ToListAsync()); } }
public async Task <List <JobTitleListModel> > SelectJobTitles() { using (var db = new InternalSystemEntities()) { return(await db.job_title.Select(x => new JobTitleListModel() { job_title_id = x.job_title_id, job_title_name = x.job_title_name }).ToListAsync()); } }
public async Task <List <JobLocationListModel> > SelectJobLocations() { using (var db = new InternalSystemEntities()) { return(await db.job_location.Select(x => new JobLocationListModel() { job_location_id = x.job_location_id, job_location_name = x.job_location_name }).ToListAsync()); } }
public async Task <List <AccountManagerListModel> > SelectAccountManagers() { using (var db = new InternalSystemEntities()) { return(await db.account_manager.Select(x => new AccountManagerListModel() { account_manager_id = x.account_manager_id, account_manager_name = x.account_manager_name }).ToListAsync()); } }
private async Task <int> GetJobLocationId(string name) { if (string.IsNullOrEmpty(name)) { return(0); } using (var db = new InternalSystemEntities()) { var result = await db.job_location.FirstOrDefaultAsync(x => x.job_location_name == name); return((result == null || result.job_location_id == 0) ? await InsertJobLocation(name) : result.job_location_id); } }
private async Task <int> GetAccountManagerNameId(string name) { if (string.IsNullOrEmpty(name)) { return(0); } using (var db = new InternalSystemEntities()) { var result = await db.account_manager.FirstOrDefaultAsync(x => x.account_manager_name == name); return((result == null || result.account_manager_id == 0) ? await InsertAccountManagerName(name) : result.account_manager_id); } }
private async Task <int> GetAssignedToWhomId(string name) { if (string.IsNullOrEmpty(name)) { return(0); } using (var db = new InternalSystemEntities()) { var result = await db.assigned_to_whom.FirstOrDefaultAsync(x => x.assigned_to_whom_name == name); return((result == null || result.assigned_to_whom_id == 0) ? await InsertAssignedToWhom(name) : result.assigned_to_whom_id); } }
private async Task <int> GetClientId(string name) { if (string.IsNullOrEmpty(name)) { return(0); } using (var db = new InternalSystemEntities()) { var result = await db.clients.FirstOrDefaultAsync(x => x.client_name == name); return((result == null || result.client_id == 0) ? await InsertClient(name) : result.client_id); } }
public async Task <List <StaffingListModel> > GetAll() { List <StaffingListModel> resp; using (var db = new InternalSystemEntities()) { resp = await(from a in db.staffings join p in db.job_title on a.job_title_id equals p.job_title_id join s in db.position_status on a.position_status_id equals s.position_status_id join q in db.job_location on a.job_location_id equals q.job_location_id into jlocationData from aq in jlocationData.DefaultIfEmpty() join r in db.clients on a.client_id equals r.client_id into clientData from ar in clientData.DefaultIfEmpty() join t in db.assigned_to_whom on a.assigned_to_whom_id equals t.assigned_to_whom_id into assignedData from at in assignedData.DefaultIfEmpty() join u in db.account_manager on a.account_manager_id equals u.account_manager_id into accmanagerData from au in accmanagerData.DefaultIfEmpty() join v in db.position_type on a.position_type_id equals v.position_type_id into positypeData from av in positypeData.DefaultIfEmpty() join w in db.client_manager on a.client_manager_id equals w.client_manager_id into clientmanagerData from aw in clientmanagerData.DefaultIfEmpty() where a.is_active == true select new StaffingListModel { staffing_id = a.staffing_id, job_title_name = p.job_title_name, epic_position = a.epic_position == true ? true : false, total_no_of_positions = a.total_no_of_positions, no_of_positions_filled = a.no_of_positions_filled, job_location_name = aq == null ? string.Empty : aq.job_location_name, date_job_received = a.date_job_received, client_name = ar == null ? string.Empty : ar.client_name, client_manager_name = aw.client_manager_name, job_description = a.job_description, assigned_to_whom_name = at == null ? string.Empty : at.assigned_to_whom_name, account_manager_name = au == null ? string.Empty : au.account_manager_name, follow_up_action = a.follow_up_action, position_type_name = av == null ? string.Empty : av.position_type_name, rate_range_w2 = a.rate_range_w2, rate_range_c2c_1099 = a.rate_range_c2c_1099, duration = a.duration, t_e_paid = a.t_e_paid == true ? "Yes" : "No", position_status_name = s.position_status_name, doc_date = a.created_datetime }).ToListAsync(); } return(resp.Count > 0 ? resp : null); }
private async Task <int> InsertAccountManagerName(string name) { var newEntry = new account_manager { account_manager_name = name, is_active = true }; using (var db = new InternalSystemEntities()) { db.account_manager.Add(newEntry); await db.SaveChangesAsync(); return(newEntry.account_manager_id); } }
private async Task <int> InsertAssignedToWhom(string name) { var newEntry = new assigned_to_whom { assigned_to_whom_name = name, is_active = true }; using (var db = new InternalSystemEntities()) { db.assigned_to_whom.Add(newEntry); await db.SaveChangesAsync(); return(newEntry.assigned_to_whom_id); } }
private async Task <int> InsertClient(string name) { var newEntry = new client { client_name = name, is_active = true }; using (var db = new InternalSystemEntities()) { db.clients.Add(newEntry); await db.SaveChangesAsync(); return(newEntry.client_id); } }
private async Task <int> InsertJobLocation(string name) { var newEntry = new job_location { job_location_name = name, is_active = true }; using (var db = new InternalSystemEntities()) { db.job_location.Add(newEntry); await db.SaveChangesAsync(); return(newEntry.job_location_id); } }
public async Task <StaffingModel> GetById(int id) { using (var db = new InternalSystemEntities()) { var data = await db.staffings.FindAsync(id); if (data == null || data.staffing_id == 0) { return(null); } var model = new StaffingModel() { staffing_id = data.staffing_id, job_title_id = data.job_title_id, epic_position = data.epic_position, total_no_of_positions = data.total_no_of_positions, no_of_positions_filled = data.no_of_positions_filled, job_location_id = data.job_location_id.HasValue ? data.job_location_id.Value : 0, date_job_received = data.date_job_received != null || data.date_job_received != DateTime.MinValue ? data.date_job_received : null, client_id = data.client_id.HasValue ? data.client_id.Value : 0, client_manager_id = data.client_manager_id.HasValue ? data.client_manager_id.Value : 0, job_description = data.job_description, assigned_to_whom_id = data.assigned_to_whom_id.HasValue ? data.assigned_to_whom_id.Value : 0, account_manager_id = data.account_manager_id.HasValue ? data.account_manager_id.Value : 0, position_status_id = data.position_status_id.HasValue ? data.position_status_id.Value : 0, follow_up_action = data.follow_up_action, position_type_id = data.position_type_id.HasValue ? data.position_type_id.Value : 0, rate_range_w2 = data.rate_range_w2, rate_range_c2c_1099 = data.rate_range_c2c_1099, duration = data.duration, t_e_paid = data.t_e_paid, job_closed_date = data.job_closed_date != null || data.job_closed_date != DateTime.MinValue ? data.job_closed_date : null }; var job_title = await db.job_title.FindAsync(data.job_title_id); if (job_title == null || job_title.job_title_id == 0) { model.job_title_name = string.Empty; } else { model.job_title_name = job_title.job_title_name; } var job_location = await db.job_location.FindAsync(data.job_location_id); if (job_location == null || job_location.job_location_id == 0) { model.job_location_name = string.Empty; } else { model.job_location_name = job_location.job_location_name; } var clients = await db.clients.FindAsync(data.client_id); if (clients == null || clients.client_id == 0) { model.client_name = string.Empty; } else { model.client_name = clients.client_name; } var assigned_to_whom = await db.assigned_to_whom.FindAsync(data.assigned_to_whom_id); if (assigned_to_whom == null || assigned_to_whom.assigned_to_whom_id == 0) { model.assigned_to_whom_name = string.Empty; } else { model.assigned_to_whom_name = assigned_to_whom.assigned_to_whom_name; } var account_manager = await db.account_manager.FindAsync(data.account_manager_id); if (account_manager == null || account_manager.account_manager_id == 0) { model.account_manager_name = string.Empty; } else { model.account_manager_name = account_manager.account_manager_name; } var client_manager = await db.client_manager.FindAsync(data.client_manager_id); if (client_manager == null || client_manager.client_manager_id == 0) { model.client_manager_name = string.Empty; } else { model.client_manager_name = client_manager.client_manager_name; } return(model); } }
public async Task <int> Update(int id, StaffingModel data, int adminId, DateTime currentDt) { using (var db = new InternalSystemEntities()) { var staffing = await db.staffings.FindAsync(id); if (staffing == null || staffing.staffing_id == 0) { return(0); } staffing.staffing_id = id; staffing.epic_position = data.epic_position; staffing.total_no_of_positions = data.total_no_of_positions; staffing.no_of_positions_filled = data.no_of_positions_filled; staffing.date_job_received = data.date_job_received != null ? data.date_job_received : null; staffing.job_description = data.job_description; staffing.position_status_id = data.position_status_id; staffing.follow_up_action = data.follow_up_action; staffing.position_type_id = data.position_type_id; staffing.rate_range_w2 = data.rate_range_w2; staffing.rate_range_c2c_1099 = data.rate_range_c2c_1099; staffing.duration = data.duration; staffing.t_e_paid = data.t_e_paid; staffing.is_active = true; staffing.last_updated_by_user_id = adminId; staffing.last_updated_datetime = currentDt; staffing.job_closed_date = data.job_closed_date != null ? data.job_closed_date : null; int newId = await GetJobTitleId(data.job_title_name); if (newId != 0) { staffing.job_title_id = newId; } newId = await GetJobLocationId(data.job_location_name); if (newId != 0) { staffing.job_location_id = newId; } newId = await GetClientId(data.client_name); if (newId != 0) { staffing.client_id = newId; } newId = await GetAssignedToWhomId(data.assigned_to_whom_name); if (newId != 0) { staffing.assigned_to_whom_id = newId; } newId = await GetAccountManagerNameId(data.account_manager_name); if (newId != 0) { staffing.account_manager_id = newId; } newId = await GetClientManagerNameId(data.client_manager_name); if (id != 0) { staffing.client_manager_id = newId; } await db.SaveChangesAsync(); return(staffing.staffing_id); } }
public async Task <List <DropdownListModel> > SelectDropdownList(string search_text, string search_in_master) { if (string.IsNullOrEmpty(search_in_master)) { return(null); } List <DropdownListModel> resp = new List <DropdownListModel>(); using (var db = new InternalSystemEntities()) { if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "job_title_name") { resp = await(from c in db.job_title where c.job_title_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.job_title_name, dropdown_id = c.job_title_id }).ToListAsync(); } else if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "job_location_name") { resp = await(from c in db.job_location where c.job_location_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.job_location_name, dropdown_id = c.job_location_id }).ToListAsync(); } else if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "client_name") { resp = await(from c in db.clients where c.client_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.client_name, dropdown_id = c.client_id }).ToListAsync(); } else if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "assigned_to_whom_name") { resp = await(from c in db.assigned_to_whom where c.assigned_to_whom_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.assigned_to_whom_name, dropdown_id = c.assigned_to_whom_id }).ToListAsync(); } else if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "account_manager_name") { resp = await(from c in db.account_manager where c.account_manager_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.account_manager_name, dropdown_id = c.account_manager_id }).ToListAsync(); } else if (!string.IsNullOrEmpty(search_in_master) && search_in_master == "client_manager_name") { resp = await(from c in db.client_manager where c.client_manager_name.ToLower().Contains(search_text.ToLower()) select new DropdownListModel() { dropdown_name = c.client_manager_name, dropdown_id = c.client_manager_id }).ToListAsync(); } } return(resp); }
public async Task <int> Insert(StaffingModel data, int adminId, DateTime currentDt) { staffing _staffing = new staffing { epic_position = data.epic_position, total_no_of_positions = data.total_no_of_positions, no_of_positions_filled = data.no_of_positions_filled, date_job_received = data.date_job_received != null || data.job_closed_date != DateTime.MinValue ? data.date_job_received : null, job_description = data.job_description, position_status_id = data.position_status_id, follow_up_action = data.follow_up_action, position_type_id = data.position_type_id, rate_range_w2 = data.rate_range_w2, rate_range_c2c_1099 = data.rate_range_c2c_1099, duration = data.duration, t_e_paid = data.t_e_paid, job_closed_date = data.job_closed_date != null || data.job_closed_date != DateTime.MinValue ? data.date_job_received : null, is_active = true, created_by_user_id = adminId, created_datetime = currentDt, last_updated_by_user_id = adminId, last_updated_datetime = currentDt }; int id = await GetJobTitleId(data.job_title_name); if (id != 0) { _staffing.job_title_id = id; } id = await GetJobLocationId(data.job_location_name); if (id != 0) { _staffing.job_location_id = id; } id = await GetClientId(data.client_name); if (id != 0) { _staffing.client_id = id; } id = await GetAssignedToWhomId(data.assigned_to_whom_name); if (id != 0) { _staffing.assigned_to_whom_id = id; } id = await GetAccountManagerNameId(data.account_manager_name); if (id != 0) { _staffing.account_manager_id = id; } id = await GetClientManagerNameId(data.client_manager_name); if (id != 0) { _staffing.client_manager_id = id; } using (var db = new InternalSystemEntities()) { db.staffings.Add(_staffing); int cnt = await db.SaveChangesAsync(); return(_staffing.staffing_id); } }
protected async Task <bool> ExecUpdate(PatchPostModel data, int userId, DateTime currentDt) { if (string.IsNullOrEmpty(UpdateWhereClause)) { throw new NotImplementedException(); } if (data == null || data.changed == null) { return(true); } string upd = $"UPDATE {TableName()} SET"; int cnt = data.changed.Count; if (cnt == 0) { return(true); } List <object> sqlparams = new List <object>(); ICollection <string> keys = data.changed.Keys; foreach (string key in keys) { string value = data.changed[key]; //upd += $" {key} = @{key},"; try { if (string.IsNullOrEmpty(value)) { //sqlparams.Add(new SqlParameter($"@{key}", null)); upd += $" {key} = null,"; continue; } upd += $" {key} = @{key},"; if (string.Equals(value, "true", StringComparison.OrdinalIgnoreCase)) { sqlparams.Add(new SqlParameter($"@{key}", true)); continue; } if (string.Equals(value, "false", StringComparison.OrdinalIgnoreCase)) { sqlparams.Add(new SqlParameter($"@{key}", false)); continue; } sqlparams.Add(new SqlParameter($"@{key}", value)); } catch (Exception ex) { string s = ex.Message; return(false); } } if (UpdateLastDateTime) { upd += " last_updated_by_user_id = @last_updated_by_user_id, last_updated_datetime = @last_updated_datetime"; sqlparams.Add(new SqlParameter("@last_updated_by_user_id", userId)); sqlparams.Add(new SqlParameter("@last_updated_datetime", currentDt)); } else { upd = upd.Substring(0, upd.Length - 1); } // await ExecuteQuery($"{upd} WHERE {UpdateWhereClause}", dict); using (var db = new InternalSystemEntities()) { try { int recCnt = await db.Database.ExecuteSqlCommandAsync($"{upd} WHERE {UpdateWhereClause}", sqlparams.ToArray()); return(recCnt > 0); } catch (Exception) { return(false); } } }