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 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 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;
}
}
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);
}
// 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 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 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 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 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 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 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 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 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 regbtn_Click(object sender, EventArgs e)
{
if (flag)
{
updateInformation();
}
else
{
if (compnametb.Text == "")
{
MessageBox.Show("Company Name can't be empty");
}
else if (compaddrtb.Text == "")
{
MessageBox.Show("Company Address can't be empty");
}
else if (compmbltb.Text == "")
{
MessageBox.Show("Company Mobile number can't be empty");
}
else if (gstuintb.Text == "")
{
MessageBox.Show("GSTIN/UIN field can't be empty");
}
else if (regkeytb.Text == "")
{
MessageBox.Show("Please, specify a registration key or Contact [email protected]");
}
else if (prefix.Text == "")
{
MessageBox.Show("Please specify a prefix for invoice number");
}
else if (compemail.Text == "")
{
compemail.Text = "0";
}
else if (compvattb.Text == "")
{
compvattb.Text = "0";
}
else if (compcsttb.Text == "")
{
compcsttb.Text = "0";
}
else if (comppantb.Text == "")
{
comppantb.Text = "0";
}
else
{
dboperation operation = new dboperation();
if (!(operation.keyvalidation(regkeytb.Text, gstuintb.Text)))
{
MessageBox.Show("Your Key is not Valid");
this.Close();
}
if (Settings.Default.DatabasePath == "" || !File.Exists(Settings.Default.DatabasePath + "\\" + Global.ROOT_DATA_FOLDER + "\\" + Global.DB_NAME_PREFIX + ".sdf"))
{
FilePath setpath = new FilePath();
setpath.ShowDialog();
}
else
{
SqlCeConnection conn = null;
SqlCeCommand cmd = null;
int flag;
byte[] photo_array = null;
MemoryStream ms = new MemoryStream();
if (complogopb.Image == null)
{
complogopb.Image = Resources.defaultlogo;
}
complogopb.Image.Save(ms, ImageFormat.Png);
photo_array = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_array, 0, photo_array.Length);
string insertquery = "INSERT INTO regdetails(Id,companyName, companyAddr, companyMobile, companyEmail, companygstin, companyVAT, companyCST, companyPAN, regkey, prefix, logo) VALUES(@compid,@cname, @caddr, @mbl, @email, @gstin, @vat, @cst, @pan,@key,@prefix,@img)";
try
{
conn = operation.dbConnection(Settings.Default.DatabasePath);
cmd = new SqlCeCommand(insertquery, conn);
cmd.Parameters.AddWithValue("@compid", "1");
cmd.Parameters.AddWithValue("@cname", compnametb.Text);
cmd.Parameters.AddWithValue("@caddr", compaddrtb.Text);
cmd.Parameters.AddWithValue("@mbl", compmbltb.Text);
cmd.Parameters.AddWithValue("@email", compemail.Text);
cmd.Parameters.AddWithValue("@gstin", gstuintb.Text);
cmd.Parameters.AddWithValue("@vat", compvattb.Text);
cmd.Parameters.AddWithValue("@cst", compcsttb.Text);
cmd.Parameters.AddWithValue("@pan", comppantb.Text);
cmd.Parameters.AddWithValue("@key", regkeytb.Text);
cmd.Parameters.AddWithValue("@prefix", prefix.Text);
cmd.Parameters.AddWithValue("@img", photo_array);
flag = cmd.ExecuteNonQuery();
if (flag < 0)
{
MessageBox.Show("Error User to Register");
this.Close();
}
else
{
this.Close();
thread = new Thread(openlogin);
thread.SetApartmentState(ApartmentState.STA);
thread.Start();
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured to creating database" + 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);
}
}
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 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 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();
}
}
}
}