public Boolean ValidateBalanceQuantityInDocument(DocumentLine validationLine,
Node node, Boolean autoThrow, bool isCrossDock)
//bool isCrossDock, Adicionada en SEP/17/2009 para manejar el balance en cross dock sin tener en cuenta BO Quantities
{
DocumentBalance docBal = new DocumentBalance
{
Document = validationLine.Document,
Product = validationLine.Product,
Unit = validationLine.Unit,
Node = node
};
docBal = Factory.DaoDocumentBalance().GeneralBalance(docBal, isCrossDock).First();
//Multiplica la cantidad por la unidad base para hacer la resta
//La unidad del documento de Balance es la unidad Basica
if (docBal == null || docBal.BaseQtyPending < validationLine.Quantity * validationLine.Unit.BaseAmount)
{
if (autoThrow)
{
Factory.Rollback();
//throw new Exception("Current balance for document is " + (docBal.QtyPending / validationLine.Unit.BaseAmount).ToString() + " and you are trying to receive " + ((int)validationLine.Quantity).ToString() + ".");
throw new Exception("Current balance for product " + docBal.Product.ProductCode + " in document "
+ docBal.Document.DocNumber +" is " + docBal.QtyPending.ToString() + " (" + docBal.Unit.Name + ")"
+ " and you are trying to process " + ((int)validationLine.Quantity).ToString() + " (" + docBal.Unit.Name + ").");
}
else
return false;
}
return true;
}
public bool IsOrderBalanceCompleted(DocumentBalance docBal)
{
return Factory.DaoDocumentBalance().IsOrderBalanceCompleted(docBal);
}
public IList<DocumentBalance> GetDocumentBalance(DocumentBalance docBalance, bool isCrossDock)
{ return Factory.DaoDocumentBalance().GeneralBalance(docBalance, isCrossDock); }
public void ReverseReceiptNodeTraceByQty(DocumentBalance docBalance, int quantity, SysUser user)
{
TranMngr.ReverseReceiptNodeTraceByQty(docBalance, quantity, user);
}
public bool IsOrderBalanceCompleted(DocumentBalance docBalance)
{
string
sQuery = "select product.ProductID, Sum(l.Quantity - l.QtyCancel - l.QtyBackOrder)*unit.BaseAmount - Isnull(n.Quantity,0) as QtyPending "
+ " from Trace.DocumentLine as l "
+ " INNER JOIN Trace.Document doc ON l.DocID = doc.DocID "
+ " INNER JOIN Trace.Node node ON node.NodeID = :id2 "
+ " INNER JOIN Master.Product product ON l.ProductID = product.ProductID "
+ " INNER JOIN Master.Unit unit ON l.UnitID = unit.UnitID LEFT OUTER JOIN "
+ " ( select l.ProductID, Isnull(Sum(x.Quantity * ISNULL(u.BaseAmount,1)),0) as Quantity from Trace.NodeTrace x "
+ " INNER JOIN Trace.Label l ON x.LabelID = l.LabelID LEFT JOIN Master.Unit u ON u.UnitID = x.UnitID "
+ " Where x.NodeID = :id2 and x.DocID = :id1 AND x.Quantity > 0 AND x.IsDebit = 0 "
+ " Group BY l.ProductID ) as n On l.ProductID = n.ProductID Where l.DocID = :id1 AND l.LineNumber > 0 ";
StringBuilder sql = new StringBuilder(sQuery);
Parms = new List<Object[]>();
Parms.Add(new Object[] { "id1", docBalance.Document.DocID });
if (docBalance.Node != null && docBalance.Node.NodeID != 0)
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
if (docBalance != null)
{
if (docBalance.Product != null && docBalance.Product.ProductID != 0)
{
sql.Append(" and l.ProductID = :id3 ");
Parms.Add(new Object[] { "id3", docBalance.Product.ProductID });
}
sql.Append(" and ( l.LineStatusID = :id4 Or l.LineStatusID = :id5 ) ");
Parms.Add(new Object[] { "id4", DocStatus.New });
Parms.Add(new Object[] { "id5", DocStatus.InProcess });
sql.Append(" and product.StatusID = :id6 ");
Parms.Add(new Object[] { "id6", EntityStatus.Active });
sql.Append(" Group By product.ProductID,n.Quantity, unit.BaseAmount ");
sql.Append(" Having Sum(l.Quantity - l.QtyCancel - l.QtyBackOrder) - Isnull(n.Quantity,0) > 0");
}
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList<Object[]> retBalance = query.List<Object[]>();
if (!Factory.IsTransactional)
Factory.Commit();
return retBalance.Count > 0 ? false : true ;
}
public IList<DocumentBalance> DocumentBalanceForEmpty(DocumentBalance docBalance)
{
string sQuery = "select n.DocID, cast(0 as bigint) as Line, n.NodeID, l.ProductID, l.UnitID, "
+ " Isnull(Sum(n.Quantity),0) as Quantity, 0.0 as QtyPending, 0 as UnitPrice "
+ " from Trace.NodeTrace n INNER JOIN Trace.Label l ON n.LabelID = l.LabelID "
+ " Where n.NodeID = :id2 and n.DocID = :id1 And n.Quantity > 0 ";
StringBuilder sql = new StringBuilder(sQuery);
Parms = new List<Object[]>();
Parms.Add(new Object[] { "id1", docBalance.Document.DocID });
if (docBalance.Node != null && docBalance.Node.NodeID != 0)
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
else if (docBalance.Node != null && !string.IsNullOrEmpty(docBalance.Node.Name))
{
docBalance.Node = Factory.DaoNode().Select(docBalance.Node).First();
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
}
if (docBalance != null)
{
if (docBalance.Product != null && docBalance.Product.ProductID != 0)
{
sql.Append(" and n.ProductID = :id3 ");
Parms.Add(new Object[] { "id3", docBalance.Product.ProductID });
}
sql.Append(" Group By n.docID, n.NodeID, l.ProductID, l.UnitID");
}
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList<DocumentBalance> retBalance = GetBalanceObject(query.List<Object[]>(), docBalance.Location);
if (!Factory.IsTransactional)
Factory.Commit();
return retBalance;
}
//Cuando las lineas deben hacer link con un documento del ERP, solo se permite que el Uom Coincida
//Con el del documento
private Object[] ReceiptLinesForErpDocument(Document receivingTask, Document prDocument)
{
Object[] result = new Object[2];
try
{
Node recNode = new Node { NodeID = NodeType.Received };
Node storedNode = new Node { NodeID = NodeType.Stored };
DocumentBalance docBal = new DocumentBalance { Document = receivingTask, Node = storedNode };
//Obtiene el balance linea por linea, Es decir el cruce contra lo ya posteado, porque la linea se
//asigna una vez se postea, lo que no tiene linea no aparece en el balance
//es decir el saldo que queda por llenar de cada linea de documento
IList<DocumentBalance> balanceList = Factory.DaoDocumentBalance().DetailedBalance(docBal, false);
balanceList = balanceList.OrderBy(f => f.DocumentLine.LineNumber).ToList(); //Ordernada por LineNumber
//Armar las lineas del documento de Recibo
IList<DocumentLine> prDocLines = new List<DocumentLine>();
NodeTrace qNodeTrace;
DocumentLine rpLine;
int line = 1;
IEnumerable<NodeTrace> nodeTraceSameUnit;
IList<NodeTrace> allNodeTrace = new List<NodeTrace>();
Dictionary<long, double> acumLines = new Dictionary<long, double>();
Status lineStatus = WType.GetStatus(new Status { StatusID = DocStatus.New });
//Label curLabel;
//Obtienen los saldos pendientes de lo que se recibio para ese documento.
nodeTraceSameUnit = Factory.DaoNodeTrace().Select(
new NodeTrace
{
Document = receivingTask,
Node = recNode,
PostingDocument = new Document { DocID = 0 }, //0 mean Null
//Label = new Label { Product = balance.Product, Unit = balance.Unit }
}).Where(f => f.Quantity > 0);
//Recorre los NodeTrace en Receiving que tengan saldo y tengan balance.
double traceBalance;
double receivedLineQty;
int xLine = 0;
foreach (NodeTrace curTrace in nodeTraceSameUnit.Where(f => f.Label.Product != null).OrderBy(f => f.Label.Product.ProductID))
{
traceBalance = curTrace.Quantity * curTrace.Unit.BaseAmount;
//Saca todas las lineas del mismos producto que tenga la misma unidad y tengan saldo pendiente.
foreach (DocumentBalance curline in balanceList
.Where(f => f.Product.ProductID == curTrace.Label.Product.ProductID
//&& (f.Unit.UnitID == curTrace.Label.Unit.UnitID || f.Unit.BaseAmount == curTrace.Label.Unit.BaseAmount)
&& f.QtyPending > 0))
{
//Para el consecutivo de la linea
if (!acumLines.ContainsKey(curline.DocumentLine.LineID))
xLine++;
//Si el valor de la cantidad del label actual es mayor o igual a la pendiente.
if (traceBalance >= curline.BaseQtyPending) //(traceBalance >= curline.QtyPending)
{
traceBalance -= curline.BaseQtyPending;
receivedLineQty = curline.BaseQtyPending;
curline.QtyPending = 0;
}
else //Si es menor
{
curline.QtyPending -= traceBalance / curline.Unit.BaseAmount; //nodeTraceSameUnit.Sum(f => f.Quantity);
receivedLineQty = traceBalance; //nodeTraceSameUnit.Sum(f => f.Quantity);
traceBalance = 0;
}
//Creado los nuevos traces en el STORED.
curTrace.DocumentLine = curline.DocumentLine;
curTrace.PostingDate = DateTime.Now;
curTrace.PostingDocument = prDocument;
curTrace.PostingDocLineNumber = line;
curTrace.PostingUserName = prDocument.CreatedBy;
curTrace.ModifiedBy = prDocument.CreatedBy;
curTrace.ModDate = DateTime.Now;
//Nuevo node para el caso de Receiving.
Factory.DaoNodeTrace().Update(curTrace);
//allNodeTrace.Add(curTrace);
if (receivedLineQty > 0)
allNodeTrace.Add(new NodeTrace
{
Node = curTrace.Node,
Document = curTrace.Document,
Label = curTrace.Label,
Quantity = receivedLineQty,
IsDebit = curTrace.IsDebit,
CreatedBy = curTrace.CreatedBy,
PostingDocument = curTrace.PostingDocument,
PostingDocLineNumber = xLine,
PostingUserName = prDocument.CreatedBy,
Bin = curTrace.Bin,
CreationDate = DateTime.Now,
PostingDate = DateTime.Now,
Status = curTrace.Status,
Unit = curline.Product.BaseUnit, //curline.Unit,
FatherLabel = curTrace.Label.FatherLabel
});
//Crea el acumulador para cada linea.
if (acumLines.ContainsKey(curline.DocumentLine.LineID))
acumLines[curline.DocumentLine.LineID] += receivedLineQty;
else
acumLines.Add(curline.DocumentLine.LineID, receivedLineQty);
}
if (traceBalance <= 0)
continue;
else //Reportar que hubo Overreceived.
{
ExceptionMngr.WriteEvent("ReceiptLinesForErpDocument: Overreceived: " + curTrace.Document.DocNumber
+ ", Product: " + curTrace.Label.Product.Name,
ListValues.EventType.Warn, null, null,
ListValues.ErrorCategory.Business);
}
}
//Para todos los saldos de las lineas.
//Crea las lineas respectivas.
DocumentLine oriLine;
foreach (long lineID in acumLines.Keys)
{
oriLine = balanceList.Where(f => f.DocumentLine.LineID == lineID).Select(f => f.DocumentLine).First();
//Crea una linea para el documento de fullfilment
rpLine = new DocumentLine
{
Product = oriLine.Product,
AccountItem = oriLine.AccountItem,
Quantity = acumLines[lineID] / oriLine.Unit.BaseAmount, //nodeTraceSameUnit.Sum(f => f.Quantity),
Unit = oriLine.Unit,
Document = prDocument,
CreationDate = DateTime.Now,
IsDebit = false,
LineNumber = line,
LineStatus = lineStatus,
Location = prDocument.Location,
UnitBaseFactor = oriLine.Unit.BaseAmount,
LinkDocNumber = oriLine.Document.DocNumber,
LinkDocLineNumber = oriLine.LineNumber,
CreatedBy = prDocument.CreatedBy,
Note = oriLine.Note, //If Note=2 indica que es un componente
Sequence = oriLine.Sequence,
Date1 = DateTime.Now,
//UnitPrice = oriLine.UnitPrice,
//ExtendedPrice = oriLine.UnitPrice * acumLines[lineID] * oriLine.Unit.BaseAmount,
UnitCost = oriLine.UnitCost,
ExtendedCost = oriLine.UnitCost * acumLines[lineID] * oriLine.Unit.BaseAmount,
Date2 = oriLine.Date2, //Pone las fechas en el recibo, siver para las fechas de entrega Date2 en ENTERPRISE
QtyOnHand = oriLine.UnitBaseFactor,
BinAffected = oriLine.BinAffected,
PostingUserName = oriLine.PostingUserName
};
prDocLines.Add(rpLine);
line++;
}
result[0] = prDocLines;
result[1] = allNodeTrace;
return result;
}
catch (Exception ex)
{
//Factory.Rollback();
ExceptionMngr.WriteEvent("ReceiptLinesForErpDocument", ListValues.EventType.Fatal, ex, null, ListValues.ErrorCategory.Business);
//throw;
return null;
}
}
public IList<DocumentBalance> GetDocumentBalanceForEmpty(DocumentBalance docBalance)
{ return Factory.DaoDocumentBalance().DocumentBalanceForEmpty(docBalance); }
public IList<DocumentBalance> DetailedBalance(DocumentBalance docBalance, bool isCrossDock)
{
string sQuery = "";
if (isCrossDock) //No se debe tener en cuenta el back order
sQuery = "SELECT l.DocID, l.LineID, node.NodeID, l.ProductID, l.UnitID, SUM(l.Quantity - l.QtyCancel) AS Quantity, " +
"SUM(l.Quantity - l.QtyCancel) - ISNULL(n.Quantity/u.BaseAmount,0) AS QtyPending, Max(l.UnitPrice) as UnitPrice ";
else
sQuery = "SELECT l.DocID, l.LineID, node.NodeID, l.ProductID, l.UnitID, SUM(l.Quantity - l.QtyCancel - l.QtyBackOrder) AS Quantity, " +
"SUM(l.Quantity - l.QtyCancel - l.QtyBackOrder) - ISNULL(n.Quantity/u.BaseAmount,0) AS QtyPending, Max(l.UnitPrice) as UnitPrice ";
sQuery += "FROM Trace.DocumentLine AS l "+
"INNER JOIN Master.Product AS p ON l.ProductID = p.ProductID "+
"INNER JOIN Master.Unit AS u ON l.UnitID = u.UnitID "+
"INNER JOIN Trace.Node node ON node.NodeID = :id2 " +
"LEFT OUTER JOIN ( "+
" SELECT lbl.ProductID, ISNULL(SUM(x.Quantity * ISNULL(un.BaseAmount,1)) ,0) AS Quantity, x.DocLineID "+
" FROM Trace.NodeTrace AS x "+
" INNER JOIN Trace.Label AS lbl ON x.LabelID = lbl.LabelID "+
" LEFT OUTER JOIN Master.Unit AS un ON lbl.UnitID = un.UnitID "+
" WHERE x.NodeID = :id2 AND x.DocID = :id1 And x.Quantity > 0 " +
" GROUP BY lbl.ProductID, x.DocLineID " +
") AS n "+
"ON n.ProductID = l.ProductID AND l.LineID = n.DocLineID "+
"WHERE (l.DocID = :id1 ) ";
StringBuilder sql = new StringBuilder(sQuery);
Parms = new List<Object[]>();
Parms.Add(new Object[] { "id1", docBalance.Document.DocID });
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
if (docBalance != null)
{
if (docBalance.Product != null && docBalance.Product.ProductID != 0)
{
sql.Append(" and p.ProductID = :id3 ");
Parms.Add(new Object[] { "id3", docBalance.Product.ProductID });
}
sql.Append(" and ( l.LineStatusID = :id4 Or l.LineStatusID = :id5 ) ");
Parms.Add(new Object[] { "id4", DocStatus.New });
Parms.Add(new Object[] { "id5", DocStatus.InProcess });
sql.Append(" and p.StatusID = :id6 ");
Parms.Add(new Object[] { "id6", EntityStatus.Active });
sql.Append("GROUP BY l.DocID, l.LineID, node.NodeID, l.ProductID, l.UnitID, u.BaseAmount, n.Quantity ");
}
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList<DocumentBalance> retBalance = GetBalanceObject(query.List<Object[]>(), docBalance.Location);
if (!Factory.IsTransactional)
Factory.Commit();
return retBalance;
}
private IList<DocumentBalance> GetBalanceObject(IList<Object[]> retList, Location location)
{
if (retList == null || retList.Count == 0)
return new List<DocumentBalance>();
IList<DocumentBalance> retBalance = new List<DocumentBalance>();
DocumentBalance curBalance;
Document document = Factory.DaoDocument().SelectById(new Document { DocID = (int)retList[0][0] });
Node node = Factory.DaoNode().SelectById(new Node { NodeID = (int)(int)retList[0][2] });
foreach (Object[] obj in retList) {
curBalance = new DocumentBalance();
curBalance.Document = document;
curBalance.DocumentLine = ((Int64)obj[1] == 0) ? null : Factory.DaoDocumentLine().SelectById(new DocumentLine { LineID = (Int64)obj[1] });
curBalance.Node = node;
curBalance.Product = Factory.DaoProduct().SelectById(new Product { ProductID = (int)obj[3] });
curBalance.Unit = Factory.DaoUnit().SelectById(new Unit { UnitID = (int)obj[4] });
curBalance.Quantity = Double.Parse(obj[5].ToString());
curBalance.QtyPending = Double.Parse(obj[6].ToString());
try { curBalance.UnitPrice = Double.Parse(obj[7].ToString()); }
catch { }
curBalance.Location = location;
retBalance.Add(curBalance);
}
return retBalance;
}
private void ProcessPrintingLine(DocumentBalance printLine, String printLot, Node node, Bin bin, UserByRol userByRol)
{
//Tipo De impresion
//1. Normal Imprime standar, sin logistica
//2. Logistic (Notes tiene data) - imprime normal mas la Logistica
Unit logisticUnit = null;
//bool printOnlyLogistic = false;
if (printLine.Notes != null && printLine.Notes.Contains("Pack"))
{
string[] dataLogistic = printLine.Notes.Split(':');
//El elemento [1] contiene la unidad logistica, si es cero es custom.
if (dataLogistic[2].Equals(WmsSetupValues.CustomUnit))
{
try
{ //trata de encontrar una unidad con ese baseamount
logisticUnit = Factory.DaoUnit().Select(
new Unit
{
BaseAmount = printLine.QtyPending,
Company = userByRol.Location.Company,
ErpCodeGroup = printLine.Unit.ErpCodeGroup
}
).First();
}
catch
{
//Obtiene la custom
logisticUnit = Factory.DaoUnit().SelectById(new Unit { UnitID = int.Parse(dataLogistic[1]) });
}
}
else // Si no es Custom
{
logisticUnit = Factory.DaoUnit().SelectById(new Unit { UnitID = int.Parse(dataLogistic[1]) });
}
}
//Se ingresa para poder sacar el dato de la company
if (printLine.Document == null)
printLine.Document = new Document { Company = userByRol.Location.Company };
//CReating Document Line to Send
DocumentLine prnLine = new DocumentLine
{
Product = printLine.Product,
Document = printLine.Document,
Unit = printLine.Unit,
Quantity = printLine.Quantity,
CreatedBy = userByRol.User.UserName
};
if (logisticUnit != null)
logisticUnit.BaseAmount = printLine.QtyPending;
//Obteniendo el factor logistico antes de enviar a crear los labels
double logisticFactor = (logisticUnit != null) ? printLine.QtyPending : 1;
//Manda a Crear los Labels
IList<Label> resultList = CreateProductLabels(logisticUnit, prnLine, node, bin, logisticFactor, printLot,"", DateTime.Now)
.Where(f => f.FatherLabel == null).ToList();
foreach (Label lbl in resultList)
this.listOfLabels.Add(lbl);
}
/// <summary>
/// Get Print file string to print
/// </summary>
/// <param name="labels">List of labels to print</param>
/// <param name="template">Template to use for the printing</param>
/// <returns></returns>
public String ProcessPrintingLine(DocumentBalance printLine, LabelTemplate template,
String printLot, Node node, Bin bin, UserByRol userByRol)
{
string result = "";
Status status = WType.GetStatus(new Status { StatusID = EntityStatus.Active }); //Active
//Obteniendo el listado de TAGS que se deben reemplazar en el template
IList<LabelMapping> labelmappings = Factory.DaoLabelMapping().Select(
new LabelMapping { LabelType = template.LabelType });
//Template base
//int i;
IList<Label> labelList = new List<Label>();
//Tipo De impresion
//1. Normal Imprime standar, sin logistica
//2. Logistic (Notes tiene data) - imprime normal mas la Logistica
Unit logisticUnit = null;
if (printLine.Notes != null && printLine.Notes.Contains("Logistic"))
{
string[] dataLogistic = printLine.Notes.Split(':');
//El primer elemento contiene la unidad logistica.
logisticUnit = Factory.DaoUnit().SelectById(new Unit { UnitID = int.Parse(dataLogistic[1]) });
//3. Only print Logistic (notes tiene "ONLYPACK") - no imprime la normal (si las crea), solo imprime las logisticas
//if (printLine.Notes.Contains("ONLYPACK"))
//printOnlyLogistic = true;
}
//CReating Document Line to Send
DocumentLine prnLine = new DocumentLine
{
Product = printLine.Product,
Document = printLine.Document,
Unit = printLine.Unit,
Quantity = printLine.Quantity
};
//Crea las etiquetas de la cantidad de producto a recibir Logisticas y sus Hijas
double logisticFactor = (logisticUnit != null) ? (double)(logisticUnit.BaseAmount / printLine.Unit.BaseAmount) : 1;
labelList = CreateProductLabels(logisticUnit, prnLine, node, bin, logisticFactor, printLot,"", DateTime.Now)
.Where(f=>f.FatherLabel == null).ToList();
//Reemplazando el Header
if (template.Header != null)
result += ReplaceTemplate(labelmappings, template.Header, labelList[0]) + Environment.NewLine;
//Reemplazando el Body
if (template.Body != null)
{
foreach (Label label in labelList)
result += ReplaceTemplate(labelmappings, template.Body, label) + Environment.NewLine;
}
return result;
}
public void PickAtOnce(Document document, Label sourceLocation, Node node, SysUser picker)
{
Factory.IsTransactional = true;
//Node node = WType.GetNode(new Node { NodeID = NodeType.Picked });
DocumentBalance docBal = new DocumentBalance
{
Document = document,
Node = node
};
IList<DocumentBalance> balanceList = Factory.DaoDocumentBalance().BalanceByUnit(docBal);
//Recorre las lineas del documento y las pickea usando PickProduct, pero solo si el balance
//existe en la location indicada para todo lo pendiente.
if (balanceList == null || balanceList.Count == 0)
throw new Exception("Document " + document.DocNumber + " not contains product pending to pick.");
DocumentLine curLine;
string fullExistence = "";
foreach (DocumentBalance line in balanceList.Where(f=>f.QtyPending > 0))
{
//Define Document, Product, Unit and Qty to send to receiving transaction
curLine = new DocumentLine
{
Document = document,
Product = line.Product,
Unit = line.Unit,
Quantity = line.QtyPending,
CreatedBy = picker.UserName
};
fullExistence += CheckForStockInLocation(curLine, sourceLocation);
}
//Si alguno no tiene existencia no puede ejecutar el PickAtOnce
if (!string.IsNullOrEmpty(fullExistence))
{
ExceptionMngr.WriteEvent("PickAtOnce:", ListValues.EventType.Error, null, null, ListValues.ErrorCategory.Business);
throw new Exception(fullExistence);
}
//Ejecutando el Picking despues de que se confirma la existencia
foreach (DocumentBalance line in balanceList)
{
//Define Document, Product, Unit and Qty to send to receiving transaction
curLine = new DocumentLine
{
Document = document,
Product = line.Product,
Unit = line.Unit,
Quantity = line.QtyPending
};
Label packageLabel = new Label { LabelID = -1 };
PickProduct(curLine, sourceLocation, node, packageLabel, picker, null);
}
}
//Cuando las lineas deben hacer link con un documento del ERP, solo se permite que el Uom Coincida
//Con el del documento
private Object[] SalesLinesForErpDocument(Document shipTask, Document ssDocument)
{
Object[] result = new Object[2];
try
{
Node releaseNode = new Node { NodeID = NodeType.Released };
Node pickNode = new Node { NodeID = NodeType.Picked };
DocumentBalance docBal = new DocumentBalance { Document = shipTask, Node = releaseNode };
//Obtiene el balance linea por linea, Es decir el cruce contra lo ya Released, porque la linea se
//asigna una vez se postea, lo que no tiene linea no aparece en el balance
//es decir el saldo que queda por llenar de cada linea de documento
IList<DocumentBalance> balanceList = Factory.DaoDocumentBalance().DetailedBalance(docBal, shipTask.CrossDocking == true ? true : false);
balanceList = balanceList.OrderBy(f => f.DocumentLine.LineNumber).ToList(); //Ordernada por LineNumber
Console.WriteLine("Balance List");
//Armar las lineas del documento de Shipment
IList<DocumentLine> ssDocLines = new List<DocumentLine>();
//NodeTrace qNodeTrace;
DocumentLine ssLine;
int line = 1;
IEnumerable<NodeTrace> nodeTraceSameUnit;
IList<NodeTrace> allNodeTrace = new List<NodeTrace>();
Dictionary<long, double> acumLines = new Dictionary<long, double>();
Status lineStatus = WType.GetStatus(new Status { StatusID = DocStatus.New });
//Obtienen los saldos pendientes de lo que se piqueo para ese documento.
nodeTraceSameUnit = nodeTraceSameUnit = Factory.DaoNodeTrace().Select(
new NodeTrace
{
Document = shipTask,
Node = pickNode,
PostingDocument = new Document { DocID = 0 }, //0 mean Null
//Label = new Label { Product = balance.Product, Unit = balance.Unit }
}).Where(f => f.Quantity > 0);
//Recorre los NodeTrace en Picking que tengan saldo y tengan balance.
double traceBalance;
double shipLineQty;
int xLine = 0;
Console.WriteLine("Before Foreach");
foreach (NodeTrace curTrace in nodeTraceSameUnit.Where(f => f.Label.Product != null).OrderBy(f => f.Label.Product.ProductID))
{
traceBalance = curTrace.Quantity * curTrace.Unit.BaseAmount;
//Aqui en shipping el despacho es en EA asi que se deben procescar todas las lineas del nodetrace
//En EA
foreach (DocumentBalance curline in balanceList
.Where(f => f.Product.ProductID == curTrace.Label.Product.ProductID
//&& (f.Unit.UnitID == curTrace.Label.Unit.UnitID || f.Unit.BaseAmount == curTrace.Label.Unit.BaseAmount)
&& f.QtyPending > 0))
{
//Para el consecutivo de la linea
if (!acumLines.ContainsKey(curline.DocumentLine.LineID))
xLine++;
//Si el valor de la cantidad del label actual es mayor o igual a la pendiente.
if (traceBalance >= curline.BaseQtyPending)
{
traceBalance -= curline.BaseQtyPending;
shipLineQty = curline.BaseQtyPending;
curline.QtyPending = 0;
}
else //Si es menor
{
curline.QtyPending -= traceBalance/curline.Unit.BaseAmount; //nodeTraceSameUnit.Sum(f => f.Quantity);
shipLineQty = traceBalance; // nodeTraceSameUnit.Sum(f => f.Quantity);
traceBalance = 0;
}
//Creado los nuevos traces en el RELEASE.
curTrace.DocumentLine = curline.DocumentLine;
curTrace.PostingDate = DateTime.Now;
curTrace.PostingDocument = ssDocument;
curTrace.PostingDocLineNumber = xLine;
curTrace.PostingUserName = ssDocument.CreatedBy;
curTrace.ModifiedBy = ssDocument.CreatedBy;
curTrace.ModDate = DateTime.Now;
//Update Anterior.
Factory.DaoNodeTrace().Update(curTrace);
//Nuevo node para el caso de Shipping.
if (shipLineQty > 0)
allNodeTrace.Add( new NodeTrace
{
Node = curTrace.Node,
Document = curTrace.Document,
Label = curTrace.Label,
Quantity = shipLineQty,
IsDebit = curTrace.IsDebit,
CreatedBy = curTrace.CreatedBy,
PostingDocument = curTrace.PostingDocument,
PostingDocLineNumber = xLine,
PostingUserName = ssDocument.CreatedBy,
Bin = curTrace.Bin,
CreationDate = DateTime.Now,
PostingDate = DateTime.Now,
Status = curTrace.Status,
Unit = curline.Product.BaseUnit,
FatherLabel = curTrace.Label.FatherLabel
});
//Crea el acumulador para cada linea.
if (acumLines.ContainsKey(curline.DocumentLine.LineID))
acumLines[curline.DocumentLine.LineID] += shipLineQty;
else
acumLines.Add(curline.DocumentLine.LineID, shipLineQty);
}
if (traceBalance <= 0)
continue;
else //Reportar que hubo overshipment.
{
ExceptionMngr.WriteEvent("SalesLinesForErpDocument: Overshipment: " + curTrace.Document.DocNumber
+ ", Product: " + curTrace.Label.Product.Name,
ListValues.EventType.Warn, null, null,
ListValues.ErrorCategory.Business);
}
}
//Para todos los saldos de las lineas.
//Crea las lineas respectivas.
DocumentLine oriLine;
foreach (long lineID in acumLines.Keys)
{
oriLine = balanceList.Where(f => f.DocumentLine.LineID == lineID).Select(f => f.DocumentLine).First();
//Crea una linea para el documento de fullfilment
ssLine = new DocumentLine
{
Product = oriLine.Product,
Quantity = acumLines[lineID] / oriLine.Unit.BaseAmount, //acumLines[lineID], //nodeTraceSameUnit.Sum(f => f.Quantity),
Unit = oriLine.Unit,
Document = ssDocument,
CreationDate = DateTime.Now,
IsDebit = false,
LineNumber = line,
LineStatus = lineStatus,
Location = ssDocument.Location,
UnitBaseFactor = oriLine.Unit.BaseAmount,
LinkDocNumber = oriLine.Document.DocNumber,
LinkDocLineNumber = oriLine.LineNumber,
CreatedBy = ssDocument.CreatedBy,
Note = oriLine.Note, //If Note=2 indica que es un componente
Sequence = oriLine.Sequence,
Date1 = DateTime.Now,
UnitPrice = oriLine.UnitPrice,
ExtendedPrice = oriLine.UnitPrice * acumLines[lineID] * oriLine.Unit.BaseAmount,
UnitCost = oriLine.UnitCost,
ExtendedCost = oriLine.UnitCost * acumLines[lineID] * oriLine.Unit.BaseAmount
};
ssDocLines.Add(ssLine);
line++;
}
result[0] = ssDocLines;
result[1] = allNodeTrace;
return result;
}
catch (Exception ex)
{
//Factory.Rollback();
ExceptionMngr.WriteEvent("SalesLinesForErpDocument", ListValues.EventType.Fatal, ex, null, ListValues.ErrorCategory.Business);
//throw;
return null;
}
//Recorre los saldos pendientes para ese documento
/*
double curQty = 0;
foreach (DocumentBalance balance in balanceList.Where(b => b.QtyPending > 0))
{
qNodeTrace = new NodeTrace
{
Document = shipTask,
Node = pickNode,
PostingDocument = new Document { DocID = 0 }, //0 mean Null
Label = new Label { Product = balance.Product, Unit = balance.Unit }
};
//PASO 1 : Primero buscar los nodetrace que coincidan con la unidad necesitada y traer los pendientes
nodeTraceSameUnit = Factory.DaoNodeTrace().Select(qNodeTrace).ToList(); //.Take(int.Parse(balance.QtyPending.ToString()))
//Si se obtienen registros
//if (nodeTraceSameUnit.Count > 0)
if (nodeTraceSameUnit.Sum(f => f.Quantity) > 0)
{
//1.1 Actualiza los que encontro
foreach (NodeTrace traceSame in nodeTraceSameUnit)
{
traceSame.DocumentLine = balance.DocumentLine;
traceSame.PostingDate = DateTime.Now;
traceSame.PostingDocument = ssDocument;
traceSame.PostingDocLineNumber = line;
traceSame.PostingUserName = ssDocument.CreatedBy;
traceSame.ModifiedBy = ssDocument.CreatedBy;
traceSame.ModDate = DateTime.Now;
allNodeTrace.Add(traceSame);
}
//1.2 Disminuye el balance
//Ajuste para que trabaje bien cuando hay items repetidos en una misma orden
if (nodeTraceSameUnit.Sum(f => f.Quantity) <= balance.QtyPending)
{
balance.QtyPending -= nodeTraceSameUnit.Sum(f => f.Quantity);
curQty = nodeTraceSameUnit.Sum(f => f.Quantity);
}
else
{
curQty = balance.QtyPending;
balance.QtyPending = 0;
}
//Crea una linea para el documento de fullfilment
ssLine = new DocumentLine
{
Product = balance.Product,
Quantity = curQty, //nodeTraceSameUnit.Sum(f => f.Quantity),
Unit = balance.Unit,
Document = ssDocument,
CreationDate = DateTime.Now,
IsDebit = false,
LineNumber = line,
LineStatus = lineStatus,
Location = ssDocument.Location,
UnitBaseFactor = balance.Unit.BaseAmount,
LinkDocNumber = balance.Document.DocNumber,
LinkDocLineNumber = balance.DocumentLine.LineNumber,
CreatedBy = ssDocument.CreatedBy,
Note = balance.DocumentLine.Note, //If Note=2 indica que es un componente
Sequence = balance.DocumentLine.Sequence,
Date1 = DateTime.Now,
UnitPrice = balance.UnitPrice,
ExtendedPrice = balance.UnitPrice * curQty * balance.Unit.BaseAmount
};
ssDocLines.Add(ssLine);
line++;
}
//1.4 Si encontro todos los que necesitaba se sale a la siguiente linea
if (balance.QtyPending <= 0)
continue;
}
result[0] = ssDocLines;
result[1] = allNodeTrace;
return result;
}
catch (Exception ex)
{
//Factory.Rollback();
ExceptionMngr.WriteEvent("SalesLinesForErpDocument", ListValues.EventType.Fatal, ex, null, ListValues.ErrorCategory.Business);
//throw;
return null;
}
* */
}
//Obtiene las diferencias entre el docuento de recibo y los documentos de despacho asociados al crossdock
public IList<DocumentBalance> GetCrossDockBalance(DocumentBalance purchaseBalance, IList<Document> salesDocs)
{ return DocMngr.GetCrossDockBalance(purchaseBalance, salesDocs); }
public IList<DocumentBalance> GetDocumentPostingBalance(DocumentBalance docBalance)
{ return Factory.DaoDocumentBalance().PostingBalance(docBalance); }
/// <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<DocumentBalance> GeneralBalance(DocumentBalance docBalance, bool isCrossDock)
{
string sQuery = "";
if (isCrossDock) //No se debe tener en cuenta el back order
sQuery = "select doc.docID, cast(0 as bigint) as Line, node.NodeID, product.ProductID, unit.UnitID, "
+ " Sum(l.Quantity - l.QtyCancel) as Quantity, "
+ " Sum(l.Quantity - l.QtyCancel) - Isnull(n.Quantity/unit.BaseAmount,0) as QtyPending, Max(l.UnitPrice) as UnitPrice, Min(l.Sequence) as Seq ";
else
sQuery = "select doc.docID, cast(0 as bigint) as Line, node.NodeID, product.ProductID, unit.UnitID, "
+ " Sum(l.Quantity - l.QtyCancel - l.QtyBackOrder) as Quantity, "
+ " Sum(l.Quantity - l.QtyCancel - l.QtyBackOrder) - Isnull(n.Quantity/unit.BaseAmount,0) as QtyPending, Max(l.UnitPrice) as UnitPrice, Min(l.Sequence) as Seq ";
sQuery += " from Trace.DocumentLine as l "
+ " INNER JOIN Trace.Document doc ON l.DocID = doc.DocID "
+ " INNER JOIN Trace.Node node ON node.NodeID = :id2 "
+ " INNER JOIN Master.Product product ON l.ProductID = product.ProductID "
+ " INNER JOIN Master.Unit unit ON l.UnitID = unit.UnitID LEFT OUTER JOIN "
+ " ( select lb.ProductID, Isnull(Sum(x.Quantity * ISNULL(u.BaseAmount,1)),0) as Quantity from Trace.NodeTrace x "
+ " INNER JOIN Trace.Label lb ON x.LabelID = lb.LabelID LEFT JOIN Master.Unit u ON u.UnitID = x.UnitID " //lb.UnitID
+ " Where x.NodeID = :id2 and x.DocID = :id1 AND x.Quantity > 0 AND x.IsDebit = 0 "
+ " Group BY lb.ProductID ) as n On l.ProductID = n.ProductID Where l.DocID = :id1 ";
StringBuilder sql = new StringBuilder(sQuery);
Parms = new List<Object[]>();
Parms.Add(new Object[] { "id1", docBalance.Document.DocID });
if (docBalance.Node != null && docBalance.Node.NodeID != 0)
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
else if (docBalance.Node != null && !string.IsNullOrEmpty(docBalance.Node.Name))
{
docBalance.Node = Factory.DaoNode().Select(docBalance.Node).First();
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
}
if (docBalance != null)
{
if (docBalance.Product != null && docBalance.Product.ProductID != 0)
{
sql.Append(" and l.ProductID = :id3 ");
Parms.Add(new Object[] { "id3", docBalance.Product.ProductID });
}
sql.Append(" and ( l.LineStatusID = :id4 Or l.LineStatusID = :id5 ) ");
Parms.Add(new Object[] { "id4", DocStatus.New });
Parms.Add(new Object[] { "id5", DocStatus.InProcess });
sql.Append(" and product.StatusID = :id6 ");
Parms.Add(new Object[] { "id6", EntityStatus.Active });
sql.Append(" Group By doc.docID, node.NodeID, product.ProductID, unit.UnitID, unit.BaseAmount, n.Quantity"); //unit.BaseAmount,
sql.Append(" Order By Seq");
}
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList<DocumentBalance> retBalance = GetBalanceObject(query.List<Object[]>(), docBalance.Location);
if (!Factory.IsTransactional)
Factory.Commit();
return retBalance;
}
/// <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<DocumentBalance> PostingBalance(DocumentBalance docBalance)
{
string sQuery = "select n.docID, cast(0 as bigint) as Line, n.NodeID, n.ProductID, n.UnitID, "
+" Isnull(n.Quantity,0) as Quantity, Isnull(unpost.Quantity,0) as QtyPending, 0 as UnitPrice "
+" From ( select x.DocID, l.ProductID, l.UnitID, x.NodeID, Isnull(Sum(x.Quantity),0) as Quantity from Trace.NodeTrace x "
+" INNER JOIN Trace.Label l ON l.LabelID = x.LabelID "
+" Where x.NodeID = :id2 and x.DocID = :id1 and x.Quantity > 0 "
+" Group BY l.ProductID, l.UnitID, x.DocID, x.NodeID ) as n INNER JOIN "
+" ( select l.ProductID, l.UnitID, Isnull(Sum(x.Quantity),0) as Quantity from Trace.NodeTrace x "
+" INNER JOIN Trace.Label l ON l.LabelID = x.LabelID "
+" Where x.NodeID = :id2 and x.DocID = :id1 And x.PostingDocumentID IS NULL and x.Quantity > 0 "
+ " Group BY l.ProductID, l.UnitID ) as unpost On n.ProductID = unpost.ProductID "
+" AND n.UnitID = unpost.UnitID Where n.DocID = :id1";
StringBuilder sql = new StringBuilder(sQuery);
Parms = new List<Object[]>();
Parms.Add(new Object[] { "id1", docBalance.Document.DocID });
if (docBalance.Node != null && docBalance.Node.NodeID != 0)
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
else if (docBalance.Node != null && !string.IsNullOrEmpty(docBalance.Node.Name))
{
docBalance.Node = Factory.DaoNode().Select(docBalance.Node).First();
Parms.Add(new Object[] { "id2", docBalance.Node.NodeID });
}
if (docBalance != null)
{
if (docBalance.Product != null && docBalance.Product.ProductID != 0)
{
sql.Append(" and n.ProductID = :id3 ");
Parms.Add(new Object[] { "id3", docBalance.Product.ProductID });
}
sql.Append(" Group By n.docID, n.NodeID, n.productid, n.unitid, n.Quantity, unpost.Quantity");
}
IQuery query = Factory.Session.CreateSQLQuery(sql.ToString());
SetParameters(query);
IList<DocumentBalance> retBalance = GetBalanceObject(query.List<Object[]>(), docBalance.Location);
if (!Factory.IsTransactional)
Factory.Commit();
return retBalance;
}
//Get the crossdok balace comparar purchase docuemnt against sales documents
public IList<DocumentBalance> GetCrossDockBalance(DocumentBalance purchaseBalance, IList<Document> salesDocs)
{
if (salesDocs == null || salesDocs.Count == 0)
throw new Exception("No sales document availables for Crossdocking.");
//Checking if trackoptions are all populated in the receiving document.
ValidateReceiptDocumentTrackingOptions(purchaseBalance.Document, new Node {NodeID = NodeType.Received }, true);
Node pickNode = Factory.DaoNode().Select(new Node { NodeID = NodeType.Picked }).First();
//get Document Balance for the purchase
IList<DocumentBalance> purchase = Factory.DaoDocumentBalance().GeneralBalance(purchaseBalance, true);
//En Qty Pending grabamos lo que queda, para procesar
//Ese dato es el que disminuye en el cada que se cruza contra una orden
for (int i = 0; i < purchase.Count; i++)
purchase[i].QtyPending = purchase[i].QtyProcessed;
//Create the acumulator document balance
IList<DocumentBalance> resultBalance = new List<DocumentBalance>();
//Recorre la lista de Sales Documents y LLena el Acumulador
IList<DocumentBalance> curSalesBalance;
foreach (Document curDoc in salesDocs)
{
//Balance del Sales Order
try
{
curSalesBalance = Factory.DaoDocumentBalance()
.GeneralBalance(new DocumentBalance { Node = pickNode, Document = curDoc }, true)
.Where(f => f.Unit.BaseAmount == 1).ToList();
}
catch { continue; }
if (curSalesBalance == null || curSalesBalance.Count == 0)
continue;
//Cruce entre el sales order y el purchase order que genera las diferencias
/*
curSalesBalance =
(from sales in curSalesBalance
join purch in purchase on sales.Product.ProductID equals purch.Product.ProductID
into gj
from sub in gj.DefaultIfEmpty()
select new DocumentBalance
{
Document = sales.Document,
Product = sales.Product,
Quantity = sales.Quantity, //Added: *sales.Unit.BaseAmount Dec 05/09
QtyPending = (sub == null) ?
sales.Quantity :
(sales.Quantity <= sub.QtyPending * sub.Unit.BaseAmount) ?
0 : sales.Quantity - sub.QtyPending * sub.Unit.BaseAmount,
Unit = sales.Product.BaseUnit, //Se cambio sales.Unit
Notes = (sub == null) ? "Qty not supplied" : (sales.Quantity <= sub.QtyPending * sub.Unit.BaseAmount) ? " OK!" : "Qty not supplied"
}).ToList();
*/
//Added: *sales.Unit.BaseAmount Dec 05/09
curSalesBalance =
(from sales in curSalesBalance
join purch in purchase on sales.Product.ProductID equals purch.Product.ProductID
into gj
from sub in gj.DefaultIfEmpty()
select new DocumentBalance
{
Document = sales.Document,
Product = sales.Product,
Quantity = sales.Quantity * sales.Unit.BaseAmount,
QtyPending = (sub == null) ?
sales.Quantity * sales.Unit.BaseAmount :
(sales.Quantity * sales.Unit.BaseAmount <= sub.QtyPending * sub.Unit.BaseAmount) ?
0 : sales.Quantity * sales.Unit.BaseAmount - sub.QtyPending * sub.Unit.BaseAmount,
Unit = sales.Product.BaseUnit, //Se cambio sales.Unit
Notes = (sub == null) ? "Qty not supplied" : (sales.Quantity * sales.Unit.BaseAmount <= sub.QtyPending * sub.Unit.BaseAmount) ? " OK!" : "Qty not supplied"
}).ToList();
purchase = //Adicion de BaseAmount porque sales viene en EACH
(from purch in purchase
join sales in curSalesBalance on purch.Product.ProductID equals sales.Product.ProductID
into gj
from sub in gj.DefaultIfEmpty()
select new DocumentBalance
{
Document = purch.Document,
Product = purch.Product,
Quantity = purch.Quantity,
QtyPending = (sub == null) ? purch.QtyPending : (double)((int)((purch.QtyPending*purch.Unit.BaseAmount - sub.QtyProcessed)/purch.Unit.BaseAmount)),
Unit = purch.Unit
}).ToList();
////Lenando el acumulador con el documento de venta actual
foreach (DocumentBalance curBal in curSalesBalance)
resultBalance.Add(curBal);
}
////Lenando el acumulador con el resumen del docuemento de compras
foreach (DocumentBalance curBal in purchase) {
if (curBal.QtyPending > 0)
curBal.Notes = "Qty remain in PO";
resultBalance.Add(curBal);
}
return resultBalance;
}
