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 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 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 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);
}
}
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 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 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 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 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)
{
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 subcatrmvbtn_Click(object sender, EventArgs e)
{
string selectquery = "SELECT catagory.MeasuringUnit FROM catagory INNER JOIN sub_catagory ON catagory.CatId=sub_catagory.undercatagory WHERE undercatagory!=" + scid.Text;
string dupquery = "SELECT ItemName FROM sub_catagory WHERE ItemId != " + scid.Text;
float pp, sp, qty;
int qtyint, addqty;
Regex rx = new Regex("([_])");
if (subcatnameupdate.Text == "")
{
MessageBox.Show("Please enter Item name");
}
else if (subcatppupdate.Text == "")
{
MessageBox.Show("Please select a measuring unit to measure the catagory of items");
}
else if (!float.TryParse(subcatppupdate.Text, out pp))
{
MessageBox.Show("Please enter float value in Purchased price field");
}
else if (!float.TryParse(subcatspupdate.Text, out sp))
{
MessageBox.Show("Please enter float value in Selling price field");
}
else if (!float.TryParse(subcatqtyupdate.Text, out qty))
{
MessageBox.Show("Please insert a numeric value in stock Quantity Field");
}
else if (rx.IsMatch(subcatnameupdate.Text))
{
MessageBox.Show("'_' Charecter is not allowed in a sub-catagory Name");
}
else
{
if (getUnit(selectquery) == "pcs")
{
if ((!int.TryParse(subcatqtyupdate.Text, out qtyint)) || (!int.TryParse(addqtytb.Text, out qtyint)))
{
MessageBox.Show("Please mention integer value in stock quantity or quantity to add field when the selected item is measure in \"pcs\"");
}
else
{
float total;
total = float.Parse(addqtytb.Text) + float.Parse(subcatqtyupdate.Text);
try
{
dboperation operation = new dboperation();
SqlCeConnection conn = null;
conn = operation.dbConnection(Settings.Default.DatabasePath);
string updatequery = "UPDATE sub_catagory SET ItemName=@item , stock_Quantity=@qty , purchase_price=@pp, selling_price=@sp WHERE ItemId= @id";
SqlCeCommand cmd = new SqlCeCommand(updatequery, conn);
if (!duplicate(conn, subcatnameupdate.Text, dupquery))
{
cmd.Parameters.AddWithValue("@item", subcatnameupdate.Text);
cmd.Parameters.AddWithValue("@qty", total.ToString());
cmd.Parameters.AddWithValue("@pp", pp.ToString());
cmd.Parameters.AddWithValue("@sp", sp.ToString());
cmd.Parameters.AddWithValue("@id", scid.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 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();
}
}
