public bool updateInputData(DateTime Id, InputData inputDataToSave, String DeviceId)
{
InputData myInputData = new InputData();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
String sqlString = "SELECT * FROM InputData WHERE Date = '" + Id.ToString("yyyy-MM-dd") + "' AND DeviceId='" + inputDataToSave.DeviceId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
sqlString = "UPDATE InputData Set Date='"
+ inputDataToSave.Date.ToString("yyyy-MM-dd") + "', ActiveMinutesReached= "
+ inputDataToSave.ActiveMinutesReached + ", StepsReached="
+ inputDataToSave.StepsReached + ", DeviceId='"
+ inputDataToSave.DeviceId + "' WHERE Date = '"
+ Id.ToString("yyyy-MM-dd") + "' AND DeviceId='" + inputDataToSave.DeviceId + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
mySqlReader.Close();
sqlString = "INSERT INTO InputData (Date, ActiveMinutesReached, StepsReached, DeviceId) VALUES ('"
+ inputDataToSave.Date.ToString("yyyy-MM-dd") + "',"
+ inputDataToSave.ActiveMinutesReached + ","
+ inputDataToSave.StepsReached + ",'"
+ inputDataToSave.DeviceId + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
}
/// <summary>
/// gets the creation Query for the given
/// Stored procedure
/// </summary>
/// <param name="proc"></param>
/// <param name="database"></param>
private void getCreate(StoredProcedure proc, MysqlHandler database)
{
if (database.isConnected())
{
MySql.Data.MySqlClient.MySqlDataReader res = database.sql_select(MysqlProcedures.readProcStruct.Replace("[NAME]", proc.name));
if (res != null)
{
while (res.Read() && res.FieldCount > 0)
{
string getResult;
try
{
getResult = res.GetString(MysqlProcedures.createCodeIndex);
}
catch (System.Data.SqlTypes.SqlNullValueException nullEx)
{
getResult = null;
}
if (null != getResult)
{
proc.created = getResult;
res.Close();
}
return;
}
res.Close();
}
}
else
{
throw new Exception("Database must be connected");
}
}
internal static CustomerOutStandingInvoiceItem FindCOIItemlistForReceipt(MySql.Data.MySqlClient.MySqlCommand cmd, int supinvItemID)
{
cmd.CommandText = CustomerOutStandingInvoiceItem.GetByIDSQL(supinvItemID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
CustomerOutStandingInvoiceItem result = CustomerOutStandingInvoiceItem.TransformReader(r);
r.Close();
cmd.CommandText = CustomerOutStandingInvoice.GetByIDSQL(result.EVENT_JOURNAL.ID);
r = cmd.ExecuteReader();
result.EVENT_JOURNAL = CustomerOutStandingInvoice.TransformReader(r);
r.Close();
cmd.CommandText = Currency.GetByIDSQLStatic(result.CURRENCY.ID);
r = cmd.ExecuteReader();
result.CURRENCY = Currency.GetCurrency(r);
r.Close();
cmd.CommandText = TermOfPayment.GetByIDSQLStatic(result.TOP.ID);
r = cmd.ExecuteReader();
result.TOP = TermOfPayment.GetTOP(r);
r.Close();
cmd.CommandText = Employee.GetByIDSQLStatic(result.EMPLOYEE.ID);
r = cmd.ExecuteReader();
result.EMPLOYEE = Employee.GetEmployee(r);
r.Close();
return(result);
}
internal static SupplierInvoiceJournalItem FindSIJournalItemlistForPayment(MySql.Data.MySqlClient.MySqlCommand cmd, int supinvItemID)
{
cmd.CommandText = SupplierInvoiceJournalItem.GetByIDSQL(supinvItemID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
SupplierInvoiceJournalItem result = SupplierInvoiceJournalItem.TransformReader(r);
r.Close();
cmd.CommandText = SupplierInvoiceJournal.GetByIDSQL(result.EVENT_JOURNAL.ID);
r = cmd.ExecuteReader();
result.EVENT_JOURNAL = SupplierInvoiceJournal.TransformReader(r);
r.Close();
cmd.CommandText = Currency.GetByIDSQLStatic(result.CURRENCY.ID);
r = cmd.ExecuteReader();
result.CURRENCY = Currency.GetCurrency(r);
r.Close();
cmd.CommandText = TermOfPayment.GetByIDSQLStatic(result.TOP.ID);
r = cmd.ExecuteReader();
result.TOP = TermOfPayment.GetTOP(r);
r.Close();
cmd.CommandText = Employee.GetByIDSQLStatic(result.EMPLOYEE.ID);
r = cmd.ExecuteReader();
result.EMPLOYEE = Employee.GetEmployee(r);
r.Close();
return(result);
}
protected override Event doGet(int ID)
{
m_command.CommandText = CustomerInvoice.GetByIDSQL(ID);
MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader();
CustomerInvoice st = CustomerInvoice.TransformReader(r);
r.Close();
m_command.CommandText = CustomerInvoiceItem.GetByEventIDSQL(ID);
r = m_command.ExecuteReader();
IList stis = CustomerInvoiceItem.TransformReaderList(r);
r.Close();
foreach (CustomerInvoiceItem sti in stis)
{
sti.EVENT = st;
sti.PART = PartRepository.GetByID(m_command, sti.PART.ID);
sti.STOCK_CARD_ENTRY = StockCardEntryRepository.FindStockCardEntryByEventItem(m_command, sti.ID, sti.STOCK_CARD_ENTRY_TYPE);
if (sti.DO_ITEM.ID > 0)
{
sti.DO_ITEM = DeliveryOrderRepository.FindDeliveryOrderItem(m_command, sti.DO_ITEM.ID);
}
st.EVENT_ITEMS.Add(sti);
}
return(st);
}
public Target getTarget(DateTime Id, String DeviceId)
{
Target target = new Target();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
String sqlString = "SELECT * FROM Target WHERE Date = '" + Id.ToString("yyyy-MM-dd") + "' AND DeviceId='" + DeviceId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
target.Date = mySqlReader.GetDateTime(0);
target.ActiveMinutes = mySqlReader.GetInt32(1);
target.Steps = mySqlReader.GetInt32(2);
target.DeviceId = mySqlReader.GetString(3);
mySqlReader.Close();
return(target);
}
else
{
mySqlReader.Close();
return(null);
}
}
protected override EventJournal doGet(int ID)
{
m_command.CommandText = ARCreditNote.GetByIDSQL(ID);
MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader();
ARCreditNote st = ARCreditNote.TransformReader(r);
r.Close();
m_command.CommandText = ARCreditNoteItem.GetByEventIDSQL(ID);
r = m_command.ExecuteReader();
IList stis = ARCreditNoteItem.TransformReaderList(r);
r.Close();
foreach (ARCreditNoteItem sti in stis)
{
sti.EVENT_JOURNAL = st;
//sti.VENDOR = PartRepository.GetByID(m_command, sti.PART.ID);
sti.VENDOR_BALANCE_ENTRY = VendorBalanceEntryRepository.FindVendorBalanceEntryByEventItem(m_command, sti.ID, sti.VENDOR_BALANCE_ENTRY_TYPE);
//sti.GRN_ITEM = GoodReceiveNoteRepository.FindGoodReceiveNoteItem(m_command, sti.GRN_ITEM.ID);
//sti.GRN_ITEM.PART = PartRepository.GetByID(m_command, sti.GRN_ITEM.PART.ID);
if (sti.SALES_RETURN != null)
{
if (sti.SALES_RETURN.ID > 0)
{
sti.SALES_RETURN = SalesReturnRepository.GetSalesReturnForCreditNote(m_command, sti.SALES_RETURN);
}
}
st.EVENT_JOURNAL_ITEMS.Add(sti);
}
return(st);
}
public int updateClient(int id, Client client)
{
MySql.Data.MySqlClient.MySqlDataReader sqlReader = null;
string sqlString = "SELECT * FROM clientes WHERE Cedula=" + id.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
if (client.cedula != id)
{
sqlReader.Close();
sqlString = "SELECT * FROM clientes WHERE Cedula=" + client.cedula.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
return(409);
}
}
sqlReader.Close();
sqlString = "UPDATE clientes SET Cedula=" + client.cedula.ToString() + ",Nombre='" + client.name + "',Apellidos='" + client.lastName + "',Provincia='" + client.province + "',Canton='" + client.canton + "',Distrito='" + client.district + "',Direccion='" + client.address + "',Telefono=" + client.phoneN.ToString() + ",Fecha_Nacimiento='" + client.birthDate.ToString("yyyy-MM-dd HH:mm:ss") + "',Usuario='" + client.userName + "',Password='******' WHERE Cedula=" + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
return(200);
}
return(404);
}
public int createCategory(Category category)
{
MySql.Data.MySqlClient.MySqlDataReader sqlReader = null;
string sqlString = "SELECT * FROM categorias WHERE ID=" + category.ID;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
return(409);
}
sqlReader.Close();
sqlString = "SELECT * FROM categorias WHERE Nombre='" + category.name + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
return(409);
}
sqlReader.Close();
sqlString = "INSERT INTO categorias (ID,Nombre) VALUES (" + category.ID.ToString() + ",'" + category.name + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
return(200);
}
public new IEntity GetById(IEntity en)
{
try
{
OpenConnection();
GeneralSetup e = (GeneralSetup)en;
MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(e.GetByIDSQL(e.GetID()), m_connection);
MySql.Data.MySqlClient.MySqlDataReader aReader = aCommand.ExecuteReader();
GeneralSetup a = (GeneralSetup)e.Get(aReader);
aReader.Close();
a.START_ENTRY_PERIOD = PeriodRepository.FindPeriod(aCommand, a.START_ENTRY_PERIOD.ID);
aCommand.CommandText = AutoNumberSetup.GetAllSQLStatic();
aReader = aCommand.ExecuteReader();
IList lst = AutoNumberSetup.GetAllStatic(aReader);
aReader.Close();
foreach (AutoNumberSetup s in lst)
{
a.AUTONUMBER_LIST.Add(s.FORM_CODE, s);
}
return(a);
}
catch (Exception x)
{
throw new Exception(getErrorMessage(x));
}
finally
{
m_connection.Close();
}
}
public bool updateTarget(DateTime Id, Target targetToSave, String DeviceId)
{
Target target = new Target();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
String sqlString = "SELECT * FROM Target WHERE Date = '" + targetToSave.Date.ToString("yyyy-MM-dd") + "' AND DeviceId='" + targetToSave.DeviceId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
sqlString = "UPDATE Target Set Date='"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "', ActiveMinutes= "
+ targetToSave.ActiveMinutes + ", Steps="
+ targetToSave.Steps + ", DeviceId='"
+ targetToSave.DeviceId + "' WHERE Date = '"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "' AND DeviceId='" + targetToSave.DeviceId + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
mySqlReader.Close();
DateTime targetDateDayBefore = targetToSave.Date.AddDays(-1);
Target targetYesterday = getTarget(targetDateDayBefore, targetToSave.DeviceId);
if (targetYesterday != null)
{
mySqlReader.Close();
sqlString = "INSERT INTO Target (Date, ActiveMinutes, Steps, DeviceId) VALUES ('"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "',"
+ targetYesterday.ActiveMinutes + ","
+ targetYesterday.Steps + ",'"
+ targetYesterday.DeviceId + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
//String id = cmd.LastInsertedId.ToString();
return(true);
}
else if (targetYesterday == null)
{
mySqlReader.Close();
sqlString = "INSERT INTO Target (Date, ActiveMinutes, Steps, DeviceId) VALUES ('"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "',"
+ targetToSave.ActiveMinutes + ","
+ targetToSave.Steps + ",'"
+ targetToSave.DeviceId + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
//String id = cmd.LastInsertedId.ToString();
return(true);
}
else
{
return(false);
}
}
}
public string updateAffiliationForm(int id, string statusComment)
{
String[] elements = statusComment.Split(':');
string status = elements[0];
string comment = elements[1];
MySql.Data.MySqlClient.MySqlDataReader sqlReader = null;
String sqlString = "SELECT * FROM afiliaciones WHERE cedula=" + id.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
if (status.Equals("READED"))
{
sqlReader.Close();
sqlString = "DELETE FROM afiliaciones WHERE cedula=" + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
}
else if (status.Equals("ACCEPTED"))
{
sqlReader.Close();
sqlString = "SELECT * FROM afiliaciones WHERE cedula=" + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
sqlReader = cmd.ExecuteReader();
sqlReader.Read();
AffilliationForm form = new AffilliationForm(sqlReader.GetInt32(0), sqlReader.GetString(1), sqlReader.GetString(2), sqlReader.GetString(12), sqlReader.GetString(3), sqlReader.GetString(4), sqlReader.GetString(5), sqlReader.GetString(6), sqlReader.GetInt32(7), sqlReader.GetDateTime(8), sqlReader.GetInt32(9), sqlReader.GetString(10), sqlReader.GetString(11), sqlReader.GetString(13));
sqlReader.Close();
sqlString = "DELETE FROM afiliaciones WHERE cedula=" + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
sqlString = "INSERT INTO productores (Cedula,Nombre,Apellidos,nombreNegocio,Provincia,Canton,Distrito,Direccion,Telefono,Fecha_Nacimiento,Num_Sinpe,Lugares_Entrega,Calificacion,Password) VALUES (" + form.cedula.ToString() + ",'" + form.name + "','" + form.lastName + "','" + form.businessName + "','" + form.province + "','" + form.canton + "','" + form.district + "','" + form.address + "'," + form.phoneN.ToString() + ",'" + form.birthDate.ToString("yyyy-MM-dd HH:mm:ss") + "'," + form.sinpeN.ToString() + ",'',5,'" + form.password + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
string token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
sqlString = "INSERT INTO tokens (Usuario,Token,Tipo) VALUES (" + form.cedula.ToString() + ",'" + token + "','productores')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
}
else
{
sqlReader.Close();
sqlString = "UPDATE afiliaciones SET Comentario='" + comment + "',Estado='" + status + "' WHERE cedula=" + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connection);
cmd.ExecuteNonQuery();
}
return("200");
}
return("404");
}
public String saveTarget(Target targetToSave)
{
Target target = new Target();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
String sqlString = "SELECT * FROM Target WHERE Date = '" + targetToSave.Date.ToString("yyyy-MM-dd") + "' AND DeviceId='" + targetToSave.DeviceId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
return("Combinatie van Datum en Apparaatnaam bestaan reed.");
}
else
{
mySqlReader.Close();
DateTime targetDateDayBefore = targetToSave.Date.AddDays(-1);
Target targetYesterday = getTarget(targetDateDayBefore, targetToSave.DeviceId);
if (targetYesterday != null)
{
mySqlReader.Close();
sqlString = "INSERT INTO Target (Date, ActiveMinutes, Steps, DeviceId) VALUES ('"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "',"
+ targetYesterday.ActiveMinutes + ","
+ targetYesterday.Steps + ",'"
+ targetYesterday.DeviceId + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
String id = cmd.LastInsertedId.ToString();
return(id);
}
else if (targetYesterday == null)
{
mySqlReader.Close();
sqlString = "INSERT INTO Target (Date, ActiveMinutes, Steps, DeviceId) VALUES ('"
+ targetToSave.Date.ToString("yyyy-MM-dd") + "',"
+ targetToSave.ActiveMinutes + ","
+ targetToSave.Steps + ",'"
+ targetToSave.DeviceId + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
String id = cmd.LastInsertedId.ToString();
return(id);
}
else
{
return(null);
}
}
}
public static Tuple <HashSet <vor>, HashSet <Brid> > voroviIBridoviSaSvojstvom(string karakteristika)
{
Tuple <HashSet <vor>, HashSet <Brid> > rješenje =
new Tuple <HashSet <vor>, HashSet <Brid> >(new HashSet <vor>(), new HashSet <Brid>());
try
{
DatabaseConnection.CheckSqlConnection();
MySqlCommand sqlCmd = DatabaseConnection.sqlCmd;
sqlCmd.Connection = DatabaseConnection.sqlConn;
sqlCmd.CommandText = "SELECT Cvorovi.Naziv " +
"FROM Cvorovi LEFT OUTER JOIN Karakteristike ON Cvorovi.ID = Karakteristike.ID_necega " +
"WHERE Karakteristike.Naziv = @karakteristika AND Karakteristike.Type = 'Cvor'";
karakteristikaParam.Value = karakteristika;
sqlCmd.Prepare();
MySqlDataReader sqlReader = sqlCmd.ExecuteReader();
while (sqlReader.Read())
{
rješenje.Item1.Add(Plan.skupČvorova[(string)sqlReader["Naziv"]]);
}
sqlReader.Close();
sqlCmd.CommandText = "SELECT Bridovi.Naziv " +
"FROM Bridovi LEFT OUTER JOIN Karakteristike ON Bridovi.ID = Karakteristike.ID_necega " +
"WHERE Karakteristike.Naziv = @karakteristika AND Karakteristike.Type = 'Brid'";
sqlCmd.Prepare();
sqlReader = sqlCmd.ExecuteReader();
while (sqlReader.Read())
{
rješenje.Item2.Add(Plan.skupBridova[(string)sqlReader["Naziv"]]);
}
sqlReader.Close();
}
catch (Exception ex)
{
MessageBox.Show("Greška pri čitanju iz baze! " + ex.Message, "Greška",
MessageBoxButtons.OK, MessageBoxIcon.Error);
rješenje = null;
}
return(rješenje);
}
protected override EventJournal doGet(int ID)
{
m_command.CommandText = Receipt.GetByIDSQL(ID);
MySql.Data.MySqlClient.MySqlDataReader r = m_command.ExecuteReader();
Receipt st = Receipt.TransformReader(r);
r.Close();
if (st == null)
{
return(null);
}
m_command.CommandText = ReceiptItem.GetByEventIDSQL(ID);
r = m_command.ExecuteReader();
IList stis = ReceiptItem.TransformReaderList(r);
r.Close();
foreach (ReceiptItem sti in stis)
{
sti.EVENT_JOURNAL = st;
//sti.VENDOR = PartRepository.GetByID(m_command, sti.PART.ID);
sti.VENDOR_BALANCE_ENTRY = VendorBalanceEntryRepository.FindVendorBalanceEntryByEventItem(m_command, sti.ID, sti.VENDOR_BALANCE_ENTRY_TYPE);
//sti.GRN_ITEM = GoodReceiveNoteRepository.FindGoodReceiveNoteItem(m_command, sti.GRN_ITEM.ID);
//sti.GRN_ITEM.PART = PartRepository.GetByID(m_command, sti.GRN_ITEM.PART.ID);
if (sti.VENDOR_BALANCE_CUSTOMER_INVOICE_TYPE == VendorBalanceEntryType.CustomerInvoice)
{
if (sti.CUSTOMER_INVOICE_JOURNAL_ITEM != null)
{
sti.CUSTOMER_INVOICE_JOURNAL_ITEM = CustomerInvoiceJournalRepository.FindCIJournalItemlistForReceipt(m_command, sti.CUSTOMER_INVOICE_JOURNAL_ITEM.GetID());
}
}
if (sti.VENDOR_BALANCE_CUSTOMER_INVOICE_TYPE == VendorBalanceEntryType.CustomerOutStandingInvoice)
{
if (sti.CUSTOMER_INVOICE_JOURNAL_ITEM != null)
{
sti.CUSTOMER_INVOICE_JOURNAL_ITEM = CustomerOutStandingInvoiceRepository.FindCOIItemlistForReceipt(m_command, sti.CUSTOMER_INVOICE_JOURNAL_ITEM.GetID());
}
}
if (sti.PAYMENT_TYPE == ReceiptType.Bank)
{
sti.BANK = getBank(sti.BANK.ID);
}
if (sti.PAYMENT_TYPE == ReceiptType.ARCreditNote)
{
sti.AR_CREDIT_NOTE = ARCreditNoteRepository.FindARCRForReceipt(m_command, sti.AR_CREDIT_NOTE.ID);
}
st.EVENT_JOURNAL_ITEMS.Add(sti);
}
return(st);
}
private void btnEjecutarSentencia_Click(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(txtSentence.Text, connection);
try
{
MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader();
/*while (rdr.Read())
* {
* lstResultado.Items.Add(rdr["NoCuenta"].ToString());
* }
* rdr.Close();*/
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
string c = rdr.GetName(i);
lstResultado.Items.Add(c + ": " + rdr[c] + Environment.NewLine);
}
lstResultado.Items.Add(Environment.NewLine);
}
rdr.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("no ha seleccionado ninguna BD");
}
}
public bool putUser(int id, User user)
{
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sql = "Select * from quiz_taker_info where id = " + id.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
sql = "Update quiz_taker_info set first_name = '" + user.Firstname + "', " +
"middle_name = '" + user.Middlename + "', " +
"last_name = '" + user.Lastname + "', " +
"email = '" + user.Username + "', " +
"group_id = '" + user.GroupId + "', " +
"password = '******' " +
"salt = '" + user.Salt + "' where id = " + id.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
};
}
internal static IList FindARCRBySalesReturn(MySql.Data.MySqlClient.MySqlCommand cmd, int prID)
{
cmd.CommandText = ARCreditNoteItem.GetARCRItemBySRID(prID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
IList result = ARCreditNoteItem.TransformReaderList(r);
r.Close();
foreach (ARCreditNoteItem i in result)
{
cmd.CommandText = ARCreditNote.GetByIDSQL(i.EVENT_JOURNAL.ID);
r = cmd.ExecuteReader();
i.EVENT_JOURNAL = ARCreditNote.TransformReader(r);
r.Close();
}
return(result);
}
//UPDATE CUSTOMER
public bool UpdateCustomer(int id, Customer updatedCustomer, string currentUser)
{
string sql = "SELECT * FROM customers WHERE id = @id";
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection);
//Parameters to avoid SQL injection
command.Parameters.AddWithValue("@id", id);
mySQLReader = command.ExecuteReader();
//If the customer exists, update him
if (mySQLReader.Read())
{
string updateSql = "UPDATE customers SET name = @name, surname = @surname, photoUrl = @photoUrl, modifiedBy = @modifiedBy WHERE id = @id";
mySQLReader.Close();
command = new MySql.Data.MySqlClient.MySqlCommand(updateSql, connection);
//Parameters to avoid SQL injection
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@name", updatedCustomer.Name);
command.Parameters.AddWithValue("@surname", updatedCustomer.Surname);
command.Parameters.AddWithValue("@photoUrl", updatedCustomer.PhotoURL);
command.Parameters.AddWithValue("@modifiedBy", currentUser);
command.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
protected void submitEventMethod(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM webappdemo.userregistration WHERE username='******' AND password='******'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("firstname")) + " " +
reader.GetString(reader.GetOrdinal("middlename")) + " " +
reader.GetString(reader.GetOrdinal("lastname"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("Login.aspx", false);
}
else
{
passwordTextBox.Text = "invlaid user";
}
reader.Close();
conn.Close();
}
/**
* returns mysql result as hashtable
*/
public List <Hashtable> selectAsHash(string sql)
{
List <Hashtable> Data = new List <Hashtable>();
MySql.Data.MySqlClient.MySqlDataReader reader = this.sql_select(sql);
if (reader != null)
{
while (reader.Read())
{
Hashtable result = new Hashtable();
for (int i = 0; i < reader.FieldCount; i++)
{
string dataStr = null;
try
{
dataStr = reader.GetString(i);
}
catch (Exception)
{
}
string column = reader.GetName(i);
result.Add(column, dataStr);
}
Data.Add(result);
}
reader.Close();
}
return(Data);
}
/**
* returns the maximum of possible query size
*/
public Int64 getMaxAllowPacketSize(bool forceRead)
{
bool disConnecting = false;
if (this.allowedpacketSize == -1 || forceRead)
{
if (!this.isConnected())
{
this.connect();
disConnecting = true;
}
string sql = "select @@max_allowed_packet";
MySql.Data.MySqlClient.MySqlDataReader myData = this.sql_select(sql);
Int64 size = -1;
if (myData != null)
{
while (myData.Read())
{
size = myData.GetInt64(0);
}
myData.Close();
}
if (disConnecting)
{
this.disConnect();
}
this.allowedpacketSize = size;
return(size);
}
else
{
return(this.allowedpacketSize);
}
}
public bool updateStudent(long ID, sinhvien studentToSave)
{
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
String sqlString = "SELECT * FROM sinhvien WHERE MSSV=" + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
mySQLReader.Close();
sqlString = "UPDATE sinhvien SET hoten='" + studentToSave.hoten + "', ngaysinh='"
+ studentToSave.ngaysinh.ToString("yyyy-MM-dd HH:mm:ss")
+ "', idlop=(SELECT idlop FROM lop WHERE lop.tenlop='" + studentToSave.lop
+ "'), diemtongket='" + studentToSave.diemtongket.ToString().Replace(",", ".")
+ "' WHERE MSSV=" + ID.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
/**
* connect the database.
* if allready connected then
* first the connection will be disconnected
* and reconnected
*/
public void connect()
{
if (this.isConnected())
{
this.disConnect();
}
if (myConnectionString != null)
{
try
{
this.connection.ConnectionString = myConnectionString;
this.connection.Open();
this.connected = true;
if (this.currentProfil.getProperty("foreign_key_check") == "1")
{
MySql.Data.MySqlClient.MySqlDataReader md = this.sql_select("SET FOREIGN_KEY_CHECKS = 0");
md.Close();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
this.lastSqlErrorMessage = ex.Message;
this.lastErrorCode = ex.ErrorCode;
this.connected = false;
}
}
else
{
this.lastSqlErrorMessage = Projector.Properties.Resources.ErrorMysqlNoProperties;
}
}
public Boolean updateBook(Book book, int isbn)
{
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
Book b = new Book();
String sqlString = "SELECT * FROM booksTable WHERE isbn = " + isbn.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
mySqlReader.Close();
String sqlStoring = "UPDATE BooksTable SET ( isbn = " + book.Isbn + ",titre = '" + book.Title + "',editeur ='" + book.Editor
+ "',auteur = '" + book.Author + "' ,nb_exemplaires = " + book.NbrExemplaires + "+) WHERE isbn = " + book.Isbn;
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
public bool deleteStudent(long ID)
{
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
String sqlString = "SELECT * FROM sinhvien WHERE MSSV=" + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
mySQLReader.Close();
sqlString = "DELETE FROM sinhvien WHERE MSSV=" + ID.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
internal static IList FindReceiptUsingARCR(MySql.Data.MySqlClient.MySqlCommand cmd, int apDNID)
{
cmd.CommandText = ReceiptItem.GetReceiptItemByARCR(apDNID);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
IList result = ReceiptItem.TransformReaderList(r);
r.Close();
foreach (ReceiptItem i in result)
{
cmd.CommandText = Receipt.GetByIDSQL(i.EVENT_JOURNAL.ID);
r = cmd.ExecuteReader();
i.EVENT_JOURNAL = Receipt.TransformReader(r);
r.Close();
}
return(result);
}
internal static IList FindPaidCustomerInvoice(MySql.Data.MySqlClient.MySqlCommand cmd, int siID)
{
cmd.CommandText = ReceiptItem.GetCustomerInvoiceBySOIID(siID, VendorBalanceEntryType.CustomerInvoice);
MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader();
IList result = ReceiptItem.TransformReaderList(r);
r.Close();
foreach (ReceiptItem i in result)
{
cmd.CommandText = Receipt.GetByIDSQL(i.EVENT_JOURNAL.ID);
r = cmd.ExecuteReader();
i.EVENT_JOURNAL = Receipt.TransformReader(r);
r.Close();
}
return(result);
}
public bool DeleteMeasurement(string id)
{
bool result = false;
Measurement m = new Measurement();
MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null;
string queryString = string.Format("SELECT * FROM measurements WHERE measurement_ID='" + id + "'");
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, connector.conn);
mySqlDataReader = cmd.ExecuteReader();
if (mySqlDataReader.Read())
{
mySqlDataReader.Close();
queryString = string.Format("DELETE FROM measurements WHERE measurement_ID='" + id + "'");
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, connector.conn);
cmd.ExecuteNonQuery();
result = true;
}
else
{
result = false;
}
return(result);
}
//DELETE CUSTOMER BY ID
public bool DeleteCustomer(int id)
{
string sql = "SELECT * FROM customers WHERE id = @id";
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection);
//Parameters to avoid SQL injection
command.Parameters.AddWithValue("@id", id);
mySQLReader = command.ExecuteReader();
//If the customer exists, delete him
if (mySQLReader.Read())
{
string deleteSql = "DELETE FROM customers WHERE id = @id";
mySQLReader.Close();
command = new MySql.Data.MySqlClient.MySqlCommand(deleteSql, connection);
//Parameters to avoid SQL injection
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
protected void LoginButton_Click(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
using (conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM db_9f3dca_webapp.user_registration WHERE username='******' AND password='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("username"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
Response.Redirect("loggedIn.aspx", false);
}
else
{
LoginError.Text = "Invalid username password combination!";
}
reader.Close();
conn.Close();
}
}
