public static List <Avtalsmodel> ParseAvtal(NpgsqlDataReader reader)
{
var lst = new List <Avtalsmodel>();
while (reader.Read())
{
string diarienr;
if (reader.GetValue(1) != DBNull.Value)
{
diarienr = reader.GetString(1);
}
else
{
diarienr = "";
}
DateTime?sd;
if (reader.GetValue(2) != DBNull.Value)
{
sd = reader.GetDateTime(2);
}
else
{
sd = null;
}
DateTime?ed;
if (reader.GetValue(3) != DBNull.Value)
{
ed = reader.GetDateTime(3);
}
else
{
ed = null;
}
//string mptyp;
//if (reader.GetString(5) != DBNull.Value)
//{
// mptyp = reader.GetString(5);
//}
//else
//{
// mptyp = "";
//}
long dbid;
if (reader.GetValue(0) != DBNull.Value)
{
dbid = reader.GetInt32(0);
}
else
{
dbid = -1;
}
long?avtalstecknare;
if (reader.GetValue(12) != DBNull.Value)
{
avtalstecknare = reader.GetInt32(12);
}
else
{
avtalstecknare = null;
}
long?avtalskontakt;
if (reader.GetValue(13) != DBNull.Value)
{
avtalskontakt = reader.GetInt32(13);
}
else
{
avtalskontakt = null;
}
long?ansvarig_sbk;
if (reader.GetValue(14) != DBNull.Value)
{
ansvarig_sbk = reader.GetInt32(14);
}
else
{
ansvarig_sbk = null;
}
long?upphandlat_av;
if (reader.GetValue(17) != DBNull.Value)
{
upphandlat_av = reader.GetInt32(17);
}
else
{
upphandlat_av = null;
}
long?datakontakt;
if (reader.GetValue(18) != DBNull.Value)
{
datakontakt = reader.GetInt32(18);
}
else
{
datakontakt = null;
}
long?ansvarig_avd;
if (reader.GetValue(15) != DBNull.Value)
{
ansvarig_avd = reader.GetInt32(15);
}
else
{
ansvarig_avd = null;
}
long?ansvarig_enhet;
if (reader.GetValue(16) != DBNull.Value)
{
ansvarig_enhet = reader.GetInt32(16);
}
else
{
ansvarig_enhet = null;
}
// TODO lägg till id för avtalstecknare, kontakt etc, så att rätt person väljs i rullisterna
lst.Add(new Avtalsmodel
{
id = dbid, // reader.GetInt32(0),
diarienummer = diarienr,
startdate = sd,
enddate = ed,
status = reader.GetString(4),
motpartstyp = reader.GetString(5),
sbkid = reader.GetInt32(6),
scan_url = reader.GetString(7),
orgnummer = reader.GetString(8),
enligtAvtal = reader.GetString(9),
interntAlias = reader.GetString(10),
kommentar = reader.GetString(11),
avtalstecknare = avtalstecknare,
avtalskontakt = avtalskontakt,
ansvarig_sbk = ansvarig_sbk,
upphandlat_av = upphandlat_av,
ansvarig_avdelning = ansvarig_avd, //reader.GetInt32(15),
ansvarig_enhet = ansvarig_enhet, //reader.GetInt32(16),
datakontakt = datakontakt,
konto = reader.GetString(19),
kstl = reader.GetString(20),
vht = reader.GetString(21),
mtp = reader.GetString(22),
aktivitet = reader.GetString(23),
objekt = reader.GetString(24),
avtalstyp = reader.GetString(25),
});
}
return(lst);
}
public Patient modifyPatient(Patient _patient)
{
int resp = 0;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("paciente_modificar(@ID, @FIRSTNAME, @SURNAME, @AGE, @CAREER, @STATE, @MUNICIPALITY, @PARISH)", conn);
NpgsqlParameter id = new NpgsqlParameter();
NpgsqlParameter firstname = new NpgsqlParameter();
NpgsqlParameter surname = new NpgsqlParameter();
NpgsqlParameter age = new NpgsqlParameter();
NpgsqlParameter career = new NpgsqlParameter();
NpgsqlParameter state = new NpgsqlParameter();
NpgsqlParameter municipality = new NpgsqlParameter();
NpgsqlParameter parish = new NpgsqlParameter();
id.ParameterName = "@ID";
firstname.ParameterName = "@FIRSTNAME";
surname.ParameterName = "@SURNAME";
age.ParameterName = "@AGE";
career.ParameterName = "@CAREER";
state.ParameterName = "@STATE";
municipality.ParameterName = "@MUNICIPALITY";
parish.ParameterName = "@PARISH";
id.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
firstname.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
surname.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
age.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
career.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
state.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
municipality.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
parish.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
id.Direction = ParameterDirection.Input;
firstname.Direction = ParameterDirection.Input;
age.Direction = ParameterDirection.Input;
career.Direction = ParameterDirection.Input;
state.Direction = ParameterDirection.Input;
municipality.Direction = ParameterDirection.Input;
parish.Direction = ParameterDirection.Input;
id.Value = _patient._ID;
firstname.Value = _patient._Name;
surname.Value = _patient._Surname;
age.Value = _patient._Age;
career.Value = _patient._Career;
state.Value = _patient._State;
municipality.Value = _patient._Municipality;
parish.Value = _patient._Parish;
command.Parameters.Add(id);
command.Parameters.Add(firstname);
command.Parameters.Add(surname);
command.Parameters.Add(age);
command.Parameters.Add(career);
command.Parameters.Add(state);
command.Parameters.Add(municipality);
command.Parameters.Add(parish);
command.CommandType = CommandType.StoredProcedure;
//command.ExecuteNonQuery();
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
resp = dr.GetInt32(0);
}
if (resp == Registry.RESULTADO_CODIGO_RECURSO_CREADO)
{
_patient._Error = Registry.RESULTADO_CODIGO_RECURSO_CREADO;
}
else
{
_patient._Error = Registry.RESULTADO_CODIGO_FALLIDO;
}
dr.Close();
tran.Commit();
return(_patient);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
}
public void SaveUserDetail()
{
try
{
btnSave.IsEnabled = true;
if (txtname.Text == String.Empty)
{
txtname.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Name ! ");
return;
}
if (txtUserName.Text == String.Empty)
{
txtname.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The User Name ! ");
return;
}
if (txtQID.Text == String.Empty)
{
txtQID.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The txtQID ! ");
return;
}
if (txtPassword.Text == String.Empty)
{
txtPassword.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Password ! ");
return;
}
if (txtAddress.Text == String.Empty)
{
txtAddress.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Address ! ");
return;
}
if (txtEmail.Text == String.Empty)
{
txtEmail.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Email ! ");
return;
}
if (txtZipCode.Text == String.Empty)
{
txtZipCode.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Zip Code ! ");
return;
}
if (txtMobile.Text == String.Empty)
{
txtMobile.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Enter The Mobile No. ! ");
return;
}
if (imagePath == String.Empty)
{
btnImgUpload.BorderBrush = (SolidColorBrush) new BrushConverter().ConvertFromString(CoustomColors.CartRedBrush);
MessageBox.Show("Please Upload The Image ! ");
return;
}
NpgsqlConnection connection = new NpgsqlConnection(connstring);
dynamic WH = Warehouse_DropDown.SelectedItem as dynamic;
int WarehouseId = WH.WarehouseId;
dynamic RD = Role_DropDown.SelectedItem as dynamic;
int Roleid = RD.RoleID;
dynamic CD = Country_DropDown.SelectedItem as dynamic;
int CountryID = CD.CountryID;
dynamic City = City_DropDown.SelectedItem as dynamic;
int CityID = City.CityID;
connection.Open();
NpgsqlCommand cmd_select_sequenc_no_ad_user_pos = new NpgsqlCommand("SELECT ad_sequence_id,name, incrementno, startno,currentnext FROM ad_sequence where name = 'ad_user_pos';", connection);
NpgsqlDataReader _get__Ad_sequenc_no_ad_user_pos = cmd_select_sequenc_no_ad_user_pos.ExecuteReader();
if (_get__Ad_sequenc_no_ad_user_pos.Read())
{
Sequenc_id = _get__Ad_sequenc_no_ad_user_pos.GetInt32(4) + 1;
}
connection.Close();
connection.Open();
NpgsqlCommand INSERT_cmd_User_Detail = new NpgsqlCommand("INSERT INTO ad_user_pos (ad_user_pos_id,ad_client_id,ad_org_id,QID,name,username,password,mobile,email,address,m_warehouse_id,ad_role_id,createdBy,islogged, isactive,ZipCode,City,CountryID,ad_user_id,imgpath) " + " " + "VALUES(" + Sequenc_id + "," + _clientId + " ," + _OrgId + " ," + txtQID.Text + ",'" + txtname.Text + "','" + txtUserName.Text + "','" + txtPassword.Text + "','" + txtMobile.Text + "','" + txtEmail.Text + "','" + txtAddress.Text + "','" + WarehouseId + "','" + Roleid + "'," + _UserID + ",'Y','Y','" + txtZipCode.Text + "','" + CountryID + "','" + CityID + "','" + Sequenc_id + "','" + imagePath + "') ;", connection);
INSERT_cmd_User_Detail.ExecuteNonQuery();
//this.Dispatcher.Invoke(() => { LoginProcessingText.Text = "Process: ad_client_id new data Inserted"; });
ClearData();
connection.Close();
connection.Open();
NpgsqlCommand cmd_update_sequenc_no_ad_user_pos = new NpgsqlCommand("UPDATE ad_sequence SET currentnext =" + Sequenc_id + " WHERE name = 'ad_user_pos';", connection);
cmd_update_sequenc_no_ad_user_pos.ExecuteReader();
connection.Close();
MessageBox.Show("Record Save Successfully");
}
catch (Exception ex)
{
log.Error(" =================== Error In Insert User Detail =========================== ");
log.Error(DateTime.Now.ToString());
log.Error(ex.ToString());
log.Error(" =================== End of Error =========================== ");
if (MessageBox.Show(ex.ToString(),
"Error In Insert User Detail", MessageBoxButton.OK, MessageBoxImage.Error) == MessageBoxResult.OK)
{
return;
}
return;
}
}
/// <summary>
/// Returns all the nodes with the given ids.
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public override IList <Node> GetNodes(IList <long> ids)
{
IList <Node> returnList = new List <Node>();
if (ids.Count > 0)
{
// initialize connection.
NpgsqlConnection con = this.CreateConnection();
// STEP 1: query nodes table.
// id, latitude, longitude, changeset_id, visible, timestamp, tile, version, usr, usr_id
Dictionary <long, Node> nodes = new Dictionary <long, Node>();
for (int idx_1000 = 0; idx_1000 <= ids.Count / 1000; idx_1000++)
{
int startIdx = idx_1000 * 1000;
int stopIdx = System.Math.Min((idx_1000 + 1) * 1000, ids.Count);
string sql = "SELECT id, latitude, longitude, changeset_id, visible, timestamp, tile, version, usr, usr_id FROM node WHERE (id IN ({0})) ";
string ids_string = this.ConstructIdList(ids, startIdx, stopIdx);
if (ids_string.Length > 0)
{
sql = string.Format(sql, ids_string);
NpgsqlCommand com = new NpgsqlCommand(sql);
com.Connection = con;
NpgsqlDataReader reader = com.ExecuteReader();
Node node = null;
List <long> nodeIds = new List <long>();
while (reader.Read())
{
// load/parse data.
long returned_id = reader.GetInt64(0);
int latitude_int = reader.GetInt32(1);
int longitude_int = reader.GetInt32(2);
long? changeset_id = reader.IsDBNull(3) ? null : (long?)reader.GetInt64(3);
bool? visible = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
DateTime?timestamp = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
long tile = reader.GetInt64(6);
ulong? version = reader.IsDBNull(7) ? null : (ulong?)reader.GetInt32(7);
string usr = reader.IsDBNull(8) ? null : reader.GetString(8);
long? usr_id = reader.IsDBNull(9) ? null : (long?)reader.GetInt32(9);
if (!nodes.ContainsKey(returned_id))
{
// create node.
node = new Node();
node.Id = returned_id;
node.Version = version;
node.UserId = usr_id;
node.UserName = usr;
node.TimeStamp = timestamp;
node.ChangeSetId = changeset_id;
node.Latitude = ((double)latitude_int) / 10000000.0;
node.Longitude = ((double)longitude_int) / 10000000.0;
node.Visible = visible;
nodes.Add(node.Id.Value, node);
nodeIds.Add(node.Id.Value);
}
}
reader.Close();
}
}
// STEP2: Load all node tags.
this.LoadNodeTags(nodes);
returnList = nodes.Values.ToList <Node>();
}
return(returnList);
}
NpgsqlDbColumn LoadColumnDefinition(NpgsqlDataReader reader, NpgsqlDatabaseInfo databaseInfo, bool oldQueryMode)
{
// We don't set ColumnName here. It should always contain the column alias rather than
// the table column name (i.e. in case of "SELECT foo AS foo_alias"). It will be set later.
var column = new NpgsqlDbColumn
{
AllowDBNull = !reader.GetBoolean(reader.GetOrdinal("attnotnull")),
BaseCatalogName = _connection.Database !,
BaseSchemaName = reader.GetString(reader.GetOrdinal("nspname")),
BaseServerName = _connection.Host !,
BaseTableName = reader.GetString(reader.GetOrdinal("relname")),
BaseColumnName = reader.GetString(reader.GetOrdinal("attname")),
ColumnAttributeNumber = reader.GetInt16(reader.GetOrdinal("attnum")),
IsKey = reader.GetBoolean(reader.GetOrdinal("isprimarykey")),
IsReadOnly = !reader.GetBoolean(reader.GetOrdinal("is_updatable")),
IsUnique = reader.GetBoolean(reader.GetOrdinal("isunique")),
TableOID = reader.GetFieldValue <uint>(reader.GetOrdinal("attrelid")),
TypeOID = reader.GetFieldValue <uint>(reader.GetOrdinal("typoid"))
};
column.PostgresType = databaseInfo.ByOID[column.TypeOID];
column.DataTypeName = column.PostgresType.DisplayName; // Facets do not get included
var defaultValueOrdinal = reader.GetOrdinal("default");
column.DefaultValue = reader.IsDBNull(defaultValueOrdinal) ? null : reader.GetString(defaultValueOrdinal);
column.IsAutoIncrement =
!oldQueryMode && reader.GetBoolean(reader.GetOrdinal("isidentity")) ||
column.DefaultValue != null && column.DefaultValue.StartsWith("nextval(");
ColumnPostConfig(column, reader.GetInt32(reader.GetOrdinal("typmod")));
return(column);
}
NpgsqlDbColumn SetUpNonColumnField(FieldDescription field)
{
// ColumnName and BaseColumnName will be set later
var column = new NpgsqlDbColumn
{
BaseCatalogName = _connection.Database !,
BaseServerName = _connection.Host !,
IsReadOnly = true,
DataTypeName = field.PostgresType.DisplayName,
TypeOID = field.TypeOID,
TableOID = field.TableOID,
ColumnAttributeNumber = field.ColumnAttributeNumber,
PostgresType = field.PostgresType
};
ColumnPostConfig(column, field.TypeModifier);
return(column);
}
/// <summary>
/// Performs some post-setup configuration that's common to both table columns and non-columns.
/// </summary>
void ColumnPostConfig(NpgsqlDbColumn column, int typeModifier)
{
var typeMapper = _connection.Connector !.TypeMapper;
column.NpgsqlDbType = typeMapper.GetTypeInfoByOid(column.TypeOID).npgsqlDbType;
column.DataType = typeMapper.TryGetByOID(column.TypeOID, out var handler)
? handler.GetFieldType()
: null;
if (column.DataType != null)
{
column.IsLong = handler is ByteaHandler;
if (handler is ICompositeHandler)
{
column.UdtAssemblyQualifiedName = column.DataType.AssemblyQualifiedName;
}
}
var facets = column.PostgresType.GetFacets(typeModifier);
if (facets.Size != null)
{
column.ColumnSize = facets.Size;
}
if (facets.Precision != null)
{
column.NumericPrecision = facets.Precision;
}
if (facets.Scale != null)
{
column.NumericScale = facets.Scale;
}
}
}
}
public List <AraPayment> ReadAllPaymentsBetweenDates(DateTime initDate, DateTime endDate)
{
conn = DAO.getConnection();
AraPayment readPayment = new AraPayment();
List <AraPayment> payments = new List <AraPayment>();
int id;
DateTime paymentDate;
DateTime expirationDate;
String paypalTransactionId;
Double amount;
Section section;
Student student;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.PaymentListSP, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
NpgsqlParameter parameter_2 = new NpgsqlParameter();
parameter.ParameterName = DAOReportResource.IniDate;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter.Direction = ParameterDirection.Input;
parameter.Value = initDate;
command.Parameters.Add(parameter);
parameter_2.ParameterName = DAOReportResource.EndDate;
parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = endDate;
command.Parameters.Add(parameter_2);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
id = dr.GetInt32(0);
paymentDate = dr.GetDateTime(1);
expirationDate = dr.GetDateTime(2);
paypalTransactionId = dr.GetString(3);
amount = dr.GetDouble(4);
int remaingClasses = dr.GetInt32(5);
int sectionId = dr.GetInt32(6);
String studentId = dr.GetString(7);
section = new Section();
section.Id = sectionId;
student = new Student();
student.Email = studentId;
readPayment = new AraPayment(id, paymentDate, expirationDate, paypalTransactionId, amount, remaingClasses, section, student);
payments.Add(readPayment);
}
dr.Close();
tran.Commit();
return(payments);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
}
// POST api/values
// public int Post(string nome, string nomeIcone)
//{
// return 0;
//}
// DELETE api/values/5
//public void Delete(int id)
//{
//}
/*
*
* select * from AG_CUSTO_VARIADO
* SELECT * FROM AG_CUSTO_FIXO
* SELECT * FROM AG_ITEM_PRINCIPAL
* select * from AG_CUSTO_VARIADO
* SELECT * FROM AG_CUSTO_FIXO
* SELECT * FROM AG_ITEM_PRINCIPAL
* CREATE TABLE AG_ITEM_PRINCIPAL (
* ID_ITEM SERIAL PRIMARY KEY NOT NULL,
* NOME_ITEM VARCHAR NOT NULL,
* DATA_INSERIDO TIMESTAMP NOT NULL,
* NOME_ICONE VARCHAR
* )
*
* CREATE TABLE AG_CUSTO_VARIADO (
* ID_ITEM SERIAL NOT NULL,
* ID_CUSTO_VARIADO SERIAL PRIMARY KEY,
* DESCRICAO VARCHAR NOT NULL,
* VALOR NUMERIC(10,2) NOT NULL,
* UNIDADE_MEDIDA VARCHAR NOT NULL,
* QUANTIDADE NUMERIC(10,2) NOT NULL,
* DATA_INSERIDO TIMESTAMP NOT NULL,
* CONSTRAINT FK_ID_ITEM
* FOREIGN KEY(ID_ITEM)
* REFERENCES AG_ITEM_PRINCIPAL(ID_ITEM)
* )
*
* CREATE TABLE AG_CUSTO_FIXO (
* ID_ITEM SERIAL NOT NULL,
* ID_CUSTO_FIXO SERIAL PRIMARY KEY,
* DESCRICAO VARCHAR NOT NULL,
* VALOR NUMERIC(10,2) NOT NULL,
* UNIDADE_MEDIDA VARCHAR NOT NULL,
* QUANTIDADE NUMERIC(10,2) NOT NULL,
* DATA_INSERIDO TIMESTAMP NOT NULL,
* CONSTRAINT FK_ID_ITEM
* FOREIGN KEY(ID_ITEM)
* REFERENCES AG_ITEM_PRINCIPAL(ID_ITEM)
* )
*
* INSERT INTO AG_ITEM_PRINCIPAL (NOME_ITEM,DATA_INSERIDO,NOME_ICONE) VALUES ('COXINHA',NOW(),'')
* *///DB DATA
public void fillItens(int Id = -1)
{
itens.Clear();
//NpgsqlConnection conn = new NpgsqlConnection("postgres://*****:*****@Server=ec2-23-21-229-200.compute-1.amazonaws.com:5432/d1n6ia2nei0rp0");
//var connString = "Server=ec2-23-21-229-200.compute-1.amazonaws.com;Port=5432;User Id=qplkemryuxkzar;Password=b5b4b8a68c0eb36a8d7d28d4a1a3c2d6fe3ed8d75badd09f611ade3a38100f69;Database=d1n6ia2nei0rp0;";
using (var conn = new NpgsqlConnection("User ID =lmzbqjgdarlvte; Password =1bb829a00da2d53999826b2e32b86af9aff29a33a7d9fff483f6f1f7f4f87b61; Host =ec2-52-87-107-83.compute-1.amazonaws.com; Port =5432; Database =db9lde3i86qlva; Pooling = true; Use SSL Stream = True; SSL Mode = Require; TrustServerCertificate = True; "))
{
conn.Open();
{
// NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL", conn);
NpgsqlCommand command;
if (Id <= -1)
{
command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL", conn);
}
else
{
command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL WHERE ID_ITEM = " + Id, conn);
}
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
//(int id, string nome, DateTime data, string icone)
int id = dr.GetInt32(0);
string nome = dr.GetString(1);
DateTime _data = dr.GetDateTime(2);
string data = _data.ToString("dd/MM/yyyy");
string icone = dr.GetString(3);
itens.Add(new AG_ITEM_PRINCIPAL(id, nome, data, icone));
}
}
conn.Close();
conn.Open();
{
// NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL", conn);
NpgsqlCommand command2 = new NpgsqlCommand("SELECT * FROM AG_CUSTO_VARIADO ", conn);
NpgsqlDataReader dr2 = command2.ExecuteReader();
while (dr2.Read())
{
int id = dr2.GetInt32(1);
string descricao = dr2.GetString(2);
float valor = dr2.GetFloat(3);
string unidade_medida = dr2.GetString(4);
int quantidade = dr2.GetInt32(5);
DateTime _data_inserido = (dr2.GetDateTime(6));
string data_inserido = _data_inserido.ToString("dd/MM/yyyy");
foreach (AG_ITEM_PRINCIPAL item in itens)
{
if (item.ID_ITEM == dr2.GetInt32(0))
{
item.custosVariados.Add(new AG_CUSTO_VARIADO(dr2.GetInt32(0), id, descricao, valor, unidade_medida, quantidade, data_inserido));
break;
}
}
}
}
conn.Close();
conn.Open();
{
// NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL", conn);
NpgsqlCommand command3 = new NpgsqlCommand("SELECT * FROM AG_CUSTO_FIXO ", conn);
NpgsqlDataReader dr3 = command3.ExecuteReader();
while (dr3.Read())
{
int id = dr3.GetInt32(1);
string descricao = dr3.GetString(2);
float valor = dr3.GetFloat(3);
string unidade_medida = dr3.GetString(4);
int quantidade = dr3.GetInt32(5);
DateTime _data_inserido = (dr3.GetDateTime(6));
string data_inserido = _data_inserido.ToString("dd/MM/yyyy");
foreach (AG_ITEM_PRINCIPAL item in itens)
{
if (item.ID_ITEM == dr3.GetInt32(0))
{
item.custosFixos.Add(new AG_CUSTO_FIXO(dr3.GetInt32(0), id, descricao, valor, unidade_medida, quantidade, data_inserido));
break;
}
}
}
}
//Console.WriteLine("I hope that works");
//NpgsqlDataReader dr = command.ExecuteReader();
/*
* while (dr.Read())
* {
* int id = int.Parse(dr.GetString(0));
* string nome = dr.GetString(1);
* DateTime data = DateTime.Parse(dr.GetString(2));
* string icone = dr.GetString(3);
* itens.Add(new AG_ITEM_PRINCIPAL(id, nome, data, icone));
* }
*/
conn.Close();
}
//NpgsqlTransaction tran = conn.BeginTransaction();
//var command = new NpgsqlCommand("SELECT * FROM AG_ITEM_PRINCIPAL", conn);
//var reader = command.ExecuteReader();
/*
* command.CommandType = System.Data.CommandType.StoredProcedure;
*
* NpgsqlDataReader dr = command.ExecuteReader();
*
* if (conn.State == ConnectionState.Open)
* {
* Console.WriteLine("Connected!!");
* }
*
* while (dr.Read())
* {
* Console.Write("{0} \t {1} \t {2} \t {3}", dr[0], dr[1], dr[2], dr[3]);
* }
*/
//tran.Commit();
}
public List <Waiter> GetWaiters()
{
string query = "SELECT * from main.list_waiters()";
List <Waiter> waiters = new List <Waiter>();
dataBase = new DataBase();
NpgsqlDataReader npgsqlDataReader = dataBase.getDataReader(query);
while (npgsqlDataReader.Read())
{
Waiter waiter = new Waiter();
waiter.Id = npgsqlDataReader.IsDBNull(npgsqlDataReader.GetOrdinal("id")) ? 0 : npgsqlDataReader.GetInt32(npgsqlDataReader.GetOrdinal("id"));
waiter.Name = npgsqlDataReader.IsDBNull(npgsqlDataReader.GetOrdinal("waiter_name")) ? string.Empty : npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("waiter_name"));
waiters.Add(waiter);
}
dataBase.CloseAndDispose();
return(waiters);
}
/// <summary>
/// Método ConsultarPorId, consulta un evento dado su id y lo retorna como una entidad.
/// </summary>
/// <param name="objeto"> Entidad que contiene el id del evento a consultar</param>
public override Entidad ConsultarPorId(Entidad objeto)
{
try
{
evento = (Evento)objeto;
Conectar();
Comando = SqlConexion.CreateCommand();
Comando.CommandText = "ConsultarEventoPorIdEvento";
Comando.CommandType = CommandType.StoredProcedure;
parametro = new NpgsqlParameter();
parametro.NpgsqlDbType = NpgsqlDbType.Integer;
parametro.Value = evento.Id;
Comando.Parameters.Add(parametro);
leerDatos = Comando.ExecuteReader();
leerDatos.Read();
DateTime horaInicio = new DateTime();
horaInicio = horaInicio.AddHours(leerDatos.GetTimeSpan(6).Hours);
horaInicio = horaInicio.AddMinutes(leerDatos.GetTimeSpan(6).Minutes);
horaInicio = horaInicio.AddSeconds(leerDatos.GetTimeSpan(6).Seconds);
DateTime horaFin = new DateTime();
horaFin = horaFin.AddHours(leerDatos.GetTimeSpan(7).Hours);
horaFin = horaFin.AddMinutes(leerDatos.GetTimeSpan(7).Minutes);
horaFin = horaFin.AddSeconds(leerDatos.GetTimeSpan(7).Seconds);
evento.Id = leerDatos.GetInt32(0);
((Evento)evento).Nombre = leerDatos.GetString(1);
((Evento)evento).Descripcion = leerDatos.GetString(2);
((Evento)evento).Precio = leerDatos.GetDouble(3);
((Evento)evento).FechaInicio = leerDatos.GetDateTime(4);
((Evento)evento).FechaFin = leerDatos.GetDateTime(5);
((Evento)evento).HoraInicio = horaInicio;
((Evento)evento).HoraFin = horaFin;
((Evento)evento).Foto = leerDatos.GetString(8);
((Evento)evento).IdLocalidad = leerDatos.GetInt32(9);
((Evento)evento).IdCategoria = leerDatos.GetInt32(10);
leerDatos.Close();
}
catch (NpgsqlException e)
{
BaseDeDatosExcepcion ex = new BaseDeDatosExcepcion(e);
ex.NombreMetodos = this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name;
throw ex;
}
catch (InvalidCastException e)
{
CasteoInvalidoExcepcion ex = new CasteoInvalidoExcepcion(e);
ex.NombreMetodos = this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name;
throw ex;
}
catch (InvalidOperationException e)
{
OperacionInvalidaExcepcion ex = new OperacionInvalidaExcepcion(e);
ex.NombreMetodos.Add(this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name);
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Desconectar();
}
return(evento);
}
public ActionResult PurchaseManagment(PurchaseManagmentModels model)
{
int idToChange = model.Id;
string currentCondition = model.Condition;
string nextCondition = "";
string type = GetTypeOfPurchase(model.Id);
string mail = "";
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
NpgsqlCommand command =
new NpgsqlCommand($"select mail from currentpurchase where id = {idToChange}", connection);
NpgsqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
mail = dataReader.GetString(0);
}
}
if (currentCondition == "handles")
{
nextCondition = "delivers";
string information = "Ваш заказ доставляется!";
SendEmail(information, mail);
}
if (currentCondition == "delivers" && type == "Самовывоз")
{
nextCondition = "ready to pickup";
string information = "Ваш заказ доставлен до точки самовывоза и готов к нему!";
SendEmail(information, mail);
}
if (currentCondition == "delivers" && type == "Доставка до квартиры")
{
nextCondition = "finished";
string information = "Спасибо, что воспользовались нашим интернет магазином! Ждём Вас снова!";
SendEmail(information, mail);
}
if (currentCondition == "ready to pickup")
{
nextCondition = "finished";
string information = "Спасибо, что воспользовались нашим интернет магазином! Ждём Вас снова!";
SendEmail(information, mail);
}
if (nextCondition == "finished")
{
string purchase = "";
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand($"select purchase from currentpurchase where id = {idToChange}", connection);
NpgsqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
purchase = dataReader.GetString(0);
}
var purchasedProductsArr = purchase.Split(',');
string purchasedProducts = "";
for (int i = 0; i < purchasedProductsArr.Length; i++)
{
if (i == purchasedProductsArr.Length - 1) //если последний элемент
{
purchasedProducts += purchasedProductsArr[i].Split(':')[0];
}
else
{
purchasedProducts += purchasedProductsArr[i].Split(':')[0] + ",";
}
}
connection.Close();
connection.Open();
NpgsqlCommand command2 = new NpgsqlCommand($"delete from currentpurchase where id={idToChange}", connection);
command2.ExecuteNonQuery();
NpgsqlCommand command3 = new NpgsqlCommand($"update finishedpurchases set purchase='{purchase}', " +
$"finishedtime='{DateTime.Now}', finished={true}, purchasedproducts='{purchasedProducts}' where id={idToChange}", connection);
command3.ExecuteNonQuery();
connection.Close();
}
}
else
{
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand($"update currentpurchase set condition='{nextCondition}' where id={idToChange}", connection);
command.ExecuteNonQuery();
connection.Close();
}
}
List <PurchaseManagmentModels> Data = new List <PurchaseManagmentModels>();
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT id, condition FROM public.currentpurchase", connection);
NpgsqlDataReader dataReader = command.ExecuteReader();
for (int i = 0; dataReader.Read(); i++)
{
Data.Add(new PurchaseManagmentModels {
Id = dataReader.GetInt32(0), Condition = dataReader.GetString(1)
});
}
Data = Data.OrderBy(x => x.Id).ToList();
connection.Close();
}
ViewBag.Data = Data;
return(View());
}
/// <summary>
/// Metodo ConsultarLista, consulta todos los eventos.
/// </summary>
/// <param name="objeto"> Entidad para utilizar en la consulta</param>
/// <returns></returns>
public override List <Entidad> ConsultarLista(Entidad objeto)
{
lista = new List <Entidad>();
try
{
Conectar();
Comando = SqlConexion.CreateCommand();
Comando.CommandText = "ConsultarEventos";
Comando.CommandType = CommandType.StoredProcedure;
leerDatos = Comando.ExecuteReader();
while (leerDatos.Read())
{
evento = FabricaEntidad.CrearEntidadEvento();
DateTime horaInicio = new DateTime();
horaInicio = horaInicio.AddHours(leerDatos.GetTimeSpan(6).Hours);
horaInicio = horaInicio.AddMinutes(leerDatos.GetTimeSpan(6).Minutes);
horaInicio = horaInicio.AddSeconds(leerDatos.GetTimeSpan(6).Seconds);
DateTime horaFin = new DateTime();
horaFin = horaFin.AddHours(leerDatos.GetTimeSpan(7).Hours);
horaFin = horaFin.AddMinutes(leerDatos.GetTimeSpan(7).Minutes);
horaFin = horaFin.AddSeconds(leerDatos.GetTimeSpan(7).Seconds);
((Evento)evento).Id = leerDatos.GetInt32(0);
((Evento)evento).Nombre = leerDatos.GetString(1);
((Evento)evento).Descripcion = leerDatos.GetString(2);
((Evento)evento).Precio = leerDatos.GetDouble(3);
((Evento)evento).FechaInicio = leerDatos.GetDateTime(4);
((Evento)evento).FechaFin = leerDatos.GetDateTime(5);
((Evento)evento).HoraInicio = horaInicio;
((Evento)evento).HoraFin = horaFin;
((Evento)evento).Foto = leerDatos.GetString(8);
((Evento)evento).IdLocalidad = leerDatos.GetInt32(9);
((Evento)evento).IdCategoria = leerDatos.GetInt32(10);
lista.Add(evento);
}
}
catch (NpgsqlException e)
{
BaseDeDatosExcepcion ex = new BaseDeDatosExcepcion(e);
ex.NombreMetodos = this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name;
throw ex;
}
catch (InvalidCastException e)
{
CasteoInvalidoExcepcion ex = new CasteoInvalidoExcepcion(e);
ex.NombreMetodos = this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name;
throw ex;
}
catch (InvalidOperationException e)
{
OperacionInvalidaExcepcion ex = new OperacionInvalidaExcepcion(e);
ex.NombreMetodos.Add(this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name);
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Desconectar();
}
return(lista);
}
public List <Player> GetAllPlayers()
{
List <Player> players = new List <Player>();
string query = @"SELECT * FROM ""player"" p
LEFT JOIN
(SELECT playerId, SUM(xpvalue) as xp FROM exp GROUP BY playerId) e ON p.playerId = e.playerId";
Player p;
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
p = new Player(reader.GetGuid(0), reader.GetString(1), new Exp(reader.GetInt32(2)));
players.Add(p);
}
}
}
return(players);
}
internal static async Task <List <LoadWidgets> > GetWidgetAsync(TeamHttpContext httpContext, string widgettype, string widgetname)//, string widgetquery, string widgetquerylevel1, string widgetquerylevel2, string widgetquerylevel3, string widgetquerylevel4)
{
string userid = httpContext.ContextUserId;
List <LoadWidgets> returnValue = new List <LoadWidgets>();
PostgresService postgresService = new PostgresService();
List <UserDashboard> objUserDashboard = new List <UserDashboard>();
using (TeamDbContext dbContext = new TeamDbContext())
{
objUserDashboard = dbContext.UserDashboards.Where(x => x.CreatedBy == userid && x.DashboardChartType == widgettype && x.DashboardWidgetName == widgetname && x.Deleted == false).ToList();
}
if (objUserDashboard.Count != 0)
{
foreach (var item in objUserDashboard)
{
List <WidgetRead> objWidgetRead = new List <WidgetRead>();
string connString = Utility.GetConnectionString(item.WidgetConnectionString);
using (NpgsqlDataReader sqlData = postgresService.ExecuteSqlReturnReader(connString, item.WidgetQuery))
{
while (sqlData.Read())
{
objWidgetRead.Add(new WidgetRead
{
Name = sqlData.GetBlankStringIfNull("name"),
Count = sqlData.GetInt32("count")
});
}
}
LoadWidgets newItem = new LoadWidgets
{
DashboardWidgetName = item.DashboardWidgetName,
WidgetId = item.Id,
DashboardChartType = item.DashboardChartType,
DashboardUserPermission = item.DashboardUserPermission,
DashboardEmailFormat = item.DashboardEmailFormat,
WidgetData = objWidgetRead.ToArray(),
WidgetConnectionString = item.WidgetConnectionString,
WidgetSchedulerType = item.WidgetSchedulerType,
WidgetSchedulerEmailIDs = item.WidgetSchedulerEmailIDs,
WidgetQuery = item.WidgetQuery,
Level1ConnectionString = item.Level1ConnectionString,
Level1SchedulerType = item.Level1SchedulerType,
L1SchedulerEmailIDs = item.L1SchedulerEmailIDs,
DashbaordQueryL1 = item.DashbaordQueryL1,
Level2ConnectionString = item.Level2ConnectionString,
Level2SchedulerType = item.Level2SchedulerType,
L2SchedulerEmailIDs = item.L2SchedulerEmailIDs,
DashbaordQueryL2 = item.DashbaordQueryL2,
Level3ConnectionString = item.Level3ConnectionString,
Level3SchedulerType = item.Level3SchedulerType,
L3SchedulerEmailIDs = item.L3SchedulerEmailIDs,
DashbaordQueryL3 = item.DashbaordQueryL3,
Level4ConnectionString = item.Level4ConnectionString,
Level4SchedulerType = item.Level4SchedulerType,
L4SchedulerEmailIDs = item.L4SchedulerEmailIDs,
DashbaordQueryL4 = item.DashbaordQueryL4,
WidgetSendEmail = item.WidgetSendEmail
};
returnValue.Add(newItem);
}
}
return(returnValue);
}
/// <summary>
/// Retrieves the session data from the data source.
/// </summary>
/// <param name="lockRecord">If true GetSessionStoreItem locks the record and sets a new LockId and LockDate.</param>
private SessionStateStoreData GetSessionStoreItem(bool lockRecord, HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actionFlags)
{
SessionStateStoreData result = null;
lockAge = TimeSpan.Zero;
lockId = null;
locked = false;
actionFlags = 0;
DateTime expires = DateTime.MinValue;
int timeout = 0;
string serializedItems = null;
using (NpgsqlConnection dbConn = new NpgsqlConnection(m_connectionString))
{
NpgsqlTransaction dbTrans = null;
try
{
dbConn.Open();
dbTrans = dbConn.BeginTransaction();
// Retrieve the current session item information and lock row
using (NpgsqlCommand dbCommand = dbConn.CreateCommand())
{
dbCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "SELECT \"Expires\", \"Timeout\", \"Locked\", \"LockId\", \"LockDate\", \"Data\", \"Flags\" FROM \"{0}\" WHERE \"SessionId\" = @SessionId AND \"ApplicationName\" = @ApplicationName FOR UPDATE", s_tableName);
dbCommand.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = id;
dbCommand.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = m_applicationName;
using (NpgsqlDataReader reader = dbCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
while (reader.Read())
{
expires = reader.GetDateTime(0);
timeout = reader.GetInt32(1);
locked = reader.GetBoolean(2);
lockId = reader.GetInt32(3);
lockAge = DateTime.Now.Subtract(reader.GetDateTime(4));
if (!reader.IsDBNull(5))
{
serializedItems = reader.GetString(5);
}
actionFlags = (SessionStateActions)reader.GetInt32(6);
}
reader.Close();
}
}
// If record was not found, is expired or is locked, return.
if (expires < DateTime.Now || locked)
{
return(result);
}
// If the actionFlags parameter is not InitializeItem, deserialize the stored SessionStateItemCollection
if (actionFlags == SessionStateActions.InitializeItem)
{
result = CreateNewStoreData(context, Convert.ToInt32(m_config.Timeout.TotalMinutes));
}
else
{
result = new SessionStateStoreData(Deserialize(serializedItems), SessionStateUtility.GetSessionStaticObjects(context), Convert.ToInt32(m_config.Timeout.TotalMinutes));
}
if (lockRecord)
{
lockId = (int)lockId + 1;
// Obtain a lock to the record
using (NpgsqlCommand dbCommand = dbConn.CreateCommand())
{
dbCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "UPDATE \"{0}\" SET \"Locked\" = @Locked, \"LockId\" = @LockId,\"LockDate\" = @LockDate, \"Flags\" = @Flags WHERE \"SessionId\" = @SessionId AND \"ApplicationName\" = @ApplicationName", s_tableName);
dbCommand.Parameters.Add("@Locked", NpgsqlDbType.Boolean).Value = true;
dbCommand.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = lockId;
dbCommand.Parameters.Add("@LockDate", NpgsqlDbType.TimestampTZ).Value = DateTime.Now;
dbCommand.Parameters.Add("@Flags", NpgsqlDbType.Integer).Value = 0;
dbCommand.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = id;
dbCommand.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = m_applicationName;
dbCommand.ExecuteNonQuery();
}
}
// Attempt to commit the transaction
dbTrans.Commit();
}
catch (Exception e)
{
Trace.WriteLine(e.ToString());
if (dbTrans != null)
{
try
{
// Attempt to roll back the transaction
Trace.WriteLine(Properties.Resources.LogRollbackAttempt);
dbTrans.Rollback();
}
catch (Exception re)
{
// Rollback failed
Trace.WriteLine(Properties.Resources.ErrRollbackFailed);
Trace.WriteLine(re.ToString());
}
}
throw new ProviderException(Properties.Resources.ErrOperationAborted);
}
finally
{
if (dbTrans != null)
{
dbTrans.Dispose();
}
if (dbConn != null)
{
dbConn.Close();
}
}
return(result);
}
}
public List <Itinerario> ConsultarItinerariosCorreo(int id_usuario)
{
List <Itinerario> itinerarios = new List <Itinerario>(); // Lista de itinerarios de un usuario
try
{
con = new ConexionBase();
con.Conectar();
comm = new NpgsqlCommand("consultar_itinerarios_correo", con.SqlConexion);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Integer, id_usuario);
NpgsqlDataReader pgread = comm.ExecuteReader();
int auxiliar = 0;
//Recorremos los registros devueltos
while (pgread.Read())
{
Itinerario iti;
if (!pgread.IsDBNull(2) && (!pgread.IsDBNull(3)))
{
iti = new Itinerario(pgread.GetInt32(0), pgread.GetString(1), pgread.GetDateTime(2), pgread.GetDateTime(3), id_usuario, true);
}
else
{
iti = new Itinerario(pgread.GetInt32(0), pgread.GetString(1), id_usuario, true);
}
//Se revisa si el registro de itinerario en la base ya se encuentra en la lista de itinerarios del usuario
if (itinerarios.Count == 0)
{
itinerarios.Add(iti);
}
foreach (Itinerario itinerario in itinerarios)
{
if (itinerario.Id == iti.Id)
{
auxiliar = 1;
}
}
if (auxiliar != 1)
{
itinerarios.Add(iti);
}
auxiliar = 0;
//Agregamos los eventos, actividades y lugares a la lista correspondiente
//Si existe lugar turistico en este registro
if (!pgread.IsDBNull(4))
{
dynamic lugar = new System.Dynamic.ExpandoObject();
lugar.Nombre = pgread.GetString(4);
lugar.Descripcion = pgread.GetString(5);
lugar.Tipo = "Lugar Turistico";
itinerarios[itinerarios.Count - 1].Items_agenda.Add(lugar);
}
//Si existe actividad en este registro
if (!pgread.IsDBNull(6))
{
dynamic actividad = new System.Dynamic.ExpandoObject();
actividad.Nombre = pgread.GetString(6);
actividad.Descripcion = pgread.GetString(7);
actividad.Tipo = "Actividad";
itinerarios[itinerarios.Count - 1].Items_agenda.Add(actividad);
}
//Si existe eventos en este registro.
if (!pgread.IsDBNull(8))
{
dynamic evento = new System.Dynamic.ExpandoObject();
evento.Nombre = pgread.GetString(8);
evento.Descripcion = pgread.GetString(9);
evento.Tipo = "Evento";
itinerarios[itinerarios.Count - 1].Items_agenda.Add(evento);
}
}
con.Desconectar();
return(itinerarios);
}
catch (NpgsqlException sql)
{
con.Desconectar();
throw sql;
}
catch (ArgumentException arg)
{
con.Desconectar();
throw arg;
}
catch (InvalidCastException cast)
{
con.Desconectar();
throw cast;
}
catch (Exception e)
{
con.Desconectar();
throw e;
}
}
public int CreateOffer(Offer offer)
{
conn = DAO.getConnection();
NpgsqlCommand command = new NpgsqlCommand(DAOOfferResource.CreateOfferSP, conn);
NpgsqlTransaction transaction = conn.BeginTransaction();
NpgsqlParameter name = new NpgsqlParameter();
NpgsqlParameter minArticles = new NpgsqlParameter();
NpgsqlParameter percentage = new NpgsqlParameter();
NpgsqlParameter initDate = new NpgsqlParameter();
NpgsqlParameter endDate = new NpgsqlParameter();
NpgsqlParameter description = new NpgsqlParameter();
name.ParameterName = DAOOfferResource.Name;
minArticles.ParameterName = DAOOfferResource.MinArticles;
percentage.ParameterName = DAOOfferResource.Percentage;
initDate.ParameterName = DAOOfferResource.InitDate;
endDate.ParameterName = DAOOfferResource.EndDate;
description.ParameterName = DAOOfferResource.Description;
name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
minArticles.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
percentage.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
initDate.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
endDate.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
description.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
name.Direction = ParameterDirection.Input;
minArticles.Direction = ParameterDirection.Input;
percentage.Direction = ParameterDirection.Input;
initDate.Direction = ParameterDirection.Input;
endDate.Direction = ParameterDirection.Input;
description.Direction = ParameterDirection.Input;
name.Value = offer.Name;
minArticles.Value = offer.MinArticles;
percentage.Value = offer.Percentage;
initDate.Value = offer.InitDate;
endDate.Value = offer.EndDate;
description.Value = offer.Description;
command.Parameters.Add(name);
command.Parameters.Add(minArticles);
command.Parameters.Add(percentage);
command.Parameters.Add(initDate);
command.Parameters.Add(endDate);
command.Parameters.Add(description);
command.CommandType = CommandType.StoredProcedure;
int response = 500;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
response = dr.GetInt32(0);
}
dr.Close();
transaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(response);
}
//-----------------------------------------------------------------------------------------------------
public Adress Select(Adress a)
{
NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand cmd = new NpgsqlCommand(null, con);
cmd.CommandText = "SELECT * FROM Adresse where adressId=@id";
NpgsqlParameter id = new NpgsqlParameter("@id", NpgsqlTypes.NpgsqlDbType.Numeric);
id.Value = a.AdressId;
cmd.Parameters.Add(id);
NpgsqlDataReader reader = cmd.ExecuteReader();
reader.Read();
Adress ad = new Adress();
if (reader.HasRows)
{
if (!(reader.IsDBNull(0)))
{
ad.AdressId = long.Parse(reader[0].ToString());
}
if (!(reader.IsDBNull(1)))
{
ad.Street = reader[1].ToString();
}
if (!(reader.IsDBNull(2)))
{
ad.City = reader.GetString(2);
}
if (!(reader.IsDBNull(3)))
{
ad.Country = reader.GetString(3);
}
if (!(reader.IsDBNull(4)))
{
ad.HouseNumber = reader.GetInt32(4);
}
if (!(reader.IsDBNull(5)))
{
ad.StairNumber = reader.GetInt32(5);
}
if (!(reader.IsDBNull(6)))
{
// stock fehlt ad. = reader[6].ToString();
}
if (!(reader.IsDBNull(7)))
{
ad.ZipCode = reader.GetInt32(7);
}
if (!(reader.IsDBNull(8)))
{
ad.DoorNumber = reader.GetInt32(8);
}
}
reader.Close();
con.Close();
return(ad);
}
public void mostrarInfo(DataGridView tablaVuelos, string origen, string destino, string directo, ArrayList paises, ArrayList escalas, ArrayList preciosescalas, ArrayList dura, int pasajeros, int habi)
{
if (directo == "Directo")
{
connection = conexion1.Conexion();
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT ru.pais_origen, ru.pais_destino, tv.precio,ru.duracion FROM rutas as ru JOIN tarijas_vuelos as tv on tv.codigo_ruta = ru.codigo " +
"WHERE ru.pais_origen = '" + origen + "' AND pais_destino = '" + destino + "'", connection);
NpgsqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
tablaVuelos.Rows.Add(reader.GetString(0), reader.GetString(1), directo, reader.GetString(1), reader.GetString(0), directo, reader.GetInt32(2), 0, reader.GetString(3), pasajeros, habi);
}
}
finally
{
reader.Close();
cmd.Dispose();
connection.Close();
}
}
else
{
for (int i = 0; i < paises.Count; i++)
{
tablaVuelos.Rows.Add(origen, destino, escalas[i], destino, origen, escalas[i], preciosescalas[i], dura[i], 0, pasajeros, habi);
}
}
tablaVuelos.Refresh();
tablaVuelos.ClearSelection();
}
private void FillPerson(Person p, NpgsqlDataReader reader)
{
if (reader.HasRows)
{
//the Reader needs to
reader.Read();
if (!reader.IsDBNull(0))
{
p.Id = reader.GetInt32(0);
}
if (!reader.IsDBNull(2))
{
p.FirstName = reader.GetString(2);
}
if (!reader.IsDBNull(1))
{
p.EMail = reader.GetString(1);
}
if (!reader.IsDBNull(3))
{
p.LastName = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
p.PhoneNumber = reader.GetString(4);
}
if (!(reader.IsDBNull(6)))
{
p.MobilePhone = reader.GetString(6);
}
if (!(reader.IsDBNull(5)))
{
p.HomeAdress.AdressId = Convert.ToInt32(reader.GetInt32(5));
}
if (!(reader.IsDBNull(7)))
{
p.IBAN = reader.GetString(7);
}
if (!(reader.IsDBNull(8)))
{
p.BIC = reader.GetString(8);
}
if (!(reader.IsDBNull(9)))
{
p.AccountHolder = reader.GetString(9);
}
if (!(reader.IsDBNull(10)))
{
p.SVN = Convert.ToInt64(reader.GetDecimal(10));
}
if (!(reader.IsDBNull(11)))
{
p.nationality = reader.GetString(11);
}
if (!(reader.IsDBNull(12)))
{
p.InfoField = reader.GetString(12);
}
if (!(reader.IsDBNull(13)))
{
p.HomeAdress = new Adress();
if (!(reader.IsDBNull(14)))
{
p.HomeAdress.Street = reader.GetString(14);
}
if (!(reader.IsDBNull(15)))
{
p.HomeAdress.City = reader.GetString(15);
}
if (!(reader.IsDBNull(16)))
{
p.HomeAdress.Country = reader.GetString(16);
}
if (!(reader.IsDBNull(17)))
{
p.HomeAdress.HouseNumber = reader.GetInt32(17);
}
if (!(reader.IsDBNull(18)))
{
p.HomeAdress.StairNumber = reader.GetInt32(18);
}
if (!(reader.IsDBNull(19)))
{
p.HomeAdress.Etage = reader.GetInt32(19);
}
if (!(reader.IsDBNull(20)))
{
p.HomeAdress.ZipCode = reader.GetInt32(20);
}
if (!(reader.IsDBNull(21)))
{
p.HomeAdress.DoorNumber = reader.GetInt32(21);
}
}
}
}
public List <ClassMeeting> ReadClassByTeacherBetweenDates(DateTime initDate, DateTime endDate, Teacher teacher)
{
conn = DAO.getConnection();
ClassMeeting meeting = new ClassMeeting();
List <ClassMeeting> meetings = new List <ClassMeeting>();
String id;
String uuId;
String hostId;
String hostEmail;
String topic;
int type;
String status;
DateTime startTime;
int duration;
String timezone;
String agenda;
DateTime createdAt;
String startUrl;
String joinUrl;
String password;
String h323Password;
String pstnPassword;
String encryptedPassword;
String comment;
Section section;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.ReadClassByTeacherPeriodSP, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
NpgsqlParameter parameter_2 = new NpgsqlParameter();
NpgsqlParameter parameter_3 = new NpgsqlParameter();
parameter.ParameterName = DAOReportResource.IniDate;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter.Direction = ParameterDirection.Input;
parameter.Value = initDate;
command.Parameters.Add(parameter);
parameter_2.ParameterName = DAOReportResource.EndDate;
parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = endDate;
command.Parameters.Add(parameter_2);
parameter_3.ParameterName = DAOReportResource.Id;
parameter_3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
parameter_3.Direction = ParameterDirection.Input;
parameter_3.Value = teacher.Email;
command.Parameters.Add(parameter_3);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
id = dr.GetString(0);
uuId = dr.GetString(1);
hostId = dr.GetString(2);
hostEmail = dr.GetString(3);
topic = dr.GetString(4);
type = dr.GetInt32(5);
status = dr.GetString(6);
startTime = dr.GetDateTime(7);
duration = dr.GetInt32(8);
timezone = dr.GetString(9);
agenda = dr.GetString(10);
createdAt = dr.GetDateTime(11);
startUrl = dr.GetString(12);
joinUrl = dr.GetString(13);
password = dr.GetString(14);
h323Password = dr.GetString(15);
pstnPassword = dr.GetString(16);
encryptedPassword = dr.GetString(17);
comment = dr.GetString(18);
int sectionId = dr.GetInt32(19);
String sectionName = dr.GetString(20);
String teacherId = dr.GetString(21);
String teacherName = dr.GetString(22);
String teacherLastname = dr.GetString(23);
section = new Section();
teacher = new Teacher();
section.Id = sectionId;
section.Name = sectionName;
teacher.Email = teacherId;
teacher.Name = teacherName;
teacher.LastName = teacherLastname;
meeting = new ClassMeeting(id, uuId, hostId, hostEmail, topic, type, status, startTime, duration, timezone, agenda, createdAt, startUrl, joinUrl, password, h323Password, pstnPassword, encryptedPassword, comment, section, teacher);
meetings.Add(meeting);
}
dr.Close();
tran.Commit();
return(meetings);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
throw new NotImplementedException();
}
public static Clases._PERSONAL LLenar(NpgsqlDataReader Dr)
{
return(new Clases._PERSONAL(Dr.GetInt32(0), Dr.GetString(1), Dr.GetString(2), Dr.GetString(3), Dr.GetDateTime(4), Dr.GetInt32(5), Dr.GetInt32(6), Dr.GetDateTime(7), Dr.GetDateTime(8), Dr.GetInt32(9), Dr.GetInt32(10)));
}
public List <Teacher> CalculateTeacherSalaryBetweenDates(DateTime initDate, DateTime endDate)
{
conn = DAO.getConnection();
ClassMeeting meeting = new ClassMeeting();
int salary = -1;
Teacher teacher = new Teacher();
List <Teacher> teachers = new List <Teacher>();
String id;
String uuId;
String hostId;
String hostEmail;
String topic;
int type;
String status;
DateTime startTime;
int duration;
String timezone;
String agenda;
DateTime createdAt;
String startUrl;
String joinUrl;
String password;
String h323Password;
String pstnPassword;
String encryptedPassword;
String comment;
Section section;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.TotalHoursTeacher, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
NpgsqlParameter parameter_2 = new NpgsqlParameter();
NpgsqlParameter parameter_3 = new NpgsqlParameter();
parameter.ParameterName = DAOReportResource.IniDate;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter.Direction = ParameterDirection.Input;
parameter.Value = initDate;
command.Parameters.Add(parameter);
parameter_2.ParameterName = DAOReportResource.EndDate;
parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = endDate;
command.Parameters.Add(parameter_2);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
teacher = new Teacher();
teacher.Salary = dr.GetInt32(0);
teacher.Email = dr.GetString(1);
teacher.Name = dr.GetString(2);
teacher.LastName = dr.GetString(3);
teachers.Add(teacher);
}
dr.Close();
tran.Commit();
return(teachers);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
throw new NotImplementedException();
}
private void MappingDataReaderToContCard(NpgsqlDataReader npgsqlDataReader, ContInOut contInOut)
{
contInOut.ContInOutId = npgsqlDataReader.GetInt64(0);
contInOut.Cont = npgsqlDataReader.GetString(1);
contInOut.CustomerCode = npgsqlDataReader.GetString(2);
contInOut.Size = npgsqlDataReader.GetString(3);
contInOut.Type = npgsqlDataReader.GetString(4);
contInOut.Berat = npgsqlDataReader.GetString(5);
contInOut.Manufacture = npgsqlDataReader.GetString(6);
contInOut.CscPlate = npgsqlDataReader.GetString(7);
contInOut.RfMachine = npgsqlDataReader.GetString(8);
contInOut.Tare = npgsqlDataReader.GetString(9);
if (npgsqlDataReader["dtmin"] != DBNull.Value)
{
contInOut.DtmIn = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmin")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
if (npgsqlDataReader["dtmout"] != DBNull.Value)
{
contInOut.DtmOut = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmout")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
if (npgsqlDataReader["dtmrepaired"] != DBNull.Value)
{
contInOut.DtmRepaired = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmrepaired")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
if (npgsqlDataReader["dtmpti"] != DBNull.Value)
{
contInOut.DtmPti = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmpti")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.WashStatus = npgsqlDataReader.GetString(14);
contInOut.Condition = npgsqlDataReader.GetString(15);
contInOut.ActIn = npgsqlDataReader.GetString(16);
contInOut.ActOut = npgsqlDataReader.GetString(17);
contInOut.Location = npgsqlDataReader.GetString(18);
contInOut.ExVessel = npgsqlDataReader.GetString(19);
contInOut.ExVesselName = npgsqlDataReader.GetString(20);
contInOut.Consignee = npgsqlDataReader.GetString(21);
if (npgsqlDataReader[75] != DBNull.Value)
{
contInOut.Commodity = npgsqlDataReader.GetString(75);
}
contInOut.NoMobilIn = npgsqlDataReader.GetString(22);
contInOut.AngkutanIn = npgsqlDataReader.GetString(23);
contInOut.Payload = npgsqlDataReader.GetString(24);
contInOut.NoMobilOut = npgsqlDataReader.GetString(25);
contInOut.AngkutanOut = npgsqlDataReader.GetString(26);
contInOut.DoNumber = npgsqlDataReader.GetString(27);
contInOut.Shipper = npgsqlDataReader.GetString(28);
contInOut.Destination = npgsqlDataReader.GetString(29);
contInOut.DestinationName = npgsqlDataReader.GetString(30);
contInOut.Seal = npgsqlDataReader.GetString(31);
contInOut.VesselVoyage = npgsqlDataReader.GetString(32);
contInOut.VesselVoyageName = npgsqlDataReader.GetString(33);
contInOut.NoSeriOrOut = npgsqlDataReader.GetString(34);
if (npgsqlDataReader["dtmoutdepoin"] != DBNull.Value)
{
contInOut.DtmOutDepoIn = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmoutdepoin")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.EdiIn = npgsqlDataReader.GetString(37);
contInOut.EdiOut = npgsqlDataReader.GetString(38);
contInOut.EdiWash = npgsqlDataReader.GetString(39);
contInOut.EdiApr = npgsqlDataReader.GetString(40);
contInOut.EdiCom = npgsqlDataReader.GetString(41);
contInOut.EdiPti = npgsqlDataReader.GetString(42);
contInOut.EdiSync = npgsqlDataReader.GetString(43);
contInOut.EirIn = npgsqlDataReader.GetString(44);
contInOut.EirOut = npgsqlDataReader.GetString(45);
if (npgsqlDataReader["dtmeirin"] != DBNull.Value)
{
contInOut.DtmEirIn = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmeirin")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.EirInContact = npgsqlDataReader.GetString(47);
if (npgsqlDataReader["dtmportout"] != DBNull.Value)
{
contInOut.DtmPortOut = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmportout")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.BlNumber = npgsqlDataReader.GetString(49);
contInOut.CleaningRefNo = npgsqlDataReader.GetString(50);
contInOut.CleaningRemark = npgsqlDataReader.GetString(51);
contInOut.CleaningLastCargo = npgsqlDataReader.GetString(52);
contInOut.CleaningCost = npgsqlDataReader.GetDouble(53);
if (npgsqlDataReader["cleaningdtmfinish"] != DBNull.Value)
{
contInOut.CleaningDtmFinish = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("cleaningdtmfinish")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.BookingAssignment = npgsqlDataReader.GetString(55);
contInOut.CleaningKode = npgsqlDataReader.GetString(56);
contInOut.CleaningDesc = npgsqlDataReader.GetString(57);
contInOut.CleaningAction = npgsqlDataReader.GetString(58);
if (npgsqlDataReader["dtmshortpti"] != DBNull.Value)
{
contInOut.DtmShortPti = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtmshortpti")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.ExVesselPort = npgsqlDataReader.GetString(60);
contInOut.RfEngineCond = npgsqlDataReader.GetString(61);
if (npgsqlDataReader["rfdtmenginerepaired"] != DBNull.Value)
{
contInOut.RfDtmEngineRepaired = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("rfdtmenginerepaired")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.RfEngineEdiIn = npgsqlDataReader.GetString(63);
contInOut.RfEngineEdiCom = npgsqlDataReader.GetString(64);
contInOut.RfPtiType = npgsqlDataReader.GetString(65);
if (npgsqlDataReader["rfptidtmapproved"] != DBNull.Value)
{
contInOut.RfPtiDtmApproved = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("rfptidtmapproved")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
if (npgsqlDataReader["rfptidtmcompleted"] != DBNull.Value)
{
contInOut.RfPtiDtmCompleted = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("rfptidtmcompleted")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
contInOut.RfPtiRemark = GetRFPTIRemark(contInOut.Cont, npgsqlDataReader.GetString(68));
contInOut.RfPtiCost = npgsqlDataReader.GetDouble(69);
contInOut.RfPtiTemp = npgsqlDataReader.GetString(70);
contInOut.RfNeedSwUpdate = npgsqlDataReader.GetInt32(71);
if (npgsqlDataReader["rfdtmswupdated"] != DBNull.Value)
{
contInOut.RfDtmSwUpdated = npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("rfdtmswupdated")).ToString(GlobalConstant.DATE_YMDHMS_LONG_FORMAT);
}
if (npgsqlDataReader[73] != DBNull.Value)
{
contInOut.GradeV2 = npgsqlDataReader.GetString(73);
}
if (npgsqlDataReader[74] != DBNull.Value)
{
contInOut.MddcRemark = npgsqlDataReader.GetString(74);
}
if (npgsqlDataReader["businessunit"] != DBNull.Value)
{
contInOut.BusinessUnit = npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("businessunit"));
}
if (npgsqlDataReader[npgsqlDataReader.GetOrdinal("ventilation")] != DBNull.Value)
{
contInOut.Ventilation = npgsqlDataReader.GetInt32(npgsqlDataReader.GetOrdinal("ventilation"));
}
if (npgsqlDataReader[npgsqlDataReader.GetOrdinal("humidity")] != DBNull.Value)
{
contInOut.Humidity = npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("humidity"));
}
if (npgsqlDataReader[npgsqlDataReader.GetOrdinal("vendorangkutanin")] != DBNull.Value)
{
contInOut.VendorAngkutanIn = npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("vendorangkutanin"));
}
if (npgsqlDataReader[npgsqlDataReader.GetOrdinal("rkemin")] != DBNull.Value)
{
contInOut.RkemIn = npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("rkemin"));
}
if (npgsqlDataReader[npgsqlDataReader.GetOrdinal("isfreeuse")] != DBNull.Value)
{
contInOut.IsFreeUse = npgsqlDataReader.GetBoolean(npgsqlDataReader.GetOrdinal("isfreeuse"));
}
}
public static void WriteValue <T>(this TypeFieldSchema field, T entity, NpgsqlDataReader reader, int index) where T : class, new()
{
switch (field.FieldType)
{
case FieldTypeEnum.Serial:
case FieldTypeEnum.Integer:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetInt32(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(int), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetInt32(index), null));
}
}
break;
case FieldTypeEnum.BigInteger:
case FieldTypeEnum.BigSerial:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetInt64(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(long), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetInt64(index), null));
}
}
break;
case FieldTypeEnum.VarChar:
case FieldTypeEnum.Text:
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetString(index), null));
}
break;
case FieldTypeEnum.Double:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDouble(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(double), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDouble(index), null));
}
}
break;
case FieldTypeEnum.Boolean:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetBoolean(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(bool), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetBoolean(index), null));
}
}
break;
case FieldTypeEnum.Decimal:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDecimal(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(decimal), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDecimal(index), null));
}
}
break;
case FieldTypeEnum.TimeStamp:
if (field.IsNullable)
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDateTime(index), null));
}
}
else
{
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, default(DateTime), null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetDateTime(index), null));
}
}
break;
case FieldTypeEnum.GeometryPoint:
case FieldTypeEnum.GeometryLineString:
case FieldTypeEnum.GeometryMultiLineString:
case FieldTypeEnum.GeometryPolygon:
case FieldTypeEnum.GeometryMultiPolygon:
case FieldTypeEnum.GeometryCollection:
if (reader.IsDBNull(index))
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, null, null));
}
else
{
field.PropertyInfo.SetValue(entity, field.Converter.ConvertTo(null, null, reader.GetString(index), null));
}
break;
}
}
public static void UpdatePlayerStats(string i_username, string result)
{
if (DBManagment.has_session(i_username) == true)
{
//fetching elo, wins, defeats, draws of player
using var con = new NpgsqlConnection(DBManagment.cs);
con.Open();
string sql = "SELECT elo, wins, defeats, draws from game_user where username = @username";
using var cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("username", i_username);
cmd.Prepare();
using NpgsqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
int elo = rdr.GetInt32(0);
int wins = rdr.GetInt32(1);
int defeats = rdr.GetInt32(2);
int draws = rdr.GetInt32(3);
rdr.Close();
//////////
string sql2 = "Update game_user set elo = @elo, wins = @wins, defeats=@defeats, draws = @draws where username = @username";
using var cmd2 = new NpgsqlCommand(sql2, con);
cmd.Parameters.AddWithValue("username", i_username);
if (result == "winner")
{
cmd2.Parameters.AddWithValue("username", i_username);
cmd2.Parameters.AddWithValue("elo", elo + 3);
cmd2.Parameters.AddWithValue("wins", wins + 1);
cmd2.Parameters.AddWithValue("defeats", defeats);
cmd2.Parameters.AddWithValue("draws", draws);
}
else if (result == "looser")
{
cmd2.Parameters.AddWithValue("username", i_username);
cmd2.Parameters.AddWithValue("elo", elo - 5);
cmd2.Parameters.AddWithValue("wins", wins);
cmd2.Parameters.AddWithValue("defeats", defeats + 1);
cmd2.Parameters.AddWithValue("draws", draws);
}
else if (result == "draw")
{
cmd2.Parameters.AddWithValue("username", i_username);
cmd2.Parameters.AddWithValue("elo", elo);
cmd2.Parameters.AddWithValue("wins", wins);
cmd2.Parameters.AddWithValue("defeats", defeats);
cmd2.Parameters.AddWithValue("draws", draws + 1);
}
else
{
return;
}
cmd2.Prepare();
cmd2.ExecuteNonQuery();
con.Close();
return;
}
else
{
return;
}
}
/// <summary>
/// Returns all data within the given bounding box and filtered by the given filter.
/// </summary>
/// <param name="box"></param>
/// <param name="filter"></param>
/// <returns></returns>
public override IList <OsmGeo> Get(GeoCoordinateBox box, OsmSharp.Osm.Filters.Filter filter)
{
// initialize connection.
NpgsqlConnection con = this.CreateConnection();
List <OsmGeo> res = new List <OsmGeo>();
// calculate bounding box parameters to query db.
long latitude_min = (long)(box.MinLat * 10000000.0);
long longitude_min = (long)(box.MinLon * 10000000.0);
long latitude_max = (long)(box.MaxLat * 10000000.0);
long longitude_max = (long)(box.MaxLon * 10000000.0);
// calculate bounding box parameters to query db.
TileRange range = TileRange.CreateAroundBoundingBox(box, 14);
IList <long> boxes = new List <long>();
foreach (Tile tile in range)
{
boxes.Add((long)tile.Id);
}
// STEP 1: query nodes table.
//id latitude longitude changeset_id visible timestamp tile version
string sql
= "SELECT id, latitude, longitude, changeset_id, visible, timestamp, tile, version, usr, usr_id FROM node WHERE (visible = true) AND (tile IN ({4})) AND (latitude >= {0} AND latitude < {1} AND longitude >= {2} AND longitude < {3})";
sql = string.Format(sql,
latitude_min.ToString(),
latitude_max.ToString(),
longitude_min.ToString(),
longitude_max.ToString(),
this.ConstructIdList(boxes));
// TODO: parameters.
NpgsqlCommand com = new NpgsqlCommand(sql);
com.Connection = con;
NpgsqlDataReader reader = com.ExecuteReader();
Node node = null;
Dictionary <long, Node> nodes = new Dictionary <long, Node>();
List <long> nodeIds = new List <long>();
while (reader.Read())
{
// load/parse data.
long returned_id = reader.GetInt64(0);
int latitude_int = reader.GetInt32(1);
int longitude_int = reader.GetInt32(2);
long? changeset_id = reader.IsDBNull(3) ? null : (long?)reader.GetInt64(3);
bool? visible = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
DateTime?timestamp = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
long tile = reader.GetInt64(6);
ulong? version = reader.IsDBNull(7) ? null : (ulong?)reader.GetInt32(7);
string usr = reader.IsDBNull(8) ? null : reader.GetString(8);
long? usr_id = reader.IsDBNull(9) ? null : (long?)reader.GetInt32(9);
if (!nodes.ContainsKey(returned_id))
{
// create node.
node = new Node();
node.Id = returned_id;
node.Version = version;
node.UserId = usr_id;
node.UserName = usr;
node.TimeStamp = timestamp;
node.ChangeSetId = changeset_id;
node.Latitude = ((double)latitude_int) / 10000000.0;
node.Longitude = ((double)longitude_int) / 10000000.0;
node.Visible = visible;
nodes.Add(node.Id.Value, node);
nodeIds.Add(node.Id.Value);
}
}
reader.Close();
// STEP2: Load all node tags.
this.LoadNodeTags(nodes);
res.AddRange(nodes.Values);
// load all ways that contain the nodes that have been found.
res.AddRange(this.GetWaysFor(nodeIds));
// get relations containing any of the nodes or ways in the current results-list.
List <Relation> relations = new List <Relation>();
HashSet <long> relationIds = new HashSet <long>();
foreach (OsmGeo osmGeo in res)
{
IList <Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
relations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
// recursively add all relations containing other relations as a member.
do
{
res.AddRange(relations); // add previous relations-list.
List <Relation> newRelations = new List <Relation>();
foreach (OsmGeo osmGeo in relations)
{
IList <Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
newRelations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
relations = newRelations;
} while (relations.Count > 0);
if (filter != null)
{
List <OsmGeo> filtered = new List <OsmGeo>();
foreach (OsmGeo geo in res)
{
if (filter.Evaluate(geo))
{
filtered.Add(geo);
}
}
}
return(res);
}
public int CreateSection(Section section)
{
conn = DAO.getConnection();
NpgsqlCommand command = new NpgsqlCommand(DAOSectionResource.CreateSectionSP, conn);
NpgsqlTransaction transaction = conn.BeginTransaction();
NpgsqlParameter name = new NpgsqlParameter();
NpgsqlParameter amount = new NpgsqlParameter();
NpgsqlParameter description = new NpgsqlParameter();
NpgsqlParameter fkcourse = new NpgsqlParameter();
name.ParameterName = DAOSectionResource.Name;
amount.ParameterName = DAOSectionResource.Amount;
description.ParameterName = DAOSectionResource.Description;
fkcourse.ParameterName = DAOSectionResource.CourseID;
name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
amount.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Double;
description.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
fkcourse.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
name.Direction = ParameterDirection.Input;
amount.Direction = ParameterDirection.Input;
description.Direction = ParameterDirection.Input;
fkcourse.Direction = ParameterDirection.Input;
name.Value = section.Name;
amount.Value = section.Amount;
description.Value = section.Description;
fkcourse.Value = section.Course.Id;
command.Parameters.Add(name);
command.Parameters.Add(amount);
command.Parameters.Add(description);
command.Parameters.Add(fkcourse);
command.CommandType = CommandType.StoredProcedure;
int response = 500;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
response = dr.GetInt32(0);
}
dr.Close();
transaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(response);
}
public IActionResult GetDevMigTwoFactorToken([FromForm] string guid, [FromForm] string sharedid, [FromForm] string twoFactorToken)
{
string errorMessage = "";
bool isError = false;
//string oguid = guid;
// guid = hashGuid(guid);
//validate regToken
bool isTokenValid = false;
string tok = "";
NpgsqlConnection conn = new NpgsqlConnection(_connstrWallet);
conn.Open();
try
{
//first validate the token provided
//to autorise the temporary device token
string sql = "sp_getemailtoken";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("p_walletid", guid));
cmd.Parameters.Add(new NpgsqlParameter("p_emailvalidationtoken", twoFactorToken));
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
DateTime expiry = reader.GetDateTime(0);
int tokenType = reader.GetInt32(1);
if (expiry > DateTime.Now)
{
isTokenValid = true;
}
}
reader.Dispose();
if (isTokenValid)
{
byte[] devkey = new byte[32];
_csprng.getRandomValues(devkey);
tok = HexString.FromByteArray(devkey);
tok = Helper.hashGuid(tok);
cmd = new NpgsqlCommand("sp_createemailtoken", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("p_emailvalidationtoken", tok));
cmd.Parameters.Add(new NpgsqlParameter("p_walletid", guid));
cmd.Parameters.Add(new NpgsqlParameter("p_emailaddress", ""));
cmd.Parameters.Add(new NpgsqlParameter("p_expirydate", DateTime.Now.AddMinutes(10)));
int tmpisu = 0;
cmd.Parameters.Add(new NpgsqlParameter("p_isused", tmpisu));
cmd.Parameters.Add(new NpgsqlParameter("p_tokentype", 6));
cmd.ExecuteNonQuery();
}
else
{
isError = true;
errorMessage = "ErrInvalid";
}
}
catch (Exception ex)
{
isError = true;
errorMessage = "ErrInvalid";
}
finally
{
conn.Close();
}
if (isError)
{
return(Json(new ErrorObject(errorMessage)));
}
else
{
dynamic ret = new System.Dynamic.ExpandoObject();
ret.message = tok;
return(Json(ret));
}
}
public static void Load()
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection(SQLManager.ConnectionString))
using (NpgsqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "SELECT * FROM shop";
using (NpgsqlDataReader data = command.ExecuteReader())
{
while (data.Read())
{
GoodItem good = new GoodItem
{
id = data.GetInt32(0),
price_gold = data.GetInt32(3),
price_cash = data.GetInt32(4),
auth_type = data.GetInt32(6), //1 = unidade 2 = dias
buy_type2 = data.GetInt32(7),
buy_type3 = data.GetInt32(8),
tag = data.GetInt32(9),
title = data.GetInt32(10),//0= Sem titulo Id do Slot=requer titulo
visibility = data.GetInt32(11)
};
good.item.SetItemId(data.GetInt32(1));
good.item.name = data.GetString(2);
good.item.count = data.GetInt32(5);
ShopAllList.Add(good);
if (good.visibility != 2 && good.visibility != 4)
{
ShopBuyableList.Add(good);
}
if (!ShopUniqueList.ContainsKey(good.item.id) && good.auth_type > 0)
{
ShopUniqueList.TryAdd(good.item.id, good);
if (good.visibility == 4)
{
set4p++;
}
}
}
LoadDataMatching1Goods(); //Pccafe 0
LoadDataMatching2(); //Pccafe basic/premium
LoadDataItems();
data.Close();
connection.Close();
}
}
if (set4p > 0)
{
Logger.Informations($" [ShopManager] Loaded {set4p} itens invisíveis com ícones liberados.");
}
}
catch (Exception ex)
{
Logger.Exception(ex);
}
//XIEMIELE();
//CreateJsonShop();
}
public List <Patient> consultAllPatients()
{
int id;
String name;
String surname;
int age;
String career;
String state;
String municipality;
String parish;
Patient _patient;
List <Patient> patientList = new List <Patient>();
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("paciente_consultar_todos()", conn);
command.CommandType = CommandType.StoredProcedure;
//command.ExecuteNonQuery();
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
id = dr.GetInt32(0);
name = dr.GetString(1);
surname = dr.GetString(2);
age = dr.GetInt32(3);
career = dr.GetString(4);
state = dr.GetString(5);
municipality = dr.GetString(6);
parish = dr.GetString(7);
if (career == "Administración y Contaduría")
{
career = "Admin. y Contaduría";
}
else if (career == "Ingeniería en Telecomunicaciones")
{
career = "Ingeniería en Telecom.";
}
_patient = new Patient(id, name, surname, age, career, state, municipality, parish);
patientList.Add(_patient);
}
dr.Close();
tran.Commit();
return(patientList);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
}
NpgsqlDbColumn LoadColumnDefinition(NpgsqlDataReader reader)
{
var columnName = reader.GetString(reader.GetOrdinal("attname"));
var column = new NpgsqlDbColumn
{
AllowDBNull = !reader.GetBoolean(reader.GetOrdinal("attnotnull")),
BaseCatalogName = _connection.Database,
BaseColumnName = columnName,
BaseSchemaName = reader.GetString(reader.GetOrdinal("nspname")),
BaseServerName = _connection.Host,
BaseTableName = reader.GetString(reader.GetOrdinal("relname")),
ColumnName = columnName,
ColumnOrdinal = reader.GetInt32(reader.GetOrdinal("attnum")) - 1,
ColumnAttributeNumber = (short)(reader.GetInt16(reader.GetOrdinal("attnum")) - 1),
IsKey = reader.GetBoolean(reader.GetOrdinal("isprimarykey")),
IsReadOnly = !reader.GetBoolean(reader.GetOrdinal("is_updatable")),
IsUnique = reader.GetBoolean(reader.GetOrdinal("isunique")),
DataTypeName = reader.GetString(reader.GetOrdinal("typname")),
TableOID = reader.GetFieldValue<uint>(reader.GetOrdinal("attrelid")),
TypeOID = reader.GetFieldValue<uint>(reader.GetOrdinal("typoid"))
};
var defaultValueOrdinal = reader.GetOrdinal("default");
column.DefaultValue = reader.IsDBNull(defaultValueOrdinal) ? null : reader.GetString(defaultValueOrdinal);
column.IsAutoIncrement = column.DefaultValue != null && column.DefaultValue.StartsWith("nextval(");
ColumnPostConfig(column, reader.GetInt32(reader.GetOrdinal("atttypmod")));
return column;
}
