public InventoryLedger GetNewestInventoryLedgerByDate(Session session, Guid _ItemUnitId, Guid _InventoryId, DateTime _IssueDate) { try { ItemUnit _ItemUnit = session.GetObjectByKey <ItemUnit>(_ItemUnitId); NAS.DAL.Nomenclature.Inventory.Inventory _Inventory = session.GetObjectByKey <NAS.DAL.Nomenclature.Inventory.Inventory>(_InventoryId); if (_ItemUnit == null || _Inventory == null) { return(null); } CriteriaOperator criteria_RowStatus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); CriteriaOperator criteria_ItemUnit = new BinaryOperator("ItemUnitId", _ItemUnit, BinaryOperatorType.Equal); CriteriaOperator criteria_Inventory = new BinaryOperator("InventoryId", _Inventory, BinaryOperatorType.Equal); CriteriaOperator criteria_IssueDate = new BinaryOperator("IssueDate", _IssueDate, BinaryOperatorType.LessOrEqual); CriteriaOperator criteria = CriteriaOperator.And(criteria_Inventory, criteria_ItemUnit, criteria_RowStatus); XPCollection <InventoryLedger> InventoryLedgerCol = new XPCollection <InventoryLedger>(session, criteria); InventoryLedgerCol.Sorting.Add(new SortProperty("IssueDate", SortingDirection.Descending)); InventoryLedgerCol.Sorting.Add(new SortProperty("CreateDate", SortingDirection.Descending)); InventoryLedger newestLedger = InventoryLedgerCol.FirstOrDefault(); return(newestLedger); } catch (Exception) { return(null); } }
public InventoryLedger GetPreviousInventoryLedger(Session session, DateTime IssueDate, Guid InventoryId, Guid AccountId, Guid LotID, Guid ItemUnitId) { InventoryLedger result = null; try { NAS.DAL.Nomenclature.Inventory.Inventory inventory = session.GetObjectByKey <NAS.DAL.Nomenclature.Inventory.Inventory>(InventoryId); Account account = session.GetObjectByKey <Account>(AccountId); Lot lot = session.GetObjectByKey <Lot>(LotID); ItemUnit itemUnit = session.GetObjectByKey <ItemUnit>(ItemUnitId); if (inventory == null || account == null || lot == null || itemUnit == null) { return(null); } CriteriaOperator criteria_RowStatus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); CriteriaOperator criteria_IssueDate = new BinaryOperator("IssueDate", IssueDate, BinaryOperatorType.LessOrEqual); CriteriaOperator criteria_Inventory = new BinaryOperator("InventoryId", inventory, BinaryOperatorType.Equal); CriteriaOperator criteria_Account = new BinaryOperator("AccountId", account, BinaryOperatorType.Equal); CriteriaOperator criteria_Lot = new BinaryOperator("LotId", lot, BinaryOperatorType.Equal); CriteriaOperator criteria_ItemUnit = new BinaryOperator("ItemUnitId", itemUnit, BinaryOperatorType.Equal); CriteriaOperator criteria = CriteriaOperator.And(criteria_IssueDate, criteria_RowStatus, criteria_Inventory, criteria_Account, criteria_Lot, criteria_ItemUnit); XPCollection <InventoryLedger> inventoryLedgerCol = new XPCollection <InventoryLedger>(session, criteria); } catch (Exception) { return(result); } return(result); }
public double GetItemUnitBalance(Session session, Guid _ItemUnitId, Guid _InventoryId) { double result = 0; try { InventoryLedger newestLedger = GetNewestInventoryLedger(session, _ItemUnitId, _InventoryId); if (newestLedger == null) { return(0); } result = newestLedger.Balance; return(result); } catch (Exception) { return(0); } }
LoadInventoryLedger(Session session, List <ETL_InventoryLedger> ledgerList) { try { if (ledgerList == null) { return; } if (ledgerList.Count == 0) { return; } if (ledgerList.Any() == false) { return; } InventoryTransaction invTransaction = session.GetObjectByKey <InventoryTransaction>((ledgerList.FirstOrDefault()).InventoryTransactionId); if (invTransaction == null) { return; } CriteriaOperator criteria_RowStatus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); CriteriaOperator criteria_Inventory = new BinaryOperator("InventoryTransactionId", invTransaction, BinaryOperatorType.Equal); CriteriaOperator criteria = CriteriaOperator.And(criteria_Inventory, criteria_RowStatus); XPCollection <InventoryLedger> OldInventoryLedger = new XPCollection <InventoryLedger>(session, criteria); foreach (InventoryLedger ledger in OldInventoryLedger) { ledger.RowStatus = Constant.ROWSTATUS_DELETED; ledger.Save(); } foreach (ETL_InventoryLedger ledger in ledgerList) { InventoryLedger newInventoryLedger = new InventoryLedger(session); newInventoryLedger.AccountId = session.GetObjectByKey <Account>(ledger.AccountId); newInventoryLedger.CreateDate = ledger.CreateDate; newInventoryLedger.Credit = ledger.Credit; newInventoryLedger.Debit = ledger.Debit; newInventoryLedger.Balance = ledger.Debit - ledger.Credit; newInventoryLedger.Description = ledger.Description; newInventoryLedger.InventoryId = session.GetObjectByKey <NAS.DAL.Nomenclature.Inventory.Inventory>(ledger.InventoryId); newInventoryLedger.InventoryTransactionId = session.GetObjectByKey <InventoryTransaction>(ledger.InventoryTransactionId); newInventoryLedger.IsOriginal = ledger.IsOriginal; newInventoryLedger.IssueDate = ledger.IssueDate; newInventoryLedger.ItemUnitId = session.GetObjectByKey <ItemUnit>(ledger.ItemUnitId); newInventoryLedger.LotId = session.GetObjectByKey <Lot>(ledger.LotId); newInventoryLedger.RowStatus = Constant.ROWSTATUS_ACTIVE; newInventoryLedger.UpdateDate = DateTime.Now; InventoryLedger preInventoryLedger = GetPreviousInventoryLedger(session, newInventoryLedger); if (preInventoryLedger != null) { newInventoryLedger.Balance += preInventoryLedger.Balance; } newInventoryLedger.Save(); } } catch (Exception) { return; } }
public InventoryLedger GetPreviousInventoryLedger(Session session, InventoryLedger ledger) { InventoryLedger result = null; try { //CriteriaOperator criteria_RowStatus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); //CriteriaOperator criteria_IssueDate = new BinaryOperator("IssueDate", ledger.IssueDate, BinaryOperatorType.Less); //CriteriaOperator criteria_IssueDate1 = new BinaryOperator("IssueDate", ledger.IssueDate, BinaryOperatorType.Equal); //CriteriaOperator criteria_CreateDate = new BinaryOperator("CreateDate", ledger.CreateDate, BinaryOperatorType.LessOrEqual); //CriteriaOperator criteria_Inventory = new BinaryOperator("InventoryId", ledger.InventoryId, BinaryOperatorType.Equal); //CriteriaOperator criteria_ItemUnit = new BinaryOperator("ItemUnitId", ledger.ItemUnitId, BinaryOperatorType.Equal); //CriteriaOperator criteria_InventoryLedgerId = new BinaryOperator("InventoryLedgerId!Key", ledger.InventoryLedgerId, BinaryOperatorType.NotEqual); //CriteriaOperator criteria_DateTime = CriteriaOperator.Or( criteria_IssueDate, // CriteriaOperator.And(criteria_IssueDate1, criteria_CreateDate)); //CriteriaOperator criteria = CriteriaOperator.And(criteria_DateTime, criteria_Inventory, criteria_InventoryLedgerId, criteria_RowStatus, criteria_ItemUnit); XPQuery <InventoryLedger> InventoryLedgerQuery = new XPQuery <InventoryLedger>(session); IQueryable <InventoryLedger> InventoryLedgerCol = (from r in InventoryLedgerQuery where r.RowStatus >= Constant.ROWSTATUS_ACTIVE && r.IssueDate == ledger.IssueDate && r.CreateDate <= ledger.CreateDate && r.InventoryId == ledger.InventoryId && r.InventoryLedgerId != ledger.InventoryLedgerId && r.ItemUnitId == ledger.ItemUnitId select r); if (InventoryLedgerCol != null) { if (InventoryLedgerCol.Count() > 0) { result = InventoryLedgerCol.OrderByDescending(r => r.CreateDate).FirstOrDefault(); return(result); } } InventoryLedgerCol = (from r in InventoryLedgerQuery where r.RowStatus >= Constant.ROWSTATUS_ACTIVE && r.IssueDate < ledger.IssueDate && r.InventoryId == ledger.InventoryId && r.InventoryLedgerId != ledger.InventoryLedgerId && r.ItemUnitId == ledger.ItemUnitId select r); if (InventoryLedgerCol != null) { if (InventoryLedgerCol.Count() > 0) { result = InventoryLedgerCol.OrderByDescending(r => r.IssueDate).OrderByDescending(r => r.CreateDate).FirstOrDefault(); return(result); } } return(null); } catch (Exception) { return(null); } return(result); }