public List<JobType> GetExclusiveJobTypeList(int departmentid)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from j in db.T_JobTypeDepartment
where j.DepartmentId == departmentid
orderby j.M_Department.Position, j.Position
select new JobType()
{
Id = j.JobTypeId,
Description = j.M_JobType.Description,
CreatedBy = j.M_JobType.CreatedBy,
LastUpdatedBy = j.M_JobType.LastUpdatedBy,
CreateDate = j.M_JobType.CreateDate,
LastUpdateDate = j.M_JobType.LastUpdateDate,
RequiredJobId = j.M_JobType.RequiredJobId,
ComputeTime = j.M_JobType.ComputeTime,
Position = j.Position,
ShowInJobOverview = j.M_JobType.ShowInJobOverview,
Acronym = j.M_JobType.Acronym
}).ToList();
db.Dispose();
return data;
}
public List<JobType> GetAvailableJobTypeForJobFlow()
{
TimeTrackerEntities db = new TimeTrackerEntities();
Department dept = new Department();
var deptlist = dept.GetDepartmentList();
int deptCount = deptlist.Count;
var data = (from j in db.T_JobType
where j.M_JobTypeFlows.Count < deptCount
orderby j.Position
select new JobType()
{
Id = j.Id,
Description = j.Description,
CreatedBy = j.CreatedBy,
LastUpdatedBy = j.LastUpdatedBy,
CreateDate = j.CreateDate,
LastUpdateDate = j.LastUpdateDate,
RequiredJobId = j.RequiredJobId,
ComputeTime = j.ComputeTime,
Position = j.Position,
ShowInJobOverview = j.ShowInJobOverview,
Acronym = j.Acronym,
IsDefaultBreak = j.IsDefaultBreak
}).ToList();
db.Dispose();
return data;
}
public CAPStageMapping GetCapStageMapping(int departmentId,int jobtypeId,int sd_stage_no,string databasemap)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from c in db.T_CAPStageMapping
where c.DepartmentId == departmentId
&& c.JobTypeId == jobtypeId
&& c.SD_Stage_No == sd_stage_no
&& c.DatabaseMap == databasemap
select new CAPStageMapping()
{
Id = c.Id,
DepartmentId = c.DepartmentId,
JobTypeId = c.JobTypeId,
SD_Stage_No = c.SD_Stage_No,
DatabaseMap = c.DatabaseMap,
StageDescription = c.StageDescription,
department = c.M_Department.Description,
jobtype = c.M_JobType.Description
}).FirstOrDefault();
db.Dispose();
return data;
}
private int GetRequestorId()
{
var ctx = new TimeTrackerEntities();
if (cbRequestedBy.SelectedValue != null)
{
var selectedRequestor = cbRequestedBy.SelectedValue.ToString();
return(Int32.Parse(selectedRequestor));
}
var newRequestor = cbRequestedBy.Text;
if (ctx.DRequestors.Any(x => x.RequestorName == newRequestor))
{
var custRec = ctx.DRequestors.FirstOrDefault(x => x.RequestorName.ToUpper() == newRequestor.ToUpper());
if (custRec != null)
{
return(custRec.RequestorId);
}
}
else
{
//create new Customer with input text.
var newRec = new DRequestor()
{
RequestorName = newRequestor
};
ctx.DRequestors.Add(newRec);
ctx.SaveChanges();
return(newRec.RequestorId);
}
throw new DataException("Inconsistent Data.");
}
private int GetCustomerId()
{
var ctx = new TimeTrackerEntities();
string selectedBillTo = cbBillTo.GetItemText(cbBillTo.SelectedItem);
if (selectedBillTo.Length == 0)
{
var newBillTo = cbBillTo.Text;
if (ctx.DCustomers.Any(x => x.CustomerName == newBillTo))
{
var custRec = ctx.DCustomers.FirstOrDefault(x => x.CustomerName.ToUpper() == newBillTo.ToUpper());
if (custRec != null)
{
return(custRec.CustomerId);
}
}
else
{
//create new Customer with input text.
var newCustomer = new DCustomer {
CustomerName = newBillTo
};
ctx.DCustomers.Add(newCustomer);
ctx.SaveChanges();
return(newCustomer.CustomerId);
}
}
else
{
return(Int32.Parse(cbBillTo.SelectedValue.ToString()));
}
throw new DataException("Wierd Data Encountered!");
}
public List<JobTypeFlow> GetDepartmentListByJobTypeId(int jobtypeid)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from j in db.T_JobTypeFlow
where j.JobTypeId == jobtypeid
&& j.DepartmentId != null
orderby j.Position
select new JobTypeFlow()
{
Id = j.Id,
JobTypeId = j.JobTypeId,
JobFlowId = j.JobFlowId,
Position = j.Position,
DepartmentId = j.DepartmentId,
jobtype = j.M_JobType.Description,
requiredJobId = j.M_JobType.RequiredJobId,
computeTime = j.M_JobType.ComputeTime,
showInJobOverview = j.M_JobType.ShowInJobOverview,
jobtypeAcronym = j.M_JobType.Acronym,
jobflow = j.M_JobFlow.Description,
jobflowAcronym = j.M_JobFlow.Acronym,
department = j.M_Department.Description,
departmentAcronym = j.M_Department.Acronym
}).ToList();
db.Dispose();
return data;
}
public JobType GetDefaultBreak()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from j in db.T_JobType
where j.IsDefaultBreak == true
select new JobType()
{
Id = j.Id,
Description = j.Description,
CreatedBy = j.CreatedBy,
LastUpdatedBy = j.LastUpdatedBy,
CreateDate = j.CreateDate,
LastUpdateDate = j.LastUpdateDate,
RequiredJobId = j.RequiredJobId,
ComputeTime = j.ComputeTime,
Position = j.Position,
ShowInJobOverview = j.ShowInJobOverview,
Acronym = j.Acronym,
IsDefaultBreak = j.IsDefaultBreak
}).FirstOrDefault();
db.Dispose();
return data;
}
public TimeClock GetStartEndTime(int userid, DateTime selectedDate)
{
TimeTrackerEntities db = new TimeTrackerEntities();
TimeClock result = new TimeClock();
User user = new User();
user = user.GetUser(userid);
//DateTime stime = Convert.ToDateTime(selectedDate.ToString("yyyy-MM-dd") + " 00:00");
//DateTime etime = Convert.ToDateTime(selectedDate.ToString("yyyy-MM-dd") + " 23:59");
DateTime stime = new DateTime();
DateTime etime = new DateTime();
TimeSpan startTime = new TimeSpan();
TimeSpan endTime = new TimeSpan();
TimeSpan.TryParse(user.startTime == "" ? "08:00":user.startTime, out startTime);
TimeSpan.TryParse(user.endTime == "" ? "17:00":user.endTime, out endTime);
if (startTime > endTime)
{
}
var data = (from t in db.T_TimeClock
where t.EmployeeNumber == user.EmployeeNumber
&& t.ScanTime >= stime
&& t.ScanTime <= etime
orderby t.ScanTime ascending
select new TimeClock()
{
Id = t.Id,
EmployeeNumber = t.EmployeeNumber,
ScanTime = t.ScanTime,
CreateDate = t.CreateDate,
Filename = t.Filename
}).ToList();
db.Dispose();
result.starttime = DateTime.Now.AddYears(1);
for (int i = 0; i < data.Count; i++)
{
if (i % 2 == 0)
{
if (result.starttime == null || result.starttime > data[i].ScanTime)
result.starttime = data[i].ScanTime;
}
else
{
if (result.endtime == null || result.endtime < data[i].ScanTime)
result.endtime = data[i].ScanTime;
}
}
return data.Count < 2 ? null : result;
}
public void Stop()
{
IsRunning = false;
MyStopWatch.Stop();
EndTime = DateTime.Now;
var ctx = new TimeTrackerEntities();
DJobTiming timing = ctx.DJobTimings.FirstOrDefault(x => x.JobTimingId == JobTimingId);
timing.EndTime = EndTime;
timing.IsRunning = false;
ctx.SaveChanges();
}
public void Update(TimeSetting timesetting)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_TimeSetting t_timesetting = db.T_TimeSetting.FirstOrDefault();
Parse(t_timesetting, timesetting);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public List<RoleDepartmentAccess> GetRoleDepartmentList()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from p in db.T_RoleDepartmentAccess
select new RoleDepartmentAccess()
{
Id = p.Id,
RoleId = p.RoleId,
DepartmentId = p.DepartmentId
}).ToList();
db.Dispose();
return data;
}
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_RolesModuleAccess t_rolemoduleaccess = new T_RolesModuleAccess();
t_rolemoduleaccess = db.T_RolesModuleAccess.FirstOrDefault(p => p.Id == id);
db.T_RolesModuleAccess.Remove(t_rolemoduleaccess);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public void Insert(JobTrackerHistory jtHist)
{
T_JobTrackerHistory j = new T_JobTrackerHistory();
ParseJobTracker(j, jtHist);
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
db.T_JobTrackerHistory.Add(j);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_JobTrackerHistory t_jthist = new T_JobTrackerHistory();
t_jthist = db.T_JobTrackerHistory.FirstOrDefault(j => j.Id == id);
db.T_JobTrackerHistory.Remove(t_jthist);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public List<TimeSetting> GetTimeSettingList()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from t in db.T_TimeSetting
select new TimeSetting()
{
Id = t.Id,
Interval = t.Interval,
LastUpdateDate = t.LastUpdateDate,
LastUpdatedBy = t.LastUpdatedBy
}).ToList();
db.Dispose();
return data;
}
//Delete user in the database
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_UserRateSchedule t_userRateSchedule = new T_UserRateSchedule();
t_userRateSchedule = db.T_UserRateSchedule.FirstOrDefault(u => u.Id == id);
db.T_UserRateSchedule.Remove(t_userRateSchedule);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_JobTypeFlow t_jobtypeflow = new T_JobTypeFlow();
t_jobtypeflow = db.T_JobTypeFlow.FirstOrDefault(j => j.Id == id);
db.T_JobTypeFlow.Remove(t_jobtypeflow);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_SupervisorMapping t_rs = new T_SupervisorMapping();
t_rs = db.T_SupervisorMapping.FirstOrDefault(p => p.Id == id);
db.T_SupervisorMapping.Remove(t_rs);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
//Delete a department in the database
public void Delete(int id)
{
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
T_Department t_department = new T_Department();
t_department = db.T_Department.FirstOrDefault(d => d.Id == id);
db.T_Department.Remove(t_department);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
//Get JobFlow By JobFlow Description
public JobFlow GetJobFlowByDescription(string description)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from f in db.T_JobFlow
where f.Description == description
select new JobFlow()
{
Id = f.Id,
Description = f.Description,
Position = f.Position,
Acronym = f.Acronym
}).FirstOrDefault();
db.Dispose();
return data;
}
public TimeSetting GetTimeSetting(int id)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from t in db.T_TimeSetting
where t.Id == id
select new TimeSetting()
{
Id = t.Id,
Interval = t.Interval,
LastUpdateDate = t.LastUpdateDate,
LastUpdatedBy = t.LastUpdatedBy
}).FirstOrDefault();
db.Dispose();
return data;
}
public void Insert(RoleDepartmentAccess roledepartmentaccess)
{
T_RoleDepartmentAccess t_roledepartmentaccess = new T_RoleDepartmentAccess();
Parse(t_roledepartmentaccess, roledepartmentaccess);
using (TimeTrackerEntities db = new TimeTrackerEntities())
{
try
{
db.T_RoleDepartmentAccess.Add(t_roledepartmentaccess);
db.SaveChanges();
}
catch (Exception ex)
{
string msg = ex.Message;
}
}
}
public List<RoleDepartmentAccess> GetRoleDepartmentList(int roleid)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from p in db.T_RoleDepartmentAccess
where p.RoleId == roleid
select new RoleDepartmentAccess()
{
Id = p.Id,
RoleId = p.RoleId,
DepartmentId = p.DepartmentId,
department = p.M_Department.Description
}).ToList();
db.Dispose();
return data;
}
public Holiday GetHoliday(DateTime date)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from d in db.T_Holiday
where d.HolidayDate == date
select new Holiday()
{
Id = d.Id,
HolidayYear = d.HolidayYear,
HolidayDate = d.HolidayDate,
Description = d.Description,
}).FirstOrDefault();
db.Dispose();
return data;
}
public List<Holiday> GetHolidays(int year)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from d in db.T_Holiday
where d.HolidayYear == year
select new Holiday()
{
Id = d.Id,
HolidayYear = d.HolidayYear,
HolidayDate = d.HolidayDate,
Description = d.Description,
}).ToList();
db.Dispose();
return data;
}
public List<Module> GetModuleList()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from m in db.T_Modules
orderby m.ModuleType,m.ArrangementOrder
select new Module()
{
Id = m.Id,
Filename = m.Filename,
Description = m.Description,
ModuleType = m.ModuleType,
ArrangementOrder = m.ArrangementOrder
}).ToList();
db.Dispose();
return data;
}
public Module GetModule(string filename)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from m in db.T_Modules
where m.Filename == filename
select new Module()
{
Id = m.Id,
Filename = m.Filename,
Description = m.Description,
ModuleType = m.ModuleType,
ArrangementOrder = m.ArrangementOrder
}).FirstOrDefault();
db.Dispose();
return data;
}
public List<RolesModuleAccess> GetRoleModuleList()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from p in db.T_RolesModuleAccess
select new RolesModuleAccess()
{
Id = p.Id,
RoleId = p.RoleId,
ModuleId = p.ModuleId,
CanView = p.CanView,
CanAdd = p.CanAdd,
CanUpdate = p.CanUpdate,
CanDelete = p.CanDelete
}).ToList();
db.Dispose();
return data;
}
public void SaveToDb()
{
StartDate = DateTime.Now;
var item = new DJobItem
{
StartDate = this.StartDate,
CustomerId = this.CustomerId,
RequestorId = this.RequestorId,
Description = this.Description,
EstimateId = this.EstimateId,
DeveloperId = this.DeveloperId,
};
var ctx = new TimeTrackerEntities();
ctx.DJobItems.Add(item);
ctx.SaveChanges();
JobItemId = item.JobItemId;
}
public RolesModuleAccess GetRolesModuleAccess(int id)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from p in db.T_RolesModuleAccess
where p.Id == id
select new RolesModuleAccess()
{
Id = p.Id,
RoleId = p.RoleId,
ModuleId = p.ModuleId,
CanView = p.CanView,
CanAdd = p.CanAdd,
CanUpdate = p.CanUpdate,
CanDelete = p.CanDelete
}).FirstOrDefault();
db.Dispose();
return data;
}
public List<SupervisorMapping> GetActiveSubordinates(int supervisorid)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from s in db.T_SupervisorMapping
where s.SupervisorId == supervisorid
select new SupervisorMapping()
{
Id = s.Id,
UserId = s.UserId,
SupervisorId = s.SupervisorId,
userfullname = s.M_User.Firstname + " " + s.M_User.Lastname,
supervisorname = s.M_Supervisor.Firstname + " " + s.M_Supervisor.Lastname,
subdepartment = s.M_User.M_Department.Description,
supdepartment = s.M_Supervisor.M_Department.Description
}).ToList();
db.Dispose();
return data;
}
//Get all departments
public List<Department> GetDepartmentList()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from d in db.T_Department
orderby d.Position ascending
select new Department()
{
Id = d.Id,
Description = d.Description,
CreateDate = d.CreateDate,
LastUpdateDate = d.LastUpdateDate,
CreatedBy = d.CreatedBy,
LastUpdatedBy = d.LastUpdatedBy,
Position = d.Position,
Acronym = d.Acronym
}).ToList();
db.Dispose();
return data;
}
//Get Department by Department Id
public Department GetDepartment(int departmentid)
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from d in db.T_Department
where d.Id == departmentid
select new Department()
{
Id = d.Id,
Description = d.Description,
CreateDate = d.CreateDate,
LastUpdateDate = d.LastUpdateDate,
CreatedBy = d.CreatedBy,
LastUpdatedBy = d.LastUpdatedBy,
Position = d.Position,
Acronym = d.Acronym
}).FirstOrDefault();
db.Dispose();
return data;
}
public List<Roles> GetRolesWithDepartmentAccess()
{
TimeTrackerEntities db = new TimeTrackerEntities();
var data = (from d in db.T_Roles
where d.M_RoleDepartmentAccesses.Count > 0
select new Roles()
{
Id = d.Id,
Description = d.Description,
Rank = d.Rank,
CreateDate = d.CreateDate,
LastUpdateDate = d.LastUpdateDate,
CreatedBy = d.CreatedBy,
LastUpdatedBy = d.LastUpdatedBy,
IsSupervisor = d.IsSupervisor
}).Distinct().ToList();
db.Dispose();
return data;
}
