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
}