API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 20th, 2017
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 46.63 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Collections;
  4. using System.ComponentModel;
  5. using System.Data;
  6. //using System.Drawing;
  7. using System.Linq;
  8. using System.Text;
  9. //using System.Windows.Forms;
  10. using System.Data.Odbc;
  11. using System.Data.OleDb;
  12. using System.Xml;
  13. using System.IO;
  14. using System.IO.Ports;
  15. using System.Net.Mail;
  16. using System.Diagnostics;
  17. using System.Security;
  18. using System.Security.Permissions;
  19. using System.Text.RegularExpressions;
  20.  
  21. namespace aGQaInvoiceMonitoringSystem
  22. {
  23. class InvoiceMonitor
  24. {
  25. public AutoCert.AutoCert ac = new AutoCert.AutoCert();
  26. public DateTime daysBack = (DateTime)Properties.Settings.Default["SeedDate"];
  27. public FileSystemWatcher watcher = new FileSystemWatcher();
  28. [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
  29.  
  30. private void OnChanged(object source, FileSystemEventArgs e)
  31. {
  32. try
  33. {
  34. DoInvoice();
  35. }
  36. catch (Exception err)
  37. {
  38. LogMonitorEvent(err.ToString(), true);
  39. SendEmail(true, "tkrapf@agqa.com", "IDD_AIMS@aGqa.com", "", "", "MONITORING FAILED!!!!", err.ToString());
  40. }
  41. }
  42.  
  43. public void MonitorStart()
  44. {
  45. watcher.Path = Properties.Settings.Default["WatchPath"].ToString();
  46. watcher.NotifyFilter = NotifyFilters.LastWrite;
  47. watcher.Filter = "recvbls.dbf";
  48. watcher.Changed += new FileSystemEventHandler(OnChanged);
  49. watcher.EnableRaisingEvents = true;
  50. }
  51.  
  52. public void MonitorStop()
  53. {
  54. watcher.EnableRaisingEvents = false;
  55. }
  56.  
  57. private void DoInvoice()
  58. {
  59. try
  60. {
  61. OdbcConnection conn = new OdbcConnection();
  62. conn.ConnectionString = Properties.Settings.Default["ConnStrFoxPro"].ToString();
  63. conn.Open();
  64.  
  65. string sqlSt = "SELECT salesman.name as 'supplierSalesman', * FROM recvbls JOIN customer ON recvbls.cust_no = customer.cust_no JOIN salesman ON recvbls.salesman1 = salesman.salesman ";
  66. sqlSt += " WHERE d_type='I' AND date >= {^" + daysBack.ToString("yyyy-MM-dd") + "} ";
  67. if ((bool)Properties.Settings.Default["WhitelistedRetailersOnly"]) { sqlSt += " AND customer.cust_no IN (SELECT alias FROM agaRetailers WHERE active = 1) "; }
  68. sqlSt += " ORDER BY INVOICE_NO";
  69.  
  70. OdbcDataAdapter dAdapter = new OdbcDataAdapter(sqlSt, conn);
  71. DataSet dsInvoices = new DataSet();
  72. dAdapter.Fill(dsInvoices, "invoices");
  73.  
  74. string invoices = "";
  75. DataSet dsItems;
  76. Boolean isNotLooseStoneItem;
  77.  
  78. foreach (DataRow i in dsInvoices.Tables["invoices"].Rows)
  79. {
  80. //is this a new invoice or one that has been processed to aGQa AutoCert already?
  81. //if this invoice has been processed already, change it's revision number and carry on without sending again to aGQa.
  82. if (iMonHasInvoiceBeenRecordedPreviously(i["invoice_no"].ToString()))
  83. { continue; } //invoice has already been processed, so just skip and goto the next in the list
  84.  
  85. //Get invoice items for this invoice
  86.  
  87. string itemsJew = " ";
  88. itemsJew += " jewhst.item_no as 'style', "; //style
  89. itemsJew += " q2 as 'itemQty', "; //item qty
  90. itemsJew += " jewhst.price as 'price', "; //invoice price
  91. itemsJew += " category2 as 'cut', "; //cut
  92. itemsJew += " color as 'color', "; //color
  93. itemsJew += " clarity as 'clarity', "; //clarity
  94. itemsJew += " sto_no as 'carat', "; //carat
  95. itemsJew += " category1 as 'department', "; //department
  96. itemsJew += " category3 as 'category', "; //category
  97. itemsJew += " metal as 'metal', "; //metal
  98. itemsJew += " sty_no as 'StyleForImage' "; //special order
  99. sqlSt = "SELECT " + itemsJew + " FROM jewhst JOIN jewelry ON jewhst.item_no = jewelry.item_no JOIN qualities ON jewelry.dia_qual = qualities.quality WHERE d_no = " + i["d_no"] + " ORDER BY jewhst.item_no ";
  100.  
  101. isNotLooseStoneItem = true;
  102. dAdapter = new OdbcDataAdapter(sqlSt, conn);
  103. dsItems = new DataSet();
  104. dAdapter.Fill(dsItems, "items");
  105.  
  106. if (dsItems.Tables["items"].Rows.Count < 1) //invoice had no manufacturered items, maybe it has loose gem items from the diaHst table?
  107. {
  108. isNotLooseStoneItem = false;
  109. string itemsDia = " ";
  110. itemsDia += " descrip as 'description', "; //invoice description line
  111. itemsDia += " cerhst.item_no as 'style', "; //style
  112. itemsDia += " q2 as 'itemQty', "; //item qty
  113. itemsDia += " cerhst.price as 'price', "; //invoice price
  114. itemsDia += " category1 as 'cut', "; //cut
  115. itemsDia += " category2 as 'color', "; //color
  116. itemsDia += " category3 as 'clarity', "; //clarity
  117. itemsDia += " avg_wght as 'carat', "; //carat
  118. itemsDia += " 'Loose Diamond(s)' as 'department', "; //department
  119. itemsDia += " 'Loose Diamond(s)' as 'category', "; //department
  120. itemsDia += " category4 as 'labName', "; //metal
  121. itemsDia += " cert_no as 'labCertNo', "; //qty1
  122. itemsDia += " length, width, depth "; //qty2
  123. sqlSt = " SELECT " + itemsDia + " FROM cerhst JOIN certs ON cerhst.item_no = certs.item_no WHERE d_no = " + i["d_no"] + " ORDER BY cerhst.item_no ";
  124. dAdapter = new OdbcDataAdapter(sqlSt, conn);
  125. dsItems = new DataSet();
  126. dAdapter.Fill(dsItems, "items");
  127.  
  128. if (dsItems.Tables["items"].Rows.Count < 1) //no manufactured items or loose stones found.... strange! just continue on, skipping this invoice.
  129. { continue; }
  130. }
  131.  
  132. XmlDocument doc = new XmlDocument();
  133. XmlElement root = doc.CreateElement("Invoices");
  134. XmlElement nInv;
  135. XmlElement node;
  136.  
  137. nInv = doc.CreateElement("Invoice");
  138. root.AppendChild(nInv);
  139.  
  140. //Invoice Date
  141. node = doc.CreateElement("Date");
  142. node.InnerText = ((DateTime)i["date"]).ToShortDateString();
  143. nInv.AppendChild(node);
  144.  
  145. //Invoice No
  146. node = doc.CreateElement("Number");
  147. node.InnerText = i["invoice_no"].ToString().Trim();
  148. nInv.AppendChild(node);
  149.  
  150. //Retailer Node
  151. XmlElement nRetailer;
  152. nRetailer = doc.CreateElement("Retailer");
  153. nInv.AppendChild(nRetailer);
  154.  
  155. //Retailer ID
  156. node = doc.CreateElement("ID");
  157. node.InnerText = i["cust_no"].ToString().Trim();
  158. nRetailer.AppendChild(node);
  159.  
  160. //Retailer Name
  161. node = doc.CreateElement("Name");
  162. node.InnerText = i["Name"].ToString().Trim();
  163. nRetailer.AppendChild(node);
  164.  
  165. //Retailer Address
  166. string addStr = "";
  167. if (i["Address"].ToString().Trim() != "") { addStr += i["Address"].ToString().Trim() + ", "; }
  168. if (i["Address2"].ToString().Trim() != "") { addStr += i["Address2"].ToString().Trim() + ", "; }
  169. if (i["City"].ToString().Trim() != "") { addStr += i["City"].ToString().Trim() + ", "; }
  170. if (i["State"].ToString().Trim() != "") { addStr += i["State"].ToString().Trim() + ", "; }
  171. if (i["Zip"].ToString().Trim() != "") { addStr += i["Zip"].ToString().Trim(); }
  172.  
  173. node = doc.CreateElement("Address");
  174. node.InnerText = addStr;
  175. nRetailer.AppendChild(node);
  176.  
  177. //Emails Node
  178. XmlElement nEmails;
  179. nEmails = doc.CreateElement("Emails");
  180. nInv.AppendChild(nEmails);
  181.  
  182. //Retailer Salesperson Email
  183. node = doc.CreateElement("Email");
  184. node.SetAttribute("type", "retailerSalesperson");
  185. node.InnerText = i["email"].ToString().Trim();
  186. nEmails.AppendChild(node);
  187.  
  188. XmlElement nSupplierSpecificInvoice;
  189. nSupplierSpecificInvoice = doc.CreateElement("SupplierSpecific");
  190. nInv.AppendChild(nSupplierSpecificInvoice);
  191.  
  192. node = doc.CreateElement("AddressStreet");
  193. node.InnerText = i["Address"].ToString().Trim();
  194. nSupplierSpecificInvoice.AppendChild(node);
  195.  
  196. node = doc.CreateElement("AddressStreet2");
  197. node.InnerText = i["Address2"].ToString().Trim();
  198. nSupplierSpecificInvoice.AppendChild(node);
  199.  
  200. node = doc.CreateElement("AddressCity");
  201. node.InnerText = i["City"].ToString().Trim();
  202. nSupplierSpecificInvoice.AppendChild(node);
  203.  
  204. node = doc.CreateElement("AddressState");
  205. node.InnerText = i["State"].ToString().Trim();
  206. nSupplierSpecificInvoice.AppendChild(node);
  207.  
  208. node = doc.CreateElement("AddressZip");
  209. node.InnerText = i["Zip"].ToString().Trim();
  210. nSupplierSpecificInvoice.AppendChild(node);
  211.  
  212. node = doc.CreateElement("ShipMethod");
  213. node.InnerText = i["shipvia"].ToString().Trim();
  214. nSupplierSpecificInvoice.AppendChild(node);
  215.  
  216. node = doc.CreateElement("Shippable");
  217. node.InnerText = "true";
  218. nSupplierSpecificInvoice.AppendChild(node);
  219.  
  220. node = doc.CreateElement("PONumber");
  221. node.InnerText = i["po_no"].ToString().Trim();
  222. nSupplierSpecificInvoice.AppendChild(node);
  223.  
  224. node = doc.CreateElement("Salesman");
  225. node.InnerText = i["supplierSalesman"].ToString().Trim();
  226. nSupplierSpecificInvoice.AppendChild(node);
  227.  
  228. node = doc.CreateElement("orderedby");
  229. node.InnerText = i["attention"].ToString().Trim();
  230. nSupplierSpecificInvoice.AppendChild(node);
  231.  
  232. //Terms
  233. node = doc.CreateElement("Terms");
  234. node.InnerText = i["Terms"].ToString().Trim();
  235. nSupplierSpecificInvoice.AppendChild(node);
  236.  
  237. //Due Date
  238. node = doc.CreateElement("DueDate");
  239. node.InnerText = ((DateTime)i["d_date"]).ToShortDateString();
  240. nSupplierSpecificInvoice.AppendChild(node);
  241.  
  242. //put in Cert/Item nodes
  243. XmlElement nCerts;
  244. nCerts = doc.CreateElement("Certificates");
  245. nInv.AppendChild(nCerts);
  246.  
  247. XmlElement nCert;
  248.  
  249. foreach (DataRow r in dsItems.Tables["items"].Rows)
  250. {
  251. nCert = doc.CreateElement("Certificate");
  252. nCert.SetAttribute("templatable", "true");
  253. nCerts.AppendChild(nCert);
  254.  
  255. node = doc.CreateElement("SpecialOrder");
  256. if (isNotLooseStoneItem) { if (r["StyleForImage"].ToString().Trim() == "SP ORDER") { node.InnerText = "true"; } else { node.InnerText = "false"; } }
  257. else { node.InnerText = "false"; }
  258. nCert.AppendChild(node);
  259.  
  260. node = doc.CreateElement("RingSize");
  261. if (isNotLooseStoneItem)
  262. {
  263. if (r["Style"].ToString().Trim().Substring(0, 1).ToUpper().Contains("R") || r["Style"].ToString().Trim().Substring(0, 1).ToUpper().Contains("Y"))
  264. { node.InnerText = "Stock"; }
  265. else
  266. { node.InnerText = ""; }
  267. }
  268. else { node.InnerText = ""; }
  269. nCert.AppendChild(node);
  270.  
  271. node = doc.CreateElement("Style");
  272. node.InnerText = r["style"].ToString().Trim();
  273. nCert.AppendChild(node);
  274.  
  275. node = doc.CreateElement("Stock");
  276. node.InnerText = r["style"].ToString().Trim();
  277. nCert.AppendChild(node);
  278.  
  279. node = doc.CreateElement("Quantity");
  280. node.InnerText = (Convert.ToInt32(r["itemQty"]) * -1).ToString().Trim(); //quantities are negative in their tables because they are being sold out of inventory
  281. nCert.AppendChild(node);
  282.  
  283. node = doc.CreateElement("Price");
  284. node.InnerText = r["price"].ToString().Trim();
  285. nCert.AppendChild(node);
  286. //---------------------------------------------------------------Supplier Specific Items-------------------------
  287. XmlElement nSupplierSpecific;
  288. nSupplierSpecific = doc.CreateElement("SupplierSpecific");
  289. nCert.AppendChild(nSupplierSpecific);
  290.  
  291. node = doc.CreateElement("Valid");
  292. node.InnerText = "1";
  293. nSupplierSpecific.AppendChild(node);
  294.  
  295. node = doc.CreateElement("Templatable");
  296. node.InnerText = "true";
  297. nSupplierSpecific.AppendChild(node);
  298.  
  299. string aliasedCategoryID = "0";
  300. string aliasedQty = "";
  301.  
  302. node = doc.CreateElement("Image");
  303. node.InnerText = AliasImageFromStyle(r["style"].ToString().Trim(), isNotLooseStoneItem, aliasQualityCut(r["cut"].ToString().Trim()), out aliasedCategoryID);
  304. nSupplierSpecific.AppendChild(node);
  305.  
  306. if (aliasedCategoryID != "0") { aliasedQty = aliasQualityQty(aliasedCategoryID); }
  307.  
  308. node = doc.CreateElement("CategoryID");
  309. if (isNotLooseStoneItem) { node.InnerText = aliasedCategoryID; }
  310. else { node.InnerText = "85"; }
  311. nSupplierSpecific.AppendChild(node);
  312.  
  313. if (isNotLooseStoneItem)
  314. {
  315. node = doc.CreateElement("Metal");
  316. node.InnerText = aliasMetal(r["metal"].ToString().Trim());
  317. nSupplierSpecific.AppendChild(node);
  318. }
  319.  
  320. node = doc.CreateElement("ItemTypeID");
  321. node.InnerText = "1";
  322. nSupplierSpecific.AppendChild(node);
  323.  
  324. if (isNotLooseStoneItem)
  325. {
  326. node = doc.CreateElement("ProductLineID");
  327. node.InnerText = "6";
  328. nSupplierSpecific.AppendChild(node);
  329.  
  330. node = doc.CreateElement("Subcategory");
  331. node.InnerText = aliasProductCategory(r["category"].ToString().Trim());
  332. nSupplierSpecific.AppendChild(node);
  333.  
  334. XmlElement nOrns;
  335. nOrns = doc.CreateElement("Ornamentals");
  336. nSupplierSpecific.AppendChild(nOrns);
  337.  
  338. XmlElement nOrn;
  339. nOrn = doc.CreateElement("Ornamental");
  340. nOrns.AppendChild(nOrn);
  341.  
  342. node = doc.CreateElement("Label");
  343. node.InnerText = "Diamond(s)";
  344. nOrn.AppendChild(node);
  345.  
  346. //4 C's
  347. node = doc.CreateElement("Cut");
  348. if (aliasQualityCut(r["cut"].ToString().Trim()).Length > 0)
  349. { node.InnerText = aliasQualityCut(r["cut"].ToString().Trim()); }
  350. else
  351. { node.InnerText = r["cut"].ToString().Trim(); }
  352. nOrn.AppendChild(node);
  353.  
  354. node = doc.CreateElement("Color");
  355. if (aliasQualityColor(r["style"].ToString().Trim()).Length > 0)
  356. { node.InnerText = aliasQualityColor(r["style"].ToString().Trim()); }
  357. else
  358. { if (r["color"].ToString().Trim() != "") { node.InnerText = r["color"].ToString().Trim(); } }
  359. nOrn.AppendChild(node);
  360.  
  361. node = doc.CreateElement("Clarity");
  362. if (aliasQualityClarity(r["style"].ToString().Trim()).Length > 0)
  363. { node.InnerText = aliasQualityClarity(r["style"].ToString().Trim()); }
  364. else
  365. { if (r["clarity"].ToString().Trim() != "") { node.InnerText = r["clarity"].ToString().Trim(); } }
  366. nOrn.AppendChild(node);
  367.  
  368. node = doc.CreateElement("Carat");
  369. node.InnerText = FractionToDecimal(r["carat"].ToString().Trim());
  370. nOrn.AppendChild(node);
  371.  
  372. node = doc.CreateElement("TQty");
  373. node.InnerText = aliasedQty;
  374. nOrn.AppendChild(node);
  375. }
  376. else //loose stones go thru this section
  377. {
  378. node = doc.CreateElement("ProductLineID");
  379. node.InnerText = "9";
  380. nSupplierSpecific.AppendChild(node);
  381.  
  382. node = doc.CreateElement("Description");
  383. node.InnerText = r["description"].ToString().Trim();
  384. nSupplierSpecificInvoice.AppendChild(node);
  385.  
  386. XmlElement nStones;
  387. nStones = doc.CreateElement("CenterStones");
  388. nSupplierSpecific.AppendChild(nStones);
  389.  
  390. XmlElement nStone;
  391. nStone = doc.CreateElement("CenterStone");
  392. nStones.AppendChild(nStone);
  393.  
  394. node = doc.CreateElement("Label");
  395. node.InnerText = "Diamond(s)";
  396. nStone.AppendChild(node);
  397.  
  398. //4 C's
  399. node = doc.CreateElement("Cut");
  400. if (aliasQualityCut(r["cut"].ToString().Trim()).Length > 0)
  401. { node.InnerText = aliasQualityCut(r["cut"].ToString().Trim()); }
  402. else
  403. { node.InnerText = r["cut"].ToString().Trim(); }
  404. nStone.AppendChild(node);
  405.  
  406. node = doc.CreateElement("Color");
  407. if (r["color"].ToString().Trim() != "") { node.InnerText = r["color"].ToString().Trim(); }
  408. nStone.AppendChild(node);
  409.  
  410. node = doc.CreateElement("Clarity");
  411. if (r["clarity"].ToString().Trim() != "") { node.InnerText = r["clarity"].ToString().Trim(); }
  412. nStone.AppendChild(node);
  413.  
  414. node = doc.CreateElement("Carat");
  415. node.InnerText = FractionToDecimal(r["carat"].ToString().Trim());
  416. nStone.AppendChild(node);
  417.  
  418. node = doc.CreateElement("TQty");
  419. node.InnerText = "1";
  420. nStone.AppendChild(node);
  421.  
  422. node = doc.CreateElement("LabName");
  423. node.InnerText = r["labName"].ToString().Trim();
  424. nStone.AppendChild(node);
  425.  
  426. node = doc.CreateElement("LabCertNo");
  427. node.InnerText = r["labCertNo"].ToString().Trim();
  428. nStone.AppendChild(node);
  429.  
  430. node = doc.CreateElement("DiamondDimensions");
  431. string dimensions = "";
  432. if (Convert.ToDecimal(r["length"]) > 0) { dimensions += r["length"].ToString().Trim() + " x "; }
  433. if (Convert.ToDecimal(r["width"]) > 0) { dimensions += r["width"].ToString().Trim(); }
  434. if (Convert.ToDecimal(r["depth"]) > 0) { dimensions += " x " + r["depth"].ToString().Trim(); }
  435. node.InnerText = dimensions;
  436. nStone.AppendChild(node);
  437.  
  438. }
  439. }
  440.  
  441. doc.AppendChild(root);
  442.  
  443. invoices = IndentXMLString(doc.InnerXml) + Environment.NewLine + Environment.NewLine;
  444. SendEmail(false, "tkrapf@agqa.com", "IDD_Invoices@aGQa.com", "", "", "IDD Invoice: " + i["invoice_no"].ToString().Trim() + " Doc#: " + i["d_no"].ToString().Trim() + " Date: " + ((DateTime)i["date"]).ToShortDateString() + " - No of Item(s): " + dsItems.Tables["items"].Rows.Count.ToString() + " - Orn: " + isNotLooseStoneItem.ToString(), invoices);
  445.  
  446. try
  447. {
  448. ac.UpdateDatabase("IDD", "diamonds", invoices);
  449. }
  450. catch (Exception err)
  451. {
  452. SendEmail(true, "tkrapf@agqa.com", "IDD_Invoice@aGqa.com", "", "", "MONITORING FAILED!!!!" + i["Invoice_No"].ToString().Trim(), err.ToString());
  453. }
  454.  
  455. //this invoice has now been processed, mark it as such in the agaIMon table.
  456. iMonInsertNewInvoice(i["invoice_no"].ToString());
  457. }
  458. conn.Close();
  459. }
  460. catch (Exception err)
  461. {
  462. LogMonitorEvent(err.ToString(), true);
  463. SendEmail(true, "tkrapf@agqa.com", "IDD_Invoice@aGqa.com", "", "", "DO_INVOICE FAILED!!!!", err.ToString());
  464. }
  465. }
  466.  
  467. #region utilsGeneral
  468. private bool iMonHasInvoiceBeenRecordedPreviously(string invoiceNo)
  469. {
  470. OdbcConnection conn = new OdbcConnection();
  471. conn.ConnectionString = Properties.Settings.Default["ConnStrFoxPro"].ToString();
  472. conn.Open();
  473. string sqlSt = "SELECT * FROM agaIMon WHERE invoice = '" + invoiceNo + "'";
  474. OdbcDataAdapter dAdapter = new OdbcDataAdapter(sqlSt, conn);
  475. DataSet dsInvoices = new DataSet();
  476. dAdapter.Fill(dsInvoices, "inv");
  477. if (dsInvoices.Tables["inv"].Rows.Count > 0)
  478. { return true; }
  479. else
  480. { return false; }
  481. }
  482. public void LogMonitorEvent(string sEvent, bool isError)
  483. {
  484. string sSource;
  485. string sLog;
  486. sSource = "aGQa Invoice Monitoring System";
  487. sLog = "Application";
  488. if (!EventLog.SourceExists(sSource)) EventLog.CreateEventSource(sSource, sLog);
  489. if (!isError)
  490. { EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Information); }
  491. else
  492. { EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Error); }
  493. }
  494. private string IndentXMLString(string xml)
  495. {
  496. string outXml = string.Empty;
  497. MemoryStream ms = new MemoryStream();
  498. XmlTextWriter xtw = new XmlTextWriter(ms, Encoding.Unicode);
  499. XmlDocument doc = new XmlDocument();
  500. try
  501. {
  502. doc.LoadXml(xml);
  503. xtw.Formatting = Formatting.Indented;
  504. doc.WriteContentTo(xtw);
  505. xtw.Flush();
  506. ms.Seek(0, SeekOrigin.Begin);
  507. StreamReader sr = new StreamReader(ms);
  508. return sr.ReadToEnd();
  509. }
  510. catch (Exception err)
  511. {
  512. LogMonitorEvent(err.ToString(), true);
  513. SendEmail(true, "tkrapf@agqa.com", "IDD_Invoice@aGqa.com", "", "", "Formating XML for Email FAILED!!!!", err.ToString());
  514. return string.Empty;
  515. }
  516. }
  517. public void SendEmail(bool isHtml, string eTo, string eFrom, string eCC, string eBCC, string eSubject, string eBody)
  518. {
  519. if (!eTo.Contains("@")) { return; }
  520.  
  521. MailMessage mail = new MailMessage(eFrom, eTo, eSubject, eBody);
  522. mail.To.Add("dwendorf@aGQa.com");
  523. mail.To.Add("scalhoun@aGQa.com");
  524. if (isHtml) { mail.IsBodyHtml = true; }
  525. if (eCC.Length > 0) { mail.CC.Add(eCC); };
  526. if (eBCC.Length > 0) { mail.Bcc.Add(eBCC); };
  527. SmtpClient sender = new SmtpClient();
  528. sender.Host = "24.196.66.22"; //Properties.Settings.Default["MailServer"].ToString();
  529. sender.Port = 25;
  530. try
  531. {
  532. sender.Send(mail);
  533. }
  534. catch (Exception ex)
  535. { }
  536.  
  537. }
  538. private void iMonInsertNewInvoice(string invoiceNo)
  539. {
  540. OdbcConnection conn = new OdbcConnection();
  541. conn.ConnectionString = Properties.Settings.Default["ConnStrFoxPro"].ToString();
  542. conn.Open();
  543. string sqlSt = "SELECT guid, date, invoice, revision FROM agaIMon";
  544.  
  545. OdbcDataAdapter dAdapter = new OdbcDataAdapter(sqlSt, conn);
  546. DataSet dsInvoices = new DataSet();
  547. dAdapter.Fill(dsInvoices, "inv");
  548.  
  549. OdbcCommand cmd = new OdbcCommand();
  550. string myGuid = System.Guid.NewGuid().ToString();
  551. string sql = "INSERT INTO agaIMon ([guid], [date], [invoice], [revision]) VALUES ";
  552. sql += "('" + myGuid + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + invoiceNo + "','0')";
  553. cmd = new OdbcCommand(sql, conn);
  554. cmd.ExecuteNonQuery();
  555. }
  556.  
  557. #endregion //utilsGeneral
  558.  
  559. #region ItemAliases
  560. static string AliasImageFromStyle(string style, bool isNotLooseStone, string cut, out string categoryId)
  561. {
  562. //default this to zero incase we don't catch the category correctly.
  563. categoryId = "0";
  564.  
  565. if (!isNotLooseStone) //it IS a loose stone!
  566. {
  567. string folder = "1345/";
  568. categoryId = "85";
  569. switch (cut.ToLower())
  570. {
  571. case "asscher": return folder + "asscher.png";
  572. case "baguette": return folder + "baguette.png";
  573. case "cushion": return folder + "cushion.png";
  574. case "emerald": return folder + "emerald.png";
  575. case "flower round": return folder + "flower-round.png";
  576. case "heart": return folder + "heart.png";
  577. case "marquise": return folder + "marquise.png";
  578. case "oval": return folder + "oval.png";
  579. case "pear": return folder + "pear.png";
  580. case "princess": return folder + "princess.png";
  581. case "princess quad": return folder + "princess-quad.png";
  582. case "round": return folder + "round.png";
  583. case "square radiant": return folder + "square-radiant.png";
  584. case "trilliant": return folder + "trilliant.png";
  585. default: return "unknown";
  586. }
  587. }
  588.  
  589. //it IS a finished good
  590. if (style.Length < 4) style += "JUNKSTYLE#"; //stops style#'s that are less than 4chars in length
  591. string pix = "";
  592. style = style.ToUpper();
  593. //-----EARRINGS----------------------------------------------------------------------------------
  594. if (style.Substring(0, 1) == "E")
  595. {
  596. if (Regex.Match(style, "E[?0-9]").Success) { pix = "EARRING-3"; categoryId = "110"; }
  597. if (Regex.Match(style, "EM[?0-9]").Success) { pix = "EARRING-2"; categoryId = "110"; }
  598. if (Regex.Match(style, "EB[?0-9]").Success) { pix = "EARRING-3"; categoryId = "110"; }
  599. if (Regex.Match(style, "EP[?0-9]").Success) { pix = "EARRING-1"; categoryId = "111"; }
  600. if (Regex.Match(style, "EPV[?0-9]").Success) { pix = "EARRING-1"; categoryId = "111"; }
  601. if (Regex.Match(style, "ERP71[?0-9]").Success) { pix = "ERP71"; categoryId = "151"; }
  602. if (Regex.Match(style, "ER71[?0-9]").Success) { pix = "ER71"; categoryId = "150"; }
  603. if (Regex.Match(style, "ERR[?0-9]").Success) { pix = "ERR050"; categoryId = "134"; }
  604. if (Regex.Match(style, "ER61[?0-9]").Success) { pix = "ER61"; categoryId = "145"; }
  605. if (Regex.Match(style, "ER81[?0-9]").Success) { pix = "ER81"; categoryId = "144"; }
  606. if (Regex.Match(style, "ER51[?0-9]").Success) { pix = "ER51"; categoryId = "146"; }
  607. if (style.Length >= 6)
  608. {
  609. if (style.Substring(0, 6) == "ER4003" || style.Substring(0, 6) == "ER4008") { pix = "ER4003"; categoryId = "147"; }
  610. if (style.Substring(0, 6) == "ER4004" || style.Substring(0, 6) == "ER4009") { pix = "ER4004"; categoryId = "148"; }
  611. if (style.Substring(0, 6) == "ER4001" || style.Substring(0, 6) == "ER4002") { pix = "ER4001"; categoryId = "149"; }
  612. if (style.Substring(0, 6) == "ER4005" || style.Substring(0, 6) == "ER4006") { pix = "ER4001"; categoryId = "149"; }
  613. }
  614. }
  615. //-----END OF EARRINGS---------------------------------------------------------------------------
  616. //-----RINGS-------------------------------------------------------------------------------------
  617. if (style.Substring(0, 1) == "R" || style.Substring(0, 1) == "Y")
  618. {
  619. if (Regex.Match(style, "R0[?0-9]").Success || Regex.Match(style, "R10[?0-9]").Success) { pix = "R01"; categoryId = "112"; }
  620. if (Regex.Match(style, "RP0[?0-9]").Success || Regex.Match(style, "RP10[?0-9]").Success) { pix = "RP01"; categoryId = "113"; }
  621. if (style.Length >= 5)
  622. {
  623. if (style.Substring(0, 5) == "R6067" || style.Substring(0, 5) == "R6085" || style.Substring(0, 5) == "R6088") { pix = "R6067"; categoryId = "114"; }
  624. if (style.Substring(0, 5) == "R6066" || style.Substring(0, 5) == "R6086" || style.Substring(0, 5) == "R6089") { pix = "R6066"; categoryId = "114"; }
  625. if (style.Substring(0, 5) == "R6068" || style.Substring(0, 5) == "R6087" || style.Substring(0, 5) == "R6090") { pix = "R6068"; categoryId = "114"; }
  626. if (Regex.Match(style, "YP0[?0-9]").Success || style.Substring(0, 5) == "YP100" || style.Substring(0, 5) == "YP150" || style.Substring(0, 5) == "YP200" || style.Substring(0, 5) == "YP300")
  627. { if (style.IndexOf("F2") > 0) { pix = "YP100F2"; categoryId = "130"; } else { pix = "YP100"; categoryId = "128"; } }
  628. if (style.Substring(0, 5) == "YE050" || style.Substring(0, 5) == "YE100" || style.Substring(0, 5) == "YE150" || style.Substring(0, 5) == "YE200") { pix = "YE100"; categoryId = "133"; }
  629. if (style.Substring(0, 5) == "YR050" || style.Substring(0, 5) == "YR075" || style.Substring(0, 5) == "YR100" || style.Substring(0, 5) == "YR150") { pix = "YR100"; categoryId = "135"; }
  630. }
  631. if (Regex.Match(style, "R171[?0-9]").Success) { pix = "R171"; categoryId = "115"; }
  632. if (Regex.Match(style, "RP171[?0-9]").Success) { pix = "RP171"; categoryId = "116"; }
  633. if (Regex.Match(style, "R161[?0-9]").Success) { pix = "R161"; categoryId = "117"; }
  634. if (Regex.Match(style, "R141[?0-9]").Success) { pix = "R141"; categoryId = "118"; }
  635. if (Regex.Match(style, "R151[?0-9]").Success) { pix = "R151"; categoryId = "119"; }
  636. if (Regex.Match(style, "R191[?0-9]").Success) { pix = "R191"; categoryId = "124"; }
  637. if (Regex.Match(style, "R181[?0-9]").Success) { pix = "R181"; categoryId = "115"; }
  638. if (Regex.Match(style, "RP181[?0-9]").Success) { pix = "RP181"; categoryId = "116"; }
  639. if (Regex.Match(style, "R101[?0-9]").Success) { pix = "R101"; categoryId = "125"; }
  640. if (Regex.Match(style, "R40[?0-9]").Success || Regex.Match(style, "R41[?0-9]").Success || Regex.Match(style, "R42[?0-9]").Success) { pix = "R4004"; categoryId = "126"; }
  641. if (style.Length >= 6)
  642. {
  643. if (style.Substring(0, 6) == "RET100" || style.Substring(0, 6) == "RET150" || style.Substring(0, 6) == "RET200" || style.Substring(0, 6) == "RET300")
  644. { if (style.IndexOf("CH") > 0) { pix = "RET100CH"; categoryId = "123"; } else { pix = "RET100"; categoryId = "123"; } }
  645. }
  646. if (style.Length >= 7)
  647. {
  648. if (style.Substring(0, 7) == "RET2002" || style.Substring(0, 7) == "RET2003" || style.Substring(0, 7) == "RET2004") { pix = "RET2000"; categoryId = "121"; }
  649. if (style.Substring(0, 7) == "RET3000" || style.Substring(0, 7) == "RET3001") { pix = "RET3000"; categoryId = "122"; }
  650. }
  651. if (style.Length >= 8)
  652. {
  653. if (style.Substring(0, 8) == "RETP100" || style.Substring(0, 8) == "RETP150" || style.Substring(0, 8) == "RETP200" || style.Substring(0, 8) == "RETP300") { pix = "RETP100"; categoryId = "123"; }
  654. }
  655. if (style.Length >= 4)
  656. {
  657. if (Regex.Match(style, "Y0[?0-9]").Success || style.Substring(0, 4) == "Y100" || style.Substring(0, 4) == "Y150" || style.Substring(0, 4) == "Y200" || style.Substring(0, 4) == "Y300")
  658. { if (style.IndexOf("F1") > 0) { pix = "Y100F1"; categoryId = "131"; } else if (style.IndexOf("F2") > 0) { pix = "Y100F2"; categoryId = "131"; } else { pix = "Y100"; categoryId = "127"; } }
  659. }
  660. if (Regex.Match(style, "RR400[?0-9]").Success) { pix = "RR400"; categoryId = "130"; }
  661. if (Regex.Match(style, "RE400[?0-9]").Success) { pix = "RE400"; categoryId = "132"; }
  662. }
  663. //-----END OF RINGS------------------------------------------------------------------------------
  664.  
  665. //----- PENDANTS --------------------------------------------------------------------------------
  666. if (style.Substring(0, 1) == "P" || style.Substring(0, 2) == "JP")
  667. {
  668. if (Regex.Match(style, "JP30[?0-9]").Success) { pix = "JP30"; categoryId = "137"; }
  669. if (Regex.Match(style, "P0[?0-9]").Success || style.Substring(0, 4) == "P100") { pix = "P100"; categoryId = "138"; }
  670. if (Regex.Match(style, "PP[?0-9]").Success) { pix = "PP100"; categoryId = "139"; }
  671. }
  672. //----- END OF PENDANTS -------------------------------------------------------------------------
  673.  
  674. //----- BRACELETS -------------------------------------------------------------------------------
  675. if (style.Length >= 4 && style.Substring(0, 1) == "B")
  676. {
  677. if (style.Substring(2, 2) == "02") { pix = "B02"; categoryId = "140"; }
  678. if (style.Substring(2, 2) == "24") { pix = "B24"; categoryId = "141"; }
  679. if (style.Substring(2, 2) == "04") { pix = "B04"; categoryId = "142"; }
  680. if (style.Substring(2, 2) == "28") { pix = "B28"; categoryId = "143"; }
  681. }
  682. //----- END OF BRACELETS ------------------------------------------------------------------------
  683.  
  684. if (pix.Length > 0)
  685. {
  686. return "1345/" + pix + ".png";
  687. }
  688. else
  689. {
  690. return "unknown";
  691. }
  692. }
  693. static string aliasQualityQty(string catId)
  694. { //if marked with //, the item actually does have qty 1 :)
  695. switch (catId)
  696. {
  697. case "110": return "2";
  698. case "111": return "2";
  699. case "112": return "1"; //
  700. case "113": return "1"; //
  701. case "114": return "1"; //?
  702. case "115": return "9"; //?
  703. case "116": return "10";//?
  704. case "117": return "7";
  705. case "118": return "5";
  706. case "119": return "5";
  707. case "120": return "23"; //?
  708. case "121": return "1";
  709. case "122": return "58"; //?
  710. case "123": return "84"; //?
  711. case "124": return "31"; //?
  712. case "125": return "20"; //?
  713. case "126": return "11"; //?
  714. case "127": return "3";
  715. case "128": return "3";
  716. case "129": return "15"; //?
  717. case "130": return "11"; //?
  718. case "131": return "1";
  719. case "132": return "1";
  720. case "133": return "3";
  721. case "134": return "2";
  722. case "135": return "3";
  723. case "136": return "1";
  724. case "137": return "7";
  725. case "138": return "1";//
  726. case "139": return "1";//
  727. case "140": return "1";
  728. case "141": return "1";
  729. case "142": return "1";
  730. case "143": return "1";
  731. case "144": return "74"; //?
  732. case "145": return "68"; //?
  733. case "146": return "140"; //?
  734. case "148": return "124"; //?
  735. case "149": return "42"; //?
  736. case "150": return "7"; //?
  737. case "151": return "7"; //?
  738. default: return "1";
  739. }
  740. }
  741. static string aliasQualityCut(string cut)
  742. {
  743. cut = cut.ToLower();
  744. if (cut == "rd") { return "Round"; }
  745. if (cut == "prin") { return "Princess"; }
  746. if (cut == "emer") { return "Emerald"; }
  747. if (cut == "mq") { return "Marquise"; }
  748. if (cut == "hrt") { return "Heart"; }
  749. if (cut == "oval") { return "Oval"; }
  750. if (cut == "pear") { return "Pear"; }
  751. if (cut == "tril") { return "Trilliant"; }
  752. if (cut == "rfl") { return "Flower Round"; }
  753. if (cut == "pr+b") { return "Princess & Baguette"; }
  754. if (cut == "pqd") { return "Princess Quad"; }
  755. if (cut == "dfl") { return "Flower Pear"; }
  756. if (cut == "bag") { return "Baguette"; }
  757. if (cut == "rd+b") { return "Round & Baguette"; }
  758. if (cut == "srad") { return "Square Radiant"; }
  759. if (cut == "rd+p") { return "Round & Princess"; }
  760. if (cut == "hfl") { return "Flower Heart"; }
  761. if (cut == "sqfl") { return "Flower Square"; }
  762. if (cut == "asc") { return "Asscher"; }
  763. if (cut == "q") { return "Quad"; }
  764. if (cut == "cus") { return "Cushion"; }
  765. if (cut == "rad") { return "Radiant"; }
  766. return "";
  767. }
  768. public string aliasMetal(string met)
  769. {
  770. switch (met.ToLower())
  771. {
  772. case "14kw": return "14KW Gold";
  773. case "14ky": return "14KY Gold";
  774. case "10ky": return "10KY Gold";
  775. case "18kw": return "18KW Gold";
  776. case "14tt": return "14K TT";
  777. case "platinum": return "Platinum";
  778. case "18ky": return "18KY Gold";
  779. case "10kw": return "10KW Gold";
  780. case "palladium": return "Palladium";
  781. case "10tt": return "10K TT";
  782. }
  783. return met;
  784. }
  785. public string aliasProductCategory(string cat3)
  786. {
  787. //not used, put into supplier specific for no good reason currently
  788. switch (cat3.ToLower())
  789. {
  790. case "3st+": return "Three Stone";
  791. case "3stn": return "Three Stone";
  792. case "annv": return "Anniversary";
  793. case "br": return "Bracelet";
  794. case "brid": return "Bridal";
  795. case "cir": return "Circle";
  796. case "eter": return "Eternity Band";
  797. case "flw": return "Flower";
  798. case "fshb": return "Fashion Bangle";
  799. case "fshe": return "Fashion Earring";
  800. case "fshp": return "Fashion Pendant";
  801. case "fsr": return "Fashion Ring";
  802. case "hoop": return "Hoop Earring";
  803. case "jour": return "Journey";
  804. case "quad": return "Princess Quad";
  805. case "rdl": return "Rondel";
  806. case "semi": return "Semi-Mounting";
  807. case "sflw": return "Square";
  808. case "sol": return "Solitaire";
  809. case "sp": return "Special Order";
  810. }
  811. return cat3;
  812. }
  813. static string aliasQualityColor(string style)
  814. {
  815. if (style.IndexOf("K3") > 0 || style.IndexOf("N3") > 0) { return "G/H"; }
  816. if (style.IndexOf("K4") > 0 || style.IndexOf("N4") > 0) { return "G/H"; }
  817. if (style.IndexOf("K5") > 0 || style.IndexOf("N5") > 0) { return "G/H"; }
  818. if (style.IndexOf("K6") > 0 || style.IndexOf("N6") > 0) { return "G/H"; }
  819. if (style.IndexOf("K7") > 0 || style.IndexOf("N7") > 0) { return "G/H"; }
  820. if (style.IndexOf("K8") > 0 || style.IndexOf("N8") > 0) { return "G/H"; }
  821. if (style.IndexOf("K9") > 0 || style.IndexOf("N9") > 0) { return "G/H"; }
  822. if (style.IndexOf("K10") > 0 || style.IndexOf("N10") > 0) { return "G/H"; }
  823.  
  824. if (style.IndexOf("S4") > 0) { return "I/J"; }
  825. if (style.IndexOf("S5") > 0) { return "I/J"; }
  826. if (style.IndexOf("S6") > 0) { return "I/J"; }
  827. if (style.IndexOf("S7") > 0) { return "I/J"; }
  828. if (style.IndexOf("S8") > 0) { return "I/J"; }
  829. if (style.IndexOf("S9") > 0) { return "I/J"; }
  830. if (style.IndexOf("S10") > 0) { return "I/J"; }
  831. return ""; //wasn't able to find
  832. }
  833. static string aliasQualityClarity(string style)
  834. {
  835. if (style.IndexOf("K3") > 0 || style.IndexOf("N3") > 0) { return "VS2"; }
  836. if (style.IndexOf("K4") > 0 || style.IndexOf("N4") > 0) { return "SI1"; }
  837. if (style.IndexOf("K5") > 0 || style.IndexOf("N5") > 0) { return "SI2"; }
  838. if (style.IndexOf("K6") > 0 || style.IndexOf("N6") > 0) { return "SI3"; }
  839. if (style.IndexOf("K7") > 0 || style.IndexOf("N7") > 0) { return "I1"; }
  840. if (style.IndexOf("K8") > 0 || style.IndexOf("N8") > 0) { return "I1/I2"; }
  841. if (style.IndexOf("K9") > 0 || style.IndexOf("N9") > 0) { return "I2"; }
  842. if (style.IndexOf("K10") > 0 || style.IndexOf("N10") > 0) { return "I2/I3"; }
  843.  
  844. if (style.IndexOf("S4") > 0) { return "SI1"; }
  845. if (style.IndexOf("S5") > 0) { return "SI2"; }
  846. if (style.IndexOf("S6") > 0) { return "SI3"; }
  847. if (style.IndexOf("S7") > 0) { return "I1"; }
  848. if (style.IndexOf("S8") > 0) { return "I1/I2"; }
  849. if (style.IndexOf("S9") > 0) { return "I2"; }
  850. if (style.IndexOf("S10") > 0) { return "I2/I3"; }
  851. return ""; //wasn't able to find
  852. }
  853.  
  854. #endregion //itemAliases
  855.  
  856. #region fracsToDecimalUtils
  857. static string FractionToDecimal(string frac)
  858. {
  859. try
  860. {
  861. if (isDecimal(frac)) { return frac; } //no nothing, it's already a decimal value
  862.  
  863. if (parseJunkCases(frac).Length > 0) { return parseJunkCases(frac); } //convert shit values like 2CT and 11/2
  864.  
  865. string[] complexParts = frac.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
  866. string fraction;
  867.  
  868. if (complexParts.Length == 1)
  869. fraction = complexParts[0];
  870. else
  871. fraction = complexParts[1];
  872.  
  873. string[] simpleParts = fraction.Split(new string[] { "/" }, StringSplitOptions.RemoveEmptyEntries);
  874. double fractionValue = Convert.ToDouble(simpleParts[0]);
  875.  
  876. if (simpleParts[0].Length == 2) { return frac; }
  877.  
  878. if (simpleParts.Length > 1) fractionValue = fractionValue / Convert.ToDouble(simpleParts[1]);
  879. if (complexParts.Length > 1) fractionValue += Convert.ToDouble(complexParts[0]);
  880.  
  881. fractionValue = Math.Round(fractionValue, 2);
  882. return fractionValue.ToString();
  883. }
  884. catch
  885. { return frac; }
  886. }
  887. static string parseJunkCases(string val)
  888. {
  889. if (val == "1CT") { return "1.00"; }
  890. if (val == "2CT") { return "2.00"; }
  891. if (val == "3CT") { return "3.00"; }
  892. if (val == "4CT") { return "4.00"; }
  893. if (val == "11/2") { return "1.50"; }
  894. if (val == "11/3") { return "1.33"; }
  895. if (val == "11/4") { return "1.25"; }
  896. if (val == "11/8") { return "1.13"; }
  897. if (val == "21/2") { return "2.50"; }
  898. if (val == "21/3") { return "2.33"; }
  899. if (val == "21/4") { return "2.25"; }
  900. if (val == "21/8") { return "2.13"; }
  901. if (val == "31/2") { return "3.50"; }
  902. if (val == "31/3") { return "3.33"; }
  903. if (val == "31/4") { return "3.25"; }
  904. if (val == "31/8") { return "3.13"; }
  905. if (val == "41/2") { return "4.50"; }
  906. if (val == "41/3") { return "4.33"; }
  907. if (val == "41/4") { return "4.25"; }
  908. if (val == "41/8") { return "4.13"; }
  909. if (val == "51/2") { return "5.50"; }
  910. if (val == "51/3") { return "5.33"; }
  911. if (val == "51/4") { return "5.25"; }
  912. if (val == "51/8") { return "5.13"; }
  913. if (val == "61/2") { return "6.50"; }
  914. if (val == "61/3") { return "6.33"; }
  915. if (val == "61/4") { return "6.25"; }
  916. if (val == "61/8") { return "6.13"; }
  917.  
  918. return ""; //couldn't fix, so short circuit
  919. }
  920. static bool isDecimal(string val)
  921. {
  922. try
  923. {
  924. Convert.ToDouble(val);
  925. return true;
  926. }
  927. catch
  928. {
  929. return false;
  930. }
  931. }
  932. #endregion
  933.  
  934. }
  935. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!