public RentalStatus Get(int id) { RentalStatus item = null; string query = String.Format("SELECT * FROM {0} WHERE transaction_id = {1}", TableName, id); MySqlCommand cmd = new MySqlCommand(query, Database.Connection); MySqlDataReader dataReader = cmd.ExecuteReader(); if (dataReader.Read()) { item = new RentalStatus(dataReader); } dataReader.Close(); return(item); }
private void LoadTransactionData(Transaction item) { //Rental textBox1.Text = item.Id.ToString(); textBox2.Text = item.RentalID.ToString(); textBox3.Text = item.StartDate.ToString("dd-MM-yyyy"); DateTime endDate = item.StartDate.Date.AddDays(item.Duration); textBox4.Text = endDate.ToString("dd-MM-yyyy"); textBox5.Text = item.Duration.ToString(); numericUpDown1.Value = item.TypeRate * item.Duration; //Customer textBox7.Text = item.CustomerID.ToString(); var cusList = Customer.Where(String.Format("id = {0}", item.CustomerID), 0, 1); if (cusList.Any()) { textBox8.Text = cusList[0].Name; textBox9.Text = cusList[0].Ic; } //Locker textBox10.Text = item.LockerID.ToString(); var lockerList = Locker.Where(String.Format("id = {0}", item.LockerID), 0, 1); if (lockerList.Any()) { textBox11.Text = lockerList[0].Code; var cabList = Cabinet.Where(String.Format("id = {0}", lockerList[0].CabinetID), 0, 1); if (cabList.Any()) { textBox12.Text = cabList[0].Code; } } textBox13.Text = item.TypeName; numericUpDown2.Value = item.TypeRate; //End Rental var tempReturnDate = item.ReturnDate.ToString("dd-MM-yyyy"); //If the return date is not initialized, do not show the date. if (tempReturnDate != "01-01-0001") { textBox15.Text = tempReturnDate; } textBox16.Text = item.OverdueTime.ToString(); numericUpDown3.Value = item.Fine; //RentalStatus if (item.OverdueTime > 0) { checkBox1.Checked = true; } List <RentalStatus> statusList = RentalStatus.Where(String.Format("transaction_id = {0}", item.Id), 0, 10); var statuses = from selected in statusList where selected.StatusId.ToString().Contains("3") select selected; if (statuses.Any()) { checkBox2.Checked = true; } statuses = from selected in statusList where selected.StatusId.ToString().Contains("4") select selected; if (statuses.Any()) { checkBox3.Checked = true; } }
private void Button3_Click(object sender, EventArgs e) //Export button { string month = dateTimePicker1.Value.ToString("MM"); string longMonth = dateTimePicker1.Value.ToString("MMM"); string fullMonth = dateTimePicker1.Value.ToString("MMMM"); string year = dateTimePicker1.Value.ToString("yyyy"); var result = MessageBox.Show("Do you want to export transactions for " + fullMonth + " " + year + "?\n\n" + "Note:\n1. Exported transactions will be deleted from the database. \n2. If you did not generate the report for " + fullMonth + " " + year + ", please generate and export it first.", "Export Transactions", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { if (dateTimePicker1.Value.Month == DateTime.Now.Month && dateTimePicker1.Value.Year == DateTime.Now.Year) { MessageBox.Show("Export Error: You can't export transactions for this month \n(" + fullMonth + " " + year + ").", "Export Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string defaultFileName = String.Format("EXPORT_TRANSACTIONS_{0}_{1}_{2}", longMonth, year, DateTime.Now.ToString("ddMMyyyy_HHmmss")); string dateCond = String.Format("return_date LIKE '{0}-{1}-%'", year, month); var transactionsList = Transaction.Where(dateCond, 0, 2147483467); if (!transactionsList.Any()) { MessageBox.Show("Error: Empty Record.\nThere are no records in " + fullMonth + " " + year + ".", "Empty Record", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } SaveFileDialog sf = new SaveFileDialog { FileName = defaultFileName, Filter = "Excel Workbook (.xlsx) |*.xlsx", Title = "Export Transactions as", FilterIndex = 1 }; if (sf.ShowDialog() == DialogResult.OK) { string savePath = Path.GetDirectoryName(sf.FileName); string fileName = Path.GetFileName(sf.FileName); string saveFile = Path.Combine(savePath, fileName); try { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Transactions"); ws.Cell(1, 1).Value = String.Format("Transactions in {0} {1}", fullMonth, year); ws.Cell(2, 1).InsertTable(transactionsList); DataTable statusTable = new DataTable(); statusTable.Columns.Add("Transaction ID"); statusTable.Columns.Add("Status ID"); foreach (Transaction t in transactionsList) { var statusList = RentalStatus.Where(String.Format("transaction_id = {0}", t.Id), 0, 5); foreach (RentalStatus s in statusList) { statusTable.Rows.Add(s.TransactionId, s.StatusId); } } ws.Cell(ws.LastRowUsed().RowNumber() + 2, 1).InsertTable(statusTable); workbook.SaveAs(saveFile); //Save the file string escapedSaveFile = saveFile.Replace(@"\", @"\\"); var log = new AccessLog() //AccessLog for export { User = Login.Username, Action = "Export", Item = "Transaction", Description = "Month of Transaction Return Date: " + fullMonth + " " + year + "; Path: " + escapedSaveFile }; log.Insert(); foreach (Transaction t in transactionsList) { var statusList = RentalStatus.Where(String.Format("transaction_id = {0}", t.Id), 0, 5); foreach (RentalStatus s in statusList) { s.Delete(); log = new AccessLog() { User = "******", Action = "Delete from database", ItemId = s.TransactionId + ", " + s.StatusId, Item = "Rental Status", Description = "Exported in " + fileName }; log.Insert(); } t.Delete(); log = new AccessLog() { User = "******", Action = "Delete from database", Item = "Transaction", Description = "Month of Transaction Return Date: " + fullMonth + " " + year + "; exported in " + fileName }; log.Insert(); } } catch (Exception exception) { MessageBox.Show(exception.Message); return; } _exportComplete = true; this.Close(); } } }
private void Button10_Click(object sender, EventArgs e) //Next button { if (numericUpDown9.Value < numericUpDown8.Value) { MessageBox.Show("Input Error: Insufficient Payment." + Environment.NewLine + "Payment amount must be equal or higher than total price.", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } numericUpDown10.Value = numericUpDown9.Value - numericUpDown8.Value; button11.Hide(); button10.Hide(); button12.Show(); //Delete Rental Log var log = new AccessLog() { User = Login.Username, Action = "End", Item = "Rental", ItemId = selectedRental.Id.ToString() }; log.Insert(); log = new AccessLog() { User = "******", Action = "Delete from database", Item = "Rental", ItemId = selectedRental.Id.ToString() }; log.Insert(); var transactionItem = Transaction.Where(String.Format("rental_id = {0}", selectedRental.Id), 0, 1); transactionItem[0].ReturnDate = DateTime.Now.Date; transactionItem[0].OverdueTime = _overdueDays; transactionItem[0].Fine = numericUpDown8.Value; transactionItem[0].Save(); log = new AccessLog() { User = "******", Action = "Update", Item = "Transaction", ItemId = transactionItem[0].Id.ToString(), Description = "Return Date: " + transactionItem[0].ReturnDate.ToString("dd-MM-yyyy") + "; Overdue Time: " + _overdueDays + " day; Fine: " + numericUpDown8.Value }; log.Insert(); //Insert transaction return status RentalStatus transReturnStatus = new RentalStatus(); if (_keyLostFineAdded) { transReturnStatus.TransactionId = transactionItem[0].Id; transReturnStatus.StatusId = 3; transReturnStatus.Insert(); log = new AccessLog() { User = "******", Action = "Add", Item = "Rental Status", ItemId = transReturnStatus.TransactionId + ", " + transReturnStatus.StatusId, Description = "Return Status: Key Lost" }; log.Insert(); } if (_lockerDamagedFineAdded) { transReturnStatus.TransactionId = transactionItem[0].Id; transReturnStatus.StatusId = 4; transReturnStatus.Insert(); log = new AccessLog() { User = "******", Action = "Add", Item = "Rental Status", ItemId = transReturnStatus.TransactionId + ", " + transReturnStatus.StatusId, Description = "Return Status: Locker Damaged" }; log.Insert(); } if (checkBox1.Checked) { transReturnStatus.TransactionId = transactionItem[0].Id; transReturnStatus.StatusId = 2; transReturnStatus.Insert(); log = new AccessLog() { User = "******", Action = "Add", Item = "Rental Status", ItemId = transReturnStatus.TransactionId + ", " + transReturnStatus.StatusId, Description = "Return Status: Overdue" }; log.Insert(); } //Release the occupied / overdue locker string lockerStatus = ""; var locker = new Locker(); locker = locker.Get(selectedRental.LockerID); if (locker.IsOverdued()) { lockerStatus = "Overdue"; } else { lockerStatus = "Occupied"; } if (!_keyLostFineAdded && !_lockerDamagedFineAdded) { locker.Reset(); log = new AccessLog() { User = "******", Action = "Update", Item = "Locker", ItemId = locker.Id.ToString(), Description = "Code: " + locker.Code + "; Status: " + lockerStatus + " to Available" }; log.Insert(); //Check is the cabinet full, if yes, set to available var cabinet = new Cabinet(); cabinet = cabinet.Get(locker.CabinetID); if (cabinet.IsFull()) { cabinet.Restore(); log = new AccessLog() { User = "******", Action = "Update", Item = "Cabinet", ItemId = cabinet.Id.ToString(), Description = "Code: " + cabinet.Code + "; Status: Full to Available" }; log.Insert(); } } else { locker.NotAvailable(); string reason = ""; if (_keyLostFineAdded && !_lockerDamagedFineAdded) { reason += "Key Lost"; } else if (!_keyLostFineAdded && _lockerDamagedFineAdded) { reason += "Locker Damaged"; } else { reason += "Key Lost & Locker Damaged"; } log = new AccessLog() { User = "******", Action = "Disable", Item = "Locker", ItemId = locker.Id.ToString(), Description = "Code: " + locker.Code + "; Status: " + lockerStatus + " to Not Available; Reason: " + reason }; log.Insert(); } //Delete the rental selectedRental.Delete(); }