//Metodo que llena el combo de identificadores de vehiculos , en la ventana de seccion de vuelo
public void LlenarVehiculos(ComboBox placa)
{
try
{
Conexion();
conexion.Open();
List <String> lista = new List <String>();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT placa FROM vehiculos", conexion);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
placa.Items.Add(dr.GetInt64(0));
}
}
conexion.Close();
}
catch (Exception error)
{
Console.WriteLine(error);
}
}
private Int64 GetMaxChangeLogId(Npgsql.NpgsqlConnection conn, int datasetid)
{
Logger.Info("GetMaxChangeLogId START");
try
{
Int64 endChangeId = 0;
string sqlSelectMaxChangeLogId = "SELECT COALESCE(MAX(endringsid),0) FROM " + PDbSchema + ".endringslogg";
NpgsqlCommand command = new NpgsqlCommand(sqlSelectMaxChangeLogId, conn);
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read(); //Only one row
endChangeId = dr.GetInt64(0);
dr.Close();
Logger.Info("GetMaxChangeLogId END");
return(endChangeId);
}
catch (System.Exception exp)
{
Logger.Error(exp, "GetMaxChangeLogId function failed:");
throw new System.Exception("GetMaxChangeLogId function failed", exp);
}
}
public int getCantidad(int id_expediente)
{
int cant = 0;
CnxBase myBase = new CnxBase();
string reqSQL = "SELECT count(id_carro) from e_carros_usados where id_expediente=" + id_expediente;
try
{
NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString);
NpgsqlCommand myCommand = new NpgsqlCommand(reqSQL, myConn);
NpgsqlDataReader myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
cant = (int)myReader.GetInt64(0);
}
myBase.CloseConnection(myConn);
}
catch (Exception myErr)
{
throw (new Exception(myErr.ToString() + reqSQL));
}
return(cant);
}
/// <summary>
/// Получить материал из таблицы БД
/// </summary>
/// <returns>Экземпляр класса Material</returns>
public List <Material> GetMaterials(string material)
{
List <Material> Result = new List <Material>();
if (Connection != null)
{
string query = $"SELECT * FROM {DBShema}._materials WHERE name='{material}' ORDER BY id ASC;";
var data = getData(query);
SQLCommand = new NpgsqlCommand(query, Connection);
Connection.Open();
SQLData = SQLCommand.ExecuteReader();
while (SQLData.Read())
{
Material layer = new Material();
long id = SQLData.GetInt64(0);
string name = SQLData.GetString(1);
int partno = SQLData.GetInt32(2);
double weight = SQLData.GetDouble(3);
double volume = SQLData.GetDouble(4);
layer.setMaterial(id, name, partno, weight, volume);
Result.Add(layer);
}
Connection.Close();
}
else
{
Result = null;
}
return(Result);
}
public static void Load()
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection(SQLManager.ConnectionString))
using (NpgsqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "SELECT * FROM server_block";
using (NpgsqlDataReader data = command.ExecuteReader())
{
while (data.Read())
{
UserBlock block = new UserBlock
{
blockId = data.GetInt64(0),
ipAddress = data.GetString(1),
macAddress = data.GetString(2),
hardwareId = data.GetString(3),
biosId = data.GetString(4),
diskId = data.GetString(5),
videoId = data.GetString(6),
startDate = data.GetDateTime(7),
endDate = data.GetDateTime(8)
};
UsersBlock.TryAdd(block.ipAddress, block);
}
data.Close();
connection.Close();
}
}
}
catch (Exception ex)
{
Logger.Exception(ex);
}
}
/*public static List<Vehicle> GetAvailableVehicles(NpgsqlConnection connection)
* {
* List<Vehicle> allVehicles = new List<Vehicle>();
*
* NpgsqlCommand command = new NpgsqlCommand();
* command.Connection = connection;
* command.CommandText = $"Select * from {TABLE} where in_use = false and (reserved < (current_timestamp + interval '1h') or reserved is null);";
* NpgsqlDataReader reader = command.ExecuteReader();
*
* while (reader.Read())
* {
* allVehicles.Add(new Vehicle(connection)
* {
* CarId = reader.GetInt64(0),
* LocationId = reader.GetInt64(1),
* Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
* Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
* HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
* Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
* Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
* Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
* Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
* Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
* NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
* Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
* InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
* Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
* });
* }
* reader.Close();
* return allVehicles;
* }*/
public static List <Vehicle> GetAvailableVehicles(NpgsqlConnection connection, DateTime time)
{
List <Vehicle> allVehicles = new List <Vehicle>();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = $"Select car_id, location_id, modell, brand, hp, price, feature1, feature2, feature3, feature4, notavailable, reserved, in_use, locked from instacar.car where deleted = false " +
$"except " +
$"Select c.car_id, c.location_id, c.modell, c.brand, c.hp, c.price, c.feature1, c.feature2, c.feature3, c.feature4, " +
$"c.notavailable, c.reserved, c.in_use, c.locked from {TABLE} as c inner join {TABLERENT} as r on r.car_id = c.car_id " +
$"where ((dateend > :t and datebegin < :t) or dateend is null) and c.notavailable = false and c.deleted = false;";
command.Parameters.AddWithValue("t", time);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
allVehicles.Add(new Vehicle(connection)
{
CarId = reader.GetInt64(0),
LocationId = reader.GetInt64(1),
Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
});
}
reader.Close();
return(allVehicles);
}
private void OutputPresentationSortedBy(NpgsqlCommand command, NpgsqlConnection connection)
{
List <ManagmentSortModels> Data;
NpgsqlDataReader dataReader = command.ExecuteReader();
Data = new List <ManagmentSortModels>();
for (int i = 0; dataReader.Read(); i++)
{
Data.Add(new ManagmentSortModels
{
Id = dataReader.GetInt64(0).ToString(),
Name = dataReader.GetString(1),
Type = dataReader.GetString(2),
AddedTime = dataReader.GetDateTime(3).ToString(),
Image = dataReader.GetString(4),
Information = dataReader.GetString(5),
Cost = dataReader.GetInt32(6).ToString()
});
}
connection.Close();
ViewBag.Data = Data;
}
public void MostrarInformacionRutas(ComboBox id_rutas, TextBox pais_origen, TextBox pais_destino, TextBox cantidad)
{
try
{
Conexion();
conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT pais_origen , pais_destino , duracion FROM rutas WHERE identificador_ruta = '" + id_rutas.SelectedItem.ToString() + "'", conexion);
NpgsqlDataReader leer = cmd.ExecuteReader();
if (leer.HasRows)
{
while (leer.Read())
{
pais_origen.Text = leer.GetString(0);
pais_destino.Text = leer.GetString(1);
cantidad.Text = leer.GetInt64(2).ToString();
}
conexion.Close();
}
}
catch (Exception error)
{
Console.WriteLine(error);
}
}
public static DataTable GetTable(NpgsqlConnection connection)
{
DataTable allUsers = new DataTable("Accounts");
allUsers.Columns.Add(new DataColumn("Name"));
allUsers.Columns.Add(new DataColumn("Role"));
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = $"Select username, role from {TABLE};";
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
allUsers.Rows.Add(reader.IsDBNull(0) ? null : reader.GetString(0),
reader.IsDBNull(1) ? 3 : reader.GetInt64(1));
}
reader.Close();
return(allUsers);
}
//Hole User mit der MenüID für Notification
public static List <long> GetUserForNotification(long menueid)
{
List <long> result = new List <long>();
try
{
string query = "select user_id from wifi_mensa.order " +
"where consumed=false and canceled=false and menue_id= :menueid";
if (DBConnection.Connection.State.ToString() == "Closed")
{
DBConnection.Connection.Open();
}
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = DBConnection.Connection;
command.Parameters.AddWithValue("menueid", menueid);
command.CommandText = query;
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
result.Add(reader.GetInt64(0));
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// Reads an event.
/// </summary>
private Event ReadEvent(NpgsqlDataReader reader)
{
return(new Event
{
EventID = reader.GetInt64(0),
Timestamp = reader.GetDateTimeUtc(1),
Hidden = reader.GetBoolean(2),
CnlNum = reader.GetInt32(3),
ObjNum = reader.GetInt32(4),
DeviceNum = reader.GetInt32(5),
PrevCnlVal = reader.GetDouble(6),
PrevCnlStat = reader.GetInt32(7),
CnlVal = reader.GetDouble(8),
CnlStat = reader.GetInt32(9),
Severity = reader.GetInt32(10),
AckRequired = reader.GetBoolean(11),
Ack = reader.GetBoolean(12),
AckTimestamp = reader.GetDateTimeUtc(13),
AckUserID = reader.GetInt32(14),
TextFormat = (EventTextFormat)reader.GetInt32(15),
Text = reader.IsDBNull(16) ? "" : reader.GetString(16),
Data = reader.IsDBNull(17) ? null : (byte[])reader[17]
});
}
public static Vehicle GetSpecificVehicles(NpgsqlConnection connection, int key)
{
Vehicle vehicle = null;
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
connection.Open();
command.CommandText = $"Select * from {TABLE} where car_id = :id;";
command.Parameters.AddWithValue("id", key);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
vehicle = new Vehicle(connection)
{
CarId = reader.GetInt64(0),
LocationId = reader.GetInt64(1),
Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
};
}
reader.Close();
connection.Close();
return(vehicle);
}
//----------------------------------------------------------------------------------------------
//Static
//----------------------------------------------------------------------------------------------
#region static
public static List <Vehicle> GetAllVehicles(NpgsqlConnection connection)
{
List <Vehicle> allVehicles = new List <Vehicle>();
Vehicle vehicle = null;
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = $"Select * from {TABLE} where deleted = false;";
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
allVehicles.Add(
vehicle = new Vehicle(connection)
{
CarId = reader.GetInt64(0),
LocationId = reader.GetInt64(1),
Modell = reader.IsDBNull(2) ? null : reader.GetString(2),
Brand = reader.IsDBNull(3) ? null : reader.GetString(3),
HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4),
Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5),
Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7),
Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8),
Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9),
NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10),
Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11),
InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12),
Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13)
}
);
}
reader.Close();
return(allVehicles);
}
private void button1_Click(object sender, EventArgs e)
{
if (label7.Visible)
{
MessageBox.Show("请填写正确的图书信息", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (String.Equals(textBox1.Text, "") || String.Equals(textBox2.Text, "") || String.Equals(textBox3.Text, "") || String.Equals(textBox4.Text, ""))
{
MessageBox.Show("请填写正确的图书信息", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
foreach (char c in textBox1.Text)
{
if (c == ' ' || c == '*' || c == '-')
{
MessageBox.Show("输入书名无效，请不要使用特殊字符", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
foreach (char c in textBox2.Text)
{
if (c == ' ' || c == '*' || c == '-')
{
MessageBox.Show("输入作者无效，请不要使用特殊字符", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
foreach (char c in textBox3.Text)
{
if (c == ' ' || c == '*' || c == '-')
{
MessageBox.Show("输入出版社无效", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
string user_name = sign_in.sign_name;
string user_pass = sign_in.sign_password;
string query_text_find_max =
"select max(book_id) from book_info ";
//1.先在系统中找到编号最大的书，然后+1
NpgsqlConnection conn = new NpgsqlConnection(sign_in.connstr);
NpgsqlCommand cmd = new NpgsqlCommand(query_text_find_max, conn);
conn.Open();
NpgsqlDataReader reader = cmd.ExecuteReader();
reader.Read();
long id = reader.GetInt64(0);
id++;
reader.Close();
//2.然后插入图书信息表
try
{
string query_text_insert_info =
"insert into book_info values(" + id + ",'" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + richTextBox1.Text + "')";
cmd.CommandText = query_text_insert_info;
cmd.ExecuteNonQuery();
//3.然后插入图书数量表
string query_text_insert_num =
"insert into book_num values(" + id + "," + textBox4.Text + ")";
cmd.CommandText = query_text_insert_num;
cmd.ExecuteNonQuery();
conn.Close();
}catch (Exception ex)
{
MessageBox.Show("您的输入有误，请重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
return;
}
MessageBox.Show("添加成功", "恭喜", MessageBoxButtons.OK);
book_manage_Load(null, null);
}
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 List <StatistiqueLivraisonCarte> getAll()
{
List <StatistiqueLivraisonCarte> listAll = new List <StatistiqueLivraisonCarte>();
conn = new DB().getConn();
try
{
string query = "select * from statistique_livraison_carte";
cmd = new NpgsqlCommand(query, conn);
reader = cmd.ExecuteReader();
while (reader.Read() == true)
{
StatistiqueLivraisonCarte stat = new StatistiqueLivraisonCarte(reader.GetInt16(0), reader.GetString(1), reader.GetInt64(2));
listAll.Add(stat);
}
}
catch (Exception e)
{
throw new Exception("Erreur dans StatistiqueLivraisonCarteDAO->getAll" + e.Message + e.StackTrace);
}
finally
{
conn.Close();
reader.Close();
}
return(listAll);
}
private void FillOptimizedChangeLog(ref Npgsql.NpgsqlCommand command, ref List <OptimizedChangeLogElement> optimizedChangeLog, int startChangeId)
{
Logger.Info("FillOptimizedChangeLog START");
try
{
OrderedDictionary tempOptimizedChangeLog = new OrderedDictionary();
//Fill optimizedChangeLog
using (NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
string gmlId = dr.GetString(0);
// TODO: Fix dirty implementation later - 20121006-Leg: Uppercase First Letter
//gmlId = char.ToUpper(gmlId[0]) + gmlId.Substring(1);
string transType = dr.GetString(1);
long changelogId = dr.GetInt64(2);
OptimizedChangeLogElement optimizedChangeLogElement;
if (transType.Equals("D"))
{
//Remove if inserted or updated earlier in this sequence of transactions
if (tempOptimizedChangeLog.Contains(gmlId))
{
optimizedChangeLogElement = (OptimizedChangeLogElement)tempOptimizedChangeLog[gmlId];
string tempTransType = optimizedChangeLogElement.TransType;
tempOptimizedChangeLog.Remove(gmlId);
if (tempTransType.Equals("U"))
{
//Add delete if last operation was update.
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
else
{
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
else
{
if (!tempOptimizedChangeLog.Contains(gmlId))
{
tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
}
}
}
}
//Fill optimizedChangeLog
foreach (var item in tempOptimizedChangeLog.Values)
{
optimizedChangeLog.Add((OptimizedChangeLogElement)item);
}
}
catch (System.Exception exp)
{
Logger.ErrorException("FillOptimizedChangeLog function failed:", exp);
throw new System.Exception("FillOptimizedChangeLog function failed", exp);
}
Logger.Info("FillOptimizedChangeLog END");
}
/// <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);
}
private async Task action(object cancellationToken)
{
if (!(cancellationToken is CancellationToken))
{
return;
}
CancellationToken ct = (CancellationToken)cancellationToken;
using (ConnectionWrapper connectionWrapper = await connectionPoolManager.GetConnectionAsync(ct))
{
connection = connectionWrapper.Connection;
WriteCommand = connection.CreateCommand();
WriteCommand.CommandType = System.Data.CommandType.Text;
WriteCommand.CommandText = "update messages SET formatting_costyl = null, media_costyl = null, formatting = @_formatting, media = @_media where message_db_id = @_id";
WriteCommand.Parameters.Add(new NpgsqlParameter("_formatting", NpgsqlTypes.NpgsqlDbType.Jsonb));
WriteCommand.Parameters.Add(new NpgsqlParameter("_media", NpgsqlTypes.NpgsqlDbType.Jsonb));
WriteCommand.Parameters.Add(new NpgsqlParameter("_id", NpgsqlTypes.NpgsqlDbType.Bigint));
ReadCommand = connection.CreateCommand();
ReadCommand.CommandType = System.Data.CommandType.Text;
ReadCommand.CommandText = "select message_db_id,media_costyl,formatting_costyl from messages where (media_costyl is not null and media_costyl!='') or formatting_costyl is not null limit 10000";
while (!ct.IsCancellationRequested)
{
try
{
List <Record> records = new List <Record>();
using (NpgsqlDataReader reader = ReadCommand.ExecuteReader())
{
while (reader.Read())
{
Record record = new Record()
{
Id = reader.GetInt64(0),
media_costyl = !reader.IsDBNull(1) ? reader.GetString(1) : null,
formatting_costyl = !reader.IsDBNull(2) ? reader.GetString(2) : null,
};
records.Add(record);
}
}
if (records.Count == 0)
{
return;
}
List <object> list = new List <object>();
using NpgsqlTransaction transaction = connection.BeginTransaction();
try
{
foreach (Record record in records)
{
object form = dataPreparator.PreparateFormatting(record.formatting_costyl);
object med = dataPreparator.PreparateMedia(record.media_costyl);
list.Add(form);
list.Add(med);
WriteCommand.Transaction = transaction;
WriteCommand.Parameters["_formatting"].Value = form;
WriteCommand.Parameters["_media"].Value = med;
WriteCommand.Parameters["_id"].Value = record.Id;
WriteCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
catch (Exception)
{
}
}
}
}
/// <summary>
/// Get the value of a column as an int64, given its zero-based ordinal.
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public long GetInt64(int i)
{
return(_rdr.GetInt64(i));
}
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;
}
}
/// <summary>
/// Получить материал из таблицы БД
/// </summary>
/// <returns>Экземпляр класса Material</returns>
public List <Material> GetMaterials(/*string material*/)
{
List <Material> Result = new List <Material>();
if (Connection != null)
{
// Полчение прихода и расхода материалов
string query = "SELECT " +
"pqca.materials.id as material_id, " + // 0
"pqca.invoices.id as invoices_id, " + // 1
"pqca.materials.material as material, " + // 2
"pqca.invoices.partno as partno, " + // 3
"COALESCE(pqca.entry.volume, 0) as entry_volume, " + // 4
"COALESCE(pqca.entry.weight, 0) as entry_weight, " + // 5
"COALESCE(pqca.sale.volume, 0) as sale_volume, " + // 6
"COALESCE(pqca.sale.weight, 0) as sale_weight " + // 7
"FROM " +
"pqca.materials LEFT JOIN pqca.invoices ON pqca.materials.id = pqca.invoices.material " +
"LEFT JOIN pqca.entry ON pqca.invoices.id = pqca.entry.invoice " +
"LEFT JOIN pqca.sale ON pqca.invoices.id = pqca.sale.invoice " +
"ORDER BY " +
"material ASC, " +
"partno ASC;";
// string query1 = $"SELECT * FROM pqca.tmp_materials ORDER BY name, partno ASC;";
// var t = getData(query1);
SQLCommand = new NpgsqlCommand(query, Connection);
Connection.Open();
SQLData = SQLCommand.ExecuteReader();
while (SQLData.Read())
{
Material layer = new Material();
List <Chemical> chemicals;
// var cell1 = SQLData[0];
// var cell2 = SQLData[1];
// var cell3 = SQLData[2];
// var cell4 = SQLData[3];
// var cell5 = SQLData[4];
// var cell6 = SQLData[5];
// var cell7 = SQLData[6];
// var cell8 = SQLData[7];
long material_id = SQLData.GetInt64(0);
long invoice_id = SQLData.GetInt64(1);
string material_name = SQLData.GetString(2);
int partno = SQLData.GetInt32(3);
double entry_volume = SQLData.GetDouble(4);
double entry_weight = SQLData.GetDouble(5);
double sale_volume = SQLData.GetDouble(6);
double sale_weight = SQLData.GetDouble(7);
double mat_weight = entry_weight - sale_weight;
double mat_volume = entry_volume - sale_volume;
if (mat_weight > 0)
{
chemicals = GetChemicals(material_name);
layer.setMaterial(material_id, invoice_id, material_name, partno, mat_weight, mat_volume);
layer.AddChemicals(chemicals);
Result.Add(layer);
}
}
Connection.Close();
}
else
{
Result = null;
}
return(Result);
}
void grid(bool filterOn)
{
dgvEmprestimo.DataSource = null;
try
{
con = new Connection("localhost", "5432", "postgres", "postgres", "admin");
string sql = "SELECT id_emprestimo, (SELECT codigo FROM public.livro WHERE public.livro.id_livro = e.id_livro) AS codigo, (SELECT titulo FROM public.livro WHERE public.livro.id_livro = e.id_livro) AS livro, (SELECT nome FROM public.user WHERE public.user.id_user = e.id_user) AS user, to_char(data_emp, 'dd/MM/yyyy'), to_char(data_prev_dev, 'dd/MM/yyyy') FROM public.emprestimo AS e INNER JOIN public.user AS u ON u.id_user = e.id_user INNER JOIN public.livro AS l ON l.id_livro = e.id_livro WHERE e.devolvido = FALSE";
NpgsqlDataReader dr = con.Select(sql);
if (dr.HasRows)
{
idEmprestimos.Clear();
idAtrasos.Clear();
while (dr.Read())
{
string dataPrevDev = dr.GetString(5);
DateTime datePrevDev = DateTime.ParseExact(dataPrevDev, "dd/MM/yyyy", null);
long id_emp = dr.GetInt64(0);
idEmprestimos.Add(id_emp);
if (DateTime.Now > datePrevDev)
{
idAtrasos.Add(id_emp);
}
}
}
else
{
util.Msg("Nenhum empréstimo foi encontrado!", MessageBoxIcon.Warning);
voltar();
}
if (filterOn)
{
string pesq;
if (txtPesquisa.Text.Length > 0)
{
pesq = txtPesquisa.Text.ToLower();
sql += " AND (lower(u.nome) LIKE '%" + pesq + "%' OR lower(l.titulo) LIKE '%" + pesq + "%' OR lower(l.codigo) LIKE '%" + pesq + "%')";
}
}
sql += " ORDER BY e.data_prev_dev ASC";
con.Close();
con = new Connection("localhost", "5432", "postgres", "postgres", "admin");
DataTable dt = con.SelectDataTable(sql);
dgvEmprestimo.Columns.Clear();
dgvEmprestimo.Rows.Clear();
if (dt.Rows.Count > 0)
{
dgvEmprestimo.DataSource = dt;
int i = 0;
setToAutoFill(dgvEmprestimo);
dgvEmprestimo.Columns[i++].HeaderText = "Id";
dgvEmprestimo.Columns[i++].HeaderText = "Código Livro";
dgvEmprestimo.Columns[i++].HeaderText = "Livro";
dgvEmprestimo.Columns[i++].HeaderText = "Usuário";
dgvEmprestimo.Columns[i++].HeaderText = "Data Retirada";
dgvEmprestimo.Columns[i++].HeaderText = "Data Devolução";
if (dgvEmprestimo.Rows.Count > 0)
{
dgvEmprestimo.Columns.Add("Atrasado", "Atrasado");
}
for (int j = 0; j < dgvEmprestimo.Rows.Count; j++)
{
string atrasado = "Não";
if (idAtrasos.Contains(Convert.ToInt64(dgvEmprestimo.Rows[j].Cells[0].Value)))
{
atrasado = "Sim";
dgvEmprestimo.Rows[j].DefaultCellStyle.BackColor = Color.FromArgb(255, 192, 192);
}
dgvEmprestimo.Rows[j].Cells[i].Value = atrasado;
}
dgvEmprestimo.Columns[0].Visible = false;
}
con.Close();
}
catch (Exception ex)
{
util.Msg("Algo deu errado!\n\nTente novamente mais tarde!\n\nMais detalhes: " + ex.Message, MessageBoxIcon.Error);
voltar();
}
dgvEmprestimo.ClearSelection();
}
public IActionResult GetTimeline(string guid, string sharedid)
{
if (!Helper.IsCallerValid(guid, sharedid, _connstrWallet))
{
ErrorObject ro = new ErrorObject();
ro.message = "ErrInvalid";
ro.error = true;
return(Json(ro));
}
string errorMessage = "";
bool isError = false;
//get addresses
//select unspent outputs
//sum and return
List <TimelineRecord> timeline = new List <TimelineRecord>();
NpgsqlConnection conn = new NpgsqlConnection(_connstrWallet);
conn.Open();
try
{
NpgsqlCommand cmd = new NpgsqlCommand("sp_timelinebyuser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("p_guid", guid));
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TimelineRecord tr = new TimelineRecord();
tr.TimelineType = reader.GetString(0);
if (!reader.IsDBNull(1))
{
tr.TransactionId = reader.GetString(1);
}
tr.UserName = reader.GetString(2);
if (!reader.IsDBNull(3))
{
tr.InvoiceId = reader.GetInt32(3);
}
tr.TimelineDate = reader.GetDateTime(4);
if (!reader.IsDBNull(5))
{
tr.Amount = reader.GetInt64(5);
}
if (!reader.IsDBNull(6))
{
tr.BlockNumber = reader.GetInt32(6);
}
if (!reader.IsDBNull(7))
{
tr.UserNameImage = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
tr.InvoiceStatus = reader.GetInt32(8);
}
if (!reader.IsDBNull(9))
{
tr.InvoiceStatusR = reader.GetInt32(9);
}
timeline.Add(tr);
}
reader.Dispose();
}
catch (Exception ex)
{
isError = true;
errorMessage = "Database related Error";
}
finally
{
conn.Close();
}
NpgsqlConnection connBlk = new NpgsqlConnection(_connstrBlockchain);
connBlk.Open();
int lastBlock = 0;
try
{
//get the block number
NpgsqlCommand cmdlr = new NpgsqlCommand("select RunningBlockIndex from LastRead", connBlk);
NpgsqlDataReader rd = cmdlr.ExecuteReader();
if (rd.Read())
{
lastBlock = rd.GetInt32(0);
}
rd.Dispose();
}
catch (Exception ex)
{
isError = true;
errorMessage = "Database related Error";
}
finally
{
connBlk.Close();
}
foreach (TimelineRecord record in timeline)
{
if (record.BlockNumber > 0)
{
record.Confirmations = (lastBlock - record.BlockNumber) + 1;
}
}
if (isError)
{
ErrorObject ro = new ErrorObject();
ro.message = errorMessage;
ro.error = true;
return(Json(ro));
}
else
{
return(Json(timeline));
}
}
public string GetNotifList()
{
this.notificationStatus.NotifDetailsList = new List <NotificationDetail>();
if (conn == null)
{
conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1;Database=wfs_db;MaxPoolSize=1000;Pooling=true;Timeout=500;SyncNotification=true;preload reader=true;");
}
try
{
sqlCmd = new NpgsqlCommand();
sqlCmd.Connection = conn;
if (sqlCmd.Connection.State == ConnectionState.Closed)
{
sqlCmd.Connection.Open();
}
if (command == null)
{
command = new NpgsqlCommand("listen notifytickets;", conn);
command.ExecuteNonQuery();
}
if (userid == null)
{
userid = web.Session.SessionUser.User.User.UserId.ToString();
}
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT * FROM dbo.notifyview WHERE userid = " + userid + " ORDER BY createdate DESC";
if (notificationflg == false)
{
conn.Notification += new NotificationEventHandler(OnNotification);
notificationflg = true;
}
#region oldcode
//using (cfgContext db = new cfgContext())
//{
// var currentUserId = WFS.web.Session.SessionUser.User.User.UserId;
// var notifList = db.User.Include("Notifications").FirstOrDefault(r => r.UserId == currentUserId).Notifications.ToList();
// foreach (var item in notifList)
// {
// NotificationDetail notif = new NotificationDetail()
// {
// NotificationId = item.notificationid,
// Title = item.title,
// Message = item.message,
// CreateDate = item.createdate.ToString(),
// State = item.state,
// IsSeen = item.isseen,
// Status = item.status,
// NotifStatus = item.notifstatus
// };
// notificationStatus.NotifDetailsList.Add(notif);
// }
//}
#endregion
reader = sqlCmd.ExecuteReader();
while (reader.Read())
{
if (!reader.GetBoolean(5))
{
NotificationDetail notif = new NotificationDetail();
notif.NotificationId = reader.GetInt64(0);
notif.Title = reader.GetString(1);
notif.Message = reader.GetString(2);
notif.CreateDate = reader.GetDateTime(3).ToString();
notif.State = reader.GetString(4);
notif.IsSeen = reader.GetBoolean(5);
notif.Status = reader.GetBoolean(6);
notif.NotifStatus = reader.GetBoolean(7);
notificationStatus.userId = Convert.ToInt64(userid);
notificationStatus.NotifDetailsList.Add(notif);
}
}
}
catch (Exception ex)
{
throw;
}
lock (threadSafeCode)
{
HttpRuntime.Cache["Notification" + userid] = SerializeObjectToJson(this.notificationStatus);
}
return(HttpRuntime.Cache["Notification" + userid] as string);
}
public void getFriendlyAccounts(FriendSystem system, bool isOnline)
{
if (system == null)
{
return;
}
if (system._friends.Count == 0)
{
return;
}
try
{
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
NpgsqlCommand command = npgsqlConnection.CreateCommand();
List <string> stringList = new List <string>();
for (int index = 0; index < system._friends.Count; ++index)
{
Friend friend = system._friends[index];
if (friend.state > 0)
{
return;
}
string parameterName = "@valor" + (object)index;
command.Parameters.AddWithValue(parameterName, (object)friend.player_id);
stringList.Add(parameterName);
}
string str = string.Join(",", stringList.ToArray());
if (str == "")
{
return;
}
npgsqlConnection.Open();
command.Parameters.AddWithValue("@on", (object)isOnline);
command.CommandText = "SELECT player_name,player_id,rank,status FROM accounts WHERE player_id in (" + str + ") AND online=@on ORDER BY player_id";
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
while (npgsqlDataReader.Read())
{
Friend friend = system.GetFriend(npgsqlDataReader.GetInt64(1));
if (friend != null)
{
friend.player.player_name = npgsqlDataReader.GetString(0);
friend.player._rank = npgsqlDataReader.GetInt32(2);
friend.player._isOnline = isOnline;
friend.player._status.SetData((uint)npgsqlDataReader.GetInt64(3), friend.player_id);
if (isOnline && !this._contas.ContainsKey(friend.player_id))
{
friend.player.setOnlineStatus(false);
friend.player._status.ResetData(friend.player_id);
}
}
}
command.Dispose();
npgsqlDataReader.Dispose();
npgsqlDataReader.Close();
npgsqlConnection.Dispose();
npgsqlConnection.Close();
}
}
catch (Exception ex)
{
Logger.error("Ocorreu um problema ao carregar (FriendAccounts2)!\r\n" + ex.ToString());
}
}
/// <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)
{
var filtered = new List <OsmGeo>();
foreach (OsmGeo geo in res)
{
if (filter.Evaluate(geo))
{
filtered.Add(geo);
}
}
return(filtered);
}
return(res);
}
public bool CreateAccount(out Account p, string login, string password, IPAddress endPoint)
{
DateTime dateTime;
if (this.LastAutoCreations.TryGetValue(endPoint.ToString(), out dateTime) && (DateTime.Now - dateTime).TotalSeconds < 180.0)
{
p = (Account)null;
return(false);
}
if (this.LastAutoCreations.ContainsKey(endPoint.ToString()))
{
this.LastAutoCreations[endPoint.ToString()] = DateTime.Now;
}
else
{
this.LastAutoCreations.Add(endPoint.ToString(), DateTime.Now);
}
try
{
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
NpgsqlCommand command = npgsqlConnection.CreateCommand();
npgsqlConnection.Open();
command.Parameters.AddWithValue("@login", (object)login);
command.Parameters.AddWithValue("@pass", (object)password);
command.Parameters.AddWithValue("@ip", (object)endPoint.ToString());
command.CommandText = "INSERT INTO accounts (login, password, lastip) VALUES (@login,@pass,@ip)";
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM accounts WHERE login=@login";
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
Account acc = new Account();
while (npgsqlDataReader.Read())
{
acc.login = login;
acc.password = password;
acc.player_id = npgsqlDataReader.GetInt64(2);
acc.SetPlayerId();
acc.player_name = npgsqlDataReader.GetString(3);
acc.name_color = npgsqlDataReader.GetInt32(4);
acc.clan_id = npgsqlDataReader.GetInt32(5);
acc._rank = npgsqlDataReader.GetInt32(6);
acc._gp = npgsqlDataReader.GetInt32(7);
acc._exp = npgsqlDataReader.GetInt32(8);
acc.pc_cafe = npgsqlDataReader.GetInt32(9);
acc._statistic.fights = npgsqlDataReader.GetInt32(10);
acc._statistic.fights_win = npgsqlDataReader.GetInt32(11);
acc._statistic.fights_lost = npgsqlDataReader.GetInt32(12);
acc._statistic.kills_count = npgsqlDataReader.GetInt32(13);
acc._statistic.deaths_count = npgsqlDataReader.GetInt32(14);
acc._statistic.headshots_count = npgsqlDataReader.GetInt32(15);
acc._statistic.escapes = npgsqlDataReader.GetInt32(16);
acc.access = npgsqlDataReader.GetInt32(17);
acc.LastRankUpDate = (uint)npgsqlDataReader.GetInt64(20);
acc._money = npgsqlDataReader.GetInt32(21);
acc._isOnline = npgsqlDataReader.GetBoolean(22);
acc._equip._primary = npgsqlDataReader.GetInt32(23);
acc._equip._secondary = npgsqlDataReader.GetInt32(24);
acc._equip._melee = npgsqlDataReader.GetInt32(25);
acc._equip._grenade = npgsqlDataReader.GetInt32(26);
acc._equip._special = npgsqlDataReader.GetInt32(27);
acc._equip._red = npgsqlDataReader.GetInt32(28);
acc._equip._blue = npgsqlDataReader.GetInt32(29);
acc._equip._helmet = npgsqlDataReader.GetInt32(30);
acc._equip._dino = npgsqlDataReader.GetInt32(31);
acc._equip._beret = npgsqlDataReader.GetInt32(32);
acc.brooch = npgsqlDataReader.GetInt32(33);
acc.insignia = npgsqlDataReader.GetInt32(34);
acc.medal = npgsqlDataReader.GetInt32(35);
acc.blue_order = npgsqlDataReader.GetInt32(36);
acc._mission.mission1 = npgsqlDataReader.GetInt32(37);
acc.clanAccess = npgsqlDataReader.GetInt32(38);
acc.effects = (CupomEffects)npgsqlDataReader.GetInt64(40);
acc._statistic.fights_draw = npgsqlDataReader.GetInt32(41);
acc._mission.mission2 = npgsqlDataReader.GetInt32(42);
acc._mission.mission3 = npgsqlDataReader.GetInt32(43);
acc._statistic.totalkills_count = npgsqlDataReader.GetInt32(44);
acc._statistic.totalfights_count = npgsqlDataReader.GetInt32(45);
acc._status.SetData((uint)npgsqlDataReader.GetInt64(46), acc.player_id);
acc.MacAddress = (PhysicalAddress)npgsqlDataReader.GetValue(50);
acc.ban_obj_id = npgsqlDataReader.GetInt64(51);
}
p = acc;
this.AddAccount(acc);
command.Dispose();
npgsqlConnection.Dispose();
npgsqlConnection.Close();
return(true);
}
}
catch (Exception ex)
{
Logger.warning("[AccountManager.CreateAccount] " + ex.ToString());
p = (Account)null;
return(false);
}
}
public static string CheckKendaraan(string contCardID)
{
string result = string.Empty;
try
{
using (NpgsqlConnection npgsqlConnection = AppConfig.GetConnection())
{
if (npgsqlConnection.State == ConnectionState.Closed)
{
npgsqlConnection.Open();
}
string query = string.Format("SELECT {0} FROM {1} WHERE contcardid=@ContCardId ",
string.Format(DEFAULT_COLUMN, string.Empty),
DEFAULT_TABLE);
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(query, npgsqlConnection))
{
npgsqlCommand.Parameters.AddWithValue("@ContCardId", contCardID);
using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader())
{
if (npgsqlDataReader.Read())
{
result = "ID ContCard: " + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("contcardid")) + " ,";
result = result + "Card Mode :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("cardmode"));
result = result + "Ref Mode :" + npgsqlDataReader.GetInt64(npgsqlDataReader.GetOrdinal("refid"));
result = result + "Cont Count :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("cont"));
result = result + "Cont Size :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("size"));
result = result + "Cont Type :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("type"));
if (npgsqlDataReader["dtm1"] != DBNull.Value)
{
result = result + "dtm1 :" + npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtm1")).ToString();
}
result = result + "Loc1 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("loc1"));
if (npgsqlDataReader["dtm2"] != DBNull.Value)
{
result = result + "dtm2 :" + npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtm2")).ToString();
}
result = result + "Loc2 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("loc2"));
result = result + "Remark :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("remark"));
if (npgsqlDataReader["dtm3"] != DBNull.Value)
{
result = result + "dtm3 :" + npgsqlDataReader.GetDateTime(npgsqlDataReader.GetOrdinal("dtm3")).ToString();
}
result = result + "contInOutID :" + npgsqlDataReader.GetInt64(npgsqlDataReader.GetOrdinal("continoutid")).ToString();
result = result + "userid3 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("userid3"));
result = result + "seal1 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("seal1"));
result = result + "seal2 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("seal2"));
result = result + "seal3 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("seal3"));
result = result + "seal4 :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("seal4"));
result = result + "nomobilout :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("nomobilout"));
result = result + "angkutanout :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("angkutanout")).ToString();
result = result + "token :" + npgsqlDataReader.GetString(npgsqlDataReader.GetOrdinal("token")).ToString();
result = result + "IsCombo :" + npgsqlDataReader.GetBoolean(npgsqlDataReader.GetOrdinal("iscombo")).ToString();
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public Account getAccountDB(object valor, object valor2, int type, int searchFlag)
{
if (type == 0 && (string)valor == "" || type == 1 && (long)valor == 0L || type == 2 && (string.IsNullOrEmpty((string)valor) || string.IsNullOrEmpty((string)valor2)))
{
return((Account)null);
}
Account acc = (Account)null;
try
{
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
NpgsqlCommand command = npgsqlConnection.CreateCommand();
npgsqlConnection.Open();
command.Parameters.AddWithValue("@valor", valor);
switch (type)
{
case 0:
command.CommandText = "SELECT * FROM accounts WHERE login=@valor LIMIT 1";
break;
case 1:
command.CommandText = "SELECT * FROM accounts WHERE player_id=@valor LIMIT 1";
break;
case 2:
command.Parameters.AddWithValue("@valor2", valor2);
command.CommandText = "SELECT * FROM accounts WHERE login=@valor AND password=@valor2 LIMIT 1";
break;
}
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
while (npgsqlDataReader.Read())
{
acc = new Account();
acc.login = npgsqlDataReader.GetString(0);
acc.password = npgsqlDataReader.GetString(1);
acc.SetPlayerId(npgsqlDataReader.GetInt64(2), searchFlag);
acc.player_name = npgsqlDataReader.GetString(3);
acc.name_color = npgsqlDataReader.GetInt32(4);
acc.clan_id = npgsqlDataReader.GetInt32(5);
acc._rank = npgsqlDataReader.GetInt32(6);
acc._gp = npgsqlDataReader.GetInt32(7);
acc._exp = npgsqlDataReader.GetInt32(8);
acc.pc_cafe = npgsqlDataReader.GetInt32(9);
acc._statistic.fights = npgsqlDataReader.GetInt32(10);
acc._statistic.fights_win = npgsqlDataReader.GetInt32(11);
acc._statistic.fights_lost = npgsqlDataReader.GetInt32(12);
acc._statistic.kills_count = npgsqlDataReader.GetInt32(13);
acc._statistic.deaths_count = npgsqlDataReader.GetInt32(14);
acc._statistic.headshots_count = npgsqlDataReader.GetInt32(15);
acc._statistic.escapes = npgsqlDataReader.GetInt32(16);
acc.access = npgsqlDataReader.GetInt32(17);
acc.LastRankUpDate = (uint)npgsqlDataReader.GetInt64(20);
acc._money = npgsqlDataReader.GetInt32(21);
acc._isOnline = npgsqlDataReader.GetBoolean(22);
acc._equip._primary = npgsqlDataReader.GetInt32(23);
acc._equip._secondary = npgsqlDataReader.GetInt32(24);
acc._equip._melee = npgsqlDataReader.GetInt32(25);
acc._equip._grenade = npgsqlDataReader.GetInt32(26);
acc._equip._special = npgsqlDataReader.GetInt32(27);
acc._equip._red = npgsqlDataReader.GetInt32(28);
acc._equip._blue = npgsqlDataReader.GetInt32(29);
acc._equip._helmet = npgsqlDataReader.GetInt32(30);
acc._equip._dino = npgsqlDataReader.GetInt32(31);
acc._equip._beret = npgsqlDataReader.GetInt32(32);
acc.brooch = npgsqlDataReader.GetInt32(33);
acc.insignia = npgsqlDataReader.GetInt32(34);
acc.medal = npgsqlDataReader.GetInt32(35);
acc.blue_order = npgsqlDataReader.GetInt32(36);
acc._mission.mission1 = npgsqlDataReader.GetInt32(37);
acc.clanAccess = npgsqlDataReader.GetInt32(38);
acc.effects = (CupomEffects)npgsqlDataReader.GetInt64(40);
acc._statistic.fights_draw = npgsqlDataReader.GetInt32(41);
acc._mission.mission2 = npgsqlDataReader.GetInt32(42);
acc._mission.mission3 = npgsqlDataReader.GetInt32(43);
acc._statistic.totalkills_count = npgsqlDataReader.GetInt32(44);
acc._statistic.totalfights_count = npgsqlDataReader.GetInt32(45);
acc._status.SetData((uint)npgsqlDataReader.GetInt64(46), acc.player_id);
acc.MacAddress = (PhysicalAddress)npgsqlDataReader.GetValue(50);
acc.ban_obj_id = npgsqlDataReader.GetInt64(51);
if (this.AddAccount(acc) && acc._isOnline)
{
acc.setOnlineStatus(false);
}
}
command.Dispose();
npgsqlDataReader.Dispose();
npgsqlDataReader.Close();
npgsqlConnection.Dispose();
npgsqlConnection.Close();
}
}
catch (Exception ex)
{
Logger.error("Ocorreu um problema ao carregar as contas4!\r\n" + ex.ToString());
}
return(acc);
}
