public void Delete(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "delete from ddt_journal WHERE dsid_journal_day = '" + id + "' ";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
command.ExecuteScalar();
sql = "delete from ddt_journal_day WHERE r_object_id = '" + id + "' ";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
command.ExecuteScalar();
}
}
private int GetAccounts(DateTime startdate, DateTime enddate, int userid, int sauserid, string volume)
{
int accountscount;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAccountsQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("sauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("startdate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("volume", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = userid;
command.Parameters[1].Value = sauserid;
command.Parameters[2].Value = startdate;
command.Parameters[3].Value = enddate;
command.Parameters[4].Value = volume;
int dr;
dr = Convert.ToInt32(command.ExecuteScalar());
accountscount = dr;
}
}
return(accountscount);
}
public int GetNonAllocatedResourcesCountFromPostgres(bool forDelivery = true)
{
int count = 0;
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_DELIVERY;
if (!forDelivery)
{
qry = PostgresSqlQueries.GET_COUNT_OF_NON_ALLOCATED_EMPLOYEES_FROM_NON_DELIVERY;
}
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
object res = cmd.ExecuteScalar();
int.TryParse(res.ToString(), out count);
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(count);
}
/// <summary>
/// The result of the registration. 1 means success, 0 means already registered.
/// </summary>
/// <returns></returns>
private bool Register()
{
try
{
int registerID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@queueName", _queueName);
cmd.Parameters.AddWithValue("@consumeName", _consumerName);
cmd.CommandText = "select * from pgq.register_consumer(@queueName,@consumeName);";
registerID = (int)cmd.ExecuteScalar();
}
}
return(REGISTER_SUCCESS == registerID);
}
catch (Exception e)
{
_logger.LogError($"[PgMQProvider.Register.Exception]: {e.ToString()}");
return(false);
}
}
} // End Function GetConnectionString
public static void CreateUser(string userName, string password)
{
string role = "CREATE ROLE \"" + userName.Replace("\"", "\"\"") + "\" "
+ "WITH PASSWORD '" + password.Replace("'", "''") + "' "
+ "LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity'; ";
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
// https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
// https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
cmd.CommandText = "SELECT COUNT(*) FROM pg_roles WHERE rolname = '" + userName.Replace("'", "''") + "'; ";
// cmd.CommandText = "SELECT COUNT(*) FROM pg_catalog.pg_user WHERE usename = '" + userName.Replace("'", "''") + "'";
long countOfExistingUsersWithThisName = (long)cmd.ExecuteScalar();
if (countOfExistingUsersWithThisName == 0)
{
cmd.CommandText = role;
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub CreateUser
/// <summary>
/// Get the ID of the next batch to be processed.
/// </summary>
/// <returns>The next batch ID to process, or null if there are no more events available.</returns>
private long?GetNextBatchId()
{
long?batchID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@queueName", _queueName);
cmd.Parameters.AddWithValue("@consumeName", _consumerName);
cmd.CommandText = "select * from pgq.next_batch(@queueName,@consumeName);";
if (long.TryParse(cmd.ExecuteScalar().ToString(), out long result))
{
batchID = result;
}
else
{
batchID = null;
}
}
}
return(batchID);
}
public string Save(DdtConsilium obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_consilium SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsdt_consilium_date = @ConsiliumDate, " +
"dss_goal = @Goal, " +
"dss_dynamics = @Dynamics, " +
"dss_diagnosis = @Diagnosis, " +
"dss_decision = @Decision, " +
"dss_duty_admin_name = @DutyAdminName " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@ConsiliumDate", obj.ConsiliumDate);
cmd.Parameters.AddWithValue("@Goal", obj.Goal == null ? "" : obj.Goal);
cmd.Parameters.AddWithValue("@Dynamics", obj.Dynamics == null ? "" : obj.Dynamics);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
cmd.Parameters.AddWithValue("@Decision", obj.Decision == null ? "" : obj.Decision);
cmd.Parameters.AddWithValue("@DutyAdminName", obj.DutyAdminName == null ? "" : obj.DutyAdminName);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_consilium(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_consilium_date,dss_goal,dss_dynamics,dss_diagnosis,dss_decision,dss_duty_admin_name) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@ConsiliumDate,@Goal,@Dynamics,@Diagnosis,@Decision,@DutyAdminName) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@ConsiliumDate", obj.ConsiliumDate);
cmd.Parameters.AddWithValue("@Goal", obj.Goal == null ? "" : obj.Goal);
cmd.Parameters.AddWithValue("@Dynamics", obj.Dynamics == null ? "" : obj.Dynamics);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
cmd.Parameters.AddWithValue("@Decision", obj.Decision == null ? "" : obj.Decision);
cmd.Parameters.AddWithValue("@DutyAdminName", obj.DutyAdminName == null ? "" : obj.DutyAdminName);
return((string)cmd.ExecuteScalar());
}
}
}
}
public string Save(DdtInspection obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_inspection SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsdt_inspection_date = @InspectionDate, " +
"dss_diagnosis = @Diagnosis, " +
"dss_complaints = @Complaints, " +
"dss_inspection = @Inspection, " +
"dss_kateter_placement = @KateterPlacement, " +
"dss_inspection_result = @InspectionResult " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@InspectionDate", obj.InspectionDate);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
cmd.Parameters.AddWithValue("@Complaints", obj.Complaints == null ? "" : obj.Complaints);
cmd.Parameters.AddWithValue("@Inspection", obj.Inspection == null ? "" : obj.Inspection);
cmd.Parameters.AddWithValue("@KateterPlacement", obj.KateterPlacement == null ? "" : obj.KateterPlacement);
cmd.Parameters.AddWithValue("@InspectionResult", obj.InspectionResult == null ? "" : obj.InspectionResult);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_inspection(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_inspection_date,dss_diagnosis,dss_complaints,dss_inspection,dss_kateter_placement,dss_inspection_result) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@InspectionDate,@Diagnosis,@Complaints,@Inspection,@KateterPlacement,@InspectionResult) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@InspectionDate", obj.InspectionDate);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
cmd.Parameters.AddWithValue("@Complaints", obj.Complaints == null ? "" : obj.Complaints);
cmd.Parameters.AddWithValue("@Inspection", obj.Inspection == null ? "" : obj.Inspection);
cmd.Parameters.AddWithValue("@KateterPlacement", obj.KateterPlacement == null ? "" : obj.KateterPlacement);
cmd.Parameters.AddWithValue("@InspectionResult", obj.InspectionResult == null ? "" : obj.InspectionResult);
return((string)cmd.ExecuteScalar());
}
}
}
}
public string Save(DdtTransfer obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_transfer SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsdt_start_date = @StartDate, " +
"dsdt_end_date = @EndDate, " +
"dss_destination = @Destination, " +
"dss_contacts = @Contacts, " +
"dss_transfer_justification = @TransferJustification, " +
"dsi_type = @Type " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@StartDate", obj.StartDate);
cmd.Parameters.AddWithValue("@EndDate", obj.EndDate);
cmd.Parameters.AddWithValue("@Destination", obj.Destination == null ? "" : obj.Destination);
cmd.Parameters.AddWithValue("@Contacts", obj.Contacts == null ? "" : obj.Contacts);
cmd.Parameters.AddWithValue("@TransferJustification", obj.TransferJustification == null ? "" : obj.TransferJustification);
cmd.Parameters.AddWithValue("@Type", obj.Type);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_transfer(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_start_date,dsdt_end_date,dss_destination,dss_contacts,dss_transfer_justification,dsi_type) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@StartDate,@EndDate,@Destination,@Contacts,@TransferJustification,@Type) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@StartDate", obj.StartDate);
cmd.Parameters.AddWithValue("@EndDate", obj.EndDate);
cmd.Parameters.AddWithValue("@Destination", obj.Destination == null ? "" : obj.Destination);
cmd.Parameters.AddWithValue("@Contacts", obj.Contacts == null ? "" : obj.Contacts);
cmd.Parameters.AddWithValue("@TransferJustification", obj.TransferJustification == null ? "" : obj.TransferJustification);
cmd.Parameters.AddWithValue("@Type", obj.Type);
return((string)cmd.ExecuteScalar());
}
}
}
}
public void Execute(string sql)
{
using (dynamic connection = connectionFactory.GetConnection())
{
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
command.ExecuteScalar();
}
}
protected static string GetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand("select version from versions where name='application' limit 1;", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
return((string)cmd.ExecuteScalar());
}
}
protected static bool TableExists(string tableName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(TABLE_EXISTS, conn, trans))
{
cmd.Parameters.Add("@tableName", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = tableName;
var exists = (long)cmd.ExecuteScalar();
return(exists > 0);
}
}
public string Save(DdtHolter obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_holter SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsid_parent = @Parent, " +
"dss_parent_type = @ParentType, " +
"dsdt_analysis_date = @AnalysisDate, " +
"dss_holter = @Holter, " +
"dss_monitoring_ad = @MonitoringAd " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Parent", obj.Parent == null ? "0000000000000000" : obj.Parent);
cmd.Parameters.AddWithValue("@ParentType", obj.ParentType == null ? "" : obj.ParentType);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@Holter", obj.Holter == null ? "" : obj.Holter);
cmd.Parameters.AddWithValue("@MonitoringAd", obj.MonitoringAd == null ? "" : obj.MonitoringAd);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_holter(dsid_hospitality_session,dsid_patient,dsid_doctor,dsid_parent,dss_parent_type,dsdt_analysis_date,dss_holter,dss_monitoring_ad) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@Parent,@ParentType,@AnalysisDate,@Holter,@MonitoringAd) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Parent", obj.Parent == null ? "0000000000000000" : obj.Parent);
cmd.Parameters.AddWithValue("@ParentType", obj.ParentType == null ? "" : obj.ParentType);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@Holter", obj.Holter == null ? "" : obj.Holter);
cmd.Parameters.AddWithValue("@MonitoringAd", obj.MonitoringAd == null ? "" : obj.MonitoringAd);
return((string)cmd.ExecuteScalar());
}
}
}
}
public string Save(DdtHormones obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_hormones SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsdt_analysis_date = @AnalysisDate, " +
"dss_t3 = @T3, " +
"dss_t4 = @T4, " +
"dss_ttg = @Ttg " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@T3", obj.T3 == null ? "" : obj.T3);
cmd.Parameters.AddWithValue("@T4", obj.T4 == null ? "" : obj.T4);
cmd.Parameters.AddWithValue("@Ttg", obj.Ttg == null ? "" : obj.Ttg);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_hormones(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_analysis_date,dss_t3,dss_t4,dss_ttg) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@AnalysisDate,@T3,@T4,@Ttg) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@T3", obj.T3 == null ? "" : obj.T3);
cmd.Parameters.AddWithValue("@T4", obj.T4 == null ? "" : obj.T4);
cmd.Parameters.AddWithValue("@Ttg", obj.Ttg == null ? "" : obj.Ttg);
return((string)cmd.ExecuteScalar());
}
}
}
}
public string Save(DdtCoagulogram obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_coagulogram SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsid_doctor = @Doctor, " +
"dsdt_analysis_date = @AnalysisDate, " +
"dss_achtv = @Achtv, " +
"dss_mcho = @Mcho, " +
"dss_ddimer = @Ddimer " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@Achtv", obj.Achtv == null ? "" : obj.Achtv);
cmd.Parameters.AddWithValue("@Mcho", obj.Mcho == null ? "" : obj.Mcho);
cmd.Parameters.AddWithValue("@Ddimer", obj.Ddimer == null ? "" : obj.Ddimer);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_coagulogram(dsid_hospitality_session,dsid_patient,dsid_doctor,dsdt_analysis_date,dss_achtv,dss_mcho,dss_ddimer) " +
"VALUES(@HospitalitySession,@Patient,@Doctor,@AnalysisDate,@Achtv,@Mcho,@Ddimer) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@AnalysisDate", obj.AnalysisDate);
cmd.Parameters.AddWithValue("@Achtv", obj.Achtv == null ? "" : obj.Achtv);
cmd.Parameters.AddWithValue("@Mcho", obj.Mcho == null ? "" : obj.Mcho);
cmd.Parameters.AddWithValue("@Ddimer", obj.Ddimer == null ? "" : obj.Ddimer);
return((string)cmd.ExecuteScalar());
}
}
}
}
public void DeleteHistoryById(string operationId)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("delete from ddt_history WHERE dsid_operation_id='{0}'", operationId);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
command.ExecuteScalar();
}
}
public void RemoveConnectionByParentAndChildIds(string parentId, string childId)
{
String query = String.Format("DELETE FROM ddt_relation WHERE dsid_parent='{0}' AND dsid_child='{1}'", parentId, childId);
using (dynamic connection = connectionFactory.GetConnection())
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
command.ExecuteScalar();
}
}
public string Save(DdtJournalDay obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_journal_day SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsdt_admission_date = @AdmissionDate, " +
"dsid_doctor = @Doctor, " +
"dsi_journal_type = @JournalType, " +
"dss_diagnosis = @Diagnosis, " +
"dss_name = @Name " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@JournalType", obj.JournalType);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_journal_day(dsid_hospitality_session,dsid_patient,dsdt_admission_date,dsid_doctor,dss_name,dsi_journal_type,dss_diagnosis) " +
"VALUES(@HospitalitySession,@Patient,@AdmissionDate,@Doctor,@Name,@JournalType,@Diagnosis) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@JournalType", obj.JournalType);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
return((string)cmd.ExecuteScalar());
}
}
}
}
public void RemoveConnectionById(string id)
{
String query = String.Format("DELETE FROM ddt_relation WHERE r_object_id='{0}' ", id);
using (dynamic connection = connectionFactory.GetConnection())
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
command.ExecuteScalar();
}
}
} // End Sub CreateUser
public static void DropCreateDb(string dbName)
{
string sql = @"
CREATE DATABASE " + dbName + @"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
";
// sql = "CREATE ROLE \"" + System.Environment.UserName + "\" WITH PASSWORD 'TopSecret' LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity';";
// System.Console.WriteLine(sql);
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd.CommandText = "SELECT COUNT(*) FROM pg_database WHERE datname = '" + dbName.Replace("'", "''") + "'";
long countOfExistingDbsWithTHisName = (long)cmd.ExecuteScalar();
if (countOfExistingDbsWithTHisName > 0)
{
cmd.CommandText = @"SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '" + dbName.Replace("'", "''") + @"'
AND pid <> pg_backend_pid();";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP DATABASE " + dbName + ";";
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub DropCreateDb
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
{
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
}
return((string)columnname);
}
}
public void update(string query)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
command.ExecuteScalar();
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public string insert(string query)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
return((string)command.ExecuteScalar());
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public string Save(DdtConsiliumGroupMember obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_consilium_group_member SET " +
"dsid_group = @Group, " +
"dss_name = @Name, " +
"dsid_doctor = @Doctor, " +
"dsid_group = @Group " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_consilium_group_member(dsid_group,dss_name,dsid_doctor,dsid_group) " +
"VALUES(@Group,@Name,@Doctor,@Group) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Group", obj.Group);
return((string)cmd.ExecuteScalar());
}
}
}
}
/// <summary>
/// Finish the given batch.
/// </summary>
/// <param name="batchId">The batch to finish.</param>
/// <returns></returns>
private int FinishBatch(long batchId)
{
_logger.LogDebug($"Finishing batch ID {batchId}");
int batchID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@batchId", batchId);
cmd.CommandText = "select * from pgq.finish_batch(@batchId);";
batchID = (int)cmd.ExecuteScalar();
}
}
return(batchID);
}
public string insertObject <T>(T obj, string tableName)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
string query = convertObjectFieldsInQuery(obj, tableName);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
return((string)command.ExecuteScalar());
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public string Save(DdtRelation relation)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(relation.ObjectId) != null)
{
string sql = "UPDATE ddt_relation SET " +
"dsid_parent = @Parent, " +
"dsid_child = @Child, " +
"dss_child_type = @ChildType " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Parent", relation.Parent);
cmd.Parameters.AddWithValue("@Child", relation.Child);
cmd.Parameters.AddWithValue("@ChildType", relation.ChildType);
cmd.Parameters.AddWithValue("@ObjectId", relation.ObjectId);
cmd.ExecuteNonQuery();
}
return(relation.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_relation(dsid_parent,dsid_child,dss_child_type) " +
"VALUES(@Parent,@Child,@ChildType) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Parent", relation.Parent);
cmd.Parameters.AddWithValue("@Child", relation.Child);
cmd.Parameters.AddWithValue("@ChildType", relation.ChildType);
return((string)cmd.ExecuteScalar());
}
}
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += " WHERE " + this.DefinitionQuery;
}
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return(count);
}
public string Save(DdtIssuedMedicine obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_issued_medicine SET " +
"dsid_med_list = @MedList, " +
"dsid_cure = @Cure " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@MedList", obj.MedList);
cmd.Parameters.AddWithValue("@Cure", obj.Cure);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_issued_medicine(dsid_med_list,dsid_cure) " +
"VALUES(@MedList,@Cure) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@MedList", obj.MedList);
cmd.Parameters.AddWithValue("@Cure", obj.Cure);
obj.ObjectId = (string)cmd.ExecuteScalar();
return(obj.ObjectId);
}
}
}
}
public void updateObject <T>(T obj, string tableName, string conditionAttrName, string conditionAttrValue)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
StringBuilder builder = new StringBuilder();
builder.Append(@"UPDATE ").Append(tableName).Append(" SET ");
FieldInfo[] fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
for (int i = 0; i < fields.Length; i++)
{
FieldInfo fieldInfo = fields[i];
TableAttribute attrInfo = Attribute.GetCustomAttribute(fieldInfo, typeof(TableAttribute)) as TableAttribute;
if (attrInfo != null && attrInfo.CanSetAttr)
{
object value = fieldInfo.GetValue(obj);
builder.Append(attrInfo.AttrName).Append("=").Append(getWrappedValue(value, fieldInfo.FieldType));
if (i < fields.Length - 1)
{
builder.Append(",");
}
}
}
builder.Append(" WHERE ").Append(conditionAttrName).Append("='").Append(conditionAttrValue).Append("'");
Console.WriteLine(builder.ToString());
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(builder.ToString(), connection);
command.ExecuteScalar();
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
protected static string GetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand("select version from versions where name='application' limit 1;", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
return (string)cmd.ExecuteScalar();
}
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public SharpMap.Geometries.BoundingBox GetExtents()
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT EXTENT(" + this.GeometryColumn + ") FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object result = command.ExecuteScalar();
conn.Close();
if (result == System.DBNull.Value)
return null;
string strBox = (string)result;
if (strBox.StartsWith("BOX("))
{
string[] vals = strBox.Substring(4, strBox.IndexOf(")")-4).Split(new char[2] { ',', ' ' });
return SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(
double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS));
}
else
return null;
}
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
public void writeLog(string _thisFunc, string _thisException, string _thisLog)
{
//if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\")))
//{
// System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\");
// System.IO.File.Create(Application.StartupPath + "\\Errors\\errlog.txt");
//}
if (!(System.IO.Directory.Exists(di.FullName + "\\Medex\\Errors\\")))
{
System.IO.Directory.CreateDirectory(di.FullName + "\\Medex\\Errors\\");
if (!(System.IO.File.Exists(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt")))
{
System.IO.File.Create(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt");
}
}
if (_thisException == "There is already an open DataReader associated with this Command which must be closed first.")
{
DBExchange.Inst.connectDb.Close();
DBExchange.Inst.connectDb.Dispose();
DBExchange.Inst.connectDb.Open();
}
else
{
try
{
string s3 = "";
s3+= "Program version: " + DBExchange.Inst.versionNumber.ToString();
s3+="\nFunction: " + _thisFunc;
s3+="\nMessage: " + _thisException;
s3+="\nStackTrace: " + _thisLog;
s3+= "\n=========================================================================================== \n\n";
Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("insert into error_log (descr, user_id) values ('"+s3+"','"+DBExchange.Inst.dbUsrId+"') returning err_id ", DBExchange.Inst.connectDb);
int error_id = (int)nci.ExecuteScalar();
System.Windows.Forms.MessageBox.Show("ВНИМАНИЕ ВОЗНИКЛА НЕПРЕДВИДЕННАЯ ОШИБКА \nОшибка зарегистрирована в базе данных \nВы можете сообщить о своих действиях на сайте http://medx.spb.ru/issues \nили описать свои деействия в следующей форме" );
Templates.FormRegisterError fre = new Templates.FormRegisterError(true,error_id);
fre.ShowDialog();
}
catch
{
FileStream fs = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter s = new StreamWriter(fs);
s.Close();
fs.Close();
FileStream fs1 = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.Append, FileAccess.Write);
StreamWriter s1 = new StreamWriter(fs1);
s1.Write("Date/Time: " + DateTime.Now.ToString());
s1.Write("\nProgram version: " + DBExchange.Inst.versionNumber.ToString());
s1.Write("\nFunction: " + _thisFunc);
s1.Write("\nMessage: " + _thisException);
s1.Write("\nStackTrace: " + _thisLog);
s1.Write("\n=========================================================================================== \n\n");
s1.Close();
fs1.Close();
Templates.FormRegisterError fre = new Templates.FormRegisterError(false,0);
System.Windows.Forms.MessageBox.Show(_thisFunc + " \n" + _thisException + " \n" + _thisLog);
fre.ShowDialog();
}
}
}
