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 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 LoadVendor()
{
cc.OpenConnection();
cc.DataGridDisplay("select distinct Vid,Vname from VendorDetails");
dataGridVendor.ItemsSource = cc.dt.AsDataView();
dataGridVendor.Visibility = System.Windows.Visibility.Visible;
cc.CloseConnection();
}
private void ShowGrid()
{
LoadExpense();
cc.OpenConnection();
cc.DataGridDisplay("select Epayment,EDate from ExpenseTransactionDetails where Eid=" + expenseID + "");
dataGridExpenseList.ItemsSource = cc.dt.AsDataView();
dataGridExpenseList.Visibility = System.Windows.Visibility.Visible;
cc.CloseConnection();
}
public ExpenseListPage()
{
InitializeComponent();
cc.OpenConnection();
cc.DataGridDisplay("select distinct Eid,Ename from ExpenseDetails");
dataGridVendor.ItemsSource = cc.dt.AsDataView();
dataGridVendor.Visibility = System.Windows.Visibility.Visible;
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 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();
}
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 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();
}
//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();
}
public PurchasedVendor(string selectedName)
{
InitializeComponent();
VnameCombo = selectedName;
LoadVendor();
cc.OpenConnection();
cc.CreateView("CREATE VIEW PurchasedVendor AS Select Stock.iname,Stock.rate,Stock.quantity,Stock.total,Stock.gtotal,TransactionDetails.Vname,TransactionDetails.PurchaseDate from Stock,TransactionDetails where Stock.Bno=TransactionDetails.Bno");
//cc.DataGridDisplay("select iname,rate,quantity,total,gtotal,PurchaseDate from Stock where Vname='"+VnameCombo+"'");
cc.DataGridDisplay("Select * from PurchasedVendor where Vname='" + VnameCombo + "'");
dataGridPurchasedVendor.ItemsSource = cc.dt.AsDataView();
dataGridPurchasedVendor.Visibility = System.Windows.Visibility.Visible;
cc.DropView("DROP VIEW PurchasedVendor");
cc.CloseConnection();
}
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 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 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")
{
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 buttonSave_Click(object sender, RoutedEventArgs e)
{
foreach (DataRow dg in dt.Rows)
{
string gridName = dg[0].ToString();
string gridQuantity = dg[1].ToString();
string gridCname = dg[2].ToString();
string gridCphone = dg[3].ToString();
string gridAdvance = dg[4].ToString();
string gridOdate = dg[5].ToString();
cc.OpenConnection();
cc.ExecuteQuery("insert into OrderedItems(OItemName,OQuantity,OCustomerName,OCustomerPhone,OAdvance,OrderDate) values ('" + gridName + "', " + Convert.ToInt32(gridQuantity) + ",'" + gridCname + "','" + gridCphone + "','" + gridAdvance + "','" + gridOdate + "')");
cc.CloseConnection();
}
MessageBox.Show("Order Saved..!");
ClearGrid();
textBoxItemName.Focus();
}
private void buttonSaveDamaged_Click(object sender, RoutedEventArgs e)
{
foreach (DataRow dg in dt.Rows)
{
string gridId = dg[0].ToString();
string gridName = dg[1].ToString();
string gridRate = dg[2].ToString();
string gridQuantity = dg[3].ToString();
string gridTotal = dg[4].ToString();
string gridVid = dg[5].ToString();
string gridVname = dg[6].ToString();
string gridVphone = dg[7].ToString();
cc.OpenConnection();
cc.ExecuteQuery("insert into DamagedStock(DItemNo,DName,DRate,DQuantity,DTotal,DVid,DVname,DVphone) values('" + gridId + "','" + gridName + "','" + gridRate + "','" + gridQuantity + "','" + gridTotal + "','" + gridVid + "','" + gridVname + "','" + gridVphone + "')");
cc.ExecuteQuery("update Stock set quantity = quantity - " + Convert.ToInt32(gridQuantity) + " where itemid = " + Convert.ToInt32(gridId) + "");
cc.CloseConnection();
}
MessageBox.Show("Item Inserted");
ClearDamageGrid();
DamageItemId.Focus();
}
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 billDisplay()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
if (MainWindow.isgst == 1)
{
cc.DataGridDisplay("select distinct ItemNo,Product,Price,ProductQuantity,TotalPrice,gstPercent,gstRate from BillStock where BillNo = " + bno + " and BillType='GST'");
}
else
{
cc.DataGridDisplay("select distinct ItemNo,Product,Price,ProductQuantity,TotalPrice,gstPercent,gstRate from BillStock where BillNo = " + bno + " and BillType='NON_GST'");
}
}
else
{
cc.DataGridDisplay("select distinct ItemNo,Product,Price,ProductQuantity,TotalPrice,gstPercent,gstRate from BillStock where BillNo = " + bno + " and BillType='GST'");
}
dataGridCustomer.ItemsSource = cc.dt.AsDataView();
dataGridCustomer.Visibility = System.Windows.Visibility.Visible;
cc.CloseConnection();
}
private void btn_Backup_Click(object sender, RoutedEventArgs e)
{
System.Windows.Forms.FolderBrowserDialog dlg = new System.Windows.Forms.FolderBrowserDialog();
dlg.Description = "Choose a directory to save the backup";
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
string baseDirectory = dlg.SelectedPath;
string saveFileName = "Billing";
cc.OpenConnection();
cc.ExecuteQuery("use master backup database Billing to disk = '" + baseDirectory + "\\" + saveFileName + ".bak'");
cc.CloseConnection();
MessageBox.Show("Backup Successful");
}
catch
{
MessageBox.Show("Can't Backuped to this Location...!");
}
}
}
private void updateBalance()
{
var date = DateTime.Now;
string today = date.ToString("dd-MMM-yy hh:mm:ss tt");
cc.OpenConnection();
cc.ExecuteQuery("insert into TransactionDetails(Vid,Vname,Bno,gtotal,Payment,PurchaseDate) values(" + vendorId + ",'" + comboBoxVendorName.SelectedItem + "',0,0," + textPayment + ",'" + today + "')");
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 LoadGrid()
{
cc.OpenConnection();
if (MainWindow.userName == "admin")
{
if (checkBoxGST.IsChecked == true)
{
cc.DataGridDisplay("select distinct CustomerName,CustomerPhone from BillStock where CustomerName not like 'Cust_%' and BillType='GST'");
}
else
{
cc.DataGridDisplay("select distinct CustomerName,CustomerPhone from BillStock where CustomerName not like 'Cust_%' and BillType='NON_GST'");
}
}
else
{
cc.DataGridDisplay("select distinct CustomerName,CustomerPhone from BillStock where CustomerName not like 'Cust_%' and BillType='GST'");
}
dataGridCustomer.ItemsSource = cc.dt.AsDataView();
dataGridCustomer.Visibility = System.Windows.Visibility.Visible;
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();
}
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 clearExpenseData()
{
cc.OpenConnection();
cc.ExecuteQuery("Delete from ExpenseData");
cc.CloseConnection();
}
private void clearTable()
{
dt.Clear();
txtItemName.Focus();
cc.CloseConnection();
txtVendorId.Text = "";
txtVendorPhone.Text = "";
txtVendorPlace.Text = "";
txtBox_CashPaid.Text = "";
Label_BalanceAmnt.Content = "";
txtBlkNetAmount.Text = "";
vendorNameComboBox.SelectedItem = null;
comboBoxPaymentVendor.SelectedItem = "By Cash";
}
private void viewBill()
{
if (txtBillNo.Text != null)
{
if (MainWindow.userName == "admin")
{
cc.OpenConnection();
string txtbn = Convert.ToString(txtBillNo.Text);
if (checkBoxGST.IsChecked == true)
{
if (cc.BillPreview("select COUNT(*) from BillStock where BillNo = @bno and BillType='GST'", "@bno", txtbn, billExistValue) > 0)
{
displayDetails();
int bn = Convert.ToInt32(txtBillNo.Text);
cc.DataGridDisplay("select ItemNo,Product,Price,ProductQuantity,gstPercent,gstRate,TotalPrice from BillStock where BillNo = " + bn + " and BillType='GST' ");
dataGrid1.ItemsSource = cc.dt.AsDataView();
}
else
{
MessageBox.Show("Bill does not exist");
txtBillNo.Text = currentBillNo;
txtBillNo.Focus();
}
}
else
{
if (cc.BillPreview("select COUNT(*) from BillStock where BillNo = @bno and BillType='NON_GST'", "@bno", txtbn, billExistValue) > 0)
{
displayDetails();
int bn = Convert.ToInt32(txtBillNo.Text);
cc.DataGridDisplay("select ItemNo,Product,Price,ProductQuantity,gstPercent,gstRate,TotalPrice from BillStock where BillNo = " + bn + " and BillType='NON_GST'");
dataGrid1.ItemsSource = cc.dt.AsDataView();
}
else
{
MessageBox.Show("Bill does not exist");
txtBillNo.Text = currentBillNo;
txtBillNo.Focus();
}
}
cc.CloseConnection();
}
else
{
cc.OpenConnection();
string txtbn = Convert.ToString(txtBillNo.Text);
if (cc.BillPreview("select COUNT(*) from BillStock where BillNo = @bno and BillType='GST'", "@bno", txtbn, billExistValue) > 0)
{
displayDetails();
int bn = Convert.ToInt32(txtBillNo.Text);
cc.DataGridDisplay("select ItemNo,Product,Price,ProductQuantity,gstPercent,gstRate,TotalPrice from BillStock where BillNo = " + bn + " and BillType='GST' ");
dataGrid1.ItemsSource = cc.dt.AsDataView();
}
else
{
MessageBox.Show("Bill does not exist");
txtBillNo.Text = currentBillNo;
txtBillNo.Focus();
}
cc.CloseConnection();
}
}
}
private void clearData()
{
cc.OpenConnection();
cc.ExecuteQuery("Delete from CustomerReport");
cc.CloseConnection();
}
