public void InsertShopify(IList <IList <Object> > values, IList <Object> listaParametros)
{
try
{
string names = string.Empty;
int pedidos = 0;
Conexiones conn = new Conexion.Conexiones();
SqlConnection conexion = conn.procesadorabd();
using (conexion)
{
for (int i = 1; i < values.Count; i++)
{
SqlCommand cmd = new SqlCommand(conn.AddShopify(), conexion);
SqlDataReader read = cmd.ExecuteReader();
read.Read();
int o = 0;
int id = read.GetInt32(0);
foreach (var item in values[i])
{
string columna = Convert.ToString(listaParametros[o]);
o++;
string data = Convert.ToString(item);
if (columna == "Line item origin location" || columna == "Line item discount allocations" || columna == "Line item destination location")
{
data = data.Replace("'", "'+''''+'");
}
conn.actualizarInsercion(columna, data, id);
if (columna == "Name" && !names.Contains(data))
{
pedidos = pedidos + 1;
if (i == 1)
{
names = data;
}
else
{
names = names + ", " + data;
}
}
}
read.Close();
conn.EjecutarLog(id, "Fila insertada correctamente", "OK INST DATA SHOPIFY");
}
conn.Correo(pedidos, names);
conexion.Close();
}
}
catch (Exception e)
{
Conexion.Conexiones c = new Conexiones();
c.EjecutarLog(0, e.ToString(), "ERROR INST DATA SHOPIFY");
}
}
public void Diferencia(IList <Object> listaParametros)
{
try
{
Conexiones conn = new Conexion.Conexiones();
SqlConnection conexion = conn.procesadorabd();
using (conexion)
{
SqlCommand cmd = new SqlCommand(conn.ConsultaColumnsShopify(), conn.procesadorabd());
DataTable colu = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(colu);
int columnasBD = colu.Rows.Count;
int totalParametros = listaParametros.Count;
foreach (var item in listaParametros)
{
bool exist = false;
string it = Convert.ToString(item);
foreach (DataRow rows in colu.Rows)
{
if (Convert.ToString(rows.ItemArray[0]) == it)
{
exist = true;
break;
}
}
if (!exist)
{
conn.InsertarColumna(it);
conn.EjecutarLog(0, "Nueva columna insertada: " + it + ".", "OK INST DIFERENCIA");
}
}
conexion.Close();
}
}
catch (Exception e)
{
Conexion.Conexiones c = new Conexiones();
c.EjecutarLog(0, e.ToString(), "ERROR INST NEW PARAMETROS");
}
}
public void EliminacionArchivosComprimidos()
{
Model.Archivo arch = new Model.Archivo();
Conexion.Conexiones cn = new Conexion.Conexiones();
arch.allFilesZip = Directory.GetFiles(arch.pathZip, "*.zip", SearchOption.AllDirectories);
try
{
for (int i = 0; i < arch.allFilesZip.Count(); i++)
{
File.Delete(arch.allFilesZip[i]);
}
cn.EjecutarLog("Correcta Eliminacion", "OK ELIMINACION");
}
catch (Exception e)
{
cn.EjecutarLog(e.ToString(), "ERROR ELIMINACION");
EnviarCorreo cr = new EnviarCorreo();
cr.CorreoErrores("Se genero un error al momento de eliminar los archivos. " + e, "Error ISV Integracion");
}
}
public void insertaDatos(string csv_file, int lineas)
{
Conexion.Conexiones cn = new Conexion.Conexiones();
DataTable csvData = new DataTable();
using (CsvTextFieldParser csvReader = new CsvTextFieldParser(csv_file))
{
csvReader.Delimiters = new string[] { ";" };
csvReader.HasFieldsEnclosedInQuotes = true;
//read column names
string[] colFields = csvReader.ReadFields();
try
{
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
csvData.Rows.Add(fieldData);
}
using (cn.Prosud_BI_A())
{
for (int i = 0; csvData.Rows.Count > i; i++)
{
cn.SP_INSERT_Datos(
csvData.Rows[i].ItemArray.GetValue(0).ToString(),
csvData.Rows[i].ItemArray.GetValue(1).ToString(),
csvData.Rows[i].ItemArray.GetValue(2).ToString(),
csvData.Rows[i].ItemArray.GetValue(3).ToString(),
csvData.Rows[i].ItemArray.GetValue(4).ToString(),
csvData.Rows[i].ItemArray.GetValue(5).ToString(),
csvData.Rows[i].ItemArray.GetValue(6).ToString(),
csvData.Rows[i].ItemArray.GetValue(7).ToString(),
csvData.Rows[i].ItemArray.GetValue(8).ToString(),
csvData.Rows[i].ItemArray.GetValue(9).ToString(),
csvData.Rows[i].ItemArray.GetValue(10).ToString(),
csvData.Rows[i].ItemArray.GetValue(11).ToString(),
csvData.Rows[i].ItemArray.GetValue(12).ToString(),
csvData.Rows[i].ItemArray.GetValue(13).ToString(),
csvData.Rows[i].ItemArray.GetValue(14).ToString(),
csvData.Rows[i].ItemArray.GetValue(15).ToString(),
csvData.Rows[i].ItemArray.GetValue(16).ToString(),
csvData.Rows[i].ItemArray.GetValue(17).ToString(),
csvData.Rows[i].ItemArray.GetValue(18).ToString(),
csvData.Rows[i].ItemArray.GetValue(19).ToString(),
csvData.Rows[i].ItemArray.GetValue(20).ToString(),
csvData.Rows[i].ItemArray.GetValue(21).ToString(),
csvData.Rows[i].ItemArray.GetValue(22).ToString(),
csvData.Rows[i].ItemArray.GetValue(23).ToString(),
csvData.Rows[i].ItemArray.GetValue(24).ToString(),
csvData.Rows[i].ItemArray.GetValue(25).ToString(),
csvData.Rows[i].ItemArray.GetValue(26).ToString());
#region
//string query = "insert into Prosud_BI.dbo.ISV_Ventas (Fechas, Holding, Cadena, Zona, ZonaNielsen, Comuna, Supervisor, Mercaderista, Local, RutMercaderista, Linea, Categoria, Marca, SubCategoria, Descripcion, CodigoInterno, EAN, DUN, Vigencia, CodCadena, Unidades, ValoresCostoNeto, Cajas, ValVentaB2B, PVPIVA, Kilos, B2BPrecios) values ('" + csvData.Rows[i].ItemArray.GetValue(0).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(1).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(2).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(3).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(4).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(5).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(6).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(7).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(8).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(9).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(10).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(11).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(12).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(13).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(14).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(15).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(16).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(17).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(18).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(19).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(20).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(21).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(22).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(23).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(24).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(25).ToString() + "','" +
// csvData.Rows[i].ItemArray.GetValue(26).ToString() + "');";
//SqlCommand command = new SqlCommand(query, cn.Prosud_BI_A());
//command.ExecuteNonQuery();
#endregion
cn.Prosud_BI_A().Close();
}
}
}
catch (Exception e)
{
cn.EjecutarLog(e.ToString(), "ERROR INSERCION");
EnviarCorreo cr = new EnviarCorreo();
cr.CorreoErrores("Se genero un error al momento de insertar los archivos. " + e, "Error ISV Integracion");
Console.WriteLine(e.Message);
Console.ReadKey();
}
}
}
public void ProcesarArchivos()
{
Conexion.Conexiones cn = new Conexion.Conexiones();
Model.Archivo arch = new Model.Archivo();
Model.Integracion it = new Model.Integracion();
DataTable dt = new DataTable();
using (cn.procesadora_analisis())
{
SqlCommand cmd = new SqlCommand(cn.query_fechas, cn.procesadora_analisis());
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
try
{
var httpWebRequest = (HttpWebRequest)WebRequest.Create(it.url);
httpWebRequest.ContentType = "application/json";
httpWebRequest.Headers.Add("Authorization", it.token);
httpWebRequest.Headers.Add("Postman-Token", "69dda9c7-6396-45c4-934f-7c59fa6f32e9");
httpWebRequest.Headers.Add("cache-control", "no_cache");
httpWebRequest.Method = "POST";
using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
{
it.json = "";
it.json = "{";
it.json = it.json + "\"views\": [],";
it.json = it.json + "\"view_type\": \"diario\",";
it.json = it.json + "\"dates\": [\"" + row[dc].ToString() + "\"";
//it.json = it.json + "\"dates\": [ \" \"";
it.json = it.json + "],";
it.json = it.json + "\"chain_codes\":[],";
it.json = it.json + "\"hierarchy\": {";
it.json = it.json + "\"Holding\":[],";
it.json = it.json + "\"Cadena\":[],";
it.json = it.json + "\"Supervisor\":[],";
it.json = it.json + "\"Zona\":[],";
it.json = it.json + "\"Local\":[],";
it.json = it.json + "\"Zona Nielsen\":[],";
it.json = it.json + "\"Comuna\":[],";
it.json = it.json + "\"Mercaderista\":[],";
it.json = it.json + "\"Rut Mercaderista\":[],";
it.json = it.json + "\"Descripción\":[],";
it.json = it.json + "\"Linea\":[],";
it.json = it.json + "\"Categoría\":[],";
it.json = it.json + "\"Marca\":[],";
it.json = it.json + "\"Sub Categoría\":[],";
it.json = it.json + "\"Código Interno\":[],";
it.json = it.json + "\"EAN\":[],";
it.json = it.json + "\"DUN\":[],";
it.json = it.json + "\"Vigencia\":[]";
it.json = it.json + "}";
it.json = it.json + "}";
streamWriter.Write(it.json);
streamWriter.Flush();
}
var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
var responseText = streamReader.ReadToEnd();
Console.WriteLine(responseText);
if (responseText == "Error en el servidor remoto: (401) No autorizado.")
{
EnviarCorreo cr = new EnviarCorreo();
cr.CorreoErrores("Error en el servidor remoto: (401) No autorizado. Descarga(" + row[dc].ToString() + "). - Procesar Tarea Nuevamente", "Error Integracion ISV");
cn.FLAG_0();
}
dynamic stuff = JsonConvert.DeserializeObject(responseText);
arch.link = stuff.download_url;
arch.zipFile = arch.pathZip + row[dc].ToString() + ".zip";
WebClient webClient = new WebClient();
webClient.DownloadFile(arch.link, arch.zipFile);
arch.allFiles = Directory.GetFiles(arch.pathExtract, "*.csv", SearchOption.AllDirectories);
try
{
File.Delete(arch.allFiles[0]);
}
catch (Exception e)
{
Console.WriteLine(e);
}
ZipFile.ExtractToDirectory(arch.zipFile, arch.pathExtract);
arch.allFilesCsv =
Directory.GetFiles(arch.pathExtract, "*.csv", SearchOption.AllDirectories);
File.Move(arch.allFilesCsv[0], arch.pathExtract + row[dc].ToString() + ".csv");
arch.csvFile = arch.pathExtract + row[dc].ToString() + ".csv";
string text = File.ReadAllText(arch.csvFile);
text = text.Replace("\"", "");
text = text.Replace(".", "");
text = text.Replace(",", ".");
File.WriteAllText(arch.csvFile, text);
var lineCount = File.ReadLines(arch.csvFile).Count();
if (cn.obtenerFlag() == "1")
{
InsercionDeDatos ins = new InsercionDeDatos();
ins.insertaDatos(arch.csvFile, lineCount);
}
Console.WriteLine(arch.link);
}
cn.EjecutarLog("Correcto Proceso de archivos", "OK PROCESADO");
}
catch (WebException ex)
{
cn.EjecutarLog(ex.ToString(), "ERROR PROCESAR ARCHIVOS");
EnviarCorreo cr = new EnviarCorreo();
cr.CorreoErrores("Se genero un error al momento de procesar los archivos. " + ex, "Error ISV Integracion");
Console.WriteLine(ex.Message);
Console.ReadKey();
}
}
}
}
cn.procesadora_analisis().Close();
}