public ActionResult Report(string id, string frmDate, string tDate)
{
if (id == null)
{
return View();
}
int ID;
DateTime fromDate = new DateTime();
DateTime toDate = new DateTime();
if (!int.TryParse(id, out ID))
{
ViewBag.error = "Không thể lấy thống kê";
return View();
}
if (ID != 6)
{
if (String.IsNullOrWhiteSpace(frmDate))
{
//fromDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
fromDate = DateTime.Now.Date;
}
else
{
if (!DateTime.TryParseExact(frmDate, "dd/MM/yyyy", null
, System.Globalization.DateTimeStyles.None
, out fromDate))
{
ViewBag.error = "Ngày bắt đầu bị sai";
return View();
}
}
if (String.IsNullOrWhiteSpace(tDate))
{
toDate = DateTime.Now;
}
else
{
if (!DateTime.TryParseExact(tDate, "dd/MM/yyyy", null
, System.Globalization.DateTimeStyles.None
, out toDate))
{
ViewBag.error = "Ngày kết thúc bị sai";
return View();
}
else
{
toDate = toDate.AddDays(1).AddTicks(-1);
}
}
}
if (fromDate > toDate)
{
ViewBag.error = "Không thể lấy thống kê";
return View();
}
//Lịch sử giao dịch
if (ID == 1)
{
DataTable transactionDT = new TransactionHistoryListTableAdapter().GetDataByDate(fromDate, toDate);
transactionDT.PrimaryKey = null;
transactionDT.Columns.Remove("TransactionHistoryID");
transactionDT.Columns.Remove("TransactionTypeID");
transactionDT.Columns.Remove("IsAuto");
transactionDT.Columns["Username"].SetOrdinal(0);
transactionDT.Columns["Value"].SetOrdinal(1);
transactionDT.Columns["TransactionContent"].SetOrdinal(2);
transactionDT.Columns["Name"].SetOrdinal(3);
transactionDT.Columns["InsertedDate"].SetOrdinal(4);
transactionDT.Columns["Username"].ColumnName = "Tên đăng nhập";
transactionDT.Columns["Value"].ColumnName = "Giá trị giao dịch";
transactionDT.Columns["TransactionContent"].ColumnName = "Nội dung giao dịch";
transactionDT.Columns["Name"].ColumnName = "Loại giao dịch";
transactionDT.Columns["InsertedDate"].ColumnName = "Thời điểm giao dịch";
CTMF_Website.Util.ExcelReport.ExportToExcel(transactionDT, "Lich_Su_Giao_Dich");
}
//Lịch sử ăn
else if (ID == 2)
{
DataTable transactionDT = new TransactionHistoryListTableAdapter().GetEatingDataByDate(fromDate, toDate);
transactionDT.PrimaryKey = null;
transactionDT.Columns.Remove("TransactionHistoryID");
transactionDT.Columns.Remove("TransactionTypeID");
transactionDT.Columns.Remove("IsAuto");
transactionDT.Columns.Remove("Name");
transactionDT.Columns["Username"].SetOrdinal(0);
transactionDT.Columns["Value"].SetOrdinal(1);
transactionDT.Columns["TransactionContent"].SetOrdinal(2);
transactionDT.Columns["InsertedDate"].SetOrdinal(3);
transactionDT.Columns["Username"].ColumnName = "Tên đăng nhập";
transactionDT.Columns["Value"].ColumnName = "Giá trị giao dịch";
transactionDT.Columns["TransactionContent"].ColumnName = "Nội dung giao dịch";
transactionDT.Columns["InsertedDate"].ColumnName = "Thời điểm giao dịch";
CTMF_Website.Util.ExcelReport.ExportToExcel(transactionDT, "Lich_Su_An");
}
//Thống kế lượt ăn theo bữa
else if (ID == 3)
{
DataTable eatTimeDT = new EatTimeTableAdapter().GetDataByDate(fromDate.Date, toDate.Date);
eatTimeDT.Columns["Date"].SetOrdinal(0);
eatTimeDT.Columns["Name"].SetOrdinal(1);
eatTimeDT.Columns["EatTime"].SetOrdinal(2);
eatTimeDT.Columns["Date"].ColumnName = "Ngày";
eatTimeDT.Columns["Name"].ColumnName = "Tên bữa ăn";
eatTimeDT.Columns["EatTime"].ColumnName = "Số lượt ăn";
CTMF_Website.Util.ExcelReport.ExportToExcel(eatTimeDT, "Luot_An_Theo_Bua");
}
//Thông kê suất ăn được sử dụng
else if (ID == 4)
{
DataTable mealSetEatTime = new MealSetEatTimeTableAdapter().GetDataByDate(fromDate, toDate);
mealSetEatTime.Columns["MealSetID"].SetOrdinal(0);
mealSetEatTime.Columns["Name"].SetOrdinal(1);
mealSetEatTime.Columns["EatTime"].SetOrdinal(2);
mealSetEatTime.Columns["MealSetID"].ColumnName = "Mã suất ăn";
mealSetEatTime.Columns["Name"].ColumnName = "Tên suất ăn";
mealSetEatTime.Columns["EatTime"].ColumnName = "Số lượt ăn";
CTMF_Website.Util.ExcelReport.ExportToExcel(mealSetEatTime, "Luot_An_Theo_Suat");
}
//Thống kê món ăn được sử dụng
else if (ID == 5)
{
DataTable dishEatTime = new DishEatTimeTableAdapter().GetDataByDate(fromDate, toDate);
dishEatTime.Columns["DishID"].SetOrdinal(0);
dishEatTime.Columns["Name"].SetOrdinal(1);
dishEatTime.Columns["EatTime"].SetOrdinal(2);
dishEatTime.Columns["DishID"].ColumnName = "Mã món ăn";
dishEatTime.Columns["Name"].ColumnName = "Tên suất ăn";
dishEatTime.Columns["EatTime"].ColumnName = "Số lượt ăn";
CTMF_Website.Util.ExcelReport.ExportToExcel(dishEatTime, "Luot_An_Theo_Mon_An");
}
//Thống kê người dùng đang nợ tiền
else if (ID == 6)
{
DataTable debtCustomer = new DebtCustomerTableAdapter().GetData();
debtCustomer.Columns["Username"].SetOrdinal(0);
debtCustomer.Columns["Name"].SetOrdinal(1);
debtCustomer.Columns["Email"].SetOrdinal(2);
debtCustomer.Columns["TypeName"].SetOrdinal(3);
debtCustomer.Columns["Money"].SetOrdinal(4);
debtCustomer.Columns["Username"].ColumnName = "Tên đăng nhập";
debtCustomer.Columns["Name"].ColumnName = "Tên";
debtCustomer.Columns["TypeName"].ColumnName = "Loại người dùng";
debtCustomer.Columns["Money"].ColumnName = "Số tiền đang nợ";
CTMF_Website.Util.ExcelReport.ExportToExcel(debtCustomer, "Nguoi_Dung_Dang_No");
}
return View();
}
public ActionResult TransactionHistory(string transactionType, string date, string page, string amountPerPage)
{
ViewBag.error = "";
string username = AccountInfo.GetUserName(Request);
TransactionHistoryListTableAdapter transactionAdapter
= new TransactionHistoryListTableAdapter();
int transactionTypeID;
if (!int.TryParse(transactionType, out transactionTypeID))
{
transactionType = null;
}
DateTime date_;
if (string.IsNullOrWhiteSpace(date))
{
date = null;
}
else if (!DateTime.TryParseExact(date, "dd/MM/yyyy", null
, System.Globalization.DateTimeStyles.None
, out date_))
{
ViewBag.error = "Sai định dạng ngày, tháng";
date = null;
}
else
{
date = date_.ToString("yyyy-MM-dd");
}
int page_;
if (!int.TryParse(page, out page_))
{
page = null;
page_ = 1;
}
int amountPerPage_;
if (!int.TryParse(amountPerPage, out amountPerPage_))
{
amountPerPage = "10";
amountPerPage_ = 10;
}
try
{
string query = "SELECT * FROM ( SELECT TH.TransactionHistoryID, TH.Username, TH.TransactionTypeID, "
+ "TH.Value, TH.TransactionContent, TH.IsAuto, TH.InsertedDate, TT.Name, ROW_NUMBER() OVER "
+ "(ORDER BY TH.InsertedDate DESC) AS RowNum FROM TransactionHistory AS TH INNER JOIN "
+ "TransactionType AS TT ON TH.TransactionTypeID = TT.TransactionTypeID "
+ "WHERE (TH.Username = '******') ";
string conditionQuery = "";
string countQuery = "select COUNT(th.TransactionHistoryID) from TransactionHistory TH WHERE (TH.Username = '******') ";
if (transactionType != null || date != null)
{
if (transactionType != null)
{
conditionQuery += "AND TH.TransactionTypeID = " + transactionType + " ";
}
if (date != null)
{
conditionQuery += "AND TH.InsertedDate BETWEEN '" + date + " 00:00:000' AND '" + date + " 23:59:59:999' ";
}
}
transactionAdapter.Connection.Open();
SqlCommand countCmd = new SqlCommand(countQuery + conditionQuery, transactionAdapter.Connection);
int count = (int)countCmd.ExecuteScalar();
int maxPage = (count / amountPerPage_);
if (count % amountPerPage_ != 0)
{
maxPage++;
}
ViewBag.maxPage = maxPage;
if (page_ > maxPage)
{
page_ = maxPage;
}
ViewBag.curPage = page_;
ViewBag.amountPerPage = amountPerPage_;
int minRowNum = ((page_ - 1) * amountPerPage_) + 1;
int maxRowNum = page_ * amountPerPage_;
query += conditionQuery;
query += ") AS SOD WHERE SOD.RowNum BETWEEN (" + minRowNum + ") AND (" + maxRowNum + ") ";
SqlCommand getDataCmd = new SqlCommand(query, transactionAdapter.Connection);
SqlDataAdapter getDataAdapter = new SqlDataAdapter(getDataCmd);
getDataAdapter.Fill(transactionDT);
DataTable transactionTypeDT = new TransactionTypeTableAdapter().GetData();
List<KeyValuePair<int, string>> transactionTypes = new List<KeyValuePair<int, string>>();
foreach (DataRow row in transactionTypeDT.Rows)
{
transactionTypes.Add(new KeyValuePair<int, string>(
row.Field<int>("TransactionTypeID"),
row.Field<string>("Name")));
}
ViewBag.transactionTypes = transactionTypes;
}
catch (Exception ex)
{
Log.ErrorLog(ex.Message);
return View("Error");
}
return View(transactionDT);
}
