private static bool CheckSameSlotExists(int userId, DateTime startTime, DateTime endTime) { using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); string sql = "select * from AVAILABLE_TIME_SLOTS where user_id = @userId and start_time = @startTime and end_time = @endTime"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.AddWithValue("@startTime", startTime); cmd.Parameters.AddWithValue("@endTime", endTime); SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Close(); return(true); } else { reader.Close(); return(false); } } }
public static void SetTimeSlot(int userId, DateTime startTime, DateTime endTime) { using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); DateTime endTimeTemp = startTime.AddHours(1); DateTime startTimeTemp = startTime; while (endTimeTemp <= endTime) { if (!CheckSameSlotExists(userId, startTimeTemp, endTimeTemp)) { string sql = "insert into AVAILABLE_TIME_SLOTS (user_id, start_time, end_time) values( @userId, @startTime , @endTime )"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.AddWithValue("@startTime", startTimeTemp); cmd.Parameters.AddWithValue("@endTime", endTimeTemp); cmd.ExecuteNonQuery(); } startTimeTemp = startTimeTemp.AddHours(1); endTimeTemp = endTimeTemp.AddHours(1); } cnn.Close(); } }
internal static bool IsUsersExists(int[] interwieverIDValues, short role) { using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); StringBuilder sb = new StringBuilder(); int i = 1; foreach (int id in interwieverIDValues) { sb.Append("@userId" + i.ToString() + ","); i++; } string inClause = sb.ToString().Substring(0, sb.ToString().Length - 1); string sql = "select * from users where id in (" + inClause + ") and role = @role"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); i = 1; foreach (int id in interwieverIDValues) { cmd.Parameters.AddWithValue("@userId" + i.ToString(), id); i++; } cmd.Parameters.AddWithValue("@role", role); var returnedRows = cmd.ExecuteScalar(); if (returnedRows == null) { return(false); } else { return(true); } } }
public static void QueryAvailabilities(int candidateIdValue, int[] interwieverIDValues) { using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { List <DateTime> candidateTimes = new List <DateTime>(); cnn.Open(); string sql = "select * from requested_time_slots where user_id = @candidate_id"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); cmd.Parameters.AddWithValue("@candidate_id", candidateIdValue); bool isSlotExists = false; SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { DateTime start_time = (DateTime)reader["start_time"]; DateTime end_time = (DateTime)reader["end_time"]; StringBuilder sb = new StringBuilder(); int i = 1; foreach (int id in interwieverIDValues) { sb.Append("@userId" + i.ToString() + ","); i++; } string inClause = sb.ToString().Substring(0, sb.ToString().Length - 1); string sqlInt = "select * from available_time_slots tim join users usr on tim.user_id = usr.id where tim.user_id in (" + inClause + ") and tim.start_time >= @start_Time and tim.end_time <= @end_time"; SQLiteCommand cmdInt = new SQLiteCommand(sqlInt, cnn); i = 1; foreach (int id in interwieverIDValues) { cmdInt.Parameters.AddWithValue("@userId" + i.ToString(), id); i++; } cmdInt.Parameters.AddWithValue("start_time", start_time); cmdInt.Parameters.AddWithValue("end_time", end_time); SQLiteDataReader readerInt = cmdInt.ExecuteReader(); while (readerInt.Read()) { isSlotExists = true; Console.WriteLine("Available time slot for interviewer " + readerInt["user_id"] + " - " + readerInt["user_name"] + " : " + readerInt["start_Time"] + " - " + readerInt["end_time"]); } } if (!isSlotExists) { Console.WriteLine("No available slot exists!"); } } }
public static void RequestTimeSlot(int userId, DateTime startTime, DateTime endTime) { if (CheckSameSlotExists(userId, startTime, endTime)) { return; } using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); string sql = "insert into REQUESTED_TIME_SLOTS (user_id, start_time, end_time) values( @userId, @startTime , @endTime )"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.AddWithValue("@startTime", startTime); cmd.Parameters.AddWithValue("@endTime", endTime); cmd.ExecuteNonQuery(); } }
internal static bool IsUserExists(int userId, short roleID) { using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); string sql = "select * from users where id = @user_id and role = @roleId"; SQLiteCommand cmd = new SQLiteCommand(sql, cnn); cmd.Parameters.AddWithValue("@user_id", userId); cmd.Parameters.AddWithValue("@roleId", roleID); var returnedRows = cmd.ExecuteScalar(); if (returnedRows == null) { return(false); } else { return(true); } } }
public static int CreateUser(string userName, short role) { if (string.IsNullOrEmpty(userName)) { throw new Exception("Please enter a valid user name!"); } using (var cnn = SqLiteBaseRepository.SimpleDbConnection()) { cnn.Open(); string sql = "insert into users (user_name, role) values(@user_name, @role)"; SQLiteCommand command = new SQLiteCommand(sql, cnn); command.Parameters.AddWithValue("@user_name", userName); command.Parameters.AddWithValue("@role", role); command.ExecuteNonQuery(); string sqlRowId = "SELECT last_insert_rowid()"; SQLiteCommand cmd = new SQLiteCommand(sqlRowId, cnn); return(Convert.ToInt32(cmd.ExecuteScalar())); } }