internal static DataSet GetSyncData(DateTime toDate)
{
MealSetTableAdapter mealSetTA = new MealSetTableAdapter();
ScheduleTableAdapter scheduleTA = new ScheduleTableAdapter();
ScheduleMealSetDetailTableAdapter scheduleMealSetDetailTA = new ScheduleMealSetDetailTableAdapter();
ServingTimeTableAdapter servingTimeTA = new ServingTimeTableAdapter();
TransactionHistoryTableAdapter transactionHistoryTA = new TransactionHistoryTableAdapter();
TransactionTypeTableAdapter transactionTypeTA = new TransactionTypeTableAdapter();
UserInfoTableAdapter userInfoTA = new UserInfoTableAdapter();
UserTypeTableAdapter userTypeTA = new UserTypeTableAdapter();
DateTime minDate = (DateTime)SqlDateTime.MinValue;
DataSet ds = new DataSet();
ds.Tables.Add(userTypeTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(userInfoTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(mealSetTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(servingTimeTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(scheduleTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(scheduleMealSetDetailTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(transactionTypeTA.GetDataByDate(minDate, toDate));
ds.Tables.Add(transactionHistoryTA.GetDataByDate(minDate, toDate));
return ds;
}
public ActionResult EditTransaction(string TransID)
{
TransactionHistoryTableAdapter adapter = new TransactionHistoryTableAdapter();
EditTransaction model = new EditTransaction();
TransactionTypeTableAdapter tranTypeAdapter = new TransactionTypeTableAdapter();
DataTable tranTypeDT = tranTypeAdapter.GetData();
List<SelectListItem> items = new List<SelectListItem>();
foreach (DataRow row in tranTypeDT.Rows)
{
items.Add(new SelectListItem { Text = row["Name"].ToString(), Value = row["TransactionTypeID"].ToString() });
}
ViewData["TransType"] = items;
try
{
int transactionID = int.Parse(TransID);
DataTable result = adapter.GetDataByKey(transactionID);
if (result.Rows.Count != 0)
{
model.Username = result.Rows[0].Field<string>("Username");
model.TransactionTypeID = result.Rows[0].Field<int>("TransactionTypeID");
model.Value = result.Rows[0].Field<int>("Value");
model.TransactionContent = result.Rows[0].Field<string>("TransactionContent");
model.ScheduleMealSetDetailID = result.Rows[0].Field<int?>("ScheduleMealSetDetailID");
model.IsAuto = result.Rows[0].Field<Boolean>("IsAuto");
model.InsertedDate = result.Rows[0].Field<DateTime>("InsertedDate");
model.UpdatedBy = result.Rows[0].Field<string>("UpdatedBy");
model.LastUpdated = result.Rows[0].Field<DateTime>("LastUpdated");
model.TransactionHistoryID = result.Rows[0].Field<int>("TransactionHistoryID");
}
}
catch (Exception ex)
{
Log.ErrorLog(ex.Message);
}
return View(model);
}
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);
}
public ActionResult EditTransaction(EditTransaction model)
{
TransactionTypeTableAdapter tranTypeAdapter = new TransactionTypeTableAdapter();
DataTable tranTypeDT = tranTypeAdapter.GetData();
List<SelectListItem> items = new List<SelectListItem>();
foreach (DataRow row in tranTypeDT.Rows)
{
items.Add(new SelectListItem { Text = row["Name"].ToString(), Value = row["TransactionTypeID"].ToString() });
}
ViewData["TransType"] = items;
if (!ModelState.IsValid)
{
return View(model);
}
int transactionHistoryID = model.TransactionHistoryID;
string username = model.Username;
int transactionTypeID = model.TransactionTypeID;
int value = model.Value;
string transactionContent = model.TransactionContent;
int? scheduleMealSetDetailID = model.ScheduleMealSetDetailID;
Boolean isAuto = model.IsAuto;
DateTime insertedDate = model.InsertedDate;
string updatedBy = model.UpdatedBy;
DateTime lastUpdated = DateTime.Now;
TransactionHistoryTableAdapter transAdapter = new TransactionHistoryTableAdapter();
try
{
transAdapter.UpdateTransactionHistory(username, transactionTypeID, value, transactionContent
, scheduleMealSetDetailID, isAuto, insertedDate, AccountInfo.GetUserName(Request), lastUpdated
, transactionHistoryID);
XmlSync.SaveTransactionHistoryXml(transactionHistoryID, username, transactionTypeID, value, transactionContent
, scheduleMealSetDetailID, isAuto, insertedDate, AccountInfo.GetUserName(Request), lastUpdated, null);
}
catch (Exception ex)
{
Log.ErrorLog(ex.Message);
}
return RedirectToAction("ListTransaction", "Transaction");
}