public IList <ProductStock> GetNoCountSummary(Location location)
{
//Stock = Counted, PackStock = Expected
string initQuery = "SELECT ProductID, UnitID, BinID, SUM(SingleQuantity + PackQuantity) AS Counted, 0 AS QtyExpected, MAX(MaxDate) AS MaxDate, MIN(MinDate) "
+ "AS MinDate FROM vwStockSummary WHERE (BinCode = :bin) AND (LocationID = :loc) "
+ "GROUP BY ProductID, UnitID, BinID HAVING SUM(SingleQuantity + PackQuantity) > 0 ";
StringBuilder sql = new StringBuilder(initQuery);
Parms = new List <Object[]>();
//Parms.Add(new Object[] { "nid", NodeType.Stored });
//Parms.Add(new Object[] { "six", EntityStatus.Active });
Parms.Add(new Object[] { "loc", location.LocationID });
Parms.Add(new Object[] { "bin", "NOCOUNT" });
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList <ProductStock> ret = DaoLabel.GetProductStockObject(query.List <Object[]>(), Factory);
if (!Factory.IsTransactional)
{
Factory.Commit();
}
return(ret);
}
public IList <ProductStock> GetProductInUseForMerged(List <int> list, Location location)
{
//Obtiene la lineas que tengan cantidad QtyAllocated - Shipped > 0
//Y que esten en status 101,102
//Sale solo product, y del nodo stored
string initQuery = " SELECT l.ProductID, p.BaseUnitID, null, SUM(ISNULL(l.QtyAllocated, 0) - ISNULL(trf.QtyShipped, 0)) AS Stock, 0 AS PackStock, NULL, NULL " +
" FROM Trace.DocumentLine AS l INNER JOIN Master.Product AS p ON l.ProductID = p.ProductID INNER JOIN " +
" Trace.[Document] AS d ON l.DocID = d.DocID AND d.LocationID = :loc1 LEFT OUTER JOIN " +
" (SELECT l.LineNumber, x.DocNumber, l.ProductID, SUM(ISNULL(l.QtyShipped, 0)) AS QtyShipped " +
" FROM Trace.DocumentLine AS l INNER JOIN " +
" Trace.[Document] AS x ON x.DocID = l.DocID " +
" WHERE x.DocTypeiD in (201) " +
" GROUP BY l.LineNumber, x.DocNumber, l.ProductID " +
" HAVING (SUM(l.QtyShipped) > 0)) AS trf ON l.LinkDocLineNumber = trf.LineNumber AND l.LinkDocNumber = trf.DocNumber AND l.ProductID = trf.ProductID " +
" WHERE (l.LineStatusID IN (101, 102)) AND (d.DocTypeID IN (206)) ";
StringBuilder sql = new StringBuilder(initQuery);
Parms = new List <Object[]>();
Parms.Add(new Object[] { "loc1", location.LocationID }); //Location
if (list != null && list.Count > 0)
{
sql.Append(" AND l.ProductID IN (0 ");
foreach (int pID in list)
{
sql.Append(", " + ":p" + pID.ToString());
Parms.Add(new Object[] { "p" + pID.ToString(), pID });
}
sql.Append(" )");
}
sql = new StringBuilder(sql.ToString());
sql.Append(" GROUP BY l.ProductID, p.BaseUnitID HAVING (SUM(ISNULL(l.QtyAllocated, 0) - ISNULL(trf.QtyShipped, 0)) > 0)");
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList <ProductStock> ret = DaoLabel.GetProductStockObject(query.List <Object[]>(), Factory);
if (!Factory.IsTransactional)
{
Factory.Commit();
}
return(ret);
}
public IList <ProductStock> GetCountInitialSummary(Document data)
{
StringBuilder sql = new StringBuilder("");
Parms = new List <Object[]>();
string initQuery = "";
if (data.Notes == "0") //Conteo por BIN
{
initQuery = "SELECT ProductID, NULL, Trace.BinByTask.BinID, 0, 0, NULL, NULL " +
"FROM Trace.BinByTask INNER JOIN Master.Bin b ON b.BinID = Trace.BinByTask.BinID " +
"WHERE Trace.BinByTask.TaskDocumentID = :doc ORDER BY b.Rank";
}
else if (data.Notes == "1") //Conteo por Producto
{
initQuery = "SELECT Trace.BinByTask.ProductID, NULL, stk.BinID, 0, 0, NULL, NULL " +
"FROM Trace.BinByTask INNER JOIN " +
"vwStockSummary AS stk ON Trace.BinByTask.ProductID = stk.ProductID " +
"WHERE Trace.BinByTask.TaskDocumentID = :doc AND (stk.LocationID = :loc) " +
"AND (stk.StatusID = :six) AND (stk.NodeID = :nid) AND (stk.SingleQuantity > 0 Or stk.PackQuantity > 0) ORDER BY stk.BinID ";
Parms.Add(new Object[] { "nid", NodeType.Stored });
Parms.Add(new Object[] { "six", EntityStatus.Active });
Parms.Add(new Object[] { "loc", data.Location.LocationID });
}
sql.Append(initQuery);
Parms.Add(new Object[] { "doc", data.DocID });
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList <ProductStock> ret = DaoLabel.GetProductStockObject(query.List <Object[]>(), Factory);
if (!Factory.IsTransactional)
{
Factory.Commit();
}
return(ret);
}
/// <summary>
/// Entrega lo que esta pendiente por recibir de un Documento contra un NodeTrace En unidad Basica
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public IList <ProductStock> GetDocumentProductStock(Document document, Product product)
{
string sQuery = "";
Parms = new List <Object[]>();
string strDoc = "";
if (document.DocID != 0)
{
strDoc = "AND d.DocID = :id0";
Parms.Add(new Object[] { "id0", document.DocID });
}
sQuery = " SELECT stk.ProductID, p.BaseUnitID, 0, SUM(ISNULL(stk.SingleQuantity,0) + ISNULL(stk.PackQuantity,0)) * u.BaseAmount AS Quantity, 0, null, null " +
" FROM vwStockSummary AS stk INNER JOIN Master.Product p " +
" ON ISNULL(stk.LevelCode,'') <> 'NOUSE' AND p.ProductID = stk.ProductID INNER JOIN Master.Unit u ON " +
" u.UnitID = stk.UnitID AND p.ProductID IN (select distinct ProductID From Trace.DocumentLine l " +
" INNER JOIN Trace.Document d ON d.DociD = l.DocID AND d.DocTypeID = :idt1 __STRCUST " +
" WHERE (d.DocStatusID = :ds1 OR d.DocStatusID = :dsp1) AND ( LineStatusID = :id3 OR LineStatusID = :is3 ) " + strDoc + " ) " +
" WHERE (stk.StatusID = :id1) AND (stk.LocationID = :id2) AND (stk.NodeID = :id4) ";
StringBuilder sql = new StringBuilder(sQuery);
Parms.Add(new Object[] { "id1", EntityStatus.Active });
Parms.Add(new Object[] { "id2", document.Location.LocationID });
Parms.Add(new Object[] { "id3", DocStatus.New });
Parms.Add(new Object[] { "is3", DocStatus.InProcess });
Parms.Add(new Object[] { "id4", NodeType.Stored });
if (document.Customer != null && document.Customer.AccountID != 0)
{
sql = sql.Replace("__STRCUST", " AND d.CustomerID = :idc0 ");
Parms.Add(new Object[] { "idc0", document.Customer.AccountID });
}
else
{
sql = sql.Replace("__STRCUST", "");
}
if (product != null && product.ProductID != 0)
{
sql.Append(" AND p.ProductID = :p0 ");
Parms.Add(new Object[] { "p0", product.ProductID });
}
Parms.Add(new Object[] { "idt1", document.DocType.DocTypeID });
Parms.Add(new Object[] { "ds1", DocStatus.New });
Parms.Add(new Object[] { "dsp1", DocStatus.InProcess });
sql.Append(" GROUP BY stk.LocationID, stk.ProductID, u.BaseAmount, p.BaseUnitID");
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList <ProductStock> ret = DaoLabel.GetProductStockObject(query.List <Object[]>(), Factory);
if (!Factory.IsTransactional)
{
Factory.Commit();
}
return(ret);
}