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);
}
}
}
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 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();
}
}
public IActionResult Up(string key, string value)
{
var repo = new SqLiteBaseRepository();
repo.Save(key, value);
return(View("Poll", key));
}
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!");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
ISqLiteBaseRepository sqlRepo = new SqLiteBaseRepository();
if (!sqlRepo.CheckExistingDB(sqlRepo.DbFile))
{
using (var cnn = sqlRepo.MySQLiteConnection())
{
cnn.Open();
sqlRepo.ResetDefault(cnn);
sqlRepo.SetupAppInfo(cnn);
sqlRepo.SetupDun(cnn);
sqlRepo.SetupMakkebun(cnn);
sqlRepo.SetupParlimen(cnn);
sqlRepo.SetupVariables(cnn);
}
}
}
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()));
}
}
private static void ProcessFirst()
{
//Application.Run(new InitializeForm());
//return;
ISqLiteBaseRepository sqlRepo = new SqLiteBaseRepository();
if (!sqlRepo.CheckExistingDB(sqlRepo.DbFile))
{
using (var cnn = sqlRepo.MySQLiteConnection())
{
cnn.Open();
sqlRepo.SetupVariableSetting(cnn);
sqlRepo.SetupVariables(cnn);
sqlRepo.SetupTBangsa(cnn);
sqlRepo.SetupDaerah(cnn);
sqlRepo.SetupDun(cnn);
sqlRepo.SetupParlimen(cnn);
sqlRepo.SetupAppInfo(cnn);
sqlRepo.SetupMakkebun(cnn);
sqlRepo.SetupSemakTapak(cnn);
}
}
//using (var cnn = sqlRepo.MySQLiteConnection())
//{
// cnn.Open();
// sqlRepo.ResetDefault(cnn);
// sqlRepo.SetupAppInfo(cnn);
// sqlRepo.SetupDun(cnn);
// sqlRepo.SetupMakkebun(cnn);
// sqlRepo.SetupParlimen(cnn);
// sqlRepo.SetupVariables(cnn);
// sqlRepo.SetupDaerah(cnn);
// sqlRepo.SetupVariableSetting(cnn);
// sqlRepo.SetupTBangsa(cnn);
//}
//IBangsaRepo BangsaRepo = new BangsaRepo();
//BangsaRepo.SyncBangsaFromAppInfoMySQL();
}
public BsNewResult Execute()
{
try
{
using (OpConn)
{
BaseRepo = new SqLiteBaseRepository(OpConn);
OpConn.Open();
SQLiteTransaction tran = OpConn.BeginTransaction();
MethodBase methodBase = new StackFrame(1).GetMethod();
if (methodBase.ReflectedType != null)
{
BsTrace.WriteLine("", methodBase.ReflectedType.Name + " - " + methodBase.Name, TraceLvl.INF);
}
DoJob();
if (Result.OpType == OpType.Successful)
{
Result.Message = "Başarıyla tamamlandı";
}
tran.Commit();
}
}
catch (BsException ex)
{
Result.Message = ex.Message;
Result.OpType = ex.OpType;
}
catch (Exception ex)
{
Result.Message = ex.Message;
Result.OpType = OpType.SystemError;
Result.Exception = ex.InnerException;
BsTrace.WriteLine(ex.Message, GetType().Name, TraceLvl.ERR);
}
return(Result);
}
