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