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