Programación: septiembre 2016

.ASPX (tiene pagina maestra, pero no es necesaria)
_________________________________________________________

<%@ Page Title="Genera Grilla Standard" Language="C#" MasterPageFile="~/Site.Master"
AutoEventWireup="true" CodeBehind="GeneraGrillaStandard.aspx.cs" Inherits="LeaseOperWeb.Utils.GeneraGrillaStandard" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="HeaderContent" runat="server">
<table width="100%" border="2">
<tr>
<td>
</td>
<td align="center">
GENERADOR GRILLA STANDARD JQUERY 
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
Ingrese Columnas<br />
Separador de Columnas ";"
</td>
<td>
<asp:Button ID="BtnGenerarGrilla" runat="server" Text="Generar Grilla JQUERY" ToolTip="el separador debe ser ;"
Height="67px" Width="154px" onclick="BtnGenerarGrilla_Click1"/>
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
 
</td>
<td>
 </td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
Grilla Standard
</td>
<td>
 
<asp:TextBox ID="txtColumnasEntrada" runat="server" Height="182px"
TextMode="MultiLine" Width="574px"></asp:TextBox>
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
 
</td>
<td>
<asp:TextBox ID="txtGrillaJqueryStandard" runat="server" Height="492px" TextMode="MultiLine"
Width="726px"></asp:TextBox>
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
 
</td>
<td>
 
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
<tr>
<td>
 
</td>
<td>
 
</td>
<td>
 
</td>
<td>
 
</td>
</tr>
</table>
</asp:Content>

.CS
_________________________________________________________

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace LeaseOperWeb.Utils
{
public partial class GeneraGrillaStandard : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
txtGrillaJqueryStandard.Attributes.Add("onfocus", "this.select();");

}

protected void BtnGenerarGrilla_Click1(object sender, EventArgs e)
{
try
{
if (txtColumnasEntrada.Text.Trim() != string.Empty)
PrepararGrilla();
else
Response.Write("<script language='JavaScript'>alert('Debe indicar las columnas para generar Grilla JQuery');</script>");
}
catch (Exception ex)
{
throw ex;
}
}

private void PrepararGrilla()
{
try
{
const string quote = "\"";
var listaColumnas = txtColumnasEntrada.Text.Trim().Split(';');
var grillaEncabezado = @"var lastsel;" + Environment.NewLine +
@"function defineGrid() {" + Environment.NewLine +
@"try {" + Environment.NewLine +
@" $(" + quote + "#Grid" + quote + ").jqGrid({" + Environment.NewLine +
@"datatype:" + quote + "local" + quote +","+ Environment.NewLine +
@"colNames: [";

var grillaColumnasEncabezado = string.Empty;
var tope = listaColumnas.Length - 1;
for (int j = 0; j < listaColumnas.Length; j++)
{
if (j != tope)
grillaColumnasEncabezado = grillaColumnasEncabezado + "'" + listaColumnas[j] + "',";
else
grillaColumnasEncabezado = grillaColumnasEncabezado + "'" + listaColumnas[j] + "']," + Environment.NewLine;
}

var grillaPreColumnas = @"colModel: [";
const string columnasStandard =
@"{name: 'nomCol', index: 'nomCol', align: 'center', width: '100px', editable: false, hidden: false }";
var grillaColumnaDetalle = string.Empty;
for (int i = 0; i < listaColumnas.Length; i++)
{
listaColumnas[i] = columnasStandard.Replace("nomCol", listaColumnas[i]);
if (i != tope)
grillaColumnaDetalle = grillaColumnaDetalle + listaColumnas[i]+","+ Environment.NewLine;
else
grillaColumnaDetalle = grillaColumnaDetalle + listaColumnas[i] + @"]," + Environment.NewLine;

}

var grillaPie = @" //multiselect: true," + Environment.NewLine +
"rowNum: 150," + Environment.NewLine +
"rowList: [10, 25, 50, 100, 150, 200, 250, 300, 350]," + Environment.NewLine +
"pgbuttons: false," + Environment.NewLine +
"pager: $('#pager2')," + Environment.NewLine +
"viewrecords: true," + Environment.NewLine +
"width: 1000," + Environment.NewLine +
"shrinkToFit: false," + Environment.NewLine +
"autowidth: false," + Environment.NewLine +
"forceFit: true," + Environment.NewLine +
"height: 200," + Environment.NewLine +
"editurl: jutils.pageName()," + Environment.NewLine +
"onSelectRow: function (id, status) {" + Environment.NewLine +
"if (id && id !== lastsel) {" + Environment.NewLine +
" $('#Grid').jqGrid('restoreRow', lastsel);" + Environment.NewLine +
"}" + Environment.NewLine +
"}" + Environment.NewLine +
"});" + Environment.NewLine +
"$(" + quote + "#Grid" + quote + ").jqGrid('navGrid'," + quote + "#pager2" + quote + ", { edit: false, add: false, del: false, refresh: false, search: false });" + Environment.NewLine +
"} catch (e) {" + Environment.NewLine +
"jutils.showError(e, arguments.callee.name);" + Environment.NewLine +
"}" + Environment.NewLine +
"}";

ArmarGrilla(grillaEncabezado, grillaColumnasEncabezado, grillaPreColumnas, grillaColumnaDetalle,
grillaPie);
}
catch (Exception ex)
{
throw ex;
}
}

private void ArmarGrilla(string grillaEncabezado, string grillaColumnasEncabezado, string grillaPreColumnas, string grillaColumnaDetalle, string grillaPie)
{
txtGrillaJqueryStandard.Text = grillaEncabezado + grillaColumnasEncabezado + grillaPreColumnas +
grillaColumnaDetalle + grillaPie;

txtGrillaJqueryStandard.Focus();
}

}
}

.ASPX (representación de la grilla en una pantalla)
_________________________________________________________

<tr>

<tr>

<td>

</td>

</tr>

</table>

</div>

</div>

</td>

</td>

</tr>

--NC VALOR JC
UPDATE #ficha_det
SET valor_nota_credito = tablaAdd.campoSum
FROM #ficha_det det, (SELECT a.num_factura,a.rut_proveedor, nc.tipo_docum, SUM(ISNULL(nc.neto,0)+ISNULL(nc.exento,0)) campoSum
FROM #ficha_det a, t_nota_credito_debito nc
WHERE nc.num_factura=a.num_factura
AND nc.rut_proveedor=a.rut_proveedor AND tipo_docum=1
GROUP BY a.num_factura,a.rut_proveedor, nc.tipo_docum ) tablaAdd
WHERE det.num_factura = tablaAdd.num_factura AND det.rut_proveedor = tablaAdd.rut_proveedor
AND tablaAdd.tipo_docum = 1

Otro Ejemplo:

--dias mora
UPDATE #universo
SET dias_mora = tablaAdd.dias_mora
FROM leaseoper..t_cuotas cu , (SELECT DATEDIFF(DAY,MIN(c.fecha_vencimiento),GETDATE()) AS dias_mora, u.operacion FROM leaseoper..t_cuotas c ,#universo u
WHERE c.operacion = u.operacion
GROUP BY dias_mora, u.operacion ) tablaAdd
WHERE cu.operacion = tablaAdd.operacion AND #universo.operacion = cu.operacion AND
cu.estado IN (1,3)

Otro Ejemplo:
--total de nominas
UPDATE #nominas_universo
SET total_nomina = tablaAdd.campoSum
FROM #nominas_universo uni , (SELECT b.numero_nomina, (SUM(ISNULL(b.monto,0))) campoSum
FROM #nominas_universo a , dbo.t_req_pago b
WHERE a.numero_nomina = b.numero_nomina
GROUP BY b.numero_nomina )tablaAdd
WHERE uni.numero_nomina = tablaAdd.numero_nomina

Programación

martes, 20 de septiembre de 2016

C# - Generar Grilla Jquery Standard pasando solo columnas (separadas por ;)

viernes, 2 de septiembre de 2016

SQL - update select con columnas agregadas (suma)