Description: You can use this code extension to remove orphan data from the inv_unit_equip_damages table and inv_unit_equip_damage_item table as required.
Abstract Base Class: com.navis.external.argo.AbstractGroovyJobCodeExtension
Method: deleteOrphanEqDamages(), internally calls the deleteDamages() and deleteDamageItem() methods
Interface:EGroovyJobCodeExtension
Module: Gate
Version Added: 3.7.19 and 3.8.15
Requires Code Extension Name or Name Pattern: Yes
Code Extension Name or Name Pattern: DeleteOrphanEquipmentDamages
Where to Specify Code Extensions of this Type: You must configure the groovy job with this code extension at a specific time when it is required to run and clean the orphan data. It is recommended to run this groovy job during the non-operational time.
System-Seeded Code Extensions Using this Type: DeleteOrphanEquipmentDamagesSample
Code example
The following sample cove implements a code extension of type GROOVY_JOB_CODE_EXTENSION as required to remove orphan data from the inv_unit_equip_damages table and inv_unit_equip_damage_item table.
/*
* $Id$
* Copyright (c) 2021 Navis LLC. All Rights Reserved.
*
*/
package system
mport com.navis.external.argo.AbstractGroovyJobCodeExtension
import com.navis.framework.business.Roastery
import com.navis.framework.persistence.DatabaseHelper
import com.navis.framework.util.BatchKeys
import com.navis.framework.util.LogUtils
import com.navis.road.RoadEntity
import org.apache.commons.lang.StringUtils
import org.apache.log4j.Level
import org.springframework.jdbc.core.JdbcTemplate
class DeleteOrphanEquipmentDamagesSample extends AbstractGroovyJobCodeExtension {
@Override
public void execute(Map<String, Object> inParams) {
LogUtils.setLogLevel(DeleteOrphanEquipmentDamagesSample.class, Level.INFO);
deleteOrphanEqDamages();
log("Deleting Orphan records of Unit Equipment Damages is completed.");
}
private void deleteOrphanEqDamages() {
log("deleteOrphanEqDamages STARTED");
log("deleteOrphanEqDamages DAMAGE ITEM FOR TRUCK TRANSACTION STARTED");
deleteDamageItem(EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_TRAN);
log("deleteOrphanEqDamages DAMAGE ITEM FOR TRUCK TRANSACTION END");
log("deleteOrphanEqDamages DAMAGE ITEM FOR GATE INSPECTION START");
deleteDamageItem(EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_INSP);
log("deleteOrphanEqDamages DAMAGE ITEM FOR GATE INSPECTION END");
log("deleteOrphanEqDamages DAMAGE ITEM FOR TRUCK TRANSACTION BUNDLE STARTED");
deleteDamageItem(EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_TRANBND);
log("deleteOrphanEqDamages DAMAGE ITEM FOR TRUCK TRANSACTION BUNDLE END");
log("deleteOrphanEqDamages DAMAGES FOR TRUCK TRANSACTION STARTED");
deleteDamages(EQ_DAMAGE_GKEY_SELECT_FROM_TRAN);
log("deleteOrphanEqDamages DAMAGES FOR TRUCK TRANSACTION END");
log("deleteOrphanEqDamages DAMAGES FOR GATE INSPECTION START");
deleteDamages(EQ_DAMAGE_GKEY_SELECT_FROM_INSP);
log("deleteOrphanEqDamages DAMAGES FOR GATE INSPECTION END");
log("deleteOrphanEqDamages DAMAGES FOR TRUCK TRANSACTION BUNDLE STARTED");
deleteDamages(EQ_DAMAGE_GKEY_SELECT_FROM_TRANBND);
log("deleteOrphanEqDamages DAMAGES FOR TRUCK TRANSACTION BUNDLE END");
log("deleteOrphanEqDamages END");
}
private void deleteDamageItem(String inSelectQuery) {
try {
JdbcTemplate jt = new JdbcTemplate(_dbHelper.getDataSource());
List<Long> dmgItemGkeys = jt.queryForList(inSelectQuery, Long.class);
String deletesql = "delete from inv_unit_equip_damage_item where gkey in (";
BatchKeys<Long> dmgItemBatchGkeys = new BatchKeys<>(dmgItemGkeys.toArray(new Long[dmgItemGkeys.size()]), 950);
for (Serializable[] dmgItemBatchGkey : dmgItemBatchGkeys) {
jt.update(deletesql + StringUtils.join(dmgItemBatchGkey, ',') + ')');
}
} catch (final Exception upgradeException) {
log(Level.ERROR, "Exception occurred while delete damage items" + upgradeException);
}
}
private void deleteDamages(String inSelectQuery) {
try {
JdbcTemplate jt = new JdbcTemplate(_dbHelper.getDataSource());
List<Long> dmgGkeys = jt.queryForList(inSelectQuery, Long.class);
String deletesql = "delete from inv_unit_equip_damages where gkey in (";
BatchKeys<Long> dmgBatchGkeys = new BatchKeys<>(dmgGkeys.toArray(new Long[dmgGkeys.size()]), 950);
for (Serializable[] dmgBatchGkey : dmgBatchGkeys) {
jt.update(deletesql + StringUtils.join(dmgBatchGkey, ',') + ')');
}
} catch (final Exception upgradeException) {
log(Level.ERROR, "Exception occurred while delete damages" + upgradeException);
}
}
private static String selectDamageItemQuery(String inEntityName, String inTableName) {
return "select gkey from inv_unit_equip_damage_item where dmgs_gkey in (select dmgs_gkey FROM inv_unit_equip_damage_item dmgItem\n" +
" INNER JOIN inv_unit_equip_damages dmg\n" +
" ON dmgItem.dmgs_gkey = dmg.gkey\n" +
" WHERE dmg.owner_entity = '" + inEntityName + "' and dmg.owner_gkey NOT IN (select gkey from " + inTableName + "))";
}
private static String selectDamageQuery(String inEntityName, String inTableName) {
return "select gkey from inv_unit_equip_damages eqdm where eqdm.owner_entity = '" + inEntityName + "' and NOT exists " +
"(select 1 from " + inTableName + " rt where eqdm.owner_gkey=rt.gkey) and owner_gkey is not null";
}
DatabaseHelper _dbHelper = Roastery.getBean(DatabaseHelper.BEAN_ID);
private static String EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_TRAN = selectDamageItemQuery(RoadEntity.TRUCK_TRANSACTION, "road_truck_transactions");
private static String EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_INSP = selectDamageItemQuery(RoadEntity.ROAD_INSPECTION, "road_inspections");
private static String EQ_DAMAGE_ITEM_GKEY_SELECT_FROM_TRANBND =
selectDamageItemQuery(RoadEntity.TRUCK_TRANSACTION_BUNDLE, "road_truck_transaction_bundles");
private static String EQ_DAMAGE_GKEY_SELECT_FROM_TRAN = selectDamageQuery(RoadEntity.TRUCK_TRANSACTION, "road_truck_transactions");
private static String EQ_DAMAGE_GKEY_SELECT_FROM_INSP = selectDamageQuery(RoadEntity.ROAD_INSPECTION, "road_inspections");
private static String EQ_DAMAGE_GKEY_SELECT_FROM_TRANBND =
selectDamageQuery(RoadEntity.TRUCK_TRANSACTION_BUNDLE, "road_truck_transaction_bundles");
}