1: using System;
2: using System.Collections.Generic;
3: using System.Configuration;
4: using System.Data;
5: using System.IO;
6: using System.Linq;
7: using System.Net;
8: using System.Reflection;
9: using System.Text;
10: using System.Web;
11: using ExcelDataReader;
12: using SimpleImpersonation;
13:
14: namespace Ia.Ngn.Cl.Model.Data.Huawei
15: {
16: ////////////////////////////////////////////////////////////////////////////
17:
18: /// <summary publish="true">
19: /// GponPhaseIiHomeConnectionAndMigration general support class for Huawei's Optical Fiber Network (OFN) data model.
20: /// </summary>
21: ///
22: /// <remarks>
23: /// Copyright © 2014-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
24: ///
25: /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
26: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
27: ///
28: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
29: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
30: ///
31: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
32: ///
33: /// Copyright notice: This notice may not be removed or altered from any source distribution.
34: /// </remarks>
35: public class GponPhaseIiHomeConnectionAndMigration
36: {
37: private static string textDocument;
38: private static Dictionary<string, List<string>> accessToPstnServiceListDictionary;
39: private static DateTime accessToPstnServiceListDictionaryTimestamp;
40: private static List<GponPhaseIiHomeConnectionAndMigrationItem> gponPhaseIiHomeConnectionAndMigration;
41: private static readonly string PathToStorageHuaweiGponPhase2Folder = @"\\" + StorageServerHostIp + @"\Huawei\GPON phase 2\MOC GPON Activation & Migration Report.xlsx";
42:
43: private static readonly object objectLock = new object();
44:
45: /// <summary/>
46: public static string StorageServerHostIp { get { return ConfigurationManager.AppSettings["storageServerHostIp"].ToString(); } }
47: /// <summary/>
48: public static string StorageServerDomain { get { return "dummydomain"; } }
49: /// <summary/>
50: public static string StorageServerUserName { get { return ConfigurationManager.AppSettings["storageServerUserName"].ToString(); } }
51: /// <summary/>
52: public static string StorageServerPassword { get { return ConfigurationManager.AppSettings["storageServerPassword"].ToString(); } }
53:
54: public class GponPhaseIiHomeConnectionAndMigrationItem
55: {
56: /// <summary/>
57: public GponPhaseIiHomeConnectionAndMigrationItem() { }
58: /// <summary/>
59: public string Area { get; set; }
60: public string OnuId { get; set; }
61: public string OnuType { get; set; }
62: public string ActivationDate { get; set; }
63: public string Sn { get; set; }
64: public string VoiceIp { get; set; }
65: public string MigrationDate { get; set; }
66: public string Tel1 { get; set; }
67: public string Tel2 { get; set; }
68: public string Tel3 { get; set; }
69: public string Tel4 { get; set; }
70: public string Tel5 { get; set; }
71: public string Tel6 { get; set; }
72: public string Tel7 { get; set; }
73: public string Tel8 { get; set; }
74: public string Tel9 { get; set; }
75: }
76:
77: ////////////////////////////////////////////////////////////////////////////
78:
79: /// <summary>
80: ///
81: /// </summary>
82: public GponPhaseIiHomeConnectionAndMigration() { }
83:
84: ////////////////////////////////////////////////////////////////////////////
85:
86: /// <summary>
87: ///
88: /// </summary>
89: public static List<GponPhaseIiHomeConnectionAndMigrationItem> List
90: {
91: get
92: {
93: string[] comma = { "," };
94:
95: if (gponPhaseIiHomeConnectionAndMigration == null || gponPhaseIiHomeConnectionAndMigration.Count == 0)
96: {
97: gponPhaseIiHomeConnectionAndMigration = new List<GponPhaseIiHomeConnectionAndMigrationItem>();
98:
99: //SaveTextDocument(TextDocumentFromFile); // Later remove
100:
101: foreach (var line in Ia.Ngn.Cl.Model.Data.Miscellaneous.Read("GponPhaseIiHomeConnectionAndMigrationTextDocument").Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
102: {
103: string[] fieldList = line.Split(comma, System.StringSplitOptions.None);
104:
105: if (fieldList.Length == 16)
106: {
107: gponPhaseIiHomeConnectionAndMigration.Add(new GponPhaseIiHomeConnectionAndMigrationItem
108: {
109: Area = fieldList[0],
110: OnuId = fieldList[1],
111: OnuType = fieldList[2],
112: ActivationDate = fieldList[3],
113: Sn = fieldList[4],
114: VoiceIp = fieldList[5],
115: MigrationDate = fieldList[6],
116: Tel1 = fieldList[7],
117: Tel2 = fieldList[8],
118: Tel3 = fieldList[9],
119: Tel4 = fieldList[10],
120: Tel5 = fieldList[11],
121: Tel6 = fieldList[12],
122: Tel7 = fieldList[13],
123: Tel8 = fieldList[14],
124: Tel9 = fieldList[15]
125: });
126: }
127: else
128: {
129:
130: }
131: }
132: }
133:
134: return gponPhaseIiHomeConnectionAndMigration;
135: }
136: }
137:
138: ////////////////////////////////////////////////////////////////////////////
139:
140: /// <summary>
141: ///
142: /// </summary>
143: public static Dictionary<string, List<string>> AccessToPstnServiceListDictionary
144: {
145: get
146: {
147: DateTime now;
148: Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
149:
150: now = DateTime.UtcNow.AddHours(3);
151: validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
152:
153: if (accessToPstnServiceListDictionary == null || accessToPstnServiceListDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, accessToPstnServiceListDictionaryTimestamp))
154: {
155: lock (objectLock)
156: {
157: accessToPstnServiceListDictionary = Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration._AccessToPstnServiceListDictionary(validityOfData);
158: }
159: }
160:
161: return accessToPstnServiceListDictionary;
162: }
163: }
164:
165: ////////////////////////////////////////////////////////////////////////////
166:
167: /// <summary>
168: ///
169: /// </summary>
170: private static Dictionary<string, List<string>> _AccessToPstnServiceListDictionary(Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData)
171: {
172: string title;
173: DateTime now, timestamp;
174:
175: now = DateTime.UtcNow.AddHours(3);
176:
177: title = "AccessToPstnServiceListDictionary";
178:
179: timestamp = Ia.Ngn.Cl.Model.Data.Miscellaneous.ReadUpdatedDateTime(title);// + "Timestamp");
180:
181: if (!Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, timestamp))
182: {
183: accessToPstnServiceListDictionary = _AccessToPstnServiceListDictionaryFromStorageServer();
184: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title, accessToPstnServiceListDictionary);
185:
186: timestamp = now;
187: //Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title + "Timestamp", timestamp);
188: }
189: else
190: {
191: accessToPstnServiceListDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, List<string>>>(title);
192: }
193:
194: accessToPstnServiceListDictionaryTimestamp = timestamp;
195:
196: return accessToPstnServiceListDictionary;
197: }
198:
199: ////////////////////////////////////////////////////////////////////////////
200:
201: /// <summary>
202: ///
203: /// </summary>
204: public static Dictionary<string, List<string>> _AccessToPstnServiceListDictionaryFromStorageServer()
205: {
206: string filePath;
207: Ia.Cl.Model.Result result;
208:
209: accessToPstnServiceListDictionary = new Dictionary<string, List<string>>();
210: result = new Ia.Cl.Model.Result();
211:
212: //filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\test.xlsx";
213: //filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\QSR-QRN-MOC_GPON_Phase2_OLT-ONT-MDU_IP-Plan-V2-12-21-2019.xlsx";
214: //filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\MOC GPON Activation & Migration Report.xlsx";
215: //filePath = @"\\172.25.221.3\Huawei\GPON phase 2\MOC GPON Activation & Migration Report.xlsx";
216:
217: filePath = PathToStorageHuaweiGponPhase2Folder;
218:
219: try
220: {
221: var userCredentials = new UserCredentials(Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.StorageServerDomain,
222: Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.StorageServerUserName,
223: Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.StorageServerPassword);
224:
225: Impersonation.RunAsUser(userCredentials, LogonType.NewCredentials, () =>
226: {
227: using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
228: {
229: using (var reader = ExcelReaderFactory.CreateReader(stream))
230: {
231: DataSet dataSet = reader.AsDataSet();
232:
233: // test.xlsx, COB
234: //DataTable dataTable = dataSet.Tables["COB"];
235: //var list = (from r in dataTable.AsEnumerable() where r.Field<string>("Column1") != "MA5672-4" select r).ToList();
236:
237: // QSR-QRN-MOC_GPON_Phase2_OLT-ONT-MDU_IP-Plan-V2-12-21-2019.xlsx, QSR
238: //DataTable dataTable = dataSet.Tables["QSR"];
239: //var list = (from r in dataTable.AsEnumerable() where r.Field<object>("Column40") != null select new { Access = r.Field<string>("Column3"), Phone = r.Field<object>("Column40") }).ToList();
240:
241: // MOC GPON Activation & Migration Report.xlsx, Copper Data Base
242: // there are MDUs with over 50 numbers
243:
244: DataTable dataTable = dataSet.Tables["Copper Data Base"];
245:
246: var list = (from r in dataTable.AsEnumerable()
247: where
248: r.Field<object>("Column2") != null || r.Field<object>("Column3") != null || r.Field<object>("Column4") != null || r.Field<object>("Column5") != null || r.Field<object>("Column6") != null || r.Field<object>("Column7") != null || r.Field<object>("Column8") != null || r.Field<object>("Column9") != null
249: || r.Field<object>("Column10") != null || r.Field<object>("Column11") != null || r.Field<object>("Column12") != null || r.Field<object>("Column13") != null || r.Field<object>("Column14") != null || r.Field<object>("Column15") != null || r.Field<object>("Column16") != null || r.Field<object>("Column17") != null || r.Field<object>("Column18") != null || r.Field<object>("Column19") != null
250: || r.Field<object>("Column20") != null || r.Field<object>("Column21") != null || r.Field<object>("Column22") != null || r.Field<object>("Column23") != null || r.Field<object>("Column24") != null || r.Field<object>("Column25") != null || r.Field<object>("Column26") != null || r.Field<object>("Column27") != null || r.Field<object>("Column28") != null || r.Field<object>("Column29") != null
251: || r.Field<object>("Column30") != null || r.Field<object>("Column31") != null || r.Field<object>("Column32") != null || r.Field<object>("Column33") != null || r.Field<object>("Column34") != null || r.Field<object>("Column35") != null || r.Field<object>("Column36") != null || r.Field<object>("Column37") != null || r.Field<object>("Column38") != null || r.Field<object>("Column39") != null
252: || r.Field<object>("Column40") != null || r.Field<object>("Column41") != null || r.Field<object>("Column42") != null || r.Field<object>("Column43") != null || r.Field<object>("Column44") != null || r.Field<object>("Column45") != null || r.Field<object>("Column46") != null || r.Field<object>("Column47") != null || r.Field<object>("Column48") != null || r.Field<object>("Column49") != null
253: || r.Field<object>("Column50") != null || r.Field<object>("Column51") != null || r.Field<object>("Column52") != null || r.Field<object>("Column53") != null || r.Field<object>("Column54") != null || r.Field<object>("Column55") != null || r.Field<object>("Column56") != null || r.Field<object>("Column57") != null || r.Field<object>("Column58") != null || r.Field<object>("Column59") != null
254: || r.Field<object>("Column60") != null || r.Field<object>("Column61") != null || r.Field<object>("Column62") != null || r.Field<object>("Column63") != null || r.Field<object>("Column64") != null || r.Field<object>("Column65") != null
255: select new
256: {
257: AreaNameOrSymbol = r.Field<object>("Column0"),
258: AccessName = r.Field<object>("Column1"),
259: Service1 = r.Field<object>("Column2"),
260: Service2 = r.Field<object>("Column3"),
261: Service3 = r.Field<object>("Column4"),
262: Service4 = r.Field<object>("Column5"),
263: Service5 = r.Field<object>("Column6"),
264: Service6 = r.Field<object>("Column7"),
265: Service7 = r.Field<object>("Column8"),
266: Service8 = r.Field<object>("Column9"),
267: Service9 = r.Field<object>("Column10"),
268:
269: Service10 = r.Field<object>("Column11"),
270: Service11 = r.Field<object>("Column12"),
271: Service12 = r.Field<object>("Column13"),
272: Service13 = r.Field<object>("Column14"),
273: Service14 = r.Field<object>("Column15"),
274: Service15 = r.Field<object>("Column16"),
275: Service16 = r.Field<object>("Column17"),
276: Service17 = r.Field<object>("Column18"),
277: Service18 = r.Field<object>("Column19"),
278: Service19 = r.Field<object>("Column20"),
279:
280: Service20 = r.Field<object>("Column21"),
281: Service21 = r.Field<object>("Column22"),
282: Service22 = r.Field<object>("Column23"),
283: Service23 = r.Field<object>("Column24"),
284: Service24 = r.Field<object>("Column25"),
285: Service25 = r.Field<object>("Column26"),
286: Service26 = r.Field<object>("Column27"),
287: Service27 = r.Field<object>("Column28"),
288: Service28 = r.Field<object>("Column29"),
289: Service29 = r.Field<object>("Column30"),
290:
291: Service30 = r.Field<object>("Column31"),
292: Service31 = r.Field<object>("Column32"),
293: Service32 = r.Field<object>("Column33"),
294: Service33 = r.Field<object>("Column34"),
295: Service34 = r.Field<object>("Column35"),
296: Service35 = r.Field<object>("Column36"),
297: Service36 = r.Field<object>("Column37"),
298: Service37 = r.Field<object>("Column38"),
299: Service38 = r.Field<object>("Column39"),
300: Service39 = r.Field<object>("Column40"),
301:
302: Service40 = r.Field<object>("Column41"),
303: Service41 = r.Field<object>("Column42"),
304: Service42 = r.Field<object>("Column43"),
305: Service43 = r.Field<object>("Column44"),
306: Service44 = r.Field<object>("Column45"),
307: Service45 = r.Field<object>("Column46"),
308: Service46 = r.Field<object>("Column47"),
309: Service47 = r.Field<object>("Column48"),
310: Service48 = r.Field<object>("Column49"),
311: Service49 = r.Field<object>("Column50"),
312:
313: Service50 = r.Field<object>("Column51"),
314: Service51 = r.Field<object>("Column52"),
315: Service52 = r.Field<object>("Column53"),
316: Service53 = r.Field<object>("Column54"),
317: Service54 = r.Field<object>("Column55"),
318: Service55 = r.Field<object>("Column56"),
319: Service56 = r.Field<object>("Column57"),
320: Service57 = r.Field<object>("Column58"),
321: Service58 = r.Field<object>("Column59"),
322: Service59 = r.Field<object>("Column60"),
323:
324: Service60 = r.Field<object>("Column61"),
325: Service61 = r.Field<object>("Column62"),
326: Service62 = r.Field<object>("Column63"),
327: Service63 = r.Field<object>("Column64"),
328: Service64 = r.Field<object>("Column65")
329: }).ToList();
330:
331: accessToPstnServiceListDictionary = new Dictionary<string, List<string>>(list.Count);
332:
333: bool hadSkippedFirstRow;
334: int currentColumnIndex;
335: string key;
336: List<string> valueList;
337:
338: hadSkippedFirstRow = false;
339:
340: foreach (var l in list)
341: {
342: if (hadSkippedFirstRow)
343: {
344: key = Ia.Ngn.Cl.Model.Business.Default.StandardizeAccessNameInLegalFormatFromValue(l.AccessName.ToString());
345:
346: valueList = new List<string>();
347:
348: currentColumnIndex = 0;
349:
350: foreach (var prop in l.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))
351: {
352: if (currentColumnIndex++ >= 2)
353: {
354: if (prop.GetValue(l, null) != null)
355: {
356: valueList.Add(prop.GetValue(l, null).ToString());
357: }
358: }
359: }
360:
361: accessToPstnServiceListDictionary[key] = valueList;
362: }
363:
364: hadSkippedFirstRow = true;
365: }
366: }
367: }
368: });
369: }
370: catch (Exception e)
371: {
372: result.AddError(e.Message);
373: }
374:
375: return accessToPstnServiceListDictionary;
376: }
377:
378: ////////////////////////////////////////////////////////////////////////////
379:
380: /// <summary>
381: ///
382: /// </summary>
383: public static void UploadStream(StreamReader streamReader, out Ia.Cl.Model.Result result)
384: {
385: string line;
386: StringBuilder stringBuilder;
387:
388: result = new Ia.Cl.Model.Result();
389: stringBuilder = new StringBuilder();
390:
391: while ((line = streamReader.ReadLine()) != null) stringBuilder.AppendLine(line);
392:
393: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate("GponPhaseIiHomeConnectionAndMigrationTextDocument", stringBuilder.ToString());
394: }
395:
396: ////////////////////////////////////////////////////////////////////////////
397:
398: /// <summary>
399: ///
400: /// </summary>
401: public static void UploadText(string attachmentText, out Ia.Cl.Model.Result result)
402: {
403: result = new Ia.Cl.Model.Result();
404:
405: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate("GponPhaseIiHomeConnectionAndMigrationTextDocument", attachmentText);
406: }
407:
408: ////////////////////////////////////////////////////////////////////////////
409: ////////////////////////////////////////////////////////////////////////////
410:
411: /// <summary>
412: ///
413: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
414: ///
415: /// 1. Change the "Build Action" property of your TXT file from "Content" to "Embedded Resource".
416: /// 2. Add "using System.Reflection".
417: /// 3. Manifest resource stream will start with the project namespace, the location of XML file.
418: ///
419: /// </summary>
420:
421: private static string TextDocumentFromFile
422: {
423: get
424: {
425: Assembly _assembly;
426: StreamReader streamReader;
427:
428: if (textDocument == null)
429: {
430: _assembly = Assembly.GetExecutingAssembly();
431: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.huawei.moc-gpon-phase-ii-home-connection-and-migration.csv"));
432:
433: try
434: {
435: if (streamReader.Peek() != -1) textDocument = streamReader.ReadToEnd();
436: }
437: catch (Exception)
438: {
439: }
440: finally
441: {
442: }
443: }
444:
445: return textDocument;
446: }
447: }
448:
449: ////////////////////////////////////////////////////////////////////////////
450: ////////////////////////////////////////////////////////////////////////////
451: }
452:
453: ////////////////////////////////////////////////////////////////////////////
454: ////////////////////////////////////////////////////////////////////////////
455: }