private void SupplierBlacklist_Load(object sender, EventArgs e)
{
string query = "SELECT `SupName`FROM `suppliertable` WHERE `supId` = '" + _supId.ToString() + "' ";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
// MessageBox.Show("2");
while (rdr.Read())
{
txtName.Text = rdr[0].ToString();
}
rdr.Close();
}
catch
{
MessageBox.Show("Error");
}
finally
{
conn.Close();
}
}
private void suppliersList()
{
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
string query = "SELECT `supId`,`SupName` FROM `suppliertable` WHERE `supStatus` = 1";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, con);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
DataRow dataRow = dataTable.NewRow();
dataTable.Rows.InsertAt(dataRow, 0);
dataRow.ItemArray = new object[] { 0, "--Select--" };
cmbSupplier.DataSource = dataTable;
cmbSupplier.DisplayMember = "SupName";
cmbSupplier.ValueMember = "supId";
}
catch
{
MessageBox.Show("Erro r");
}
finally
{
con.Close();
}
}
private void button10_Click_1(object sender, EventArgs e)
{
string alternateBarcode = "";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
string Query = "SELECT `proBarCode` FROM `producttable` WHERE `proName` = '" + cmbSearch.Text + "' LIMIT 1";
MySqlCommand mySqlCommand = new MySqlCommand(Query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
while (rdr.Read())
{
alternateBarcode = rdr[0].ToString();
}
rdr.Close();
}
catch
{
}
finally
{
conn.Close();
}
if (alternateBarcode != "")
{
txtScan.Text = alternateBarcode;
}
alternateBarcode = "";
}
private void btnSignIn_Click(object sender, EventArgs e)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "UPDATE `suppliertable` SET `supStatus`= 0 ,`supBlackListReason`= '" + textBox1.Text + "',`supBlackListDate`= '" + DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE `supId` = '" + _supId + "'";
comm.ExecuteNonQuery();
MessageBox.Show("Successfully updated");
}
catch
{
MessageBox.Show("Cannot create the profile at this moment. Please try again.");
}
finally
{
conn.Close();
}
System.Threading.Thread.Sleep(2000);
this.Close();
}
private void btnRefresh_Click(object sender, EventArgs e)
{
chbStatus.Checked = false;
string sqlSelectAll = "SELECT `supId` as`ID` , `SupName` as`Name` , `supTp` as`Telephone` , `supAgentName` as`Agent Name` , `supAgentTp` as`Agent Telephone` FROM `suppliertable` WHERE `supStatus` = 1 ";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
public void refreshProducts()
{
cmbSupplier.Text = "--Select--";
string sqlSelectAll = "SELECT a.`proId` AS `ID`,a.`proName` AS `Name`, a.`proDiscountPrice` as " +
"`Selling Price`, a.`proMinPrice` as `Min Price`, a.`proMinStock` as 'Safety Stock', a.`proStock` as `Stock`, b.`SupName` as `Supplier`" +
" FROM `producttable` a, `suppliertable` b where a.`proSupplierId` = b.`supId` LIMIT 50";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
private void ucInventoryData()
{
string sqlSelectAll = "SELECT po.`po_id` AS 'ID', ppq.`product_id` AS 'Prod. Id', pro.`proName`AS Product, " +
"ppq.`product_qty` AS Qty , sup.SupName as Supplier ,po.`datePlaced` AS 'Date Palced', po.`dateExpected` AS 'Date Expected' " +
"FROM `purchaseordertable` po JOIN `purchaseorder_product_quantitytable` ppq on po.`po_id`= ppq.`purchaseorderId` " +
"JOIN producttable pro ON ppq.`product_id` = pro.`proId` JOIN suppliertable sup ON po.`supplier_id`=sup.supId WHERE po.`status` = 0";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
public void refreshProducts()
{
//cmbSupplier.Text = "--Select--";
string sqlSelectAll = @"SELECT r.`recievedId`, r.`poId`, r.`invoiceNo`, r.`recievedDate`, r.`total`, s.`SupName` FROM `recievedtable` r join `purchaseordertable` po on r.`poId`=po.`po_id` join `suppliertable` s on s.supId=po.supplier_id";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
// string sqlSelectAll = "SELECT `empId` as`E No.` , `empName` as `Full Name`, `empCallingName` as `Calling Name`, `empNIC` as `NIC`, `empMobile` as `Mobile` FROM `employeetable` WHERE `status` = 1";
public void ucInventoryData()
{
string sqlSelectAll = "SELECT `empId` as`E No.` , `empName` as `Full Name`, `empCallingName` as `Calling Name`, `empNIC` as `NIC`, `empMobile` as `Mobile` FROM `employeetable` WHERE `status` = 1";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
if ((name & address & agentName & telephone & agentTelephone & bank & bankBranch & bankNo) == true)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "UPDATE `suppliertable` SET `SupName`= '" + txtName.Text + "',`supAddress`='" + txtAddress.Text + "',`supTp`='" + txtTelephone.Text + "',`supAgentName`='" + txtAgentName.Text + "',`supAgentTp`='" + txtAgentTP.Text + "',`supBankName`='" + txtBank.Text + "',`supBankNo`='" + txtBankAccount.Text + "',`supBankBranch`='" + txtBranch.Text + "' WHERE `supId` = '" + _supId.ToString() + "'";
comm.ExecuteNonQuery();
MessageBox.Show("Successfully updated");
}
catch
{
MessageBox.Show("Cannot create the profile at this moment. Please try again.");
}
finally
{
conn.Close();
}
}
else
{
updateCheck();
}
}
private void chbStatus_CheckedChanged(object sender, EventArgs e)
{
if (chbStatus.Checked == false)
{
string sqlSelectAll = "SELECT `empId` as`E No.` , `empName` as `Full Name`, `empCallingName` as `Calling Name`, `empNIC` as `NIC`, `empMobile` as `Mobile` FROM `employeetable` WHERE `status` = 0";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
else
{
string sqlSelectAll = "SELECT `empId` as`E No.` , `empName` as `Full Name`, `empCallingName` as `Calling Name`, `empNIC` as `NIC`, `empMobile` as `Mobile` FROM `employeetable` WHERE `status` = 1";
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
if (!username)
{
pbUserNameCorrect.Visible = false;
pcUsernameWrong.Visible = true;
}
if (!password)
{
pbPassCorrect.Visible = false;
pcPasswordWrong.Visible = true;
}
if (!confirm)
{
pbConfirmCorrect.Visible = false;
pcConfirmWrong.Visible = true;
}
if (password & confirm & username)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
//Send to db
// ConfigurationManager.ConnectionStrings["default"].ConnectionString;
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO `employeetable` (`empId`, `empName`, `empCallingName`, `empNIC`, `empAddress`, `empTelephone`, `empMobile`, `empGender`, `empEmail`, `empUsername`, `empPassword`, `empRole`) VALUES(NULL, '', '', '', '', '', '', '', '', @username, @password, '');";
comm.Parameters.AddWithValue("@username", txtUsername.Text);
comm.Parameters.AddWithValue("@password", txtPassword.Text);
comm.ExecuteNonQuery();
conn.Close();
//usernameVariable = txtUsername.Text;
this.Hide();
var createUser2 = new createUserAcc2Window(true, 0);
createUser2.Closed += (s, args) => this.Close();
createUser2.Show();
}
catch
{
conn.Close();
}
finally
{
//Close Connection
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
// Local Variables
float vValue = 0;
string issuedDate = "";
string expiaryDate = "";
string to = "";
string from = "";
try
{
conn.Open();
string Query = "SELECT `vExpDate`, `vIssuedDate`, `vTo`, `vFrom`, `value` FROM `giftvouchertable` WHERE `vBarcode`= '" + txtVoucher.Text + "' AND `vPIN` = '" + txtPIN.Text + "' AND `status` = 1 ";
MySqlCommand mySqlCommand = new MySqlCommand(Query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
while (rdr.Read())
{
expiaryDate = rdr[0].ToString();
vValue = Convert.ToSingle(rdr[4]);
issuedDate = rdr[1].ToString();
to = rdr[2].ToString();
from = rdr[3].ToString();
}
rdr.Close();
}
catch
{
MessageBox.Show("Error");
}
finally
{
conn.Close();
}
if (vValue != 0)
{
txtValue.Text = vValue.ToString();
txtExpiary.Text = expiaryDate.ToString();
txtDate.Text = issuedDate.ToString();
txtTo.Text = to.ToString();
txtFrom.Text = from.ToString();
vValue = 0;
}
else
{
MessageBox.Show("Error occured");
}
}
private void invPurchaseOrder_Load(object sender, EventArgs e)
{
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
string query = "SELECT `supId`,`SupName` FROM `suppliertable` WHERE `supStatus` = 1";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, con);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
DataRow dataRow = dataTable.NewRow();
dataTable.Rows.InsertAt(dataRow, 0);
dataRow.ItemArray = new object[] { 0, "--Select--" };
cmbSupplier.DataSource = dataTable;
cmbSupplier.DisplayMember = "SupName";
cmbSupplier.ValueMember = "supId";
}
catch
{
MessageBox.Show("Erro r");
}
finally
{
con.Close();
}
dataGridView1.ColumnCount = 8;
dataGridView1.Columns[0].Name = "ID";
dataGridView1.Columns[1].Name = "Product Name";
dataGridView1.Columns[1].Width = 300;
dataGridView1.Columns[2].Name = "Max Price";
dataGridView1.Columns[3].Name = "Min Price";
dataGridView1.Columns[4].Name = "Current Stock";
dataGridView1.Columns[5].Name = "Forecasted Order";
dataGridView1.Columns[6].Name = "Order Value";
dataGridView1.Columns[7].Name = "Total";
dataGridView1.Columns[7].MinimumWidth = 184;
dataGridView1.Columns[0].ReadOnly = true;
dataGridView1.Columns[1].ReadOnly = true;
dataGridView1.Columns[2].ReadOnly = true;
dataGridView1.Columns[3].ReadOnly = true;
dataGridView1.Columns[4].ReadOnly = true;
dataGridView1.Columns[5].ReadOnly = true;
dataGridView1.Columns[6].ReadOnly = false; // Can be Edited
dataGridView1.Columns[6].FillWeight = 30;
}
private void txtUsername_TextChanged(object sender, EventArgs e)
{
pcUsernameWrong.Visible = false;
if (txtUsername.TextLength > 5)
{
// string ConnectionString = "Server=127.0.0.1;Database=thushiniminisuper;Uid=root;Pwd=;";
dbString db = new dbString();
string ConnectionString = db.connectionSring;
using (MySqlConnection con = new MySqlConnection(ConnectionString))
try
{
con.Open();
using (MySqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = @"select count(*) from employeetable where empUsername=@UserName";
cmd.Parameters.Add(new MySqlParameter("UserName", txtUsername.Text));
int i = Convert.ToInt32(cmd.ExecuteScalar());
if (i == 1)
{
pbUserNameCorrect.Visible = false;
username = false;
}
else
{
pbUserNameCorrect.Visible = true;
username = true;
}
}
}
catch
{
//does nothing
}
finally
{
con.Close();
}
}
else
{
pbUserNameCorrect.Visible = false;
username = false;
}
}
private void inventorySupplierView_Load(object sender, EventArgs e)
{
string query = "SELECT `SupName`, `supAddress`, `supTp`, `supAgentName`, `supAgentTp`, `supBankName`, `supBankNo`, `supBankBranch`, `supStatus`, `supBlackListReason`, `supBlackListDate` FROM `suppliertable` WHERE `supId` = '" + _supId.ToString() + "' ";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
// MessageBox.Show("2");
while (rdr.Read())
{
txtName.Text = rdr[0].ToString();
txtAddress.Text = rdr[1].ToString();
txtTelephone.Text = rdr[2].ToString();
txtAgentName.Text = rdr[3].ToString();
txtAgentTP.Text = rdr[4].ToString();
txtBankAccount.Text = rdr[6].ToString();
txtBank.Text = rdr[5].ToString();
txtBranch.Text = rdr[7].ToString();
txtReason.Text = rdr[9].ToString();
//Status
if (Convert.ToInt32(rdr[8]) == 1)
{
chbStatus.Checked = false;
}
else
{
chbStatus.Checked = true;
txtFrom.Text = rdr[10].ToString();
}
// textBox1.Text = rdr[9].ToString();
}
rdr.Close();
}
catch
{
MessageBox.Show("Error");
}
finally
{
conn.Close();
}
}
private void notificationStockToRecieve()
{
string rtn = "SELECT COUNT(ppq.`ppq_id`) FROM purchaseorder_product_quantitytable ppq JOIN purchaseordertable po on po.po_id=ppq.purchaseorderId WHERE po.dateExpected='" + DateTime.Today.ToString("yyyy-MM-dd") + "'";
string variable = "";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(rtn, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
variable = rdr[0].ToString();
}
}
catch (Exception exb)
{
//MessageBox.Show("" + exb);
}
finally
{
conn.Close();
}
// MessageBox.Show(variable);
if (variable == "0")
{
btnNotification3.Hide();
}
else if (variable == "1")
{
btnNotification3.Show();
btnNotification3.Text = variable + " Item is to recieve today";
}
else
{
btnNotification3.Show();
btnNotification3.Text = variable + " Items are to recieve today";
}
}
private void notificationReahingStock()
{
string rtn = "SELECT COUNT(`proId`) FROM `producttable` WHERE `proStock` <= `proMinStock` AND `proStatus`=1 AND `proStock` > 0";
string variable = "";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(rtn, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
variable = rdr[0].ToString();
}
}
catch (Exception exb)
{
//MessageBox.Show("" + exb);
}
finally
{
conn.Close();
}
// MessageBox.Show(variable);
if (variable == "0")
{
btnNotification2.Hide();
}
else if (variable == "1")
{
btnNotification2.Show();
btnNotification2.Text = variable + " Item is reaching stock limits";
}
else
{
btnNotification2.Show();
btnNotification2.Text = variable + " Items are reaching stock limits";
}
}
private void SupplierUpdate_Load(object sender, EventArgs e)
{
string query = "SELECT `SupName`, `supAddress`, `supTp`, `supAgentName`, `supAgentTp`, `supBankName`, `supBankNo`, `supBankBranch`, `supStatus`, `supBlackListReason`, `supBlackListDate`, `supStartDate` FROM `suppliertable` WHERE `supId` = '" + _supId.ToString() + "' ";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
// MessageBox.Show("2");
while (rdr.Read())
{
txtName.Text = rdr[0].ToString();
txtAddress.Text = rdr[1].ToString();
txtTelephone.Text = rdr[2].ToString();
txtAgentName.Text = rdr[3].ToString();
txtAgentTP.Text = rdr[4].ToString();
txtBankAccount.Text = rdr[6].ToString();
txtBank.Text = rdr[5].ToString();
txtBranch.Text = rdr[7].ToString();
}
rdr.Close();
}
catch
{
MessageBox.Show("Error");
}
finally
{
name = true;
address = true;
telephone = true;
agentName = true;
agentTelephone = true;
bankNo = true;
bank = true;
bankBranch = true;
conn.Close();
}
}
private void invAddProducts_Load(object sender, EventArgs e)
{
onlyAddProducts();
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
con.Open();
string query = "SELECT `categoryId`, `categoryName` FROM `productcategory` WHERE 1";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, con);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow.ItemArray = new object[] { 0, "--Select--" };
dataTable.Rows.InsertAt(dataRow, 0);
cmbCategory.DataSource = dataTable;
cmbCategory.DisplayMember = "categoryName";
cmbCategory.ValueMember = "categoryId";
//
string query1 = "SELECT `supId`, `SupName` FROM `suppliertable` WHERE `supStatus` = 1";
MySqlDataAdapter mySqlDataAdapter1 = new MySqlDataAdapter(query1, con);
DataTable dataTable1 = new DataTable();
mySqlDataAdapter1.Fill(dataTable1);
DataRow dataRow1 = dataTable1.NewRow();
dataRow1.ItemArray = new object[] { 0, "--Select--" };
dataTable1.Rows.InsertAt(dataRow1, 0);
txtSupplier.DataSource = dataTable1;
txtSupplier.DisplayMember = "SupName";
txtSupplier.ValueMember = "supId";
}
catch
{
// MessageBox.Show("Error");
}
finally
{
con.Close();
}
}
public void empView_Load(object sender, EventArgs e)
{
string query = "SELECT `empId`, `empName`, `empCallingName`, `empNIC`, `empAddress`, `empTelephone`, `empMobile`, `empGender`, `empEmail`, `empRole`, `status` FROM `employeetable` WHERE `empId`=" + employeeID + "";
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand mySqlCommand = new MySqlCommand(query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
// MessageBox.Show("2");
while (rdr.Read())
{
txtFullName.Text = rdr[1].ToString();
txtAddress.Text = rdr[4].ToString();
txtCallingName.Text = rdr[2].ToString();
txtNic.Text = rdr[3].ToString();
txtTelephone.Text = rdr[5].ToString();
txtMobile.Text = rdr[6].ToString();
txtEmail.Text = rdr[8].ToString();
if (rdr[7].ToString() == "Male")
{
rdoMale.Checked = true;
}
else
{
rdoFemale.Checked = true;
}
textBox1.Text = rdr[9].ToString();
}
rdr.Close();
}
catch
{
MessageBox.Show("Error");
}
finally
{
conn.Close();
}
}
private void chbStockinRisk_CheckedChanged(object sender, EventArgs e)
{
chbStatus.Checked = false;
string sqlSelectAll;
if (cmbSupplier.Text == "--Select--")
{
sqlSelectAll = "SELECT a.`proId` AS `ID`,a.`proName` AS `Name`, a.`proDiscountPrice` as " +
"`Selling Price`, a.`proMinPrice` as `Min Price`,a.`proMinStock` as 'Safety Stock' , a.`proStock` as `Stock`, b.`SupName` as `Supplier`" +
" FROM `producttable` a, `suppliertable` b where a.`proSupplierId` = b.`supId` and a.`proStock`<=a.`proMinStock` and a.`proStock`>0 LIMIT 50";
}
else
{
sqlSelectAll = "SELECT a.`proId` AS `ID`,a.`proName` AS `Name`, a.`proDiscountPrice` as " +
"`Selling Price`, a.`proMinPrice` as `Min Price`,a.`proMinStock` as 'Safety Stock' , a.`proStock` as `Stock`, b.`SupName` as `Supplier`" +
" FROM `producttable` a, `suppliertable` b where a.`proSupplierId` = b.`supId` and b.`SupName`='" + cmbSupplier.Text + "'and a.`proStock`<= a.`proMinStock` and a.`proStock`>0 LIMIT 50";
}
dbString db = new dbString();
string connectionString = db.connectionSring;
MySqlConnection mysqlCon = new MySqlConnection(connectionString);
try
{
mysqlCon.Open();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
}
catch
{
}
finally
{
mysqlCon.Close();
}
}
//Variable
private void alternateAddProducts()
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection con = new MySqlConnection(connString);
try
{
con.Open();
using (MySqlDataAdapter sda = new MySqlDataAdapter("SELECT `proId`, `proName` FROM `producttable` WHERE `proStatus` = 1", con))
{
//Fill the DataTable with records from Table.
DataTable dt = new DataTable();
sda.Fill(dt);
//Insert the Default Item to DataTable.
DataRow dataRow = dt.NewRow();
dt.Rows.InsertAt(dataRow, 0);
dataRow.ItemArray = new object[] { 0, "--Select--" };
cmbSearch.DataSource = dt;
//Assign DataTable as DataSource.
cmbSearch.DataSource = dt;
cmbSearch.DisplayMember = "proName";
cmbSearch.ValueMember = "proId";
//Set AutoCompleteMode.
cmbSearch.AutoCompleteMode = AutoCompleteMode.Suggest;
cmbSearch.AutoCompleteSource = AutoCompleteSource.ListItems;
}
}
catch
{
MessageBox.Show("alternate");
}
finally
{
con.Close();
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
if (earnedPoints <= 0)
{
allowSubmit = false;
}
else if (total <= deductedPoints)
{
allowSubmit = false;
MessageBox.Show("Deducted points should be equal or less than the total.");
}
else
{
allowSubmit = true;
}
if (allowSubmit == true)
{
string query1 = "UPDATE `customertable` SET `cusLoyaltyPoints`= (`cusLoyaltyPoints` -'" + (deductedPoints + earnedPoints) + "') WHERE `cusId` = '" + CustomerId + "'";
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = query1;
cmd.ExecuteNonQuery();
}
catch
{
//MessageBox.Show("Hi");
}
finally
{
this.Close();
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
//alternate add products
alternateAddProducts();
//For Keypad
btnBackspace.Visible = false;
lblTime.Text = DateTime.Now.ToShortTimeString();
textBoxName = 'A';
txtScan.Focus();
txtTransactionDate.Text = DateTime.Today.ToShortDateString();
lblDate.Text = DateTime.Today.ToShortDateString();
//Assigning date and invoiceNo
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
string Query = "SELECT `salesId` FROM `salestable` ORDER by `salesId` DESC LIMIT 1;";
MySqlCommand mySqlCommand = new MySqlCommand(Query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
while (rdr.Read())
{
lastInvoice = Convert.ToInt32(rdr[0]);
}
rdr.Close();
}
catch
{
MessageBox.Show("Exception Occured");
}
finally
{
txtInvoice.Text = (lastInvoice + 1).ToString();
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
string Query = "UPDATE `giftvouchertable` SET `status`= 2 WHERE `vBarcode` = '" + _barcode + "'";
MySqlCommand mySqlCommand = new MySqlCommand(Query, conn);
mySqlCommand.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Error in connecting with the Database");
}
finally
{
conn.Close();
this.Close();
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO `giftvouchertable`(`vBarcode`, `vPIN`, `vExpDate`, `vIssuedDate`, `vTo`, `vFrom`, `status`, `value`)VALUES ('" + txtVoucher.Text + "','" + txtPIN.Text + "','" + x.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + txtTo.Text + "','" + txtFrom.Text + "','1','" + txtValue.Text + "' )";
comm.ExecuteNonQuery();
MessageBox.Show("Gift voucher processed successfully");
this.Close();
}
catch
{
MessageBox.Show("Exception Occured in the process");
}
finally
{
conn.Close();
}
}
private void cmbCategory_Leave(object sender, EventArgs e)
{
if ((cmbCategory.Text == "-Select-") || (cmbCategory.Text == ""))
{
category = false;
pbCategory.Visible = true;
}
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
con.Open();
string query = "SELECT `subcategoryId`, `subcategoryname` FROM `productsubcategory` WHERE `maincategoryId` = (SELECT `categoryId` FROM `productcategory` WHERE `categoryName` = '" + cmbCategory.Text + "')";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, con);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
DataRow dataRow = dataTable.NewRow()
;
dataRow.ItemArray = new object[] { 0, "--Select--" };
dataTable.Rows.InsertAt(dataRow, 0);
cmbSubCategory.DataSource = dataTable;
cmbSubCategory.DisplayMember = "subcategoryname";
cmbSubCategory.ValueMember = "subcategoryId";
}
catch
{
MessageBox.Show("Error");
}
finally
{
con.Close();
}
}
private void giftVoucherProcess()
{
//Local Variables
string voucherBarcodeNo = txtScan.Text;
float vValue = 0;
int vStatus = 0;
bool notExpired = false;
string expiaryDate = "";
//
dbString db = new dbString();
string connString = db.connectionSring;
MySqlConnection conn = new MySqlConnection(connString);
try
{
conn.Open();
string Query = "SELECT `vExpDate`, `status`, `value` FROM `giftvouchertable` WHERE `vBarcode` = '" + voucherBarcodeNo + "'";
MySqlCommand mySqlCommand = new MySqlCommand(Query, conn);
MySqlDataReader rdr = mySqlCommand.ExecuteReader();
while (rdr.Read())
{
expiaryDate = rdr[0].ToString();
vStatus = Convert.ToInt32(rdr[1]);
vValue = Convert.ToSingle(rdr[2]);
}
rdr.Close();
try
{
exp = Convert.ToDateTime(expiaryDate);
}
catch
{
notExpired = false;
// MessageBox.Show("Error in Date conversion");
}
if (date > exp)//expied
{
notExpired = false;
expiaryDate = "";
}
else if (date <= exp)//Not Expired
{
// textBox1.Text = "Not Exp";
notExpired = true;
expiaryDate = "";
}
}
catch
{
MessageBox.Show("Exception Occured in connecting to Database");
}
finally
{
conn.Close();
}
//////////////////////////////////////////////////
/////
if (vStatus == 1 && notExpired == true && (total >= vValue)) //Voucher Issued and not presented for redemption
{
if (proIdBuffer == "")
{
txtScan.Enabled = false; txtQty.Enabled = false; txtSearch.Enabled = false;
giftVTransition1 state = new giftVTransition1(string.Empty, 0);
var GV = new GiftVoucherPay(voucherBarcodeNo, state);
GV.ShowDialog();
//textBox1.Text = state.Text;
//textBox1.SelectionStart = state.CaretPosition;
loyalty = loyalty + Convert.ToSingle(state.Text);
txtLoyalty.Text = loyalty.ToString();
total = subTotal - loyalty - discount;
txtTotal.Text = total.ToString();
}
else
{
txtScan.Clear();
MessageBox.Show("Complete the existing transaction and try again");
txtCash.Focus();
}
notExpired = false;
}
else if (vStatus == 2) //Voucher is redemmed
{
MessageBox.Show("This voucher already presented for redemption.");
}
else if (total < vValue) // if voucher cannot be fully utilized
{
MessageBox.Show("Total sum of amount should be greater than gift voucher value");
}
else if (vStatus == 0)//New voucher
{
Regex validator500 = new Regex("^['V'][0-9]{10}[1]$");
if (validator500.Match(voucherBarcodeNo).Success)
{//
// voucherBarcodeNo.Text = "500.00";
var state = new giftVTransition1(string.Empty, 0);
GiftVoucher gv = new GiftVoucher(voucherBarcodeNo, state);
gv.ShowDialog();
txtScan.Text = "1111111110500";
txtScan.Enabled = false;
txtQty.Enabled = false;
btnAddItem.PerformClick();
}
Regex validator1000 = new Regex("^['V'][0-9]{10}[2]$");
if (validator1000.Match(voucherBarcodeNo).Success)
{//
//txtValue.Text = "1000.00";
var state = new giftVTransition1(string.Empty, 0);
GiftVoucher gv = new GiftVoucher(voucherBarcodeNo, state);
gv.ShowDialog();
txtScan.Text = "1111111111000";
txtScan.Enabled = false;
txtQty.Enabled = false;
btnAddItem.PerformClick();
}
Regex validator2000 = new Regex("^['V'][0-9]{10}[3]$");
if (validator2000.Match(voucherBarcodeNo).Success)
{//
//txtValue.Text = "2000.00";
var state = new giftVTransition1(string.Empty, 0);
GiftVoucher gv = new GiftVoucher(voucherBarcodeNo, state);
gv.ShowDialog();
txtScan.Text = "1111111112000";
txtScan.Enabled = false;
txtQty.Enabled = false;
btnAddItem.PerformClick();
}
Regex validator5000 = new Regex("^['V'][0-9]{10}[4]$");
if (validator5000.Match(voucherBarcodeNo).Success)
{//
// txtValue.Text = "5000.00";
var state = new giftVTransition1(string.Empty, 0);
GiftVoucher gv = new GiftVoucher(voucherBarcodeNo, state);
gv.ShowDialog();
txtScan.Text = "1111111115000";
txtScan.Enabled = false;
txtQty.Enabled = false;
btnAddItem.PerformClick();
}
}
else if (notExpired == false)// if Voucher is Expired
{
MessageBox.Show("This voucher is Expired");
}
// Reset Local Variables
voucherBarcodeNo = "";
vValue = 0;
vStatus = 0;
notExpired = false;
expiaryDate = "";
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
txtLoyalty.Text = (Convert.ToSingle(txtTotal.Text) / 100).ToString();
}
catch {
}
float balx = -1;
try
{
balx = Convert.ToSingle(txtBalance.Text);
}
catch
{
}
bool cardMode = true;
if (btnMode.Text == "CARD" && txtCardNo.Text == "")
{
cardMode = false;
}
else
{
cardMode = true;
}
if ((subTotal >= 1) && (balx > (-1)) && cardMode)
{
btnPrint.Enabled = false;
btnAddItem.PerformClick();
pdf();
dataGridView1.Rows.Clear();
dbString dbc = new dbString();
string conString = dbc.connectionSring;
MySqlConnection con = new MySqlConnection(conString);
try
{
con.Open();
MySqlCommand comm = con.CreateCommand();
string que = "";
string cus = "";
if (cus == "")
{
que = "INSERT INTO `salestable` (`salesId`, `salesDate`, `salesTime`, `salesSubTotal`, `salesDiscount`, `salesTotal`, `salesEmpUsername`, `salesCusId`, `salesLoyaltyRedeem`, `salesLoyaltyEarn`) VALUES ('" + (lastInvoice + 1) + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', CURRENT_TIME(), '" + subTotal + "', '" + discount + "', '" + (subTotal - discount) + "', '" + empUsername + "', '0', '10', '" + (subTotal / 100) + "');";
}
else
{
que = "INSERT INTO `salestable` (`salesId`, `salesDate`, `salesTime`, `salesSubTotal`, `salesDiscount`, `salesTotal`, `salesEmpUsername`, `salesCusId`, `salesLoyaltyRedeem`, `salesLoyaltyEarn`) VALUES ('" + (lastInvoice + 1) + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', CURRENT_TIME(), '" + subTotal + "', '" + discount + "', '" + (subTotal - discount) + "', '" + empUsername + "', '0', '0', '0');";
}
comm.CommandText = que;
comm.ExecuteNonQuery();
}
catch
{
MessageBox.Show("Error");
}
finally
{
con.Close();
}
afterBillPrinted();
btnPrint.Enabled = true;
txtScan.Enabled = true; txtQty.Enabled = true; txtSearch.Enabled = true;
}
cash_Card_Change();
//// print Exchange Bill
}