private void billNoTextBoxChange()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
if (checkBoxGST.IsChecked == true)
{
cc.DataReader("select distinct Bno from Stock where BillType='GST'");
}
else
{
cc.DataReader("select distinct Bno from Stock where BillType='NON_GST'");
}
}
else
{
cc.DataReader("select distinct Bno from Stock where BillType='GST'");
}
while (cc.reader.Read())
{
billNosList.Add(Convert.ToString(cc.reader["Bno"]));
}
cc.CloseReader();
cc.CloseConnection();
}
private void displayDetails()
{
cc.OpenConnection();
int tbn = Convert.ToInt32(txtBillNo.Text);
if (MainWindow.userName == "admin")
{
if (checkBoxGST.IsChecked == true)
{
cc.DataReader("select NetAmount,SaleDate,CustomerName,CustomerPhone,Credit from BillStock where BillNo = " + tbn + " and BillType='GST' ");
}
else
{
cc.DataReader("select NetAmount,SaleDate,CustomerName,CustomerPhone,Credit from BillStock where BillNo = " + tbn + " and BillType='NON_GST' ");
}
}
else
{
cc.DataReader("select NetAmount,SaleDate,CustomerName,CustomerPhone,Credit from BillStock where BillNo = " + tbn + " and BillType='GST' ");
}
while (cc.reader.Read())
{
txtBlkNetAmount.Text = cc.reader["NetAmount"].ToString();
txtSaleDate.Text = cc.reader["SaleDate"].ToString();
textBoxCustomer.Text = cc.reader["CustomerName"].ToString();
textBoxCustomerPh.Text = cc.reader["CustomerPhone"].ToString();
textBlock_Credit.Text = cc.reader["Credit"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void CurrentMonthData()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
cc.DataReader("select distinct BillNo,NetAmount,Credit from BillStock where SaleDate between '" + fromCurrent + "' and '" + toCurrent + "'");
}
else
{
cc.DataReader("select distinct BillNo,NetAmount,Credit from BillStock where SaleDate between '" + fromCurrent + "' and '" + toCurrent + "' and BillType='GST'");
}
while (cc.reader.Read())
{
cmNetAmountList.Add(Convert.ToDouble(cc.reader["NetAmount"]));
cmCreditList.Add(Convert.ToDouble(cc.reader["Credit"]));
}
cc.CloseReader();
currentMonthSales = cmNetAmountList.Sum().ToString();
currentMonthCredit = cmCreditList.Sum().ToString();
cc.DataReader("select month(Edate) as Mon,sum(Epayment) as Expense from ExpenseTransactionDetails where Edate between '" + fromCurrent + "' and '" + toCurrent + "' group by month(Edate)");
while (cc.reader.Read())
{
currentMonthExpense = cc.reader["Expense"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void ExpenseIdStore()
{
cc.OpenConnection();
cc.DataReader("select Eid from ExpenseDetails where Ename='" + comboBoxExpenseName.SelectedItem + "'");
while (cc.reader.Read())
{
labelExpenseId.Content = Convert.ToString(cc.reader["Eid"]);
}
cc.CloseReader();
cc.CloseConnection();
}
private void vendorIdStore()
{
cc.OpenConnection();
cc.DataReader("select Vid from TransactionDetails where Vname='" + comboBoxVendorName.SelectedItem + "'");
while (cc.reader.Read())
{
vendorId = Convert.ToInt32(cc.reader["Vid"]);
}
cc.CloseReader();
cc.CloseConnection();
}
private void storeLists()
{
storeData();
cc.OpenConnection();
cc.DataReader("select * from CustomerReport where Credit > 0");
while (cc.reader.Read())
{
customerNameList.Add(Convert.ToString(cc.reader["CustomerName"]));
customerPhoneList.Add(Convert.ToString(cc.reader["CustomerPhone"]));
creditList.Add(Convert.ToDouble(cc.reader["Credit"]));
}
}
private void storeLists()
{
cc.OpenConnection();
cc.DataReader("select * from ExpenseData");
while (cc.reader.Read())
{
expenseNameList.Add(Convert.ToString(cc.reader["ExpenseName"]));
expenseDateList.Add(Convert.ToString(cc.reader["ExpenseDate"]));
expenseAmountList.Add(Convert.ToDouble(cc.reader["ExpenseAmount"]));
}
cc.CloseReader();
cc.CloseConnection();
}
private void storeExpense()
{
cc.OpenConnection();
cc.DataReader("select Ename,Epayment,Edate from ExpenseTransactionDetails where Edate between '" + startDate + "' and '" + endDate + "'");
while (cc.reader.Read())
{
expenseAmountList.Add(Convert.ToDouble(cc.reader["Epayment"]));
expenseNameList.Add(Convert.ToString(cc.reader["Ename"]));
expenseDateList.Add(Convert.ToString(cc.reader["Edate"]));
}
cc.CloseReader();
cc.CloseConnection();
}
private void ShowDetails()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
if (MainWindow.isgst == 1)
{
cc.CreateView("CREATE VIEW PurchaseBill AS Select Distinct Stock.Bno,Stock.gtotal,FORMAT(TransactionDetails.PurchaseDate,'dd-MMM-yyyy') as PDate,TransactionDetails.CreditAmount,TransactionDetails.PaymentType,TransactionDetails.GstAmount from Stock,TransactionDetails where Stock.Bno=TransactionDetails.Bno and Stock.BillType='GST' and TransactionDetails.BillType='GST'");
}
else
{
cc.CreateView("CREATE VIEW PurchaseBill AS Select Distinct Stock.Bno,Stock.gtotal,FORMAT(TransactionDetails.PurchaseDate,'dd-MMM-yyyy') as PDate,TransactionDetails.CreditAmount,TransactionDetails.PaymentType,TransactionDetails.GstAmount from Stock,TransactionDetails where Stock.Bno=TransactionDetails.Bno and Stock.BillType='NON_GST' and TransactionDetails.BillType='NON_GST'");
}
}
else
{
cc.CreateView("CREATE VIEW PurchaseBill AS Select Distinct Stock.Bno,Stock.gtotal,FORMAT(TransactionDetails.PurchaseDate,'dd-MMM-yyyy') as PDate,TransactionDetails.CreditAmount,TransactionDetails.PaymentType,TransactionDetails.GstAmount from Stock,TransactionDetails where Stock.Bno=TransactionDetails.Bno and Stock.BillType='GST' and TransactionDetails.BillType='GST'");
}
cc.DataReader("select * from PurchaseBill where Bno = " + billNumber + "");
while (cc.reader.Read())
{
txt_Bill_Number.Text = cc.reader["Bno"].ToString();
txt_Sale_Date.Text = cc.reader["PDate"].ToString();
txt_Grant_Total.Text = "₹ " + cc.reader["gtotal"].ToString();
textBox_Credit.Text = cc.reader["CreditAmount"].ToString();
txt_Payment_Type.Text = cc.reader["PaymentType"].ToString();
}
cc.CloseReader();
cc.DropView("DROP VIEW PurchaseBill");
cc.CloseConnection();
}
private void buttonListOrder_Click(object sender, RoutedEventArgs e)
{
listdt.Clear();
cc.OpenConnection();
cc.DataReader("select OItemName,OQuantity,OCustomerName,OCustomerPhone,OAdvance,OrderDate from OrderedItems");
while (cc.reader.Read())
{
OName = cc.reader["OItemName"].ToString();
OQuantity = cc.reader["OQuantity"].ToString();
OCname = cc.reader["OCustomerName"].ToString();
OCphone = cc.reader["OCustomerPhone"].ToString();
OAdvanceAmnt = cc.reader["OAdvance"].ToString();
ODate = cc.reader["OrderDate"].ToString();
listdt.Rows.Add(new string[] { OName, OQuantity, OCname, OCphone, OAdvanceAmnt, ODate });
}
cc.CloseReader();
cc.CloseConnection();
if (listdt.Rows.Count <= 0)
{
MessageBox.Show("No Items to List");
}
else
{
dataCustomerOrder.ItemsSource = listdt.DefaultView;
}
}
private void readCompanyDetails()
{
cc.OpenConnection();
cc.DataReader("select ConfigValue from ConfigTable where ConfigId = 1");
while (cc.reader.Read())
{
companyName = cc.reader["ConfigValue"].ToString();
}
cc.CloseReader();
cc.DataReader("select ConfigValue from ConfigTable where ConfigId = 2");
while (cc.reader.Read())
{
companyDetails = cc.reader["ConfigValue"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void calculateCashPaid()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
if (MainWindow.isgst == 1)
{
cc.DataReader("select sum(Payment) as CashPaid from TransactionDetails where Vid=" + vendorID + " and BillType='GST'");
}
else
{
cc.DataReader("select sum(Payment) as CashPaid from TransactionDetails where Vid=" + vendorID + " and BillType='NON_GST'");
}
}
else
{
cc.DataReader("select sum(Payment) as CashPaid from TransactionDetails where Vid=" + vendorID + " and BillType='GST'");
}
while (cc.reader.Read())
{
cashPaid = Convert.ToInt32(cc.reader["CashPaid"]);
}
cc.CloseReader();
cc.CloseConnection();
}
private void calculateCredit()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
cc.DataReader("select sum(CreditAmount) as CreditSum from TransactionDetails");
}
else
{
cc.DataReader("select sum(CreditAmount) as CreditSum from TransactionDetails where BillType='GST'");
}
while (cc.reader.Read())
{
totalCredit = cc.reader["CreditSum"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
txt_Balance.Text = totalCredit;
}
private void GetFromDamageStock()
{
cc.OpenConnection();
cc.DataReader("select DItemNo,DName,DRate,DQuantity,DTotal,DVid,DVname,DVphone from DamagedStock");
while (cc.reader.Read())
{
DItemNo = cc.reader["DItemNo"].ToString();
DName = cc.reader["DName"].ToString();
DRate = cc.reader["DRate"].ToString();
DQuantity = cc.reader["DQuantity"].ToString();
DTotal = cc.reader["DTotal"].ToString();
DVid = cc.reader["DVid"].ToString();
DVname = cc.reader["DVname"].ToString();
DVphone = cc.reader["DVphone"].ToString();
dt.Rows.Add(new string[] { DItemNo, DName, DRate, DQuantity, DTotal, DVid, DVname, DVphone });
}
cc.CloseReader();
dataGridDamage.ItemsSource = dt.DefaultView;
cc.CloseConnection();
}
private void LoadExpense()
{
cc.OpenConnection();
cc.DataReader("select Eid,Ename from ExpenseTransactionDetails where Eid=" + expenseID + "");
while (cc.reader.Read())
{
textBlockEId.Text = cc.reader["Eid"].ToString();
textBlockEName.Text = cc.reader["Ename"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void readCustomerId()
{
cc.OpenConnection();
cc.DataReader("select ConfigValue from ConfigTable where ConfigId = 8 ");
while (cc.reader.Read())
{
genCustomerId = cc.reader["ConfigValue"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
txt_Customer_Id.Text = genCustomerId;
}
private void ExpenseName()
{
cc.OpenConnection();
cc.DataReader("select Ename from ExpenseDetails");
while (cc.reader.Read())
{
expenseList.Add(cc.reader["Ename"].ToString());
expenseList.Sort();
}
cc.CloseReader();
cc.CloseConnection();
}
private void vendorName()
{
cc.OpenConnection();
cc.DataReader("select Vname from VendorDetails");
while (cc.reader.Read())
{
vendorList.Add(cc.reader["Vname"].ToString());
vendorList.Sort();
}
cc.CloseReader();
cc.CloseConnection();
}
//public event EventHandler<DialogInputEventArgs> InputChanged = delegate { };
private void readExpenseId()
{
cc.OpenConnection();
cc.DataReader("select ConfigValue from ConfigTable where ConfigId = 7 ");
while (cc.reader.Read())
{
genExpenseId = cc.reader["ConfigValue"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
txt_Expense_Id.Text = genExpenseId;
txt_Expense_Name.Focus();
}
private void billNumberDisplay()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
cc.DataReader("select distinct BillNo,netGst,NetAmount,SaleDate,Credit,Particulars from BillStock where BillNo = " + bno + " ");
}
else
{
cc.DataReader("select distinct BillNo,netGst,NetAmount,SaleDate,Credit,Particulars from BillStock where BillNo = " + bno + " and BillType='GST'");
}
while (cc.reader.Read())
{
txt_Bill_Number.Text = cc.reader["BillNo"].ToString();
txt_Sale_Date.Text = cc.reader["SaleDate"].ToString();
txt_Total_Gst.Text = "₹ " + cc.reader["netGst"].ToString();
txt_Grant_Total.Text = "₹ " + cc.reader["NetAmount"].ToString();
textBox_Credit.Text = cc.reader["Credit"].ToString();
txt_Payment_Type.Text = cc.reader["Particulars"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void LoadVendor()
{
cc.OpenConnection();
cc.DataReader("select Vid,Vname,Vphone,Vplace from TransactionDetails where Vname='" + VnameCombo + "'");
while (cc.reader.Read())
{
textBlockVId.Text = cc.reader["Vid"].ToString();
textBlockVName.Text = cc.reader["Vname"].ToString();
textBlockVPhone.Text = cc.reader["Vphone"].ToString();
textBlockVPlace.Text = cc.reader["Vplace"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
public static void storeLists()
{
ConnectionClass cc = new ConnectionClass();
cc.OpenConnection();
cc.DataReader("select itemid,iname,squantity from Stock where Bno=" + billNumber + "");
while (cc.reader.Read())
{
idList.Add(Convert.ToInt32(cc.reader["itemid"]));
nameList.Add(Convert.ToString(cc.reader["iname"]));
sqtyList.Add(Convert.ToInt32(cc.reader["squantity"]));
}
cc.CloseReader();
cc.CloseConnection();
}
private void addData()
{
cc.OpenConnection();
cc.DataReader("select * from OrderedItems");
while (cc.reader.Read())
{
itemNameList.Add(Convert.ToString(cc.reader["OItemName"]));
quantityList.Add(Convert.ToInt32(cc.reader["OQuantity"]));
customerNameList.Add(Convert.ToString(cc.reader["OCustomerName"]));
customerPhoneList.Add(Convert.ToString(cc.reader["OCustomerPhone"]));
advanceAmountList.Add(Convert.ToDouble(cc.reader["OAdvance"]));
orderDateList.Add(Convert.ToString(cc.reader["OrderDate"]));
}
cc.CloseReader();
}
private void GetFromStock()
{
cc.OpenConnection();
cc.DataReader("select itemid,iname,quantity,sprice,gst_percent,gst_amount from Stock where itemid= " + Convert.ToInt32(txtItemId.Text) + "");
while (cc.reader.Read())
{
itemi = cc.reader["itemid"].ToString();
itname = cc.reader["iname"].ToString();
quantyty = cc.reader["quantity"].ToString();
spric = cc.reader["sprice"].ToString();
gstper = cc.reader["gst_percent"].ToString();
gstam = cc.reader["gst_amount"].ToString();
}
cc.CloseReader();
cc.CloseConnection();
}
private void addData()
{
cc.OpenConnection();
cc.DataReader("select * from Stock ORDER BY Sno ASC");
while (cc.reader.Read())
{
sList.Add(Convert.ToInt32(cc.reader["Sno"]));
iList.Add(Convert.ToInt32(cc.reader["itemid"]));
nList.Add(Convert.ToString(cc.reader["iname"]));
rList.Add(Convert.ToDouble(cc.reader["rate"]));
qList.Add(Convert.ToInt32(cc.reader["quantity"]));
gstList.Add(Convert.ToDouble(cc.reader["gst_amount"]));
spList.Add(Convert.ToDouble(cc.reader["sprice"]));
gstpList.Add(Convert.ToString(cc.reader["gst_percent"]));
}
cc.CloseReader();
}
private void customerName()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
cc.DataReader("select distinct CustomerName from CustomerTransactions where CustomerName not like 'Cust_%'");
}
else
{
cc.DataReader("select distinct CustomerName from CustomerTransactions where CustomerName not like 'Cust_%' and BillType='GST'");
}
while (cc.reader.Read())
{
customerNameList.Add(cc.reader["CustomerName"].ToString());
customerNameList.Sort();
}
cc.CloseReader();
cc.CloseConnection();
}
private void calculateCashPaid()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
cc.DataReader("select sum(AmountPaid) as CashPaid from CustomerTransactions where CustomerPhone='" + customerPhone + "'");
}
else
{
cc.DataReader("select sum(AmountPaid) as CashPaid from CustomerTransactions where CustomerPhone='" + customerPhone + "' and BillType='GST'");
}
while (cc.reader.Read())
{
cashPaid = Convert.ToDouble(cc.reader["CashPaid"]);
}
cc.CloseReader();
cc.CloseConnection();
}
private void btnSubmit_Click(object sender, RoutedEventArgs e)
{
cc.OpenConnection();
string userPassword = txtPassword.Password;
string exactPassword = userPassword.Substring(0, 4);
GetMacAddress();
if (cc.UserAuthentication("select Count(1) from LoginTable where Username=@username And Password=@password", "@username", txtUsername.Text, "@password", exactPassword, userExist) > 0)
{
cc.DataReader("select Password from LoginTable where Username='******'");
while (cc.reader.Read())
{
passwordCheck = cc.reader["Password"].ToString();
}
cc.CloseReader();
if (txtPassword.Password == passwordCheck + Mac)
{
username = txtUsername.Text;
password = txtPassword.Password.Substring(0, 4);
MainWindow mw = new MainWindow(username, password);
mw.Show();
this.Close();
}
else
{
MessageBox.Show("Incorrect Password");
txtPassword.Clear();
txtPassword.Focus();
}
}
else
{
MessageBox.Show("Incorrect Username");
txtUsername.Focus();
}
//MessageBox.Show("Something went wrong");
cc.CloseConnection();
}
