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