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