public AG_CUSTO_FIXO Get(int Id) //get by ID_CUSTO_FIXO
{
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();
AG_CUSTO_FIXO cFixoList;
{
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM AG_CUSTO_FIXO WHERE ID_CUSTO_FIXO =" + Id, conn);
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
DateTime _data = dr.GetDateTime(6);
string data = _data.ToString("dd/MM/yyyy");
//int id, int id_cv, string descricao, float valor, string um, float quantidade, string data
cFixoList = new AG_CUSTO_FIXO(dr.GetInt32(0),
dr.GetInt32(1),
dr.GetString(2),
dr.GetFloat(3),
dr.GetString(4),
dr.GetFloat(5),
data
);
}
conn.Close();
return(cFixoList);
}
}
private void AddCurrentUserInformation(NpgsqlDataReader R)
{
userNameTextBlock.Text = R.GetString(0);
userStarsTextBlock.Text = R.GetFloat(1).ToString();
userYelpingSinceTextBlock.Text = R.GetDate(2).ToString();
userLatTextBox.Text = R.GetFloat(3).ToString();
userLongTextBox.Text = R.GetFloat(4).ToString();
selectedUserLat = R.GetFloat(3).ToString();
selectedUserLong = R.GetFloat(4).ToString();
userFansTextBlock.Text = R.GetInt32(5).ToString();
}
private Condominio PreencherObjeto(NpgsqlDataReader dataReader)
{
int i = 0;
Condominio obj = new Condominio();
obj.Id = dataReader.GetInt32(i++);
obj.Id_endereco = dataReader.GetInt32(i++);
obj.Qtd_Apt = dataReader.GetInt32(i++);
obj.Valor_agua = dataReader.GetFloat(i++);
obj.Valor_luz = dataReader.GetFloat(i++);
obj.Valor_gas = dataReader.GetFloat(i++);
obj.Nome = dataReader.GetString(i++);
return(obj);
}
private void addCurrentUserFriends(NpgsqlDataReader R)
{
userFriendsDataGrid.Items.Add(new User()
{
name = R.GetString(0), avgStars = R.GetFloat(1), yelpingSince = R.GetDateTime(2)
});
}
public void InsertSingleValueNpgsqlDbType()
{
_conn.Open();
NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
command.Parameters.Add(new NpgsqlParameter(":a", NpgsqlDbType.Real));
command.Parameters[0].Value = 7.4F;
Int32 rowsAdded = command.ExecuteNonQuery();
Assert.AreEqual(1, rowsAdded);
command.CommandText = "select * from tabled where field_float4 = :a";
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
Single result = dr.GetFloat(1);
command.CommandText = "delete from tabled where field_serial > 2;";
command.Parameters.Clear();
command.ExecuteNonQuery();
Assert.AreEqual(7.4F, result);
}
public MedicoDTO ObtenerMedico(string cedula)
{
MedicoDTO medico = new MedicoDTO();
conn.Open();
comm.Connection = conn;
comm.CommandText = "select * from medico where cedula = '" + cedula + "';";
comm.ExecuteNonQuery();
LeerFilas = comm.ExecuteReader();
if (LeerFilas.HasRows)
{
while (LeerFilas.Read())
{
medico.Nombre = LeerFilas.GetString(0);
medico.Apellido = LeerFilas.GetString(1);
medico.Cedula = LeerFilas.GetString(2);
medico.Especializacion = LeerFilas.GetString(3);
medico.Horario = LeerFilas.GetString(4);
medico.Salario = LeerFilas.GetFloat(5);
medico.Sexo = LeerFilas.GetChar(6);
medico.Password = LeerFilas.GetString(7);
}
}
LeerFilas.Close();
conn.Close();
return(medico);
}
private void AddCurrentUserFavoriteBusinesses(NpgsqlDataReader R)
{
userFavoriteBusinessesDataGrid.Items.Add(new Business()
{
businessName = R.GetString(0), stars = R.GetFloat(1), city = R.GetString(2), zip = R.GetInt32(3), address = R.GetString(4), businessID = R.GetString(5)
});
}
//Metodos
public List <MedicoDTO> VerRegistros(string condicion)
{
conn.Open();
comm.Connection = conn;
comm.CommandText = "select * from " + condicion + ";";
comm.ExecuteNonQuery();
LeerFilas = comm.ExecuteReader();
List <MedicoDTO> listaGenerica = new List <MedicoDTO>();
while (LeerFilas.Read())
{
listaGenerica.Add(new MedicoDTO {
Nombre = LeerFilas.GetString(0),
Apellido = LeerFilas.GetString(1),
Cedula = LeerFilas.GetString(2),
Especializacion = LeerFilas.GetString(3),
Horario = LeerFilas.GetString(4),
Salario = LeerFilas.GetFloat(5),
Sexo = LeerFilas.GetChar(6),
Password = LeerFilas.GetString(7)
});
}
LeerFilas.Close();
conn.Close();
return(listaGenerica);
}
private void addReviewGridRow(NpgsqlDataReader R)
{
reviewDataGrid.Items.Add(new Review()
{
userName = R.GetString(0), stars = R.GetFloat(1), content = R.GetString(2)
});
}
private void addFriends(NpgsqlDataReader R)
{
dataGrid.Items.Add(new Review()
{
date = R.GetValue(0).ToString(), name = R.GetString(1), stars = R.GetFloat(2), text = R.GetString(3)
});
}
/// <summary>
/// 检索题库中相似题目
/// </summary>
/// <param name="keyword"></param>
/// <returns></returns>
private List <Dictionary <String, String> > queryLib(String keyword)
{
showMessage("正在从本地题库检索相似题目");
List <Dictionary <String, String> > results = new List <Dictionary <String, String> >();
try
{
connection.Open();
NpgsqlCommand command = connection.CreateCommand();
command.CommandText = String.Format("select question, similarity(question,'{0}') as sml,\"enrollTime\" from qiangguo where question % '{1}' order by sml DESC;", keyword, keyword);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <String, String> item = new Dictionary <String, String>();
item.Add("question", reader.GetString(0));
item.Add("similarity", reader.GetFloat(1).ToString());
item.Add("enrollTime", reader.GetTimeStamp(2).ToDateTime().ToString());
results.Add(item);
}
}
catch (PostgresException ex)
{
Console.WriteLine(ex.MessageText);
}
finally
{
connection.Close();
}
return(results);
}
public static PlayerC GetUser(AuthenticationData authData, NpgsqlConnection conn)
{
using (var command = new NpgsqlCommand("SELECT * FROM PLAYER WHERE login = @l AND password = @p", conn))
{
command.Parameters.AddWithValue("l", authData.Login);
command.Parameters.AddWithValue("p", authData.Password);
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
long id = reader.GetInt32(0);
string name = reader.GetString(1);
int level = reader.GetInt32(2);
float exp = reader.GetFloat(3);
int hp = reader.GetInt32(6);
int mp = reader.GetInt32(7);
int stamina = reader.GetInt32(8);
int meleeDamage = reader.GetInt32(9);
int rangeDamage = reader.GetInt32(10);
int mageDamage = reader.GetInt32(11);
return(new PlayerC(
id,
name,
level,
exp,
new PlayerCStats(hp, mp, stamina, meleeDamage, rangeDamage, mageDamage),
null, // later
authData
));
}
return(null);
}
}
public void generar_cuotas(string codigo_transaccion, int tiempo) //0jo hay que obtener el
//codigo de la transaccion del objeto transaccion con el metodo materializar TRANSACCION
{
string consulta = "select fechainicioprestamo,fechaterminoprestamo,tasaprestamo,montoprestamo" +
" from transacciones_prestamo where codprestamo like'" + codigo_transaccion + "' ";
NpgsqlDataReader resultados = metodos.resultados_consulta(consulta);
resultados.Read();
DateTime fechainicio = resultados.GetDateTime(0);
DateTime fechatermino = resultados.GetDateTime(1);
int totaldias = ((fechatermino - fechainicio).Days);
int num_cuotas = totaldias / tiempo;
string codigo_crono = Convert.ToString(this.getcodigo());
DateTime fecha_cuota = fechainicio;
string fecha_planeada;
long codigo_cuota;
string estado;
int montototal = Convert.ToInt32(resultados.GetString(3));
float tasa = (resultados.GetFloat(2));
double tasa1 = (resultados.GetDouble(2) / 100);
//double montofinal = montototal * (1 +Math.Pow() (tasa1 / 100)) ^ num_cuotas;
double anualidad = montototal * ((tasa1) / (1 - Math.Pow((1 + tasa1), (-1) * num_cuotas)));
//MessageBox.Show("presesnte="+montototal);
//string comprobante; //el comprobante es un codigo o cheque
//string modalidadpago;
//string fecha_efectiva=null;
for (int i = 1; i <= num_cuotas; i++)
{
///--aqui se calculala fecha de la transaccion
fecha_cuota = fecha_cuota.AddDays(tiempo);
fecha_planeada = fecha_cuota.ToString("dd/MM/yyyy");
codigo_cuota = metodos.generar_codigocuota(10, "cuotas_prestamo", "codcuotaprestamo");
estado = "PENDIENTE";
//string monto = Convert.ToString(montofinal / num_cuotas); //Aqui hay que calcular el precio de la cuota
string monto = Convert.ToString(Math.Round(anualidad, 2));
string consulta2 = "insert into cuotas_prestamo " + // modalidd,comprobante fecha efectiva
" values('" + codigo_cuota + "','" + codigo_crono + "','" + codigo_transaccion + "','" + monto + "','" + tasa + "'," + "null,null,'" + estado + "','" + fecha_planeada + "',null)";
metodos.ejecutar_consulta(consulta2);
}
}
public static void Load()
{
try
{
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
NpgsqlCommand command = npgsqlConnection.CreateCommand();
npgsqlConnection.Open();
command.CommandText = "SELECT * FROM clan_data";
command.CommandType = CommandType.Text;
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
while (npgsqlDataReader.Read())
{
long int64 = npgsqlDataReader.GetInt64(3);
if (int64 != 0L)
{
Clan clan = new Clan()
{
_id = npgsqlDataReader.GetInt32(0),
_rank = npgsqlDataReader.GetInt32(1),
_name = npgsqlDataReader.GetString(2),
owner_id = int64,
_logo = (uint)npgsqlDataReader.GetInt64(4),
_name_color = npgsqlDataReader.GetInt32(5),
_info = npgsqlDataReader.GetString(6),
_news = npgsqlDataReader.GetString(7),
creationDate = npgsqlDataReader.GetInt32(8),
autoridade = npgsqlDataReader.GetInt32(9),
limite_rank = npgsqlDataReader.GetInt32(10),
limite_idade = npgsqlDataReader.GetInt32(11),
limite_idade2 = npgsqlDataReader.GetInt32(12),
partidas = npgsqlDataReader.GetInt32(13),
vitorias = npgsqlDataReader.GetInt32(14),
derrotas = npgsqlDataReader.GetInt32(15),
_pontos = npgsqlDataReader.GetFloat(16),
maxPlayers = npgsqlDataReader.GetInt32(17),
_exp = npgsqlDataReader.GetInt32(18)
};
string Exp = npgsqlDataReader.GetString(19);
string Part = npgsqlDataReader.GetString(20);
string Wins = npgsqlDataReader.GetString(21);
string Kills = npgsqlDataReader.GetString(22);
string Hs = npgsqlDataReader.GetString(23);
clan.BestPlayers.SetPlayers(Exp, Part, Wins, Kills, Hs);
ClanManager._clans.Add(clan);
}
}
command.Dispose();
npgsqlDataReader.Close();
npgsqlConnection.Close();
}
}
catch (Exception ex)
{
Logger.error(ex.ToString());
}
}
public static List <Clan> GetClanListPerPage(int page)
{
List <Clan> clans = new List <Clan>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection(SQLManager.ConnectionString))
using (NpgsqlCommand command = connection.CreateCommand())
{
connection.Open();
command.Parameters.AddWithValue("@page", 170 * page);
command.CommandText = "SELECT * FROM clan_data ORDER BY clan_id DESC OFFSET @page LIMIT 170";
using (NpgsqlDataReader data = command.ExecuteReader())
{
while (data.Read())
{
long owner = data.GetInt64(3);
if (owner == 0)
{
continue;
}
Clan clan = new Clan
{
id = data.GetInt32(0),
rank = (byte)data.GetInt32(1),
name = data.GetString(2),
ownerId = owner,
logo = (uint)data.GetInt64(4),
nameColor = (byte)data.GetInt32(5),
informations = data.GetString(6),
notice = data.GetString(7),
creationDate = data.GetInt32(8),
authorityConfig = (ClanAuthorityConfigEnum)data.GetInt32(9),
limitRankId = data.GetInt32(10),
limitAgeBigger = data.GetInt32(11),
limitAgeSmaller = data.GetInt32(12),
partidas = data.GetInt32(13),
vitorias = data.GetInt32(14),
derrotas = data.GetInt32(15),
pontos = data.GetFloat(16),
maxPlayers = data.GetInt32(17),
exp = data.GetInt32(18)
};
clan.BestPlayers.SetPlayers(data.GetString(19), data.GetString(20), data.GetString(21), data.GetString(22), data.GetString(23));
clans.Add(clan);
}
data.Close();
connection.Close();
}
}
}
catch (Exception ex)
{
Logger.Exception(ex);
}
return(clans);
}
public static PlayerModel GetPlayerID(int ID)
{
PlayerModel playerModel = new PlayerModel();
playerModel.Id = 0;
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
try
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT name, cash, admin, age, status, place, sex, health, armor, posx, posy, posz, rotation FROM server.players WHERE id = @ID LIMIT 1;", conn);
command.Parameters.AddWithValue("@ID", ID);
command.ExecuteNonQuery();
using (NpgsqlDataReader reader = command.ExecuteReader())
{
reader.Read();
float posX = reader.GetFloat(9);
float posY = reader.GetFloat(10);
float posZ = reader.GetFloat(11);
float rot = reader.GetFloat(12);
playerModel.Id = ID;
playerModel.Name = reader.GetString(0);
playerModel.Cash = reader.GetDecimal(1);
playerModel.Admin = reader.GetInt16(2);
playerModel.Age = reader.GetInt16(3);
playerModel.Status = reader.GetBoolean(4);
playerModel.Place = reader.GetInt16(5);
playerModel.Sex = reader.GetBoolean(6);
playerModel.Health = reader.GetInt16(7);
playerModel.Armor = reader.GetInt16(8);
playerModel.Position = new Vector3(posX, posY, posZ);
playerModel.Rotation = new Vector3(0.0, 0.0, rot);
return(playerModel);
}
}
catch (Exception ex)
{
Globals.log.Trace(ex);
NAPI.Util.ConsoleOutput("[EXCEPTION CreateCharacter] " + ex.Message);
NAPI.Util.ConsoleOutput("[EXCEPTION CreateCharacter] " + ex.StackTrace);
}
return(playerModel);
}
}
/// <summary>
/// Returns the column value as a nullable <see cref="float"/>.
/// </summary>
/// <param name="reader">The data reader.</param>
/// <param name="ordinal">The zero-based column position.</param>
/// <returns>The nullable column value.</returns>
public static float?GetNullableFloat(this NpgsqlDataReader reader, int ordinal)
{
if (reader[ordinal] == DBNull.Value)
{
return(null);
}
else
{
return(reader.GetFloat(ordinal));
}
}
/// <summary>
/// Usa el <c>Reader</c> para hacer una lectura
/// </summary>
/// <param name="posicion">Poscicion en la tabla, inicio en 0</param>
/// <returns>Dato de tipo <c>float</c></returns>
public float ReadFloat(int posicion)
{
try
{
return(Reader.GetFloat(posicion));
}
catch (Exception e)
{
return(0);
}
}
public static void Load()
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection(SQLManager.ConnectionString))
using (NpgsqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "SELECT * FROM clan_data";
using (NpgsqlDataReader data = command.ExecuteReader())
{
while (data.Read())
{
long owner = data.GetInt64(3);
if (owner == 0)
{
continue;
}
Clan clan = new Clan
{
id = data.GetInt32(0),
rank = (byte)data.GetInt32(1),
name = data.GetString(2),
ownerId = owner,
logo = (uint)data.GetInt64(4),
nameColor = (byte)data.GetInt32(5),
informations = data.GetString(6),
notice = data.GetString(7),
creationDate = data.GetInt32(8),
authorityConfig = (ClanAuthorityConfigEnum)data.GetInt32(9),
limitRankId = data.GetInt32(10),
limitAgeBigger = data.GetInt32(11),
limitAgeSmaller = data.GetInt32(12),
partidas = data.GetInt32(13),
vitorias = data.GetInt32(14),
derrotas = data.GetInt32(15),
pontos = data.GetFloat(16),
maxPlayers = data.GetInt32(17),
exp = data.GetInt32(18)
};
clan.BestPlayers.SetPlayers(data.GetString(19), data.GetString(20), data.GetString(21), data.GetString(22), data.GetString(23));
clans.Add(clan);
}
data.Close();
connection.Close();
}
}
Logger.Informations($" [ClanManager] Loaded {clans.Count} clans.");
}
catch (Exception ex)
{
Logger.Exception(ex);
}
}
public void GetFloat()
{
NpgsqlCommand command = new NpgsqlCommand("select * from tabled where field_serial = 1;", _conn);
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
//Single result = Single.Parse(dr.GetInt32(2).ToString());
Single result = dr.GetFloat(1);
Assert.AreEqual(.123456F, result);
}
public Photos GetPhoto(int id)
{
String command = "SELECT * FROM photos WHERE id=" + id;
Photos photo = new Photos();
NpgsqlCommand cmd = new NpgsqlCommand(command, conn);
NpgsqlDataReader result = cmd.ExecuteReader();
while (result.Read())
{
photo.Id = result.GetInt32(0);
photo.UserId = result.GetInt32(1);
photo.Name = result.GetString(2);
photo.Width = result.GetFloat(3);
photo.Height = result.GetFloat(4);
photo.Size = result.GetFloat(5);
photo.Date = result.GetDateTime(6);
}
conn.Close();
return(photo);
}
public void GetFloat()
{
_conn.Open();
NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 2;", _conn);
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
Single result = dr.GetFloat(2);
Console.WriteLine(result);
_conn.Close();
}
public List <Photos> GetPhotos()
{
String command = "SELECT * FROM photos";
List <Photos> photos = new List <Photos>();
NpgsqlCommand cmd = new NpgsqlCommand(command, conn);
NpgsqlDataReader result = cmd.ExecuteReader();
while (result.Read())
{
Photos photo = new Photos();
photo.Id = result.GetInt32(0);
photo.UserId = result.GetInt32(1);
photo.Name = result.GetString(2);
photo.Width = result.GetFloat(3);
photo.Height = result.GetFloat(4);
photo.Size = result.GetFloat(5);
photo.Date = result.GetDateTime(6);
photos.Add(photo);
}
conn.Close();
return(photos);
}
//try multiple format to read into the cell
private void readerJudge(NpgsqlDataReader rd, int row, int column)
{
try
{
this.dataGridView1.Rows[row].Cells[column].Value = rd.GetString(column);
}
catch (InvalidCastException)
{
try
{
this.dataGridView1.Rows[row].Cells[column].Value = rd.GetFloat(column);
}
catch (InvalidCastException)
{
}
}
}
public List <ServiceData> GetServiceData()
{
var Command = new NpgsqlCommand(Views.GetServicesList, Connection);
NpgsqlDataReader Reader = Command.ExecuteReader();
List <ServiceData> services = new List <ServiceData>();
while (Reader.Read())
{
services.Add(new ServiceData()
{
Id = Reader.GetInt32(0),
Name = Reader.GetString(1),
Description = Reader.GetString(2),
Price = Reader.GetFloat(3)
});
}
return(services);
}
internal async Task <ValidationResponse> validateClientVersion(string clientVersion)
{
float currentClientVersion;
bool num = float.TryParse(clientVersion, out currentClientVersion);
try
{
var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM \"OStoreVersions\" LIMIT 1;";
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
latestVersion = reader.GetFloat(0);
mandatoryUpdate = reader.GetBoolean(2);
}
}
catch (Exception msg)
{
Console.WriteLine(msg);
}
if (currentClientVersion == latestVersion)
{
return(new ValidationResponse
{
error_code = 0,
CurrentStableVersion = default(string)
});
}
else
{
return(new ValidationResponse
{
error_code = 1,
CurrentStableVersion = latestVersion.ToString(),
MandatoryUpdate = mandatoryUpdate
});
}
}
public List <TableDetails> getTableDetailsByTableId(int tableId)
{
List <TableDetails> totalListByTableId = new List <TableDetails>();
string totalQuery = "SELECT * from main.get_table_details_by_table_id(:t_id)";
dataBase = new DataBase();
var npgCommand = dataBase.getNpgCommandForRequest(totalQuery);
npgCommand.Parameters.AddWithValue("t_id", tableId);
NpgsqlDataReader reader = npgCommand.ExecuteReader();
while (reader.Read())
{
TableDetails total = new TableDetails();
total.Id = reader.IsDBNull(reader.GetOrdinal("td_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("td_id"));
total.Table.Id = reader.IsDBNull(reader.GetOrdinal("table_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("table_id"));
total.FoodStuff.Id = reader.IsDBNull(reader.GetOrdinal("food_stuff_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("food_stuff_id"));
total.FoodQty = reader.IsDBNull(reader.GetOrdinal("food_qty")) ? 0 : reader.GetInt32(reader.GetOrdinal("food_qty"));
total.Sum = reader.IsDBNull(reader.GetOrdinal("summary")) ? 0 : reader.GetFloat(reader.GetOrdinal("summary"));
total.Table.Name = reader.IsDBNull(reader.GetOrdinal("table_name")) ? string.Empty : reader.GetString(reader.GetOrdinal("table_name"));
total.FoodStuff.Price = reader.IsDBNull(reader.GetOrdinal("price")) ? 0 : reader.GetFloat(reader.GetOrdinal("price"));
total.FoodStuff.Name = reader.IsDBNull(reader.GetOrdinal("food_stuff_name")) ? string.Empty : reader.GetString(reader.GetOrdinal("food_stuff_name"));
totalListByTableId.Add(total);
}
dataBase.CloseAndDispose();
return(totalListByTableId);
}
/// <summary>
/// Get the value of a column as a float, given its zero-based ordinal.
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public float GetFloat(int i)
{
return(_rdr.GetFloat(i));
}
// 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 <CameraData> getCameraData(int factory_no, int furnace_no, int limit = 20)
{
string sql = "select a_i,b_i,c_i,i_default," +
"a_v,b_v,c_v,e_speed,r_speed,a_action,b_action,c_action,status,feed,exhaust,d_time from camera_data " +
"where factory_no=@fac_no and furnace_no=@fur_no order by d_time desc limit @limit_num";
List <CameraData> data = new List <CameraData>();
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn)) {
cmd.Parameters.AddWithValue("@fac_no", NpgsqlDbType.Integer, factory_no);
cmd.Parameters.AddWithValue("@fur_no", NpgsqlDbType.Integer, furnace_no);
cmd.Parameters.AddWithValue("@limit_num", NpgsqlDbType.Integer, limit);
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CameraData cd = new CameraData();
cd.a_i = reader.GetFloat(0);
cd.b_i = reader.GetFloat(1);
cd.c_i = reader.GetFloat(2);
cd.i_default = reader.GetFloat(3);
cd.a_v = reader.GetFloat(4);
cd.b_v = reader.GetFloat(5);
cd.c_v = reader.GetFloat(6);
cd.e_speed = reader.GetFloat(7);
cd.r_speed = reader.GetFloat(8);
cd.a_action = reader.GetFloat(9);
cd.b_action = reader.GetFloat(10);
cd.c_action = reader.GetFloat(11);
cd.status = reader.GetBoolean(12);
cd.feed = reader.GetBoolean(13);
cd.exhaust = reader.GetBoolean(14);
cd.d_time = timestampToDateTime(reader.GetTimeStamp(15));
cd.factory_no = factory_no;
cd.furnace_no = furnace_no;
data.Add(cd);
}
}
reader.Close();
}
return(data);
}
