public string CreateDataTableHTML(int show = 25, int pg = 1, string search = "", string orderby = "", string sort = "", string filterFecha = "", string sede = "")
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
StringBuilder sb = new StringBuilder(10000000);
RecalculoAsimilados recalculo = new RecalculoAsimilados();
sb.Append("<style> .datagrid table { border-collapse: collapse; text-align: left; width: 100%; } .datagrid { background: #fff; overflow: auto; border: 1px solid #AAAAAA; -webkit-border-radius: 6px; -moz-border-radius: 6px; border-radius: 6px; }.datagrid table td, .datagrid table th { padding: 1px 10px; }.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #FF8520), color-stop(1, #FF8520) );background:-moz-linear-gradient( center top, #FF8520 5%, #FF8520 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#FF8520', endColorstr='#FF8520');background-color:#FF8520; color:#FFFFFF; font-size: 15px; font-weight: bold; border-left: 1px solid #FFFFFF; } .datagrid table thead th:first-child { border: none; }.datagrid table tbody td { color: #000000; border-left: 1px solid #FF8520;font-size: 12px;font-weight: normal; border-top: 1px solid #FF8520; }.datagrid table tbody .alt td { background: #FFB982; color: #00496B; }.datagrid table tbody td:first-child { border-left: none; }.datagrid table tbody tr:last-child td { border-bottom: none; }.datagrid table tfoot td div { border-top: 1px solid #AAAAAA;background: #E1EEF4;} .datagrid table tfoot td { padding: 0; font-size: 12px } .datagrid table tfoot td div{ padding: 2px; }.datagrid table tfoot td ul { margin: 0; padding:0; list-style: none; text-align: right; }.datagrid table tfoot li { display: inline; }.datagrid table tfoot li a { text-decoration: none; display: inline-block; padding: 2px 8px; margin: 1px;color: #FFFFFF;border: 1px solid #006699;-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#006699', endColorstr='#00557F');background-color:#006699; }.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none;border-color: #006699; color: #FFFFFF; background: none; background-color:#00557F;} iv.dhtmlx_window_active, div.dhx_modal_cover_dv { position: fixed !important; }</style>");
sb.Append("<div class=\"datagrid\">");
string fullMonthName;
string condicionsql = "";
string sql2 = "SELECT * FROM QNominaFechaSum ";
string sql;
condicionsql = "where convert(varchar(12),fecha_de_pago,103)='" + filterFecha + "'" + " and sede='" + sede + "'";
sql2 = sql2 + condicionsql;
ResultSet res2 = db.getTable(sql2);
if (!res2.HasRows)
{
sb.Append("<div class=\"jumbotron m-b-0 text-center\" style=\"width: 100 %; \"><h1>No existen registros para la fecha " + filterFecha + "</h1></div>");
}
else
{
sb.Append("<table id=\"t1\" border=1>");
sb.Append("<thead><tr><td></td><td></td><td></td><td></td><th><b>" + filterFecha + "</b></th><td></td><td></td><td></td><td></td><td></td></tr>");
sb.Append("<tr><th>Tipo de Transferencia</th> <th>Tipo de Pago</th><th>Fecha de Pago</th><th>ID empleado</th><th>Empleado</th><th>Suma de Importe</th><th>Suma de ImpIVA</th><th>Suma de ImpIVARet</th><th>Suma de ImpISRRet</th><th>Suma Bancos</th></tr> </thead>");
/* _____________________________ bandera recalculo ___________________________ */
List <string> listIDSIU = new List <string>();
while (res2.Next())
{
sql = "SELECT * FROM QNominaMesAnio " + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
if (!listIDSIU.Contains(res.Get("id")))
{
listIDSIU.Add(res.Get("id"));
}
}
}
foreach (string IDSIU in listIDSIU)
{
recalculo.bandera_recalculo(IDSIU, sede);
}
/* ________________________________________________________ */
res2.ReStart();
while (res2.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_transferencia"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_pago"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td></td><td></td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s = double.Parse(res2.Get("suma_importe"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_isr_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_bancos"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<tbody>");
sql = "SELECT * FROM QNominaMesAnio ";
sql = sql + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("fecha_de_pago").Substring(0, 10));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\@'>");
sb.Append("​");
sb.Append(res.Get("id"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("empleado"));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s0 = double.Parse(res.Get("importe"));
sb.Append(s0.ToString("C", CultureInfo.CreateSpecificCulture("es-US")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s1 = double.Parse(res.Get("importe_iva"));
sb.Append(s1.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s2 = double.Parse(res.Get("importe_iva_retenido"));
sb.Append(s2.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s3 = double.Parse(res.Get("importe_isr_retenido"));
sb.Append(s3.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s4 = double.Parse(res.Get("bancos"));
sb.Append(s4.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("</tr>");
}
}
sb.Append("</tbody>");
sb.Append("<table>");
sb.Append("</div>");
}
return(sb.ToString());
}
public string CreateDataTableHTML(int show = 25, int pg = 1, string search = "", string orderby = "", string sort = "", string filterMes = "", string filterAnio = "", string sede = "")
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
StringBuilder sb = new StringBuilder(10000000);
RecalculoAsimilados recalculo = new RecalculoAsimilados();
sb.Append("<style> .datagrid table { border-collapse: collapse; text-align: left; width: 100%; } .datagrid { background: #fff; overflow: auto; border: 1px solid #AAAAAA; -webkit-border-radius: 6px; -moz-border-radius: 6px; border-radius: 6px; }.datagrid table td, .datagrid table th { padding: 1px 10px; }.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #FF8520), color-stop(1, #FF8520) );background:-moz-linear-gradient( center top, #FF8520 5%, #FF8520 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#FF8520', endColorstr='#FF8520');background-color:#FF8520; color:#FFFFFF; font-size: 15px; font-weight: bold; border-left: 1px solid #FFFFFF; } .datagrid table thead th:first-child { border: none; }.datagrid table tbody td { color: #000000; border-left: 1px solid #FF8520;font-size: 12px;font-weight: normal; border-top: 1px solid #FF8520; }.datagrid table tbody .alt td { background: #FFB982; color: #00496B; }.datagrid table tbody td:first-child { border-left: none; }.datagrid table tbody tr:last-child td { border-bottom: none; }.datagrid table tfoot td div { border-top: 1px solid #AAAAAA;background: #E1EEF4;} .datagrid table tfoot td { padding: 0; font-size: 12px } .datagrid table tfoot td div{ padding: 2px; }.datagrid table tfoot td ul { margin: 0; padding:0; list-style: none; text-align: right; }.datagrid table tfoot li { display: inline; }.datagrid table tfoot li a { text-decoration: none; display: inline-block; padding: 2px 8px; margin: 1px;color: #FFFFFF;border: 1px solid #006699;-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#006699', endColorstr='#00557F');background-color:#006699; }.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none;border-color: #006699; color: #FFFFFF; background: none; background-color:#00557F;} iv.dhtmlx_window_active, div.dhx_modal_cover_dv { position: fixed !important; }</style>");
sb.Append("<div class=\"datagrid\">");
if (!filterMes.Equals(""))
{
string fullMonthName;
string condicionsql = "";
string sql2 = "SELECT * FROM QNominaMesAnioSumFD";
string sql;
condicionsql = " WHERE sede = '" + sede + "'";
if (!filterAnio.Equals("") && !filterMes.Equals(""))
{
condicionsql += " AND anio = '" + filterAnio + "' and mes='" + filterMes + "'";
}
sql2 = sql2 + condicionsql;
ResultSet res2 = db.getTable(sql2);
fullMonthName = new DateTime(2015, Int32.Parse(filterMes), 1).ToString("MMMM", CultureInfo.CreateSpecificCulture("es"));
sb.Append("<table id=\"t1\" border=1>");
sb.Append("<thead><tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><th><b>" + fullMonthName.ToUpper() + " " + filterAnio + "</b></th><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>");
sb.Append("<tr><th>Tipo de Transferencia</th>")
.Append("<th>Tipo de Pago</th>")
.Append("<th>Número de Cuenta</th>")
.Append("<th>Cuenta IVA</th>")
.Append("<th>Cuenta Retención IVA</th>")
.Append("<th>Cuenta Retención ISR</th>")
.Append("<th>Carrera</th>")
.Append("<th>Fecha de Depósito</th>")
.Append("<th>Folio</th>")
.Append("<th>ID empleado</th>")
.Append("<th>Empleado</th>")
.Append("<th>RFC Persona Física</th>")
.Append("<th>CURP</th>")
.Append("<th>Facturar con</th>")
.Append("<th>RFC Persona Moral</th>")
.Append("<th>Persona Moral</th>")
.Append("<th>Suma de Importe</th>")
.Append("<th>Suma de ImpIVA</th>")
.Append("<th>Suma de ImpIVARet</th>")
.Append("<th>Suma de ImpISRRet</th>")
.Append("<th>Suma Bancos</th>")
.Append("</tr> </thead>");
/* _____________________________ bandera recalculo ___________________________ */
List <string> listIDSIU = new List <string>();
while (res2.Next())
{
sql = "SELECT * FROM QReportePolizaFD " + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
if (!listIDSIU.Contains(res.Get("id")))
{
listIDSIU.Add(res.Get("id"));
}
}
}
foreach (string IDSIU in listIDSIU)
{
recalculo.bandera_recalculo(IDSIU, sede);
}
/* ________________________________________________________ */
res2.ReStart();
while (res2.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_transferencia"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_pago"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td></td><td></td>");
sb.Append("<td></td><td></td>");
sb.Append("<td></td>");//nuevo
sb.Append("<td></td>");
sb.Append("<td></td>");
sb.Append("<td></td>");
sb.Append("<td></td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s = double.Parse(res2.Get("suma_importe"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_isr_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_bancos"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<tbody>");
sql = "SELECT * FROM QReportePolizaFD ";
/*sql = "SELECT pfd1.[id_estadodecuenta],[tipo_transferencia],[cveTipo_transferencia],[tipo_pago] "
+ " ,stuff( "
+ " (select ', ' + pfd2.[numero_de_cuenta] "
+ " from QReportePolizaFD pfd2 "
+ " where pfd1.id_estadodecuenta = pfd2.id_estadodecuenta "
+ " for XML PATH('') "
+ " ) "
+ " ,1,1,'') as numero_de_cuenta "
+ " ,stuff( "
+ " (select ', ' + pfd2.[cuenta_iva] "
+ " from QReportePolizaFD pfd2 "
+ " where pfd1.id_estadodecuenta = pfd2.id_estadodecuenta "
+ " for XML PATH('') "
+ " ) "
+ " ,1,1,'') as cuenta_iva "
+ " ,stuff( "
+ " (select ', ' + pfd2.[cuenta_ret_iva] "
+ " from QReportePolizaFD pfd2 "
+ " where pfd1.id_estadodecuenta = pfd2.id_estadodecuenta "
+ " for XML PATH('') "
+ " ) "
+ " ,1,1,'') as cuenta_ret_iva "
+ " ,stuff( "
+ " (select ', ' + pfd2.[cuenta_ret_isr] "
+ " from QReportePolizaFD pfd2 "
+ " where pfd1.id_estadodecuenta = pfd2.id_estadodecuenta "
+ " for XML PATH('') "
+ " ) "
+ " ,1,1,'') as cuenta_ret_isr "
+ " ,stuff( "
+ " (select ', ' + pfd2.[carrera] "
+ " from QReportePolizaFD pfd2 "
+ " where pfd1.id_estadodecuenta = pfd2.id_estadodecuenta "
+ " for XML PATH('') "
+ " ) "
+ " ,1,1,'') as carrera "
+ " ,[fecha_de_deposito],[FOLIO],[anio],[mes],[id],[empleado],[importe],[importe_iva],[importe_iva_retenido],[importe_isr_retenido] "
+ " ,[bancos],[RFC],[CURP],[DATOSFISCALES],[RZ_RFC],[PERSONA_MORAL],[sede] "
+ " FROM[dbo].[QReportePolizaFD] pfd1 ";
+ sql = sql + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
+ sql = sql + " group by pfd1.[id_estadodecuenta],[tipo_transferencia],[cveTipo_transferencia],[tipo_pago] "
+ " ,[fecha_de_deposito],[FOLIO],[anio],[mes],[id],[empleado],[importe],[importe_iva],[importe_iva_retenido],[importe_isr_retenido] "
+ " ,[bancos],[RFC],[CURP],[DATOSFISCALES],[RZ_RFC],[PERSONA_MORAL],[sede]";*/
sql = sql + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("numero_de_cuenta"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("cuenta_iva"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("cuenta_ret_iva"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("cuenta_ret_isr"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("carrera"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("fecha_de_deposito").Substring(0, 10));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("FOLIO"));
sb.Append("</td>");
sb.Append("<td class='text'>");
sb.Append("​");
sb.Append(res.Get("id"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("empleado"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("RFC"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("CURP"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("DATOSFISCALES"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("RZ_RFC"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("PERSONA_MORAL"));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s0 = decimal.Parse(res.Get("importe"));
sb.Append(s0.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s1 = decimal.Parse(res.Get("importe_iva"));
sb.Append(s1.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s2 = decimal.Parse(res.Get("importe_iva_retenido"));
sb.Append(s2.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s3 = decimal.Parse(res.Get("importe_isr_retenido"));
sb.Append(s3.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s4 = decimal.Parse(res.Get("bancos"));
sb.Append(s4.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("</td>");
sb.Append("</tr>");
}
}
sb.Append("</tbody>");
sb.Append("</table>");
}
else
{
sb.Append("<table id=\"t1\" border=1>");
sb.Append("<tr>");
if (!CreateDateMes("1", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("1", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("2", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("2", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("3", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("3", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("4", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("4", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("5", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("5", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("6", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("6", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("7", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("7", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("8", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("8", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("9", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("9", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("10", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("10", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("11", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("11", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("12", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("12", filterAnio, sede));
sb.Append("</td>");
}
sb.Append("</tr>");
sb.Append("</table>");
}
sb.Append("</div>");
return(sb.ToString());
}
public string CreateDataTableHTML(int show = 25, int pg = 1, string search = "", string orderby = "", string sort = "", string filterMes = "", string filterAnio = "", string sede = "", string tipodocente = "", string pagosdeposito = "")
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
StringBuilder sb = new StringBuilder(10000000);
RecalculoAsimilados recalculo = new RecalculoAsimilados();
sb.Append("<style> .datagrid table { border-collapse: collapse; text-align: left; width: 100%; } .datagrid { background: #fff; overflow: auto; border: 1px solid #AAAAAA; -webkit-border-radius: 6px; -moz-border-radius: 6px; border-radius: 6px; }.datagrid table td, .datagrid table th { padding: 1px 10px; }.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #FF8520), color-stop(1, #FF8520) );background:-moz-linear-gradient( center top, #FF8520 5%, #FF8520 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#FF8520', endColorstr='#FF8520');background-color:#FF8520; color:#FFFFFF; font-size: 15px; font-weight: bold; border-left: 1px solid #FFFFFF; } .datagrid table thead th:first-child { border: none; }.datagrid table tbody td { color: #000000; border-left: 1px solid #FF8520;font-size: 12px;font-weight: normal; border-top: 1px solid #FF8520; }.datagrid table tbody .alt td { background: #FFB982; color: #00496B; }.datagrid table tbody td:first-child { border-left: none; }.datagrid table tbody tr:last-child td { border-bottom: none; }.datagrid table tfoot td div { border-top: 1px solid #AAAAAA;background: #E1EEF4;} .datagrid table tfoot td { padding: 0; font-size: 12px } .datagrid table tfoot td div{ padding: 2px; }.datagrid table tfoot td ul { margin: 0; padding:0; list-style: none; text-align: right; }.datagrid table tfoot li { display: inline; }.datagrid table tfoot li a { text-decoration: none; display: inline-block; padding: 2px 8px; margin: 1px;color: #FFFFFF;border: 1px solid #006699;-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#006699', endColorstr='#00557F');background-color:#006699; }.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none;border-color: #006699; color: #FFFFFF; background: none; background-color:#00557F;} iv.dhtmlx_window_active, div.dhx_modal_cover_dv { position: fixed !important; }</style>");
sb.Append("<div class=\"datagrid\">");
if (!filterMes.Equals(""))
{
string fullMonthName;
string condicionsql = "";
string condicionsql2 = "";
string sqlselect = "SELECT * FROM QReportePolizaCC";
string sql2 = "SELECT * FROM QNominaMesAnioSumCC";
string sql;
condicionsql = " WHERE sede = '" + sede + "'";
if (!filterAnio.Equals("") && !filterMes.Equals(""))
{
condicionsql += " AND anio = '" + filterAnio + "' and mes='" + filterMes + "'";
}
if ((tipodocente != "" && tipodocente != "null") && (tipodocente != "T"))
{
condicionsql2 = " AND CVE_TIPOFACTURA = '" + tipodocente + "'";
}
if (pagosdeposito != "" && pagosdeposito != "null")
{
if (pagosdeposito == "PD")//PD pagos depositados o pagados(con fecha de deposito)
{
sql2 = "SELECT * FROM QNominaMesAnioSumCCDeposito";
sqlselect = "SELECT * FROM QReportePolizaCCDeposito";
//condicionsql2 = " AND (FECHADEPOSITO IS NOT NULL AND FECHADEPOSITO <> '' )";
}
else if (pagosdeposito == "PP")//PP pagos pendientes o no depositados (sin fecha de deposito)
{
sql2 = "SELECT * FROM QNominaMesAnioSumCCSINDeposito";
sqlselect = "SELECT * FROM QReportePolizaCCSINDeposito";
//condicionsql2 = " AND (FECHADEPOSITO IS NULL OR FECHADEPOSITO = '' )";
}
else
{
sql2 = "SELECT * FROM QNominaMesAnioSumCC";
sqlselect = "SELECT * FROM QReportePolizaCC";
}//T todos
}
sql2 = sql2 + condicionsql + condicionsql2;
ResultSet res2 = db.getTable(sql2);
fullMonthName = new DateTime(2015, Int32.Parse(filterMes), 1).ToString("MMMM", CultureInfo.CreateSpecificCulture("es"));
sb.Append("<table id=\"t1\" border=1>");
sb.Append("<thead><tr><td></td><td></td><td></td><td></td><th><b>" + fullMonthName.ToUpper() + " " + filterAnio + "</b></th><td></td><td></td><td></td><td></td></tr>");//<td></td><td></td><td></td><td></td>
sb.Append("<tr><th>Tipo de Transferencia</th>")
.Append("<th>Tipo de Pago</th>")
.Append("<th>Número de Cuenta</th>")
.Append("<th>Descripción CC</th>")
.Append("<th>Suma de Importe</th>")
.Append("<th>Suma de ImpIVA</th>")
.Append("<th>Suma de ImpIVARet</th>")
.Append("<th>Suma de ImpISRRet</th>")
.Append("<th>Suma Bancos</th>")
.Append("</tr> </thead>");
/* _____________________________ bandera recalculo ___________________________ */
List <string> listIDSIU = new List <string>();
while (res2.Next())
{
sql = sqlselect + " " + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
if (!listIDSIU.Contains(res.Get("id")))
{
listIDSIU.Add(res.Get("id"));
}
}
}
foreach (string IDSIU in listIDSIU)
{
recalculo.bandera_recalculo(IDSIU, sede);
}
/* ________________________________________________________ */
res2.ReStart();
while (res2.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_transferencia"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res2.Get("tipo_pago"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");//NUM CUENTA
sb.Append("<td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
double s = double.Parse(res2.Get("suma_importe"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_iva_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_importe_isr_retenido"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
s = double.Parse(res2.Get("suma_bancos"));
sb.Append(s.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<tbody>");
sql = sqlselect + condicionsql + " and tipo_transferencia='" + res2.Get("tipo_transferencia") + "' and tipo_pago='" + res2.Get("tipo_pago") + "'";
ResultSet res = db.getTable(sql);
while (res.Next())
{
sb.Append("<tr>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("numero_de_cuenta"));
sb.Append("</td>");
sb.Append("<td>");
sb.Append(res.Get("CENTRODECOSTOS"));//
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s0 = decimal.Parse(res.Get("importe"));
sb.Append(s0.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s1 = decimal.Parse(res.Get("importe_iva"));
sb.Append(s1.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s2 = decimal.Parse(res.Get("importe_iva_retenido"));
sb.Append(s2.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s3 = decimal.Parse(res.Get("importe_isr_retenido"));
sb.Append(s3.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("<td style='mso-number-format:\\0022$\\0022\\#\\,\\#\\#0\\.00'>");
decimal s4 = decimal.Parse(res.Get("bancos"));
sb.Append(s4.ToString("C", CultureInfo.CreateSpecificCulture("es-MX")));
sb.Append("</td>");
sb.Append("</td>");
sb.Append("</tr>");
}
}
sb.Append("</tbody>");
sb.Append("</table>");
}
else
{
sb.Append("<table id=\"t1\" border=1>");
sb.Append("<tr>");
if (!CreateDateMes("1", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("1", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("2", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("2", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("3", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("3", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("4", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("4", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("5", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("5", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("6", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("6", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("7", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("7", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("8", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("8", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("9", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("9", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("10", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("10", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("11", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("11", filterAnio, sede));
sb.Append("</td>");
}
if (!CreateDateMes("12", filterAnio, sede).Equals(""))
{
sb.Append("<td valign=\"top\">");
sb.Append(CreateDateMes("12", filterAnio, sede));
sb.Append("</td>");
}
sb.Append("</tr>");
sb.Append("</table>");
}
sb.Append("</div>");
return(sb.ToString());
}
