/// <summary>
/// Gets the Server date.
/// </summary>
/// <returns></returns>
public DateTime GetServerDate()
{
using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
{
return(db.GetServerDate());
}
}
/// <summary>
/// Deletes the transaction.
/// </summary>
/// <param name="miscIssueMasterId">The misc issue master id.</param>
public void DeleteTransaction(int miscIssueMasterId)
{
const string methodName = This + ".DeleteTransaction()";
try
{
using (var trans = new TransactionScope())
{
using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
{
var miscTranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.IVMiscellaneousIssue.ToString()).TranTypeID;
var deleteTranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.DeleteTransaction.ToString()).TranTypeID;
var serverDate = db.GetServerDate();
#region IV_MiscellaneousIssueMaster
var issueMaster = db.IV_MiscellaneousIssueMasters.FirstOrDefault(e => e.MiscellaneousIssueMasterID == miscIssueMasterId);
if (issueMaster == null)
{
return;
}
#endregion
#region old transaction history to be updated
var oldSourceHistory = db.MST_TransactionHistories.Where(e => e.RefMasterID == miscIssueMasterId && e.TranTypeID == miscTranTypeId);
foreach (var history in oldSourceHistory)
{
// mark as delete transaction
history.TranTypeID = deleteTranTypeId;
history.UserName = SystemProperty.UserName;
}
#endregion
#region cache data
foreach (IV_MiscellaneousIssueDetail issueDetail in issueMaster.IV_MiscellaneousIssueDetails)
{
var allowNegative = issueDetail.ITM_Product.AllowNegativeQty.GetValueOrDefault(false);
#region subtract from destionation cache
if (issueMaster.DesMasLocationID.GetValueOrDefault(0) > 0)
{
#region bin cache
var destBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.DesLocationID && e.BinID == issueMaster.DesBinID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (destBin == null || destBin.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, destBin == null ? 0 : destBin.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (destBin != null)
{
destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity;
}
else
{
// create new record
destBin = new IV_BinCache
{
BinID = issueMaster.DesBinID.GetValueOrDefault(0),
CCNID = issueMaster.CCNID,
LocationID = issueMaster.DesLocationID.GetValueOrDefault(0),
MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0),
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.Quantity
};
db.IV_BinCaches.InsertOnSubmit(destBin);
}
#endregion
#region location cache
var destLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.LocationID == issueMaster.DesLocationID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (destLocation == null || destLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, destLocation == null ? 0 : destLocation.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (destLocation != null)
{
destLocation.OHQuantity = destLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity;
}
else
{
// create new record
destLocation = new IV_LocationCache
{
CCNID = issueMaster.CCNID,
LocationID = issueMaster.DesLocationID.GetValueOrDefault(0),
MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0),
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.Quantity
};
db.IV_LocationCaches.InsertOnSubmit(destLocation);
}
#endregion
#region master location cache
var destMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (destMasLocation == null || destMasLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, destMasLocation == null ? 0 : destMasLocation.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (destMasLocation != null)
{
destMasLocation.OHQuantity = destMasLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity;
}
else
{
// create new record
destMasLocation = new IV_MasLocCache
{
CCNID = issueMaster.CCNID,
MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0),
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.Quantity
};
db.IV_MasLocCaches.InsertOnSubmit(destMasLocation);
}
#endregion
#region Transaction history
var destHistory = new MST_TransactionHistory
{
CCNID = issueMaster.CCNID,
StockUMID = issueDetail.StockUMID,
MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0),
ProductID = issueDetail.ProductID,
LocationID = issueMaster.DesLocationID,
BinID = issueMaster.DesBinID,
RefMasterID = issueMaster.MiscellaneousIssueMasterID,
RefDetailID = issueDetail.MiscellaneousIssueDetailID,
PostDate = issueMaster.PostDate,
TransDate = serverDate,
Quantity = -issueDetail.Quantity,
UserName = SystemProperty.UserName,
TranTypeID = miscTranTypeId,
BinCommitQuantity = destBin.CommitQuantity,
BinOHQuantity = destBin.OHQuantity,
IssuePurposeID = issueMaster.IssuePurposeID,
LocationCommitQuantity = destLocation.CommitQuantity,
LocationOHQuantity = destLocation.OHQuantity,
MasLocCommitQuantity = destMasLocation.CommitQuantity,
MasLocOHQuantity = destMasLocation.OHQuantity
};
db.MST_TransactionHistories.InsertOnSubmit(destHistory);
#endregion
}
#endregion
#region add to source cache
#region bin cache
var sourceBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.SourceLocationID && e.BinID == issueMaster.SourceBinID && e.ProductID == issueDetail.ProductID);
if (sourceBin != null)
{
sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity;
}
else
{
// create new record
sourceBin = new IV_BinCache
{
BinID = issueMaster.SourceBinID.GetValueOrDefault(0),
CCNID = issueMaster.CCNID,
LocationID = issueMaster.SourceLocationID,
MasterLocationID = issueMaster.SourceMasLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.Quantity
};
db.IV_BinCaches.InsertOnSubmit(sourceBin);
}
#endregion
#region location cache
var sourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.LocationID == issueMaster.SourceLocationID && e.ProductID == issueDetail.ProductID);
if (sourceLocation != null)
{
sourceLocation.OHQuantity = sourceLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity;
}
else
{
// create new record
sourceLocation = new IV_LocationCache
{
CCNID = issueMaster.CCNID,
LocationID = issueMaster.SourceLocationID,
MasterLocationID = issueMaster.SourceMasLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.Quantity
};
db.IV_LocationCaches.InsertOnSubmit(sourceLocation);
}
#endregion
#region master location cache
var sourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.ProductID == issueDetail.ProductID);
if (sourceMasLocation != null)
{
sourceMasLocation.OHQuantity = sourceMasLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity;
}
else
{
// create new record
sourceMasLocation = new IV_MasLocCache
{
CCNID = issueMaster.CCNID,
MasterLocationID = issueMaster.SourceMasLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.Quantity
};
db.IV_MasLocCaches.InsertOnSubmit(sourceMasLocation);
}
#endregion
#region Transaction history
var sourceHistory = new MST_TransactionHistory
{
CCNID = issueMaster.CCNID,
StockUMID = issueDetail.StockUMID,
MasterLocationID = issueMaster.SourceMasLocationID,
ProductID = issueDetail.ProductID,
LocationID = issueMaster.SourceLocationID,
BinID = issueMaster.SourceBinID,
RefMasterID = issueMaster.MiscellaneousIssueMasterID,
RefDetailID = issueDetail.MiscellaneousIssueDetailID,
PostDate = issueMaster.PostDate,
TransDate = serverDate,
Quantity = issueDetail.Quantity,
UserName = SystemProperty.UserName,
TranTypeID = miscTranTypeId,
BinCommitQuantity = sourceBin.CommitQuantity,
BinOHQuantity = sourceBin.OHQuantity,
IssuePurposeID = issueMaster.IssuePurposeID,
LocationCommitQuantity = sourceLocation.CommitQuantity,
LocationOHQuantity = sourceLocation.OHQuantity,
MasLocCommitQuantity = sourceMasLocation.CommitQuantity,
MasLocOHQuantity = sourceMasLocation.OHQuantity
};
db.MST_TransactionHistories.InsertOnSubmit(sourceHistory);
#endregion
#endregion
db.IV_MiscellaneousIssueDetails.DeleteOnSubmit(issueDetail);
}
#endregion
db.IV_MiscellaneousIssueMasters.DeleteOnSubmit(issueMaster);
db.SubmitChanges();
trans.Complete();
}
}
}
catch (PCSBOException ex)
{
if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
}
if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE)
{
throw;
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
}
public void SaveCompletion(DataTable dataSource, DateTime postDate, string multiTransNo, int masterLocationId, int shiftId, int purposeId)
{
const string methodName = This + ".SaveCompletion()";
try
{
using (var trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (var dataContext = new PCSDataContext(Utils.Instance.ConnectionString))
{
int tranTypeId = dataContext.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionType.WORK_ORDER_COMPLETION).TranTypeID;
var serverDate = dataContext.GetServerDate();
foreach (var woCompletion in from DataRow row in dataSource.Rows
where row.RowState != DataRowState.Deleted && row.RowState != DataRowState.Detached
select CreateNewCompletion(row, postDate, serverDate, multiTransNo, masterLocationId, shiftId, purposeId))
{
// save work order completion transaction first
dataContext.PRO_WorkOrderCompletions.InsertOnSubmit(woCompletion);
dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);
#region get list of components and subtract quantity
var components = dataContext.ITM_BOMs.Where(b => b.ProductID == woCompletion.ProductID);
// DO NOT ALLOW USER COMPLETE THE WORK ORDER THAT NOT HAS COMPONENT
if (components.Count() == 0)
{
throw new PCSBOException(ErrorCode.MESSAGE_NOT_HAS_COMPONENT_TO_COMPLETE, methodName, new Exception());
}
var binCacheData = from binCache in dataContext.IV_BinCaches
join bin in dataContext.MST_BINs on binCache.BinID equals bin.BinID
where bin.LocationID == woCompletion.PRO_WorkOrderMaster.PRO_ProductionLine.LocationID.GetValueOrDefault(0) &&
bin.BinTypeID == (int?)BinTypeEnum.IN
select binCache;
int locationId = woCompletion.PRO_WorkOrderMaster.PRO_ProductionLine.LocationID.GetValueOrDefault(0);
//Subtract quantity in buffer location
foreach (var component in components)
{
var subtractQuantity = woCompletion.CompletedQuantity * component.Quantity;
var binCache = binCacheData.FirstOrDefault(b => b.ProductID == component.ComponentID);
// check onhand quantity in case component not allow negative onhand quantity
if (!component.ITM_Product.AllowNegativeQty.GetValueOrDefault(false))
{
// not enough quantity to subtract
if (binCache == null || subtractQuantity > binCache.OHQuantity.GetValueOrDefault(0))
{
var productError = new Hashtable {
{ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD, woCompletion.WorkOrderDetailID }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE,
component.ITM_Product.Code, new Exception(), productError);
}
}
#region substract Master Location Cache
var masLocCache = dataContext.IV_MasLocCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.ProductID == component.ComponentID);
if (masLocCache != null)
{
masLocCache.OHQuantity = masLocCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity;
}
else
{
masLocCache = new IV_MasLocCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
ProductID = component.ComponentID,
OHQuantity = -subtractQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_MasLocCaches.InsertOnSubmit(masLocCache);
}
decimal?masLocOnHand = masLocCache.OHQuantity;
decimal?masLocCommit = masLocCache.CommitQuantity;
#endregion
#region substract Location Cache
var locCache = dataContext.IV_LocationCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == locationId && m.ProductID == component.ComponentID);
if (locCache != null)
{
locCache.OHQuantity = locCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity;
}
else
{
locCache = new IV_LocationCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
LocationID = locationId,
ProductID = component.ComponentID,
OHQuantity = -subtractQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_LocationCaches.InsertOnSubmit(locCache);
}
decimal?locOnHand = locCache.OHQuantity;
decimal?locCommit = locCache.CommitQuantity;
#endregion
#region substract Bin Cache
var componentBinCache = dataContext.IV_BinCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == locationId && m.BinID == binCache.BinID && m.ProductID == component.ComponentID);
if (componentBinCache != null)
{
componentBinCache.OHQuantity = componentBinCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity;
}
else
{
componentBinCache = new IV_BinCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
LocationID = locationId,
BinID = binCache.BinID,
ProductID = component.ComponentID,
OHQuantity = -subtractQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_BinCaches.InsertOnSubmit(componentBinCache);
}
decimal?binOnHand = componentBinCache.OHQuantity;
decimal?binCommit = componentBinCache.CommitQuantity;
#endregion
#region transaction history
var transHistory = new MST_TransactionHistory
{
BinID = binCache.BinID,
TransDate = serverDate,
TranTypeID = tranTypeId,
LocationID = locationId,
ProductID = component.ComponentID,
CCNID = woCompletion.CCNID,
Lot = woCompletion.Lot,
MasterLocationID = woCompletion.MasterLocationID,
StockUMID = component.ITM_Product.StockUMID,
Serial = woCompletion.Serial,
PostDate = woCompletion.PostDate,
RefMasterID = woCompletion.WorkOrderCompletionID,
Quantity = -subtractQuantity,
MasLocCommitQuantity = masLocCommit,
BinCommitQuantity = binCommit,
LocationCommitQuantity = locCommit,
MasLocOHQuantity = masLocOnHand,
LocationOHQuantity = locOnHand,
BinOHQuantity = binOnHand,
UserName = SystemProperty.UserName,
IssuePurposeID = (int?)PurposeEnum.Completion
};
dataContext.MST_TransactionHistories.InsertOnSubmit(transHistory);
#endregion
}
#endregion
#region add completion quantity to cache and add transaction history
#region add Master Location Cache
var masLoc = dataContext.IV_MasLocCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.ProductID == woCompletion.ProductID);
if (masLoc != null)
{
masLoc.OHQuantity = masLoc.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity;
}
else
{
masLoc = new IV_MasLocCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
ProductID = woCompletion.ProductID,
OHQuantity = woCompletion.CompletedQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_MasLocCaches.InsertOnSubmit(masLoc);
}
decimal?masOnHand = masLoc.OHQuantity;
decimal?masCommit = masLoc.CommitQuantity;
#endregion
#region add Location Cache
var lCache = dataContext.IV_LocationCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == woCompletion.LocationID && m.ProductID == woCompletion.ProductID);
if (lCache != null)
{
lCache.OHQuantity = lCache.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity;
}
else
{
lCache = new IV_LocationCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
LocationID = woCompletion.LocationID,
ProductID = woCompletion.ProductID,
OHQuantity = woCompletion.CompletedQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_LocationCaches.InsertOnSubmit(lCache);
}
decimal?lOnhand = lCache.OHQuantity;
decimal?lCommit = lCache.CommitQuantity;
#endregion
#region add Bin Cache
var bCache = dataContext.IV_BinCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == woCompletion.LocationID && m.BinID == woCompletion.BinID && m.ProductID == woCompletion.ProductID);
if (bCache != null)
{
bCache.OHQuantity = bCache.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity;
}
else
{
bCache = new IV_BinCache
{
CCNID = woCompletion.CCNID,
MasterLocationID = woCompletion.MasterLocationID,
LocationID = woCompletion.LocationID,
BinID = woCompletion.BinID.GetValueOrDefault(0),
ProductID = woCompletion.ProductID,
OHQuantity = woCompletion.CompletedQuantity,
Lot = woCompletion.Lot
};
dataContext.IV_BinCaches.InsertOnSubmit(bCache);
}
decimal?bOnhand = bCache.OHQuantity;
decimal?bCommit = bCache.CommitQuantity;
#endregion
#region transaction history
var transHistory1 = new MST_TransactionHistory
{
BinID = woCompletion.BinID,
TransDate = serverDate,
TranTypeID = tranTypeId,
LocationID = woCompletion.LocationID,
ProductID = woCompletion.ProductID,
CCNID = woCompletion.CCNID,
Lot = woCompletion.Lot,
MasterLocationID = woCompletion.MasterLocationID,
StockUMID = woCompletion.StockUMID,
Serial = woCompletion.Serial,
PostDate = woCompletion.PostDate,
RefMasterID = woCompletion.WorkOrderCompletionID,
RefDetailID = woCompletion.WorkOrderCompletionID,
Quantity = woCompletion.CompletedQuantity,
MasLocCommitQuantity = masCommit,
BinCommitQuantity = bCommit,
LocationCommitQuantity = lCommit,
MasLocOHQuantity = masOnHand,
LocationOHQuantity = lOnhand,
BinOHQuantity = bOnhand,
UserName = SystemProperty.UserName,
IssuePurposeID = (int?)PurposeEnum.Completion
};
dataContext.MST_TransactionHistories.InsertOnSubmit(transHistory1);
#endregion
#endregion
#region update work order detail status if completed
var orderQuantity = woCompletion.PRO_WorkOrderDetail.OrderQuantity;
var totalCompleted = dataContext.PRO_WorkOrderCompletions.Where(
w => w.WorkOrderDetailID == woCompletion.WorkOrderDetailID).Sum(w => w.CompletedQuantity);
if (totalCompleted >= orderQuantity)
{
dataContext.PRO_WorkOrderDetails.FirstOrDefault(w => w.WorkOrderDetailID == woCompletion.WorkOrderDetailID).Status = (byte?)WOLineStatus.MfgClose;
}
#endregion
// submit changes for each completion transaction
dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);
#region refresh all changes made since we already submit changes
var changeSet = dataContext.GetChangeSet();
var refreshedTables = new List <ITable>();
foreach (var item in changeSet.Updates)
{
var table = dataContext.GetTable(item.GetType());
if (refreshedTables.Contains(table))
{
continue;
}
refreshedTables.Add(table);
dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table);
}
foreach (var item in changeSet.Deletes)
{
var table = dataContext.GetTable(item.GetType());
if (refreshedTables.Contains(table))
{
continue;
}
refreshedTables.Add(table);
dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table);
}
foreach (var item in changeSet.Inserts)
{
var table = dataContext.GetTable(item.GetType());
if (refreshedTables.Contains(table))
{
continue;
}
refreshedTables.Add(table);
dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table);
}
#endregion
}
}
trans.Complete();
}
}
catch (SqlException ex)
{
if (ex.Errors.Count > 1)
{
if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
}
/// <summary>
/// Adjusts the purchase orders.
/// </summary>
/// <param name="fromDate">From date.</param>
/// <param name="toDate">To date.</param>
/// <param name="partyId"></param>
public void AdjustPurchaseOrder(DateTime fromDate, DateTime toDate, int?partyId)
{
var methodName = GetType().FullName + ".UpdateCPODataset()";
try
{
using (var trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {
IsolationLevel = IsolationLevel.ReadUncommitted
}))
{
using (var dataContext = new PCSDataContext(Utils.Instance.ConnectionString))
{
var serverDate = dataContext.GetServerDate();
var newDeliveries = new List <PO_DeliverySchedule>();
var newDetails = new List <PO_PurchaseOrderDetail>();
var updatedDetail = new List <int>();
// find all purchase orders in range was approved
var approvedPOs = (from orderDetail in dataContext.PO_PurchaseOrderDetails
join orderMaster in dataContext.PO_PurchaseOrderMasters on
orderDetail.PurchaseOrderMasterID equals
orderMaster.PurchaseOrderMasterID
join schedule in dataContext.PO_DeliverySchedules on
orderDetail.PurchaseOrderDetailID equals schedule.PurchaseOrderDetailID
where orderDetail.ApproverID.GetValueOrDefault(0) > 0 &&
schedule.ScheduleDate.CompareTo(fromDate) >= 0 &&
schedule.ScheduleDate.CompareTo(toDate) <= 0 &&
schedule.ReceivedQuantity.GetValueOrDefault(0) == 0 &&
partyId.GetValueOrDefault(0) > 0 ? orderMaster.PartyID == partyId.GetValueOrDefault(0) : orderMaster.PartyID > 0
select new {
orderMaster.PartyID,
orderMaster.PurchaseOrderMasterID,
orderDetail.PurchaseOrderDetailID,
orderDetail.ProductID,
schedule.DeliveryScheduleID,
schedule.ScheduleDate,
schedule.DeliveryQuantity
}).ToList();
// find all un-approved purchase orders in range
var unApprovedPOs = (from orderDetail in dataContext.PO_PurchaseOrderDetails
join orderMaster in dataContext.PO_PurchaseOrderMasters on
orderDetail.PurchaseOrderMasterID equals orderMaster.PurchaseOrderMasterID
join schedule in dataContext.PO_DeliverySchedules on orderDetail.PurchaseOrderDetailID equals schedule.PurchaseOrderDetailID
where orderDetail.ApproverID.GetValueOrDefault(0) == 0 &&
schedule.ScheduleDate.CompareTo(fromDate) >= 0 &&
schedule.ScheduleDate.CompareTo(toDate) <= 0 &&
partyId.GetValueOrDefault(0) > 0 ? orderMaster.PartyID == partyId.GetValueOrDefault(0) : orderMaster.PartyID > 0
select new {
orderMaster.PartyID,
orderMaster.PurchaseOrderMasterID,
orderDetail.PurchaseOrderDetailID,
orderDetail.ProductID,
schedule.DeliveryScheduleID,
schedule.ScheduleDate,
schedule.DeliveryQuantity
}).ToList();
foreach (var approvedPO in approvedPOs)
{
#region find new delivery with same schedule date
var delivery = dataContext.PO_DeliverySchedules.FirstOrDefault(d => d.DeliveryScheduleID == approvedPO.DeliveryScheduleID);
var newDelivery = unApprovedPOs.FirstOrDefault(e =>
e.PartyID == approvedPO.PartyID && e.ProductID == approvedPO.ProductID &&
e.ScheduleDate.Equals(approvedPO.ScheduleDate));
if (newDelivery != null)
{
delivery.Adjustment = newDelivery.DeliveryQuantity - delivery.DeliveryQuantity;
delivery.DeliveryQuantity = newDelivery.DeliveryQuantity;
}
else
{
// old delivery does not exist in new schedules
delivery.Adjustment = -delivery.DeliveryQuantity;
delivery.DeliveryQuantity = 0;
}
if (!updatedDetail.Contains(delivery.PurchaseOrderDetailID))
{
updatedDetail.Add(delivery.PurchaseOrderDetailID);
}
#endregion
}
// new delivery schedule does not exist in current schedules
foreach (var unApprovedPO in unApprovedPOs)
{
var approvedSchedule = approvedPOs.FirstOrDefault(
d => d.PartyID == unApprovedPO.PartyID && d.ProductID == unApprovedPO.ProductID &&
d.ScheduleDate == unApprovedPO.ScheduleDate);
if (approvedSchedule == null)
{
var approvedPO = approvedPOs.FirstOrDefault(d => d.PartyID == unApprovedPO.PartyID && d.ProductID == unApprovedPO.ProductID);
if (approvedPO != null)
{
// check in new local list if we insert it or not
if (newDeliveries.Any(d => d.PO_PurchaseOrderDetail.ProductID == unApprovedPO.ProductID &&
d.PO_PurchaseOrderDetail.PO_PurchaseOrderMaster.PartyID == unApprovedPO.PartyID &&
d.ScheduleDate == unApprovedPO.ScheduleDate))
{
continue;
}
var poDetail = dataContext.PO_PurchaseOrderDetails.FirstOrDefault(p => p.PurchaseOrderDetailID == approvedPO.PurchaseOrderDetailID);
var maxDeliveryLine = poDetail.PO_DeliverySchedules.Max(d => d.DeliveryLine);
var newSchedule = new PO_DeliverySchedule
{
DeliveryQuantity = unApprovedPO.DeliveryQuantity,
Adjustment = unApprovedPO.DeliveryQuantity,
PurchaseOrderDetailID = poDetail.PurchaseOrderDetailID,
ScheduleDate = unApprovedPO.ScheduleDate,
DeliveryLine = maxDeliveryLine == 0 ? 1 : maxDeliveryLine + 1
};
if (!updatedDetail.Contains(poDetail.PurchaseOrderDetailID))
{
updatedDetail.Add(poDetail.PurchaseOrderDetailID);
}
// add new delivery schedule to po detail
poDetail.PO_DeliverySchedules.Add(newSchedule);
// add to local list
newDeliveries.Add(newSchedule);
}
else // need to add new purchase order detail and its delivery schedules
{
// check in new local list if we insert it or not
if (newDetails.Any(d => d.ProductID == unApprovedPO.ProductID && d.PO_PurchaseOrderMaster.PartyID == unApprovedPO.PartyID))
{
continue;
}
var unApprovedDetail = dataContext.PO_PurchaseOrderDetails.FirstOrDefault(p => p.PurchaseOrderDetailID == unApprovedPO.PurchaseOrderDetailID);
var approvedMaster = approvedPOs.FirstOrDefault(p => p.PartyID == unApprovedPO.PartyID);
var poMaster = dataContext.PO_PurchaseOrderMasters.FirstOrDefault(p => p.PurchaseOrderMasterID == approvedMaster.PurchaseOrderMasterID);
var maxLine = poMaster.PO_PurchaseOrderDetails.Max(d => d.Line);
// product in new order does not exists in current order
var newOrderDetail = new PO_PurchaseOrderDetail
{
ProductID = unApprovedDetail.ProductID,
BuyingUMID = unApprovedDetail.BuyingUMID,
DiscountAmount = unApprovedDetail.DiscountAmount,
ImportTax = unApprovedDetail.ImportTax,
NetAmount = unApprovedDetail.NetAmount,
OrderQuantity = unApprovedDetail.OrderQuantity,
Line = maxLine > 0 ? maxLine + 1 : 1,
RequiredDate = unApprovedDetail.RequiredDate,
SpecialTax = unApprovedDetail.SpecialTax,
SpecialTaxAmount = unApprovedDetail.SpecialTaxAmount,
StockUMID = unApprovedDetail.StockUMID,
TotalAmount = unApprovedDetail.TotalAmount,
UMRate = unApprovedDetail.UMRate,
UnitPrice = unApprovedDetail.UnitPrice,
VAT = unApprovedDetail.VAT,
VATAmount = unApprovedDetail.VATAmount,
VendorItem = unApprovedDetail.VendorItem,
VendorRevision = unApprovedDetail.VendorRevision,
ApproverID = SystemProperty.UserID,
ApprovalDate = serverDate
};
// add to local list
newDetails.Add(newOrderDetail);
foreach (var deliverySchedule in unApprovedDetail.PO_DeliverySchedules)
{
var newSchedule = new PO_DeliverySchedule
{
DeliveryQuantity = deliverySchedule.DeliveryQuantity,
Adjustment = deliverySchedule.DeliveryQuantity,
ScheduleDate = deliverySchedule.ScheduleDate,
DeliveryLine = deliverySchedule.DeliveryLine
};
// add delivery to new order dteail
newOrderDetail.PO_DeliverySchedules.Add(newSchedule);
newDeliveries.Add(newSchedule);
}
// add new order detail to order master
poMaster.PO_PurchaseOrderDetails.Add(newOrderDetail);
}
}
}
// now recalculate order quantity of approved purchase order detail
foreach (var detailId in updatedDetail)
{
var detail = dataContext.PO_PurchaseOrderDetails.FirstOrDefault(d => d.PurchaseOrderDetailID == detailId);
var orderQuantity = detail.PO_DeliverySchedules.Sum(d => d.DeliveryQuantity);
detail.OrderQuantity = orderQuantity;
}
dataContext.SubmitChanges();
}
trans.Complete();
}
}
catch (SqlException ex)
{
if (ex.Errors.Count > 1)
{
if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
}
public int AddAndReturnId(object pobjMaster, DataSet pdstDetailData)
{
const string methodName = "PCSComProduction.WorkOrder.BO.AddAndReturnID()";
try
{
using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted, Timeout = new TimeSpan(0, 1, 0)
}))
{
using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
{
var serverDate = db.GetServerDate();
var tranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.PROIssueMaterial.ToString()).TranTypeID;
#region add the master first
//add the master first
var objMaster = (PRO_IssueMaterialMasterVO)pobjMaster;
var issueMaster = new PRO_IssueMaterialMaster
{
MasterLocationID = objMaster.MasterLocationID,
PostDate = objMaster.PostDate,
IssueNo = objMaster.IssueNo,
CCNID = objMaster.CCNID,
IssuePurposeID = objMaster.IssuePurposeID,
ToLocationID = objMaster.ToLocationID,
ToBinID = objMaster.ToBinID,
ShiftID = objMaster.ShiftID
};
if (objMaster.WorkOrderMasterID > 0)
{
issueMaster.WorkOrderMasterID = objMaster.WorkOrderMasterID;
}
if (objMaster.WorkOrderDetailID > 0)
{
issueMaster.WorkOrderDetailID = objMaster.WorkOrderDetailID;
}
#endregion
#region insert issue detail
//update the detail
foreach (var issueDetail in
from DataRow dr in pdstDetailData.Tables[0].Rows
where dr.RowState != DataRowState.Deleted
select CreateIssueDetail(dr))
{
issueMaster.PRO_IssueMaterialDetails.Add(issueDetail);
}
db.PRO_IssueMaterialMasters.InsertOnSubmit(issueMaster);
db.SubmitChanges();
#endregion
#region cache data
foreach (var issueDetail in issueMaster.PRO_IssueMaterialDetails)
{
var allowNegative = issueDetail.ITM_Product.AllowNegativeQty.GetValueOrDefault(false);
#region subtract from source cache
#region bin cache
var sourceBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueDetail.LocationID && e.BinID == issueDetail.BinID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (sourceBin == null || sourceBin.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, sourceBin == null ? 0 : sourceBin.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (sourceBin != null)
{
sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity;
}
else
{
// create new record
sourceBin = new IV_BinCache
{
BinID = issueDetail.BinID.GetValueOrDefault(0),
CCNID = issueMaster.CCNID,
LocationID = issueDetail.LocationID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.CommitQuantity
};
db.IV_BinCaches.InsertOnSubmit(sourceBin);
}
#endregion
#region location cache
var sourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.LocationID == issueDetail.LocationID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (sourceLocation == null || sourceLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, sourceLocation == null ? 0 : sourceLocation.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (sourceLocation != null)
{
sourceLocation.OHQuantity = sourceLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity;
}
else
{
// create new record
sourceLocation = new IV_LocationCache
{
CCNID = issueMaster.CCNID,
LocationID = issueDetail.LocationID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.CommitQuantity
};
db.IV_LocationCaches.InsertOnSubmit(sourceLocation);
}
#endregion
#region master location cache
var sourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.ProductID == issueDetail.ProductID);
if (!allowNegative && (sourceMasLocation == null || sourceMasLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity))
{
var productError = new Hashtable
{
{ ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID },
{ IV_BinCacheTable.OHQUANTITY_FLD, sourceMasLocation == null ? 0 : sourceMasLocation.OHQuantity.GetValueOrDefault(0) }
};
throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError);
}
if (sourceMasLocation != null)
{
sourceMasLocation.OHQuantity = sourceMasLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity;
}
else
{
// create new record
sourceMasLocation = new IV_MasLocCache
{
CCNID = issueMaster.CCNID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = -issueDetail.CommitQuantity
};
db.IV_MasLocCaches.InsertOnSubmit(sourceMasLocation);
}
#endregion
#region Transaction history
var sourceHistory = new MST_TransactionHistory
{
CCNID = issueMaster.CCNID,
StockUMID = issueDetail.StockUMID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
LocationID = issueDetail.LocationID,
BinID = issueDetail.BinID,
RefMasterID = issueMaster.IssueMaterialMasterID,
RefDetailID = issueDetail.IssueMaterialDetailID,
PostDate = issueMaster.PostDate,
TransDate = serverDate,
Quantity = -issueDetail.CommitQuantity,
UserName = SystemProperty.UserName,
TranTypeID = tranTypeId,
BinCommitQuantity = sourceBin.CommitQuantity,
BinOHQuantity = sourceBin.OHQuantity,
IssuePurposeID = issueMaster.IssuePurposeID,
LocationCommitQuantity = sourceLocation.CommitQuantity,
LocationOHQuantity = sourceLocation.OHQuantity,
MasLocCommitQuantity = sourceMasLocation.CommitQuantity,
MasLocOHQuantity = sourceMasLocation.OHQuantity
};
db.MST_TransactionHistories.InsertOnSubmit(sourceHistory);
#endregion
#endregion
#region add to destination cache
#region bin cache
var destBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.ToLocationID && e.BinID == issueMaster.ToBinID && e.ProductID == issueDetail.ProductID);
if (destBin != null)
{
destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity;
}
else
{
// create new record
destBin = new IV_BinCache
{
BinID = issueMaster.ToBinID.GetValueOrDefault(0),
CCNID = issueMaster.CCNID,
LocationID = issueMaster.ToLocationID.GetValueOrDefault(0),
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.CommitQuantity
};
db.IV_BinCaches.InsertOnSubmit(destBin);
}
#endregion
#region location cache
var destLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.LocationID == issueMaster.ToLocationID && e.ProductID == issueDetail.ProductID);
if (destLocation != null)
{
destLocation.OHQuantity = destLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity;
}
else
{
// create new record
destLocation = new IV_LocationCache
{
CCNID = issueMaster.CCNID,
LocationID = issueMaster.ToLocationID.GetValueOrDefault(0),
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.CommitQuantity
};
db.IV_LocationCaches.InsertOnSubmit(destLocation);
}
#endregion
#region master location cache
var destMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.ProductID == issueDetail.ProductID);
if (destMasLocation != null)
{
destMasLocation.OHQuantity = destMasLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity;
}
else
{
// create new record
destMasLocation = new IV_MasLocCache
{
CCNID = issueMaster.CCNID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
OHQuantity = issueDetail.CommitQuantity
};
db.IV_MasLocCaches.InsertOnSubmit(destMasLocation);
}
#endregion
#region Transaction history
var destHistory = new MST_TransactionHistory
{
CCNID = issueMaster.CCNID,
StockUMID = issueDetail.StockUMID,
MasterLocationID = issueMaster.MasterLocationID,
ProductID = issueDetail.ProductID,
LocationID = issueMaster.ToLocationID,
BinID = issueMaster.ToBinID,
RefMasterID = issueMaster.IssueMaterialMasterID,
RefDetailID = issueDetail.IssueMaterialDetailID,
PostDate = issueMaster.PostDate,
TransDate = serverDate,
Quantity = issueDetail.CommitQuantity,
UserName = SystemProperty.UserName,
TranTypeID = tranTypeId,
BinCommitQuantity = destBin.CommitQuantity,
BinOHQuantity = destBin.OHQuantity,
IssuePurposeID = issueMaster.IssuePurposeID,
LocationCommitQuantity = destLocation.CommitQuantity,
LocationOHQuantity = destLocation.OHQuantity,
MasLocCommitQuantity = destMasLocation.CommitQuantity,
MasLocOHQuantity = destMasLocation.OHQuantity
};
db.MST_TransactionHistories.InsertOnSubmit(destHistory);
#endregion
#endregion
db.SubmitChanges();
}
#endregion
db.SubmitChanges();
trans.Complete();
return(issueMaster.IssueMaterialMasterID);
}
}
}
catch (PCSBOException ex)
{
if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
}
if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE)
{
throw;
}
throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
}
}