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