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);
}
}
}