コード例 #1
0
ファイル: DataAccess.cs プロジェクト: Grania/ctm-project
        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;
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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");
        }