/// <summary>
/// Retrieves a subset of incident information to display in the all incidents tab DataSet.
/// This method was designed for efficiency - all incident is retrieved in a single SELECT statement and passed to an Incident object
/// which then exposes the data
/// </summary>
/// <param name="incidentNumber">The unique incident number to retrieve information for</param>
/// <returns>An object representing the collection of information for a single incident</returns>
public IncidentInfo GetAllDetails(int incidentNumber)
{
//will hold the results
DateTime dateTime = DateTime.MinValue;
string caller = string.Empty;
string exchange = string.Empty;
IncidentType type = null;
string details = string.Empty;
Address address = null;
AdditionalAddressInfo additionalAddressInfo = null;
string summary = string.Empty;
string oic = string.Empty;
List<AssignedResource> assignedResources = new List<AssignedResource>();
string operatorName = string.Empty;
DateTime stopTime = DateTime.MinValue;
DateTime incidentClosedTime = DateTime.MinValue;
//the statement to execute
string statement = "SELECT * FROM incident" + Environment.NewLine +
"INNER JOIN IncidentType" + Environment.NewLine +
"ON incident.IncidentTypeName = incidenttype.Name" + Environment.NewLine +
"LEFT JOIN Incident_Resource " + Environment.NewLine +
"ON incident.IncidentNumber = Incident_Resource.IncidentIncidentNumber" + Environment.NewLine +
"INNER JOIN operator" + Environment.NewLine +
"ON operator.Id = incident.OperatorId" + Environment.NewLine +
"LEFT JOIN appliance" + Environment.NewLine +
"ON Incident_Resource.ResourceCallSign = appliance.CallSign" + Environment.NewLine +
"WHERE incident.IncidentNumber = @incidentNo;";
//object that will execute the query
MySqlCommand command = new MySqlCommand(statement, connection);
command.Parameters.AddWithValue("@incidentNo", incidentNumber);
//execute
MySqlDataReader myReader = null; //reads the database data
try
{
// open the connection only if it is currently closed
if (command.Connection.State == System.Data.ConnectionState.Closed)
command.Connection.Open();
//close the reader if it currently exists
if (myReader != null && !myReader.IsClosed)
myReader.Close();
myReader = command.ExecuteReader(); //execute the select statement
//read the data sent back from MySQL server and create the IncidentInfo object
while (myReader.Read())
{
//retrieve all the data - each area is separated into different regions below
#region Incident Details
if (!myReader.IsDBNull(12)) dateTime = myReader.GetDateTime(12);
if (!myReader.IsDBNull(13)) caller = myReader.GetString(13);
if (!myReader.IsDBNull(14)) exchange = myReader.GetString(14);
if (!myReader.IsDBNull(20) && !myReader.IsDBNull(21)) type = new IncidentType(myReader.GetString(20), myReader.GetString(21));
if (!myReader.IsDBNull(15)) details = myReader.GetString(15);
if (!myReader.IsDBNull(18)) summary = myReader.GetString(18);
if (!myReader.IsDBNull(19)) oic = myReader.GetString(19);
if (!myReader.IsDBNull(32)) operatorName = myReader.GetString(32);
if (!myReader.IsDBNull(16)) stopTime = myReader.GetDateTime(16);
if (!myReader.IsDBNull(17)) incidentClosedTime = myReader.GetDateTime(17);
#endregion
#region Address Data
string building = string.Empty;
if (!myReader.IsDBNull(2)) building = myReader.GetString(2);
string number = string.Empty;
if (!myReader.IsDBNull(3)) number = myReader.GetString(3);
string street = string.Empty;
if (!myReader.IsDBNull(4)) street = myReader.GetString(4);
string town = string.Empty;
if (!myReader.IsDBNull(5)) town = myReader.GetString(5);
string postcode = string.Empty;
if (!myReader.IsDBNull(6)) postcode = myReader.GetString(6);
string county = string.Empty;
if (!myReader.IsDBNull(7)) county = myReader.GetString(7);
double longitude = myReader.GetDouble(8);
double latitude = myReader.GetDouble(9);
address = new Address(-1, building, number, street, town, postcode, county, longitude, latitude);
#endregion
#region Assigned Resource Data
string callSign = string.Empty;
if (!myReader.IsDBNull(23)) callSign = myReader.GetString(23);
DateTime alerted = DateTime.MinValue;
if (!myReader.IsDBNull(24)) alerted = myReader.GetDateTime(24);
DateTime mobile = DateTime.MinValue;
if (!myReader.IsDBNull(25)) mobile = myReader.GetDateTime(25);
DateTime inAttendance = DateTime.MinValue;
if (!myReader.IsDBNull(26)) inAttendance = myReader.GetDateTime(26);
DateTime available = DateTime.MinValue;
if (!myReader.IsDBNull(27)) available = myReader.GetDateTime(27);
DateTime closedDown = DateTime.MinValue;
if (!myReader.IsDBNull(28)) closedDown = myReader.GetDateTime(28);
string appOic = string.Empty;
if (!myReader.IsDBNull(37)) appOic = myReader.GetString(37);
int crew = -1;
if (!myReader.IsDBNull(38)) crew = myReader.GetInt32(38);
int ba = -1;
if (!myReader.IsDBNull(39)) ba = myReader.GetInt32(39);
assignedResources.Add(new AssignedResource(callSign, alerted, mobile, inAttendance, available, closedDown, appOic, crew, ba));
#endregion
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show(statement);
}
finally
{
//close the connections
if (myReader != null)
myReader.Close();
command.Connection.Close();
}
//return the data
return new IncidentInfo(incidentNumber, dateTime, caller, exchange, type, details, address, additionalAddressInfo, summary, oic, assignedResources.ToArray(), operatorName, stopTime, incidentClosedTime);
}
public EventReportForm(int proj_id, int sch_no, int event_id)
{
InitializeComponent();
DataTable evnt = new DataTable();
DataTable reso = new DataTable();
DataTable serviceEng = new DataTable();
evnt.Columns.Add("event_id", typeof(int));
evnt.Columns.Add("proj_id", typeof(int));
evnt.Columns.Add("visit_type_id", typeof(int));
evnt.Columns.Add("to_date_time", typeof(string));
evnt.Columns.Add("from_date_time", typeof(string));
evnt.Columns.Add("sch_no", typeof(int));
evnt.Columns.Add("feedback", typeof(string));
evnt.Columns.Add("Other", typeof(string));
evnt.Columns.Add("to_do_list", typeof(string));
evnt.Columns.Add("check_list", typeof(string));
evnt.Columns.Add("travelling_mode", typeof(string));
evnt.Columns.Add("accommodation_mode", typeof(string));
evnt.Columns.Add("meals", typeof(string));
evnt.Columns.Add("client_name", typeof(string));
evnt.Columns.Add("proj_name", typeof(string));
evnt.Columns.Add("visit_type", typeof(string));
reso.Columns.Add("name", typeof(string));
reso.Columns.Add("qty", typeof(int));
reso.Columns.Add("event_id", typeof(int));
reso.Columns.Add("proj_id", typeof(int));
reso.Columns.Add("sch_no", typeof(int));
serviceEng.Columns.Add("staff_id", typeof(int));
serviceEng.Columns.Add("name", typeof(string));
serviceEng.Columns.Add("task", typeof(string));
serviceEng.Columns.Add("feedback", typeof(string));
serviceEng.Columns.Add("man_hours", typeof(double));
serviceEng.Columns.Add("event_id", typeof(int));
serviceEng.Columns.Add("sch_no", typeof(int));
serviceEng.Columns.Add("proj_no", typeof(int));
string qry = "select s.visit_type_id, s.to_date_time, s.from_date_time, s.feedback," +
"s.to_do_list, s.check_list, s.travelling_mode, s.accommodation_mode, s.meals, s.Other, p.proj_name, " +
"c.name, v.type " +
"from event s, project p, client c, visit_type v " +
"where (s.sch_no = " + sch_no + " and s.proj_id = " + proj_id + " and s.event_id = " + event_id + ") and (p.proj_id = s.proj_id) and (p.client_id = c.client_id) and (s.visit_type_id = v.visit_type_id);";
try
{
MySqlDataReader reader = DBConnection.getData(qry);
if (reader.HasRows)
{
while (reader.Read())
{
evnt.Rows.Add(event_id, proj_id, reader.GetInt16("visit_type_id"), reader.GetString("from_date_time"), reader.GetString("to_do_list")
, sch_no, reader.GetString("feedback"), reader.GetString("Other"), reader.GetString("to_do_list"), reader.GetString("check_list"), reader.GetString("travelling_mode")
, reader.GetString("accommodation_mode"), reader.GetString("meals"), reader.GetString("name"), reader.GetString("proj_name"), reader.GetString("type"));
}
}
reader.Close();
string qry1 = "select et.staff_id, s.first_name, s.last_name, ett.task, et.feedback, ett.man_hours from event_technicians et, staff s, event_technician_task ett where (ett.event_tech_id = et.event_staff_id) and (et.event_id = " + event_id + " and et.proj_id = " + proj_id + " and et.sch_no = " + sch_no + ") and (et.staff_id = s.staff_id);";
MySqlDataReader reader1 = DBConnection.getData(qry1);
if (reader1.HasRows)
{
while (reader1.Read())
{
serviceEng.Rows.Add(reader1.GetInt16("staff_id"), reader1.GetString("first_name") + " " + reader1.GetString("last_name"), reader1.GetString("task"), reader1.GetString("feedback"), reader1.GetDouble("man_hours"), event_id, sch_no, proj_id);
}
}
string qry2 = "select r.name, sr.qty from resource r, event_resources sr where (sr.proj_id = " + proj_id + " and sr.sch_no = " + sch_no + " and sr.event_id = " + event_id + " ) and (sr.resource_id = r.resource_id)";
reader1.Close();
MySqlDataReader reader2 = DBConnection.getData(qry2);
if (reader2.HasRows)
{
while (reader2.Read())
{
reso.Rows.Add(reader2.GetString("name"), reader2.GetInt16("qty"), event_id, proj_id, sch_no);
}
}
reader2.Close();
EventReport sr = new EventReport();
sr.Database.Tables["schedule"].SetDataSource(evnt);
sr.Database.Tables["scheduleReso"].SetDataSource(reso);
sr.Database.Tables["engineers"].SetDataSource(serviceEng);
crystalReportViewer1.ReportSource = sr;
}
catch (Exception e)
{
throw;
}
}
private void Finish_Click(object sender, RoutedEventArgs e)
{
if (Cart.Items.IsEmpty)
{
MessageBox.Show("You cannot make a purchase without anything in your cart");
//Stop if there are no items to be purchased
return;
}
MySqlConnection con = new MySqlConnection(c.dbConnection);
con.Open();
int amount = 0;
double price = 0.0;
double currentCash = 0.0;
MySqlCommand cmd = new MySqlCommand("select amount, price from product where name='" + Cart.Items[0] + "';", con);
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
amount = (reader.GetInt32(0));
price = (reader.GetDouble(1));
}
reader.Close();
MySqlCommand cmd2 = new MySqlCommand("select cash from customer where username='******';", con);
cmd2.ExecuteNonQuery();
MySqlDataReader reader2 = cmd2.ExecuteReader();
while (reader2.Read())
{
currentCash = (reader2.GetDouble(0));
}
reader2.Close();
if (currentCash < 0)
{
MessageBox.Show("You cannot finalize your purchases, you do not have enough money left");
//return to skip the database updates and not make the purchase
return;
}
//Finalize purchase by updating database
MySqlCommand cmd3 = new MySqlCommand("update webshop.product set amount =" + (amount - 1) + " where name = '" + Cart.Items[0] + "'; ", con);
cmd3.ExecuteNonQuery();
MySqlCommand cmd4 = new MySqlCommand("update webshop.customer set cash =" + (currentCash - price) + " where username = '******'; ", con);
cmd4.ExecuteNonQuery();
MySqlCommand cmd5 = new MySqlCommand("insert into webshop.purchases (customer, product, date) values ('" + ValueFromLogin + "', '" + Cart.Items[0] + "', now()); ", con);
cmd5.ExecuteNonQuery();
//Update GUI
Cash.Content = "€" + currentCash;
Cart.Items.Remove(Cart.Items[0]);
MessageBox.Show("You succesfully made a purchase");
}
public List <MessageModel> getAllUserMessages(int id)
{
List <MessageModel> messages = new List <MessageModel>();
if (id > -1)
{
databaseConnection.Open();
query = "Select * from message where userId =" + id;
MySqlCommand commandDatabase = new MySqlCommand(query, databaseConnection);
MySqlDataReader reader = commandDatabase.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
MessageModel model = new MessageModel(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetDouble(4));
messages.Add(model);
}
}
databaseConnection.Close();
}
return(messages);
}
public void beolvasSQL()
{
lb_sql.Items.Clear();
//a reader.GetVAR(y) olvassa ki a megfelelő oszlopot!
MySqlConnection con = null;
MySqlDataReader reader = null;
try
{
con = new MySqlConnection(str);
con.Open();
string cmdText = "SELECT * FROM vasarlok ORDER BY vnev, knev";
MySqlCommand cmd = new MySqlCommand(cmdText, con);
reader = cmd.ExecuteReader();
while (reader.Read())
{
string nev = reader.GetString(0) + " " + reader.GetString(1);
if (nev.Length >= 17)
{
nev = nev.Substring(0, 17);
}
string kartyaSzam =
"["
+ reader.GetString(2).Substring(0, 4)
+ "-"
+ reader.GetString(2).Substring(4, 4)
+ "-"
+ reader.GetString(2).Substring(8, 4)
+ "-"
+ reader.GetString(2).Substring(12, 4)
+ "]";
lb_sql.Items.Add(
new Vasarlo(nev, reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetDouble(3)));
lb_sqlkartya.Items.Add(kartyaSzam);
}
}
catch (MySqlException err)
{
System.IO.File.WriteAllText("log.txt", err.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void Initialize_CalorieChart()
{
try
{
int height, age, activity;
double weight;
string gender;
bool sex = true;
var dateTimeToday1 = DateTime.Today.ToString("yyyy-MM-dd");
MySqlConnection conCal = DietTracker.DatabaseConnect.OpenDefaultDBConnection();
MySqlCommand heightCommand = new MySqlCommand();
heightCommand.CommandText = "SELECT Height FROM users WHERE Username = '******';";
heightCommand.Connection = conCal;
MySqlCommand weightCommand = new MySqlCommand();
weightCommand.CommandText = "SELECT Weight FROM day WHERE UserID = '" + Username + "' AND Date = '" + dateTimeToday1 + "';";
weightCommand.Connection = conCal;
MySqlCommand activityCommand = new MySqlCommand();
activityCommand.CommandText = "SELECT Activity FROM users WHERE Username = '******';";
activityCommand.Connection = conCal;
MySqlCommand ageCommand = new MySqlCommand();
ageCommand.CommandText = "SELECT DoB FROM users WHERE Username = '******';";
ageCommand.Connection = conCal;
MySqlCommand genderCommand = new MySqlCommand();
genderCommand.CommandText = "SELECT Gender FROM users WHERE Username = '******';";
genderCommand.Connection = conCal;
conCal.Open();
MySqlDataReader userHeightRead = heightCommand.ExecuteReader();
userHeightRead.Read();
height = userHeightRead.GetInt32(0);
userHeightRead.Close();
conCal.Close();
conCal.Open();
MySqlDataReader userWeightRead = weightCommand.ExecuteReader();
userWeightRead.Read();
weight = userWeightRead.GetDouble(0);
userWeightRead.Close();
conCal.Close();
conCal.Open();
MySqlDataReader userActivityRead = activityCommand.ExecuteReader();
userActivityRead.Read();
activity = userActivityRead.GetInt32(0);
userActivityRead.Close();
conCal.Close();
conCal.Open();
MySqlDataReader userDoBRead = ageCommand.ExecuteReader();
userDoBRead.Read();
string DoB = userDoBRead.GetDateTime(0).ToShortDateString();
DateTime parsedDoB = DateTime.Parse(DoB);
var dateTimeToday = DateTime.Today;
age = dateTimeToday.Year - parsedDoB.Year;
userDoBRead.Close();
conCal.Close();
conCal.Open();
MySqlDataReader userGenderRead = genderCommand.ExecuteReader();
userGenderRead.Read();
gender = userGenderRead.GetString(0);
if (gender == "Male")
{
sex = true;
}
else if (gender == "Female")
{
sex = false;
}
userGenderRead.Close();
conCal.Close();
MySqlConnection conWUser = DietTracker.DatabaseConnect.OpenDefaultDBConnection();
MySqlCommand dayWeightCommand = new MySqlCommand();
dayWeightCommand.CommandText = "SELECT Weight FROM day WHERE UserID = '" + Username + "' AND Date = '" + dateTimeToday1 + "';";
dayWeightCommand.Connection = conWUser;
conWUser.Open();
MySqlDataReader dayWeightReader = dayWeightCommand.ExecuteReader();
dayWeightReader.Read();
double currentWeight = dayWeightReader.GetDouble(0);
conWUser.Close();
//Inserts BMR value in text field
Formler bmrValue = new Formler();
double show = bmrValue.BMRCalc(height, age, weight, activity, sex);
//Constucts graph
var info = new UpdateCaloriesGraph(show, CaloriesRead);
double caloriesLeft = info.maxCalories - info.CaloriesRead;
_calorieChart.Series["CalorieIntake"].Points.AddXY("Calories Eaten", info.maxCalories);
_calorieChart.Series["CalorieIntake"].Points.AddXY("Calories left", caloriesLeft);
//Inserts text for the BMI value
double showBMI = bmrValue.BMICalc(currentWeight, height);
double newShow = showBMI * 10000;
string visible = "Calories eaten: " + info.CaloriesRead + Environment.NewLine +
"Calories left for today: " + caloriesLeft + Environment.NewLine + Environment.NewLine +
"BMR value (Basal Metabolic Rate)" + Environment.NewLine +
"Calories: " + show + Environment.NewLine + Environment.NewLine +
"BMI Value (Body Mass Index): " + Environment.NewLine + Math.Round(newShow, 1);
_displayMaxCalorie.Text = visible;
}
catch (MySqlException e)
{
MessageBox.Show("Error happened in Connection:" + Environment.NewLine + e);
}
catch (Exception)
{
MessageBox.Show("Something unexpected happened");
}
}
public static void getAccountsByUserID(int id)
{
connection.Open();
try
{
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM bank_accounts WHERE user_id = @id";
cmd.Parameters.AddWithValue("@id", id);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
switch (reader.GetInt32("type"))
{
case 0:
AccountType = "Normal";
break;
case 1:
AccountType = "Plus Konto";
break;
case 2:
AccountType = "Business Konto";
break;
}
UserWindow.items.Add(new Account()
{
Account_id = reader.GetInt32("id"), Account_name = reader.GetString("name"), Account_Type = AccountType, Money_amount = reader.GetDouble("amount")
});
}
}
}
catch (Exception)
{
throw;
}
connection.Close();
}
public override double GetDouble(int ordinal)
{
return(_reader.GetDouble(ordinal));
}
public ReviewOrderMain()
{
InitializeComponent();
orders = new List <Order>();
City tempCity = new City();
allCities = tempCity.getAllCities();
// get all of the existing customers from the database
string requestConfirmedOrders = "SELECT * FROM Orders WHERE OrderStatus = " + "'" + "Confirmed" + "'";
MySqlConnection connectToDatabase = new MySql.Data.MySqlClient.MySqlConnection("host=127.0.0.1;user=root;password=Conestoga1;database=tms;");
connectToDatabase.Open();
MySqlCommand displayTheOrders = new MySqlCommand(requestConfirmedOrders, connectToDatabase);
MySqlDataReader reader = displayTheOrders.ExecuteReader();
try
{
//Read the information in the database and display in textboxes
while (reader.Read())
{
//Read columns in database row by row
var OrderID_T = reader.GetInt32("OrderID");
var StartingCityID_T = reader.GetInt32("StartingCityID");
var EndingCityID_T = reader.GetInt32("EndingCityID");
var CarrierId_T = reader.GetInt32("CarrierID");
var JobType_T = reader.GetString("JobType");
var FTLRate_T = reader.GetDouble("FTLRate");
var LTLRate_T = reader.GetDouble("LTLRate");
var OrderStartDate_T = reader.GetString("OrderStartDate");
var OrderCompleteDate_T = reader.GetString("OrderCompleteDate");
var TotalCost_T = reader.GetDouble("TotalCost");
var OrderStatus_T = reader.GetString("OrderStatus");
var VanType_T = reader.GetString("VanType");
var Quantity_T = reader.GetInt32("Quantity");
orders.Add(new Order()
{
OrderID = OrderID_T,
StartingCityID = StartingCityID_T,
EndingCityID = EndingCityID_T,
CarrierID = CarrierId_T,
JobType = JobType_T,
FTLRate = FTLRate_T,
LTLRate = LTLRate_T,
OrderStartDate = OrderStartDate_T,
OrderCompleteDate = OrderCompleteDate_T,
TotalCost = TotalCost_T,
OrderStatus = OrderStatus_T,
VanType = VanType_T,
Quantity = Quantity_T
});
var OriginCity = allCities.Find(i => i.CityID == StartingCityID_T);
var OriginCityName = OriginCity.CityName;
var DestinationCity = allCities.Find(i => i.CityID == EndingCityID_T);
var DestinationCityName = DestinationCity.CityName;
String orderToDisplay = "OrderId: " + OrderID_T + " " + "CarrierId: " + CarrierId_T + " " + "Origin: " + OriginCityName + " " +
"Destination: " + DestinationCityName + " " + "Total Amount: " + TotalCost_T;
CompletedOrders.Items.Add(orderToDisplay);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
connectToDatabase.Close();
}
public clsNotaIngreso CargaNotaIngreso(Int32 CodNota)
{
clsNotaIngreso nota = null;
try
{
con.conectarBD();
cmd = new MySqlCommand("MuestraNotaIngreso", con.conector);
cmd.Parameters.AddWithValue("codnota", CodNota);
cmd.CommandType = CommandType.StoredProcedure;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
nota = new clsNotaIngreso();
nota.CodNotaIngreso = dr.GetString(0);
nota.CodAlmacen = Convert.ToInt32(dr.GetDecimal(1));
nota.CodTipoTransaccion = Convert.ToInt32(dr.GetDecimal(2));
nota.SiglaTransaccion = dr.GetString(3);
nota.DescripcionTransaccion = dr.GetString(4);
nota.CodTipoDocumento = Convert.ToInt32(dr.GetDecimal(5));
nota.SiglaDocumento = dr.GetString(6);
nota.NumDoc = dr.GetString(7);
nota.CodProveedor = Convert.ToInt32(dr.GetString(8));
nota.RUCProveedor = dr.GetString(9);
nota.RazonSocialProveedor = dr.GetString(10);
nota.Moneda = Convert.ToInt32(dr.GetString(11));
nota.TipoCambio = dr.GetDouble(12);
nota.FechaIngreso = dr.GetDateTime(13);
nota.Comentario = dr.GetString(14);
nota.MontoBruto = dr.GetDouble(15);
nota.MontoDscto = dr.GetDouble(16);
nota.Igv = dr.GetDouble(17);
nota.Total = dr.GetDouble(18);
nota.Abonado = dr.GetDouble(19);
nota.Pendiente = dr.GetDouble(20);
nota.FormaPago = Convert.ToInt32(dr.GetString(23));
nota.FechaPago = dr.GetDateTime(24);
nota.Cancelado = Convert.ToInt32(dr.GetDecimal(25));
nota.CodUser = Convert.ToInt32(dr.GetDecimal(26));
nota.FechaRegistro = dr.GetDateTime(27);
nota.CodSerie = Convert.ToInt32(dr.GetDecimal(28));
nota.Serie = dr.GetString(29);
nota.CodReferencia = Convert.ToInt32(dr.GetDecimal(30));
nota.Flete = dr.GetDouble(31);
nota.SDocumentoOrden = dr.GetString(32);
nota.codalmacenemisor = dr.GetInt32(33);
nota.Codconductor = dr.GetInt32(34);
nota.Codvehiculotransporte = dr.GetInt32(35);
nota.Estado = Convert.ToInt32(dr.GetDecimal(21));
nota.Recibido = Convert.ToInt32(dr.GetDecimal(22));
nota.Aplicada = Convert.ToInt32(dr.GetString(36));
nota.CodAplicada = dr.GetInt32(37);
nota.Motivo = dr.GetString(38);
}
}
return(nota);
}
catch (MySqlException ex)
{
throw ex;
}
finally { con.conector.Dispose(); cmd.Dispose(); con.desconectarBD(); }
}
internal void SyncEventTypes()
{
eventtypes = new ObservableCollection <EventType>();
eventtypes_final = new ObservableCollection <EventType>();
ComboBoxItem ci = (ComboBoxItem)ItemsPerPage.SelectedItem;
int itemsPerPage = Convert.ToInt32(ci.Content);
int page = 1;
int count = 0;
dbman = new DBConnectionManager();
using (conn = new MySqlConnection(dbman.GetConnStr()))
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
using (MySqlConnection conn2 = new MySqlConnection(dbman.GetConnStr()))
{
conn2.Open();
MySqlCommand cmd = conn2.CreateCommand();
cmd.CommandText = "SELECT * FROM appointment_types;";
MySqlDataReader db_reader = cmd.ExecuteReader();
while (db_reader.Read())
{
string type = "";
if (db_reader.GetInt32("custom") == 1)
{
type = "Mass";
}
else
{
type = "Special";
}
string active = "";
if (db_reader.GetInt32("status") == 1)
{
active = "Active";
}
else
{
active = "Inactive";
}
eventtypes.Add(new EventType()
{
TypeID = db_reader.GetString("type_id"),
AppointmentType = db_reader.GetString("appointment_type"),
IsCustom = type,
Fee = db_reader.GetDouble("fee"),
IsActive = active,
Page = page
});
count++;
if (count == itemsPerPage)
{
page++;
count = 0;
}
}
int temp = 1;
foreach (var cur in eventtypes)
{
if (cur.Page == CurrentPage.Value)
{
eventtypes_final.Add(new EventType()
{
No = temp,
TypeID = cur.TypeID,
AppointmentType = cur.AppointmentType,
IsCustom = cur.IsCustom,
Fee = cur.Fee,
IsActive = cur.IsActive,
Page = cur.Page
});
temp++;
}
}
//close Connection
conn2.Close();
//AccountsItemContainer.Items.Clear();
EventTypeItemContainer.Items.Refresh();
EventTypeItemContainer.ItemsSource = eventtypes_final;
EventTypeItemContainer.Items.Refresh();
CurrentPage.Maximum = page;
}
}
else
{
}
conn.Close();
}
}
public Produto GetProdutoNome(string nome)
{
_logger.LogDebug("A executar [ProdutoDAO -> GetProdutoNome]");
try
{
Produto produto = null;
_connectionDBService.OpenConnection();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = _connectionDBService.Connection;
cmd.CommandText = "get_produto_nome";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?nome", nome);
cmd.Parameters["?nome"].Direction = ParameterDirection.Input;
using (MySqlDataReader var = cmd.ExecuteReader())
{
if (var.Read())
{
produto = new Produto {
IdProduto = var.GetInt32(0), Nome = nome, IdCategoria = var.GetInt32(3), Preco = var.GetDouble(1), Ingredientes = new List <string>(), Alergenios = new List <string>(), ExtensaoImagem = var.GetString(2)
};
}
}
}
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = _connectionDBService.Connection;
cmd.CommandText = "get_ingredientes_produto";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?idProduto", produto.IdProduto);
cmd.Parameters["?idProduto"].Direction = ParameterDirection.Input;
using (MySqlDataReader var = cmd.ExecuteReader())
{
while (var.Read())
{
produto.Ingredientes.Add(var.GetString(0));
}
}
}
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = _connectionDBService.Connection;
cmd.CommandText = "get_alergenios_produto";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?idProduto", produto.IdProduto);
cmd.Parameters["?idProduto"].Direction = ParameterDirection.Input;
using (MySqlDataReader var = cmd.ExecuteReader())
{
while (var.Read())
{
produto.Alergenios.Add(var.GetString(0));
}
}
}
return(produto);
}
catch (Exception) { throw; }
finally
{
_connectionDBService.CloseConnection();
}
}
//Elimina un artículo de la lista segun id
public bool EliminarArticulo(string nombre)
{
bool res = true;
int idp = 0;
double precio;
ConexionBBDD conexion = new ConexionBBDD();
if (conexion.AbrirConexion())
{
//bool esta = false;
string consulta = "select nombre, cantidad, idarticulo from articulospedido inner join Articulos on idarticulo=id";
MySqlCommand comando = new MySqlCommand(consulta, conexion.Conexion);
MySqlDataReader reader = comando.ExecuteReader();
Articulos articulo = null;
int cantidad = 0;
while (reader.Read())
{
if (reader.GetString(0) == nombre)
{
//esta = true;
idp = reader.GetInt32(2);
}
cantidad = reader.GetInt32(1);
precio = reader.GetDouble(2);
articulo = new Articulos(nombre, cantidad, idp);
}
reader.Close();
consulta = String.Format("select cantidad from articulospedido inner join Articulos on idarticulo=id inner join pedidos on pedidos.id = articulospedido.idpedido where idarticulo = {0} and pedidos.id = {1}", idp, this.id);
comando = new MySqlCommand(consulta, conexion.Conexion);
reader = comando.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
cantidad = reader.GetInt32(0);
reader.Close();
if (cantidad >= 2)
{
DecrementarCantidadArticulo(articulo);
}
else
{
consulta = String.Format("delete from articulospedido where idarticulo in(select id from articulos where nombre = '{0}') and idpedido = {1}", nombre, this.id);
comando = new MySqlCommand(consulta, conexion.Conexion);
comando.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("No esta el artículo");
res = false;
}
}
else
{
MessageBox.Show("Error");
}
return(res);
//ConexionBBDD conexion = new ConexionBBDD();
//List<Articulos> articulos = new List<Articulos>();
//string consulta = "select id, nombre from articulospedido inner join Articulos on idarticulo=id";
//MySqlCommand comando = new MySqlCommand(consulta, conexion.Conexion);
//MySqlDataReader reader = comando.ExecuteReader();
//while (reader.Read())
//{
// articulos.Add(new Articulos(reader.GetString(0)));
//}
//reader.Close();
//consulta = String.Format("delete from articulospedido where id = {0}", id);
//comando = new MySqlCommand(consulta, conexion.Conexion);
//comando.ExecuteNonQuery();
//Recorro la lista y cuando el id coincide lo borra
//foreach (Articulos elem in articulos)
//{
// if(elem.Id == id)
// {
// //articulos.Remove(elem);//cambiar por delete en bd
// }
//}
}
public void RenderGrid()
{
Connection connect = new Connection();
conn = connect.Connect();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
string sqlSelectAll = "SELECT spares.spares_id,spares.spares_name,verify_item.num,format(verify_item.price,0),format((verify_item.num * verify_item.price),0) as all_price " +
"from verify_item " +
"INNER JOIN spares on verify_item.spares_id = spares.spares_id " +
"where ver_id='" + ver_id.Text + "' ";
// Console.WriteLine(sqlSelectAll);
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, conn);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
///ปรับแต่งข้อความ header ของ gridview
dataGridView1.Columns[0].HeaderText = "ID";
dataGridView1.Columns[0].Width = 200;
dataGridView1.Columns[1].HeaderText = "ชื่ออะไหล่";
dataGridView1.Columns[1].Width = 180;
dataGridView1.Columns[2].HeaderText = "จำนวน";
dataGridView1.Columns[2].Width = 70;
dataGridView1.Columns[3].HeaderText = "ราคาขาย";
dataGridView1.Columns[3].Width = 150;
dataGridView1.Columns[4].HeaderText = "รวม";
dataGridView1.Columns[4].Width = 150;
string query = "SELECT SUM(vm.price*vm.num) AS price " +
"FROM spares AS sp " +
"INNER JOIN verify_item AS vm ON vm.spares_id = sp.spares_id " +
"WHERE vm.ver_id = @id GROUP BY vm.ver_id ";
MySqlCommand cmdQuery = new MySqlCommand(query, conn);
cmdQuery.Parameters.AddWithValue("@id", ver_id.Text);
conn.Open();
MySqlDataReader dr = cmdQuery.ExecuteReader();
double c_cost = 0;
while (dr.Read())
{
c_cost = dr.GetDouble("price");
}
tb_change.Text = String.Format("{0:#,##0}", c_cost);
hiddenval.Text = c_cost.ToString();
if (c_cost == 0)
{
button1.Enabled = false;
}
else
{
button1.Enabled = true;
}
conn.Close();
}
/// <summary>
/// Retrieves all appliance information from the database.
/// </summary>
/// <param name="callSign">The unique appliance call sign to retrieve the information for.</param>
/// <returns>An container class that stores all the information</returns>
public ApplianceInfo GetApplianceInfo(string callSign)
{
string statement = "SELECT resource.Name, resource.MobilePhoneNumber, " + Environment.NewLine +
"base.id, base.name, base.OfficePhoneNumber," + Environment.NewLine +
"base_address.id, base_address.building, base_address.number, base_address.street, base_address.town, base_address.postcode, base_address.county, base_address.longitude, base_address.latitude," + Environment.NewLine +
"current_address.id, current_address.building, current_address.number, current_address.street, current_address.town, current_address.postcode, current_address.county, current_address.longitude, current_address.latitude," + Environment.NewLine +
"status.code, status.Description, status.IsAvailable, status.IsMobile, status.ResourceLogEnumeration, resource_status.DateTime, resource_status.OperatorId," + Environment.NewLine +
"appliance.OfficerInCharge, appliance.CrewNumber, appliance.BaNumber," + Environment.NewLine +
"appliancetype.Name, appliancetype.Description, resource.AttachedIncidentNumber" + Environment.NewLine +
"FROM resource" + Environment.NewLine +
"INNER JOIN base ON resource.BaseLocationId = base.Id" + Environment.NewLine +
"INNER JOIN address AS base_address ON base.AddressId = base_address.Id" + Environment.NewLine +
"INNER JOIN Address_Resource ON Address_Resource.ResourceCallSign = resource.CallSign" + Environment.NewLine +
"INNER JOIN address AS current_address ON address_resource.AddressId = current_address.Id" + Environment.NewLine +
"INNER JOIN resource_status ON resource_status.ResourceCallSign = resource.CallSign" + Environment.NewLine +
"INNER JOIN status ON resource_status.StatusCode = status.Code" + Environment.NewLine +
"INNER JOIN appliance ON resource.CallSign = appliance.CallSign" + Environment.NewLine +
"INNER JOIN appliancetype ON appliance.ApplianceTypeName = appliancetype.Name" + Environment.NewLine +
"WHERE Address_Resource.DateTime = (SELECT MAX(DateTime) FROM Address_Resource WHERE ResourceCallSign = resource.CallSign)" + Environment.NewLine +
"AND resource_status.DateTime = (SELECT MAX(DateTime) FROM resource_status WHERE ResourceCallSign = resource.CallSign)" + Environment.NewLine +
"AND resource.CallSign = @callsign;";
//object that will execute the query
MySqlCommand command = new MySqlCommand(statement, connection);
command.Parameters.AddWithValue("@callsign", callSign);
//execute
MySqlDataReader myReader = null; //reads the database data
try
{
// open the connection only if it is currently closed
if (command.Connection.State == System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
//close the reader if it currently exists
if (myReader != null && !myReader.IsClosed)
{
myReader.Close();
}
myReader = command.ExecuteReader(); //execute the select statement
//read the data sent back from MySQL server and create the Appliance objects
while (myReader.Read())
{
//read all the data returned from the database - each information area is split into a different region below
string resourceName = myReader.GetString(0);
string mobileNo = myReader.GetString(1);
#region Base Address Data
int baseId = myReader.GetInt32(2);
string baseName = myReader.GetString(3);
string officeNo = myReader.GetString(4);
int baseAddressId = myReader.GetInt32(5);
string baseBuilding = string.Empty;
if (!myReader.IsDBNull(6))
{
baseBuilding = myReader.GetString(6);
}
string baseNumber = string.Empty;
if (!myReader.IsDBNull(7))
{
baseNumber = myReader.GetString(7);
}
string baseStreet = string.Empty;
if (!myReader.IsDBNull(8))
{
baseStreet = myReader.GetString(8);
}
string baseTown = string.Empty;
if (!myReader.IsDBNull(9))
{
baseTown = myReader.GetString(9);
}
string basePostcode = string.Empty;
if (!myReader.IsDBNull(10))
{
basePostcode = myReader.GetString(10);
}
string baseCounty = string.Empty;
if (!myReader.IsDBNull(11))
{
baseCounty = myReader.GetString(11);
}
double baseLong = myReader.GetDouble(12);
double baseLat = myReader.GetDouble(13);
Address baseAddress = new Address(baseAddressId, baseBuilding, baseNumber, baseStreet, baseTown, basePostcode, baseCounty, baseLong, baseLat);
Base baseLocation = new Base(baseId, officeNo, baseAddress, baseName);
#endregion
#region Current Address Data
int currentId = myReader.GetInt32(14);
string currentBuilding = string.Empty;
if (!myReader.IsDBNull(15))
{
currentBuilding = myReader.GetString(15);
}
string currentNumber = string.Empty;
if (!myReader.IsDBNull(16))
{
currentNumber = myReader.GetString(16);
}
string currentStreet = string.Empty;
if (!myReader.IsDBNull(17))
{
currentStreet = myReader.GetString(17);
}
string currentTown = string.Empty;
if (!myReader.IsDBNull(18))
{
currentTown = myReader.GetString(18);
}
string currentPostcode = string.Empty;
if (!myReader.IsDBNull(19))
{
currentPostcode = myReader.GetString(19);
}
string currentCounty = string.Empty;
if (!myReader.IsDBNull(20))
{
currentCounty = myReader.GetString(20);
}
double currentLong = myReader.GetDouble(21);
double currentLat = myReader.GetDouble(22);
Address currentAddress = new Address(currentId, currentBuilding, currentNumber, currentStreet, currentTown, currentPostcode, currentCounty, currentLong, currentLat);
#endregion
#region Status Code Data
int statusCode = -1;
if (!myReader.IsDBNull(23))
{
statusCode = myReader.GetInt32(23);
}
string statusDescription = string.Empty;
if (!myReader.IsDBNull(24))
{
statusDescription = myReader.GetString(24);
}
bool isAvail = false;
if (!myReader.IsDBNull(25))
{
isAvail = myReader.GetBoolean(25);
}
bool isMobile = false;
if (!myReader.IsDBNull(26))
{
isMobile = myReader.GetBoolean(26);
}
int resourceLogEnumeration = -1;
if (!myReader.IsDBNull(27))
{
resourceLogEnumeration = myReader.GetInt32(27);
}
ResourceLogTime incidentLogAction = ResourceLogTime.Ignore;
if (Enum.IsDefined(typeof(ResourceLogTime), resourceLogEnumeration))
{
incidentLogAction = (ResourceLogTime)resourceLogEnumeration;
}
ResourceStatus status = new ResourceStatus(statusCode, statusDescription, isAvail, isMobile, incidentLogAction);
#endregion
#region Appliance Data
string oic = string.Empty;
if (!myReader.IsDBNull(30))
{
oic = myReader.GetString(30);
}
int crew = -1;
if (!myReader.IsDBNull(31))
{
crew = myReader.GetInt32(31);
}
int ba = -1;
if (!myReader.IsDBNull(32))
{
ba = myReader.GetInt32(32);
}
string applianceTypeName = string.Empty;
if (!myReader.IsDBNull(33))
{
applianceTypeName = myReader.GetString(33);
}
string applianceTypeDescription = string.Empty;
if (!myReader.IsDBNull(34))
{
applianceTypeDescription = myReader.GetString(34);
}
int assignedIncident = -1;
if (!myReader.IsDBNull(35))
{
assignedIncident = myReader.GetInt32(35);
}
ApplianceType type = new ApplianceType(applianceTypeName, applianceTypeDescription);
#endregion
return(new ApplianceInfo(resourceName, mobileNo, baseLocation, currentAddress, status, oic, crew, ba, type, assignedIncident));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show(statement);
}
finally
{
//close the connections
if (myReader != null)
{
myReader.Close();
}
command.Connection.Close();
}
return(null);
}
internal void insertarApuesta(ApuestaDTO aps)
{
CultureInfo culInfo = new System.Globalization.CultureInfo("es-ES");
culInfo.NumberFormat.NumberDecimalSeparator = ".";
culInfo.NumberFormat.CurrencyDecimalSeparator = ".";
culInfo.NumberFormat.PercentDecimalSeparator = ".";
culInfo.NumberFormat.CurrencyDecimalSeparator = ".";
System.Threading.Thread.CurrentThread.CurrentCulture = culInfo;
Debug.WriteLine("apuesta vale " + aps);
MySqlConnection con = Connect();
MySqlCommand command = con.CreateCommand();
command.CommandText = "insert into apuestas(tipo_apuesta,cuota,dinero_apostado,mercados_id_mercado,usuarios_id_usuarios) values ('" + aps.Tipo_apuesta + "','" + aps.Cuota +
"','" + aps.Dinero_apostado + "'" + ",'" + aps.Id_mercado + "','" + aps.Email + "')";
Debug.WriteLine("comando " + command.CommandText);
try
{
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch (MySqlException e)
{
Debug.WriteLine("Se ha producido un error de conexión");
con.Close();
}
con.Open();
command.CommandText = "select dinero_over from mercados where id_mercado=" + aps.Id_mercado + "; ";
Debug.WriteLine("COMMAND " + command.CommandText);
MySqlDataReader reader = command.ExecuteReader();
reader.Read();
double dineroOver = reader.GetDouble(0);
reader.Close();
con.Close();
con.Open();
command.CommandText = "select dinero_under from mercados where id_mercado=" + aps.Id_mercado + "; ";
reader = command.ExecuteReader();
reader.Read();
double dineroUnder = reader.GetDouble(0);
reader.Close();
con.Close();
if (aps.Tipo_apuesta == 1)
{
dineroOver = dineroOver + aps.Dinero_apostado;
}
else
{
dineroUnder = dineroUnder + aps.Dinero_apostado;
}
Debug.WriteLine(dineroOver);
Debug.WriteLine(dineroUnder);
double po = dineroOver / (dineroOver + dineroUnder);
double pu = dineroUnder / (dineroUnder + dineroOver);
Debug.WriteLine(po);
Debug.WriteLine(pu);
double co = Convert.ToDouble((1 / po) * 0.95);
double cu = Convert.ToDouble((1 / pu) * 0.95);
if (aps.Tipo_apuesta == 1)
{
command.CommandText = "update mercados set cuota_over = '" + co + "',cuota_under = '" + cu + "', dinero_over = '" + dineroOver + "' where id_mercado =" + aps.Id_mercado + ";";
try
{
con.Open();
command.ExecuteNonQuery();
}
catch (MySqlException e)
{
Console.WriteLine("Error " + e);
}
con.Close();
}
else
{
command.CommandText = "update mercados set cuota_under = '" + cu + "', cuota_over = '" + co + "', dinero_under = '" + dineroUnder + "' where id_mercado =" + aps.Id_mercado + "; ";
try
{
con.Open();
command.ExecuteNonQuery();
}
catch (MySqlException e)
{
Console.WriteLine("Error " + e);
}
con.Close();
}
}
internal List <Mercado> Retrieve(int id)
{
MySqlConnection con = Connect();
MySqlCommand command = con.CreateCommand();
command.CommandText = "select * from mercado where id = " + id;
try
{
con.Open();
MySqlDataReader res = command.ExecuteReader();
Mercado m = null;
List <Mercado> mercados = new List <Mercado>();
while (res.Read())
{
m = new Mercado(res.GetInt32(0), res.GetInt32(1), res.GetInt32(2), res.GetInt32(3), res.GetDouble(4));
mercados.Add(m);
}
con.Close();
return(mercados);
}
catch (MySqlException e)
{
Debug.WriteLine("Error al conectar con la base de datos");
return(null);
}
}
internal List <Usuario> Retrieve()
{
MySqlConnection con = Connect();
MySqlCommand command = con.CreateCommand();
command.CommandText = "select * from usuario";
try
{
con.Open();
MySqlDataReader res = command.ExecuteReader();
Usuario us = null;
List <Usuario> usuarios = new List <Usuario>();
while (res.Read())
{
Debug.WriteLine("Recuperado: " + res.GetInt32(0) + " " + res.GetString(1) + " " + res.GetString(2) + " " + res.GetString(3) + " " + res.GetString(4) + " " + res.GetInt32(5) + " " + res.GetDouble(6));
us = new Usuario(res.GetInt32(0), res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetInt32(5), res.GetDouble(6));
usuarios.Add(us);
}
con.Close();
return(usuarios);
} catch (MySqlException e)
{
Debug.WriteLine("Se ha producido un error de conexión.");
return(null);
}
}
private void CurrentDayData()
{
try
{
var dateTimeToday = DateTime.Today.ToString("yyyy-MM-dd");
MySqlConnection conCDayData = DietTracker.DatabaseConnect.OpenDefaultDBConnection();
MySqlCommand doesDataExistCommand = new MySqlCommand();
doesDataExistCommand.CommandText = "SELECT Date FROM day WHERE UserID = '" + Username + "' AND Date = '" + dateTimeToday + "';";
doesDataExistCommand.Connection = conCDayData;
conCDayData.Open();
MySqlDataReader doesDataExistRead = doesDataExistCommand.ExecuteReader();
if (doesDataExistRead.Read() == true)
{
conCDayData.Close();
}
else
{
try
{
MySqlConnection conI = DietTracker.DatabaseConnect.OpenDefaultDBConnection();
MySqlConnection conR = DietTracker.DatabaseConnect.OpenDefaultDBConnection();
MySqlCommand ReadWeightFromDataDayCommand = new MySqlCommand();
ReadWeightFromDataDayCommand.CommandText =
"SELECT Weight FROM diettracker.day WHERE UserID = '" + Username + "' AND Date >= COALESCE((SELECT Date FROM diettracker.day ORDER BY Date DESC LIMIT 1),(SELECT MAX(Date) FROM diettracker.day));";
ReadWeightFromDataDayCommand.Connection = conR;
conR.Open();
MySqlDataReader readWeight = ReadWeightFromDataDayCommand.ExecuteReader();
readWeight.Read();
string weight = readWeight.GetDouble(0).ToString(CultureInfo.InvariantCulture);
conR.Close();
MySqlCommand insertDataCommand = new MySqlCommand();
insertDataCommand.CommandText =
"INSERT INTO day (Date, Weight, Calories, UserID)" +
"VALUES ('" + dateTimeToday + "', '" + weight + "', 0, '" + Username + "');";
insertDataCommand.Connection = conI;
conI.Open();
insertDataCommand.ExecuteNonQuery();
conI.Close();
}
catch
{
MessageBox.Show("Couldn't insert data for new day on login");
}
}
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server.");
break;
case 1062:
MessageBox.Show("That Username is already in use");
break;
case 1264:
MessageBox.Show("You can't be higher than 255 cm (Come on, you're not that tall)");
break;
}
}
}
public void copiaRegistroExistencia()
{
try
{
DateTime fecha = DateTime.Now;
List <Existencia> existencia = new List <Existencia>();
using (var conexion = new MySqlConnection(datasource))
{
conexion.Open();
query = "SELECT idRegistro FROM existencia where fecha = current_date() limit 1;";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return;
}
}
}
}
using (var conexion = new MySqlConnection(datasource))
{
conexion.Open();
query = "SELECT fecha FROM existencia order by idRegistro limit 1;";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
fecha = reader.GetDateTime("fecha");
}
}
}
}
using (var conexion = new MySqlConnection(datasource))
{
conexion.Open();
query = "SELECT idProducto, cantidad FROM existencia WHERE fecha = ?fecha;";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
cmd.Parameters.AddWithValue("?fecha", fecha);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Existencia registro = new Existencia();
registro.IdProducto = reader.GetInt32("idProducto");
registro.Cantidad = reader.GetDouble("cantidad");
registro.Fecha = DateTime.Now;
existencia.Add(registro);
}
}
}
}
using (var conexion = new MySqlConnection(datasource))
{
conexion.Open();
using (MySqlTransaction tx = conexion.BeginTransaction())
{
try
{
foreach (Existencia registro in existencia)
{
guardaRegistroExistencia(conexion, registro);
}
tx.Commit();
}
catch (MySqlException e)
{
tx.Rollback();
Console.WriteLine(e.ToString());
}
}
}
}
catch (MySqlException e)
{
Console.WriteLine(e.ToString());
}
}
public static void getTransactions(int id, int account_id)
{
connection.Open();
try
{
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM transactions WHERE from_user_id = @id OR to_user_id = @id AND from_account_id = @account_id OR to_account_id = @account_id";
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@account_id", account_id);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UserWindow.items.Add(new Account()
{
Account_id = reader.GetInt32("id"), Account_name = reader.GetString("name"), Money_amount = reader.GetDouble("amount")
});
}
}
}
catch (Exception)
{
throw;
}
connection.Close();
}
public int guardaRegistroFrio(Existencia salida)
{
int ans = -1, id_registro = 0;
Double existencia_cantidad = 0;
try
{
using (var conexion = new MySqlConnection(datasource))
{
conexion.Open();
query = "SELECT idRegistro, cantidad FROM existencia WHERE idProducto = ?idProducto AND fecha = ?fecha;";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
cmd.Parameters.AddWithValue("?idProducto", salida.IdProducto);
cmd.Parameters.AddWithValue("?fecha", DateTime.Now.Date);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
id_registro = reader.GetInt32("idRegistro");
existencia_cantidad = reader.GetDouble("cantidad");
}
else
{
conexion.Close();
return(0);
}
}
}
existencia_cantidad -= salida.Cantidad;
if (existencia_cantidad < 0)
{
conexion.Close();
return(1);
}
query = "UPDATE existencia SET cantidad = ?cantidad WHERE idRegistro = ?idRegistro;";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
cmd.Parameters.AddWithValue("?cantidad", existencia_cantidad);
cmd.Parameters.AddWithValue("?idRegistro", id_registro);
cmd.ExecuteNonQuery();
}
query = "INSERT INTO frio(idProducto,fecha,cantidad) VALUES(?idProducto,?fecha,?cantidad);";
using (MySqlCommand cmd = new MySqlCommand(query, conexion))
{
cmd.Parameters.AddWithValue("?idProducto", salida.IdProducto);
cmd.Parameters.AddWithValue("?fecha", salida.Fecha);
cmd.Parameters.AddWithValue("?cantidad", salida.Cantidad);
cmd.ExecuteNonQuery();
ans = 2;
}
}
}
catch (MySqlException e)
{
e.ToString();
ans = -1;
}
return(ans);
}
public Winkel GetWinkel(string winkelnaam)
{
this.ResetErrorMessage();
Winkel winkel = new Winkel();
try
{
this.MySqlConnection.Open();
string sql = $"SELECT winkelnr, naam, beheerder, actief, goedgekeurd, adress FROM tblwinkel WHERE naam = @naam;";
MySqlCommand command = new MySqlCommand(sql, this.MySqlConnection);
command.Parameters.AddWithValue("@naam", winkelnaam);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
winkel.winkelnr = reader.GetInt32(0);
winkel.naam = reader.GetString(1);
winkel.beheerder = reader.GetString(2);
winkel.actief = reader.GetBoolean(3);
winkel.goedgekeurd = reader.GetBoolean(4);
winkel.adress = reader.GetString(5);
}
//Reader sluiten
reader.Close();
}
catch (MySqlException ex)
{
//Bij een error word de ToString van die error op ErrorMessage gezet zodat dit gebruikt kan worden, voornamelijk tijdens het developen
this.ErrorMessage = ex.ToString();
}
//Connectie sluiten
this.MySqlConnection.Close();
List <Artikel> artikels = new List <Artikel>();
Artikel artikel;
try
{
this.MySqlConnection.Open();
string sql = $"SELECT productnr, productnaam, prijs, stock, winkelnr, korting, actief FROM tblartikel WHERE winkelnr = @winkelnr;";
MySqlCommand command = new MySqlCommand(sql, this.MySqlConnection);
command.Parameters.AddWithValue("@winkelnr", winkel.winkelnr);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
artikel = new Artikel
{
productnr = reader.GetInt32(0),
productnaam = reader.GetString(1),
standaardPrijs = reader.GetDouble(2),
stock = reader.GetInt32(3),
winkelnr = reader.GetInt32(4),
korting = reader.GetInt32(5),
actief = (reader.GetInt32(6) == 1) ? true : false
};
artikels.Add(artikel);
}
//Reader sluiten
reader.Close();
}
catch (MySqlException ex)
{
//Bij een error word de ToString van die error op ErrorMessage gezet zodat dit gebruikt kan worden, voornamelijk tijdens het developen
this.ErrorMessage = ex.ToString();
}
//Connectie sluiten
this.MySqlConnection.Close();
winkel.artikels = artikels;
//Return object
return(winkel);
}
static void Hämta_Konton()
{
string query = "SELECT * FROM banktest.konto where användarnamn = '" + Program.user.användarnamn + "';";
DBConnection dBConnection = DBConnection.Instance();
if (dBConnection.IsConnect())
{
MySqlCommand cmd = new MySqlCommand(query, dBConnection.Connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
konton.Add(new Konto(reader.GetInt16("idKonto"), reader.GetString("namn"), reader.GetDouble("saldo"), reader.GetString("användarnamn")));
}
reader.Close();
}
}
/// <summary>
/// 获取内容模板信息
/// </summary>
/// <param name="url"></param>
/// <param name="isMobile"></param>
void GetContentTemplate(string url, bool isMobile)
{
//url = url.Replace("." + BaseConfig.extension, "");
double dataTypeId = 0, rootId = 0, moduleId = 0, skinId = 0, id = 0, classId = 0;
TableHandle table = new TableHandle("maintable");
Dictionary <string, object> p = new Dictionary <string, object>();
p.Add("url", url);
Dictionary <string, object> variable = table.GetModel("dataTypeId,classId,id,skinId,moduleId, rootId", "url=@url and orderId>-1", p);
if (variable == null)
{
throw new Exception("访问数据不存在");
}
moduleId = (double)variable["moduleId"];
rootId = (double)variable["rootId"];
dataTypeId = (double)variable["dataTypeId"];
skinId = (double)variable["skinId"];
id = (double)variable["id"];
classId = (double)variable["classId"];
MySqlDataReader rs = null;
if (skinId == 0)
{
rs = Sql.ExecuteReader("select " + (isMobile ? "_contentSkinID" : "contentSkinID") + " from class where id=@id", new MySqlParameter[] { new MySqlParameter("id", classId) });
if (rs.Read())
{
skinId = rs.IsDBNull(0) ? 0 : rs.GetDouble(0);
}
rs.Close();
}
DAL.Datatype.TableHandle T = new DAL.Datatype.TableHandle(dataTypeId);
variable = T.GetModel(id);
variable["attribute"] = "";
/*
* string tableName = (string)Helper.Sql.ExecuteScalar("select tableName from datatype where id=" + dataTypeId.ToString());
* rs = Helper.Sql.ExecuteReader("select * from " + tableName + " where id=@id", new MySqlParameter[] {
* new MySqlParameter("id",id)});
* if (rs.Read())
* {
* for (int i = 0; i < rs.FieldCount; i++)
* {
* string fieldName = rs.GetName(i);
*
* if (rs.IsDBNull(i))
* {
* variable[rs.GetName(i)] = "";
* }
* else
* {
* if (rs.GetDataTypeName(rs.GetOrdinal(fieldName)) == "ntext")
* {
* //SystemLink v1 = new SystemLink();
* string FieldValue = rs[i] + "";
* //FieldValue = v1.Replace(FieldValue);
* variable[fieldName] = FieldValue;
* }
* else
* {
* variable[fieldName] = rs[i];
* }
* }
* }
* }
* rs.Close();*/
this.Variable = variable;
if (skinId > 0)
{
try
{
Get(skinId);
}
catch
{
Get(moduleId, rootId, 2, dataTypeId, isMobile);
}
}
else
{
Get(moduleId, rootId, 2, dataTypeId, isMobile);
}
}
public void buscarDatosNC(string fe1, string fe2)
{
try
{
cn.Open();
cn2.Open();
date1 = fe1;
date2 = fe2;
string date = Convert.ToDateTime((date1)).ToString("yyyy/MM/dd");
string dateDos = Convert.ToDateTime((date2)).ToString("yyyy/MM/dd");
string mysql = "Select plex.comprascabecera.IDComprobante, plex.comprascabecera.tipo, plex.comprascabecera.IDProveedor, plex.comprascabecera.Letra, plex.comprascabecera.PuntoVta, plex.comprascabecera.Numero, EXTRACT(MONTH FROM plex.comprascabecera.FechaEmision),EXTRACT(YEAR FROM plex.comprascabecera.FechaEmision),plex.comprascabecera.FechaEmision, plex.comprascabecera.FechaImputacion, plex.comprascabecera.CUIT, plex.comprascabecera.Estado, plex.comprasdetalle.sucursal,plex.comprasdetalle.IVAAlicuota,plex.comprasdetalle.NetoExento, plex.comprascabecera.IDTipoGasto, plex.comprasdetalle.NetoNoGravado, plex.comprasdetalle.PercepDGR,plex.comprasdetalle.IVAImporte, plex.comprasdetalle.NetoGravado FROM plex.comprascabecera INNER JOIN plex.comprasdetalle ON plex.comprascabecera.IDComprobante = plex.comprasdetalle.IDComprobante WHERE comprascabecera.fechaEmision between '" + date + "' and '" + dateDos + "' AND CUIT<> '' and PuntoVta between 0 and 30000 Having Tipo='OP' OR Tipo='NC'";
MySqlDataReader reader = null;
MySqlCommand comando = new MySqlCommand(mysql, cn2);
reader = comando.ExecuteReader();
List <string> lista = new List <string>();
if (reader.HasRows)
{
while (reader.Read())
{
//GUARDO LOS RESULTADOS DE LA CONSULTA A LOS ATRIBUTOS
this.idcomprobante = reader.GetInt64(0);
this.tipo = reader.GetString(1);
this.idproveedor = reader.GetInt32(2);
this.letra = reader.GetChar(3);
this.ptovta = reader.GetInt32(4);
this.nro = reader.GetInt64(5);
this.mes = reader.GetInt32(6);
this.anio = reader.GetInt32(7);
this.fechaemi = reader.GetString(8);
this.fechaimpu = reader.GetString(9);
this.cuit = reader.GetString(10);
this.estado = reader.GetString(11);
this.sucursal = reader.GetInt32(12);
this.AliIVA = reader.GetInt32(13);
this.M_exe = reader.GetDouble(14);
this.tipoGasto = reader.GetInt32(15);
this.M_sb = reader.GetDouble(16);
this.M_perIB = reader.GetDouble(17);
this.M_iva = reader.GetDouble(18);
this.NetoGravado = reader.GetDouble(19);
string id = "";
string ti = tipo;
string mod = modulo;
string CodComp = Cod_comp;
int socio = idproveedor;
char le = letra;
int pvta = ptovta;
long nro_comp = nro;
string fecha = fechaemi;
string feContable = fechaimpu;
string nroDoc = this.cuit;
int suc = this.sucursal;
int tipoG = this.tipoGasto;
string tipOperacion = "ALTA";
string fechaHoy = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
string Cod_equipo = Dns.GetHostName();
feHoy = fechaHoy;
CodEquipo = Cod_equipo;
tipoOpe = tipOperacion;
long IDComprobante = idcomprobante;
System.Guid miGUID = System.Guid.NewGuid();
id = miGUID.ToString();
if (M_sb > 0)
{
AliIVA = 21;
}
else if (M_sb == 0)
{
AliIVA = 0;
}
double aliVa = this.AliIVA;
double Mex = this.M_exe;
int tipG = this.tipoGasto;
double sb = this.M_sb;
double per = this.M_perIB;
double iv = this.M_iva;
double NetoGra = this.NetoGravado;
total2 = Mex + sb + per + iv + NetoGra;
calculo(IDComprobante);
//calculoItems(IDComprobante);
insertarIMmovimientos(id, ti, mod, CodComp, socio, le, pvta, nro_comp, nroDoc, suc, fecha, feContable, total, 0, tipoG, aliVa, Mex, tipG, sb, per, iv, NetoGra, total2);
}
reader.Close();
}
else
{
//MessageBox.Show("No se encontraron Notas de Crédito", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
Console.WriteLine("\nComprobantes agregados: " + this.contNC);
}
catch (MySqlException ex)
{
string numeroLinea = ex.StackTrace.Substring(ex.StackTrace.Length - 4, 4);
MessageBox.Show("Error al buscar " + ex.Message + " Linea: " + numeroLinea, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
cn2.Close();
}
}
public void testReport(string qry)
{
DataTable table = new DataTable();
MySqlDataReader reader = null;
table.Columns.Add("Project_Name", typeof(string));
table.Columns.Add("Client_Name", typeof(string));
table.Columns.Add("No_of_Events", typeof(int));
table.Columns.Add("Expense", typeof(double));
try
{
reader = DBConnection.getData(qry);
if (reader.HasRows)
{
while (reader.Read())
{
table.Rows.Add(reader.GetString("proj_name"), reader.GetString("name"), reader.GetInt32("nos"), reader.GetDouble("exp"));
}
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
}
//MonthlyProjectExpReport rpt = new MonthlyProjectExpReport();
ProjectProfileTestTwoReport rpt = new ProjectProfileTestTwoReport();
rpt.Database.Tables["ProjExp"].SetDataSource(table);
crvProjExpMainViewer.ReportSource = null;
crvProjExpMainViewer.ReportSource = rpt;
}
else
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
}
MessageBox.Show("No records yet!", "Project Expense Reporting", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception exc)
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
}
MessageBox.Show("No records yet!\n" + exc, "Project Expense Reporting", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public bool CreateInvoice(Reservation reservation)
{
if (OpenConnection() == false)
{
return(false);
}
string req = "SELECT * FROM invoice WHERE id_Reservation = @id;";
MySqlCommand mySqlCommand = new MySqlCommand(req, mySqlConnection);
mySqlCommand.Parameters.Add(new MySqlParameter("@id", reservation.Id));
MySqlDataReader reader = mySqlCommand.ExecuteReader();
if (reader.HasRows)
{
return(false);
}
reader.Close();
req = "INSERT INTO invoice (priceHT, priceTTC, state, date, id_Reservation) VALUES (0, 0, @state, @date, @id)";
MySqlCommand mySqlCommand2 = new MySqlCommand(req, mySqlConnection);
mySqlCommand2.Parameters.Add(new MySqlParameter("@state", "A payer"));
mySqlCommand2.Parameters.Add(new MySqlParameter("@date", DateTime.Now));
mySqlCommand2.Parameters.Add(new MySqlParameter("@id", reservation.Id));
int res = mySqlCommand2.ExecuteNonQuery();
int idInvoice = (int)mySqlCommand2.LastInsertedId;
foreach (BedroomViewModel bvm in reservation.ListBedroom)
{
AddLineInvoice("Chambre - " + bvm.Bedroom.Number + " " + bvm.Bedroom.TypeBedroom.Name, bvm.Bedroom.TypeBedroom.Price / 1.20, bvm.Bedroom.TypeBedroom.Price, idInvoice);
}
foreach (OptionViewModel ovm in reservation.ListOptions)
{
AddLineInvoice("Option - " + ovm.Option.Name, ovm.Option.Price / 1.20, ovm.Option.Price, idInvoice);
}
foreach (MealViewModel mvm in reservation.ListMeal)
{
AddLineInvoice("Repas - " + mvm.Meal.Name + "-" + mvm.Meal.Date, mvm.Meal.Price / 1.20, mvm.Meal.Price, idInvoice);
}
double priceHT = 0;
double priceTTC = 0;
req = "SELECT SUM(pht) AS sumpht, SUM(pttc) AS sumpttc FROM line_invoice WHERE id_invoice = @id";
MySqlCommand mySqlCommand3 = new MySqlCommand(req, mySqlConnection);
mySqlCommand3.Parameters.Add(new MySqlParameter("@id", idInvoice));
MySqlDataReader reader2 = mySqlCommand3.ExecuteReader();
while (reader2.Read())
{
priceHT = reader2.GetDouble("sumpht");
priceTTC = reader2.GetDouble("sumpttc");
}
reader2.Close();
req = "UPDATE invoice SET priceHT = @sumpht , priceTTC = @sumpttc WHERE id = @id;";
MySqlCommand mySqlCommand4 = new MySqlCommand(req, mySqlConnection);
mySqlCommand4.Parameters.Add(new MySqlParameter("@sumpht", priceHT));
mySqlCommand4.Parameters.Add(new MySqlParameter("@sumpttc", priceTTC));
mySqlCommand4.Parameters.Add(new MySqlParameter("@id", idInvoice));
mySqlCommand4.ExecuteNonQuery();
CloseConnection();
return(true);
}
public List <Car_Leasings_Payment> getPaymentSchedule(string Leasing_id)
{
MySqlConnection con = MySQLConnection.connectionMySQL();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand("g_car_leasings_payment", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@i_Leasing_id", Leasing_id);
MySqlDataReader reader = cmd.ExecuteReader();
List <Car_Leasings_Payment> list_cls_pay = new List <Car_Leasings_Payment>();
while (reader.Read())
{
Car_Leasings_Payment cls_pay = new Car_Leasings_Payment();
int defaultNum = 0;
string defaultString = "";
cls_pay.Leasing_id = reader.IsDBNull(0) ? defaultString : reader.GetString(0);
cls_pay.Period_no = reader.IsDBNull(1) ? defaultNum : reader.GetInt32(1);
cls_pay.Period_schedule = reader.IsDBNull(2) ? defaultString : reader.GetString(2);
cls_pay.Period_current = reader.IsDBNull(3) ? defaultNum : reader.GetDouble(3);
cls_pay.Period_cash = reader.IsDBNull(4) ? defaultNum : reader.GetDouble(4);
cls_pay.Period_value = reader.IsDBNull(5) ? defaultNum : reader.GetDouble(5);
cls_pay.Period_principle = reader.IsDBNull(6) ? defaultNum : reader.GetDouble(6);
cls_pay.Period_interst = reader.IsDBNull(7) ? defaultNum : reader.GetDouble(7);
cls_pay.Period_vat = reader.IsDBNull(8) ? defaultNum : reader.GetDouble(8);
cls_pay.Period_fine = reader.IsDBNull(9) ? defaultNum : reader.GetDouble(9);
cls_pay.Total_Period_fine = reader.IsDBNull(10) ? defaultNum : reader.GetDouble(10);
cls_pay.Total_Period_left = reader.IsDBNull(11) ? defaultNum : reader.GetDouble(11);
cls_pay.Period_payment_status = reader.IsDBNull(12) ? defaultNum : reader.GetInt32(12);
cls_pay.Period_Save_Date = reader.IsDBNull(13) ? defaultString : reader.GetString(13);
list_cls_pay.Add(cls_pay);
}
return(list_cls_pay);
}
catch (MySqlException ex)
{
error = "MysqlException ==> Managers_Leasings --> Car_Leasing_Payment_Manager --> getPaymentSchedule() ";
Log_Error._writeErrorFile(error, ex);
return(null);
}
catch (Exception ex)
{
error = "Exception ==> Managers_Leasings --> Car_Leasing_Payment_Manager --> getPaymentSchedule() ";
Log_Error._writeErrorFile(error, ex);
return(null);
}
finally
{
con.Close();
}
}
void GeneratePie()
{
chart1.Titles.Add("Nutrient Proportion");
try
{
con1.Open();
string query = "Select * from dietcharttoday where userid=" + userid;
MySqlCommand cmd = new MySqlCommand(query, con1);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
c = dr.GetDouble("c");
p = dr.GetDouble("p");
v = dr.GetDouble("v");
f = dr.GetDouble("f");
o = (dr.IsDBNull(5)) ? 0.00 : dr.GetDouble("o");
calToday = dr.GetDouble("cal");
}
dr.Close();
}
catch (Exception es)
{
MessageBox.Show(es.Message, "Error in DB", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
try
{
string query = "select GoalCalorieIntake from user where userid=" + userid;
MySqlCommand cmd = new MySqlCommand(query, con1);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
goals = dr.GetDouble(0);
}
}
catch (Exception es)
{
MessageBox.Show(es.Message, "Error in DB", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
con1.Close();
chart1.Series[0].IsValueShownAsLabel = false;
//chart1.Series[0].IsXValueIndexed = true;
chart1.Series[0].Points.AddXY("Carbo", c);
chart1.Series[0].Points.AddXY("Protein", p);
chart1.Series[0].Points.AddXY("Vit", v);
chart1.Series[0].Points.AddXY("Fat", f);
chart1.Series[0].Points.AddXY("Others", o);
progressBar1.Maximum = Convert.ToInt32(goals);
int calDis = Convert.ToInt32(calToday);
DESC.Text = "Consumed " + calToday.ToString() + " out of " + goals.ToString();
if (calDis > progressBar1.Maximum)
{
progressBar1.Value = progressBar1.Maximum;
MessageBox.Show("You Have EATEN EXCESS! ", "More cal today", MessageBoxButtons.OK, MessageBoxIcon.Information);
DESC.Text += "You have EATEN EXCESS";
}
else
{
progressBar1.Value = calDis;
}
}