public DataSet GetProductsWithVendorsDataSet() { DataSet ds = new DataSet(); string sql = "select p.*,"; sql += "(select c.name from productcategory c"; sql += " join productsubcategory s on s.productcategoryid=c.productcategoryid"; sql += " where s.productsubcategoryid=p.productsubcategoryid) Category,"; sql += " (select s.name from productsubcategory s where s.productsubcategoryid=p.productsubcategoryid) SubCategory,"; sql += " (select v.name from vendor v where v.vendorid=p.primaryvendorid) PrimaryVendor,"; sql += " (select v.name from vendor v where v.vendorid=p.secondaryvendorid) SecondaryVendor"; sql += " from product p Order by p.name"; GenericQuery q = new GenericQuery(); try { ds = q.GetDataSet(false, sql); } catch (Exception ex) { log.Write(ex.Message, sql); throw (ex); } return(ds); }
public decimal GetTotalOrdersByCustomer(int CustomerID) { decimal tot = 0; GenericQuery gen = new GenericQuery(); try { string sql = "select sum(TotalDue) as tot from salesorderheader where status=6 and customerid=)" + CustomerID.ToString(); DataSet ds = new DataSet(); ds = gen.GetDataSet(false, sql); foreach (DataRow dr in ds.Tables[0].Rows) { if (dr["tot"] != DBNull.Value) { tot += decimal.Parse(dr["tot"].ToString()); } } } catch (Exception ex) { log.Write(ex.Message, "GetTotalOrdersByCustomer"); throw (ex); } finally { gen = null; } return(tot); }
public decimal GetTotalPaymentsByInvoice(int InvoiceID) { decimal tot = 0; GenericQuery gen = new GenericQuery(); try { string sql = "select sum(amount) as tot from payment where invoiceid=" + InvoiceID.ToString(); DataSet ds = new DataSet(); ds = gen.GetDataSet(false, sql); foreach (DataRow dr in ds.Tables[0].Rows) { if (dr["tot"] != DBNull.Value) { tot += decimal.Parse(dr["tot"].ToString()); } } } catch (Exception ex) { log.Write(ex.Message, "GetTotalPaymentsByInvoice"); throw (ex); } finally { gen = null; } return(tot); }
public void UpdatePaymentStatus(int invoiceid) { decimal payments = 0; SalesInvoiceHeader inv = new SalesInvoiceHeader(); GenericQuery q = new GenericQuery(); try { inv = inv.GetSalesInvoiceHeaders(invoiceid); string sql = "select sum(amount) as TotalPayments from payment where invoiceid=" + inv.InvoiceID; DataSet ds = new DataSet(); ds = q.GetDataSet(false, sql); if (ds.Tables[0].Rows.Count > 0) { payments = decimal.Parse(ds.Tables[0].Rows[0]["TotalPayments"].ToString()); } if (payments >= inv.TotalDue) { inv.Status = 5; inv.UpdateSalesInvoiceHeader(inv); } } catch (Exception ex) { throw (ex); } finally { inv = null; q = null; } }
private void btnHSearch_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; // int productid = Int32.Parse(cmbProducts.SelectedValue.ToString()); GenericQuery q = new GenericQuery(); DataSet ds = new DataSet(); string productDesc = txtHProduct.Text; string sql = "select p.productid,c.name as Category,s.name as Subcategory,p.description as Product,"; sql += " v.quantity as Quantity, h.adjustedquantity as AdjustedQuanity,"; sql += "h.reason as Reason, "; sql += " h.ModifiedDate as LastModified "; sql += " from "; sql += " product p ,productinventory v,productcategory c,productsubcategory s, productadjustmenthistory h "; sql += " where "; sql += " p.productid=v.productid and p.productid=h.productid and p.productsubcategoryid = s.productsubcategoryid and s.productcategoryid=c.productcategoryid"; string where = " and p.description like '" + productDesc + "%'"; if (cmbProductSubCategories.SelectedIndex > 0) { where += " and p.productsubcategoryid=" + cmbProductSubCategories.SelectedValue.ToString(); } else if (cmbProductCategories.SelectedIndex > 0) { where += " and s.productcategoryid=" + cmbProductCategories.SelectedValue.ToString(); } string OrderBy = " order by p.description,LastModified desc "; sql += where + OrderBy; try { ds = q.GetDataSet(false, sql); dgHisotry.DataSource = ds.Tables[0]; dgHisotry.Columns["Quantity"].Visible = false; dgHisotry.Columns["productid"].Visible = false; if (dgHisotry.Rows.Count > 0) { txtQuantity.Text = dgHisotry.Rows[0].Cells["Quantity"].Value.ToString(); } else { MessageBox.Show("Product doesn't have adjustment history", "Product Inventory", MessageBoxButtons.OK, MessageBoxIcon.Information); txtQuantity.Text = String.Empty; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Product Inventory", MessageBoxButtons.OK, MessageBoxIcon.Error); Cursor.Current = Cursors.Default; } finally { ds = null; q = null; Cursor.Current = Cursors.Default; } }
public DataSet GetInovoicesBalanceDataSet() { DataSet ds = new DataSet(); GenericQuery q = new GenericQuery(); string sql = "select * from InvoicesBalance_View where balance<>0"; try { ds = q.GetDataSet(false, sql); } catch (Exception ex) { log.Write(ex.Message, "GetInvoicesBalanceDataSet"); throw (ex); } finally { q = null; } return(ds); }
public DataSet GetDiscountByProduct(int productid, int quantity) { GenericQuery q = new GenericQuery(); DataSet ds = new DataSet(); string sql = "select s.description as SpecialOfferDesc,s.specialofferid,discountpct,type,minqty,maxqty"; sql += " from specialoffer s"; sql += " join specialofferproduct sp on s.specialofferid=sp.specialofferid"; sql += " where productid=" + productid.ToString(); sql += " and startDate <=getdate() and EndDate >= getdate()"; sql += " and (minqty=0 or minqty >= " + quantity.ToString() + ")"; try { ds = q.GetDataSet(false, sql); } catch (Exception ex) { log.Write(ex.Message, sql); throw (ex); } return(ds); }
public DataSet GetInvoiceDetailsGroupedByName(int invoiceid) { DataSet ds = new DataSet(); string sql = "select p.name, sum(o.quantity) as Quantity, o.unitprice as Price,"; sql += " sum(UnitPriceDiscount) as Discount,"; sql += " ( sum(quantity * unitprice) - sum(UnitPriceDiscount) ) as [Grand Total]"; sql += " from salesinvoicedetail o,product p"; sql += " where o.productid=p.productid and"; sql += " o.invoiceid=" + invoiceid.ToString(); sql += " group by p.name,o.unitprice order by p.name"; try { GenericQuery q = new GenericQuery(); ds = q.GetDataSet(false, sql); } catch (Exception ex) { log.Write(ex.Message, sql); throw (ex); } return(ds); }
private void Search(int selectedIndex) { InventoryAdjustmentCount = 0; Cursor.Current = Cursors.WaitCursor; GenericQuery gq = new GenericQuery(); int categoryid = Int32.Parse(cmbCategories.SelectedValue.ToString()); int subcategoryid = 0; if (cmbSubCategory.Items.Count > 0) { subcategoryid = Int32.Parse(cmbSubCategory.SelectedValue.ToString()); } string item = txtProduct.Text.Trim(); string sql = String.Empty; sql = "select p.productid,c.name as Category,s.name as Subcategory,rtrim(p.Description) as Product,"; sql += "(select sum(v.quantity) from productinventory v where v.productid=p.productid) as Quantity,"; sql += "(null) as AdjustedQuantity,"; sql += "('') as Reason"; sql += " from product p ,productcategory c,productsubcategory s"; string where = " where p.productsubcategoryid = s.productsubcategoryid and s.productcategoryid=c.productcategoryid"; where += " and p.description like '" + item + "%'"; if (subcategoryid > 0) { where += " and p.productsubcategoryid =" + subcategoryid; } else if (categoryid > 0) { where += " and c.productcategoryid =" + categoryid; } string groupby = " group by p.productid,p.Description ,c.name ,s.name"; string orderyby = " order by p.description"; sql += where + groupby + orderyby; // if (categoryid == 0) //{ // sql = "select p.productid,p.Description as Product,c.name as Category,s.name as Subcategory,"; // sql += " v.quantity as Quantity,(select adjustedquantity from productadjustmenthistory where productid = p.productid and id=(select max(id) from productadjustmenthistory where productid =p.productid )) as AdjustedQuanity,"; // sql += "(select reason from productadjustmenthistory where productid = p.productid and id=(select max(id) from productadjustmenthistory where productid =p.productid )) as Reason, "; // sql += " v.ModifiedDate as LastModified "; // sql += " from "; // sql += " product p ,productinventory v,productcategory c,productsubcategory s "; // sql += " where description like '" + item + "%' and" ; // sql += " p.productid=v.productid and p.productsubcategoryid = s.productsubcategoryid and s.productcategoryid=c.productcategoryid"; // sql += " order by p.Description"; //} //if(categoryid >=1) //{ // where += " and c.productcategoryid =" + categoryid; // if (subcategoryid > 0) // { // where += " and p.productsubcategoryid = " + subcategoryid; // } // sql = "select p.productid,p.Description as Product,c.name as Category,s.name as Subcategory,"; // sql += " v.quantity as Quantity,(select adjustedquantity from productadjustmenthistory where productid = p.productid and id=(select max(id) from productadjustmenthistory where productid =p.productid )) as AdjustedQuanity,"; // sql += "(select reason from productadjustmenthistory where productid = p.productid and id=(select max(id) from productadjustmenthistory where productid =p.productid )) as Reason ,"; // sql += " v.ModifiedDate as LastModified "; // sql += " from "; // sql += " product p ,productinventory v,productcategory c,productsubcategory s "; // sql += " where "; // sql += " p.productid=v.productid and p.productsubcategoryid = s.productsubcategoryid and s.productcategoryid=c.productcategoryid"; // sql += " and c.productcategoryid =" + categoryid; // sql += " order by p.description "; //} try { dsProductInventory = new DataSet(); dsProductInventory = gq.GetDataSet(false, sql); dgInventory.DataSource = dsProductInventory.Tables[0]; dgInventory.Columns["productid"].Visible = false; dgInventory.Columns["Category"].ReadOnly = true; dgInventory.Columns["Subcategory"].ReadOnly = true; dgInventory.Columns["Product"].ReadOnly = true; dgInventory.Columns["Quantity"].ReadOnly = true; if (dsProductInventory.Tables[0].Rows.Count > 0) { dgInventory.Rows[0].Selected = false; dgInventory.Rows[selectedIndex].Selected = true; dgInventory.FirstDisplayedScrollingRowIndex = selectedIndex; GridClick(selectedIndex); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Product Inventory", MessageBoxButtons.OK, MessageBoxIcon.Error); Cursor.Current = Cursors.Default; } finally { gq = null; Cursor.Current = Cursors.Default; } }
private void SearchProducts() { Cursor.Current = Cursors.WaitCursor; GenericQuery gq = new GenericQuery(); DataSet ds = new DataSet(); DataGridViewButtonColumn button = new DataGridViewButtonColumn(); DataGridViewImageColumn img = new DataGridViewImageColumn(); img.Image = Properties.Resources.user_go; //img.Name= button.HeaderText = "Last 6 Orders"; button.Text = "Orders"; button.UseColumnTextForButtonValue = true; button.DisplayIndex = 0; button.FlatStyle = FlatStyle.System; string searchedProduct = txtProductCode.Text.Trim(); int productSubcategoryID = 0; int productCategoryId = Int32.Parse(cmbCategory.SelectedValue.ToString()); if (productCategoryId > 0) { productSubcategoryID = Int32.Parse(cmbSubCategory.SelectedValue.ToString()); } string sql = "SELECT [Product].[ProductID], [Product].[Description] AS Name, "; sql += "[Product].[ProductNumber] PRODUCT_CODE, [Product].[ReorderPoint],"; sql += "[ProductInventory].[QUANTITY] as INSTOCK,"; //sql += "max([so].specialofferid)specialofferid,[so].description as SpecialOfferDesc,"; sql += "(select max([so].specialofferid) from specialoffer so join specialofferproduct sop on sop.SpecialOfferID=so.SpecialOfferID"; sql += " where sop.productid=product.productid and so.StartDate <= getdate() and so.EndDate > getdate())specialofferid,"; sql += " (select [so].description from specialoffer so join specialofferproduct sop on sop.SpecialOfferID=so.SpecialOfferID "; sql += " where sop.productid=product.productid and so.StartDate <= getdate() and so.EndDate > getdate()) SpecialOfferDesc,"; if (m_OrderType == (int)OrderType.SaleOrder) { sql += "max([Product].[ListPrice]) AS PRICE "; } else { sql += "max([Product].[StandardCost]) AS PRICE "; } sql += " FROM Product"; sql += " INNER JOIN ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID "; sql += " LEFT OUTER JOIN ProductInventory ON Product.ProductID = ProductInventory.ProductID "; // sql += " LEFT OUTER JOIN SpecialOfferProduct sop on Product.ProductID=sop.ProductID"; // sql += " LEFT OUTER JOIN SpecialOffer so on sop.SpecialOfferID=so.SpecialOfferID and so.StartDate <= getdate() and so.EndDate > getdate()"; sql += " WHERE "; if (!chkInactive.Checked) { sql += "activeflag=1 and "; } if (productSubcategoryID > 0) { sql += "(Product.ProductSubcategoryID =" + productSubcategoryID + ") AND "; } if (productCategoryId > 0) { sql += " (ProductSubcategory.ProductCategoryID = " + productCategoryId + ") AND "; } if (chkCriticalInvenotry.Checked) { sql += "(Product.ReorderPoint > ProductInventory.Quantity) AND "; } if (m_VendorID > 0) { sql += "(product.primaryvendorid=" + m_VendorID + " or product.secondaryvendorid=" + m_VendorID + ") AND "; } if (searchedProduct.Trim() == String.Empty) { sql += " (Product.Description LIKE '%')"; } else { sql += " (Product.Description LIKE '" + searchedProduct + "%')"; } sql += " group by [Product].[ProductID],[Product].[Description], [Product].[ProductNumber],[Product].[ReorderPoint],[ProductInventory].[QUANTITY]"; sql += " Order by product.Description"; try { ds = gq.GetDataSet(false, sql); dtProducts = ds.Tables[0]; /* if (SearchedOrdersGrid.Columns["Edit/View"].Index < 0) * { * SearchedOrdersGrid.Columns.Add(imgEdit); * SearchedOrdersGrid.Columns.Add(imgInvoice); * }*/ dtProducts.Columns.Add("No Cases"); dtProducts.Columns.Add("Unit/Case"); dtProducts.Columns.Add(new DataColumn("Quantity", typeof(int))); dtProducts.Columns.Add(new DataColumn("Total", typeof(decimal))); dtProducts.AcceptChanges(); productGrid.DataSource = dtProducts; // productGrid.Columns.Add(img); productGrid.Columns["ProductID"].Visible = false; //DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["PRODUCTID"].ReadOnly = true; if (m_OrderType == (int)OrderType.SaleOrder) { productGrid.Columns["REORDERPOINT"].Visible = false; } else { productGrid.Columns["SpecialOfferDesc"].Visible = false; } productGrid.Columns["REORDERPOINT"].ReadOnly = true; productGrid.Columns["REORDERPOINT"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["REORDERPOINT"].HeaderText = "Reorder Point"; productGrid.Columns["REORDERPOINT"].Width = 70; productGrid.Columns["INSTOCK"].ReadOnly = true; productGrid.Columns["INSTOCK"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["INSTOCK"].HeaderText = "In Stock"; productGrid.Columns["INSTOCK"].Width = 60; productGrid.Columns["specialofferid"].Visible = false; productGrid.Columns["SpecialOfferDesc"].HeaderText = "Special Offer"; productGrid.Columns["SpecialOfferDesc"].ReadOnly = true; productGrid.Columns["SpecialOfferDesc"].Width = 180; productGrid.Columns["NAME"].ReadOnly = true; productGrid.Columns["NAME"].Width = 280; productGrid.Columns["PRODUCT_CODE"].Visible = false; productGrid.Columns["PRICE"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["PRICE"].DefaultCellStyle.Format = "c"; productGrid.Columns["PRICE"].Width = 70; productGrid.Columns["No Cases"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["Unit/Case"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["Total"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["Total"].DefaultCellStyle.Format = "c"; productGrid.Columns["Quantity"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; productGrid.Columns["Quantity"].Width = 70; } catch (Exception ex) { MessageBox.Show(ex.Message, "Product Picker", MessageBoxButtons.OK, MessageBoxIcon.Error); Cursor.Current = Cursors.Default; } finally { gq = null; } Cursor.Current = Cursors.Default; }