768 lines
26 KiB
C#
768 lines
26 KiB
C#
using MySql.Data.MySqlClient;
|
|
using Simulator.Models;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data.Entity;
|
|
using System.IO;
|
|
|
|
namespace Simulator
|
|
{
|
|
public class SimulatorDbContext : DbContext
|
|
{
|
|
private readonly string _connectionString = null;
|
|
|
|
public SimulatorDbContext(string connectionString)
|
|
{
|
|
this._connectionString = connectionString;
|
|
|
|
if(!this.DatabaseExists())
|
|
{
|
|
this.DatabaseCreate();
|
|
}
|
|
}
|
|
|
|
public bool CreatePatient(Patient patient)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (patient != null && !string.IsNullOrEmpty(patient.Name) && !string.IsNullOrEmpty(patient.Gebdatum) && !string.IsNullOrEmpty(patient.PLZ))
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"insert into patient (name, plz, gebdatum) values(@name, @plz, @gebdatum)";
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("name", patient.Name));
|
|
cmd.Parameters.Add(new MySqlParameter("plz", patient.PLZ));
|
|
cmd.Parameters.Add(new MySqlParameter("gebdatum", patient.Gebdatum));
|
|
cmd.ExecuteNonQuery();
|
|
result = true;
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool CreateBefund(Befund befund, Patient patient)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (befund != null && !string.IsNullOrEmpty(befund.Auftragsnummer) && !string.IsNullOrEmpty(befund.Inhalt) && befund.PatientId > 0)
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"insert into befund (auftragsnummer, befund, patient_id, udid, status) values(@auftragsnummer, @befund, @patient_id, @udid, 'COMPLETED')";
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("auftragsnummer", befund.Auftragsnummer));
|
|
cmd.Parameters.Add(new MySqlParameter("befund", befund.Inhalt));
|
|
cmd.Parameters.Add(new MySqlParameter("patient_id", befund.PatientId));
|
|
|
|
if(!string.IsNullOrEmpty(patient.UDID))
|
|
{
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("udid", patient.UDID));
|
|
}
|
|
else
|
|
{
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("udid", DBNull.Value));
|
|
}
|
|
|
|
cmd.ExecuteNonQuery();
|
|
result = true;
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool UpdateBefund(Befund befund)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (befund != null && !string.IsNullOrEmpty(befund.Auftragsnummer) && !string.IsNullOrEmpty(befund.Inhalt) && befund.PatientId > 0)
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"update befund set status = @status, befund = @inhalt, udid = @udid, modified=now() where id = @id";
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("status", befund.Status));
|
|
cmd.Parameters.Add(new MySqlParameter("inhalt", befund.Inhalt));
|
|
|
|
if(!string.IsNullOrEmpty(befund.UDID))
|
|
{
|
|
cmd.Parameters.Add(new MySqlParameter("udid", befund.UDID));
|
|
}
|
|
else
|
|
{
|
|
cmd.Parameters.Add(new MySqlParameter("udid", DBNull.Value));
|
|
}
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("id", befund.BefundId));
|
|
cmd.ExecuteNonQuery();
|
|
result = true;
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
System.IO.File.AppendAllText(@"C:\tmp\simulator.txt", "ERROR1:" + ex.Message + "\r\n");
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool UpdatePatient(Patient patient)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (patient != null && !string.IsNullOrEmpty(patient.UDID) && patient.PatientId > 0)
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"update patient set udid = @udid where id = @id";
|
|
|
|
if (!string.IsNullOrEmpty(patient.UDID))
|
|
{
|
|
cmd.Parameters.Add(new MySqlParameter("udid", patient.UDID));
|
|
}
|
|
else
|
|
{
|
|
cmd.Parameters.Add(new MySqlParameter("udid", DBNull.Value));
|
|
}
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("id", patient.PatientId));
|
|
cmd.ExecuteNonQuery();
|
|
result = true;
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
public List<Patient> GetPatienten()
|
|
{
|
|
List<Patient> result = null;
|
|
|
|
try
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"select * from patient order by name";
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader != null && reader.HasRows)
|
|
{
|
|
result = new List<Patient>();
|
|
|
|
while (reader.Read())
|
|
{
|
|
Patient patient = this.ReadPatient(reader);
|
|
|
|
if (patient != null)
|
|
{
|
|
result.Add(patient);
|
|
}
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public Patient GetPatient(int id)
|
|
{
|
|
Patient result = null;
|
|
|
|
try
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"select * from patient where id = @id";
|
|
cmd.Parameters.Add(new MySqlParameter("id", id));
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader != null && reader.HasRows && reader.Read())
|
|
{
|
|
result = this.ReadPatient(reader);
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public List<Befund> GetBefunde()
|
|
{
|
|
List<Befund> result = null;
|
|
|
|
try
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"select
|
|
b.id,
|
|
b.auftragsnummer,
|
|
b.befund,
|
|
p.id patient_id,
|
|
p.name patient_name,
|
|
p.udid patient_udid,
|
|
b.udid,
|
|
b.created,
|
|
b.status,
|
|
b.modified
|
|
from
|
|
befund b
|
|
inner join
|
|
patient p on p.id = b.patient_id
|
|
order by
|
|
b.created desc";
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader != null && reader.HasRows)
|
|
{
|
|
result = new List<Befund>();
|
|
|
|
while (reader.Read())
|
|
{
|
|
Befund befund = this.ReadBefund(reader);
|
|
|
|
if (befund != null)
|
|
{
|
|
result.Add(befund);
|
|
}
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public Befund GetBefund(int id)
|
|
{
|
|
Befund result = null;
|
|
|
|
try
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"select
|
|
b.id,
|
|
b.auftragsnummer,
|
|
b.befund,
|
|
p.id patient_id,
|
|
p.name patient_name,
|
|
p.udid patient_udid,
|
|
b.udid,
|
|
b.created,
|
|
b.status,
|
|
b.modified
|
|
from
|
|
befund b
|
|
inner join
|
|
patient p on p.id = b.patient_id
|
|
where
|
|
b.id = @id";
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("id", id));
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader != null && reader.HasRows && reader.Read())
|
|
{
|
|
Befund befund = this.ReadBefund(reader);
|
|
|
|
if (befund != null)
|
|
{
|
|
result = befund;
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public Befund GetBefundByAuftragsnummer(string auftragsnummer, string plz, string gebdatum)
|
|
{
|
|
Befund result = null;
|
|
|
|
try
|
|
{
|
|
if(gebdatum.Length > 10)
|
|
{
|
|
gebdatum = gebdatum.Substring(0, 10);
|
|
}
|
|
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = @"select
|
|
b.id,
|
|
b.auftragsnummer,
|
|
b.befund,
|
|
p.id patient_id,
|
|
p.name patient_name,
|
|
p.udid patient_udid,
|
|
b.udid,
|
|
b.created,
|
|
b.status,
|
|
b.modified
|
|
from
|
|
befund b
|
|
inner join
|
|
patient p on p.id = b.patient_id
|
|
where
|
|
b.auftragsnummer = @auftragsnummer and
|
|
p.plz = @plz and
|
|
date_format(p.gebdatum, '%Y-%m-%d') = @gebdatum";
|
|
|
|
cmd.Parameters.Add(new MySqlParameter("auftragsnummer", auftragsnummer));
|
|
cmd.Parameters.Add(new MySqlParameter("plz", plz));
|
|
cmd.Parameters.Add(new MySqlParameter("gebdatum", gebdatum));
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader != null && reader.HasRows && reader.Read())
|
|
{
|
|
Befund befund = this.ReadBefund(reader);
|
|
|
|
if (befund != null)
|
|
{
|
|
result = befund;
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
private Patient ReadPatient(MySqlDataReader reader)
|
|
{
|
|
Patient result = null;
|
|
|
|
try
|
|
{
|
|
if (reader["id"] != null && reader["id"] != DBNull.Value)
|
|
{
|
|
result = new Patient();
|
|
|
|
result.PatientId = (int)reader["id"];
|
|
result.Name = (string)reader["name"];
|
|
result.PLZ = (string)reader["plz"];
|
|
result.Gebdatum = ((DateTime)reader["gebdatum"]).ToString("yyyy-MM-dd");
|
|
result.UDID = ((reader["udid"] != null && reader["udid"] != DBNull.Value) ? (string)reader["udid"] : null);
|
|
result.Created = ((DateTime)reader["created"]).ToString("dd.MM.yyyy HH:mm:ss");
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
private Befund ReadBefund(MySqlDataReader reader)
|
|
{
|
|
Befund result = null;
|
|
|
|
try
|
|
{
|
|
if (reader["id"] != null && reader["id"] != DBNull.Value)
|
|
{
|
|
result = new Befund();
|
|
|
|
result.BefundId = (int)reader["id"];
|
|
result.Auftragsnummer = (string)reader["auftragsnummer"];
|
|
result.Inhalt = (string)reader["befund"];
|
|
result.Patient = ((reader["patient_name"] != null && reader["patient_name"] != DBNull.Value) ? (string)reader["patient_name"] : null);
|
|
result.PatientId = ((reader["patient_id"] != null && reader["patient_id"] != DBNull.Value) ? (int)reader["patient_id"] : 0);
|
|
result.UDID = ((reader["udid"] != null && reader["udid"] != DBNull.Value) ? (string)reader["udid"] : null);
|
|
result.Created = ((DateTime)reader["created"]).ToString("dd.MM.yyyy HH:mm:ss");
|
|
result.Status = ((reader["status"] != null && reader["status"] != DBNull.Value) ? (string)reader["status"] : null);
|
|
result.StatusText = this.GetBefundStatusText(result.Status);
|
|
result.Modified = ((reader["modified"] != null && reader["modified"] != DBNull.Value) ? (string)reader["modified"] : null);
|
|
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
private string GetBefundStatusText(string status)
|
|
{
|
|
switch(status)
|
|
{
|
|
case "CREATED": return "Erstellt";
|
|
case "COMPLETED": return "Fertig";
|
|
case "PICKEDUP": return "Abgeholt";
|
|
case "SUBSCRIBE": return "Abonniert";
|
|
case "UNSUBSCRIBE": return "Gelöscht";
|
|
case "ERROR": return "Fehler";
|
|
default: return "";
|
|
}
|
|
}
|
|
|
|
public bool PatientExists(string name)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
cmd.CommandText = @"select count(*) cnt from patient where name = @name";
|
|
cmd.Parameters.Add(new MySqlParameter("name", name));
|
|
|
|
string res = cmd.ExecuteScalar().ToString();
|
|
|
|
if (!string.IsNullOrEmpty(res))
|
|
{
|
|
int r = 0;
|
|
|
|
if (Int32.TryParse(res, out r))
|
|
{
|
|
result = (r > 0);
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool BefundExists(string auftragsnummer)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (!string.IsNullOrEmpty(auftragsnummer))
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
cmd.CommandText = @"select count(*) cnt from befund where auftragsnummer = @auftragsnummer";
|
|
cmd.Parameters.Add(new MySqlParameter("auftragsnummer", auftragsnummer));
|
|
|
|
string res = cmd.ExecuteScalar().ToString();
|
|
|
|
if (!string.IsNullOrEmpty(res))
|
|
{
|
|
int r = 0;
|
|
|
|
if (Int32.TryParse(res, out r))
|
|
{
|
|
result = (r > 0);
|
|
}
|
|
}
|
|
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool PatientDelete(int id)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (id > 0)
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
cmd.CommandText = @"delete from patient where id = @id";
|
|
cmd.Parameters.Add(new MySqlParameter("id", id));
|
|
cmd.ExecuteNonQuery();
|
|
client.Close();
|
|
|
|
result = true;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool BefundDelete(int id)
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
if (id > 0)
|
|
{
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
cmd.CommandText = @"delete from befund where id = @id";
|
|
cmd.Parameters.Add(new MySqlParameter("id", id));
|
|
cmd.ExecuteNonQuery();
|
|
client.Close();
|
|
|
|
result = true;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool DatabaseCreate()
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
MySqlConnection dbConnection = this.CreateConnection();
|
|
|
|
using (MySqlConnection client = this.CreateConnectionWithoutSelectedDatabase())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
cmd.CommandText = "create database if not exists " + dbConnection.Database + " character set utf8mb4 collate utf8mb4_general_ci";
|
|
cmd.ExecuteNonQuery();
|
|
client.Close();
|
|
|
|
}
|
|
|
|
using (MySqlConnection client = this.CreateConnection())
|
|
{
|
|
client.Open();
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
|
|
cmd.CommandText = @"create table if not exists patient (
|
|
id int primary key auto_increment,
|
|
name varchar(255) not null,
|
|
plz varchar(255) not null,
|
|
gebdatum datetime not null,
|
|
udid varchar(255) null,
|
|
created timestamp not null default current_timestamp()
|
|
)";
|
|
|
|
cmd.ExecuteNonQuery();
|
|
|
|
cmd.CommandText = @"create table if not exists befund (
|
|
id int primary key auto_increment,
|
|
auftragsnummer varchar(255) not null,
|
|
befund text not null,
|
|
patient_id int null,
|
|
udid varchar(255) null,
|
|
status varchar(255) null,
|
|
modified datetime null,
|
|
created timestamp not null default current_timestamp(),
|
|
constraint fk_patient_id foreign key(patient_id) references patient(id) on update cascade on delete cascade,
|
|
constraint auftragsnummer unique(auftragsnummer)
|
|
)";
|
|
|
|
cmd.ExecuteNonQuery();
|
|
|
|
client.Close();
|
|
result = true;
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = false;
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public bool DatabaseExists()
|
|
{
|
|
bool result = false;
|
|
|
|
try
|
|
{
|
|
MySqlConnection dbConnection = this.CreateConnection();
|
|
|
|
using (MySqlConnection client = this.CreateConnectionWithoutSelectedDatabase())
|
|
{
|
|
client.Open();
|
|
|
|
MySqlCommand cmd = client.CreateCommand();
|
|
|
|
cmd.CommandText = "show databases like '" + dbConnection.Database + "'";
|
|
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
if (reader.HasRows)
|
|
{
|
|
result = true;
|
|
reader.Close();
|
|
cmd.Clone();
|
|
client.Close();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = false;
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public MySqlConnection CreateConnection()
|
|
{
|
|
return new MySqlConnection(this._connectionString);
|
|
}
|
|
|
|
public MySqlConnection CreateConnectionWithoutSelectedDatabase()
|
|
{
|
|
string connectionString = null;
|
|
|
|
if (!string.IsNullOrEmpty(this._connectionString))
|
|
{
|
|
string[] parts = this._connectionString.Split(';');
|
|
|
|
if (parts.Length > 0)
|
|
{
|
|
connectionString = "";
|
|
|
|
foreach (string part in parts)
|
|
{
|
|
string[] values = part.Split('=');
|
|
|
|
if (values.Length > 0)
|
|
{
|
|
if (values[0].ToLower().Replace(" ", "") != "database")
|
|
{
|
|
connectionString += part + ";";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return new MySqlConnection(connectionString);
|
|
}
|
|
}
|
|
} |