public OutHeaderPQR GetHeaderPQR(string executiveID, int reportType, DateTime startDate, DateTime endDate, string loanNumber, string PQRnumber,
int flowType, string status, string childs)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutHeaderPQR response = new OutHeaderPQR();
var ora = new OracleServer(connectionString);
HeaderPQR justification;
List <HeaderPQR> list = new List <HeaderPQR>();
string command = string.Empty;
try
{
command = "SELECT NUMERO_PROCESO, NOMBRE_PROCESO, TIPO_FLUJO, NOMBRE_FLUJO, ESTADO, LLAVE_PROCESO, NUI, NUMERO_CARPETA, NRO_CREDITO, " +
"FECHA_INSERTA_NEGOCIO, USUARIO_INSERTA, FECHA_INSERTA_SYS, TIPO_LLAVE, NOMBRE_CLIENTE, NOMBRE_ESTADO, PRIORIDAD_MANUAL, " +
"NOMBRE_PRIORIDAD, CEDULA, ANALISTA_ASIGNADO, CEDULA_ASESOR_RADICA, FECHA_CIERRE, CODIGO_JUSTIFICACION , MOTIVO, PROMOTOR_NOMBRE FROM BBS_WFG_V_PQR_CAB WHERE";
switch (reportType)
{
case 1:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}' AND NUMERO_PROCESO = {1}", executiveID, PQRnumber);
break;
case 2:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}' AND NRO_CREDITO = {1}", executiveID, loanNumber);
break;
case 3:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}' AND FECHA_DESEMBOLSO BETWEEN to_date('{1}', 'dd-mm-yyyy') AND to_date('{2}', 'dd-mm-yyyy')", executiveID, startDate.ToString("dd-MM-yyyy"), endDate.ToString("dd-MM-yyyy"));
break;
case 4:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}' AND FECHA_PAGO BETWEEN to_date('{1}', 'dd-mm-yyyy') AND to_date('{2}', 'dd-mm-yyyy')", executiveID, startDate.ToString("dd-MM-yyyy"), endDate.ToString("dd-MM-yyyy"));
break;
case 5:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}'AND TIPO_FLUJO = {1}", executiveID, flowType);
break;
case 6:
command = command + string.Format(" CEDULA_ASESOR_RADICA = '{0}' AND TIPO_FLUJO = {1} AND ESTADO ={2} ", executiveID, flowType, status);
break;
case 7:
command = command + string.Format(" CEDULA_ASESOR_RADICA in ({0}) ", childs);
break;
}
var rdr = ora.ExecuteCommand(command);
while (rdr.Read())
{
justification = new HeaderPQR();
justification.processNumber = DBNull.Value.Equals(rdr["NUMERO_PROCESO"]) ? 0 : int.Parse(rdr["NUMERO_PROCESO"].ToString());
justification.processName = DBNull.Value.Equals(rdr["NOMBRE_PROCESO"]) ? string.Empty : rdr["NOMBRE_PROCESO"].ToString();
justification.flowType = DBNull.Value.Equals(rdr["TIPO_FLUJO"]) ? 0 : int.Parse(rdr["TIPO_FLUJO"].ToString());
justification.flowName = DBNull.Value.Equals(rdr["NOMBRE_FLUJO"]) ? string.Empty : rdr["NOMBRE_FLUJO"].ToString();
justification.state = DBNull.Value.Equals(rdr["ESTADO"]) ? 0 : int.Parse(rdr["ESTADO"].ToString());
justification.processKey = DBNull.Value.Equals(rdr["LLAVE_PROCESO"]) ? 0 : int.Parse(rdr["LLAVE_PROCESO"].ToString());
justification.nui = DBNull.Value.Equals(rdr["NUI"]) ? 0 : double.Parse(rdr["NUI"].ToString());
justification.folderNumber = DBNull.Value.Equals(rdr["NUMERO_CARPETA"]) ? 0 : int.Parse(rdr["NUMERO_CARPETA"].ToString());
justification.loanNumber = DBNull.Value.Equals(rdr["NRO_CREDITO"]) ? 0 : int.Parse(rdr["NRO_CREDITO"].ToString());
justification.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");
justification.userInsert = DBNull.Value.Equals(rdr["USUARIO_INSERTA"]) ? string.Empty : rdr["USUARIO_INSERTA"].ToString();
justification.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");
justification.keyType = DBNull.Value.Equals(rdr["TIPO_LLAVE"]) ? 0 : int.Parse(rdr["TIPO_LLAVE"].ToString());
justification.customerName = DBNull.Value.Equals(rdr["NOMBRE_CLIENTE"]) ? string.Empty : rdr["NOMBRE_CLIENTE"].ToString();
justification.stateName = DBNull.Value.Equals(rdr["NOMBRE_ESTADO"]) ? string.Empty : rdr["NOMBRE_ESTADO"].ToString();
justification.priorityManual = DBNull.Value.Equals(rdr["PRIORIDAD_MANUAL"]) ? 0 : int.Parse(rdr["PRIORIDAD_MANUAL"].ToString());
justification.priorityName = DBNull.Value.Equals(rdr["NOMBRE_PRIORIDAD"]) ? string.Empty : rdr["NOMBRE_PRIORIDAD"].ToString();
justification.documentID = DBNull.Value.Equals(rdr["CEDULA"]) ? string.Empty : rdr["CEDULA"].ToString();
justification.AssignedAnalyst = DBNull.Value.Equals(rdr["ANALISTA_ASIGNADO"]) ? string.Empty : rdr["ANALISTA_ASIGNADO"].ToString();
justification.closeDate = DBNull.Value.Equals(rdr["FECHA_CIERRE"]) ? DateTime.Today.ToString("dd/MM/yyyy") : DateTime.Parse(rdr["FECHA_CIERRE"].ToString()).ToString("dd/MM/yyyy");
justification.justificationCode = DBNull.Value.Equals(rdr["CODIGO_JUSTIFICACION"]) ? 0 : int.Parse(rdr["CODIGO_JUSTIFICACION"].ToString());
justification.reason = DBNull.Value.Equals(rdr["MOTIVO"]) ? string.Empty : rdr["MOTIVO"].ToString();
justification.executiveName = DBNull.Value.Equals(rdr["PROMOTOR_NOMBRE"]) ? string.Empty : rdr["PROMOTOR_NOMBRE"].ToString();
list.Add(justification);
}
rdr.Close();
response.lstHeaderPQR = 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);
}
