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