public List <TTimesheet> EmployeeClockIn(int EmpId)
{
DateTime dateTime = DateTime.Now;
string time = "HH:mm:ss";
string date = "yyyy-MM-dd";
LinqToSQLDataContext db = new LinqToSQLDataContext();
var checkIfEmpIsClockedIn = db.ExecuteQuery <TTimesheet>(@"SELECT * FROM TTimesheet WHERE EmployeeId = {0} AND [Date] = {1}", EmpId, dateTime.ToString(date)).ToList();
if (checkIfEmpIsClockedIn.Count > 0)
{
if (checkIfEmpIsClockedIn[0].CLockInTime == TimeSpan.Zero)
{
var query = db.ExecuteQuery <TTimesheet>(@"INSERT INTO [dbo].[TTimesheet] ([EmployeeId], [Date], [CLockInTime], [ClockOutTime], [TotalHours]) VALUES ({0}, {1}, {2}, {3}, {4});
SELECT TimesheetId, EmployeeId, Date, CLockInTime, ClockOutTime, TotalHours FROM TTimesheet WHERE (TimesheetId = SCOPE_IDENTITY())", EmpId, dateTime, dateTime.ToString(time), string.Empty, 0);
MessageBox.Show("Successfully clocked in");
return(query.ToList());
}
else
{
MessageBox.Show("you are already logged in");
}
}
else
{
var query = db.ExecuteQuery <TTimesheet>(@"INSERT INTO [dbo].[TTimesheet] ([EmployeeId], [Date], [CLockInTime], [ClockOutTime], [TotalHours]) VALUES ({0}, {1}, {2}, {3}, {4});
SELECT TimesheetId, EmployeeId, Date, CLockInTime, ClockOutTime, TotalHours FROM TTimesheet WHERE (TimesheetId = SCOPE_IDENTITY())", EmpId, dateTime, dateTime.ToString(time), string.Empty, 0);
MessageBox.Show("Successfully clocked in");
return(query.ToList());
}
return(null);
}
public List <TUserLogin> GetUserLoginsPermission(string nameOfuser)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
var query = db.ExecuteQuery <TUserLogin>(@"SELECT * FROM TUserLogin where UserName = '******'");
return(query.ToList());
}
public List <TPayslip> GetUserPaySlip(int empId)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
var query = db.ExecuteQuery <TPayslip>(@"SELECT PaySlipId, EmployeeId, DateFrom, EndDate, TotalHours, TotalMoney FROM TPayslip where EmployeeId = {0}", empId);
return(query.ToList());
}
public List <TEmployee> GetEmployeeInfo(int id)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
var query = db.ExecuteQuery <TEmployee>(@"SELECT * FROM TEmployee WHERE EmployeeId = {0}", id);
return(query.ToList());
}
public List <TSchedule> GetEmployeeScheduled(string schedule)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
var query = db.ExecuteQuery <TSchedule>(@"SELECT * FROM TSchedule WHERE [Date] = {0}", schedule);
return(query.ToList());
}
public void InsertTotalHours(int EmpId)
{
DateTime dateTime = DateTime.Now;
string time = "HH:mm:ss";
string date = "yyyy-MM-dd";
LinqToSQLDataContext db = new LinqToSQLDataContext();
var getClockOutTime = db.ExecuteQuery <TTimesheet>(@"SELECT * FROM TTimesheet WHERE EmployeeId = {0} AND [Date] = {1}", EmpId, dateTime.ToString(date)).ToList();
if (getClockOutTime.Count <= 0)
{
}
else
{
if (getClockOutTime[0].ClockOutTime != TimeSpan.Zero)
{
TimeSpan clockInTime = getClockOutTime[0].CLockInTime;
TimeSpan clockOutTime = getClockOutTime[0].ClockOutTime;
TimeSpan timeDiff = clockOutTime - clockInTime;
decimal totalHours = Convert.ToDecimal(timeDiff.TotalHours);
var insertTime = db.ExecuteQuery <TTimesheet>(@"UPDATE [dbo].[TTimesheet] SET [TotalHours] = {0} WHERE ([EmployeeId] = {1}) AND ([Date] = {2});
SELECT TimesheetId, EmployeeId, Date, CLockInTime, ClockOutTime, TotalHours FROM TTimesheet", totalHours, EmpId, dateTime.ToString(date));
}
else
{
MessageBox.Show("first clockout Yourself");
}
}
}
public void EmployeeClockOut(int EmpId)
{
DateTime dateTime = DateTime.Now;
string time = "HH:mm:ss";
string date = "yyyy-MM-dd";
LinqToSQLDataContext db = new LinqToSQLDataContext();
var checkIfEmpIsClockedOut = db.ExecuteQuery <TTimesheet>(@"SELECT * FROM TTimesheet WHERE EmployeeId = {0} AND [Date] = {1}", EmpId, dateTime.ToString(date)).ToList();
if (checkIfEmpIsClockedOut.Count > 0)
{
if (checkIfEmpIsClockedOut[0].CLockInTime == TimeSpan.Zero)
{
MessageBox.Show("You have to first clock in");
}
else
{
if (checkIfEmpIsClockedOut[0].ClockOutTime == TimeSpan.Zero)
{
var query = db.ExecuteQuery <TTimesheet>(@"UPDATE [dbo].[TTimesheet] SET [ClockOutTime] = {0} WHERE ([EmployeeId] = {1}) AND ([Date] = {2});
SELECT TimesheetId, EmployeeId, Date, CLockInTime, ClockOutTime, TotalHours FROM TTimesheet",
dateTime.ToString(time), EmpId, dateTime.ToString(date));
MessageBox.Show("successfully clockout");
}
else
{
MessageBox.Show("you are already clocked out");
}
}
}
else
{
MessageBox.Show("You have to first clock in");
}
}
public List <TEmployee> GetEmployeeDetails(string firstname)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
var query = db.ExecuteQuery <TEmployee>(@"SELECT * FROM TEmployee WHERE FirstName = {0}", firstname);
return(query.ToList());
}
public List <TTimesheet> GetTimesheetInfo(int id, DateTime date)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
string sqlDate = "'" + date.ToShortDateString() + "'";
var query = db.ExecuteQuery <TTimesheet>(@"SELECT * FROM TTimesheet WHERE EmployeeId = " + id + " AND [Date] = " + sqlDate);
return(query.ToList());
}
public List <TPayslip> AddToPayslip(int id, DateTime start, DateTime end, decimal totalHours, decimal totalMoney)
{
LinqToSQLDataContext db = new LinqToSQLDataContext();
try
{
var query = db.ExecuteQuery <TPayslip>(@"INSERT INTO TPayslip (EmployeeId, DateFrom, EndDate, TotalHours, ToTalMoney)
VALUES({0},{1},{2},{3},{4});
SELECT * FROM TPayslip WHERE EmployeeId = {5}", id, start.ToShortDateString(), end.ToShortDateString(), totalHours, totalMoney, id).ToList();
return(query);
}
catch (Exception)
{
}
return(null);
}