private void GetProductInfo()
{
try
{
SQLConn.sqL = "SELECT ProductCode, Description, UnitPrice, StocksOnHand FROM Product WHERE ProductNo =" + productID + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductCode.Text = SQLConn.dr[0].ToString();
lblDescription.Text = SQLConn.dr[1].ToString();
lblPrice.Text = Strings.FormatNumber(SQLConn.dr[2]).ToString();
lblCurrentStocks.Text = SQLConn.dr[3].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadUpdateCategory()
{
try
{
SQLConn.sqL = "SELECT * FROM Category WHERE CategoryNo = '" + categoryID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblCategoryNo.Text = SQLConn.dr["CategoryNo"].ToString();
txtCatName.Text = SQLConn.dr["CategoryName"].ToString();
txtDescription.Text = SQLConn.dr["Description"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadCategories(string strSearch)
{
try
{
SQLConn.sqL = "SELECT * FROM CATEGORY WHERE CategoryName LIKE '" + strSearch + "%' ORDER By CategoryNO";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader(CommandBehavior.CloseConnection);
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["CategoryNo"].ToString());
x.SubItems.Add(SQLConn.dr["CategoryName"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void LoadStaffs(string search)
{
try
{
SQLConn.sqL = "SELECT StaffID, CONCAT(Firstname, ', ', Lastname, ' ') as ClientName, CONCAT(Street, ', ', Area, ', ', City , ', ', Province) as Address, ContactNo, username, role FROM Staff WHERE Firstname LIKE '" + search.Trim() + "%' ORDER By Firstname";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["StaffId"].ToString());
x.SubItems.Add(SQLConn.dr["ClientName"].ToString());
x.SubItems.Add(SQLConn.dr["ContactNo"].ToString());
x.SubItems.Add(SQLConn.dr["Address"].ToString());
x.SubItems.Add(SQLConn.dr["username"].ToString());
x.SubItems.Add(SQLConn.dr["Role"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog theDialog = new OpenFileDialog();
theDialog.Title = "Open Text File";
theDialog.Filter = "BackUp File (*.sql)|*.sql";
if (theDialog.ShowDialog() == DialogResult.OK)
{
string file = Path.GetFullPath(theDialog.FileName);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
MySqlBackup mb = new MySqlBackup(SQLConn.cmd);
mb.ImportInfo.TargetDatabase = "rahman_educational_service";
mb.ImportInfo.DatabaseDefaultCharSet = "utf8";
mb.ImportFromFile(file);
// LoadStaffs("");
MessageBox.Show("Restoring Successfully Completed.");
}
}
catch (Exception ex)
{
MessageBox.Show("Restroring UnSuccessfull. " + ex);
}
}
private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT td.InvoiceNo,TDate,td.ProductNo,Description,ItemPrice,SUM(TD.Quantity) as totalQuantity,Discount,perItem from transactions as t,transactiondetails as td,product as p where p.ProductNo = td.ProductNo AND td.InvoiceNo = t.InvoiceNo AND DATE_FORMAT(STR_TO_DATE(TDate, '%m/%d/%Y'), '%Y-%m-%d') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY TDate, td.InvoiceNo,P.ProductNo ORDER By TDate";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new MySqlDataAdapter(SQLConn.cmd);
this.rahman_educational_serviceDataSet.Account.Clear();
//this.dsReportC.StocksIn.Clear();
SQLConn.da.Fill(this.rahman_educational_serviceDataSet.Account);
ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString());
ReportParameter endDate = new ReportParameter("EndDate", EndDate.ToString());
this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate });
this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 90;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
private void LoadUpdateCategory()
{
try
{
SQLConn.sqL = "SELECT ProductNo, ProductCode, P.Description, P.CategoryNo, CategoryName, UnitPrice, StocksOnHand FROM Product as P LEFT JOIN Category as C ON P.CategoryNo = C.CategoryNo WHERE ProductNo = '" + productID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = SQLConn.dr["ProductNo"].ToString();
txtProductCode.Text = SQLConn.dr["ProductCode"].ToString();
txtDescription.Text = SQLConn.dr["Description"].ToString();
txtCategory.Text = SQLConn.dr["CategoryName"].ToString();
categoryID = Convert.ToInt32(SQLConn.dr["CategoryNo"]);
txtUnitPrice.Text = Strings.Format(SQLConn.dr["UnitPrice"], "#,##0.00");
txtStocksOnHand.Text = SQLConn.dr["StocksOnHand"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (textBox1.Text != "")
{
int i = Convert.ToInt32(textBox1.Text);
SQLConn.sqL = "SELECT * FROM payment WHERE InvoiceNo LIKE '" + i + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
frmPrint abcd = new frmPrint(i, "frmPOS");
abcd.ShowDialog();
}
else
{
MessageBox.Show("Error:Invalid InvoiceNo.");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
this.Close();
}
public void loadSchool()
{
frmS = "";
try
{
SQLConn.sqL = "select * FROM saccounts where Name='" + accName.Text + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
accNo.Text = SQLConn.dr["AccountID"].ToString();
Money_Debt = SQLConn.dr["Money_Debt"].ToString();
frmS = SQLConn.dr["form"].ToString();
item_des_text.Focus();
}
else
{
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
void addTransactionsDetails()
{
try
{
for (int i = 0; i < listView1.Items.Count; ++i)
{
String itemcode = listView1.Items[i].SubItems[1].Text;
String itemdes = listView1.Items[i].SubItems[2].Text;
String unitP = listView1.Items[i].SubItems[3].Text;
String quan = listView1.Items[i].SubItems[4].Text;
String to = listView1.Items[i].SubItems[5].Text;
String disc = listView1.Items[i].SubItems[6].Text;
// MessageBox.Show(itemcode + itemdes + unitP + quan + to + disc); // For Testing
int productNo = getProductNo(itemcode);
getquantity(productNo, quan);
SQLConn.sqL = "INSERT INTO transactiondetails(InvoiceNo,ProductNo,ItemPrice,Quantity,perItem,Discount) VALUES('" + invoiceNo + "', '" + productNo + "', '" + unitP + "' ,'" + quan + "','" + to + "','" + disc + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
void getquantity(int p, string q)
{
int s = 0;
try
{
SQLConn.sqL = "SELECT StocksOnHand FROM product WHERE ProductNo ='" + p + "' ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
s = Convert.ToInt32(SQLConn.dr["StocksOnHand"]);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
cutstock(p, q, s);
}
public void loadInfo()
{
dataGridView1.Rows.Clear();
if (textBox1.Text != "")
{
string i = textBox1.Text;
try
{
SQLConn.sqL = "SELECT TD.ProductNo,ItemPrice,SUM(Quantity)as Quantity,Discount,SUM(perItem)as perItem,ProductCode,Description,AccType,AccNo FROM transactiondetails as TD ,Product as P, transactions as T WHERE AccName LIKE '" + i + "' AND TD.ProductNo=P.ProductNo AND TD.InvoiceNo=T.InvoiceNo GROUP BY TD.ProductNo,Discount";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
dataGridView1.Rows.Add(SQLConn.dr["ProductNo"].ToString(), SQLConn.dr["ProductCode"].ToString(), SQLConn.dr["Description"].ToString(), SQLConn.dr["ItemPrice"].ToString(), SQLConn.dr["Quantity"].ToString(), SQLConn.dr["Discount"].ToString(), SQLConn.dr["perItem"].ToString(), "0", "0");
}
}
catch (Exception ex)
{
MessageBox.Show("Here:" + ex.Message.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
}
private void LoadCategory()
{
try
{
SQLConn.sqL = "SELECT * FROM Product WHERE Description LIKE '" + txtCatName.Text + "%' ORDER BY ProductNo ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
ListView1.Items.Clear();
while (SQLConn.dr.Read() == true)
{
x = new ListViewItem(SQLConn.dr["ProductNo"].ToString());
x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["Description"].ToString());
ListView1.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void GetDebtInfo()
{
try
{
SQLConn.sqL = "SELECT Name,Money_Debt FROM DACCOUNTS WHERE AccountID =" + accountID + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblName.Text = SQLConn.dr[0].ToString();
lblMoney.Text = SQLConn.dr[1].ToString();
txtMoney_Debt.Text = "0";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
void gettingInvoice()
{
SQLConn.sqL = "select * FROM transactions";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
try
{
while (SQLConn.dr.Read() == true)
{
invoiceNo = Convert.ToInt32(SQLConn.dr["InvoiceNo"]);
}
if (invoiceNo == 0)
{
invoiceNo = 100000000;
label9.Text = invoiceNo.ToString();
}
else if (invoiceNo >= 100000000)
{
int sum = invoiceNo;
sum = sum + 1;
invoiceNo = sum;
label9.Text = invoiceNo.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
}
private void GetProductNo()
{
try
{
SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblProductNo.Text = (Convert.ToInt32(SQLConn.dr["ProductNo"]) + 1).ToString();
}
else
{
lblProductNo.Text = "1";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadUpdateStaff()
{
txt_money.ReadOnly = true;
try
{
SQLConn.sqL = "SELECT * FROM saccounts WHERE AccountID = '" + LSAccountID + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblAccountNo.Text = SQLConn.dr["AccountID"].ToString();
txtName.Text = SQLConn.dr["Name"].ToString();
txtArea.Text = SQLConn.dr["Area"].ToString();
txtCity.Text = SQLConn.dr["City"].ToString();
txtShopNo.Text = SQLConn.dr["ShopNo"].ToString();
txtContractNo.Text = SQLConn.dr["ContactNo"].ToString();
txt_money.Text = SQLConn.dr["Money_Debt"].ToString();
spring = SQLConn.dr["form"].ToString();
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void GetStaffID()
{
try
{
SQLConn.sqL = "SELECT AccountID FROM saccounts ORDER BY AccountID DESC";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
lblAccountNo.Text = (Convert.ToInt32(SQLConn.dr["AccountID"]) + 1).ToString();
}
else
{
lblAccountNo.Text = "1";
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
void loadForm()
{
string[] split = spring.Split(',');
string asd;
try
{
foreach (string item in split)
{
SQLConn.sqL = "SELECT Description FROM product WHERE ProductNo = '" + item + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
asd = SQLConn.dr["Description"].ToString();
listView1.Items.Add(asd);
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void load_chart()
{
try
{
string abc = DateTime.Now.ToString("MM-dd-yyyy");
// MessageBox.Show(abc);
SQLConn.sqL = " SELECT * FROM transactions where TDate= '" + abc + "' ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
this.chart1.Series["POS"].Points.AddY(SQLConn.dr["TotalAmount"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void LoadReport()
{
try
{
SQLConn.sqL = "SELECT S.ProductNo, Description, SUM(Quantity) as Quantity, DateIn FROM Product as P, StockIn as S WHERE S.ProductNo = P.ProductNo AND DATE_FORMAT(STR_TO_DATE(DateIN, '%m/%d/%Y'), '%Y-%m-%d') BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, DateIN ORDER BY DateIn, Description";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new MySqlDataAdapter(SQLConn.cmd);
this.rahman_educational_serviceDataSet.stockin.Clear();
//this.dsReportC.StocksIn.Clear();
SQLConn.da.Fill(this.rahman_educational_serviceDataSet.stockin);
ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString());
ReportParameter endDate = new ReportParameter("EndDate", EndDate.ToString());
this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate });
this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
this.reportViewer1.ZoomPercent = 90;
this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent;
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
}
public void loadDis()
{
try
{
SQLConn.sqL = "select * FROM daccounts where Name='" + textBox1.Text + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
accidTxt.Text = SQLConn.dr["AccountID"].ToString();
Money_Debt = SQLConn.dr["Money_Debt"].ToString();
}
else
{
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
void gettingStaff()
{
try
{
SQLConn.sqL = "SELECT * FROM Staff WHERE StaffID ='" + staffId + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
textBox2.Text = SQLConn.dr["Role"].ToString();
string name = SQLConn.dr["Firstname"].ToString();
string last = SQLConn.dr["Lastname"].ToString();
name = name + " " + last;
textBox4.Text = name;
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
public void AutoCompleteText(bool chec)
{
item_des_text.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
item_des_text.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection coll = new AutoCompleteStringCollection();
try
{
if (chec == true)
{
SQLConn.sqL = "SELECT * FROM Product ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
des = SQLConn.dr["Description"].ToString();
coll.Add(des);
}
}
else if (chec == false)
{
string[] split = frmS.Split(',');
foreach (string item in split)
{
SQLConn.sqL = "SELECT * FROM product WHERE ProductNo = '" + item + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
des = SQLConn.dr["Description"].ToString();
coll.Add(des);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
finally // only checking remove in case of error
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
item_des_text.AutoCompleteCustomSource = coll;
} //// Description textBox , Drop Down Class
void addTransactionsDetails()
{
String quan;
String itemcode;
String itemdes;
String unitP;
String disc;
String to;
productNo = 0;
try
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
quan = dataGridView1.Rows[i].Cells[7].Value.ToString();
if (quan != "" && Convert.ToInt32(quan) > 0)
{
productNo = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value.ToString());
itemcode = dataGridView1.Rows[i].Cells[1].Value.ToString();
itemdes = dataGridView1.Rows[i].Cells[2].Value.ToString();
unitP = dataGridView1.Rows[i].Cells[3].Value.ToString();
disc = dataGridView1.Rows[i].Cells[5].Value.ToString();
to = dataGridView1.Rows[i].Cells[8].Value.ToString();
SQLConn.sqL = "INSERT INTO transactiondetails(InvoiceNo,ProductNo,ItemPrice,Quantity,Discount,perItem) VALUES('" + invoiceNo + "', '" + productNo + "', '" + unitP + "' ,'" + quan + "','" + disc + "','" + to + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
Stream myS = null;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "BackUp File (*.sql)|*.sql";
saveFileDialog1.RestoreDirectory = true;
try
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
if ((myS = saveFileDialog1.OpenFile()) != null)
{
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
MySqlBackup mb = new MySqlBackup(SQLConn.cmd);
string file = Path.GetFullPath(saveFileDialog1.FileName);
myS.Close();
mb.ExportInfo.AddCreateDatabase = true;
List <string> abc = new List <string>();
abc.Add("daccounts");
abc.Add("saccounts");
abc.Add("debtin");
abc.Add("debtins");
abc.Add("payment");
abc.Add("transactions");
abc.Add("transactiondetails");
abc.Add("stockin");
abc.Add("staff");
abc.Add("product");
abc.Add("category");
mb.ExportInfo.TablesToBeExportedList = abc;
mb.ExportInfo.ExportTableStructure = true;
mb.ExportInfo.ExportRows = true;
mb.ExportToFile(file);
MessageBox.Show("BackUp Successfully Completed.");
}
}
}
catch (Exception ex)
{
MessageBox.Show("BackUp UnSuccessfull. " + ex);
}
}
public void AutoCompleteAccount()
{
accName.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
accName.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection accColl = new AutoCompleteStringCollection();
try
{
if (accType.Text == "Distributor")
{
SQLConn.sqL = "SELECT * FROM daccounts ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
des = SQLConn.dr["Name"].ToString();
accColl.Add(des);
}
}
else if (accType.Text == "School Sale")
{
SQLConn.sqL = "SELECT * FROM saccounts ";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
des = SQLConn.dr["Name"].ToString();
accColl.Add(des);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
finally // only checking remove in case of error
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
accName.AutoCompleteCustomSource = accColl;
}
public void AEform()
{
string Fall = null;
if (listView1.Items.Count == 0)
{
spring = null;
}
else
{
try
{
for (int i = 0; i < listView1.Items.Count; ++i)
{
string abc = listView1.Items[i].Text.ToString();
SQLConn.sqL = "SELECT ProductNo FROM product WHERE Description = '" + abc + "'";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
if (i == 0)
{
spring = SQLConn.dr["ProductNo"].ToString();
}
else
{
Fall = SQLConn.dr["ProductNo"].ToString();
spring = string.Concat(spring, ",", Fall);
}
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
}
void addTransactions()
{
string accT = "";
string no = "";
string name = "";
if (accType.Text == "Counter Sale")
{
accT = "CS";
no = "0";
name = accName.Text;
}
else if (accType.Text == "Distributor")
{
accT = "DS";
no = accNo.Text;
name = accName.Text;
}
else if (accType.Text == "School Sale")
{
accT = "SS";
no = accNo.Text;
name = accName.Text;
}
else
{
MessageBox.Show("Error.");
}
try
{
SQLConn.sqL = "INSERT INTO transactions(InvoiceNo,TDate,TTime,TotalAmount,StaffID,AccType,AccNo,AccName) VALUES('" + invoiceNo + "', '" + System.DateTime.Now.ToString("MM/dd/yyyy") + "', '" + System.DateTime.Now.ToString("hh:mm:ss") + "' ,'" + totalAmount + "','" + staffId + "','" + accT + "','" + no + "','" + name + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
private void AddStockIn()
{
try
{
SQLConn.sqL = "INSERT INTO StockIn(ProductNo, Quantity, DateIn) Values('" + lblProductNo.Text + "', '" + txtStocksOnHand.Text + "', '" + System.DateTime.Now.ToString("MM/dd/yyyy") + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
