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 { }
}
