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