private void CreateStockTakeHHTDetails(Guid hhtHeaderId, string txNumber, string hhtId, DateTime uploadedOn, List <ImportDetailsInfo> detailList, string hhtTxNumber)
{
int iCount = 0;
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
foreach (ImportDetailsInfo detail in detailList)
{
Guid productId = ProductBarcodeEx.GetProductIdByBarcode(detail.Barcode);
if (!string.IsNullOrEmpty(detail.Barcode.Trim()) && productId != System.Guid.Empty)
{
#region update dbo.StockTakeDetails_HHT
//string sql = "HeaderId = '" + hhtHeaderId.ToString() + "' AND TxNumber = '" + txNumber + "' AND ProductId = '" + productId.ToString() + "'";
var hhtDetail = ctx.StockTakeDetails_HHT
.Where(x => x.HeaderId == hhtHeaderId && x.TxNumber == txNumber && x.ProductId == productId)
.FirstOrDefault();
if (hhtDetail == null)
{
hhtDetail = new EF6.StockTakeDetails_HHT();
hhtDetail.DetailsId = Guid.NewGuid();
hhtDetail.HeaderId = hhtHeaderId;
hhtDetail.TxNumber = txNumber;
hhtDetail.LineNumber = iCount++;
hhtDetail.UploadedOn = uploadedOn;
hhtDetail.ProductId = productId;
hhtDetail.Barcode = detail.Barcode;
ctx.StockTakeDetails_HHT.Add(hhtDetail);
}
hhtDetail.Qty = detail.Qty;
hhtDetail.Remarks = hhtTxNumber;
ctx.SaveChanges();
#endregion
}
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}
private void CreatedStockTakeDetail(Guid stktkHeaderId, string txNumber, List <ImportDetailsInfo> detailList, Guid workplaceId, DateTime uploadedOn)
{
using (var ctx = new EF6.RT2020Entities())
{
using (var scope = ctx.Database.BeginTransaction())
{
try
{
foreach (ImportDetailsInfo detail in detailList)
{
Guid productId = ProductBarcodeEx.GetProductIdByBarcode(detail.Barcode);
if (!string.IsNullOrEmpty(detail.Barcode.Trim()) && productId != Guid.Empty)
{
//string sql = "HeaderId = '" + stktkHeaderId.ToString() + "' AND TxNumber = '" + txNumber + "' AND ProductId = '" + productId.ToString() + "' AND WorkplaceId = '" + workplaceId.ToString() + "'";
var item = ctx.StockTakeDetails.Where(x => x.HeaderId == stktkHeaderId && x.TxNumber == txNumber && x.ProductId == productId && x.WorkplaceId == workplaceId).FirstOrDefault();
if (item == null)
{
item = new EF6.StockTakeDetails();
item.DetailsId = Guid.NewGuid();
item.HeaderId = stktkHeaderId;
item.TxNumber = txNumber;
item.ProductId = productId;
item.WorkplaceId = workplaceId;
item.ModifiedOn = uploadedOn;
item.ModifiedBy = ConfigHelper.CurrentUserId;
ctx.StockTakeDetails.Add(item);
}
ctx.SaveChanges();
}
}
scope.Commit();
}
catch (Exception ex)
{
scope.Rollback();
}
}
}
}
// Verify the data from excel file exists or not
private string VerifyDuplicatedData(int iCount, DataRow row)
{
iCount = iCount + 1;
StringBuilder returnResult = new StringBuilder();
StringBuilder verifiedResult = new StringBuilder();
verifiedResult.Append("Row#").Append(iCount.ToString()).Append(":");
int iLen = verifiedResult.ToString().Length;
// Check whether the StockCode is duplicate or not
//string sql = @"STKCODE = '" + Utility.VerifyQuotes(row[colSTKCode].ToString().Trim()) + "' AND APPENDIX1 = '" + Utility.VerifyQuotes(row[colAppendix1].ToString().Trim())
// + "' AND APPENDIX2 = '" + Utility.VerifyQuotes(row[colAppendix2].ToString().Trim()) + "' AND APPENDIX3 = '" + Utility.VerifyQuotes(row[colAppendix3].ToString().Trim()) + "'";
//RT2020.DAL.Product oStockItem = RT2020.DAL.Product.LoadWhere(sql);
//if (oStockItem != null)
if (ProductHelper.IsDuplicated(
Utility.VerifyQuotes(row[colSTKCode].ToString().Trim()),
Utility.VerifyQuotes(row[colAppendix1].ToString().Trim()),
Utility.VerifyQuotes(row[colAppendix2].ToString().Trim()),
Utility.VerifyQuotes(row[colAppendix3].ToString().Trim())))
{
returnResult.Append("Duplicated Stock Code;");
}
// Appendix1
//sql = "Appendix1Code = '" + Utility.VerifyQuotes(row[colAppendix1].ToString().Trim()) + "'";
//ProductAppendix1 oApp1 = ProductAppendix1.LoadWhere(sql);
//if (oApp1 != null)
if (ProductAppendix1Ex.IsAppendixCodeInUse(Utility.VerifyQuotes(row[colAppendix3].ToString())))
{
verifiedResult.Append(colAppendix1).Append(";");
}
// Appendix2
//sql = "Appednix2Code = '" + Utility.VerifyQuotes(row[colAppendix2].ToString().Trim()) + "'";
//ProductAppendix2 oApp2 = ProductAppendix2.LoadWhere(sql);
//if (oApp2 != null)
if (ProductAppendix2Ex.IsAppendixCodeInUse(Utility.VerifyQuotes(row[colAppendix3].ToString())))
{
verifiedResult.Append(colAppendix2).Append(";");
}
// Appendix3
//sql = "Appendix3Code = '" + Utility.VerifyQuotes(row[colAppendix3].ToString().Trim()) + "'";
//ProductAppendix3 oApp3 = ProductAppendix3.LoadWhere(sql);
//if (oApp3 != null)
if (ProductAppendix3Ex.IsAppendixCodeInUse(Utility.VerifyQuotes(row[colAppendix3].ToString())))
{
verifiedResult.Append(colAppendix3).Append(";");
}
// Class1
//sql = "Class1Code = '" + Utility.VerifyQuotes(row[colClass1].ToString().Trim()) + "'";
//ProductClass1 oCls1 = ProductClass1.LoadWhere(sql);
//if (oCls1 != null)
if (ProductClass1Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass1].ToString().Trim())))
{
verifiedResult.Append(colClass1).Append(";");
}
// Class2
//sql = "Class2Code = '" + Utility.VerifyQuotes(row[colClass2].ToString().Trim()) + "'";
//ProductClass2 oCls2 = ProductClass2.LoadWhere(sql);
//if (oCls2 != null)
if (ProductClass2Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass2].ToString().Trim())))
{
verifiedResult.Append(colClass2).Append(";");
}
// Class3
//sql = "Class3Code = '" + Utility.VerifyQuotes(row[colClass3].ToString().Trim()) + "'";
//ProductClass3 oCls3 = ProductClass3.LoadWhere(sql);
//if (oCls3 != null)
if (ProductClass3Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass3].ToString().Trim())))
{
verifiedResult.Append(colClass3).Append(";");
}
// Class4
//sql = "Class4Code = '" + Utility.VerifyQuotes(row[colClass4].ToString().Trim()) + "'";
//ProductClass4 oCls4 = ProductClass4.LoadWhere(sql);
//if (oCls4 != null)
if (ProductClass4Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass4].ToString().Trim())))
{
verifiedResult.Append(colClass4).Append(";");
}
// Class5
//sql = "Class5Code = '" + Utility.VerifyQuotes(row[colClass5].ToString().Trim()) + "'";
//ProductClass5 oCls5 = ProductClass5.LoadWhere(sql);
//if (oCls5 != null)
if (ProductClass5Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass5].ToString().Trim())))
{
verifiedResult.Append(colClass5).Append(";");
}
// Class6
//sql = "Class6Code = '" + Utility.VerifyQuotes(row[colClass6].ToString().Trim()) + "'";
//ProductClass6 oCls6 = ProductClass6.LoadWhere(sql);
//if (oCls6 != null)
if (ProductClass6Ex.IsClassCodeInUse(Utility.VerifyQuotes(row[colClass6].ToString().Trim())))
{
verifiedResult.Append(colClass6).Append(";");
}
// Barcode
var barcode = Utility.VerifyQuotes(row[colSTKCode].ToString().Trim()) + Utility.VerifyQuotes(row[colAppendix1].ToString().Trim()) + Utility.VerifyQuotes(row[colAppendix2].ToString().Trim()) + Utility.VerifyQuotes(row[colAppendix3].ToString().Trim());
var sql = "BARCODE = '" + barcode + "'";
if (ProductBarcodeEx.IsBarcodeInUse(barcode))
{
returnResult.Append("Duplicated Barcode;");
}
if (verifiedResult.ToString().Length > iLen)
{
duplicatedRec.Add(verifiedResult.ToString());
}
return(returnResult.ToString());
}
private void Save()
{
int iCount = 1;
bool isValid = true;
foreach (ListViewItem lvItem in lvPPCPacketList.Items)
{
if (lvItem.Checked)
{
string stktkNumber = string.Empty;
Guid workplaceId = Guid.Empty;
DateTime uploadedOn = DateTime.Now;
List <ImportDetailsInfo> detailAllList = new List <ImportDetailsInfo>();
decimal totalLine = 0, totalQty = 0, missingLine = 0, missingQty = 0;
this.CheckHHTLog(lvItem.SubItems[4].Text);
#region Load Header's detail info
FileHelperEngine <ImportHeaderInfo> headerInfoEngine = new FileHelperEngine <ImportHeaderInfo>();
headerInfoEngine.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;
ImportHeaderInfo[] headerInfoList = headerInfoEngine.ReadFile(lvItem.SubItems[8].Text);
if (headerInfoEngine.ErrorManager.ErrorCount > 0)
{
headerInfoEngine.ErrorManager.SaveErrors(logFile);
}
#endregion
Utility.WriteLog("Date Create : "+ uploadedOn.ToString("dd/MM/yyyy HH:mm:ss"), logFile);
Utility.WriteLog("Session ID : "+ uploadedOn.ToString("yyyyMMdd-HHmmss") + "-" + iCount.ToString().PadLeft(3, '0'), logFile);
Utility.WriteLog("Upload Time : "+ uploadedOn.ToString("dd/MM/yyyy HH:mm:ss"), logFile);
Utility.WriteLog("HHT TRN# : "+ lvItem.SubItems[2].Text, logFile);
Utility.WriteLog("Location# : " + lvItem.SubItems[9].Text + " [Original]; " + lvItem.Text + " [Current]", logFile);
Utility.WriteLog("Stock Take# : "+ lvItem.SubItems[10].Text + " [Suggested]; " + lvItem.SubItems[1].Text + " [Current]", logFile);
Utility.WriteLog("Process Detail : Import Data", logFile);
Utility.WriteLog("Message :- ", logFile);
Utility.WriteLog("=> Checking Loc# ", logFile);
#region Check Workplace (Loc#)
using (var ctx = new EF6.RT2020Entities())
{
var wp = ctx.Workplace.Where(x => x.WorkplaceCode == lvItem.Text).AsNoTracking().FirstOrDefault();
if (wp != null)
{
if (wp.Retired)
{
Utility.WriteLog(" [ERROR] Loc# was retired ", logFile);
isValid = isValid & false;
}
else
{
Utility.WriteLog(" [OK] ", logFile);
workplaceId = wp.WorkplaceId;
}
}
else
{
Utility.WriteLog(" [ERROR] Loc# Not Found", logFile);
isValid = isValid & false;
}
}
#endregion
Utility.WriteLog(" RESULT : COMPLETED", logFile);
Utility.WriteLog("=> Import Packet File ", logFile);
#region Load details files
string[] packetFiles = Directory.GetFiles(tempDirectory, "DT_" + lvItem.SubItems[2].Text + "*", SearchOption.TopDirectoryOnly);
for (int i = 0; i < packetFiles.Length; i++)
{
Utility.WriteLog(@" " + (i + 1).ToString() + @") Packet => " + Path.GetFileNameWithoutExtension(packetFiles[i]) + " [" + packetFiles[i] + "] ", logFile);
}
#endregion
Utility.WriteLog(" RESULT : COMPLETED", logFile);
Utility.WriteLog("=> Checking (Header) ", logFile);
stktkNumber = lvItem.SubItems[1].Text.Trim();
#region checking Header info
using (var ctx = new EF6.RT2020Entities())
{
var stktkHeader = ctx.StockTakeHeader.Where(x => x.TxNumber == lvItem.SubItems[1].Text.Trim()).FirstOrDefault();
if (stktkHeader != null)
{
if (!string.IsNullOrEmpty(stktkHeader.ADJNUM))
{
Utility.WriteLog(" [ERROR] The Stock Take Number was posted, cannot be used anymore. ", logFile);
isValid = isValid & false;
}
else if (!WorkplaceEx.GetWorkplaceCodeById(stktkHeader.WorkplaceId.Value).Equals(lvItem.Text.Trim()))
{
Utility.WriteLog(" [ERROR] The loc# in Stock Take Header must be as same as the selected one. ", logFile);
isValid = isValid & false;
}
else
{
//? Why compare the UploadedOn down to "seconds"
//string sql = "TxNumber = '" + lvItem.SubItems[1].Text.Trim() + "' AND HHTId = '" + lvItem.SubItems[4].Text + "' AND CONVERT(NVARCHAR(20), UploadedOn, 120) = '" + uploadedOn.ToString("yyyy-MM-dd HH:mm:ss") + "'";
var txNumber = lvItem.SubItems[1].Text.Trim();
var hhtId = lvItem.SubItems[4].Text;
var hhtHeader = ctx.StocktakeHeader_HHT.Where(x => x.TxNumber == txNumber && x.HHTId == hhtId && x.UploadedOn.Value.ToString("yyyy-MM-dd HH:mm:ss") == uploadedOn.ToString("yyyy-MM-dd HH:mm:ss")).FirstOrDefault();
if (hhtHeader != null)
{
if (hhtHeader.PostedOn.Value.Year > 1900)
{
Utility.WriteLog(" [ERROR] The Stock Take (HHT) Number was posted, cannot be used anymore. ", logFile);
isValid = isValid & false;
}
else
{
Utility.WriteLog(" [ERROR] The Stock Take (HHT) Number existed. ", logFile);
isValid = isValid & false;
}
}
else
{
Utility.WriteLog(" [OK] ", logFile);
}
}
}
else
{
Utility.WriteLog(" [OK] ", logFile);
}
}
#endregion
Utility.WriteLog("=> Checking (Detail) ", logFile);
int iCountBarcode = 0;
#region checking details info
for (int iHeader = 0; iHeader < headerInfoList.Length; iHeader++)
{
ImportHeaderInfo headerInfo = headerInfoList[iHeader];
FileHelperEngine <ImportDetailsInfo> detailInfoEngine = new FileHelperEngine <ImportDetailsInfo>();
detailInfoEngine.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;
string detailPacket = Path.Combine(tempDirectory, "DT_" + lvItem.SubItems[2].Text + "_" + headerInfo.ShelfId + ".TXT");
ImportDetailsInfo[] detailInfoList = detailInfoEngine.ReadFile(detailPacket);
if (headerInfoEngine.ErrorManager.ErrorCount > 0)
{
headerInfoEngine.ErrorManager.SaveErrors(logFile);
}
Utility.WriteLog(" => Checking Shelf ("+ headerInfo.ShelfId + " - " + headerInfo.ShelfName + ")", logFile);
for (int iDetail = 0; iDetail < detailInfoList.Length; iDetail++)
{
ImportDetailsInfo detailInfo = detailInfoList[iDetail];
if (string.IsNullOrEmpty(detailInfo.Barcode))
{
iCountBarcode++;
missingQty += detailInfo.Qty;
Utility.WriteLog(" [ERROR] Barcode does not exist. ", logFile);
}
else
{
Guid productId = ProductBarcodeEx.GetProductIdByBarcode(detailInfo.Barcode);
if (productId == System.Guid.Empty)
{
iCountBarcode++;
missingQty += detailInfo.Qty;
Utility.WriteLog(" [ERROR] Barcode ("+ detailInfo.Barcode + ") does not exist. ", logFile);
}
else
{
if (detailInfo.Qty <= 0)
{
Utility.WriteLog(" [ERROR] Barcode ("+ detailInfo.Barcode + ") QTY <= 0 ", logFile);
isValid = isValid & false;
}
else
{
Utility.WriteLog(" [OK] Barcode ("+ detailInfo.Barcode + ") QTY > 0 ", logFile);
}
}
}
totalLine++;
totalQty += detailInfo.Qty;
detailAllList.Add(detailInfo);
}
missingLine += iCountBarcode;
if (iCountBarcode > 0)
{
Utility.WriteLog(" [ERROR] Details of Shelf ("+ headerInfo.ShelfId + " - " + headerInfo.ShelfName + ") has " + iCountBarcode.ToString() + " empty barcode.", logFile);
}
else
{
Utility.WriteLog(" [OK] Details of Shelf ("+ headerInfo.ShelfId + " - " + headerInfo.ShelfName + ") has 0 empty barcode.", logFile);
}
}
#endregion
Utility.WriteLog(" RESULT : COMPLETED", logFile);
Utility.WriteLog("=> Save Packet", logFile);
#region isValid: wrapping up
if (isValid)
{
if (stktkNumber.Trim().Length == 0)
{
stktkNumber = SystemInfoHelper.Settings.QueuingTxNumber(EnumHelper.TxType.STK);
}
Utility.WriteLog(" [OK] System Queue ", logFile);
if (stktkNumber.Length > 0)
{
// Stock take header
System.Guid stktkheaderId = CreateStockTakeHeader(stktkNumber.Trim(), workplaceId);
Utility.WriteLog(" [OK] Create Worksheet (Stock Take - Header)", logFile);
// Stock take details
if (stktkheaderId != System.Guid.Empty)
{
CreatedStockTakeDetail(stktkheaderId, stktkNumber.Trim(), detailAllList, workplaceId, uploadedOn);
}
Utility.WriteLog(" [OK] Create Worksheet (Stock Take - Detail)", logFile);
// Stock take header (HHT)
System.Guid hhtHeaderId = CreateStockTakeHHTHeader(stktkNumber.Trim(), lvItem.SubItems[4].Text, uploadedOn, workplaceId, lvItem.SubItems[2].Text,
totalLine, totalQty, missingLine, missingQty);
Utility.WriteLog(" [OK] Create Worksheet (HHT Data Review - Header)", logFile);
// Stock take details (HHT)
if (hhtHeaderId != System.Guid.Empty)
{
CreateStockTakeHHTDetails(hhtHeaderId, stktkNumber.Trim(), lvItem.SubItems[4].Text, uploadedOn, detailAllList, lvItem.SubItems[2].Text);
}
Utility.WriteLog(" [OK] Create Worksheet (HHT Data Review - Detail)", logFile);
Utility.WriteLog(" [OK] Barcode Matching", logFile);
Utility.WriteLog(" [OK] Counting Missing Data", logFile);
Utility.WriteLog(" RESULT : COMPLETED", logFile);
// Backup text files
if (!Directory.Exists(backupDirectory))
{
Directory.CreateDirectory(backupDirectory);
}
// Header file
File.Move(lvItem.SubItems[8].Text, Path.Combine(backupDirectory, Path.GetFileName(lvItem.SubItems[8].Text)));
for (int i = 0; i < packetFiles.Length; i++)
{
File.Move(packetFiles[i], Path.Combine(backupDirectory, Path.GetFileName(packetFiles[i])));
}
Utility.WriteLog("=> Backup Data RESULT : COMPLETED", logFile);
}
}
#endregion
iCount++;
}
}
}
private void CheckBarcode()
{
int barcodeLen = 22;
bool isValid = false;
if (string.IsNullOrEmpty(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "Can not be blank!");
}
else
{
if (txtBarcode.Text.Length > barcodeLen)
{
errorProvider.SetError(txtBarcode, "Invalid Barcode Length!");
}
else if (cboBarcodeType.Text.Length == 0)
{
errorProvider.SetError(cboBarcodeType, "Cannot be blank!");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
errorProvider.SetError(cboBarcodeType, string.Empty);
isValid = true;
}
}
if (isValid)
{
string sql = "ProductId = '" + this.ProductId.ToString() + "' AND Barcode = '" + txtBarcode.Text.Trim() + "'";
//ProductBarcode oBarcode = ProductBarcode.LoadWhere(sql);
if (ProductBarcodeEx.IsBarcodeInUse(this.ProductId, txtBarcode.Text.Trim()))
{
errorProvider.SetError(txtBarcode, "Barcode Already Exists");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
}
if (string.IsNullOrEmpty(errorProvider.GetError(txtBarcode)))
{
//Verify type and check digit
switch (cboBarcodeType.SelectedItem.ToString())
{
case "INTERNAL (128B)": // INTERNAL (128B)
txtBarcode.Text = Utility.VerifyQuotes(txtBarcode.Text);
if (txtBarcode.Text.Length > 22)
{
errorProvider.SetError(txtBarcode, "The length of Barcode(INTERNAL (128B)) must be <= 22!");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
}
break;
case "EAN8": // EAN8 (GTIN-8)
if (txtBarcode.Text.Length != 8)
{
errorProvider.SetError(txtBarcode, "The length of Barcode(EAN8) must be 8!");
}
else if (!VerifyBarcodeNumber(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "Barcode(EAN8) must be Numeric Only!");
}
else if (!VerifyBarcodeCheckDigit(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "The Check Digit of Barcode(EAN8) is Not Valid!");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
}
break;
case "EAN13": // EAN13 (GTIN-13)
if (txtBarcode.Text.Length != 13)
{
errorProvider.SetError(txtBarcode, "The length of Barcode(EAN13) must be 13!");
}
else if (!VerifyBarcodeNumber(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "Barcode(EAN13) must be Numeric Only!");
}
else if (!VerifyBarcodeCheckDigit(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "The Check Digit of Barcode(EAN13) is Not Valid!");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
}
break;
case "UPC12": // UPC12 (GTIN-12)
if (txtBarcode.Text.Length != 12)
{
errorProvider.SetError(txtBarcode, "The length of Barcode(UPC12) must be 12!");
}
else if (!VerifyBarcodeNumber(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "Barcode(UPC12) must be Numeric Only!");
}
else if (!VerifyBarcodeCheckDigit(txtBarcode.Text))
{
errorProvider.SetError(txtBarcode, "The Check Digit of Barcode(UPC12) is Not Valid!");
}
else
{
errorProvider.SetError(txtBarcode, string.Empty);
}
break;
}
}
}
}
