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); } }