public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlCommand v_cmd = null;
Npgsql.NpgsqlDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo PostgreSQL usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Npgsql.NpgsqlCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
private void RefreshButtonClick(object sender, RoutedEventArgs e)
{
var pgQuery = new Npgsql.NpgsqlCommand("SELECT * FROM info_log");
pgQuery.Connection = _pgConnection;
var reader = pgQuery.ExecuteReader();
ServerLogGrid.ItemsSource = reader;
ServerLogGrid.Items.Refresh();
}
public IList <DdtEkg> GetByParentId(string parentId)
{
IList <DdtEkg> list = new List <DdtEkg>();
if (parentId == null)
{
return(list);
}
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format(CultureInfo.CurrentCulture, "SELECT dsid_hospitality_session, ek.r_object_id, dsdt_analysis_date, " +
"r_modify_date, ek.dss_parent_type, r_creation_date, rel.dsid_parent, dsb_admission_analysis, dss_ekg, " +
"dsid_doctor, dsid_patient FROM ddt_ekg ek, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=ek.r_object_id", parentId);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtEkg obj = new DdtEkg();
obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.AnalysisDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.ParentType = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.CreationDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
obj.Parent = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.AdmissionAnalysis = reader.GetBoolean(7);
obj.Ekg = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.Doctor = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.Patient = reader.IsDBNull(10) ? null : reader.GetString(10);
list.Add(obj);
}
}
}
return(list);
}
public DdtIssuedMedicineList GetLastMedList(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, dss_has_kag, dsid_parent_id, dsid_pharmacologist, dss_diagnosis, r_creation_date, " +
"dsid_director, dsid_doctor, dsid_patient, dsid_hospitality_session, dsid_nurse, r_modify_date, dss_parent_type, dss_template_name, " +
"dsdt_issuing_date, dsb_skip_print FROM ddt_issued_medicine_list WHERE dsid_hospitality_session='{0}' ORDER BY r_modify_date DESC", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtIssuedMedicineList obj = new DdtIssuedMedicineList();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.HasKag = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.ParentId = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.Pharmacologist = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.Diagnosis = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.CreationDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
obj.Director = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Doctor = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.Patient = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.HospitalitySession = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.Nurse = reader.IsDBNull(10) ? null : reader.GetString(10);
obj.ModifyDate = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
obj.ParentType = reader.IsDBNull(12) ? null : reader.GetString(12);
obj.TemplateName = reader.IsDBNull(13) ? null : reader.GetString(13);
obj.IssuingDate = reader.IsDBNull(14) ? DateTime.MinValue : reader.GetDateTime(14);
obj.SkipPrint = reader.GetBoolean(15);
return(obj);
}
}
}
return(null);
}
public Recipe GetRecipe(int id)
{
_conn.Open();
var data = new object[] { };
// Retrieve all rows
using (var cmd = new Npgsql.NpgsqlCommand("SELECT id, user_id, name, ingredients, instructions FROM recipes WHERE id = @id LIMIT 1", _conn))
{
cmd.Parameters.AddWithValue("id", id);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
reader.GetValues(data);
}
Console.WriteLine(reader.GetString(0));
}
_conn.Close();
return(new Recipe(int.Parse(data[0].ToString()), data[1].ToString(), data[2].ToString(), data[3].ToString()));
}
public IList <DdtOncologicMarkers> GetAll()
{
IList <DdtOncologicMarkers> list = new List <DdtOncologicMarkers>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, dsdt_analysis_date, r_creation_date, dss_cea, dsid_parent, dss_psa_common, dss_psa_free, dsid_doctor, dsid_patient, dsid_hospitality_session, dss_hgch, r_modify_date, dss_parent_type, dss_ca_125, dss_ca_199, dss_ca_153, dss_afr FROM ddt_oncologic_markers";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtOncologicMarkers obj = new DdtOncologicMarkers();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Cea = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.Parent = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.PsaCommon = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.PsaFree = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Doctor = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.Patient = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.HospitalitySession = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.Hgch = reader.IsDBNull(10) ? null : reader.GetString(10);
obj.ModifyDate = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
obj.ParentType = reader.IsDBNull(12) ? null : reader.GetString(12);
obj.Ca125 = reader.IsDBNull(13) ? null : reader.GetString(13);
obj.Ca199 = reader.IsDBNull(14) ? null : reader.GetString(14);
obj.Ca153 = reader.IsDBNull(15) ? null : reader.GetString(15);
obj.Afr = reader.IsDBNull(16) ? null : reader.GetString(16);
list.Add(obj);
}
}
}
return(list);
}
/// <summary>
/// Get's all users from database.
/// </summary>
/// <returns></returns>
public IEnumerable <User> GetAllUsers()
{
//This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User
IList <User> allUsers = new List <User>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SelectAllQuery, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
User newUser = populateUserFromDB(dr);
allUsers.Add(newUser);
}
}
}
}
return(allUsers);
}
public IEnumerable <SubPracticeHeadCountDto> GetSubPracticeWiseHeadCountFromPostgres()
{
List <SubPracticeHeadCountDto> records = new List <SubPracticeHeadCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_SUB_PRACTICE_WISE_HEAD_COUNT;
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
int?nullInt = null;
if (res.HasRows)
{
while (res.Read())
{
records.Add(new SubPracticeHeadCountDto
{
HeadCount = res.IsDBNull(4) == false ? (int)res.GetInt64(4) : nullInt,
Practice = res.IsDBNull(1) == false ? res.GetString(1) : "",
PracticeID = res.IsDBNull(0) == false ? (int)res.GetInt64(0) : nullInt,
SubPractice = res.IsDBNull(3) == false ? res.GetString(3) : "",
SubPracticeID = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : nullInt
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
public void Select(string sql, string key, string value, OnKeyValue handler)
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (dynamic connection = connectionFactory.GetConnection())
{
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
System.Collections.ObjectModel.ReadOnlyCollection <DbColumn> columns = reader.GetColumnSchema();
IEnumerator <DbColumn> colsNumerator = columns.GetEnumerator();
while (reader.Read())
{
string attrKeyValue = null;
string attrValValue = null;
while (colsNumerator.MoveNext())
{
if (key.Equals(colsNumerator.Current.ColumnName, StringComparison.Ordinal))
{
int indx = columns.IndexOf(colsNumerator.Current);
attrKeyValue = getWrappedValue(reader.GetValue(indx), reader.GetFieldType(indx));
}
else if (value.Equals(colsNumerator.Current.ColumnName, StringComparison.Ordinal))
{
int indx = columns.IndexOf(colsNumerator.Current);
attrValValue = getWrappedValue(reader.GetValue(indx), reader.GetFieldType(indx));
}
}
colsNumerator.Reset();
if (attrKeyValue != null)
{
handler(attrKeyValue, attrValValue);
}
}
}
}
}
public IList <DdtCoagulogram> getByParentId(string parentId)
{
IList <DdtCoagulogram> list = new List <DdtCoagulogram>();
if (parentId == null)
{
return(list);
}
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT dss_ddimer, dsid_hospitality_session, cc.r_object_id, dsdt_analysis_date, r_modify_date, dss_mcho, r_creation_date, dss_achtv, " +
"dsid_doctor, dsid_patient FROM ddt_coagulogram cc, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=cc.r_object_id", parentId);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtCoagulogram obj = new DdtCoagulogram();
obj.Ddimer = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.HospitalitySession = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.ObjectId = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.AnalysisDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.ModifyDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
obj.Mcho = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
obj.Achtv = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.Doctor = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.Patient = reader.IsDBNull(9) ? null : reader.GetString(9);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtHospital> GetAll()
{
IList <DdtHospital> list = new List <DdtHospital>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, dss_diagnosis, dsid_duty_doctor, r_creation_date, dsi_release_type, dsdt_admission_date, dsid_patient, dsid_curing_doctor, dsb_active, r_modify_date, dsb_reject_cure, dss_room_cell, dsb_death, dsid_dir_cardio_reanim_doctor, dsid_substitution_doctor, dsid_anesthetist_doctor FROM ddt_hospital";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtHospital obj = new DdtHospital();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.Diagnosis = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.DutyDoctor = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.ReleaseType = reader.IsDBNull(4) ? -1 : reader.GetInt16(4);
obj.AdmissionDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
obj.Patient = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.CuringDoctor = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.Active = reader.GetBoolean(8);
obj.ModifyDate = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
obj.RejectCure = reader.GetBoolean(10);
obj.RoomCell = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.Death = reader.GetBoolean(12);
obj.DirCardioReanimDoctor = reader.IsDBNull(13) ? null : reader.GetString(13);
obj.SubstitutionDoctor = reader.IsDBNull(14) ? null : reader.GetString(14);
obj.AnesthetistDoctor = reader.IsDBNull(15) ? null : reader.GetString(15);
list.Add(obj);
}
}
}
return(list);
}
/// <summary>
/// get XmlData list
/// </summary>
/// <returns></returns>
public IList <XElement> GetXElements()
{
CreateTable();
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"select ""XmlData"" from public.""DataProtectionKeys"" ";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
var elements = new List <XElement>();
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
elements.Add(XElement.Parse(reader.GetString(0)));
}
reader.Close();
}
con.Close();
return(elements);
}
}
}
public IList <AppointmentSheet> AssignAppointments(int appointmentid)
{
assignableAppointments.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetAppointmentQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter(":appointmentsheetid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = appointmentid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
AppointmentSheet newAppointment = populateAppointmentFromDB(dr);
assignableAppointments.Add(newAppointment);
}
}
}
}
return(assignableAppointments);
}
public IList <Lead> Assignleads(int leadid)
{
assignableLeads.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetLeadsQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter(":leadid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = leadid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Lead newLead = populateLeadFromDB(dr);
assignableLeads.Add(newLead);
}
}
}
}
return(assignableLeads);
}
public DdvAllDiagnosis GetById(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT dsid_hospitality_session, r_object_id, object_type, dss_diagnosis FROM ddv_all_diagnosis WHERE r_object_id = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdvAllDiagnosis obj = new DdvAllDiagnosis();
obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.Type = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.Diagnosis = reader.IsDBNull(3) ? null : reader.GetString(3);
return(obj);
}
}
}
return(null);
}
public T queryObject <T>(string query)
{
Npgsql.NpgsqlConnection connection = null;
try
{
connection = getConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(fillObject <T>(reader));
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(default(T));
}
public IEnumerable <Ticket> GetTicketsByAccountID(int accountid)
{
realTickets.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketAccountSelectQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = accountid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Ticket newTicket = populateTicketsFromDB(dr);
realTickets.Add(newTicket);
}
}
}
}
return(realTickets.AsQueryable());
}
// private static int counter = 1;
public PhoneUser GetPhoneUser(int userid)
{
PhoneUser newPhoneuser = new PhoneUser();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(UserSelectQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = userid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
newPhoneuser = populatePhoneUserFromDB(dr);
}
}
}
}
return(newPhoneuser);
}
public IEnumerable <TicketHistory> GetTicketHistoryByTicketID(string ticketid)
{
realTicketHistory.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketHistoryTicketSelectQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("ticketid", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = ticketid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
TicketHistory newTicketHistory = populateTicketHistoryFromDB(dr);
realTicketHistory.Add(newTicketHistory);
}
}
}
}
return(realTicketHistory.AsQueryable());
}
public IList <DdtHormones> GetByParentId(string parentId)
{
IList <DdtHormones> list = new List <DdtHormones>();
if (parentId == null)
{
return(list);
}
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT dsid_hospitality_session, ho.r_object_id, dss_ttg, dsdt_analysis_date, dss_t3, r_modify_date, dss_t4, r_creation_date, " +
"dsid_doctor, dsid_patient FROM ddt_hormones ho, ddt_relation rel WHERE rel.dsid_parent = '{0}' AND rel.dsid_child=ho.r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtHormones obj = new DdtHormones();
obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.Ttg = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.AnalysisDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.T3 = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.ModifyDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
obj.T4 = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.CreationDate = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
obj.Doctor = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.Patient = reader.IsDBNull(9) ? null : reader.GetString(9);
list.Add(obj);
}
}
}
return(list);
}
public string recoverPassword(string userRequesting)
{
using (var connection = GetConnection())
{
connection.Open();
var sql = "select * from users where username =@user or email=@mail";
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@user", userRequesting);
cmd.Parameters.AddWithValue("@mail", userRequesting);
cmd.CommandType = CommandType.Text;
Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() == true)
{
string username = reader.GetString(1);
string password = reader.GetString(2);
string email = reader.GetString(3);
var mailService = new EmailService.SystemSupportMail();
mailService.sendMail(
subject: "System: Password Recovery request",
body: "Hi " + username + "\n you requested to recover your password.\n" +
"your current password is " + password,
recipientMail: new List <string> {
email
});
return("Hi " + username + "\n you requested to recover your password.\n" +
"your current password is " + password);
}
else
{
return("Sorry you dont have any accout with us");
}
}
}
}
public IList <DdtUzi> GetAll()
{
IList <DdtUzi> list = new List <DdtUzi>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, dsdt_analysis_date, dss_eho_kg, r_creation_date, dss_pleurs_uzi, dsid_parent, dsid_doctor, dsid_patient, " +
"dsid_hospitality_session, dss_uzd_bca, r_modify_date, dss_parent_type, dss_cds, dss_uzi_obp FROM ddt_uzi";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtUzi obj = new DdtUzi();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.EhoKg = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.PleursUzi = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.Parent = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.Doctor = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Patient = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.HospitalitySession = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.UzdBca = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.ModifyDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
obj.ParentType = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.Cds = reader.IsDBNull(12) ? null : reader.GetString(12);
obj.UziObp = reader.IsDBNull(13) ? null : reader.GetString(13);
list.Add(obj);
}
}
}
return(list);
}
public List <DistanciaViagemDTO> GetDistanciaViagem(int radarInicio, int radarFinal)
{
List <DistanciaViagemDTO> listaRetorno = new List <DistanciaViagemDTO>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText =
"select br0.codigo as RadarInicio, " +
"br1.codigo as RadarFinal, " +
"ST_Distance(ST_Transform(concat('SRID=4326;POINT(', cast(br0.lat as varchar(20)), ' ', cast(br0.lon as varchar(20)), ')')::geometry, 3857), " +
"ST_Transform(concat('SRID=4326;POINT(', cast(br1.lat as varchar(20)), ' ', cast(br1.lon as varchar(20)), ')')::geometry, 3857)) * cosd(42.3521) as distancia " +
"from base_radares_lat_lon br0 inner join base_radares_lat_lon br1 " +
"on br1.codigo = " + radarFinal + " where br0.codigo = " + radarInicio + "; ";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
DistanciaViagemDTO ett = new DistanciaViagemDTO();
ett.codigoRadarInicio = Convert.ToInt32(dr["RadarInicio"]);
ett.codigoRadarFinal = Convert.ToInt32(dr["RadarFinal"]);
ett.distancia = Convert.ToDecimal(dr["distancia"]);
listaRetorno.Add(ett);
}
}
return(listaRetorno);
}
private IList <DdtRelation> Select(string query)
{
IList <DdtRelation> list = new List <DdtRelation>();
using (dynamic connection = connectionFactory.GetConnection())
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", query);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtRelation obj = new DdtRelation();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.Parent = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.Child = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.ChildType = reader.IsDBNull(3) ? null : reader.GetString(3);
list.Add(obj);
}
}
}
return(list);
}
public DmGroupUsers GetById(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dsid_doctor_id, dss_group_name FROM dm_group_users WHERE r_object_id = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DmGroupUsers obj = new DmGroupUsers();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.DoctorId = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.GroupName = reader.IsDBNull(4) ? null : reader.GetString(4);
return(obj);
}
}
}
return(null);
}
public IQueryable <Domain.Lead> LeadByStatus(string status, int userId)
{
fakeLeads.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(LeadByStatusSelectQuery, conn))
{
command.Parameters.AddWithValue("status", status);
command.Parameters.AddWithValue("userId", userId);
command.Prepare();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Lead newLead = populateLeadFromDB(dr);
fakeLeads.Add(newLead);
}
}
}
}
return(fakeLeads.AsQueryable());
}
public IList <DdtSerology> GetAll()
{
IList <DdtSerology> list = new List <DdtSerology>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, dsdt_analysis_date, dss_blood_type, r_creation_date, dss_kell_ag, dss_phenotype, dss_rw, dsid_doctor, dsid_patient, dss_hbs_ag, dss_anti_hcv, dsid_hospitality_session, r_modify_date, dss_hiv, dss_rhesus_factor FROM ddt_serology";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtSerology obj = new DdtSerology();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.BloodType = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.KellAg = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.Phenotype = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.Rw = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Doctor = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.Patient = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.HbsAg = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.AntiHcv = reader.IsDBNull(10) ? null : reader.GetString(10);
obj.HospitalitySession = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.ModifyDate = reader.IsDBNull(12) ? DateTime.MinValue : reader.GetDateTime(12);
obj.Hiv = reader.IsDBNull(13) ? null : reader.GetString(13);
obj.RhesusFactor = reader.IsDBNull(14) ? null : reader.GetString(14);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtVariousSpecConcluson> GetListByParentId(string parentId)
{
IList <DdtVariousSpecConcluson> list = new List <DdtVariousSpecConcluson>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT dss_additional_info0, r_object_id, dsb_visible, dss_specialist_type, dss_additional_info2, dss_additional_info1, r_creation_date, dsid_parent, dsdt_admission_date, dss_additional_info4, dss_additional_info3, dss_specialist_conclusion, r_modify_date, dss_parent_type, dsb_additional_bool FROM ddt_various_spec_concluson WHERE dsid_parent = '{0}' AND dsb_additional_bool = false order by dsdt_admission_date", parentId);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtVariousSpecConcluson obj = new DdtVariousSpecConcluson();
obj.AdditionalInfo0 = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.Visible = reader.GetBoolean(2);
obj.SpecialistType = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.AdditionalInfo2 = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.AdditionalInfo1 = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
obj.Parent = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.AdmissionDate = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
obj.AdditionalInfo4 = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.AdditionalInfo3 = reader.IsDBNull(10) ? null : reader.GetString(10);
obj.SpecialistConclusion = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.ModifyDate = reader.IsDBNull(12) ? DateTime.MinValue : reader.GetDateTime(12);
obj.ParentType = reader.IsDBNull(13) ? null : reader.GetString(13);
obj.AdditionalBool = reader.GetBoolean(14);
list.Add(obj);
}
}
}
return(list);
}
public IEnumerable <Equipment> GetEquipmentByType(string equipmenttype)
{
IList <Domain.Equipment> equiptype = new List <Domain.Equipment>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EquipmentTypeQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("equiptype", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = equipmenttype;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Equipment newequip = populateEquipmentFromDB(dr);
equiptype.Add(newequip);
}
}
}
}
return(equiptype);
}
void ShowDate()
{
try
{
var sql = @"select(c.model || ' ' || b.brand) as c_col, count(p.id) as avto
from car c, brands b, purchases p
where b.id = c.brand_id and c.id = p.car_id
group by c_col;";
Connection.con = new Npgsql.NpgsqlConnection(Connection.connection());
Connection.con.Open();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, Connection.con);
DataTable dt = new DataTable();
Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
dataGridView1.DataSource = dt;
Main.LocalizeHeaders(dataGridView1);
Connection.con.Close();
}
catch
{
Connection.con.Close();
}
}
public DmGroup GetGroupByName(string groupName)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, dss_description, r_modify_date, r_creation_date, dss_name FROM dm_group WHERE dss_name = '{0}'", groupName);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DmGroup obj = new DmGroup();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.Description = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.ModifyDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.Name = reader.IsDBNull(4) ? null : reader.GetString(4);
return(obj);
}
}
}
return(null);
}
public static DataTable Npgsql_Data(String sql)
{
//參考資料
//https://www.npgsql.org/doc/index.html
String connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
DataTable dt = new DataTable();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstr))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
//執行不Query的方式
//cmd.ExecuteNonQuery();
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
}
return(dt);
}
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans))
{
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
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;
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
public static List<Common.Models.Tasks.Task> ListChildren(
long? parentId,
List<Tuple<string, string>> filter = null,
IDbConnection conn = null,
bool closeConnection = true)
{
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
IEnumerable<DBOs.Tasks.Task> ie = null;
//filter = new List<Tuple<string, string>>();
//filter.Add(new Tuple<string, string>("status", "pending"));
if (filter != null)
{
string filterStr = null;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
filter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Item1))
cats.Add(x.Item1.ToLower());
if (!string.IsNullOrWhiteSpace(x.Item2))
tags.Add(x.Item2.ToLower());
});
filterStr = "SELECT * FROM \"task\" WHERE \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND \"parent_id\"";
if (parentId.HasValue && parentId.Value > 0)
{
filterStr += "=:parentid ";
parms.Add(new Npgsql.NpgsqlParameter("parentid", DbType.Int64) { Value = parentId.Value.ToString() });
}
else
filterStr += " is null ";
filterStr += "AND \"utc_disabled\" is null";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(filterStr, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
else
{
conn = DataHelper.OpenIfNeeded(conn);
if (parentId.HasValue)
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null",
new { ParentId = parentId.Value });
else
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\" is null AND \"utc_disabled\" is null");
foreach (DBOs.Tasks.Task dbo in ie)
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
DataHelper.Close(conn, closeConnection);
return list;
}
protected static bool FunctionExists(string functionName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(FUNCTION_EXISTS, conn, trans))
{
cmd.Parameters.Add("@procname", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = functionName;
using (var r = cmd.ExecuteReader())
{
return r.HasRows;
}
}
}
public override List<List<string>> Select(string sql, List<Database.Bind> bind_var = null)
{
if (!this.IsConnected)
throw new WmibException("The database is not connected");
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, this.connection);
if (bind_var != null)
BindVars(sql, bind_var, command);
SystemHooks.OnSQL(LocalName, sql);
Npgsql.NpgsqlDataReader dr = command.ExecuteReader();
List<List<string>> results = new List<List<string>>();
while (dr.Read())
{
List<string> line = new List<string>();
results.Add(line);
for (int i = 0; i < dr.FieldCount; i++)
{
line.Add(dr[i].ToString());
}
}
return results;
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(List<Common.Models.Settings.TagFilter> tagFilter, DateTime? start = null, DateTime? stop = null)
{
string sql;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
tagFilter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Category))
cats.Add(x.Category.ToLower());
if (!string.IsNullOrWhiteSpace(x.Tag))
tags.Add(x.Tag.ToLower());
});
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND " +
"\"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
return list;
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT " + this.ObjectIdColumn + " ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(
DateTime? start = null,
DateTime? stop = null,
IDbConnection conn = null,
bool closeConnection = true)
{
string sql;
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
DataHelper.Close(conn, closeConnection);
return list;
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<SharpMap.Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
System.Collections.Generic.List<SharpMap.Geometries.Geometry> features = new Collection<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
IGeometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if(geom!=null)
features.Add(geom);
}
}
}
conn.Close();
}
}
return features;
}