public void populateTable(string toPopulateWith,DateTime date) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQL MySQLLocationHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection locationConnection = MySQLLocationHandle.Connect(); string datestring = date.ToString("yyyy-MM-dd"); MySqlDataReader dataReader = MySQLHandle.Select("SELECT * FROM shipping_log WHERE `date_delivered` LIKE '"+datestring+"' AND run_driver='" + toPopulateWith + "' ORDER BY `order` ASC;", sqlReader); int shippingtotal = 0; int grosstrucktotal = 0; int nettrucktotal = 0; for (int rowCounter = 0; dataReader.Read(); rowCounter++) { string customerName = dataReader.GetString(3); string invoice = dataReader.GetString(4); MySqlDataReader locationDataReader = MySQLHandle.Select("SELECT `INV_CITY`, `INV_STATE`,`INV_SHIPPING`,`INV_SALES_AMOUNT`,`INV_COST`,`INV_CUSTOMER_NAME` FROM `invoice_data` WHERE `INV_NUMBER`='" + invoice + "';", locationConnection); //Console.WriteLine("SELECT `INV_CITY`, `INV_STATE` FROM `invoice_data` WHERE `INV_NUMBER`='" + invoice + "';"); locationDataReader.Read(); string location = ""; string shippingamount=""; string grosstruckamount=""; string netontruckamount=""; if (locationDataReader.HasRows == true) { location = locationDataReader.GetString(0) + ", " + locationDataReader.GetString(1); shippingamount = DataFormat.FormatMoneyToString(locationDataReader.GetInt32(2)); grosstruckamount = DataFormat.FormatMoneyToString(locationDataReader.GetInt32(3)); netontruckamount = DataFormat.FormatMoneyToString(locationDataReader.GetInt32(3) + locationDataReader.GetInt32(2) - locationDataReader.GetInt32(4)); grosstrucktotal += locationDataReader.GetInt32(3); shippingtotal += locationDataReader.GetInt32(2); nettrucktotal += locationDataReader.GetInt32(3) + locationDataReader.GetInt32(2) - locationDataReader.GetInt32(4); if (customerName == "") { customerName = locationDataReader.GetString(5); } } locationDataReader.Close(); string dateDelivered = dataReader.GetString(1); string id = dataReader.GetString(0); addRow(customerName, invoice, location, dateDelivered, id,grosstruckamount,shippingamount,netontruckamount); } dataReader.Close(); sqlReader.Close(); locationConnection.Close(); for (int rowNumber = 0; rowNumber < dataGrid.Rows.Count; rowNumber++) { dataGrid.Rows[rowNumber].HeaderCell.Value = (rowNumber + 1).ToString(); } for (int i = 0; i < dataGrid.Rows.Count; i++) { } this.Parent.Controls["textShipping"].Text = DataFormat.FormatMoneyToString(shippingtotal); this.Parent.Controls["textGOT"].Text = DataFormat.FormatMoneyToString(grosstrucktotal); this.Parent.Controls["textNOT"].Text = DataFormat.FormatMoneyToString(nettrucktotal); }
public void populateTable(string toPopulateWith,DateTime date) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQL MySQLLocationHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection locationConnection = MySQLLocationHandle.Connect(); string datestring = date.ToString("yyyy-MM-dd"); MySqlDataReader dataReader = MySQLHandle.Select("SELECT * FROM shipping_log WHERE `date_delivered` LIKE '"+datestring+"' AND run_driver='" + toPopulateWith + "' ORDER BY `order` ASC;", sqlReader); for (int rowCounter = 0; dataReader.Read(); rowCounter++) { string customerName = dataReader.GetString(3); string invoice = dataReader.GetString(4); MySqlDataReader locationDataReader = MySQLHandle.Select("SELECT `INV_CITY`, `INV_STATE` FROM `invoice_data` WHERE `INV_NUMBER`='" + invoice + "';", locationConnection); Console.WriteLine("SELECT `INV_CITY`, `INV_STATE` FROM `invoice_data` WHERE `INV_NUMBER`='" + invoice + "';"); locationDataReader.Read(); locationDataReader.Close(); string location = ""; if (locationDataReader.HasRows == true) { location = locationDataReader.GetString(0) + ", " + locationDataReader.GetString(1); } string dateDelivered = dataReader.GetString(1); string id = dataReader.GetString(0); addRow(customerName, invoice, location, dateDelivered, id); } dataReader.Close(); sqlReader.Close(); locationConnection.Close(); }
public addTHRyanDriver() { InitializeComponent(); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT `DRV_NAME` FROM drivers;", sqlReader); for(int rowCounter=0; dataReader.Read(); rowCounter++) { driverName.Items.Add(new Item(dataReader.GetString(0), rowCounter)); } }
public customDataGridShippingLog() { InitializeComponent(); editing = false; MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); sqlReader = MySQLHandle.Connect(); autoCompleteCollection = MySQLHandle.returnAutoComplete(); for (int rowNumber = 0; rowNumber < dataGrid.Rows.Count; rowNumber++) { dataGrid.Rows[rowNumber].HeaderCell.Value = (rowNumber + 1).ToString(); } }
public customDataGridShippingLog() { InitializeComponent(); MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); sqlReader = MySQLHandle.Connect(); for (int rowNumber = 0; rowNumber < dataGrid.Rows.Count; rowNumber++) { dataGrid.Rows[rowNumber].HeaderCell.Value = (rowNumber + 1).ToString(); } string connStr = "Server=" + GlobalVar.sqlhost + ";Uid=" + GlobalVar.sqlusername + ";Pwd=;Database=" + GlobalVar.sqldatabase + ";"; connection = new MySqlConnection(connStr); connection.Open(); }
public void refreshDriverInfo(DateTime date) { Driver driver = new Driver(); driver.name = driverLabel.Text.ToString(); driver.date = date; MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQLHandle.addDriverHourLogger(driver, sqlReader); driver=MySQLHandle.getDriverHourLogger(driver,date, sqlReader); billedhoursTextbox.Text=driver.hours.ToString("N"+2); billedmilesTextbox.Text = driver.miles.ToString("N" + 2); if (driver.reason != "") { reviewCheckbox.Checked = true; weeklyReviewTextBox.Text = driver.reason; } }
void bw3_DoWork(object sender, DoWorkEventArgs e) { DateTime viewdate = currentLogDate.Value; try { //get invoices for current day MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); string selectquery = "SELECT `invoice` FROM shipping_log WHERE `date_delivered` LIKE '" + currentLogDate.Value.ToString("yyyy-MM-dd") + "';"; MySqlDataReader dataReader = MySQLHandle.Select(selectquery, sqlReader); List<int> invoices = new List<int>(); for (int i = 0; dataReader.Read(); i++) { invoices.Add(dataReader.GetInt32(0)); } dataReader.Close(); sqlReader.Close(); rrsdatareader = new RRSDataReader(GlobalVar.sqlsettings.RRSHeaderFile, GlobalVar.sqlsettings.RRSLinesFile); rrsdatareader.ReadInvoices(viewdate); List<Invoice> filteredinvoices = rrsdatareader.FilterInvoices(viewdate, invoices); //List<LineItem> filteredlineitems = rrsdatareader.FilterLineItems(filteredinvoices); List<Invoice> newfilteredinvoices = new List<Invoice>(filteredinvoices); mysql_invoices = new MySQL_Invoices(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); newfilteredinvoices = mysql_invoices.AddInvoices(newfilteredinvoices, false); mysql_invoices.UpdateInvoices(filteredinvoices, viewdate, true); dataReader.Close(); sqlReader.Close(); MySQLHandle.Disconnect(); for (int i = 0; i < newfilteredinvoices.Count; i++) { MySqlConnection sqlWriter = MySQLHandle.Connect(); String updatecmd = "UPDATE `shipping_log` SET customer_name='" + newfilteredinvoices[i].customername + "' WHERE `invoice`=" + newfilteredinvoices[i].number + ";"; MySQLHandle.Update(updatecmd, sqlWriter); MySQLHandle.Disconnect(); } } catch { } }
private void addDriverButton_Click(object sender, EventArgs e) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); string stringdrivername = driverName.Text; DateTimePicker picker = (DateTimePicker)this.Parent.Parent.Parent.Parent.Controls["logDate"]; GregorianCalendar gc = new GregorianCalendar(); int weekno = gc.GetWeekOfYear(picker.Value, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday); string stringdate = "" + picker.Value.Year + weekno; string insertstring="INSERT INTO `th_ryan_invoices` (`id`, `billed_hours`, `billed_miles`, `cost`, `review`, `driver`,`date`) VALUES (NULL, '', '', '', '', '"+stringdrivername+"','"+stringdate+"');"; FlowLayoutPanel tabcontrol = (FlowLayoutPanel)this.Parent.Parent.Controls[0]; WeeklyLog newDriver = new WeeklyLog(stringdrivername,picker.Value); tabcontrol.Controls.Add(newDriver); FlowLayoutPanel newlayout = new FlowLayoutPanel(); tabcontrol.Controls.SetChildIndex(newDriver, tabcontrol.Controls.Count - 2); tabcontrol = newlayout; MySQLHandle.Insert(insertstring, sqlReader); MySQLHandle.Disconnect(); driverName.Text = ""; }
static void Main() { try { //GlobalVar.authenticated = true; StreamReader sr = new StreamReader("RRS.conf"); GlobalVar.sqlhost = sr.ReadLine(); GlobalVar.sqlport = Int32.Parse(sr.ReadLine()); GlobalVar.sqldatabase = sr.ReadLine(); GlobalVar.sqlusername = sr.ReadLine(); sr.Close(); } catch { MessageBox.Show("Unable to load configuration file RRS.conf"); return; } try { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); if (sqlReader == null) { MessageBox.Show("Unable to connect to database"); return; } MySQLHandle.Disconnect(); } catch { MessageBox.Show("Unable to connect to database"); return; } try { MySQL_Settings mysql_settings = new MySQL_Settings(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); GlobalVar.sqlsettings = mysql_settings.GetSettings(); } catch { } Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new mainWindow()); }
private void refreshData() { if (tabControl1.SelectedIndex == 0) { clearDrivers(); string date = "" + logDate.Value.ToString("yyyy-MM-dd"); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT `run_driver` FROM `shipping_log` WHERE `date_delivered` LIKE '" + date + "'", sqlReader); List<String> drivers = new List<String>(); for (int rowCounter = 0; dataReader.Read(); rowCounter++) { drivers.Add(dataReader[0].ToString()); } drivers = drivers.Distinct().ToList(); for (int i = 0; i < drivers.Count; i++) { addDriverToDriverHourLog(drivers[i]); } dataReader.Close(); sqlReader.Close(); MySQLHandle.Disconnect(); } if (tabControl1.SelectedIndex == 1) { populateTYRyan(); } }
private void printLogButton_Click_1(object sender, EventArgs e) { StreamWriter sw=null; try { string currentdirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); currentdirectory = currentdirectory.Replace("\\", "/"); currentdirectory = Uri.EscapeUriString(currentdirectory); string tovisit = "file:///" + currentdirectory + "/" + "toprint.html"; string localPath = new Uri(tovisit).LocalPath; sw = new StreamWriter(localPath, false); sw.WriteLine("<body style=\"font-family:calibri\">"); sw.WriteLine("<div style=\"width:21.59cm\">"); sw.WriteLine("<strong style='font-size:24px'>Driver log for" + logDate.Value.Date.ToShortDateString() + "</strong><br>"); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); foreach (driverInstanceDriverHourLog currentInstance in driversContainer.Controls) { int hourrate = 0; int milerate = 0; try { string selectcmd="SELECT `DRV_HOUR_RATE`, `DRV_FUEL_SURCH` FROM drivers WHERE `DRV_NAME` LIKE '" + currentInstance.getDriverName() + "';"; MySqlDataReader dataReader = MySQLHandle.Select(selectcmd, sqlReader); dataReader.Read(); if (dataReader.HasRows == true) { hourrate = Convert.ToInt32(dataReader[0].ToString()); milerate = Convert.ToInt32(dataReader[1].ToString()); } dataReader.Close(); } catch { } sw.WriteLine("<div style='font-size:18px; font-weight:bold'>" + currentInstance.getDriverName() + " - Hours: " + currentInstance.billedhoursTextbox.Text + " Miles: " + currentInstance.billedmilesTextbox.Text + " Gross on Truck: " + currentInstance.textGOT.Text + " Net on Truck: " + currentInstance.textNOT.Text); List<string> customers = new List<string>(); List<string> towrite = new List<string>(); towrite.Add("<table border=1'>"); towrite.Add("<tr><td style='font-weight:bold'>CUSTOMER NAME</td><td style='font-weight:bold'>INVOICE</td><td style='font-weight:bold'>LOCATION</td><td style='font-weight:bold'>GROSS ON TRUCK</td><td style='font-weight:bold'>COG ON TRUCK</td><td style='font-weight:bold'>NET ON TRUCK</td></tr>"); int grosstotal = 0; foreach (DataGridViewRow currentRow in currentInstance.getDataGrid()) { int invoicenumber = -1; try { invoicenumber = Convert.ToInt32(currentRow.Cells[1].Value.ToString()); } catch { } int salesamount=0; int shipping=0; int tax=0; int total=0; int cost=0; int net = 0; string stringsalesamount = ""; string stringshipping = ""; string stringtax = ""; string stringtotal = ""; string stringcost = ""; string stringnet = ""; try { MySqlDataReader dataReader2 = MySQLHandle.Select("SELECT `INV_SALES_AMOUNT`, `INV_SHIPPING`, `INV_TAX`,`INV_TOTAL`,`INV_COST` FROM invoice_data WHERE `INV_NUMBER`=" + invoicenumber+ ";", sqlReader); dataReader2.Read(); if (dataReader2.HasRows) { salesamount = Convert.ToInt32(dataReader2[0].ToString()); grosstotal += salesamount; shipping = Convert.ToInt32(dataReader2[1].ToString()); tax = Convert.ToInt32(dataReader2[2].ToString()); total = Convert.ToInt32(dataReader2[3].ToString()); cost = Convert.ToInt32(dataReader2[4].ToString()); net = Convert.ToInt32(salesamount - cost); stringsalesamount = DataFormat.FormatMoneyToString(salesamount); stringshipping = DataFormat.FormatMoneyToString(shipping); stringtax = DataFormat.FormatMoneyToString(tax); stringtotal = DataFormat.FormatMoneyToString(total); stringcost = DataFormat.FormatMoneyToString(cost); stringnet = DataFormat.FormatMoneyToString(net); } dataReader2.Close(); } catch { } customers.Add(currentRow.Cells[0].Value.ToString()); towrite.Add("<tr><td>" + currentRow.Cells[0].Value.ToString() + "</td><td>" + currentRow.Cells[1].Value.ToString() + "</td><td>" + currentRow.Cells[3].Value.ToString() + "</td><td>" + stringsalesamount + "</td><td>" + stringcost + "</td><td>" + stringnet + "</td></tr>"); } customers=customers.Distinct().ToList(); int stopcount = customers.Count; double hours = currentInstance.getHours(); double miles = currentInstance.getMiles(); double truckcost = hourrate * hours + milerate * miles; double costperstop = (double)truckcost / (double)stopcount; double shippercentsales = (double)truckcost / (double)grosstotal; string stringtruckcost = DataFormat.FormatMoneyToString((int)truckcost); string stringcostperstop = DataFormat.FormatMoneyToString((int)costperstop); string stringshippercentsales = shippercentsales.ToString("P"); sw.WriteLine("<div style='font-size:18px; font-weight:bold'>Cost of Truck: " + stringtruckcost + " Cost per Stop: " + stringcostperstop + " Shipping as % of Sales: " + stringshippercentsales); foreach (string output in towrite) { sw.WriteLine(output); } sw.WriteLine("</table><br><br>"); sw.WriteLine("</div>"); } sqlReader.Close(); MySQLHandle.Disconnect(); sw.WriteLine("</div></body>"); sw.Close(); Console.WriteLine(tovisit); htmlParserBrowser.Navigate(tovisit); //htmlParserBrowser.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(htmlParserBrowser_DocumentCompleted); //htmlParserBrowser.ShowPrintDialog(); } catch { if (sw != null) { sw.Close(); } } }
private void populateTYRyan() { THRyanFlowPanel.Controls.Clear(); GregorianCalendar gc = new GregorianCalendar(); int weekno = gc.GetWeekOfYear(logDate.Value, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); string stringdate = logDate.Value.Year.ToString() + weekno.ToString(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT * FROM `th_ryan_invoices` WHERE date = " + stringdate + ";", sqlReader); Console.WriteLine(weekno.ToString()); for (int rowCounter = 0; dataReader.Read(); rowCounter++) { string id = dataReader.GetString(0); string billedHours = dataReader.GetString(1); string billedMiles = dataReader.GetString(2); string cost = dataReader.GetString(3); string review = dataReader.GetString(4); string driver = dataReader.GetString(5); //MySQLHandle.Insert("INSERT INTO `rrs`.`th_ryan_invoies` (`id`, `billed_hours`, `billed_miles`, `cost`, `review`, `driver`) VALUES (NULL, '', '', '', '', '');", sqlReader); addDriverToTHRyan(driver,logDate.Value); } dataReader.Close(); addTHRyanDriver1 = new addTHRyanDriver(); THRyanFlowPanel.Controls.Add(addTHRyanDriver1); }
private void populateAddDriverSelect() { selectDriverBox.Items.Clear(); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT `DRV_NAME`, `DRV_NUMBER` FROM drivers;", sqlReader); while (dataReader.Read()) { selectDriverBox.Items.Add(new Item(dataReader.GetString(0), dataReader.GetInt32(1))); } selectDriverBox.SelectedIndex = 0; sqlReader.Close(); }
public WeeklyLog(string driverName,DateTime date) { InitializeComponent(); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); GregorianCalendar gc = new GregorianCalendar(); int weekno = gc.GetWeekOfYear(date, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday); string stringdate = "" + date.Year + weekno; //MySQLHandle.Insert("INSERT INTO `rrs`.`th_ryan_invoies` (`id`, `billed_hours`, `billed_miles`, `cost`, `review`, `driver`) VALUES (NULL, 0, 0, 0, '', '');", sqlReader); globalDriverName=driverName; driverNameTHRyan.Text = driverName; string command="SELECT * FROM `th_ryan_invoices` WHERE `driver` LIKE '" + driverName + "' AND `date` LIKE '" + stringdate + "';"; MySqlDataReader dataReader = MySQLHandle.Select(command, sqlReader); dataReader.Read(); if (dataReader.HasRows == true) { string id = dataReader.GetString(0); int billedHours = dataReader.GetInt32(1); int billedMiles = dataReader.GetInt32(2); int cost = dataReader.GetInt32(3); string review = dataReader.GetString(4); string driver = dataReader.GetString(5); billedhoursTextbox.Text = billedHours.ToString(); billedmilesTextbox.Text = billedMiles.ToString(); costTextBox.Text = cost.ToString(); if (review != "") { weeklyReviewTextBox.Text = review; reviewCheckbox.Checked = true; } else { reviewCheckbox.Checked = false; } int diff = date.DayOfWeek - DayOfWeek.Sunday; if (diff < 0) { diff += 7; } date = date.AddDays(-1 * diff); string date1=date.ToString("yyyy-MM-dd"); date=date.AddDays(1); string date2 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); string date3 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); string date4 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); string date5 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); string date6 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); string date7 = date.ToString("yyyy-MM-dd"); date = date.AddDays(1); command = "SELECT * FROM `driver_log` WHERE `driver` LIKE '" + driverName + "' AND (`date` LIKE '" + date1 + "' OR `date` LIKE '" + date2 + "' OR `date` LIKE '" + date3 + "' OR `date` LIKE '" + date4+ "' OR `date` LIKE '" + date5+ "' OR `date` LIKE '" + date6+ "' OR `date` LIKE '" + date7 + "');"; dataReader.Close(); dataReader = MySQLHandle.Select(command, sqlReader); int hours=0; int miles=0; for (int i = 0; dataReader.Read(); i++) { hours+=dataReader.GetInt32(3); miles+=dataReader.GetInt32(4); } dataReader.Close(); loggedhoursTextBox.Text = hours.ToString(); loggedmilesTextBox.Text = miles.ToString(); } dataReader.Close(); }
private void updateDriver() { string driverName = "" + this.Controls[0].Text; DateTimePicker temp = (DateTimePicker)this.Parent.Parent.Parent.Parent.Controls["logDate"]; DateTime logDate = temp.Value; GregorianCalendar gc = new GregorianCalendar(); int weekno = gc.GetWeekOfYear(logDate, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday); string stringdate = DateTime.Now.Year.ToString() + weekno.ToString(); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlWriter = MySQLHandle.Connect(); string command = "UPDATE `th_ryan_invoices` SET `cost`="+costTextBox.Text+" ,`billed_hours`="+billedhoursTextbox.Text+",`billed_miles`="+billedmilesTextbox.Text+", review='"+weeklyReviewTextBox.Text+"' WHERE `driver` LIKE '" + driverName + "' AND `date` LIKE '" + stringdate + "';"; MySQLHandle.Update(command, sqlWriter); MySQLHandle.Disconnect(); }
private void populateViewLogTable() { try { viewDriverLogDataGrid.clearRows(); int endRowCounter = 0; string currentDate = currentLogDate.Value.ToString("yyyy-MM-dd"); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySQL MySQLHandle2 = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader2 = MySQLHandle2.Connect(); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT * FROM shipping_log WHERE date_delivered='" + currentDate + "' ORDER BY `order` ASC;", sqlReader); for (int rowCounter = 0; dataReader.Read(); rowCounter++) { endRowCounter++; string id = dataReader.GetString(0); string dateDelivered = dataReader.GetString(1); string runDriver = dataReader.GetString(2); string customerName = dataReader.GetString(3); string invoice = dataReader.GetString(4); if (customerName == "" && invoice != "") { try { MySqlDataReader dataReader2 = MySQLHandle2.Select("SELECT `INV_CUSTOMER_NAME` FROM invoice_data WHERE `INV_NUMBER`=" + invoice + ";", sqlReader2); dataReader2.Read(); if (dataReader2.HasRows == true) { customerName = dataReader2.GetString(0); } dataReader2.Close(); } catch { } } viewDriverLogDataGrid.addRow(dateDelivered, runDriver, customerName, invoice, id); } if (viewDriverLogDataGrid.returnRows().Count == 0) { viewDriverLogDataGrid.addBlankRow(currentLogDate.Value); } dataReader.Close(); sqlReader.Close(); sqlReader2.Close(); } catch { } }
private void button2_Click(object sender, EventArgs e) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQLHandle.Insert("INSERT INTO `rrs`.`drivers` (`DRV_NUMBER`, `DRV_NAME`, `DRV_FLAT`, `DRV_TRAILER`, `DRV_HOUR_RATE`, `DRV_OVERTIME`, `DRV_FUEL_SURCH`, `DRV_MAINT_SURCH`, `DRV_NOTES`) VALUES (NULL, '" + driverNameTextbox.Text + "', '1', '0', '" + hourlyRateTextbox.Text + "', '', '" + mileageRateTextbox.Text + "', '0', '0');", sqlReader); sqlReader.Close(); populateAddDriverSelect(); }
private void dataGrid_DragDrop(object sender, DragEventArgs e) { // The mouse locations are relative to the screen, so they must be // converted to client coordinates. Point clientPoint = dataGrid.PointToClient(new Point(e.X, e.Y)); // Get the row index of the item the mouse is below. rowIndexOfItemUnderMouseToDrop = dataGrid.HitTest(clientPoint.X, clientPoint.Y).RowIndex; // If the drag operation was a move then remove and insert the row. if (e.Effect == DragDropEffects.Move) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); DataGridViewRow rowToMove = e.Data.GetData( typeof(DataGridViewRow)) as DataGridViewRow; if(dataGrid.Rows[rowIndexOfItemUnderMouseToDrop].IsNewRow==false) { dataGrid.Rows.RemoveAt(rowIndexFromMouseDown); dataGrid.Rows.Insert(rowIndexOfItemUnderMouseToDrop, rowToMove); for (int rowCounter = 0; rowCounter < dataGrid.RowCount; rowCounter++) { MySQLHandle.updateDriverLogOrder(dataGrid.Rows[rowCounter], sqlReader); } } for (int rowNumber = 0; rowNumber < dataGrid.Rows.Count; rowNumber++) { dataGrid.Rows[rowNumber].HeaderCell.Value = (rowNumber + 1).ToString(); } sqlReader.Close(); MySQLHandle.Disconnect(); } }
public void updateRow(DataGridViewRow rowToUpdate) { Console.WriteLine("test"); string dateDelivered=""; string customerName=""; string invoice=""; string id=""; string location = ""; if (rowToUpdate.Cells[0].Value != null) customerName = rowToUpdate.Cells[0].Value.ToString(); if (rowToUpdate.Cells[1].Value != null) invoice = rowToUpdate.Cells[1].Value.ToString(); if (rowToUpdate.Cells[2].Value != null) id = rowToUpdate.Cells[2].Value.ToString(); if (rowToUpdate.Cells[3].Value != null) location = rowToUpdate.Cells[3].Value.ToString(); if (rowToUpdate.Cells[4].Value != null) dateDelivered = rowToUpdate.Cells[4].Value.ToString(); MySqlCommand myCommand = new MySqlCommand("UPDATE `rrs`.`shipping_log` SET `customer_name` = '" + customerName + "', `invoice` = '" + invoice + "' WHERE `shipping_log`.`id` = " + id + ";"); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection connection = MySQLHandle.Connect(); myCommand.Connection = connection; myCommand.ExecuteNonQuery(); }
private void searchAndPopulate() { searchDataGrid.clearRows(); string startDate = searchFromDatePicker.Value.ToString("yyyy-MM-dd"); string endDate = searchToDatePicker.Value.ToString("yyyy-MM-dd"); string filterMasterString = ""; foreach (filterBox filterInstance in searchRunDriverFlowContainer.Controls) { string filterInstanceString=filterInstance.getFilter(); filterMasterString += (" && (run_driver='" + filterInstanceString + "')"); } foreach (filterBox filterInstance in searchCustomerNameFlowContainer.Controls) { string filterInstanceString = filterInstance.getFilter(); filterMasterString += (" && (customer_name='" + filterInstanceString + "')"); } foreach (filterBox filterInstance in searchInvoiceFlowContainer.Controls) { string filterInstanceString = filterInstance.getFilter(); filterMasterString += (" && (invoice='" + filterInstanceString + "')"); } MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT * FROM shipping_log WHERE (date_delivered BETWEEN DATE('" + startDate + "') AND DATE('" + endDate + "'))" + filterMasterString + ";", sqlReader); for (int rowCounter = 0; dataReader.Read(); rowCounter++) { string id = dataReader.GetString(0); string dateDelivered = dataReader.GetString(1); string runDriver = dataReader.GetString(2); string customerName = dataReader.GetString(3); string invoice = dataReader.GetString(4); searchDataGrid.addRow(dateDelivered, runDriver, customerName, invoice, id); } dataReader.Close(); sqlReader.Close(); searchDataGrid.stopUserEdit(); }
private void selectDriverBox_SelectedIndexChanged(object sender, EventArgs e) { currentAddDriver = selectDriverBox.Text; driverNameTextbox.Text = selectDriverBox.Text; MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT `DRV_HOUR_RATE`, `DRV_FUEL_SURCH` FROM drivers WHERE `DRV_NAME`='" + currentAddDriver + "';", sqlReader); dataReader.Read(); hourlyRateTextbox.Text = dataReader.GetString(0); mileageRateTextbox.Text = dataReader.GetString(1); sqlReader.Close(); }
private void updateAddDriver() { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQLHandle.Update("UPDATE `rrs`.`drivers` SET `DRV_NAME` = '" + driverNameTextbox.Text + "', `DRV_HOUR_RATE` = '" + hourlyRateTextbox.Text + "', `DRV_FUEL_SURCH` = '" + mileageRateTextbox.Text + "' WHERE `drivers`.`DRV_NAME` ='" + currentAddDriver + "';", sqlReader); sqlReader.Close(); }
public int updateShippingLogInvoice(DataGridViewRow rowToAdd,bool rowadded,bool dosearch) { string dateDelivered = ""; string runDriver = ""; string customerName = ""; string invoice = ""; string id = ""; string orderNumber = ""; if (rowToAdd.Cells[4].Value == null) addBlankShippingLogInvoice(rowToAdd, connection); if (rowToAdd.Cells[0].FormattedValue.ToString() != null) dateDelivered = rowToAdd.Cells[0].FormattedValue.ToString(); if (rowToAdd.Cells[1].Value != null) runDriver = rowToAdd.Cells[1].Value.ToString(); if (rowToAdd.Cells[2].Value != null) { customerName = rowToAdd.Cells[2].Value.ToString(); } if (rowToAdd.Cells[3].Value != null&&dosearch==true) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySqlDataReader dataReader = MySQLHandle.Select("SELECT `INV_CUSTOMER_NAME` FROM invoice_data WHERE `INV_NUMBER`='" + rowToAdd.Cells[3].Value + "';", sqlReader); if (dataReader.Read()) { rowToAdd.Cells[2].Value = dataReader.GetString(0); customerName = rowToAdd.Cells[2].Value.ToString(); } dataReader.Close(); } if (rowToAdd.Cells[3].Value != null) invoice = rowToAdd.Cells[3].Value.ToString(); if (rowToAdd.Cells[4].Value != null) id = rowToAdd.Cells[4].Value.ToString(); orderNumber = rowToAdd.Index.ToString(); MySQL MySQLHandle2 = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlWriter = MySQLHandle2.Connect(); string insertquery = "INSERT INTO `shipping_log` (`id`, `date_delivered`, `run_driver`, `customer_name`, `invoice`, `order`) VALUES (NULL,'" + dateDelivered + "','" + runDriver + "','" + customerName + "','" + invoice + "'," + orderNumber + ");"; if (rowadded == true) { long temp=Insert(insertquery, sqlWriter); id = temp.ToString(); } Update("UPDATE `rrs`.`shipping_log` SET `date_delivered` = '" + dateDelivered + "', `run_driver` = '" + runDriver + "', `customer_name` = '" + customerName + "', `invoice` = '" + invoice + "', `order` = '" + orderNumber + "' WHERE `shipping_log`.`id` = " + id + ";", sqlWriter); return Convert.ToInt32(id); }
private void button3_Click(object sender, EventArgs e) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); MySQLHandle.Delete("DELETE FROM `rrs`.`drivers` WHERE `drivers`.`DRV_NAME` = '" + selectDriverBox.Text + "';", sqlReader); sqlReader.Close(); populateAddDriverSelect(); }
private void dataGrid_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e) { editing = true; int column = dataGrid.CurrentCell.ColumnIndex; string headerText = dataGrid.Columns[column].HeaderText; if (e.Control is TextBox) { TextBox tb = e.Control as TextBox; tb.KeyPress -= new KeyPressEventHandler(tb_KeyPress); if (headerText.Equals("INVOICE")) { tb.KeyPress += new KeyPressEventHandler(tb_KeyPress); } } if (headerText.Equals("RUN DRIVER")) { TextBox tb = e.Control as TextBox; if (tb != null) { MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader = MySQLHandle.Connect(); tb.AutoCompleteMode = AutoCompleteMode.SuggestAppend; tb.AutoCompleteCustomSource = autoCompleteCollection; tb.AutoCompleteSource = AutoCompleteSource.CustomSource; } } }
private void deleteRowToolStripMenuItem_Click(object sender, EventArgs e) { try { MySQL MySQLHandle2 = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, ""); MySqlConnection sqlReader2 = MySQLHandle2.Connect(); Int32 rowToDelete = dataGrid.Rows.GetFirstRow(DataGridViewElementStates.Selected); if (dataGrid.Rows[rowToDelete].Cells[4].Value != null) { string idOfRow = dataGrid.Rows[rowToDelete].Cells[4].Value.ToString(); MySqlCommand myCommand = new MySqlCommand("DELETE FROM `rrs`.`shipping_log` WHERE `shipping_log`.`id` = " + idOfRow + ";"); myCommand.Connection = sqlReader2; myCommand.ExecuteNonQuery(); dataGrid.Rows.RemoveAt(rowToDelete); dataGrid.ClearSelection(); for (int rowNumber = 0; rowNumber < dataGrid.Rows.Count; rowNumber++) { dataGrid.Rows[rowNumber].HeaderCell.Value = (rowNumber + 1).ToString(); } } sqlReader2.Close(); MySQLHandle2.Disconnect(); } catch { } }
private void updateDriverInfo() { try { string reviewtext = ""; if (reviewCheckbox.Checked == true) { reviewtext = weeklyReviewTextBox.Text; } DateTimePicker temp = (DateTimePicker)this.Parent.Parent.Parent.Parent.Controls["logDate"]; DateTime viewdate = temp.Value; Driver driver = new Driver(driverLabel.Text.ToString(), Convert.ToDouble(billedhoursTextbox.Text), Convert.ToDouble(billedmilesTextbox.Text), reviewtext, temp.Value); MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost,GlobalVar.sqlport,GlobalVar.sqldatabase,GlobalVar.sqlusername,""); MySqlConnection sqlWriter = MySQLHandle.Connect(); MySQLHandle.Update("UPDATE `rrs`.`driver_log` SET `miles` = " + driver.miles.ToString("N" + 2) + ",`hours` = " + driver.hours.ToString("N" + 2) + ", reason='" + driver.reason.ToString() + "' WHERE (`driver_log`.`driver` LIKE '" + driverLabel.Text + "') && (`driver_log`.`date` LIKE '" + viewdate.Date.ToString("yyyy-MM-dd") + "');", sqlWriter); //MySQLHandle.Update("UPDATE `rrs`.`driver_log` SET `miles` = " + billedhoursTextbox.Text + " WHERE (`driver_log`.`driver` = '" + driverLabel.Text + "') && (`driver_log`.`date` = '" + viewdate.Date.ToString("yyyy-MM-dd") + "');", sqlWriter); //MySQLHandle.updateDriverHourLogger(driver, sqlWriter); MySQLHandle.Disconnect(); } catch { } }