private void btnDelete_Click(object sender, EventArgs e)
{
if (grv.RowCount > 0)
{
for (int i = 0; i < grv.SelectedRowsCount; i++)
{
if (grv.GetSelectedRows()[i] >= 0)
{
int x = Convert.ToInt32(grv.GetSelectedRows()[i]);
int id = Convert.ToInt32(grv.GetRowCellValue(x, colPID));
sc = new Server2Client();
prd = new Products();
sc = prd.deleteProduct(id);
if (sc.Message != null)
{
XtraMessageBox.Show(sc.Message);
}
}
}
lueCAT_EditValueChanged(null, null);
}
else
{
XtraMessageBox.Show("Nothing to delete");
}
}
private void bDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
CID = Convert.ToInt32(grv.GetFocusedRowCellValue(colCID));
Category c = new Category();
c.CategoryID = CID;
c.CategoryName = txtCNM.Text.ToUpper();
if (XtraMessageBox.Show("Are you sure you want to delete this Category? Deleting this category will also delete corresponding Products", "Confirm Delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Server2Client sc = new Server2Client();
Categories cat = new Categories();
sc = cat.DeleteCategory(c);
if (sc.Message == null)
{
XtraMessageBox.Show("Category deleted successfully!");
}
else
{
XtraMessageBox.Show(sc.Message);
}
LoadData();
}
}
public ucProducts()
{
InitializeComponent();
InitDataTable();
Server2Client sc = new Server2Client();
Products prd = new Products();
sc = prd.GetProductValues();
DataTable d = new DataTable();
d = sc.dataTable;
for (int i = 0; i <= d.Rows.Count - 1; i++)
{
//CategoryName, ProductName, BuyingValue, SellingValue, TotalQuantity
DataRow r = dt.NewRow();
r["CategoryName"] = d.Rows[i].ItemArray[0].ToString();
r["ProductName"] = d.Rows[i].ItemArray[1].ToString();
r["BuyingValue"] = Convert.ToDouble(d.Rows[i].ItemArray[2]);
r["SellingValue"] = Convert.ToDouble(d.Rows[i].ItemArray[3]);
r["TotalQuantity"] = Convert.ToInt32(d.Rows[i].ItemArray[4]);
r["TotalBuyingValue"] = Convert.ToDouble(d.Rows[i].ItemArray[2]) * Convert.ToInt32(d.Rows[i].ItemArray[4]);
r["TotalSellingValue"] = Convert.ToDouble(d.Rows[i].ItemArray[3]) * Convert.ToInt32(d.Rows[i].ItemArray[4]);
dt.Rows.Add(r);
}
grd.DataSource = dt;
grd.Refresh();
}
private void bSuppliersList_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
Server2Client sc = new Server2Client();
Suppliers sup = new Suppliers();
sc = sup.getSuppliersFull();
rptSuppliers rpt = new rptSuppliers()
{
DataSource = sc.dataTable
};
XRSummary tbal = new XRSummary();
rpt.lbCNM.DataBindings.Add("Text", null, "SupplierName");
rpt.lbADR.DataBindings.Add("Text", null, "Address");
rpt.lbPHN.DataBindings.Add("Text", null, "Phone");
rpt.lbEML.DataBindings.Add("Text", null, "Email");
rpt.lbBAL.DataBindings.Add("Text", null, "Balance", "{0:C2}");
rpt.lbTBAL.DataBindings.Add("Text", null, "Balance", "{0:C2}");
tbal.FormatString = "{0:C2}";
tbal.Running = SummaryRunning.Report;
rpt.lbTBAL.Summary = tbal;
dv.PrintingSystem = rpt.PrintingSystem;
rpt.CreateDocument(true);
}
private void btnOK_Click(object sender, EventArgs e)
{
if (!dxVP.Validate())
{
return;
}
Product p = new Product();
p.ProductID = Convert.ToInt32(luePRD.EditValue);
p.CategoryID = Convert.ToInt32(lueCAT2.EditValue);
p.ProductName = txtPNM.EditValue.ToString();
p.BuyingValue = Convert.ToDouble(txtBVL.EditValue);
p.SellingValue = Convert.ToDouble(txtSVL.EditValue);
p.Quantity = Convert.ToInt32(txtQTY.EditValue);
p.BarCode = txtBCD.Text;
Server2Client sc = new Server2Client();
Products prd = new Products();
sc = prd.updateProduct(p);
if (sc.Message != null)
{
XtraMessageBox.Show(sc.Message);
}
else
{
DialogResult = DialogResult.OK;
}
}
private void bPSUPX_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
Server2Client sc = new Server2Client();
ProductDetails pd = new ProductDetails();
sc = pd.ProductListBySupplierExtended();
rptProductBySup rpt = new rptProductBySup()
{
DataSource = sc.dataTable
};
GroupField grp = new GroupField("SupplierName");
rpt.groupHeader.GroupFields.Add(grp);
rpt.lbSUP.DataBindings.Add("Text", null, "SupplierName");
rpt.lbPNM.DataBindings.Add("Text", null, "ProductName");
rpt.lbBVL.DataBindings.Add("Text", null, "BuyingValue", "{0:C2}");
rpt.lbSVL.DataBindings.Add("Text", null, "SellingValue", "{0:C2}");
rpt.lbQTY.DataBindings.Add("Text", null, "Quantity");
rpt.lbBCD.DataBindings.Add("Text", null, "BarCode");
dv.PrintingSystem = rpt.PrintingSystem;
rpt.CreateDocument(true);
}
private XtraReport rpt()
{
Server2Client sc = new Server2Client();
Suppliers sup = new Suppliers();
sc = sup.getSuppliersFull();
Utils u = new Utils();
rptCustomers rpt = new rptCustomers()
{
DataSource = sc.dataTable
};
XRSummary tbal = new XRSummary();
rpt.lbCNM.DataBindings.Add("Text", null, "SupplierName");
rpt.lbADR.DataBindings.Add("Text", null, "Address");
rpt.lbPHN.DataBindings.Add("Text", null, "Phone");
rpt.lbEML.DataBindings.Add("Text", null, "Email");
rpt.lbBAL.DataBindings.Add("Text", null, "Balance", "{0:C2}");
rpt.lbTBAL.DataBindings.Add("Text", null, "Balance", "{0:C2}");
tbal.FormatString = "{0:C2}";
tbal.Running = SummaryRunning.Report;
rpt.lbTBAL.Summary = tbal;
return(rpt);
}
public ucReports()
{
InitializeComponent();
if (dv.Document == null)
{
rpPreview.Visible = false;
}
chkALL.Checked = true;
checkEdit1.Checked = true;
Server2Client sc;
sc = new Server2Client();
Customers cus = new Customers();
sc = cus.getCustomers();
lueCUS.Properties.DataSource = sc.dataTable;
lueCUS.Properties.DisplayMember = "CustomerName";
lueCUS.Properties.ValueMember = "ID";
sc = new Server2Client();
Suppliers sup = new Suppliers();
sc = sup.getSuppliers();
lueSUP.Properties.DataSource = sc.dataTable;
lueSUP.Properties.DisplayMember = "SupplierName";
lueSUP.Properties.ValueMember = "ID";
}
private void bNew_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
frmUsers frm = new frmUsers();
if (frm.ShowDialog() == DialogResult.OK)
{
sc = new Server2Client();
u = new Users();
User uu = new User();
uu.Username = frm.Username;
uu.Password = frm.Password;
uu.AccountType = frm.AccountType;
uu.Active = frm.Active;
sc = u.AddUser(uu);
if (sc.Message == null)
{
XtraMessageBox.Show("New user successfully created!");
InitUsers();
}
else
{
XtraMessageBox.Show(sc.Message);
}
}
}
void InitUsers()
{
sc = new Server2Client();
u = new Users();
sc = u.GetUsers();
grd.DataSource = sc.dataTable;
}
private void bInvoiceNo_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
frmSelect frm = new frmSelect(false);
if (frm.ShowDialog() == DialogResult.OK)
{
Server2Client sc = new Server2Client();
string dt = null;
if (frm.RetVal == 0)
{
Sales s = new Sales();
sc = s.getProfitLoss(frm.DateOn);
dt = "Date " + frm.DateOn.ToShortDateString();
}
else
{
Sales s = new Sales();
sc = s.getProfitLoss(frm.DateFrom, frm.DateTo);
dt = "Between " + frm.DateFrom.ToShortDateString() + " and " + frm.DateTo.ToShortDateString();
}
rptPL rpt = new rptPL()
{
DataSource = sc.dataTable
};
XRSummary gsv = new XRSummary();
XRSummary gbv = new XRSummary();
XRSummary gpf = new XRSummary();
rpt.lbDT.Text = dt;
rpt.lbSDT.DataBindings.Add("Text", null, "SaleDate", "{0:dd-MM-yyyy}");
rpt.lbSVL.DataBindings.Add("Text", null, "TotalSellingValue", "{0:C2}");
rpt.lbBVL.DataBindings.Add("Text", null, "TotalBuyingValue", "{0:C2}");
rpt.lbPFT.DataBindings.Add("Text", null, "Profit", "{0:C2}");
rpt.lbGSV.DataBindings.Add("Text", null, "TotalSellingValue", "{0:C2}");
rpt.lbGBV.DataBindings.Add("Text", null, "TotalBuyingValue", "{0:C2}");
rpt.lbGPF.DataBindings.Add("Text", null, "Profit", "{0:C2}");
gsv.FormatString = "{0:C2}";
gbv.FormatString = "{0:C2}";
gpf.FormatString = "{0:C2}";
gsv.Running = SummaryRunning.Report;
gbv.Running = SummaryRunning.Report;
gpf.Running = SummaryRunning.Report;
rpt.lbGSV.Summary = gsv;
rpt.lbGBV.Summary = gbv;
rpt.lbGPF.Summary = gpf;
dv.PrintingSystem = rpt.PrintingSystem;
rpt.CreateDocument(true);
}
}
void InitCategories()
{
Server2Client sc = new Server2Client();
Categories cat = new Categories();
sc = cat.GetCategories();
lueCAT.Properties.DataSource = sc.dataTable;
lueCAT.Properties.DisplayMember = "CategoryName";
lueCAT.Properties.ValueMember = "ID";
}
void InitCustomers()
{
Server2Client sc = new Server2Client();
Customers cus = new Customers();
sc = cus.getCustomers();
lueCNM.Properties.DataSource = sc.dataTable;
lueCNM.Properties.DisplayMember = "CustomerName";
lueCNM.Properties.ValueMember = "ID";
}
void fillGrid()
{
cust = new Customers();
sc = new Server2Client();
sc = cust.getCustomers();
grd.DataSource = sc.dataTable;
ButtonDisableEnable(sc.Count);
}
void InitSuppliers()
{
Server2Client sc = new Server2Client();
Suppliers sup = new Suppliers();
sc = sup.getSuppliers();
lueSUP.Properties.DataSource = sc.dataTable;
lueSUP.Properties.DisplayMember = "SupplierName";
lueSUP.Properties.ValueMember = "ID";
}
private void lueCAT_EditValueChanged(object sender, EventArgs e)
{
int CID = Convert.ToInt32(lueCAT.EditValue);
sc = new Server2Client();
prd = new Products();
sc = prd.GetProductByCategory(CID);
grd.DataSource = sc.dataTable;
}
void InitProducts()
{
Server2Client sc = new Server2Client();
Products prd = new Products();
sc = prd.GetProducts();
luePNM.Properties.DataSource = sc.dataTable;
luePNM.Properties.DisplayMember = "ProductName";
luePNM.Properties.ValueMember = "ID";
}
private void fillGrid()
{
sup = new Suppliers();
sc = new Server2Client();
sc = sup.getSuppliers();
grd.DataSource = sc.dataTable;
ButtonDisableEnable(sc.Count);
}
private void bCreditPayment_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
frmSelectCustomer frm = new frmSelectCustomer();
if (frm.ShowDialog() == DialogResult.OK)
{
Server2Client sc = new Server2Client();
CustomerAccounts ca = new CustomerAccounts();
Customers cc = new Customers();
Customer c = new Customer();
double bal = 0;
c = cc.getCustomer(frm.CustomerID);
rptCreditPayment rpt = new rptCreditPayment();
XRSummary tdr = new XRSummary();
XRSummary tcr = new XRSummary();
rpt.lbCNM.Text = c.CustomerName;
rpt.lbADR.Text = c.Address;
rpt.lbPHN.Text = c.Phone;
rpt.lbEML.Text = c.Email;
if (!frm.DateSelected)
{
sc = ca.getCustomerBalance(frm.CustomerID);
bal = sc.Value;
sc = ca.getTransactionDetails(frm.CustomerID);
rpt.DataSource = sc.dataTable;
}
else
{
sc = ca.getCustomerBalance(frm.CustomerID, frm.DateFrom, frm.DateTo);
bal = sc.Value;
sc = ca.AccountStatement(frm.CustomerID, frm.DateFrom, frm.DateTo);
rpt.DataSource = sc.dataTable;
}
rpt.lbTDT.DataBindings.Add("Text", null, "TransDate", "{0:dd-MM-yyyy}");
rpt.lbRMK.DataBindings.Add("Text", null, "Description");
rpt.lbTDR.DataBindings.Add("Text", null, "Debit", "{0:c}");
rpt.lbTCR.DataBindings.Add("Text", null, "Credit", "{0:c}");
rpt.lbBAL.DataBindings.Add("Text", null, "Balance", "{0:c}");
rpt.lblTDR.DataBindings.Add("Text", null, "Debit", "{0:C2}");
rpt.lblTCR.DataBindings.Add("Text", null, "Credit", "{0:C2}");
rpt.lblTBL.Text = bal.ToString("c2");
tdr.FormatString = "{0:C2}";
tcr.FormatString = "{0:C2}";
tdr.Running = SummaryRunning.Report;
tcr.Running = SummaryRunning.Report;
rpt.lblTCR.Summary = tdr;
rpt.lblTDR.Summary = tcr;
dv.PrintingSystem = rpt.PrintingSystem;
rpt.CreateDocument(true);
}
}
//public Server2Client SalesByCategory(int Month)
//{
// Server2Client sc = new Server2Client();
// OleDbCommand cmd = new OleDbCommand("SELECT Day([SaleDate]) AS SalesDate, Count(Category.CategoryName) AS CountOfCategoryName, Category.CategoryName FROM Sale INNER JOIN((Category INNER JOIN Product ON Category.ID = Product.CategoryID) INNER JOIN SaleDetail ON Product.ID = SaleDetail.ProductID) ON Sale.InvoiceNo = SaleDetail.InvoiceNo WHERE(((Month([SaleDate])) = 5)) GROUP BY Day([SaleDate]), Category.CategoryName", cm);
// OleDbDataAdapter da = new OleDbDataAdapter(cmd);
// DataSet ds = new DataSet();
// da.Fill(ds);
// sc.dataTable = ds.Tables[0];
// return sc;
//}
public Server2Client PaymentReceived(int month)
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Day([SaleDate]) AS SellDate, Sum(Sale.Amount) AS TotalAmount, Sum(Sale.Payment) AS TotalPayment, Sum(Sale.Balance) AS TotalCredit FROM Sale WHERE Month([SaleDate])=" + month + " GROUP BY Day([SaleDate])", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client CreditBalance()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Customer.CustomerName, SUM(CustomerAccount.Debit - CustomerAccount.Credit) AS Balance FROM Customer INNER JOIN CustomerAccount ON Customer.ID = CustomerAccount.CustomerID GROUP BY CustomerName", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.dataTable = ds.Tables[0];
return(sc);
}
private void lueSUP_EditValueChanged(object sender, EventArgs e)
{
int id = lueSUP.EditValue == null ? 0 : Convert.ToInt32(lueSUP.EditValue);
Server2Client sc = new Server2Client();
SupplierAccounts sa = new SupplierAccounts();
Suppliers sup = new Suppliers();
//c = cus.getCustomer(id);
sc = sa.getSupplierBalance(id);
SupplierBalance = sc.Value;
}
public Server2Client SoldProducts(int month)
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Category.CategoryName, Product.ProductName, Sum(SaleDetail.Quantity) AS TotalQuantity FROM Sale INNER JOIN((Category INNER JOIN Product ON Category.ID = Product.CategoryID) INNER JOIN SaleDetail ON Product.ID = SaleDetail.ProductID) ON Sale.InvoiceNo = SaleDetail.InvoiceNo WHERE Month([SaleDate])=" + month + " GROUP BY Category.CategoryName, Product.ProductName", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client ProductListBySupplierExtended()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Supplier.SupplierName, Product.ProductName, Product.BuyingValue, Product.SellingValue, Quantity, Product.BarCode FROM Supplier LEFT JOIN Product ON Supplier.ID = Product.SupplierID GROUP BY Supplier.SupplierName, Product.ProductName, Product.BuyingValue, Product.SellingValue, Product.BarCode, Quantity", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client ProductListByCategoryExtended()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Category.CategoryName, Product.ProductName, Product.BuyingValue, Product.SellingValue, Product.Quantity, Product.BarCode FROM Category INNER JOIN Product ON Category.ID = Product.CategoryID WHERE Product.Quantity > 0 GROUP BY Category.CategoryName, Product.ProductName, Product.BuyingValue, Product.SellingValue, Product.BarCode, Product.Quantity", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client GetProductByCategory(int CategoryID)
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT ID, ProductName, BuyingValue, SellingValue, SUM(Quantity) AS Quantity FROM Product WHERE CategoryID=" + CategoryID + " AND Quantity > 0 GROUP BY ID, ProductName, BuyingValue, SellingValue", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Count = ds.Tables[0].Rows.Count;
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client GetProductValues()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT CategoryName, ProductName, BuyingValue, SellingValue, SUM(Quantity) AS TotalQuantity FROM Category INNER JOIN Product ON Category.ID = Product.CategoryID GROUP BY CategoryName, ProductName, BuyingValue, SellingValue", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Count = ds.Tables[0].Rows.Count;
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client GetProductsTable()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT ID, CategoryID, ProductName, BarCode FROM Product", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Count = ds.Tables[0].Rows.Count;
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client GetProductFull()
{
Server2Client sc = new Server2Client();
OleDbCommand cmd = new OleDbCommand("SELECT Product.ID, Category.CategoryName, Product.ProductName, ProductDetail.BuyingValue, ProductDetail.SellingValue, ProductDetail.Quantity, Product.BarCode, Product.ReorderLevel FROM(Category INNER JOIN Product ON Category.ID = Product.CategoryID) INNER JOIN ProductDetail ON Product.ID = ProductDetail.ProductID", cm);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Count = ds.Tables[0].Rows.Count;
sc.dataTable = ds.Tables[0];
return(sc);
}
public Server2Client getCustomersFull()
{
sc = new Server2Client();
MySqlCommand cmd = new MySqlCommand("SELECT Customer.ID, Customer.CustomerName, Customer.Address, Customer.Phone, Customer.Email, SUM(CustomerAccount.Debit) - SUM(CustomerAccount.Credit) AS Balance FROM Customer LEFT OUTER JOIN CustomerAccount ON Customer.ID=CustomerAccount.CustomerID WHERE Customer.CustomerName NOT LIKE 'DefaultCustomer%' GROUP BY Customer.ID, Customer.CustomerName, Customer.Address, Customer.Phone, Customer.Email", cm);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
sc.Count = ds.Tables[0].Rows.Count;
sc.dataTable = ds.Tables[0];
return(sc);
}