public HttpResponseMessage getReporteDeFacturasMensual() { if (!utilidades.validar_token(Request)) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } IEnumerable <string> headerValues_id_usuario = Request.Headers.GetValues("id_usuario"); string id_usuario = headerValues_id_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_id_tipo_de_usuario = Request.Headers.GetValues("id_tipo_de_usuario"); string id_tipo_de_usuario = headerValues_id_tipo_de_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_mes = Request.Headers.GetValues("mes"); string mes_string = headerValues_mes.FirstOrDefault().ToString(); if (id_tipo_de_usuario != "1") { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } try { //Hacemos el split del mes. string[] arreglo = mes_string.ToString().Replace("'", "''").Split('-'); int año = int.Parse(arreglo[0]); int mes = int.Parse(arreglo[1]); DateTime primer_dia = new DateTime(año, mes, 1); DateTime ultimo_dia = primer_dia.AddMonths(1).AddDays(-1); string select_query = string.Format("select * from (select " + "a.id " + ", a.folio as folio " + ", date_format(a.fecha, '%d/%m/%Y') as fecha_de_emision " + ", a.concepto as concepto " + ", a.importe as importe " + ", a.iva as iva " + ", a.total as total " + ", c.rfc as rfc " + ", c.razon_social as cliente " + ", b.nombre as estado_de_factura " + ", a.estado as estado " + "from ft_facturas a " + "left join cf_estados_de_factura b on a.id_estado_de_factura=b.id " + " left join lu_clientes c on a.id_cliente=c.id " + "where a.fecha >= date_format('{0} 00:00:00', '%Y-%m-%d %H:%i:%s') " + "and a.fecha <= date_format('{1} 23:59:59', '%Y-%m-%d %H:%i:%s') " + "union all " + "select " + "a.id " + ", a.folio as folio " + ", date_format(a.fecha, '%d/%m/%Y') as fecha_de_emision " + ", a.concepto as concepto " + ", a.importe as importe " + ", a.iva as iva " + ", a.total as total " + ", c.rfc as rfc " + ", c.razon_social as cliente " + ", b.nombre as estado_de_factura " + ", a.estado as estado " + " from ft_facturas a " + " left join cf_estados_de_factura b on a.id_estado_de_factura=b.id " + " left join lu_clientes c on a.id_cliente=c.id " + "where a.id_estado_de_factura=1 " + "and a.fecha <= date_format('{1} 23:59:59', '%Y-%m-%d %H:%i:%s')) a " + "where a.estado=1 " + "group by a.id " + "order by a.id; " , primer_dia.ToString("yyyy-MM-dd") , ultimo_dia.ToString("yyyy-MM-dd")); DataTable dt = Database.runSelectQuery(select_query); if (dt == null) { dt = new DataTable(); } if (dt.Columns.Contains("id")) { dt.Columns.Remove("id"); } if (dt.Columns.Contains("estado")) { dt.Columns.Remove("estado"); } IWorkbook workbook = GenerateExcelFile(dt); var fileName = "Reporte_de_facturas_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //save the file to server temp folder string fullPath = Path.Combine(System.Web.Hosting.HostingEnvironment.MapPath("~/temp"), fileName); using (var exportData = new MemoryStream()) { //I don't show the detail how to create the Excel, this is not the point of this article, //I just use the NPOI for Excel handler FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Close(); } //Convirtiendo el archivo var dataBytes = File.ReadAllBytes(fullPath); //adding bytes to memory stream var dataStream = new MemoryStream(dataBytes); Descarga descarga = new Descarga(dataStream, Request, fileName); return(descarga.generar_respuesta()); //Comenzamos la descarga. } catch (Exception ex) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, ex.ToString()); return(response); } }
public HttpResponseMessage getComplementoDePago() { try { if (!utilidades.validar_token(Request)) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } IEnumerable <string> headerValues_id_usuario = Request.Headers.GetValues("id_usuario"); string id_usuario = headerValues_id_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_id_tipo_de_usuario = Request.Headers.GetValues("id_tipo_de_usuario"); string id_tipo_de_usuario = headerValues_id_tipo_de_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_id_cliente = Request.Headers.GetValues("id_cliente"); string id_cliente = headerValues_id_cliente.FirstOrDefault().ToString(); IEnumerable <string> headerValues_fecha_inicial = Request.Headers.GetValues("fecha_inicial"); string fecha_inicial_string = headerValues_fecha_inicial.FirstOrDefault().ToString(); IEnumerable <string> headerValues_fecha_final = Request.Headers.GetValues("fecha_final"); string fecha_final_string = headerValues_fecha_final.FirstOrDefault().ToString(); if (id_tipo_de_usuario != "1") { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } //Hacemos el split del mes. string[] arreglo = fecha_inicial_string.ToString().Replace("'", "''").Split('-'); int año = int.Parse(arreglo[0]); int mes = int.Parse(arreglo[1]); int dia = int.Parse(arreglo[2]); string[] arreglo_final = fecha_final_string.ToString().Replace("'", "''").Split('-'); int año_final = int.Parse(arreglo_final[0]); int mes_final = int.Parse(arreglo_final[1]); int dia_final = int.Parse(arreglo_final[2]); DateTime fecha_inicial = new DateTime(año, mes, dia); DateTime fecha_final = new DateTime(año_final, mes_final, dia_final); /* * "select " + * "b.folio as folio " + * ", b.fecha as fecha_de_factura " + * ", b.concepto as concepto " + * ", b.total as importe_de_factura " + * ", a.fecha as fecha_de_pago " + * ", a.importe as importe_de_pago " + * ", c.nombre as banco " + * ", round(ceiling((b.total - a.importe)*100)*1.0/100, 2) as saldo " + * "from ft_pagos a " + * "left join ft_facturas b on b.id=a.id_factura " + * "left join cf_bancos c on c.id=a.id_banco " + * "where a.fecha>=date_format('{0} 00:00:00', '%Y-%m-%d %H:%i:%s') " + * "and a.fecha<=date_format('{1} 23:59:59', '%Y-%m-%d %H:%i:%s') " + * "and a.estado=1 " + * */ string select_query = string.Format("select a.folio as `Folio` " + ", date_format(a.fecha , '%d/%m/%Y') as `Fecha de emisión de factura` " + ", a.concepto as `Concepto` " + ", a.total as `Importe de factura` " + ", date_format(b.fecha , '%d/%m/%Y') as `Fecha de pago` " + ", b.importe as `Importe de pago` " + ", c.nombre as `Banco` " + ", round(ceiling((a.total - b.importe)*100)*1.0/100 , 2) as `Saldo` " + "from ft_facturas a " + "left join ft_pagos b on (a.id=b.id_factura and b.estado=1)" + "left join cf_bancos c on c.id=b.id_banco " + "where a.fecha>=date_format('{0} 00:00:00', '%Y-%m-%d %H:%i:%s') " + "and a.fecha<=date_format('{1} 23:59:59', '%Y-%m-%d %H:%i:%s') " + "and a.estado=1 " + "and a.id_cliente='{2}'; " , fecha_inicial.ToString("yyyy-MM-dd") , fecha_final.ToString("yyyy-MM-dd") , id_cliente); DataTable dt = Database.runSelectQuery(select_query); if (dt == null) { dt = new DataTable(); } if (dt.Columns.Contains("id")) { dt.Columns.Remove("id"); } //Obtenemos el nombre del cliente string query_cliente = string.Format("Select razon_social from lu_clientes where id='{0}';", id_cliente); DataTable cliente = Database.runSelectQuery(query_cliente); string titulo_excel = string.Format("Complemento de Pago de {0} del {1} al {2}" , cliente.Rows[0]["razon_social"].ToString() , fecha_inicial.ToString("dd/MM/yyyy") , fecha_final.ToString("dd/MM/yyyy")); IWorkbook workbook = GenerateExcelFileWithTitle(dt, titulo_excel); var fileName = "Complemento_de_pago" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //save the file to server temp folder string fullPath = Path.Combine(System.Web.Hosting.HostingEnvironment.MapPath("~/temp"), fileName); using (var exportData = new MemoryStream()) { //I don't show the detail how to create the Excel, this is not the point of this article, //I just use the NPOI for Excel handler FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Close(); } //Convirtiendo el archivo var dataBytes = File.ReadAllBytes(fullPath); //adding bytes to memory stream var dataStream = new MemoryStream(dataBytes); Descarga descarga = new Descarga(dataStream, Request, fileName); return(descarga.generar_respuesta()); //Comenzamos la descarga. } catch (Exception ex) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, ex.ToString()); return(response); } }
public HttpResponseMessage getEtiquetas() { if (!utilidades.validar_token(Request)) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } IEnumerable <string> headerValues_id_usuario = Request.Headers.GetValues("id_usuario"); string id_usuario = headerValues_id_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_id_tipo_de_usuario = Request.Headers.GetValues("id_tipo_de_usuario"); string id_tipo_de_usuario = headerValues_id_tipo_de_usuario.FirstOrDefault().ToString(); if (id_tipo_de_usuario != "1") { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } string select_query = string.Format("SELECT calibre, " + "color_e as Color, " + "cliente as Cliente, " + "clave_producto as ClaveProducto, " + "turno as Turno, " + "fecha_de_impresion as FechaImpresion, " + "embobinadora as Embobinadora, " + "etiquetas as Etiquetas, " + "carrete_inicial as CarreteInicial, " + "carrete_final as CarreteFinal, " + "linea as Linea, " + "numero_envio as Envio, " + "hilos as Hilos " + "FROM ft_etiquetas " + "where estado=1 " + "order by embobinadora asc; "); DataTable dt = Database.runSelectQuery(select_query); IWorkbook workbook = GenerateExcelFile(dt); var fileName = "exportacion_etiquetas_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //save the file to server temp folder string fullPath = Path.Combine(System.Web.Hosting.HostingEnvironment.MapPath("~/temp"), fileName); using (var exportData = new MemoryStream()) { //I don't show the detail how to create the Excel, this is not the point of this article, //I just use the NPOI for Excel handler FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Close(); } //Convirtiendo el archivo var dataBytes = File.ReadAllBytes(fullPath); //adding bytes to memory stream var dataStream = new MemoryStream(dataBytes); Descarga descarga = new Descarga(dataStream, Request, fileName); return(descarga.generar_respuesta()); //Comenzamos la descarga. //return descarga; }
// GET api/clientesdescargas public HttpResponseMessage getReporteDeClientes() { if (!utilidades.validar_token(Request)) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } IEnumerable <string> headerValues_id_usuario = Request.Headers.GetValues("id_usuario"); string id_usuario = headerValues_id_usuario.FirstOrDefault().ToString(); IEnumerable <string> headerValues_id_tipo_de_usuario = Request.Headers.GetValues("id_tipo_de_usuario"); string id_tipo_de_usuario = headerValues_id_tipo_de_usuario.FirstOrDefault().ToString(); if (id_tipo_de_usuario != "1") { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, "incorrecto"); return(response); } string select_query = string.Format("select " + "a.razon_social as Cliente " + ", (CASE " + "when sum(b.total) is null then 0 " + "else sum(b.total) " + "end) as Facturado " + ", (CASE " + "when sum(c.importe) is null then 0 " + "else sum(c.importe) " + "end) as Pagado " + "from lu_clientes a " + "LEFT JOIN ft_facturas b on (a.id=b.id_cliente and b.estado=1) " + "LEFT JOIN ft_pagos c on (c.id_factura=b.id and c.estado=1) " + "where a.estado=1 " + "group by a.id; "); DataTable dt = Database.runSelectQuery(select_query); IWorkbook workbook = GenerateExcelFile(dt); var fileName = "Reporte_de_clientes" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //save the file to server temp folder string fullPath = Path.Combine(System.Web.Hosting.HostingEnvironment.MapPath("~/temp"), fileName); using (var exportData = new MemoryStream()) { //I don't show the detail how to create the Excel, this is not the point of this article, //I just use the NPOI for Excel handler FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Close(); } //Convirtiendo el archivo var dataBytes = File.ReadAllBytes(fullPath); //adding bytes to memory stream var dataStream = new MemoryStream(dataBytes); Descarga descarga = new Descarga(dataStream, Request, fileName); return(descarga.generar_respuesta()); //Comenzamos la descarga. }