protected void btnExport_OnClick(object sender, EventArgs e) { if (!AllowExportDebtReceivables) { ShowErrors("Bạn không có quyền xuất file báo cáo nợ phải thu"); return; } //DateTime from = DateTimeUtil.DateGetDefaultFromDate(); //if (!string.IsNullOrWhiteSpace(Request.QueryString["f"])) // from = DateTime.ParseExact(Request.QueryString["f"], "dd/MM/yyyy", CultureInfo.InvariantCulture); //txtFrom.Text = from.ToString("dd/MM/yyyy"); var to = DateTimeUtil.DateGetDefaultToDate(); if (!string.IsNullOrEmpty(Request.QueryString["t"])) { to = DateTime.ParseExact(Request.QueryString["t"], "dd/MM/yyyy", CultureInfo.InvariantCulture); } txtTo.Text = to.ToString("dd/MM/yyyy"); var agencyId = -1; if (!string.IsNullOrEmpty(Request.QueryString["ai"])) { agencyId = Int32.Parse(Request.QueryString["ai"]); var agency = Module.AgencyGetById(Convert.ToInt32(agencyId)); agencySelector.Value = agency.Id.ToString(); agencySelectornameid.Text = agency.Name; } var list = Module.GetBookingDebtReceivables(to, agencyId); list = list.Where(b => b.Value != 0.0).ToList(); Dictionary <Agency, DebtReceivable> dictionary = new Dictionary <Agency, DebtReceivable>(); foreach (Booking booking in list) { if (dictionary.ContainsKey(booking.Agency)) { if (booking.IsTotalUsd) { dictionary[booking.Agency].TotalReceivableUSD += booking.Value; } else { dictionary[booking.Agency].TotalReceivableVND += booking.Value; } } else { var debt = new DebtReceivable(); debt.Agency = booking.Agency; if (booking.IsTotalUsd) { debt.TotalReceivableUSD += booking.Value; } else { debt.TotalReceivableVND += booking.Value; } dictionary.Add(booking.Agency, debt); } } List <DebtReceivable> debtReceivables = new List <DebtReceivable>(); foreach (KeyValuePair <Agency, DebtReceivable> valuePair in dictionary) { debtReceivables.Add(valuePair.Value); } debtReceivables = debtReceivables.OrderByDescending(x => x.TotalReceivableUSD).ToList(); ExcelFile excelFile = ExcelFile.Load(Server.MapPath("/Modules/Sails/Admin/ExportTemplates/bao_cao_no_phai_thu.xls")); ExcelWorksheet sheet = excelFile.Worksheets[0]; const int firstrow = 6; int crow = firstrow; sheet.Rows.InsertCopy(crow, list.Count, sheet.Rows[firstrow]); var index = 1; //sheet.Cells["B3"].Value = from.ToString("dd/MM/yyyy"); sheet.Cells["B4"].Value = to.ToString("dd/MM/yyyy"); foreach (DebtReceivable debtReceivable in debtReceivables) { sheet.Cells[crow, 0].Value = index; sheet.Cells[crow, 1].Value = debtReceivable.Agency != null ? debtReceivable.Agency.Name : ""; _totalReceivableUSD += debtReceivable.TotalReceivableUSD; sheet.Cells[crow, 2].Value = debtReceivable.TotalReceivableUSD.ToString("#,##0.##"); _totalReceivableVND += debtReceivable.TotalReceivableVND; sheet.Cells[crow, 3].Value = debtReceivable.TotalReceivableVND.ToString("#,##0.##"); crow++; index++; } sheet.Cells[crow, 1].Value = "Tổng"; sheet.Cells[crow, 2].Value = _totalReceivableUSD.ToString("#,##0.##"); sheet.Cells[crow, 3].Value = _totalReceivableVND.ToString("#,##0.##"); excelFile.Save(Response, string.Format("bao_cao_no_phai_thu_{0:dd_MM_yyyy}.xlsx", to)); }
private void GetReportData() { //DateTime from = DateTimeUtil.DateGetDefaultFromDate(); //if (!string.IsNullOrWhiteSpace(Request.QueryString["f"])) // from = DateTime.ParseExact(Request.QueryString["f"], "dd/MM/yyyy", CultureInfo.InvariantCulture); //txtFrom.Text = from.ToString("dd/MM/yyyy"); var to = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); if (!string.IsNullOrEmpty(Request.QueryString["t"])) { to = DateTime.ParseExact(Request.QueryString["t"], "dd/MM/yyyy", CultureInfo.InvariantCulture); } txtTo.Text = to.ToString("dd/MM/yyyy"); var agencyId = -1; if (!string.IsNullOrEmpty(Request.QueryString["ai"])) { agencyId = Int32.Parse(Request.QueryString["ai"]); var agency = Module.AgencyGetById(Convert.ToInt32(agencyId)); agencySelector.Value = agency.Id.ToString(); agencySelectornameid.Text = agency.Name; } var list = Module.GetBookingDebtReceivables(to, agencyId); list = list.Where(b => b.Value != 0.0).ToList(); Dictionary <Agency, DebtReceivable> dictionary = new Dictionary <Agency, DebtReceivable>(); foreach (Booking booking in list) { if (dictionary.ContainsKey(booking.Agency)) { if (booking.IsTotalUsd) { dictionary[booking.Agency].TotalReceivableUSD += booking.Value; } else { dictionary[booking.Agency].TotalReceivableVND += booking.Value; } } else { var debt = new DebtReceivable(); debt.Agency = booking.Agency; if (booking.IsTotalUsd) { debt.TotalReceivableUSD += booking.Value; } else { debt.TotalReceivableVND += booking.Value; } dictionary.Add(booking.Agency, debt); } } List <DebtReceivable> debtReceivables = new List <DebtReceivable>(); foreach (KeyValuePair <Agency, DebtReceivable> valuePair in dictionary) { debtReceivables.Add(valuePair.Value); } debtReceivables = debtReceivables.OrderByDescending(x => x.TotalReceivableUSD).ToList(); rptReport.DataSource = debtReceivables; rptReport.DataBind(); }