API tools faq
paste
jojofever

Untitled

jojofever
Mar 17th, 2019
64
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 264.04 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Configuration;
  6. using System.IO;
  7. using System.Collections;
  8. using System.Threading;
  9. using System.Data;
  10. using System.Data.SqlClient;
  11. using System.Data.OleDb;
  12. using System.Net.Mail;
  13. using log4net;
  14.  
  15. namespace LPS.NETFileWatcher
  16. {
  17. class LaunchThread : DatabaseFunctions, IDisposable
  18. {
  19. Shippingdetails objShipDtl = new Shippingdetails();
  20.  
  21. # region variable declaration
  22.  
  23. private FileSystemWatcher LPSFileWatcher;
  24. private static string m_Username = ConfigurationSettings.AppSettings.Get("USERNAME");
  25. private static string m_password = ConfigurationSettings.AppSettings.Get("PASSWORD");
  26. private static string m_FilePath = ConfigurationSettings.AppSettings.Get("PATHTOWATCH");
  27. private static string m_BackupPath = ConfigurationSettings.AppSettings.Get("PATHFORBACKUP");
  28. private static string m_TempPath = ConfigurationSettings.AppSettings.Get("PATHFORDELTEMP");
  29.  
  30.  
  31. private static string m_ProdFileName = ConfigurationSettings.AppSettings.Get("ProdLabel");
  32. private static string m_ProdShipFileName = ConfigurationSettings.AppSettings.Get("ProdShipLabel");
  33. private static string m_QCFileName = ConfigurationSettings.AppSettings.Get("QCLabel");
  34. private static string m_ShipMarkFileName = ConfigurationSettings.AppSettings.Get("ShipMarkLabel");
  35. private static string m_ProdBatchFileName = ConfigurationSettings.AppSettings.Get("ProdBatchLabel");
  36. private static string m_MaterialsFileName = ConfigurationSettings.AppSettings.Get("Materials");
  37. private static string m_ProdConfigFileName = ConfigurationSettings.AppSettings.Get("ProdConfig");
  38. private static string m_ShippingLabelDetails = ConfigurationSettings.AppSettings.Get("ShippingLabelDetails");
  39. private static string m_ProductGroupFileName = ConfigurationSettings.AppSettings.Get("ProductGroup");
  40. private static string m_ProductGroupMasterFileName = ConfigurationSettings.AppSettings.Get("ProductGroupMaster");
  41. private static string m_SafetyRiskPhrases = ConfigurationSettings.AppSettings.Get("SafetyRiskPhrases");
  42. private static string m_Sentences = ConfigurationSettings.AppSettings.Get("Sentences");
  43. private static string m_GHS = ConfigurationSettings.AppSettings.Get("GHS");
  44. private static string m_Location = ConfigurationSettings.AppSettings.Get("Location");
  45.  
  46. private static string m_XMLFileName = ConfigurationSettings.AppSettings.Get("XMLStructureFile");
  47.  
  48. private int A4Limit = Convert.ToInt16(ConfigurationSettings.AppSettings["A4Limit"].ToString());
  49. private int A5Limit = Convert.ToInt16(ConfigurationSettings.AppSettings["A5Limit"].ToString());
  50. private int A6Limit = Convert.ToInt16(ConfigurationSettings.AppSettings["A6Limit"].ToString());
  51.  
  52. private static string OldRefId = "0";
  53.  
  54.  
  55. public int m_intThreadno;
  56. private static readonly ILog log = LogManager.GetLogger(typeof(LaunchThread));
  57. private static string connstring = ConfigurationSettings.AppSettings.Get("Constr");
  58. private static object _lock = new object();
  59.  
  60. private DataSet QCDs = new DataSet();
  61. private DataSet ProdDs = new DataSet();
  62. private DataSet ProdShipDs = new DataSet();
  63. private DataSet ShipMarkDs = new DataSet();
  64. private DataSet PBDs = new DataSet();
  65. private DataSet MatDs = new DataSet();
  66. public ArrayList ArrHead = new ArrayList();
  67. public ArrayList ArrLang = new ArrayList();
  68. public ArrayList ArrSentence = new ArrayList();
  69.  
  70. private static string LastCleanedMonth = "0";
  71.  
  72. public string sMailContent = "";
  73.  
  74. # endregion
  75.  
  76. # region Constructor
  77.  
  78. public LaunchThread()
  79. {
  80. base.ConnectionString = connstring;
  81. }
  82.  
  83. public LaunchThread(int intThread)
  84. {
  85. try
  86. {
  87. log.Info("Inside Launch Thread");
  88. m_intThreadno = intThread;
  89. Exception customEx;
  90. LPSFileWatcher = new FileSystemWatcher();
  91. base.ConnectionString = connstring;
  92. log.Info("File Path" + m_FilePath.ToString());
  93. if (Directory.Exists(m_FilePath))
  94. {
  95. log.Info("Inside If loop");
  96. LPSFileWatcher.Path = m_FilePath;
  97. }
  98. else
  99. {
  100. log.Info("Inside else loop");
  101. customEx = new Exception(@"Path To Watch for FILES Does Not Exists!!!Please create the folder and then proceed!!!");
  102. log.Fatal(customEx);
  103. LPSFileWatcher.Path = m_FilePath;
  104. }
  105. log.Info("Loop finished");
  106. log.Info("SRP File Watcher Created");
  107. LPSFileWatcher.Created += new FileSystemEventHandler(LPSFileWatcher_Created);
  108. }
  109. catch (Exception ex)
  110. {
  111. log.Fatal(ex);
  112. }
  113.  
  114. }
  115. # endregion
  116.  
  117. # region Public Methods
  118. public void StartThread()
  119. {
  120. LPSFileWatcher.EnableRaisingEvents = true;
  121.  
  122. //Get the structure of the SAP file format from a XML file
  123. string LabelType = "";
  124. try
  125. {
  126. log.Info("START THREAD!!");
  127. string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
  128. //Directory.SetCurrentDirectory(@"..\..\");
  129. //path = Directory.GetCurrentDirectory();
  130. path = path + "\\" + m_XMLFileName;
  131. DataSet ds = new DataSet();
  132. ds.ReadXml(path);
  133.  
  134. //string LabelType = "";
  135. DataRow dr;
  136.  
  137. QCDs.ReadXml(path);
  138. QCDs.Tables[0].Clear();
  139.  
  140. ProdDs.ReadXml(path);
  141. ProdDs.Tables[0].Clear();
  142.  
  143. ProdShipDs.ReadXml(path);
  144. ProdShipDs.Tables[0].Clear();
  145.  
  146. ShipMarkDs.ReadXml(path);
  147. ShipMarkDs.Tables[0].Clear();
  148.  
  149. PBDs.ReadXml(path);
  150. PBDs.Tables[0].Clear();
  151.  
  152. MatDs.ReadXml(path);
  153. MatDs.Tables[0].Clear();
  154.  
  155. if (ds.Tables[0].Rows.Count > 0)
  156. {
  157. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  158. {
  159. dr = ds.Tables[0].Rows[i];
  160. LabelType = dr["Label"].ToString();
  161. if (LabelType.ToUpper().Equals(m_QCFileName))
  162. {
  163. DataRow newDr = QCDs.Tables[0].NewRow();
  164.  
  165. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  166. {
  167. newDr[j] = dr[j];
  168. }
  169.  
  170. QCDs.Tables[0].Rows.Add(newDr);
  171. }
  172. if (LabelType.ToUpper().Equals(m_ProdShipFileName))
  173. {
  174. DataRow newDr = ProdShipDs.Tables[0].NewRow();
  175.  
  176. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  177. {
  178. newDr[j] = dr[j];
  179. }
  180.  
  181. ProdShipDs.Tables[0].Rows.Add(newDr);
  182. }
  183. if (LabelType.ToUpper().Equals(m_ProdFileName))
  184. {
  185. DataRow newDr = ProdDs.Tables[0].NewRow();
  186.  
  187. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  188. {
  189. newDr[j] = dr[j];
  190. }
  191.  
  192. ProdDs.Tables[0].Rows.Add(newDr);
  193. }
  194. if (LabelType.ToUpper().Equals(m_ShipMarkFileName))
  195. {
  196. DataRow newDr = ShipMarkDs.Tables[0].NewRow();
  197.  
  198. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  199. {
  200. newDr[j] = dr[j];
  201. }
  202.  
  203. ShipMarkDs.Tables[0].Rows.Add(newDr);
  204. }
  205. if (LabelType.ToUpper().Equals(m_ProdBatchFileName))
  206. {
  207. DataRow newDr = PBDs.Tables[0].NewRow();
  208.  
  209. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  210. {
  211. newDr[j] = dr[j];
  212. }
  213.  
  214. PBDs.Tables[0].Rows.Add(newDr);
  215. }
  216. if (LabelType.ToUpper().Equals(m_MaterialsFileName))
  217. {
  218. DataRow newDr = MatDs.Tables[0].NewRow();
  219.  
  220. for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  221. {
  222. newDr[j] = dr[j];
  223. }
  224.  
  225. MatDs.Tables[0].Rows.Add(newDr);
  226. }
  227. }
  228. }
  229.  
  230. string[] files = Directory.GetFiles(m_FilePath);
  231.  
  232. if (files.Length > 0)
  233. {
  234. for (int i = 0; i < files.Length; i++)
  235. {
  236. if (!Directory.Exists(files[i]))
  237. AddExistingFileDetails(files[i]);
  238. }
  239. }
  240.  
  241. }
  242. catch (Exception ex)
  243. {
  244. log.Fatal(ex);
  245. // ErrorAlertToUser("Error Occured while Starting Thread..."+"Labeltype="+LabelType+" "+ex.Message);
  246. }
  247.  
  248. //End of getting the structure of the SAP file format from a XML file
  249. }
  250. # endregion
  251.  
  252. # region File Watcher Methods
  253. private void LPSFileWatcher_Created(object sender, System.IO.FileSystemEventArgs e)
  254. {
  255. log.Info("NEW FILE");
  256. string strlogMsg = @"";
  257. string strUsername = System.Environment.UserName;
  258. try
  259. {
  260. log.Info("Name- " + e.Name.ToString());
  261. CleanupFiles();
  262. m_FilePath = ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + "\\" + GetFileNameFromPath(e.Name);
  263. strlogMsg = GetFileNameFromPath(e.Name);
  264. strlogMsg = strlogMsg + "," + e.FullPath;
  265. strlogMsg = strlogMsg + "," + "Created";
  266. strlogMsg = strlogMsg + "," + strUsername;
  267. strlogMsg = strlogMsg + "," + System.DateTime.Now.Date.ToShortDateString();
  268. strlogMsg = strlogMsg + "," + System.DateTime.Now.TimeOfDay.ToString();
  269.  
  270. log.Info(strlogMsg);
  271. log.Info("Before If");
  272.  
  273. log.Info(e.Name.Length.ToString());
  274.  
  275. if (e.Name.ToUpper().Contains("EXPORT"))
  276. {
  277. log.Info("PC Length- " + m_ProdConfigFileName.Length.ToString() + " PC Name- " + m_ProdConfigFileName.ToString());
  278. }
  279. else if (e.Name.ToUpper().Contains("PHRASE"))
  280. {
  281. log.Info("SP Length- " + m_SafetyRiskPhrases.Length.ToString() + " SP Name- " + m_SafetyRiskPhrases.ToString());
  282. }
  283. else
  284. {
  285. log.Info("GHS Length- " + m_GHS.Length.ToString() + " GHS Name- " + m_GHS.ToString());
  286. }
  287.  
  288. if (e.Name.Length >= m_ProdConfigFileName.Length && (e.Name.Substring(0, m_ProdConfigFileName.Length).ToUpper() == m_ProdConfigFileName))
  289. {
  290. Thread.Sleep(20000);
  291. log.Info(e.FullPath);
  292. string sSheetName = "Product";
  293. DataSet dsXL = new DataSet();
  294. dsXL = InitializeXLDataset(dsXL);
  295. dsXL = ReadExcelFile(dsXL, e.FullPath, sSheetName);
  296. saveProdConfigDetails(dsXL, e.FullPath, Path.GetFileName(e.FullPath));
  297. strlogMsg = strlogMsg + "," + "PRODUCT CONFIG";
  298. log.Info(strlogMsg);
  299. }
  300. else if (e.Name.Length >= m_SafetyRiskPhrases.Length && (e.Name.Substring(0, m_SafetyRiskPhrases.Length).ToUpper() == m_SafetyRiskPhrases))
  301. {
  302. log.Info("Inside Phrases");
  303. Thread.Sleep(20000);
  304. log.Info(e.FullPath);
  305. string sSheetName = "Phrases";
  306. ArrSentence = GetLangCode4Sentences();
  307. DataSet dsXL = new DataSet();
  308. dsXL = InitializeSentencesXL(dsXL);
  309. dsXL = ReadSentencesXL(dsXL, e.FullPath, sSheetName);
  310. SaveSentencesXLFileDetails(dsXL, e.FullPath, sSheetName);
  311. strlogMsg = strlogMsg + "," + "SafetyRiskPhrases";
  312. log.Info(strlogMsg);
  313. }
  314. else if (e.Name.Length >= m_GHS.Length && (e.Name.Substring(0, m_GHS.Length).ToUpper() == m_GHS))
  315. {
  316. Thread.Sleep(20000);
  317. log.Info(e.FullPath);
  318. string sSheetName = "Type";
  319. ArrSentence = GetLangCode4Sentences();
  320. DataSet dsXL = new DataSet();
  321. dsXL = InitializeGHSXL(dsXL);
  322. dsXL = ReadGHSXLFile(dsXL, e.FullPath, sSheetName);
  323. SaveGHSXL(dsXL, e.FullPath, sSheetName);
  324. strlogMsg = strlogMsg + "," + "GHS File";
  325. log.Info(strlogMsg);
  326. }
  327.  
  328. else
  329. {
  330. log.Info("14");
  331. strlogMsg = "UNRECOGNIZED FILE";
  332. File.Copy(e.FullPath, @ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + "\\ARCHIVE\\" + GetFileNameFromPath(e.Name), true);
  333. File.Delete(e.FullPath);
  334. log.Info(strlogMsg);
  335. ////dbObj.UpdateAudtLog(e.Name, "File Format", "UNRECOGNIZED FILE");
  336. }
  337. }
  338. catch (Exception ex)
  339. {
  340. log.Info("Catch Exception");
  341. log.Fatal(ex);
  342. ErrorAlertToUser(strlogMsg + " " + ex.Message);
  343. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  344. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  345. }
  346. }
  347.  
  348.  
  349. #region GHS
  350. public DataSet InitializeGHSXL(DataSet ds)
  351. {
  352. ds = new DataSet();
  353. ds.Tables.Add("GHSMaster");
  354. ds.Tables["GHSMaster"].Columns.Add("UNIQID");
  355. ds.Tables["GHSMaster"].Columns.Add("MTEXT_VI_VN");
  356. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ID_ID");
  357. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ENG");
  358. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ZHO");
  359. ds.Tables["GHSMaster"].Columns.Add("MTEXT_CHE");
  360. ds.Tables["GHSMaster"].Columns.Add("MTEXT_DAN");
  361. ds.Tables["GHSMaster"].Columns.Add("MTEXT_DUT");
  362. ds.Tables["GHSMaster"].Columns.Add("MTEXT_EST");
  363. ds.Tables["GHSMaster"].Columns.Add("MTEXT_FIN");
  364. ds.Tables["GHSMaster"].Columns.Add("MTEXT_FRA");
  365. ds.Tables["GHSMaster"].Columns.Add("MTEXT_GER");
  366. ds.Tables["GHSMaster"].Columns.Add("MTEXT_HON");
  367. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ITA");
  368. ds.Tables["GHSMaster"].Columns.Add("MTEXT_NOR");
  369. ds.Tables["GHSMaster"].Columns.Add("MTEXT_POR");
  370. ds.Tables["GHSMaster"].Columns.Add("MTEXT_RUS");
  371. ds.Tables["GHSMaster"].Columns.Add("MTEXT_SPA");
  372. ds.Tables["GHSMaster"].Columns.Add("MTEXT_SWE");
  373. ds.Tables["GHSMaster"].Columns.Add("MTEXT_TUR");
  374. ds.Tables["GHSMaster"].Columns.Add("MTEXT_GRE");
  375. ds.Tables["GHSMaster"].Columns.Add("MTEXT_POL");
  376. ds.Tables["GHSMaster"].Columns.Add("MTEXT_CRO");
  377. ds.Tables["GHSMaster"].Columns.Add("MTEXT_FRA_CA");
  378. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ARA");
  379. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ZHO_TR");
  380. ds.Tables["GHSMaster"].Columns.Add("MTEXT_BUL");
  381. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ENG_US");
  382. ds.Tables["GHSMaster"].Columns.Add("MTEXT_JAP");
  383. ds.Tables["GHSMaster"].Columns.Add("MTEXT_KOR");
  384. ds.Tables["GHSMaster"].Columns.Add("MTEXT_LAT");
  385. ds.Tables["GHSMaster"].Columns.Add("MTEXT_LIT");
  386. ds.Tables["GHSMaster"].Columns.Add("MTEXT_ROM");
  387. ds.Tables["GHSMaster"].Columns.Add("MTEXT_SLO");
  388. ds.Tables["GHSMaster"].Columns.Add("MTEXT_SLV");
  389. ds.Tables["GHSMaster"].Columns.Add("MTEXT_SPA_MX");
  390. ds.Tables["GHSMaster"].Columns.Add("MTEXT_THA");
  391. return ds;
  392.  
  393. }
  394. private DataSet ReadGHSXLFile(DataSet dsXL, string sFilePath, string sSheetName)
  395. {
  396. string sFileDesc = "GHS";
  397. string sMailStatus = "Y";
  398. log.Info("Enter Read " + sFileDesc + " Function - " + Path.GetFileName(sFilePath));
  399. bool bWrkSht = false;
  400. bool rAdd = true;
  401. int rCount = 0;
  402. string sUNIQID = "";
  403. string sMTEXT_VI_VN = "";
  404. string sMTEXT_ID_ID = "";
  405. string sMTEXT_ENG = "";
  406. string sMTEXT_ZHO = "";
  407. string sMTEXT_CHE = "";
  408. string sMTEXT_DAN = "";
  409. string sMTEXT_DUT = "";
  410. string sMTEXT_EST = "";
  411. string sMTEXT_FIN = "";
  412. string sMTEXT_FRA = "";
  413. string sMTEXT_GER = "";
  414. string sMTEXT_HON = "";
  415. string sMTEXT_ITA = "";
  416. string sMTEXT_NOR = "";
  417. string sMTEXT_POR = "";
  418. string sMTEXT_RUS = "";
  419. string sMTEXT_SPA = "";
  420. string sMTEXT_SWE = "";
  421. string sMTEXT_TUR = "";
  422. string sMTEXT_GRE = "";
  423. string sMTEXT_POL = "";
  424. string sMTEXT_CRO = "";
  425. string sMTEXT_FRA_CA = "";
  426. string sMTEXT_ARA = "";
  427. string sMTEXT_ZHO_TR = "";
  428. string sMTEXT_BUL = "";
  429. string sMTEXT_ENG_US = "";
  430. string sMTEXT_JAP = "";
  431. string sMTEXT_KOR = "";
  432. string sMTEXT_LAT = "";
  433. string sMTEXT_LIT = "";
  434. string sMTEXT_ROM = "";
  435. string sMTEXT_SLO = "";
  436. string sMTEXT_SLV = "";
  437. string sMTEXT_SPA_MX = "";
  438. string sMTEXT_THA = "";
  439. try
  440. {
  441. log.Info("Start Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  442.  
  443. string szTempPath = sFilePath;
  444.  
  445. string strConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + szTempPath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
  446. OleDbConnection conn1 = new OleDbConnection(strConn);
  447.  
  448. OleDbConnection conn2 = new OleDbConnection(strConn);
  449.  
  450. OleDbCommand cmdRC = new OleDbCommand("Select * From [" + sSheetName + "$]", conn2);
  451. OleDbDataAdapter adpRC = new OleDbDataAdapter(cmdRC);
  452. DataTable excelRC = new DataTable();
  453.  
  454. try
  455. {
  456. adpRC.Fill(excelRC);
  457. }
  458. catch (Exception ex)
  459. {
  460. log.Fatal(ex.Message);
  461. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  462. if (sMailStatus == "Y")
  463. {
  464. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  465. sMailStatus = "N";
  466. }
  467. return dsXL;
  468. }
  469.  
  470. int XLRowCount = excelRC.Rows.Count + 1;
  471. excelRC.Dispose();
  472. cmdRC.Dispose();
  473. adpRC.Dispose();
  474. conn2.Close();
  475. conn2.Dispose();
  476.  
  477. OleDbCommand cmd1 = new OleDbCommand("Select * From [" + sSheetName + "$A1:AK" + XLRowCount.ToString() + "]", conn1);
  478. OleDbDataAdapter adp = new OleDbDataAdapter(cmd1);
  479. DataTable excel = new DataTable();
  480. bWrkSht = false;
  481. try
  482. {
  483. adp.Fill(excel);
  484. }
  485. catch (Exception ex)
  486. {
  487. log.Fatal(ex.Message);
  488. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  489. if (sMailStatus == "Y")
  490. {
  491. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  492. sMailStatus = "N";
  493. }
  494. return dsXL;
  495. }
  496.  
  497. bWrkSht = true;
  498.  
  499. if (true)
  500. {
  501. if (excel.Columns[0].ColumnName.ToUpper().Trim() == "UNIQID".ToUpper().Trim() &&
  502. excel.Columns[1].ColumnName.ToUpper().Trim() == "MTEXT_VI_VN ".ToUpper().Trim() &&
  503. excel.Columns[2].ColumnName.ToUpper().Trim() == "MTEXT_ID_ID".ToUpper().Trim() &&
  504. excel.Columns[3].ColumnName.ToUpper().Trim() == "MTEXT_ENG".ToUpper().Trim() &&
  505. excel.Columns[4].ColumnName.ToUpper().Trim() == "MTEXT_ZHO".ToUpper().Trim() &&
  506. excel.Columns[5].ColumnName.ToUpper().Trim() == "MTEXT_CHE".ToUpper().Trim() &&
  507. excel.Columns[6].ColumnName.ToUpper().Trim() == "MTEXT_DAN".ToUpper().Trim() &&
  508. excel.Columns[7].ColumnName.ToUpper().Trim() == "MTEXT_DUT".ToUpper().Trim() &&
  509. excel.Columns[8].ColumnName.ToUpper().Trim() == "MTEXT_EST".ToUpper().Trim() &&
  510. excel.Columns[9].ColumnName.ToUpper().Trim() == "MTEXT_FIN".ToUpper().Trim() &&
  511. excel.Columns[10].ColumnName.ToUpper().Trim() == "MTEXT_FRA".ToUpper().Trim() &&
  512. excel.Columns[11].ColumnName.ToUpper().Trim() == "MTEXT_GER".ToUpper().Trim() &&
  513. excel.Columns[12].ColumnName.ToUpper().Trim() == "MTEXT_HON".ToUpper().Trim() &&
  514. excel.Columns[13].ColumnName.ToUpper().Trim() == "MTEXT_ITA".ToUpper().Trim() &&
  515. excel.Columns[14].ColumnName.ToUpper().Trim() == "MTEXT_NOR".ToUpper().Trim() &&
  516. excel.Columns[15].ColumnName.ToUpper().Trim() == "MTEXT_POR".ToUpper().Trim() &&
  517. excel.Columns[16].ColumnName.ToUpper().Trim() == "MTEXT_RUS".ToUpper().Trim() &&
  518. excel.Columns[17].ColumnName.ToUpper().Trim() == "MTEXT_SPA".ToUpper().Trim() &&
  519. excel.Columns[18].ColumnName.ToUpper().Trim() == "MTEXT_SWE".ToUpper().Trim() &&
  520. excel.Columns[19].ColumnName.ToUpper().Trim() == "MTEXT_TUR".ToUpper().Trim() &&
  521. excel.Columns[20].ColumnName.ToUpper().Trim() == "MTEXT_GRE".ToUpper().Trim() &&
  522. excel.Columns[21].ColumnName.ToUpper().Trim() == "MTEXT_POL".ToUpper().Trim() &&
  523. excel.Columns[22].ColumnName.ToUpper().Trim() == "MTEXT_CRO".ToUpper().Trim() &&
  524. excel.Columns[23].ColumnName.ToUpper().Trim() == "MTEXT_FRA_CA".ToUpper().Trim() &&
  525. excel.Columns[24].ColumnName.ToUpper().Trim() == "MTEXT_ARA".ToUpper().Trim() &&
  526. excel.Columns[25].ColumnName.ToUpper().Trim() == "MTEXT_ZHO_TR".ToUpper().Trim() &&
  527. excel.Columns[26].ColumnName.ToUpper().Trim() == "MTEXT_BUL".ToUpper().Trim() &&
  528. excel.Columns[27].ColumnName.ToUpper().Trim() == "MTEXT_ENG_US".ToUpper().Trim() &&
  529. excel.Columns[28].ColumnName.ToUpper().Trim() == "MTEXT_JAP".ToUpper().Trim() &&
  530. excel.Columns[29].ColumnName.ToUpper().Trim() == "MTEXT_KOR".ToUpper().Trim() &&
  531. excel.Columns[30].ColumnName.ToUpper().Trim() == "MTEXT_LAT".ToUpper().Trim() &&
  532. excel.Columns[31].ColumnName.ToUpper().Trim() == "MTEXT_LIT".ToUpper().Trim() &&
  533. excel.Columns[32].ColumnName.ToUpper().Trim() == "MTEXT_ROM".ToUpper().Trim() &&
  534. excel.Columns[33].ColumnName.ToUpper().Trim() == "MTEXT_SLO".ToUpper().Trim() &&
  535. excel.Columns[34].ColumnName.ToUpper().Trim() == "MTEXT_SLV".ToUpper().Trim() &&
  536. excel.Columns[35].ColumnName.ToUpper().Trim() == "MTEXT_SPA_MX".ToUpper().Trim() &&
  537. excel.Columns[36].ColumnName.ToUpper().Trim() == "MTEXT_THA".ToUpper().Trim())
  538. {
  539.  
  540. rAdd = true;
  541. for (int i = 0; i <= excel.Rows.Count - 1; i++)
  542. {
  543. sUNIQID = "";
  544. sMTEXT_VI_VN = "";
  545. sMTEXT_ID_ID = "";
  546. sMTEXT_ENG = "";
  547. sMTEXT_ZHO = "";
  548. sMTEXT_CHE = "";
  549. sMTEXT_DAN = "";
  550. sMTEXT_DUT = "";
  551. sMTEXT_EST = "";
  552. sMTEXT_FIN = "";
  553. sMTEXT_FRA = "";
  554. sMTEXT_GER = "";
  555. sMTEXT_HON = "";
  556. sMTEXT_ITA = "";
  557. sMTEXT_NOR = "";
  558. sMTEXT_POR = "";
  559. sMTEXT_RUS = "";
  560. sMTEXT_SPA = "";
  561. sMTEXT_SWE = "";
  562. sMTEXT_TUR = "";
  563. sMTEXT_GRE = "";
  564. sMTEXT_POL = "";
  565. sMTEXT_CRO = "";
  566. sMTEXT_FRA_CA = "";
  567. sMTEXT_ARA = "";
  568. sMTEXT_ZHO_TR = "";
  569. sMTEXT_BUL = "";
  570. sMTEXT_ENG_US = "";
  571. sMTEXT_JAP = "";
  572. sMTEXT_KOR = "";
  573. sMTEXT_LAT = "";
  574. sMTEXT_LIT = "";
  575. sMTEXT_ROM = "";
  576. sMTEXT_SLO = "";
  577. sMTEXT_SLV = "";
  578. sMTEXT_SPA_MX = "";
  579. sMTEXT_THA = "";
  580. rCount = 1;
  581.  
  582. if (rCount != -1)
  583. {
  584. sUNIQID = excel.Rows[i][0].ToString().Trim();
  585. sMTEXT_VI_VN = excel.Rows[i][1].ToString().Trim();
  586. sMTEXT_ID_ID = excel.Rows[i][2].ToString().Trim();
  587. sMTEXT_ENG = excel.Rows[i][3].ToString().Trim();
  588. sMTEXT_ZHO = excel.Rows[i][4].ToString().Trim();
  589. sMTEXT_CHE = excel.Rows[i][5].ToString().Trim();
  590. sMTEXT_DAN = excel.Rows[i][6].ToString().Trim();
  591. sMTEXT_DUT = excel.Rows[i][7].ToString().Trim();
  592. sMTEXT_EST = excel.Rows[i][8].ToString().Trim();
  593. sMTEXT_FIN = excel.Rows[i][9].ToString().Trim();
  594. sMTEXT_FRA = excel.Rows[i][10].ToString().Trim();
  595. sMTEXT_GER = excel.Rows[i][11].ToString().Trim();
  596. sMTEXT_HON = excel.Rows[i][12].ToString().Trim();
  597. sMTEXT_ITA = excel.Rows[i][13].ToString().Trim();
  598. sMTEXT_NOR = excel.Rows[i][14].ToString().Trim();
  599. sMTEXT_POR = excel.Rows[i][15].ToString().Trim();
  600. sMTEXT_RUS = excel.Rows[i][16].ToString().Trim();
  601. sMTEXT_SPA = excel.Rows[i][17].ToString().Trim();
  602. sMTEXT_SWE = excel.Rows[i][18].ToString().Trim();
  603. sMTEXT_TUR = excel.Rows[i][19].ToString().Trim();
  604. sMTEXT_GRE = excel.Rows[i][20].ToString().Trim();
  605. sMTEXT_POL = excel.Rows[i][21].ToString().Trim();
  606. sMTEXT_CRO = excel.Rows[i][22].ToString().Trim();
  607. sMTEXT_FRA_CA = excel.Rows[i][23].ToString().Trim();
  608. sMTEXT_ARA = excel.Rows[i][24].ToString().Trim();
  609. sMTEXT_ZHO_TR = excel.Rows[i][25].ToString().Trim();
  610. sMTEXT_BUL = excel.Rows[i][26].ToString().Trim();
  611. sMTEXT_ENG_US = excel.Rows[i][27].ToString().Trim();
  612. sMTEXT_JAP = excel.Rows[i][28].ToString().Trim();
  613. sMTEXT_KOR = excel.Rows[i][29].ToString().Trim();
  614. sMTEXT_LAT = excel.Rows[i][30].ToString().Trim();
  615. sMTEXT_LIT = excel.Rows[i][31].ToString().Trim();
  616. sMTEXT_ROM = excel.Rows[i][32].ToString().Trim();
  617. sMTEXT_SLO = excel.Rows[i][33].ToString().Trim();
  618. sMTEXT_SLV = excel.Rows[i][34].ToString().Trim();
  619. sMTEXT_SPA_MX = excel.Rows[i][35].ToString().Trim();
  620. sMTEXT_THA = excel.Rows[i][36].ToString().Trim();
  621.  
  622. if (sUNIQID != "")
  623. {
  624. DataRow Row = dsXL.Tables["GHSMaster"].NewRow();
  625. Row.BeginEdit();
  626. Row["UNIQID"] = sUNIQID;
  627. Row["MTEXT_VI_VN"] = sMTEXT_VI_VN;
  628. Row["MTEXT_ID_ID"] = sMTEXT_ID_ID;
  629. Row["MTEXT_ENG"] = sMTEXT_ENG;
  630. Row["MTEXT_ZHO"] = sMTEXT_ZHO;
  631. Row["MTEXT_CHE"] = sMTEXT_CHE;
  632. Row["MTEXT_DAN"] = sMTEXT_DAN;
  633. Row["MTEXT_DUT"] = sMTEXT_DUT;
  634. Row["MTEXT_EST"] = sMTEXT_EST;
  635. Row["MTEXT_FIN"] = sMTEXT_FIN;
  636. Row["MTEXT_FRA"] = sMTEXT_FRA;
  637. Row["MTEXT_GER"] = sMTEXT_GER;
  638. Row["MTEXT_HON"] = sMTEXT_HON;
  639. Row["MTEXT_ITA"] = sMTEXT_ITA;
  640. Row["MTEXT_NOR"] = sMTEXT_NOR;
  641. Row["MTEXT_POR"] = sMTEXT_POR;
  642. Row["MTEXT_RUS"] = sMTEXT_RUS;
  643. Row["MTEXT_SPA"] = sMTEXT_SPA;
  644. Row["MTEXT_SWE"] = sMTEXT_SWE;
  645. Row["MTEXT_TUR"] = sMTEXT_TUR;
  646. Row["MTEXT_GRE"] = sMTEXT_GRE;
  647. Row["MTEXT_POL"] = sMTEXT_POL;
  648. Row["MTEXT_CRO"] = sMTEXT_CRO;
  649. Row["MTEXT_FRA_CA"] = sMTEXT_FRA_CA;
  650. Row["MTEXT_ARA"] = sMTEXT_ARA;
  651. Row["MTEXT_ZHO_TR"] = sMTEXT_ZHO_TR;
  652. Row["MTEXT_BUL"] = sMTEXT_BUL;
  653. Row["MTEXT_ENG_US"] = sMTEXT_ENG_US;
  654. Row["MTEXT_JAP"] = sMTEXT_JAP;
  655. Row["MTEXT_KOR"] = sMTEXT_KOR;
  656. Row["MTEXT_LAT"] = sMTEXT_LAT;
  657. Row["MTEXT_LIT"] = sMTEXT_LIT;
  658. Row["MTEXT_ROM"] = sMTEXT_ROM;
  659. Row["MTEXT_SLO"] = sMTEXT_SLO;
  660. Row["MTEXT_SLV"] = sMTEXT_SLV;
  661. Row["MTEXT_SPA_MX"] = sMTEXT_SPA_MX;
  662. Row["MTEXT_THA"] = sMTEXT_THA;
  663. Row.EndEdit();
  664. dsXL.Tables["GHSMaster"].Rows.Add(Row);
  665.  
  666. log.Info("Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - " + sUNIQID + "," +
  667. sMTEXT_VI_VN + "," +
  668. sMTEXT_ID_ID + "," +
  669. sMTEXT_ENG + "," +
  670. sMTEXT_ZHO + "," +
  671. sMTEXT_CHE + "," +
  672. sMTEXT_DAN + "," +
  673. sMTEXT_DUT + "," +
  674. sMTEXT_EST + "," +
  675. sMTEXT_FIN + "," +
  676. sMTEXT_FRA + "," +
  677. sMTEXT_GER + "," +
  678. sMTEXT_HON + "," +
  679. sMTEXT_ITA + "," +
  680. sMTEXT_NOR + "," +
  681. sMTEXT_POR + "," +
  682. sMTEXT_RUS + "," +
  683. sMTEXT_SPA + "," +
  684. sMTEXT_SWE + "," +
  685. sMTEXT_TUR + "," +
  686. sMTEXT_GRE + "," +
  687. sMTEXT_POL + "," +
  688. sMTEXT_CRO + "," +
  689. sMTEXT_FRA_CA + "," +
  690. sMTEXT_ARA + "," +
  691. sMTEXT_ZHO_TR + "," +
  692. sMTEXT_BUL + "," +
  693. sMTEXT_ENG_US + "," +
  694. sMTEXT_JAP + "," +
  695. sMTEXT_KOR + "," +
  696. sMTEXT_LAT + "," +
  697. sMTEXT_LIT + "," +
  698. sMTEXT_ROM + "," +
  699. sMTEXT_SLO + "," +
  700. sMTEXT_SLV + "," +
  701. sMTEXT_SPA_MX + "," +
  702. sMTEXT_THA);
  703. }
  704. else
  705. {
  706. if (sMailStatus == "Y")
  707. {
  708. ErrorAlertToUser("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  709. sMailStatus = "N";
  710. }
  711. log.Error("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  712. dsXL.Clear();
  713. return dsXL;
  714. }
  715. }
  716. }
  717.  
  718. excel.Dispose();
  719. cmd1.Dispose();
  720. adp.Dispose();
  721. conn1.Close();
  722. conn1.Dispose();
  723. log.Info("Finished Reading " + sFileDesc + " File - " + Path.GetFileName(sFilePath));
  724.  
  725. }
  726. else
  727. {
  728. // Invalid Header Name in Excel Sheet !!!
  729. log.Error("Invalid Header in " + sFileDesc + " File !!!");
  730. ErrorAlertToUser("Invalid Header in " + sFileDesc + " File !!!" + sSheetName);
  731. dsXL.Clear();
  732. return dsXL;
  733. }
  734. }
  735. }
  736.  
  737. catch (Exception ex)
  738. {
  739. log.Fatal(ex.Message);
  740. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  741. if (sMailStatus == "Y")
  742. {
  743. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  744. sMailStatus = "N";
  745. }
  746. dsXL.Clear();
  747. return dsXL;
  748. }
  749. finally
  750. {
  751.  
  752. }
  753. log.Info("Leave Read " + sFileDesc + " File Function - " + Path.GetFileName(sFilePath));
  754. return dsXL;
  755.  
  756.  
  757. }
  758. public bool SaveGHSXL(DataSet dsXL, string sFilePath, string sSheetName)
  759. {
  760. bool bRet = true;
  761. if (dsXL.Tables.Count > 0)
  762. {
  763. try
  764. {
  765. string sCode_Code = "";
  766. string SafetyRiskPhrsLang_Code = "";
  767. int sCode_LangId = 0;
  768. string sCode_Description = "";
  769. int sCode_CreatedBy = 1;
  770. string sSRP_Desc = "";
  771. string sCode_Type = "";
  772. if (m_GHS == "GHS")
  773. {
  774. sCode_Type = "GHS";
  775. }
  776.  
  777. if (dsXL.Tables["GHSMaster"].Rows.Count > 0)
  778. {
  779. using (MasterModule MasMod = new MasterModule(DBMethods.ConnString()))
  780. {
  781. for (int i = 0; i < dsXL.Tables["GHSMaster"].Rows.Count; i++)
  782. {
  783. sCode_Code = dsXL.Tables["GHSMaster"].Rows[i][0].ToString().Trim();
  784.  
  785. for (int j = 1; j < dsXL.Tables["GHSMaster"].Columns.Count; j++)
  786. {
  787.  
  788. SafetyRiskPhrsLang_Code = ArrSentence[j].ToString();
  789. sCode_LangId = MasMod.GetSafetyRiskPhrsLangId(SafetyRiskPhrsLang_Code);
  790. if (sCode_LangId == 1)
  791. {
  792. int tem = 0;
  793. sCode_Description = dsXL.Tables["GHSMaster"].Rows[i][3].ToString().Trim();
  794. tem = MasMod.GetSafetyRiskPhrsIdCount(sCode_Code, sCode_Description);
  795. if (tem > 0)
  796. {
  797. MasMod.UpdateSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description);
  798. log.Info("Updated : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  799. sCode_LangId + "," +
  800. sCode_Description + "," +
  801. sCode_CreatedBy);
  802. }
  803. else
  804. {
  805. MasMod.InsertSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description, sCode_Type, sCode_CreatedBy);
  806. log.Info("Inserted : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  807. sCode_LangId + "," +
  808. sCode_Description + "," +
  809. sCode_CreatedBy);
  810. }
  811. }
  812. else
  813. {
  814. int sSRPCode_Id = 0;
  815. int sSRPLang_Id = 0;
  816. sSRPCode_Id = MasMod.GetCode_Id(sCode_Code);
  817. int tem = 0;
  818. if (sSRPCode_Id == 0)
  819. {
  820. sCode_Description = dsXL.Tables["GHSMaster"].Rows[i][3].ToString().Trim();
  821. tem = MasMod.GetSafetyRiskPhrsIdCount(sCode_Code, sCode_Description);
  822. sCode_LangId = 1;
  823. if (tem > 0)
  824. {
  825. MasMod.UpdateSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description);
  826. log.Info("Updated : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  827. sCode_LangId + "," + sCode_Type + "," +
  828. sCode_Description + "," +
  829. sCode_CreatedBy);
  830. }
  831. else
  832. {
  833. sCode_LangId = 1;
  834. MasMod.InsertSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description, sCode_Type, sCode_CreatedBy);
  835. log.Info("Inserted : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  836. sCode_LangId + "," + sCode_Type + "," +
  837. sCode_Description + "," +
  838. sCode_CreatedBy);
  839. }
  840. }
  841. sSRPCode_Id = MasMod.GetCode_Id(sCode_Code);
  842. sSRPLang_Id = sCode_LangId = MasMod.GetSafetyRiskPhrsLangId(SafetyRiskPhrsLang_Code);
  843. int sSRP_CreatedBy = 1;
  844. sSRP_Desc = dsXL.Tables["GHSMaster"].Rows[i][j].ToString().Trim();
  845. int tem2 = 0;
  846. tem2 = MasMod.GetSRP_IdCount(sSRPCode_Id, sSRPLang_Id);
  847. if (tem2 > 0)
  848. {
  849. MasMod.UpdateSRSTable(sSRPCode_Id, sSRPLang_Id, sSRP_Desc);
  850. log.Info("Updated : SafetyRiskPhrases Table " + " - Record No : " + (j + 1).ToString() + " - " + sSRPCode_Id + "," +
  851. sSRPLang_Id + "," +
  852. sSRP_Desc);
  853. }
  854. else
  855. {
  856. MasMod.InsertSRPTable(sSRPCode_Id, sSRPLang_Id, sSRP_Desc, sSRP_CreatedBy);
  857. log.Info("Inserted : SafetyRiskPhrases Table " + " - Record No : " + (j + 1).ToString() + " - " + sSRPCode_Id + "," +
  858. sSRPLang_Id + "," +
  859. sSRP_Desc + "," +
  860. sCode_CreatedBy);
  861. }
  862.  
  863. }
  864. }
  865. }
  866.  
  867. }
  868. }
  869. }
  870. catch (Exception ex)
  871. {
  872. bRet = false;
  873. }
  874.  
  875. }
  876. if (bRet == true)
  877. {
  878. if (File.Exists(m_BackupPath + "\\" + Path.GetFileName(sFilePath)))
  879. File.Delete(sFilePath);
  880. else
  881. File.Move(sFilePath, m_BackupPath + "\\" + Path.GetFileName(sFilePath));
  882. }
  883.  
  884. return bRet;
  885.  
  886. }
  887. #endregion
  888.  
  889. #region SafetyRiskPhrases
  890. public ArrayList GetLangCode()
  891. {
  892. ArrayList arr = new ArrayList();
  893. arr.Add("Header");
  894. arr.Add("ENG");
  895. arr.Add("ZHO");
  896. arr.Add("CHE");
  897. arr.Add("DAN");
  898. arr.Add("DUT");
  899. arr.Add("EST");
  900. arr.Add("FIN");
  901. arr.Add("FRA");
  902. arr.Add("GER");
  903. arr.Add("HON");
  904. arr.Add("ITA");
  905. arr.Add("NOR");
  906. arr.Add("POR");
  907. arr.Add("RUS");
  908. arr.Add("SPA");
  909. arr.Add("SWE");
  910. arr.Add("TUR");
  911. arr.Add("GRE");
  912. arr.Add("POL");
  913. arr.Add("CRO");
  914. return arr;
  915. }
  916.  
  917.  
  918. public DataSet InitializeSafetyRiskPhrasesXL(DataSet ds)
  919. {
  920. ds = new DataSet();
  921. ds.Tables.Add("SafetyRiskPhrasesMaster");
  922. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("UNIQID");
  923. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ENG");
  924. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ZHO");
  925. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_CHE");
  926. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_DAN");
  927. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_DUT");
  928. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_EST");
  929. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_FIN");
  930. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_FRA");
  931. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_GER");
  932. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_HON");
  933. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ITA");
  934. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_NOR");
  935. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_POR");
  936. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_RUS");
  937. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_SPA");
  938. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_SWE");
  939. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_TUR");
  940. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_GRE");
  941. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_POL");
  942. ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_CRO");
  943.  
  944. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_FRA_CA");
  945. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ARA");
  946. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ZHO_TR");
  947. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_BUL");
  948. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ENG_US");
  949. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_JAP");
  950. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_KOR");
  951. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_LAT");
  952. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_LIT");
  953. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ROM");
  954. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_SLO");
  955. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_SLV");
  956. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_SPA_MX");
  957. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_THA");
  958. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_BRA");
  959. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_VI_VN");
  960. //ds.Tables["SafetyRiskPhrasesMaster"].Columns.Add("MTEXT_ID_ID");
  961.  
  962. return ds;
  963. }
  964. private DataSet ReadSafetyRiskPhrasesXL(DataSet dsXL, string sFilePath, string sSheetName)
  965. {
  966. string sFileDesc = "SAFETYRISKPHRASES";
  967. string sMailStatus = "Y";
  968. log.Info("Enter Read " + sFileDesc + " Function - " + Path.GetFileName(sFilePath));
  969. bool bWrkSht = false;
  970. bool rAdd = true;
  971. int rCount = 0;
  972. string sUNIQID = "";
  973. string sMTEXT_ENG = "";
  974. string sMTEXT_ZHO = "";
  975. string sMTEXT_CHE = "";
  976. string sMTEXT_DAN = "";
  977. string sMTEXT_DUT = "";
  978. string sMTEXT_EST = "";
  979. string sMTEXT_FIN = "";
  980. string sMTEXT_FRA = "";
  981. string sMTEXT_GER = "";
  982. string sMTEXT_HON = "";
  983. string sMTEXT_ITA = "";
  984. string sMTEXT_NOR = "";
  985. string sMTEXT_POR = "";
  986. string sMTEXT_RUS = "";
  987. string sMTEXT_SPA = "";
  988. string sMTEXT_SWE = "";
  989. string sMTEXT_TUR = "";
  990. string sMTEXT_GRE = "";
  991. string sMTEXT_POL = "";
  992. string sMTEXT_CRO = "";
  993. try
  994. {
  995. log.Info("Start Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  996.  
  997. string szTempPath = sFilePath;
  998.  
  999. string strConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + szTempPath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
  1000. OleDbConnection conn1 = new OleDbConnection(strConn);
  1001.  
  1002. OleDbConnection conn2 = new OleDbConnection(strConn);
  1003.  
  1004. OleDbCommand cmdRC = new OleDbCommand("Select * From [" + sSheetName + "$]", conn2);
  1005. OleDbDataAdapter adpRC = new OleDbDataAdapter(cmdRC);
  1006. DataTable excelRC = new DataTable();
  1007.  
  1008. try
  1009. {
  1010. adpRC.Fill(excelRC);
  1011. }
  1012. catch (Exception ex)
  1013. {
  1014. log.Fatal(ex.Message);
  1015. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1016. if (sMailStatus == "Y")
  1017. {
  1018. sMailStatus = "N";
  1019. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1020.  
  1021. }
  1022. return dsXL;
  1023. }
  1024.  
  1025. int XLRowCount = excelRC.Rows.Count + 1;
  1026. excelRC.Dispose();
  1027. cmdRC.Dispose();
  1028. adpRC.Dispose();
  1029. conn2.Close();
  1030. conn2.Dispose();
  1031.  
  1032. OleDbCommand cmd1 = new OleDbCommand("Select * From [" + sSheetName + "$A1:U" + XLRowCount.ToString() + "]", conn1);
  1033. OleDbDataAdapter adp = new OleDbDataAdapter(cmd1);
  1034. DataTable excel = new DataTable();
  1035. bWrkSht = false;
  1036. try
  1037. {
  1038. adp.Fill(excel);
  1039. }
  1040. catch (Exception ex)
  1041. {
  1042. log.Fatal(ex.Message);
  1043. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1044. if (sMailStatus == "Y")
  1045. {
  1046. sMailStatus = "N";
  1047. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1048.  
  1049. }
  1050. return dsXL;
  1051. }
  1052.  
  1053. bWrkSht = true;
  1054.  
  1055. if (true)
  1056. {
  1057. if (excel.Columns[0].ColumnName.ToUpper().Trim() == "UNIQID".ToUpper().Trim() &&
  1058. excel.Columns[1].ColumnName.ToUpper().Trim() == "MTEXT_ENG".ToUpper().Trim() &&
  1059. excel.Columns[2].ColumnName.ToUpper().Trim() == "MTEXT_ZHO".ToUpper().Trim() &&
  1060. excel.Columns[3].ColumnName.ToUpper().Trim() == "MTEXT_CHE".ToUpper().Trim() &&
  1061. excel.Columns[4].ColumnName.ToUpper().Trim() == "MTEXT_DAN".ToUpper().Trim() &&
  1062. excel.Columns[5].ColumnName.ToUpper().Trim() == "MTEXT_DUT".ToUpper().Trim() &&
  1063. excel.Columns[6].ColumnName.ToUpper().Trim() == "MTEXT_EST".ToUpper().Trim() &&
  1064. excel.Columns[7].ColumnName.ToUpper().Trim() == "MTEXT_FIN".ToUpper().Trim() &&
  1065. excel.Columns[8].ColumnName.ToUpper().Trim() == "MTEXT_FRA".ToUpper().Trim() &&
  1066. excel.Columns[9].ColumnName.ToUpper().Trim() == "MTEXT_GER".ToUpper().Trim() &&
  1067. excel.Columns[10].ColumnName.ToUpper().Trim() == "MTEXT_HON".ToUpper().Trim() &&
  1068. excel.Columns[11].ColumnName.ToUpper().Trim() == "MTEXT_ITA".ToUpper().Trim() &&
  1069. excel.Columns[12].ColumnName.ToUpper().Trim() == "MTEXT_NOR".ToUpper().Trim() &&
  1070. excel.Columns[13].ColumnName.ToUpper().Trim() == "MTEXT_POR".ToUpper().Trim() &&
  1071. excel.Columns[14].ColumnName.ToUpper().Trim() == "MTEXT_RUS".ToUpper().Trim() &&
  1072. excel.Columns[15].ColumnName.ToUpper().Trim() == "MTEXT_SPA".ToUpper().Trim() &&
  1073. excel.Columns[16].ColumnName.ToUpper().Trim() == "MTEXT_SWE".ToUpper().Trim() &&
  1074. excel.Columns[17].ColumnName.ToUpper().Trim() == "MTEXT_TUR".ToUpper().Trim() &&
  1075. excel.Columns[18].ColumnName.ToUpper().Trim() == "MTEXT_GRE".ToUpper().Trim() &&
  1076. excel.Columns[19].ColumnName.ToUpper().Trim() == "MTEXT_POL".ToUpper().Trim() &&
  1077. excel.Columns[20].ColumnName.ToUpper().Trim() == "MTEXT_CRO".ToUpper().Trim())
  1078. {
  1079.  
  1080. rAdd = true;
  1081.  
  1082. for (int i = 0; i <= excel.Rows.Count - 1; i++)
  1083. {
  1084. sUNIQID = "";
  1085. sMTEXT_ENG = "";
  1086. sMTEXT_ZHO = "";
  1087. sMTEXT_CHE = "";
  1088. sMTEXT_DAN = "";
  1089. sMTEXT_DUT = "";
  1090. sMTEXT_EST = "";
  1091. sMTEXT_FIN = "";
  1092. sMTEXT_FRA = "";
  1093. sMTEXT_GER = "";
  1094. sMTEXT_HON = "";
  1095. sMTEXT_ITA = "";
  1096. sMTEXT_NOR = "";
  1097. sMTEXT_POR = "";
  1098. sMTEXT_RUS = "";
  1099. sMTEXT_SPA = "";
  1100. sMTEXT_SWE = "";
  1101. sMTEXT_TUR = "";
  1102. sMTEXT_GRE = "";
  1103. sMTEXT_POL = "";
  1104. sMTEXT_CRO = "";
  1105. rCount = 1;
  1106.  
  1107. if (rCount != -1)
  1108. {
  1109. sUNIQID = excel.Rows[i][0].ToString().Trim();
  1110. sMTEXT_ENG = excel.Rows[i][1].ToString().Trim();
  1111. sMTEXT_ZHO = excel.Rows[i][2].ToString().Trim();
  1112. sMTEXT_CHE = excel.Rows[i][3].ToString().Trim();
  1113. sMTEXT_DAN = excel.Rows[i][4].ToString().Trim();
  1114. sMTEXT_DUT = excel.Rows[i][5].ToString().Trim();
  1115. sMTEXT_EST = excel.Rows[i][6].ToString().Trim();
  1116. sMTEXT_FIN = excel.Rows[i][7].ToString().Trim();
  1117. sMTEXT_FRA = excel.Rows[i][8].ToString().Trim();
  1118. sMTEXT_GER = excel.Rows[i][9].ToString().Trim();
  1119. sMTEXT_HON = excel.Rows[i][10].ToString().Trim();
  1120. sMTEXT_ITA = excel.Rows[i][11].ToString().Trim();
  1121. sMTEXT_NOR = excel.Rows[i][12].ToString().Trim();
  1122. sMTEXT_POR = excel.Rows[i][13].ToString().Trim();
  1123. sMTEXT_RUS = excel.Rows[i][14].ToString().Trim();
  1124. sMTEXT_SPA = excel.Rows[i][15].ToString().Trim();
  1125. sMTEXT_SWE = excel.Rows[i][16].ToString().Trim();
  1126. sMTEXT_TUR = excel.Rows[i][17].ToString().Trim();
  1127. sMTEXT_GRE = excel.Rows[i][18].ToString().Trim();
  1128. sMTEXT_POL = excel.Rows[i][19].ToString().Trim();
  1129. sMTEXT_CRO = excel.Rows[i][20].ToString().Trim();
  1130.  
  1131. if (sUNIQID != "")
  1132. {
  1133. DataRow Row = dsXL.Tables["SafetyRiskPhrasesMaster"].NewRow();
  1134. Row.BeginEdit();
  1135. Row["UNIQID"] = sUNIQID;
  1136. Row["MTEXT_ENG"] = sMTEXT_ENG;
  1137. Row["MTEXT_ZHO"] = sMTEXT_ZHO;
  1138. Row["MTEXT_CHE"] = sMTEXT_CHE;
  1139. Row["MTEXT_DAN"] = sMTEXT_DAN;
  1140. Row["MTEXT_DUT"] = sMTEXT_DUT;
  1141. Row["MTEXT_EST"] = sMTEXT_EST;
  1142. Row["MTEXT_FIN"] = sMTEXT_FIN;
  1143. Row["MTEXT_FRA"] = sMTEXT_FRA;
  1144. Row["MTEXT_GER"] = sMTEXT_GER;
  1145. Row["MTEXT_HON"] = sMTEXT_HON;
  1146. Row["MTEXT_ITA"] = sMTEXT_ITA;
  1147. Row["MTEXT_NOR"] = sMTEXT_NOR;
  1148. Row["MTEXT_POR"] = sMTEXT_POR;
  1149. Row["MTEXT_RUS"] = sMTEXT_RUS;
  1150. Row["MTEXT_SPA"] = sMTEXT_SPA;
  1151. Row["MTEXT_SWE"] = sMTEXT_SWE;
  1152. Row["MTEXT_TUR"] = sMTEXT_TUR;
  1153. Row["MTEXT_GRE"] = sMTEXT_GRE;
  1154. Row["MTEXT_POL"] = sMTEXT_POL;
  1155. Row["MTEXT_CRO"] = sMTEXT_CRO;
  1156. Row.EndEdit();
  1157. dsXL.Tables["SafetyRiskPhrasesMaster"].Rows.Add(Row);
  1158.  
  1159. log.Info("Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - " + sUNIQID + "," +
  1160. sMTEXT_ENG + "," +
  1161. sMTEXT_ZHO + "," +
  1162. sMTEXT_CHE + "," +
  1163. sMTEXT_DAN + "," +
  1164. sMTEXT_DUT + "," +
  1165. sMTEXT_EST + "," +
  1166. sMTEXT_FIN + "," +
  1167. sMTEXT_FRA + "," +
  1168. sMTEXT_GER + "," +
  1169. sMTEXT_HON + "," +
  1170. sMTEXT_ITA + "," +
  1171. sMTEXT_NOR + "," +
  1172. sMTEXT_POR + "," +
  1173. sMTEXT_RUS + "," +
  1174. sMTEXT_SPA + "," +
  1175. sMTEXT_SWE + "," +
  1176. sMTEXT_TUR + "," +
  1177. sMTEXT_GRE + "," +
  1178. sMTEXT_POL + "," +
  1179. sMTEXT_CRO);
  1180. }
  1181. else
  1182. {
  1183. log.Error("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1184. if (sMailStatus == "Y")
  1185. {
  1186. sMailStatus = "N";
  1187. ErrorAlertToUser("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1188. }
  1189. dsXL.Clear();
  1190. return dsXL;
  1191. }
  1192. }
  1193. }
  1194.  
  1195. excel.Dispose();
  1196. cmd1.Dispose();
  1197. adp.Dispose();
  1198. conn1.Close();
  1199. conn1.Dispose();
  1200. log.Info("Finished Reading " + sFileDesc + " File - " + Path.GetFileName(sFilePath));
  1201.  
  1202. }
  1203. else
  1204. {
  1205. // Invalid Header Name in Excel Sheet !!!
  1206. log.Error("Invalid Header in " + sFileDesc + " File !!!");
  1207. ErrorAlertToUser("Invalid Header in " + sFileDesc + " File !!!" + sSheetName);
  1208. dsXL.Clear();
  1209. return dsXL;
  1210. }
  1211. }
  1212. }
  1213.  
  1214. catch (Exception ex)
  1215. {
  1216. log.Fatal(ex.Message);
  1217. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1218. if (sMailStatus == "Y")
  1219. {
  1220. sMailStatus = "N";
  1221. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1222. }
  1223. dsXL.Clear();
  1224. return dsXL;
  1225. }
  1226. finally
  1227. {
  1228.  
  1229. }
  1230. log.Info("Leave Read " + sFileDesc + " File Function - " + Path.GetFileName(sFilePath));
  1231. return dsXL;
  1232. }
  1233. public bool SaveSafetyRiskPhrasesDetails(DataSet dsXL, string sFilePath, string sSheetName)
  1234. {
  1235. bool bRet = true;
  1236. if (dsXL.Tables.Count > 0)
  1237. {
  1238. try
  1239. {
  1240. string sCode_Code = "";
  1241. string SafetyRiskPhrsLang_Code = "";
  1242. int sCode_LangId = 0;
  1243. string sCode_Description = "";
  1244. int sCode_CreatedBy = 1;
  1245. string sSRP_Desc = "";
  1246. string sCode_Type = "";
  1247. if (m_SafetyRiskPhrases == "SAFETYRISKPHRASES")
  1248. {
  1249. sCode_Type = "SR";
  1250. }
  1251.  
  1252. if (dsXL.Tables["SafetyRiskPhrasesMaster"].Rows.Count > 0)
  1253. {
  1254. using (MasterModule MasMod = new MasterModule(DBMethods.ConnString()))
  1255. {
  1256. for (int i = 0; i < dsXL.Tables["SafetyRiskPhrasesMaster"].Rows.Count; i++)
  1257. {
  1258. sCode_Code = dsXL.Tables["SafetyRiskPhrasesMaster"].Rows[i][0].ToString().Trim();
  1259.  
  1260. for (int j = 1; j < dsXL.Tables["SafetyRiskPhrasesMaster"].Columns.Count; j++)
  1261. {
  1262.  
  1263. SafetyRiskPhrsLang_Code = ArrLang[j].ToString();
  1264. sCode_LangId = MasMod.GetSafetyRiskPhrsLangId(SafetyRiskPhrsLang_Code);
  1265. if (sCode_LangId == 1)
  1266. {
  1267. int tem = 0;
  1268. sCode_Description = dsXL.Tables["SafetyRiskPhrasesMaster"].Rows[i][1].ToString().Trim();
  1269. tem = MasMod.GetSafetyRiskPhrsIdCount(sCode_Code, sCode_Description);
  1270. if (tem > 0)
  1271. {
  1272. MasMod.UpdateSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description);
  1273. log.Info("Updated : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  1274. sCode_LangId + "," +
  1275. sCode_Description + "," +
  1276. sCode_CreatedBy);
  1277. }
  1278. else
  1279. {
  1280. MasMod.InsertSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description, sCode_Type, sCode_CreatedBy);
  1281. log.Info("Inserted : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  1282. sCode_LangId + "," +
  1283. sCode_Description + "," +
  1284. sCode_CreatedBy);
  1285. }
  1286. }
  1287. else
  1288. {
  1289.  
  1290. int sSRPCode_Id = 0;
  1291. int sSRPLang_Id = 0;
  1292. sSRPCode_Id = MasMod.GetCode_Id(sCode_Code);
  1293. int tem = 0;
  1294. if (sSRPCode_Id == 0)
  1295. {
  1296. sCode_Description = dsXL.Tables["SafetyRiskPhrasesMaster"].Rows[i][3].ToString().Trim();
  1297. tem = MasMod.GetSafetyRiskPhrsIdCount(sCode_Code, sCode_Description);
  1298. sCode_LangId = 1;
  1299. if (tem > 0)
  1300. {
  1301. MasMod.UpdateSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description);
  1302. log.Info("Updated : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  1303. sCode_LangId + "," + sCode_Type + "," +
  1304. sCode_Description + "," +
  1305. sCode_CreatedBy);
  1306. }
  1307. else
  1308. {
  1309. sCode_LangId = 1;
  1310. MasMod.InsertSafetyRiskToCodeTable(sCode_Code, sCode_LangId, sCode_Description, sCode_Type, sCode_CreatedBy);
  1311. log.Info("Inserted : Code Table " + " - Record No : " + (i + 1).ToString() + " - " + sCode_Code + "," +
  1312. sCode_LangId + "," + sCode_Type + "," +
  1313. sCode_Description + "," +
  1314. sCode_CreatedBy);
  1315. }
  1316. }
  1317. sSRPCode_Id = MasMod.GetCode_Id(sCode_Code);
  1318. sSRPLang_Id = sCode_LangId = MasMod.GetSafetyRiskPhrsLangId(SafetyRiskPhrsLang_Code);
  1319. int sSRP_CreatedBy = 1;
  1320. sSRP_Desc = dsXL.Tables["SafetyRiskPhrasesMaster"].Rows[i][j].ToString().Trim();
  1321. int tem2 = 0;
  1322. tem2 = MasMod.GetSRP_IdCount(sSRPCode_Id, sSRPLang_Id);
  1323. if (tem2 > 0)
  1324. {
  1325. MasMod.UpdateSRSTable(sSRPCode_Id, sSRPLang_Id, sSRP_Desc);
  1326. log.Info("Updated : SafetyRiskPhrases Table " + " - Record No : " + (j + 1).ToString() + " - " + sSRPCode_Id + "," +
  1327. sSRPLang_Id + "," +
  1328. sSRP_Desc);
  1329. }
  1330. else
  1331. {
  1332. MasMod.InsertSRPTable(sSRPCode_Id, sSRPLang_Id, sSRP_Desc, sSRP_CreatedBy);
  1333. log.Info("Inserted : SafetyRiskPhrases Table " + " - Record No : " + (j + 1).ToString() + " - " + sSRPCode_Id + "," +
  1334. sSRPLang_Id + "," +
  1335. sSRP_Desc + "," +
  1336. sCode_CreatedBy);
  1337. }
  1338.  
  1339. }
  1340. }
  1341. }
  1342.  
  1343. }
  1344. }
  1345. }
  1346. catch (Exception ex)
  1347. {
  1348. bRet = false;
  1349. }
  1350.  
  1351. }
  1352. if (bRet == true)
  1353. {
  1354. if (File.Exists(m_BackupPath + "\\" + Path.GetFileName(sFilePath)))
  1355. File.Delete(sFilePath);
  1356. else
  1357. File.Move(sFilePath, m_BackupPath + "\\" + Path.GetFileName(sFilePath));
  1358. }
  1359.  
  1360. return bRet;
  1361. }
  1362.  
  1363. #endregion
  1364.  
  1365.  
  1366. public DataSet FileConvert(string File)
  1367. {
  1368.  
  1369.  
  1370. DataSet ds = new DataSet();
  1371.  
  1372. DataTable Table1 = new DataTable();
  1373.  
  1374. Table1.Columns.Add("order_No", typeof(string));
  1375. Table1.Columns.Add("Customer_Name", typeof(string));
  1376. Table1.Columns.Add("Cust_City", typeof(string));
  1377. Table1.Columns.Add("Cust_Country", typeof(string));
  1378. Table1.Columns.Add("Lang", typeof(string));
  1379. Table1.Columns.Add("ProductCode", typeof(string));
  1380. Table1.Columns.Add("Prd_Description", typeof(string));
  1381. Table1.Columns.Add("Qty", typeof(string));
  1382. Table1.Columns.Add("DODate", typeof(string));
  1383. Table1.Columns.Add("CustomerName", typeof(string));
  1384. Table1.Columns.Add("Street", typeof(string));
  1385. Table1.Columns.Add("postalcode", typeof(string));
  1386. Table1.Columns.Add("City", typeof(string));
  1387. Table1.Columns.Add("Country", typeof(string));
  1388. Table1.Columns.Add("DOLineNo", typeof(string));
  1389. Table1.Columns.Add("DONo", typeof(string));
  1390. Table1.Columns.Add("BatchNo", typeof(string));
  1391. //Table1.Columns.Add("Date", typeof(string));
  1392. Table1.Columns.Add("ProductionDate", typeof(string));
  1393. Table1.Columns.Add("Ref", typeof(string));
  1394. Table1.Columns.Add("Remarks", typeof(string));
  1395.  
  1396. StreamReader sr = new StreamReader(File, System.Text.Encoding.Default);
  1397.  
  1398. string strContent = sr.ReadToEnd();
  1399.  
  1400. string[] strArray1 = strContent.Split(new string[] { "\n" }, StringSplitOptions.None);//Delimited the NewLine keycode
  1401. sr.Close();
  1402. sr.Dispose();
  1403. for (int i = 0; i < strArray1.Count(); i++)
  1404. {
  1405. if (strArray1[i].Trim() != "")
  1406. {
  1407. DataRow Row = Table1.NewRow();
  1408. Row.BeginEdit();
  1409.  
  1410. string[] strArray2 = strArray1[i].Split(new string[] { "\"," }, StringSplitOptions.None);//Delimited the comma keycode
  1411.  
  1412. string sorder_No = "";
  1413. string sCustomer_Name = "";
  1414. string sCust_City = "";
  1415. string sCust_Country = "";
  1416. string sLang = "";
  1417. string sProductCode = "";
  1418. string sPrd_Description = "";
  1419. string sQty = "";
  1420. string sDODate = "";
  1421. string sCustomerName = "";
  1422. string sStreet = "";
  1423. string spostalcode = "";
  1424. string sCity = "";
  1425. string sCountry = "";
  1426. string sDOLineNo = "";
  1427. string sDONo = "";
  1428. string sBatchNo = "";
  1429. string sDate = "";
  1430. string sProductionDate = "";
  1431. string sRef = "";
  1432. string sRemarks = "";
  1433.  
  1434. if (strArray2[0].Trim() != "")
  1435. sorder_No = strArray2[0];
  1436. if (strArray2[1].Trim() != "")
  1437. sCustomer_Name = strArray2[1]; ;
  1438. if (strArray2[2].Trim() != "")
  1439. sCust_City = strArray2[2]; ;
  1440. if (strArray2[3].Trim() != "")
  1441. sCust_Country = strArray2[3]; ;
  1442. if (strArray2[4].Trim() != "")
  1443. sLang = strArray2[4];
  1444. if (strArray2[5].Trim() != "")
  1445. sProductCode = strArray2[5]; ;
  1446. if (strArray2[6].Trim() != "")
  1447. sPrd_Description = strArray2[6]; ;
  1448. if (strArray2[7].Trim() != "")
  1449. sQty = strArray2[7];
  1450. if (strArray2[8].Trim() != "")
  1451. sDODate = strArray2[8]; ;
  1452. if (strArray2[9].Trim() != "")
  1453. sCustomerName = strArray2[9];
  1454. if (strArray2[10].Trim() != "")
  1455. sStreet = strArray2[10]; ;
  1456. if (strArray2[11].Trim() != "")
  1457. spostalcode = strArray2[11]; ;
  1458. if (strArray2[12].Trim() != "")
  1459. sCity = strArray2[12];
  1460. if (strArray2[13].Trim() != "")
  1461. sCountry = strArray2[13]; ;
  1462. if (strArray2[14].Trim() != "")
  1463. sDOLineNo = strArray2[14];
  1464. if (strArray2[15].Trim() != "")
  1465. sDONo = strArray2[15];
  1466. if (strArray2[16].Trim() != "")
  1467. sBatchNo = strArray2[16];
  1468.  
  1469.  
  1470. //if (strArray2[17].Trim() != "")
  1471. // sDate = strArray2[17];
  1472.  
  1473.  
  1474. //if (strArray2[18].Trim() != "")
  1475. // sProductionDate = strArray2[18];
  1476. //if (strArray2[19].Trim() != "")
  1477. // sRef = strArray2[19];
  1478. //if (strArray2.Count() == 21)
  1479. //{
  1480. // if (strArray2[20].Trim() != "")
  1481. // sRemarks = strArray2[20];
  1482. //}
  1483.  
  1484.  
  1485.  
  1486. if (strArray2[17].Trim() != "")
  1487. sProductionDate = strArray2[17];
  1488. if (strArray2[18].Trim() != "")
  1489. sRef = strArray2[18];
  1490. if (strArray2[19].Trim() != "")
  1491. sRemarks = strArray2[19];
  1492.  
  1493.  
  1494.  
  1495.  
  1496. Row["order_No"] = sorder_No.Replace("\"", "");
  1497. Row["Customer_Name"] = sCustomer_Name.Replace("\"", "");
  1498. Row["Cust_City"] = sCust_City.Replace("\"", "");
  1499. Row["Cust_Country"] = sCust_Country.Replace("\"", "");
  1500. Row["Lang"] = sLang.Replace("\"", "");
  1501. Row["ProductCode"] = sProductCode.Replace("\"", "");
  1502. Row["Prd_Description"] = sPrd_Description.Replace("\"", "");
  1503. Row["Qty"] = sQty.Replace("\"", "");
  1504. Row["DODate"] = sDODate.Replace("\"", "");
  1505. Row["CustomerName"] = sCustomerName.Replace("\"", "");
  1506. Row["Street"] = sStreet.Replace("\"", "");
  1507. Row["postalcode"] = spostalcode.Replace("\"", "");
  1508. Row["City"] = sCity.Replace("\"", "");
  1509. Row["Country"] = sCountry.Replace("\"", "");
  1510. Row["DOLineNo"] = sDOLineNo.Replace("\"", "");
  1511. Row["DONo"] = sDONo.Replace("\"", "");
  1512. Row["BatchNo"] = sBatchNo.Replace("\"", "");
  1513. //Row["Date"] = sDate.Replace("\"", "");
  1514. Row["ProductionDate"] = sProductionDate.Replace("\"", "");
  1515. Row["Ref"] = sRef.Replace("\"", "");
  1516. Row["Remarks"] = sRemarks.Replace("\"", "");
  1517.  
  1518.  
  1519.  
  1520. Row.EndEdit();
  1521.  
  1522. Table1.Rows.InsertAt(Row, i);
  1523. }
  1524. }
  1525.  
  1526. ds.Tables.Add(Table1);
  1527.  
  1528. return ds;
  1529.  
  1530. }
  1531.  
  1532. public ArrayList GetLangCode4Sentences()
  1533. {
  1534. ArrayList arr = new ArrayList();
  1535. arr.Add("Header");
  1536. arr.Add("VI_VN");
  1537. arr.Add("ID_ID ");
  1538. arr.Add("ENG");
  1539. arr.Add("ZHO");
  1540.  
  1541. arr.Add("CHE");
  1542. arr.Add("DAN");
  1543. arr.Add("DUT");
  1544. arr.Add("EST");
  1545. arr.Add("FIN");
  1546. arr.Add("FRA");
  1547. arr.Add("GER");
  1548. arr.Add("HON");
  1549. arr.Add("ITA");
  1550. arr.Add("NOR");
  1551. arr.Add("POR");
  1552. arr.Add("RUS");
  1553. arr.Add("SPA");
  1554. arr.Add("SWE");
  1555. arr.Add("TUR");
  1556. arr.Add("GRE");
  1557. arr.Add("POL");
  1558. arr.Add("CRO");
  1559. arr.Add("FRA_CA");
  1560. arr.Add("ARA");
  1561. arr.Add("ZHO_TR");
  1562. arr.Add("BUL");
  1563. arr.Add("ENG_US");
  1564. arr.Add("JAP");
  1565. arr.Add("KOR");
  1566. arr.Add("LAT");
  1567. arr.Add("LIT");
  1568. arr.Add("ROM");
  1569. arr.Add("SLO");
  1570. arr.Add("SLV");
  1571. arr.Add("SPA_MX");
  1572. arr.Add("THA");
  1573. arr.Add("BRA");
  1574.  
  1575. return arr;
  1576. }
  1577.  
  1578. public DataSet InitializeSentencesXL(DataSet ds)
  1579. {
  1580. ds = new DataSet();
  1581. ds.Tables.Add("SentenceMaster");
  1582. ds.Tables["SentenceMaster"].Columns.Add("UNIQID");
  1583. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_VI_VN");
  1584. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ID_ID");
  1585. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ENG");
  1586. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ZHO");
  1587. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_CHE");
  1588. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_DAN");
  1589. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_DUT");
  1590. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_EST");
  1591. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_FIN");
  1592. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_FRA");
  1593. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_GER");
  1594. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_HON");
  1595. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ITA");
  1596. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_NOR");
  1597. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_POR");
  1598. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_RUS");
  1599. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_SPA");
  1600. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_SWE");
  1601. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_TUR");
  1602. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_GRE");
  1603. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_POL");
  1604. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_CRO");
  1605. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_FRA_CA");
  1606. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ARA");
  1607. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ZHO_TR");
  1608. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_BUL");
  1609. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ENG_US");
  1610. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_JAP");
  1611. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_KOR");
  1612. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_LAT");
  1613. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_LIT");
  1614. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_ROM");
  1615. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_SLO");
  1616. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_SLV");
  1617. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_SPA_MX");
  1618. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_THA");
  1619. ds.Tables["SentenceMaster"].Columns.Add("MTEXT_BRA");
  1620. return ds;
  1621.  
  1622. }
  1623.  
  1624. private DataSet ReadSentencesXL(DataSet dsXL, string sFilePath, string sSheetName)
  1625. {
  1626. DataSet ds = new DataSet();
  1627. DataTable dt = new DataTable();
  1628. string sFileDesc = "SafetyRiskPhrases";
  1629. string sMailStatus = "Y";
  1630. log.Info("Enter Read " + sFileDesc + " Function - " + Path.GetFileName(sFilePath));
  1631. int rCount = 0;
  1632. bool Chk = false;
  1633. //StreamReader sr = new StreamReader(sFilePath, System.Text.Encoding.Default);
  1634. try
  1635. {
  1636. //string strContent = sr.ReadToEnd();
  1637. GC.Collect();
  1638. var strArray1 = File.ReadAllLines(sFilePath);
  1639. //string[] strArray1 = strContent.Split(new string[] { "\r\n" }, StringSplitOptions.None);//Delimited the NewLine keycode
  1640. //sr.Close();
  1641. //sr.Dispose();
  1642. DataRow Row = dsXL.Tables["SentenceMaster"].NewRow();
  1643. int count = 0;
  1644. for (int i = 0; i < strArray1.Count(); i++)
  1645. {
  1646. if (strArray1[i].Trim() != "")
  1647. {
  1648. Row = dsXL.Tables["SentenceMaster"].NewRow();
  1649. Row.BeginEdit();
  1650. string[] strArray2 = strArray1[i].Split(new string[] { "|" }, StringSplitOptions.None);
  1651. int j = 0;
  1652. foreach (string sVal in strArray2)
  1653. {
  1654. Row[j] = sVal.Replace("\"", "");
  1655. j++;
  1656. if (i == 0)
  1657. count++;
  1658. }
  1659. Row.EndEdit();
  1660.  
  1661. if (i == 0)
  1662. {
  1663.  
  1664. if (Row[0].ToString().ToUpper().Trim() == "UNIQID".ToUpper().Trim() &&
  1665. Row[1].ToString().ToUpper().Trim() == "MTEXT_VI_VN ".ToUpper().Trim() &&
  1666. Row[2].ToString().ToUpper().Trim() == "MTEXT_ID_ID".ToUpper().Trim() &&
  1667. Row[3].ToString().ToUpper().Trim() == "MTEXT_ENG".ToUpper().Trim() &&
  1668. Row[4].ToString().ToUpper().Trim() == "MTEXT_ZHO".ToUpper().Trim() &&
  1669. Row[5].ToString().ToUpper().Trim() == "MTEXT_CHE".ToUpper().Trim() &&
  1670. Row[6].ToString().ToUpper().Trim() == "MTEXT_DAN".ToUpper().Trim() &&
  1671. Row[7].ToString().ToUpper().Trim() == "MTEXT_DUT".ToUpper().Trim() &&
  1672. Row[8].ToString().ToUpper().Trim() == "MTEXT_EST".ToUpper().Trim() &&
  1673. Row[9].ToString().ToUpper().Trim() == "MTEXT_FIN".ToUpper().Trim() &&
  1674. Row[10].ToString().ToUpper().Trim() == "MTEXT_FRA".ToUpper().Trim() &&
  1675. Row[11].ToString().ToUpper().Trim() == "MTEXT_GER".ToUpper().Trim() &&
  1676. Row[12].ToString().ToUpper().Trim() == "MTEXT_HON".ToUpper().Trim() &&
  1677. Row[13].ToString().ToUpper().Trim() == "MTEXT_ITA".ToUpper().Trim() &&
  1678. Row[14].ToString().ToUpper().Trim() == "MTEXT_NOR".ToUpper().Trim() &&
  1679. Row[15].ToString().ToUpper().Trim() == "MTEXT_POR".ToUpper().Trim() &&
  1680. Row[16].ToString().ToUpper().Trim() == "MTEXT_RUS".ToUpper().Trim() &&
  1681. Row[17].ToString().ToUpper().Trim() == "MTEXT_SPA".ToUpper().Trim() &&
  1682. Row[18].ToString().ToUpper().Trim() == "MTEXT_SWE".ToUpper().Trim() &&
  1683. Row[19].ToString().ToUpper().Trim() == "MTEXT_TUR".ToUpper().Trim() &&
  1684. Row[20].ToString().ToUpper().Trim() == "MTEXT_GRE".ToUpper().Trim() &&
  1685. Row[21].ToString().ToUpper().Trim() == "MTEXT_POL".ToUpper().Trim() &&
  1686. Row[22].ToString().ToUpper().Trim() == "MTEXT_CRO".ToUpper().Trim() &&
  1687. Row[23].ToString().ToUpper().Trim() == "MTEXT_FRA_CA".ToUpper().Trim() &&
  1688. Row[24].ToString().ToUpper().Trim() == "MTEXT_ARA".ToUpper().Trim() &&
  1689. Row[25].ToString().ToUpper().Trim() == "MTEXT_ZHO_TR".ToUpper().Trim() &&
  1690. Row[26].ToString().ToUpper().Trim() == "MTEXT_BUL".ToUpper().Trim() &&
  1691. Row[27].ToString().ToUpper().Trim() == "MTEXT_ENG_US".ToUpper().Trim() &&
  1692. Row[28].ToString().ToUpper().Trim() == "MTEXT_JAP".ToUpper().Trim() &&
  1693. Row[29].ToString().ToUpper().Trim() == "MTEXT_KOR".ToUpper().Trim() &&
  1694. Row[30].ToString().ToUpper().Trim() == "MTEXT_LAT".ToUpper().Trim() &&
  1695. Row[31].ToString().ToUpper().Trim() == "MTEXT_LIT".ToUpper().Trim() &&
  1696. Row[32].ToString().ToUpper().Trim() == "MTEXT_ROM".ToUpper().Trim() &&
  1697. Row[33].ToString().ToUpper().Trim() == "MTEXT_SLO".ToUpper().Trim() &&
  1698. Row[34].ToString().ToUpper().Trim() == "MTEXT_SLV".ToUpper().Trim() &&
  1699. Row[35].ToString().ToUpper().Trim() == "MTEXT_SPA_MX".ToUpper().Trim() &&
  1700. Row[36].ToString().ToUpper().Trim() == "MTEXT_THA".ToUpper().Trim() &&
  1701. Row[37].ToString().ToUpper().Trim() == "MTEXT_BRA".ToUpper().Trim())
  1702. {
  1703. Chk = true;
  1704. }
  1705. else
  1706. {
  1707. log.Error("Invalid Header in " + sFileDesc + " File !!!");
  1708. //ErrorAlertToUser("Invalid Header in " + sFileDesc + " File !!!"+sSheetName);
  1709. dsXL.Clear();
  1710. return dsXL;
  1711. }
  1712. }
  1713. //if(i>0)
  1714. //{
  1715. if (Chk && i != 0)
  1716. {
  1717. //dt.Rows.InsertAt(Row, i);
  1718. dsXL.Tables[0].Rows.InsertAt(Row, i);
  1719. }
  1720. }
  1721. //}
  1722. }
  1723. //dsXL.Tables.Add(dt);
  1724. log.Info("Finished Reading " + sFileDesc + " File - " + Path.GetFileName(sFilePath));
  1725. }
  1726.  
  1727. catch (Exception ex)
  1728. {
  1729. log.Fatal(ex.Message);
  1730. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1731. if (sMailStatus == "Y")
  1732. {
  1733. sMailStatus = "N";
  1734. //ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1735. }
  1736. dsXL.Clear();
  1737. return dsXL;
  1738. }
  1739. finally
  1740. {
  1741.  
  1742. }
  1743. log.Info("Leave Read " + sFileDesc + " File Function - " + Path.GetFileName(sFilePath));
  1744. return dsXL;
  1745.  
  1746. }
  1747.  
  1748. public bool SaveSentencesXLFileDetails(DataSet dsXL, string sFilePath, string sSheetName)
  1749. {
  1750. bool bRet = true;
  1751.  
  1752. if (dsXL.Tables.Count > 0)
  1753. {
  1754. try
  1755. {
  1756. using (MasterModule MasMod = new MasterModule(DBMethods.ConnString()))
  1757. {
  1758. BulkInsertDetails(dsXL);
  1759. MasMod.PhrasesUpload();
  1760. GC.Collect();
  1761. }
  1762.  
  1763. }
  1764.  
  1765. catch (Exception ex)
  1766. {
  1767. bRet = false;
  1768. log.Error("SaveSentencesXLFileDetails - ", ex);
  1769. }
  1770.  
  1771. }
  1772. if (bRet == true)
  1773. {
  1774. if (File.Exists(m_BackupPath + "\\" + Path.GetFileName(sFilePath)))
  1775. File.Delete(sFilePath);
  1776. else
  1777. File.Move(sFilePath, m_BackupPath + "\\" + Path.GetFileName(sFilePath));
  1778. }
  1779.  
  1780. return bRet;
  1781.  
  1782. }
  1783.  
  1784. public bool BulkInsertDetails(DataSet dsXL)
  1785. {
  1786. string ConStr = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  1787.  
  1788. if (dsXL.Tables.Count > 0)
  1789. {
  1790. try
  1791. {
  1792. if (dsXL.Tables[0].Rows.Count > 0)
  1793. {
  1794.  
  1795. SqlConnection sqlCon = new SqlConnection(ConStr);
  1796.  
  1797. sqlCon.Open();
  1798.  
  1799. using (SqlBulkCopy copy = new SqlBulkCopy(sqlCon))
  1800. {
  1801. copy.DestinationTableName = "TBL_LPSphrase";
  1802. copy.BatchSize = dsXL.Tables[0].Rows.Count;
  1803. copy.BulkCopyTimeout = 20000;
  1804. copy.WriteToServer(dsXL.Tables[0]);
  1805. log.Info("LPSPhrase Inserted Successfully!!!");
  1806. }
  1807. sqlCon.Close();
  1808. }
  1809. }
  1810.  
  1811. catch (Exception ex)
  1812. {
  1813. log.Error("BulkInsertDetails - ", ex);
  1814. }
  1815.  
  1816. }
  1817. return true;
  1818.  
  1819. }
  1820.  
  1821. #region Import Product Config Details
  1822.  
  1823.  
  1824.  
  1825. private DataSet ReadExcelFile(DataSet dsXL, string sFilePath, string sSheetName)
  1826. {
  1827. string sFileDesc = "Product Config";
  1828. log.Info("Enter Read " + sFileDesc + " Function - " + Path.GetFileName(sFilePath));
  1829. bool bWrkSht = false;
  1830. bool rAdd = true;
  1831. int rCount = 0;
  1832. string sMailStatus = "Y";
  1833. string sCode = "";
  1834. string sGHSSymCode = "";
  1835. string sFlasCE = "";
  1836. string sChNam = "";
  1837. string s1M = "";
  1838. string s2M = "";
  1839. string sSigWord = "";
  1840. string sHazState = "";
  1841. string sPreCauGen = "";
  1842. string sPreCauPre = "";
  1843. string sPreCauRes = "";
  1844. string sPreCauSto = "";
  1845. string sPreCauDis = "";
  1846. string sContains = "";
  1847. string sFreeze = "";
  1848. string sTrans = "";
  1849. string sPG = "";
  1850. string sPSB = "";
  1851. string sMarine = "";
  1852. string sSP640 = "";
  1853. string sUNNo = "";
  1854. string sTox = "";
  1855. string sToxChro = "";
  1856. string sFlame = "";
  1857. string sReactive = "";
  1858. string sClothing = "";
  1859. string sBrand = "";
  1860. string sStirWell = "";
  1861. string sKeepunderShade = "";
  1862. string sKeepunderShademax35degrees = "";
  1863. string sStoreunderNitrogen = "";
  1864. string sStorageCndn = "";
  1865. string sCOUNTRY = "";
  1866. string sHazardInducing = "";
  1867. string sUnder10 = "";
  1868. string sLabelling = "";
  1869. string sSplLabelling = "";
  1870.  
  1871. StreamReader sr = new StreamReader(sFilePath, System.Text.Encoding.Default);
  1872.  
  1873. string sFileExt = sFilePath.Substring(sFilePath.LastIndexOf(".") + 1).ToString();
  1874. sFileExt = sFileExt.ToUpper();
  1875. if (sFileExt.Contains("XLS"))
  1876. {
  1877. try
  1878. {
  1879. log.Info("Start Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1880.  
  1881. string szTempPath = sFilePath;
  1882.  
  1883. string strConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + szTempPath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
  1884. OleDbConnection conn1 = new OleDbConnection(strConn);
  1885.  
  1886. OleDbConnection conn2 = new OleDbConnection(strConn);
  1887.  
  1888. OleDbCommand cmdRC = new OleDbCommand("Select * From [" + sSheetName + "$]", conn2);
  1889. OleDbDataAdapter adpRC = new OleDbDataAdapter(cmdRC);
  1890. DataTable excelRC = new DataTable();
  1891.  
  1892. try
  1893. {
  1894. adpRC.Fill(excelRC);
  1895.  
  1896. }
  1897. catch (Exception ex)
  1898. {
  1899. log.Fatal(ex.Message);
  1900. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1901. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1902. return dsXL;
  1903. }
  1904.  
  1905. int XLRowCount = excelRC.Rows.Count + 1;
  1906. excelRC.Dispose();
  1907. cmdRC.Dispose();
  1908. adpRC.Dispose();
  1909. conn2.Close();
  1910. conn2.Dispose();
  1911.  
  1912. OleDbCommand cmd1 = new OleDbCommand("Select * From [" + sSheetName + "$A1:AC" + XLRowCount.ToString() + "]", conn1);
  1913. OleDbDataAdapter adp = new OleDbDataAdapter(cmd1);
  1914. DataTable excel = new DataTable();
  1915. bWrkSht = false;
  1916.  
  1917. try
  1918. {
  1919. adp.Fill(excel);
  1920. }
  1921. catch (Exception ex)
  1922. {
  1923. log.Fatal(ex.Message);
  1924. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  1925. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  1926. return dsXL;
  1927. }
  1928.  
  1929. bWrkSht = true;
  1930.  
  1931. if (true)
  1932. {
  1933. if (excel.Columns[0].ColumnName.ToUpper().Trim() == "CodeNE".ToUpper().Trim() &&
  1934. excel.Columns[1].ColumnName.ToUpper().Trim() == "CodeNE-STAHL_ISS_GHS_SYMBOL_CODES".ToUpper().Trim() &&
  1935. excel.Columns[2].ColumnName.ToUpper().Trim() == "FlasCE".ToUpper().Trim() &&
  1936. excel.Columns[3].ColumnName.ToUpper().Trim() == "ChNamM_Eng".ToUpper().Trim() &&
  1937. excel.Columns[4].ColumnName.ToUpper().Trim() == "MComer1M_Eng".ToUpper().Trim() &&
  1938. excel.Columns[5].ColumnName.ToUpper().Trim() == "ISS_GHS_EU_SIGNAL_WORD".ToUpper().Trim() &&
  1939. excel.Columns[6].ColumnName.ToUpper().Trim() == "IS_GHS_EU_HAZARD_STAT".ToUpper().Trim() &&
  1940. excel.Columns[7].ColumnName.ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_GEN".ToUpper().Trim() &&
  1941. excel.Columns[8].ColumnName.ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_PREV".ToUpper().Trim() &&
  1942. excel.Columns[9].ColumnName.ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_RESP".ToUpper().Trim() &&
  1943. excel.Columns[10].ColumnName.ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_STOR".ToUpper().Trim() &&
  1944. excel.Columns[11].ColumnName.ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_DISP".ToUpper().Trim() &&
  1945. excel.Columns[12].ColumnName.ToUpper().Trim() == "EU_CONTAINS__TEXT_FORM_FIELD_".ToUpper().Trim() &&
  1946. excel.Columns[13].ColumnName.ToUpper().Trim() == "Keep_from_freezing".ToUpper().Trim() &&
  1947. excel.Columns[14].ColumnName.ToUpper().Trim() == "STAHL_TRANSPORT_CLASS".ToUpper().Trim() &&
  1948. excel.Columns[15].ColumnName.ToUpper().Trim() == "Stahl_PG".ToUpper().Trim() &&
  1949. excel.Columns[16].ColumnName.ToUpper().Trim() == "Stahl_PSN".ToUpper().Trim() &&
  1950. excel.Columns[17].ColumnName.ToUpper().Trim() == "Stahl_UN_Number".ToUpper().Trim() &&
  1951. //excel.Columns[18].ColumnName.ToUpper().Trim() == "IMDG_MARINE_POLLUTANT_YES_NO__ML_OR_UL_DEPENDING_ON_PARAMETER_".ToUpper().Trim() &&
  1952. //excel.Columns[19].ColumnName.ToUpper().Trim() == "Stahl_SP640".ToUpper().Trim() &&
  1953. excel.Columns[18].ColumnName.ToUpper().Trim() == "HMIS_TOXICITY".ToUpper().Trim() &&
  1954. excel.Columns[19].ColumnName.ToUpper().Trim() == "HMIS_Toxicity_Chronic".ToUpper().Trim() &&
  1955. excel.Columns[20].ColumnName.ToUpper().Trim() == "HMIS_Flammability".ToUpper().Trim() &&
  1956. excel.Columns[21].ColumnName.ToUpper().Trim() == "HMIS_Reactivity".ToUpper().Trim() &&
  1957. excel.Columns[22].ColumnName.ToUpper().Trim() == "HMIS_CLOTHING".ToUpper().Trim() &&
  1958. excel.Columns[31].ColumnName.ToUpper().Trim() == "Hazard_Inducing".ToUpper().Trim()
  1959. //excel.Columns[23].ColumnName.ToUpper().Trim() == "HMIS_CLOTHING".ToUpper().Trim()
  1960. //excel.Columns[24].ColumnName.ToUpper().Trim() == "HMIS_CLOTHING".ToUpper().Trim()
  1961. //excel.Columns[25].ColumnName.ToUpper().Trim() == "BRAND".ToUpper().Trim()
  1962.  
  1963. )
  1964. {
  1965. rAdd = true;
  1966.  
  1967. for (int i = 0; i <= excel.Rows.Count - 1; i++)
  1968. {
  1969. sCode = "";
  1970. sGHSSymCode = "";
  1971. sFlasCE = "";
  1972. sChNam = "";
  1973. s1M = "";
  1974. s2M = "";
  1975. sSigWord = "";
  1976. sHazState = "";
  1977. sPreCauGen = "";
  1978. sPreCauPre = "";
  1979. sPreCauRes = "";
  1980. sPreCauSto = "";
  1981. sPreCauDis = "";
  1982. sContains = "";
  1983. sFreeze = "";
  1984. sTrans = "";
  1985. sPG = "";
  1986. sPSB = "";
  1987. sMarine = "";
  1988. sSP640 = "";
  1989. sUNNo = "";
  1990. sTox = "";
  1991. sToxChro = "";
  1992. sFlame = "";
  1993. sReactive = "";
  1994. sBrand = "";
  1995. sStirWell = "";
  1996. sKeepunderShade = "";
  1997. sKeepunderShademax35degrees = "";
  1998. sStoreunderNitrogen = "";
  1999. sStorageCndn = "";
  2000. sCOUNTRY = "";
  2001. sHazardInducing = "";
  2002. sUnder10 = "";
  2003. sLabelling = "";
  2004. sSplLabelling = "";
  2005.  
  2006. rCount = 1;
  2007.  
  2008. if (rCount != -1)
  2009. {
  2010. sCode = excel.Rows[i][0].ToString().Trim().Substring(excel.Rows[i][0].ToString().Trim().LastIndexOf("-") + 1);
  2011.  
  2012. if (sCode == "P86450" || sCode == "P86113")
  2013. {
  2014.  
  2015. }
  2016.  
  2017. sGHSSymCode = GetCodeFromDB("GHSSymCode", excel.Rows[i][1].ToString().Trim(), "Pictograms", "Pict_Id", "Pict_Code", new string[] { " " });
  2018.  
  2019. sFlasCE = excel.Rows[i][2].ToString().Trim();
  2020. sChNam = GetCodeFromDB("ChName", excel.Rows[i][3].ToString().Trim().ToUpper(), "ProductGroup", "PrdGrp_Id", "PrdGrp_Description",
  2021. excel.Rows[i][3].ToString().Trim().Contains(",") ? new string[] { "," } : new string[] { "" });
  2022. s1M = excel.Rows[i][4].ToString().Trim();
  2023. //s2M = excel.Rows[i][5].ToString().Trim();
  2024. if (excel.Rows[i][5].ToString().Trim() != "" && !excel.Rows[i][5].ToString().Trim().ToUpper().Contains("No Signal Word".ToUpper()))
  2025. {
  2026. sSigWord = GetCodeFromDB("SigWord", excel.Rows[i][5].ToString().Trim(), "Codes", "Code_Id", "Code_Description", new string[] { "," });
  2027. }
  2028. else
  2029. {
  2030. sSigWord = "";
  2031. }
  2032.  
  2033. sHazState = GetCodeFromDB("HazState", excel.Rows[i][6].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2034. sPreCauGen = GetCodeFromDB("CauGen", excel.Rows[i][7].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2035. sPreCauPre = GetCodeFromDB("CauPre", excel.Rows[i][8].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2036. sPreCauRes = GetCodeFromDB("CauRes", excel.Rows[i][9].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2037. sPreCauSto = GetCodeFromDB("CauSto", excel.Rows[i][10].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2038. sPreCauDis = GetCodeFromDB("CauDis", excel.Rows[i][11].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2039. sContains = excel.Rows[i][12].ToString().Trim();
  2040. sFreeze = GetCodeFromDB("Pict", excel.Rows[i][13].ToString().Trim(), "Pictograms", "Pict_Id", "Pict_Description",
  2041. excel.Rows[i][13].ToString().Trim().Contains(",") ? new string[] { "," } : new string[] { "" });
  2042. sTrans = excel.Rows[i][14].ToString().Trim();
  2043. sPG = excel.Rows[i][15].ToString().Trim();
  2044. sPSB = excel.Rows[i][16].ToString().Trim();
  2045. //sMarine = excel.Rows[i][18].ToString().Trim().ToUpper() == "MARINE POLLUTANT" ? "Y" : "N";
  2046. //sSP640 = excel.Rows[i][19].ToString().Trim();
  2047. sMarine = "";
  2048. sSP640 = "";
  2049. sUNNo = excel.Rows[i][17].ToString().Trim();
  2050. sTox = excel.Rows[i][18].ToString().Trim();
  2051. sToxChro = excel.Rows[i][19].ToString().Trim();
  2052. sFlame = excel.Rows[i][20].ToString().Trim();
  2053. sReactive = excel.Rows[i][21].ToString().Trim();
  2054. sClothing = excel.Rows[i][22].ToString().Trim();
  2055. sHazardInducing = excel.Rows[i][31].ToString().Trim();
  2056.  
  2057. if (excel.Columns.Count > 23)
  2058. {
  2059. if (excel.Rows[i][23].ToString().Trim() != "")
  2060. {
  2061. sBrand = excel.Rows[i][23].ToString().Trim();
  2062. }
  2063. else
  2064. {
  2065. sBrand = "STAHL";
  2066. }
  2067. }
  2068. else
  2069. {
  2070. sBrand = "STAHL";
  2071. }
  2072.  
  2073. if (excel.Columns.Count > 24)
  2074. {
  2075. if (excel.Rows[i][24].ToString().Trim() != "")
  2076. {
  2077. sStirWell = excel.Rows[i][24].ToString().Trim();
  2078. }
  2079. else
  2080. {
  2081. sStirWell = "";
  2082. }
  2083. }
  2084. if (excel.Columns.Count > 25)
  2085. {
  2086. if (excel.Rows[i][25].ToString().Trim() != "")
  2087. {
  2088. sKeepunderShade = excel.Rows[i][25].ToString().Trim();
  2089. }
  2090. else
  2091. {
  2092. sKeepunderShade = "";
  2093. }
  2094. }
  2095. if (excel.Columns.Count > 26)
  2096. {
  2097. if (excel.Rows[i][26].ToString().Trim() != "")
  2098. {
  2099. sKeepunderShademax35degrees = excel.Rows[i][26].ToString().Trim();
  2100. }
  2101. else
  2102. {
  2103. sKeepunderShademax35degrees = "";
  2104. }
  2105. }
  2106. if (excel.Columns.Count > 27)
  2107. {
  2108. if (excel.Rows[i][27].ToString().Trim() != "")
  2109. {
  2110. sStoreunderNitrogen = excel.Rows[i][27].ToString().Trim();
  2111. }
  2112. else
  2113. {
  2114. sStoreunderNitrogen = "";
  2115. }
  2116. }
  2117. if (excel.Columns.Count > 28)
  2118. {
  2119. if (excel.Rows[i][28].ToString().Trim() != "")
  2120. {
  2121. sUnder10 = excel.Rows[i][28].ToString().Trim();
  2122. }
  2123. else
  2124. {
  2125. sUnder10 = "";
  2126. }
  2127. }
  2128. if (excel.Columns.Count > 29)
  2129. {
  2130. if (excel.Rows[i][29].ToString().Trim() != "")
  2131. {
  2132. sStorageCndn = excel.Rows[i][29].ToString().Trim();
  2133. }
  2134. else
  2135. {
  2136. sStorageCndn = "";
  2137. }
  2138. }
  2139.  
  2140. if (excel.Columns.Count > 30)
  2141. {
  2142. if (excel.Rows[i][30].ToString().Trim() != "")
  2143. {
  2144. sCOUNTRY = excel.Rows[i][30].ToString().Trim();
  2145. }
  2146. else
  2147. {
  2148. sCOUNTRY = "";
  2149. }
  2150. }
  2151. if (excel.Columns.Count > 31)
  2152. {
  2153. if (excel.Rows[i][31].ToString().Trim() != "")
  2154. {
  2155. sHazardInducing = excel.Rows[i][31].ToString().Trim();
  2156. }
  2157. else
  2158. {
  2159. sHazardInducing = "";
  2160. }
  2161. }
  2162. if (excel.Columns.Count > 32)
  2163. {
  2164. if (excel.Rows[i][32].ToString().Trim() != "")
  2165. {
  2166. sLabelling = GetCodeFromDB_Label("Labelling", excel.Rows[i][32].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2167. //sLabelling = Row[31].ToString().Trim();
  2168. }
  2169. else
  2170. {
  2171. sLabelling = "";
  2172. }
  2173. }
  2174. if (excel.Columns.Count > 33)
  2175. {
  2176. if (excel.Rows[i][33].ToString().Trim() != "")
  2177. {
  2178. //sSplLabelling = Row[32].ToString().Trim();
  2179. sSplLabelling = GetCodeFromDB_Label("sSplLabelling", excel.Rows[i][33].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2180. }
  2181. else
  2182. {
  2183. sSplLabelling = "";
  2184. }
  2185. }
  2186. //**************************************Start Find warning Code *******************************
  2187.  
  2188.  
  2189. if (excel.Columns.Count > 24)
  2190. {
  2191. if (excel.Rows[i][24].ToString().Trim() != "")
  2192. {
  2193. sStirWell = excel.Rows[i][24].ToString().Trim();
  2194. if (sStirWell.ToUpper() == "Y")
  2195. {
  2196. string sFreeze1 = GetCodeFromDB1("StirWell", "Pict_Description = 'Stir Well' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2197.  
  2198. if (sFreeze.Contains(sFreeze1) == false)
  2199. {
  2200. sFreeze = sFreeze + "," + sFreeze1;
  2201. }
  2202. }
  2203.  
  2204. }
  2205.  
  2206. }
  2207. if (excel.Columns.Count > 25)
  2208. {
  2209. if (excel.Rows[i][25].ToString().Trim() != "")
  2210. {
  2211.  
  2212. sKeepunderShade = excel.Rows[i][25].ToString().Trim();
  2213. if (sKeepunderShade.ToUpper() == "Y")
  2214. {
  2215. string sFreeze1 = GetCodeFromDB1("KeepunderShade", "Pict_Description = 'Keep under Shade' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2216.  
  2217. if (sFreeze.Contains(sFreeze1) == false)
  2218. {
  2219. sFreeze = sFreeze + "," + sFreeze1;
  2220. }
  2221. }
  2222. }
  2223.  
  2224. }
  2225. if (excel.Columns.Count > 26)
  2226. {
  2227. if (excel.Rows[i][26].ToString().Trim() != "")
  2228. {
  2229. sKeepunderShademax35degrees = excel.Rows[i][26].ToString().Trim();
  2230. if (sKeepunderShademax35degrees.ToUpper() == "Y")
  2231. {
  2232.  
  2233. string sFreeze1 = GetCodeFromDB1("KeepunderShademax35degrees", "Pict_Description = 'Keep under Shade max 35 degrees' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2234. if (sFreeze.Contains(sFreeze1) == false)
  2235. {
  2236. sFreeze = sFreeze + "," + sFreeze1;
  2237. }
  2238. }
  2239. }
  2240. }
  2241. if (excel.Columns.Count > 27)
  2242. {
  2243. if (excel.Rows[i][27].ToString().Trim() != "")
  2244. {
  2245. sStoreunderNitrogen = excel.Rows[i][27].ToString().Trim();
  2246. if (sStoreunderNitrogen.ToUpper() == "Y")
  2247. {
  2248. string sFreeze1 = GetCodeFromDB1("StoreunderNitrogen", "Pict_Description = 'Store Under Nitrogen.' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2249.  
  2250. if (sFreeze.Contains(sFreeze1) == false)
  2251. {
  2252. sFreeze = sFreeze + "," + sFreeze1;
  2253. }
  2254. }
  2255. }
  2256. }
  2257.  
  2258. if (excel.Columns.Count > 29)
  2259. {
  2260. if (excel.Rows[i][29].ToString().Trim() != "")
  2261. {
  2262. sStorageCndn = excel.Rows[i][29].ToString().Trim();
  2263. if (sStorageCndn.ToUpper() == "Y")
  2264. {
  2265. string sFreeze1 = GetCodeFromDB1("KeepunderShadebetween5and45Degrees", "Pict_Description = 'Keep under Shade between 5 and 45 Degrees' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2266.  
  2267. if (sFreeze.Contains(sFreeze1) == false)
  2268. {
  2269. sFreeze = sFreeze + "," + sFreeze1;
  2270. }
  2271. }
  2272. }
  2273. }
  2274.  
  2275. //**************************************End Find warning Code *******************************
  2276.  
  2277. if (sCode != "")
  2278. {
  2279. DataRow Row = dsXL.Tables["tblProdConfig"].NewRow();
  2280. Row.BeginEdit();
  2281. Row["Code"] = sCode;
  2282. Row["GHSSymCode"] = sGHSSymCode;
  2283. Row["FlasCE"] = sFlasCE;
  2284. Row["ChNam"] = sChNam;
  2285. Row["1M"] = s1M;
  2286. //Row["2M"] = s2M;
  2287. Row["SigWord"] = sSigWord;
  2288. Row["HazState"] = sHazState;
  2289. Row["PreCauGen"] = sPreCauGen;
  2290. Row["PreCauPre"] = sPreCauPre;
  2291. Row["PreCauRes"] = sPreCauRes;
  2292. Row["PreCauSto"] = sPreCauSto;
  2293. Row["PreCauDis"] = sPreCauDis;
  2294. Row["Contains"] = sContains;
  2295. Row["Freeze"] = sFreeze;
  2296. Row["Trans"] = sTrans;
  2297. Row["PG"] = sPG;
  2298. Row["PSB"] = sPSB;
  2299. Row["Marine"] = sMarine;
  2300. Row["SP640"] = sSP640;
  2301. Row["UNNo"] = sUNNo;
  2302. Row["Tox"] = sTox;
  2303. Row["ToxChro"] = sToxChro;
  2304. Row["Flame"] = sFlame;
  2305. Row["Reactive"] = sReactive;
  2306. Row["Clothing"] = sClothing;
  2307. Row["Brand"] = sBrand;
  2308. Row["StirWell"] = sStirWell;
  2309. Row["KeepunderShade"] = sKeepunderShade;
  2310. Row["KeepunderShademax35degrees"] = sKeepunderShademax35degrees;
  2311. Row["StoreunderNitrogen"] = sStoreunderNitrogen;
  2312. Row["StorageCndn"] = sStorageCndn;
  2313. Row["COUNTRY"] = sCOUNTRY;
  2314. Row["HazardInducing"] = sHazardInducing;
  2315. Row["Under10"] = sUnder10;
  2316. Row["Labelling"] = sLabelling;
  2317. Row["SplLabelling"] = sSplLabelling;
  2318.  
  2319. Row.EndEdit();
  2320. dsXL.Tables["tblProdConfig"].Rows.Add(Row);
  2321. log.Info("Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - " + sCode + "," +
  2322. sGHSSymCode + "," +
  2323. sFlasCE + "," +
  2324. sChNam + "," +
  2325. s1M + "," +
  2326. //s2M + "," +
  2327. sSigWord + "," +
  2328. sHazState + "," +
  2329. sPreCauGen + "," +
  2330. sPreCauPre + "," +
  2331. sPreCauRes + "," +
  2332. sPreCauSto + "," +
  2333. sPreCauDis + "," +
  2334. sContains + "," +
  2335. sFreeze + "," +
  2336. sTrans + "," +
  2337. sPG + "," +
  2338. sPSB + "," +
  2339. sMarine + "," +
  2340. sSP640 + "," +
  2341. sUNNo + "," +
  2342. sTox + "," +
  2343. sToxChro + "," +
  2344. sFlame + "," +
  2345. sReactive + "," +
  2346. sClothing + "," + sBrand + "," +
  2347. sStirWell + "," +
  2348. sKeepunderShade + "," +
  2349. sKeepunderShademax35degrees + "," +
  2350. sStoreunderNitrogen + "," +
  2351. sStorageCndn + "," +
  2352. sCOUNTRY + "," +
  2353. sHazardInducing + "," +
  2354. sUnder10 + "," +
  2355. sLabelling + "," +
  2356. sSplLabelling);
  2357. sMailContent = "Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - Product Code:" + sCode;
  2358. }
  2359. else
  2360. {
  2361. // log.Error("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  2362. if (sMailStatus == "Y")
  2363. {
  2364. // ErrorAlertToUser("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName+Environment.NewLine+sMailContent);
  2365. sMailContent = "";
  2366. sMailStatus = "N";
  2367.  
  2368. }
  2369. //dsXL.Clear();
  2370. //return dsXL;
  2371. }
  2372. }
  2373. }
  2374.  
  2375. excel.Dispose();
  2376. cmd1.Dispose();
  2377. adp.Dispose();
  2378. conn1.Close();
  2379. conn1.Dispose();
  2380. log.Info("Finished Reading " + sFileDesc + " File - " + Path.GetFileName(sFilePath));
  2381. if (dsXL.Tables.Count > 0)
  2382. {
  2383. if (dsXL.Tables["tblProdConfig"].Rows.Count > 0)
  2384. {
  2385. //GridView1.DataSource = dsXL;
  2386. //GridView1.DataBind();
  2387. }
  2388. else
  2389. {
  2390. //GridView1.DataSource = null;
  2391. }
  2392. }
  2393. else
  2394. {
  2395. //GridView1.DataSource = null;
  2396. }
  2397.  
  2398. }
  2399. else
  2400. {
  2401. // Invalid Header Name in Excel Sheet !!!
  2402. log.Error("Invalid Header in " + sFileDesc + " File !!!");
  2403. ErrorAlertToUser("Invalid Header in " + sFileDesc + " File !!!" + sSheetName);
  2404. dsXL.Clear();
  2405. return dsXL;
  2406. }
  2407. }
  2408.  
  2409. }
  2410. catch (Exception ex)
  2411. {
  2412. log.Fatal(ex.Message);
  2413. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  2414. if (sMailStatus == "Y")
  2415. {
  2416. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  2417. sMailStatus = "N";
  2418. }
  2419. dsXL.Clear();
  2420. return dsXL;
  2421. }
  2422. finally
  2423. {
  2424.  
  2425. }
  2426. log.Info("Leave Read " + sFileDesc + " File Function - " + Path.GetFileName(sFilePath));
  2427. return dsXL;
  2428. }
  2429. else
  2430. {
  2431. try
  2432. {
  2433. sr = File.OpenText(sFilePath);
  2434. string strContent = sr.ReadToEnd();
  2435. string[] strArray1 = strContent.Split(new string[] { "\n" }, StringSplitOptions.None);//Delimited the NewLine keycode
  2436. sr.Close();
  2437. sr.Dispose();
  2438. DataRow Row = dsXL.Tables["tblProdConfig"].NewRow();
  2439. int count = 0;
  2440.  
  2441. for (int i = 0; i < strArray1.Count(); i++)
  2442. {
  2443. if (strArray1[i].Trim() != "")
  2444. {
  2445. Row = dsXL.Tables["tblProdConfig"].NewRow();
  2446. Row.BeginEdit();
  2447. string[] strArray2 = strArray1[i].Split(new string[] { "|" }, StringSplitOptions.None);
  2448. int j = 0;
  2449. foreach (string sVal in strArray2)
  2450. {
  2451. Row[j] = sVal.Replace("\"", "");
  2452. j++;
  2453. if (i == 0)
  2454. count++;
  2455. }
  2456. Row.EndEdit();
  2457. if (i == 0)
  2458. {
  2459. if (Row[0].ToString().ToUpper().Trim() == "CodeNE".ToUpper().Trim() &&
  2460. Row[1].ToString().ToUpper().Trim() == "CodeNE-STAHL_ISS_GHS_SYMBOL_CODES".ToUpper().Trim() &&
  2461. Row[2].ToString().ToUpper().Trim() == "FlasCE".ToUpper().Trim() &&
  2462. Row[3].ToString().ToUpper().Trim() == "ChNamM_Eng".ToUpper().Trim() &&
  2463. Row[4].ToString().ToUpper().Trim() == "MComer1M_Eng".ToUpper().Trim() &&
  2464. Row[5].ToString().ToUpper().Trim() == "ISS_GHS_EU_SIGNAL_WORD".ToUpper().Trim() &&
  2465. Row[6].ToString().ToUpper().Trim() == "IS_GHS_EU_HAZARD_STAT".ToUpper().Trim() &&
  2466. Row[7].ToString().ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_GEN".ToUpper().Trim() &&
  2467. Row[8].ToString().ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_PREV".ToUpper().Trim() &&
  2468. Row[9].ToString().ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_RESP".ToUpper().Trim() &&
  2469. Row[10].ToString().ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_STOR".ToUpper().Trim() &&
  2470. Row[11].ToString().ToUpper().Trim() == "IS_GHS_EU_PRECAUT_STAT_DISP".ToUpper().Trim() &&
  2471. Row[12].ToString().ToUpper().Trim() == "EU_CONTAINS__TEXT_FORM_FIELD_".ToUpper().Trim() &&
  2472. Row[13].ToString().ToUpper().Trim() == "Keep_from_freezing".ToUpper().Trim() &&
  2473. Row[14].ToString().ToUpper().Trim() == "STAHL_TRANSPORT_CLASS".ToUpper().Trim() &&
  2474. Row[15].ToString().ToUpper().Trim() == "Stahl_PG".ToUpper().Trim() &&
  2475. Row[16].ToString().ToUpper().Trim() == "Stahl_PSN".ToUpper().Trim() &&
  2476. Row[17].ToString().ToUpper().Trim() == "Stahl_UN_Number".ToUpper().Trim() &&
  2477. Row[18].ToString().ToUpper().Trim() == "HMIS_TOXICITY".ToUpper().Trim() &&
  2478. Row[19].ToString().ToUpper().Trim() == "HMIS_Toxicity_Chronic".ToUpper().Trim() &&
  2479. Row[20].ToString().ToUpper().Trim() == "HMIS_Flammability".ToUpper().Trim() &&
  2480. Row[21].ToString().ToUpper().Trim() == "HMIS_Reactivity".ToUpper().Trim() &&
  2481. Row[22].ToString().ToUpper().Trim() == "HMIS_CLOTHING".ToUpper().Trim() &&
  2482. Row[31].ToString().ToUpper().Trim() == "Hazard_Inducing".ToUpper().Trim())
  2483. {
  2484. rAdd = true;
  2485. }
  2486. continue;
  2487. }
  2488.  
  2489. //for (int i = 0; i <= excel.Rows.Count - 1; i++)
  2490. //{
  2491. sCode = "";
  2492. sGHSSymCode = "";
  2493. sFlasCE = "";
  2494. sChNam = "";
  2495. s1M = "";
  2496. s2M = "";
  2497. sSigWord = "";
  2498. sHazState = "";
  2499. sPreCauGen = "";
  2500. sPreCauPre = "";
  2501. sPreCauRes = "";
  2502. sPreCauSto = "";
  2503. sPreCauDis = "";
  2504. sContains = "";
  2505. sFreeze = "";
  2506. sTrans = "";
  2507. sPG = "";
  2508. sPSB = "";
  2509. sMarine = "";
  2510. sSP640 = "";
  2511. sUNNo = "";
  2512. sTox = "";
  2513. sToxChro = "";
  2514. sFlame = "";
  2515. sReactive = "";
  2516. sBrand = "";
  2517. sStirWell = "";
  2518. sKeepunderShade = "";
  2519. sKeepunderShademax35degrees = "";
  2520. sStoreunderNitrogen = "";
  2521. sStorageCndn = "";
  2522. sCOUNTRY = "";
  2523. sHazardInducing = "";
  2524. sUnder10 = "";
  2525. sLabelling = "";
  2526. sSplLabelling = "";
  2527.  
  2528. rCount = 1;
  2529.  
  2530. if (rCount != -1)
  2531. {
  2532. sCode = Row[0].ToString().Trim().Substring(Row[0].ToString().Trim().LastIndexOf("-") + 1);
  2533. sGHSSymCode = GetCodeFromDB("GHSSymCode", Row[1].ToString().Trim(), "Pictograms", "Pict_Id", "Pict_Code", new string[] { "," });
  2534. sFlasCE = Row[2].ToString().Trim();
  2535. if (Row[3].ToString().Trim() == "")
  2536. {
  2537. sChNam = "";
  2538. }
  2539. else
  2540. {
  2541. sChNam = GetCodeFromDB("ChName", Row[3].ToString().Trim().ToUpper().Substring(5), "ProductGroup", "PrdGrp_Id", "PrdGrp_Code",
  2542. Row[3].ToString().Trim().Contains(",") ? new string[] { "," } : new string[] { "" });
  2543. }
  2544. s1M = Row[4].ToString().Trim();
  2545. //s2M = Row[5].ToString().Trim();
  2546. if (Row[5].ToString().Trim() != "" && !Row[5].ToString().Trim().ToUpper().Contains("No Signal Word".ToUpper()))
  2547. sSigWord = GetCodeFromDB("SigWord", Row[5].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2548. else
  2549. sSigWord = "";
  2550.  
  2551. sHazState = GetCodeFromDB("HazState", RemoveCust(Row[6].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2552. sPreCauGen = GetCodeFromDB("CauGen", RemoveCust(Row[7].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2553. sPreCauPre = GetCodeFromDB("CauPre", RemoveCust(Row[8].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2554. sPreCauRes = GetCodeFromDB("CauRes", RemoveCust(Row[9].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2555. sPreCauSto = GetCodeFromDB("CauSto", RemoveCust(Row[10].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2556. sPreCauDis = GetCodeFromDB("CauDis", RemoveCust(Row[11].ToString().Trim()), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2557. sContains = Row[12].ToString().Trim();
  2558. sFreeze = GetCodeFromDB("Pict", Row[13].ToString().Trim(), "Pictograms", "Pict_Id", "Pict_Description",
  2559. Row[13].ToString().Trim().Contains(",") ? new string[] { "," } : new string[] { "" });
  2560. sTrans = Row[14].ToString().Trim();
  2561. sPG = Row[15].ToString().Trim();
  2562. sPSB = Row[16].ToString().Trim();
  2563. //sMarine = excel.Rows[i][18].ToString().Trim().ToUpper() == "MARINE POLLUTANT" ? "Y" : "N";
  2564. //sSP640 = excel.Rows[i][19].ToString().Trim();
  2565. sMarine = "";
  2566. sSP640 = "";
  2567. sUNNo = Row[17].ToString().Trim();
  2568. sTox = Row[18].ToString().Trim();
  2569. sToxChro = Row[19].ToString().Trim();
  2570. sFlame = Row[20].ToString().Trim();
  2571. sReactive = Row[21].ToString().Trim();
  2572. sClothing = Row[22].ToString().Trim();
  2573. sHazardInducing = Row[31].ToString().Trim();
  2574. if (count > 23)
  2575. {
  2576. if (Row[23].ToString().Trim() != "")
  2577. sBrand = Row[23].ToString().Trim();
  2578. else
  2579. sBrand = "STAHL";
  2580. }
  2581. else
  2582. {
  2583. sBrand = "STAHL";
  2584. }
  2585.  
  2586. if (count > 24)
  2587. {
  2588. if (Row[24].ToString().Trim() != "")
  2589. {
  2590. sStirWell = Row[24].ToString().Trim();
  2591. }
  2592. else
  2593. {
  2594. sStirWell = "";
  2595. }
  2596. }
  2597. if (count > 25)
  2598. {
  2599. if (Row[25].ToString().Trim() != "")
  2600. {
  2601. sKeepunderShade = Row[25].ToString().Trim();
  2602. }
  2603. else
  2604. {
  2605. sKeepunderShade = "";
  2606. }
  2607. }
  2608. if (count > 26)
  2609. {
  2610. if (Row[26].ToString().Trim() != "")
  2611. {
  2612. sKeepunderShademax35degrees = Row[26].ToString().Trim();
  2613. }
  2614. else
  2615. {
  2616. sKeepunderShademax35degrees = "";
  2617. }
  2618. }
  2619. if (count > 27)
  2620. {
  2621. if (Row[27].ToString().Trim() != "")
  2622. {
  2623. sStoreunderNitrogen = Row[27].ToString().Trim();
  2624. }
  2625. else
  2626. {
  2627. sStoreunderNitrogen = "";
  2628. }
  2629. }
  2630. if (count > 28)
  2631. {
  2632. if (Row[28].ToString().Trim() != "")
  2633. {
  2634. sUnder10 = Row[28].ToString().Trim();
  2635. }
  2636. else
  2637. {
  2638. sUnder10 = "";
  2639. }
  2640. }
  2641. if (count > 29)
  2642. {
  2643. if (Row[29].ToString().Trim() != "")
  2644. {
  2645. sStorageCndn = Row[29].ToString().Trim();
  2646. }
  2647. else
  2648. {
  2649. sStorageCndn = "";
  2650. }
  2651. }
  2652. if (count > 30)
  2653. {
  2654. if (Row[30].ToString().Trim() != "")
  2655. {
  2656. sCOUNTRY = Row[30].ToString().Trim();
  2657. }
  2658. else
  2659. {
  2660. sCOUNTRY = "";
  2661. }
  2662. }
  2663. if (count > 31)
  2664. {
  2665. if (Row[31].ToString().Trim() != "")
  2666. {
  2667. sHazardInducing = Row[31].ToString().Trim();
  2668. }
  2669. else
  2670. {
  2671. sHazardInducing = "";
  2672. }
  2673. }
  2674. if (count > 32)
  2675. {
  2676. if (Row[32].ToString().Trim() != "")
  2677. {
  2678. sLabelling = GetCodeFromDB_Label("Labelling", Row[32].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2679. //sLabelling = Row[31].ToString().Trim();
  2680. }
  2681. else
  2682. {
  2683. sLabelling = "";
  2684. }
  2685. }
  2686. if (count > 33)
  2687. {
  2688. if (Row[33].ToString().Trim() != "")
  2689. {
  2690. //sSplLabelling = Row[32].ToString().Trim();
  2691. sSplLabelling = GetCodeFromDB_Label("sSplLabelling", Row[33].ToString().Trim(), "Codes", "Code_Id", "Code_Code", new string[] { "," });
  2692. }
  2693. else
  2694. {
  2695. sSplLabelling = "";
  2696. }
  2697. }
  2698.  
  2699.  
  2700. //**************************************Start Find warning Code *******************************
  2701.  
  2702.  
  2703. if (count > 24)
  2704. {
  2705. if (Row[24].ToString().Trim() != "")
  2706. {
  2707. sStirWell = Row[24].ToString().Trim();
  2708. if (sStirWell.ToUpper() == "Y")
  2709. {
  2710. string sFreeze1 = GetCodeFromDB1("StirWell", "Pict_Description = 'Stir Well' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2711.  
  2712. if (sFreeze.Contains(sFreeze1) == false)
  2713. {
  2714. sFreeze = sFreeze + "," + sFreeze1;
  2715. }
  2716. }
  2717.  
  2718. }
  2719.  
  2720. }
  2721. if (count > 25)
  2722. {
  2723. if (Row[25].ToString().Trim() != "")
  2724. {
  2725.  
  2726. sKeepunderShade = Row[25].ToString().Trim();
  2727. if (sKeepunderShade.ToUpper() == "Y")
  2728. {
  2729. string sFreeze1 = GetCodeFromDB1("KeepunderShade", "Pict_Description = 'Keep under Shade' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2730.  
  2731. if (sFreeze.Contains(sFreeze1) == false)
  2732. {
  2733. sFreeze = sFreeze + "," + sFreeze1;
  2734. }
  2735. }
  2736. }
  2737. }
  2738. if (count > 26)
  2739. {
  2740. if (Row[26].ToString().Trim() != "")
  2741. {
  2742. sKeepunderShademax35degrees = Row[26].ToString().Trim();
  2743. if (sKeepunderShademax35degrees.ToUpper() == "Y")
  2744. {
  2745.  
  2746. string sFreeze1 = GetCodeFromDB1("KeepunderShademax35degrees", "Pict_Description = 'Keep under Shade max 35 degrees' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2747. if (sFreeze.Contains(sFreeze1) == false)
  2748. {
  2749. sFreeze = sFreeze + "," + sFreeze1;
  2750. }
  2751. }
  2752. }
  2753. }
  2754. if (count > 27)
  2755. {
  2756. if (Row[27].ToString().Trim() != "")
  2757. {
  2758. sStoreunderNitrogen = Row[27].ToString().Trim();
  2759. if (sStoreunderNitrogen.ToUpper() == "Y")
  2760. {
  2761. string sFreeze1 = GetCodeFromDB1("StoreunderNitrogen", "Pict_Description = 'Store Under Nitrogen.' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2762.  
  2763. if (sFreeze.Contains(sFreeze1) == false)
  2764. {
  2765. sFreeze = sFreeze + "," + sFreeze1;
  2766. }
  2767. }
  2768. }
  2769. }
  2770.  
  2771. if (count > 29)
  2772. {
  2773. if (Row[29].ToString().Trim() != "")
  2774. {
  2775. sStorageCndn = Row[29].ToString().Trim();
  2776. if (sStorageCndn.ToUpper() == "Y")
  2777. {
  2778. string sFreeze1 = GetCodeFromDB1("KeepunderShadebetween5and45Degrees", "Pict_Description = 'Keep under Shade between 5 and 45 Degrees' And Pict_ActiveStatus ='Y'", "Pictograms", "Pict_Id", "Pict_Description", new string[] { " " });
  2779.  
  2780. if (sFreeze.Contains(sFreeze1) == false)
  2781. {
  2782. sFreeze = sFreeze + "," + sFreeze1;
  2783. }
  2784. }
  2785. }
  2786. }
  2787. //**************************************End Find warning Code *******************************
  2788.  
  2789. if (sCode != "")
  2790. {
  2791. DataRow RowExport = dsXL.Tables["tblProdConfig"].NewRow();
  2792. RowExport.BeginEdit();
  2793. RowExport["Code"] = sCode;
  2794. RowExport["GHSSymCode"] = sGHSSymCode;
  2795. RowExport["FlasCE"] = sFlasCE;
  2796. RowExport["ChNam"] = sChNam;
  2797. RowExport["1M"] = s1M;
  2798. //RowExport["2M"] = s2M;
  2799. RowExport["SigWord"] = sSigWord;
  2800. RowExport["HazState"] = sHazState;
  2801. RowExport["PreCauGen"] = sPreCauGen;
  2802. RowExport["PreCauPre"] = sPreCauPre;
  2803. RowExport["PreCauRes"] = sPreCauRes;
  2804. RowExport["PreCauSto"] = sPreCauSto;
  2805. RowExport["PreCauDis"] = sPreCauDis;
  2806. RowExport["Contains"] = sContains;
  2807. RowExport["Freeze"] = sFreeze;
  2808. RowExport["Trans"] = sTrans;
  2809. RowExport["PG"] = sPG;
  2810. RowExport["PSB"] = sPSB;
  2811. RowExport["Marine"] = sMarine;
  2812. RowExport["SP640"] = sSP640;
  2813. RowExport["UNNo"] = sUNNo;
  2814. RowExport["Tox"] = sTox;
  2815. RowExport["ToxChro"] = sToxChro;
  2816. RowExport["Flame"] = sFlame;
  2817. RowExport["Reactive"] = sReactive;
  2818. RowExport["Clothing"] = sClothing;
  2819. RowExport["Brand"] = sBrand;
  2820. RowExport["StirWell"] = sStirWell;
  2821. RowExport["KeepunderShade"] = sKeepunderShade;
  2822. RowExport["KeepunderShademax35degrees"] = sKeepunderShademax35degrees;
  2823. RowExport["StoreunderNitrogen"] = sStoreunderNitrogen;
  2824. RowExport["StorageCndn"] = sStorageCndn;
  2825. RowExport["COUNTRY"] = sCOUNTRY;
  2826. RowExport["HazardInducing"] = sHazardInducing;
  2827. RowExport["Under10"] = sUnder10;
  2828. RowExport["Labelling"] = sLabelling;
  2829. RowExport["SplLabelling"] = sSplLabelling;
  2830.  
  2831. RowExport.EndEdit();
  2832. dsXL.Tables["tblProdConfig"].Rows.Add(RowExport);
  2833. log.Info("Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - " + sCode + "," +
  2834. sGHSSymCode + "," +
  2835. sFlasCE + "," +
  2836. sChNam + "," +
  2837. s1M + "," +
  2838. //s2M + "," +
  2839. sSigWord + "," +
  2840. sHazState + "," +
  2841. sPreCauGen + "," +
  2842. sPreCauPre + "," +
  2843. sPreCauRes + "," +
  2844. sPreCauSto + "," +
  2845. sPreCauDis + "," +
  2846. sContains + "," +
  2847. sFreeze + "," +
  2848. sTrans + "," +
  2849. sPG + "," +
  2850. sPSB + "," +
  2851. sMarine + "," +
  2852. sSP640 + "," +
  2853. sUNNo + "," +
  2854. sTox + "," +
  2855. sToxChro + "," +
  2856. sFlame + "," +
  2857. sReactive + "," +
  2858. sClothing + "," + sBrand + "," +
  2859. sStirWell + "," +
  2860. sKeepunderShade + "," +
  2861. sKeepunderShademax35degrees + "," +
  2862. sStoreunderNitrogen + "," +
  2863. sCOUNTRY + "," +
  2864. sHazardInducing + "," +
  2865. sUnder10 + "," +
  2866. sLabelling + "," +
  2867. sSplLabelling
  2868. );
  2869. sMailContent = "Read " + sFileDesc + " - Record No : " + (i + 1).ToString() + " - Product Code:" + sCode;
  2870. }
  2871. else
  2872. {
  2873. // log.Error("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  2874. if (sMailStatus == "Y")
  2875. {
  2876. // ErrorAlertToUser("INVALID RECORD IN " + sFileDesc.ToUpper() + " FILE - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName+Environment.NewLine+sMailContent);
  2877. sMailContent = "";
  2878. sMailStatus = "N";
  2879.  
  2880. }
  2881. //dsXL.Clear();
  2882. //return dsXL;
  2883. }
  2884. }
  2885.  
  2886.  
  2887.  
  2888. log.Info("Finished Reading " + sFileDesc + " File - " + Path.GetFileName(sFilePath));
  2889. if (dsXL.Tables.Count > 0)
  2890. {
  2891. if (dsXL.Tables["tblProdConfig"].Rows.Count > 0)
  2892. {
  2893. //GridView1.DataSource = dsXL;
  2894. //GridView1.DataBind();
  2895. }
  2896. else
  2897. {
  2898. //GridView1.DataSource = null;
  2899. }
  2900. }
  2901. else
  2902. {
  2903. //GridView1.DataSource = null;
  2904. }
  2905.  
  2906. }
  2907. //else
  2908. //{
  2909. // // Invalid Header Name in Excel Sheet !!!
  2910. // log.Error("Invalid Header in " + sFileDesc + " File !!!");
  2911. // ErrorAlertToUser("Invalid Header in " + sFileDesc + " File !!!" + sSheetName);
  2912. // //dsXL.Clear();
  2913. // return dsXL;
  2914. //}
  2915. }
  2916. }
  2917. catch (Exception ex)
  2918. {
  2919. log.Fatal(ex.Message);
  2920. log.Error("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName);
  2921. if (sMailStatus == "Y")
  2922. {
  2923. ErrorAlertToUser("Error Reading " + sFileDesc + " File - " + sFilePath + ", " + Path.GetFileName(sFilePath) + " , SheetName : " + sSheetName + " " + ex.Message);
  2924. sMailStatus = "N";
  2925. }
  2926. dsXL.Clear();
  2927. return dsXL;
  2928. }
  2929. finally
  2930. {
  2931. GC.Collect();
  2932. }
  2933. log.Info("Leave Read " + sFileDesc + " File Function - " + Path.GetFileName(sFilePath));
  2934. return dsXL;
  2935. }
  2936. }
  2937.  
  2938. public DataSet InitializeXLDataset(DataSet ds)
  2939. {
  2940. ds = new DataSet();
  2941. ds.Tables.Add("tblProdConfig");
  2942. ds.Tables["tblProdConfig"].Columns.Add("Code");
  2943. ds.Tables["tblProdConfig"].Columns.Add("GHSSymCode");
  2944. ds.Tables["tblProdConfig"].Columns.Add("FlasCE");
  2945. ds.Tables["tblProdConfig"].Columns.Add("ChNam");
  2946. ds.Tables["tblProdConfig"].Columns.Add("1M");
  2947. //ds.Tables["tblProdConfig"].Columns.Add("2M");
  2948. ds.Tables["tblProdConfig"].Columns.Add("SigWord");
  2949. ds.Tables["tblProdConfig"].Columns.Add("HazState");
  2950. ds.Tables["tblProdConfig"].Columns.Add("PreCauGen");
  2951. ds.Tables["tblProdConfig"].Columns.Add("PreCauPre");
  2952. ds.Tables["tblProdConfig"].Columns.Add("PreCauRes");
  2953. ds.Tables["tblProdConfig"].Columns.Add("PreCauSto");
  2954. ds.Tables["tblProdConfig"].Columns.Add("PreCauDis");
  2955. ds.Tables["tblProdConfig"].Columns.Add("Contains");
  2956. ds.Tables["tblProdConfig"].Columns.Add("Freeze");
  2957. ds.Tables["tblProdConfig"].Columns.Add("Trans");
  2958. ds.Tables["tblProdConfig"].Columns.Add("PG");
  2959. ds.Tables["tblProdConfig"].Columns.Add("PSB");
  2960. ds.Tables["tblProdConfig"].Columns.Add("Marine");
  2961. ds.Tables["tblProdConfig"].Columns.Add("SP640");
  2962. ds.Tables["tblProdConfig"].Columns.Add("UNNo");
  2963. ds.Tables["tblProdConfig"].Columns.Add("Tox");
  2964. ds.Tables["tblProdConfig"].Columns.Add("ToxChro");
  2965. ds.Tables["tblProdConfig"].Columns.Add("Flame");
  2966. ds.Tables["tblProdConfig"].Columns.Add("Reactive");
  2967. ds.Tables["tblProdConfig"].Columns.Add("Clothing");
  2968. ds.Tables["tblProdConfig"].Columns.Add("Brand");
  2969. ds.Tables["tblProdConfig"].Columns.Add("StirWell");
  2970. ds.Tables["tblProdConfig"].Columns.Add("KeepunderShade");
  2971. ds.Tables["tblProdConfig"].Columns.Add("KeepunderShademax35degrees");
  2972. ds.Tables["tblProdConfig"].Columns.Add("StoreunderNitrogen");
  2973. ds.Tables["tblProdConfig"].Columns.Add("StorageCndn");
  2974. ds.Tables["tblProdConfig"].Columns.Add("COUNTRY");
  2975. ds.Tables["tblProdConfig"].Columns.Add("HazardInducing");
  2976. ds.Tables["tblProdConfig"].Columns.Add("Under10");
  2977. ds.Tables["tblProdConfig"].Columns.Add("Labelling");
  2978. ds.Tables["tblProdConfig"].Columns.Add("SplLabelling");
  2979.  
  2980. return ds;
  2981. }
  2982.  
  2983. public string GetCodeFromDB_Label(string sColName, string sVal, string sTableName, string sRetFieldName, string sCondFieldName, string[] sSep)
  2984. {
  2985. string sRet = "";
  2986.  
  2987. try
  2988. {
  2989. if (sVal != "")
  2990. {
  2991. string[] sArr = sVal.Split(sSep, StringSplitOptions.RemoveEmptyEntries);
  2992. if (sArr.Length > 0)
  2993. {
  2994. for (int i = 0; i < sArr.Length; i++)
  2995. {
  2996. string sTmp = "";
  2997. string sCondFieldValue = RemoveCust(sArr[i].ToString().Trim()) == string.Empty ? sArr[i].ToString().Trim()
  2998. : RemoveCust(sArr[i].ToString().Trim());
  2999.  
  3000. sTmp = getFieldValue(sTableName, sCondFieldName + "='" + sCondFieldValue + "'", sRetFieldName);
  3001.  
  3002. if (sTmp == "")
  3003. {
  3004. log.Info("Value not found for " + sColName + "-" + sTableName + "," + sCondFieldName + "='" + sArr[i].ToString().Trim() + "'," + sRetFieldName);
  3005. }
  3006. else
  3007. {
  3008. sRet = sRet + sTmp + ",";
  3009. }
  3010. }
  3011. }
  3012. }
  3013. }
  3014. catch (Exception ex)
  3015. {
  3016. sRet = "";
  3017. }
  3018.  
  3019. return sRet.Contains(",") ? sRet.Substring(0, sRet.Length - 1) : sRet;
  3020. }
  3021.  
  3022. public string GetCodeFromDB(string sColName, string sVal, string sTableName, string sRetFieldName, string sCondFieldName, string[] sSep)
  3023. {
  3024. string sRet = "";
  3025.  
  3026. try
  3027. {
  3028. if (sVal != "")
  3029. {
  3030. string[] sArr = sVal.Split(sSep, StringSplitOptions.RemoveEmptyEntries);
  3031. if (sArr.Length > 0)
  3032. {
  3033. for (int i = 0; i < sArr.Length; i++)
  3034. {
  3035. string sGVal = sArr[i].ToString().Trim();
  3036. string sGCode = "";
  3037. if (sGVal == "CUST-ATG2GC:IKOE:7PT")
  3038. sGCode = "GHS01";
  3039. else if (sGVal == "CUST-ATG26V:DV9O:7PT")
  3040. sGCode = "GHS02";
  3041. else if (sGVal == "CUST-ATG270:DV9O:7PT")
  3042. sGCode = "GHS03";
  3043. else if (sGVal == "CUST-ATG271:DV9O:7PT")
  3044. sGCode = "GHS04";
  3045. else if (sGVal == "CUST-ATG272:DV9O:7PT")
  3046. sGCode = "GHS05";
  3047. else if (sGVal == "CUST-ATG273:DV9O:7PT")
  3048. sGCode = "GHS06";
  3049. else if (sGVal == "CUST-ATG274:DV9P:7PT")
  3050. sGCode = "GHS07";
  3051. else if (sGVal == "CUST-ATG275:DV9P:7PT")
  3052. sGCode = "GHS08";
  3053. else if (sGVal == "CUST-ATG276:DV9P:7PT")
  3054. sGCode = "GHS09";
  3055. else if (sGVal == "CUST-ATG237:DUNH:7PT")
  3056. sGCode = "ATG237:DUNH:7PT";
  3057. else if (sGVal == "CUST-ATG238:DUNH:7PT")
  3058. sGCode = "ATG238:DUNH:7PT";
  3059. else if (sGVal == "CUST-AT8QS:4758:7PT")
  3060. sGCode = "AT8QS:4758:7PT";
  3061. else
  3062. sGCode = sGVal;
  3063.  
  3064. string sTmp = "";
  3065. if (sTableName == "ProductGroup")
  3066. {
  3067. string sValC = "";
  3068. using (MasterModule MasMod = new MasterModule(DBMethods.ConnString()))
  3069. {
  3070. DataSet dsp = MasMod.SelectPrdGrpCount(sGCode);
  3071. sValC = dsp.Tables[0].Rows[0]["Column1"].ToString();
  3072. }
  3073. if (sValC == "0")
  3074. {
  3075. using (MasterModule MasMod = new MasterModule(DBMethods.ConnString()))
  3076. {
  3077. MasMod.PrdGrpInsert(sGCode);
  3078. }
  3079.  
  3080. if (sGCode == "Y")
  3081. {
  3082. sTmp = getFieldValue(sTableName, sCondFieldName + " Like '" + "Keep From Freezing%' ", sRetFieldName);
  3083. }
  3084. else
  3085. {
  3086. sGCode = RemoveCust(sGCode) == string.Empty ? sGCode : RemoveCust(sGCode);
  3087. sTmp = getFieldValue(sTableName, sCondFieldName + "='" + sGCode + "'", sRetFieldName);
  3088. }
  3089. }
  3090. else
  3091. {
  3092. if (sGCode == "Y")
  3093. {
  3094. sTmp = getFieldValue(sTableName, sCondFieldName + " Like '" + "Keep From Freezing%' ", sRetFieldName);
  3095. }
  3096. else
  3097. {
  3098. sGCode = RemoveCust(sGCode) == string.Empty ? sGCode : RemoveCust(sGCode);
  3099. sTmp = getFieldValue(sTableName, sCondFieldName + "='" + sGCode + "'", sRetFieldName);
  3100. }
  3101.  
  3102. }
  3103. }
  3104. else
  3105. {
  3106. if (sGCode == "Y")
  3107. {
  3108. sTmp = getFieldValue(sTableName, sCondFieldName + " Like '" + "Keep From Freezing%' ", sRetFieldName);
  3109. }
  3110. else
  3111. {
  3112. sGCode = RemoveCust(sGCode) == string.Empty ? sGCode : RemoveCust(sGCode);
  3113. sTmp = getFieldValue(sTableName, sCondFieldName + "='" + sGCode + "'", sRetFieldName);
  3114. }
  3115. }
  3116. if (sTmp == "")
  3117. {
  3118. log.Info("Value not found for " + sColName + "-" + sTableName + "," + sCondFieldName + "='" + sGCode + "'," + sRetFieldName);
  3119. }
  3120. else
  3121. {
  3122. sRet = sRet + sTmp + ",";
  3123. }
  3124. }
  3125. }
  3126. }
  3127. }
  3128. catch (Exception ex)
  3129. {
  3130. sRet = "";
  3131. }
  3132.  
  3133. return sRet.Contains(",") ? sRet.Substring(0, sRet.Length - 1) : sRet;
  3134. }
  3135.  
  3136. //public string RemoveCust(string strVal)
  3137. //{
  3138. // string strRet = "";
  3139. // if (strVal != "")
  3140. // {
  3141. // string[] strArr = strVal.Split(',');
  3142. // foreach (string sSplit in strArr)
  3143. // {
  3144. // if (sSplit.Substring(0, 5) == "CUST-")
  3145. // {
  3146. // if (strRet == "")
  3147. // strRet = sSplit.Substring(5, sSplit.Length - 5);
  3148. // else
  3149. // strRet = strRet + "," + sSplit.Substring(5, sSplit.Length - 5);
  3150. // }
  3151. // }
  3152. // if (strRet.Substring(strRet.Length - 1, 1) == ",")
  3153. // strRet = strRet.Substring(0, strRet.Length - 2);
  3154. // }
  3155. // return strRet;
  3156. //}
  3157. public string RemoveCust(string strVal)
  3158. {
  3159.  
  3160. string strRet = string.Empty;
  3161. if (strVal != string.Empty)
  3162. {
  3163. string[] strArr = strVal.Split(',');
  3164. if (strArr.Length > 0)
  3165. {
  3166. foreach (string sSplit in strArr)
  3167. {
  3168. if (sSplit != string.Empty)
  3169. {
  3170. if (sSplit.Length >= 5)
  3171. {
  3172. if (sSplit.Substring(0, 5) == "CUST-")
  3173. {
  3174. if (strRet == "")
  3175. strRet = sSplit.Substring(5, sSplit.Length - 5);
  3176. else
  3177. strRet = strRet + "," + sSplit.Substring(5, sSplit.Length - 5);
  3178. }
  3179. }
  3180. }
  3181. }
  3182. if (strRet != string.Empty && strRet.Substring(strRet.Length - 1, 1) == ",")
  3183. {
  3184. strRet = strRet.Substring(0, strRet.Length - 2);
  3185. }
  3186. }
  3187. }
  3188. return strRet;
  3189. }
  3190.  
  3191.  
  3192.  
  3193.  
  3194. public string GetCodeFromDB1(string sColName, string sVal, string sTableName, string sRetFieldName, string sCondFieldName, string[] sSep)
  3195. {
  3196. string sRet = "";
  3197.  
  3198. try
  3199. {
  3200. if (sVal != "")
  3201. {
  3202.  
  3203.  
  3204. string sTmp = "";
  3205. sTmp = getFieldValue(sTableName, sVal.ToString().Trim(), sRetFieldName);
  3206. if (sTmp == "")
  3207. {
  3208. log.Error("Value not found for " + sColName + "-" + sTableName + "," + sCondFieldName + "='" + sVal.ToString().Trim() + "'," + sRetFieldName);
  3209. }
  3210. else
  3211. {
  3212. sRet = sRet + sTmp + ",";
  3213. }
  3214.  
  3215.  
  3216. }
  3217. }
  3218. catch (Exception ex)
  3219. {
  3220. sRet = "";
  3221. }
  3222.  
  3223. return sRet.Contains(",") ? sRet.Substring(0, sRet.Length - 1) : sRet;
  3224. }
  3225.  
  3226. public static string getFieldValue(string TblName, string Condition, string returnField)
  3227. {
  3228. string retValue = "";
  3229. string Qry = "Select " + returnField + " from " + TblName + " where " + Condition;
  3230. string ConStr = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  3231. DataTable dt = new DataTable();
  3232. DataRow dr;
  3233.  
  3234. using (SqlConnection cn = new SqlConnection(ConStr))
  3235. {
  3236. if (Qry != "")
  3237. {
  3238. cn.Open();
  3239. SqlDataAdapter objSda = new SqlDataAdapter(Qry, cn);
  3240. objSda.SelectCommand.CommandTimeout = 0;
  3241. objSda.Fill(dt);
  3242.  
  3243. if (dt.Rows.Count > 0)
  3244. {
  3245. dr = dt.Rows[0];
  3246. retValue = dr[0].ToString();
  3247. }
  3248. }
  3249. }
  3250. return retValue;
  3251. }
  3252.  
  3253. //public bool SaveProductGroupDetails(DataSet ds,string sFilePath,string sFileName)
  3254. //{
  3255. //string PrdGrpLang = "";
  3256. //string subHeader = "";
  3257. //string PrdGrpDescription = "";
  3258. //string PrdGrpCode = "";
  3259. //int PHead = 0;
  3260. //if (dsXL.Tables.Count > 0)
  3261. //{
  3262. // try
  3263. // {
  3264. // if (dsXL.Tables["ProductMaster"].Rows.Count > 0)
  3265. // {
  3266. // using (MasterModule MasMod = new MasterModule(General.ConnString()))
  3267. // {
  3268. // for (int i = 0; i <= dsXL.Tables["ProductMaster"].Rows.Count - 1; i++)
  3269. // {
  3270. // PrdGrpCode = dsXL.Tables["ProductMaster"].Rows[i][0].ToString().Trim();
  3271. // for (int j = 1; j <= dsXL.Tables["ProductMaster"].Columns.Count - 3; j++)
  3272. // {
  3273. // //Header = dsXL.Tables["ProductMaster"].Rows[0][j].ToString().Trim();
  3274. // PrdGrpLang = ArrHead[j].ToString();
  3275. // PrdGrpDescription = dsXL.Tables["ProductMaster"].Rows[i][j].ToString().Trim();
  3276. // if (PrdGrpDescription == "")
  3277. // {
  3278. // PrdGrpDescription = "Nil";
  3279. // MasMod.InsertXLtoPrdtDetailsMaster(PrdGrpCode, PrdGrpLang, PrdGrpDescription);
  3280. // }
  3281. // else
  3282. // {
  3283. // MasMod.InsertXLtoPrdtDetailsMaster(PrdGrpCode, PrdGrpLang, PrdGrpDescription);
  3284. // }
  3285. // }
  3286. // }
  3287. // resultlbl2.Text = "Sent Data to Server Successfully";
  3288. // }
  3289. // }
  3290. // }
  3291. // catch (Exception ex)
  3292. // {
  3293.  
  3294. // }
  3295. //}
  3296.  
  3297. public bool saveProdConfigDetails(DataSet ds, string sFilePath, string sFileName)
  3298. {
  3299. bool bRet = false;
  3300. int iInserted = 0;
  3301. int iIupdated = 0;
  3302. int iSkipped = 0;
  3303. int iTotalCount = 0;
  3304. iTotalCount = Convert.ToInt32(ds.Tables[0].Rows.Count);
  3305. try
  3306. {
  3307. int userid = 1; //Convert.ToInt16(Session["UId"].ToString());
  3308. string errordesc = "";
  3309. if (ds.Tables.Count > 0)
  3310. {
  3311. if (ds.Tables[0].Rows.Count > 0)
  3312. {
  3313. for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
  3314. {
  3315. int PrdGrpId = 0;
  3316.  
  3317. DataRow dr = ds.Tables[0].Rows[k];
  3318.  
  3319. string sStirWell = getFieldValue("Pictograms", "Pict_Code = 'STIR'", "Pict_Id");
  3320. string code = dr["GHSSymCode"].ToString();
  3321. string pict = dr["Freeze"].ToString();
  3322. //this is newly added code by selva on july 27 2012 0704PMSGT....
  3323. //this code is used to avoid auto update of stirwell to all products....
  3324. //dorai asked us(integra) set stirwell auto update in all products on september 12 2012.
  3325. // dorai asked us(integra) to remove stirwell auto update in LPS filewatcher.
  3326. // for more details check bala sir mail....
  3327. string sFlash = dr["FlasCE"].ToString().Trim();
  3328. if (sFlash.Contains("°C"))
  3329. {
  3330. sFlash = sFlash.Substring(0, sFlash.Length - 3);
  3331. }
  3332.  
  3333. byte[] asciiBytes = Encoding.ASCII.GetBytes(sFlash);
  3334. string sCheckStirwel = "";
  3335. sCheckStirwel = Convert.ToString(dr["StirWell"]);
  3336. if (sCheckStirwel != "")
  3337. {
  3338. pict = pict + "," + sStirWell;
  3339. }
  3340. string sStrCond = getFieldValue("Pictograms", "Pict_Code = 'StrCond'", "Pict_Id");
  3341. string[] sValCheck = pict.Split(',');
  3342. int iVal = Array.IndexOf(sValCheck, sStrCond);
  3343. if (dr["Under10"].ToString() == "X")
  3344. {
  3345. if (iVal < 0)
  3346. {
  3347. pict = pict + "," + sStrCond;
  3348. }
  3349. }
  3350. else
  3351. {
  3352. if (iVal > 0)
  3353. {
  3354. string sPicttmp = "";
  3355. foreach (string sId in sValCheck)
  3356. {
  3357. if (sId != sStrCond)
  3358. {
  3359. if (sPicttmp.Contains(","))
  3360. sPicttmp = sPicttmp + sId + ",";
  3361. else
  3362. sPicttmp = sId + ",";
  3363. }
  3364. }
  3365. if (sPicttmp.Contains(","))
  3366. {
  3367. if (sPicttmp.Substring(sPicttmp.Length - 1, 1) == ",")
  3368. sPicttmp = sPicttmp.Substring(0, sPicttmp.Length - 1);
  3369. }
  3370.  
  3371. pict = sPicttmp;
  3372. }
  3373. }
  3374.  
  3375. //if (sStirWell != pict)
  3376. //{
  3377. // if (pict != "")
  3378. // {
  3379. // if (pict.Contains(sStirWell) == false)
  3380. // {
  3381. // pict = pict + "," + sStirWell;
  3382. // }
  3383. // }
  3384. // else
  3385. // {
  3386. // pict = sStirWell;
  3387. // }
  3388. //}
  3389.  
  3390. string SRP = "";
  3391.  
  3392. string sTradeMark = "";
  3393. if (dr["1M"].ToString().Trim() != "")
  3394. sTradeMark = getFieldValue("TradeMark", "TrdMrk_Description Like '%" + dr["1M"].ToString().Replace("'", "").Trim() + "%'", "TrdMrk_Id");
  3395.  
  3396. //if (dr["SigWord"].ToString() != "") SRP += dr["SigWord"].ToString() + ",";
  3397. if (dr["HazState"].ToString() != "") SRP += dr["HazState"].ToString() + ",";
  3398. if (dr["PreCauGen"].ToString() != "") SRP += dr["PreCauGen"].ToString() + ",";
  3399. if (dr["PreCauPre"].ToString() != "") SRP += dr["PreCauPre"].ToString() + ",";
  3400. if (dr["PreCauRes"].ToString() != "") SRP += dr["PreCauRes"].ToString() + ",";
  3401. if (dr["PreCauSto"].ToString() != "") SRP += dr["PreCauSto"].ToString() + ",";
  3402. if (dr["PreCauDis"].ToString() != "") SRP += dr["PreCauDis"].ToString() + ",";
  3403.  
  3404. if (SRP.Contains(","))
  3405. SRP = SRP.Substring(0, SRP.Length - 1);
  3406.  
  3407. if (dr["ChNam"].ToString() != "")
  3408. {
  3409. int iId = 0;
  3410. if (int.TryParse(dr["ChNam"].ToString(), out iId))
  3411. {
  3412. PrdGrpId = iId;
  3413. }
  3414. else
  3415. {
  3416. PrdGrpId = 0;
  3417. LogWriter logWriter = new LogWriter("Invalid record in the product - " + dr["Code"].ToString().Trim() + ". Record is - " + dr["ChNam"].ToString());
  3418. //logWriter();
  3419. log.Info("Invalid record in the product - " + dr["Code"].ToString().Trim() + ". Record is - " + dr["ChNam"].ToString());
  3420. }
  3421. //PrdGrpId = int.Parse(dr["ChNam"].ToString());
  3422. }
  3423. else
  3424. {
  3425. PrdGrpId = 0;
  3426. }
  3427.  
  3428. string PrdType = "";
  3429. PrdType = dr["Brand"].ToString().ToString();
  3430. string MPolutant = dr["Marine"].ToString();
  3431. string sLocation = "";
  3432. sLocation = Convert.ToString(dr["COUNTRY"]);
  3433. if (sLocation == "")
  3434. {
  3435. sLocation = m_Location;
  3436. }
  3437.  
  3438. if (sLocation.Trim() != "REG_WORLD" && sLocation.Trim() != "REG_NA")
  3439. {
  3440. using (MasterModule msmod = new MasterModule(ConfigurationSettings.AppSettings["CONSTR"].ToString()))
  3441. {
  3442. string sSplitVal = "";
  3443. if (sLocation.Trim() == "REG_EU_EXT")
  3444. {
  3445. sSplitVal = "SG,ME,IN,ES,EU";
  3446. }
  3447. else
  3448. {
  3449. sSplitVal = sLocation;
  3450. }
  3451.  
  3452. string[] sCountryList = sSplitVal.Split(',');
  3453. foreach (string sCountry in sCountryList)
  3454. {
  3455. if (getFieldValue("ProductConfig", " PrdCfg_SPCCode='" + dr["Code"].ToString().Trim() + "' AND PrdCfg_Country='" + sCountry + "'", "PrdCfg_Id") == "")
  3456. {
  3457. msmod.ProductConfigSave
  3458. (
  3459. dr["Code"].ToString().Trim(),
  3460. dr["1M"].ToString().Trim().Replace("Â", ""),
  3461. PrdGrpId,
  3462. dr["PG"].ToString().Trim(),
  3463. "",
  3464. "",
  3465. dr["UNNo"].ToString().Trim(),
  3466. dr["PSB"].ToString().Trim(),
  3467. sFlash,
  3468. dr["Trans"].ToString().Trim(),
  3469. "",
  3470. sTradeMark,
  3471. "",
  3472. "",
  3473. "",
  3474. "",
  3475. dr["Flame"].ToString().Trim(),
  3476. dr["Reactive"].ToString().Trim(),
  3477. "",
  3478. "",
  3479. "",
  3480. MPolutant,
  3481. "",
  3482. "",
  3483. code,
  3484. pict,
  3485. userid,
  3486. out errordesc,
  3487. PrdType,
  3488. SRP,
  3489. JoinImagesPict(pict),
  3490. JoinImagesPict(code),
  3491. dr["Contains"].ToString().Trim(),
  3492. dr["PG"].ToString().Trim(),
  3493. dr["PSB"].ToString().Trim(),
  3494. dr["SP640"].ToString().Trim(),
  3495. dr["Tox"].ToString().Trim(),
  3496. dr["ToxChro"].ToString().Trim(),
  3497. dr["Clothing"].ToString().Trim(),
  3498. clsColorMatrix.MakeGrayscale3(JoinImagesPict(pict)),
  3499. clsColorMatrix.MakeGrayscale3(JoinImagesPict(code)),
  3500. dr["SigWord"].ToString(),
  3501. dr["StirWell"].ToString(),
  3502. dr["KeepunderShade"].ToString(),
  3503. dr["KeepunderShademax35degrees"].ToString(),
  3504. dr["StoreunderNitrogen"].ToString(),
  3505. dr["StorageCndn"].ToString(),
  3506. sCountry,
  3507. dr["HazardInducing"].ToString(),
  3508. dr["Under10"].ToString(),
  3509. dr["Labelling"].ToString(),
  3510. dr["SplLabelling"].ToString(),
  3511. dr["HazState"].ToString(),
  3512. dr["PreCauGen"].ToString(),
  3513. dr["PreCauPre"].ToString(),
  3514. dr["PreCauRes"].ToString(),
  3515. dr["PreCauSto"].ToString(),
  3516. dr["PreCauDis"].ToString()
  3517. );
  3518. if (errordesc == "" || errordesc == null || errordesc == "0")
  3519. {
  3520. log.Info("Record Inserted Successfully - Rec No " + (k + 1).ToString() + " - " + dr["Code"].ToString().Trim());
  3521. bRet = true;
  3522. iInserted = iInserted + 1;
  3523. }
  3524. else
  3525. {
  3526. log.Error("Error Inserting Record - " + dr["Code"].ToString().Trim());
  3527. bRet = false;
  3528. return false;
  3529. }
  3530. }
  3531. else
  3532. {
  3533. int PrdCfgId = int.Parse(getFieldValue("ProductConfig", " PrdCfg_SPCCode='" + dr["Code"].ToString().Trim() + "' AND PrdCfg_Country='" + sCountry + "'", "PrdCfg_Id"));
  3534.  
  3535. msmod.ProductConfigUpdate
  3536. (
  3537. PrdCfgId,
  3538. dr["Code"].ToString().Trim(),
  3539. dr["1M"].ToString().Trim().Replace("Â", ""),
  3540. PrdGrpId,
  3541. dr["PG"].ToString().Trim(),
  3542. "",
  3543. "",
  3544. dr["UNNo"].ToString().Trim(),
  3545. dr["PSB"].ToString().Trim(),
  3546. sFlash,
  3547. dr["Trans"].ToString().Trim(),
  3548. "",
  3549. sTradeMark,
  3550. "",
  3551. "",
  3552. "",
  3553. "",
  3554. dr["Flame"].ToString().Trim(),
  3555. dr["Reactive"].ToString().Trim(),
  3556. "",
  3557. "",
  3558. "",
  3559. MPolutant,
  3560. "",
  3561. "",
  3562. code,
  3563. pict,
  3564. userid,
  3565. PrdType,
  3566. SRP,
  3567. JoinImagesPict(pict),
  3568. JoinImagesPict(code),
  3569. dr["Contains"].ToString().Trim(),
  3570. dr["PG"].ToString().Trim(),
  3571. dr["PSB"].ToString().Trim(),
  3572. dr["SP640"].ToString().Trim(),
  3573. dr["Tox"].ToString().Trim(),
  3574. dr["ToxChro"].ToString().Trim(),
  3575. dr["Clothing"].ToString().Trim(),
  3576. clsColorMatrix.MakeGrayscale3(JoinImagesPict(pict)),
  3577. clsColorMatrix.MakeGrayscale3(JoinImagesPict(code)),
  3578. dr["SigWord"].ToString(),
  3579. dr["StirWell"].ToString(),
  3580. dr["KeepunderShade"].ToString(),
  3581. dr["KeepunderShademax35degrees"].ToString(),
  3582. dr["StoreunderNitrogen"].ToString(),
  3583. dr["StorageCndn"].ToString(),
  3584. sCountry,
  3585. dr["HazardInducing"].ToString(),
  3586. dr["Under10"].ToString(),
  3587. dr["Labelling"].ToString(),
  3588. dr["SplLabelling"].ToString(),
  3589. dr["HazState"].ToString(),
  3590. dr["PreCauGen"].ToString(),
  3591. dr["PreCauPre"].ToString(),
  3592. dr["PreCauRes"].ToString(),
  3593. dr["PreCauSto"].ToString(),
  3594. dr["PreCauDis"].ToString()
  3595. );
  3596. log.Info("Record Updated Successfully Rec No - " + dr["Code"].ToString().Trim());
  3597. iIupdated = iIupdated + 1;
  3598. bRet = true;
  3599. }
  3600. }
  3601.  
  3602. //Insert Material Master Values
  3603. log.Info("Start Add Material Master Detail - Mat Code - " + dr["Code"].ToString().Trim());
  3604. msmod.MaterialMasterSave(dr["Code"].ToString().Trim(), dr["1M"].ToString().Trim(), "KG", "1", "1", userid, out errordesc);
  3605. if (errordesc == "" || errordesc == null || errordesc == "0")
  3606. {
  3607. log.Info("Add Material Master Successfully - Mat Code - " + dr["Code"].ToString().Trim());
  3608. bRet = true;
  3609. }
  3610. else
  3611. {
  3612. log.Error("Error Inserting Material Code - " + dr["Code"].ToString().Trim() + ", Reason is - " + errordesc);
  3613. ErrorAlertToUser("Error Inserting Material Code - " + dr["Code"].ToString().Trim() + ", Reason is - " + errordesc);
  3614. bRet = true;
  3615. }
  3616. }
  3617. }
  3618. }
  3619. }
  3620. else
  3621. {
  3622. if (File.Exists(m_BackupPath + "\\" + sFileName))
  3623. File.Delete(sFilePath);
  3624. else
  3625. File.Move(sFilePath, m_BackupPath + "\\" + sFileName);
  3626. }
  3627. }
  3628. }
  3629. catch (Exception ex)
  3630. {
  3631. bRet = false;
  3632. log.Error("Error in insert product config" + ex.ToString());
  3633. }
  3634.  
  3635. iSkipped = iTotalCount - (iIupdated + iInserted);
  3636. log.Info("Number of Products Created: " + iInserted);
  3637. log.Info("Number of Products Updated: " + iIupdated);
  3638. log.Info("Number of Products Skipped: " + iSkipped);
  3639. log.Info("Total Count: " + iTotalCount);
  3640. if (bRet == true)
  3641. {
  3642. if (File.Exists(m_BackupPath + "\\" + sFileName))
  3643. File.Delete(sFilePath);
  3644. else
  3645. File.Move(sFilePath, m_BackupPath + "\\" + sFileName);
  3646. }
  3647.  
  3648. return bRet;
  3649. }
  3650.  
  3651.  
  3652.  
  3653. #region Join Images
  3654.  
  3655. public System.Drawing.Bitmap JoinImagesPict(string PictIds)
  3656. {
  3657. //read all images into memory
  3658. List<System.Drawing.Bitmap> images = new List<System.Drawing.Bitmap>();
  3659. System.Drawing.Bitmap finalImage = null;
  3660. System.Drawing.Bitmap spaceHImage = null;
  3661. System.Drawing.Bitmap spaceVImage = null;
  3662.  
  3663. try
  3664. {
  3665. if (!PictIds.Equals(""))
  3666. {
  3667. DataSet ImgDs = new DataSet();
  3668. using (MasterModule MastMod = new MasterModule(ConfigurationSettings.AppSettings["CONSTR"].ToString()))
  3669. {
  3670. ImgDs = MastMod.SelectPictogramImages(PictIds);
  3671. }
  3672.  
  3673. int width = 0;
  3674. int height = 0;
  3675.  
  3676. if (ImgDs.Tables.Count > 0)
  3677. {
  3678. if (ImgDs.Tables[0].Rows.Count > 0)
  3679. {
  3680. for (int i = 0; i < ImgDs.Tables[0].Rows.Count; i++)
  3681. {
  3682. DataRow ImgDr = ImgDs.Tables[0].Rows[i];
  3683. byte[] img = null;
  3684. img = (byte[])ImgDr["Pict_Image"];
  3685.  
  3686. MemoryStream mStream = new MemoryStream(img);
  3687. System.Drawing.Bitmap bitmap = new System.Drawing.Bitmap(mStream);
  3688.  
  3689. //update the size of the final bitmap
  3690. if ((i + 1) % 2 > 0)
  3691. {
  3692. if (i + 1 == 1)
  3693. {
  3694. width += bitmap.Width; // = bitmap.Width > width ? bitmap.Width : width;
  3695. height = bitmap.Height > height ? bitmap.Height : height; // += bitmap.Height;
  3696. }
  3697. else
  3698. {
  3699. height += bitmap.Height;
  3700. }
  3701. }
  3702. else
  3703. {
  3704. if (i + 1 == 2)
  3705. {
  3706. width += bitmap.Width;
  3707. }
  3708. }
  3709.  
  3710. //width = bitmap.Width > width ? bitmap.Width : width;
  3711. //height += bitmap.Height;
  3712.  
  3713. images.Add(bitmap);
  3714. }
  3715. }
  3716. }
  3717. //create a bitmap to hold the combined image
  3718. if (images.Count > 2)
  3719. finalImage = new System.Drawing.Bitmap(width + 10, height + (images.Count / 2) * 10);
  3720. else
  3721. {
  3722. if (images.Count == 1)
  3723. finalImage = new System.Drawing.Bitmap(width, height);
  3724. else
  3725. finalImage = new System.Drawing.Bitmap(width + 10, height);
  3726. }
  3727.  
  3728. spaceHImage = new System.Drawing.Bitmap(width, 10);
  3729.  
  3730. //finalImage = new System.Drawing.Bitmap(width, height + (images.Count - 1) * 20);
  3731. //spaceImage = new System.Drawing.Bitmap(width, 20);
  3732.  
  3733.  
  3734. //get a graphics object from the image so we can draw on it
  3735. using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(finalImage))
  3736. {
  3737. //set background color
  3738. g.Clear(System.Drawing.Color.White);
  3739.  
  3740. //go through each image and draw it on the final image
  3741. int hoffset = 0;
  3742. int voffset = 0;
  3743. int count = 1;
  3744. foreach (System.Drawing.Bitmap image in images)
  3745. {
  3746. if (count % 2 > 0)
  3747. {
  3748. if (count > 2)
  3749. {
  3750. g.DrawImage(spaceHImage, new System.Drawing.Rectangle(hoffset, voffset, spaceHImage.Width, 10));
  3751. voffset += 10;
  3752. }
  3753. g.DrawImage(image, new System.Drawing.Rectangle(hoffset, voffset, image.Width, image.Height));
  3754. hoffset += image.Width;
  3755. }
  3756. else
  3757. {
  3758. spaceVImage = new System.Drawing.Bitmap(10, image.Height);
  3759. g.DrawImage(spaceVImage, new System.Drawing.Rectangle(hoffset, voffset, 10, spaceVImage.Height));
  3760. hoffset += 10;
  3761. g.DrawImage(image, new System.Drawing.Rectangle(hoffset, voffset, image.Width, image.Height));
  3762. hoffset = 0;
  3763. voffset += image.Height;
  3764. }
  3765. count += 1;
  3766. }
  3767. }
  3768.  
  3769.  
  3770. ////if (ImgDs.Tables.Count > 0)
  3771. ////{
  3772. //// if (ImgDs.Tables[0].Rows.Count > 0)
  3773. //// {
  3774. //// for (int i = 0; i < ImgDs.Tables[0].Rows.Count; i++)
  3775. //// {
  3776. //// DataRow ImgDr = ImgDs.Tables[0].Rows[i];
  3777. //// byte[] img = null;
  3778. //// img = (byte[])ImgDr["Pict_Image"];
  3779.  
  3780. //// MemoryStream mStream = new MemoryStream(img);
  3781. //// System.Drawing.Bitmap bitmap = new System.Drawing.Bitmap(mStream);
  3782.  
  3783. //// //update the size of the final bitmap
  3784. //// width += bitmap.Width; // = bitmap.Width > width ? bitmap.Width : width;
  3785. //// height = bitmap.Height > height ? bitmap.Height : height; // += bitmap.Height;
  3786.  
  3787. //// //width = bitmap.Width > width ? bitmap.Width : width;
  3788. //// //height += bitmap.Height;
  3789.  
  3790. //// images.Add(bitmap);
  3791. //// }
  3792. //// }
  3793. ////}
  3794. //////create a bitmap to hold the combined image
  3795. ////finalImage = new System.Drawing.Bitmap(width + (images.Count - 1) * 10, height);
  3796. ////spaceImage = new System.Drawing.Bitmap(10, height);
  3797.  
  3798. //////finalImage = new System.Drawing.Bitmap(width, height + (images.Count - 1) * 20);
  3799. //////spaceImage = new System.Drawing.Bitmap(width, 20);
  3800.  
  3801. //////using (System.Drawing.Graphics g1 = System.Drawing.Graphics.FromImage(spaceImage))
  3802. //////{
  3803. ////// g1.Clear(System.Drawing.Color.Transparent);
  3804. ////// g1.DrawImage(System.Drawing.Image.FromFile(Server.MapPath("Pictograms") + "\\spaceImage.png"), new System.Drawing.Rectangle(0, 0, 10, spaceImage.Height));
  3805.  
  3806. //// //get a graphics object from the image so we can draw on it
  3807. //// using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(finalImage))
  3808. //// {
  3809. //// //set background color
  3810. //// g.Clear(System.Drawing.Color.White);
  3811.  
  3812. //// //go through each image and draw it on the final image
  3813. //// int offset = 0;
  3814. //// foreach (System.Drawing.Bitmap image in images)
  3815. //// {
  3816. //// g.DrawImage(image, new System.Drawing.Rectangle(offset, 0, image.Width, image.Height));
  3817. //// offset += image.Width;
  3818. //// g.DrawImage(spaceImage, new System.Drawing.Rectangle(offset, 0, 10, spaceImage.Height));
  3819. //// offset += 10;
  3820. //// }
  3821. //// }
  3822. //////}
  3823.  
  3824. //MemoryStream ms = new MemoryStream();
  3825. //finalImage.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
  3826. //byte[] bitmapData = ms.ToArray();
  3827.  
  3828. return finalImage;
  3829. }
  3830. else
  3831. {
  3832. //finalImage = new System.Drawing.Bitmap(1, 1);
  3833. return null;
  3834. }
  3835. }
  3836. catch (Exception ex)
  3837. {
  3838. if (finalImage != null)
  3839. finalImage.Dispose();
  3840.  
  3841. //LblError.Visible = true;
  3842. //LblError.Text = GeneralException.Errcode(ex).ToString();
  3843. log.Error("ProductConfigDtl_JoinImagesPict", ex);
  3844. return null;
  3845. }
  3846. finally
  3847. {
  3848. //clean up memory
  3849. foreach (System.Drawing.Bitmap image in images)
  3850. {
  3851. image.Dispose();
  3852. }
  3853. }
  3854. }
  3855.  
  3856. public System.Drawing.Bitmap JoinImagesSS(string SSIds)
  3857. {
  3858. //read all images into memory
  3859. List<System.Drawing.Bitmap> images = new List<System.Drawing.Bitmap>();
  3860. System.Drawing.Bitmap finalImage = null;
  3861. System.Drawing.Bitmap spaceImage = null;
  3862.  
  3863. try
  3864. {
  3865. if (!SSIds.Equals(""))
  3866. {
  3867. DataSet ImgDs = new DataSet();
  3868. using (MasterModule MastMod = new MasterModule(ConfigurationSettings.AppSettings["CONSTR"].ToString()))
  3869. {
  3870. ImgDs = MastMod.SelectSSImages(SSIds);
  3871. }
  3872.  
  3873. int width = 0;
  3874. int height = 0;
  3875.  
  3876. if (ImgDs.Tables.Count > 0)
  3877. {
  3878. if (ImgDs.Tables[0].Rows.Count > 0)
  3879. {
  3880. for (int i = 0; i < ImgDs.Tables[0].Rows.Count; i++)
  3881. {
  3882. DataRow ImgDr = ImgDs.Tables[0].Rows[i];
  3883. byte[] img = null;
  3884. img = (byte[])ImgDr["Pict_Image"];
  3885.  
  3886. MemoryStream mStream = new MemoryStream(img);
  3887. System.Drawing.Bitmap bitmap = new System.Drawing.Bitmap(mStream);
  3888.  
  3889. //update the size of the final bitmap
  3890. width += bitmap.Width; // = bitmap.Width > width ? bitmap.Width : width;
  3891. // Commented by Bala On Jun 27 2011 to Fix Stiched Image to be Very Small
  3892. height = bitmap.Height > height ? bitmap.Height : height; // += bitmap.Height;
  3893. //height = 225; // += bitmap.Height;
  3894.  
  3895. //width = bitmap.Width > width ? bitmap.Width : width;
  3896. //height += bitmap.Height;
  3897.  
  3898. images.Add(bitmap);
  3899. }
  3900. }
  3901. }
  3902. //create a bitmap to hold the combined image
  3903. finalImage = new System.Drawing.Bitmap(width + (images.Count - 1) * 10, height);
  3904. spaceImage = new System.Drawing.Bitmap(10, height);
  3905.  
  3906. //finalImage = new System.Drawing.Bitmap(width, height + (images.Count - 1) * 20);
  3907. //spaceImage = new System.Drawing.Bitmap(width, 20);
  3908.  
  3909. //using (System.Drawing.Graphics g1 = System.Drawing.Graphics.FromImage(spaceImage))
  3910. //{
  3911. //g1.Clear(System.Drawing.Color.White);
  3912. // string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
  3913. // path = path + "\\spaceImage.png";
  3914. //g1.DrawImage(System.Drawing.Image.FromFile(path), new System.Drawing.Rectangle(0, 0, 10, spaceImage.Height));
  3915.  
  3916. //get a graphics object from the image so we can draw on it
  3917. using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(finalImage))
  3918. {
  3919. //set background color
  3920. g.Clear(System.Drawing.Color.White);
  3921.  
  3922. //go through each image and draw it on the final image
  3923. int offset = 0;
  3924. foreach (System.Drawing.Bitmap image in images)
  3925. {
  3926. g.DrawImage(image, new System.Drawing.Rectangle(offset, 0, image.Width, image.Height));
  3927. offset += image.Width;
  3928. g.DrawImage(spaceImage, new System.Drawing.Rectangle(offset, 0, 10, spaceImage.Height));
  3929. offset += 10;
  3930. }
  3931. }
  3932. //}
  3933.  
  3934. //MemoryStream ms = new MemoryStream();
  3935. //finalImage.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
  3936. //byte[] bitmapData = ms.ToArray();
  3937. return finalImage;
  3938. }
  3939. else
  3940. {
  3941. //finalImage = new System.Drawing.Bitmap(1, 1);
  3942. return finalImage;
  3943. }
  3944. }
  3945. catch (Exception ex)
  3946. {
  3947. if (finalImage != null)
  3948. finalImage.Dispose();
  3949.  
  3950. //LblError.Visible = true;
  3951. //LblError.Text = GeneralException.Errcode(ex).ToString();
  3952.  
  3953. log.Error("ProductConfigDtl_JoinImagesSS", ex);
  3954.  
  3955. return null;
  3956. }
  3957. finally
  3958. {
  3959. //clean up memory
  3960. foreach (System.Drawing.Bitmap image in images)
  3961. {
  3962. image.Dispose();
  3963. }
  3964. }
  3965. }
  3966.  
  3967. #endregion
  3968.  
  3969.  
  3970.  
  3971. #endregion
  3972.  
  3973. private void AddExistingFileDetails(string FName)
  3974. {
  3975. log.Info("NEW FILE");
  3976. string strlogMsg = @"";
  3977. string sMailStatus = "Y";
  3978. string strUsername = System.Environment.UserName;
  3979. string FileName = "";
  3980. try
  3981. {
  3982. //CleanupFiles();
  3983. m_FilePath = ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + GetFileNameFromPath(FName);
  3984. FileName = GetFileNameFromPath(FName);
  3985. //File.Copy(e.FullPath, @ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + GetFileNameFromPath(e.Name), true);
  3986. strlogMsg = GetFileNameFromPath(FName);
  3987. strlogMsg = strlogMsg + "," + FName;
  3988. strlogMsg = strlogMsg + "," + "Created";
  3989. strlogMsg = strlogMsg + "," + strUsername;
  3990. strlogMsg = strlogMsg + "," + System.DateTime.Now.Date.ToShortDateString();
  3991. strlogMsg = strlogMsg + "," + System.DateTime.Now.TimeOfDay.ToString();
  3992.  
  3993. log.Info(strlogMsg);
  3994. if (FileName.Substring(0, m_QCFileName.Length).ToUpper() == m_QCFileName)
  3995. {
  3996. //Thread.Sleep(20000);
  3997. log.Info(FName);
  3998. AddQCDetail(FName, FileName);
  3999. strlogMsg = strlogMsg + "," + "QC LABEL";
  4000. log.Info(strlogMsg);
  4001. }
  4002. else if (FileName.Substring(0, m_ProdFileName.Length).ToUpper() == m_ProdFileName)
  4003. {
  4004. //Thread.Sleep(20000);
  4005. log.Info(FName);
  4006. AddProdDetail(FName, FileName);
  4007. strlogMsg = strlogMsg + "," + "PRODUCT LABEL";
  4008. log.Info(strlogMsg);
  4009. }
  4010. else if (FileName.Substring(0, m_ShipMarkFileName.Length).ToUpper() == m_ShipMarkFileName)
  4011. {
  4012. //Thread.Sleep(20000);
  4013. log.Info(FName);
  4014. AddShipMarkDetail(FName, FileName);
  4015. strlogMsg = strlogMsg + "," + "SHIPPING MARK LABEL";
  4016. log.Info(strlogMsg);
  4017. }
  4018. else if (FileName.Substring(0, m_ProdShipFileName.Length).ToUpper() == m_ProdShipFileName)
  4019. {
  4020. //Thread.Sleep(20000);
  4021. log.Info(FName);
  4022. AddProdShipDetail(FName, FileName);
  4023. strlogMsg = strlogMsg + "," + "PRODUCT WITH CPN LABEL";
  4024. log.Info(strlogMsg);
  4025. }
  4026. else if (FileName.Substring(0, m_ProdBatchFileName.Length).ToUpper() == m_ProdBatchFileName)
  4027. {
  4028. //Thread.Sleep(20000);
  4029. log.Info(FName);
  4030. AddProdBatchDetail(FName, FileName);
  4031. strlogMsg = strlogMsg + "," + "PRODUCT BATCH NUMBERS";
  4032. log.Info(strlogMsg);
  4033. }
  4034. else if (FileName.Substring(0, m_MaterialsFileName.Length).ToUpper() == m_MaterialsFileName)
  4035. {
  4036. Thread.Sleep(20000);
  4037. log.Info(FName);
  4038. AddMaterialsDetail(FName, FileName);
  4039. strlogMsg = strlogMsg + "," + "MATERIALS MASTERS";
  4040. log.Info(strlogMsg);
  4041. }
  4042. else
  4043. {
  4044. strlogMsg = "UNRECOGNIZED FILE";
  4045. File.Copy(FName, @ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + "\\ARCHIVE\\" + GetFileNameFromPath(FileName), true);
  4046. File.Delete(FName);
  4047. log.Info(strlogMsg);
  4048. ////dbObj.UpdateAudtLog(e.Name, "File Format", "UNRECOGNIZED FILE");
  4049. }
  4050. }
  4051. catch (Exception ex)
  4052. {
  4053. log.Fatal(ex);
  4054. if (sMailStatus == "Y")
  4055. {
  4056. ErrorAlertToUser("Add Existing File Dunction" + " " + strlogMsg + " " + ex.Message);
  4057. sMailStatus = "N";
  4058. }
  4059. //this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4060. //this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4061. }
  4062. }
  4063.  
  4064. # endregion
  4065.  
  4066. # region Static Methods
  4067.  
  4068. /// <summary>
  4069. /// Gets The File Name From Specified Path
  4070. /// </summary>
  4071. private static string GetFileNameFromPath(string path)
  4072. {
  4073. string fileName = @"";
  4074. int indexOfLastSlash = 0;
  4075. try
  4076. {
  4077. indexOfLastSlash = path.LastIndexOf(@"\");
  4078. fileName = path.Substring(indexOfLastSlash + 1);
  4079. return fileName;
  4080. }
  4081. catch (Exception ex)
  4082. {
  4083. log.Fatal(ex);
  4084. return "";
  4085. }
  4086. }
  4087.  
  4088. //private static string getConstr(string Key)
  4089. //{
  4090. // ConfigFileEncryption ConfigDecryptor = new ConfigFileEncryption(ConfigFileType.APP_CONFIG);
  4091. // ConfigDecryptor.WriteConfigFile();
  4092. // return ConfigDecryptor.ReadFromFile(Key);
  4093. //}
  4094.  
  4095. # endregion
  4096.  
  4097. # region Private Member Functions
  4098.  
  4099. private void CleanupFiles()
  4100. {
  4101. try
  4102. {
  4103. int CurrentMonth = GetCurrentMonth();
  4104. if (LastCleanedMonth != CurrentMonth.ToString())
  4105. {
  4106. log.Info("File Cleanup Start");
  4107. LastCleanedMonth = CurrentMonth.ToString();
  4108.  
  4109. DirectoryInfo BackupDI = new DirectoryInfo(m_BackupPath);
  4110. FileInfo[] ArchFiles = BackupDI.GetFiles();
  4111.  
  4112. foreach (FileInfo fi in ArchFiles)
  4113. {
  4114. if (CheckFileStatus(fi.LastWriteTime.ToString("dd/MM/yyyy")))
  4115. {
  4116. log.Info("Delete process start for File from Archive -- " + fi.Name.ToString());
  4117. fi.Delete();
  4118. log.Info("Delete process end for File from Archive -- " + fi.Name.ToString());
  4119. }
  4120. }
  4121.  
  4122. DirectoryInfo DelTemp = new DirectoryInfo(m_TempPath);
  4123. FileInfo[] TempFiles = DelTemp.GetFiles();
  4124.  
  4125. foreach (FileInfo TFiles in TempFiles)
  4126. {
  4127. if (CheckFileStatus(TFiles.LastWriteTime.ToString("dd/MM/yyyy")))
  4128. {
  4129. log.Info("Delete process start for File from Temp -- " + TFiles.Name.ToString());
  4130. TFiles.Delete();
  4131. log.Info("Delete process end for File from Temp -- " + TFiles.Name.ToString());
  4132. }
  4133. }
  4134.  
  4135. log.Info("File Cleanup Completed");
  4136. }
  4137.  
  4138. }
  4139. catch (Exception ex)
  4140. {
  4141. log.Fatal(ex);
  4142. ////ErrorAlertToUser(ex.Message);
  4143. }
  4144. }
  4145.  
  4146. public Boolean CheckFileStatus(string FileCreatedDate)
  4147. {
  4148. try
  4149. {
  4150. SqlParameter[] setparameter = new SqlParameter[3];
  4151. setparameter[0] = ReturnSQLParameter("@i_CreatedDate", SqlDbType.VarChar, ParameterDirection.Input, FileCreatedDate, 100);
  4152. setparameter[1] = ReturnSQLOutputParameter("@o_ExpiredStatus", SqlDbType.Int, 10);
  4153. setparameter[2] = ReturnSQLOutputParameter("@o_ErrorCode", SqlDbType.Int, 10);
  4154. ExecuteSqlNonQueryforStoredProcedure("GetFileExpiredStatus", setparameter);
  4155. if (Convert.ToInt16(setparameter[1].Value.ToString()) == 0)
  4156. return false;
  4157. else
  4158. return true;
  4159. }
  4160. catch (Exception ex)
  4161. {
  4162. log.Fatal(ex);
  4163. ////ErrorAlertToUser(ex.Message);
  4164. return false;
  4165. }
  4166. }
  4167.  
  4168. public int GetCurrentMonth()
  4169. {
  4170. try
  4171. {
  4172. SqlParameter[] setparameter = new SqlParameter[2];
  4173. setparameter[0] = ReturnSQLOutputParameter("@o_CurrMonth", SqlDbType.Int, 10);
  4174. setparameter[1] = ReturnSQLOutputParameter("@o_ErrorCode", SqlDbType.Int, 10);
  4175. ExecuteSqlNonQueryforStoredProcedure("GetCurrentMonth", setparameter);
  4176. return Convert.ToInt16(setparameter[0].Value.ToString());
  4177. }
  4178. catch (Exception ex)
  4179. {
  4180. log.Fatal(ex);
  4181. // ErrorAlertToUser(ex.Message);
  4182. return 0;
  4183. }
  4184. }
  4185.  
  4186. private void AddQCDetail(string sCoreFile, string sFileName)
  4187. {
  4188. DBMethods dbObj = new DBMethods();
  4189. try
  4190. {
  4191. StreamReader objReader = new StreamReader(sCoreFile);
  4192.  
  4193. string sLine = "";
  4194. ArrayList arrText = new ArrayList();
  4195. string[] strArray = new string[] { };
  4196. bool bStatus = false;
  4197. int LineNr = 0;
  4198. string SPCCodeField = ConfigurationSettings.AppSettings["QCSPCCode"].ToString();
  4199. int SPCCodeRow = 0;
  4200. string SPCCode = "";
  4201.  
  4202. //string str_PartNo = "";
  4203. //string str_LotNo = "";
  4204. //string str_GrossWeight = "";
  4205. //string str_UOM = "";
  4206. //string str_NetWeight = "";
  4207. //string str_ReceiptTicketNo = "";
  4208. //string str_QCStatus = "";
  4209. //string str_Location = "";
  4210. //string str_Quantity = "";
  4211. //string str_OrderUnit = "";
  4212. //string str_ReceiptTktLineNo = "";
  4213. //string str_VendorLotNo = "";
  4214. //string str_OrderNo = "";
  4215.  
  4216. string QCQry = "";
  4217. DataRow QCDr;
  4218. if (QCDs.Tables[0].Rows.Count > 0)
  4219. {
  4220. QCQry = "Insert into QCLabelDtl (";
  4221.  
  4222. for (int i = 0; i < QCDs.Tables[0].Rows.Count; i++)
  4223. {
  4224. QCDr = QCDs.Tables[0].Rows[i];
  4225. QCQry = QCQry + QCDr["FieldName"].ToString() + ", ";
  4226. if (QCDr["FieldName"].ToString().Equals(SPCCodeField))
  4227. SPCCodeRow = i;
  4228. }
  4229.  
  4230. QCQry = QCQry.Substring(0, QCQry.Length - 2);
  4231. QCQry = QCQry + ", QCDtl_InsertedDate) Values (";
  4232. }
  4233.  
  4234. string InsertQry = "";
  4235. while (sLine != null && sLine != "null")
  4236. {
  4237. sLine = objReader.ReadLine();
  4238.  
  4239. if (sLine != null)
  4240. {
  4241. if (sLine.Trim() != "")
  4242. {
  4243. LineNr = LineNr + 1;
  4244. InsertQry = QCQry;
  4245. arrText.Add(sLine);
  4246. strArray = sLine.ToString().Split(';');
  4247.  
  4248. for (int i = 0; i < QCDs.Tables[0].Rows.Count; i++)
  4249. {
  4250. DataRow dr = QCDs.Tables[0].Rows[i];
  4251. string strDataType = dr["FieldType"].ToString();
  4252.  
  4253. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4254. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4255. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL") || strDataType.ToUpper().Equals("NUMBER"))
  4256. {
  4257. if (strArray[i].ToString().Trim().Equals(""))
  4258. InsertQry = InsertQry + "null, ";
  4259. else
  4260. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4261. }
  4262.  
  4263. if (i == SPCCodeRow)
  4264. SPCCode = strArray[i].ToString().Trim();
  4265.  
  4266. }
  4267.  
  4268. InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + ", GetDate())";
  4269.  
  4270. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4271. if (!bStatus)
  4272. {
  4273. using (DBMethods dbMod = new DBMethods())
  4274. {
  4275. OldRefId = "0";
  4276. Int64 res = dbMod.AddLabelDtl(InsertQry, "QC", out OldRefId);
  4277. if (res > 0)
  4278. {
  4279. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // QC Label Detail Inserted Successfully ");
  4280. int resUpd = dbMod.UpdateLabelQCDetail(res.ToString());
  4281. if (resUpd > 0)
  4282. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // QC Label Details updated Successfully");
  4283. }
  4284. else if (res == 0)
  4285. {
  4286. if (Convert.ToInt64(OldRefId) > 0)
  4287. {
  4288. int resUpd = dbMod.UpdateLabelQCDetail(OldRefId);
  4289. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // QC Label Detail already exists and Updated Successfully");
  4290. }
  4291. }
  4292. }
  4293.  
  4294. //using (SqlConnection cn = new SqlConnection(ConString))
  4295. //{
  4296. // cn.Open();
  4297. // SqlCommand sqlCmd = new SqlCommand(InsertQry,cn);
  4298. // sqlCmd.CommandTimeout = 0;
  4299. // sqlCmd.ExecuteNonQuery();
  4300. // log.Info("QC Label Detail Inserted Successfully ");
  4301. //}
  4302. }
  4303.  
  4304. //str_PartNo = strArray[0].ToString();
  4305. //str_LotNo = strArray[1].ToString();
  4306. //str_GrossWeight = strArray[2].ToString();
  4307. //str_UOM = strArray[3].ToString();
  4308. //str_NetWeight = strArray[4].ToString();
  4309. //str_ReceiptTicketNo = strArray[5].ToString();
  4310. //str_QCStatus = strArray[6].ToString();
  4311. //str_Location = strArray[7].ToString();
  4312. //str_Quantity = strArray[8].ToString();
  4313. //str_OrderUnit = strArray[9].ToString();
  4314. //str_ReceiptTktLineNo = strArray[10].ToString();
  4315. //str_VendorLotNo = strArray[11].ToString();
  4316. //str_OrderNo = strArray[12].ToString();
  4317.  
  4318. //if (bStatus == false)
  4319. //{
  4320. // if (dbObj.AddQCLabelDetail(str_PartNo, str_LotNo, str_GrossWeight, str_UOM, str_NetWeight, str_ReceiptTicketNo,
  4321. // str_QCStatus, str_Location, str_Quantity, str_OrderUnit, str_ReceiptTktLineNo, str_VendorLotNo, str_OrderNo))
  4322. // {
  4323. // log.Info("QC Label Detail Inserted Successfully ");
  4324. // //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "QC Label Detail Inserted Successfully ");
  4325. // }
  4326. // else
  4327. // {
  4328.  
  4329. // log.Info("Failed to insert QC Label Detail ");
  4330. // //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Failed to insert QC Label Detail");
  4331. // File.Copy(sCoreFile, @ConfigurationSettings.AppSettings.Get("PATHTOWATCH") + "/UnSuccessfiles/" + sFileName, true);
  4332. // File.Delete(sCoreFile);
  4333. // }
  4334. //}
  4335. }
  4336. }
  4337. }
  4338. objReader.Close();
  4339. //File.Delete(sCoreFile);
  4340. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4341. File.Delete(sCoreFile);
  4342. else
  4343. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  4344. }
  4345. catch (Exception ex)
  4346. {
  4347. log.Fatal(ex);
  4348. ////ErrorAlertToUser(ex.Message);
  4349. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  4350. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4351. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4352. }
  4353. }
  4354.  
  4355. private void AddProdDetail(string sCoreFile, string sFileName)
  4356. {
  4357. DBMethods dbObj = new DBMethods();
  4358. try
  4359. {
  4360. StreamReader objReader = new StreamReader(sCoreFile);
  4361.  
  4362. string sLine = "";
  4363. ArrayList arrText = new ArrayList();
  4364. string[] strArray = new string[] { };
  4365. bool bStatus = false;
  4366.  
  4367. int LineNr = 0;
  4368. string SPCCodeField = ConfigurationSettings.AppSettings["ProdSPCCode"].ToString();
  4369. int SPCCodeRow = 0;
  4370. string SPCCode = "";
  4371.  
  4372. string ProdQry = "";
  4373. DataRow ProdDr;
  4374.  
  4375. if (ProdDs.Tables[0].Rows.Count > 0)
  4376. {
  4377. ProdQry = "Insert into ProductLabelDtl (";
  4378.  
  4379. for (int i = 0; i < ProdDs.Tables[0].Rows.Count; i++)
  4380. {
  4381. ProdDr = ProdDs.Tables[0].Rows[i];
  4382. ProdQry = ProdQry + ProdDr["FieldName"].ToString() + ", ";
  4383.  
  4384. if (ProdDr["FieldName"].ToString().Equals(SPCCodeField))
  4385. SPCCodeRow = i;
  4386. }
  4387.  
  4388. ProdQry = ProdQry.Substring(0, ProdQry.Length - 2);
  4389. ProdQry = ProdQry + ", ProdDtl_InsertedDate) Values (";
  4390. }
  4391.  
  4392. string InsertQry = "";
  4393. while (sLine != null)
  4394. {
  4395. sLine = objReader.ReadLine();
  4396. LineNr = LineNr + 1;
  4397.  
  4398. if (sLine != null)
  4399. {
  4400. if (sLine.Trim() != "")
  4401. {
  4402. InsertQry = ProdQry;
  4403. arrText.Add(sLine);
  4404. strArray = sLine.ToString().Split(';');
  4405.  
  4406. for (int i = 0; i < ProdDs.Tables[0].Rows.Count; i++)
  4407. {
  4408. DataRow dr = ProdDs.Tables[0].Rows[i];
  4409. string strDataType = dr["FieldType"].ToString();
  4410.  
  4411. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4412. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4413. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL"))
  4414. {
  4415. if (strArray[i].ToString().Trim().Equals(""))
  4416. InsertQry = InsertQry + "null, ";
  4417. else
  4418. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4419. }
  4420.  
  4421. if (i == SPCCodeRow)
  4422. SPCCode = strArray[i].ToString();
  4423.  
  4424. }
  4425. //InsertQry = InsertQry.TrimEnd(',');
  4426. InsertQry = InsertQry.Remove(InsertQry.Length - 2, 1);
  4427. InsertQry = InsertQry.Substring(0, InsertQry.LastIndexOf(','));
  4428. //InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + "'"+m_Location+"'"+", GetDate())";
  4429. InsertQry = InsertQry + "," + "'" + m_Location + "'" + ", GetDate())";
  4430.  
  4431. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4432. if (!bStatus)
  4433. {
  4434. using (DBMethods dbMod = new DBMethods())
  4435. {
  4436. OldRefId = "0";
  4437. Int64 res = dbMod.AddLabelDtl(InsertQry, "Product", out OldRefId);
  4438. if (res > 0)
  4439. {
  4440. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail Inserted Successfully ");
  4441.  
  4442. int resUpd = dbMod.UpdateLabelProdDetail(A4Limit, A5Limit, A6Limit, res.ToString());
  4443. if (resUpd > 0)
  4444. {
  4445. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label details updated Successfully");
  4446. int resBatch = dbMod.UpdateProdBatchDetails(res.ToString());
  4447. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Batch Master Updated Successfully");
  4448. }
  4449. }
  4450. else if (res == 0)
  4451. {
  4452. if (Convert.ToInt64(OldRefId) > 0)
  4453. {
  4454. int resUpd = dbMod.UpdateLabelProdDetail(A4Limit, A5Limit, A6Limit, OldRefId);
  4455. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail already exists and Updated Successfully");
  4456. int resBatch = dbMod.UpdateProdBatchDetails(OldRefId.ToString());
  4457. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Batch Master Updated Successfully");
  4458. }
  4459.  
  4460. }
  4461. //int resUpd = dbMod.UpdateProductLabelForLabelSize(A4Limit, A5Limit, A6Limit);
  4462. //if (resUpd > 0)
  4463. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Size updated Successfully");
  4464.  
  4465. //int NoLblUpd = dbMod.UpdateProductLabelForNoOfLabels();
  4466. //if (NoLblUpd > 0)
  4467. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label No of Labels updated Successfully");
  4468. }
  4469. //using (SqlConnection cn = new SqlConnection(ConString))
  4470. //{
  4471. // cn.Open();
  4472. // SqlCommand sqlCmd = new SqlCommand(InsertQry, cn);
  4473. // sqlCmd.CommandTimeout = 0;
  4474. // sqlCmd.ExecuteNonQuery();
  4475. // log.Info("Product Label Detail Inserted Successfully ");
  4476. //}
  4477. }
  4478. }
  4479. }
  4480. }
  4481. objReader.Close();
  4482. //File.Delete(sCoreFile);
  4483. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4484. File.Delete(sCoreFile);
  4485. else
  4486. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  4487. }
  4488. catch (Exception ex)
  4489. {
  4490. log.Fatal(ex);
  4491. ////ErrorAlertToUser(ex.Message);
  4492. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  4493. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4494. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4495. }
  4496. }
  4497.  
  4498. private void AddProdShipDetail(string sCoreFile, string sFileName)
  4499. {
  4500. DBMethods dbObj = new DBMethods();
  4501. try
  4502. {
  4503. StreamReader objReader = new StreamReader(sCoreFile);
  4504.  
  4505. string sLine = "";
  4506. ArrayList arrText = new ArrayList();
  4507. string[] strArray = new string[] { };
  4508. bool bStatus = false;
  4509.  
  4510. int LineNr = 0;
  4511. string SPCCodeField = ConfigurationSettings.AppSettings["ProdShipSPCCode"].ToString();
  4512. int SPCCodeRow = 0;
  4513. string SPCCode = "";
  4514.  
  4515. string ProdShipQry = "";
  4516. DataRow ProdShipDr;
  4517. if (ProdShipDs.Tables[0].Rows.Count > 0)
  4518. {
  4519. ProdShipQry = "Insert into ProductLabelDtl (";
  4520.  
  4521. for (int i = 0; i < ProdShipDs.Tables[0].Rows.Count; i++)
  4522. {
  4523. ProdShipDr = ProdShipDs.Tables[0].Rows[i];
  4524. ProdShipQry = ProdShipQry + ProdShipDr["FieldName"].ToString() + ", ";
  4525.  
  4526. if (ProdShipDr["FieldName"].ToString().Equals(SPCCodeField))
  4527. SPCCodeRow = i;
  4528. }
  4529.  
  4530. ProdShipQry = ProdShipQry.Substring(0, ProdShipQry.Length - 2);
  4531. ProdShipQry = ProdShipQry + ", ProdDtl_InsertedDate) Values (";
  4532. }
  4533.  
  4534. string InsertQry = "";
  4535. while (sLine != null && sLine != "null")
  4536. {
  4537. sLine = objReader.ReadLine();
  4538. int temp;
  4539. temp = objReader.Read();
  4540. LineNr = LineNr + 1;
  4541.  
  4542. if (sLine != null)
  4543. {
  4544. if (sLine.Trim() != "")
  4545. {
  4546. InsertQry = ProdShipQry;
  4547. arrText.Add(sLine);
  4548. strArray = sLine.ToString().Split(';');
  4549.  
  4550. for (int i = 0; i < ProdShipDs.Tables[0].Rows.Count; i++)
  4551. {
  4552. DataRow dr = ProdShipDs.Tables[0].Rows[i];
  4553. string strDataType = dr["FieldType"].ToString();
  4554.  
  4555. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4556. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4557. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL"))
  4558. {
  4559. if (strArray[i].ToString().Trim().Equals(""))
  4560. InsertQry = InsertQry + "null, ";
  4561. else
  4562. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4563. }
  4564.  
  4565. if (i == SPCCodeRow)
  4566. SPCCode = strArray[i].ToString();
  4567.  
  4568. }
  4569.  
  4570. InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + ", GetDate())";
  4571.  
  4572. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4573. if (!bStatus)
  4574. {
  4575. using (DBMethods dbMod = new DBMethods())
  4576. {
  4577. OldRefId = "0";
  4578. Int64 res = dbMod.AddLabelDtl(InsertQry, "Product", out OldRefId);
  4579. if (res > 0)
  4580. {
  4581. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail with CPN Inserted Successfully ");
  4582. log.Info("A4 Limit - " + A4Limit);
  4583. log.Info("A5 Limit - " + A5Limit);
  4584. log.Info("A6 Limit - " + A6Limit);
  4585. int resUpd = dbMod.UpdateLabelProdShipDetail(A4Limit, A5Limit, A6Limit, res.ToString());
  4586. if (resUpd > 0)
  4587. {
  4588. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label details updated Successfully");
  4589. int resBatch = dbMod.UpdateProdBatchDetails(res.ToString());
  4590. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Batch Master Updated Successfully");
  4591. }
  4592. }
  4593. else if (res == 0)
  4594. {
  4595. if (Convert.ToInt64(OldRefId) > 0)
  4596. {
  4597. int resUpd = dbMod.UpdateLabelProdShipDetail(A4Limit, A5Limit, A6Limit, OldRefId);
  4598. log.Info("A4 Limit - " + A4Limit);
  4599. log.Info("A5 Limit - " + A5Limit);
  4600. log.Info("A6 Limit - " + A6Limit);
  4601. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail with CPN already exists and Updated Successfully. Ref Id = " + OldRefId.ToString());
  4602. int resBatch = dbMod.UpdateProdBatchDetails(OldRefId.ToString());
  4603. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Batch Master Updated Successfully");
  4604. }
  4605. }
  4606.  
  4607.  
  4608. //if (res > 0)
  4609. //{
  4610. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail Inserted Successfully ");
  4611.  
  4612. // int resUpd = dbMod.UpdateLabelProdDetail(A4Limit, A5Limit, A6Limit, res);
  4613. // if (resUpd > 0)
  4614. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label details updated Successfully");
  4615. //}
  4616. //else if (res == 0)
  4617. //{
  4618. // if (OldProdId > 0)
  4619. // {
  4620. // int resUpd = dbMod.UpdateLabelProdDetail(A4Limit, A5Limit, A6Limit, OldProdId);
  4621. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Detail already exists and Updated Successfully");
  4622. // }
  4623.  
  4624. //}
  4625.  
  4626. //int resUpd = dbMod.UpdateProductLabelForLabelSize(A4Limit, A5Limit, A6Limit);
  4627. //if (resUpd > 0)
  4628. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label Size updated Successfully");
  4629.  
  4630. //int NoLblUpd = dbMod.UpdateProductLabelForNoOfLabels();
  4631. //if (NoLblUpd > 0)
  4632. // log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Label No of Labels updated Successfully");
  4633. }
  4634.  
  4635. //using (SqlConnection cn = new SqlConnection(ConString))
  4636. //{
  4637. // cn.Open();
  4638. // SqlCommand sqlCmd = new SqlCommand(InsertQry, cn);
  4639. // sqlCmd.CommandTimeout = 0;
  4640. // sqlCmd.ExecuteNonQuery();
  4641. // log.Info("Product Label Detail with CPN Inserted Successfully ");
  4642. //}
  4643. }
  4644. }
  4645. }
  4646. }
  4647. objReader.Close();
  4648. //File.Delete(sCoreFile);
  4649. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4650. File.Delete(sCoreFile);
  4651. else
  4652. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  4653. }
  4654. catch (Exception ex)
  4655. {
  4656. log.Fatal(ex);
  4657. ErrorAlertToUser(ex.Message);
  4658. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  4659. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4660. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4661. }
  4662. }
  4663.  
  4664. private void AddShipMarkDetail(string sCoreFile, string sFileName)
  4665. {
  4666. DBMethods dbObj = new DBMethods();
  4667. try
  4668. {
  4669. StreamReader objReader = new StreamReader(sCoreFile);
  4670.  
  4671. string sLine = "";
  4672. ArrayList arrText = new ArrayList();
  4673. string[] strArray = new string[] { };
  4674. bool bStatus = false;
  4675.  
  4676. int LineNr = 0;
  4677. string SPCCodeField = ConfigurationSettings.AppSettings["ProdShipSPCCode"].ToString();
  4678. int SPCCodeRow = 0;
  4679. string SPCCode = "";
  4680.  
  4681. string SMQry = "";
  4682. DataRow SMDr;
  4683. if (ShipMarkDs.Tables[0].Rows.Count > 0)
  4684. {
  4685. SMQry = "Insert into ShipMarkLabelDtl (";
  4686.  
  4687. for (int i = 0; i < ShipMarkDs.Tables[0].Rows.Count; i++)
  4688. {
  4689. SMDr = ShipMarkDs.Tables[0].Rows[i];
  4690. SMQry = SMQry + SMDr["FieldName"].ToString() + ", ";
  4691.  
  4692. if (SMDr["FieldName"].ToString().Equals(SPCCodeField))
  4693. SPCCodeRow = i;
  4694. }
  4695.  
  4696. SMQry = SMQry.Substring(0, SMQry.Length - 2);
  4697. SMQry = SMQry + ", SMark_InsertedDate) Values (";
  4698. }
  4699.  
  4700. string InsertQry = "";
  4701. while (sLine != null && sLine != "null")
  4702. {
  4703. sLine = objReader.ReadLine();
  4704. LineNr = LineNr + 1;
  4705.  
  4706. if (sLine != null)
  4707. {
  4708. if (sLine.Trim() != "")
  4709. {
  4710. InsertQry = SMQry;
  4711. arrText.Add(sLine);
  4712. strArray = sLine.ToString().Split(';');
  4713.  
  4714. for (int i = 0; i < ShipMarkDs.Tables[0].Rows.Count; i++)
  4715. {
  4716. DataRow dr = ShipMarkDs.Tables[0].Rows[i];
  4717. string strDataType = dr["FieldType"].ToString();
  4718.  
  4719. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4720. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4721. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL") || strDataType.ToUpper().Equals("NUMBER"))
  4722. {
  4723. if (strArray[i].ToString().Trim().Equals(""))
  4724. InsertQry = InsertQry + "null, ";
  4725. else
  4726. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4727. }
  4728. else if (strDataType.ToUpper().Equals("DATETIME"))
  4729. {
  4730. if (strArray[i].ToString().Trim().Equals(""))
  4731. InsertQry = InsertQry + "null, ";
  4732. else
  4733. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim() + "', ";
  4734. }
  4735.  
  4736. if (i == SPCCodeRow)
  4737. SPCCode = strArray[i].ToString();
  4738. }
  4739. //InsertQry = InsertQry.Remove(InsertQry.Length - 2, 1);
  4740. //InsertQry = InsertQry.Substring(0, InsertQry.LastIndexOf(','));
  4741. //InsertQry = InsertQry + ",'" + m_Location + "', GetDate())";
  4742. InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + ", GetDate())";
  4743.  
  4744. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4745. if (!bStatus)
  4746. {
  4747. using (DBMethods dbMod = new DBMethods())
  4748. {
  4749. OldRefId = "0";
  4750. Int64 res = dbMod.AddLabelDtl(InsertQry, "SMark", out OldRefId);
  4751. if (res > 0)
  4752. {
  4753. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Shipping Mark Label Detail Inserted Successfully ");
  4754.  
  4755. int resUpd = dbMod.UpdateLabelSMDetail(res.ToString());
  4756. if (resUpd > 0)
  4757. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Shipping Mark Label detail updated Successfully");
  4758. }
  4759. else if (res == 0)
  4760. {
  4761. if (Convert.ToInt64(OldRefId) > 0)
  4762. {
  4763. int resUpd = dbMod.UpdateLabelSMDetail(OldRefId);
  4764. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Shipping Mark Label Detail already exists and Updated Successfully");
  4765. }
  4766. }
  4767. }
  4768.  
  4769.  
  4770.  
  4771. //using (SqlConnection cn = new SqlConnection(ConString))
  4772. //{
  4773. // cn.Open();
  4774. // SqlCommand sqlCmd = new SqlCommand(InsertQry, cn);
  4775. // sqlCmd.CommandTimeout = 0;
  4776. // sqlCmd.ExecuteNonQuery();
  4777. // log.Info("Shipping Mark Label Detail Inserted Successfully ");
  4778. //}
  4779. }
  4780. }
  4781. }
  4782. }
  4783. objReader.Close();
  4784. //File.Delete(sCoreFile);
  4785. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4786. File.Delete(sCoreFile);
  4787. else
  4788. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  4789. }
  4790. catch (Exception ex)
  4791. {
  4792. log.Fatal(ex);
  4793. ErrorAlertToUser(ex.Message);
  4794. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  4795. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4796. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4797. }
  4798. }
  4799.  
  4800. private void AddProdBatchDetail(string sCoreFile, string sFileName)
  4801. {
  4802. DBMethods dbObj = new DBMethods();
  4803. try
  4804. {
  4805. StreamReader objReader = new StreamReader(sCoreFile);
  4806.  
  4807. string sLine = "";
  4808. ArrayList arrText = new ArrayList();
  4809. string[] strArray = new string[] { };
  4810. bool bStatus = false;
  4811.  
  4812. int LineNr = 0;
  4813. string SPCCodeField = ConfigurationSettings.AppSettings["ProdShipSPCCode"].ToString();
  4814. int SPCCodeRow = 0;
  4815. string SPCCode = "";
  4816.  
  4817. string PBQry = "";
  4818. DataRow PBDr;
  4819. if (PBDs.Tables[0].Rows.Count > 0)
  4820. {
  4821. PBQry = "Insert into BatchNumbers (";
  4822.  
  4823. for (int i = 0; i < PBDs.Tables[0].Rows.Count; i++)
  4824. {
  4825. PBDr = PBDs.Tables[0].Rows[i];
  4826. PBQry = PBQry + PBDr["FieldName"].ToString() + ", ";
  4827.  
  4828. if (PBDr["FieldName"].ToString().Equals(SPCCodeField))
  4829. SPCCodeRow = i;
  4830. }
  4831.  
  4832. PBQry = PBQry.Substring(0, PBQry.Length - 2);
  4833. PBQry = PBQry + ", Batch_InsertedDate) Values (";
  4834. }
  4835.  
  4836. string InsertQry = "";
  4837. while (sLine != null)
  4838. {
  4839. sLine = objReader.ReadLine();
  4840. LineNr = LineNr + 1;
  4841.  
  4842. if (sLine.Trim() != "")
  4843. {
  4844. InsertQry = PBQry;
  4845. arrText.Add(sLine);
  4846. strArray = sLine.ToString().Split(';');
  4847.  
  4848. for (int i = 0; i < PBDs.Tables[0].Rows.Count; i++)
  4849. {
  4850. DataRow dr = PBDs.Tables[0].Rows[i];
  4851. string strDataType = dr["FieldType"].ToString();
  4852.  
  4853. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4854. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4855. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL"))
  4856. {
  4857. if (strArray[i].ToString().Trim().Equals(""))
  4858. InsertQry = InsertQry + "null, ";
  4859. else
  4860. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4861. }
  4862.  
  4863. if (i == SPCCodeRow)
  4864. SPCCode = strArray[i].ToString();
  4865. }
  4866.  
  4867. InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + ", GetDate())";
  4868.  
  4869. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4870. if (!bStatus)
  4871. {
  4872. using (DBMethods dbMod = new DBMethods())
  4873. {
  4874. Int64 res = dbMod.AddLabelDtl(InsertQry, "ProductBatch", out OldRefId);
  4875. if (res > 0)
  4876. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Product Batch Numbers Detail Inserted Successfully ");
  4877. }
  4878. //using (SqlConnection cn = new SqlConnection(ConString))
  4879. //{
  4880. // cn.Open();
  4881. // SqlCommand sqlCmd = new SqlCommand(InsertQry, cn);
  4882. // sqlCmd.CommandTimeout = 0;
  4883. // sqlCmd.ExecuteNonQuery();
  4884. // log.Info("Product Batch Numbers details Inserted Successfully ");
  4885. //}
  4886. }
  4887. }
  4888. }
  4889. objReader.Close();
  4890. //File.Delete(sCoreFile);
  4891. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4892. File.Delete(sCoreFile);
  4893. else
  4894. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  4895. }
  4896. catch (Exception ex)
  4897. {
  4898. log.Fatal(ex);
  4899. ErrorAlertToUser(ex.Message);
  4900. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  4901. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4902. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  4903. }
  4904. }
  4905.  
  4906. private void AddMaterialsDetail(string sCoreFile, string sFileName)
  4907. {
  4908. DBMethods dbObj = new DBMethods();
  4909. try
  4910. {
  4911. StreamReader objReader = new StreamReader(sCoreFile);
  4912. log.Info("Start Inside Add Material function");
  4913. string sLine = "";
  4914. ArrayList arrText = new ArrayList();
  4915. string[] strArray = new string[] { };
  4916. bool bStatus = false;
  4917.  
  4918. int LineNr = 0;
  4919. string SPCCodeField = ConfigurationSettings.AppSettings["MaterialsSPCCode"].ToString();
  4920. int SPCCodeRow = 0;
  4921. string SPCCode = "";
  4922.  
  4923. string MatQry = "";
  4924. DataRow MatDr;
  4925. log.Info("Dataset Count -- " + MatDs.Tables[0].Rows.Count.ToString());
  4926. if (MatDs.Tables[0].Rows.Count > 0)
  4927. {
  4928. MatQry = "Insert into Materials (";
  4929.  
  4930. for (int i = 0; i < MatDs.Tables[0].Rows.Count; i++)
  4931. {
  4932. MatDr = MatDs.Tables[0].Rows[i];
  4933. MatQry = MatQry + MatDr["FieldName"].ToString() + ", ";
  4934.  
  4935. if (MatDr["FieldName"].ToString().Equals(SPCCodeField))
  4936. SPCCodeRow = i;
  4937. }
  4938.  
  4939. MatQry = MatQry.Substring(0, MatQry.Length - 2);
  4940. MatQry = MatQry + ",Mat_ActiveStatus,Mat_CreatedBy, Mat_CreatedOn) Values (";
  4941. }
  4942.  
  4943. string InsertQry = "";
  4944. log.Info("Line Details -- " + sLine);
  4945. while (sLine != null && sLine != "null")
  4946. {
  4947. sLine = objReader.ReadLine();
  4948. LineNr = LineNr + 1;
  4949.  
  4950. if (sLine != null && sLine.Trim() != "" && sLine != "null")
  4951. {
  4952. InsertQry = MatQry;
  4953. arrText.Add(sLine);
  4954. strArray = sLine.ToString().Split(';');
  4955.  
  4956. for (int i = 0; i < MatDs.Tables[0].Rows.Count; i++)
  4957. {
  4958. DataRow dr = MatDs.Tables[0].Rows[i];
  4959. string strDataType = dr["FieldType"].ToString();
  4960. log.Info("Data Type -- " + strDataType);
  4961.  
  4962. if (strDataType.ToUpper().Equals("VARCHAR") || strDataType.ToUpper().Equals("CHAR"))
  4963. InsertQry = InsertQry + "'" + strArray[i].ToString().Trim().Replace("'", "`") + "', ";
  4964. else if (strDataType.ToUpper().Equals("INT") || strDataType.ToUpper().Equals("DECIMAL") || strDataType.ToUpper().Equals("NUMBER"))
  4965. {
  4966. if (strArray[i].ToString().Trim().Equals(""))
  4967. InsertQry = InsertQry + "null, ";
  4968. else
  4969. InsertQry = InsertQry + strArray[i].ToString().Trim().Replace(",", "") + ", ";
  4970. }
  4971.  
  4972. if (i == SPCCodeRow)
  4973. SPCCode = strArray[i].ToString();
  4974. }
  4975. string strStatus = "Y";
  4976.  
  4977. InsertQry = InsertQry.Substring(0, InsertQry.Length - 2) + ",'" + strStatus + "',1, GetDate())";
  4978. //log.Info("Insert Query -- " + InsertQry);
  4979.  
  4980. //string ConString = ConfigurationSettings.AppSettings["CONSTR"].ToString();
  4981. if (!bStatus)
  4982. {
  4983. using (DBMethods dbMod = new DBMethods())
  4984. {
  4985. Int64 res = dbMod.AddLabelDtl(InsertQry, "Materials", out OldRefId);
  4986. if (res > 0)
  4987. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Materials Detail Inserted Successfully ");
  4988. else
  4989. log.Info("Row : " + LineNr.ToString() + "//SPC Code : " + SPCCode + " // Materials Detail Updated Successfully ");
  4990. }
  4991. }
  4992. }
  4993. }
  4994. objReader.Close();
  4995. //File.Delete(sCoreFile);
  4996. if (File.Exists(m_BackupPath + "\\" + sFileName))
  4997. File.Delete(sCoreFile);
  4998. else
  4999. File.Move(sCoreFile, m_BackupPath + "\\" + sFileName);
  5000. }
  5001. catch (Exception ex)
  5002. {
  5003. log.Fatal(ex);
  5004. ErrorAlertToUser(ex.Message);
  5005. //dbObj.AddAudit(sFileName, "LPSFILEWATCHER | QCLABELDTLADD", "Invalid file format");
  5006. this.LPSFileWatcher.Created -= new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  5007. this.LPSFileWatcher.Created += new System.IO.FileSystemEventHandler(this.LPSFileWatcher_Created);
  5008. }
  5009. }
  5010.  
  5011. private void ErrorAlertToUser(string ErrMsg)
  5012. {
  5013. try
  5014. {
  5015. string FromUser = ConfigurationSettings.AppSettings["FromEmailId"].ToString();
  5016. string ToUser = ConfigurationSettings.AppSettings["ToEmailId"].ToString();
  5017. string CcUser = ConfigurationSettings.AppSettings["CCEmailId"].ToString();
  5018.  
  5019. string MsgBody = "Error occured in LPS.NET Data download Service from the SAP System. <br>";
  5020. MsgBody = MsgBody + "Please check service log for the Error Infomation. <br> ";
  5021. MsgBody = MsgBody + "Error Message is : <br> " + ErrMsg;
  5022. MsgBody = MsgBody + "<br><br><br>Thanks & Regards <br>";
  5023. MsgBody = MsgBody + "Administrator <br>";
  5024. MsgBody = MsgBody + "<br><br><br><br><br><br><br><br>";
  5025. MsgBody = MsgBody + "This is system generated mail. Please do not replay to this mail";
  5026.  
  5027. MailMessage mMailMessage = new MailMessage();
  5028. mMailMessage.From = new MailAddress(FromUser);
  5029. mMailMessage.To.Add(new MailAddress(ToUser));
  5030. if ((CcUser != null) && (CcUser != string.Empty))
  5031. {
  5032. mMailMessage.CC.Add(new MailAddress(CcUser));
  5033. }
  5034. mMailMessage.Subject = "LPS.NET Data Download Error";
  5035. mMailMessage.Body = MsgBody;
  5036. mMailMessage.IsBodyHtml = true;
  5037. mMailMessage.Priority = MailPriority.Normal;
  5038. SmtpClient mSmtpClient = new SmtpClient();
  5039. //SmtpClient mSmtpClient = new SmtpClient("pod51021.outlook.com", 587);//,"selva@integratech.com.sg","Integra@123");
  5040. //NetworkCredential crd = new NetworkCredential("selva@integratech.com.sg", "Integra@123");
  5041. //mSmtpClient.UseDefaultCredentials = false;
  5042. //mSmtpClient.Credentials = crd;
  5043. //mSmtpClient.EnableSsl = true;
  5044. //mSmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
  5045. mSmtpClient.Send(mMailMessage);
  5046. }
  5047. catch (Exception ex)
  5048. {
  5049. log.Fatal(ex.Message);
  5050. }
  5051. }
  5052. # endregion
  5053.  
  5054. }
  5055. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!