// OLY_OCM-615の仕様変更によって、これから使う予定
|
global without sharing class ET_Product_ScoreTableHistory_Batch2 implements Database.Batchable<sObject>, Database.Stateful{
|
|
global Integer totalCount = 0; // 总件数
|
global Integer executedCount = 0;
|
global Integer successCount = 0; // 成功件数
|
global Integer failedCount = 0; // 失败件数
|
global String errMsgBody = '';
|
global String term;
|
global Boolean isAllFlag = false;
|
global Integer cstYear = 9999;
|
global Integer cstMonth = 0;
|
global Integer testSkip = 0;
|
|
// 月ごとにProduct_Score_Table_History__cのSales_Amount__cとQty__cに集計する
|
// ②活動管理画面で第二テーブルの最新実績金額という列がSales_Amount__cを使ってる
|
global Database.QueryLocator start(Database.BatchableContext BC){
|
// testskip
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
testSkip++;
|
|
System.debug('********** ET_Product_ScoreTableHistory_Batch2'+BC.getJobId()+'start');
|
|
Date systemDate = Date.today().addDays(-Integer.valueof(System.Label.ET_Date_Calculate));
|
|
Set<String> s = new Set<String>{'停止','有効','有効（再申請中）','失効（期限内生産済在庫対応）'};
|
Product2 p = [SELECT Plan_Term__c FROM Product2
|
WHERE Is_ET_APP__c = TRUE
|
AND Category3__c <> ''
|
AND SFDA_Status__c IN :s
|
LIMIT 1];
|
|
Date date_limit_h = Date.today().addDays(-1);
|
Date date_limit_l = Date.newInstance(date_limit_h.year(), date_limit_h.month(), 1);
|
term = p.Plan_Term__c.left(4);
|
if (4 == Date.today().month() && Date.today().day() <= 1) {
|
term = String.valueOf(Integer.valueOf(term.left(3))-1)+'P';
|
}
|
|
if (true == this.isAllFlag && 9999 != cstYear && 0 != cstMonth) {
|
date_limit_h = Date.newInstance(cstYear, cstMonth + 1, 1).addDays(-1);
|
date_limit_l = Date.newInstance(cstYear, cstMonth, 1);
|
// the difference month between the year of cstDate and this year(cstDate - YYYY/04/01)
|
Integer year_diff = Date.today().addDays(-1).addMonths(-3).year() - date_limit_l.addMonths(-3).year();
|
if (year_diff > 0) {
|
term = String.valueOf(Integer.valueOf(term.left(3)) - year_diff) + 'P';
|
}
|
}
|
|
//Get 直销Recordtype Id
|
Id PSTH_RecordTypeId = Schema.SObjectType.Product_Score_Table_History__c.getRecordTypeInfosByDeveloperName().get('Company').getRecordTypeId();
|
/*
|
* 按月筛选本次 Batch 产生/更新的行
|
*/
|
Product_Score_Table_History__c [] pcts = [
|
SELECT Id
|
FROM Product_Score_Table_History__c
|
WHERE Shipping_date__c = :date_limit_l
|
AND RecordTypeId = :PSTH_RecordTypeId
|
AND Province_From_Consume__c = FALSE
|
];
|
/*
|
* 清除表中临时统计信息
|
*/
|
// for(Product_Score_Table_History__c pct: pcts) {
|
// pct.put('Sales_Amount__c', 0);
|
// pct.put('Qty__c', 0);
|
// }
|
delete pcts;
|
|
// Id devRecordTypeId = Schema.SObjectType.Account.getRecordTypeInfosByDeveloperName().get('Agency').getRecordTypeId();
|
// ETAPP重点产品拆分 fy start Consumable_product__r.Product2__r.Category5__c
|
String query =
|
' SELECT Detail_Count__c, Intra_Trade_Without_Tax__c, Dealer_Info_text__c, Dealer_Info_ID__c, HospItal_Name__c, HospItal_Code__c, Hospital_ID__c, Consumable_product__c, Product_OutDate__c, Asset_Model_No__c, ' +
|
' Consumable_product__r.Product2__r.Category3__c, Consumable_product__r.Product2__r.Category4__c,Consumable_product__r.Product2__r.Category5__c,Consumable_product__r.Product2__r.Plan_Term__c,Consumable_Sale_order__r.Opportunity__r.Sales_Root__c' +
|
' FROM Consumable_order_details2__c '+
|
' WHERE (NOT Dealer_Info_text__c LIKE \'%TEST%\') ';
|
// if (true != this.isAllFlag) {
|
// query +=
|
// ' AND LastModifiedDate >= :systemDate ';
|
// }
|
query +=
|
' AND (NOT HospItal_Name__c LIKE \'%TEST%\') ' +
|
' AND HospItal_Name__c <> null ' +
|
' AND Consumable_product__r.Product2__r.Category3__c <> null ' +
|
' AND Consumable_product__r.Product2__r.Category4__c <> null ' +
|
' AND Detail_Count__c <> null ' +
|
' AND Intra_Trade_Without_Tax__c <> null ' +
|
' AND Inventory_Status__c <> \'未到经销商\' ' +
|
' AND Inventory_Status__c <> \'经销商库存\' ' +
|
' AND Consumable_product__r.Product2__r.Is_ET_APP__c = true ' +
|
' AND Cancellation_Flag__c = false ' +
|
' AND Dealer_Returned__c = false ' +
|
' AND Lose_Flag__c = false' +
|
' AND Consumable_Sale_order__r.Opportunity__r.Sales_Root__c = \'OCM直接販売\'' +
|
' AND Product_OutDate__c >= :date_limit_l' +
|
' AND Product_OutDate__c <= :date_limit_h';
|
return Database.getQueryLocator(query);
|
}
|
|
global void execute(Database.BatchableContext BC, List<Consumable_order_details2__c> codList){
|
totalCount += codList.size();
|
|
System.debug('ET_Product_ScoreTableHistory_Batch2 '+BC.getJobId()+' execute start');
|
//产品得分表取得
|
List<String> pstIdList = new List<String>();
|
for (Consumable_order_details2__c cod : codList) {
|
String pSTUniqueKey = term + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
|
// ETAPP重点产品拆分 fy start Consumable_product__r.Product2__r.Category5__c
|
pstIdList.add(uniqueCalculate(pSTUniqueKey, cod.Consumable_product__r.Product2__r.Category5__c,cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c));
|
}
|
Map<String, Product_Score_Table__c> pstIdMap = new Map<String, Product_Score_Table__c>();
|
for(Product_Score_Table__c tmpObj : [SELECT Id, UniqueKey__c
|
FROM Product_Score_Table__c
|
WHERE UniqueKey__c IN :pstIdList]){
|
pstIdMap.put(tmpObj.UniqueKey__c, tmpObj);
|
}
|
|
// 医院和经销商的关联参照取得
|
// List<String> hosIdList = new List<String>();
|
// for (Consumable_order_details2__c cod : codList) {
|
// hosIdList.add(cod.Hospital_ID__c);
|
// }
|
// Map<String, Agency_Hospital_Link__c> hosAgcMap = new Map<String, Agency_Hospital_Link__c>();
|
// for(Agency_Hospital_Link__c tmpObj : [SELECT Id, Name, Agency__c, Agency__r.Name, Hospital__c
|
// FROM Agency_Hospital_Link__c
|
// WHERE Hospital__c IN :hosIdList
|
// AND AgencyHos_ET__c = true
|
// AND Agency__c IN (SELECT Agent_Ref__c
|
// FROM Account
|
// WHERE ET_SP_Dealer__c = true)]){
|
// hosAgcMap.put(tmpObj.Hospital__c, tmpObj);
|
// }
|
|
// 医院和管理省的关联参照取得
|
List<String> hosIdList = new List<String>();
|
for (Consumable_order_details2__c cod : codList) {
|
hosIdList.add(cod.Hospital_ID__c);
|
}
|
Map<String, Account> hosProvinMap = new Map<String, Account>();
|
for(Account tmpObj : [SELECT Id, Name, OCM_man_province_HP__c
|
FROM Account
|
WHERE Id IN :hosIdList]){
|
hosProvinMap.put(tmpObj.Id, tmpObj);
|
}
|
|
// upsert产品得分表历史
|
// 今現在処理対象の合計
|
Map<String, Product_Score_Table_History__c> tempRollupMap = new Map<String, Product_Score_Table_History__c>();
|
//Get 经销商Recordtype Id
|
Id PSTH_RecordTypeId = Schema.SObjectType.Product_Score_Table_History__c.getRecordTypeInfosByDeveloperName().get('Company').getRecordTypeId();
|
|
for (Consumable_order_details2__c cod : codList) {
|
|
//uniqueKeyATR : U:计画财年(151P):发货月:医院:经销商:第三分类 :新4.5分类
|
String uniqueKeyPSTH = 'U:' + term + ':' + cod.Product_OutDate__c.month() + ':' + cod.HospItal_Code__c + ':' + cod.Dealer_Info_ID__c + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
|
// ETAPP重点产品拆分 fy start Consumable_product__r.Product2__r.Category5__c
|
uniqueKeyPSTH = uniqueCalculate(uniqueKeyPSTH, cod.Consumable_product__r.Product2__r.Category5__c,cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c);
|
|
// Shipping_date__c yyyy/mm/01
|
Date sd = Date.newInstance(cod.Product_OutDate__c.year(), cod.Product_OutDate__c.month(), 1);
|
Product_Score_Table_History__c psth = new Product_Score_Table_History__c(
|
RecordTypeId = PSTH_RecordTypeId,
|
UniqueKey__c = uniqueKeyPSTH,
|
Shipping_date__c = sd,
|
Hospital__c = cod.Hospital_ID__c,
|
Agency__c = cod.Dealer_Info_ID__c
|
);
|
|
//产品得分表 step: soql map id
|
String pstUniqueKey = term + ':' + cod.Consumable_product__r.Product2__r.Category3__c + ':';
|
// ETAPP重点产品拆分 fy start Consumable_product__r.Product2__r.Category5__c
|
pstUniqueKey = uniqueCalculate(pstUniqueKey, cod.Consumable_product__r.Product2__r.Category5__c,cod.Consumable_product__r.Product2__r.Category4__c, cod.Consumable_product__r.Product2__r.Category3__c, cod.Asset_Model_No__c);
|
|
if (pstIdMap.containsKey(pstUniqueKey)) {
|
psth.Product_Score_Table__c = pstIdMap.get(pstUniqueKey).Id;
|
}
|
|
//设置经销商字段
|
// String hospitalKey = cod.Hospital_ID__c;
|
// if (hosAgcMap.containsKey(hospitalKey)) {
|
// psth.Agency__c = hosAgcMap.get(hospitalKey).Agency__c;
|
// }
|
|
//设置医院的管理省
|
String hospitalKey = cod.Hospital_ID__c;
|
if (hosProvinMap.containsKey(hospitalKey)) {
|
psth.OCM_Province_Text__c = hosProvinMap.get(hospitalKey).OCM_man_province_HP__c;
|
}
|
|
//金額(含税): Intra_Trade_Without_Tax__c と 数量(盒)：Detail_Count__c ばら売りした場合は小数が入る
|
if (tempRollupMap.containsKey(uniqueKeyPSTH)) {
|
Product_Score_Table_History__c tempPSTH = tempRollupMap.get(uniqueKeyPSTH);
|
psth.Sales_Amount__c = cod.Intra_Trade_Without_Tax__c + tempPSTH.Sales_Amount__c;
|
psth.Qty__c = cod.Detail_Count__c + tempPSTH.Qty__c;
|
tempRollupMap.put(uniqueKeyPSTH, psth);
|
} else {
|
psth.Sales_Amount__c = cod.Intra_Trade_Without_Tax__c;
|
psth.Qty__c = cod.Detail_Count__c;
|
tempRollupMap.put(uniqueKeyPSTH, psth);
|
}
|
|
}
|
|
// 既に保存済みのRecord soql map
|
Map<String, Product_Score_Table_History__c> donePSTHMap = new Map<String, Product_Score_Table_History__c>();
|
for(Product_Score_Table_History__c tmpPSTH : [SELECT Id, UniqueKey__c, Sales_Amount__c, Shipping_date__c,
|
Hospital__c, Agency__c,Qty__c
|
FROM Product_Score_Table_History__c
|
WHERE UniqueKey__c IN :tempRollupMap.keySet()]){
|
donePSTHMap.put(tmpPSTH.UniqueKey__c, tmpPSTH);
|
}
|
|
//取得既存テーブルのSales_Amount__cとQty__c 集計
|
for (String uniqueKey : tempRollupMap.keySet()) {
|
Product_Score_Table_History__c psth = tempRollupMap.get(uniqueKey);
|
//有既存资料且为第二次进来的产品得分表历史相加
|
if (donePSTHMap.containsKey(uniqueKey)) {
|
Product_Score_Table_History__c tmpPSTH = donePSTHMap.get(uniqueKey);
|
tmpPSTH.Sales_Amount__c = psth.Sales_Amount__c + tmpPSTH.Sales_Amount__c;
|
tmpPSTH.Shipping_date__c = psth.Shipping_date__c;
|
tmpPSTH.Hospital__c = psth.Hospital__c;
|
tmpPSTH.Agency__c = psth.Agency__c;
|
tmpPSTH.RecordTypeId = psth.RecordTypeId;
|
tmpPSTH.Product_Score_Table__c = psth.Product_Score_Table__c;
|
tmpPSTH.Qty__c = psth.Qty__c+tmpPSTH.Qty__c;
|
tempRollupMap.put(uniqueKey, tmpPSTH); // doneのIdを利用する
|
}
|
else {
|
tempRollupMap.put(uniqueKey, psth); // doneのIdを利用する
|
}
|
|
}
|
|
|
if (!tempRollupMap.isEmpty()){
|
Database.UpsertResult [] results = Database.upsert(tempRollupMap.values(), Product_Score_Table_History__c.Id, false);
|
for(Integer index = 0; index < results.size(); index++) {
|
if(results[index].isSuccess()) {
|
successCount++;
|
}
|
else{
|
for (Database.Error err : results[index].getErrors()) {
|
String message = 'UniqueKey : '
|
+ tempRollupMap.values()[index].UniqueKey__c + ' ; '
|
+ err.getStatusCode() + '; '
|
+ err.getFields() + ' ; '
|
+ err.getMessage();
|
failedCount++;
|
errMsgBody += message + '\n';
|
}
|
}
|
}
|
}
|
|
executedCount += codList.size();
|
System.debug('ET_Product_ScoreTableHistory_Batch2 execute end');
|
}
|
|
global void finish(Database.BatchableContext BC) {
|
System.debug('ET_Product_ScoreTableHistory_Batch2 finish method start');
|
BatchEmailUtil eb = new BatchEmailUtil();
|
String title = '产品得分表历史Batch(直销实绩)';
|
String[] emailList = new String[] {};
|
// 失败件数为0 寄出成功的mail
|
if (failedCount == 0 && totalCount == executedCount) {
|
String emailLabel = 'BatchNotify';
|
for (OrgWideEmailAddress tmpEmailObj : [SELECT Id, Address, DisplayName
|
FROM OrgWideEmailAddress
|
WHERE DisplayName = :emailLabel]) {
|
emailList.add(tmpEmailObj.Address);
|
}
|
eb.successMail(emailList, null, title, executedCount);
|
}
|
else {
|
if (totalCount != executedCount) {
|
errMsgBody += '有一部分Batch没有运行, 请确认系统管理员。\n';
|
}
|
eb.failedMail(title, errMsgBody, totalCount, executedCount, failedCount);
|
}
|
eb.send();
|
System.debug('ET_Product_ScoreTableHistory_Batch2 finish method end');
|
}
|
|
private static String uniqueCalculate(String uniqueKey, String category5,String category4, String category3, String assetModelNo) {
|
if ('吸引活检针' == category4 && 'EUS' == category3) {
|
if (assetModelNo.startsWith('NA-U200H')) {
|
uniqueKey += '吸引活检针-EZ3P';
|
} else {
|
uniqueKey += '吸引活检针-EZ3P以外';
|
}
|
} else if ('先端系粘膜切开刀' == category4 && 'ESD' == category3) {
|
if (assetModelNo.startsWith('KD-655')) {
|
uniqueKey += '先端系粘膜切开刀-DualJ';
|
} else {
|
uniqueKey += '先端系粘膜切开刀-DualJ以外';
|
}
|
}
|
// ETAPP重点产品拆分 fy start
|
else if('导丝' == category4){
|
if (assetModelNo.startsWith('G-240')) {
|
uniqueKey += '导丝-G-240';
|
}else{
|
uniqueKey += '导丝-G-260';
|
}
|
}
|
else if('乳头切开刀' == category4){
|
if(category5.contains('乳头切开-三腔')){
|
uniqueKey +='乳头切开刀-三腔';
|
}else{
|
uniqueKey +='乳头切开刀-其他';
|
}
|
} else if('注射针（胃镜）'== category4){
|
if (assetModelNo.startsWith('NM-20')){
|
uniqueKey += '注射针（胃镜）-200';
|
}else{
|
uniqueKey += '注射针（胃镜）-400';
|
}
|
}
|
else if('注射针（肠镜）'== category4){
|
if (assetModelNo.startsWith('NM-20')){
|
uniqueKey += '注射针（肠镜）-200';
|
}else{
|
uniqueKey += '注射针（肠镜）-400';
|
}
|
}
|
else if('呼吸科ET'==category3 && '吸引活检针'==category4){
|
if(assetModelNo.startsWith('NA-U401SX')||assetModelNo.startsWith('NA-U403SX')){
|
uniqueKey +='吸引活检针-Visishot2';
|
}else{
|
uniqueKey +='吸引活检针-Visishot2以外';
|
}
|
}
|
// ETAPP重点产品拆分 fy end
|
else {
|
uniqueKey += category4;
|
}
|
|
return uniqueKey;
|
}
|
}
|
