@Test(dependsOnMethods = {"testSheet"})
public void readDatabase() {
List<AnnualDataFleetFuel> annualDataFleetFuelList = annualDataService.findAll();
for (AnnualDataFleetFuel annualDataFuel : annualDataFleetFuelList) {
System.out.println(
"READING DATABASE - TruckId: "
+ annualDataFuel.getTruckId()
+ ". Date: "
+ annualDataFuel.getTransactionDate()
+ ". Amount: "
+ annualDataFuel.getMonthlyFuelCost()
+ ". Closing Mileage: "
+ annualDataFuel.getClosingMileage()
+ ". Driver Person ID: "
+ annualDataFuel.getDriverPersonId());
}
}
@Test
public void testSheet() {
try {
truckService = ctx.getBean(TruckService.class);
annualDataService = ctx.getBean(AnnualDataFleetFuelService.class);
personService = ctx.getBean(PersonService.class);
StringTokenizer stringTokenizer;
SimpleDateFormat simpleFormat = (SimpleDateFormat) DateFormat.getDateInstance();
simpleFormat.applyPattern("dd-MMM-yyyy");
// Open the Excel File
URL url = this.getClass().getResource("/setupfleet/Fleet_Data_Annual.xls");
FileInputStream fileInputStream = new FileInputStream(url.getFile());
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); // Open Spreadsheet Work book
// Process each Sheet at a time. API FAILS to move to next sheets
for (int j = 16; j <= 16; j++) {
Date datee = null;
String truckId = null;
String driverPersonId = null;
BigDecimal amount;
Integer closingMileage;
// Get the first Sheet(0)
HSSFSheet worksheet = workbook.getSheetAt(j); // First Sheet is 0 , second is 1 and so on
vehicleNumber = worksheet.getRow(4).getCell(0).toString().trim();
employeeNumber = worksheet.getRow(4).getCell(4).toString().trim();
System.out.println(
"\n\n"
+ "Begin processing Sheet: "
+ j
+ " i.e. "
+ vehicleNumber
+ ", Driver: "
+ employeeNumber);
// Get the Truck based of the VEHICLEnUMBER
truck = truckService.findByVehicleNumber(vehicleNumber);
// Set this as Start Milleage for Truck
if (truck != null) {
truckId = truck.getId();
}
// Get personId
driver = personService.findDriverWithEmployeeNumber(employeeNumber);
if (driver != null) {
driverPersonId = driver.getId();
}
if (truck != null && driver != null) {
// iterated through all the rows
for (int i = 4;
i <= worksheet.getPhysicalNumberOfRows();
i++) { // if Row 6, index is (5)
try {
datee =
simpleFormat.parse(
worksheet.getRow(i).getCell(1).toString().trim()); // String to Date
} catch (ParseException ex) {
Logger.getLogger(Setup_DailyInputSheetOne.class.getName())
.log(Level.SEVERE, null, ex);
List<AnnualDataFleetFuel> annualDataFleetFuelList = annualDataService.findAll();
for (AnnualDataFleetFuel annualDataFuel : annualDataFleetFuelList) {
System.out.println(
"READING DATABASE - TruckId: "
+ annualDataFuel.getTruckId()
+ ". Date: "
+ annualDataFuel.getTransactionDate()
+ ". Amount: "
+ annualDataFuel.getMonthlyFuelCost()
+ ". Closing Mileage: "
+ annualDataFuel.getClosingMileage()
+ ". Driver Person ID: "
+ annualDataFuel.getDriverPersonId());
}
}
//
amount = new BigDecimal(worksheet.getRow(i).getCell(2).toString().trim());
if (amount.compareTo(new BigDecimal("0.0")) == 0) {
amount = new BigDecimal("0.00");
}
//
String mileageToken = worksheet.getRow(i).getCell(3).toString().trim();
stringTokenizer = new StringTokenizer(mileageToken, ".");
closingMileage = Integer.parseInt(stringTokenizer.nextElement().toString());
//
System.out.println(
"TruckId: "
+ truckId
+ ". Date: "
+ datee
+ ". Amount: "
+ amount.setScale(2, BigDecimal.ROUND_HALF_UP)
+ ". Closing Mileage: "
+ closingMileage
+ ". Driver Person ID: "
+ driverPersonId);
// Build and Persist the AnnualDataFleetFuel Object
AnnualDataFleetFuel annualData =
createAnnualDataEntity(
datee,
amount.setScale(2, BigDecimal.ROUND_HALF_UP),
closingMileage,
truckId,
driverPersonId);
annualDataService.persist(annualData);
// // Build and Update the Truck Object
// addTruckAnnualData(annualData);
}
} else {
if (truck != null) {
System.out.println("Truck NOT FOUND");
}
if (driver != null) {
System.out.println("DRIVER NOT FOUND");
}
}
}
fileInputStream.close();
} catch (FileNotFoundException e) {
System.out.println("\n\n" + "File Not Found in function setupDailyInputs()");
System.out.println("" + e);
} catch (IOException e) {
System.out.println("\n\n" + "Problem Reading File: /setupfleet/Fleet_Data_Annual.xls");
System.out.println("" + e);
}
}
