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); }