public bool Insert(TimesheetModel parameter)
{
bool result = false;
try
{
TB_TIMESHEET data = new TB_TIMESHEET();
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
data.TIMESHEET_DATE = parameter.DATE_OF;
data.EMPLOYEE_ID = parameter.EMPLOYEE_ID;
data.JOBCODE_ID = parameter.JOB_CODE_ID;
data.TICKET_ID = parameter.TICKET_ID;
data.TIMESHEET_REMARK = parameter.DESCRIPTION;
data.WORK_HOUR = decimal.Parse(parameter.WORK_HOUR);
data.WORK_LOCATION = parameter.WORK_LOCATION;
db.TB_TIMESHEET.Add(data);
db.SaveChanges();
result = true;
}
}
catch (Exception ex)
{
}
return(result);
}
public List <TimesheetModel> GetList(int empID)
{
List <TimesheetModel> lst = new List <TimesheetModel>();
try
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
lst = db.SP_GET_LIST_TIMESHEET_MONTH(empID.ToString()).Select(i => new TimesheetModel()
{
TIMESHEET_ID = i.TIMESHEET_ID.ToString(),
EMPLOYEE_ID = i.EMPLOYEE_ID.Value,
DATE_OF = i.DATE_OF.Value,
Str_DATE_OF = i.Str_DATE_OF,
JOB_CODE = i.JOBCODE_NO,
JOB_CODE_NAME = i.JOBCODE_NAME,
SUB_JOB_CODE = i.SUB_JOBCODE,
TICKET_ID = i.TICKET_ID,
WORK_HOUR = i.WORK_HOUR.ToString() == "0.00" ? "-" : i.WORK_HOUR.ToString(),
WORK_LOCATION = i.WORK_LOCATION,
DESCRIPTION = i.DESCRIPTION
}).ToList();
}
}
catch (Exception ex)
{
}
return(lst);
}
public bool CheckDayOff8(DateTime today, string strLeave, int emp_id)
{
bool result = false;
decimal sumHour = 0;
try
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
var sumHourWork = db.TB_TIMESHEET.Where(i => i.EMPLOYEE_ID.Value.Equals(emp_id) && i.TIMESHEET_DATE.Value.Equals(today)).Sum(i => i.WORK_HOUR) ?? 0;
var sumHourLeaveLst = db.TB_TIMESHEET.Where(i => i.EMPLOYEE_ID.Value.Equals(emp_id) && i.WORK_HOUR.Value.Equals(0) && i.TIMESHEET_DATE.Value.Equals(today));
if (sumHourLeaveLst.Count() > 0)
{
sumHour += sumHourLeaveLst.Sum(i => i.TIMESHEET_REMARK == "ลาทั้งวัน" ? 8 : 4);
if (sumHourLeaveLst.FirstOrDefault(i => i.TIMESHEET_REMARK.Contains(strLeave)) != null)
{
return(false);
}
}
var leave = strLeave == "ลาทั้งวัน" ? 8 : 4;
sumHour += (leave + sumHourWork);
result = sumHour <= 8 ? true : false;
}
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public bool CheckWorkDay8(DateTime today, string work_hour, int emp_id)
{
bool result = false;
try
{
decimal sumHour = 0;
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
var sumHourWork = db.TB_TIMESHEET.Where(i => i.EMPLOYEE_ID.Value.Equals(emp_id) && i.TIMESHEET_DATE.Value.Equals(today)).Sum(i => i.WORK_HOUR) ?? 0;
var sumHourLeaveLst = db.TB_TIMESHEET.Where(i => i.EMPLOYEE_ID.Value.Equals(emp_id) && i.WORK_HOUR.Value.Equals(0) && i.TIMESHEET_DATE.Value.Equals(today));
if (sumHourLeaveLst.Count() > 0)
{
sumHour += sumHourLeaveLst.Sum(i => i.TIMESHEET_REMARK == "ลาทั้งวัน" ? 8 : 4);
}
sumHour += (sumHourWork + decimal.Parse(work_hour));
result = sumHour <= 8 ? true : false;
}
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public void RemoveOldData()
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <TB_WEEKLY_TIMESHEET> removeOldData = db.TB_WEEKLY_TIMESHEET.ToList();
db.TB_WEEKLY_TIMESHEET.RemoveRange(removeOldData);
db.SaveChanges();
}
}
public List <SubJobCodeListModel> GetAllSubJobCode(string job_code_id = null)
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <SubJobCodeListModel> lst = db.TB_JOBCODE.Where(i => i.JOBCODE_ID_PARENT.Value.ToString().Equals(job_code_id) && i.ACTIVE.Value.Equals(true)).Select(i => new SubJobCodeListModel
{
SUB_JOBCODE_ID = i.JOBCODE_ID.ToString(),
SUB_JOBCODE_NO = i.JOBCODE_NO,
SUB_JOBCODE_NAME = i.JOBCODE_NAME
}).OrderBy(i => i.SUB_JOBCODE_NAME).ToList();
return(lst);
}
}
public bool Insert(DataTable dt)
{
bool result = false;
try
{
string strFullName = dt.AsEnumerable().FirstOrDefault(i => i.Field <string>("PERFORMANCE EVALUATION REPORT").Equals("NAME")).Field <string>("F2").ToString();
string strMonthOf = dt.AsEnumerable().FirstOrDefault(i => i.Field <string>("PERFORMANCE EVALUATION REPORT").Equals("MONTH OF")).Field <string>("F2").ToString();
var dtData = dt.AsEnumerable().Where(i => !string.IsNullOrEmpty(i.Field <string>("F4")) && i.Field <string>("F2") != "Date");
string strDateOf = "";
TB_WEEKLY_TIMESHEET data = new TB_WEEKLY_TIMESHEET();
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
foreach (DataRow row in dtData)
{
data.EMPLOYEE_NAME = strFullName;
data.MONTH_OF = strMonthOf;
data.DATE_OF = string.IsNullOrEmpty(row["F2"].ToString()) ? int.Parse(strDateOf) : int.Parse(row["F2"].ToString().Trim());
data.JOB_TYPE = row["F4"].ToString().Trim();
data.JOB_CODE = row["F5"].ToString().Trim();
data.JOB_NAME = row["F6"].ToString().Trim();
data.PROJECT_NAME = row["F7"].ToString().Trim();
data.INCIDENT_NO = row["F8"].ToString().Trim();
data.DESCRIPTION = row["F9"].ToString().Trim();
data.PROGRAM_NAME = row["F10"].ToString().Trim();
data.WORK_HOUR = (string.IsNullOrEmpty(row["F11"].ToString().Trim()) || row["F11"].ToString().Trim() == "-") ? 0 : decimal.Parse(row["F11"].ToString().Trim());
data.PROJECT_MANAGER = row["F12"].ToString().Trim();
data.DEPARTMENT = row["F13"].ToString().Trim();
data.WORK_LOCATION = row["F14"].ToString().Trim();
data.ACTIVE = true;
data.UPDATE_BY = "Admin";
data.UPDATE_DATE = DateTime.Now;
strDateOf = data.DATE_OF.Value.ToString();
db.TB_WEEKLY_TIMESHEET.Add(data);
db.SaveChanges();
result = true;
}
}
}
catch (Exception ex)
{
}
return(result);
}
public static LoginUser GetUser(string Login_Name, bool New_Employee = false)
{
try
{
LoginUser user = new LoginUser();
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <LoginUser> loginUser = db.TB_EMPLOYEE.Select(t => new LoginUser
{
EMPLOYEE_ID = t.EMPLOYEE_ID,
EMPLOYEE_NO = t.EMPLOYEE_NO,
EMPLOYEE_TYPE = t.EMPLOYEE_TYPE,
FULLNAME_TH = t.FULLNAME_TH,
FULLNAME_EN = t.FULLNAME_EN,
NICKNAME = t.NICKNAME,
BIRTH_DATE = t.BIRTH_DATE,
ZODIAC = t.ZODIAC,
POSITION = t.POSITION,
POSITION_LEVEL = t.POSITION_LEVEL,
COMPANY = t.COMPANY,
MOBILE_NUMBER = t.MOBILE_NUMBER,
MAIL_PTT_DIGITAL = t.MAIL_PTT_DIGITAL,
MAIL_OTHER = t.MAIL_OTHER,
LINE_ID = t.LINE_ID,
ADDRESS = t.ADDRESS,
INTERVIEW_DATE = t.INTERVIEW_DATE,
START_WORK_DATE = t.START_WORK_DATE,
STATUS = t.STATUS
}).ToList();
if (New_Employee)
{
user = loginUser.FirstOrDefault(p => p.MAIL_PTT_DIGITAL.ToLower().Contains(Login_Name.ToLower()) && p.EMPLOYEE_NO == null);
}
else
{
user = loginUser.FirstOrDefault(p => p.MAIL_PTT_DIGITAL.ToLower().Contains(Login_Name.ToLower()));
}
return(user);
}
}
catch (Exception ex)
{
return(new LoginUser());
}
}
public List <JobCodeListModel> GetAllJobCode(string job_id = null)
{
List <JobCodeListModel> lst = new List <JobCodeListModel>();
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <SP_GET_JOB_CODE_Result> data = db.SP_GET_JOB_CODE(job_id).ToList();
lst = data.Select(i => new JobCodeListModel()
{
JOBCODE_ID = i.JOBCODE_ID.ToString(),
JOBCODE_NAME = i.JOBCODE_NAME,
JOBCODE_NO = i.JOBCODE_NO
}).ToList();
}
return(lst);
}
public List <JobCodeListModel> GetAllJobCodeTest(string job_code)
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <JobCodeListModel> lst = db.TB_JOBCODE.Select(i => new JobCodeListModel
{
JOBCODE_ID = i.JOBCODE_ID.ToString(),
JOBCODE_NO = i.JOBCODE_NO,
JOBCODE_NAME = i.JOBCODE_NAME
}).ToList();
if (!string.IsNullOrEmpty(job_code))
{
lst = lst.Where(i => i.JOBCODE_NO.Equals(job_code)).ToList();
}
return(lst);
}
}
public bool Delete(int timesheet_id)
{
bool result;
try
{
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
TB_TIMESHEET data = db.TB_TIMESHEET.FirstOrDefault(i => i.TIMESHEET_ID.Equals(timesheet_id));
db.TB_TIMESHEET.Remove(data);
db.SaveChanges();
result = true;
}
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public List <ListModel> GetList(string name)
{
List <ListModel> lst = new List <ListModel>();
using (DB_TIMESHEETEntities db = new DB_TIMESHEETEntities())
{
List <SP_GET_LIST_TIMESHEET_Result> data = db.SP_GET_LIST_TIMESHEET(name).ToList();
lst = data.Select(i => new ListModel()
{
EMPLOYEE_NAME = i.EMPLOYEE_NAME,
COUNT_JOB_CODE_PROJECT = i.PROJECT_JOB_CODE.ToString(),
HOUR_JOB_CODE_SUPPORT = double.Parse(i.SUPPORT_WORK_HOUR.ToString()),
HOUR_JOB_CODE_PROJECT = double.Parse(i.PROJECT_WORK_HOUR.ToString()),
AVERAGE_JOB_CODE_SUPPORT = double.Parse(i.SUPPORT_PERCENT.ToString()),
AVERAGE_JOB_CODE_PROJECT = double.Parse(i.PROJECT_PERCENT.ToString()),
LAST_UPDATE_DATE = i.LAST_UPDATE_DATE.ToString()
}).ToList();
}
return(lst);
}
