private void btnOk_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
ConexionMba cs = new ConexionMba();
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
string cadena = "SELECT PRODUCT_ID AS `CODIGO`, DESCRIPTION AS `NOMBRE`, CODE_PROV_O_ALT `CODIGO BARRAS`, img.Fotografia AS `IMAGEN` " +
" FROM {oj INVT_FICHA_PRINCIPAL inv LEFT OUTER JOIN SIST_IMAGENES img ON img.REFERENCIA = " +
" inv.PRODUCT_ID_CORP } WHERE(CODE_PROV_O_ALT <> '') AND(PRODUCT_ID_CORP LIKE '%LABOV') ORDER BY DESCRIPTION";
//string cadena = "SELECT DISTINCT PRODUCT_ID AS CODIGO, DESCRIPTION AS NOMBRE, CODE_PROV_O_ALT AS `CODIGO BARRAS`, img.Fotografia AS `IMAGEN` FROM {oj INVT_FICHA_PRINCIPAL inv LEFT OUTER JOIN SIST_IMAGENES img ON img.REFERENCIA = inv.PRODUCT_ID_CORP } WHERE(CODE_PROV_O_ALT <> '') AND (PRODUCT_ID_CORP LIKE '%LABOV') AND(PRODUCT_ID LIKE '%GV000166%') OR (PRODUCT_ID LIKE 'GV000180') ORDER BY DESCRIPTION";
dataGridView1.RowTemplate.Height = 120;
fg.FillDataGridImg(cadena, dataGridView1);
//Hace falsa la columna donde se encuentra el barcode en numeros
dataGridView1.Columns[2].Visible = false;
var writer = new BarcodeWriter
{
Format = BarcodeFormat.EAN_13,
Options = new ZXing.Common.EncodingOptions
{
Width = 120,
Height = 60
}
};
DataGridViewImageColumn imageColumn = new DataGridViewImageColumn();
imageColumn = (DataGridViewImageColumn)dataGridView1.Columns[3];
imageColumn.ImageLayout = DataGridViewImageCellLayout.Stretch;
//dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataGridViewImageColumn Barras = new DataGridViewImageColumn();
dataGridView1.Columns.Add(Barras);
imageColumn = (DataGridViewImageColumn)dataGridView1.Columns[4];
imageColumn.ImageLayout = DataGridViewImageCellLayout.Normal;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
for (int i = 0; i < dataGridView1.RowCount; i++)
{
string content = dataGridView1.Rows[i].Cells[2].Value.ToString();
dataGridView1.Rows[i].Cells[4].Value = writer.Write(content);
}
dataGridView1.Columns[0].HeaderText = "Código";
dataGridView1.Columns[1].HeaderText = "Producto";
dataGridView1.Columns[3].HeaderText = "Imagen";
dataGridView1.Columns[4].HeaderText = "Cod. Barras";
}
private void btnOk_Click(object sender, EventArgs e)
{
//flag para chequear si existen un Acreedor en particular
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
Boolean flag;
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string _Acree = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
string Acree = "SELECT CODIGO_PROVEEDOR_EMPRESA FROM PROV_FICHA_PRINCIPAL" +
" WHERE VENDOR_NAME ='" + cmbAcreedor.Text + "'" +
" AND CODIGO_PROVEEDOR_EMPRESA LIKE '%" + _CORP + "'";
DbCommand = new OdbcCommand(Acree, cs.getConexion());
reader = DbCommand.ExecuteReader();
while (reader.Read())
{
_Acree = reader.GetString(0);
}
cs.cerrarConexion();
string cadena = "";
fg.FillDataGrid(cadena, dataGridView1);
return;
}
void ObtenerFacturas()
{
ConexionMba cs = new ConexionMba();
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
string cadena = " SELECT FP.EMPRESA `EMPRESA `, CASE FP.ORIGEN WHEN 'PRI' THEN 'COSTA'" +
" WHEN 'LA2' THEN 'SIERRA' WHEN 'LE2' THEN 'SIERRA' " +
" WHEN 'DA2' THEN 'SIERRA' WHEN 'FA2' THEN 'SIERRA' WHEN 'AN2' THEN 'SIERRA' " +
" WHEN 'ME2' THEN 'SIERRA' WHEN 'LA3' THEN 'AUSTRO' WHEN 'LE3' THEN 'AUSTRO' " +
" WHEN 'DA3' THEN 'AUSTRO' WHEN 'ME3' THEN 'AUSTRO' WHEN 'FA3' THEN 'AUSTRO' " +
" WHEN 'AN3' THEN 'AUSTRO' END AS REGION, " +
" FIP.ESTADO `PROVINCIA`, FIP.CIUDAD_PRINCIPAL `CIUDAD`, FIP.NOMBRE_SECTOR `SECTOR`, " +
" DATE_TO_CHAR(FP.fecha_factura, 'dd[/]mm[/]yyyy') AS `FECHA FACT`, " +
" CAST(FP.VALOR_FACTURA AS float) `VALOR TOTAL`, FP.codigo_factura `FACTURA`, " +
" DATE_TO_CHAR(FP.FECHA_EMBARQUE, 'dd[/]mm[/]yyyy') AS `DESPACHO`, " +
" FIP.IDENTIFICACION_FISCAL `IDENTIFICACION`, FIP.NOMBRE_CLIENTE `CLIENTE`, FIP.DIRECCION_PRINCIPAL_1 `DIRECCION`," +
" VEN.DESCRIPTION_SPN `VENDEDOR`, FP.VALOR_FACTURA `VALOR BRUTO`, FP.TOTAL_DEVOLUCION `NOTA DE CREDITO`, " +
" (FP.VALOR_FACTURA - FP.TOTAL_DEVOLUCION) `VALOR NETO`" +
" FROM " +
" CLNT_FACTURA_PRINCIPAL FP " +
" INNER JOIN " +
" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
" ON FP.CODIGO_CLIENTE_EMPRESA = FIP.CODIGO_CLIENTE_EMPRESA " +
" WHERE ANULADA = CAST('FALSE' AS BOOLEAN) AND CONFIRMADO = CAST('TRUE' AS BOOLEAN) " +
" AND FECHA_FACTURA >= '" + Fech1 + "' AND FECHA_FACTURA <='" + Fech2 + "' AND FIP.CLIENT_TYPE IN ('DISTR', 'FARMA')" +
" AND VEN.GROUP_CATEGORY = 'SELLm' AND VEN.CORP=FIP.EMPRESA " +
" AND FP.codigo_factura = '" + cmbCodigo.Text + "'";
if (cmbEmpresa.SelectedIndex != -1)
{
cadena += " AND FP.EMPRESA ='" + _CORP + "' ";
}
fg.FillDataGrid(cadena, dataGridView1);
}
}
void ObtenerFacturas()
{
ConexionMba cs = new ConexionMba();
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad = "";
string flag_region = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
string cadena = " SELECT FP.EMPRESA `EMPRESA `, CASE FP.ORIGEN WHEN 'PRI' THEN 'COSTA'" +
" WHEN 'LA2' THEN 'SIERRA' WHEN 'LE2' THEN 'SIERRA' " +
" WHEN 'DA2' THEN 'SIERRA' WHEN 'FA2' THEN 'SIERRA' WHEN 'AN2' THEN 'SIERRA' " +
" WHEN 'ME2' THEN 'SIERRA' WHEN 'LA3' THEN 'AUSTRO' WHEN 'LE3' THEN 'AUSTRO' " +
" WHEN 'DA3' THEN 'AUSTRO' WHEN 'ME3' THEN 'AUSTRO' WHEN 'FA3' THEN 'AUSTRO' " +
" WHEN 'AN3' THEN 'AUSTRO' END AS REGION, " +
" FIP.ESTADO `PROVINCIA`, FIP.CIUDAD_PRINCIPAL `CIUDAD`, FIP.NOMBRE_SECTOR `SECTOR`, " +
// " FP.codigo_cliente, " +
" DATE_TO_CHAR(FP.fecha_factura, 'dd[/]mm[/]yyyy') AS `FECHA FACT`, " +
" CAST(FP.VALOR_FACTURA AS float) `VALOR TOTAL`, FP.codigo_factura `FACTURA`, " +
" DATE_TO_CHAR(FP.FECHA_EMBARQUE, 'dd[/]mm[/]yyyy') AS `DESPACHO`, " +
// " FP.numero_factura, FP.origen, FP.total_devolucion, valor_factura, " +
" FIP.IDENTIFICACION_FISCAL `IDENTIFICACION`, FIP.NOMBRE_CLIENTE `CLIENTE`, FIP.DIRECCION_PRINCIPAL_1 `DIRECCION`," +
" VEN.DESCRIPTION_SPN `VENDEDOR`, FP.VALOR_FACTURA `VALOR BRUTO`, FP.TOTAL_DEVOLUCION `NOTA DE CREDITO`, " +
" (FP.VALOR_FACTURA - FP.TOTAL_DEVOLUCION) `VALOR NETO`" +
// "FIP.CLIENT_TYPE, FIP.SALESMAN, VEN.DESCRIPTION_SPN " +
" FROM " +
" CLNT_FACTURA_PRINCIPAL FP " +
" INNER JOIN " +
" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
" ON FP.CODIGO_CLIENTE_EMPRESA = FIP.CODIGO_CLIENTE_EMPRESA " +
" WHERE ANULADA = CAST('FALSE' AS BOOLEAN) AND CONFIRMADO = CAST('TRUE' AS BOOLEAN) " +
" AND VEN.GROUP_CATEGORY = 'SELLm' AND VEN.CORP=FIP.EMPRESA ";
//" AND FP.EMPRESA = '" + _CORP + "' ";
//" AND VEN.CODE= '" + + "' " +
fg.FillDataGrid(cadena, dataGridView1);
}
public void FillDataGridImg(string cadena, DataGridView dataGridView1)
{
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.AllowUserToAddRows = false;
ConexionMba cn = new ConexionMba();
OdbcCommand DbCommand = new OdbcCommand(cadena, cn.getConexion());
OdbcDataAdapter adp1 = new OdbcDataAdapter();
DataTable dt = new DataTable();
adp1.SelectCommand = DbCommand;
adp1.Fill(dt);
if (dt.Rows.Count > 0)
{
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
}
cn.cerrarConexion();
}
void Pruebita()
{
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
if (!string.IsNullOrEmpty(cmbEmpresa.Text))
{
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad;
while (reader.Read())
{
_CORP = reader.GetString(0);
if (cmbLocalidad.SelectedIndex == 0)
{
flag_localidad = "C";
}
else
{
flag_localidad = "G";
}
string cadena = " SELECT VEN.DESCRIPTION_SPN, TRUNC(SUM(valor_factura), 2) `Valor Factura`" +
" FROM " +
" CLNT_FACTURA_PRINCIPAL FP " +
" INNER JOIN " +
" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
" ON FP.CODIGO_CLIENTE_EMPRESA = FIP.CODIGO_CLIENTE_EMPRESA " +
" WHERE ANULADA = CAST('FALSE' AS BOOLEAN) AND CONFIRMADO = CAST('TRUE' AS BOOLEAN) " +
" AND FECHA_FACTURA >= AND FIP.CLIENT_TYPE IN ('DISTR', 'FARMA')" +
" AND VEN.GROUP_CATEGORY = 'SELLm' AND VEN.CORP=FIP.EMPRESA " +
" AND FP.EMPRESA = '" + _CORP + "' " +
" AND FIP.ZONA= '" + flag_localidad + "' " +
" GROUP BY VEN.DESCRIPTION_SPN ";
fg.FillDataGrid(cadena, dataGridView1);
}
cs.cerrarConexion();
}
}
private void CargarCombo()
{
ConexionMba cs = new ConexionMba();
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
string query = "SELECT BANC_Ficha_Principal.NOMBRE_CUENTA_CONTABLE FROM BANC_Ficha_Principal " +
" BANC_FICHA_PRINCIPAL, SIST_Parametros_Empresa SIST_Parametros_Empresa WHERE SIST_Parametros_Empresa.CORP = BANC_Ficha_Principal.EMPRESA " +
" AND SIST_Parametros_Empresa.CORP= '" + _CORP + "'";
fg.LlenarCombo(query, cmbCBancaria, 1);
}
private void Fill(string query, ComboBox cb, int a)
{
ConexionMba cn = new ConexionMba();
OdbcCommand DbCommand = new OdbcCommand(query, cn.getConexion());
OdbcDataAdapter adp1 = new OdbcDataAdapter();
DataTable dt = new DataTable();
DbCommand.Connection = cn.getConexion();
DbCommand.CommandText = query;
adp1.SelectCommand = DbCommand;
adp1.Fill(dt);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
cb.Items.Add(dt.Rows[i][a].ToString());
}
}
cn.cerrarConexion();
}
private void CargarDataGrid()
{
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
Boolean flag;
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string empresa = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
if (!chkAllProv.Checked)
{
empresa = " AND FP.EMPRESA = '" + _CORP + "'";
}
else
{
empresa = " ";
}
cs.cerrarConexion();
string cadena = "SELECT INV.PRODUCT_ID `CODIGO`, " +
" INV.PRODUCT_NAME `PRODUCTO`, IFP.TIPO_ORIGEN `SEGMENTO`, INV.QUANTITY CANTIDAD , CASE " +
" WHEN INV.LINE_TOTAL > 0 THEN 'FACTURADO' " +
" WHEN INV.LINE_TOTAL = 0 THEN 'BONIFICADO'" +
" ELSE 'N/A'" +
" END AS `TIPO DE VENTA`, " +
" FIP.NOMBRE_CLIENTE `CLIENTE`, FIP.CLIENT_TYPE `TIPO CLIENTE`, FP.CODIGO_FACTURA `FACTURA`, DATE_TO_CHAR(FP.FECHA_FACTURA, 'dd[/]mm[/]yyyy') AS `FECHA FACTURA`, " +
" CASE FP.ORIGEN " +
" WHEN 'PRI' THEN 'COSTA' WHEN 'LA2' THEN 'SIERRA' WHEN 'LE2' THEN 'SIERRA' " +
" WHEN 'DA2' THEN 'SIERRA' WHEN 'FA2' THEN 'SIERRA' WHEN 'AN2' THEN 'SIERRA' " +
" WHEN 'ME2' THEN 'SIERRA' WHEN 'LA3' THEN 'AUSTRO' WHEN 'LE3' THEN 'AUSTRO' " +
" WHEN 'DA3' THEN 'AUSTRO' WHEN 'ME3' THEN 'AUSTRO' WHEN 'FA3' THEN 'AUSTRO' " +
" WHEN 'AN3' THEN 'AUSTRO' END AS REGION," +
" FP.VENDEDOR `CODIGO VENDEDOR`, VEN.DESCRIPTION_SPN `VENDEDOR`, FIP.ZONA `CIUDAD O GIRA`,FIP.ESTADO `PROVINCIA`, FIP.CIUDAD_PRINCIPAL `CIUDAD`, " +
" FIP.NOMBRE_SECTOR `PARROQUIA`, INV.LINE_TOTAL `VALOR LINEA` " +
" FROM CLNT_FACTURA_PRINCIPAL FP INNER JOIN " +
" INVT_PRODUCTO_MOVIMIENTOS INV ON FP.CODIGO_FACTURA = INV.DOC_ID_CORP2, " +
" CLNT_FICHA_PRINCIPAL FIP, SIST_LISTA_1 VEN, INVT_FICHA_PRINCIPAL IFP " +
" WHERE FP.ANULADA = CAST('FALSE' AS BOOLEAN) AND " +
" FIP.CODIGO_CLIENTE_EMPRESA=FP.CODIGO_CLIENTE_EMPRESA AND " +
" VEN.CODE = FP.VENDEDOR AND " +
" VEN.GROUP_CATEGORY='SELLm'AND " +
" VEN.CORP = FP.EMPRESA AND " +
" IFP.PRODUCT_ID_CORP = INV.PRODUCT_ID_CORP AND" +
" FP.CONFIRMADO = CAST('TRUE' AS BOOLEAN) AND " +
" FP.FECHA_FACTURA >= '" + Fech1 + "' AND " +
" FP.FECHA_FACTURA <= '" + Fech2 + "' AND" +
" INV.PRODUCT_ID IS NOT NULL " + empresa + " AND " +
" FIP.CLIENT_TYPE NOT LIKE '%EMPRE%' AND FIP.CLIENT_TYPE NOT LIKE '%TERCE%'" +
" ORDER BY FP.CODIGO_FACTURA";
fg.FillDataGrid(cadena, dataGridView1);
}
}
private void btnOk_Click(object sender, EventArgs e)
{
//flag para chequear si existen un Acreedor en particular
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
if (!string.IsNullOrEmpty(cmbEmpresa.Text))
{
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad;
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
if (cmbLocalidad.SelectedIndex == 0)
{
flag_localidad = "C";
}
else
{
flag_localidad = "G";
}
string cadena = "SELECT " +
" Vend.DESCRIPTION_SPN as `VENDEDOR`, " +
" INVT_Presupuesto_Detalle.PRES_VALOR_" + (cmbMes.SelectedIndex + 1) + " as CUOTA, " +
" CASE Vend.Origin WHEN 'PRI' THEN 'COSTA' WHEN 'LA2' THEN 'SIERRA' WHEN 'LA3' THEN " +
" 'AUSTRO' END AS REGION " +
" FROM INVT_Presupuesto_Detalle, " +
" SIST_Lista_1 Vend WHERE(INVT_Presupuesto_Detalle.PRES_CODIGO_ID_CORP = 'PR01-" + _CORP + "') " +
" AND Vend.GROUP_CATEGORY = 'SELLm' AND INVT_Presupuesto_Detalle.PRES_VENDEDOR = Vend.CODE " +
" AND Vend.CORP = INVT_Presupuesto_Detalle.CORP AND INVT_Presupuesto_Detalle.PRES_CODIGO_ZONA = '" + flag_localidad + "'";
//string cadena = "SELECT Vend.DESCRIPTION_SPN as `VENDEDOR`, " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_1 as `ENERO`," +
// " INVT_Presupuesto_Detalle.PRES_VALOR_2 as `FEBRERO`, INVT_Presupuesto_Detalle.PRES_VALOR_3 as `MARZO`, " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_4 as `ABRIL`, INVT_Presupuesto_Detalle.PRES_VALOR_5 as `MAYO`, " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_6 as `JUNIO`, INVT_Presupuesto_Detalle.PRES_VALOR_7 AS `JULIO` , " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_8 AS `AGOSTO`, INVT_Presupuesto_Detalle.PRES_VALOR_9 AS `SEPTIEMBRE`, " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_10 AS `OCTUBRE`, INVT_Presupuesto_Detalle.PRES_VALOR_11 AS `NOVIEMBRE`, " +
// " INVT_Presupuesto_Detalle.PRES_VALOR_12 AS `DICIEMBRE` " +
// " FROM INVT_Presupuesto_Detalle " +
// " INVT_Presupuesto_Detalle, SIST_Lista_1 Vend WHERE(INVT_Presupuesto_Detalle.PRES_CODIGO_ID_CORP = 'PR02-LABOV') " +
// " AND Vend.GROUP_CATEGORY = 'SELLm' AND INVT_Presupuesto_Detalle.PRES_VENDEDOR = Vend.CODE " +
// " AND Vend.CORP = 'LABOV' AND INVT_Presupuesto_Detalle.PRES_CODIGO_ZONA = '" + flag_localidad + "'";
fg.FillDataGrid(cadena, dataGridView1);
#region Valores totales
//double[] totales = new double[1];
//for (int a = 1; a <= 1; a++)
//{
// double sum = 0.00;
// for (int i = 0; i < dataGridView1.Rows.Count; ++i)
// {
// sum += Convert.ToDouble(dataGridView1.Rows[i].Cells[a].Value);
// }
// totales[0] = sum;
//}
//txtTotal.Text = Math.Round(totales[0], 2).ToString();
#endregion
}
return;
}
void ObtenerFacturas()
{
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
if (!string.IsNullOrEmpty(cmbEmpresa.Text))
{
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad;
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
if (cmbLocalidad.SelectedIndex == 0)
{
flag_localidad = "C";
}
else
{
flag_localidad = "G";
}
string cadena = " SELECT FP.codigo_factura, FP.codigo_cliente_empresa, FP.empresa," +
" DATE_TO_CHAR(FP.fecha_factura, 'dd[/]mm[/]yyyy') AS `fecha fact`, " +
" FP.numero_factura, FP.origen, FP.total_devolucion, valor_factura, " +
" FIP.CLIENT_TYPE, FIP.SALESMAN, VEN.DESCRIPTION_SPN " +
" FROM " +
" CLNT_FACTURA_PRINCIPAL FP " +
" INNER JOIN " +
" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
" ON FP.CODIGO_CLIENTE_EMPRESA = FIP.CODIGO_CLIENTE_EMPRESA " +
" WHERE ANULADA = CAST('FALSE' AS BOOLEAN) AND CONFIRMADO = CAST('TRUE' AS BOOLEAN) " +
" AND FECHA_FACTURA >= '01/06/2018' AND FIP.CLIENT_TYPE IN ('DISTR', 'FARMA')" +
" AND VEN.GROUP_CATEGORY = 'SELLm' AND VEN.CORP=FIP.EMPRESA " +
" AND FP.EMPRESA = '" + _CORP + "' " +
" AND FIP.ZONA= '" + flag_localidad + "' ";
//string cadena = "SELECT " +
//" FP.codigo_factura, FP.codigo_cliente_empresa, " +
//" FP.empresa, DATE_TO_CHAR(FP.fecha_factura, 'dd[/]mm[/]yyyy') AS `Fecha factura`, " +
//" FP.numero_factura, FP.origen, FP.total_devolucion, " +
//" FP.valor_factura, FIP.CLIENT_TYPE, FIP.SALESMAN, VEN.DESCRIPTION_SPN " +
//" FROM " +
//" CLNT_FACTURA_PRINCIPAL FP " +
//" INNER JOIN " +
//" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
//" WHERE " +
//" ANULADA = CAST('FALSE' AS BOOLEAN) AND " +
//" CONFIRMADO = CAST('TRUE' AS BOOLEAN) AND " +
//" FECHA_FACTURA >= '01/06/2018' AND " +
//" FIP.CLIENT_TYPE IN ('DISTR', 'FARMA') AND " +
//" VEN.GROUP_CATEGORY = 'SELLm' AND " +
//" VEN.CORP = FIP.EMPRESA";
fg.FillDataGrid(cadena, dataGridView1);
}
}
private void btnOk_Click(object sender, EventArgs e)
{
//flag para chequear si existen un Acreedor en particular
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
Boolean flag;
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string _Acree = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
if (!chkAllProv.Checked)
{
string Acree = "SELECT CODIGO_PROVEEDOR_EMPRESA FROM PROV_FICHA_PRINCIPAL" +
" WHERE VENDOR_NAME ='" + cmbAcreedor.Text + "'" +
" AND CODIGO_PROVEEDOR_EMPRESA LIKE '%" + _CORP + "'";
DbCommand = new OdbcCommand(Acree, cs.getConexion());
reader = DbCommand.ExecuteReader();
while (reader.Read())
{
_Acree = reader.GetString(0);
}
cs.cerrarConexion();
flag = true;
}
else
{
flag = false;
}
string cadena = "SELECT pfp.VENDOR_NAME AS Proveedor , TRIM(' DIAS' FROM pfp.`TERMS ALFA`) AS `Dias Credito`, " +
" fp.DOC_REFERENCE AS Factura, " +
" DATE_TO_CHAR(fp.INVOICE_DATE, 'dd[/]mm[/]yyyy') AS `Fecha factura`," +
" fp.`RETENTION BASIS` AS Subtotal, fp.MONTO_IMPUESTO_1 as `Total Impuestos`, " +
" fp.`AMOUNT TAX2` AS `Total Cr Tributario`,fp.INVOICE_TOTAL AS `Total Factura`, " +
" fp.`AMOUNT PAID` AS `Total Pagos`, fp.`BALANCE` AS `Saldos Actual`, " +
" fp.MEMO as `F.Memo`, " +
" pfp.LOCALIZACION_PROVEEDOR as `L / E` " +
" FROM PROV_Factura_Principal fp, " +
" PROV_Ficha_Principal pfp " +
" WHERE " +
" fp.VENDOR_ID_CORP = pfp.CODIGO_PROVEEDOR_EMPRESA AND " +
" fp.VOID = cast('False' as Boolean) AND " +
" fp.INVOICE_DATE >= '" + Fech1 + "' AND " +
" fp.INVOICE_DATE <= '" + Fech2 + "' AND " +
" fp.CORP = '" + _CORP + "' ";
//"pcu.`PAYMENT AMOUNT` < fp.INVOICE_TOTAL";
// string cadena = "SELECT `INVOICE ID`, CORP, CON_DATOS, REFERENCIA_3, COBRADOR, PEDIDO_N FROM CLNT_Factura_Principal_Adiciona";
if (flag)
{
cadena += " AND fp.VENDOR_ID_CORP = '" + _Acree + "'";
}
fg.FillDataGrid(cadena, dataGridView1);
}
return;
}
void ObtenerFacturas()
{
ConexionMba cs = new ConexionMba();
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad = "";
string flag_region = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
switch (cmbLocalidad.SelectedIndex)
{
case 0:
flag_localidad = "C";
break;
case 1:
flag_localidad = "G";
break;
}
string cadena = " SELECT FP.EMPRESA `EMPRESA `, CASE FP.ORIGEN WHEN 'PRI' THEN 'COSTA'" +
" WHEN 'LA2' THEN 'SIERRA' WHEN 'LE2' THEN 'SIERRA' " +
" WHEN 'DA2' THEN 'SIERRA' WHEN 'FA2' THEN 'SIERRA' WHEN 'AN2' THEN 'SIERRA' " +
" WHEN 'ME2' THEN 'SIERRA' WHEN 'LA3' THEN 'AUSTRO' WHEN 'LE3' THEN 'AUSTRO' " +
" WHEN 'DA3' THEN 'AUSTRO' WHEN 'ME3' THEN 'AUSTRO' WHEN 'FA3' THEN 'AUSTRO' " +
" WHEN 'AN3' THEN 'AUSTRO' END AS REGION, " +
" FIP.ESTADO `PROVINCIA`, FIP.CIUDAD_PRINCIPAL `CIUDAD`, FIP.NOMBRE_SECTOR `SECTOR`, " +
// " FP.codigo_cliente, " +
" DATE_TO_CHAR(FP.fecha_factura, 'dd[/]mm[/]yyyy') AS `FECHA FACT`, " +
" CAST(FP.VALOR_FACTURA AS float) `VALOR TOTAL`, FP.codigo_factura `FACTURA`, " +
" DATE_TO_CHAR(FP.FECHA_EMBARQUE, 'dd[/]mm[/]yyyy') AS `DESPACHO`, " +
// " FP.numero_factura, FP.origen, FP.total_devolucion, valor_factura, " +
" FIP.IDENTIFICACION_FISCAL `IDENTIFICACION`, FIP.NOMBRE_CLIENTE `CLIENTE`, FIP.DIRECCION_PRINCIPAL_1 `DIRECCION`," +
" VEN.DESCRIPTION_SPN `VENDEDOR`, FP.VALOR_FACTURA `VALOR BRUTO`, FP.TOTAL_DEVOLUCION `NOTA DE CREDITO`, " +
" (FP.VALOR_FACTURA - FP.TOTAL_DEVOLUCION) `VALOR NETO`" +
// "FIP.CLIENT_TYPE, FIP.SALESMAN, VEN.DESCRIPTION_SPN " +
" FROM " +
" CLNT_FACTURA_PRINCIPAL FP " +
" INNER JOIN " +
" (CLNT_FICHA_PRINCIPAL FIP INNER JOIN SIST_LISTA_1 VEN ON FIP.SALESMAN = VEN.CODE) " +
" ON FP.CODIGO_CLIENTE_EMPRESA = FIP.CODIGO_CLIENTE_EMPRESA " +
" WHERE ANULADA = CAST('FALSE' AS BOOLEAN) AND CONFIRMADO = CAST('TRUE' AS BOOLEAN) " +
" AND FECHA_FACTURA >= '" + Fech1 + "' AND FECHA_FACTURA <='" + Fech2 + "' AND FIP.CLIENT_TYPE IN ('DISTR', 'FARMA')" +
" AND VEN.GROUP_CATEGORY = 'SELLm' AND VEN.CORP=FIP.EMPRESA ";
//" AND FP.EMPRESA = '" + _CORP + "' ";
//" AND VEN.CODE= '" + + "' " +
if (cmbLocalidad.SelectedIndex != -1)
{
cadena += " AND FIP.ZONA= '" + flag_localidad + "' ";
}
if (cmbRegion.SelectedIndex != -1)
{
cadena += " AND FP.ORIGEN='" + CheckRegion(cmbRegion, flag_region, _CORP) + "' ";
}
if (cmbEmpresa.SelectedIndex != -1)
{
cadena += " AND FP.EMPRESA ='" + _CORP + "' ";
}
if (cmbCodigo.SelectedIndex != -1)
{
cadena += " AND VEN.CODE ='" + CheckCodeVendedor() + "' ";
}
if (_CORP == "" && cmbRegion.Text.Length > 0 && flag_region.Length == 0)
{
MessageBox.Show("Es necesario seleccionar una empresa para mostrar su region", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
fg.FillDataGrid(cadena, dataGridView1);
}
}
void ObtenerFacturas()
{
ConexionMba cs = new ConexionMba();
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string flag_localidad = "";
string flag_region = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
switch (cmbLocalidad.SelectedIndex)
{
case 0:
flag_localidad = "C";
break;
case 1:
flag_localidad = "G";
break;
}
string cadena = "SELECT CASE CAST(CCP.ANULADO AS INT) WHEN 0 THEN 'ACTIVO' WHEN 1 THEN 'ANULADO' END AS VALIDEZ ," +
" DATE_TO_CHAR(FP.FECHA_FACTURA, 'dd[/]mm[/]yyyy') AS `FECHA FACTURA`," +
" CCD.CODIGO_FACTURA `FACTURA`, CCD.CODIGO_COBRO, " +
" CFP.NOMBRE_CLIENTE `CLIENTE`, DATE_TO_CHAR(CCP.FECHA_COBRO, 'dd[/]mm[/]yyyy') AS `FECHA PAGO`," +
" DATE_TO_CHAR(CCP.FECHA_COBRO, 'dd[/]mm[/]yyyy') AS `FECHA DOCUMENTO`, " +
" DATE_TO_CHAR(FP.FECHA_VENCIMIENTO, 'dd[/]mm[/]yyyy') AS `FECHA VENCIMIENTO` ," +
" CCP.VALOR_COBRO `VALOR COBRO`, CCP.CHEQUE_NUMERO `NUMERO DOCUMENTO`, CCP.ID_FISCAL `CUENTA BANCARIA`," +
" CBFP.FECHA_CHEQUE_O_EXPIRA_TC `FECHA CHEQUE`, CCP.FORMA_DE_PAGO `FORMA DE PAGO`," +
" CCP.CODIGO_COBRADOR `CODIGO COBRADOR`, VEN.DESCRIPTION_SPN `COBRADOR` " +
" FROM CLNT_FICHA_PRINCIPAL CFP, CLNT_COBRO_DETALLE CCD," +
" CLNT_COBRO_PRINCIPAL CCP,CLNT_FACTURA_PRINCIPAL FP, CLNT_COBRO_FORMADEPAGO CBFP, SIST_LISTA_1 VEN" +
" WHERE " +
" CCD.CODIGO_COBRO = CCP.CODIGO_COBRO AND " +
" CFP.CODIGO_CLIENTE_EMPRESA = CCD.CODIGO_CLIENTE AND " +
" CCD.CODIGO_FACTURA = FP.CODIGO_FACTURA AND " +
" CBFP.CODIGO_COBRO = CCD.CODIGO_COBRO AND" +
" VEN.CODE = CCP.CODIGO_COBRADOR AND" +
" CCP.FECHA_COBRO >= '" + Fech1 + "' AND CCP.FECHA_COBRO <='" + Fech2 + "' AND" +
" VEN.GROUP_CATEGORY = 'SELLm' AND" +
" VEN.CORP=CCP.EMPRESA AND" +
" CCP.CODIGO_COBRADOR <> '' ";
if (cmbLocalidad.SelectedIndex != -1)
{
cadena += " AND CFP.ZONA= '" + flag_localidad + "' ";
}
if (cmbRegion.SelectedIndex != -1)
{
cadena += " AND FP.ORIGEN='" + CheckRegion(cmbRegion, flag_region, _CORP) + "' ";
}
if (cmbEmpresa.SelectedIndex != -1)
{
cadena += " AND CCP.EMPRESA ='" + _CORP + "' ";
}
if (cmbCodigo.SelectedIndex != -1)
{
cadena += " AND VEN.CODE ='" + CheckCodeVendedor() + "' ";
}
if (_CORP == "" && cmbRegion.Text.Length > 0 && flag_region.Length == 0)
{
MessageBox.Show("Es necesario seleccionar una empresa para mostrar su region", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
fg.FillDataGrid(cadena, dataGridView1);
}
}
private void CargarDataGrid()
{
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
Boolean flag;
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string _Acree = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
if (!chkAllProv.Checked)
{
string Acree = "SELECT CODIGO_CLIENTE_EMPRESA " +
" FROM CLNT_FICHA_PRINCIPAL WHERE" +
" NOMBRE_CLIENTE = '" + cmbAcreedor.Text + "'" +
" and EMPRESA = '" + _CORP + "'";
DbCommand = new OdbcCommand(Acree, cs.getConexion());
reader = DbCommand.ExecuteReader();
while (reader.Read())
{
_Acree = reader.GetString(0);
}
cs.cerrarConexion();
flag = true;
}
else
{
flag = false;
}
string cadena = "SELECT CLNT.empresa `EMPRESA`, CLNT.CODIGO_CLIENTE_EMPRESA `CODIGO CLIENTE`, " +
" CLNT.CHEQUE_NUMERO `CHEQUE`, CLNT.VALOR_COBRO `VALOR COBRO`," +
" DATE_TO_CHAR(CLNT.FECHA_COBRO, 'dd[/]mm[/]yyyy') AS `FECHA COBRO` , " +
" CLNT.CODIGO_COBRO `CODIGO COBRO`, CLNT.ID_FISCAL `CUENTA BANCARIA` , CBR.`BANK O CC TYPE` `BANCO`, " +
" DATE_TO_CHAR( CBR.FECHA_CHEQUE_O_EXPIRA_TC, 'dd[/]mm[/]yyyy') AS `FECHA CHEQUE` , CASE CBR.ORIGEN " +
" WHEN 'PRI' THEN 'COSTA' WHEN 'LA2' THEN 'SIERRA' WHEN 'LE2' THEN 'SIERRA' " +
" WHEN 'DA2' THEN 'SIERRA' WHEN 'FA2' THEN 'SIERRA' WHEN 'AN2' THEN 'SIERRA' " +
" WHEN 'ME2' THEN 'SIERRA' WHEN 'LA3' THEN 'AUSTRO' WHEN 'LE3' THEN 'AUSTRO' " +
" WHEN 'DA3' THEN 'AUSTRO' WHEN 'ME3' THEN 'AUSTRO' WHEN 'FA3' THEN 'AUSTRO' " +
" WHEN 'AN3' THEN 'AUSTRO' END AS REGION, CFP.NOMBRE_CLIENTE `NOMBRE CLIENTE`," +
" VEN.DESCRIPTION_SPN `COBRADOR` " +
" FROM CLNT_COBRO_FORMADEPAGO CBR, CLNT_COBRO_PRINCIPAL CLNT, CLNT_FICHA_PRINCIPAL CFP," +
" SIST_LISTA_1 VEN " +
" WHERE CBR.CODIGO_COBRO = CLNT.CODIGO_COBRO AND " +
" CLNT.CODIGO_CLIENTE_EMPRESA = CFP.CODIGO_CLIENTE_EMPRESA AND " +
" CLNT.ANULADO = cast('False' as Boolean) AND " +
" CLNT.FORMA_DE_PAGO='CHEQUE' AND CBR.FORMA_DE_PAGO='Cheque' AND " +
" CLNT.Codigo_Cobrador = VEN.CODE AND" +
" VEN.GROUP_CATEGORY = 'SELLm' AND " +
" VEN.CORP=CLNT.EMPRESA AND " +
" CLNT.EMPRESA = '" + _CORP + "' AND" +
" CBR.FECHA_CHEQUE_O_EXPIRA_TC >= '" + Fech1 + "' AND " +
" CBR.FECHA_CHEQUE_O_EXPIRA_TC <= '" + Fech2 + "' ";
if (flag)
{
cadena += " AND CLNT.CODIGO_CLIENTE_EMPRESA = '" + _Acree + "'";
}
fg.FillDataGrid(cadena, dataGridView1);
}
return;
}
private void CargarDataGrid()
{
ConexionMba cs = new ConexionMba();
CleanGrid(dataGridView1);
Boolean flag;
if (!fg.CheckDatePicker(dtpFechAct, dtpFechFin))
{
string Fech1 = dtpFechAct.Value.ToString("dd/MM/yyyy");
string Fech2 = dtpFechFin.Value.ToString("dd/MM/yyyy");
string CORP = "SELECT CORP FROM SIST_Parametros_Empresa WHERE `CORPORATION NAM`= '" + cmbEmpresa.Text + "' ";
OdbcCommand DbCommand = new OdbcCommand(CORP, cs.getConexion());
OdbcDataReader reader = DbCommand.ExecuteReader();
string _CORP = "";
string _Acree = "";
while (reader.Read())
{
_CORP = reader.GetString(0);
}
cs.cerrarConexion();
if (!chkAllProv.Checked)
{
string Acree = "SELECT CODIGO_BANCO_EMPRESA, FECHA_PAGO, " +
" BENEFICIARIO, VALOR, MEMO, EMPRESA, info_creacion," +
" ORIGEN FROM BANC_MOVIMIENTOS_PRINCIPAL WHERE" +
" ANULADO= cast('False' as Boolean) " +
" and Pago_numero=0 AND PAGO_O_DEPOSITO='C' and " +
" IMPRESO= cast('True' as Boolean) and" +
" BENEFICIARIO = '" + cmbAcreedor.Text + "'" +
" and EMPRESA = '" + _CORP + "'";
DbCommand = new OdbcCommand(Acree, cs.getConexion());
reader = DbCommand.ExecuteReader();
while (reader.Read())
{
_Acree = reader.GetString(0);
}
cs.cerrarConexion();
flag = true;
}
else
{
flag = false;
}
string cadena = "SELECT mov.CODIGO_BANCO_EMPRESA, mov.FECHA_PAGO, " +
" banc.Cuenta_banco, banc.Nombre_banco ," +
" mov.BENEFICIARIO, mov.VALOR, mov.MEMO, mov.EMPRESA, mov.info_creacion," +
" mov.ORIGEN " +
" FROM BANC_MOVIMIENTOS_PRINCIPAL mov " +
" LEFT OUTER JOIN BANC_FICHA_PRINCIPAL banc " +
" ON banc.CODIGO_BANCO_EMPRESA = mov.CODIGO_BANCO_EMPRESA " +
" WHERE " +
" mov.ANULADO = cast('False' as Boolean) " +
" and mov.Pago_numero=0 AND mov.PAGO_O_DEPOSITO='C' and " +
" mov.IMPRESO= cast('True' as Boolean) " +
" and mov.EMPRESA = '" + _CORP + "' and " +
" mov.Fecha_Pago >= '" + Fech1 + "' and mov.Fecha_Pago <= '" + Fech2 + "' ";
// string cadena = "SELECT `INVOICE ID`, CORP, CON_DATOS, REFERENCIA_3, COBRADOR, PEDIDO_N FROM CLNT_Factura_Principal_Adiciona";
if (flag)
{
cadena += " AND mov.Beneficiario = '" + _Acree + "'";
}
fg.FillDataGrid(cadena, dataGridView1);
}
}