public static int agregarPagoVentaEfectivo(DataGridView dgv_tabla, long id_venta, int medio_de_pago, long documento)
{
int retorno = 0;
string query = string.Empty;
string fecha = ControladorFuncVariadas.convertirFecha(DateTime.Now);
decimal monto = new decimal(0);
for (int i = 0; i < dgv_tabla.Rows.Count; ++i)
{
monto += Convert.ToDecimal(dgv_tabla.Rows[i].Cells[5].Value);
}
try
{
query = string.Format("Insert into pagosparciales (id_venta,monto,medio_de_pago,fecha,documento) values ({0},{1},{2},'{3}',{4})",
id_venta, monto, medio_de_pago, fecha, documento);
MySqlCommand comando = new MySqlCommand(query, Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error en agregar pago parcial. ->" + e);
}
return(retorno);
}
public static int agregarPagoParcialReparacion(PagoParcial a)
{
int retorno = 0;
string query = string.Empty;
string fecha = ControladorFuncVariadas.convertirFecha(a.fecha);
string vencimiento = ControladorFuncVariadas.convertirFecha(a.vencimiento);
try
{
if (a.medio_de_pago == 1)
{
query = string.Format("Insert into pagosparciales (monto,medio_de_pago,fecha,id_reparacion,recibo,retencion_iva,retencion_ig,retencion_iibb,retencion_suss,documento) values ({0},{1},'{2}',{3},'{4}',{5},{6},{7},{8},{9})",
a.monto, a.medio_de_pago, fecha, a.id_reparacion, a.recibo, a.retencion_iva, a.retencion_ig, a.retencion_iibb, a.retencion_suss, a.documento);
Console.WriteLine(query);
}
else
{
query = string.Format("Insert into pagosparciales (id_reparacion,monto,medio_de_pago,fecha,comprobante,vencimiento,razon_social,banco,recibo,retencion_iva,retencion_ig,retencion_iibb,retencion_suss) values ({0},{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}',{9},{10},{11},{12},{13})",
a.id_reparacion, a.monto, a.medio_de_pago, fecha, a.comprobante, vencimiento, a.razon_social, a.banco, a.recibo, a.retencion_iva, a.retencion_ig, a.retencion_iibb, a.retencion_suss, a.documento);
Console.WriteLine(query);
}
MySqlCommand comando = new MySqlCommand(query, Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error en agregar pago parcial. ->" + e);
}
return(retorno);
}
public static int FinalizarReparacion(long id_reparacion, Reparacion informacion, int tipo = 0)
{
int retorno = 0;
int cobrada;
string consulta = string.Empty;
string fecha_vencimiento = ControladorFuncVariadas.convertirFecha(informacion.vencimiento);
if (informacion.cobrada)
{
cobrada = 1;
}
else
{
cobrada = 0;
}
MySqlConnection conexion;
try {
conexion = Conexion.ObtenerConexion();
if (informacion.cobrada)
{
consulta = string.Format("Update reparaciones set entregado='{0}', fecha_pago = CURRENT_DATE, medio_de_pago = '{1}', vencimiento = '{2}', tipo_factura = '{3}', usuario = '{4}', cobrada = '{5}',fecha_cobro = CURRENT_DATE, tipo = {7}, remito = {8}, ordencompra = '{9}' where id={6}", 1, informacion.medio_de_pago, informacion.vencimiento, informacion.tipo_factura, Usuario.User, cobrada, id_reparacion, tipo, informacion.remito, informacion.ordenDeCompra);
}
else
{
consulta = string.Format("Update reparaciones set entregado='{0}', fecha_pago = CURRENT_DATE, medio_de_pago = '{1}', vencimiento = '{2}', tipo_factura = '{3}', usuario = '{4}', cobrada = '{5}', tipo = {7}, remito = {8}, ordencompra = '{9}' where id={6}", 1, informacion.medio_de_pago, fecha_vencimiento, informacion.tipo_factura, Usuario.User, cobrada, id_reparacion, tipo, informacion.remito, informacion.ordenDeCompra);
}
MySqlCommand comando = new MySqlCommand(consulta, conexion);
retorno = comando.ExecuteNonQuery();
conexion.Close();
if (informacion.medio_de_pago == 1 || informacion.medio_de_pago == 3 || informacion.medio_de_pago == 4)
{
ControladorPagoParcial.agregarPagoReparacionEfectivo(id_reparacion, informacion.medio_de_pago, informacion.documento);
}
}catch (MySqlException a) { Console.WriteLine(a); }
return(retorno);
}
public static int agregarPagoReparacionEfectivo(long id_reparacion, int medio_de_pago, long documento)
{
decimal monto = new decimal(0);
string consulta = string.Empty;
int retorno = 0;
string query = string.Empty;
string fecha = ControladorFuncVariadas.convertirFecha(DateTime.Now);
try
{
consulta = "SELECT subtotal FROM detalle_reparaciones WHERE id_reparacion = " + id_reparacion;
MySqlCommand cmd = new MySqlCommand(consulta, Conexion.ObtenerConexion());
MySqlDataReader _reader = cmd.ExecuteReader();
while (_reader.Read())
{
monto += _reader.GetDecimal(0);
}
query = string.Format("Insert into pagosparciales (monto,medio_de_pago,fecha,id_reparacion, documento) values ({0},{1},'{2}',{3}, {4})",
monto, medio_de_pago, fecha, id_reparacion, documento);
MySqlCommand comando = new MySqlCommand(query, Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error en agregar pago parcial (reparacion). ->" + e);
}
return(retorno);
}
public static int Insertarnuevacompra(ComprasaProveedores compras)
{
int retorno = 0;
int pagada = 0;
DateTime fechapago1 = DateTime.Now;
if (compras.Pagada)
{
pagada = 1;
}
string fecha = ControladorFuncVariadas.convertirFecha(compras.Vencimiento);
string fechapago = ControladorFuncVariadas.convertirFecha(fechapago1);
try
{
if (pagada == 1)
{
MySqlCommand comando = new MySqlCommand(string.Format("Insert into compras (proveedor,motivo,vencimiento,monto,pagada,detalle,comprobante,iva,razon,fecha_pago,iva10,no_gravado,percepcion_iva,percepcion_iibb) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}')",
compras.Proveedor, compras.Motivo, fecha, compras.Monto, pagada, compras.Detalle, compras.Comprobante, compras.Iva, compras.Razon, fechapago, compras.Iva10, compras.Nogravado, compras.Percepcioniva, compras.Percepcioniibb), Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
else
{
MySqlCommand comando = new MySqlCommand(string.Format("Insert into compras (proveedor,motivo,vencimiento,monto,pagada,detalle,comprobante,iva,razon,iva10,no_gravado,percepcion_iva,percepcion_iibb) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}',{11},'{12}')",
compras.Proveedor, compras.Motivo, fecha, compras.Monto, pagada, compras.Detalle, compras.Comprobante, compras.Iva, compras.Razon, compras.Iva10, compras.Nogravado, compras.Percepcioniva, compras.Percepcioniibb), Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine("Error" + e);
System.Windows.Forms.MessageBox.Show(Convert.ToString(e));
}
return(retorno);
}
public static void armarExcelRendiciones(DataGridView dgv_tabla_venta, DataGridView dgv_tabla_reparacion, DateTime desde, DateTime hasta, string usuario)
{
ControladorFuncVariadas control = new ControladorFuncVariadas();
Cursor.Current = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Interactive = false;
try
{
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.WindowState = XlWindowState.xlMaximized;
Microsoft.Office.Interop.Excel.Range cabecera = null;
ws.Cells[1, 1] = "RENDICIÓN " + usuario;
ws.Range[ws.Cells[1, 1], ws.Cells[3, 6]].Merge();
cabecera = ws.get_Range("a1", "f3");
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
ws.Cells[4, 1] = "Entre " + desde.ToString("dd/MM/yyyy") + " y " + hasta.ToString("dd/MM/yyyy");
ws.Range[ws.Cells[4, 1], ws.Cells[4, 6]].Merge();
Range desdehasta = null;
desdehasta = ws.get_Range("a4", "f4");
ws.Cells[4, 1].Font.Bold = true;
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Size = 11;
desdehasta.BorderAround2();
desdehasta.Interior.Color = Color.White;
ws.Cells[5, 1] = "ID VENTA";
ws.Cells[5, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range id_venta = ws.get_Range("a5", "a5");
id_venta.BorderAround2();
id_venta.Interior.Color = Color.White;
ws.Cells[5, 2] = "Fecha";
ws.Cells[5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[5, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 2].Font.Bold = true;
Range venta_fecha = ws.get_Range("b5", "b5");
venta_fecha.BorderAround2();
venta_fecha.Interior.Color = Color.White;
ws.Cells[5, 3] = "Vendedor";
ws.Cells[5, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_vendedor = ws.get_Range("c5", "c5");
venta_vendedor.BorderAround2();
venta_vendedor.Interior.Color = Color.White;
ws.Cells[5, 4] = "Cliente";
ws.Cells[5, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_cliente = ws.get_Range("d5", "d5");
venta_cliente.BorderAround2();
venta_cliente.Interior.Color = Color.White;
ws.Cells[5, 5] = "Monto";
ws.Cells[5, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_monto = ws.get_Range("e5", "e5");
venta_monto.BorderAround2();
venta_monto.Interior.Color = Color.White;
ws.Cells[5, 6] = "Cobrado";
ws.Cells[5, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_cobrado = ws.get_Range("f5", "f5");
venta_cobrado.BorderAround2();
venta_cobrado.Interior.Color = Color.White;
//********************
//Empiezo por cantidad
//********************
int fila = 6;
int columnas = 6;
for (int i = 0; i < dgv_tabla_venta.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla_venta.Rows[i].Cells["Id Venta"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
}
else if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla_venta.Rows[i].Cells["Fecha"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla_venta.Rows[i].Cells["Vendedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla_venta.Rows[i].Cells["Cliente"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 5] = dgv_tabla_venta.Rows[i].Cells["Monto"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla_venta.Rows[i].Cells["Cobrado"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
}
fila++;
}
ws.Cells[fila, 1] = "ID REPARACIÓN";
ws.Cells[fila, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range id_reparacion = ws.get_Range("a" + fila, "a" + fila);
id_reparacion.BorderAround2();
id_reparacion.Interior.Color = Color.White;
ws.Cells[fila, 2] = "Fecha";
ws.Cells[fila, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[fila, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 2].Font.Bold = true;
Range reparacion_fecha = ws.get_Range("b" + fila, "b" + fila);
reparacion_fecha.BorderAround2();
reparacion_fecha.Interior.Color = Color.White;
ws.Cells[fila, 3] = "Vendedor";
ws.Cells[fila, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_vendedor = ws.get_Range("c" + fila, "c" + fila);
reparacion_vendedor.BorderAround2();
reparacion_vendedor.Interior.Color = Color.White;
ws.Cells[fila, 4] = "Cliente";
ws.Cells[fila, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_cliente = ws.get_Range("d" + fila, "d" + fila);
reparacion_cliente.BorderAround2();
reparacion_cliente.Interior.Color = Color.White;
ws.Cells[fila, 5] = "Monto";
ws.Cells[fila, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_monto = ws.get_Range("e" + fila, "e" + fila);
reparacion_monto.BorderAround2();
reparacion_monto.Interior.Color = Color.White;
ws.Cells[fila, 6] = "Cobrado";
ws.Cells[fila, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_cobrado = ws.get_Range("f" + fila, "f" + fila);
reparacion_cobrado.BorderAround2();
reparacion_cobrado.Interior.Color = Color.White;
fila++;
for (int i = 0; i < dgv_tabla_reparacion.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla_reparacion.Rows[i].Cells["Id Venta"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
}
else if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla_reparacion.Rows[i].Cells["Fecha"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla_reparacion.Rows[i].Cells["Vendedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla_reparacion.Rows[i].Cells["Cliente"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 5] = dgv_tabla_reparacion.Rows[i].Cells["Monto"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla_reparacion.Rows[i].Cells["Cobrado"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
}
fila++;
}
//ws.Columns[2].NumberFormat = "######";
ws.Columns[5].NumberFormat = "$ #.###,00";
ws.Columns[6].NumberFormat = "$ #.###,00";
ws.Columns.AutoFit();
Excel.Interactive = true;
Excel.Visible = true;
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally { Cursor.Current = Cursors.Default; }
}
public static void armarExcelListaCompras(DataGridView dgv_tabla)
{
ControladorFuncVariadas control = new ControladorFuncVariadas();
Cursor.Current = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Interactive = false;
try
{
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.WindowState = XlWindowState.xlMaximized;
Microsoft.Office.Interop.Excel.Range cabecera = null;
ws.Cells[1, 1] = "Lista Compras";
ws.Range[ws.Cells[1, 1], ws.Cells[3, 11]].Merge();
cabecera = ws.get_Range("a1", "k3");
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
ws.Cells[4, 1] = "Proveedor";
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range proveedor = ws.get_Range("a4", "a4");
proveedor.BorderAround2();
proveedor.Interior.Color = Color.White;
ws.Cells[4, 2] = "Motivo";
ws.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 2].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range motivo = ws.get_Range("b4", "b4");
motivo.BorderAround2();
motivo.Interior.Color = Color.White;
ws.Cells[4, 3] = "Vencimiento";
ws.Cells[4, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range vencimiento = ws.get_Range("c4", "c4");
vencimiento.BorderAround2();
vencimiento.Interior.Color = Color.White;
ws.Cells[4, 4] = "Monto";
ws.Cells[4, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range monto = ws.get_Range("d4", "d4");
monto.BorderAround2();
monto.Interior.Color = Color.White;
ws.Cells[4, 5] = "Fecha de Ingreso";
ws.Cells[4, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range pagada = ws.get_Range("e4", "e4");
pagada.BorderAround2();
pagada.Interior.Color = Color.White;
ws.Cells[4, 6] = "Detalle";
ws.Cells[4, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range detalle = ws.get_Range("f4", "f4");
detalle.BorderAround2();
detalle.Interior.Color = Color.White;
ws.Cells[4, 7] = "Razón Social";
ws.Cells[4, 7].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 7].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 7].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range razon = ws.get_Range("g4", "g4");
razon.BorderAround2();
razon.Interior.Color = Color.White;
ws.Cells[4, 8] = "IVA";
ws.Cells[4, 8].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 8].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 8].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range iva = ws.get_Range("h4", "h4");
iva.BorderAround2();
iva.Interior.Color = Color.White;
ws.Cells[4, 9] = "Fecha de Pago";
ws.Cells[4, 9].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 9].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 9].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range fecha_pago = ws.get_Range("i4", "i4");
fecha_pago.BorderAround2();
fecha_pago.Interior.Color = Color.White;
ws.Cells[4, 10] = "Comprobante";
ws.Cells[4, 10].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 10].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 10].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range comprobante = ws.get_Range("j4", "j4");
comprobante.BorderAround2();
comprobante.Interior.Color = Color.White;
ws.Cells[4, 11] = "Pagada";
ws.Cells[4, 11].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 11].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 11].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range fecha_ingreso = ws.get_Range("k4", "k4");
fecha_ingreso.BorderAround2();
fecha_ingreso.Interior.Color = Color.White;
//********************
//Empiezo por cantidad
//********************
int fila = 5;
int columnas = 11;
for (int i = 0; i < dgv_tabla.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla.Rows[i].Cells["Proveedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
}
if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla.Rows[i].Cells["Motivo"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla.Rows[i].Cells["Vencimiento"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla.Rows[i].Cells["Monto"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 10)
{
ws.Cells[fila, 5] = dgv_tabla.Rows[i].Cells["Fecha Ingreso"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla.Rows[i].Cells["Detalle"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
else if (j == 6)
{
ws.Cells[fila, 7] = dgv_tabla.Rows[i].Cells["Razon Social"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 7].Borders);
}
else if (j == 7)
{
ws.Cells[fila, 8] = dgv_tabla.Rows[i].Cells["Iva"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 8].Borders);
}
else if (j == 8)
{
if (ControladorFuncVariadas.validarDateTimeVacio(Convert.ToDateTime(dgv_tabla.Rows[i].Cells["Fecha Pago"].Value.ToString())))
{
ws.Cells[fila, 9] = dgv_tabla.Rows[i].Cells["Fecha Pago"].Value;
}
else
{
ws.Cells[fila, 9] = "Pendiente";
}
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 9].Borders);
}
else if (j == 9)
{
ws.Cells[fila, 10] = dgv_tabla.Rows[i].Cells["Comprobante"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 10].Borders);
}
else if (j == 4)
{
if (dgv_tabla.Rows[i].Cells["Pagada"].Value.ToString() == "True")
{
ws.Cells[fila, 11] = "Sí";
}
else
{
ws.Cells[fila, 11] = "No";
}
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 11].Borders);
}
}
fila++;
}
//ws.Columns[2].NumberFormat = "######";
ws.Columns[4].NumberFormat = "$ #.###,00";
ws.Columns[6].NumberFormat = "$ #.###,00";
ws.Columns[8].NumberFormat = "$ #.###,00";
ws.Columns[3].NumberFormat = "dd/MM/yyyy";
ws.Columns[5].NumberFormat = "dd/MM/yyyy";
ws.Columns[9].NumberFormat = "dd/MM/yyyy";
ws.Columns.AutoFit();
Excel.Interactive = true;
Excel.Visible = true;
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally { Cursor.Current = Cursors.Default; }
}
public static void armarExcelReparacionesRealizadas(DataGridView dgv_tabla)
{
ControladorFuncVariadas control = new ControladorFuncVariadas();
Cursor.Current = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Interactive = false;
try
{
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.WindowState = XlWindowState.xlMaximized;
Microsoft.Office.Interop.Excel.Range cabecera = null;
ws.Cells[1, 1] = "Reparaciones Realizadas";
ws.Range[ws.Cells[1, 1], ws.Cells[3, 11]].Merge();
cabecera = ws.get_Range("a1", "k3");
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
ws.Cells[4, 1] = "Razón Social";
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range razon = ws.get_Range("a4", "a4");
razon.BorderAround2();
razon.Interior.Color = Color.White;
ws.Cells[4, 2] = "Máquina";
ws.Cells[4, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[4, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 2].Font.Bold = true;
Range maquina = ws.get_Range("b4", "b4");
maquina.BorderAround2();
maquina.Interior.Color = Color.White;
ws.Cells[4, 3] = "Nro Factura";
ws.Cells[4, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range nrofactura = ws.get_Range("c4", "c4");
nrofactura.BorderAround2();
nrofactura.Interior.Color = Color.White;
ws.Cells[4, 4] = "Tipo";
ws.Cells[4, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range tipo = ws.get_Range("d4", "d4");
tipo.BorderAround2();
tipo.Interior.Color = Color.White;
ws.Cells[4, 5] = "Remito";
ws.Cells[4, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range remito = ws.get_Range("e4", "e4");
remito.BorderAround2();
remito.Interior.Color = Color.White;
ws.Cells[4, 6] = "Total";
ws.Cells[4, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range total = ws.get_Range("f4", "f4");
total.BorderAround2();
total.Interior.Color = Color.White;
ws.Cells[4, 7] = "Fecha";
ws.Cells[4, 7].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 7].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 7].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range fecha = ws.get_Range("g4", "g4");
fecha.BorderAround2();
fecha.Interior.Color = Color.White;
ws.Cells[4, 8] = "Vencimiento";
ws.Cells[4, 8].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 8].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 8].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range vencimiento = ws.get_Range("h4", "h4");
vencimiento.BorderAround2();
vencimiento.Interior.Color = Color.White;
ws.Cells[4, 9] = "Fecha Cobro";
ws.Cells[4, 9].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 9].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 9].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range fecha_cobro = ws.get_Range("i4", "i4");
fecha_cobro.BorderAround2();
fecha_cobro.Interior.Color = Color.White;
ws.Cells[4, 10] = "Vendedor";
ws.Cells[4, 10].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 10].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 10].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range vendedor = ws.get_Range("j4", "j4");
vendedor.BorderAround2();
vendedor.Interior.Color = Color.White;
ws.Cells[4, 11] = "Cobrada";
ws.Cells[4, 11].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 11].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 11].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range cobrada = ws.get_Range("k4", "k4");
cobrada.BorderAround2();
cobrada.Interior.Color = Color.White;
//********************
//Empiezo por cantidad
//********************
int fila = 5;
int columnas = 11;
for (int i = 0; i < dgv_tabla.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla.Rows[i].Cells["Razon Social"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
//ws.Cells[fila, 1].Color = Color.White;
}
else if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla.Rows[i].Cells[2].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla.Rows[i].Cells["Nro Factura"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla.Rows[i].Cells["Tipo"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 5] = dgv_tabla.Rows[i].Cells["Remito"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla.Rows[i].Cells["Total"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
else if (j == 6)
{
ws.Cells[fila, 7] = dgv_tabla.Rows[i].Cells["Fecha"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 7].Borders);
}
else if (j == 7)
{
ws.Cells[fila, 8] = dgv_tabla.Rows[i].Cells["Vencimiento"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 8].Borders);
}
else if (j == 8)
{
ws.Cells[fila, 9] = dgv_tabla.Rows[i].Cells["Fecha de Cobro"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 9].Borders);
}
else if (j == 9)
{
ws.Cells[fila, 10] = dgv_tabla.Rows[i].Cells["Vendedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 10].Borders);
}
else if (j == 10)
{
if (dgv_tabla.Rows[i].Cells["Cobrada"].Value.ToString() == "True")
{
ws.Cells[fila, 11] = "Sí";
}
else
{
ws.Cells[fila, 11] = "No";
}
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 11].Borders);
}
}
fila++;
}
ws.Columns[2].NumberFormat = "######";
ws.Columns[6].NumberFormat = "$ #.###,00";
ws.Columns.AutoFit();
Excel.Interactive = true;
Excel.Visible = true;
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally { Cursor.Current = Cursors.Default; }
}
public static int AgregarReparacion(Reparacion a)
{
int retorno = 0;
try
{
MySqlCommand comando = new MySqlCommand(string.Format("INSERT INTO reparaciones (documento, problema, fecha_salida, id_motor, maquina, comentarios) VALUES('{0}','{1}','{2}', '{3}','{4}','{5}')",
a.documento, a.problema, ControladorFuncVariadas.convertirFecha(a.fecha_salida), a.id_motor, a.maquina, a.comentarios), Conexion.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error" + e);
}
return(retorno);
}
public static int actualizarReparacion(Modelo.Reparacion reparacion, DataGridView dgv_tabla, bool nuevasFilas)
{
int retorno = 0;
MySqlConnection conexion = Conexion.ObtenerConexion();
int entregado = 0;
if (reparacion.entregado)
{
entregado = 1;
}
try
{
MySqlCommand comando = new MySqlCommand(string.Format("Update reparaciones set problema = '{0}', comentarios ='{1}', fecha_salida ='{2}', entregado='{3}' where id={4}",
reparacion.problema, reparacion.comentarios, ControladorFuncVariadas.convertirFecha(reparacion.fecha_salida), entregado, reparacion.id), conexion);
Console.WriteLine(comando.CommandText);
Console.WriteLine(comando);
retorno = comando.ExecuteNonQuery();
conexion.Close();
}
catch (Exception ex) { MessageBox.Show("Error en el metodo Actualizar Reparacion\n" + ex); }
insertarDetalleReparacion(dgv_tabla, reparacion.id, nuevasFilas);
return(retorno);
}