protected void btnDelete_Click(object sender, EventArgs e)
{
Button btnDelete = (Button)sender;
USDRate rate = Module.ExchangedGetById(Convert.ToInt32(btnDelete.CommandArgument));
Module.Delete(rate);
PageRedirect(Request.RawUrl);
}
protected void btnAdd_Click(object sender, EventArgs e)
{
USDRate rate = new USDRate();
rate.ValidFrom = DateTime.ParseExact(txtValidFrom.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
rate.Rate = Convert.ToDouble(txtExchangeRate.Text);
Module.SaveOrUpdate(rate);
PageRedirect(Request.RawUrl);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
index = 0;
_currentRate = Module.ExchangeGetByDate(DateTime.Today);
rptAgencies.DataSource = Module.AgencyGetReceivable();
rptAgencies.DataBind();
index = 0;
rptPayables.DataSource = Module.AgencyGetPayable();
rptPayables.DataBind();
}
}
protected void rptExchangedRate_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.DataItem is USDRate)
{
USDRate rate = (USDRate)e.Item.DataItem;
Literal litValidFrom = (Literal)e.Item.FindControl("litValidFrom");
if (litValidFrom != null)
{
litValidFrom.Text = rate.ValidFrom.ToString("dd/MM/yyyy");
}
Literal litExchangeRate = (Literal)e.Item.FindControl("litExchangeRate");
if (litExchangeRate != null)
{
litExchangeRate.Text = rate.Rate.ToString("0");
}
}
}
protected void GetDataSource()
{
DateTime from;
DateTime to;
bool isDaily = true;
if (ddlMonths.SelectedIndex > 0)
{
int year;
if (string.IsNullOrEmpty(txtYear.Text))
{
year = DateTime.Now.Year;
}
else
{
year = Convert.ToInt32(txtYear.Text);
}
from = new DateTime(year, Convert.ToInt32(ddlMonths.SelectedValue), 1);
to = from.AddMonths(1).AddDays(-1);
}
else
{
if (string.IsNullOrEmpty(txtYear.Text))
{
from = DateTime.Today.AddDays(-DateTime.Today.Day + 1);
to = from.AddMonths(1).AddDays(-1);
}
else
{
from = new DateTime(Convert.ToInt32(txtYear.Text), 1, 1); // Lấy ngày from là ngày 1/1/xxxx
to = from.AddYears(1).AddDays(-1);
isDaily = false;
}
}
if (isDaily)
{
ddlMonths.SelectedValue = from.Month.ToString();
}
else
{
ddlMonths.SelectedIndex = 0;
}
txtYear.Text = to.Year.ToString();
IList list = new ArrayList();
if (isDaily)
{
list = Module.ExpenseGetByDate(ActiveCruise, from, to);
}
else
{
while (from <= to)
{
list.Add(from.Month);
from = from.AddMonths(1);
}
}
_currentRate = Module.ExchangeGetByDate(DateTime.Now);
rptBalance.DataSource = list;
rptBalance.DataBind();
}
private void InsertBookingData(USDRate currentRate, ExcelWorksheet sheet, int current, int index,
Booking booking, Room room, IList _policies)
{
sheet.Cells[current, 0].Value = index; // Cột index
if (!string.IsNullOrEmpty(booking.AgencyCode))
{
sheet.Cells[current, 1].Value = booking.AgencyCode;
}
else
{
sheet.Cells[current, 1].Value = string.Format(BookingFormat, booking.Id);
}
if (booking.Booker != null)
{
sheet.Cells[current, 2].Value = booking.Booker.Name; // Cột booker
}
if (sheet.Name == "Total") //Để chỉnh sửa cột dữ liệu sheet total và các sheet khác cho phù hợp
{
if (booking.Agency != null)
{
sheet.Cells[current, 3].Value = booking.Agency.Name;
}
sheet.Cells[current, 4].Value = booking.StartDate; // Check in
sheet.Cells[current, 5].Value = booking.StartDate.AddDays(booking.Trip.NumberOfDay - 1);
if (booking.Trip.NumberOfOptions <= 1)
{
sheet.Cells[current, 6].Value = booking.Trip.TripCode;
}
else
{
sheet.Cells[current, 6].Value = booking.Trip.TripCode + booking.TripOption;
}
sheet.Cells[current, 7].Value = booking.Adult; // Số người lớn
sheet.Cells[current, 8].Value = booking.Child; // Số trẻ em
if (booking.ComRate == 0)
{
booking.ComRate = currentRate.Rate;
}
sheet.Cells[current, 10].Value = booking.Commission.ToString("#,0.#");
sheet.Cells[current, 11].Value = booking.CommissionVND.ToString("#,0.#");
sheet.Cells[current, 12].Value = booking.ComRate;
sheet.Cells[current, 13].Value = booking.Commission * booking.ComRate + booking.CommissionVND;
sheet.Cells[current, 14].Value = booking.Note;
sheet.Cells[current, 15].Value = booking.ComPaid ? "Paid" : "UnPaid";
}
else
{
sheet.Cells[current, 3].Value = booking.StartDate; // Check in
sheet.Cells[current, 4].Value = booking.StartDate.AddDays(booking.Trip.NumberOfDay - 1);
if (booking.Trip.NumberOfOptions <= 1)
{
sheet.Cells[current, 5].Value = booking.Trip.TripCode;
}
else
{
sheet.Cells[current, 5].Value = booking.Trip.TripCode + booking.TripOption;
}
sheet.Cells[current, 6].Value = booking.Adult; // Số người lớn
sheet.Cells[current, 7].Value = booking.Child; // Số trẻ em
if (booking.ComRate == 0)
{
booking.ComRate = currentRate.Rate;
}
sheet.Cells[current, 9].Value = booking.Commission.ToString("#,0.#");
sheet.Cells[current, 10].Value = booking.CommissionVND.ToString("#,0.#");
sheet.Cells[current, 11].Value = booking.ComRate;
sheet.Cells[current, 12].Value = booking.Commission * booking.ComRate + booking.CommissionVND;
sheet.Cells[current, 13].Value = booking.Note;
sheet.Cells[current, 14].Value = booking.ComPaid ? "Paid" : "UnPaid";
}
}
/// <summary>
/// Xuất thông tin theo một đại lý
/// </summary>
/// <param name="currentRate">Tỉ giá hiện tại (mặc định cho các booking chưa áp tỉ giá</param>
/// <param name="agency">Đối tác cần xuất công nợ</param>
/// <param name="list">Các booking của đối tác xuất công nợ</param>
/// <param name="sheet">Sheet sẽ chứa dữ liệu xuất</param>
/// <param name="time">Thời điểm của báo cáo</param>
private void ExportAgencyData(USDRate currentRate, Agency agency, IList list, ExcelWorksheet sheet, string time)
{
#region -- Thông tin chung --
//Agency agency = Module.AgencyGetById(Convert.ToInt32(ddlAgencies.SelectedValue));
sheet.Cells["G1"].Value = time;
if (agency != null)
{
sheet.Cells["C5"].Value = agency.Accountant + " " + agency.Name;
sheet.Cells["C6"].Value = agency.Address;
sheet.Cells["C7"].Value = agency.Phone;
sheet.Cells["H7"].Value = agency.Fax;
}
// Tổng ban đầu = 0
double _totalReceivables = 0;
// Đối với mỗi booking trong danh sách export, cộng thêm giá trị còn lại
foreach (Booking booking in list)
{
// Nếu chưa có tỉ giá thì phải chia cho tỉ giá mặc định
if (booking.ComRate > 0)
{
_totalReceivables += booking.CommissionLeft / booking.ComRate; // In USD
}
else
{
_totalReceivables += booking.CommissionLeft / currentRate.Rate;
}
}
//TODO: check lại phương pháp tính tổng
//sheet.Cells["L14"].Value = _totalReceivables;
//sheet.Cells["N14"].Value = _totalReceivables * currentRate.Rate;
sheet.Cells["M21"].Value = UserIdentity.FullName;
if (agency != null && agency.Sale != null)
{
sheet.Cells["B21"].Value = agency.Sale.FullName;
}
#endregion
#region -- Các giá trị dùng chung --
// Lấy giá theo phòng
Room room = null;
// Chính sách giá cho đại lý hiện tại
IList _policies;
// Nếu là export công nợ đại lý và đại lý có role
if (agency != null && agency.Role != null)
{
_policies = Module.AgencyPolicyGetByRole(agency.Role);
}
// Nếu không phải công nợ đại lý hoặc không có role thì lấy giá anonymous
else
{
_policies = Module.AgencyPolicyGetByRole(Module.RoleGetById(4));
}
#endregion
// Sao chép dòng đầu theo số lượng booking
// Dòng đầu tiên là dòng 11
const int firstrow = 12;
sheet.Rows[firstrow].InsertCopy(list.Count - 1, sheet.Rows[firstrow]);
#region -- Thông tin từng booking --
// Ghi vào file excel theo từng dòng
int current = firstrow;
double totalCommission = 0.0;
double totalCommissionIsVND = 0.0;
double totalComissionVND = 0.0;
foreach (Booking booking in list)
{
int index = current - firstrow + 1;
InsertBookingData(currentRate, sheet, current, index, booking, room, _policies);
totalCommission = totalCommission + booking.Commission;
totalCommissionIsVND = totalCommissionIsVND + booking.CommissionVND;
totalComissionVND = totalComissionVND + (booking.Commission * booking.ComRate + booking.CommissionVND);
current += 1;
}
if (sheet.Name == "Total")
{
sheet.Cells["K" + (current + 1)].Value = "$ " + totalCommission.ToString("#,0.#");
sheet.Cells["L" + (current + 1)].Value = "VND " + totalCommissionIsVND.ToString("#,0.#");
sheet.Cells["N" + (current + 1)].Value = "VND " + totalComissionVND.ToString("#,0.#");
}
else
{
sheet.Cells["J" + (current + 1)].Value = "$ " + totalCommission.ToString("#,0.#");
sheet.Cells["K" + (current + 1)].Value = "VND " + totalCommissionIsVND.ToString("#,0.#");
sheet.Cells["M" + (current + 1)].Value = "VND " + totalComissionVND.ToString("#,0.#");
}
#endregion
}
/// <summary>
/// Xuất công nợ, lọc theo 1 đối tác = 1 sheet
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
if (!Module.PermissionCheck(Permission.ACTION_EXPORTCONGNO, UserIdentity))
{
ShowError("You do not have permission to use this function!");
return;
}
// Bắt đầu thao tác export
ExcelFile excelFile = new ExcelFile();
excelFile.LoadXls(Server.MapPath("/Modules/Sails/Admin/ExportTemplates/Commission.xls"));
ExcelWorksheet sheet;
ExcelWorksheet SheetTotal;
#region -- Lấy các dữ liệu chuẩn bị --
// Lấy danh sách agency
IList agencyList = GetAgencies();
int count;
//TODO: Sort theo start date
IList fullList = GetData(out count, Order.Asc("Booker")); // Danh sách toàn bộ booking
try
{
if (agencyList.Count == 0)
{
foreach (Booking booking in fullList)
{
if (booking.Agency == null)
{
continue;
}
if (!agencyList.Contains(booking.Agency))
{
agencyList.Add(booking.Agency);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Cannot get agency list", ex);
}
DateTime from;
DateTime to;
if (string.IsNullOrEmpty(txtFrom.Text) || string.IsNullOrEmpty(txtTo.Text))
{
from = DateTime.Today.AddDays(-DateTime.Today.Day + 1);
to = from.AddMonths(1).AddDays(-1);
}
else
{
from = DateTime.ParseExact(txtFrom.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
to = DateTime.ParseExact(txtTo.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
string time;
if (from.AddMonths(1).AddDays(-1) == to)
{
time = from.ToString("MMM - yyyy");
}
else
{
time = string.Format("{0:dd/MM/yyyy} - {1:dd/MM/yyyy}", from, to);
}
#endregion
USDRate currentRate = Module.ExchangeGetByDate(DateTime.Now);
if (currentRate == null)
{
ShowError("Exchanged rate haven't been configurated!");
}
SheetTotal = excelFile.Worksheets.AddCopy("Total", excelFile.Worksheets[0]); //Để tạo sheet Total là sheet đầu tiên trong file công nợ
ExportAgencyData(currentRate, null, fullList, SheetTotal, time);
foreach (Agency agency in agencyList)
{
sheet = excelFile.Worksheets.AddCopy(string.Format("{0} ({1})", agency.Name, agency.Id), excelFile.Worksheets[0]);
sheet.Columns[3].Delete(); //Để xóa cột partner trong sheet mẫu
// Tạo sheet mới, sao chép nguyên từ sheet cũ, số lượng sheet = số lượng agency
IList list = new ArrayList();
// Chỉ lấy các booking chưa trả hết nợ của agency này
foreach (Booking booking in fullList)
{
if (booking.Agency != agency)
{
continue;
}
// Chỉ loại trừ khi nợ đúng bằng 0
list.Add(booking);
}
ExportAgencyData(currentRate, agency, list, sheet, time);
}
#region -- Trả dữ liệu về cho người dùng --
// Xóa sheet mẫu
if (excelFile.Worksheets.Count > 1)
{
excelFile.Worksheets[0].Delete();
}
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("content-disposition", "attachment; filename=" + string.Format("Congno{0}.xls", time));
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
}
