1: using System;
2: using System.Web;
3: using System.Xml;
4: using System.Xml.Linq;
5: using System.IO;
6: using System.Configuration;
7: using System.Text;
8: using System.Text.RegularExpressions;
9: using System.Data;
10: using System.Data.SqlClient;
11: using System.Web.UI;
12: using System.Web.UI.WebControls;
13: using System.Collections;
14: using System.Linq;
15:
16: namespace Ia.Ngn.Ims.Wa.Model.Data
17: {
18: ////////////////////////////////////////////////////////////////////////////
19:
20: /// <summary publish="true">
21: /// Default support class for Next Generation Network (NGN) web application (Intranet) model.
22: /// </summary>
23: ///
24: /// <remarks>
25: /// Copyright © 2006-2015 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
26: ///
27: /// 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
28: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
29: ///
30: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
31: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
32: ///
33: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
34: ///
35: /// Copyright notice: This notice may not be removed or altered from any source distribution.
36: /// </remarks>
37: public class Default
38: {
39: private XDocument area, service, hsi, maintenance, administration, ndd, contact, _event;
40: private Ia.Cl.Model.Xml xml;
41: private Ia.Cl.Model.Db.SqlServer sqlserver;
42:
43: ////////////////////////////////////////////////////////////////////////////
44:
45: /// <summary>
46: ///
47: /// </summary>
48: public Default()
49: {
50: sqlserver = new Ia.Cl.Model.Db.SqlServer();
51: xml = new Ia.Cl.Model.Xml();
52:
53: area = Ia.Cl.Model.Xml.Load(@"app_data\area.xml");
54: service = Ia.Cl.Model.Xml.Load(@"app_data\service.xml", @"app_data\default.xsl");
55: hsi = Ia.Cl.Model.Xml.Load(@"app_data\hsi.xml");
56: maintenance = Ia.Cl.Model.Xml.Load(@"app_data\maintenance.xml", @"app_data\default.xsl");
57:
58: administration = Ia.Cl.Model.Xml.Load(@"app_data\administration.xml", @"app_data\default.xsl");
59: ndd = Ia.Cl.Model.Xml.Load(@"app_data\ndd.xml");
60:
61: contact = Ia.Cl.Model.Xml.Load(@"app_data\contact.xml");
62: _event = Ia.Cl.Model.Xml.Load(@"app_data\event.xml");
63: }
64:
65: ////////////////////////////////////////////////////////////////////////////
66: ////////////////////////////////////////////////////////////////////////////
67:
68: #region XML
69:
70: ////////////////////////////////////////////////////////////////////////////
71:
72: /// <summary>
73: ///
74: /// </summary>
75: public XDocument Ndd() { return ndd; }
76:
77: ////////////////////////////////////////////////////////////////////////////
78:
79: /// <summary>
80: ///
81: /// </summary>
82: public XDocument Area() { return area; }
83:
84: ////////////////////////////////////////////////////////////////////////////
85:
86: /// <summary>
87: ///
88: /// </summary>
89: public static XDocument Service
90: {
91: get
92: {
93: XDocument xd;
94:
95: xd = Ia.Cl.Model.Xml.Load(@"app_data\service.xml", @"app_data\default.xsl");
96:
97: return xd;
98: }
99: }
100:
101: ////////////////////////////////////////////////////////////////////////////
102:
103: /// <summary>
104: ///
105: /// </summary>
106: public XDocument Hsi() { return hsi; }
107:
108: ////////////////////////////////////////////////////////////////////////////
109:
110: /// <summary>
111: ///
112: /// </summary>
113: public static XDocument Maintenance
114: {
115: get
116: {
117: XDocument xd;
118:
119: xd = Ia.Cl.Model.Xml.Load(@"app_data\maintenance.xml", @"app_data\default.xsl");
120:
121: return xd;
122: }
123: set
124: {
125: }
126: }
127:
128: ////////////////////////////////////////////////////////////////////////////
129:
130: /// <summary>
131: ///
132: /// </summary>
133: public static XDocument Contact()
134: {
135: XDocument xd;
136:
137: xd = Ia.Cl.Model.Xml.Load(@"app_data\contact.xml");
138:
139: return xd;
140: }
141:
142: ////////////////////////////////////////////////////////////////////////////
143:
144: /// <summary>
145: ///
146: /// </summary>
147: public XDocument Event() { return _event; }
148:
149: #endregion
150:
151: #region SQL
152:
153: ////////////////////////////////////////////////////////////////////////////
154:
155: /// <summary>
156: ///
157: /// </summary>
158: public int UpdateOntPots(DataTable dt, out string result)
159: {
160: int op;
161: string id, sql, r1;
162:
163: op = 0;
164: result = sql = r1 = "";
165:
166: if (dt != null && dt.Rows.Count > 0)
167: {
168: // below: build string of entered keys
169: foreach (DataRow r in dt.Rows)
170: {
171: if (r["id"] != DBNull.Value && r["id"].ToString().Length > 0)
172: {
173: id = r["id"].ToString();
174: sql += "id=" + id + " OR ";
175: }
176: }
177:
178: sql = sql.Remove(sql.Length - 4, 4);
179:
180: string[] in_field = { "id", "ia_ont_id", "state", "card", "port", "td", "dn", "lan", "ip", "customer_info", "", "" };
181: string[] field = { "id", "ia_ont_id", "state", "card", "port", "td", "dn", "lan", "ip", "customer_info", "created", "updated" };
182: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
183:
184: op = sqlserver.Update(dt, "ia_ont_pots", @"SELECT * FROM [ia_ont_pots] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "all", out r1);
185: }
186:
187: result = r1;
188:
189: return op;
190: }
191:
192: ////////////////////////////////////////////////////////////////////////////
193:
194: /// <summary>
195: ///
196: /// </summary>
197: public int UpdateOntHsi(DataTable dt, out string result)
198: {
199: int op;
200: string id, sql, r1;
201:
202: op = 0;
203: result = sql = r1 = "";
204:
205: if (dt != null && dt.Rows.Count > 0)
206: {
207: // below: build string of entered keys
208: foreach (DataRow r in dt.Rows)
209: {
210: if (r["id"] != DBNull.Value && r["id"].ToString().Length > 0)
211: {
212: id = r["id"].ToString();
213: sql += "id=" + id + " OR ";
214: }
215: }
216:
217: sql = sql.Remove(sql.Length - 4, 4);
218:
219: string[] in_field = { "id", "ia_ont_id", "state", "card", "port", "service", "upstream_bandwidth_profile_id", "downstream_bandwidth_profile_id", "priority_queue_profile_id", "aes", "svlan", "label", "customer_id", "", "" };
220: string[] field = { "id", "ia_ont_id", "state", "card", "port", "service", "upstream_bandwidth_profile_id", "downstream_bandwidth_profile_id", "priority_queue_profile_id", "aes", "svlan", "label", "customer_id", "created", "updated" };
221: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
222:
223: op = sqlserver.Update(dt, "ia_ont_hsi", @"SELECT * FROM [ia_ont_hsi] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "all", out r1);
224: }
225:
226: result = r1;
227:
228: return op;
229: }
230:
231: ////////////////////////////////////////////////////////////////////////////
232:
233: /// <summary>
234: ///
235: /// </summary>
236: public int AmsAlarmDeleteRecord(out string result)
237: {
238: int op, c;
239: string sql;
240:
241: op = 0;
242: result = "";
243:
244: sql = "SELECT COUNT(1) AS c FROM ia_event";
245:
246: c = sqlserver.ScalarInteger(sql);
247:
248: sql = "DELETE ia_event";
249:
250: sqlserver.Sql(sql);
251:
252: if (c > 0) op = 1;
253: else op = 0;
254:
255: result = "(" + c + " records to be deleted)";
256:
257: return op;
258: }
259:
260: ////////////////////////////////////////////////////////////////////////////
261:
262: /// <summary>
263: ///
264: /// </summary>
265: public DataTable ReturnMiscValueForParameter(string parameter)
266: {
267: DataTable dt;
268:
269: dt = sqlserver.Select(@"SELECT value FROM ia_misc WHERE name = '" + parameter + "'");
270:
271: return dt;
272: }
273:
274: ////////////////////////////////////////////////////////////////////////////
275:
276: /// <summary>
277: ///
278: /// </summary>
279: public bool InsertMiscParameter(string parameter)
280: {
281: // below: insert first ia_misc record
282:
283: return sqlserver.Sql(@"INSERT INTO ia_misc (name) VALUES ('" + parameter + "')");
284: }
285:
286: ////////////////////////////////////////////////////////////////////////////
287:
288: /// <summary>
289: ///
290: /// </summary>
291: public bool UpdateMiscParameterWithValue(string parameter, string value)
292: {
293: // below: update ia_misc parameter with value
294: bool b;
295:
296: b = sqlserver.Sql(@"UPDATE ia_misc SET value = '" + value + "' WHERE name = '" + parameter + "'");
297:
298: return b;
299: }
300:
301: ////////////////////////////////////////////////////////////////////////////
302:
303: /// <summary>
304: ///
305: /// </summary>
306: public DataTable ReturnHsiPositionsFromOnts(string sql)
307: {
308: DataTable dt;
309:
310: dt = sqlserver.Select(@"
311: SELECT oh.id, st.ia_ont_id, st.gpon, st.olt_rack, st.olt_sub, st.olt_card, st.olt_port, st.ont, oh.card, oh.port, oh.service
312: FROM ia_standard AS st LEFT OUTER JOIN
313: ia_ont AS o ON o.id = st.ia_ont_id LEFT OUTER JOIN
314: ia_ont_hsi AS oh ON oh.ia_ont_id = o.id WHERE " + sql + @"
315: ORDER BY st.ia_ont_id
316: ");
317:
318: return dt;
319: }
320:
321: ////////////////////////////////////////////////////////////////////////////
322:
323: /// <summary>
324: ///
325: /// </summary>
326: public DataTable ReturnDistinctLceidToAllowEvenDistributionOfLans(int multiplier)
327: {
328: // below: Alcatel-Lucent recommends that modules are filled in multiples of 1000. I will check and select lceids such that lans are distributed in 1000 steps
329: DataTable dt;
330:
331: dt = sqlserver.Select(@"SELECT DISTINCT lceid FROM ia_protocol AS p WHERE (lceid IS NOT NULL) AND (lceid <> '') AND ((SELECT COUNT(1) AS count FROM ia_system AS s WHERE (lceid IS NOT NULL) AND (lan IS NOT NULL) AND (lceid = p.lceid)) <= " + ((int)(multiplier * 1024)).ToString() + @") ORDER BY p.lceid");
332:
333: return dt;
334: }
335:
336: ////////////////////////////////////////////////////////////////////////////
337:
338: /// <summary>
339: ///
340: /// </summary>
341: public DataTable ReturnDnValuesForLceidAndLan(ArrayList al)
342: {
343: int n;
344: StringBuilder sb;
345: DataTable dt;
346:
347: sb = new StringBuilder(1000);
348:
349: // below: create an SQL statement to read dn values for lan and lceid values in al, then add them as empty dn record into dt table
350: sb.Append("SELECT dn FROM ia_system WHERE ");
351:
352: for (n = 0; n < al.Count; n++)
353: {
354: sb.Append("(lan = " + al[n].ToString().Split(':')[0].ToString() + " AND lceid = '" + al[n].ToString().Split(':')[1].ToString() + "') OR ");
355: }
356:
357: sb.Append("(lan = " + al[0].ToString().Split(':')[0].ToString() + " AND lceid = '" + al[0].ToString().Split(':')[1].ToString() + "')");
358:
359: dt = sqlserver.Select(sb.ToString());
360:
361: return dt;
362: }
363:
364: ////////////////////////////////////////////////////////////////////////////
365: ////////////////////////////////////////////////////////////////////////////
366:
367: /// <summary>
368: ///
369: /// </summary>
370: public int UpdateSystemIp(int start, int end, string lceid, DataTable data_dt, out string result)
371: {
372: int op, lceid_id;
373: string sql;
374: XElement xe;
375:
376: op = 0;
377: result = "";
378:
379: lceid = lceid.ToLower();
380:
381: xe = (from u in service.Elements("service").Elements("lceid") where u.Attribute("name").Value == lceid select u).First();
382: lceid_id = int.Parse(xe.Attribute("id").Value);
383:
384: start = start + 8192 * 2 * (lceid_id / 2);
385: end = end + 8192 * 2 * (lceid_id / 2);
386:
387: sql = "id >= " + start + " AND id <=" + end; // +" AND lceid='" + lceid + "'";
388:
389: string[] in_field = { "id", "ia_lceid_lan_id", "lan", "lceid", "ip", "td", "", "" };
390: string[] field = { "id", "ia_lceid_lan_id", "lan", "lceid", "ip", "td", "created", "updated" };
391: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
392:
393: op = sqlserver.Update(data_dt, "ia_protocol", @"SELECT * FROM [ia_protocol] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "keep primary key", out result);
394:
395: return op;
396: }
397:
398: ////////////////////////////////////////////////////////////////////////////
399:
400: /// <summary>
401: ///
402: /// </summary>
403: public int UpdateSystemAndServiceAndServiceActivation(DataTable dt, out string result)
404: {
405: int op;
406: string dn, sql, r1, r2, r3;
407:
408: op = 0;
409: result = sql = r1 = r2 = r3 = "";
410:
411: if (dt != null && dt.Rows.Count > 0)
412: {
413: // below: build string of entered dn values
414: foreach (DataRow r in dt.Rows)
415: {
416: dn = r["dn"].ToString();
417:
418: if (dn.Length > 0) sql += "dn=" + dn + " OR ";
419: }
420:
421: sql = sql.Remove(sql.Length - 4, 4);
422:
423: string[] in_field = { "dn", "subtyp", "ocbp", "cw", "clip", "cfu", "tps", "ocbuc", "almcall", "acb", "", "" };
424: string[] field = { "dn", "subtyp", "ocbp", "cw", "clip", "cfu", "tps", "ocbuc", "almcall", "acb", "created", "updated" };
425: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
426:
427: op = sqlserver.Update(dt, "ia_service", @"SELECT * FROM [ia_service] WHERE (" + sql + ")", "dn", in_field, field, field_rule, false, "keep primary key", out r1);
428:
429: // below: issue a call to ia_system to update number status
430: // delete numbers that have "DN = 965xxxxxxxx DN IS NOT ASSIGNED"
431:
432: string[] in_field2 = { "dn", "ia_lceid_lan_id", "lceid", "lan", "", "" };
433: string[] field2 = { "dn", "ia_lceid_lan_id", "lceid", "lan", "created", "updated" };
434: Ia.Cl.Model.Db.SqlServer.F[] field_rule2 = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
435:
436: op += sqlserver.Update(dt, "ia_system", @"SELECT * FROM [ia_system] WHERE (" + sql + ")", "dn", in_field2, field2, field_rule2, false, "keep primary key", out r2);
437:
438: string[] in_field3 = { "dn", "cw_active", "cfu_dn", "ocbuc_active", "pin", "", "" };
439: string[] field3 = { "dn", "cw", "cfu", "ocbuc", "pin", "created", "updated" };
440: Ia.Cl.Model.Db.SqlServer.F[] field_rule3 = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Bit, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
441:
442: op += sqlserver.Update(dt, "ia_service_active", @"SELECT * FROM [ia_service_active] WHERE (" + sql + ")", "dn", in_field3, field3, field_rule3, false, "keep primary key", out r3);
443: }
444:
445: result = r1 + " " + r2 + " " + r3;
446:
447: return op;
448: }
449:
450: ////////////////////////////////////////////////////////////////////////////
451:
452: /// <summary>
453: ///
454: /// </summary>
455: public int UpdateOntPon(DataTable dt, out string result)
456: {
457: int op;
458: string id, sql, r1;
459:
460: op = 0;
461: result = sql = r1 = "";
462:
463: if (dt != null && dt.Rows.Count > 0)
464: {
465: // below: build string of entered dn values
466: foreach (DataRow r in dt.Rows)
467: {
468: if (r["id"] != DBNull.Value && r["id"].ToString().Length > 0)
469: {
470: id = r["id"].ToString();
471: sql += "id=" + id + " OR ";
472: }
473: }
474:
475: sql = sql.Remove(sql.Length - 4, 4);
476:
477: string[] in_field = { "id", "ont", "status", "ont_serial", "software", "", "" };
478: string[] field = { "id", "ont", "status", "ont_serial", "software", "created", "updated" };
479: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
480:
481: op = sqlserver.Update(dt, "ia_ont ", @"SELECT * FROM [ia_ont] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "all", out r1);
482: }
483:
484: result = r1;
485:
486: return op;
487: }
488:
489: ////////////////////////////////////////////////////////////////////////////
490:
491: /// <summary>
492: ///
493: /// </summary>
494: public int UpdateOnt(DataTable dt, out string result)
495: {
496: int op;
497: string id, sql, r1;
498:
499: op = 0;
500: result = sql = r1 = "";
501:
502: if (dt != null && dt.Rows.Count > 0)
503: {
504: // below: build string of entered keys
505: foreach (DataRow r in dt.Rows)
506: {
507: if (r["id"] != DBNull.Value && r["id"].ToString().Length > 0)
508: {
509: id = r["id"].ToString();
510: sql += "id=" + id + " OR ";
511: }
512: }
513:
514: sql = sql.Remove(sql.Length - 4, 4);
515:
516: string[] in_field = { "id", "family_type", "state", "ont_serial", "software_active", "software_passive", "software_planned", "desc1", "desc2", "ont", "", "" };
517: string[] field = { "id", "family_type", "state", "ont_serial", "software_active", "software_passive", "software_planned", "desc1", "desc2", "ont", "created", "updated" };
518: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
519:
520: op = sqlserver.Update(dt, "ia_ont ", @"SELECT * FROM [ia_ont] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "all", out r1);
521: }
522:
523: result = r1;
524:
525: return op;
526: }
527:
528: ////////////////////////////////////////////////////////////////////////////
529:
530: /// <summary>
531: ///
532: /// </summary>
533: public int UpdateOntVoip(DataTable dt, out string result)
534: {
535: int op;
536: string id, sql, r1;
537:
538: op = 0;
539: result = sql = r1 = "";
540:
541: if (dt != null && dt.Rows.Count > 0)
542: {
543: // below: build string of entered keys
544: foreach (DataRow r in dt.Rows)
545: {
546: if (r["id"] != DBNull.Value && r["id"].ToString().Length > 0)
547: {
548: id = r["id"].ToString();
549: sql += "id=" + id + " OR ";
550: }
551: }
552:
553: sql = sql.Remove(sql.Length - 4, 4);
554:
555: string[] in_field = { "id", "ia_ont_id", "state", "card", "lan", "ip", "ftp_ip", "mgc_ip", "configfile", "customer_id", "label", "", "" };
556: string[] field = { "id", "ia_ont_id", "state", "card", "lan", "ip", "ftp_ip", "mgc_ip", "configfile", "customer_id", "label", "created", "updated" };
557: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
558:
559: op = sqlserver.Update(dt, "ia_ont_voip", @"SELECT * FROM [ia_ont_voip] WHERE (" + sql + ")", "id", in_field, field, field_rule, false, "all", out r1);
560: }
561:
562: result = r1;
563:
564: return op;
565: }
566:
567: ////////////////////////////////////////////////////////////////////////////
568:
569: /// <summary>
570: ///
571: /// </summary>
572: public int InsertAmsAlarm(DataTable dt, out string result)
573: {
574: int op;
575: string s;
576: DateTime now;
577: StringBuilder sb;
578:
579: op = 0;
580: result = "";
581: sb = new StringBuilder(dt.Rows.Count * 100);
582: sb.Length = 0;
583: now = DateTime.UtcNow.AddHours(3);
584:
585: /*
586: JHR-2-1 08-07-09 23:03:41 * 0 REPT ALM PON "PON-1-1-4-1:MN,NEWONT,NSA,07-09,23-03-41: \"SERNUM = ALCLA0A23CCC, SLID = DEFAULT\""
587: |N O D E T I M E |N O D E T IME
588: |CLASS |SEVERITY
589: |AID |CAUSE |SERVICE EFFECT
590: |SYSTEM ID
591: */
592:
593: if (dt != null && dt.Rows.Count > 0)
594: {
595: // below: <type id="1" name="Alarm"/>
596:
597: sb.Append(@"INSERT INTO [ia_event] ([type_id],[event_time],[node_time],[system_id],[class],[aid],[cause],[severity],[service_effect],[num],[detail],[created]) ");
598:
599: foreach (DataRow r in dt.Rows)
600: {
601: sb.Append(@"SELECT 1,'" + r["event_time"].ToString() + @"','" + r["node_time"].ToString() + @"','" + r["system_id"].ToString() + @"','" + r["class"].ToString() + @"','" + r["aid"].ToString() + @"','" + r["cause"].ToString() + @"','" + r["severity"].ToString() + @"','" + r["service_effect"].ToString() + @"'," + r["num"].ToString() + @",'" + r["detail"].ToString() + @"','" + sqlserver.SmallDateTime(now) + "' \n");
602: sb.Append("UNION ALL \n");
603: }
604:
605: if (sb.Length > 0)
606: {
607: // below: remove last "UNION ALL\n"
608: s = sb.ToString();
609: s = s.Remove(s.Length - 11, 11);
610:
611: try
612: {
613: sqlserver.Sql(s);
614:
615: result = "(" + dt.Rows.Count + "-na/" + dt.Rows.Count + "/*)";
616: op = 1;
617: }
618: catch (Exception ex)
619: {
620: result = "Exception: " + ex.ToString() + " {" + s + "}";
621: op = -1;
622: }
623: }
624: else
625: {
626: result = "(0-na/0/*)";
627: op = 0;
628: }
629: }
630: else
631: {
632: result = "(0-na/0/*)";
633: op = 0;
634: }
635:
636: return op;
637: }
638:
639: ////////////////////////////////////////////////////////////////////////////
640:
641: /// <summary>
642: ///
643: /// </summary>
644: public int UpdateSQLServerWithValuesFromOracle(string name, string name_from, string primary_key, int row_count_max, DateTime from_datetime, DateTime to_datetime, out string result, bool temp)
645: {
646: int op;
647: string sql, from_datetime_sqlserver, from_datetime_oracle, to_datetime_sqlserver, to_datetime_oracle;
648: string from_ia_service_request_id, to_ia_service_request_id;
649: DataTable dt = null;
650: //DataTable from_dt;
651:
652: //string[] in_field, field;
653: //Ia.Cl.Model.Db.SqlServer.F[] field_rule;
654:
655: //from_dt = null;
656: op = 0;
657:
658: result = sql = "";
659: from_ia_service_request_id = to_ia_service_request_id = "0";
660:
661: // below: this will subtract 1 minute to guarantee no values are skipped in the < or > statement parts
662: from_datetime = from_datetime.AddMinutes(-from_datetime.Minute);
663:
664: // below: note a slight difference in format between SQL Server and Oracle here
665: from_datetime_sqlserver = "'" + from_datetime.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
666: to_datetime_sqlserver = "'" + to_datetime.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
667:
668: from_datetime_oracle = "'" + from_datetime.ToString("dd/MM/yyyy HH:mm:ss") + "'";
669: to_datetime_oracle = "'" + to_datetime.ToString("dd/MM/yyyy HH:mm:ss") + "'";
670:
671: if (temp)
672: {
673: dt = sqlserver.Select("SELECT TOP(1) id,request_time FROM ia_service_request WHERE (request_time >= " + from_datetime_sqlserver + ") ORDER BY request_time ASC, id ASC");
674:
675: if (dt != null && dt.Rows.Count > 0)
676: {
677: from_ia_service_request_id = dt.Rows[0]["id"].ToString();
678:
679: dt = sqlserver.Select("SELECT TOP(1) id,request_time FROM ia_service_request WHERE (request_time <= " + to_datetime_sqlserver + ") ORDER BY request_time DESC, id DESC");
680:
681: if (dt != null && dt.Rows.Count > 0)
682: {
683: to_ia_service_request_id = dt.Rows[0]["id"].ToString();
684:
685: sql = @"SELECT * FROM [" + name + "] WHERE (ia_service_request_id >= " + from_ia_service_request_id + " AND ia_service_request_id <= " + to_ia_service_request_id + ")";
686: }
687: }
688: }
689: else
690: {
691: sql = @"SELECT * FROM [" + name + "] WHERE (request_time >= " + from_datetime_sqlserver + " AND request_time <= " + to_datetime_sqlserver + ")";
692: }
693:
694: #if WINDOWS_FORM
695: try
696: {
697: if (temp)
698: {
699: dt = sqlserver.Select("SELECT * FROM " + name + " WHERE (ia_service_request_id >= " + from_ia_service_request_id + " AND ia_service_request_id <= " + to_ia_service_request_id + ") ORDER BY id ASC");
700: from_dt = oracle.Select("SELECT concat(cast(SRV_REQ_ID as varchar(10)) ,LPAD(cast(TECH_TYPE_ID as varchar(10)),2,'0') ) AS id,srv_req_id,tech_type_id, val FROM " + name_from + " WHERE (SRV_REQ_ID >= " + from_ia_service_request_id + " AND SRV_REQ_ID <= " + to_ia_service_request_id + ") ORDER BY SRV_REQ_ID ASC");
701:
702: string[] in_field2 = { "id", "srv_req_id", "tech_type_id", "val" };
703: string[] field2 = { "id", "ia_service_request_id", "type_id", "type_value" };
704: Ia.Cl.Model.Db.SqlServer.F[] field_rule2 = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St };
705:
706: in_field = in_field2;
707: field = field2;
708: field_rule = field_rule2;
709: }
710: else
711: {
712: dt = sqlserver.Select("SELECT * FROM " + name + " WHERE (request_time >= " + from_datetime_sqlserver + " AND request_time <= " + to_datetime_sqlserver + ") ORDER BY request_time ASC, id ASC");
713: oracle.Sql("ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'");
714:
715: from_dt = oracle.Select("SELECT SRV_REQ_ID AS id,* FROM " + name_from + " WHERE (REQ_DATE >= TO_DATE(" + from_datetime_oracle + ", 'dd/mm/yyyy hh24:mi:ss') AND REQ_DATE <= TO_DATE(" + to_datetime_oracle + ", 'dd/mm/yyyy hh24:mi:ss') ) ORDER BY REQ_DATE ASC, SRV_REQ_ID ASC");
716:
717: string[] in_field1 = { "srv_req_id", "srv_no", "srv_ser_no", "status", "req_date", "account_no", "name", "cust_cat_id", "address", "srv_id", "srv_cat_id" };
718: string[] field1 = { "id", "dn", "serial", "status", "request_time", "customer_id", "customer_name", "customer_category_id", "customer_address", "service_id", "service_category_id" };
719: sqlserver.F[] field_rule1 = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Srs, Ia.Cl.Model.Db.SqlServer.F.Sdt, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In };
720:
721: in_field = in_field1;
722: field = field1;
723: field_rule = field_rule1;
724: }
725:
726: if (from_dt != null && dt != null)
727: {
728: if (from_dt.Rows.Count > 0 || dt.Rows.Count > 0)
729: {
730: op = sqlserver.Update(from_dt, name, sql, primary_key, in_field, field, field_rule, true, "all", service, out result);
731: }
732: else
733: {
734: result = "(0-0/0/0)";
735: op = 0;
736: }
737: }
738: else if (from_dt == null && dt == null)
739: {
740: result = "(0-0/null/null)";
741: op = 0;
742: }
743: else if (from_dt == null && dt != null)
744: {
745: result = "(0-0/" + dt.Rows.Count + "/null)";
746: op = 0;
747: }
748: else if (from_dt != null && dt == null)
749: {
750: result = "(0-0/null/" + from_dt.Rows.Count + ")";
751: op = 0;
752: }
753: }
754: catch (Exception ex)
755: {
756: #if DEBUG
757: result += "Error: occured during process: " + ex.ToString();
758: #else
759: result += "Error: occured during process: " + ex.Message;
760: #endif
761: op = -1;
762: }
763:
764: #endif
765:
766: return op;
767: }
768:
769: ////////////////////////////////////////////////////////////////////////////
770: ////////////////////////////////////////////////////////////////////////////
771:
772: /// <summary>
773: ///
774: /// </summary>
775: public int UpdateSQLServerWithValuesFromOracle4(out ArrayList al, out string result)
776: {
777: int op;
778: string[] in_field, field;
779: ArrayList delete_al; // ???
780: DataTable dt = null;
781: DataTable from_dt;
782: Ia.Cl.Model.Db.SqlServer.F[] field_rule;
783:
784: result = "";
785:
786: delete_al = new ArrayList(10); // ??
787:
788: from_dt = null;
789: op = 0;
790:
791: #if WINDOWS_FORM
792:
793: try
794: {
795: dt = sqlserver.Select("SELECT * FROM ia_service_request WHERE (status <> 5) ORDER BY request_time ASC, id ASC");
796: oracle.Sql("ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'");
797: from_dt = oracle.Select("SELECT SRV_REQ_ID AS id,* FROM srv_req_fiper WHERE (STATUS <> 2005) ORDER BY REQ_DATE ASC,SRV_REQ_ID ASC");
798:
799: string[] in_field1 = { "srv_req_id", "srv_no", "status", "req_date", "account_no", "name", "cust_cat_id", "address", "srv_id", "srv_cat_id" };
800: string[] field1 = { "id", "dn", "status", "request_time", "customer_id", "customer_name", "customer_category_id", "customer_address", "service_id", "service_category_id" };
801: sqlserver.F[] field_rule1 = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Srs, Ia.Cl.Model.Db.SqlServer.F.Sdt, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In };
802:
803: in_field = in_field1;
804: field = field1;
805: field_rule = field_rule1;
806:
807: if (from_dt != null && dt != null)
808: {
809: if (from_dt.Rows.Count > 0 || dt.Rows.Count > 0)
810: {
811: op = sqlserver.Update(from_dt, "ia_service_request", @"SELECT * FROM [ia_service_request] WHERE (status <> 5)", "id", in_field, field, field_rule, true, "all", service, out result);
812: }
813: else
814: {
815: result = "(0-0/0/0)";
816: op = 0;
817: }
818: }
819: else if (from_dt == null && dt == null)
820: {
821: result = "(0-0/null/null)";
822: op = 0;
823: }
824: else if (from_dt == null && dt != null)
825: {
826: result = "(0-0/" + dt.Rows.Count + "/null)";
827: op = 0;
828: }
829: else if (from_dt != null && dt == null)
830: {
831: result = "(0-0/null/" + from_dt.Rows.Count + ")";
832: op = 0;
833: }
834: }
835: catch (Exception ex)
836: {
837: #if DEBUG
838: result += "Error: occured during process: " + ex.ToString();
839: #else
840: result += "Error: occured during process: " + ex.Message;
841: #endif
842: op = -1;
843: }
844:
845: #endif
846: al = delete_al;
847:
848: return op;
849: }
850:
851: ////////////////////////////////////////////////////////////////////////////
852: ////////////////////////////////////////////////////////////////////////////
853:
854: /// <summary>
855: ///
856: /// </summary>
857: public void ReturnDataTableOfRecordsFromFieldUpdatedBySupplier(DataTable misc_dt, ref DataTable dt)
858: {
859: string sql;
860: StringBuilder sb;
861:
862: if (misc_dt.Columns.Count != 0)
863: {
864: DataColumn[] misc_keys = new DataColumn[1];
865: misc_keys[0] = misc_dt.Columns["id"];
866: misc_dt.PrimaryKey = misc_keys;
867:
868: // below: read the records in field that were updated by supplier
869: if (misc_dt.Rows.Count > 0)
870: {
871: sb = new StringBuilder(misc_dt.Rows.Count * 20);
872:
873: foreach (DataRow r in misc_dt.Rows) sb.Append("f.id=" + r["id"].ToString() + " OR ");
874:
875: sql = sb.ToString();
876:
877: if (sql.Length > 0)
878: {
879: sql = sql.Remove(sql.Length - 4, 4);
880:
881: dt = sqlserver.Select(@"SELECT * FROM ia_field AS f WHERE (" + sql + ")");
882:
883: DataColumn[] m_keys = new DataColumn[1];
884: m_keys[0] = dt.Columns["id"];
885: dt.PrimaryKey = m_keys;
886: }
887: }
888: }
889: }
890:
891: ////////////////////////////////////////////////////////////////////////////
892: ////////////////////////////////////////////////////////////////////////////
893:
894: /// <summary>
895: ///
896: /// </summary>
897: public int UpdateField(string file, DataTable dt, DataTable m_dt, DataTable misc_dt, out string result)
898: {
899: bool b;
900: int count_in, count, op;
901: string sql, t;
902: StringBuilder sb;
903: DataRow misc_dr, m_dr;
904:
905: // below: update database
906: string[] field_xml = { "id", "area", "premises_old", "premises_new", "street", "block", "pon", "ont", "odf", "gpon", "olt_rack", "olt_sub", "olt_card", "olt_port", "ont_serial", "ont_type", "source_id", "", "" };
907: string[] field_db = { "id", "area", "premises_old", "premises_new", "street", "block", "pon", "ont", "odf", "gpon", "olt_rack", "olt_sub", "olt_card", "olt_port", "ont_serial", "ont_type", "source_id", "created", "updated" };
908: Ia.Cl.Model.Db.SqlServer.F[] field_rule = { Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.St, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.In, Ia.Cl.Model.Db.SqlServer.F.Cr, Ia.Cl.Model.Db.SqlServer.F.Up };
909:
910: op = 0;
911: result = "";
912:
913: if (dt.Rows.Count > 0)
914: {
915: sb = new StringBuilder(dt.Rows.Count * 20);
916:
917: foreach (DataRow r in dt.Rows) sb.Append("f.id=" + r["id"].ToString() + " OR ");
918:
919: sql = sb.ToString();
920:
921: if (sql.Length > 0)
922: {
923: sql = sql.Remove(sql.Length - 4, 4);
924:
925: op = sqlserver.Update(dt, "ia_field", @"SELECT * FROM ia_field AS f WHERE (" + sql + ")", "id", field_xml, field_db, field_rule, false, "all", out t);
926:
927: result += " " + t.Replace("(", "(" + file.Replace(".txt", ": ")); // change to (SLA: 9-2/1707/1700)
928:
929: // below: I will remove any items in ia_misc "supplier_ont_installed_add" that exacly match a value read from the submittal files:
930:
931: // below: I will loop though data returned and will delete all entries from misc_dt that have same id
932:
933: b = false;
934:
935: if (misc_dt.Columns.Count != 0 && m_dt.Columns.Count != 0)
936: {
937: foreach (DataRow r in dt.Rows)
938: {
939: misc_dr = misc_dt.Rows.Find(r["id"].ToString());
940: m_dr = m_dt.Rows.Find(r["id"].ToString());
941:
942: if (misc_dr != null && m_dr != null)
943: {
944: if (m_dr["id"].ToString() == r["id"].ToString() &&
945: m_dr["area"].ToString() == r["area"].ToString() &&
946: m_dr["premises_old"].ToString() == r["premises_old"].ToString() &&
947: m_dr["premises_new"].ToString() == r["premises_new"].ToString() &&
948: m_dr["street"].ToString() == r["street"].ToString() &&
949: m_dr["block"].ToString() == r["block"].ToString() &&
950: m_dr["pon"].ToString() == r["pon"].ToString() &&
951: m_dr["ont"].ToString() == r["ont"].ToString() &&
952: m_dr["odf"].ToString() == r["odf"].ToString() &&
953: m_dr["gpon"].ToString() == r["gpon"].ToString() &&
954: m_dr["olt_rack"].ToString() == r["olt_rack"].ToString() &&
955: m_dr["olt_sub"].ToString() == r["olt_sub"].ToString() &&
956: m_dr["olt_card"].ToString() == r["olt_card"].ToString() &&
957: m_dr["olt_port"].ToString() == r["olt_port"].ToString() &&
958: m_dr["ont_serial"].ToString() == r["ont_serial"].ToString() &&
959: m_dr["ont_type"].ToString() == r["ont_type"].ToString())
960: {
961: misc_dr.Delete();
962: b = true;
963: }
964: }
965: }
966: }
967:
968: if (b)
969: {
970: misc_dt.AcceptChanges();
971: sqlserver.UpdateFromMiscellaneousTable("supplier_ont_installed_add", misc_dt);
972: }
973: }
974: else
975: {
976: result += "Update: Error sql is zero";
977: }
978: }
979: else
980: {
981: result += "Update: Error dt.Rows.Count is zero";
982: }
983:
984: return op;
985: }
986:
987: #endregion
988:
989: ////////////////////////////////////////////////////////////////////////////
990: ////////////////////////////////////////////////////////////////////////////
991: }
992:
993: ////////////////////////////////////////////////////////////////////////////
994: ////////////////////////////////////////////////////////////////////////////
995: }