public OutMenuType GetMenuType(string user) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); var ora = new OracleServer(connectionString); OutMenuType response = new OutMenuType(); string command = string.Empty; try { command = string.Format("SELECT nvl(BBS_WFG_V_TIPO_MENU.TIPO_MENU,0) as menu FROM BBS_WFG_V_TIPO_MENU WHERE BBS_WFG_V_TIPO_MENU.cedula = '{0}' ", user); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { response.menuType = DBNull.Value.Equals(rdr["menu"]) ? 0 : int.Parse(rdr["menu"].ToString()); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("AuthenticationDAO.GetMenuType", ex); } finally { ora.Dispose(); } return(response); }
public OutCustomer GetCustomerInformation(string customerID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutCustomer response = new OutCustomer(); var ora = new OracleServer(connectionString); string command = string.Empty; try { command = " SELECT T.TIPO_DOCUMENTO, T.CEDULA, T.NOMBRE1, T.NOMBRE2, T.APELLIDO1, T.APELLIDO2, T.GENERO_PERSONA,T.FECHA_NACTO, T.CELULAR, "; command = command + " T.CONVENIO, T.PAGADURIA, T.CARGO, T.FECHA_VINCULACION_LABORAL, T.CONTRATO, T.SALARIO, T.VLR_SALUD, T.VLR_PENSION, "; command = command + " T.VLR_RETEFUENTE,T.OTROS_DESCUENTOS, T.VLR_FONDOSOLIDARIDAD, T.LINEA_CREDITO, T.TIPO_CREDITO, T.PLAZO, T.MONTO_SOLICITADO, T.OTROS_INGRESOS "; command = command + string.Format("FROM V_DLG_SOLICITUDES T WHERE CEDULA = '{0}'", customerID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { response.documentType = DBNull.Value.Equals(rdr["CEDULA"]) ? 0 : int.Parse(rdr["CEDULA"].ToString()); response.documentID = DBNull.Value.Equals(rdr["TIPO_DOCUMENTO"]) ? string.Empty : rdr["TIPO_DOCUMENTO"].ToString(); response.name1 = DBNull.Value.Equals(rdr["NOMBRE1"]) ? string.Empty : rdr["NOMBRE1"].ToString(); response.name2 = DBNull.Value.Equals(rdr["NOMBRE2"]) ? string.Empty : rdr["NOMBRE2"].ToString(); response.surname1 = DBNull.Value.Equals(rdr["APELLIDO1"]) ? string.Empty : rdr["APELLIDO1"].ToString(); response.surname2 = DBNull.Value.Equals(rdr["APELLIDO2"]) ? string.Empty : rdr["APELLIDO2"].ToString(); response.gender = DBNull.Value.Equals(rdr["GENERO_PERSONA"]) ? "1" : rdr["GENERO_PERSONA"].ToString(); response.birthdate = DBNull.Value.Equals(rdr["FECHA_NACTO"]) ? string.Empty : DateTime.Parse(rdr["FECHA_NACTO"].ToString()).ToString("dd'/'MM'/'yyyy"); response.cellphone = DBNull.Value.Equals(rdr["CELULAR"]) ? 0 : double.Parse(rdr["CELULAR"].ToString()); response.agreement = DBNull.Value.Equals(rdr["CONVENIO"]) ? 0 : double.Parse(rdr["CONVENIO"].ToString()); response.payable = DBNull.Value.Equals(rdr["PAGADURIA"]) ? 0 : double.Parse(rdr["PAGADURIA"].ToString()); response.position = DBNull.Value.Equals(rdr["CARGO"]) ? 0 : double.Parse(rdr["CARGO"].ToString()); response.vinculationDate = DBNull.Value.Equals(rdr["FECHA_VINCULACION_LABORAL"]) ? string.Empty : DateTime.Parse(rdr["FECHA_VINCULACION_LABORAL"].ToString()).ToString("dd'/'MM'/'yyyy"); response.contractType = DBNull.Value.Equals(rdr["CONTRATO"]) ? 0 : int.Parse(rdr["CONTRATO"].ToString()); response.salary = DBNull.Value.Equals(rdr["SALARIO"]) ? 0 : double.Parse(rdr["SALARIO"].ToString()); response.health = DBNull.Value.Equals(rdr["VLR_SALUD"]) ? 0 : double.Parse(rdr["VLR_SALUD"].ToString()); response.pension = DBNull.Value.Equals(rdr["VLR_PENSION"]) ? 0 : double.Parse(rdr["VLR_PENSION"].ToString()); response.retefuente = DBNull.Value.Equals(rdr["VLR_RETEFUENTE"]) ? 0 : double.Parse(rdr["VLR_RETEFUENTE"].ToString()); response.otherDiscounts = DBNull.Value.Equals(rdr["OTROS_DESCUENTOS"]) ? 0 : double.Parse(rdr["OTROS_DESCUENTOS"].ToString()); response.solidarityFunds = DBNull.Value.Equals(rdr["VLR_FONDOSOLIDARIDAD"]) ? 0 : double.Parse(rdr["VLR_FONDOSOLIDARIDAD"].ToString()); response.LoanLine = DBNull.Value.Equals(rdr["LINEA_CREDITO"]) ? 0 : int.Parse(rdr["LINEA_CREDITO"].ToString()); response.LoanType = DBNull.Value.Equals(rdr["TIPO_CREDITO"]) ? 0 : int.Parse(rdr["TIPO_CREDITO"].ToString()); response.term = DBNull.Value.Equals(rdr["PLAZO"]) ? 0 : int.Parse(rdr["PLAZO"].ToString()); response.amount = DBNull.Value.Equals(rdr["MONTO_SOLICITADO"]) ? 0 : double.Parse(rdr["MONTO_SOLICITADO"].ToString()); response.otherIncome = DBNull.Value.Equals(rdr["OTROS_INGRESOS"]) ? 0 : double.Parse(rdr["OTROS_INGRESOS"].ToString()); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("CustomerDAO.GetCustomerInformation", ex); } finally { ora.Dispose(); } return(response); }
public OutLoanHeader GetLoanHeader(double folderNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutLoanHeader response = new OutLoanHeader(); var ora = new OracleServer(connectionString); //LoanDetail detail; string command = string.Empty; try { command = "SELECT NUMERO_CARPETA, NUMERO_IDENTIFICACION, NOMBRE_PERSONA, FECHA_CREACION, CODIGO_ESTADO_CARPETA, "; command = command + "NOMBRE_ESTADO_CARPETA, MONTO_SOLICITADO, PLAZO_SOLICITADO, TASA_SOLICITADO, MONTO_APROBADO, "; command = command + "PLAZO_APROBADO, TASA_APROBADA, VALOR_CUOTA_APROBADA, CODIGO_CONVENIO_LIBRANZA, NOMBRE_CONVENIO, "; command = command + "CODIGO_PAGADURIA_LIBRANZA, NOMBRE_PAGADURIA, CODIGO_SUCURSAL, SUCURSAL_HOMOLOGA "; command = command + string.Format("FROM BBS_LIQCOM_V_CAB_CARPETAS WHERE NUMERO_CARPETA = {0}", folderNumber); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { response.folderNumber = DBNull.Value.Equals(rdr["NUMERO_CARPETA"]) ? 0 : double.Parse(rdr["NUMERO_CARPETA"].ToString()); response.numberID = DBNull.Value.Equals(rdr["NUMERO_IDENTIFICACION"]) ? string.Empty : rdr["NUMERO_IDENTIFICACION"].ToString(); response.personName = DBNull.Value.Equals(rdr["NOMBRE_PERSONA"]) ? string.Empty : rdr["NOMBRE_PERSONA"].ToString(); response.creationDate = DBNull.Value.Equals(rdr["FECHA_CREACION"]) ? DateTime.Today : DateTime.Parse(rdr["FECHA_CREACION"].ToString()); response.folderStateCode = DBNull.Value.Equals(rdr["CODIGO_ESTADO_CARPETA"]) ? 0 : double.Parse(rdr["CODIGO_ESTADO_CARPETA"].ToString()); response.folserStateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_CARPETA"]) ? string.Empty : rdr["NOMBRE_ESTADO_CARPETA"].ToString(); response.amountRequested = DBNull.Value.Equals(rdr["MONTO_SOLICITADO"]) ? 0 : decimal.Parse(rdr["MONTO_SOLICITADO"].ToString()); response.termRequested = DBNull.Value.Equals(rdr["PLAZO_SOLICITADO"]) ? 0 : decimal.Parse(rdr["PLAZO_SOLICITADO"].ToString()); response.rateRequested = DBNull.Value.Equals(rdr["TASA_SOLICITADO"]) ? 0 : decimal.Parse(rdr["TASA_SOLICITADO"].ToString()); response.amountApproved = DBNull.Value.Equals(rdr["MONTO_APROBADO"]) ? 0 : decimal.Parse(rdr["MONTO_APROBADO"].ToString()); response.termApproved = DBNull.Value.Equals(rdr["PLAZO_APROBADO"]) ? 0 : double.Parse(rdr["PLAZO_APROBADO"].ToString()); response.rateApproved = DBNull.Value.Equals(rdr["TASA_APROBADA"]) ? 0 : decimal.Parse(rdr["TASA_APROBADA"].ToString()); response.monthlyPaymentApproved = DBNull.Value.Equals(rdr["VALOR_CUOTA_APROBADA"]) ? 0 : decimal.Parse(rdr["VALOR_CUOTA_APROBADA"].ToString()); response.agreementCode = DBNull.Value.Equals(rdr["CODIGO_CONVENIO_LIBRANZA"]) ? 0 : double.Parse(rdr["CODIGO_CONVENIO_LIBRANZA"].ToString()); response.agreementName = DBNull.Value.Equals(rdr["NOMBRE_CONVENIO"]) ? string.Empty : rdr["NOMBRE_CONVENIO"].ToString(); response.payableCode = DBNull.Value.Equals(rdr["CODIGO_PAGADURIA_LIBRANZA"]) ? 0 : double.Parse(rdr["CODIGO_PAGADURIA_LIBRANZA"].ToString()); response.payableName = DBNull.Value.Equals(rdr["NOMBRE_PAGADURIA"]) ? string.Empty : rdr["NOMBRE_PAGADURIA"].ToString(); response.branchCode = DBNull.Value.Equals(rdr["CODIGO_SUCURSAL"]) ? 0 : double.Parse(rdr["CODIGO_SUCURSAL"].ToString()); response.branchHomologous = DBNull.Value.Equals(rdr["SUCURSAL_HOMOLOGA"]) ? 0 : double.Parse(rdr["SUCURSAL_HOMOLOGA"].ToString()); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("RequisitionDAO.GetLoanDetailList", ex); } finally { ora.Dispose(); } return(response); }
public OutUserOptions GetUserOptions(string executiveID, string ind_menu, string svrpath) { if (executiveID == "" || executiveID == null) { return(null); } string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutUserOptions response = new OutUserOptions(); var ora = new OracleServer(connectionString); UserOptions status; List <UserOptions> list = new List <UserOptions>(); string command = string.Empty; try { command = "select cedula, ind_menu_inicio, funcionalidad_nombre, ventana_objeto from dlg_portal_get_user_options"; command = command + string.Format(" where ind_menu_inicio = {0} and cedula = '{1}' ", ind_menu, executiveID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { status = new UserOptions(); status.funcionalidad_nombre = DBNull.Value.Equals(rdr["funcionalidad_nombre"]) ? string.Empty : rdr["funcionalidad_nombre"].ToString(); if (ind_menu == "5") { status.ventana_objeto = DBNull.Value.Equals(rdr["ventana_objeto"]) ? string.Empty : rdr["ventana_objeto"].ToString(); } else { status.ventana_objeto = DBNull.Value.Equals(rdr["ventana_objeto"]) ? string.Empty : /*svrpath +*/ rdr["ventana_objeto"].ToString(); } list.Add(status); } rdr.Close(); response.lstUserOptions = list; response.msg = new Response { errorCode = "200", errorMessage = "OK" }; } catch (Exception ex) { throw new Exception("UserDAO.GetUserOptions", ex); } finally { ora.Dispose(); } return(response); }
public OutLoanDetail GetLoanDetailList(double folderNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutLoanDetail response = new OutLoanDetail(); var ora = new OracleServer(connectionString); LoanDetail detail; List <LoanDetail> list = new List <LoanDetail>(); string command = string.Empty; try { command = "SELECT NUMERO_CARPETA, TIPO_TRAMITE, DESC_TIPO_TRAMITE, FECHA_TRAMITE, USUARIO_TRAMITE, CODIGO_ESTADO_ANTERIOR, "; command = command + "NOMBRE_ESTADO_ANT, CODIGO_ESTADO_NUEVO,NOMBRE_ESTADO_NVO, OBSERVACIONES,TIPO_LOG "; command = command + string.Format("FROM BBS_LIQCOM_V_DET_CARPETAS WHERE NUMERO_CARPETA = {0} ORDER BY FECHA_TRAMITE", folderNumber); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { detail = new LoanDetail(); detail.folderNumber = DBNull.Value.Equals(rdr["NUMERO_CARPETA"]) ? 0 : double.Parse(rdr["NUMERO_CARPETA"].ToString()); detail.processType = DBNull.Value.Equals(rdr["TIPO_TRAMITE"]) ? 0 : double.Parse(rdr["TIPO_TRAMITE"].ToString()); detail.processTypeDescription = DBNull.Value.Equals(rdr["DESC_TIPO_TRAMITE"]) ? string.Empty : rdr["DESC_TIPO_TRAMITE"].ToString(); detail.processDate = DBNull.Value.Equals(rdr["FECHA_TRAMITE"]) ? DateTime.Today.ToString() : rdr["FECHA_TRAMITE"].ToString(); detail.processUser = DBNull.Value.Equals(rdr["USUARIO_TRAMITE"]) ? string.Empty : rdr["USUARIO_TRAMITE"].ToString(); detail.previousStateCode = DBNull.Value.Equals(rdr["CODIGO_ESTADO_ANTERIOR"]) ? 0 : double.Parse(rdr["CODIGO_ESTADO_ANTERIOR"].ToString()); detail.previousStateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_ANT"]) ? string.Empty : rdr["NOMBRE_ESTADO_ANT"].ToString(); detail.NewStateCode = DBNull.Value.Equals(rdr["CODIGO_ESTADO_NUEVO"]) ? 0 : double.Parse(rdr["CODIGO_ESTADO_NUEVO"].ToString()); detail.NewStateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_NVO"]) ? string.Empty : rdr["NOMBRE_ESTADO_NVO"].ToString(); detail.observations = DBNull.Value.Equals(rdr["OBSERVACIONES"]) ? string.Empty : rdr["OBSERVACIONES"].ToString(); detail.LogType = DBNull.Value.Equals(rdr["TIPO_LOG"]) ? 0 : double.Parse(rdr["TIPO_LOG"].ToString()); list.Add(detail); } rdr.Close(); response.loanDetailList = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("RequisitionDAO.GetLoanDetailList", ex); } finally { ora.Dispose(); } return(response); }
public OutLogPQR GetLogPQR(int processNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutLogPQR response = new OutLogPQR(); var ora = new OracleServer(connectionString); LogPQR log; List <LogPQR> list = new List <LogPQR>(); string command = string.Empty; try { command = "SELECT NUMERO_PROCESO, SECUENCIA_NOVEDAD, ESTADO_ANTERIOR, ESTADO_NUEVO, DESCRIPCION, FECHA_INSERTA_NEGOCIO, USUARIO_INSERTA, "; command = command + "FECHA_INSERTA_SYS, NOMBRE_ESTADO_NVO, NOMBRE_ESTADO_ANT "; command = command + string.Format("FROM BBS_WFG_V_PQR_LOG WHERE NUMERO_PROCESO = {0} ", processNumber.ToString()); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { log = new LogPQR(); log.processNumber = DBNull.Value.Equals(rdr["NUMERO_PROCESO"]) ? 0 : int.Parse(rdr["NUMERO_PROCESO"].ToString()); log.SequenceNovelty = DBNull.Value.Equals(rdr["SECUENCIA_NOVEDAD"]) ? 0 : int.Parse(rdr["SECUENCIA_NOVEDAD"].ToString()); log.previousState = DBNull.Value.Equals(rdr["ESTADO_ANTERIOR"]) ? 0 : int.Parse(rdr["ESTADO_ANTERIOR"].ToString()); log.newState = DBNull.Value.Equals(rdr["ESTADO_NUEVO"]) ? 0 : int.Parse(rdr["ESTADO_NUEVO"].ToString()); log.description = DBNull.Value.Equals(rdr["DESCRIPCION"]) ? string.Empty : rdr["DESCRIPCION"].ToString(); log.dateInsertBusiness = DBNull.Value.Equals(rdr["FECHA_INSERTA_NEGOCIO"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_INSERTA_NEGOCIO"].ToString()).ToString("dd/MM/yyyy"); log.userInsert = DBNull.Value.Equals(rdr["USUARIO_INSERTA"]) ? string.Empty : rdr["USUARIO_INSERTA"].ToString(); log.dateInsertSYS = DBNull.Value.Equals(rdr["FECHA_INSERTA_SYS"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_INSERTA_SYS"].ToString()).ToString("dd/MM/yyyy"); log.statusNameNew = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_NVO"]) ? string.Empty : rdr["NOMBRE_ESTADO_NVO"].ToString(); log.statusNamePrevious = DBNull.Value.Equals(rdr["NOMBRE_ESTADO_ANT"]) ? string.Empty : rdr["NOMBRE_ESTADO_ANT"].ToString(); list.Add(log); } rdr.Close(); response.lstLogPQR = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetLogPQR", ex); } finally { ora.Dispose(); } return(response); }
public OutNoveltyPQR GetNoveltyPQR(int processNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutNoveltyPQR response = new OutNoveltyPQR(); var ora = new OracleServer(connectionString); NoveltyPQR novelty; List <NoveltyPQR> list = new List <NoveltyPQR>(); string command = string.Empty; try { command = "SELECT CREDITO, NUMERO_PROCESO, SECUENCIA_NOVEDAD, CODIGO_NOVEDAD, DESCRIPCION, FECHA_INSERTA_NEGOCIO, USUARIO_INSERTA, FECHA_INSERTA_SYS, "; command = command + string.Format("NOMBRE_NOVEDAD, FLUJO, PROMOTOR_NOMBRE FROM BBS_WFG_V_PQR_NOVEDAD WHERE NUMERO_PROCESO = {0} ", processNumber.ToString()); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { novelty = new NoveltyPQR(); novelty.loanNumber = DBNull.Value.Equals(rdr["CREDITO"]) ? 0 : int.Parse(rdr["CREDITO"].ToString()); novelty.processNumber = DBNull.Value.Equals(rdr["NUMERO_PROCESO"]) ? 0 : int.Parse(rdr["NUMERO_PROCESO"].ToString()); novelty.SequenceNovelty = DBNull.Value.Equals(rdr["SECUENCIA_NOVEDAD"]) ? 0 : int.Parse(rdr["SECUENCIA_NOVEDAD"].ToString()); novelty.noveltyCode = DBNull.Value.Equals(rdr["CODIGO_NOVEDAD"]) ? 0 : int.Parse(rdr["CODIGO_NOVEDAD"].ToString()); novelty.description = DBNull.Value.Equals(rdr["DESCRIPCION"]) ? string.Empty : rdr["DESCRIPCION"].ToString(); novelty.dateInsertBusiness = DBNull.Value.Equals(rdr["FECHA_INSERTA_NEGOCIO"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_INSERTA_NEGOCIO"].ToString()).ToString("dd/MM/yyyy"); novelty.userInsert = DBNull.Value.Equals(rdr["USUARIO_INSERTA"]) ? string.Empty : rdr["USUARIO_INSERTA"].ToString(); novelty.dateInsertSYS = DBNull.Value.Equals(rdr["FECHA_INSERTA_SYS"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_INSERTA_SYS"].ToString()).ToString("dd/MM/yyyy"); novelty.NoveltyName = DBNull.Value.Equals(rdr["NOMBRE_NOVEDAD"]) ? string.Empty : rdr["NOMBRE_NOVEDAD"].ToString(); novelty.flow = DBNull.Value.Equals(rdr["FLUJO"]) ? string.Empty : rdr["FLUJO"].ToString(); novelty.executiveName = DBNull.Value.Equals(rdr["PROMOTOR_NOMBRE"]) ? string.Empty : rdr["PROMOTOR_NOMBRE"].ToString(); list.Add(novelty); } rdr.Close(); response.lstNoveltyPQR = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetNoveltyPQR", ex); } finally { ora.Dispose(); } return(response); }
public OutSummaryPQR GetSummaryPQR(string executiveID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutSummaryPQR response = new OutSummaryPQR(); List <SummaryPQR> summaryList = new List <SummaryPQR>(); var ora = new OracleServer(connectionString); string command = string.Empty; try { command = "SELECT TO_CHAR(BBS_LIQCOM_V_PQRS.FECHA_INSERTA_SYS,'MM/YYYY') AS MES, BBS_LIQCOM_V_PQRS.NOMBRE_PROCESO, BBS_LIQCOM_V_PQRS.ESTADO, " + "BBS_LIQCOM_V_PQRS.NOMBRE_ESTADO, COUNT(*) AS NRO_ACLARACIONES FROM BBS_LIQCOM_V_PQRS "; command = command + string.Format(" WHERE CEDULA = '{0}'", executiveID); command = command + "GROUP BY TO_CHAR(BBS_LIQCOM_V_PQRS.FECHA_INSERTA_SYS, 'MM/YYYY'), BBS_LIQCOM_V_PQRS.NOMBRE_PROCESO, "; command = command + "BBS_LIQCOM_V_PQRS.ESTADO, BBS_LIQCOM_V_PQRS.NOMBRE_ESTADO ORDER BY TO_CHAR(BBS_LIQCOM_V_PQRS.FECHA_INSERTA_SYS,'MM/YYYY'), "; command = command + "BBS_LIQCOM_V_PQRS.NOMBRE_PROCESO, BBS_LIQCOM_V_PQRS.ESTADO"; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { SummaryPQR summary = new SummaryPQR(); summary.month = DBNull.Value.Equals(rdr["MES"]) ? string.Empty : rdr["MES"].ToString(); summary.processName = DBNull.Value.Equals(rdr["NOMBRE_PROCESO"]) ? string.Empty : rdr["NOMBRE_PROCESO"].ToString(); summary.status = DBNull.Value.Equals(rdr["ESTADO"]) ? 0 : double.Parse(rdr["ESTADO"].ToString()); summary.stateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO"]) ? string.Empty : rdr["NOMBRE_ESTADO"].ToString(); summary.PQRNumber = DBNull.Value.Equals(rdr["NRO_ACLARACIONES"]) ? 0 : double.Parse(rdr["NRO_ACLARACIONES"].ToString()); summaryList.Add(summary); } rdr.Close(); response.lstSummaryPQR = summaryList; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetLoanResume", ex); } finally { ora.Dispose(); } return(response); }
public OutParamGuias GetTablasAs() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutParamGuias response = new OutParamGuias(); var ora = new OracleServer(connectionString); ParamGuias guias; response.ListDoc = new List <ParamGuias>(); string command = string.Empty; try { command = " SELECT * FROM TABLAS WHERE IND_ESTADO = 1 ORDER BY SECTOR "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { guias = new ParamGuias(); guias.codigo_guia = DBNull.Value.Equals(rdr["CODIGO_TABLA"]) ? 0 : double.Parse(rdr["CODIGO_TABLA"].ToString()); guias.sector = DBNull.Value.Equals(rdr["SECTOR"]) ? 0 : double.Parse(rdr["SECTOR"].ToString()); guias.nombre = DBNull.Value.Equals(rdr["NOMBRE_DOC"]) ? "" : (rdr["NOMBRE_DOC"].ToString()); guias.path = DBNull.Value.Equals(rdr["URL_DOC"]) ? "" : (rdr["URL_DOC"].ToString()); guias.fecha = DBNull.Value.Equals(rdr["FECHA_ACT"]) ? "" : (rdr["FECHA_ACT"].ToString()); guias.fecha = guias.fecha.Substring(0, 10); guias.nombre = guias.nombre.Replace(".pdf", ""); guias.ind_estado = DBNull.Value.Equals(rdr["IND_ESTADO"]) ? 0 : double.Parse(rdr["IND_ESTADO"].ToString()); response.ListDoc.Add(guias); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("DocumentsDAO.GetTablas", ex); } finally { ora.Dispose(); } return(response); }
public OutUploadDocuments GetUploadDocuments(string documentID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutUploadDocuments response = new OutUploadDocuments(); var ora = new OracleServer(connectionString); UploadDocuments document; List <UploadDocuments> list = new List <UploadDocuments>(); string command = string.Empty; try { command = " SELECT BBS_PORTAL_LOG_IMG.CONSECUTIVO, BBS_PORTAL_LOG_IMG.DESCRIPCION_DOCUMENTO, BBS_PORTAL_LOG_IMG.NOMBRE_DOCUMENTO_CARGADO, "; command = string.Format("{0} BBS_PORTAL_LOG_IMG.YEAR, BBS_PORTAL_LOG_IMG.MONTH FROM BBS_PORTAL_LOG_IMG WHERE ", command); command = string.Format("{0} BBS_PORTAL_LOG_IMG.NUMERO_CEDULA = '{1}' ORDER BY BBS_PORTAL_LOG_IMG.CONSECUTIVO DESC ", command, documentID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { document = new UploadDocuments(); document.Consecutive = DBNull.Value.Equals(rdr["CONSECUTIVO"]) ? 0 : double.Parse(rdr["CONSECUTIVO"].ToString()); document.Description = DBNull.Value.Equals(rdr["DESCRIPCION_DOCUMENTO"]) ? string.Empty : rdr["DESCRIPCION_DOCUMENTO"].ToString(); document.Name = DBNull.Value.Equals(rdr["NOMBRE_DOCUMENTO_CARGADO"]) ? string.Empty : rdr["NOMBRE_DOCUMENTO_CARGADO"].ToString(); document.Year = DBNull.Value.Equals(rdr["YEAR"]) ? 0 : int.Parse(rdr["YEAR"].ToString()); document.Month = DBNull.Value.Equals(rdr["MONTH"]) ?0 : int.Parse(rdr["MONTH"].ToString()); list.Add(document); } rdr.Close(); response.lstUploadDocuments = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("DocumentsDAO.GetUploadDocuments", ex); } finally { ora.Dispose(); } return(response); }
public OutBalancesCommision GetBalancesCommissions(string executiveID, double accountNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutBalancesCommision response = new OutBalancesCommision(); var ora = new OracleServer(connectionString); BalancesCommision balance; List <BalancesCommision> list = new List <BalancesCommision>(); string command = string.Empty; try { command = "SELECT CODIGO_CONCEPTO, NOMBRE_CONCEPTO, SALDO_CONCEPTO, CODIGO_CUENTA, CODIGO_EJECUTIVO, CEDULA, NOMBRE_EJECUTIVO "; command = command + string.Format(" FROM BBS_LIQCOM_V_SALDOS WHERE cedula = '{0}' AND CODIGO_CUENTA = {1} ", executiveID, accountNumber); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { balance = new BalancesCommision(); balance.conceptCode = DBNull.Value.Equals(rdr["CODIGO_CONCEPTO"]) ? 0 : double.Parse(rdr["CODIGO_CONCEPTO"].ToString()); balance.conceptName = DBNull.Value.Equals(rdr["NOMBRE_CONCEPTO"]) ? string.Empty : rdr["NOMBRE_CONCEPTO"].ToString(); balance.conceptBalance = DBNull.Value.Equals(rdr["SALDO_CONCEPTO"]) ? 0 : double.Parse(rdr["SALDO_CONCEPTO"].ToString()); balance.accountCode = DBNull.Value.Equals(rdr["CODIGO_CUENTA"]) ? 0 : double.Parse(rdr["CODIGO_CUENTA"].ToString()); balance.executiveCode = DBNull.Value.Equals(rdr["CODIGO_EJECUTIVO"]) ? 0 : double.Parse(rdr["CODIGO_EJECUTIVO"].ToString()); balance.documentID = DBNull.Value.Equals(rdr["CEDULA"]) ? string.Empty : rdr["CEDULA"].ToString(); balance.executiveName = DBNull.Value.Equals(rdr["NOMBRE_EJECUTIVO"]) ? string.Empty : rdr["NOMBRE_EJECUTIVO"].ToString(); list.Add(balance); } rdr.Close(); response.lstBalancesCommision = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("CommissionsDAO.GetBalancesCommissions", ex); } finally { ora.Dispose(); } return(response); }
public OutParamDocuments GetLisDocuments(string documentType) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutParamDocuments response = new OutParamDocuments(); var ora = new OracleServer(connectionString); ParamDocuments paramDocuments; List <ParamDocuments> list = new List <ParamDocuments>(); string command = string.Empty; try { command = " SELECT SECUENCIA,DESCRIPCION_DOCUMENTO,RUTA_RAIZ,NOMBRE_DOCUMENTO,TIPO_PARAMETRO,CARPETA "; command = string.Format("{0} FROM BBS_PORTAL_RUTA_IMAGENES WHERE TIPO_PARAMETRO = '{1}'", command, documentType); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { paramDocuments = new ParamDocuments(); paramDocuments.Code = DBNull.Value.Equals(rdr["SECUENCIA"]) ? 0 : double.Parse(rdr["SECUENCIA"].ToString()); paramDocuments.Description = DBNull.Value.Equals(rdr["DESCRIPCION_DOCUMENTO"]) ? string.Empty : rdr["DESCRIPCION_DOCUMENTO"].ToString(); paramDocuments.Path = DBNull.Value.Equals(rdr["RUTA_RAIZ"]) ? string.Empty : rdr["RUTA_RAIZ"].ToString(); paramDocuments.Name = DBNull.Value.Equals(rdr["NOMBRE_DOCUMENTO"]) ? string.Empty : rdr["NOMBRE_DOCUMENTO"].ToString(); paramDocuments.DocumentType = DBNull.Value.Equals(rdr["NOMBRE_DOCUMENTO"]) ? string.Empty : rdr["NOMBRE_DOCUMENTO"].ToString(); paramDocuments.Folder = DBNull.Value.Equals(rdr["CARPETA"]) ? string.Empty : rdr["CARPETA"].ToString(); list.Add(paramDocuments); } rdr.Close(); response.lstParamDocuments = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetLisDocuments", ex); } finally { ora.Dispose(); } return(response); }
public OutAccumulatedClarifications GetAccumulatedClarifications(string executiveID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutAccumulatedClarifications response = new OutAccumulatedClarifications(); var ora = new OracleServer(connectionString); AccumulatedClarifications accumulated; List <AccumulatedClarifications> list = new List <AccumulatedClarifications>(); string command = string.Empty; try { command = "SELECT MES,NUMERO_ACLARACIONES,NUMERO_RESPONDIDAS,NUMERO_PENDIENTES,PORCENTAJE_RESPONDIDAS,PORCENTAJE_PENDIENTES "; command = command + string.Format(" FROM BBS_LIQCOM_V_PQR_RESUM WHERE BBS_LIQCOM_V_PQR_RESUM.CODIGO_ASESOR = '{0}' ", executiveID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { accumulated = new AccumulatedClarifications(); accumulated.month = DBNull.Value.Equals(rdr["MES"]) ? string.Empty : rdr["MES"].ToString(); accumulated.total = DBNull.Value.Equals(rdr["NUMERO_ACLARACIONES"]) ? 0 : double.Parse(rdr["NUMERO_ACLARACIONES"].ToString()); accumulated.answered = DBNull.Value.Equals(rdr["NUMERO_RESPONDIDAS"]) ? 0 : double.Parse(rdr["NUMERO_RESPONDIDAS"].ToString()); accumulated.pending = DBNull.Value.Equals(rdr["NUMERO_PENDIENTES"]) ? 0 : double.Parse(rdr["NUMERO_PENDIENTES"].ToString()); accumulated.percentageAnswered = DBNull.Value.Equals(rdr["PORCENTAJE_RESPONDIDAS"]) ? 0 : double.Parse(rdr["PORCENTAJE_RESPONDIDAS"].ToString()); accumulated.percentagePending = DBNull.Value.Equals(rdr["PORCENTAJE_PENDIENTES"]) ? 0 : double.Parse(rdr["PORCENTAJE_PENDIENTES"].ToString()); list.Add(accumulated); } rdr.Close(); response.lstAccumulatedClarifications = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ComplianceGoalDAO.GetAccumulatedClarifications", ex); } finally { ora.Dispose(); } return(response); }
public OutCategoryRange GetCategoryRange() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutCategoryRange response = new OutCategoryRange(); var ora = new OracleServer(connectionString); CategoryRange categoryRange; List <CategoryRange> list = new List <CategoryRange>(); string command = string.Empty; try { command = "SELECT bbs_liqcom_v_par_categoria.codigo_esquema, bbs_liqcom_v_par_categoria.nombre_esquema, bbs_liqcom_v_par_categoria.rango_inicial, bbs_liqcom_v_par_categoria.rango_final, " + " bbs_liqcom_v_par_categoria.codigo_categoria, bbs_liqcom_v_par_categoria.nombre_categoria FROM bbs_liqcom_v_par_categoria "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { categoryRange = new CategoryRange(); categoryRange.SchemeCode = DBNull.Value.Equals(rdr["codigo_esquema"]) ? 0 : double.Parse(rdr["codigo_esquema"].ToString()); categoryRange.SchemeName = DBNull.Value.Equals(rdr["nombre_esquema"]) ? string.Empty : rdr["nombre_esquema"].ToString(); categoryRange.InitialRange = DBNull.Value.Equals(rdr["rango_inicial"]) ? 0 : double.Parse(rdr["rango_inicial"].ToString()); categoryRange.FinalRange = DBNull.Value.Equals(rdr["rango_final"]) ? 0 : double.Parse(rdr["rango_final"].ToString()); categoryRange.CategoryCode = DBNull.Value.Equals(rdr["codigo_categoria"]) ? 0 : double.Parse(rdr["codigo_categoria"].ToString()); categoryRange.CategoryName = DBNull.Value.Equals(rdr["nombre_categoria"]) ? string.Empty : rdr["nombre_categoria"].ToString(); list.Add(categoryRange); } rdr.Close(); response.lstCategoryRange = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ComplianceGoalDAO.GetCategoryRange", ex); } finally { ora.Dispose(); } return(response); }
public ParamGuias GetIdGuias(double codigo) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutParamGuias response = new OutParamGuias(); var ora = new OracleServer(connectionString); ParamGuias guia = new ParamGuias(); string command = string.Empty; try { command = " SELECT * FROM GUIAS "; command += string.Format("WHERE CODIGO_GUIA = {0}", codigo); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { guia = new ParamGuias(); guia.codigo_guia = DBNull.Value.Equals(rdr["CODIGO_GUIA"]) ? 0 : double.Parse(rdr["CODIGO_GUIA"].ToString()); guia.sector = DBNull.Value.Equals(rdr["SECTOR"]) ? 0 : double.Parse(rdr["SECTOR"].ToString()); guia.nombre = DBNull.Value.Equals(rdr["NOMBRE_DOC"]) ? "" : (rdr["NOMBRE_DOC"].ToString()); guia.path = DBNull.Value.Equals(rdr["URL_DOC"]) ? "" : (rdr["URL_DOC"].ToString()); guia.fecha = DBNull.Value.Equals(rdr["FECHA_ACT"]) ? "" : (rdr["FECHA_ACT"].ToString()); guia.ind_estado = DBNull.Value.Equals(rdr["IND_ESTADO"]) ? 0 : double.Parse(rdr["IND_ESTADO"].ToString()); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("DocumentsDAO.GetGuias", ex); } finally { ora.Dispose(); } return(guia); }
public OutAccumulatedLoan GetAccumulatedLoan(string executiveID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutAccumulatedLoan response = new OutAccumulatedLoan(); var ora = new OracleServer(connectionString); AccumulatedLoan accumulated; List <AccumulatedLoan> list = new List <AccumulatedLoan>(); string command = string.Empty; try { command = "SELECT BBS_LIQCOM_V_COLOCA.CEDULA_ASESOR, BBS_LIQCOM_V_COLOCA.MES, BBS_LIQCOM_V_COLOCA.MONTO, BBS_LIQCOM_V_COLOCA.NRO_CREDITOS "; command = command + string.Format(" FROM BBS_LIQCOM_V_COLOCA WHERE BBS_LIQCOM_V_COLOCA.CEDULA_ASESOR = '{0}' ", executiveID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { accumulated = new AccumulatedLoan(); accumulated.amount = DBNull.Value.Equals(rdr["MONTO"]) ? 0 : double.Parse(rdr["MONTO"].ToString()); accumulated.executiveID = DBNull.Value.Equals(rdr["CEDULA_ASESOR"]) ? string.Empty : rdr["CEDULA_ASESOR"].ToString(); accumulated.month = DBNull.Value.Equals(rdr["MES"]) ? string.Empty : rdr["MES"].ToString(); accumulated.loanCount = DBNull.Value.Equals(rdr["NRO_CREDITOS"]) ? 0 : double.Parse(rdr["NRO_CREDITOS"].ToString()); list.Add(accumulated); } rdr.Close(); response.lstAccumulatedLoan = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ComplianceGoalDAO.GetBalancesCommissions", ex); } finally { ora.Dispose(); } return(response); }
public OutLoanResume GetLoanResume(double loanNumber) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutLoanResume response = new OutLoanResume(); var ora = new OracleServer(connectionString); string command = string.Empty; try { command = "SELECT NUMERO_CREDITO, FECHA_INICIO_CREDITO, MONTO, VALOR_DESEMBOSO, CODIGO_ASESOR," + " CODIGO_SUCURSAL, NOMBRE_CODIGO_SUCURSAL, CEDULA_ASESOR FROM BBS_LIQCOM_V_CREDITOSMX "; command = command + string.Format(" WHERE NUMERO_CREDITO = {0} ", loanNumber.ToString()); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { response.loanNumber = DBNull.Value.Equals(rdr["NUMERO_CREDITO"]) ? 0 : double.Parse(rdr["NUMERO_CREDITO"].ToString()); response.startDate = DBNull.Value.Equals(rdr["FECHA_INICIO_CREDITO"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_INICIO_CREDITO"].ToString()).ToString("dd/MM/yyyy"); response.amount = DBNull.Value.Equals(rdr["MONTO"]) ? 0 : double.Parse(rdr["MONTO"].ToString()); response.disbursement = DBNull.Value.Equals(rdr["VALOR_DESEMBOSO"]) ? 0 : double.Parse(rdr["VALOR_DESEMBOSO"].ToString()); response.executiveCode = DBNull.Value.Equals(rdr["CODIGO_ASESOR"]) ? 0 : double.Parse(rdr["CODIGO_ASESOR"].ToString()); response.branchCode = DBNull.Value.Equals(rdr["CODIGO_SUCURSAL"]) ? 0 : double.Parse(rdr["CODIGO_SUCURSAL"].ToString()); response.branchName = DBNull.Value.Equals(rdr["NOMBRE_CODIGO_SUCURSAL"]) ? string.Empty : rdr["NOMBRE_CODIGO_SUCURSAL"].ToString(); response.documentID = DBNull.Value.Equals(rdr["CEDULA_ASESOR"]) ? string.Empty : rdr["CEDULA_ASESOR"].ToString(); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetLoanResume", ex); } finally { ora.Dispose(); } return(response); }
public OutProductivity GetProductivity(string executiveID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutProductivity response = new OutProductivity(); var ora = new OracleServer(connectionString); Productivity productivity; List <Productivity> list = new List <Productivity>(); string command = string.Empty; try { command = "SELECT FIGURA,NRO_CREDITOS,MONTO_CREDITOS,PROMEDIO FROM BBS_LIQCOM_V_PROD_JEFE WHERE "; command = command + string.Format(" CEDULA_ASESOR = '{0}' ", executiveID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { productivity = new Productivity(); productivity.Figure = DBNull.Value.Equals(rdr["FIGURA"]) ? string.Empty : rdr["FIGURA"].ToString(); productivity.LoanCount = DBNull.Value.Equals(rdr["NRO_CREDITOS"]) ? 0 : double.Parse(rdr["NRO_CREDITOS"].ToString()); productivity.LoanAmount = DBNull.Value.Equals(rdr["MONTO_CREDITOS"]) ? 0 : double.Parse(rdr["MONTO_CREDITOS"].ToString()); productivity.Average = DBNull.Value.Equals(rdr["PROMEDIO"]) ? 0 : double.Parse(rdr["PROMEDIO"].ToString()); list.Add(productivity); } rdr.Close(); response.lstProductivity = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ComplianceGoalDAO.GetProductivity", ex); } finally { ora.Dispose(); } return(response); }
public OutFolder GetFolderInformation(string customerID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutFolder response = new OutFolder(); var ora = new OracleServer(connectionString); Folder folder; List <Folder> list = new List <Folder>(); string command = string.Empty; try { command = "select numero_carpeta, fecha_creacion, monto_solicitado, plazo_solicitado from v_dlg_carpetas "; command += "where not codigo_estado_carpeta in (0, 100, 1300, 1500, 1600, 1700, 1800, 900000) "; command += string.Format("and numero_identificacion = '{0}'", customerID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { folder = new Folder(); folder.folder = DBNull.Value.Equals(rdr["numero_carpeta"]) ? 0 : double.Parse(rdr["numero_carpeta"].ToString()); folder.monto = DBNull.Value.Equals(rdr["monto_solicitado"]) ? 0 : double.Parse(rdr["monto_solicitado"].ToString()); folder.plazo = DBNull.Value.Equals(rdr["plazo_solicitado"]) ? 0 : double.Parse(rdr["plazo_solicitado"].ToString()); folder.create_date = DBNull.Value.Equals(rdr["fecha_creacion"]) ? string.Empty : rdr["fecha_creacion"].ToString(); list.Add(folder); } rdr.Close(); response.lstFolder = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("CustomerDAO.GetFolderInformation", ex); } finally { ora.Dispose(); } return(response); }
/// <summary> /// Gets the neighborhood. /// </summary> /// <param name="municipalityID">The municipality identifier.</param> /// <returns></returns> /// <exception cref="Exception">ParametersDAO.GetNeighborhood</exception> public OutDepartments GetNeighborhood(int municipalityID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutDepartments response = new OutDepartments(); var ora = new OracleServer(connectionString); Departments department; List <Departments> list = new List <Departments>(); string command = string.Empty; try { command = " select cod_ctro_pobla, nom_ctro_pobla, cod_munic from dfpctropoblado "; command = command + string.Format(" where cod_munic = {0} ", municipalityID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { department = new Departments(); department.populationID = DBNull.Value.Equals(rdr["cod_ctro_pobla"]) ? 0 : int.Parse(rdr["cod_ctro_pobla"].ToString()); department.populationName = DBNull.Value.Equals(rdr["nom_ctro_pobla"]) ? string.Empty : rdr["nom_ctro_pobla"].ToString(); department.municipalityID = DBNull.Value.Equals(rdr["cod_munic"]) ? 0 : int.Parse(rdr["cod_munic"].ToString()); list.Add(department); } rdr.Close(); response.lstDepartments = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetNeighborhood", ex); } finally { ora.Dispose(); } return(response); }
/// <summary> /// Gets the cities. /// </summary> /// <param name="departmentID">The department identifier.</param> /// <returns></returns> /// <exception cref="Exception">ParametersDAO.GetCities</exception> public OutDepartments GetCities(int departmentID) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutDepartments response = new OutDepartments(); var ora = new OracleServer(connectionString); Departments department; List <Departments> list = new List <Departments>(); string command = string.Empty; try { command = "select distinct A.cod_munic, A.nom_munic, A.cod_depto from dfpctropoblado A where CONSECUTIVO > 0 "; command = command + string.Format(" and A.cod_depto = {0} ", departmentID); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { department = new Departments(); department.municipalityID = DBNull.Value.Equals(rdr["cod_munic"]) ? 0 : int.Parse(rdr["cod_munic"].ToString()); department.municipalityName = DBNull.Value.Equals(rdr["nom_munic"]) ? string.Empty : rdr["nom_munic"].ToString(); department.departmentID = DBNull.Value.Equals(rdr["cod_depto"]) ? 0 : int.Parse(rdr["cod_depto"].ToString()); list.Add(department); } rdr.Close(); response.lstDepartments = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetCities", ex); } finally { ora.Dispose(); } return(response); }
/// <summary> /// Gets the born city. /// </summary> /// <returns></returns> /// <exception cref="Exception">ParametersDAO.GetBornCity</exception> public OutBornCities GetBornCity() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutBornCities response = new OutBornCities(); var ora = new OracleServer(connectionString); BornCities city; List <BornCities> list = new List <BornCities>(); string command = string.Empty; try { command = " SELECT to_char(A.nombre_ciudad || ' / ' || B.nombre_depto) as nombre_ciudad, A.codigo_ciudad as codigo_ciudad "; command = command + " FROM dfpcdd07 A, dfpdpt06 B WHERE B.codigo_estado = A.codigo_estado and B.codigo_depto = A.codigo_depto "; command = command + " and A.Codigo_Ciudad > 0 order by 1 "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { city = new BornCities(); city.cityName = DBNull.Value.Equals(rdr["nombre_ciudad"]) ? string.Empty : rdr["nombre_ciudad"].ToString(); city.cityCode = DBNull.Value.Equals(rdr["codigo_ciudad"]) ? 0 : int.Parse(rdr["codigo_ciudad"].ToString()); list.Add(city); } rdr.Close(); response.lstBornCities = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetBornCity", ex); } finally { ora.Dispose(); } return(response); }
public OutExecutiveChilds GetExecutiveChilds(string executiveID, int level) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutExecutiveChilds response = new OutExecutiveChilds(); var ora = new OracleServer(connectionString); ExecutiveChilds executiveChilds; List <ExecutiveChilds> list = new List <ExecutiveChilds>(); string command = string.Empty; try { command = "SELECT BBS_LIQCOM_V_ASECARGO.codigo_asesor_hijo, BBS_LIQCOM_V_ASECARGO.cedula_asesor_hijo, BBS_LIQCOM_V_ASECARGO.nombre_asesor_hijo FROM BBS_LIQCOM_V_ASECARGO "; command = command + string.Format(" WHERE BBS_LIQCOM_V_ASECARGO.cedula_asesor_superior = '{0}' and BBS_LIQCOM_V_ASECARGO.nivel = {1} ", executiveID, level); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { executiveChilds = new ExecutiveChilds(); executiveChilds.Code = DBNull.Value.Equals(rdr["codigo_asesor_hijo"]) ? 0 : double.Parse(rdr["codigo_asesor_hijo"].ToString()); executiveChilds.Name = DBNull.Value.Equals(rdr["nombre_asesor_hijo"]) ? string.Empty : rdr["nombre_asesor_hijo"].ToString(); executiveChilds.DocumentID = DBNull.Value.Equals(rdr["cedula_asesor_hijo"]) ? string.Empty : rdr["cedula_asesor_hijo"].ToString(); list.Add(executiveChilds); } rdr.Close(); response.lstExecutiveChilds = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ExecutiveDAO.GetExecutiveChilds", ex); } finally { ora.Dispose(); } return(response); }
public OutStates GetStates() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutStates response = new OutStates(); var ora = new OracleServer(connectionString); State state; List <State> list = new List <State>(); string command = string.Empty; try { command = "SELECT TIPO_FLUJO, NOMBRE_FLUJO, CODIGO_ESTADO, NOMBRE_ESTADO FROM BBS_LIQCOM_V_EDO_PQR "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { state = new State(); state.flowType = DBNull.Value.Equals(rdr["TIPO_FLUJO"]) ? 0 : int.Parse(rdr["TIPO_FLUJO"].ToString()); state.flowName = DBNull.Value.Equals(rdr["NOMBRE_FLUJO"]) ? string.Empty : rdr["NOMBRE_FLUJO"].ToString(); state.codeState = DBNull.Value.Equals(rdr["CODIGO_ESTADO"]) ? 0 : int.Parse(rdr["CODIGO_ESTADO"].ToString()); state.stateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO"]) ? string.Empty : rdr["NOMBRE_ESTADO"].ToString(); list.Add(state); } rdr.Close(); response.lstStates = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetStates", ex); } finally { ora.Dispose(); } return(response); }
/// <summary> /// Gets the eps. /// </summary> /// <returns></returns> /// <exception cref="Exception">ParametersDAO.GetEPS</exception> public OutEPS GetEPS() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutEPS response = new OutEPS(); var ora = new OracleServer(connectionString); EPS eps; List <EPS> list = new List <EPS>(); string command = string.Empty; try { command = " SELECT NIT, NOMBRE, CODIGO_MINISTERIO FROM BBS_WFC_PAR_EPS where nit> 0 "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { eps = new EPS(); eps.epsNIT = DBNull.Value.Equals(rdr["NIT"]) ? string.Empty : rdr["NIT"].ToString(); eps.epsName = DBNull.Value.Equals(rdr["NOMBRE"]) ? string.Empty : rdr["NOMBRE"].ToString(); eps.epsCodeMinistry = DBNull.Value.Equals(rdr["CODIGO_MINISTERIO"]) ? string.Empty : rdr["CODIGO_MINISTERIO"].ToString(); list.Add(eps); } rdr.Close(); response.lstEPS = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetEPS", ex); } finally { ora.Dispose(); } return(response); }
/// <summary> /// Gets the banks. /// </summary> /// <returns></returns> /// <exception cref="Exception">ParametersDAO.GetBanks</exception> public OutBanks GetBanks() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutBanks response = new OutBanks(); var ora = new OracleServer(connectionString); Banks bank; List <Banks> list = new List <Banks>(); string command = string.Empty; try { command = " select trim(A.BCO_NIT_ENTIDAD) as BCO_NIT_ENTIDAD, trim(A.Bco_Nombre_Entidad ) as Bco_Nombre_Entidad "; command = command + " from dfcpsn02_bancos A where A.nui > 0 order by 2"; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { bank = new Banks(); bank.entityNIT = DBNull.Value.Equals(rdr["BCO_NIT_ENTIDAD"]) ? string.Empty : rdr["BCO_NIT_ENTIDAD"].ToString(); bank.entityName = DBNull.Value.Equals(rdr["Bco_Nombre_Entidad"]) ? string.Empty : rdr["Bco_Nombre_Entidad"].ToString(); list.Add(bank); } rdr.Close(); response.lstBanks = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("ParametersDAO.GetBanks", ex); } finally { ora.Dispose(); } return(response); }
public OutFlowType GetFlows() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutFlowType response = new OutFlowType(); var ora = new OracleServer(connectionString); FlowType flow; List <FlowType> list = new List <FlowType>(); string command = string.Empty; try { command = "SELECT BBS_WFG_TIPO_FLUJO.TIPO_FLUJO, BBS_WFG_TIPO_FLUJO.NOMBRE_FLUJO FROM BBS_WFG_TIPO_FLUJO "; command = command + " WHERE BBS_WFG_TIPO_FLUJO.IND_VER_DESDE_PORTAL = 1 AND BBS_WFG_TIPO_FLUJO.ESTADO_FLUJO = 1 "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { flow = new FlowType(); flow.flowType = DBNull.Value.Equals(rdr["TIPO_FLUJO"]) ? 0 : int.Parse(rdr["TIPO_FLUJO"].ToString()); flow.flowName = DBNull.Value.Equals(rdr["NOMBRE_FLUJO"]) ? string.Empty : rdr["NOMBRE_FLUJO"].ToString(); list.Add(flow); } rdr.Close(); response.lstFlowType = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetFlows", ex); } finally { ora.Dispose(); } return(response); }
public OutJustification GetJustification(int flowType) { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutJustification response = new OutJustification(); var ora = new OracleServer(connectionString); Justification justification; List <Justification> list = new List <Justification>(); string command = string.Empty; try { command = "SELECT BBS_WFG_TIPO_JUSTIFICA.TIPO_REQUISITO, BBS_WFG_TIPO_JUSTIFICA.NOMBRE_REQUISITO FROM BBS_WFG_TIPO_JUSTIFICA "; command = command + string.Format(" WHERE BBS_WFG_TIPO_JUSTIFICA.TIPO_FLUJO = {0} ", flowType); var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { justification = new Justification(); justification.justificationType = DBNull.Value.Equals(rdr["TIPO_REQUISITO"]) ? 0 : int.Parse(rdr["TIPO_REQUISITO"].ToString()); justification.justificationName = DBNull.Value.Equals(rdr["NOMBRE_REQUISITO"]) ? string.Empty : rdr["NOMBRE_REQUISITO"].ToString(); list.Add(justification); } rdr.Close(); response.lstJustification = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("PqrDAO.GetJustification", ex); } finally { ora.Dispose(); } return(response); }
public OutDocuments GetDocuments() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutDocuments response = new OutDocuments(); var ora = new OracleServer(connectionString); Documents document; List <Documents> list = new List <Documents>(); string command = string.Empty; try { command = " SELECT BBS_LIQCOM_PARAM_CARGA_DOCTOS.TIPO_DOCTO, BBS_LIQCOM_PARAM_CARGA_DOCTOS.NOMBRE_DOCTO FROM BBS_LIQCOM_PARAM_CARGA_DOCTOS "; command = command + " WHERE BBS_LIQCOM_PARAM_CARGA_DOCTOS.IND_ACTIVO = 1 order by ORDEN , TIPO_DOCTO "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { document = new Documents(); document.documentType = DBNull.Value.Equals(rdr["TIPO_DOCTO"]) ? 0 : int.Parse(rdr["TIPO_DOCTO"].ToString()); document.documentName = DBNull.Value.Equals(rdr["NOMBRE_DOCTO"]) ? string.Empty : rdr["NOMBRE_DOCTO"].ToString(); list.Add(document); } rdr.Close(); response.lstDocuments = list; response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("DocumentsDAO.GetDocuments", ex); } finally { ora.Dispose(); } return(response); }
public OutParamSectorTablas GetddSectorTablas() { string connectionString = DataBaseHelper.GetConnectionString("DLG"); OutParamSectorTablas response = new OutParamSectorTablas(); var ora = new OracleServer(connectionString); ParamSectorTablas Secguias; response.ListSectorTablas = new List <ParamSectorTablas>(); string command = string.Empty; try { command = " select * from dlg_param_sectorTablas where ind_estado = 1 order by sector "; var rdr = ora.ExecuteCommand(command); while (rdr.Read()) { Secguias = new ParamSectorTablas(); Secguias.secuencia = DBNull.Value.Equals(rdr["SECUENCIA"]) ? 0 : double.Parse(rdr["SECUENCIA"].ToString()); Secguias.sector = DBNull.Value.Equals(rdr["SECTOR"]) ? "" : (rdr["SECTOR"].ToString()); response.ListSectorTablas.Add(Secguias); } rdr.Close(); response.msg = new Response(); response.msg.errorCode = "200"; response.msg.errorMessage = "OK"; } catch (Exception ex) { throw new Exception("DocumentsDAO.GetddSectorTablas", ex); } finally { ora.Dispose(); } return(response); }