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;
}
}
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 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 Task <IEnumerable <GenericModel> > Handle(
GenericQuery request,
CancellationToken cancellationToken
)
{
IEnumerable <GenericModel> result = new List <GenericModel>
{
new GenericModel
{
Value = "GenericModel.Value 1"
},
new GenericModel
{
Value = "GenericModel.Value 2"
},
new GenericModel
{
Value = "GenericModel.Value 3"
},
new GenericModel
{
Value = "GenericModel.Value 4"
},
};
return(Task.FromResult(
result
));
}
IGraphQLBuilder IGraphQLBuilder.Query <T>(GraphQLQuery query)
{
var method = query;
var graphQlQueryInstance = new GenericQuery <T>(method());
((IGraphQLBuilder)this).Queries.Add(graphQlQueryInstance);
return(this);
}
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 IEnumerable <TEntity> Handle(GenericQuery <TEntity> query)
{
using (var dbContextScope = _dbContextScopeFactory.CreateReadOnly())
{
var dbCtx = dbContextScope.DbContexts.GetByInterface <IDbContext>();
dbCtx.DbCtx.Configuration.ProxyCreationEnabled = false;
var testSet = dbCtx.DbCtx.Set <TEntity>().ToList();
return(testSet);
}
}
public LocateImagesResult LocateImages(LocateImagesRequest request)
{
QueryDelegate <ImageIdentifier> query = (criteria, studyRootQuery) => studyRootQuery.ImageQuery(criteria);
LocateFailureInfo[] failures;
var results = new GenericQuery <ImageIdentifier>(query, true).Query(request.Criteria, out failures);
return(new LocateImagesResult {
Images = results, Failures = failures
});
}
public async Task <IActionResult> GetAuthors(GenericQuery query)
{
try
{
var authors = await authorsRepository.GetAuthorsAsync(query);
return(Ok(mapper.Map <QueryResult <Author>, QueryResultResource <AuthorResource> >(authors)));
}
catch (System.Exception ex)
{
return(BadRequest(ex.Message));
}
}
public async Task <IActionResult> GetCategories(GenericQuery query)
{
try
{
var categories = await categoriesRepository.GetCategoriesAsync(query);
return(Ok(mapper.Map <QueryResult <Category>, QueryResultResource <CategoryResource> >(categories)));
}
catch (System.Exception ex)
{
return(BadRequest(ex.Message));
}
}
public async Task <QueryResult <Author> > GetAuthorsAsync(GenericQuery query)
{
var result = new QueryResult <Author>();
var dataQuery = context.Authors.OrderBy(A => A.Id).AsQueryable();
dataQuery = dataQuery.ApplyPaging(query);
result.TotalItems = await dataQuery.CountAsync();
result.Items = await dataQuery.ToListAsync();
return(result);
}
public async Task <QueryResult <Category> > GetCategoriesAsync(GenericQuery query)
{
var result = new QueryResult <Category>();
var dataQuery = context.Categories.OrderBy(C => C.Id).AsQueryable();
dataQuery = dataQuery.ApplyPaging(query);
result.TotalItems = await dataQuery.CountAsync();
result.Items = await dataQuery.ToListAsync();
return(result);
}
public IEnumerable <TEntity> Handle(GenericQuery <TEntity> args)
{
using (var dbContextScope = _dbContextScopeFactory.CreateReadOnly())
{
IDbContext dbCtx = dbContextScope.DbContexts.GetByInterface <TDbContext>();
((DbContext)dbCtx).Configuration.ProxyCreationEnabled = false;
IQueryable <TEntity> entities = dbCtx.Set <TEntity>();
entities = entities.Include(args);
entities = entities.Where(args);
entities = entities.OrderBy(args);
// Depending on your needs, you may not want to have .Take be mandatory
return(entities.Take(args.PageSize).ToList());
}
}
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 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;
}
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;
}
}
public IEnumerable <TSrcEntity> Retreive(GenericQuery <TSrcEntity> query)
{
return(GetDocuments());
}
public IListViewModel <T> GetList(IFilter <T> filters, Pagination pagination, string orderQuery)
{
IGenericQuery <T> genericQuery = new GenericQuery <T>(filters, orderQuery);
return(this.GetList(genericQuery, pagination));
}
public IListViewModel <T> GetList(IFilter <T> filters, Pagination pagination, SortItem <T> sortItem)
{
IGenericQuery <T> genericQuery = new GenericQuery <T>(filters, sortItem);
return(this.GetList(genericQuery, pagination));
}
public AddPlayers()
{
InitializeComponent();
GenericQuery = new GenericQuery <Player>(App.Database.GetSQLiteConnection());
}
