public JsonResult getGridTransaction(string sEcho, int iDisplayStart, int iDisplayLength, string sSortDir_0, int iSortCol_0) { var msgTotalTransactions = new Dominio.Mensagens.Filtro.TotalTransactions(); msgTotalTransactions.iDisplayStart = iDisplayStart; msgTotalTransactions.iDisplayLength = iDisplayLength; msgTotalTransactions.sSortDir_0 = "desc";// sSortDir_0; msgTotalTransactions.iSortCol_0 = iSortCol_0; msgTotalTransactions.Report_Type = Convert.ToInt32(Request["cmbReport_Type"]); msgTotalTransactions.dtInicio = Convert.ToString(Request["txtDtInicio"]); msgTotalTransactions.dtFim = Convert.ToString(Request["txtDtFim"]); msgTotalTransactions.txtNome = Convert.ToString(Request["txtNome"]); msgTotalTransactions.ID_REPORT_TYPE = (int)TabReport_Type.Bank; var displayedCompanies = Fachada.Repositorio.TOTAL_TRANSACTIONS.GetAllTotalTransactionsByBank(msgTotalTransactions); foreach (var item in displayedCompanies.Instances) { var msgEnvelope = Fachada.Repositorio.MONEY_COUNT.GetMoney_CountEnvelopeByIDCalc(item.IDLastTotalCalc, (int)TabCalc_Type.Envelope); if (msgEnvelope != null && msgEnvelope.Instance != null) { item.CashierLastReturn = item.TotalFinal; item.BankEnvelope = msgEnvelope.Instance.Total; item.TotalFinal = item.TotalTransactions + item.LastTotalCashier; } } var result = (from c in displayedCompanies.Instances select new[] { c.ID.ToString(), c.DT_Reg.ToString("dd/MM/yyyy HH:MM"), c.LogLogin, c.TotalTransactions.ToString("C2"), c.LastTotalCashier.ToString("C2"), c.TotalFinal.ToString("C2"), c.CashierLastReturn.ToString("C2"), c.BankEnvelope.ToString("C2") //c.TotalCalc.ToString("C2"), //c.DifferenceFinalCalc.ToString("C2"), }).Take(iDisplayLength); return(Json(new { sEcho = sEcho, // Variável Padraão do plugin iTotalRecords = 50, // Deve realizar outra pesquisa no Banco de Daods sem Filtro de pesquisa e mostar a quantidade de registros no Banco nessa variável "iTotalRecords" no exemplo esta 1000 fixo mas deve ser a qtde total de registro que existe na tabela iTotalDisplayRecords = displayedCompanies.Code, //Total de Registro com filtro Aplicado aaData = result // Lista que será exibida do grid }, JsonRequestBehavior.AllowGet)); }
public JsonResult getGridTransaction(string sEcho, int iDisplayStart, int iDisplayLength, string sSortDir_0, int iSortCol_0) { var msgTotalTransactions = new Dominio.Mensagens.Filtro.TotalTransactions(); msgTotalTransactions.iDisplayStart = iDisplayStart; msgTotalTransactions.iDisplayLength = iDisplayLength; msgTotalTransactions.sSortDir_0 = "desc";// sSortDir_0; msgTotalTransactions.iSortCol_0 = iSortCol_0; msgTotalTransactions.Report_Type = Convert.ToInt32(Request["cmbReport_Type"]); msgTotalTransactions.dtInicio = Convert.ToString(Request["txtDtInicio"]); msgTotalTransactions.dtFim = Convert.ToString(Request["txtDtFim"]); msgTotalTransactions.txtNome = Convert.ToString(Request["txtNome"]); msgTotalTransactions.ID_REPORT_TYPE = (int)TabReport_Type.Bank; var displayedCompanies = Fachada.Repositorio.TOTAL_TRANSACTIONS.GetAllTotalTransactions(msgTotalTransactions); var result = (from c in displayedCompanies.Instances select new[] { c.ID.ToString(), c.DT_Reg.ToString(), UtilsLibrary.GetListEnum(typeof(TabSHIFT_TYPE)).Where(w => w.Value == c.ID_SHIFT_TYPE.ToString()).Select(s => s.StringDescription).FirstOrDefault(), c.LogLogin, c.TotalTransactions.ToString("C2"), c.LastTotalCashier.ToString("C2"), c.TotalFinal.ToString("C2"), c.TotalCalc.ToString("C2"), c.DifferenceFinalCalc.ToString("C2"), UtilsLibrary.GetListEnum(typeof(TabReport_Type)).Where(w => w.Value == c.ID_Report_Type.ToString()).Select(s => s.StringDescription).FirstOrDefault(), }).Take(iDisplayLength); return(Json(new { sEcho = sEcho, // Variável Padraão do plugin iTotalRecords = 50, // Deve realizar outra pesquisa no Banco de Daods sem Filtro de pesquisa e mostar a quantidade de registros no Banco nessa variável "iTotalRecords" no exemplo esta 1000 fixo mas deve ser a qtde total de registro que existe na tabela iTotalDisplayRecords = displayedCompanies.Code, //Total de Registro com filtro Aplicado aaData = result // Lista que será exibida do grid }, JsonRequestBehavior.AllowGet)); }
public ActionResult ReportEndOfTheDay([Bind(Include = "txtDtInicio, txtDtFim, txtNome, ID_Report_Type, ID_User, ID_SHIFT_TYPE")] Dominio.Mensagens.GridTotalTransactionIndex transaction) { var msgTotalTransactions = new Dominio.Mensagens.Filtro.TotalTransactions(); msgTotalTransactions.dtInicio = Convert.ToString(Request["txtDtInicio"]); msgTotalTransactions.dtFim = Convert.ToString(Request["txtDtFim"]); msgTotalTransactions.txtNome = Convert.ToString(Request["txtNome"]); msgTotalTransactions.ID_REPORT_TYPE = transaction.ID_Report_Type; msgTotalTransactions.ID_SHIFT_TYPE = transaction.ID_SHIFT_TYPE; if (transaction.ID_User > 0) { var msg = Fachada.Repositorio.USER.GetUserById(transaction.ID_User); if (msg != null) { msgTotalTransactions.Username = msg.Instance.Username; } } var displayedCompanies = Fachada.Repositorio.TOTAL_TRANSACTIONS.GetReportEndOfTheDayTotalTransactions(msgTotalTransactions); string returnFolder = returnFolderPDF("EndOfTheDay.pdf"); //Create new PDF document Document document = new Document(PageSize.A4, 20f, 20f, 20f, 20f); try { PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(returnFolder, FileMode.Create)); writer.PageEvent = new PDFUtils(); document.Open(); document.Add(createHeader("End Of The Day")); decimal total = 0; decimal totalReport = 0; List <Dominio.Mensagens.GridTotalTransactionIndex> listGTT = new List <Dominio.Mensagens.GridTotalTransactionIndex>(); DateTime dtNow = DateTime.Now; foreach (var item in displayedCompanies.Instances.OrderByDescending(q => q.DAY_MONTH_YEAR_REFERENCE)) { if (item.DAY_MONTH_YEAR_REFERENCE.HasValue) { if (dtNow != item.DAY_MONTH_YEAR_REFERENCE.Value) { if (listGTT != null && listGTT.Count > 0) { document.Add(createTableEndOfTheDay(listGTT, total)); listGTT = new List <Dominio.Mensagens.GridTotalTransactionIndex>(); total = 0; } dtNow = item.DAY_MONTH_YEAR_REFERENCE.Value; } listGTT.Add(item); total += item.TotalTransactions; totalReport += item.TotalTransactions; } } PdfPTable table = new PdfPTable(2); table.LockedWidth = true; float[] widths = new float[] { 5f, 2f }; table.SetWidths(widths); table.TotalWidth = 300F; PdfPCell cell = new PdfPCell(); cell.Colspan = 2; cell.PaddingBottom = 5f; cell.Border = 0; cell.HorizontalAlignment = 0; //0=Left, 1=Centre, 2=Right table.AddCell(cell); Font fontTitle2 = new Font(Font.FontFamily.TIMES_ROMAN, 14f, Font.BOLD); Font font = new Font(Font.FontFamily.TIMES_ROMAN, 12f, Font.NORMAL); table.AddCell(new Phrase("Total End Of The Day: ", fontTitle2)); table.AddCell(new Phrase(totalReport.ToString("C2"), font)); table.AddCell(cell); document.Add(table); } catch (Exception ex) { } finally { document.Close(); } ViewBag.ShowPDF = true; return(View()); }
public ActionResult ReportShift([Bind(Include = "txtDtInicio, txtDtFim, txtNome")] Dominio.Mensagens.GridTotalTransactionIndex transaction) { var msgTotalTransactions = new Dominio.Mensagens.Filtro.TotalTransactions(); msgTotalTransactions.Report_Type = Convert.ToInt32(Request["cmbReport_Type"]); msgTotalTransactions.dtInicio = Convert.ToString(Request["txtDtInicio"]); msgTotalTransactions.dtFim = Convert.ToString(Request["txtDtFim"]); msgTotalTransactions.txtNome = Convert.ToString(Request["txtNome"]); //msgTotalTransactions.ID_REPORT_TYPE = (int)TabReport_Type.Bank; var displayedCompanies = Fachada.Repositorio.TOTAL_TRANSACTIONS.GetReportEndOfTheDayTotalTransactions(msgTotalTransactions); string returnFolder = returnFolderPDF("Shift.pdf"); //Create new PDF document Document document = new Document(PageSize.A4, 20f, 20f, 20f, 20f); try { PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(returnFolder, FileMode.Create)); writer.PageEvent = new PDFUtils(); document.Open(); document.Add(createHeader("Shifts")); List <Dominio.Mensagens.GridTotalTransactionIndex> listGTT = new List <Dominio.Mensagens.GridTotalTransactionIndex>(); DateTime dtNow = DateTime.Now; int idShiftTypeActual = 1; foreach (var item in displayedCompanies.Instances) { if (item.DAY_MONTH_YEAR_REFERENCE.HasValue) { if (dtNow != item.DAY_MONTH_YEAR_REFERENCE.Value) { if (listGTT != null && listGTT.Count > 0) { document.Add(createTableEndOfTheDay(listGTT, 0)); listGTT = new List <Dominio.Mensagens.GridTotalTransactionIndex>(); } dtNow = item.DAY_MONTH_YEAR_REFERENCE.Value; listGTT.Add(item); } else { if (item.ID_SHIFT_TYPE != idShiftTypeActual) { document.Add(createTableEndOfTheDay(listGTT, 0)); listGTT = new List <Dominio.Mensagens.GridTotalTransactionIndex>(); } } } } } catch (Exception ex) { } finally { document.Close(); } ViewBag.ShowPDF = true; return(View()); }
MessageCollection <GridTotalTransactionIndex> ITOTAL_TRANSACTIONS.GetReportEndOfTheDayTotalTransactions(Dominio.Mensagens.Filtro.TotalTransactions filtroPesquisa) { var msg = new MessageCollection <Dominio.Mensagens.GridTotalTransactionIndex>(); try { using (var context = new HostelEntities()) { var query = (from tt in context.Total_Transactions join c in context.Calcs on tt.ID_Calc equals c.ID into cLeft join lt in context.Total_Transactions on tt.ID_Last_Transaction equals lt.ID into ltLeft from subC in cLeft.DefaultIfEmpty() from subLT in ltLeft.DefaultIfEmpty() where tt.DayMonthYearReference != null && (tt.ID_Report_Type == filtroPesquisa.ID_REPORT_TYPE || filtroPesquisa.ID_REPORT_TYPE == 0) && (tt.ID_SHIFT_TYPE == filtroPesquisa.ID_SHIFT_TYPE || filtroPesquisa.ID_SHIFT_TYPE == 0) select new Dominio.Mensagens.GridTotalTransactionIndex() { ID = tt.ID, DT_Reg = tt.DT_Reg.Value, LogLogin = tt.LogLogin, TotalTransactions = tt.TotalTransactions.Value, IDLastTotalCalc = (subC != null && subC.ID > 0 ? subC.ID : 0), TotalCalc = (subC != null && subC.Total > 0 ? subC.Total : 0), IDLastTotalCashier = (subLT != null && subLT.ID > 0 ? subLT.ID : 0), LastTotalCashier = (subLT != null && subLT.TotalFinal.HasValue ? subLT.TotalFinal.Value : 0), TotalFinal = tt.TotalFinal.Value, DifferenceFinalCalc = tt.DifferenceFinalCalc.HasValue ? tt.DifferenceFinalCalc.Value : 0, ID_Report_Type = tt.ID_Report_Type.HasValue ? tt.ID_Report_Type.Value : 0, ID_SHIFT_TYPE = tt.ID_SHIFT_TYPE.HasValue ? tt.ID_SHIFT_TYPE.Value : 0, DAY_MONTH_YEAR_REFERENCE = tt.DayMonthYearReference.Value }); query.OrderByDescending(q => q.DAY_MONTH_YEAR_REFERENCE); ////Filtros de Pesquisa if (!string.IsNullOrEmpty(filtroPesquisa.Username)) { query = query.Where(q => q.LogLogin.Contains(filtroPesquisa.Username)); } if (!string.IsNullOrEmpty(filtroPesquisa.dtInicio)) { DateTime dtFim = DateTime.UtcNow; if (!string.IsNullOrEmpty(filtroPesquisa.dtFim)) { dtFim = Convert.ToDateTime(filtroPesquisa.dtFim); } DateTime dtInicio = Convert.ToDateTime(filtroPesquisa.dtInicio); query = query.Where(q => q.DAY_MONTH_YEAR_REFERENCE >= dtInicio.Date && q.DAY_MONTH_YEAR_REFERENCE <= dtFim.Date); } msg.Instances = query.ToList(); msg.Code = query.Count(); } } catch (Exception ex) { msg.Exception = ex; } return(msg); }
MessageCollection <GridTotalTransactionIndex> ITOTAL_TRANSACTIONS.GetAllTotalTransactions(Dominio.Mensagens.Filtro.TotalTransactions filtroPesquisa) { var msg = new MessageCollection <Dominio.Mensagens.GridTotalTransactionIndex>(); try { using (var context = new HostelEntities()) { var query = (from tt in context.Total_Transactions join c in context.Calcs on tt.ID_Calc equals c.ID into cLeft join lt in context.Total_Transactions on tt.ID_Last_Transaction equals lt.ID into ltLeft from subC in cLeft.DefaultIfEmpty() from subLT in ltLeft.DefaultIfEmpty() where tt.ID_Report_Type != filtroPesquisa.ID_REPORT_TYPE select new Dominio.Mensagens.GridTotalTransactionIndex() { ID = tt.ID, DT_Reg = tt.DT_Reg.Value, LogLogin = tt.LogLogin, TotalTransactions = tt.TotalTransactions.Value, IDLastTotalCalc = (subC != null && subC.ID > 0 ? subC.ID : 0), TotalCalc = (subC != null && subC.Total > 0 ? subC.Total : 0), IDLastTotalCashier = (subLT != null && subLT.ID > 0 ? subLT.ID : 0), LastTotalCashier = (subLT != null && subLT.TotalFinal.HasValue ? subLT.TotalFinal.Value : 0), TotalFinal = tt.TotalFinal.Value, DifferenceFinalCalc = tt.DifferenceFinalCalc.HasValue ? tt.DifferenceFinalCalc.Value : 0, ID_Report_Type = tt.ID_Report_Type.HasValue ? tt.ID_Report_Type.Value : 0, ID_SHIFT_TYPE = tt.ID_SHIFT_TYPE.HasValue ? tt.ID_SHIFT_TYPE.Value : 0, DAY_MONTH_YEAR_REFERENCE = tt.DayMonthYearReference.HasValue ? tt.DayMonthYearReference.Value : (DateTime?)null }); ////Filtros de Pesquisa if (filtroPesquisa.Report_Type > 0) { query = query.Where(q => (q.ID_Report_Type == filtroPesquisa.Report_Type)); } if (!string.IsNullOrEmpty(filtroPesquisa.txtNome)) { query = query.Where(q => q.LogLogin.Contains(filtroPesquisa.txtNome)); } if (!string.IsNullOrEmpty(filtroPesquisa.dtInicio)) { DateTime dtFim = DateTime.UtcNow; if (!string.IsNullOrEmpty(filtroPesquisa.dtFim)) { dtFim = Convert.ToDateTime(filtroPesquisa.dtFim); } DateTime dtInicio = Convert.ToDateTime(filtroPesquisa.dtInicio); query = query.Where(q => q.DT_Reg >= dtInicio && q.DT_Reg <= dtFim); } //ordenação if (filtroPesquisa.sSortDir_0 == "asc") { if (filtroPesquisa.iSortCol_0 == 1) { query = query.OrderBy(q => q.DT_Reg); } else if (filtroPesquisa.iSortCol_0 == 2) { query = query.OrderBy(q => q.LogLogin); } else if (filtroPesquisa.iSortCol_0 == 3) { query = query.OrderBy(q => q.TotalTransactions); } else if (filtroPesquisa.iSortCol_0 == 4) { query = query.OrderBy(q => q.LastTotalCashier); } else if (filtroPesquisa.iSortCol_0 == 5) { query = query.OrderBy(q => q.TotalFinal); } else if (filtroPesquisa.iSortCol_0 == 6) { query = query.OrderBy(q => q.DifferenceFinalCalc); } else if (filtroPesquisa.iSortCol_0 == 7) { query = query.OrderBy(q => q.Desc_Report_Type); } else { query = query.OrderBy(q => q.DT_Reg); } } else if (filtroPesquisa.sSortDir_0 == "desc") { if (filtroPesquisa.iSortCol_0 == 1) { query = query.OrderByDescending(q => q.DT_Reg); } else if (filtroPesquisa.iSortCol_0 == 2) { query = query.OrderByDescending(q => q.LogLogin); } else if (filtroPesquisa.iSortCol_0 == 3) { query = query.OrderByDescending(q => q.TotalTransactions); } else if (filtroPesquisa.iSortCol_0 == 4) { query = query.OrderByDescending(q => q.LastTotalCashier); } else if (filtroPesquisa.iSortCol_0 == 5) { query = query.OrderByDescending(q => q.TotalFinal); } else if (filtroPesquisa.iSortCol_0 == 6) { query = query.OrderByDescending(q => q.DifferenceFinalCalc); } else if (filtroPesquisa.iSortCol_0 == 7) { query = query.OrderByDescending(q => q.Desc_Report_Type); } else { query = query.OrderByDescending(q => q.DT_Reg); } } else { query = query.OrderByDescending(q => q.DT_Reg); } query = query.Take(100); ////Extract only current page msg.Instances = query.Skip(filtroPesquisa.iDisplayStart).Take(filtroPesquisa.iDisplayLength).ToList(); //msg.Instances = query.ToList(); ////Campo improvisado para gerar o total de paginacao msg.Code = query.Count();//Total de registro com filtros aplicados //Total de registro no banco sem filtro aplicado Deve ser feito uma pesquisa direto na Tabela outra Query Deverrá ser informado em outro campo no grid CONtroller eu coloquei fixo 110 pra teste } } catch (Exception ex) { msg.Exception = ex; } return(msg); }