· 6 years ago · Mar 17, 2019, 06:38 AM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Configuration;
6using System.IO;
7using System.Collections;
8using System.Threading;
9using System.Data;
10using System.Data.SqlClient;
11using System.Data.OleDb;
12using System.Net.Mail;
13using log4net;
14
15namespace 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}