private void FunCargarCombos(int opcion)
{
switch (opcion)
{
case 0:
DdlCedente.DataSource = new CedenteDAO().FunGetCedentes();
DdlCedente.DataTextField = "Descripcion";
DdlCedente.DataValueField = "Codigo";
DdlCedente.DataBind();
itemC.Text = "--Seleccione Catálago/Producto--";
itemC.Value = "0";
DdlCatalogo.Items.Add(itemC);
itemG.Text = "--Seleccione Gestor--";
itemG.Value = "0";
DdlGestores.Items.Add(itemG);
break;
case 1:
dtbBrenchDet.Clear();
ViewState["BrenchDet"] = dtbBrench;
dtbBrench.Clear();
ViewState["BrenchCab"] = dtbBrench;
GrdvBrenchCab.DataSource = dtbBrench;
GrdvBrenchCab.DataBind();
GrdvBrenchDet.DataSource = dtbBrenchDet;
GrdvBrenchDet.DataBind();
DdlCatalogo.DataSource = new CedenteDAO().FunGetCatalogoProducto(int.Parse(DdlCedente.SelectedValue));
DdlCatalogo.DataTextField = "CatalogoProducto";
DdlCatalogo.DataValueField = "CodigoCatalogo";
DdlCatalogo.DataBind();
dts = new ControllerDAO().FunGetConsultasCatalogo(12, "--Seleccione Gestor--", int.Parse(DdlCedente.SelectedValue),
0, 0, "", "", "", Session["Conectar"].ToString());
DdlGestores.DataSource = dts;
DdlGestores.DataTextField = "Descripcion";
DdlGestores.DataValueField = "Codigo";
DdlGestores.DataBind();
break;
case 2:
dtbBrenchDet.Clear();
ViewState["BrenchDet"] = dtbBrench;
dtbBrench.Clear();
ViewState["BrenchCab"] = dtbBrench;
GrdvBrenchCab.DataSource = dtbBrench;
GrdvBrenchCab.DataBind();
GrdvBrenchDet.DataSource = dtbBrenchDet;
GrdvBrenchDet.DataBind();
dts = new CedenteDAO().FunGetBrenchDet(int.Parse(DdlCedente.SelectedValue), int.Parse(ViewState["codigoCPCE"].ToString()));
GrdvBrenchCab.DataSource = dts;
GrdvBrenchCab.DataBind();
ViewState["BrenchCab"] = dts.Tables[0];
break;
}
}
protected void ImgModificar_Click(object sender, ImageClickEventArgs e)
{
try
{
if (string.IsNullOrEmpty(TxtPresupuesto.Text.Trim()) || TxtPresupuesto.Text.Trim() == "0")
{
new FuncionesDAO().FunShowJSMessage("Ingrese Presupuesto..!", this);
return;
}
//vPorcentaje = Math.Round(decimal.Parse(TxtPresupuesto.Text.Trim(),CultureInfo.InvariantCulture), 2);
vExigible = Math.Round(decimal.Parse(ViewState["Exigible"].ToString()), 2);
//vPresupuesto = Math.Round((vExigible * vPorcentaje) / 100, 2);
vPresupuesto = decimal.Parse(TxtPresupuesto.Text.Trim(), CultureInfo.InvariantCulture);
vPorcentaje = Math.Round((vPresupuesto / vExigible) * 100, 2);
dtbBrenchDet = (DataTable)ViewState["BrenchDet"];
resultado = dtbBrenchDet.Select("CodigoAlter='" + ViewState["Codigo"].ToString() + "'").FirstOrDefault();
resultado["Porcentaje"] = vPorcentaje;
resultado["Presupuesto"] = vPresupuesto.ToString("N2");
resultado["PorcentajeValor"] = vPorcentaje;
resultado["PresupuestoValor"] = vPresupuesto;
dtbBrench = (DataTable)ViewState["BrenchCab"];
result = dtbBrench.Select("Etiqueta='" + resultado["Rango"].ToString() + "'").FirstOrDefault();
resultado["RangoInicial"] = result["RangoIni"].ToString();
resultado["RangoFinal"] = result["RangoFin"].ToString();
resultado["Orden"] = result["Orden"].ToString();
dtbBrenchDet.AcceptChanges();
ViewState["BrenchDet"] = dtbBrenchDet;
GrdvBrenchDet.DataSource = dtbBrenchDet;
GrdvBrenchDet.DataBind();
//TxtPresupuesto.Text = "";
TxtPresupuesto.Enabled = false;
ImgModificar.Enabled = false;
}
catch (Exception ex)
{
Lblerror.Text = ex.ToString();
}
}
private void FunCargaMantenimiento()
{
try
{
dtbBrenchDet.Clear();
ViewState["CodigoBRMC"] = "0";
ViewState["BrenchDet"] = dtbBrenchDet;
GrdvBrenchDet.DataSource = dtbBrenchDet;
GrdvBrenchDet.DataBind();
dtbBrench = (DataTable)ViewState["BrenchCab"];
dts = new ConsultaDatosDAO().FunConsultaDatos(116, int.Parse(DdlCedente.SelectedValue),
int.Parse(DdlCatalogo.SelectedValue), int.Parse(DdlGestores.SelectedValue), "", "", "",
Session["Conectar"].ToString());
if (dts.Tables[0].Rows.Count > 0)
{
ViewState["CodigoBRMC"] = dts.Tables[0].Rows[0]["Codigo"].ToString();
}
if (dtbBrench.Rows.Count > 0)
{
sql += "SELECT CodigoAlter = ROW_NUMBER() OVER(ORDER BY rango_dias), Codigo = ISNULL((SELECT BMD.BRMD_CODIGO FROM SoftCob_BRENCHMESDET BMD ";
sql += "INNER JOIN SoftCob_BRENCHMESCAB BMC ON BMD.BRMC_CODIGO=BMC.BRMC_CODIGO ";
sql += "WHERE BMC.brmc_cedecodigo=" + DdlCedente.SelectedValue + " AND BMC.brmc_cpcecodigo=" + DdlCatalogo.SelectedValue + " AND BMC.brmc_presupuestoanio=" + ViewState["Anio"].ToString() + " AND ";
sql += "BMC.brmc_presupuestomes=" + ViewState["Mes"].ToString() + " and BMC.brmc_gestorasignado=" + DdlGestores.SelectedValue + " and BMD.brmd_brench=rango_dias),0),";
//sql += "ROW_NUMBER() OVER(ORDER BY rango_dias)),";
sql += "Operaciones = COUNT(*),Rango = rango_dias,";
sql += "Monto = CONVERT(VARCHAR(20),CAST(SUM(ctde_totaldeuda) AS money),1),";
sql += "Exigible = CONVERT(varchar(20),CAST(SUM(ctde_valorexigible) AS money),1),";
sql += "Porcentaje = ISNULL((SELECT BMD.brmd_porcentaje FROM SoftCob_BRENCHMESDET BMD ";
sql += "INNER JOIN SoftCob_BRENCHMESCAB BMC ON BMD.BRMC_CODIGO=BMC.BRMC_CODIGO ";
sql += "WHERE BMC.brmc_cedecodigo=" + DdlCedente.SelectedValue + " AND BMC.brmc_cpcecodigo=" + DdlCatalogo.SelectedValue + " AND BMC.brmc_presupuestoanio=" + ViewState["Anio"].ToString() + " AND ";
sql += "BMC.brmc_presupuestomes=" + ViewState["Mes"].ToString() + " AND BMC.brmc_gestorasignado=" + DdlGestores.SelectedValue + " AND BMD.brmd_brench=rango_dias),'0.00'),";
sql += "Presupuesto = ISNULL((SELECT CONVERT(VARCHAR(20),CAST(BMD.brmd_presupuesto AS money),1) FROM SoftCob_BRENCHMESDET BMD INNER JOIN SoftCob_BRENCHMESCAB BMC ON BMD.BRMC_CODIGO=BMC.BRMC_CODIGO ";
sql += "WHERE BMC.brmc_cedecodigo=" + DdlCedente.SelectedValue + " AND BMC.brmc_cpcecodigo=" + DdlCatalogo.SelectedValue + " AND ";
sql += "BMC.brmc_presupuestoanio=" + ViewState["Anio"].ToString() + " AND BMC.brmc_presupuestomes=" + ViewState["Mes"].ToString() + " AND ";
sql += "BMC.brmc_gestorasignado=" + DdlGestores.SelectedValue + " AND BMD.brmd_brench=rango_dias),'0.00'),";
sql += "PorcentajeValor=0.00,";
sql += "PresupuestoValor = ISNULL((SELECT BMD.brmd_presupuesto FROM SoftCob_BRENCHMESDET BMD INNER JOIN SoftCob_BRENCHMESCAB BMC ON BMD.BRMC_CODIGO=BMC.BRMC_CODIGO ";
sql += "WHERE BMC.brmc_cedecodigo=" + DdlCedente.SelectedValue + " AND BMC.brmc_cpcecodigo=" + DdlCatalogo.SelectedValue + " AND ";
sql += "BMC.brmc_presupuestoanio=" + ViewState["Anio"].ToString() + " AND BMC.brmc_presupuestomes=" + ViewState["Mes"].ToString() + " AND ";
sql += "BMC.brmc_gestorasignado=" + DdlGestores.SelectedValue + " AND BMD.brmd_brench=rango_dias),'0.00'),";
sql += "ExigibleValor = SUM(ctde_valorexigible),MontoValor = SUM(ctde_totaldeuda),";
sql += "RangoInicial = ISNULL((SELECT brde_rangoinicial FROM SoftCob_BRENCHDET WHERE brde_etiqueta=rango_dias " +
"AND brde_auxi1=" + ViewState["codigoCPCE"].ToString() + "),0),";
sql += "RangoFinal = ISNULL((SELECT brde_rangofinal FROM SoftCob_BRENCHDET WHERE brde_etiqueta=rango_dias " +
"AND brde_auxi1=" + ViewState["codigoCPCE"].ToString() + "),0),";
sql += "Orden = ISNULL((SELECT brde_orden FROM SoftCob_BRENCHDET WHERE brde_etiqueta=rango_dias " +
"AND brde_auxi1=" + ViewState["codigoCPCE"].ToString() + "),0) ";
sql += "FROM (SELECT CASE";
dtbBrench = (DataTable)ViewState["BrenchCab"];
foreach (DataRow row in dtbBrench.Rows)
{
casos += " WHEN dias_mora BETWEEN " + row["RangoIni"].ToString() + " AND " + row["RangoFin"].ToString() +
" THEN '" + row["Etiqueta"].ToString() + "'";
}
casos += " END AS rango_dias,ctde_valorexigible,ctde_totaldeuda FROM (";
casos += "SELECT CDE.ctde_totaldeuda, CDE.ctde_valorexigible,CDE.ctde_diasmora AS dias_mora ";
casos += "FROM SoftCob_CUENTA_DEUDOR CDE INNER JOIN SoftCob_CLIENTE_DEUDOR CLI ON CDE.CLDE_CODIGO=CLI.CLDE_CODIGO ";
casos += "WHERE CLI.CPCE_CODIGO=" + ViewState["codigoCPCE"].ToString() + " AND CDE.ctde_gestorasignado=";
casos += DdlGestores.SelectedValue + " AND CDE.ctde_estado=1 AND CLI.clde_estado=1) res) tabla ";
casos += "WHERE ISNULL(rango_dias,'')!='' ";
casos += "GROUP BY rango_dias ORDER BY rango_dias";
sql = sql + casos;
dts = new ConsultaDatosDAO().FunConsultaDatos(15, 0, 0, 0, sql, "", "", Session["Conectar"].ToString());
ViewState["BrenchDet"] = dts.Tables[0];
GrdvBrenchDet.DataSource = dts;
GrdvBrenchDet.DataBind();
//dts = new ConsultaDatosDTO().funConsultaDatos(111, int.Parse(ddlCedente.SelectedValue), int.Parse(ddlCatalogo.SelectedValue),
// int.Parse(ddlGestores.SelectedValue), "", ViewState["Anio"].ToString(), ViewState["Mes"].ToString(), Session["Conectar"].ToString());
//if (dts.Tables[0].Rows.Count > 0)
//{
// ViewState["Procesado"] = "SI";
// ViewState["BrenchDet"] = dts.Tables[0];
// grdvBrenchDet.DataSource = dts;
// grdvBrenchDet.DataBind();
// btnGrabar.Enabled = false;
//}
//else
//{
// ViewState["Procesado"] = "NO";
// btnGrabar.Enabled = true;
// sql = "select Codigo = ROW_NUMBER() over(order by rango_dias),";
// sql += "Operaciones = count(*),Rango = rango_dias,";
// sql += "Monto = cast(convert(varchar(20),cast(sum(ctde_totaldeuda) as money),1) as varchar),";
// sql += "Exigible = cast(convert(varchar(20),cast(sum(ctde_valorexigible) as money),1) as varchar),";
// sql += "Porcentaje = '0.00',";
// sql += "Presupuesto = '0.00',";
// sql += "PorcentajeValor=0.00,PresupuestoValor=0.00,ExigibleValor = sum(ctde_valorexigible),MontoValor = sum(ctde_totaldeuda),";
// sql += "RangoInicial = 0,RangoFinal = 0,Orden = 0 from (select case";
// dtbBrench = (DataTable)ViewState["BrenchCab"];
// foreach (DataRow row in dtbBrench.Rows)
// {
// casos += " when dias_mora between " + row["RangoIni"].ToString() + " and " + row["RangoFin"].ToString() +
// " then '" + row["Etiqueta"].ToString() + "'";
// }
// casos += " end as rango_dias,ctde_valorexigible,ctde_totaldeuda from (";
// casos += "select CD.ctde_totaldeuda, CD.ctde_valorexigible,CD.ctde_diasmora as dias_mora ";
// casos += "from SoftCob_CUENTA_DEUDOR CD INNER JOIN SoftCob_CLIENTE_DEUDOR CL ON CD.CLDE_CODIGO=CL.CLDE_CODIGO ";
// casos += "where CL.CPCE_CODIGO=" + ViewState["codigoCPCE"].ToString() + " and CD.ctde_gestorasignado=";
// casos += ddlGestores.SelectedValue + " and CD.ctde_estado=1 and CL.clde_estado=1) res) tabla ";
// casos += "where isnull(rango_dias,'')!='' ";
// casos += "group by rango_dias order by rango_dias";
// sql = sql + casos;
// dts = new ConsultaDatosDTO().funConsultaDatos(15, 0, 0, 0, sql, "", "", Session["Conectar"].ToString());
// ViewState["BrenchDet"] = dts.Tables[0];
// grdvBrenchDet.DataSource = dts;
// grdvBrenchDet.DataBind();
//}
}
else
{
new FuncionesDAO().FunShowJSMessage("No existe Brench Creado para el Cedente..!", this);
}
}
catch (Exception ex)
{
Lblerror.Text = ex.ToString();
}
}
