public string GetGuide(string guideId)
{
var output = "";
var intGuideId = -1;
try
{
intGuideId = Int32.Parse(guideId);
}
catch { }
var guide = ExpenseHistoryBLL.AgencyGetById(intGuideId);
if (guide != null && guide.Id > 0)
{
output += guide.Name + " - M: <span>" + NumberUtil.FormatPhoneNumber(guide.Phone) + "</span>";
}
return(output);
}
public string GuidePhone_AgencyGetById(int?gi)
{
int guideId = 0;
if (gi.HasValue)
{
guideId = gi.Value;
}
var guide = TransferRequestByDateBLL.AgencyGetById(guideId);
if (guide == null || guide.Id <= 0)
{
return(null);
}
var guidePhone = NumberUtil.FormatPhoneNumber(guide.Phone);
Dispose();
return(JsonConvert.SerializeObject(guidePhone));
}
private void FillOpt(List <User> listOpt, ExcelWorksheet sheet, ref int currentRow)
{
for (var i = 0; i < listOpt.Count; i++)
{
//Nếu là Opt thứ 2 trở đi xóa chữ "Opt:"
if (i == 0)
{
sheet.Cells[currentRow, 2].Value = "Opt:";
}
else
{
sheet.Cells[currentRow, 2].Value = "";
}
//--
sheet.Cells[currentRow, 3].Value = listOpt[i].FullName;
sheet.Cells[currentRow, 7].Value = "Mob:";
sheet.Cells[currentRow, 8].Value = NumberUtil.FormatPhoneNumber(listOpt[i].Phone);
++currentRow;
}
}
private void FillDriver(IList <BusByDate> listBusByDate, ExcelWorksheet sheet, ref int currentRow)
{
for (var i = 0; i < listBusByDate.Count; i++)
{
//Nếu là Driver thứ 2 trở đi xóa chữ "Driver:"
if (i == 0)
{
sheet.Cells[currentRow, 2].Value = "Driver:";
}
else
{
sheet.Cells[currentRow, 2].Value = "";
}
//--
sheet.Cells[currentRow, 3].Value = listBusByDate[i].Driver_Name;
sheet.Cells[currentRow, 4].Value = "Mob:";
sheet.Cells[currentRow, 5].Value = NumberUtil.FormatPhoneNumber(listBusByDate[i].Driver_Phone);
++currentRow;
}
}
private void FillGuide(IList <Guide> listGuide, ExcelWorksheet sheet, ref int currentRow)
{
for (var i = 0; i < listGuide.Count; i++)
{
//Nếu là Guide thứ 2 trở đi xóa chữ "Guide:"
if (i == 0)
{
sheet.Cells[currentRow, 2].Value = "Guide:";
}
else
{
sheet.Cells[currentRow, 2].Value = "";
}
//--
sheet.Cells[currentRow, 3].Value = listGuide[i].Name;
sheet.Cells[currentRow, 7].Value = "Mob:";
sheet.Cells[currentRow, 8].Value = NumberUtil.FormatPhoneNumber(listGuide[i].Phone);
++currentRow;
}
}
public void ExportTourByCruiseAndGuide(string ci, string gi, string d, string ob, string op)
{
var cruiseId = -1;
try
{
cruiseId = Int32.Parse(ci);
}
catch { }
var cruise = BookingReportBLL.CruiseGetById(cruiseId);
var guideId = -1;
try
{
guideId = Int32.Parse(gi);
}
catch { }
var guide = BookingReportBLL.Guide_AgencyGetById(guideId);
if (guide == null)
{
guide = new Agency();
}
DateTime?date = null;
try
{
date = DateTime.ParseExact(d, "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
catch
{
date = DateTime.Today;
}
var operationName = ob;
var operationPhone = op;
var listBooking = BookingReportBLL.BookingGetByCriterion(date, cruise).Where(x => x.Status == StatusType.Approved)
.Future().ToList();
using (var memoryStream = new MemoryStream())
{
using (var excelPackage = new ExcelPackage(new FileInfo(Server.MapPath("/Modules/Sails/Admin/ExportTemplates/TourCommand.xlsx"))))
{
var sheet = excelPackage.Workbook.Worksheets.Copy("Tour Command", "TC" + "-" + cruise.Code + "-" + guide.Name);
sheet.Cells["I1"].Value = cruise.Name;
sheet.Cells["E1"].Value = (date.HasValue ? date.Value.ToLongDateString() : "");
sheet.Cells["B3"].Value = "Guide:";
sheet.Cells["C3"].Value = (guide != null ? guide.Name : "");
sheet.Cells["D3"].Value = "Mob:";
sheet.Cells["E3"].Value = (guide != null ? NumberUtil.FormatPhoneNumber(guide.Phone) : "");
sheet.Cells["B5"].Value = "Opt:";
sheet.Cells["C5"].Value = operationName;
sheet.Cells["D5"].Value = "Mob:";
sheet.Cells["E5"].Value = NumberUtil.FormatPhoneNumber(operationPhone);
//Tìm driver theo booking
var listBusByDate = new List <BusByDate>();
foreach (var booking in listBooking)
{
var busByDate = booking.ListBookingBusByDate.Where(x => x.BusByDate != null && x.BusByDate.Date == date)
.Select(x => x.BusByDate)
.FirstOrDefault();
if (busByDate != null)
{
listBusByDate.Add(busByDate);
}
}
//Điền driver vào lệnh điều tour
var templateDriverRow = 4;
sheet.Cells[templateDriverRow, 2].Value = "Driver:";
sheet.InsertRow(templateDriverRow, listBusByDate.Count - 1, templateDriverRow);
FillDriver(listBusByDate, sheet, ref templateDriverRow);
//--
//Export booking trong ngày
int templateRow = templateDriverRow + 3;
int currentRow = templateRow + 1;
int totalRow = templateRow + listBooking.Count();
int index = 1;
sheet.InsertRow(currentRow, listBooking.Count, currentRow - 1);
for (int i = 0; i < listBooking.Count; i++)
{
var booking = listBooking[i] as Booking;
if (booking != null)
{
var name = booking.CustomerNameFull.Replace("<br/>", "\r\n").ToUpper();
sheet.Cells[currentRow, 1].Value = index;
sheet.Cells[currentRow, 2, currentRow, 3].Merge = true;
sheet.Cells[currentRow, 2].Value = name;
sheet.Cells[currentRow, 4].Value = booking.Adult;
sheet.Cells[currentRow, 5].Value = booking.Child;
sheet.Cells[currentRow, 6].Value = booking.Baby;
sheet.Cells[currentRow, 7].Value = booking.Trip.TripCode;
sheet.Cells[currentRow, 8].Value = booking.PickupAddress;
sheet.Cells[currentRow, 9].Value = booking.SpecialRequest;
sheet.Cells[currentRow, 11].Value = "OS" + booking.Id;
sheet.Cells[currentRow, 26].Value = name;//Work around cho cột merged name không hiển thị hết khi nội dung quá dài
currentRow++;
index++;
}
}
sheet.DeleteRow(templateRow);
sheet.Cells[totalRow, 4].Value = listBooking.Sum(x => x.Adult);
sheet.Cells[totalRow, 5].Value = listBooking.Sum(x => x.Child);
sheet.Cells[totalRow, 6].Value = listBooking.Sum(x => x.Baby);
//--
//Export booking đi 2 ngày bắt đầu từ ngày hôm trước
listBooking = BookingReportBLL.BookingGetByCriterion(date.Value.AddDays(-1), cruise)
.Where(x => x.Status == StatusType.Approved)
.Future().ToList()
.Where(x => x.Trip.NumberOfDay > 2)
.ToList();
templateRow = currentRow + 4;
currentRow = templateRow + 1;
totalRow = templateRow + listBooking.Count();
index = 1;
sheet.InsertRow(currentRow, listBooking.Count, currentRow - 1);
for (int i = 0; i < listBooking.Count; i++)
{
var booking = listBooking[i] as Booking;
if (booking != null)
{
var name = booking.CustomerNameFull.Replace("<br/>", "\r\n").ToUpper();
sheet.Cells[currentRow, 1].Value = index;
sheet.Cells[currentRow, 2, currentRow, 3].Merge = true;
sheet.Cells[currentRow, 2].Value = name;
sheet.Cells[currentRow, 4].Value = booking.Adult;
sheet.Cells[currentRow, 5].Value = booking.Child;
sheet.Cells[currentRow, 6].Value = booking.Baby;
sheet.Cells[currentRow, 7].Value = booking.Trip.TripCode;
sheet.Cells[currentRow, 8].Value = booking.PickupAddress;
sheet.Cells[currentRow, 9].Value = booking.SpecialRequest;
sheet.Cells[currentRow, 11].Value = "OS" + booking.Id;
sheet.Cells[currentRow, 26].Value = name;//Work around cho cột merged name không hiển thị hết khi nội dung quá dài
currentRow++;
index++;
}
}
sheet.DeleteRow(templateRow);
sheet.Cells[totalRow, 4].Value = listBooking.Sum(x => x.Adult);
sheet.Cells[totalRow, 5].Value = listBooking.Sum(x => x.Child);
sheet.Cells[totalRow, 6].Value = listBooking.Sum(x => x.Baby);
//--
excelPackage.Workbook.Worksheets.Delete("Tour Command");
excelPackage.SaveAs(memoryStream);
}
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.SuppressContent = true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
Dispose();
}
public void ExportWelcomeBoard(ExcelPackage excelPackage, BusByDate busByDate)
{
var listBookingBusByDate = TransferRequestByDateBLL.BookingBusByDateGetAllByCriterion(busByDate).Future().ToList();
var listBooking = listBookingBusByDate.Select(x => x.Booking).ToList();
var shortRouteName = StringUtil.GetFirstLetter(busByDate.Route.Name);
var sheet = excelPackage.Workbook.Worksheets.Copy("Welcome Board", "WB" + "-"
+ shortRouteName.Replace(" ", "").Replace("-", "_") + "-" + busByDate.BusType.Name
+ "-G" + busByDate.Group);
var totalRow = 6;
var totalCol = 11;
for (int i = 0; i < listBooking.Count; i++)
{
var booking = listBooking[i];
var listCustomer = booking.Customers;
sheet.Cells[1, 1, totalRow, totalCol].Copy(sheet.Cells[(i + 1) * totalRow + 1, 1]);
var currentRow = (i + 2) * totalRow;
//chỉnh lại chiều cao của các row vừa mới copy
sheet.Row(currentRow - 5).Height = 119.25;
sheet.Row(currentRow - 4).Height = 42;
sheet.Row(currentRow - 3).Height = 300.75;
sheet.Row(currentRow - 2).Height = 30.75;
sheet.Row(currentRow - 1).Height = 45.75;
sheet.Row(currentRow - 0).Height = 42;
var name = booking.CustomerNameFull.Replace("<br/>", "\r\n").ToUpper();
sheet.Cells[currentRow - 3, 1].Value = name;
sheet.Cells[currentRow - 1, 2].Value = busByDate.Driver_Name + " - " + NumberUtil.FormatPhoneNumber(busByDate.Driver_Phone);
foreach (var busByDateGuide in busByDate.BusByDatesGuides)
{
sheet.Cells[currentRow - 0, 2].Value += (busByDateGuide.Guide != null ? busByDateGuide.Guide.Name : "") + " - "
+ (busByDateGuide.Guide != null ? NumberUtil.FormatPhoneNumber(busByDateGuide.Guide.Phone) : "") + "\r\n";
}
sheet.Cells[currentRow - 1, 5].Value = booking.PickupAddress;
sheet.Cells[currentRow - 1, 10].Value = CurrentUser.FullName;
}
sheet.DeleteRow(1, totalRow);
}
