protected CostingTable GetCurrentTable(DateTime date, SailsTrip trip, TripOption option) { #region -- costing table -- if (_tableCache == null) { // Lấy về mảng costing table int trips = Module.TripMaxId() + 1; const int options = 3; _tableCache = new CostingTable[trips, options]; } // Nếu bảng giá tại vị trí này là null hoặc hết hạn if (_tableCache[trip.Id, (int)option] == null || _tableCache[trip.Id, (int)option].ValidTo < date) { _tableCache[trip.Id, (int)option] = Module.CostingTableGetValid(date, trip, option); } _table = _tableCache[trip.Id, (int)option]; #endregion if (_table == null) { throw new Exception(string.Format("No costing table for {0:dd/MM/yyyy}, {1} {2}", date, trip.Name, option)); } return(_table); }
protected void rptCostingTable_ItemDataBound(object sender, RepeaterItemEventArgs e) { if (e.Item.DataItem is CostingTable) { CostingTable table = (CostingTable)e.Item.DataItem; Literal litValidFrom = e.Item.FindControl("litValidFrom") as Literal; if (litValidFrom != null) { litValidFrom.Text = table.ValidFrom.ToString("dd/MM/yyyy"); } HyperLink hyperLinkEdit = e.Item.FindControl("hyperLinkEdit") as HyperLink; if (hyperLinkEdit != null) { hyperLinkEdit.NavigateUrl = string.Format("Costing.aspx?NodeId={0}&SectionId={1}&TableId={2}", Node.Id, Section.Id, table.Id); } Literal litTrip = e.Item.FindControl("litTrip") as Literal; if (litTrip != null) { if (table.Trip != null) { litTrip.Text = table.Trip.Name; } } Literal litOption = e.Item.FindControl("litOption") as Literal; if (litOption != null) { if (table.Trip != null) { if (table.Trip.NumberOfOptions > 0) { litOption.Text = table.Option.ToString(); } } } } }
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 }