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");
            }
        }
Beispiel #3
0
 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();
                }
            }
        }
Beispiel #5
0
        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();
        }