public string GetProductTypeFromMasterSKUBarcode(string barcode, SettingBean setting) { SqlConnection conn = new SqlConnection(GetConnectionStringRequest(setting)); SqlDataAdapter da = new SqlDataAdapter(); string ProductType = ""; DataSet dsProdType = new DataSet(); using (SqlConnection con = new SqlConnection(GetConnectionStringRequest(setting))) { try { string cmdGetProdType = "SELECT TOP 1 ProductType FROM MasterSKU WHERE MKCode = '" + barcode + "'"; da = new SqlDataAdapter(cmdGetProdType, conn); da.Fill(dsProdType); //DateTime countDate = (DateTime)dtCountDate.Rows[0][0]; ProductType = (string)dsProdType.Tables[0].Rows[0][0]; } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), ex.Message); } } return(ProductType); }
public bool IsStocktakingIDExist(string stocktakingID, SettingBean setting) { SqlConnection conn = new SqlConnection(GetConnectionStringRequest(setting)); SqlDataAdapter da = new SqlDataAdapter(); DataTable dtStocktakingID = new DataTable(); string cmd = "SELECT * FROM tmpHHTStocktaking WHERE StocktakingID = '" + stocktakingID + "'"; try { da = new SqlDataAdapter(cmd, conn); da.Fill(dtStocktakingID); } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), ex.Message); } int countRow = dtStocktakingID.Rows.Count; if (countRow > 0) { return(true); } else { return(false); } }
public static String GetConnectionStringRequest(SettingBean setting) { StringBuilder connectionStr = new StringBuilder(); connectionStr.Append("Data Source=" + setting.dataSourceRequest + ";"); connectionStr.Append("Initial catalog=" + setting.databaseRequest + ";"); if (setting.isWindownAuthen.Equals("0")) { connectionStr.Append("User ID=" + setting.userIdRequest + ";"); connectionStr.Append("Password="******";"); } else { connectionStr.Append("Integrated Security=True;"); connectionStr.Append("MultipleActiveResultSets=True;"); } return(connectionStr.ToString()); }
public static Boolean CheckSettingFile(SettingBean settingBean) { Boolean chk = true; if (String.IsNullOrWhiteSpace(settingBean.sourcePath)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.dataSourceRequest)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.databaseRequest)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.userIdRequest)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.passwordRequest)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.logPath)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.logFileName)) { chk = false; } if (String.IsNullOrWhiteSpace(settingBean.timeout)) { chk = false; } return(chk); }
public static SettingBean SetSettingBean() { Console.WriteLine("Enter to SetSettingBean"); DateTime localDate = DateTime.Now; SettingBean settingBean = new SettingBean(); String configFilePath = AppDomain.CurrentDomain.BaseDirectory + @"setting.ini"; InitFile init = new InitFile(configFilePath); settingBean.sourcePath = init.InitReadValue("setting", "SourcePath"); settingBean.dataSourceRequest = init.InitReadValue("database connection", "DataSourceRequest"); settingBean.databaseRequest = init.InitReadValue("database connection", "DbNameRequest"); settingBean.userIdRequest = init.InitReadValue("database connection", "UserIDRequest"); settingBean.passwordRequest = init.InitReadValue("database connection", "PasswordRequest"); settingBean.logPath = init.InitReadValue("Logfile", "Logpath"); settingBean.logFileName = init.InitReadValue("Logfile", "LogName") + localDate.ToString("yyyyMM") + ".log"; settingBean.timeout = init.InitReadValue("setting", "TimeOut"); settingBean.sleeptime = Convert.ToInt32(init.InitReadValue("setting", "SleepTime")) * 1000; settingBean.isWindownAuthen = init.InitReadValue("database connection", "IsWindownAuthen"); LogFile logfile = new LogFile(settingBean.logPath, settingBean.logFileName); return(settingBean); }
static void Main(string[] args) { SettingBean setting = InitialSetting.SetSettingBean(); LogFile.write(Status.INFO.ToString(), "Start Process"); Boolean chkSettings = InitialSetting.CheckSettingFile(setting); if (chkSettings) { Management manage = new Management(); Database db = new Database(); DataTable dtFile = new DataTable(); dtFile.Columns.Add("PathFileName", typeof(string)); dtFile.Columns.Add("DateModify", typeof(DateTime)); dtFile.Columns.Add("FileName", typeof(string)); dtFile.Columns.Add("FileType", typeof(string)); List <string> filePathError = new List <string>(); if (Directory.Exists(setting.sourcePath)) { string directoryPath = setting.sourcePath; deleteFileBin(directoryPath); } while (true) { Thread.Sleep(Convert.ToInt32(setting.sleeptime)); if (Directory.Exists(setting.sourcePath)) { if (!(Directory.Exists(setting.sourcePath + "\\tmp"))) { Directory.CreateDirectory(setting.sourcePath + "\\tmp"); } string directoryPath = setting.sourcePath; dtFile.Clear(); string[] zipFiles = Directory.GetFiles(directoryPath); List <string> listZipFiles = new List <string>(); //listZipFiles = zipFiles.ToList(); listZipFiles = zipFiles.Where(all => !filePathError.Any(except => except == all)).ToList(); //listZipFiles = listZipFiles.Where(all => !all.ToUpper().Contains("TEMP")).ToList(); if (listZipFiles.Count() > 0) { foreach (string fileZipPath in listZipFiles) { DateTime lastModified = System.IO.File.GetLastWriteTime(fileZipPath); string[] splitName = fileZipPath.Split('.'); string lastSplit = splitName[0]; string extension = Path.GetExtension(fileZipPath); DataRow drFile = dtFile.NewRow(); drFile[0] = fileZipPath; drFile[1] = lastModified; drFile[2] = lastSplit; drFile[3] = extension; dtFile.Rows.Add(drFile); } List <string> tmpFileName = new List <string>(); tmpFileName = (from dt in dtFile.AsEnumerable() group dt by dt[2] into grp where grp.Count() == 1 select grp.Key.ToString() ).ToList(); if (tmpFileName.Count > 0) { dtFile = dtFile.AsEnumerable().Where(all => tmpFileName.Any(except => except.Equals(all["FileName"]))).CopyToDataTable(); if (dtFile.AsEnumerable().Where(all => !all["FileType"].Equals("")).Count() > 0) { dtFile = dtFile.AsEnumerable().Where(all => !all["FileType"].Equals("")).CopyToDataTable(); } else { dtFile.Clear(); } //dtFile = manage.LINQResultToDataTable(dtTemp); } else { dtFile.Clear(); } if (dtFile.Rows.Count > 0) { string FileNameModifyFirst = (from dt in dtFile.AsEnumerable() select dt[0].ToString()).OrderBy(x => x[1]).FirstOrDefault(); try { String extensionZip = Path.GetExtension(FileNameModifyFirst); if (extensionZip.Equals(".zip")) { if (File.Exists(FileNameModifyFirst)) { string filePath = directoryPath + "\\tmp\\Record.txt"; File.Delete(filePath); ZipFile.ExtractToDirectory(FileNameModifyFirst, directoryPath + "\\tmp"); String extensionTxt = Path.GetExtension(filePath); if (extensionTxt.Equals(".txt")) { if (File.Exists(filePath)) { Hashtable hasResult = new Hashtable(); hasResult = manage.ReadFile(filePath, filePathError, FileNameModifyFirst, setting); ImportModel importData = (ImportModel)hasResult["importData"]; filePathError = (List <string>)hasResult["filePathError"]; int countRow = (int)hasResult["countRow"]; string[] splitName1 = FileNameModifyFirst.Split('_'); string lastSplit1 = splitName1[splitName1.Length - 1]; string[] splitName2 = lastSplit1.Split('.'); string lastSplit2 = splitName2[0]; int countRealFile; if (Int32.TryParse(lastSplit2, out countRealFile)) { if (countRealFile == (countRow - 3)) { if (importData.RecordData.Count > 0) { bool importSuccess = db.ImportRecord(importData.RecordData, importData.HHTID, importData.DeviceName, importData.Mode, FileNameModifyFirst, setting); if (importSuccess) { LogFile.write(Status.INFO.ToString(), "Success File " + FileNameModifyFirst); File.Delete(FileNameModifyFirst); File.Delete(filePath); } } } else { File.Delete(filePath); filePathError.Add(FileNameModifyFirst); LogFile.write(Status.ERROR.ToString(), FileNameModifyFirst + " is incorrect data"); //File.Delete(FileNameModifyFirst); } } else { File.Delete(filePath); filePathError.Add(FileNameModifyFirst); LogFile.write(Status.ERROR.ToString(), filePath + " is string could not be parsed."); //File.Delete(FileNameModifyFirst); } } } else { File.Delete(filePath); filePathError.Add(FileNameModifyFirst); LogFile.write(Status.ERROR.ToString(), filePath + " is not TXT. "); //File.Delete(FileNameModifyFirst); } } } else { filePathError.Add(FileNameModifyFirst); LogFile.write(Status.ERROR.ToString(), FileNameModifyFirst + " is not zip. "); //File.Delete(FileNameModifyFirst); } } catch (Exception ex) { filePathError.Add(FileNameModifyFirst); LogFile.write(Status.ERROR.ToString(), FileNameModifyFirst + " : " + ex.Message); } } } } } } else { LogFile.write(Status.ERROR.ToString(), "Initial Setting is not Correctly."); } LogFile.write(Status.INFO.ToString(), "End Process"); }
public bool ImportRecord(List <AuditStocktakingModel> recordsList, string hhtID, string deviceName, string mode, string fileName, SettingBean setting) { SqlConnection conn = new SqlConnection(GetConnectionStringRequest(setting)); SqlDataAdapter da = new SqlDataAdapter(); DataSet dsCountDate = new DataSet(); CultureInfo defaulCulture = new CultureInfo("en-US"); DateTime countDate = new DateTime(); DateTime currentDate = DateTime.Now; using (SqlConnection con = new SqlConnection(GetConnectionStringRequest(setting))) { SqlCommand ObjCommand = con.CreateCommand(); ObjCommand.CommandType = CommandType.Text; SqlTransaction trans; con.Open(); trans = con.BeginTransaction(); ObjCommand.Transaction = trans; try { string cmdGetCountDate = "SELECT TOP 1 ValueDate FROM SystemSettings WHERE SettingKey = 'CountDate'"; da = new SqlDataAdapter(cmdGetCountDate, conn); da.Fill(dsCountDate); //DateTime countDate = (DateTime)dtCountDate.Rows[0][0]; countDate = (DateTime)dsCountDate.Tables[0].Rows[0][0]; } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), ex.Message); } try { List <string> duplicateList = new List <string>(); foreach (AuditStocktakingModel record in recordsList) { bool stocktakingIDExist = this.IsStocktakingIDExist(record.StockTakingID, setting); if (stocktakingIDExist) { duplicateList.Add(record.StockTakingID); } } // upload all if (mode == "1") { //do nothing } else //upload only different if (mode == "2") { if (duplicateList.Count > 0) { recordsList = recordsList.Where(p => !duplicateList.Any(d => d == p.StockTakingID)).ToList(); } } foreach (var auditData in recordsList) { int ScanMode = 0; //DataTable dtScanMode = new DataTable(); //string cmdGetLocation = "SELECT TOP 1 ScanMode FROM Location WHERE LocationCode = '" + auditData.LocationCode + "'"; //try //{ // da = new SqlDataAdapter(cmdGetLocation, conn); // da.Fill(dtScanMode); //} //catch (Exception ex) //{ // LogFile.write(Status.ERROR.ToString(), ex.Message); //} //if (dtScanMode.Rows.Count > 0) //{ // ScanMode = (int)dtScanMode.Rows[0][0]; //} //else //{ // ScanMode = auditData.ScanMode; //} ScanMode = auditData.ScanMode; string description = ""; if (auditData.Description == "") { description = null; } else { description = auditData.Description; } string SKUCode = ""; if (auditData.SKUCode == "") { SKUCode = null; } else { SKUCode = auditData.SKUCode; } string ExBarcode = ""; if (auditData.ExBarcode == "") { ExBarcode = null; } else { ExBarcode = auditData.ExBarcode; } string InBarcode = ""; if (auditData.InBarcode == "") { InBarcode = null; } else { InBarcode = auditData.InBarcode; } string[] fileNames = fileName.Split('\\'); fileName = fileNames[fileNames.Length - 1]; string cmdAddHHT = "EXEC [dbo].[SP_BATCH_ADDHHT] @StocktakingID = '" + auditData.StockTakingID + "'" + " ,@ScanMode = '" + ScanMode + "',@LocationCode = '" + auditData.LocationCode + "'" + " ,@Barcode = '" + auditData.Barcode + "',@Quantity = '" + auditData.Quantity + "'" + " ,@NewQuantity = NULL ,@UnitCode = '" + auditData.UnitCode + "'" + " ,@Flag = '" + auditData.Flag + "',@Description = '" + description + "'" + " ,@SKUCode = '" + SKUCode + "',@ExBarCode = '" + ExBarcode + "'" + " ,@InBarCode = '" + InBarcode + "',@SKUMode = '" + auditData.SKUMode + "'" + " ,@HHTName = '" + deviceName + "',@CountDate = '" + countDate + "'" + " ,@DepartmentCode = '" + auditData.DepartmentCode + "',@CreateDate = '" + auditData.CreateDate + "'" + " ,@CreateBy = '" + auditData.CreateBy + "',@UpdateDate = '" + auditData.CreateDate + "'" + " ,@UpdateBy = '" + auditData.CreateBy + "',@FileName ='" + fileName + "',@FlagImport = 0,@HHTID = '" + hhtID + "'" + " ,@ImportDate = '" + currentDate + "'"; try { ObjCommand.CommandText = cmdAddHHT.ToString(); ObjCommand.ExecuteNonQuery(); } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), ex.Message); } } ObjCommand.Transaction.Commit(); con.Close(); return(true); } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), "Error but we are rollbacking"); ObjCommand.Transaction.Rollback(); con.Close(); return(false); } } }
public Hashtable ReadFile(string filePath, List <string> filePathError, string fileNameModifyFirst, SettingBean setting) { Hashtable hastResult = new Hashtable(); ImportModel importData = new ImportModel(); Database db = new Database(); List <AuditStocktakingModel> recordData = new List <AuditStocktakingModel>(); string line = ""; string[] columns; int countRow = 0; using (StreamReader sr = new StreamReader(filePath)) { try { for (int i = 1; sr.Peek() >= 0; i++) { line = sr.ReadLine(); if (i == 1) { importData.HHTID = line; } else if (i == 2) { importData.DeviceName = line; } else if (i == 3) { importData.Mode = line; } else { columns = line.Split(','); string StockTakingID = columns[0]; int ScanMode = Convert.ToInt32(columns[1]); string LocationCode = columns[2]; string Barcode = columns[3]; string Flag = columns[6]; string Description = columns[7]; string SKUCode = columns[8]; string ExBarcode = columns[9]; string InBarcode = columns[10]; bool SKUMode = Convert.ToBoolean(columns[11]); string CreateDate = columns[12]; string CreateBy = columns[13]; string DepartmentCode = columns[14]; decimal Quantity = Convert.ToDecimal(columns[4]); int UnitCode = Convert.ToInt32(columns[5]); // 4 is FreshFood //if (ScanMode.Equals(4)) //{ // string ProdType = ""; // if (Barcode.Length == 5) // { // ProdType = db.GetProductTypeFromMasterSKUBarcode(Barcode, setting); // } // else if (Barcode.Length == 18) // { // string castBarcode = Barcode.Substring(1); // castBarcode = castBarcode.Substring(0, 5); // ProdType = db.GetProductTypeFromMasterSKUBarcode(castBarcode, setting); // } // else // { // ProdType = db.GetProductTypeFromMasterSKUInExBarcode(InBarcode, ExBarcode, setting); // } // if (string.IsNullOrWhiteSpace(ProdType)) // { // Flag = "F"; // } // else if (ProdType.Equals("G") || ProdType.Equals("F")) // { // UnitCode = 1; // 1 is Piece // } // else if(ProdType.Equals("W")) // { // Quantity = Quantity * 1000; // UnitCode = 4; // 4 is gram // } //} AuditStocktakingModel record = new AuditStocktakingModel(); record.StockTakingID = StockTakingID; record.ScanMode = ScanMode; record.LocationCode = LocationCode; record.Barcode = Barcode; record.Quantity = Quantity; record.UnitCode = UnitCode; record.Flag = Flag; record.Description = Description; record.SKUCode = SKUCode; record.ExBarcode = ExBarcode; record.InBarcode = InBarcode; record.SKUMode = SKUMode; record.CreateDate = CreateDate; record.CreateBy = CreateBy; record.DepartmentCode = DepartmentCode; recordData.Add(record); } countRow = i; } importData.RecordData = recordData; } catch (Exception ex) { LogFile.write(Status.ERROR.ToString(), fileNameModifyFirst + " : " + ex.Message); filePathError.Add(fileNameModifyFirst); importData.RecordData = new List <AuditStocktakingModel>(); } hastResult.Add("importData", importData); hastResult.Add("filePathError", filePathError); hastResult.Add("countRow", countRow); return(hastResult); } }