public void runStoreFullSync() { storeSyncError = 0; try { List <string[]> docs = rproDBHandler.getInTransitDocuments(); if (storeSyncError > 0) { return; } if (docs.Count > 0) { StoreSyncConfirmationForm sscf = new StoreSyncConfirmationForm(docs); sscf.ShowDialog(); if (!sscf.cont) { return; } } Queue <StoreCost> storeCosts = rproDBHandler.getStoreCosts(); msSqlDBHandler.insertStoreCost(storeCosts, rproDBHandler); string body = "Store sync completed with " + storeSyncError + " error(s)."; rproDBHandler.addLog(MainController.LogType.REPORT, null, null, MainController.Features.STORE_SYNC, body, null); } catch (Exception e) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, MainController.Features.STORE_SYNC, "Exception occurred when running store sync ", e); return; } }
public void sendEmail(string subject, string body, RproDBHandler rproDBHandler, MainController.Features feature) { try { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient(smtp_address, smtp_port); mail.From = new MailAddress(email_sender_address); mail.To.Add(email_recepient_address); mail.Subject = subject; mail.Body = body; // SmtpServer.Port = smtp_port; SmtpServer.UseDefaultCredentials = false; SmtpServer.Credentials = new System.Net.NetworkCredential(email_sender_address, email_password); SmtpServer.EnableSsl = email_enable_SSL; SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network; SmtpServer.Send(mail); rproDBHandler.addLog(MainController.LogType.REPORT, "", "", MainController.Features.SEND_EMAIL, "Email Sent", null); } catch (Exception e) { if (rproDBHandler != null) { string msg = "Exception occurred when sending email on error report. "; rproDBHandler.addLog(MainController.LogType.REPORT, "", "", feature, msg, e); } } }
public void insertAdjustments(Dictionary <string, List <Adj_div> > adjustments, RproDBHandler rproDBHandler) { foreach (KeyValuePair <string, List <Adj_div> > entry in adjustments) { List <Adj_div> adjustment = entry.Value; string sid = "ADJ" + entry.Key; using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = connection.BeginTransaction("AdjustmentTransaction"); command.Connection = connection; command.Transaction = transaction; string sql = "insert into RetailPro_Adjustment(adj_sid, adj_date, storecode, subsidiary, cardcode, sign, division, totalvalue, reason, b1_status) values "; sql += "(@adj_sid, @adj_date, @storecode, @subsidiary, @cardcode, @sign, @division, @totalvalue, @reason, @b1_status)"; command.CommandText = sql; try { foreach (Adj_div adj_div in adjustment) { command.Parameters.Clear(); string reason = adj_div.getReason(); if (reason == "") { rproDBHandler.addLog(MainController.LogType.ERROR, sid, adj_div.division, MainController.Features.ADJUSTMENT, "Invalid creating_doc_type value " + adj_div.creating_doc_type + " in adjustment", null); continue; } command.Parameters.AddWithValue("adj_sid", sid); command.Parameters.AddWithValue("adj_date", adj_div.adj_date); command.Parameters.AddWithValue("storecode", adj_div.storecode); command.Parameters.AddWithValue("subsidiary", adj_div.subsidiary); command.Parameters.AddWithValue("cardcode", adj_div.cardcode); command.Parameters.AddWithValue("sign", adj_div.getSign()); command.Parameters.AddWithValue("division", adj_div.division); command.Parameters.AddWithValue("totalvalue", adj_div.totalvalue.Replace("-", "")); command.Parameters.AddWithValue("reason", reason); command.Parameters.AddWithValue("b1_status", adj_div.getB1_status()); command.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.ADJUSTMENT, "Exception occurred when updating adjustment table", ex); AdjustmentController.error += 1; // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.SALE, "Exception occurred when performing rollback on adjustment table ", ex2); } } } } }
public void insertVoucherReturns(Dictionary <String, List <VoucherReturnItem> > vouchers, RproDBHandler rproDBHandler) { foreach (KeyValuePair <string, List <VoucherReturnItem> > entry in vouchers) { List <VoucherReturnItem> voucher = entry.Value; string sid = entry.Key; using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = connection.BeginTransaction("VoucherReturnTransaction"); command.Connection = connection; command.Transaction = transaction; string sql = "insert into Retailpro_Return(vou_sid, vou_date, alu, quantity, linenum, storeid, subsidiary, vend_code, reason, comments) values "; sql += "(@vou_sid, @vou_date, @alu, @quantity, @linenum, @storeid, @subsidiary, @vend_code, @reason, @comments)"; command.CommandText = sql; int linenum = 0; try { foreach (VoucherReturnItem item in voucher) { command.Parameters.Clear(); command.Parameters.AddWithValue("vou_sid", sid); command.Parameters.AddWithValue("vou_date", item.vou_date); command.Parameters.AddWithValue("alu", item.alu); command.Parameters.AddWithValue("quantity", item.qty); command.Parameters.AddWithValue("linenum", linenum); command.Parameters.AddWithValue("storeid", item.storeid); command.Parameters.AddWithValue("subsidiary", item.subsidiary); command.Parameters.AddWithValue("vend_code", item.vend_code); command.Parameters.AddWithValue("reason", item.reason); command.Parameters.AddWithValue("comments", item.comments); command.ExecuteNonQuery(); linenum++; } transaction.Commit(); } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.VOU_RETURN, "Exception occurred when updating voucher return table", ex); VoucherReturnController.error += 1; // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.VOU_RETURN, "Exception occurred when performing rollback on voucher return table ", ex2); } } } } }
public void insertVoucherDiscrepancies(Dictionary <String, List <VoucherDiscrepancyItem> > vou_discrepancies, RproDBHandler rproDBHandler) { foreach (KeyValuePair <string, List <VoucherDiscrepancyItem> > entry in vou_discrepancies) { List <VoucherDiscrepancyItem> voucher = entry.Value; string sid = "VOU" + entry.Key; using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = connection.BeginTransaction("VoucherDiscrepancyTransaction"); command.Connection = connection; command.Transaction = transaction; string sql = "insert into Retailpro_Discrepancy(vou_sid, vou_date, alu, quantity, value, sign, storeid, subsidiary, division, comments) values "; sql += "(@vou_sid, @vou_date, @alu, @quantity, @value, @sign, @storeid, @subsidiary, @division, @comments)"; command.CommandText = sql; try { foreach (VoucherDiscrepancyItem item in voucher) { command.Parameters.Clear(); command.Parameters.AddWithValue("vou_sid", sid + item.getSignSingle()); command.Parameters.AddWithValue("vou_date", item.vou_date); command.Parameters.AddWithValue("alu", item.alu); command.Parameters.AddWithValue("quantity", item.getAbsoluteQty()); command.Parameters.AddWithValue("value", item.value); command.Parameters.AddWithValue("sign", item.getSign()); command.Parameters.AddWithValue("storeid", item.storeid); command.Parameters.AddWithValue("subsidiary", item.subsidiary); command.Parameters.AddWithValue("division", item.division); command.Parameters.AddWithValue("comments", item.comments); command.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.VOU_DISCREPANCY, "Exception occurred when updating voucher discrepancy table", ex); VoucherDiscrepancyController.error += 1; // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, sid, null, MainController.Features.VOU_DISCREPANCY, "Exception occurred when performing rollback on voucher discrepancy table ", ex2); } } } } }
public void insertSalesSync(Invoice invoice, string invc_sid, RproDBHandler rproDBHandler) { if (invoice.divisions.Count == 0 || invoice.hasZeroCost) { return; } using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = connection.BeginTransaction("InvoiceTransaction"); command.Connection = connection; command.Transaction = transaction; try { string sql = "insert into RetailPro_Sales_Sync(SID, invc_sid, date, storecode, subsidiary, cardcode, sign, totalcost, totalnetsales, division) "; sql += "values (@SID, @invc_sid, @date, @storecode, @subsidiary, @cardcode, @sign, @totalcost, @totalnetsales, @division) "; command.CommandText = sql; foreach (InvoiceDivision div in invoice.divisions) { command.Parameters.Clear(); command.Parameters.AddWithValue("@SID", invc_sid + div.getSignSingle()); command.Parameters.AddWithValue("@invc_sid", div.invc_sid); command.Parameters.AddWithValue("@date", div.invc_date); command.Parameters.AddWithValue("@storecode", div.storeCode); command.Parameters.AddWithValue("@subsidiary", div.sbs_no); command.Parameters.AddWithValue("@cardcode", div.cardcode); command.Parameters.AddWithValue("@sign", div.getSign()); command.Parameters.AddWithValue("@totalcost", div.getAbsoluteCost()); command.Parameters.AddWithValue("@totalnetsales", div.sales); command.Parameters.AddWithValue("@division", div.division); command.ExecuteNonQuery(); } // Attempt to commit the transaction. transaction.Commit(); } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, invc_sid, null, MainController.Features.SALE, "Exception occurred when inserting invoice " + invoice.invc_sid, ex); InvoiceController.error += 1; // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, invc_sid, null, MainController.Features.SALE, "Exception occurred when performing rollback on invoice " + invoice.invc_sid, ex2); } } } }
public HashSet <string> getExistingSIDs(string sql, RproDBHandler rproDBHandler, MainController.Features feature) { HashSet <string> results = new HashSet <string>(); using (SqlConnection connection = new SqlConnection(getConnectionString())) { try { connection.Open(); SqlCommand cmd = new SqlCommand(sql, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { results.Add(reader["sid"].ToString()); } } catch (Exception e) { string title = feature.ToString() + " exception occured."; string body = "Exception occured when retrieving existing SIDs from B1 database. Unable to perform matching. Run aborted."; rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, feature, body, e); new EmailController(settings).sendEmail(title, body, rproDBHandler, feature); throw e; } finally { connection.Close(); } } return(results); }
public void runUpdateInvoices() { error = 0; zeroCostError = 0; int invoice_days = Int32.Parse(settings.getDays(feature)); string day_limit = DateTime.Now.AddDays((invoice_days + 1) * -1).Date.ToString(MsSqlDBHandler.DATE_FORMAT); Queue <ZeroCostDocument> zeroCostInvoices = new Queue <ZeroCostDocument>(); string sql = "select distinct invc_sid as sid from Retailpro_Sales_Sync where date >= " + day_limit; HashSet <string> invoiceSIDs = null; try { invoiceSIDs = msSqlDBHandler.getExistingSIDs(sql, rproDBHandler, feature); } catch (Exception) { return; } string subsidiaryFilter = settings.getSubsidiaries(feature); rproDBHandler.getInvoiceDivisions(invoiceSIDs, invoice_days, subsidiaryFilter, zeroCostInvoices, this); //add invoice divisions to mysql table // select into invoice table using transactions int invoiceCount = 0; foreach (List <Invoice> inv_group in invoiceGroups.Values) { if (inv_group.Count < 1) { continue; } string inv_group_name = "INV" + inv_group[0].invc_sid; foreach (Invoice inv in inv_group) { invoiceCount += 1; msSqlDBHandler.insertSalesSync(inv, inv_group_name, rproDBHandler); } } //insert into sales table from sales_sync table msSqlDBHandler.insertSales(rproDBHandler); int zeroCostTotal = zeroCostInvoices.Count; rproDBHandler.insertZeroCostDocuments(zeroCostInvoices, feature); string msg = invoiceCount + " Invoices fetched and inserted into B1 with " + error + " error(s). "; msg += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors."; rproDBHandler.addLog(MainController.LogType.REPORT, "", "", feature, msg, null); if (error > 0 || zeroCostTotal > 0) { string subject = "Errors/Zero cost in B1 Interface for " + MainController.Features.SALE.ToString(); string body = "There are " + error + " errors when processing " + MainController.Features.SALE.ToString() + " on " + DateTime.Now.ToString() + ". \n"; body += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors.\n"; body += "Please check log for details."; new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } }
public void insertSales(RproDBHandler rproDBHandler) { using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = connection.BeginTransaction("SalesTransaction"); command.Connection = connection; command.Transaction = transaction; try { string sql = "insert into RetailPro_Sales(sid, date, storecode, subsidiary, cardcode, sign, totalcost, totalnetsales, division) "; sql += "select sid, date, storecode, subsidiary, cardcode, sign, sum(totalcost) as totalcost, sum(totalnetsales) as totalnetsales, division from Retailpro_Sales_Sync "; sql += "where processed='N' group by sid, date, storecode, subsidiary, cardcode, sign, division "; command.CommandText = sql; command.ExecuteNonQuery(); command.CommandText = "update RetailPro_Sales_Sync set processed = 'Y' where processed = 'N'"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, MainController.Features.SALE, "Exception occurred when updating sales table from sales_sync table ", ex); InvoiceController.error += 1; // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, MainController.Features.SALE, "Exception occurred when performing rollback sales_sync to sales table ", ex2); } } } }
public void runUpdateVoucherDiscrepancies() { error = 0; zeroCostError = 0; int vou_disc_days = Int32.Parse(settings.getDays(feature)); string day_limit = DateTime.Now.AddDays((vou_disc_days + 1) * -1).Date.ToString(MsSqlDBHandler.DATE_FORMAT); string sql = "SELECT SUBSTRING(VOU_SID, 4, len(VOU_SID)-4) AS sid FROM Retailpro_Discrepancy WHERE vou_date >= " + day_limit; HashSet <string> sids = null; try { sids = msSqlDBHandler.getExistingSIDs(sql, rproDBHandler, feature); } catch (Exception) { return; } string subsidiaryFilter = settings.getSubsidiaries(feature); if (!string.IsNullOrWhiteSpace(subsidiaryFilter)) { subsidiaryFilter = " and vou.sbs_no in (" + subsidiaryFilter + ") "; } Dictionary <String, List <VoucherDiscrepancyItem> > vou_discrepancies = rproDBHandler.getVoucherDiscrepancies(sids, vou_disc_days, subsidiaryFilter); Queue <ZeroCostDocument> zeroCostDocuments = filterZeroDocuments(vou_discrepancies); msSqlDBHandler.insertVoucherDiscrepancies(vou_discrepancies, rproDBHandler); int zeroCostTotal = zeroCostDocuments.Count; rproDBHandler.insertZeroCostDocuments(zeroCostDocuments, MainController.Features.VOU_DISCREPANCY); string msg = vou_discrepancies.Count + " vouchers with discrepancies fetched and inserted into B1 with " + error + " error(s). "; msg += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors."; if (error + zeroCostTotal > 0) { string subject = "Errors/Zero cost in B1 Interface for " + feature.ToString(); string body = msg; body += "Please check log for details."; new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } rproDBHandler.addLog(MainController.LogType.REPORT, "", "", feature, msg, null); }
public void insertSlips(Queue <Slip> slips, RproDBHandler rproDBHandler) { using (SqlConnection connection = new SqlConnection(getConnectionString())) { while (slips.Count > 0) { Slip slip = slips.Dequeue(); try { /* * bool shouldUpdate = validateStoresAndSubsidiaries(slip.from_store, slip.from_sbs) && validateStoresAndSubsidiaries(slip.to_store, slip.to_sbs); * if (!shouldUpdate) * { * continue; * } */ connection.Open(); string sql = "Insert into RetailPro_Slip (slip_sid, slip_date, fromstore, fromsbs, tostore, tosbs,slip_value) values (@slip_sid, @slip_date, @from_store, @from_sbs, @to_store, @to_sbs, @slip_value)"; SqlCommand cmd = new SqlCommand(sql, connection); cmd.Parameters.Add(new SqlParameter("@slip_sid", "SLP" + slip.slip_sid)); cmd.Parameters.Add(new SqlParameter("@slip_date", slip.slip_date)); cmd.Parameters.Add(new SqlParameter("@from_store", slip.from_store)); cmd.Parameters.Add(new SqlParameter("@from_sbs", slip.from_sbs)); cmd.Parameters.Add(new SqlParameter("@to_store", slip.to_store)); cmd.Parameters.Add(new SqlParameter("@to_sbs", slip.to_sbs)); Double slip_value = Double.Parse(slip.slip_value); cmd.Parameters.Add(new SqlParameter("@slip_value", slip_value.ToString("0.00"))); cmd.ExecuteNonQuery(); } catch (Exception e) { SlipController.error += 1; rproDBHandler.addLog(MainController.LogType.EXCEPTION, slip.slip_sid, null, MainController.Features.SLIP, "Exception caught when inserting slip " + slip.slip_sid, e); } finally { connection.Close(); } } } }
public void insertStoreCost(Queue <StoreCost> storecosts, RproDBHandler rproDBHandler) { using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); StoreCost storecost = null; SqlCommand command = connection.CreateCommand(); command.Connection = connection; /* * string currentDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); * string sql = "insert into Retailpro_StoreFullSync(storecode, subsidiary, value, post_date) values (@storecode, @subsidiary, @value, @post_date)"; */ string currentDate = DateTime.Now.ToString("yyyy-MM-dd"); command.CommandText = "update Retailpro_StoreFullSync set value = @value where storecode = @storecode and subsidiary=@subsidiary and post_date = @post_date "; command.CommandText += "IF @@ROWCOUNT=0 "; command.CommandText += "insert into Retailpro_StoreFullSync(storecode, subsidiary, value, post_date) values (@storecode, @subsidiary, @value, @post_date)"; try { while (storecosts.Count > 0) { storecost = storecosts.Dequeue(); if (string.IsNullOrWhiteSpace(storecost.value.Trim())) { storecost.value = "0"; } command.Parameters.Clear(); command.Parameters.AddWithValue("storecode", storecost.storecode.Trim()); command.Parameters.AddWithValue("subsidiary", storecost.subsidiary.Trim()); command.Parameters.AddWithValue("value", storecost.value.Trim()); command.Parameters.AddWithValue("post_date", currentDate); command.ExecuteNonQuery(); } } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, storecost.storecode, storecost.subsidiary, MainController.Features.STORE_SYNC, "Exception occurred when updating store sync table", ex); } } }
public Dictionary <string, double> getItemCosts(RproDBHandler rproDBHandler) { string sql = "select sid, nullif(cost, 0.0) as cost from BES_TNotification "; Dictionary <string, double> itemCost = new Dictionary <string, double>(); string sid = "";; using (SqlConnection connection = new SqlConnection(getConnectionString())) { connection.Open(); SqlCommand command = new SqlCommand(sql, connection); try { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { sid = reader["sid"].ToString(); double cost = 0; if (reader["cost"] != DBNull.Value) { cost = Convert.ToDouble(reader["cost"]); } itemCost[sid] = cost; } } catch (Exception ex) { rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, sid, MainController.Features.ITEM_COST, "Exception occurred when fetching item cost from B1", ex); ItemCostController.error += 1; } finally { connection.Close(); } } return(itemCost); }
public void runUpdateSFSVoucherReturns() { error = 0; int vou_return_days = Int32.Parse(settings.getDays(feature)); string day_limit = DateTime.Now.AddDays((vou_return_days + 1) * -1).Date.ToString(MsSqlDBHandler.DATE_FORMAT); string sql = "SELECT VOU_SID AS sid FROM Retailpro_Return WHERE vou_date >= " + day_limit; HashSet <string> sids = null; try { sids = msSqlDBHandler.getExistingSIDs(sql, rproDBHandler, feature); } catch (Exception) { return; } string subsidiaryFilter = settings.getSubsidiaries(feature); if (!string.IsNullOrWhiteSpace(subsidiaryFilter)) { subsidiaryFilter = " and vou.sbs_no in (" + subsidiaryFilter + ") "; } Dictionary <String, List <VoucherReturnItem> > vou_items = rproDBHandler.getSFSVoucherReturnItems(sids, vou_return_days, subsidiaryFilter); msSqlDBHandler.insertVoucherReturns(vou_items, rproDBHandler); string msg = vou_items.Count + " SFS return vouchers fetched and inserted into B1 with " + error + " error(s). "; if (error > 0) { string subject = "Exceptions encountered when processing " + feature.ToString(); string body = msg; new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } rproDBHandler.addLog(MainController.LogType.REPORT, "", "", feature, msg, null); }
public void runItemCost() { error = 0; Dictionary <string, double> B1_costs = msSqlDBHandler.getItemCosts(rproDBHandler); string modified_date = rproDBHandler.getServerDate(rproDBHandler); if (string.IsNullOrWhiteSpace(modified_date)) { return; } InventoryBO inventory = new InventoryBO(modified_date); Queue <CostDifference> updates = rproDBHandler.matchItemCost(B1_costs); foreach (CostDifference update in updates) { if (update.B1cost == 0) { rproDBHandler.addLog(MainController.LogType.ERROR, update.item_sid, update.alu, feature, "B1 and Rpro cost mismatch - Cost in B1 is 0 when cost in Rpro is " + update.RproCost.ToString("0.0000"), null); } } if (updates.Count == 0) { rproDBHandler.addLog(MainController.LogType.REPORT, null, null, feature, "No XML file is created because there are no items to update", null); return; } while (updates.Count > 0) { CostDifference update = updates.Dequeue(); inventory.AddInventory(update.item_sid, update.B1cost); } string filepath = createInventoryXMLFilePath(); try { inventory.save(filepath); } catch (Exception e) { string subject = "Exception occurred when running " + feature.ToString(); string body = "Exception occured when trying to create Inventory.xml during Item Cost. Filepath: " + filepath; rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, feature, body, e); new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); return; } string message = inventory.inventoryCount() + " items are updated in " + filepath; rproDBHandler.addLog(MainController.LogType.REPORT, null, null, feature, message, null); string command = settings.getItemCostCMDInstruction(); string ecm = settings.getECM(); if (!string.IsNullOrWhiteSpace(command) && !string.IsNullOrWhiteSpace(ecm)) { try { ProcessStartInfo cmdsi = new ProcessStartInfo(ecm); cmdsi.Arguments = command; Process cmd = Process.Start(cmdsi); cmd.WaitForExit(); Queue <CostDifference> costDifferences = getCostDifferences(); if (costDifferences.Count == 0) { rproDBHandler.addLog(MainController.LogType.REPORT, null, null, feature, "0 cost differences after running item cost.", null); } else { rproDBHandler.addLog(MainController.LogType.ERROR, null, null, feature, costDifferences.Count + " cost differences after running item cost.", null); } } catch (Exception e) { string subject = "Exception occurred when running " + feature.ToString(); string body = "Exception occured when processing Inventory.xml using ECM."; rproDBHandler.addLog(MainController.LogType.EXCEPTION, null, null, feature, "Exception occured when processing Inventory.xml using ECM.", e); new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); return; } } }
public void runUpdateAdjustments() { error = 0; zeroCostError = 0; int adj_days = Int32.Parse(settings.getDays(feature)); string day_limit = DateTime.Now.AddDays((adj_days + 1) * -1).Date.ToString(MsSqlDBHandler.DATE_FORMAT); string sql = "SELECT SUBSTRING(ADJ_SID, 4, len(ADJ_SID)-3) AS sid FROM RetailPro_ADJUSTMENT WHERE ADJ_DATE >= " + day_limit; HashSet <string> adjSIDs = null; try { adjSIDs = msSqlDBHandler.getExistingSIDs(sql, rproDBHandler, feature); } catch (Exception e) { return; } string subsidiaryFilter = settings.getSubsidiaries(MainController.Features.ADJUSTMENT); if (!string.IsNullOrWhiteSpace(subsidiaryFilter)) { subsidiaryFilter = " and adj.sbs_no in (" + subsidiaryFilter + ") "; } Dictionary <string, List <Adj_div> > adjustments = rproDBHandler.getAdjustments(adjSIDs, adj_days, subsidiaryFilter); System.Console.WriteLine("getAdjustments : " + DateTime.Now); msSqlDBHandler.insertAdjustments(adjustments, rproDBHandler); // msSqlDBHandler.updateSlipsToDB(slips, rproDBHandler); /* * with adj as (select distinct item.adj_sid, max(case when invn.cost = 0 then 1 else 0 end) as zeroCostFlag,adj.adj_no as adj_no, * to_char(adj.created_date, 'YYYYMMDD') as adj_date, adj.sbs_no as sbs_no, adj.store_no as store_no, adj.adj_reason_name as reason * from adj_item_v item inner join invn_sbs_v invn on item.item_sid = invn.item_sid and invn.sbs_no = 1 * inner join adjustment_v adj on item.adj_sid = adj.adj_sid * where adj.adj_type = 0 and adj.status = 0 and adj.held = 0 and adj.isreversed = 0 and adj.created_date >= sysdate - 15 * group by item.adj_sid, adj.adj_no, to_char(adj.created_date, 'YYYYMMDD'), adj.sbs_no , adj.store_no, adj.adj_reason_name * having max(case when invn.cost = 0 then 1 else 0 end) = 1) * select item.adj_sid as doc_sid, invn.alu as alu from adj_item item inner join adj adj on adj.adj_sid = item.adj_sid * inner join invn_sbs_v invn on item.item_sid = invn.item_sid and invn.sbs_no = 1 where nvl(invn.cost, 0) = 0 */ sql = "with adj as (select distinct item.adj_sid, max(case when invn.cost = 0 then 1 else 0 end) as zeroCostFlag,adj.adj_no as adj_no, "; sql += "to_char(adj.created_date, 'YYYYMMDD') as adj_date, adj.sbs_no as sbs_no, adj.store_no as store_no, adj.adj_reason_name as reason "; sql += "from adj_item_v item inner join invn_sbs_v invn on item.item_sid = invn.item_sid and invn.sbs_no = 1 "; sql += "inner join adjustment_v adj on item.adj_sid = adj.adj_sid "; sql += "where adj.adj_type = 0 and adj.status = 0 and adj.held = 0 and adj.isreversed = 0 and adj.creating_doc_type!=9 and adj.created_date >= trunc(sysdate) - " + adj_days + subsidiaryFilter + " "; sql += "group by item.adj_sid, adj.adj_no, to_char(adj.created_date, 'YYYYMMDD'), adj.sbs_no , adj.store_no, adj.adj_reason_name "; sql += "having max(case when invn.cost = 0 then 1 else 0 end) = 1) "; sql += "select item.adj_sid as doc_sid, invn.alu as alu from adj_item item inner join adj adj on adj.adj_sid = item.adj_sid "; sql += "inner join invn_sbs_v invn on item.item_sid = invn.item_sid and invn.sbs_no = 1 where nvl(invn.cost, 0) = 0 "; Queue <ZeroCostDocument> zeroCostDocuments = rproDBHandler.getZeroCostDocument(sql, MainController.Features.ADJUSTMENT); int zeroCostTotal = zeroCostDocuments.Count; rproDBHandler.insertZeroCostDocuments(zeroCostDocuments, MainController.Features.ADJUSTMENT); string msg = adjustments.Count + " Adjustments fetched and inserted into B1 with " + error + " error(s). "; msg += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors."; rproDBHandler.addLog(MainController.LogType.REPORT, "", "", MainController.Features.ADJUSTMENT, msg, null); if (error > 0 || zeroCostTotal > 0) { string subject = "Errors/Zero cost in B1 Interface for " + feature.ToString(); string body = "There are " + error + " errors when processing " + feature.ToString() + " on " + DateTime.Now.ToString() + ". \n"; body += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors.\n"; body += "Please check log for details."; new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } }
public void sendDailyEmail( ) { RproDBHandler rproDBHandler = new RproDBHandler(settings); try { List <string[]> errorLogs = rproDBHandler.getLogDetails("where logtype != '" + MainController.LogType.REPORT.ToString() + "' and date1 > trunc(sysdate) "); List <string[]> zeroCostLogs = rproDBHandler.getZeroCost("where date1 > trunc(sysdate)"); String body = "<table width='100%' cellspacing='0' border='1' style='border - collapse:collapse;' >"; body += "<caption>Error Logs</caption>"; foreach (var item in errorLogs) { body += "<tr>"; for (int i = 0; i < item.Length - 1; i++) { body += "<td stlye='color:blue;'>" + item[i] + "</td>"; } body += "</tr>"; } body += "</table>"; body += "<br> <br/>"; body += "<table width='100%' cellspacing='0' border='1' style='border - collapse:collapse;' >"; body += "<caption>Zero Cost Logs</caption>"; foreach (var item in zeroCostLogs) { body += "<tr>"; for (int i = 0; i < item.Length; i++) { body += "<td stlye='color:blue;'>" + item[i] + "</td>"; } body += "</tr>"; } body += "</table>"; MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient(smtp_address, smtp_port); mail.From = new MailAddress(email_sender_address); mail.To.Add(email_recepient_address); mail.Subject = "B1 Daily Email " + System.DateTime.Today.ToShortDateString().ToString(); mail.IsBodyHtml = true; mail.Body = body; //SmtpServer.Port = smtp_port; SmtpServer.Credentials = new System.Net.NetworkCredential(email_sender_address, email_password); SmtpServer.EnableSsl = email_enable_SSL; SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network; SmtpServer.Send(mail); rproDBHandler.addLog(MainController.LogType.REPORT, "", "", MainController.Features.SEND_EMAIL, "Email Sent (Today's Log)", null); } catch (Exception e) { if (rproDBHandler != null) { string msg = "Exception occurred when sending email on error report. "; rproDBHandler.addLog(MainController.LogType.REPORT, "", "", MainController.Features.SEND_EMAIL, msg, e); } } }
public void runUpdateSlips() { error = 0; zeroCostError = 0; int slip_days = Int32.Parse(settings.getDays(feature)); string day_limit = DateTime.Now.AddDays((slip_days + 1) * -1).Date.ToString(MsSqlDBHandler.DATE_FORMAT); string sql = "SELECT SUBSTRING(SLIP_SID, 4, len(SLIP_SID)-3) AS sid FROM RetailPro_SLIP WHERE SLIP_DATE >= " + day_limit; HashSet <string> slipSIDs = null; try { slipSIDs = msSqlDBHandler.getExistingSIDs(sql, rproDBHandler, MainController.Features.SLIP); } catch (Exception) { return; } string subsidiaryFilter = settings.getSubsidiaries(feature); if (!string.IsNullOrWhiteSpace(subsidiaryFilter)) { if (subsidiaryFilter == "1") { subsidiaryFilter = " and slip.sbs_no = 1 and slip.in_sbs_no = 1 "; } else { subsidiaryFilter = " and slip.sbs_no in (" + subsidiaryFilter + ") "; } } try { Queue <Slip> slips = rproDBHandler.getSlips(slipSIDs, slip_days, subsidiaryFilter); int slipCount = slips.Count; msSqlDBHandler.insertSlips(slips, rproDBHandler); sql = "select to_char(item.slip_sid) as doc_sid, alu from slip_item_v item inner join invn_sbs_v invn on item.item_sid = invn.item_sid and invn.sbs_no = 1 "; sql += "inner join slip_v slip on slip.slip_sid = item.slip_sid where nvl(invn.cost, 0) = 0 and slip.modified_date >= trunc(sysdate)- " + slip_days + subsidiaryFilter + " order by item.slip_sid"; Queue <ZeroCostDocument> zeroCostDocuments = rproDBHandler.getZeroCostDocument(sql, MainController.Features.SLIP); int zeroCostTotal = zeroCostDocuments.Count; rproDBHandler.insertZeroCostDocuments(zeroCostDocuments, MainController.Features.SLIP); string msg = slipCount + " Slips fetched and inserted into B1 with " + error + " error(s). \n "; msg += zeroCostTotal + " items with zero cost were inserted with " + zeroCostError + " errors. \n"; if (error + zeroCostTotal > 0) { string subject = "Errors/Zero cost in B1 Interface for " + feature.ToString(); string body = msg; body += "Please check log for details."; new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } rproDBHandler.addLog(MainController.LogType.REPORT, "", "", MainController.Features.SLIP, msg, null); } catch (Exception e) { string subject = "Exception occured when sending processing " + feature.ToString(); string body = "An unexpected exception occured: " + e.ToString(); new EmailController(settings).sendEmail(subject, body, rproDBHandler, feature); } }