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(); } }
}
void bw3_DoWork(object sender, DoWorkEventArgs e)
{
DateTime viewdate = logDate.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 '" + logDate.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();
}
catch { }
}
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 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 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();
}
}
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 { }
}