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