private int Authentication(String user, String pwd) { int flag = 0; if (user == "admin" && pwd == Settings.Default.useradminpwd) { flag = 1; } else if (user != "admin") { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); SqlCeDataReader reader = null; try { string query = "SELECT UserName,Password FROM users WHERE UserName=@user AND Password=@pwd"; SqlCeCommand cmd = new SqlCeCommand(query, conn); cmd.Parameters.AddWithValue("@user", user); cmd.Parameters.AddWithValue("@pwd", pwd); reader = cmd.ExecuteReader(); while (reader.Read()) { flag = 2; } } catch (Exception ex) { MessageBox.Show("" + ex); } } return(flag); }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { try { SqlCeDataReader reader = null; dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT MeasuringUnit FROM catagory WHERE Catagory=@cat"; SqlCeCommand cmd = new SqlCeCommand(query, conn); cmd.Parameters.AddWithValue("@cat", selectcatcombo.Text); reader = cmd.ExecuteReader(); while (reader.Read()) { // Measuring unit selection mulabel.Text = "Measuring Unit: " + reader.GetString(0); } operation.closeDBConnection(conn); } catch (Exception ex) { } }
private double getSellingPrice(string itemid) { double sprice = 0; dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT selling_price FROM sub_catagory WHERE ItemId=@id"; SqlCeDataReader reader = null; try { SqlCeCommand cmd = new SqlCeCommand(query, conn); cmd.Parameters.AddWithValue("@id", itemid); reader = cmd.ExecuteReader(); while (reader.Read()) { sprice = reader.GetDouble(0); } } catch (Exception ex) { MessageBox.Show("Exception Happend while getting the Selling Price " + ex); } return(sprice); }
private string getUnit(string query) { dboperation operation = new dboperation(); SqlCeConnection conn = null; string result = ""; SqlCeDataReader reader = null; SqlCeCommand cmd = null; try { conn = operation.dbConnection(Settings.Default.DatabasePath); cmd = new SqlCeCommand(query, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { result = reader.GetString(0); } } catch (Exception ex) { MessageBox.Show("" + ex); } operation.closeDBConnection(conn); return(result); }
private void Preferences_Load(object sender, EventArgs e) { string dbpath, pathbackup; dbpath = Settings.Default.DatabasePath; dbpathpref.Text = dbpath; pathbackup = Settings.Default.BackupLocation; backupdbpathpref.Text = pathbackup; if (pathbackup == "") { warningbackup.Text = "* Please mention a path for database to Backup, prevent you to protect the data from system failure."; } if (dbpath == "") { var filepath = new FilePath(); filepath.ShowDialog(); } else { FilePath createDBfolder = new FilePath(); string db = Settings.Default.DatabasePath + "\\" + Global.ROOT_DATA_FOLDER + "\\" + Global.DB_NAME_PREFIX + ".sdf"; // If Database file not exist then create a database file createDBfolder.CreateRootFolder(Settings.Default.DatabasePath); if (!File.Exists(db)) { Boolean flagdb; string flagtable; dboperation operation = new dboperation(); flagdb = operation.createDatabase(Settings.Default.DatabasePath); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); if (conn.State == System.Data.ConnectionState.Open && flagdb == true) { flagtable = operation.createTable(conn); if (flagtable != "s") { MessageBox.Show("An error occured to create table in the database " + flagtable); } operation.closeDBConnection(conn); } else { MessageBox.Show("An error occured to build connection with databases"); } } else { // MessageBox.Show("Database File already Exist"); } } if (dbpathpref.Text == "" && backupdbpathpref.Text == "") { backupDBbtn.Enabled = false; } }
// Insert Data to Database private void InsertData() { // Code for adding sub-catagory goes here string subcatname, scatagory; double purchase, selling; float stkqty; subcatname = subcattb.Text; scatagory = selectcatcombo.Text; stkqty = float.Parse(stockqty.Text); purchase = double.Parse(pprice.Text); selling = double.Parse(sellprice.Text); try { dboperation operation = new dboperation(); SqlCeConnection conn = null; SqlCeDataReader reader = null; long catid = -1; conn = operation.dbConnection(Settings.Default.DatabasePath); string selectquery = "SELECT CatId FROM catagory WHERE Catagory=@cat"; string insertquery = "INSERT INTO sub_catagory (ItemName,stock_Quantity,purchase_price,selling_price,undercatagory) VALUES(@item,@stock,@purchase,@sell,@ucat)"; SqlCeCommand cmd = new SqlCeCommand(selectquery, conn); cmd.Parameters.AddWithValue("@cat", scatagory); reader = cmd.ExecuteReader(); while (reader.Read()) { catid = reader.GetInt64(0); } int result; SqlCeCommand cmdinsert = new SqlCeCommand(insertquery, conn); cmdinsert.Parameters.AddWithValue("@item", subcatname); cmdinsert.Parameters.AddWithValue("@stock", stkqty); cmdinsert.Parameters.AddWithValue("@purchase", purchase); cmdinsert.Parameters.AddWithValue("@sell", selling); cmdinsert.Parameters.AddWithValue("@ucat", catid.ToString()); result = cmdinsert.ExecuteNonQuery(); if (result < 0) { MessageBox.Show("An error occured while inserting the subcatagory in the database"); } else { // MessageBox.Show("Item inserted Successfully"); load_subcat_gridview(conn); subcattb.Clear(); selectcatcombo.ResetText(); stockqty.Clear(); pprice.Clear(); sellprice.Clear(); } operation.closeDBConnection(conn); } catch (Exception ex) { MessageBox.Show("" + ex); } }
static void Main() { dboperation operation = new dboperation(); Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); string db = Settings.Default.DatabasePath + "\\" + Global.ROOT_DATA_FOLDER + "\\" + Global.DB_NAME_PREFIX + ".sdf"; if (!File.Exists(db)) { Application.Run(new registration()); } else { if (operation.keyvalidation()) { Application.Run(new loginPanel()); } else { Application.Run(new registration()); } } // Application.Run(new TestPicture()); }
private void catAddbtn_Click(object sender, EventArgs e) { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query1 = "DELETE FROM sub_catagory WHERE undercatagory= @cat"; string delquery = "DELETE FROM Catagory WHERE CatId= @cat"; try { SqlCeCommand cmd = new SqlCeCommand(query1, conn); cmd.Parameters.AddWithValue("@cat", rmvcid.Text); cmd.ExecuteNonQuery(); SqlCeCommand cmd2 = new SqlCeCommand(delquery, conn); cmd2.Parameters.AddWithValue("@cat", rmvcid.Text); cmd2.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Exception Happened while removing " + catnamermv.Text + " from the Catagory List" + ex); } operation.closeDBConnection(conn); this.Close(); }
private void updatestock_Click(object sender, EventArgs e) { try { var editsubcat = new editSubCatagory(); foreach (DataGridViewRow row in itemdetails.SelectedRows) { editsubcat.subcatnameupdate.Text = row.Cells[1].Value.ToString(); editsubcat.subcatppupdate.Text = row.Cells[5].Value.ToString(); editsubcat.subcatspupdate.Text = row.Cells[6].Value.ToString(); editsubcat.subcatqtyupdate.Text = row.Cells[2].Value.ToString(); editsubcat.scid.Text = row.Cells[0].Value.ToString(); editsubcat.subcatnameupdate.Enabled = false; editsubcat.subcatppupdate.Enabled = false; editsubcat.subcatspupdate.Enabled = false; editsubcat.edititemheader.Text = "Update Stock"; editsubcat.ShowDialog(); } dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); // load_subcat_gridview(conn); operation.closeDBConnection(conn); }catch (Exception ex) { MessageBox.Show("" + ex); } }
private void Form1_Load(object sender, EventArgs e) { try { SqlCeDataReader reader = null; dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT UserName FROM users"; string checkregquery = "SELECT companyName,regkey FROM regdetails WHERE Id=1"; if (!checkRegistration(conn, checkregquery)) { this.Close(); thread = new Thread(openreg); thread.SetApartmentState(ApartmentState.STA); thread.Start(); } SqlCeCommand cmd = new SqlCeCommand(query, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { // Check wheather user exist or not userselector.Items.Add(reader.GetString(0)); } operation.closeDBConnection(conn); } catch (Exception ex) { } }
private void saveprint_Click(object sender, EventArgs e) { string invoiceid; dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string insertCustdetailsquery = "INSERT INTO customerDetails(customerName, custAddress,custMobile,paymentDetails ,totalSGST,totalCGST,totalDiscount,totalItemPrice,percentSGST,percentCGST,InvoiceTime) VALUES(@custname,@custaddr,@custmbl,@payment,@totalsgst,@totalcgst,@dis,@totaliprice,@percentcgst,@percentsgst,GETDATE())"; string insertInvoicedetailsquery = "INSERT INTO invoiceDetails(InvoiceNo,ItemName,quantity,TotalSellingPrice,TotalPurchasedPrice,perItemSGST,perItemCGST,discountPerItemType,measuringUnit,description) VALUES(@invoiceno,@item,@qty,@itotalsp,@itotalpp,@peritemsgst,@peritemcgst,@itemdisc,@itemmu,@itemdesc)"; if (invoicenumber != "") { printinvoice.Print(); return; } invoiceid = insertinvoicedata1(conn, insertCustdetailsquery); if (invoiceid != "-1") { MessageBox.Show(invoiceid); invoicenumber = invoiceid; insertinvoicedata2(conn, insertInvoicedetailsquery); updatestock(conn); printinvoice.Print(); } else { MessageBox.Show("Error Occured While printing and Saving Invoice"); } }
private void checkupdatestock_Activated(object sender, EventArgs e) { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); load_item_list(conn); }
private void Createuserbtn_Click(object sender, EventArgs e) { string user, passwd1; passwd1 = newuserpwd1.Text; if (passwd1.Length < 5) { MessageBox.Show("Error! Password strength should be atleast 5 charecter long"); newuserpwd1.Clear(); newusrpwd2.Clear(); return; } user = string.Concat(newuser.Text.Where(char.IsLetterOrDigit)); try { SqlCeDataReader reader = null; dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT UserName FROM users"; SqlCeCommand cmd = new SqlCeCommand(query, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { // Check wheather user exist or not if (reader.GetString(0) == user) { MessageBox.Show("Username already exist, Please choose another one"); return; } } // Insert Username and Password to the Database query = "INSERT INTO users (UserName,Password) VALUES (@usr,@pwd)"; cmd = new SqlCeCommand(query, conn); cmd.Parameters.AddWithValue("@usr", user); cmd.Parameters.AddWithValue("@pwd", passwd1); int result = cmd.ExecuteNonQuery(); if (result < 0) { MessageBox.Show("Error Inserting New User"); return; } else { MessageBox.Show("User: "******" for billing created successfully"); } this.Close(); // MessageBox.Show("Insert new Record"); operation.closeDBConnection(conn); } catch (Exception ex) { MessageBox.Show("An error Occured to create User: " + ex); } }
private void savedestinationbtn_Click(object sender, EventArgs e) { if (!this.ValidateChildren(ValidationConstraints.Enabled)) { System.Media.SystemSounds.Exclamation.Play(); return; } Settings.Default.DatabasePath = browsedbpath.Text; Settings.Default.BackupLocation = browsebackupdbpath.Text; if (!CreateRootFolder(browsedbpath.Text)) { return; } try { Boolean flag; Settings.Default.Save(); dboperation operation = new dboperation(); SqlCeConnection conn = null; if (!File.Exists(Settings.Default.DatabasePath + "\\" + Global.ROOT_DATA_FOLDER + "\\" + Global.DB_NAME_PREFIX + ".sdf")) { flag = operation.createDatabase(Settings.Default.DatabasePath); } else { throw new Exception("Database already exist in selected path"); } if (flag) { conn = operation.dbConnection(Settings.Default.DatabasePath); string tabflag = operation.createTable(conn); if (tabflag == "s") { MessageBox.Show("Database Created Successfully"); } else { MessageBox.Show("Error creating database"); } } else { MessageBox.Show("An error Occured to create the Database or Database already Exist"); } } catch (Exception ex) { string message = "An error occurred to save the settings." + "\nThe error text is as follows: \n" + Global.getExceptionText(ex); SystemSounds.Hand.Play(); MessageBox.Show(message, "Error Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } this.Close(); }
private void catagoryInitiation_Activated(object sender, EventArgs e) { try { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); load_datagridview(conn); operation.closeDBConnection(conn); } catch (Exception ex) { MessageBox.Show("Error Loading database tables"); } }
private void fetchDetails() { dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); SqlCeCommand cmd = null; byte[] photo_array = null; SqlCeDataReader reader = null; MemoryStream ms = new MemoryStream(); if (complogopb.Image != null) { complogopb.Image.Save(ms, ImageFormat.Png); photo_array = new byte[ms.Length]; ms.Position = 0; ms.Read(photo_array, 0, photo_array.Length); } string selectquery = "SELECT Id,companyName, companyAddr, companyMobile, companyEmail, companygstin, companyVAT, companyCST, companyPAN, regkey,prefix,logo FROM regdetails WHERE Id=1"; cmd = new SqlCeCommand(selectquery, conn); try { reader = cmd.ExecuteReader(); while (reader.Read()) { compnametb.Text = reader.GetString(1); compaddrtb.Text = reader.GetString(2); compmbltb.Text = reader.GetString(3); compemail.Text = reader.GetString(4); gstuintb.Text = reader.GetString(5); compvattb.Text = reader.GetString(6); compcsttb.Text = reader.GetString(7); comppantb.Text = reader.GetString(8); regkeytb.Text = "Hidden"; prefix.Text = reader.GetString(10); photo_array = (byte[])reader["logo"]; MemoryStream mes = new MemoryStream(photo_array); var bitmap = new Bitmap(mes); complogopb.Image = bitmap; } } catch (Exception ex) { // MessageBox.Show("" + ex); } }
private void billing_Load(object sender, EventArgs e) { if (!withoutstock.Checked) { label11.Visible = false; manualinvoiceno.Visible = false; manualprefix.Visible = false; label17.Visible = false; manualinvoiceno.Text = ""; } else { label11.Visible = true; manualinvoiceno.Visible = true; manualprefix.Visible = true; label17.Visible = true; } dboperation operation = new dboperation(); SqlCeConnection conn = null; SqlCeDataReader reader = null; SqlCeDataReader reader2 = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string selectquery = "SELECT sub_catagory.ItemName,catagory.Catagory FROM sub_catagory INNER JOIN catagory ON sub_catagory.undercatagory=catagory.CatId ORDER BY sub_catagory.ItemName"; try { SqlCeCommand cmd = new SqlCeCommand(selectquery, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { searchItemcb.Items.Add(reader.GetString(0) + "_ -" + reader.GetString(1)); } string query2 = "SELECT prefix FROM regdetails WHERE Id=1"; SqlCeCommand cmd2 = new SqlCeCommand(query2, conn); reader2 = cmd2.ExecuteReader(); while (reader2.Read()) { prefix = reader2.GetString(0); } operation.closeDBConnection(conn); } catch (Exception ex) { } }
private void catRmv_Click(object sender, EventArgs e) { var removecat = new rmvCatagory(); foreach (DataGridViewRow row in catDataShow.SelectedRows) { removecat.catnamermv.Text = row.Cells[1].Value.ToString(); removecat.catmurmv.Text = row.Cells[2].Value.ToString(); removecat.catsgstrmv.Text = row.Cells[3].Value.ToString(); removecat.catcgstrmv.Text = row.Cells[4].Value.ToString(); removecat.rmvcid.Text = row.Cells[0].Value.ToString(); removecat.ShowDialog(); } dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); load_datagridview(conn); operation.closeDBConnection(conn); }
private void button2_Click(object sender, EventArgs e) { var editsubcat = new editSubCatagory(); foreach (DataGridViewRow row in subCatList.SelectedRows) { editsubcat.subcatnameupdate.Text = row.Cells[1].Value.ToString(); editsubcat.subcatppupdate.Text = row.Cells[5].Value.ToString(); editsubcat.subcatspupdate.Text = row.Cells[4].Value.ToString(); editsubcat.subcatqtyupdate.Text = row.Cells[3].Value.ToString(); editsubcat.scid.Text = row.Cells[0].Value.ToString(); editsubcat.ShowDialog(); } dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); load_subcat_gridview(conn); operation.closeDBConnection(conn); }
private void button1_Click_1(object sender, EventArgs e) { var removesubcat = new rmvSubCatagory(); foreach (DataGridViewRow row in subCatList.SelectedRows) { removesubcat.subcatnamermv.Text = row.Cells[1].Value.ToString(); removesubcat.subcatpprmv.Text = row.Cells[2].Value.ToString(); removesubcat.subcatqtyrmv.Text = row.Cells[3].Value.ToString(); removesubcat.subcatsprmv.Text = row.Cells[4].Value.ToString(); removesubcat.rmvscid.Text = row.Cells[0].Value.ToString(); removesubcat.ShowDialog(); } dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); load_subcat_gridview(conn); operation.closeDBConnection(conn); }
private void prevInvoice_Load(object sender, EventArgs e) { dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT prefix FROM regdetails WHERE Id=1"; SqlCeCommand cmd = new SqlCeCommand(query, conn); SqlCeDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader.Read()) { prefixtb.Text = reader.GetString(0); } } catch (Exception ex) { MessageBox.Show("" + ex); } }
private void searchItemcb_TextChanged(object sender, EventArgs e) { if (searchItemcb.Text == "") { currentstocklbl.Text = ""; } dboperation operation = new dboperation(); SqlCeConnection conn = null; SqlCeDataReader reader = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT sub_catagory.ItemId, sub_catagory.ItemName, sub_catagory.stock_Quantity, sub_catagory.purchase_price, sub_catagory.selling_price, catagory.MeasuringUnit, catagory.Catagory, catagory.sgst, catagory.cgst FROM sub_catagory INNER JOIN catagory ON catagory.CatId = sub_catagory.undercatagory WHERE sub_catagory.ItemName = @itemname"; try { SqlCeCommand cmd = new SqlCeCommand(query, conn); char[] splitchar = { '_' }; string itemcat = searchItemcb.Text; string[] temp = itemcat.Split(splitchar); // Regex.Match(searchItemcb.Text, @"\b($ $)\b") cmd.Parameters.AddWithValue("@itemname", temp[0]); reader = cmd.ExecuteReader(); while (reader.Read()) { itemid = reader.GetInt64(0).ToString(); stock_qty = double.Parse(reader.GetString(2)); currentstocklbl.Text = "Current Stock of the " + temp[0] + " is " + stock_qty.ToString() + " " + reader.GetString(5).ToString(); billcat.Text = reader.GetString(6); spricetb.Text = reader.GetDouble(4).ToString(); mu.Text = reader.GetString(5).ToString(); sgsttb.Text = reader.GetDouble(7).ToString(); cgsttb.Text = reader.GetDouble(8).ToString(); } } catch (Exception ex) { MessageBox.Show("" + ex); } }
private void subCatagoryInitiation_Load(object sender, EventArgs e) { try { SqlCeDataReader reader = null; dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string query = "SELECT Catagory FROM catagory"; SqlCeCommand cmd = new SqlCeCommand(query, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { // Add Catagory List selectcatcombo.Items.Add(reader.GetString(0)); } load_subcat_gridview(conn); operation.closeDBConnection(conn); } catch (Exception ex) { } }
private void button1_Click(object sender, EventArgs e) { dboperation operation = new dboperation(); if (!operation.keyvalidation()) { this.Close(); thread = new Thread(openreg); thread.SetApartmentState(ApartmentState.STA); thread.Start(); } int flag = 0; if (userselector.Text != null && passwd.Text != null) { flag = Authentication(userselector.Text, passwd.Text); if (flag == 1) { this.Close(); thread = new Thread(openPref); thread.SetApartmentState(ApartmentState.STA); thread.Start(); } else if (flag == 2) { this.Close(); thread = new Thread(openBill); thread.SetApartmentState(ApartmentState.STA); thread.Start(); } else { MessageBox.Show("Incorrect Login Credential", "Error"); passwd.Clear(); } } }
private void stocksearchtxtbox_TextChanged(object sender, EventArgs e) { if (stocksearchtb.Text != null) { dboperation operation = new dboperation(); SqlCeConnection conn = null; string selectquery = "SELECT sub_catagory.ItemId, sub_catagory.ItemName, sub_catagory.stock_Quantity, sub_catagory.purchase_price, sub_catagory.selling_price, catagory.MeasuringUnit, catagory.Catagory FROM sub_catagory INNER JOIN catagory ON catagory.CatId=sub_catagory.undercatagory WHERE sub_catagory.ItemName LIKE @itemname"; conn = operation.dbConnection(Settings.Default.DatabasePath); try { SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectquery, conn); adapter.SelectCommand.Parameters.AddWithValue("@itemname", "%" + stocksearchtb.Text + "%"); DataTable dt = new DataTable(); itemdetails.AutoGenerateColumns = false; adapter.Fill(dt); itemdetails.DataSource = dt; } catch (Exception ex) { MessageBox.Show("" + ex); } } }
private void button1_Click(object sender, EventArgs e) { float pp, sp, qty; int qtyint; Regex rx = new Regex("([_])"); if (subcattb.Text == "") { MessageBox.Show("Please enter a name of the item"); } else if (selectcatcombo.Text == "") { MessageBox.Show("Please select a catagory of the item"); } else if (stockqty.Text == "") { MessageBox.Show("Please enter stock quantity of the item"); } else if (pprice.Text == "") { MessageBox.Show("Please enter purchased price per unit basis or float value"); } else if (sellprice.Text == "") { MessageBox.Show("Please enter selling price per unit basis"); } else if (!float.TryParse(pprice.Text, out pp)) { MessageBox.Show("Please enter float value in Purchased price field"); } else if (!float.TryParse(sellprice.Text, out sp)) { MessageBox.Show("Please enter float value in Selling price field"); } else if (!float.TryParse(stockqty.Text, out qty)) { MessageBox.Show("Please insert a numeric value in stock Quantity Field"); } else if (rx.IsMatch(subcattb.Text)) { MessageBox.Show("'_' Charecter is not allowed in a sub-catagory Name"); } else { dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string checkdup = "SELECT sub_catagory.ItemName,catagory.Catagory FROM sub_catagory INNER JOIN catagory ON sub_catagory.undercatagory = catagory.CatId"; if (mulabel.Text == "Measuring Unit: pcs") { if (!int.TryParse(stockqty.Text, out qtyint)) { MessageBox.Show("Please mention integer value in stock quantity field when the selected item is measure in \"pcs\""); } else { if (sub_duplicate(conn, subcattb.Text, selectcatcombo.Text, checkdup)) { MessageBox.Show("Duplicate entry found in the list, try other name"); } else { InsertData(); } } } else { if (sub_duplicate(conn, subcattb.Text, selectcatcombo.Text, checkdup)) { MessageBox.Show("Duplicate entry found in the list, try other name"); } else { InsertData(); } } // Else Statement for other unit goes here } }
private void button1_Click(object sender, EventArgs e) { float stax, ctax; Regex rx = new Regex("([_])"); if (insertcattextbox.Text == "") { MessageBox.Show("Please enter a catagory name"); } else if (mucombobox.Text == "") { MessageBox.Show("Please select a measuring unit to measure the catagory of items"); } else if (!float.TryParse(sgsttb.Text, out stax)) { MessageBox.Show("Please enter only numeric value in SGST field"); } else if (!float.TryParse(cgsttb.Text, out ctax)) { MessageBox.Show("Please enter only numeric value in CGST field"); } else if (float.Parse(cgsttb.Text) > 99 || float.Parse(cgsttb.Text) < 0) { MessageBox.Show("CGST value is unacceptable (0 - 99)"); } else if (float.Parse(sgsttb.Text) > 99 || float.Parse(sgsttb.Text) < 0) { MessageBox.Show("SGST value is unacceptable (0 - 99)"); } else if (rx.IsMatch(insertcattextbox.Text)) { MessageBox.Show("'_' Charecter is not allowed in a Catagory Name"); } else { try { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); if (!duplicate(conn, insertcattextbox.Text)) { string insertquery = "INSERT INTO catagory(Catagory,sgst,cgst,MeasuringUnit) VALUES(@cat,@sgst,@cgst,@mu)"; SqlCeCommand cmd = new SqlCeCommand(insertquery, conn); cmd.Parameters.AddWithValue("@cat", insertcattextbox.Text); cmd.Parameters.AddWithValue("@sgst", sgsttb.Text); cmd.Parameters.AddWithValue("@cgst", cgsttb.Text); cmd.Parameters.AddWithValue("@mu", mucombobox.Text); int result = cmd.ExecuteNonQuery(); if (result < 0) { MessageBox.Show("Error Inserting New User"); return; } insertcattextbox.Clear(); mucombobox.ResetText(); sgsttb.Clear(); cgsttb.Clear(); bool flag = false; flag = load_datagridview(conn); if (flag == true) { // MessageBox.Show("Table Loading Complete"); } else { MessageBox.Show("Error occured to load the table from the database"); } } else { MessageBox.Show("Error! Please try a different name as a catagory"); } operation.closeDBConnection(conn); } catch (Exception ex) { } } }
private void content1() { List <int> bucket = new List <int>(); DateTime fdt, tdt; fdt = fromdtp.Value; tdt = todtp.Value; dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string query1 = "SELECT InvoiceNo, totalSGST, totalCGST, totalDiscount, InvoiceTime, totalItemPrice FROM customerDetails WHERE InvoiceTime>=@start AND InvoiceTime<=@end"; // 0 1 2 3 4 5 string query2 = "SELECT ItemName, quantity, TotalSellingPrice, TotalPurchasedPrice, perItemSGST, perItemCGST, discountPerItemType FROM invoiceDetails WHERE InvoiceNo="; SqlCeCommand cmd = new SqlCeCommand(query1, conn); SqlCeDataReader reader = null; try { cmd.Parameters.AddWithValue("@start", fdt); cmd.Parameters.AddWithValue("@end", tdt); reader = cmd.ExecuteReader(); while (reader.Read()) { invoiceno = reader.GetInt64(0).ToString(); totaldiscount = reader.GetString(3); totalsgst = reader.GetString(1); totalcgst = reader.GetString(2); totalsp = reader.GetString(5); sheet.Cells[rowcounter, 1] = invoiceno; sheet.Cells[rowcounter, 2] = reader.GetDateTime(4).ToString("dd/MM/yyyy"); getinfoperinvoice(conn, query2 + invoiceno); string totalPP = purchashed_price.ToString(); // Insert data to excel; sheet.Cells[rowcounter, 2] = "Total"; sheet.Cells[rowcounter, 5] = totalPP; sheet.Cells[rowcounter, 6] = totalsp; sheet.Cells[rowcounter, 7] = totaldiscount; sheet.Cells[rowcounter, 8] = totalsgst; sheet.Cells[rowcounter, 9] = totalcgst; sheet.Cells[rowcounter, 9].EntireRow.Font.Bold = true; sheet.Cells[rowcounter, 10] = double.Parse(totalsp) - double.Parse(totalPP) - double.Parse(totaldiscount); bucket.Add(rowcounter); rowcounter++; } if (bucket.Count > 0) { string grandpp, grandsp, grandsgst, grandcgst, granddisc, grandprofit; grandpp = "=SUM("; grandsp = "=SUM("; grandsgst = "=SUM("; grandcgst = "=SUM("; granddisc = "=SUM("; grandprofit = "=SUM("; for (int i = 0; i < bucket.Count; i++) { // Function for total Sum if (grandpp == "=SUM(") { grandpp += " E" + bucket[i]; grandsp += " F" + bucket[i]; grandsgst += " H" + bucket[i]; grandcgst += " I" + bucket[i]; granddisc += " G" + bucket[i]; grandprofit += " J" + bucket[i]; } else { grandpp += " , E" + bucket[i]; grandsp += " , F" + bucket[i]; grandsgst += " , H" + bucket[i]; grandcgst += " , I" + bucket[i]; granddisc += " , G" + bucket[i]; grandprofit += " , J" + bucket[i]; } } grandpp += " )"; grandsp += " )"; grandsgst += " )"; grandcgst += " )"; granddisc += " )"; grandprofit += " )"; rowcounter++; sheet.Range["E" + rowcounter].Formula = grandpp; sheet.Range["F" + rowcounter].Formula = grandsp; sheet.Range["H" + rowcounter].Formula = grandsgst; sheet.Range["I" + rowcounter].Formula = grandcgst; sheet.Range["G" + rowcounter].Formula = granddisc; sheet.Range["J" + rowcounter].Formula = grandprofit; sheet.Cells[rowcounter, 2] = "GRAND TOTAL"; sheet.Cells[rowcounter, 3].EntireRow.Font.Bold = true; sheet.get_Range("A" + rowcounter, "J" + rowcounter).Cells.Font.Size = 14; } } catch (Exception ex) { MessageBox.Show("" + ex); } }
private void catAddbtn_Click(object sender, EventArgs e) { float stax, ctax; Regex rx = new Regex("([_])"); string query = "SELECT Catagory FROM catagory WHERE CatId!=" + cid.Text; if (catnameupdate.Text == "") { MessageBox.Show("Please enter a catagory name"); } else if (updatemu.Text == "") { MessageBox.Show("Please select a measuring unit to measure the catagory of items"); } else if (!float.TryParse(updatecgst.Text, out stax)) { MessageBox.Show("Please enter only numeric value in SGST field"); } else if (!float.TryParse(updatecgst.Text, out ctax)) { MessageBox.Show("Please enter only numeric value in CGST field"); } else if (float.Parse(updatecgst.Text) > 99 || float.Parse(updatecgst.Text) < 0) { MessageBox.Show("CGST value is unacceptable (0 - 99)"); } else if (float.Parse(updatesgst.Text) > 99 || float.Parse(updatesgst.Text) < 0) { MessageBox.Show("SGST value is unacceptable (0 - 99)"); } else if (rx.IsMatch(catnameupdate.Text)) { MessageBox.Show("'_' Charecter is not allowed in a Catagory Name"); } else { try { dboperation operation = new dboperation(); SqlCeConnection conn = null; conn = operation.dbConnection(Settings.Default.DatabasePath); string updatequery = "UPDATE catagory SET Catagory=@cat , sgst=@sgst, cgst=@cgst , MeasuringUnit=@mu WHERE CatId= @id"; SqlCeCommand cmd = new SqlCeCommand(updatequery, conn); if (!duplicate(conn, catnameupdate.Text, query)) { cmd.Parameters.AddWithValue("@cat", catnameupdate.Text); cmd.Parameters.AddWithValue("@sgst", updatesgst.Text); cmd.Parameters.AddWithValue("@cgst", updatecgst.Text); cmd.Parameters.AddWithValue("@mu", updatemu.Text); cmd.Parameters.AddWithValue("@id", cid.Text); // MessageBox.Show(updatequery); int result = cmd.ExecuteNonQuery(); operation.closeDBConnection(conn); if (result != 0) { MessageBox.Show("Data Updated Successfully"); } } else { MessageBox.Show("Duplicate entry found in Database, please try another name"); } } catch (Exception ex) { MessageBox.Show("An Error occured to update the Catagory list " + ex); } this.Close(); } }
private void searchinvoicebtn_Click(object sender, EventArgs e) { FinalInvoice invoice = new FinalInvoice(); // Disabling the controls of FinalInvoice invoice.custNametb.Enabled = false; invoice.addrtb.Enabled = false; invoice.mobiletb.Enabled = false; invoice.paymenttypecb.Enabled = false; invoice.paymentDetailstb.Enabled = false; invoice.finalsgsttb.Enabled = false; invoice.finalcgsttb.Enabled = false; dboperation operation = new dboperation(); SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath); string query1 = "SELECT InvoiceNo, customerName, custAddress, custMobile, paymentDetails, totalSGST, totalCGST, totalDiscount, percentSGST, percentCGST, InvoiceTime FROM customerDetails WHERE InvoiceNo=@invoiceno"; // DATEPART(day,InvoiceTime), DATEPART(month,InvoiceTime), DATEPART(year,InvoiceTime) string query2 = "SELECT InvoiceNo,ItemName,quantity,TotalSellingPrice,TotalPurchasedPrice,perItemSGST,perItemCGST,discountPerItemType,measuringUnit,description FROM invoiceDetails WHERE InvoiceNo=@invoiceno"; // 0 1 2 3 4 5 6 7 8 9 string invoiceresult = ""; SqlCeCommand cmd1 = null; SqlCeCommand cmd2 = null; SqlCeDataReader reader = null; try { cmd1 = new SqlCeCommand(query1, conn); cmd1.Parameters.AddWithValue("@invoiceno", invoicenotb.Text); reader = cmd1.ExecuteReader(); if (reader.Read()) { string temp; string[] separator; char[] tag = new char[] { '^' }; invoiceresult = reader.GetInt64(0).ToString(); invoice.invoicenumber = invoiceresult; invoice.custNametb.Text = reader.GetString(1); invoice.addrtb.Text = reader.GetString(2); invoice.mobiletb.Text = reader.GetString(3); temp = reader.GetString(4); separator = temp.Split(tag); invoice.paymenttypecb.Text = separator[0]; if (separator.Length > 1) { invoice.paymentDetailstb.Text = separator[1]; } else { invoice.paymentDetailstb.Text = ""; } invoice.finalsgsttb.Text = reader.GetString(8); invoice.finalcgsttb.Text = reader.GetString(9); invoice.dt = reader.GetDateTime(10);//Convert.ToDateTime(reader.GetInt32(10).ToString() + "/" + reader.GetInt32(11).ToString() + "/" + reader.GetInt32(12)); } else { MessageBox.Show("Invoice No: " + invoicenotb.Text + " not exist in the Database"); } if (invoiceresult != "") { int count = 0; cmd2 = new SqlCeCommand(query2, conn); SqlCeDataReader read = null; cmd2.Parameters.AddWithValue("@invoiceno", invoiceresult); read = cmd2.ExecuteReader(); while (read.Read()) { invoice.ItemList.Rows.Add(); invoice.ItemList.Rows[count].Cells[1].Value = read.GetString(1); // Item Name invoice.ItemList.Rows[count].Cells[2].Value = read.GetString(9); // Description invoice.ItemList.Rows[count].Cells[3].Value = read.GetString(2); // Quantity invoice.ItemList.Rows[count].Cells[4].Value = read.GetString(8); // Measuring Unit invoice.ItemList.Rows[count].Cells[5].Value = read.GetString(3); // Total Selling Price invoice.ItemList.Rows[count].Cells[6].Value = read.GetString(7); // Total Discount per item invoice.ItemList.Rows[count].Cells[7].Value = read.GetDouble(5); // percent SGST invoice.ItemList.Rows[count].Cells[8].Value = read.GetDouble(6); // percent CGST count++; } invoice.searchflag = true; invoice.ShowDialog(); } } catch (Exception ex) { MessageBox.Show("" + ex); } }