private void UpdateStock_Load(object sender, EventArgs e)
{
try
{
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select * From stock WHERE `ItemName` like '" + itemname.Text + "'";
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
code.Text = read.GetString(0);
textBox1.Text = read.GetString(1);
quantity.Text = read.GetString(2);
units.Text = read.GetString(4);
bp.Text = read.GetString(5);
supplier.Text = read.GetString(6);
newitem.Text = read.GetString(1);
newunits.Text = read.GetString(4);
newsupplier.Text = read.GetString(6);
}
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || comboBox1.Text == "")
{
MessageBox.Show("Please Fill Account Details", "Invalid Account", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//check if account exists
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select * From `account` WHERE `AccountNumber` = '" + textBox1.Text + "'";
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
MessageBox.Show("Duplicate Account Exists", "Account", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//create account
String SQLQUERY = "INSERT INTO `account`(`AccountNumber`, `AccountName`, `Bank`, `DateOpened`, `Signitory`, `Accounttype`, `Amount`) VALUES ('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + dateTimePicker1.Text + "','" + textBox4.Text + "','" + comboBox1.Text + "','" + textBox5.Text + "')";
HorsePower.ExecuteSQL(SQLQUERY);
MessageBox.Show("Account Information Saved", "Account", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select `AccountNumber`, `AccountName`, `Bank`, `Signitory`, `Amount` From `account` WHERE `AccountName` = '" + comboBox1.Text + "'";
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
accountnumber.Text = read.GetString(0);
accountname.Text = read.GetString(1);
bankname.Text = read.GetString(2);
signitory.Text = read.GetString(3);
amount.Text = read.GetString(4);
}
else
{
accountnumber.Text = "";
bankname.Text = "";
signitory.Text = "";
amount.Text = "";
}
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
//check user account
String AS = "SELECT `Username`, `Role`, `Password` FROM `login2` WHERE `Username` = '" + comboBox2.Text + "' AND `Role` = 'Admin' AND `Password` = '" + textBox1.Text + "'";
// V2MySqlServer v2 = new V2MySqlServer(Server.getIP(), "root", "", "hotel");
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = AS;
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
if (!comboBox1.Text.Equals(""))
{
//proceed to transfer money
int total = int.Parse(amount.Text) + int.Parse(tobank.Text);
//confirm from user
DialogResult res = MessageBox.Show("AMOUNT KSH:" + tobank.Text + " \n WILL BE TRANSFERED TO : " + bankname.Text + " \n ON : " + dateTimePicker1.Text + " \n BY : " + comboBox2.Text, "CONFIRM", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
textBox1.Text = "";
textBox1.Visible = false;
label11.Visible = false;
//proceed
HorsePower.ExecuteSQL("UPDATE `account` SET `Amount`='" + total.ToString() + "' WHERE `AccountNumber` ='" + accountnumber.Text + "' AND `AccountName` = '" + accountname.Text + "'");
//save transaction trail
TransactionTrail();
//clear everything
accountname.Text = "";
accountnumber.Text = "";
bankname.Text = "";
signitory.Text = "";
amount.Text = "";
tobank.Text = "";
source.Text = "";
//refresh the account balance
RefreshAccount(comboBox1.Text);
}
else
{
MessageBox.Show("Oooh No ! You Have Not Selected Bank Account", "Invalid Parameters", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
//do nothing
}
else
{
//do not do anything
MessageBox.Show("Sorry You Do Not Have Sufficient To Complete This Operation", "Invalid Account", MessageBoxButtons.OK, MessageBoxIcon.Hand);
textBox1.Text = "";
textBox1.Visible = false;
label11.Visible = false;
}
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void LoadReport()
{
try
{
String SQL = "SELECT `FirstName`, `LastName`, `IDNumber`, `Gender`, `Telephone`, `Residence`, `WorkPosition`, `Salary`, `PaymentMode`, `WorkingDuration` FROM `workers` WHERE 1";
MySqlCommand cmd = new MySqlCommand(SQL, HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
var html = new StringBuilder();
html.AppendLine("<html><body>");
html.AppendLine("<head><style>table {background:white;border-collapse: collapse;width: 100%;},th{background:#4CAF50;}, td { text-align: left;padding: 10px;background:#fafafa;}tr:nth-child(even){background-color: #f2f2f2}th {background-color: #4CAF50;color: white;}</style></head>");
html.AppendLine("<p>");
html.AppendLine("</p>");
html.AppendLine("<table border='1'><tr><th><center><h2>WORKERS REPORT<h2></center></th> </tr> </table> ");
html.AppendLine("<table>");
// html.AppendLine("<tr><td></td><td><h3>GreenCare POS</h3></td></tr>");
// html.AppendLine("<tr><td></td><td></td></tr>");
html.AppendLine("</table>");
html.AppendLine("<tr>");
html.AppendLine("<table border='1'><tr><td></td><td></td>");
html.AppendLine("<tr>");
html.AppendLine("<th>No.</th><th>FirstName</th><th>LastName</th><th>IDNumber</th><th>Gender</th><th>Mobile Number</th><th>Residence</th><th>Work Position</th><th>Salary</th><th>Payment Mode</th><th>Working Duration</th>");
html.AppendLine("<tr>");
int i = 0;
while (read.Read())
{
i++;
html.AppendLine("<tr>");
html.AppendLine("<td>" + i + "</td>");
html.AppendLine("<td>" + read.GetString(0) + "</td>");
html.AppendLine("<td>" + read.GetString(1) + "</td>");
html.AppendLine("<td>" + read.GetString(2) + "</td>");
html.AppendLine("<td>" + read.GetString(3) + "</td>");
html.AppendLine("<td>" + read.GetString(4) + "</td>");
html.AppendLine("<td>" + read.GetString(5) + "</td>");
html.AppendLine("<td>" + read.GetString(6) + "</td>");
html.AppendLine("<td>" + read.GetString(7) + "</td>");
html.AppendLine("<td>" + read.GetString(8) + "</td>");
html.AppendLine("<td>" + read.GetString(9) + "</td>");
html.AppendLine("</tr>");
}
webBrowser1.DocumentText = html.ToString();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void LoadReport()
{
try
{
String SQL = "SELECT `Code`, `ItemName`, `Quantity`, `Categories`, `Units`, `BuyingPrice`, `Supplier`, `PurchaseDate` FROM `stock` WHERE 1";
MySqlCommand cmd = new MySqlCommand(SQL, HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
var html = new StringBuilder();
html.AppendLine("<html><body>");
html.AppendLine("<head><style>table {background:white;border-collapse: collapse;width: 100%;},th{background:#4CAF50;}, td { text-align: left;padding: 10px;background:#fafafa;}tr:nth-child(even){background-color: #f2f2f2}th {background-color: #4CAF50;color: white;}</style></head>");
html.AppendLine("<p>");
html.AppendLine("</p>");
html.AppendLine("<table border='1'><tr><th><center><h2>STOCKS REPORT<h2></center></th> </tr> </table> ");
html.AppendLine("<table>");
// html.AppendLine("<tr><td></td><td><h3>GreenCare POS</h3></td></tr>");
// html.AppendLine("<tr><td></td><td></td></tr>");
html.AppendLine("</table>");
html.AppendLine("<tr>");
html.AppendLine("<table border='1'><tr><td></td><td></td>");
html.AppendLine("<tr>");
html.AppendLine("<th>No.</th><th>ItemCode</th><th>ItemName</th><th>Quantity</th><th>Category</th><th>Units</th><th>BuyingPrice</th><th>Supplier</th><th>PurchaseDate</th>");
html.AppendLine("<tr>");
int i = 0;
while (read.Read())
{
i++;
html.AppendLine("<tr>");
html.AppendLine("<td>" + i + "</td>");
html.AppendLine("<td>" + read.GetString(0) + "</td>");
html.AppendLine("<td>" + read.GetString(1) + "</td>");
html.AppendLine("<td>" + read.GetString(2) + "</td>");
html.AppendLine("<td>" + read.GetString(3) + "</td>");
html.AppendLine("<td>" + read.GetString(4) + "</td>");
html.AppendLine("<td>" + read.GetString(5) + "</td>");
html.AppendLine("<td>" + read.GetString(6) + "</td>");
html.AppendLine("<td>" + read.GetString(7) + "</td>");
html.AppendLine("</tr>");
}
webBrowser1.DocumentText = html.ToString();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void LoadRange(string text1, string text2)
{
try
{
String SQL = "SELECT `Name`, `Quantity`, `Price`, `Total` FROM `purchase_log` WHERE `Date` BETWEEN '" + text1 + "' AND '" + text2 + "'";
MySqlCommand cmd = new MySqlCommand(SQL, HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
var html = new StringBuilder();
html.AppendLine("<html><body>");
html.AppendLine("<head><style>table {background:white;border-collapse: collapse;width: 100%;},th{background:#4CAF50;}, td { text-align: left;padding: 10px;background:#fafafa;}tr:nth-child(even){background-color: #f2f2f2}th {background-color: #4CAF50;color: white;}</style></head>");
html.AppendLine("<table border='1'><tr><th><center><h2>PURCHASE LOG REPORT<h2></center></th> </tr> </table> ");
html.AppendLine("<table>");
// html.AppendLine("<tr><td></td><td><h3>GreenCare POS</h3></td></tr>");
// html.AppendLine("<tr><td></td><td></td></tr>");
html.AppendLine("</table>");
html.AppendLine("<tr>");
html.AppendLine("<table border='0'><tr><td></td><td></td>");
html.AppendLine("<tr>");
html.AppendLine("<td>No.</td><td>Name</td><td>Quantity</td><td>Price</td><td>Total</td>");
html.AppendLine("<tr>");
int i = 0;
int sum = 0;
while (read.Read())
{
i++;
sum += int.Parse(read.GetString(3));
html.AppendLine("<tr>");
html.AppendLine("<td>" + i + "</td>");
html.AppendLine("<td>" + read.GetString(0) + "</td>");
html.AppendLine("<td>" + read.GetString(1) + "</td>");
html.AppendLine("<td>" + read.GetString(2) + "</td>");
html.AppendLine("<td>" + read.GetString(3) + "</td>");
html.AppendLine("</tr>");
}
html.AppendLine("<tr><td></td><td></td><td></td><td></td><td></td></tr>");
html.AppendLine("<tr><td></td><td></td><td></td><td>Grand Total</td><td>" + sum + "</td></tr>");
webBrowser1.DocumentText = html.ToString();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void RefreshAccount(string text)
{
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select `AccountNumber`, `AccountName`, `Bank`, `Signitory`, `Amount` From `account` WHERE `AccountName` = '" + text + "'";
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
accountnumber.Text = read.GetString(0);
accountname.Text = read.GetString(1);
bankname.Text = read.GetString(2);
signitory.Text = read.GetString(3);
amount.Text = read.GetString(4);
}
}
private void LoadReport()
{
try
{
String SQL = "SELECT `Company`, `Location`, `FullNames`, `OfficeTel`, `Mobile` FROM `suppliers` WHERE 1";
MySqlCommand cmd = new MySqlCommand(SQL, HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
var html = new StringBuilder();
html.AppendLine("<html><body>");
html.AppendLine("<head><style>table {background:white;border-collapse: collapse;width: 100%;},th{background:#4CAF50;}, td { text-align: left;padding: 10px;background:#fafafa;}tr:nth-child(even){background-color: #f2f2f2}th {background-color: #4CAF50;color: white;}</style></head>");
html.AppendLine("<p>");
html.AppendLine("</p>");
html.AppendLine("<table border='1'><tr><th><center><h2>SUPPLIERS REPORT<h2></center></th> </tr> </table> ");
html.AppendLine("<table>");
// html.AppendLine("<tr><td></td><td><h3>GreenCare POS</h3></td></tr>");
// html.AppendLine("<tr><td></td><td></td></tr>");
html.AppendLine("</table>");
html.AppendLine("<tr>");
html.AppendLine("<table border='1'><tr><td></td><td></td>");
html.AppendLine("<tr>");
html.AppendLine("<th>No.</th><th>Company</th><th>Location</th><th>SupplierName</th><th>OfficeTelephone</th><th>Mobile</th>");
html.AppendLine("<tr>");
int i = 0;
while (read.Read())
{
i++;
html.AppendLine("<tr>");
html.AppendLine("<td>" + i + "</td>");
html.AppendLine("<td>" + read.GetString(0) + "</td>");
html.AppendLine("<td>" + read.GetString(1) + "</td>");
html.AppendLine("<td>" + read.GetString(2) + "</td>");
html.AppendLine("<td>" + read.GetString(3) + "</td>");
html.AppendLine("<td>" + read.GetString(4) + "</td>");
// html.AppendLine("<td>" + read.GetString(5) + "</td>");
html.AppendLine("</tr>");
}
webBrowser1.DocumentText = html.ToString();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
public void LoadUsers()
{
try
{
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select * From `login2` WHERE 1";
MySqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
comboBox2.Items.Add(read.GetString(1));
}
}
catch (Exception ex)
{
Console.Write(ex);
}
}
public void generateReport()
{
try
{
String SQL = "SELECT `ItemName`, `Quantity`, `Total` FROM `sales` WHERE 1";
MySqlCommand cmd = new MySqlCommand(SQL, HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
var html = new StringBuilder();
html.AppendLine("<html><body>");
html.AppendLine("<head><style>table {background:white;border-collapse: collapse;width: 100%;},th{background:#4CAF50;}, td { text-align: left;padding: 10px;background:#fafafa;}tr:nth-child(even){background-color: #f2f2f2}th {background-color: #4CAF50;color: white;}</style></head>");
html.AppendLine("<table border='1'><tr><th><center><h3>SALES REPORT</h3></center></th></tr> </table> ");
// html.AppendLine("<table>");
// html.AppendLine("<tr><td></td><td><h3>GreenCare POS</h3></td></tr>");
// html.AppendLine("<tr><td></td><td></td></tr>");
//html.AppendLine("</table>");
html.AppendLine("<tr>");
html.AppendLine("<table border='0'><tr><td></td><td></td>");
html.AppendLine("<tr>");
html.AppendLine("<td>No.</td><td>Item Name</td><td>Quantity</td><td>Total</td>");
html.AppendLine("<tr>");
int i = 0;
int sum = 0;
while (read.Read())
{
i++;
sum += int.Parse(read.GetString(2));
html.AppendLine("<tr>");
html.AppendLine("<td>" + i + "</td>");
html.AppendLine("<td>" + read.GetString(0) + "</td>");
html.AppendLine("<td>" + read.GetString(1) + "</td>");
html.AppendLine("<td>" + read.GetString(2) + "</td>");
html.AppendLine("</tr>");
}
html.AppendLine("<tr><td></td><td></td><td>Grand Total</td><td>" + sum + "</td></tr>");
webBrowser1.DocumentText = html.ToString();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void TransferMoney_Load(object sender, EventArgs e)
{
try
{
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select * From `account` WHERE 1";
MySqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
comboBox1.Items.Add(read.GetString(1));
}
}
catch (Exception ex)
{
Console.Write(ex);
}
//load users
LoadUsers();
}
public NewStock()
{
InitializeComponent();
try
{
itemname.AutoCompleteMode = AutoCompleteMode.Suggest;
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select `ItemName` From stock WHERE 1";
MySqlDataReader read = cmd.ExecuteReader();
AutoCompleteStringCollection ds = new AutoCompleteStringCollection();
while (read.Read())
{
ds.Add(read.GetString(0));
}
itemname.AutoCompleteCustomSource = ds;
}
catch (Exception ex)
{
Console.Write(ex);
}
}
private void itemname_TextChanged(object sender, EventArgs e)
{
try
{
dataGridView1.DataSource = HorsePower.Select("SELECT * FROM `stock` WHERE `ItemName` LIKE '" + itemname.Text + "%'");
MySqlCommand cmd = HorsePower.OpenConnection().CreateCommand();
cmd.CommandText = "Select * From stock WHERE `ItemName` like '" + itemname.Text + "'";
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
code.Text = read.GetString(0);
quantity.Text = read.GetString(2);
categories.Text = read.GetString(3);
units.Text = read.GetString(4);
bp.Text = read.GetString(5);
supplier.Text = read.GetString(6);
button1.Visible = false;
button2.Visible = true;
button3.Visible = true;
button4.Visible = true;
}
else
{
code.Text = "";
quantity.Text = "";
categories.Text = "";
units.Text = "";
bp.Text = "";
supplier.Text = "";
button1.Visible = true;
button2.Visible = false;
button3.Visible = false;
button4.Visible = false;
}
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
}
Boolean check(string idnumber, string date)
{
MySqlCommand cmd = new MySqlCommand("SELECT `IDNumber`, `FullNames`, `Date`, `StartTime`, `StopTime` FROM `shift` WHERE `IDNumber`= '" + idnumber + "' AND `Date` = '" + date + "'", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
return(true);
}
else
{
return(false);
}
// return false;
}
private void SaveSale(string on, string waiter)
{
//loop through the order
MySqlCommand cmd = new MySqlCommand("SELECT `ItemName`, `Quantity`, `Price`, `Total` FROM `orderinfor` WHERE `OrderNumber` = '" + on + "'", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
//insert the sales table
HorsePower.ExecuteSQL("INSERT INTO `sales`(`Worker`,`Date`,`ItemName`,`Quantity`, `Price`,`Total`) VALUES ('" + waiter + "','" + dateTimePicker1.Text + "','" + read.GetString(0) + "','" + read.GetString(1) + "','" + read.GetString(2) + "','" + read.GetString(3) + "')");
}
MessageBox.Show("Hotel Sale Information Saved Successfully ", "Sale Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public void PopulateList()
{
//Take imagelist for store images to use in listview.
System.Windows.Forms.ImageList myImageList1 = new ImageList();
//set the image size of which size images will be displayed in the listview.
myImageList1.ImageSize = new Size(64, 64);
//Now add the images into the imageList..
MySqlCommand cmd = new MySqlCommand("SELECT * FROM `itemcategories` WHERE 1", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
int x = 0;
if (read.Read())
{
byte[] pic = read["image"] as byte[] ?? null;
MemoryStream m = new MemoryStream(pic);
x = x + 1;
//myImageList1.Images.Add(Image.FromStream(m));
this.listView1.LargeImageList = myImageList1;
this.listView1.Items.Add(read.GetString(0), x);
}
}
public void checklogin()
{
if (textBox1.Text == "" || comboBox2.Text == "" || textBox2.Text == "")
{
MessageBox.Show("Some Fields Are Empty", "Login Fail", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MySqlCommand cmd = new MySqlCommand("Select * FROM `login2` WHERE Username = '******' AND Role = '" + comboBox2.Text + "' AND Password = '******'", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
WriteLog();
this.Hide();
Menus c = new Menus(textBox1.Text, comboBox2.Text);
c.ShowDialog();
}
else
{
MessageBox.Show("Login Failed Try Again", "Unable To Login", MessageBoxButtons.OK, MessageBoxIcon.Error);
textBox1.Text = "";
textBox2.Text = "";
comboBox2.Text = "";
textBox1.Focus();
}
}
}
public void PopulateList()
{
try
{
//Take imagelist for store images to use in listview.
System.Windows.Forms.ImageList myImageList1 = new ImageList();
//set the image size of which size images will be displayed in the listview.
myImageList1.ImageSize = new Size(64, 64);
//Now add the images into the imageList..
MySqlCommand cmd = new MySqlCommand("SELECT * FROM `itemcategories` WHERE 1", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
// int x = 0;
while (read.Read())
{
String image = read.GetString(1);
String im = image.Replace(@"/", @"\");
// x = x + 1;
// myImageList1.Images.Add(Image.FromFile(im));
// this.listView2.LargeImageList = myImageList1;
// this.listView2.Items.Add(read.GetString(0), x);
}
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
}
private void ReduceQty(string name, string qty)
{
try
{
int q = 0;
//fetch the quantity
MySqlCommand cmd = new MySqlCommand("SELECT `Quantity` FROM `stock` WHERE `ItemName` = '" + name + "'", HorsePower.OpenConnection());
MySqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
q = int.Parse(read.GetString(0));
}
//now reduce the stock by subtratcting
int total = q - int.Parse(qty);
//now update the new stock qty
HorsePower.ExecuteSQL("UPDATE `stock` SET `Quantity`='" + total + "' WHERE `ItemName` = '" + name + "'");
}
catch (Exception ex)
{
Console.Write(ex);
}
}