public List <InvoiceItemcs> getAllAccounts()
{
List <InvoiceItemcs> ListAccount = new List <InvoiceItemcs>();
string Query = "SELECT id, code, name, price, quantity, codeinvoice, nota FROM facturaitem WHERE active=1;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
Cmm.Connection.Close();
if (reader.HasRows)
{
while (reader.Read())
{
newItem = new InvoiceItemcs();
newItem.Id = (int)reader["id"];
newItem.Code = (string)reader["code"];
newItem.Name = (string)reader["name"];
newItem.Price = (double)reader["price"];
newItem.Quantity = (int)reader["quantity"];
newItem.CodeInvoice = (string)reader["codeinvoice"];
newItem.Note = (string)reader["nota"];
ListAccount.Add(newItem);
}
}
return(ListAccount);
}
public List <Product> getAllProducts()
{
List <Product> ListProducts = new List <Product>();
string Query = "SELECT * FROM producto;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
newProduct = new Product();
newProduct.Id = (int)reader["id"];
newProduct.Code = (string)reader["code"];
newProduct.Name = (string)reader["name"];
newProduct.IdCategory = (int)reader["idcategory"];
newProduct.Description = (string)reader["description"];
newProduct.CostSale = (double)reader["costsale"];
newProduct.CostBuy = (double)reader["costbuy"];
newProduct.Stock = (int)reader["stock"];
ListProducts.Add(newProduct);
}
}
return(ListProducts);
}
//Metodo insertar proveedor
public void insertProvider(Provider _Provider)
{
try
{
if (ExistBank(_Provider.Identification))
{
Query = "UPDATE proveedor SET "
+ "name='" + _Provider.Name.Trim() + "',"
+ "address='" + _Provider.Adress.Trim() + "',"
+ "phone='" + _Provider.Phone.Trim()
+ "' WHERE identification='" + _Provider.Identification + "';";
}
else
{
Query = "INSERT INTO proveedor(identification, name, address, phone) values('"
+ _Provider.Identification + "','"
+ _Provider.Name + "','"
+ _Provider.Adress + "','"
+ _Provider.Phone + "');";
}
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
Cmm.ExecuteNonQuery();
Cmm.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\ninsertProvider", "Error del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <Promotion> getAllPromos()
{
List <Promotion> ListPromos = new List <Promotion>();
string Query = "SELECT * FROM promo;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
Cmm.Connection.Close();
while (reader.Read())
{
newPromo = new Promotion();
newPromo.Id = (int)reader["id"];
newPromo.Code = (string)reader["code"];
newPromo.Name = (string)reader["name"];
newPromo.Description = (string)reader["description"];
newPromo.Active = (bool)reader["active"];
newPromo.Percent = (int)reader["percent"];
ListPromos.Add(newPromo);
}
return(ListPromos);
}
public List <User> getAllUsers()
{
List <User> ListUsers = new List <User>();
string Query = "SELECT id, identification, name, nickName, password, active FROM usuario;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
newUser = new User();
newUser.Id = (int)reader["id"];
newUser.ID = (string)reader["identification"];
newUser.Name = (string)reader["name"];
newUser.NickName = (string)reader["nickName"];
newUser.Password = (string)reader["password"];
newUser.Active = (bool)reader["active"];
ListUsers.Add(newUser);
}
}
Cmm.Connection.Close();
return(ListUsers);
}
public void insertDebt(Debt _debt)
{
try
{
if (!ExistDebt(_debt.Id))
{
Query = "INSERT INTO deuda(IDClient, IDBanda, nameClient, codeFact, amount, date, active) " +
"VALUES('" + _debt.IDClient + "','" + _debt.IDBanda + "','" + _debt.NameClient + "','"
+ _debt.CodFact + "','" + _debt.Amount + "','" + _debt.Date + "',"
+ _debt.Active + ");";
}
else
{
Query = "UPDATE deuda SET amount='" + _debt.Amount + "' WHERE id='" + _debt.Id + "';";
}
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
Cmm.ExecuteNonQuery();
Cmm.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\ninsertDebt", "Error del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <Bank> getAllBanks()
{
List <Bank> ListBanks = new List <Bank>();
string Query = "SELECT id, name FROM banco;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
newBank = new Bank();
newBank.Id = (int)reader["id"];
newBank.Name = (string)reader["name"];
ListBanks.Add(newBank);
}
}
Cmm.Connection.Close();
return(ListBanks);
}
public List <Account> getCajas()
{
List <Account> ListAccount = new List <Account>();
string Query = "SELECT id, name, amount FROM cuenta;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
newAccount = new Account();
newAccount.Id = (int)reader["id"];
newAccount.Name = (string)reader["name"];
newAccount.Amount = Convert.ToDouble(reader["amount"]);
ListAccount.Add(newAccount);
}
}
Cmm.Connection.Close();
return(ListAccount);
}
public List <Account> getAllAccounts()
{
List <Account> ListAccount = new List <Account>();
string Query = "SELECT id, code, name, active FROM cuenta WHERE active=1;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
Cmm.Connection.Close();
if (reader.HasRows)
{
while (reader.Read())
{
newAccount = new Account();
newAccount.Id = (int)reader["id"];
newAccount.Name = (string)reader["name"];
ListAccount.Add(newAccount);
}
}
return(ListAccount);
}
public DataSet getIngEgrDia(String Fecha)
{
DataSet ds = new DataSet();
string Query = "SELECT movimientosmonetarios.id AS CODIGOMOVIMIENTO, movimientosmonetarios.idaccount AS IDCUENTAAFECTADA, cuenta.name AS NOMBRECUENTAAFECTADA, movimientosmonetarios.idcatmovement AS IDCATEGORIAMOVIMIENTO, categoriamovimientos.name AS CATEGORIAMOVIMIENTO, IF(idtypemovement = 0, 'INGRESO', 'EGRESO') AS TIPOMOVIMIENTO, movimientosmonetarios.amount AS VALOR, movimientosmonetarios.description AS DESCRIPCION, movimientosmonetarios.date AS FECHA FROM(movimientosmonetarios INNER JOIN cuenta ON movimientosmonetarios.idaccount = cuenta.id INNER JOIN categoriamovimientos ON movimientosmonetarios.idcatmovement = categoriamovimientos.id) WHERE movimientosmonetarios.date ='" + Fecha + "';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataAdapter da = new MySqlDataAdapter(Cmm);
da.Fill(ds);
Cmm.Connection.Close();
return(ds);
}
public DataSet getFacturasDia(String Fecha)
{
DataSet ds = new DataSet();
string Query = "SELECT factura.id AS ID, factura.code AS CODIGO, factura.identification AS IDCLIENTE, factura.nameClient AS NOMBRECLIENTE, factura.banda AS BANDA, factura.fecha AS FECHA, factura.amount AS VALOR, cuenta.name AS CUENTAAFECTADA FROM(factura INNER JOIN cuenta ON factura.idaccountin = cuenta.id) WHERE factura.fecha = '" + Fecha + "';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataAdapter da = new MySqlDataAdapter(Cmm);
da.Fill(ds);
Cmm.Connection.Close();
return(ds);
}
public DataSet getComprasDia(String Fecha)
{
DataSet ds = new DataSet();
string Query = "SELECT compra.id AS CódigoCompra, compra.idproveedor AS IDProveedor, proveedor.name AS NombreProveedor, compra.idaccountout AS CuentaAfectada, cuenta.name AS NombreCuentaAfectada, compra.description AS Descripcion, compra.amount AS Valor, compra.date FROM(compra INNER JOIN cuenta ON compra.idaccountout = cuenta.id INNER JOIN proveedor ON compra.idproveedor = proveedor.id) WHERE compra.date ='" + Fecha + "';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataAdapter da = new MySqlDataAdapter(Cmm);
da.Fill(ds);
Cmm.Connection.Close();
return(ds);
}
public DataSet getVentasDia(String Fecha)
{
DataSet ds = new DataSet();
string Query = "SELECT factura.code AS CódigoFactura, factura.identification AS IDCliente, factura.nameClient AS NombreCliente, factura.banda AS Banda, factura.fecha AS Fecha, factura.amount AS Valor, cuenta.name AS CuentaAfectada FROM(factura INNER JOIN cuenta ON factura.idaccountin = cuenta.id) WHERE factura.fecha = '" + Fecha + "';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataAdapter da = new MySqlDataAdapter(Cmm);
da.Fill(ds);
Cmm.Connection.Close();
return(ds);
}
//Metodo de carga de datos para grid
public DataTable fillCategoriesDT()
{
DataTable dt = new DataTable();
try
{
MySqlDataAdapter da = new MySqlDataAdapter("Select id, name, description From categoriamovimientos", ConectDB.getConection());
da.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error en la carga de bancos: " + ex.Message);
}
return(dt);
}
private void btnRefresh_Click(object sender, EventArgs e)
{
DaoRepVentas daoRepVentas = new DaoRepVentas();
DataSet dsData = daoRepVentas.getVentasMes(dtpFecha.Value.ToShortDateString());
refreshData();
dgvData.Columns.Clear();
dgvData.Rows.Clear();
dgvData.DataSource = dsData.Tables[0];
DataGridViewColumn colum = new DataGridViewColumn();
colum.HeaderText = "FacturaEnDeuda";
colum.CellTemplate = new DataGridViewTextBoxCell();
dgvData.Columns.Add(colum);
int idFact = 0;
string Fact = "";
double totalDeudas = 0;
int countDeudas = 0;
System.Collections.IList list = dgvData.Rows;
for (int i = 0; i < list.Count; i++)
{
DataGridViewRow row = (DataGridViewRow)list[i];
string Query = "SELECT deuda.id AS CODIGODEUDA, deuda.codeFact AS FACTURA, amount AS MONTO FROM deuda WHERE deuda.codeFact = '" + row.Cells[0].Value + "' AND deuda.active = 1;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
idFact = (int)reader["CODIGODEUDA"];
Fact = (string)reader["FACTURA"];
}
if (idFact > 0)
{
row.Cells[7].Value = "SI";
row.Cells[7].ReadOnly = true;
row.Cells[7].Style.BackColor = Color.Red;
}
else
{
row.Cells[7].Value = "NO";
row.Cells[7].ReadOnly = true;
row.Cells[7].Style.BackColor = Color.Green;
}
}
Cmm.Connection.Close();
}
RecalcularTotales();
}
public void insertBill(int _Proveedor, int _AccoutOut, string _Description, double _amount, string _Date)
{
try
{
Query = "INSERT INTO compra(idproveedor, idaccountout, description, amount, date) values('" + _Proveedor + "','" + _AccoutOut + "','" + _Description + "','" + _amount + "','" + _Date + "');";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
Cmm.ExecuteNonQuery();
Cmm.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\ninsertCompra", "Error del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <Client> getAllClients()
{
List <Client> ListClients = new List <Client>();
string Query = "SELECT id, identification, name, idband, email, address, phone, mobilephone, credit FROM cliente;";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
Cmm.Connection.Close();
if (reader.HasRows)
{
while (reader.Read())
{
newClient = new Client();
newClient.Id = (int)reader["id"];
newClient.ID = (string)reader["identification"];
newClient.Name = (string)reader["name"];
newClient.IdBand = (int)reader["idband"];
newClient.Email = (string)reader["email"];
newClient.Address = (string)reader["address"];
newClient.Phone = (string)reader["phone"];
newClient.MobilePhone = (string)reader["mobilephone"];
newClient.Credit = (double)reader["credit"];
ListClients.Add(newClient);
}
}
return(ListClients);
}
public void insertMovementMoney(MovementMoney _MovementMoney)
{
try
{
Query = "INSERT INTO movimientosmonetarios(idaccount, idcatmovement, idtypemovement, amount, description, date) VALUES('" +
_MovementMoney.IdAccount.ToString() + "','" +
_MovementMoney.IdCatMovement.ToString() + "','" +
_MovementMoney.IdTypeMovement.ToString() + "','" +
_MovementMoney.Amount.ToString() + "','" +
_MovementMoney.Description.ToString() + "','" +
_MovementMoney.Date.Trim() + "');";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
Cmm.ExecuteNonQuery();
Cmm.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\ninsertBand", "Error del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//Metodo Insertar Banco
public void insertBank(Bank _Bank)
{
try
{
//if (ExistBank(_Bank.Id))
//{
// Query = "UPDATE banco SET name='" + _Bank.Name.Trim() + "' WHERE id='" + _Bank.Id + "';";
//}
//else
//{
Query = "INSERT INTO banco(name) values('" + _Bank.Name.Trim() + "');";
//}
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
Cmm.ExecuteNonQuery();
Cmm.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\ninsertBank", "Error del Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DataSet getRepoTranferencias(String Fecha)
{
string mesAno = Fecha.Substring(Fecha.IndexOf("/"), 8);
DataSet ds = new DataSet();
string Query = "SELECT t1.id AS CODIGOMOVIMIENTO, t1.idaccountout AS IDCUENTADERETIRO, t2.name AS CUENTADERETIRO, t1.idaccountin AS IDCUENTADEINGRESO, t3.name AS CUENTADEINGRESO, t1.amount AS VALOR, t1.dateMove AS FECHA FROM transferenciacuentas t1 INNER JOIN cuenta t2 INNER JOIN cuenta t3 ON t1.idaccountin = t2.id AND t1.idaccountout = t3.id WHERE t1.dateMove LIKE '%" + mesAno + "%';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataAdapter da = new MySqlDataAdapter(Cmm);
da.Fill(ds);
Cmm.Connection.Close();
return(ds);
}
public void getFactura(string code)
{
Invoice factura = new Invoice();
DaoItemInvoice daoItemFact = new DaoItemInvoice();
ds = daoItemFact.fillItemsDT(code);
dgvListado.DataSource = ds;
lblNameClient.Text = factura.NameClient;
lblCantProductos.Text = factura.Code + "Fecha: " + factura.Fecha;
lblFact.Text = factura.Identification;
lblTotal.Text = factura.Amount.ToString();
string Query = "SELECT * FROM factura WHERE code='" + code + "';";
MySqlCommand Cmm = new MySqlCommand(Query, ConectDB.getConection());
MySqlDataReader reader;
reader = Cmm.ExecuteReader();
factura = new Invoice();
if (reader.Read())
{
factura.Id = (int)reader["id"];
factura.Code = (string)reader["code"];
factura.Identification = (string)reader["identification"];
factura.NameClient = (string)reader["nameClient"];
factura.Band = (string)reader["banda"];
factura.Fecha = (string)reader["fecha"];
factura.Amount = (double)reader["amount"];
}
int intCantFact = dgvListado.Rows.Count - 1;
Cmm.Connection.Close();
lblBandName.Text = factura.Band.ToString();
lblCantProductos.Text = intCantFact.ToString();
lblFact.Text = factura.Code;
lblNameClient.Text = factura.NameClient;
lblTotal.Text = "$ " + factura.Amount.ToString();
lblFecha.Text = factura.Fecha;
}