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);
}
