public OutMovesCommission GetMovesCommissions(string executiveID, double accountNumber, string creditNumber, int reportType, DateTime startDate,
DateTime endDate, string[] childList)
{
OutMovesCommission data = new OutMovesCommission();
try
{
string childs = string.Empty;
if (childList != null)
{
for (int i = 0; i < childList.Length; i++)
{
childs += "'" + childList[i] + "'" + ",";
}
childs = childs.Substring(0, childs.Length - 1);
}
CommissionsDAO dao = new CommissionsDAO();
data = dao.GetMovesCommissions(executiveID, accountNumber, creditNumber, reportType, startDate, endDate, childs);
}
catch (Exception ex)
{
//escribir en el log
LogHelper.WriteLog("Models", "ManageCommissions", "GetMovesCommissions", ex, "");
}
return(data);
}
/// <summary>
///
/// </summary>
/// <param name="executiveID"></param>
/// <param name="creditNumber"></param>
/// <param name="reportType">1 Por fecha de desembolso, 2 Por fecha de pago, 3 Por credito</param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public OutMovesCommission GetMovesCommissions(string executiveID, double accountNumber, string creditNumber, int reportType, DateTime startDate, DateTime endDate, string childs)
{
string connectionString = DataBaseHelper.GetConnectionString("DLG");
OutMovesCommission response = new OutMovesCommission();
var ora = new OracleServer(connectionString);
MovementCommission movement;
List <MovementCommission> list = new List <MovementCommission>();
string command = string.Empty;
try
{
command = "SELECT CONCEPTO, NOMBRE_CONCEPTO, VALOR_DB, VALOR_CR, FECHA_PAGO, NRO_CREDITO, FECHA_DESEMBOLSO, NUEVO_RENOVADO," +
"DESEMBOLSO_TOTAL, COLOCACION_TOTAL, PORC_COMISION, IMPORTE_COMISION, TIPO_ESQUEMA_PAGO, OBSERVACIONES, NATURALEZA, " +
" PORC_BONOS, IMPORTE_BONOS, TOTAL_PAGADO FROM BBS_LIQCOM_V_MOVIMIENTOSMX WHERE ";
switch (reportType)
{
case 1:
command = command + string.Format(" cedula = '{0}' AND CODIGO_CUENTA = {1} ", executiveID, accountNumber);
command = command + string.Format(" AND FECHA_DESEMBOLSO BETWEEN to_date('{0}', 'dd-mm-yyyy') AND to_date('{1}', 'dd-mm-yyyy')", startDate.ToString("dd-MM-yyyy"), endDate.ToString("dd-MM-yyyy"));
break;
case 2:
command = command + string.Format(" cedula = '{0}' AND CODIGO_CUENTA = {1} ", executiveID, accountNumber);
command = command + string.Format(" AND FECHA_PAGO BETWEEN to_date('{0}', 'dd-mm-yyyy') AND to_date('{1}', 'dd-mm-yyyy')", startDate.ToString("dd-MM-yyyy"), endDate.ToString("dd-MM-yyyy"));
break;
case 3:
command = command + string.Format(" cedula = '{0}' AND CODIGO_CUENTA = {1} ", executiveID, accountNumber);
command = command + string.Format(" AND NRO_CREDITO = {0}", creditNumber);
break;
case 4:
command = command + string.Format(" cedula in ({0}) ", childs);
break;
}
LogHelper.WriteLog("DAO", "CommisionsDAO", "GetMovesCommissions" + command, new Exception(), "");
var rdr = ora.ExecuteCommand(command);
while (rdr.Read())
{
movement = new MovementCommission();
movement.conceptCode = DBNull.Value.Equals(rdr["concepto"]) ? 0 : double.Parse(rdr["concepto"].ToString());
movement.conceptName = DBNull.Value.Equals(rdr["NOMBRE_CONCEPTO"]) ? string.Empty : rdr["NOMBRE_CONCEPTO"].ToString();
movement.ammountDB = DBNull.Value.Equals(rdr["valor_db"]) ? 0 : double.Parse(rdr["valor_db"].ToString());
movement.ammountCR = DBNull.Value.Equals(rdr["valor_cr"]) ? 0 : double.Parse(rdr["valor_cr"].ToString());
movement.paymentDate = DBNull.Value.Equals(rdr["fecha_pago"]) ? DateTime.Today : DateTime.Parse(rdr["fecha_pago"].ToString());
movement.creditNumber = DBNull.Value.Equals(rdr["nro_credito"]) ? 0 : double.Parse(rdr["nro_credito"].ToString());
movement.disbursementDate = DBNull.Value.Equals(rdr["fecha_desembolso"]) ? DateTime.Today : DateTime.Parse(rdr["fecha_desembolso"].ToString());
movement.newRenovated = DBNull.Value.Equals(rdr["nuevo_renovado"]) ? string.Empty : rdr["nuevo_renovado"].ToString();
movement.disbursementAmount = DBNull.Value.Equals(rdr["desembolso_total"]) ? 0 : double.Parse(rdr["desembolso_total"].ToString());
movement.investmentAmount = DBNull.Value.Equals(rdr["colocacion_total"]) ? 0 : double.Parse(rdr["colocacion_total"].ToString());
movement.commissionPercentage = DBNull.Value.Equals(rdr["porc_comision"]) ? 0 : double.Parse(rdr["porc_comision"].ToString());
movement.commissionAmount = DBNull.Value.Equals(rdr["importe_comision"]) ? 0 : double.Parse(rdr["importe_comision"].ToString());
movement.schemePayment = DBNull.Value.Equals(rdr["tipo_esquema_pago"]) ? string.Empty : rdr["tipo_esquema_pago"].ToString();
movement.observations = DBNull.Value.Equals(rdr["OBSERVACIONES"]) ? string.Empty : rdr["OBSERVACIONES"].ToString();
movement.nature = DBNull.Value.Equals(rdr["NATURALEZA"]) ? string.Empty : rdr["NATURALEZA"].ToString();
movement.bonnusPercentage = DBNull.Value.Equals(rdr["PORC_BONOS"]) ? 0 : double.Parse(rdr["PORC_BONOS"].ToString());
movement.bonnusAmount = DBNull.Value.Equals(rdr["IMPORTE_BONOS"]) ? 0 : double.Parse(rdr["IMPORTE_BONOS"].ToString());
movement.paymentTotal = DBNull.Value.Equals(rdr["TOTAL_PAGADO"]) ? 0 : double.Parse(rdr["TOTAL_PAGADO"].ToString());
list.Add(movement);
}
rdr.Close();
response.lstMovesCommission = list;
response.msg = new Response();
response.msg.errorCode = "200";
response.msg.errorMessage = "OK";
}
catch (Exception ex)
{
throw new Exception("CommissionsDAO.GetMovesCommissions", ex);
}
finally
{
ora.Dispose();
}
return(response);
}
