public Response EliminarArchvio(double codigo, string tabla)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
var pi_codigo = new OracleParameter("fa_codigo_doc", OracleDbType.Double, codigo, ParameterDirection.Input);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
try
{
po_ErrorMessage.Size = 200;
ora.AddParameter(pi_codigo);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
var sp = (tabla == "GUIA") ? "sp_delete_doc_guias" : "sp_delete_doc_tablas";
ora.ExecuteProcedureNonQuery(sp);
//Respuesta del procedimiento
response.errorCode = ora.GetParameter("fa_Error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
}
catch (Exception ex)
{
throw new Exception("ParamsDAO.deleteConfigDoc", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public Response ChangeEstadoSoli(string asesor)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
var pi_cod_asesor = new OracleParameter("fa_asesor", OracleDbType.Varchar2, asesor, ParameterDirection.Input);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
try
{
po_ErrorMessage.Size = 2000;
ora.AddParameter(pi_cod_asesor);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("sp_dlg_dias_expiracion_form");
//Respuesta del procedimiento
response.errorCode = ora.GetParameter("fa_Error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
}
catch (Exception ex)
{
throw new Exception("ParamsDAO.ChangeEstadoSoli", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutValidateUser ValidateUser(string asesor, ref InValidateUser user)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutValidateUser response = new OutValidateUser();
var ora = new OracleServer(connectionString);
try
{
var pi_Company = new OracleParameter("fa_empresa", OracleDbType.Double, user.company, ParameterDirection.Input);
var pi_asesor = new OracleParameter("fa_asesor", OracleDbType.Varchar2, asesor, ParameterDirection.Input);
var po_User = new OracleParameter("fa_usuario", OracleDbType.Varchar2, user.userID, ParameterDirection.Output)
{
Size = 100
};
var po_Password = new OracleParameter("fa_clave", OracleDbType.Varchar2, user.password, ParameterDirection.Output)
{
Size = 100
};
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
var po_ChangePassword = new OracleParameter("fa_ind_obliga_cambio", OracleDbType.Double, ParameterDirection.Output);
var po_UserName = new OracleParameter("fa_nombre_usuario", OracleDbType.Varchar2, ParameterDirection.Output)
{
Size = 100
};
po_ErrorMessage.Size = 100;
ora.AddParameter(pi_Company);
ora.AddParameter(pi_asesor);
ora.AddParameter(po_User);
ora.AddParameter(po_Password);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.AddParameter(po_ChangePassword);
ora.AddParameter(po_UserName);
ora.ExecuteProcedureNonQuery("dlg_portal_val_ingreso_asesor");
response.msg.errorCode = ora.GetParameter("fa_Error").ToString();
response.msg.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
response.userName = ora.GetParameter("fa_nombre_usuario").ToString();
response.userName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(response.userName.ToLower());
response.changePassword = int.Parse(ora.GetParameter("fa_ind_obliga_cambio").ToString());
response.userID = ora.GetParameter("fa_usuario").ToString();
response.password = ora.GetParameter("fa_clave").ToString();
}
catch (Exception ex)
{
throw new Exception("AuthenticationDAO.ValidateUser", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutNextCategory GetNextCategory(string executiveID)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
OutNextCategory response = new OutNextCategory();
try
{
var pi_documentID = new OracleParameter("fa_codigo_asesor", OracleDbType.Varchar2, executiveID, ParameterDirection.Input);
ora.AddParameter(pi_documentID);
var pi_amount = new OracleParameter("fa_MONTO_COLOCAR", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(pi_amount);
var pi_categoryCode = new OracleParameter("fa_CODIGO_CATEGORIA", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(pi_categoryCode);
var pi_categoryName = new OracleParameter("fa_NOMBRE_CATEGORIA", OracleDbType.Varchar2, ParameterDirection.Output);
pi_categoryName.Size = 100;
ora.AddParameter(pi_categoryName);
var pi_schemeCode = new OracleParameter("fa_codigo_esquema", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(pi_schemeCode);
var pi_subscheme = new OracleParameter("fa_codigo_subesquema", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(pi_subscheme);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_LIQCOM2_F_BUSCA_SIG_CAT");
response.msg = new Response();
response.amount = double.Parse(ora.GetParameter("fa_MONTO_COLOCAR").ToString());
response.categoryName = ora.GetParameter("fa_NOMBRE_CATEGORIA").ToString();
response.msg.errorCode = ora.GetParameter("fa_Error").ToString();
response.msg.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
ora.Dispose();
}
catch (Exception ex)
{
throw new Exception("ComplianceGoalDAO.GetNextCategory", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutValidateUser ValidateUser(InValidateUser user)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutValidateUser response = new OutValidateUser();
var ora = new OracleServer(connectionString);
try
{
System.Diagnostics.Debug.WriteLine("empresa --> ", user.company);
var pi_Company = new OracleParameter("fa_empresa", OracleDbType.Double, user.company, ParameterDirection.Input);
var pi_User = new OracleParameter("fa_usuario", OracleDbType.Varchar2, user.userID, ParameterDirection.Input);
var pi_Password = new OracleParameter("fa_clave", OracleDbType.Varchar2, user.password, ParameterDirection.Input);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
var po_ChangePassword = new OracleParameter("fa_ind_obliga_cambio", OracleDbType.Double, ParameterDirection.Output);
var po_UserName = new OracleParameter("fa_nombre_usuario", OracleDbType.Varchar2, ParameterDirection.Output);
var po_sucursal = new OracleParameter("fa_sucursal", OracleDbType.Double, ParameterDirection.Output);
var po_asesor = new OracleParameter("fa_codigo_asesor", OracleDbType.Double, ParameterDirection.Output);
po_UserName.Size = 300;
po_ErrorMessage.Size = 300;
ora.AddParameter(pi_Company);
ora.AddParameter(pi_User);
ora.AddParameter(pi_Password);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.AddParameter(po_ChangePassword);
ora.AddParameter(po_UserName);
ora.AddParameter(po_sucursal);
ora.AddParameter(po_asesor);
ora.ExecuteProcedureNonQuery("BBS_PORTAL_F_VALIDA_INGRESO");
response.msg.errorCode = ora.GetParameter("fa_Error").ToString();
response.msg.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
response.userName = ora.GetParameter("fa_nombre_usuario").ToString();
response.changePassword = int.Parse(ora.GetParameter("fa_ind_obliga_cambio").ToString());
response.sucursal = double.Parse(ora.GetParameter("fa_sucursal").ToString());
response.asesor = double.Parse(ora.GetParameter("fa_codigo_asesor").ToString());
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
throw new Exception("AuthenticationDAO.ValidateUser", ex);
}
finally {
ora.Dispose();
}
return(response);
}
public Response ChangePassword(InValidateUser input)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
Response response = new Response();
try
{
var ora = new OracleServer(connectionString);
var pi_User = new OracleParameter("fa_cedula", OracleDbType.Double, input.userID, ParameterDirection.Input);
var pi_Password = new OracleParameter("fa_clave", OracleDbType.Varchar2, input.password, ParameterDirection.Input);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(pi_User);
ora.AddParameter(pi_Password);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_PORTAL_F_CAMBIO_CLAVE");
response.errorCode = ora.GetParameter("fa_Error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
ora.Dispose();
}
catch (Exception ex)
{
throw new Exception("ChangePassword", ex);
}
return(response);
}
public Response LogUploadDocuments(InLogDocuments input)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
string esNuloError = string.Empty;
try
{
var pi_sequence = new OracleParameter("A_SECUENCIA", OracleDbType.Double, input.Sequence, ParameterDirection.Input);
ora.AddParameter(pi_sequence);
var pi_path = new OracleParameter("A_RUTA_DOCUMENTO_CARGADO", OracleDbType.Varchar2, input.Path, ParameterDirection.Input);
ora.AddParameter(pi_path);
var pi_name = new OracleParameter("A_NOMBRE_DOCUMENTO_CARGADO", OracleDbType.Varchar2, input.Name, ParameterDirection.Input);
ora.AddParameter(pi_name);
var pi_documentID = new OracleParameter("A_NUMERO_CEDULA", OracleDbType.Varchar2, input.DocumentID, ParameterDirection.Input);
ora.AddParameter(pi_documentID);
var po_ErrorCode = new OracleParameter("fa_codigo_error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_LIQCOM_SP_LOG_CARGA_IMG");
response.errorCode = ora.GetParameter("fa_codigo_error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
ora.Dispose();
}
catch (Exception ex)
{
//response.errorMessage = ex.InnerException.ToString();
//throw new Exception("ExecutiveDAO.UpdateExecutive", ex);
LogHelper.WriteLog("Models", "ManagerPQR", "CreatePQR" + esNuloError, ex, "");
}
finally
{
ora.Dispose();
}
return(response);
}
public Response EditarArchvio(ref ParamGuias guia, string tabla)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
var pi_codigo = new OracleParameter("fa_codigo_doc", OracleDbType.Double, guia.codigo_guia, ParameterDirection.Input);
var pi_nombre = new OracleParameter("fa_Nombre_doc", OracleDbType.Varchar2, guia.nombre, ParameterDirection.Input);
var pi_url = new OracleParameter("fa_path", OracleDbType.Varchar2, guia.path, ParameterDirection.Input);
var pi_sector = new OracleParameter("fa_sector", OracleDbType.Varchar2, guia.sector, ParameterDirection.Input);
var pi_estado = new OracleParameter("fa_estado", OracleDbType.Double, guia.ind_estado, ParameterDirection.Input);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
try
{
po_ErrorMessage.Size = 200;
ora.AddParameter(pi_codigo);
ora.AddParameter(pi_nombre);
ora.AddParameter(pi_url);
ora.AddParameter(pi_sector);
ora.AddParameter(pi_estado);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
var sp = (tabla == "GUIA") ? "sp_upd_doc_guias" : "sp_upd_doc_tablas";
ora.ExecuteProcedureNonQuery(sp);
//Respuesta del procedimiento
response.errorCode = ora.GetParameter("fa_Error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
}
catch (Exception ex)
{
throw new Exception("ParamsDAO.EditarArchvio", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public Response UpdateExecutive(InUpdateExecutive input)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
try
{
var pi_documentID = new OracleParameter("fa_cedula", OracleDbType.Varchar2, input.documentID, ParameterDirection.Input);
ora.AddParameter(pi_documentID);
var pi_name1 = new OracleParameter("fa_nombre1", OracleDbType.Varchar2, input.name1, ParameterDirection.Input);
ora.AddParameter(pi_name1);
var pi_name2 = new OracleParameter("fa_nombre2", OracleDbType.Varchar2, input.name2, ParameterDirection.Input);
ora.AddParameter(pi_name2);
var pi_surname1 = new OracleParameter("fa_apellido1", OracleDbType.Varchar2, input.surname1, ParameterDirection.Input);
ora.AddParameter(pi_surname1);
var pi_surname2 = new OracleParameter("fa_apellido2", OracleDbType.Varchar2, input.surname2, ParameterDirection.Input);
ora.AddParameter(pi_surname2);
var pi_birthDate = new OracleParameter("fa_fecha_nacto", OracleDbType.Date, input.birthDate, ParameterDirection.Input);
ora.AddParameter(pi_birthDate);
var pi_placeBirth = new OracleParameter("fa_lugar_nacto", OracleDbType.Double, input.placeBirth, ParameterDirection.Input);
ora.AddParameter(pi_placeBirth);
var pi_gender = new OracleParameter("fa_genero_persona", OracleDbType.Double, input.gender, ParameterDirection.Input);
ora.AddParameter(pi_gender);
var pi_civilStatus = new OracleParameter("fa_estado_civil", OracleDbType.Double, input.civilStatus, ParameterDirection.Input);
ora.AddParameter(pi_civilStatus);
var pi_notifyAddress = new OracleParameter("fa_direccion_notificacion", OracleDbType.Varchar2, input.notifyAddress, ParameterDirection.Input);
ora.AddParameter(pi_notifyAddress);
var pi_department = new OracleParameter("fa_departamento", OracleDbType.Double, input.department, ParameterDirection.Input);
ora.AddParameter(pi_department);
var pi_city = new OracleParameter("fa_ciudad_ase", OracleDbType.Double, input.city, ParameterDirection.Input);
ora.AddParameter(pi_city);
var pi_neighborhood = new OracleParameter("fa_barrio", OracleDbType.Double, input.neighborhood, ParameterDirection.Input);
ora.AddParameter(pi_neighborhood);
var pi_executivePhone = new OracleParameter("fa_telefono_ejecutivo", OracleDbType.Varchar2, input.executivePhone, ParameterDirection.Input);
ora.AddParameter(pi_executivePhone);
var pi_housePhone = new OracleParameter("fa_telefono_fijo", OracleDbType.Varchar2, input.housePhone, ParameterDirection.Input);
ora.AddParameter(pi_housePhone);
var pi_housingType = new OracleParameter("fa_tipo_vivienda", OracleDbType.Double, input.housingType, ParameterDirection.Input);
ora.AddParameter(pi_housingType);
var pi_email = new OracleParameter("fa_correo_electronico", OracleDbType.Varchar2, input.email, ParameterDirection.Input);
ora.AddParameter(pi_email);
var pi_appliedStudies = new OracleParameter("fa_estudios_realizados", OracleDbType.Double, input.appliedStudies, ParameterDirection.Input);
ora.AddParameter(pi_appliedStudies);
var pi_notifyEmail = new OracleParameter("fa_correo_ele_notificacion", OracleDbType.Varchar2, input.notifyEmail, ParameterDirection.Input);
ora.AddParameter(pi_notifyEmail);
var pi_bayportEmail = new OracleParameter("fa_correo_ele_bayport", OracleDbType.Varchar2, input.bayportEmail, ParameterDirection.Input);
ora.AddParameter(pi_bayportEmail);
var pi_emergencyPhone = new OracleParameter("fa_tel_caso_de_emergencia", OracleDbType.Varchar2, input.emergencyPhone, ParameterDirection.Input);
ora.AddParameter(pi_emergencyPhone);
var pi_bankAccount = new OracleParameter("fa_cuentabancaria", OracleDbType.Varchar2, input.bankAccount, ParameterDirection.Input);
ora.AddParameter(pi_bankAccount);
var pi_accountType = new OracleParameter("fa_tipodecuenta", OracleDbType.Double, input.accountType, ParameterDirection.Input);
ora.AddParameter(pi_accountType);
var pi_entityBank = new OracleParameter("fa_bancooentidad", OracleDbType.Double, input.entityBank, ParameterDirection.Input);
ora.AddParameter(pi_entityBank);
var pi_spouseName = new OracleParameter("fa_nombre_conyuge", OracleDbType.Varchar2, input.spouseName, ParameterDirection.Input);
ora.AddParameter(pi_spouseName);
var pi_spouseID = new OracleParameter("fa_cedula_conyuge", OracleDbType.Varchar2, input.spouseID, ParameterDirection.Input);
ora.AddParameter(pi_spouseID);
var pi_spouseCellphone = new OracleParameter("fa_celular_conyuge", OracleDbType.Varchar2, input.spouseCellphone, ParameterDirection.Input);
ora.AddParameter(pi_spouseCellphone);
var pi_spouseEmail = new OracleParameter("fa_correo_ele_conyuge", OracleDbType.Varchar2, input.spouseEmail, ParameterDirection.Input);
ora.AddParameter(pi_spouseEmail);
var pi_assets = new OracleParameter("fa_total_activos", OracleDbType.Double, input.assets, ParameterDirection.Input);
ora.AddParameter(pi_assets);
var pi_liabilities = new OracleParameter("fa_total_pasivos", OracleDbType.Double, input.liabilities, ParameterDirection.Input);
ora.AddParameter(pi_liabilities);
var pi_income = new OracleParameter("fa_ingresos", OracleDbType.Double, input.income, ParameterDirection.Input);
ora.AddParameter(pi_income);
var pi_expenses = new OracleParameter("fa_gastos", OracleDbType.Double, input.expenses, ParameterDirection.Input);
ora.AddParameter(pi_expenses);
var pi_otherIncome = new OracleParameter("fa_otros_ingresos", OracleDbType.Double, input.otherIncome, ParameterDirection.Input);
ora.AddParameter(pi_otherIncome);
var pi_afpNIT = new OracleParameter("fa_nit_afp", OracleDbType.Double, input.afpNIT, ParameterDirection.Input);
ora.AddParameter(pi_afpNIT);
var pi_arpNIT = new OracleParameter("fa_nit_arp", OracleDbType.Double, input.arpNIT, ParameterDirection.Input);
ora.AddParameter(pi_arpNIT);
var pi_epsNIT = new OracleParameter("fa_nit_eps", OracleDbType.Double, input.epsNIT, ParameterDirection.Input);
ora.AddParameter(pi_epsNIT);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("DLGWFC_F_UPDATE_ASESOR_PORTAL");
response.errorCode = ora.GetParameter("fa_Error").ToString();
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
ora.Dispose();
}
catch (Exception ex)
{
throw new Exception("ExecutiveDAO.UpdateExecutive", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutProgressExecutive GetProgressExecutive(string executiveID, DateTime startDate, DateTime endDate)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutProgressExecutive response = new OutProgressExecutive();
var ora = new OracleServer(connectionString);
try
{
var po_executiveID = new OracleParameter("fa_cedula_asesor", OracleDbType.Varchar2, executiveID, ParameterDirection.Input);
ora.AddParameter(po_executiveID);
var po_iniDate = new OracleParameter("fa_fecha_ini", OracleDbType.Date, startDate, ParameterDirection.Input);
ora.AddParameter(po_iniDate);
var po_endDate = new OracleParameter("fa_fecha_fin", OracleDbType.Date, endDate, ParameterDirection.Input);
ora.AddParameter(po_endDate);
var po_newLoanCount = new OracleParameter("fa_nro_credito_nuevos", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_newLoanCount);
var po_NewDisbursementAmount = new OracleParameter("fa_valor_desembolso_nuevos", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_NewDisbursementAmount);
var po_NewLoanValue = new OracleParameter("fa_valor_credito_nuevos", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_NewLoanValue);
var po_RenovatedLoanCount = new OracleParameter("fa_nro_credito_renovados", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_RenovatedLoanCount);
var po_RenovatedDisbursementAmount = new OracleParameter("fa_valor_desembolso_renovados", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_RenovatedDisbursementAmount);
var po_RenovatedLoanValue = new OracleParameter("fa_valor_credito_renovados", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_RenovatedLoanValue);
var po_LoanCount = new OracleParameter("fa_nro_creditos", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_LoanCount);
var po_DisbursementAmount = new OracleParameter("fa_valor_desembolso", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_DisbursementAmount);
var po_LoanValue = new OracleParameter("fa_valor_credito", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_LoanValue);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_LIQCOM2_F_COLOCA_RESUM");
response.progressExecutive = new ProgressExecutive();
response.progressExecutive.newLoanCount = double.Parse(ora.GetParameter("fa_nro_credito_nuevos").ToString());
response.progressExecutive.NewDisbursementAmount = double.Parse(ora.GetParameter("fa_valor_desembolso_nuevos").ToString());
response.progressExecutive.NewLoanValue = double.Parse(ora.GetParameter("fa_valor_credito_nuevos").ToString());
response.progressExecutive.RenovatedLoanCount = double.Parse(ora.GetParameter("fa_nro_credito_renovados").ToString());
response.progressExecutive.RenovatedDisbursementAmount = double.Parse(ora.GetParameter("fa_valor_desembolso_renovados").ToString());
response.progressExecutive.RenovatedLoanValue = double.Parse(ora.GetParameter("fa_valor_credito_renovados").ToString());
response.progressExecutive.LoanCount = double.Parse(ora.GetParameter("fa_nro_creditos").ToString());
response.progressExecutive.DisbursementAmount = double.Parse(ora.GetParameter("fa_valor_desembolso").ToString());
response.progressExecutive.LoanValue = double.Parse(ora.GetParameter("fa_valor_credito").ToString());
}
catch (Exception ex)
{
throw new Exception("ComplianceGoalDAO.GetProgressExecutive", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutGoalSupervisor GetGoalSupervisor(string supervisorID)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutGoalSupervisor response = new OutGoalSupervisor();
var ora = new OracleServer(connectionString);
try
{
var po_executiveID = new OracleParameter("fa_cedula_asesor", OracleDbType.Varchar2, supervisorID, ParameterDirection.Input);
ora.AddParameter(po_executiveID);
var po_goalValue = new OracleParameter("fa_META_VLR", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_goalValue);
var po_complianceValue = new OracleParameter("fa_CUMPLIMIENTO_VLR", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_complianceValue);
var po_compliancePercentage = new OracleParameter("fa_CUMPLIMIENTO_PORC", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_compliancePercentage);
var po_goalNewValue = new OracleParameter("fa_META_VLR_NUEVOS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_goalNewValue);
var po_complianceNewValue = new OracleParameter("fa_CUMPLIMIENTO_VLR_NUEVOS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_complianceNewValue);
var po_complianceNewPercentage = new OracleParameter("fa_CUMPLIMIENTO_PORC_NUEVOS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_complianceNewPercentage);
var po_goalRenovatedValue = new OracleParameter("fa_META_VLR_RENOVADO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_goalRenovatedValue);
var po_complianceRenovatedValue = new OracleParameter("fa_CUMPLIMIENTO_VLR_RENOVADO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_complianceRenovatedValue);
var po_complianceRenovatedPercentage = new OracleParameter("fa_CUMPLIMIENTO_PORC_RENOVADO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_complianceRenovatedPercentage);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_LIQCOM2_F_META_JEFE");
response.goalSupervisor = new GoalSupervisor();
response.goalSupervisor.goalValue = double.Parse(ora.GetParameter("fa_META_VLR").ToString());
response.goalSupervisor.complianceValue = double.Parse(ora.GetParameter("fa_CUMPLIMIENTO_VLR").ToString());
response.goalSupervisor.compliancePercentage = double.Parse(ora.GetParameter("fa_CUMPLIMIENTO_PORC").ToString());
}
catch (Exception ex)
{
throw new Exception("ComplianceGoalDAO.GetGoalSupervisor", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
public OutCategorySimulation GetCategorySimulation(InCategorySimulation input)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
OutCategorySimulation response = new OutCategorySimulation();
try
{
var pi_executiveCode = new OracleParameter("fa_codigo_asesor", OracleDbType.Varchar2, input.executiveCode, ParameterDirection.Input);
ora.AddParameter(pi_executiveCode);
var pi_amount = new OracleParameter("fa_monto_digitado", OracleDbType.Double, input.amount, ParameterDirection.Input);
ora.AddParameter(pi_amount);
var pi_currentInd = new OracleParameter("fa_ind_actual", OracleDbType.Double, 0, ParameterDirection.Input);
ora.AddParameter(pi_currentInd);
var po_case = new OracleParameter("fa_CASO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_case);
var po_previousFigure = new OracleParameter("fa_FIGURA_ANTERIOR", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_previousFigure);
var po_previousFigureName = new OracleParameter("fa_NOMBRE_FIGURA_ANTERIOR", OracleDbType.Varchar2, ParameterDirection.Output);
ora.AddParameter(po_previousFigureName);
var po_currentFigure = new OracleParameter("fa_FIGURA_ACTUAL", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_currentFigure);
var po_currentFigureName = new OracleParameter("fa_NOMBRE_FIGURA_ACTUAL", OracleDbType.Varchar2, ParameterDirection.Output);
ora.AddParameter(po_currentFigureName);
var po_iniDayFigAnt = new OracleParameter("fa_DIA_INI_ALTA_FIG_ACT", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_iniDayFigAnt);
var po_endDayFigAnt = new OracleParameter("fa_DIA_FIN_ALTA_FIG_ACT", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_endDayFigAnt);
var po_daysIniFigCur = new OracleParameter("fa_DIF_DIAS_INI_FIG_ACT", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_daysIniFigCur);
var po_daysEndFigCur = new OracleParameter("fa_DIF_DIAS_FIN_FIG_ACT ", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_daysEndFigCur);
var po_indDateRange = new OracleParameter("fa_IND_ACUMULA_RANGO_FECHAS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_indDateRange);
var po_categoryValue = new OracleParameter("fa_VALOR_TMP_PARA_CATEGORIZAR", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_categoryValue);
var po_fromDate = new OracleParameter("fa_FECHA_DESDE", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_fromDate);
var po_toDate = new OracleParameter("fa_FECHA_HASTA", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_toDate);
var po_averagingNro = new OracleParameter("fa_PROMEDIAR_EN_NRO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_averagingNro);
var po_schemeCode = new OracleParameter("fa_CODIGO_ESQUEMA", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_schemeCode);
var po_subschemeCode = new OracleParameter("fa_CODIGO_SUBESQUEMA", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_subschemeCode);
var po_amountLoans = new OracleParameter("fa_MONTO_TOTAL_CREDITOS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_amountLoans);
var po_averagingPeriod = new OracleParameter("fa_PROMEDIO_PERIODO", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_averagingPeriod);
var po_iniDateAcum = new OracleParameter("fa_FECHA_INI_ACUM", OracleDbType.Date, ParameterDirection.Output);
ora.AddParameter(po_iniDateAcum);
var po_endDateAcum = new OracleParameter("fa_FECHA_FIN_ACUM", OracleDbType.Date, ParameterDirection.Output);
ora.AddParameter(po_endDateAcum);
var po_dateCurrent = new OracleParameter("fa_FEC_ALTA_FIG_ACTUAL", OracleDbType.Date, ParameterDirection.Output);
ora.AddParameter(po_dateCurrent);
var po_dateprevious = new OracleParameter("fa_FEC_ALTA_FIG_ANTERIOR", OracleDbType.Date, ParameterDirection.Output);
ora.AddParameter(po_dateprevious);
var po_dayDif = new OracleParameter("fa_DIFERENCIA_DIAS", OracleDbType.Double, ParameterDirection.Output);
ora.AddParameter(po_dayDif);
var po_categoryCode = new OracleParameter("fa_CODIGO_CATEGORIA", OracleDbType.Double, ParameterDirection.Output);
var po_categoryName = new OracleParameter("fa_NOMBRE_CATEGORIA", OracleDbType.Varchar2, ParameterDirection.Output);
var po_feeNew = new OracleParameter("fa_TASA_NUEVOS", OracleDbType.Double, ParameterDirection.Output);
var po_feeRenovated = new OracleParameter("fa_TASA_RENOVADOS", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
po_categoryName.Size = 100;
po_previousFigureName.Size = 100;
po_currentFigureName.Size = 100;
ora.AddParameter(po_categoryCode);
ora.AddParameter(po_categoryName);
ora.AddParameter(po_feeNew);
ora.AddParameter(po_feeRenovated);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_LIQCOM2_F_SIMULA_CAT");
response.categoryCode = double.Parse(ora.GetParameter("fa_CODIGO_CATEGORIA").ToString());
response.categoryName = ora.GetParameter("fa_NOMBRE_CATEGORIA").ToString();
response.feeNew = ora.GetParameter("fa_TASA_NUEVOS").ToString();
response.feeRenovated = ora.GetParameter("fa_TASA_RENOVADOS").ToString();
ora.Dispose();
}
catch (Exception ex)
{
LogHelper.WriteLog("Models", "SimulatorDAO", "GetCategorySimulation", ex, "");
}
finally
{
ora.Dispose();
}
return(response);
}
public Response CreatePQR(InCreatePQR input)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
var ora = new OracleServer(connectionString);
Response response = new Response();
string esNuloError = string.Empty;
try
{
var pi_company = new OracleParameter("fa_empresa", OracleDbType.Double, input.company, ParameterDirection.Input);
ora.AddParameter(pi_company);
var pi_flowType = new OracleParameter("fa_tipo_flujo", OracleDbType.Double, input.flowType, ParameterDirection.Input);
ora.AddParameter(pi_flowType);
var pi_reason = new OracleParameter("fa_motivo", OracleDbType.Double, input.reason, ParameterDirection.Input);
ora.AddParameter(pi_reason);
var pi_executiveID = new OracleParameter("fa_cedula_asesor", OracleDbType.Varchar2, input.executiveID, ParameterDirection.Input);
ora.AddParameter(pi_executiveID);
var pi_customerID = new OracleParameter("fa_cedula_cliente", OracleDbType.Varchar2, input.customerID, ParameterDirection.Input);
ora.AddParameter(pi_customerID);
var pi_description = new OracleParameter("fa_breve_descripcion", OracleDbType.Varchar2, input.description, ParameterDirection.Input);
ora.AddParameter(pi_description);
var pi_loanNumber = new OracleParameter("fa_numero_credito", OracleDbType.Varchar2, input.loanNumber, ParameterDirection.Input);
ora.AddParameter(pi_loanNumber);
var pi_PQRnumber = new OracleParameter("fa_numero_pqrs", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorCode = new OracleParameter("fa_Error", OracleDbType.Double, ParameterDirection.Output);
var po_ErrorMessage = new OracleParameter("fa_Descripcion_Error", OracleDbType.Varchar2, ParameterDirection.Output);
po_ErrorMessage.Size = 100;
ora.AddParameter(pi_PQRnumber);
ora.AddParameter(po_ErrorCode);
ora.AddParameter(po_ErrorMessage);
ora.ExecuteProcedureNonQuery("BBS_PQRS_F_CREAR_CASO");
if (ora.GetParameter("fa_Error") == null)
{
esNuloError = "fa_error es nulo";
}
else
{
response.errorCode = ora.GetParameter("fa_Error").ToString();
}
if (ora.GetParameter("fa_Descripcion_Error") == null)
{
esNuloError = esNuloError + "fa_descripcion_error es nulo";
}
else
{
response.errorMessage = ora.GetParameter("fa_Descripcion_Error").ToString();
}
if (ora.GetParameter("fa_numero_pqrs") == null)
{
esNuloError = esNuloError + "fa_numero_pqrs es nulo";
}
else
{
response.errorMessage = "Se ha creado el PQR con identificador: " + ora.GetParameter("fa_numero_pqrs").ToString();
}
ora.Dispose();
}
catch (Exception ex)
{
//response.errorMessage = ex.InnerException.ToString();
//throw new Exception("ExecutiveDAO.UpdateExecutive", ex);
LogHelper.WriteLog("Models", "ManagerPQR", "CreatePQR" + esNuloError, ex, "");
}
finally
{
ora.Dispose();
}
return(response);
}