private void verifylogin()
{
string password = Encryptordecrypt.Decript_String(txtpass.Text);
sqlconnectionclass read4login = new sqlconnectionclass();
DR = read4login.ReadDB("select [user_id],[username],[password],[userlevel],[status] from [users] where username COLLATE Latin1_General_CS_AS='" + txtusername.Text + "' and [password] COLLATE Latin1_General_CS_AS='" + password + "'");
if (DR.HasRows)
{
sqlconnectionclass checkstatus = new sqlconnectionclass();
DR1 = checkstatus.ReadDB("select [user_id],[username],[password],[userlevel],[status] from [users] where username COLLATE Latin1_General_CS_AS='" + txtusername.Text + "' and [password] COLLATE Latin1_General_CS_AS='" + password + "'and status = 'active'");
if (DR1.HasRows)
{
while (DR.Read())
{
this.DialogResult = DialogResult.OK;
//usernamemenu.FindForm(Menu).Text = DR["username"].ToString();
userid = DR["user_id"].ToString();
UserName = DR["username"].ToString();
userlevel = DR["userlevel"].ToString();
string user = "******" + Form1.UserName;
//string insert = "insert into usersession(user_id,logintime,logouttime)values('" + Form1.userid + "','" + System.DateTime.Now + "','" + System.DateTime.Now + "')";
//new sqlconnectionclass().WriteDB(insert);
foreach (Form f in Application.OpenForms)
{
f.Text = user;
}
}
}
else
{
MessageBox.Show("User account" + txtusername.Text + " is deactivated \n Contact the administrator");
Application.Exit();
}
}
else if (attempts >= 3)
{
MessageBox.Show("Maximum number of attempts" + "\r\n the user account is deactivated!", "Attempts Exceeded", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Application.Exit();
}
else
{
sqlconnectionclass checkuserexist = new sqlconnectionclass();
DR2 = checkuserexist.ReadDB("select user_id,username,password from users where username COLLATE Latin1_General_CS_AS='" + txtusername.Text + "'");
if (DR.HasRows)
{
MessageBox.Show("Username and password incorrect, Please try again \r\n Current attempts:" + string.Concat(attempts, " of 3"), "Attempts space", MessageBoxButtons.OK, MessageBoxIcon.Error);
attempts += 1;
//txtusername.Text = "";
txtpass.Text = "";
}
else
{
MessageBox.Show("Username" + txtusername.Text + " doesn't exist");
}
}
}
private void creditbalance()
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select ((select ISNULL(SUM(amount), 0)as availab from Credit_sales where credit_customerID= '" + cust_id + "')-(select ISNULL(SUM(amount), 0)as qty from creditPayment where credit_customerID= '" + cust_id + "')) As available from Credit_Customers where credit_customerID='" + cust_id + "'");
if (DR.Read())
{
bal = Convert.ToInt32(DR[0]);
}
}
private void AddCart()
{
//throw new NotImplementedException();
try
{
//if ((Convert.ToInt32(textBoxUnitPrice.Text) <= Convert.ToInt32(label12.Text)))
//{
if (textBoxProductName.Text != "" && textBoxUnitPrice.Text != "" && textBoxQuantity.Text != "" && txtBoxVat.Text != "" && textBoxTotal.Text != "")
{
if (rdunknown.Checked == true || (Convert.ToInt32(textBoxQuantity.Text) <= Convert.ToInt32(textBox1.Text)))
{
//Receipt obj = new Receipt() { Id = ReceiptNo++, ProductName = textBoxProductName.Text, unitprice = Convert.ToInt32(textBoxUnitPrice.Text), Quantity = Convert.ToInt32(textBoxQuantity.Text), VAT = Convert.ToDouble(txtBoxVat.Text) };
//total += obj.unitprice * obj.Quantity;
//receiptBindingSource.Add(obj);
//receiptBindingSource.MoveLast();
//adds to the text box
if (textBoxProductName.TextLength > 18)
{
productname = textBoxProductName.Text.Substring(0, 18);
}
else
{
productname = textBoxProductName.Text;
}
table.Rows.Add(pid, productname, textBoxUnitPrice.Text, textBoxQuantity.Text, txtBoxVat.Text, textBoxTotal.Text);
dataGridView1.DataSource = table;
totaladd = Convert.ToInt32(textBoxTotal.Text);
totalamount = Convert.ToInt32(textBoxTotalAmount.Text) + totaladd;
textBoxTotalAmount.Text = totalamount.ToString();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where ProductName='" + itemsearchBox1.Text + "'");
}
else
{
MessageBox.Show("Quantity cannot exceed Stock Quantity");
}
textBoxTotal.Text = ""; textBoxQuantity.Text = ""; textBoxProductName.Text = ""; txtBoxVat.Text = ""; textBoxUnitPrice.Text = "";
}
else
{
MessageBox.Show("Fields can't be empty!");
}
// }
//else
//{
// MessageBox.Show("Item cannot be sold UnderPrice!");
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string pid = comboBox1.Text.Split(':')[0];
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR1 = selecttousers.ReadDB("select baseqty,((select isnull(sum(qty),0)as availab from stockin where ProductNo= '" + pid + "')-(select isnull(sum(sqty),0)as qty from sales where ProductNo= '" + pid + "')) As available from products where ProductNo='" + pid + "'");
while (DR1.Read())
{
textBox1.Text = DR1[1].ToString();
}
}
private void GenerateStatementBtn_Click(object sender, EventArgs e)
{
//try
//{
sqlconnectionclass read4report = new sqlconnectionclass();
DR = read4report.ReadDB("SELECT stockin.rep_no, products.productName,stockin.qty, stockin.unitprice,stockin.vat,stockin.totalcost,stockin.greplenishno,stockin.suppliername, stockin.date FROM stockin INNER JOIN products ON products.productno=stockin.productno where stockin.Date between '" + DateTime.Parse(dateTimePickerfrom.Text) + "' and '" + DateTime.Parse(dateTimePickerTo.Text) + "'");//'" + DateTime.Parse(dtpfrom.Text) + "' and date<='" + DateTime.Parse(dtpto.Text) + "' "
if (DR.HasRows)
{
}
}
private void button1_Click(object sender, EventArgs e)
{
sqlconnectionclass read4report = new sqlconnectionclass();
DR = read4report.ReadDB("SELECT dbo.GLedger.Date, dbo.GLedger.Naration, ISNULL(dbo.GLedger.Debit, 0) AS Debit, ISNULL(dbo.GLedger.Credit, 0) AS Credit, dbo.GLedger.Balance, dbo.GLedger.[user] FROM dbo.credit_sales LEFT OUTER JOIN dbo.Credit_Customers ON dbo.credit_sales.credit_customerID = dbo.Credit_Customers.credit_customerID LEFT OUTER JOIN dbo.GLedger ON dbo.Credit_Customers.credit_customerID = dbo.GLedger.credit_customerID LEFT OUTER JOIN dbo.creditPayment ON dbo.GLedger.credit_customerID = dbo.creditPayment.credit_customerID WHERE (dbo.Credit_Customers.BussinessName = '" + cmbCreditorsName.Text + "') AND (dbo.GLedger.Date BETWEEN '" + dateTimePicker1.Value + "' AND '" + dateTimePicker2.Value + "') GROUP BY ISNULL(dbo.GLedger.Debit, 0), ISNULL(dbo.GLedger.Credit, 0), dbo.GLedger.Balance, dbo.GLedger.credit_customerID, dbo.Credit_Customers.BussinessName, dbo.GLedger.Naration, dbo.GLedger.Date, dbo.GLedger.[user]");
if (DR.HasRows)
{
CRStatement cr = new CRStatement();
cr.Show();
}
}
private void listprodu()
{
//throw new NotImplementedException();
List <String> productlist = new List <string>();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where itemtype='SI'");
while (DR.Read())
{
productlist.Add(DR[0] + ":" + DR[1]);
}
listBox1.DataSource = productlist;
}
private void cmbBoxVATCodes_SelectedIndexChanged(object sender, EventArgs e)
{
sqlconnectionclass selectusers = new sqlconnectionclass();
DR1 = selectusers.ReadDB("select * from VAT where VAT_Codes='" + cmbBoxVATCodes.Text + "'");
//DataTable dt = new DataTable();
while (DR1.Read())
{
string VAT_Percentage = (string)DR1["VAT_Percentage"].ToString();
textBoxRates.Text = VAT_Percentage;
}
}
void fill_combo1()
{
cmbBoxVATCodes.Items.Clear();
sqlconnectionclass selectusers = new sqlconnectionclass();
DR1 = selectusers.ReadDB("select * from VAT");
if (DR1.HasRows)
{
while (DR1.Read())
{
cmbBoxVATCodes.Items.Add(DR1[0]);
}
}
}
private void txtSearch_SelectedIndexChanged(object sender, EventArgs e)
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where ProductNo='" + txtSearch.Text.Split(':')[0] + "'");
//DataTable dt = new DataTable();
while (DR.Read())
{
string ProductName = (string)DR["ProductName"].ToString();;
txtDescription.Text = ProductName;
string ProductNo = (string)DR["ProductNo"].ToString();;
textBoxProductNo.Text = ProductNo;
string BarCode = (string)DR["BarCode"].ToString();
txtBarcode.Text = BarCode;
string Reorderlevel = (string)DR["Reorderlevel"].ToString();;
txtReorderLevel.Text = Reorderlevel;
string Category = (string)DR["Category"].ToString();;
txtCategory.Text = Category;
string PackageMode = (string)DR["PackageMode"].ToString();
cmbBoxParkageMode.Text = PackageMode;
string PartNo = (string)DR["PartNo"].ToString();
textBoxPartNo.Text = PartNo;
string Shelf = (string)DR["Shelf"].ToString();
textBoxShelf.Text = Shelf;
string Vat_Code = (string)DR["Vat_Code"].ToString();
cmbBoxVATCodes.Text = Vat_Code;
string Vat_Rate = (string)DR["Vat_Rate"].ToString();
textBoxRates.Text = Vat_Rate;
if (DR[10].ToString() == "OI")
{
rdoitem.Checked = true; textBox4.Text = DR["baseqty"].ToString();
}
else if (DR[10].ToString() == "SI")
{
rdsitem.Checked = true;
}
}
}
static void Main()
{
System.Data.SqlClient.SqlDataReader DR;
//Application.Run(new Sales());
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
sqlconnectionclass read4login = new sqlconnectionclass();
DR = read4login.ReadDB("select * from [users]");
if (DR.HasRows)
{
while (DR.Read())
{
Form1 L = new Form1();
DialogResult dr = L.ShowDialog();
if (dr == DialogResult.OK)
{
Home F = new Home();
F.Text = "EasyPOS" + " " + "You are login as: " + Form1.UserName;
Application.Run(F);
}
}
}
else
{
try
{
string datet = System.DateTime.Now.ToString();
string password = Encryptordecrypt.Decript_String("admin123");// Decryptor.Decript_String(textBox1.Text);
string username = "******";
string userlevel = "super_admin";
string inserttousers = ("INSERT INTO users(username,password,userlevel,created_at)values('" + username + "','" + password + "','" + userlevel + "','" + datet + "')");
new sqlconnectionclass().WriteDB(inserttousers);
}
catch (Exception)
{
}
finally
{
Form1 L = new Form1();
DialogResult dr = L.ShowDialog();
if (dr == DialogResult.OK)
{
Home F = new Home();
F.Text = "EasyPOS" + "" + "You are login as: " + Form1.UserName;
Application.Run(F);
}
}
}
}
void fill_combo2()
{
cmbBoxParkageMode.Items.Clear();
sqlconnectionclass selectusers = new sqlconnectionclass();
DR1 = selectusers.ReadDB("select * from Packaging");
if (DR1.HasRows)
{
while (DR1.Read())
{
cmbBoxParkageMode.Items.Add(DR1[0]);
}
}
}
//this is for searchbox(combobox)
void fill_combo3()
{
txtSearch.Items.Clear();
sqlconnectionclass selectusers = new sqlconnectionclass();
DR1 = selectusers.ReadDB("select * from Products ");
if (DR1.HasRows)
{
while (DR1.Read())
{
txtSearch.Items.Add(DR1[0] + ":" + DR1[1] + "-" + DR1[10]);
}
}
}
//combobox for Categories
void fill_combo()
{
txtCategory.Items.Clear();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from P_Category ");
// DR1 = selecttousers.ReadDB("select * from Suppliers ");
while (DR.Read())
{
string Name = DR.GetString(1);
txtCategory.Items.Add(Name);
}
}
void fill_combo1()
{
cmbCreditorsName.Items.Clear();
sqlconnectionclass selectusers = new sqlconnectionclass();
DR = selectusers.ReadDB("select * from Credit_Customers");
if (DR.HasRows)
{
while (DR.Read())
{
cmbCreditorsName.Items.Add(DR[1]);
tel = Convert.ToString(DR[4]);
Add = Convert.ToString(DR[3]);
}
}
}
private void getreceiptno()
{
//throw new NotImplementedException();
sqlconnectionclass getrecip = new sqlconnectionclass();
DR = getrecip.ReadDB("select top 1 receiptno from sales order by sales_id desc");
if (DR.HasRows)
{
DR.Read();
ReceiptNo = (Convert.ToInt32(DR[0]) + 1).ToString("00000000");
}
else
{
ReceiptNo = "00000001";
}
}
void fill_combo()
{
try
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Suppliers");
while (DR.Read())
{
string sname = DR.GetString(1);
comboSupplier.Items.Add(sname);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
void fill_combo()
{
try
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Credit_Customers ");
while (DR.Read())
{
string BusinessName = DR.GetString(1);
cmbCreditorsName.Items.Add(BusinessName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void cmbBusinessname_SelectedIndexChanged(object sender, EventArgs e)
{
sqlconnectionclass selectpid = new sqlconnectionclass();
DR1 = selectpid.ReadDB("select BussinessName,credit_customerID from Credit_Customers where BussinessName= '" + cmbBusinessname.Text + "'");
if (DR1.HasRows)
{
DR1.Read();
pid = DR1["credit_customerID"].ToString();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select ((select ISNULL(SUM(amount), 0)as availab from Credit_sales where credit_customerID= '" + pid + "')-(select ISNULL(SUM(amount), 0)as qty from creditPayment where credit_customerID= '" + pid + "')) As available from Credit_Customers where credit_customerID='" + pid + "'");
//DR = selecttousers.ReadDB("(select ISNULL(SUM(amount), 0) from credit_sales)-(select ISNULL(SUM(amount), 0) from creditPayment)");
if (DR.Read())
{
balancetxt.Text = DR[0].ToString();
}
}
}
private void fillproducts()
{
comboBox2.Items.Clear();
sqlconnectionclass selectusers = new sqlconnectionclass();
DR1 = selectusers.ReadDB("select * from Products where itemtype='OI'");
if (DR1.HasRows)
{
while (DR1.Read())
{
comboBox2.Items.Add(DR1[0] + ":" + DR1[1]);
ProductNod = DR1[0].ToString();
VATd = DR1[12].ToString();
unitpriced = DR1[9].ToString();
}
}
}
// private float bal;
void fill_combo()
{
try
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products ");
while (DR.Read())
{
string ProductName = DR.GetString(1);
itemsearchBox1.Items.Add(ProductName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
void fill_combo1()
{
try
{
comboBoxCustomer.Items.Clear();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Credit_Customers ");
while (DR.Read())
{
string BussinessName = DR.GetString(1);
string id = DR[0].ToString();
comboBoxCustomer.Items.Add(id.ToString() + ": " + BussinessName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void itemsearchBox1_SelectedIndexChanged(object sender, EventArgs e)
{
sqlconnectionclass selectpid = new sqlconnectionclass();
DR1 = selectpid.ReadDB("select ProductNo,vat_rate from Products where productname= '" + itemsearchBox1.Text + "'");
if (DR1.HasRows)
{
DR1.Read();
pid = DR1["ProductNo"].ToString();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select ProductName,(select top 1 unitprice from stockin where productno = '" + pid + "' ORDER BY rep_no DESC ) as currentprice,((select isnull(sum(qty),0)as availab from stockin where ProductNo= '" + pid + "')-(select isnull(sum(sqty),0)as qty from sales where ProductNo= '" + pid + "')) As available from products where ProductNo='" + pid + "'");
while (DR.Read())
{
string ProductName = (string)DR["ProductName"].ToString();;
textBoxProductName.Text = ProductName;
string RetailPrice = (string)DR["currentprice"].ToString();
label11.Visible = true;
label12.Visible = true;
label12.Text = "KES " + RetailPrice;
textBox1.Text = DR[2].ToString();
//itemsearchBox1.Text = "";
}
sqlconnectionclass getstatus = new sqlconnectionclass();
dr2 = getstatus.ReadDB("select count(*) from stockin where productno='" + pid + "'");
if (dr2.HasRows)
{
dr2.Read(); if (Convert.ToInt32(dr2[0]) > 0)
{
rdunknown.Checked = false;
}
else
{
rdunknown.Checked = true;
}
}
}
//textBoxQuantity.Text;
}
private void Search()
{
//throw new NotImplementedException();
textBoxSearch.Text = listBox1.SelectedValue.ToString();
sqlconnectionclass selectpid = new sqlconnectionclass();
DR = selectpid.ReadDB("select ProductNo from Products where productno='" + textBoxSearch.Text.Split(':')[0] + "'");
if (DR.HasRows)
{
DR.Read();
pid = DR["ProductNo"].ToString();
sqlconnectionclass selectpd = new sqlconnectionclass();
DR1 = selectpd.ReadDB("select (select top 1 unitprice from stockin where productno = '" + pid + "' ORDER BY rep_no DESC ) as currentprice,vat_rate,((select isnull(sum(qty),0)as availab from stockin where ProductNo= '" + pid + "')-(select isnull(sum(sqty),0)as qty from sales where ProductNo= '" + pid + "')) As available from Products where ProductNo= '" + pid + "'");
if (DR1.HasRows)
{
DR1.Read();
textBox2.Text = DR1[0].ToString();
textBox8.Text = DR1[2].ToString();
//textBox4.Text = DR1[0].ToString();
Vatrate = Convert.ToDouble(DR1[1]);
}
}
}
private void getCreditno()
{
//throw new NotImplementedException();
if (radioButton2.Checked == true)
{
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select top 1 credit_no from credit_sales ORDER BY credit_no DESC");
if (DR.HasRows)
{
DR.Read();
nextcreditno = Convert.ToInt32(DR[0]) + 1;
}
else
{
nextcreditno = 1;
}
cust_id = label3.Text;
}
else
{
nextcreditno = 0; cust_id = "0";
}
}
private void button1_Click(object sender, EventArgs e)
{
int replenish = 0;
Totalcostd = (Convert.ToInt32(textBox4.Text) * Convert.ToInt32(unitpriced)).ToString();
Totalcosts = (Convert.ToInt32(textBox3.Text) * (-1) * Convert.ToInt32(unitprices)).ToString();
TVATd = (Convert.ToInt32(Totalcostd) * Convert.ToInt32(VATd) / 100).ToString();
TVATs = (Convert.ToInt32(Totalcosts) * Convert.ToInt32(VATs) / 100).ToString();
sqlconnectionclass getrepno = new sqlconnectionclass();
DR1 = getrepno.ReadDB("select top 1 greplenishno from stockin ORDER BY greplenishno DESC");
if (DR1.HasRows)
{
DR1.Read();
replenish = Convert.ToInt32(DR1[0]) + 1;
}
else
{
replenish = 1;
}
float qtys = Convert.ToInt32(textBox3.Text) * (-1);
//save source
string insertsource = "INSERT INTO [stockin]([ProductNo],[Suppliername],[deliverymode],[note_no],[qty],[VAT],[Totalcost],[unitprice],[greplenishno],[date])VALUES('" + ProductNods + "','" + Suppliernames + "','" + deliverymode + "','" + note_no + "','" + qtys + "','" + TVATs + "','" + Totalcosts + "','" + unitprices + "','" + replenish + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertsource);
string insertdes = "INSERT INTO [stockin]([ProductNo],[Suppliername],[deliverymode],[note_no],[qty],[VAT],[Totalcost],[unitprice],[greplenishno],[date])VALUES('" + ProductNod + "','" + Suppliernamed + "','" + deliverymode + "','" + note_no + "','" + textBox4.Text + "','" + TVATd + "','" + Totalcostd + "','" + unitpriced + "','" + replenish + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertdes);
MessageBox.Show("Done converting stock");
textBox1.Text = "0";
textBox2.Text = "0";
textBox3.Text = "0";
textBox4.Text = "0";
comboBox1.SelectedIndex = -1;
comboBox2.SelectedIndex = -1;
}
private void button14_Click(object sender, EventArgs e)
{
try
{
sqlconnectionclass selectpid = new sqlconnectionclass();
DR1 = selectpid.ReadDB("select * from P_Category where cat_id = '30'");
if (DR1.HasRows)
{
// DR1.Read();
//pid = DR1["ProductNo"].ToString();
//sqlconnectionclass selecttousers = new sqlconnectionclass();
//DR = selecttousers.ReadDB("select ProductName,(select top 1 unitprice from stockin where productno = '" + pid + "' ORDER BY rep_no DESC ) as currentprice,((select isnull(sum(qty),0)as availab from stockin where ProductNo= '" + pid + "')-(select isnull(sum(sqty),0)as qty from sales where ProductNo= '" + pid + "')) As available from products where ProductNo='" + pid + "'");
//while (DR.Read())
//{
//string Name = (string)DR["name"].ToString(); ;
//button7.Text = Name;
//string RetailPrice = (string)DR["currentprice"].ToString();
//label11.Visible = true;
//label12.Visible = true;
//label12.Text = "KES " + RetailPrice;
button7.Text = DR["1"].ToString();
//itemsearchBox1.Text = "";
//}
//sqlconnectionclass getstatus = new sqlconnectionclass();
//dr2 = getstatus.ReadDB("select count(*) from stockin where productno='" + pid + "'");
//if (dr2.HasRows) { dr2.Read(); if (Convert.ToInt32(dr2[0]) > 0) { rdunknown.Checked = false; } else { rdunknown.Checked = true; } }
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
{
}
}
private void textBoxSearch_TextChanged(object sender, EventArgs e)
{
if (radioButton1.Checked == true)
{
List <String> productlist = new List <string>();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where ProductName like '%" + textBoxSearch.Text + "%' and itemtype='SI'");
while (DR.Read())
{
productlist.Add(DR[0] + ":" + DR[1]);
}
listBox1.DataSource = productlist;
}
else if (radioButton3.Checked == true)
{
List <String> productlist = new List <string>();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where ProductNo like '%" + textBoxSearch.Text + "%' and itemtype='SI'");
while (DR.Read())
{
productlist.Add(DR[0] + ":" + DR[1]);
}
listBox1.DataSource = productlist;
}
else if (radioButton2.Checked == true)
{
List <String> productlist = new List <string>();
sqlconnectionclass selecttousers = new sqlconnectionclass();
DR = selecttousers.ReadDB("select * from Products where barcode like '%" + textBoxSearch.Text + "%' and itemtype='SI'");
while (DR.Read())
{
productlist.Add(DR[0] + ":" + DR[1]);
}
listBox1.DataSource = productlist;
}
}
private void button1_Click(object sender, EventArgs e)
{
float available = 0, added = 0, totalqty;
available = Convert.ToInt32(textBox8.Text);
added = Convert.ToInt32(textBox6.Text);
totalqty = available + added;
//textBoxBalance.Text = Balance.ToString();
int replenish = 0;
sqlconnectionclass getrepno = new sqlconnectionclass();
DR = getrepno.ReadDB("select top 1 greplenishno from stockin ORDER BY greplenishno DESC");
if (DR.HasRows)
{
DR.Read();
replenish = Convert.ToInt32(DR[0]) + 1;
}
else
{
replenish = 1;
}
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[0].Value != null)
{
string insertstock = "INSERT INTO [stockin]([ProductNo],[Suppliername],[deliverymode],[note_no],[qty],[VAT],[Totalcost],[unitprice],[greplenishno],[orderno],[vehicleno],[date])VALUES('" + row.Cells[0].Value + "','" + row.Cells[4].Value + "','" + row.Cells[2].Value + "','" + row.Cells[3].Value + "','" + row.Cells[7].Value + "','" + row.Cells[9].Value + "','" + row.Cells[10].Value + "','" + row.Cells[8].Value + "','" + replenish + "','" + row.Cells[5].Value + "','" + row.Cells[6].Value + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertstock);
string insertstockcard = "INSERT INTO [Stockcard]([ProductNo] ,[Transaction_Date],[Naration] ,[Qty_in] ,[Qty_out],[Lacation],[System_User],[New_Stock],[Available_Stock],[DateReport],[Timereport]) VALUES('" + row.Cells[0].Value + "','" + System.DateTime.Now + "','Replenishment of''" + row.Cells[1].Value + "','" + row.Cells[7].Value + "','0','Store','" + Form1.UserName + "','" + totalqty + "','" + textBox8.Text + "','" + System.DateTime.Now + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertstockcard);
}
}
MessageBox.Show("Replenishment done successfully!");
dataGridView1.Columns.RemoveAt(11);
dataGridView1.Columns.RemoveAt(0);
dataGridView1.Columns.RemoveAt(2);
dataGridView1.Columns.RemoveAt(3);
dataGridView1.Columns.RemoveAt(4);
dataGridView1.Columns.RemoveAt(5);
//dataGridView1.Columns.RemoveAt(6);
//dataGridView1.Columns.RemoveAt(8);
//dataGridView1.Columns.RemoveAt(9);
//Open the print dialog
PrintDialog printDialog = new PrintDialog();
printDialog.Document = printDocument1;
printDialog.UseEXDialog = true;
//Get the document
if (DialogResult.OK == printDialog.ShowDialog())
{
printDocument1.DocumentName = "EasyPOS Print";
printDocument1.Print();
}
//Open the print preview dialog
PrintPreviewDialog objPPdialog = new PrintPreviewDialog();
objPPdialog.Document = printDocument1;
objPPdialog.ShowDialog();
this.Close();
}
private void sell()
{
//throw new NotImplementedException();
try
{
if (textBoxTotalAmount.Text != "" && textBoxBalance.Text != "" && textBoxCash.Text != "" || textBox2.Text != "")
{
getreceiptno();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[0].Value != null)
{
//float available = 0,totalqty;
available = Convert.ToInt32(textBox1.Text);
//sold = Convert.ToInt32(textBoxQuantity.Text);
totalqty = available - qty;
getCreditno();
sqlconnectionclass getstatus = new sqlconnectionclass();
DR = getstatus.ReadDB("select count(*) from stockin where productno='" + row.Cells[0].Value + "'");
if (DR.HasRows)
{
DR.Read(); if (Convert.ToInt32(DR[0]) > 0)
{
status = 0;
}
else
{
status = 1;
}
}
string insertsale = "INSERT INTO [Sales]([ProductNo],[sqty],[sprice],[totalcost],[VAT],[credit_customerID],[credit_no],[unknown],[receiptno],[date])VALUES('" + row.Cells[0].Value + "','" + row.Cells[3].Value + "','" + row.Cells[2].Value + "','" + row.Cells[5].Value + "','" + row.Cells[4].Value + "','" + cust_id + "','" + nextcreditno + "','" + status + "','" + ReceiptNo + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertsale);
string insertstockcard = "INSERT INTO [Stockcard]([ProductNo] ,[Transaction_Date],[Naration] ,[Qty_in] ,[Qty_out],[Lacation],[System_User],[New_Stock],[Available_Stock],[DateReport],[Timereport]) VALUES('" + row.Cells[0].Value + "','" + System.DateTime.Now + "','Sale of ''" + productname + "','" + '0' + "','" + row.Cells[3].Value + "','Store','" + Form1.UserName + "','" + totalqty + "','" + textBox1.Text + "','" + System.DateTime.Now + "','" + System.DateTime.Now + "')";
new sqlconnectionclass().WriteDB(insertstockcard);
if (radioButton2.Checked == true)
{
string insertcredit = "INSERT INTO credit_sales([credit_no],[credit_customerID],[amount],[description],[user],[date])values('" + nextcreditno + "','" + cust_id + "','" + txtcreditsales.Text + "','being sales of''" + productname + "','" + Form1.UserName + "','" + System.DateTime.Now.ToShortDateString() + "')";
new sqlconnectionclass().WriteDB(insertcredit);
creditbalance();
totalbal = Convert.ToInt32(txtcreditsales.Text);
bal2 = bal + totalbal;
string insertgledger = "INSERT INTO [GLedger]([credit_customerID],[Debit],[Balance],[Date],[user],[Naration])values('" + cust_id + "','" + txtcreditsales.Text + "','" + bal2 + "','" + System.DateTime.Now + "','" + Form1.UserName + "','Being sales of''" + productname + "')";
new sqlconnectionclass().WriteDB(insertgledger);
}
}
}
//if(){}
/*****======================Begin Print=====================*****/
PrinterUtility.EscPosEpsonCommands.EscPosEpson obj = new PrinterUtility.EscPosEpsonCommands.EscPosEpson();
var BytesValue = Encoding.ASCII.GetBytes(string.Empty);
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.CharSize.DoubleWidth2());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.FontSelect.FontA());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Center());
//BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("JOMAT GENERAL HARDWARE\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.CharSize.Nomarl());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.FontSelect.FontA());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Dealers in: Hardware e.g Iron sheets, Cement,\n Plywood, Paints, Glassmart, Supply of general\nbuilding materials etc.\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("P.O. BOX 4, NGONG\nNGONG RD-KISERIAN, MATASIA SHOPPING CENTRE\nTEL: 0721-283-402/0724-477-792\nKRA PIN.:A001204489T \n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.CharSize.Nomarl());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("RECEIPT\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Left());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Receipt No : " + ReceiptNo + "\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Date : " + System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture) + "\n"));
//BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("PIN. :A001204489T \n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Right());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes(" Item Qty U. Price VAT Total\n"));
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Center());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes(" Name KES KES KES\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
foreach (DataGridViewRow dgv in dataGridView1.Rows)
{
BytesValue = PrintExtensions.AddBytes(BytesValue, string.Format("{0,-19}{1,9}{2,9}{3,10}{4,16:N2}\n", dgv.Cells[1].Value, dgv.Cells[3].Value, dgv.Cells[2].Value, dgv.Cells[4].Value, dgv.Cells[5].Value));
}
//BytesValue = PrintExtensions.AddBytes(BytesValue, string.Format("{0,-40}{1,6}{2,9}{3,9:N2}\n", "item 1", 12, 11, 144.00));
//BytesValue = PrintExtensions.AddBytes(BytesValue, string.Format("{0,-40}{1,6}{2,9}{3,9:N2}\n", "item 2", 12, 11, 144.00));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Right());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Total: "));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes(textBoxTotalAmount.Text + "\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Cash: "));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes(textBoxCash.Text + "\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Change: "));
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes(textBoxBalance.Text + "\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.CharSize.DoubleWidth2());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.FontSelect.FontA());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Center());
BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("Mpesa Till No 524049\n"));
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.CharSize.Nomarl());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.FontSelect.FontA());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Lf());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Center());
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.BarCode.Code128("12345"));
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.QrCode.Print("12345", PrinterUtility.Enums.QrCodeSize.Grande));
BytesValue = PrintExtensions.AddBytes(BytesValue, "Goods Once sold cannot be returned\n");
BytesValue = PrintExtensions.AddBytes(BytesValue, "You're served by " + Form1.UserName + "\n");
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, "Designed by Amtech Technologies\n");
BytesValue = PrintExtensions.AddBytes(BytesValue, "Website: www.amtechafrica.com Email: [email protected]\n");
BytesValue = PrintExtensions.AddBytes(BytesValue, "------Thank you for shopping with us-------\n");
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Left());
BytesValue = PrintExtensions.AddBytes(BytesValue, CutPage());
//PrinterUtility.PrintExtensions.Print(BytesValue, EasyPOS.Properties.Settings.Default.printerpath);
if (File.Exists(".\\tmpPrint.print"))
{
File.Delete(".\\tmpPrint.print");
}
File.WriteAllBytes(".\\tmpPrint.print", BytesValue);
RawPrinterHelper.SendFileToPrinter("Generic / Text Only", ".\\tmpPrint.print");
try
{
File.Delete(".\\tmpPrint.print");
}
catch
{
}
/******END PRINT & Begin collection**********/
PrinterUtility.EscPosEpsonCommands.EscPosEpson obj1 = new PrinterUtility.EscPosEpsonCommands.EscPosEpson();
var BytesValue1 = Encoding.ASCII.GetBytes(string.Empty);
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Separator());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.CharSize.DoubleWidth2());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.FontSelect.FontA());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Alignment.Center());
//BytesValue = PrintExtensions.AddBytes(BytesValue, Encoding.ASCII.GetBytes("\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes("JOMAT GENERAL HARDWARE\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.CharSize.Nomarl());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes("Collection RECEIPT\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.CharSize.Nomarl());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Alignment.Right());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes("Receipt No : " + ReceiptNo + "\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes("Date: " + System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture) + "\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Separator());
BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Left());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes(" Item Qty U. Price VAT Total\n"));
//BytesValue = PrintExtensions.AddBytes(BytesValue, obj.Alignment.Center());
BytesValue = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes(" Name KES KES KES\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Separator());
foreach (DataGridViewRow dgv in dataGridView1.Rows)
{
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, string.Format("{0,-19}{1,9}{2,9}{3,10}{4,16:N2}\n", dgv.Cells[1].Value, dgv.Cells[3].Value, dgv.Cells[2].Value, dgv.Cells[4].Value, dgv.Cells[5].Value));
}
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Alignment.Right());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Separator());
//BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes("Total: "));
//BytesValue1 = PrintExtensions.AddBytes(BytesValue1, Encoding.ASCII.GetBytes(textBoxTotalAmount.Text + "\n"));
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj.Lf());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, "Signed:.............................................\n");
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Alignment.Center());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, "You're served by " + Form1.UserName + "\n");
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj.Separator());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, "Designed by Amtech Technologies\n");
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, "Website: www.amtechafrica.com Email: [email protected]\n");
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, obj1.Alignment.Left());
BytesValue1 = PrintExtensions.AddBytes(BytesValue1, CutPage());
if (File.Exists(".\\tmpPrint1.print"))
{
File.Delete(".\\tmpPrint1.print");
}
File.WriteAllBytes(".\\tmpPrint1.print", BytesValue1);
RawPrinterHelper.SendFileToPrinter("Generic / Text Only", ".\\tmpPrint1.print");
try
{
File.Delete(".\\tmpPrint1.print");
}
catch
{
}
/*********END collector copy****************/
textBoxTotalAmount.Text = "0"; textBoxCash.Text = "0"; textBoxBalance.Text = "0";
}
else
{
MessageBox.Show("Enter Cash Amount");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
