public void importar() { eventLog eventTracerLog = new eventLog(); SqlConnection Conn; SqlCommand cmdPedidos; String strQuery; String queryValues; Boolean verifyInsert = true; Boolean errorFound = false; String[] ColumnsName = { "tipo", "campo_desconocido", "idProveedor", "idPedido", "fechaDesde", "fechaHasta", "idMaterial", "descripcion", "medida", "mail", "centro", "nameProveedor", "idSAP", "centroDesc", "posicion", "cantidad", "ocabi" }; String[] ColumnsTypes = { "string", "int", "int", "int", "string", "string", "int", "string", "string", "string", "string", "string", "string", "string", "int", "int", "string" }; DataSet dsExcel = new DataSet(); String[] valoresRow = new String[ColumnsName.Length]; SqlDataAdapter dtPedidoConsulta = new SqlDataAdapter(); DataSet dsPedidoConsulta = new DataSet(); Boolean pedidoExist = false; String baseDir = "e:\\proveedores\\compras_ofertas\\"; try { if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv"); } // COPIA DEL ARCHIVO File.Copy(baseDir+"salidawebprd.txt", AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv",true); String strTextFile = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv"); strTextFile = strTextFile.Replace(',', '.'); strTextFile = strTextFile.Replace('"', 'º'); File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/salidawebprd.csv", strTextFile); String strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};" + "Dbq=" + AppDomain.CurrentDomain.BaseDirectory + ";"; // Get all Table Names OdbcConnection conn = new OdbcConnection(strConn); OdbcDataAdapter daExcel = new OdbcDataAdapter("SELECT * FROM [salidawebprd.csv]", conn); daExcel.Fill(dsExcel); if (dsExcel.Tables[0].Rows.Count > 0) { Conn = new SqlConnection("Persist Security Info=False;Data Source=localhost;Initial Catalog=zeniprovedores;User ID=arlequinsql;Password=123"); //Conn = new SqlConnection("Persist Security Info=False;Data Source=verite;Initial Catalog=webores;User ID=sa;Password=Pqqk7D0XUaos4H"); Conn.Open(); dtPedidoConsulta.SelectCommand = new SqlCommand("(SELECT DISTINCT pedidos.idPedido FROM pedidos) UNION ALL (SELECT DISTINCT pedidos_hist.idPedido FROM pedidos_hist)", Conn); dtPedidoConsulta.Fill(dsPedidoConsulta, "pedidos"); cmdPedidos = new SqlCommand(); //cmdPedidos.ExecuteNonQuery(); for (int rowCount = 0; rowCount < dsExcel.Tables[0].Rows.Count; rowCount++) { if (dsExcel.Tables[0].Rows[rowCount][0].ToString().Length >= 6) { valoresRow = dsExcel.Tables[0].Rows[rowCount][0].ToString().Split(dsExcel.Tables[0].Rows[rowCount][0].ToString()[6]); pedidoExist = false; foreach (DataRow rowSelect in dsPedidoConsulta.Tables[0].Rows) { if (rowSelect[0].ToString() == valoresRow[3]) //-- Compara Si existe el Pedido { pedidoExist = true; break; } } if (!pedidoExist) { strQuery = "INSERT INTO pedidos ("; queryValues = ""; verifyInsert = true; for (int columnCount = 0; columnCount < ColumnsName.Length; columnCount++) { if (ColumnsName[columnCount] != "tipo") { strQuery += ColumnsName[columnCount] + ","; if (ColumnsName[columnCount] != "ocabi" && (valoresRow[columnCount] == "" || valoresRow[columnCount] == null)) { verifyInsert = false; } switch (ColumnsTypes[columnCount]) { case "int": { queryValues += valoresRow[columnCount].Trim() + ","; break; } default: { queryValues += "'" + valoresRow[columnCount].Trim() + "',"; break; } } } } if (verifyInsert) { strQuery = strQuery.Substring(0, strQuery.Length - 1) + ") VALUES (" + queryValues.Substring(0, queryValues.Length - 1) + ")"; //Response.Write(strQuery + "<br><br>"); cmdPedidos = new SqlCommand(strQuery, Conn); cmdPedidos.ExecuteNonQuery(); } } } } daExcel.Dispose(); dsExcel.Dispose(); dsPedidoConsulta.Dispose(); dtPedidoConsulta.Dispose(); cmdPedidos.Dispose(); conn.Close(); conn.Dispose(); Conn.Close(); Conn.Dispose(); } } catch (SqlException sqlEx) { errorFound = true; eventTracerLog.createEntry("Importación de Pedidos: " + sqlEx.Message, true); } catch (Exception ex) { errorFound = false; eventTracerLog.createEntry("Importación de Pedidos: " + ex.Message, true); } finally { if (!errorFound) { eventTracerLog.createEntry("Importación de Pedidos", false); } } }
public void exportar() { if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones.txt")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones.txt"); } eventLog eventTracerLog = new eventLog(); File.CreateText(AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones.txt").Close(); SqlConnection Conn; SqlDataAdapter dtPedidoConsulta = new SqlDataAdapter(); DataSet dsPedidoConsulta = new DataSet(); String strToInsert; Boolean errorFound = false; String concatIds = ""; SqlCommand cmdEnvio; String fechaForm; DateTime fechaActual = DateTime.Now; String fileBackup = AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones_" + fechaActual.Hour.ToString() + "_" + fechaActual.Minute.ToString() + "_" + fechaActual.Day.ToString() + "_" + fechaActual.Month.ToString() + "_" + fechaActual.Year.ToString() + ".txt"; String baseDir = "e:\\proveedores\\compras_ofertas\\"; try{ File.Copy(baseDir+"cotizaciones.txt", fileBackup,true); } catch (Exception exCopy) { } try { Conn = new SqlConnection("Persist Security Info=False;Data Source=localhost;Initial Catalog=zeniprovedores;User ID=arlequinsql;Password=123"); //Conn = new SqlConnection("Persist Security Info=False;Data Source=verite;Initial Catalog=webores;User ID=sa;Password=Pqqk7D0XUaos4H"); Conn.Open(); dtPedidoConsulta.SelectCommand = new SqlCommand("SELECT * FROM pedidos_hist WHERE envio = '0' OR envio IS NULL", Conn); dtPedidoConsulta.Fill(dsPedidoConsulta, "pedidos_hist"); strToInsert = ""; if (dsPedidoConsulta.Tables[0].Rows.Count > 0) { foreach (DataRow row in dsPedidoConsulta.Tables[0].Rows) { if (row["cotizado"].ToString() == "1") { /* foreach (DataColumn column in dsPedidoConsulta.Tables[0].Columns) { strRow += row[column] + "|"; } */ if (strToInsert != "") { strToInsert += "\n"; } fechaForm = ""; if (row["fechaEntrega"].ToString() != "" && row["fechaEntrega"].ToString() != null) { fechaForm = row["fechaEntrega"].ToString().Substring(0, 4) + "-" + row["fechaEntrega"].ToString().Substring(4, 2) + "-" + row["fechaEntrega"].ToString().Substring(6, 2) + " 00:00:00"; } strToInsert += row["idPedido"] + "|" + row["idMaterial"] + "|" + row["cantidad"] + "|" + row["precio"] + "|" + row["moneda"] + "|" + fechaForm + "|" + row["observaciones"]; } concatIds += row["idLine"] + ","; } //Response.Write(strToInsert); File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones.txt", strToInsert); cmdEnvio = new SqlCommand("UPDATE pedidos_hist SET envio = '1' WHERE idLine IN (" + concatIds.Substring(0, concatIds.Length - 1) + ")", Conn); cmdEnvio.ExecuteNonQuery(); cmdEnvio.Dispose(); } dsPedidoConsulta.Dispose(); dtPedidoConsulta.Dispose(); Conn.Close(); Conn.Dispose(); File.Copy(AppDomain.CurrentDomain.BaseDirectory + "/cotizaciones.txt", baseDir + "cotizaciones.txt",true); } catch (SqlException sqlEx) { errorFound = true; eventTracerLog.createEntry("Exportación de Cotizaciones: " + sqlEx.Message, true); } catch (Exception ex) { errorFound = false; eventTracerLog.createEntry("Exportación de Cotizaciones: " + ex.Message, true); } finally { if (!errorFound) { eventTracerLog.createEntry("Exportación de Cotizaciones", false); } } }
public void importar() { string cuenta = ""; eventLog eventTracerLog = new eventLog(); Boolean errorFound = false; SqlConnection Conn; SqlCommand cmdPedidos; String strQuery; String queryValues; Boolean verifyInsert = true; String[] ColumnsName = { "no", "idProveedor", "tipoDoc", "idSap", "idFactura", "ejercicio", "monto", "moneda", "cuentaDesc", "claveCont", "fecha", "estado", "relacion", "tipoCuenta", "fechaComp", "viaPago" }; String[] ColumnsTypes = { "int", "string", "string", "int", "string", "int", "int", "string", "string", "int", "datetime", "string", "int", "string", "datetime", "string" }; DataSet dsExcel = new DataSet(); String[] valoresRow = new String[ColumnsName.Length]; SqlDataAdapter dtPedidoConsulta = new SqlDataAdapter(); DataSet dsPedidoConsulta = new DataSet(); Boolean pedidoExist = false; String[] fecha = new String[3]; Boolean compensada = false; String strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};" + "Dbq=" + AppDomain.CurrentDomain.BaseDirectory + ";"; OdbcConnection conn; OdbcDataAdapter daExcel; DateTime fechaActual = DateTime.Now; String baseDir = "e:\\proveedores\\compras_ofertas\\"; try { //IF FILE EXISTS, THE DELETE IT if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt")) { String fileBackup = AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion_" + fechaActual.Hour.ToString() + "_" + fechaActual.Minute.ToString() + "_" + fechaActual.Day.ToString() + "_" + fechaActual.Month.ToString() + "_" + fechaActual.Year.ToString() + ".txt"; File.Copy(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt", fileBackup); File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt"); } File.CreateText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt").Close(); if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv")) { File.Delete(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); } // FTP CONNECTION File.Copy(baseDir+"Facturacion.txt", AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); // REPLACE INVALIDED CHARACTERS String strTextFile = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv"); strTextFile = strTextFile.Replace(',', '.'); strTextFile = strTextFile.Replace('"', 'º'); strTextFile = strTextFile.Replace(';', ','); File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/facturacion.csv", "\n"+strTextFile); //CONNECTION FILE CSV conn = new OdbcConnection(strConn); daExcel = new OdbcDataAdapter("SELECT * FROM [facturacion.csv]", conn); daExcel.Fill(dsExcel); if (dsExcel.Tables[0].Rows.Count > 0) { Conn = new SqlConnection("Persist Security Info=False;Data Source=localhost;Initial Catalog=zeniprovedores;User ID=arlequinsql;Password=123"); //Conn = new SqlConnection("Persist Security Info=False;Data Source=verite;Initial Catalog=webores;User ID=sa;Password=Pqqk7D0XUaos4H"); Conn.Open(); dtPedidoConsulta.SelectCommand = new SqlCommand("SELECT DISTINCT facturacion.idFactura, facturacion.idProveedor, facturacion.estado FROM facturacion", Conn); dtPedidoConsulta.Fill(dsPedidoConsulta, "facturacion"); cmdPedidos = new SqlCommand(); //cmdPedidos.ExecuteNonQuery(); for (int rowCount = 0; rowCount < dsExcel.Tables[0].Rows.Count; rowCount++) { if (dsExcel.Tables[0].Rows[rowCount][0].ToString().Length >= 6) { valoresRow = dsExcel.Tables[0].Rows[rowCount][0].ToString().Split(','); cuenta = dsExcel.Tables[0].Rows[rowCount][0].ToString(); eventTracerLog.createEntry("procesando: " + rowCount +" - "+cuenta, true); pedidoExist = false; compensada = false; foreach (DataRow rowSelect in dsPedidoConsulta.Tables[0].Rows) { if (rowSelect[0].ToString() == valoresRow[4] && Convert.ToInt32(rowSelect[1].ToString()) == Convert.ToInt32(valoresRow[1].ToString())) //-- Compara Si existe el Pedido { compensada = (rowSelect["estado"].ToString().ToLower() == "x" ? true : false); pedidoExist = true; break; } } eventTracerLog.createEntry("procesando ok: " + rowCount + " - " + cuenta, true); if (!pedidoExist) { strQuery = "INSERT INTO facturacion ("; } else { strQuery = "UPDATE facturacion SET "; } queryValues = ""; verifyInsert = true; if (!compensada) { for (int columnCount = 0; columnCount < ColumnsName.Length; columnCount++) { if (ColumnsName[columnCount] != "no") { strQuery += ColumnsName[columnCount] + (pedidoExist ? "=" : ","); if (ColumnsName[columnCount] != "estado" && ColumnsName[columnCount] != "relacion" && ColumnsName[columnCount] != "viaPago" && (valoresRow[columnCount] == "" || valoresRow[columnCount] == null)) { verifyInsert = false; } if (Convert.ToInt32(valoresRow[0].ToString()) < 30000 || Convert.ToInt32(valoresRow[0].ToString()) >= 49999) { verifyInsert = false; } switch (valoresRow[2]) { case "KR": case "KG": case "KU": case "RE": case "KC": case "ZP": case "KZ": case "KA": case "KD": case "EX": break; default: verifyInsert = false; break; } if (valoresRow[13].ToString().ToUpper() != "K") { verifyInsert = false; } switch (ColumnsTypes[columnCount]) { case "int": { if (valoresRow[columnCount].Trim() == "") { valoresRow[columnCount] = "0"; } if (pedidoExist) { strQuery += valoresRow[columnCount].Trim() + ","; } else { queryValues += valoresRow[columnCount].Trim() + ","; } break; } case "datetime": { fecha = valoresRow[columnCount].Trim().Split('.'); if (pedidoExist) { if (fecha[1] != "00") { strQuery += "'" + fecha[1] + "-" + fecha[0] + "-" + fecha[2] + "',"; } else { strQuery += "null,"; } } else { if (fecha[1] != "00") { queryValues += "'" + fecha[1] + "-" + fecha[0] + "-" + fecha[2] + "',"; } else { queryValues += "null,"; } } break; } default: { if (pedidoExist) { strQuery += "'" + valoresRow[columnCount].Trim() + "',"; } else { queryValues += "'" + valoresRow[columnCount].Trim() + "',"; } break; } } } } if (verifyInsert) { if (pedidoExist) { strQuery = strQuery.Substring(0, strQuery.Length - 1) + " WHERE idFactura = '" + valoresRow[4] + "' and idProveedor = " + valoresRow[1]; } else { strQuery = strQuery.Substring(0, strQuery.Length - 1) + ") VALUES (" + queryValues.Substring(0, queryValues.Length - 1) + ")"; } cmdPedidos = new SqlCommand(strQuery, Conn); cmdPedidos.ExecuteNonQuery(); } if (!verifyInsert) { strQuery = ""; } File.WriteAllText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt", File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "/log_facturacion.txt").ToString() + "\n" + "linea: " + (rowCount + 1) + " " + " Ejecucion: " + verifyInsert + " Datos: " + dsExcel.Tables[0].Rows[rowCount][0].ToString() + " Consulta ejecutada: " + strQuery); } } } daExcel.Dispose(); dsExcel.Dispose(); dsPedidoConsulta.Dispose(); dtPedidoConsulta.Dispose(); cmdPedidos.Dispose(); conn.Close(); conn.Dispose(); Conn.Close(); Conn.Dispose(); } } catch (SqlException sqlEx) { errorFound = true; eventTracerLog.createEntry("Importación de Facturación de sql: " + sqlEx.Message, true); } catch (Exception ex) { errorFound = true; eventTracerLog.createEntry("Importación de Facturación de parseo: " + ex.Message, true); } finally { if (!errorFound) { eventTracerLog.createEntry("Importación de Facturación correcta", false); } } }