public IList FindByMaxDate(ObjectCriteria criteria) { var sqlString = new StringBuilder("select stock, max(stock.CreateDate) FROM StockHistory stock, Product p WHERE stock.Product.ProductId = p.ProductId "); foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sqlString.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(" "); } sqlString.Append(" Group BY pm.ProductMasterId"); //criteria.AddQueryCriteria("productName", "Ao%"); //var sqlString = "select * FROM Stock stock, Product p WHERE stock.Product_Id = p.Product_Id"; return StockHistoryDAO.FindByMaxDate(sqlString.ToString(), criteria); }
public IList ListProductMasterStockQuery(string sqlString, ObjectCriteria criteria) { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); foreach (SQLQueryCriteria crit in query) { //sqlString += " AND " + crit.PropertyName + " " + crit.SQLString + " :" + crit.PropertyName + " "; paramNames.Add(crit.PropertyName); values.Add(crit.Value); } return HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray()); }
public IList FindByQuery(ObjectCriteria criteria) { var sql = new StringBuilder("SELECT s FROM DepartmentStockInDetail s, DepartmentStockIn deptStockIn, Product p, ProductMaster pm, DepartmentPrice price "); sql.Append("WHERE s.DepartmentStockInDetailPK.ProductId = p.ProductId AND p.ProductMaster.ProductMasterId = pm.ProductMasterId "); sql.Append(" AND pm.ProductMasterId = price.DepartmentPricePK.ProductMasterId "); sql.Append(" AND s.DepartmentStockInDetailPK.DepartmentId = price.DepartmentPricePK.DepartmentId "); sql.Append(" AND s.DepartmentStockInDetailPK.DepartmentId = deptStockIn.DepartmentStockInPK.DepartmentId "); sql.Append(" AND s.DepartmentStockInDetailPK.StockInId = deptStockIn.DepartmentStockInPK.StockInId"); int i = 0; foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sql.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(i++) .Append(" "); } return DepartmentStockInDetailDAO.FindByQuery(sql.ToString(), criteria); }
public IList FindByQueryForDeptStock(string sqlString, ObjectCriteria criteria) { ISession session = DbUtility.getSession(HibernateTemplate); try { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); foreach (SQLQueryCriteria crit in query) { //sqlString += " AND " + crit.PropertyName + " " + crit.SQLString + " :" + crit.PropertyName + " "; paramNames.Add(crit.PropertyName); values.Add(crit.Value); } HibernateTemplate.MaxResults = criteria.MaxResult; IList list = HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray()); IList returnList = new ArrayList(); for (int i = 0; i < list.Count; i++) { var stock = (DepartmentStock)((object[])list[i])[0]; stock.Quantity = Int64.Parse(((object[])list[i])[1].ToString()); returnList.Add(stock); } return returnList; } finally { session.Close(); } }
public IList FindStockQuantityForPurchaseOrder(string sqlString, ObjectCriteria criteria) { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); foreach (SQLQueryCriteria crit in query) { paramNames.Add(crit.PropertyName); values.Add(crit.Value); } IList list = HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray()); IList returnList = new ArrayList(); for (int i = 0; i < list.Count; i++) { var obj = (object[])list[i]; var result = new DepartmentStockSearchResult { DepartmentStock = ((DepartmentStock)obj[0]), DepartmentPrice = ((DepartmentPrice)obj[1]) }; returnList.Add(result); } return returnList; }
public IList FindByQueryForRemainStock(ObjectCriteria criteria, bool isMin) { var sqlString = new StringBuilder(); if (isMin) { sqlString.Append("select stockindetail, min(stockin.StockInDate) FROM StockIn stockin, StockInDetail stockindetail, ProductMaster pm WHERE stockindetail.ProductMaster.ProductMasterId = pm.ProductMasterId AND stockin.StockInId = stockindetail.StockInId "); } else { sqlString.Append("select stockindetail, max(stockin.StockInDate) FROM StockIn stockin, StockInDetail stockindetail, ProductMaster pm WHERE stockindetail.ProductMaster.ProductMasterId = pm.ProductMasterId AND stockin.StockInId = stockindetail.StockInId "); } foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sqlString.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(" "); } sqlString.Append(" Group BY pm.ProductMasterId"); return StockInDetailDAO.FindByQueryForRemainStockMin(sqlString.ToString(), criteria); }
public IList FindByQuery(string sqlString, ObjectCriteria criteria) { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); foreach (SQLQueryCriteria crit in query) { //sqlString += " AND " + crit.PropertyName + " " + crit.SQLString + " :" + crit.PropertyName + " "; paramNames.Add(crit.PropertyName); values.Add(crit.Value); } IList list = HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray()); IList returnList = new ArrayList(); for (int i = 0; i < list.Count; i++) { object[] obj = (object[]) list[i]; long qty = Int64.Parse(obj[1].ToString()); var result = new DepartmentStockSearchResult { DepartmentStock = ((DepartmentStock)obj[0]), SumQuantity = qty, SumInPrice = Int64.Parse(obj[2].ToString()), SumSellPrice = obj[3] != null ? ((DepartmentPrice)obj[3]).Price * qty : 0//Int64.Parse(obj[3].ToString()) }; returnList.Add(result); } return returnList; }
public IList FindByQueryForDeptStock(ObjectCriteria criteria) { var sqlString = new StringBuilder("select stock, sum(stock.Quantity) FROM DepartmentStock stock, Product p, ProductMaster pm WHERE stock.Product.ProductId = p.ProductId AND p.ProductMaster.ProductMasterId = pm.ProductMasterId "); foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sqlString.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(" "); } sqlString.Append(" Having sum(stock.Quantity) > 0 "); sqlString.Append(" Group BY pm.ProductMasterId"); //criteria.AddQueryCriteria("productName", "Ao%"); //var sqlString = "select * FROM Stock stock, Product p WHERE stock.Product_Id = p.Product_Id"; return DepartmentStockDAO.FindByQueryForDeptStock(sqlString.ToString(), criteria); }
public IList FindByQuery(ObjectCriteria criteria) { var sql = new StringBuilder("SELECT s, SUM(s.Quantity), SUM(s.Quantity * sdetail.Price), price FROM DepartmentStock s, Product p, ProductMaster pm, DepartmentStockInDetail sdetail, DepartmentPrice price WHERE s.DepartmentStockPK.ProductId = p.ProductId AND p.ProductMaster.ProductMasterId = pm.ProductMasterId AND s.Product.ProductId = sdetail.Product.ProductId AND pm.ProductMasterId = price.DepartmentPricePK.ProductMasterId "); foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sql.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(" "); } sql.Append(" GROUP BY pm.ProductMasterId"); //var sqltemp = new StringBuilder("SELECT s from DepartmentStockProductMasterView s"); //DepartmentStockDAO.FindByQuery(sqltemp.ToString(), new ObjectCriteria()); return DepartmentStockDAO.FindByQuery(sql.ToString(), criteria); }
public IList FindByQueryForStockIn(ObjectCriteria criteria) { var stockCount = StockDAO.SelectSpecificType(null, Projections.Count("StockId")); if(50 < (int)stockCount) { GlobalCache.Instance().WarningText = " Số lượng tìm thấy quá nhiều. Chỉ hiển thị 50 kết quả đầu."; criteria.MaxResult = 50; } var sqlString = new StringBuilder("select stock, sum(stock.Quantity) FROM Stock stock, Product p, ProductMaster pm WHERE stock.Product.ProductId = p.ProductId AND p.ProductMaster.ProductMasterId = pm.ProductMasterId "); foreach (SQLQueryCriteria crit in criteria.GetQueryCriteria()) { sqlString.Append(" AND ") .Append(crit.PropertyName) .Append(" ") .Append(crit.SQLString) .Append(" :") .Append(crit.PropertyName) .Append(" "); } sqlString.Append(" Having sum(stock.Quantity) > 0 "); sqlString.Append(" Group BY pm.ProductMasterId"); //criteria.AddQueryCriteria("productName", "Ao%"); //var sqlString = "select * FROM Stock stock, Product p WHERE stock.Product_Id = p.Product_Id"; return StockDAO.FindByQuery(sqlString.ToString(), criteria); }
public IList FindByQuery(string sqlString, ObjectCriteria criteria) { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); var types = new List<IType>(); int i = 0; foreach (SQLQueryCriteria crit in query) { paramNames.Add(crit.PropertyName + i++); values.Add(crit.Value); types.Add(crit.Type); } return HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray(), types.ToArray()); }
public IList FindByQueryForRemainStockMin(string sqlString, ObjectCriteria criteria) { ISession session = DbUtility.getSession(HibernateTemplate); try { List<SQLQueryCriteria> query = criteria.GetQueryCriteria(); var paramNames = new List<string>(); var values = new List<object>(); foreach (SQLQueryCriteria crit in query) { paramNames.Add(crit.PropertyName); values.Add(crit.Value); } IList list = HibernateTemplate.FindByNamedParam(sqlString, paramNames.ToArray(), values.ToArray()); IList returnList = new ArrayList(); for (int i = 0; i < list.Count; i++) { var stockIndetail = (StockInDetail)((object[])list[i])[0]; returnList.Add(stockIndetail); } return returnList; } finally { session.Close(); } }