public bool UpdateDB(SalarySetting salarySetting)
{
try
{
OpenConnection();
string query = @"update SalarySetting set salaryBase = @salaryBase,moneyPerShift = @moneyPerShift,moneyPerFault = @moneyPerFault, standardWorkDays = @standardWorkDays where typeEmployee = N'" + salarySetting.TypeEmployee + "'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@salaryBase", salarySetting.SalaryBase.ToString());
cmd.Parameters.AddWithValue("@moneyPerShift", salarySetting.MoneyPerShift.ToString());
cmd.Parameters.AddWithValue("@moneyPerFault", salarySetting.MoneyPerFault.ToString());
cmd.Parameters.AddWithValue("@standardWorkDays", salarySetting.StandardWorkDays.ToString());
if (cmd.ExecuteNonQuery() < 1)
{
return(false);
}
else
{
return(true);
}
}
catch
{
return(false);
}
finally
{
CloseConnection();
}
}
public bool AddIntoDB(SalarySetting salarySetting)
{
try
{
OpenConnection();
string query = @"insert into SalarySetting (salaryBase, moneyPerShift, moneyPerFault, typeEmployee, standardWorkDays) " +
"values(@salaryBase, @moneyPerShift, @moneyPerFault, @typeEmployee, @standardWorkDays)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@salaryBase", salarySetting.SalaryBase.ToString());
cmd.Parameters.AddWithValue("@moneyPerShift", salarySetting.MoneyPerShift.ToString());
cmd.Parameters.AddWithValue("@moneyPerFault", salarySetting.MoneyPerFault.ToString());
cmd.Parameters.AddWithValue("@typeEmployee", salarySetting.TypeEmployee);
cmd.Parameters.AddWithValue("@standardWorkDays", salarySetting.StandardWorkDays.ToString());
if (cmd.ExecuteNonQuery() < 1)
{
return(false);
}
else
{
return(true);
}
}
catch
{
return(false);
}
finally
{
CloseConnection();
}
}
public SalarySetting GetSalarySettings(string typeEmployee) // lấy ra nhân viên có chức vụ
{
try
{
OpenConnection();
string query = @"select * from SalarySetting where typeEmployee = N'" + typeEmployee + "'";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
SalarySetting newItem = new SalarySetting(long.Parse(dt.Rows[0].ItemArray[0].ToString()),
long.Parse(dt.Rows[0].ItemArray[1].ToString()), long.Parse(dt.Rows[0].ItemArray[2].ToString()),
dt.Rows[0].ItemArray[3].ToString(), int.Parse(dt.Rows[0].ItemArray[4].ToString()));
return(newItem);
}
else
{
return(null);
}
}
catch
{
return(null);
}
finally
{
CloseConnection();
}
}
public ActionResult Create(SalarySettingViewModel model)
{
if (ModelState.IsValid)
{
var SalarySetting = new SalarySetting();
AutoMapper.Mapper.Map(model, SalarySetting);
SalarySetting.IsDeleted = false;
SalarySetting.CreatedUserId = WebSecurity.CurrentUserId;
SalarySetting.ModifiedUserId = WebSecurity.CurrentUserId;
SalarySetting.AssignedUserId = WebSecurity.CurrentUserId;
SalarySetting.CreatedDate = DateTime.Now;
SalarySetting.ModifiedDate = DateTime.Now;
SalarySetting.Name = model.Name;
SalarySettingRepository.InsertSalarySetting(SalarySetting);
var salarySettingTemplate = SalarySettingRepository.GetAllSalarySetting()
.Where(item => item.IsTemplate).FirstOrDefault();
if (salarySettingTemplate != null)
{
var listAll = salarySettingDetailRepository.GetAllSalarySettingDetail()
.Where(item => item.SalarySettingId == salarySettingTemplate.Id).ToList();
var listGroup = listAll.Where(item => item.ParentId == null).ToList();
foreach (var group in listGroup)
{
int groupId = group.Id;
group.SalarySettingId = SalarySetting.Id;
group.CreatedUserId = WebSecurity.CurrentUserId;
group.ModifiedUserId = WebSecurity.CurrentUserId;
group.AssignedUserId = WebSecurity.CurrentUserId;
group.CreatedDate = DateTime.Now;
group.ModifiedDate = DateTime.Now;
salarySettingDetailRepository.InsertSalarySettingDetail(group);
var subList = listAll.Where(i => i.ParentId == groupId).ToList();
foreach (var item in subList)
{
item.SalarySettingId = SalarySetting.Id;
item.ParentId = group.Id;
item.CreatedUserId = WebSecurity.CurrentUserId;
item.ModifiedUserId = WebSecurity.CurrentUserId;
item.AssignedUserId = WebSecurity.CurrentUserId;
item.CreatedDate = DateTime.Now;
item.ModifiedDate = DateTime.Now;
salarySettingDetailRepository.InsertSalarySettingDetail(item);
}
}
}
//TempData[Globals.SuccessMessageKey] = App_GlobalResources.Wording.InsertSuccess;
return(RedirectToAction("Edit", new { Id = SalarySetting.Id }));
}
return(View(model));
}
public List <SalarySetting> ConvertDBToList()
{
DataTable dt;
List <SalarySetting> tmp = new List <SalarySetting>();
try
{
dt = LoadData("SalarySetting");
}
catch
{
CloseConnection();
dt = LoadData("SalarySetting");
}
for (int i = 0; i < dt.Rows.Count; i++)
{
SalarySetting newItem = new SalarySetting(long.Parse(dt.Rows[i].ItemArray[0].ToString()), long.Parse(dt.Rows[i].ItemArray[1].ToString()), long.Parse(dt.Rows[i].ItemArray[2].ToString()), dt.Rows[i].ItemArray[3].ToString(), int.Parse(dt.Rows[i].ItemArray[4].ToString()));
tmp.Add(newItem);
}
return(tmp);
}
private void PopulateGrid(List <Employee> employees)
{
int bonusId = Convert.ToInt32(comboBox1.SelectedValue);
Bonus bonus = db.Bonus.Find(bonusId);
DateTime To = bonus.Date;
DateTime From = To.AddYears(-1);
List <ViewBonus> viewBonus = new List <ViewBonus>();
foreach (Employee employee in employees)
{
ViewBonus view = new ViewBonus();
try
{
DateTime JoiningDate = Convert.ToDateTime(employee.HireDate);
view.JoiningDate = JoiningDate;
TimeSpan timeSpan = To.Subtract(JoiningDate);
view.Year = timeSpan.TotalDays / 365;
}
catch
{
}
view.CompanyPercentage = bonus.Amount;
view.Emp_Id = employee.Emp_Id;
view.Name = employee.EmpFullName;
if (employee.IsWorker == true)
{
List <WorkerDesignationHistory> workerDesignationHistories = db.WorkerDesignationHistories.Where(a => a.EmployeeId == employee.Id && a.From <= To).ToList();
if (workerDesignationHistories.Count > 0)
{
DateTime maxDate = workerDesignationHistories.Max(a => a.From);
WorkerDesignationHistory workerDesignationHistory = workerDesignationHistories.FirstOrDefault(a => a.From == maxDate);
// Find Salary Grade At That Time
DL.WorkerDesignation workerDesignation = db.WorkerDesignations.Find(workerDesignationHistory.WorkerDesignationId);
List <ShadowSalaryGrade> shadowSalaryGrades = db.ShadowSalaryGrades.Where(a => a.UpdatedAt <= To && a.RoWId == workerDesignation.SalaryGrade.Id).ToList();
DateTime D2 = DateTime.Now;
if (shadowSalaryGrades.Count > 0)
{
D2 = shadowSalaryGrades.Max(a => a.UpdatedAt);
var shadowgrade = shadowSalaryGrades.FirstOrDefault(a => a.RoWId == workerDesignation.SalaryGrade.Id && a.UpdatedAt == D2);
view.Grade = workerDesignation.Name;
view.Gross = shadowgrade.Total;
view.Basic = shadowgrade.Salary;
}
}
}
else
{
List <Salary> salaries = db.Salaries.Where(a => a.EmployeeId == employee.Id && a.Date <= To).ToList();
if (salaries.Count > 0)
{
DateTime maxDate = salaries.Max(a => a.Date);
Salary salary = salaries.FirstOrDefault(a => a.Date == maxDate);
//view.Grade = salary.;
view.Gross = salary.Amount;
List <SalarySetting> salarySettings = db.SalarySettings.Where(a => a.Date <= To).ToList();
if (salarySettings.Count > 0)
{
DateTime maxDate2 = salarySettings.Max(a => a.Date);
SalarySetting settings = salarySettings.FirstOrDefault(a => a.Date == maxDate2);
view.Basic = salary.Amount * (100 / settings.Basic);
}
}
}
//Count Employee attendance
List <Emp_CheckInOut> emp_CheckInOuts = db.Emp_CheckInOuts.Where(a => a.UserId == employee.Emp_Id).ToList();
var DayCount = emp_CheckInOuts.Where(a => a.CHECKTIME >= From && a.CHECKTIME <= To).GroupBy(a => a.CHECKTIME.Date).ToList();
view.Days = DayCount.Count - 1;
viewBonus.Add(view);
}
viewBonusDataGridView.DataSource = viewBonus.ToList();
// viewBonusDataGridView1.DataSource = viewBonus.ToList();
}
public ActionResult Duplicate(SalarySettingViewModel model)
{
if (ModelState.IsValid)
{
var SalarySetting = new SalarySetting();
AutoMapper.Mapper.Map(model, SalarySetting);
SalarySetting.IsDeleted = false;
SalarySetting.CreatedUserId = WebSecurity.CurrentUserId;
SalarySetting.ModifiedUserId = WebSecurity.CurrentUserId;
SalarySetting.AssignedUserId = WebSecurity.CurrentUserId;
SalarySetting.CreatedDate = DateTime.Now;
SalarySetting.ModifiedDate = DateTime.Now;
SalarySetting.Name = model.Name;
SalarySettingRepository.InsertSalarySetting(SalarySetting);
var salarySettingTemplate = SalarySettingRepository.GetAllSalarySetting()
.Where(item => item.Id == model.Id).FirstOrDefault();
if (salarySettingTemplate != null)
{
var listAll = salarySettingDetailRepository.GetAllSalarySettingDetail()
.Where(item => item.SalarySettingId == salarySettingTemplate.Id).ToList();
var listGroup = listAll.Where(item => item.ParentId == null).ToList();
foreach (var group in listGroup)
{
int groupId = group.Id;
group.SalarySettingId = SalarySetting.Id;
group.CreatedUserId = WebSecurity.CurrentUserId;
group.ModifiedUserId = WebSecurity.CurrentUserId;
group.AssignedUserId = WebSecurity.CurrentUserId;
group.CreatedDate = DateTime.Now;
group.ModifiedDate = DateTime.Now;
salarySettingDetailRepository.InsertSalarySettingDetail(group);
var subList = listAll.Where(i => i.ParentId == groupId).ToList();
foreach (var item in subList)
{
item.SalarySettingId = SalarySetting.Id;
item.ParentId = group.Id;
item.CreatedUserId = WebSecurity.CurrentUserId;
item.ModifiedUserId = WebSecurity.CurrentUserId;
item.AssignedUserId = WebSecurity.CurrentUserId;
item.CreatedDate = DateTime.Now;
item.ModifiedDate = DateTime.Now;
salarySettingDetailRepository.InsertSalarySettingDetail(item);
}
}
}
var newModel = SalarySettingRepository.GetAllSalarySetting().OrderByDescending(n => n.Id).SingleOrDefault(n => n.Name.Contains(model.Name) && n.CreatedUserId == WebSecurity.CurrentUserId && n.AssignedUserId == WebSecurity.CurrentUserId);
TempData[Globals.SuccessMessageKey] = App_GlobalResources.Wording.InsertSuccess;
var _model = new SalarySettingViewModel();
AutoMapper.Mapper.Map(newModel, _model);
var urlRefer = @Url.Action("Edit", "SalarySetting", new { Id = _model.Id });
if (Request["IsPopup"] == "true" || Request["IsPopup"] == "True")
{
TempData[Globals.SuccessMessageKey] = App_GlobalResources.Wording.InsertSuccess;
ViewBag.closePopup = "true";
ViewBag.urlRefer = urlRefer;
return(View(_model));
}
return(RedirectToAction("Edit", new { Id = SalarySetting.Id }));
}
return(View(model));
}