public autosgrsm_SOM getSomDataByCRMNO(string connection, string parametro) { Log log = new Log(); autosgrsm_SOM dataSOM = new autosgrsm_SOM(); try { dataSOM.cadena = EncryptionManager.Decrypt(connection); dataSOM.querySelect = "WHERE CRMNO = @CRMNO"; dicParameter = new Dictionary <string, string>(); dicParameter.Add("@CRMNO", parametro); DataTable tbSOM = dataSOM.select(dicParameter).Tables[0]; dataSOM.Id = Convert.ToInt32(tbSOM.Rows[0]["Id"].ToString()); dataSOM.FLOWID = tbSOM.Rows[0]["FLOWID"].ToString(); dataSOM.TECNOLOGIA = tbSOM.Rows[0]["TECNOLOGIA"].ToString(); dataSOM.OPERACION_COMERCIAL = tbSOM.Rows[0]["OPERACION_COMERCIAL"].ToString(); dataSOM.ESTADO_DE_LA_ORDEN = tbSOM.Rows[0]["ESTADO_DE_LA_ORDEN"].ToString(); dataSOM.APPOINTMENT_ID = tbSOM.Rows[0]["APPOINTMENT_ID"].ToString(); dataSOM.FLOWNO = tbSOM.Rows[0]["FLOWNO"].ToString(); dataSOM.ORDERCREATETIME = Convert.ToDateTime(tbSOM.Rows[0]["ORDERCREATETIME"].ToString()); dataSOM.WORKCREATETIME = Convert.ToDateTime(tbSOM.Rows[0]["WORKCREATETIME"].ToString()); dataSOM.DATE_ERROR = Convert.ToDateTime(tbSOM.Rows[0]["DATE_ERROR"].ToString()); dataSOM.FLOWTYPE = tbSOM.Rows[0]["FLOWTYPE"].ToString(); dataSOM.FLOWTYPEID = tbSOM.Rows[0]["FLOWTYPEID"].ToString(); dataSOM.PROCESSINSTNAME = tbSOM.Rows[0]["PROCESSINSTNAME"].ToString(); dataSOM.ACTIVITYDEFID = tbSOM.Rows[0]["ACTIVITYDEFID"].ToString(); dataSOM.ACTIVITYINSTNAME = tbSOM.Rows[0]["ACTIVITYINSTNAME"].ToString(); dataSOM.CRMNO = tbSOM.Rows[0]["CRMNO"].ToString(); dataSOM.CUSTOMNAME = tbSOM.Rows[0]["CUSTOMNAME"].ToString(); dataSOM.PRODUCT = tbSOM.Rows[0]["PRODUCT"].ToString(); dataSOM.OPERATION = tbSOM.Rows[0]["OPERATION"].ToString(); dataSOM.ACCESSACCOUNT = tbSOM.Rows[0]["ACCESSACCOUNT"].ToString(); dataSOM.INSTALL_ADDRESS = tbSOM.Rows[0]["INSTALL_ADDRESS"].ToString(); dataSOM.CODIGO_LOCALIDAD = tbSOM.Rows[0]["CODIGO_LOCALIDAD"].ToString(); dataSOM.ERROR_CODE = tbSOM.Rows[0]["ERROR_CODE"].ToString(); dataSOM.ERROR_DESC = tbSOM.Rows[0]["ERROR_DESC"].ToString(); dataSOM.CREATED = Convert.ToDateTime(tbSOM.Rows[0]["CREATED"].ToString()); dataSOM.STATUS_FLOW = Convert.ToInt32(tbSOM.Rows[0]["STATUS_FLOW"].ToString()); dataSOM.STATUS_CAI = Convert.ToInt32(tbSOM.Rows[0]["STATUS_CAI"].ToString()); } catch (Exception ex) { log.insertLogGeneral(4, "No se pueden consultar los parámetros: " + ex.Message); } return(dataSOM); }
public List <CAIs> SearchSOMDataInitial(string connection) { Log log = new Log(); log.insertLogGeneral(1, "Consulta de SOM pendientes: Estado Cargue Inicial"); List <CAIs> listado = new List <CAIs>(); string STATUS_FLOW = "2"; try { autosgrsm_SOM dataSom = new autosgrsm_SOM(); dataSom.cadena = EncryptionManager.Decrypt(connection); dataSom.querySelect = "WHERE STATUS_FLOW = @STATUS_FLOW"; dicParameter = new Dictionary <string, string>(); dicParameter.Add("@STATUS_FLOW", STATUS_FLOW); DataTable somDatatable = dataSom.select(dicParameter).Tables[0]; if (somDatatable.Rows.Count > 0) { foreach (DataRow item in somDatatable.Rows) { CAIs cai = new CAIs() { CRMNO = item["CRMNO"].ToString(), CAI = item["ERROR_DESC"].ToString().Replace("Service", "").Replace("already exists", "").Trim() }; listado.Add(cai); } } else { log.insertLogGeneral(1, "No se encontraron datos de SOM pendientes. Proceso finalizado."); } } catch (Exception ex) { log.insertLogGeneral(4, "Error en la consulta de datos de SOM pendientes: " + ex.Message); } return(listado); }
public List <autosgrsm_SOM> consulta() { List <autosgrsm_SOM> objetoSOM = new List <autosgrsm_SOM>(); autosgrsm_Parametros parametros = new autosgrsm_Parametros(); //string a = EncryptionManager.Encrypt(@"Server=10.80.3.147\SQLINSTPROY;Database=E2E_Salvalineas_base_dev;User Id=E2E_Salvalineas_base;Password=7SrD2TA$;"); parametros.cadena = EncryptionManager.Decrypt(ConfigurationManager.ConnectionStrings["DB_CONNECTION_" + cadena].ToString()); parametros.querySelect = "WHERE Nombre_Parametro = @Nombre_Parametro"; dicParameter = new Dictionary <string, string>(); dicParameter.Add("@Nombre_Parametro", "SQL_SELECT_SOM"); DataTable tbParametros = parametros.select(dicParameter).Tables[0]; string consultaSOM = tbParametros.Rows[0]["Valor_Parametro"].ToString(); //string oradb = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.109.84)(PORT = 1526))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = somdb)));User Id=SQL_AUTOMAT;Password=Kiweth3ns4sx;Connection Timeout=600;"; //string oradb = oradb = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.109.84)(PORT = 1526))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = somdb)));User Id=GMOVISTAR;Password=Tele2020*!Gvist#; Connection Timeout=600;"; //string oradb = oradb = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.109.84)(PORT = 1526))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = somdb)));User Id=SQL_LSANABRIAHO;Password=Tele2020*!Nov#; Connection Timeout=600;"; //string encriptado = EncryptionManager.Encrypt(oradb); string db_som = EncryptionManager.Decrypt(ConfigurationManager.ConnectionStrings["DB_CONNECTION_SOM_" + cadena].ToString()); DataTable query = new DataTable(); if (entorno == "2") { using (OracleConnection conn = new OracleConnection(db_som)) { try { OracleCommand cmd = new OracleCommand(); cmd.CommandText = consultaSOM; cmd.Connection = conn; conn.Open(); OracleDataReader queryOracle = cmd.ExecuteReader(); query.Load(queryOracle); conn.Close(); } catch (Exception ex) { throw; } } } else { query = new DataTable(); DataColumn column = new DataColumn(); column.ColumnName = "FLOWID"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "TECNOLOGIA"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "OPERACION_COMERCIAL"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ESTADO_DE_LA_ORDEN"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "APPOINTMENT_ID"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "FLOWNO"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ORDERCREATETIME"; column.DataType = Type.GetType("System.DateTime"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "WORKCREATETIME"; column.DataType = Type.GetType("System.DateTime"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "DATE_ERROR"; column.DataType = Type.GetType("System.DateTime"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "FLOWTYPE"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "FLOWTYPEID"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "PROCESSINSTNAME"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ACTIVITYDEFID"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ACTIVITYINSTNAME"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "CRMNO"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "CUSTOMNAME"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "PRODUCT"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "OPERATION"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ACCESSACCOUNT"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "INSTALL_ADDRESS"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "CODIGO_LOCALIDAD"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ERROR_CODE"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); column = new DataColumn(); column.ColumnName = "ERROR_DESC"; column.DataType = Type.GetType("System.String"); query.Columns.Add(column); for (int i = 0; i < 10; i++) { DataRow myNewRow; myNewRow = query.NewRow(); myNewRow["FLOWID"] = "16065051850190100-" + i; myNewRow["TECNOLOGIA"] = ""; myNewRow["OPERACION_COMERCIAL"] = "Posventa"; myNewRow["ESTADO_DE_LA_ORDEN"] = "Executing"; myNewRow["APPOINTMENT_ID"] = "FS202011271424130143"; myNewRow["FLOWNO"] = "4531008"; myNewRow["ORDERCREATETIME"] = "27/11/2020 14:26"; myNewRow["WORKCREATETIME"] = "27/11/2020 14:26"; myNewRow["DATE_ERROR"] = "27/11/2020 14:37"; myNewRow["FLOWTYPE"] = "OM"; myNewRow["FLOWTYPEID"] = "OM"; myNewRow["PROCESSINSTNAME"] = "FMC Process-GOMEZ GOMEZ MORALES DEISY GOMEZ MORALES"; myNewRow["ACTIVITYDEFID"] = "InvokeIM"; myNewRow["ACTIVITYINSTNAME"] = "InvokeIM"; myNewRow["CRMNO"] = "20000474329533"; myNewRow["CUSTOMNAME"] = "GOMEZ GOMEZ MORALES DEISY GOMEZ MORALES"; myNewRow["PRODUCT"] = "Broadband Service+Fixed Line Voice+IPTV+TV"; myNewRow["OPERATION"] = "Keep+Keep+Install+Uninstall"; myNewRow["ACCESSACCOUNT"] = "IPTV:640000181711+DTH:651340443462+LB:82692349+BB:2582692349"; myNewRow["INSTALL_ADDRESS"] = "CL 4 KR 4-54 LA POLA"; myNewRow["CODIGO_LOCALIDAD"] = "73001000"; myNewRow["ERROR_CODE"] = "INV-SIGRES-00010001"; myNewRow["ERROR_DESC"] = "Service CIP_00000000000000000000000004531008 already exists"; query.Rows.Add(myNewRow); } } if (query.Rows.Count > 0) { foreach (DataRow item in query.Rows) { autosgrsm_SOM itemSom = new autosgrsm_SOM() { FLOWID = item["FLOWID"].ToString(), TECNOLOGIA = item["TECNOLOGIA"].ToString(), OPERACION_COMERCIAL = item["OPERACION_COMERCIAL"].ToString(), ESTADO_DE_LA_ORDEN = item["ESTADO_DE_LA_ORDEN"].ToString(), APPOINTMENT_ID = item["APPOINTMENT_ID"].ToString(), FLOWNO = item["FLOWNO"].ToString(), ORDERCREATETIME = Convert.ToDateTime(item["ORDERCREATETIME"].ToString()), WORKCREATETIME = Convert.ToDateTime(item["WORKCREATETIME"].ToString()), DATE_ERROR = Convert.ToDateTime(item["DATE_ERROR"].ToString()), FLOWTYPE = item["FLOWTYPE"].ToString(), FLOWTYPEID = item["FLOWTYPEID"].ToString(), PROCESSINSTNAME = item["PROCESSINSTNAME"].ToString(), ACTIVITYDEFID = item["ACTIVITYDEFID"].ToString(), ACTIVITYINSTNAME = item["ACTIVITYINSTNAME"].ToString(), CRMNO = item["CRMNO"].ToString(), CUSTOMNAME = item["CUSTOMNAME"].ToString(), PRODUCT = item["PRODUCT"].ToString(), OPERATION = item["OPERATION"].ToString(), ACCESSACCOUNT = item["ACCESSACCOUNT"].ToString(), INSTALL_ADDRESS = item["INSTALL_ADDRESS"].ToString(), CODIGO_LOCALIDAD = item["CODIGO_LOCALIDAD"].ToString(), ERROR_CODE = item["ERROR_CODE"].ToString(), ERROR_DESC = item["ERROR_DESC"].ToString() }; objetoSOM.Add(itemSom); } } else { objetoSOM = new List <autosgrsm_SOM>(); } return(objetoSOM); }
public void loadSIGRESData(string connection, string consultaApi, CAIs CAI) { Log log = new Log(); List <autosgrsm_SIGRES> jsonSigres = null; if (consultaApi != "") { var request = (HttpWebRequest)WebRequest.Create(consultaApi + CAI.CAI); request.Method = "GET"; request.ContentType = "application/json"; request.Accept = "application/json"; try { using (WebResponse response = request.GetResponse()) { using (Stream strReader = response.GetResponseStream()) { if (strReader == null) { return; } using (StreamReader objReader = new StreamReader(strReader)) { string json = objReader.ReadToEnd(); JavaScriptSerializer js = new JavaScriptSerializer(); jsonSigres = js.Deserialize <List <autosgrsm_SIGRES> >(json); } } } } catch (WebException ex) { log.insertLogGeneral(3, "Falló Api Sigres: " + ex.Message); } if (jsonSigres.Count > 0) { try { foreach (autosgrsm_SIGRES item in jsonSigres) { autosgrsm_SOM som = getSomDataByCRMNO(connection, CAI.CRMNO); som.cadena = EncryptionManager.Decrypt(connection); som.STATUS_CAI = item.ESTADO; som.STATUS_FLOW = 5; som.queryUpdate = "WHERE CRMNO = @CRMNO"; dicParameter = new Dictionary <string, string>(); dicParameter.Add("@CRMNO", CAI.CRMNO); som.update(dicParameter); log.insertLog(CAI.CRMNO, som.STATUS_FLOW, "Actualización del Estado CAI"); } } catch (Exception ex) { log.insertLogGeneral(4, "Falló Actualziación en tabla SOM (Estado CAI): " + ex.Message); } } else { log.insertLog(CAI.CRMNO, 3, "No se encontró data para el CAI: " + CAI.CAI); Console.WriteLine("No se encontró data para el CAI: " + CAI.CAI); } } }