private void UpdateProduct(Guid txHeaderId, Guid workplaceId)
{
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
//string sql = "HeaderId = '" + txHeaderId.ToString() + "'";
var detailsList = ctx.StockTakeDetails.Where(x => x.HeaderId == txHeaderId).AsNoTracking().ToList();
foreach (var detail in detailsList)
{
//StockTakeDetails detail = detailsList[i];
decimal stkTtlQty = (detail.Book1Qty.Value + detail.Book2Qty.Value + detail.Book3Qty.Value + detail.Book4Qty.Value + detail.Book5Qty.Value + detail.HHTQty.Value) - detail.CapturedQty.Value;
Guid productId = detail.ProductId.Value;
decimal qty = stkTtlQty;
#region UpdateProductCurrentSummary(detail.ProductId.Value, stkTtlQty);
var currProd = ctx.ProductCurrentSummary.Where(x => x.ProductId == productId).FirstOrDefault();
if (currProd == null)
{
currProd = new EF6.ProductCurrentSummary();
currProd.CurrentSummaryId = Guid.NewGuid();
currProd.ProductId = productId;
ctx.ProductCurrentSummary.Add(currProd);
}
currProd.CDQTY += qty;
#endregion
#region UpdateProductQty(detail.ProductId.Value, workplaceId, stkTtlQty);
var wpProd = ctx.ProductWorkplace.Where(x => x.ProductId == productId && x.WorkplaceId == workplaceId).FirstOrDefault();
if (wpProd == null)
{
wpProd = new EF6.ProductWorkplace();
wpProd.ProductWorkplaceId = Guid.NewGuid();
wpProd.ProductId = productId;
wpProd.WorkplaceId = workplaceId;
ctx.ProductWorkplace.Add(wpProd);
}
wpProd.CDQTY += qty;
#endregion
ctx.SaveChanges();
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}
public static EF6.ProductWorkplace Get(Guid productId, Guid workplaceId)
{
EF6.ProductWorkplace result = null;
using (var ctx = new EF6.RT2020Entities())
{
result = ctx.ProductWorkplace.Where(x => x.ProductId == productId && x.WorkplaceId == workplaceId).AsNoTracking().FirstOrDefault();
}
return(result);
}
private void UpdateLedgerDetails()
{
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
DateTime currMonth = SystemInfoEx.CurrentInfo.Default.CurrentSystemDate;
string whereClause = "TxDate >= CAST('" + currMonth.AddMonths(-1).ToString("yyyy-MM-dd 00:00:00") + "' AS DATETIME) AND TxDate < CAST('" + currMonth.ToString("yyyy-MM-dd 00:00:00") + "' AS DATETIME)";
var oHeaderList = ctx.InvtLedgerHeader.SqlQuery(
string.Format("Select * from InvtLedgerHeader Where{0} ", whereClause)
);
foreach (var oHeader in oHeaderList)
{
var oDetailList = ctx.InvtLedgerDetails.Where(x => x.HeaderId == oHeader.HeaderId);
foreach (var oDetail in oDetailList)
{
if (oDetail != null)
{
oDetail.TxDate = oHeader.TxDate;
oDetail.SHOP = WorkplaceEx.GetWorkplaceCodeById(oHeader.WorkplaceId);
#region this.AppendMissingProductWorkplace(oDetail.ProductId, oHeader.WorkplaceId);
var oProdWp = ctx.ProductWorkplace.Where(x => x.ProductId == oDetail.ProductId && x.WorkplaceId == oHeader.WorkplaceId).FirstOrDefault();
if (oProdWp == null)
{
oProdWp = new EF6.ProductWorkplace();
oProdWp.ProductWorkplaceId = Guid.NewGuid();
oProdWp.ProductId = oDetail.ProductId.Value;
oProdWp.WorkplaceId = oHeader.WorkplaceId;
ctx.ProductWorkplace.Add(oProdWp);
}
#endregion
}
}
ctx.SaveChanges();
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}
private void Confirmation()
{
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
var oHeader = ctx.InvtBatchTXF_Header.Find(this.TxferId);
if (oHeader != null)
{
var txnumber_Batch = oHeader.TxNumber;
#region SaveTxferDetail();
foreach (ListViewItem listItem in lvDetailsList.Items)
{
Guid detailId = Guid.Empty;
Guid productId = Guid.Empty;
if (Guid.TryParse(listItem.Text.Trim(), out detailId) && Guid.TryParse(listItem.SubItems[10].Text.Trim(), out productId))
{
//Guid detailId = new Guid(listItem.Text.Trim());
var oDetail = ctx.InvtBatchTXF_Details.Find(detailId);
if (oDetail != null)
{
oDetail.QtyConfirmed = Convert.ToDecimal(listItem.SubItems[9].Text.Length == 0 ? "0" : listItem.SubItems[9].Text);
ctx.SaveChanges();
}
var oLedgerDetail = ctx.InvtLedgerDetails.Find(detailId);
if (oLedgerDetail != null)
{
oLedgerDetail.CONFIRM_TRF_QTY = Convert.ToDecimal(listItem.SubItems[9].Text.Length == 0 ? "0" : listItem.SubItems[9].Text);
var oSubLedgerDetail = ctx.InvtSubLedgerTXF_Details.Find(oLedgerDetail.SubLedgerDetailsId);
if (oSubLedgerDetail != null)
{
oSubLedgerDetail.QtyConfirmed = Convert.ToDecimal(listItem.SubItems[9].Text.Length == 0 ? "0" : listItem.SubItems[9].Text);
}
ctx.SaveChanges();
}
var oFepDetail = ctx.FepBatchDetail.Find(detailId);
if (oFepDetail != null)
{
oFepDetail.CONFIRM_TRF_QTY = Convert.ToDecimal(listItem.SubItems[9].Text.Length == 0 ? "0" : listItem.SubItems[9].Text);
ctx.SaveChanges();
}
if (listItem.SubItems[9].Text.Trim().CompareTo(listItem.SubItems[8].Text.Trim()) == 0)
{
this.IsConfirmedTransaction = "Y";
}
else
{
this.IsConfirmedTransaction = "N";
}
}
}
#endregion
// Update TXF SubLedger
#region UpdateTXFSubLedger(oHeader.TxNumber);
var oSubTXF = ctx.InvtSubLedgerTXF_Header.Find(this.TxferId);
if (oSubTXF != null)
{
#region UpdateTXFSubLedgerDetail(oSubTXF.HeaderId);
var subLedgerHeaderId = oSubTXF.HeaderId;
foreach (ListViewItem listItem in lvDetailsList.Items)
{
Guid productId = Guid.Empty;
decimal qty = 0;
if (Guid.TryParse(listItem.SubItems[10].Text, out productId))
{
//string sql = "HeaderId = '" + subledgerHeaderId.ToString() + "' AND ProductId = '" + listItem.SubItems[10].Text + "'";
var oSubLedgerDetail = ctx.InvtSubLedgerTXF_Details.Where(x => x.HeaderId == subLedgerHeaderId && x.ProductId == productId).FirstOrDefault();
if (oSubLedgerDetail != null)
{
if (decimal.TryParse(listItem.SubItems[9].Text, out qty))
{
oSubLedgerDetail.QtyConfirmed = qty;
ctx.SaveChanges();
}
}
}
}
#endregion
oSubTXF.CONFIRM_TRF = this.IsConfirmedTransaction;
oSubTXF.CONFIRM_TRF_LASTUPDATE = DateTime.Now;
oSubTXF.CONFIRM_TRF_LASTUSER = ConfigHelper.CurrentUserId;
oSubTXF.ModifiedBy = ConfigHelper.CurrentUserId;
oSubTXF.ModifiedOn = DateTime.Now;
ctx.SaveChanges();
}
#endregion
// Update Ledger
#region UpdateLedger(oHeader.TxNumber);
var oLedgerHeader = ctx.InvtLedgerHeader.Find(this.TxferId);
if (oLedgerHeader != null)
{
#region UpdateLedgerDetails(oLedgerHeader.HeaderId);
var ledgerHeaderId = oLedgerHeader.HeaderId;
foreach (ListViewItem listItem in lvDetailsList.Items)
{
Guid productId = Guid.Empty;
decimal qty = 0;
if (Guid.TryParse(listItem.SubItems[10].Text, out productId))
{
//string sql = "HeaderId = '" + ledgerHeaderId.ToString() + "' AND ProductId = '" + listItem.SubItems[10].Text + "'";
var oLedgerDetail = ctx.InvtLedgerDetails.Where(x => x.HeaderId == ledgerHeaderId && x.ProductId == productId).FirstOrDefault();
if (oLedgerDetail != null)
{
if (decimal.TryParse(listItem.SubItems[9].Text, out qty))
{
oLedgerDetail.Qty = qty;
ctx.SaveChanges();
}
}
}
}
#endregion
oLedgerHeader.CONFIRM_TRF = this.IsConfirmedTransaction;
oLedgerHeader.CONFIRM_TRF_LASTUPDATE = DateTime.Now;
oLedgerHeader.CONFIRM_TRF_LASTUSER = StaffEx.GetStaffNumberById(ConfigHelper.CurrentUserId);
oLedgerHeader.ModifiedBy = ConfigHelper.CurrentUserId;
oLedgerHeader.ModifiedOn = DateTime.Now;
ctx.SaveChanges();
}
#endregion
// Update Product Info
#region UpdateProduct(oHeader);
var headerId = oHeader.HeaderId;
var fromId = oHeader.FromLocation.Value;
var toId = oHeader.ToLocation.Value;
//string sql = "HeaderId = '" + oBatchHeader.HeaderId.ToString() + "'";
var detailsList = ctx.InvtBatchTXF_Details.Where(x => x.HeaderId == headerId);
foreach (var detail in detailsList)
{
//InvtBatchTXF_Details detail = detailsList[i];
Guid productId = detail.ProductId.Value;
decimal qty = 0;
//Out
#region UpdateProductQty(detail.ProductId.Value, fromId, detail.QtyConfirmed.Value * (-1));
qty = detail.QtyConfirmed.Value * (-1);
var outPorductWorkplace = ctx.ProductWorkplace.Where(x => x.ProductId == productId && x.WorkplaceId == fromId).FirstOrDefault();
if (outPorductWorkplace == null)
{
outPorductWorkplace = new EF6.ProductWorkplace();
outPorductWorkplace.ProductWorkplaceId = Guid.NewGuid();
outPorductWorkplace.ProductId = productId;
outPorductWorkplace.WorkplaceId = fromId;
ctx.ProductWorkplace.Add(outPorductWorkplace);
}
outPorductWorkplace.CDQTY += qty;
if (qty > 0)
{
outPorductWorkplace.RECQTY += qty;
}
ctx.SaveChanges();
#endregion
//In
#region UpdateProductQty(detail.ProductId.Value, toId, detail.QtyConfirmed.Value);
qty = detail.QtyConfirmed.Value;
var inPorductWorkplace = ctx.ProductWorkplace.Where(x => x.ProductId == productId && x.WorkplaceId == toId).FirstOrDefault();
if (inPorductWorkplace == null)
{
inPorductWorkplace = new EF6.ProductWorkplace();
inPorductWorkplace.ProductWorkplaceId = Guid.NewGuid();
inPorductWorkplace.ProductId = productId;
inPorductWorkplace.WorkplaceId = fromId;
ctx.ProductWorkplace.Add(inPorductWorkplace);
}
inPorductWorkplace.CDQTY += qty;
if (qty > 0)
{
inPorductWorkplace.RECQTY += qty;
}
ctx.SaveChanges();
#endregion
}
#endregion
oHeader.CONFIRM_TRF = this.IsConfirmedTransaction;
oHeader.CONFIRM_TRF_LASTUPDATE = DateTime.Now;
oHeader.CONFIRM_TRF_LASTUSER = ConfigHelper.CurrentUserId;
oHeader.ModifiedBy = ConfigHelper.CurrentUserId;
oHeader.ModifiedOn = DateTime.Now;
ctx.SaveChanges();
this.TxferId = oHeader.HeaderId;
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}
private void CreateADJTx(ListViewItem listItem)
{
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
Guid headerId = Guid.Empty;
if (Guid.TryParse(listItem.Text, out headerId))
{
var oBatchHeader = ctx.InvtBatchADJ_Header.Find(headerId);
if (oBatchHeader != null)
{
// Update Product Info
#region UpdateProduct(oBatchHeader.HeaderId, oBatchHeader.WorkplaceId);
Guid txHeaderId = oBatchHeader.HeaderId;
Guid workplaceId = oBatchHeader.WorkplaceId;
var detailsList = ctx.InvtBatchADJ_Details.Where(x => x.HeaderId == txHeaderId);
foreach (var item in detailsList)
{
Guid productId = item.ProductId;
decimal qty = item.Qty.Value;
#region UpdateProductCurrentSummary(item.ProductId, item.Qty.Value);
var currProd = ctx.ProductCurrentSummary.Where(x => x.ProductId == productId).FirstOrDefault();
if (currProd == null)
{
currProd = new EF6.ProductCurrentSummary();
currProd.CurrentSummaryId = Guid.NewGuid();
currProd.ProductId = productId;
ctx.ProductCurrentSummary.Add(currProd);
}
currProd.CDQTY += qty;
#endregion
#region UpdateProductQty(item.ProductId, workplaceId, item.Qty.Value);
var wpProd = ctx.ProductWorkplace.Where(x => x.ProductId == productId && x.WorkplaceId == workplaceId).FirstOrDefault();
if (wpProd == null)
{
wpProd = new EF6.ProductWorkplace();
wpProd.ProductWorkplaceId = Guid.NewGuid();
wpProd.ProductId = productId;
wpProd.WorkplaceId = workplaceId;
ctx.ProductWorkplace.Add(wpProd);
}
wpProd.CDQTY += qty;
#endregion
ctx.SaveChanges();
}
#endregion
// Create ADJ SubLedger
string txNumber_SubLedger = oBatchHeader.TxNumber;
#region Guid subLedgerHeaderId = CreateADJSubLedgerHeader(txNumber_SubLedger, oBatchHeader.TxDate, oBatchHeader.WorkplaceId, oBatchHeader.StaffId, oBatchHeader.Remarks, oBatchHeader.Reference + "\t" + oBatchHeader.TxNumber);
string txnumber = txNumber_SubLedger;
DateTime txDate = oBatchHeader.TxDate.Value;
//Guid workplaceId = oBatchHeader.WorkplaceId;
Guid staffId = oBatchHeader.StaffId;
string remarks = oBatchHeader.Remarks;
string reference = oBatchHeader.Reference + "\t" + oBatchHeader.TxNumber;
var oSubADJ = new EF6.InvtSubLedgerADJ_Header();
oSubADJ.HeaderId = Guid.NewGuid();
oSubADJ.TxNumber = txnumber;
oSubADJ.TxType = EnumHelper.TxType.ADJ.ToString();
oSubADJ.WorkplaceId = workplaceId;
oSubADJ.TxDate = txDate;
oSubADJ.Reference = reference;
oSubADJ.Remarks = remarks;
oSubADJ.StaffId = staffId;
oSubADJ.Status = Convert.ToInt32(EnumHelper.Status.Active.ToString("d"));
oSubADJ.CreatedBy = ConfigHelper.CurrentUserId;
oSubADJ.CreatedOn = DateTime.Now;
oSubADJ.ModifiedBy = ConfigHelper.CurrentUserId;
oSubADJ.ModifiedOn = DateTime.Now;
ctx.InvtSubLedgerADJ_Header.Add(oSubADJ);
ctx.SaveChanges();
Guid subLedgerHeaderId = oSubADJ.HeaderId;
#endregion
#region CreateADJSubLedgerDetail(txNumber_SubLedger, oBatchHeader.HeaderId, subLedgerHeaderId);
//string txnumber = txNumber_SubLedger;
Guid batchHeaderId = oBatchHeader.HeaderId;
decimal ttlAmt = 0;
string sql = "HeaderId = '" + batchHeaderId.ToString() + "'";
string[] orderBy = new string[] { "LineNumber" };
var oBatchDetails = ctx.InvtBatchADJ_Details.Where(x => x.HeaderId == batchHeaderId).OrderBy(x => x.LineNumber);
foreach (var oBDetail in oBatchDetails)
{
var oSubLedgerDetail = new EF6.InvtSubLedgerADJ_Details();
oSubLedgerDetail.DetailsId = Guid.NewGuid();
oSubLedgerDetail.HeaderId = subLedgerHeaderId;
oSubLedgerDetail.LineNumber = oBDetail.LineNumber.Value;
oSubLedgerDetail.ProductId = oBDetail.ProductId;
oSubLedgerDetail.Qty = oBDetail.Qty.Value;
oSubLedgerDetail.TxNumber = txnumber;
oSubLedgerDetail.TxType = EnumHelper.TxType.ADJ.ToString();
oSubLedgerDetail.AverageCost = oBDetail.AverageCost.Value;
oSubLedgerDetail.ReasonCode = oBDetail.ReasonCode;
oSubLedgerDetail.Remarks = oBDetail.Remarks;
ctx.InvtSubLedgerADJ_Details.Add(oSubLedgerDetail);
ttlAmt += oSubLedgerDetail.Qty.Value * oSubLedgerDetail.AverageCost.Value;
}
var oSubLedgerHeader = ctx.InvtSubLedgerADJ_Header.Find(subLedgerHeaderId);
if (oSubLedgerHeader != null)
{
oSubLedgerHeader.TotalAmount = ttlAmt;
oSubLedgerHeader.ModifiedOn = DateTime.Now;
oSubLedgerHeader.ModifiedBy = ConfigHelper.CurrentUserId;
}
ctx.SaveChanges();
#endregion
// Create Ledger for TxType 'ADJ'
string txNumber_Ledger = oBatchHeader.TxNumber;
#region Guid ledgerHeaderId = CreateLedgerHeader(txNumber_Ledger, oBatchHeader.TxDate, subLedgerHeaderId, oBatchHeader.WorkplaceId, oBatchHeader.StaffId, oBatchHeader.Reference + "\t" + txNumber_SubLedger, oBatchHeader.Remarks);
//string txnumber = txNumber_Ledger;
//DateTime txDate = oBatchHeader.TxDate.Value;
//Guid subLedgerHeaderId = subLedgerHeaderId;
//Guid workplaceId = oBatchHeader.WorkplaceId;
//Guid staffId = oBatchHeader.StaffId;
//string reference = oBatchHeader.Reference + "\t" + txNumber_SubLedger;
//string remarks = oBatchHeader.Remarks;
var oLedgerHeader = new EF6.InvtLedgerHeader();
oLedgerHeader.HeaderId = Guid.NewGuid();
oLedgerHeader.TxNumber = txnumber;
oLedgerHeader.TxType = EnumHelper.TxType.ADJ.ToString();
oLedgerHeader.TxDate = txDate;
oLedgerHeader.SubLedgerHeaderId = subLedgerHeaderId;
oLedgerHeader.WorkplaceId = workplaceId;
oLedgerHeader.StaffId = staffId;
oLedgerHeader.Reference = reference;
oLedgerHeader.Remarks = remarks;
oLedgerHeader.Status = Convert.ToInt32(EnumHelper.Status.Active.ToString("d"));
oLedgerHeader.CreatedBy = ConfigHelper.CurrentUserId;
oLedgerHeader.CreatedOn = DateTime.Now;
oLedgerHeader.ModifiedBy = ConfigHelper.CurrentUserId;
oLedgerHeader.ModifiedOn = DateTime.Now;
ctx.InvtLedgerHeader.Add(oLedgerHeader);
ctx.SaveChanges();
Guid ledgerHeaderId = oLedgerHeader.HeaderId;
#endregion
#region CreateLedgerDetails(txNumber_Ledger, subLedgerHeaderId, ledgerHeaderId, oBatchHeader);
//string txnumber = txNumber_Ledger;
//Guid subledgerHeaderId = subLedgerHeaderId;
//Guid ledgerHeaderId = ledgerHeaderId;
//InvtBatchADJ_Header oBatchHeader
//string sql = "HeaderId = '" + subledgerHeaderId.ToString() + "'";
//string[] orderBy = new string[] { "LineNumber" };
var oSubLedgerDetails = ctx.InvtSubLedgerADJ_Details.Where(x => x.HeaderId == subLedgerHeaderId).OrderBy(x => x.LineNumber);
foreach (var oSDetail in oSubLedgerDetails)
{
var oLedgerDetail = new EF6.InvtLedgerDetails();
oLedgerDetail.DetailsId = Guid.NewGuid();
oLedgerDetail.HeaderId = ledgerHeaderId;
oLedgerDetail.SubLedgerDetailsId = oSDetail.DetailsId;
oLedgerDetail.LineNumber = oSDetail.LineNumber.Value;
oLedgerDetail.ProductId = oSDetail.ProductId;
oLedgerDetail.Qty = oSDetail.Qty.Value;
oLedgerDetail.TxNumber = txnumber;
oLedgerDetail.TxType = EnumHelper.TxType.ADJ.ToString();
oLedgerDetail.TxDate = oBatchHeader.TxDate.Value;
oLedgerDetail.Amount = oLedgerDetail.Qty * oLedgerDetail.AverageCost;
oLedgerDetail.Notes = string.Empty;
oLedgerDetail.SerialNumber = string.Empty;
oLedgerDetail.SHOP = WorkplaceEx.GetWorkplaceCodeById(oBatchHeader.WorkplaceId);
oLedgerDetail.OPERATOR = StaffEx.GetStaffNumberById(oBatchHeader.StaffId);
// Product Info
var oItem = ctx.Product.Find(oSDetail.ProductId);
if (oItem != null)
{
oLedgerDetail.BasicPrice = oItem.RetailPrice.Value;
oLedgerDetail.UnitAmount = ProductCurrentSummaryEx.GetAverageCode(oItem.ProductId);
oLedgerDetail.Discount = oItem.NormalDiscount;
oLedgerDetail.Amount = oLedgerDetail.UnitAmount * oLedgerDetail.Qty;
oLedgerDetail.AverageCost = ProductCurrentSummaryEx.GetAverageCode(oItem.ProductId);
var priceTypeId = ProductPriceTypeEx.GetIdByPriceType(ProductHelper.Prices.VPRC.ToString());
//sql = "ProductId = '" + oSDetail.ProductId.ToString() + "' AND PriceTypeId = '" + priceTypeId.ToString() + "'";
var oPrice = ctx.ProductPrice.Where(x => x.ProductId == oSDetail.ProductId && x.PriceTypeId == priceTypeId).FirstOrDefault();
if (oPrice != null)
{
oLedgerDetail.VendorRef = oPrice.CurrencyCode;
}
}
ctx.InvtLedgerDetails.Add(oLedgerDetail);
var oLedgerHeader2 = ctx.InvtLedgerHeader.Find(ledgerHeaderId);
if (oLedgerHeader2 != null)
{
oLedgerHeader2.TotalAmount += oLedgerDetail.Amount;
}
ctx.SaveChanges();
}
#endregion
oBatchHeader.PostedBy = ConfigHelper.CurrentUserId;
oBatchHeader.PostedOn = DateTime.Now;
oBatchHeader.ModifiedBy = ConfigHelper.CurrentUserId;
oBatchHeader.ModifiedOn = DateTime.Now;
ctx.SaveChanges();
#region ClearBatchTransaction(oBatchHeader);
string query = "HeaderId = '" + oBatchHeader.HeaderId.ToString() + "'";
var detailList = ctx.InvtBatchADJ_Details.Where(x => x.HeaderId == oBatchHeader.HeaderId);
foreach (var detail in detailList)
{
ctx.InvtBatchADJ_Details.Remove(detail);
}
ctx.InvtBatchADJ_Header.Remove(oBatchHeader);
ctx.SaveChanges();
#endregion
}
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}