public void Save(UserRegistration user, OperationType operation, string cvr)
{
long user_id = 0;
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
using (DbCommand command = DaoUtil.GetCommand(UserStatements.Insert, connection))
{
command.Transaction = transaction;
command.Parameters.Add(DaoUtil.GetParameter("@uuid", user.Uuid));
command.Parameters.Add(DaoUtil.GetParameter("@shortkey", user.ShortKey ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@user_id", user.UserId ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@phone_number", user.PhoneNumber ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@name", user.Person.Name ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@cpr", user.Person.Cpr ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@email", user.Email ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@racfid", user.RacfID ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@location", user.Location ?? (object)DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@cvr", cvr));
command.Parameters.Add(DaoUtil.GetParameter("@operation", operation.ToString()));
user_id = Convert.ToInt64(command.ExecuteScalar());
}
// insert positions
foreach (Position position in user.Positions ?? Enumerable.Empty <Position>())
{
using (DbCommand command = DaoUtil.GetCommand(UserStatements.InsertPositions, connection))
{
command.Transaction = transaction;
command.Parameters.Add(DaoUtil.GetParameter("@user_id", user_id));
command.Parameters.Add(DaoUtil.GetParameter("@name", position.Name));
command.Parameters.Add(DaoUtil.GetParameter("@orgunit_uuid", position.OrgUnitUuid));
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
public void OnSuccess(long id)
{
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbCommand command = DaoUtil.GetCommand(UserStatements.Success, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", id));
command.ExecuteNonQuery();
}
}
}
public void Delete(long id)
{
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Delete, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", id));
command.ExecuteNonQuery();
}
}
}
public void OnFailure(long id, String errorMessage)
{
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbCommand command = DaoUtil.GetCommand(UserStatements.Failure, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", id));
command.Parameters.Add(DaoUtil.GetParameter("@error", errorMessage));
command.ExecuteNonQuery();
}
}
}
public static void InitializeDatabase()
{
if (!string.IsNullOrEmpty(OrganisationRegistryProperties.GetInstance().DBConnectionString))
{
using (DbConnection connection = DaoUtil.GetConnection())
{
try
{
string location = OrganisationRegistryProperties.GetInstance().MigrationScriptsPath;
if (OrganisationRegistryProperties.GetInstance().Database.Equals(DatabaseType.MSSQL))
{
var evolve = new Evolve.Evolve(connection, msg => log.Info(msg))
{
Locations = new[] { location },
Schemas = new[] { "dbo" }, // default schema can be NULL in SQL Server, which makes Evolve unhappy
IsEraseDisabled = true
};
evolve.Migrate();
}
else
{
var evolve = new Evolve.Evolve(connection, msg => log.Info(msg))
{
Locations = new[] { location },
IsEraseDisabled = true
};
evolve.Migrate();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public List <UserRegistrationExtended> Get4OldestEntries()
{
var users = new List <UserRegistrationExtended>();
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbCommand command = DaoUtil.GetCommand(UserStatements.Select, connection))
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
UserRegistrationExtended user = new UserRegistrationExtended();
long user_id = (long)reader["id"];
user.Id = user_id;
user.PhoneNumber = GetValue(reader, "phone_number");
user.Email = GetValue(reader, "email");
user.RacfID = GetValue(reader, "racfid");
user.Location = GetValue(reader, "location");
user.UserId = GetValue(reader, "user_id");
user.Cvr = GetValue(reader, "cvr");
user.Uuid = GetValue(reader, "uuid");
user.ShortKey = GetValue(reader, "shortkey");
user.Person.Name = GetValue(reader, "name");
user.Person.Cpr = GetValue(reader, "cpr");
user.Timestamp = (DateTime)reader["timestamp"];
user.Operation = (OperationType)Enum.Parse(typeof(OperationType), GetValue(reader, "operation"));
users.Add(user);
}
}
}
foreach (var user in users)
{
// read positions
using (DbCommand command = DaoUtil.GetCommand(UserStatements.SelectPositions, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", user.Id));
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Position position = new Position();
position.OrgUnitUuid = GetValue(reader, "orgunit_uuid");
position.Name = GetValue(reader, "name");
user.Positions.Add(position);
}
}
}
}
}
return(users);
}
public void Save(OrgUnitRegistration ou, OperationType operation, string cvr)
{
long orgunit_id = 0;
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Insert, connection))
{
command.Transaction = transaction;
command.Parameters.Add(DaoUtil.GetParameter("@uuid", ou.Uuid));
command.Parameters.Add(DaoUtil.GetParameter("@shortkey", (object)ou.ShortKey ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@name", (object)ou.Name ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@parent_ou_uuid", (object)ou.ParentOrgUnitUuid ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@payout_ou_uuid", (object)ou.PayoutUnitUuid ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@cvr", cvr));
command.Parameters.Add(DaoUtil.GetParameter("@operation", operation.ToString()));
command.Parameters.Add(DaoUtil.GetParameter("@orgunit_type", ou.Type.ToString()));
command.Parameters.Add(DaoUtil.GetParameter("@los_shortname", (object)ou.LOSShortName ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@los_id", (object)ou.LOSId ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@phone_number", (object)ou.PhoneNumber ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@url", (object)ou.Url ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@landline", (object)ou.Landline ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@email", (object)ou.Email ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@location", (object)ou.Location ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@ean", (object)ou.Ean ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@contact_open_hours", (object)ou.ContactOpenHours ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@dtr_id", (object)ou.DtrId ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@email_remarks", (object)ou.EmailRemarks ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@contact", (object)ou.Contact ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@post_return", (object)ou.PostReturn ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@phone_open_hours", (object)ou.PhoneOpenHours ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@post", (object)ou.Post ?? DBNull.Value));
command.Parameters.Add(DaoUtil.GetParameter("@manager_uuid", (object)ou.ManagerUuid ?? DBNull.Value));
orgunit_id = Convert.ToInt64(command.ExecuteScalar());
}
// insert tasks
foreach (string task in ou.Tasks ?? Enumerable.Empty <string>())
{
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.InsertTasks, connection))
{
command.Transaction = transaction;
command.Parameters.Add(DaoUtil.GetParameter("@orgunit_id", orgunit_id));
command.Parameters.Add(DaoUtil.GetParameter("@task", task));
command.ExecuteNonQuery();
}
}
// insert contact for tasks
foreach (string task in ou.ContactForTasks ?? Enumerable.Empty <string>())
{
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.InsertContactForTasks, connection))
{
command.Transaction = transaction;
command.Parameters.Add(DaoUtil.GetParameter("@orgunit_id", orgunit_id));
command.Parameters.Add(DaoUtil.GetParameter("@task", task));
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
public List <OrgUnitRegistrationExtended> Get4OldestEntries()
{
List <OrgUnitRegistrationExtended> result = new List <OrgUnitRegistrationExtended>();
using (DbConnection connection = DaoUtil.GetConnection())
{
connection.Open();
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Select, connection))
{
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
long orgUnitId = (long)reader["id"];
var orgUnit = new OrgUnitRegistrationExtended();
orgUnit.Id = orgUnitId;
orgUnit.Uuid = GetValue(reader, "uuid");
orgUnit.ShortKey = GetValue(reader, "shortkey");
orgUnit.Name = GetValue(reader, "name");
orgUnit.ParentOrgUnitUuid = GetValue(reader, "parent_ou_uuid");
orgUnit.PayoutUnitUuid = GetValue(reader, "payout_ou_uuid");
orgUnit.ManagerUuid = GetValue(reader, "manager_uuid");
orgUnit.LOSShortName = GetValue(reader, "los_shortname");
orgUnit.LOSId = GetValue(reader, "losid");
orgUnit.PhoneNumber = GetValue(reader, "phone_number");
orgUnit.Email = GetValue(reader, "email");
orgUnit.Landline = GetValue(reader, "landline");
orgUnit.Url = GetValue(reader, "url");
orgUnit.Location = GetValue(reader, "location");
orgUnit.Ean = GetValue(reader, "ean");
orgUnit.Post = GetValue(reader, "post_address");
orgUnit.ContactOpenHours = GetValue(reader, "contact_open_hours");
orgUnit.DtrId = GetValue(reader, "dtr_id");
orgUnit.EmailRemarks = GetValue(reader, "email_remarks");
orgUnit.Contact = GetValue(reader, "contact");
orgUnit.PostReturn = GetValue(reader, "post_return");
orgUnit.PhoneOpenHours = GetValue(reader, "phone_open_hours");
orgUnit.Operation = (OperationType)Enum.Parse(typeof(OperationType), GetValue(reader, "operation"));
orgUnit.Cvr = GetValue(reader, "cvr");
// type can be null on DELETEs
string tmpType = GetValue(reader, "orgunit_type");
if (!string.IsNullOrEmpty(tmpType))
{
orgUnit.Type = (OrgUnitType)Enum.Parse(typeof(OrgUnitType), tmpType);
}
else
{
orgUnit.Type = OrgUnitType.DEPARTMENT;
}
result.Add(orgUnit);
}
}
}
foreach (var orgUnit in result)
{
orgUnit.Tasks = new List <string>();
orgUnit.ContactForTasks = new List <string>();
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.SelectTasks, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", orgUnit.Id));
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string task = GetValue(reader, "task");
orgUnit.Tasks.Add(task);
}
}
}
using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.SelectContactForTasks, connection))
{
command.Parameters.Add(DaoUtil.GetParameter("@id", orgUnit.Id));
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string task = GetValue(reader, "task");
orgUnit.ContactForTasks.Add(task);
}
}
}
}
}
return(result);
}