public List<ProductionWorker> GetEmployees(string searchWord, string searchField, int limit, int page)
{
int RowCount = 0;
connection.Open();
var getRecords = connection.CreateCommand();
var getTotalRecords = connection.CreateCommand();
getRecords.CommandText = "select GUID GUID, firstname FirstName, lastname LastName, department Department, role Role, dtCreated DTCreated,dtModified DTModified,dtDeleted DTDeleted, login Login from Employee where dtdeleted>'" + DateTime.Now.Ticks + "';";
getTotalRecords.CommandText = "select Count(*) from Employee where dtdeleted>"+DateTime.Now.Ticks + ";";
if (limit > 0)
getRecords.CommandText += " limit " + limit;
if (limit > 0)
getRecords.CommandText += " offset " + (page - 1) * limit;
getRecords.CommandType = CommandType.Text;
RowCount = Convert.ToInt32(getTotalRecords.ExecuteScalar());
SqliteDataReader sqlite_datareader = getRecords.ExecuteReader();
//sqlite_datareader.HasRows;
List<ProductionWorker> typeData = sqlite_datareader.Cast<IDataRecord>()
.Select(dr => new ProductionWorker
{
GUID = (string)dr["GUID"],
FirstName = (string)dr["firstname"],
LastName = (string)dr["lastname"],
Department = (string)dr["department"],
Role = (string)dr["role"],
DTCreated = new DateTime(long.Parse(dr["dtCreated"].ToString())),
DTModified = new DateTime(long.Parse(dr["dtModified"].ToString())),
DTDeleted = new DateTime(long.Parse(dr["dtDeleted"].ToString())),
Login = (string)dr["login"]
}).ToList();
return typeData;
}
public List <Shift> GetShifts()
{
int RowCount = 0;
connection.Open();
var getRecords = connection.CreateCommand();
var getTotalRecords = connection.CreateCommand();
getRecords.CommandText = "select DayOfWeekFlags DayOfWeekFlags, StartDate StartDate, StartTime StartTime, Duration Duration, Category Category from Shift;";
getTotalRecords.CommandText = "select Count(*) from Shift ;";
getRecords.CommandType = CommandType.Text;
RowCount = Convert.ToInt32(getTotalRecords.ExecuteScalar());
SqliteDataReader sqlite_datareader = getRecords.ExecuteReader();
List <Shift> typeData = sqlite_datareader.Cast <IDataRecord>()
.Select(dr => new Shift
{
DayOfWeekFlags = (DayOfWeekFlag)Enum.Parse(typeof(DayOfWeekFlag), dr["DayOfWeekFlags"].ToString()),
StartDate = DateTime.Parse(dr["StartDate"].ToString()),
StartTime = (TimeSpan)TimeSpan.Parse(dr["StartTime"].ToString()),
Duration = (TimeSpan)TimeSpan.Parse(dr["Duration"].ToString()),
Category = (EventCategory)Enum.Parse(typeof(EventCategory), dr["Category"].ToString())
}).ToList();
return(typeData);
}
public List<HolidayLog> GetLeaves(string employeeID)
{
connection.Open();
//var getRecords = connection.CreateCommand();
var getTotalRecords = connection.CreateCommand();
getTotalRecords.CommandText = "select * from Log where resource='" + employeeID + "' and type in ("+
(int)LogType.PaidLeave+","+
(int)LogType.UnpaidLeave + "," +
(int)LogType.SickLeave+") and comment like '%[1]%'";
getTotalRecords.CommandType = CommandType.Text;
SqliteDataReader sqlite_datareader = getTotalRecords.ExecuteReader();
List<HolidayLog> logList = sqlite_datareader.Cast<IDataRecord>()
.Select(dr => new HolidayLog((string)dr["Comment"])
{
OID = dr["guid"].ToString(),
Type = (LogType)int.Parse(dr["Type"].ToString()),
//Resource = (Employee)dr["Resource"],
//Comment = (string)dr["Comment"],
DTCreated = new DateTime(long.Parse(dr["DTCreated"].ToString()))
}).ToList();
connection.Close();
return logList;
}
public List <Log> GetLogs(string guid, DateTime from, DateTime till)
{
connection.Open();
var insertCmd = connection.CreateCommand();
insertCmd.CommandText = "Select guid guid, DTCreated DTCreated,Type Type,Resource Resource,Comment Comment from Log where DTCreated between '" + from.Ticks + "' and '" + till.Ticks + "'";
if (!string.IsNullOrEmpty(guid))
{
insertCmd.CommandText += " and Resource='" + guid + "';";
}
insertCmd.ExecuteNonQuery();
SqliteDataReader sqlite_datareader = insertCmd.ExecuteReader();
//sqlite_datareader.HasRows;
List <Log> typeData = sqlite_datareader.Cast <IDataRecord>()
.Select(dr => new Log
{
OID = dr["guid"].ToString(),
Type = (LogType)int.Parse(dr["Type"].ToString()),
//Resource = (Employee)dr["Resource"],
Comment = (string)dr["Comment"],
DTCreated = new DateTime(long.Parse(dr["DTCreated"].ToString()))
}).ToList();
connection.Close();
return(typeData);
}
public static async Task <IEnumerable <IDataRecord> > ExecuteReadAllAsync(string sql, IEnumerable <KeyValuePair <string, object> > parameters = null)
{
using (SqliteCommand command = await GetCommand(sql, parameters))
{
using (SqliteDataReader reader = await command.ExecuteReaderAsync())
{
return(reader.Cast <IDataRecord>().ToArray());
}
}
}
public List<ShiftLog> LogWorkingHours(string employeeID)
{
connection.Open();
var getTotalRecords = connection.CreateCommand();
getTotalRecords.CommandText = "select guid guid, dtcreated DTCreated, type, comment from Log where resource='" + employeeID + "' and type in (0,1) order by dtcreated ASC ;";
getTotalRecords.CommandType = CommandType.Text;
SqliteDataReader sqlite_datareader = getTotalRecords.ExecuteReader();
List<Log> logList = sqlite_datareader.Cast<IDataRecord>()
.Select(dr => new Log
{
OID = dr["guid"].ToString(),
Type = (LogType)int.Parse(dr["Type"].ToString()),
//Resource = (Employee)dr["Resource"],
Comment = (string)dr["Comment"],
DTCreated = new DateTime(long.Parse(dr["DTCreated"].ToString()))
}).ToList();
connection.Close();
IShiftService _shiftService = new ShiftService();
List<Shift> shiftList = _shiftService.GetShifts();
List<ShiftLog> shiftLogList = new List<ShiftLog>();
for (int a=0; a< logList.Count;a++)
{
ShiftLog sh = new ShiftLog();
sh.TimeFrom = logList[a].DTCreated;
// Fetch rate for this shift from comments
if (!string.IsNullOrEmpty(logList[a].Comment))
sh.Rate = double.Parse(logList[a].Comment);
if (a!= logList.Count-1 && logList[a + 1].Type == LogType.Logout)
{
// in case worker logs in and out we skip second iteration.
sh.TimeTill = logList[a + 1].DTCreated;
TimeSpan durationTmsp = sh.TimeTill.TimeOfDay - sh.TimeFrom.TimeOfDay;
sh.Hours = Math.Round(durationTmsp.TotalHours,2);
sh.DisplayHours = string.Format("{0}:{1}", durationTmsp.Hours, durationTmsp.Minutes);
a++;
}
else
{
// If worker didnt log out set default date span
Shift ss = shiftList.FirstOrDefault(shft => shft.Category == EventCategory.WorkingTime);
TimeSpan durationTmsp = ss.EndTime - sh.TimeFrom.TimeOfDay;
sh.Hours = Math.Round(durationTmsp.TotalHours,2);
sh.DisplayHours = string.Format("{0}:{1}", durationTmsp.Hours, durationTmsp.Minutes);
sh.TimeTill = sh.TimeFrom.AddMinutes(durationTmsp.TotalMinutes);// Temporary; TODO: add shift dictionary to get default working hours
}
shiftLogList.Add(sh);
}
return shiftLogList;
}
private bool IsOnLeave(string employeeID)
{
connection.Open();
var getTotalRecords = connection.CreateCommand();
getTotalRecords.CommandText = "select * from Log where resource='" + employeeID + "'and type = 2 and comment like '%[1]%' order by dtcreated DESC";
getTotalRecords.CommandType = CommandType.Text;
SqliteDataReader sqlite_datareader = getTotalRecords.ExecuteReader();
List<HolidayLog> logList = sqlite_datareader.Cast<IDataRecord>()
.Select(dr => new HolidayLog((string)dr["Comment"])
{
OID = dr["guid"].ToString(),
Type = (LogType)int.Parse(dr["Type"].ToString()),
//Resource = (Employee)dr["Resource"],
//Comment = (string)dr["Comment"],
DTCreated = new DateTime(long.Parse(dr["DTCreated"].ToString()))
}).ToList();
connection.Close();
return logList.Exists(hlog => DateTime.Now.Date > hlog.DateFrom.Date && DateTime.Now.Date < hlog.DateTill);
}
public ReturnObject GetUsers(int limit, int page = 1)
{
ReturnObject rObject = new ReturnObject();
int RowCount = 0;
//var connectionStringBuilder = new SqliteConnectionStringBuilder();
////Use DB in project directory. If it does not exist, create it:
//connectionStringBuilder.DataSource = "./SqliteDB.db";
//using (var connection = new SqliteConnection(connectionStringBuilder.ConnectionString))
//{
// connection.Open();
// //Create a table (drop if already exists first):
// var delTableCmd = connection.CreateCommand();
// delTableCmd.CommandText = "DROP TABLE IF EXISTS favorite_beers";
// delTableCmd.ExecuteNonQuery();
// var createTableCmd = connection.CreateCommand();
// createTableCmd.CommandText = "CREATE TABLE favorite_beers(name VARCHAR(50))";
// createTableCmd.ExecuteNonQuery();
// //Seed some data:
// using (var transaction = connection.BeginTransaction())
// {
// var insertCmd = connection.CreateCommand();
// insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('LAGUNITAS IPA')";
// insertCmd.ExecuteNonQuery();
// insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('JAI ALAI IPA')";
// insertCmd.ExecuteNonQuery();
// insertCmd.CommandText = "INSERT INTO favorite_beers VALUES('RANGER IPA')";
// insertCmd.ExecuteNonQuery();
// transaction.Commit();
// }
// //Read the newly inserted data:
// var selectCmd = connection.CreateCommand();
// selectCmd.CommandText = "SELECT name FROM favorite_beers";
// using (var reader = selectCmd.ExecuteReader())
// {
// while (reader.Read())
// {
// var message = reader.GetString(0);
// Console.WriteLine(message);
// }
// }
//}
connection.Open();
var getRecords = connection.CreateCommand();
var getTotalRecords = connection.CreateCommand();
getRecords.CommandText = "select OID OID,title Title, firstname FirstName, lastname LastName, email Email, dob DOB, phonenumber PhoneNumber from User";
getTotalRecords.CommandText = "select Count(*) from User";
if (limit > 0)
{
getRecords.CommandText += " limit " + limit;
}
if (limit > 0)
{
getRecords.CommandText += " offset " + (page - 1) * limit;
}
getRecords.CommandType = CommandType.Text;
RowCount = Convert.ToInt32(getTotalRecords.ExecuteScalar());
SqliteDataReader sqlite_datareader = getRecords.ExecuteReader();
//sqlite_datareader.HasRows;
IEnumerable <User> typeData = sqlite_datareader.Cast <IDataRecord>()
.Select(dr => new User {
OID = (string)dr["OID"],
Title = (string)dr["title"],
FirstName = (string)dr["firstname"],
LastName = (string)dr["lastname"],
DOB = (string)dr["dob"],
PhoneNumber = (string)dr["phonenumber"],
Email = (string)dr["email"]
});
if (page <= 0)
{
page = 1;
}
if (limit <= 0)
{
limit = 1;
}
rObject.CurrPage = page;
rObject.Limit = limit;
rObject.UserList = typeData.ToList();
rObject.NumOfRecords = RowCount;
rObject.TotalPages = (int)Math.Ceiling((decimal)RowCount / limit);
connection.Close();
return(rObject);
}
