public static void Export(IList list, int count, IList expenseList, DateTime _date, string bookingFormat, HttpResponse Response, string templatePath, SailsTrip trip, SailsAdminBasePage page) { SailsModule module = page.Module; // Bắt đầu thao tác export ExcelFile excelFile = new ExcelFile(); excelFile.LoadXls(templatePath); // Số lệnh điều tua = số lượng group int numberOfGroup = -1; foreach (Booking booking in list) { if (booking.Group > numberOfGroup) { numberOfGroup = booking.Group; } } foreach (ExpenseService service in expenseList) { if (service.Group > numberOfGroup) { numberOfGroup = service.Group; } } if (numberOfGroup <= 0) { numberOfGroup = 1; // Tối thiểu là 1 group } #region -- sheet 0: Lệnh điều tour -- for (int ii = 0; ii <= numberOfGroup; ii++) { // Mở sheet 0 ExcelWorksheet originSheet = excelFile.Worksheets[0]; int numberOfBooking = 0; foreach (Booking booking in list) { if (booking.Group == ii) { numberOfBooking++; } } if (numberOfBooking == 0 && ii == 0) { continue; } ExcelWorksheet sheet = excelFile.Worksheets.AddCopy("LenhDieuTour" + ii, originSheet); #region -- Thông tin chung -- if (expenseList != null) { // Các thông tin chèn thêm foreach (ExpenseService service in expenseList) { if (service.Group != ii) { continue; } switch (service.Type.Id) { case SailsModule.GUIDE_COST: if (service.Type.IsCustomType || service.Supplier == null) { if (string.IsNullOrEmpty(service.Name) && service.Supplier != null) { sheet.Cells["C3"].Value = service.Supplier.Name; } else { sheet.Cells["C3"].Value = service.Name; } if (string.IsNullOrEmpty(service.Name) && service.Supplier != null) { sheet.Cells["F3"].Value = service.Supplier.Phone; } else { sheet.Cells["F3"].Value = service.Phone; } } else { sheet.Cells["C3"].Value = service.Supplier.Name; sheet.Cells["f3"].Value = service.Supplier.Phone; } break; case SailsModule.TRANSPORT: if (service.Type.IsCustomType || service.Supplier == null) { if (string.IsNullOrEmpty(service.Name) && service.Supplier != null) { sheet.Cells["C4"].Value = service.Supplier.Name; } else { sheet.Cells["C4"].Value = service.Name; } if (string.IsNullOrEmpty(service.Name) && service.Supplier != null) { sheet.Cells["F4"].Value = service.Supplier.Phone; } else { sheet.Cells["F4"].Value = service.Phone; } } else { sheet.Cells["C4"].Value = service.Supplier.Name; sheet.Cells["f4"].Value = service.Supplier.Phone; } break; case SailsModule.HOTEL: if (service.Supplier != null) { sheet.Cells["C7"].Value = service.Supplier.Name; } else { sheet.Cells["C7"].Value = service.Name; } break; } } } sheet.Cells["D1"].Value = _date; // Tính tổng pax int adult = 0; int child = 0; int baby = 0; int pAdult = 0; int pChild = 0; int pBaby = 0; foreach (Booking booking in list) { if (booking.StartDate == _date && booking.Group == ii) { adult += booking.Adult; child += booking.Child; baby += booking.Baby; } else if (booking.Group == ii) { pAdult += booking.Adult; pChild += booking.Child; pBaby += booking.Baby; } } int pax = adult + child + pAdult + pChild; sheet.Cells["C5"].Value = page.UserIdentity.FullName; sheet.Cells["C6"].Value = pax; sheet.Cells["C12"].Value = adult; sheet.Cells["D12"].Value = child; sheet.Cells["E12"].Value = baby; sheet.Cells["C18"].Value = pAdult; sheet.Cells["D18"].Value = pChild; sheet.Cells["E18"].Value = pBaby; #endregion #region -- Sao chép các dòng -- // Sao chép dòng đầu theo số lượng booking // Dòng đầu tiên là dòng 11 const int firstrow = 10; // Đếm số book trong ngày int curr = 0; foreach (Booking booking in list) { if (booking.StartDate == _date && booking.Group == ii) { curr += 1; } } sheet.Rows[16].InsertCopy(count - curr - 1, sheet.Rows[firstrow]); if (curr > 0) { sheet.Rows[firstrow].InsertCopy(curr - 1, sheet.Rows[firstrow]); } int firstProw = 16 + curr; #endregion #region -- Thông tin từng booking -- // Ghi vào file excel theo từng dòng int crow = firstrow; int cProw = firstProw - 1; foreach (Booking booking in list) { if (booking.Group != ii) { continue; } sheet.Cells["D2"].Value = string.Format("{0}{1:ddMMyy}-{2:00}", booking.Trip.TripCode, booking.StartDate, booking.Group); int current; int index; if (booking.StartDate != _date) { // Dòng previous hiện tại current = cProw; // Index = cột previous hiện tại - previous đầu tiên index = cProw - firstProw + 2; cProw++; } else { current = crow; index = crow - firstrow + 1; crow++; } sheet.Cells[current, 0].Value = index; // Cột index string name = booking.CustomerName.Replace("<br/>", "\n"); if (name.Length > 0) { name = name.Remove(name.Length - 1); } sheet.Cells[current, 1].Value = name; // Cột name sheet.Cells[current, 2].Value = booking.Adult; // Cột adult sheet.Cells[current, 3].Value = booking.Child; // Cột child sheet.Cells[current, 4].Value = booking.Baby; // Cột baby sheet.Cells[current, 5].Value = booking.Trip.TripCode; // Cột trip code sheet.Cells[current, 6].Value = booking.PickupAddress; // Cột pickup address sheet.Cells[current, 7].Value = booking.SpecialRequest; // Cột special request sheet.Cells[current, 9].Style.Borders.SetBorders(MultipleBorders.Outside, System.Drawing.Color.Black, LineStyle.Thin); if (booking.Agency != null) { sheet.Cells[current, 8].Value = booking.Agency.Name; // Cột agency if (!string.IsNullOrEmpty(booking.AgencyCode)) { sheet.Cells[current, 9].Value = booking.AgencyCode; // Chỉ hiển thị nếu có agency (cột TA COde) } else { if (booking.CustomBookingId > 0) { sheet.Cells[current, 9].Value = string.Format(bookingFormat, booking.CustomBookingId); } else { sheet.Cells[current, 9].Value = string.Format(bookingFormat, booking.Id); } } } else { sheet.Cells[current, 8].Value = SailsModule.NOAGENCY; // Hiển thị oriental nếu ko có agency if (booking.CustomBookingId > 0) { sheet.Cells[current, 9].Value = string.Format(bookingFormat, booking.CustomBookingId); } else { sheet.Cells[current, 9].Value = string.Format(bookingFormat, booking.Id); } } if (booking.PickupTime != null) { sheet.Cells[current, 10].Value = booking.PickupTime.Value.ToString("HH:mm"); } if (booking.SeeoffTime != null) { sheet.Cells[current, 11].Value = booking.SeeoffTime.Value.ToString("HH:mm"); } if (trip.Name.ToLower() != "airport transfer") { sheet.Columns[11].Delete(); } if (trip.Name.ToLower() != "airport transfer") { sheet.Columns[10].Delete(); } } #endregion // Mở sheet 1 originSheet = excelFile.Worksheets[1]; ExcelWorksheet budget = excelFile.Worksheets.AddCopy("Budget" + ii, originSheet); budget.Cells["C3"].Value = sheet.Cells["C3"].Value; budget.Cells["C4"].Value = sheet.Cells["C4"].Value; budget.Cells["A1"].Value = sheet.Cells["D1"].Value; budget.Cells["C5"].Value = page.UserIdentity.FullName; budget.Cells["C6"].Value = sheet.Cells["C6"].Value; budget.Cells["A1"].Value = string.Format("{0}{1:ddMMyyyy}", trip.TripCode, _date); // Các chi phí xuất ra bao gồm chi phí theo ngày và chi phí theo khách ExpenseCalculator calculator = new ExpenseCalculator(module, true); SailExpense expense = module.ExpenseGetByDate(trip, _date); IList expenses = module.ExpenseServiceGet(trip, _date, _date, null, null, null, null, false, null, 0, ""); CostingTable cost = calculator.GetCurrentTable(_date, trip, TripOption.Option1); IList costTypes = new ArrayList(); foreach (KeyValuePair <CostType, double> entry in module.CostTypeGetAutoDailyBase()) { costTypes.Add(entry.Key); } Dictionary <CostType, Costing> unitPrices = cost.GetCostMap(costTypes); const int first = 8; curr = first; foreach (ExpenseService entry in expenses) { if (entry.Group != ii) { continue; } if (entry.Type.IsPayNow) { if (entry.Type.IsDailyInput)// Nếu là chi phí nhập tay { if (entry.Cost == 0) { continue; } budget.Rows[curr].InsertCopy(1, budget.Rows[curr]); budget.Cells[curr, 0].Value = curr - first + 1; budget.Cells[curr, 1].Value = _date; if (entry.Type.Name.ToLower().Contains("others")) { budget.Cells[curr, 2].Value = entry.Name; } else { budget.Cells[curr, 2].Value = entry.Type.Name; } budget.Cells[curr, 3].Value = entry.Cost; // chi phí dành cho cả đoàn budget.Cells[curr, 4].Value = entry.Cost; curr++; } else if (!entry.Type.IsMonthly && !entry.Type.IsYearly) { double value = unitPrices[entry.Type].Adult * adult; if (value > 0) { budget.Rows[curr].InsertCopy(1, budget.Rows[curr]); budget.Cells[curr, 0].Value = curr - first + 1; budget.Cells[curr, 1].Value = _date; budget.Cells[curr, 2].Value = entry.Type.Name + " (adult)"; budget.Cells[curr, 3].Value = unitPrices[entry.Type].Adult; // giá adult budget.Cells[curr, 4].Value = value; curr++; } value = unitPrices[entry.Type].Child * child; if (value > 0) { budget.Rows[curr].InsertCopy(1, budget.Rows[curr]); budget.Cells[curr, 0].Value = curr - first + 1; budget.Cells[curr, 1].Value = _date; budget.Cells[curr, 2].Value = entry.Type.Name + " (child)"; budget.Cells[curr, 3].Value = unitPrices[entry.Type].Child; // giá adult budget.Cells[curr, 4].Value = value; curr++; } value = unitPrices[entry.Type].Baby * baby; if (value > 0) { budget.Rows[curr].InsertCopy(1, budget.Rows[curr]); budget.Cells[curr, 0].Value = curr - first + 1; budget.Cells[curr, 1].Value = _date; budget.Cells[curr, 2].Value = entry.Type.Name + " (baby)"; budget.Cells[curr, 3].Value = unitPrices[entry.Type].Baby; // giá adult budget.Cells[curr, 4].Value = value; curr++; } } } } } #endregion #region -- sheet 1: //if (excelFile.Worksheets.Count == 1) //{ // goto end; //} //ExcelWorksheet budget = excelFile.Worksheets[1]; #endregion end: #region -- Trả dữ liệu về cho người dùng -- Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.AppendHeader("content-disposition", "attachment; filename=" + string.Format("Lenhdieutour{0:dd_MMM}.xls", _date)); MemoryStream m = new MemoryStream(); excelFile.SaveXls(m); Response.OutputStream.Write(m.GetBuffer(), 0, m.GetBuffer().Length); Response.OutputStream.Flush(); Response.OutputStream.Close(); m.Close(); Response.End(); #endregion }