Thread: Logical Error in Multi-Cell Combinational CSV File Analytics Java Program

I'm working on a Java program to track bonus (free) products given to customers. The problem occurs when a campaign with minimum quantity requirements is satisfied for the first product but not the second one. The campaign should not be valid for the first product sale either, but the code fails to cut the bonuses correctly in this specific scenario.

The relevant parts of my code are below. This is the code so far (***I strongly suspect either the compareSalesWithCampaigns() or the getCampaignBonus() method is the culprit*** for minimal reproducible example purposes):

```
package mf_app.BonusCalculate_3;

import com.opencsv.CSVReader;
import com.opencsv.CSVWriter;

import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class BonusTracker {
private static final SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
private static List<Map<String, String>> campaignData;

public static void main(String[] args) {
String salesFilePath = "./sales.csv";
String campaignsFilePath = "./bonuses.csv";
String reportFilePath = "./output.csv";

try {
ParsedCSV salesParsedCSV = parseCSV(salesFilePath);
ParsedCSV campaignsParsedCSV = parseCSV(campaignsFilePath);
campaignData = campaignsParsedCSV.data;

List<Map<String, String>> discrepancies = compareSalesWithCampaigns(salesParsedCSV.data, campaignData);
generateReport(discrepancies, reportFilePath, salesParsedCSV.headers);
} catch (IOException | ParseException e) {
e.printStackTrace();
}
}

static class ParsedCSV {
List<String> headers;
List<Map<String, String>> data;

public ParsedCSV(List<String> headers, List<Map<String, String>> data) {
this.headers = headers;
this.data = data;
}
}

private static ParsedCSV parseCSV(String filePath) throws IOException {
List<Map<String, String>> data = new ArrayList<>();
List<String> headersList = new ArrayList<>();
try (CSVReader reader = new CSVReader(new FileReader(filePath))) {
String[] headers = reader.readNext();
headersList.addAll(Arrays.asList(headers));
String[] line;
while ((line = reader.readNext()) != null) {
Map<String, String> row = new HashMap<>();
for (int i = 0; i < headers.length; i++) {
row.put(headers[i], line[i]);
}
data.add(row);
}
}
return new ParsedCSV(headersList, data);
}

private static List<Map<String, String>> compareSalesWithCampaigns(List<Map<String, String>> salesData, List<Map<String, String>> campaignData) throws ParseException {
List<Map<String, String>> discrepancies = new ArrayList<>();

for (Map<String, String> sale : salesData) {
String productBarcode = sale.get("BARCODE");
int saleQuantity = Integer.parseInt(sale.get("QUANTITY"));
int saleBonus = Integer.parseInt(sale.get("BONUS"));

int bestCampaignBonus = 0;
for (Map<String, String> campaign : campaignData) {
boolean isEligible = isProductEligibleForCampaign(campaign, productBarcode, saleQuantity);
if (isEligible) {
int campaignBonus = getCampaignBonus(campaign, saleQuantity);
if (campaignBonus > bestCampaignBonus && campaignBonus <= saleBonus) {
bestCampaignBonus = campaignBonus;
}
}
}

int autoApproved = bestCampaignBonus;
int autoCut = saleBonus - autoApproved;

Map<String, String> discrepancyRecord = new HashMap<>(sale);
discrepancyRecord.put("AUTO APPROVED", String.valueOf(autoApproved));
discrepancyRecord.put("AUTO CUT", String.valueOf(autoCut));
discrepancies.add(discrepancyRecord);
}

return discrepancies;
}

private static boolean isProductEligibleForCampaign(Map<String, String> campaign, String productBarcode, int saleQuantity) {
for (int i = 1; i <= 3; i++) {
String campaignProductBarcode = campaign.get("PRODUCT_" + i + "_BARCODE");
if (campaignProductBarcode.equals(productBarcode)) {
int minOrderQuantity = Integer.parseInt(campaign.get("PRODUCT_" + i + "_MINIMUM_QUANTITY"));
if (saleQuantity >= minOrderQuantity) {
return true;
}
}
}
return false;
}

private static int getCampaignBonus(Map<String, String> campaign, int totalSaleQuantity) {
int maxCampaignBonus = 0;
for (int i = 1; i <= 3; i++) {
String campaignQtyStr = campaign.get("PRODUCT_" + i + "_MINIMUM_QUANTITY");
if (!campaignQtyStr.isEmpty()) {
int campaignQty = Integer.parseInt(campaignQtyStr);
if (totalSaleQuantity >= campaignQty) {
int campaignBonus = Integer.parseInt(campaign.get("PRODUCT_" + i + "_MF"));
maxCampaignBonus = Math.max(maxCampaignBonus, campaignBonus);
}
}
}
return maxCampaignBonus;
}

private static void generateReport(List<Map<String, String>> discrepancies, String filePath, List<String> headers) throws IOException {
try (CSVWriter writer = new CSVWriter(new FileWriter(filePath))) {
writer.writeNext(headers.toArray(new String[0]));
for (Map<String, String> discrepancy : discrepancies) {
List<String> record = new ArrayList<>();
for (String header : headers) {
record.add(discrepancy.getOrDefault(header, ""));
}
writer.writeNext(record.toArray(new String[0]));
}
}
}
}
```

this is a sample bonus campaigns CSV file ("MF" is company shorthand for a specific type of bonus, and not something vulgar:)

[sample bonus campaigns CSV file]
PRODUCT_1_NAME,PRODUCT_1_BARCODE,PRODUCT_1_MINIMUM _QUANTITY,PRODUCT_1_MF,PRODUCT_1_PRICE,PRODUCT_2_N AME,PRODUCT_2_BARCODE,PRODUCT_2_MINIMUM_QUANTITY,P RODUCT_2_MF,PRODUCT_2_PRICE,PRODUCT_3_NAME,PRODUCT _3_BARCODE,PRODUCT_3_MINIMUM_QUANTITY,PRODUCT_3_MF ,PRODUCT_3_PRICE,ValidFrom,ValidUntil
A,1,10,2,1000,,,,,,,,,,,01/01/2024,01/02/2024
A,1,10,3,1000,,,,,,,,,,,01/01/2024,01/02/2024
A,1,10,5,1000,,,,,,,,,,,10/01/2024,15/02/2024
A,1,10,6,1000,B,2,3,1,1500,,,,,,01/01/2024,01/02/2024
B,2,100,50,1500,,,,,,,,,,,01/01/2024,01/02/2024
B,2,10,3,1500,,,,,,,,,,,01/01/2024,01/02/2024

this is a sample sales data CSV file to be checked:

[sample sales data CSV file]

PRODUCT NAME,BARCODE,PHARMACY GLN NUMBER,PHARMACY NAME,DATE,ORDER NO,TOWN,PROVINCE,QUANTITY,BONUS,PRICE
A,1,100,Pharma 1,02/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,1000
A,1,200,Pharma 2,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,3,1000
A,1,300,Pharma 3,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,11,3,1000
A,1,400,Pharma 4,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,20,6,1000
A,1,500,Pharma 5,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000
A,1,600,Pharma 6,10/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000
A,1,700,Pharma 7,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000
B,2,700,Pharma 7,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,3,1,1500
A,1,800,Pharma 8,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000
B,2,800,Pharma 8,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,2,0,1500
B,2,900,Pharma 9,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,105,50,1500
B,2,1000,Pharma 10,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,20,10,1500
A,1,1100,Pharma 11,02/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,800
C,3,1200,Pharma 12,02/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,5,1,35


this is the output the program is supposed to get:

[output the program is supposed to get]

PRODUCT NAME,BARCODE,PHARMACY GLN NUMBER,PHARMACY NAME,DATE,ORDER NO,TOWN,PROVINCE,QUANTITY,BONUS,PRICE,WAREHOUSE,AU TO APPROVED,AUTO CUT,MANUAL APPROVE,HIGHLIGHT,NOTE
A,1,100,Pharma 1,02/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,1000,xxxxxxx,2,, ,,Auto Approved: 2.
A,1,200,Pharma 2,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,3,1000,xxxxxxx,3,, ,,Auto Approved: 3.
A,1,300,Pharma 3,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,11,3,1000,xxxxxxx,3,, ,,Auto Approved: 3.
A,1,400,Pharma 4,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,20,6,1000,xxxxxxx,6,, ,,Auto Approved: 6.
A,1,500,Pharma 5,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000,xxxxxxx,3,2 ,,!,Auto Approved: 3.
A,1,600,Pharma 6,10/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000,xxxxxxx,5,, ,,Auto Approved: 5.
A,1,700,Pharma 7,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000,xxxxxxx,6,, ,,Auto Approved: 6.
B,2,700,Pharma 7,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,3,1,1500,xxxxxxx,1,,, ,Auto Approved: 1.
A,1,800,Pharma 8,04/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000,xxxxxxx,3,3 ,,!,Auto Approved: 3.
B,2,800,Pharma 8,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,2,0,1500,xxxxxxx,0,,, ,NO BONUS
B,2,900,Pharma 9,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,105,50,1500,xxxxxxx,5 0,,,,Auto Approved: 50.
B,2,1000,Pharma 10,03/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,20,10,1500,xxxxxxx,6, 4,,!,Auto Approved: 6.
A,1,1100,Pharma 11,02/01/2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,800,xxxxxxx,2,,, !!,"Price mismatch, but: Auto Approved: 2. "

this is the output it is currently generating (notice line 10!!!):

[output it is currently generating]

PRODUCT NAME,BARCODE,PHARMACY GLN NUMBER,PHARMACY NAME,DATE,ORDER NO,TOWN,PROVINCE,QUANTITY,BONUS,PRICE,WAREHOUSE,AU TO APPROVED,AUTO CUT,MANUAL APPROVED,HIGHLIGHT,PRICE HIGHLIGHT,NOTE
A,1,100,Pharma 1,02.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,1000,TO_ CHECK_EXAMPLE,2,0,,,,Auto Approved: 2.
A,1,200,Pharma 2,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,3,1000,TO_ CHECK_EXAMPLE,3,0,,,,Auto Approved: 3.
A,1,300,Pharma 3,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,11,3,1000,TO_ CHECK_EXAMPLE,3,0,,,,Auto Approved: 3.
A,1,400,Pharma 4,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,20,6,1000,TO_ CHECK_EXAMPLE,6,0,,,,Auto Approved: 6.
A,1,500,Pharma 5,04.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000,TO_ CHECK_EXAMPLE,3,2,,!,,Auto Approved: 3.
A,1,600,Pharma 6,10.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,5,1000,TO_ CHECK_EXAMPLE,5,0,,,,Auto Approved: 5.
A,1,700,Pharma 7,04.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000,TO_ CHECK_EXAMPLE,6,0,,,,Auto Approved: 6.
B,2,700,Pharma 7,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,3,1,1500,TO_C HECK_EXAMPLE,1,0,,,,Auto Approved: 1.
A,1,800,Pharma 8,04.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,6,1000,TO_ CHECK_EXAMPLE,6,0,,,,Auto Approved: 6.
B,2,800,Pharma 8,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,2,0,1500,TO_C HECK_EXAMPLE,0,0,,,,NO BONUS
B,2,900,Pharma 9,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,105,50,1500,T O_CHECK_EXAMPLE,50,0,,,,Auto Approved: 50.
B,2,1000,Pharma 10,03.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,20,10,1500,T O_CHECK_EXAMPLE,6,4,,!,,Auto Approved: 6.
A,1,1100,Pharma 11,02.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,10,2,800,TO_ CHECK_EXAMPLE,2,0,,,!!,"Price mismatch, but: Auto Approved: 2. "
C,3,1200,Pharma 12,02.01.2024,xxxxxxx,xxxxxxx,xxxxxxx,5,1,35,TO_CH ECK_EXAMPLE,0,1,,!,,Rejected: No matching campaign found

the rules applied are based on the bonus campaigns CSV file. Some campaigns (each line in this CSV is a campaign) have more than one product a minimum quantity of which needs to be purchased for the campaign to be valid. For example, in the sample bonus campaigns file, there is a campaign which says that if a pharmacy gets 10 x product A and 3 x product B, it gets 6 x product A + 1 x product B for free. in the example sales file above, there's a line (line 10) where pharmacy with unique ID 800 has purchased 10 of product A. However, the same pharmacy/outlet has not purchased a minimum of 3 of product B, so it is not entitled to this campaign. the best campaign it is therefore entitled to is the 10 + 3 campaign (there's also a 10 + 5 campaign, but it is not valid for this date). That is why 3 of the 6 bonus products requested in line 10 should be cut.

as said above, I suspect the issue is in the compareSalesWithCampaigns or getCampaignBonus methods, but I can't seem to figure out the problem. For one thing, the code in isProductEligibleForCampaign() seems to check for a single product whether it is eligible for a campaign, no matter whether that is the first, second or third product, which is an issue.

each line of the bonus campaigns CSV file represent a specific campaign, and for a product to satisfy that campaign it is supposed to satisfy the minimum order quantity of every product in there. since each line in the sales CSV file is for a single product type, the program would need to check, using the "PHARMACY GLN NUMBER" column (this is the unique ID of every outlet), that the pharmacy has also purchased required amounts of the other products in a given campaign to be eligible. Any thoughts on how I can achieve this would be appreciated!

note also that this is a sort of minimum reproducible example version of a slightly longer program - I can share the full class if anyone thinks this might be helpful!