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);
}
}
}
