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