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();
}
}
