You need to sign in to do that
Don't have an account?
Kunal Purohit 4
How to parse multiple values?
Hello All,
I have written below code to parse values of excel sheet. It works fine if no of records is upto 4. But if i insert records beyond 4, i.e either 5 or 6 records, parse button didnt works. Below is the code.
I have written below code to parse values of excel sheet. It works fine if no of records is upto 4. But if i insert records beyond 4, i.e either 5 or 6 records, parse button didnt works. Below is the code.
public with sharing class InvoiceImportController { public InvoiceImportController() { } @RemoteAction public static Map<String, List<String>> getValidationData(string invoices, string invoiceDetails, string vendorBills, string vendorBillDetails) { Map<String, List<String>> outputMap = new Map<String, List<String>> (); // Parse the raw data List<invoiceStruct> invoiceList = parseInvoiceHeaders(invoices); List<invoiceDetailStruct> detailList = parseInvoiceDetails(invoiceDetails); List<vendorBillStruct> billList = parseVendorBills(vendorBills); List<vendorBillDetailStruct> billDetailList = parseVendorBillDetails(vendorBillDetails); // Capture the data points to validate Set<String> customerIds = new Set<String> (); Set<String> glAccountIds = new Set<String> (); Set<String> itemIds = new Set<String> (); Set<String> vendorIds = new Set<String> (); for (invoiceStruct inv : invoiceList) { customerIds.add(inv.record.Customer_ID__c); glAccountIds.add(inv.record.GL_Account_Id__c); } for (invoiceDetailStruct invDetail : detailList) { itemIds.add(invDetail.record.Item_Id__c); } for (vendorBillStruct vb : billList) { customerIds.add(vb.record.Customer__c); glAccountIds.add(vb.record.GL_Account_Id__c); vendorIds.add(vb.record.Vendor_Id__c); } for (vendorBillDetailStruct vbd : billDetailList) { itemIds.add(vbd.record.Item_Id__c); } Map<Id, Account> customerIdMap = new Map<Id, Account> ([select id from account where id in :customerIds]); Map<Id, GL_Account__c> glAccountIdMap = new Map<Id, GL_Account__c> ([select id from gl_account__c where id in :glAccountIds]); Map<Id, Netsuite_Item__c> itemIdMap = new Map<Id, Netsuite_Item__c> ([Select id from Netsuite_Item__c where id in :itemIds]); Map<Id, Account> vendorIdMap = new Map<Id, Account> ([Select id from Account where id in :vendorIds]); List<String> picklistInvoiceLocations = getPickListValuesIntoList('Invoice_Header__c', 'Location__c'); List<String> picklistInvoiceStatuses = getPickListValuesIntoList('Invoice_Header__c', 'Invoicing_Status__c'); List<String> picklistInvoiceRemitTos = getPickListValuesIntoList('Invoice_Header__c', 'Remit_to_Bank__c'); List<String> picklistInvoiceDetailLocations = getPickListValuesIntoList('Invoice_Detail__c', 'Location__c'); List<String> picklistVendorBillLocations = getPickListValuesIntoList('Vendor_Bill__c', 'Location__c'); //List<String> picklistVendorBillTerms = getPickListValuesIntoList('Vendor_Bill__c', 'Terms__c'); List<String> picklistVendorBillDetailLocations = getPickListValuesIntoList('Vendor_Bill_Detail__c', 'Location__c'); outputMap.put('CustomerIds', new List<Id> (customerIdMap.keySet())); outputMap.put('GLAccountIds', new List<Id> (glAccountIdMap.keySet())); outputMap.put('ItemIds', new List<Id> (itemIdMap.keySet())); outputMap.put('VendorIds', new List<Id> (vendorIdMap.keySet())); outputMap.put('InvoiceLocations', picklistInvoiceLocations); outputMap.put('InvoiceStatuses', picklistInvoiceStatuses); outputMap.put('InvoiceRemitTos', picklistInvoiceRemitTos); outputMap.put('InvoiceDetailLocations', picklistInvoiceDetailLocations); outputMap.put('VendorBillLocations', picklistVendorBillLocations); //outputMap.put('VendorBillTerms', picklistVendorBillTerms); outputMap.put('VendorBillDetailLocations', picklistVendorBillDetailLocations); System.debug(outputMap); System.debug(glAccountIdMap); return outputMap; } public static List<String> getPickListValuesIntoList(String sobjectname, String fieldapiName) { List<String> pickListValuesList = new List<String> (); List<Schema.PicklistEntry> ple = Schema.getGlobalDescribe().get(sobjectname).getDescribe().fields.getMap().get(fieldApiName).getDescribe().getPicklistValues(); for (Schema.PicklistEntry pickListVal : ple) { pickListValuesList.add(pickListVal.getLabel()); } return pickListValuesList; } @RemoteAction public static Map<String, Object> loadData(string invoices, string invoiceDetails, string vendorBills, string vendorBillDetails) { System.debug('remoting loadData'); Map<String, Object> retMap = new Map<String, Object> (); retMap.put('errors', new Map<String, Object> ()); retMap.put('records', new Map<String, List<recordStruct>> ()); List<recordStruct> retInvoices = new List<recordStruct> (); List<recordStruct> retInvoiceDetails = new List<recordStruct> (); List<recordStruct> retVendorBills = new List<recordStruct> (); List<recordStruct> retVendorBillDetails = new List<recordStruct> (); List<invoiceStruct> invoiceList; List<invoiceDetailStruct> detailList; List<vendorBillStruct> billList; List<vendorBillDetailStruct> billDetailList; try { invoiceList = parseInvoiceHeaders(invoices); detailList = parseInvoiceDetails(invoiceDetails); billList = parseVendorBills(vendorBills); billDetailList = parseVendorBillDetails(vendorBillDetails); } catch(Exception e) { ((Map<String, Object>) retMap.get('errors')).put('Parse', e.getMessage()); return retMap; } Set<String> accountIds = new Set<String> (); for (invoiceStruct inv : invoiceList) { accountIds.add(inv.record.Customer_ID__c); } Map<Id, Account> accountAddresses = new Map<Id, Account> ([select Id, Name, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, ShippingCountry, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, Phone, Phone__c, Phone_2__c, Email__c, Fax from Account where id in :accountIds]); Savepoint sp = Database.setSavepoint(); try { // Invoices List<Invoice_Header__c> newInvoices = new List<Invoice_Header__c> (); for (invoiceStruct inv : invoiceList) { Invoice_Header__c record = inv.record; Account acct = accountAddresses.get(record.Customer_ID__c); record.Billing_Addressee__c = acct.Name; record.Billing_Line_1__c = acct.BillingStreet; record.Billing_City__c = acct.BillingCity; record.Billing_State__c = acct.BillingState; record.Billing_Zip__c = acct.BillingPostalCode; record.Billing_Country__c = acct.BillingCountry; record.Shipping_Addressee__c = acct.Name; record.Shipping_Line_1__c = acct.ShippingStreet; record.Shipping_City__c = acct.ShippingCity; record.Shipping_State__c = acct.ShippingState; record.Shipping_Zip__c = acct.ShippingPostalCode; record.Shipping_Country__c = acct.ShippingCountry; record.Fax__c = acct.Fax; record.Billing_Phone__c = acct.Phone__c; record.Shipping_Phone__c = acct.Phone_2__c; record.Email__c = acct.Email__c; newInvoices.add(inv.record); } insert newInvoices; Map<String, Invoice_Header__c> invoiceMap = new Map<String, Invoice_Header__c> (); for (invoiceStruct inv : invoiceList) { invoiceMap.put(inv.tmpId, inv.record); recordStruct r = new recordStruct(); r.tmpId = inv.tmpId; r.Id = inv.record.Id; retInvoices.add(r); } // Vendor Bills List<Vendor_Bill__c> newVendorBills = new List<Vendor_Bill__c> (); for (vendorBillStruct bill : billList) { newVendorBills.add(bill.record); } insert newVendorBills; Map<String, Vendor_Bill__c> vendorBillMap = new Map<String, Vendor_Bill__c> (); List<Vendor_Payment__c> vendorPayments = new List<Vendor_Payment__c>(); for (vendorBillStruct bill : billList) { vendorBillMap.put(bill.tmpId, bill.record); if(bill.paymentRecord.Payment_Date__c != null){ bill.paymentRecord.Vendor_Bill_Id__c = bill.record.Id; vendorPayments.add(bill.paymentRecord); } recordStruct r = new recordStruct(); r.tmpId = bill.tmpId; r.Id = bill.record.Id; retVendorBills.add(r); } if(vendorPayments.size() > 0) insert vendorPayments; List<Vendor_Bill_Detail__c> newVendorBillDetails = new List<Vendor_Bill_Detail__c> (); for (vendorBillDetailStruct billDetail : billDetailList) { billDetail.record.Vendor_Bill_Id__c = vendorBillMap.get(billDetail.tmpIdVendorBill).Id; billDetail.record.Customer_Id__c = vendorBillMap.get(billDetail.tmpIdVendorBill).Customer__c; newVendorBillDetails.add(billDetail.record); } insert newVendorBillDetails; for (vendorBillDetailStruct billDetail : billDetailList) { recordStruct r = new recordStruct(); r.tmpId = billDetail.tmpId; r.Id = billDetail.record.Id; retVendorBillDetails.add(r); } List<Invoice_Detail__c> newInvoiceDetails = new List<Invoice_Detail__c> (); for (invoiceDetailStruct invDetail : detailList) { invDetail.record.Vendor_Bill_Id__c = vendorBillMap.get(invDetail.tmpIdVendorBill).Id; invDetail.record.Invoice_Header__c = invoiceMap.get(invDetail.tmpIdInvoice).Id; newInvoiceDetails.add(invDetail.record); } insert newInvoiceDetails; for (invoiceDetailStruct invDetail : detailList) { recordStruct r = new recordStruct(); r.tmpId = invDetail.tmpId; r.Id = invDetail.record.Id; retInvoiceDetails.add(r); } } catch(Exception e) { database.rollback(sp); ((Map<String, Object>) retMap.get('errors')).put('Insert: ', e.getMessage()); return retMap; } ((Map<String, Object>) retMap.get('records')).put('Invoices', retInvoices); ((Map<String, Object>) retMap.get('records')).put('Invoice Details', retInvoiceDetails); ((Map<String, Object>) retMap.get('records')).put('Vendor Bills', retVendorBills); ((Map<String, Object>) retMap.get('records')).put('Vendor Bill Details', retVendorBillDetails); return retMap; } public static List<invoiceStruct> parseInvoiceHeaders(string invoices) { List<invoiceStruct> invoiceList = new List<invoiceStruct> (); List<Object> invoiceJSON = (List<Object>) JSON.deserializeUntyped(invoices); Id rt = Schema.SObjectType.Invoice_Header__c.RecordTypeInfosByName.get('Treasury').RecordTypeId; for (Object obj : invoiceJSON) { Invoice_Header__c row = new Invoice_Header__c(); row.RecordTypeId = rt; Map<String, Object> raw = (Map<String, Object>) obj; string tmpId = (string) ((Map<String, Object>) raw.get('tmpId')).get('value'); // Lookups row.Customer_ID__c = stringId(raw, 'Customer_ID__c'); row.GL_Account_Id__c = stringId(raw, 'GL_Account_Id__c'); row.Location__c = (string) ((Map<String, Object>) raw.get('Location__c')).get('value'); row.Terms__c = (string) ((Map<String, Object>) raw.get('Terms__c')).get('value'); row.Invoicing_Status__c = (string) ((Map<String, Object>) raw.get('Invoicing_Status__c')).get('value'); row.Remit_to_Bank__c = (string) ((Map<String, Object>) raw.get('Remit_to_Bank__c')).get('value'); row.Customer_PO__c = (string) ((Map<String, Object>) raw.get('Customer_PO__c')).get('value'); row.Invoice_Period__c = (string) ((Map<String, Object>) raw.get('Invoice_Period__c')).get('value'); row.Memo__c = (string) ((Map<String, Object>) raw.get('Memo__c')).get('value'); //string dueDate = (string) ((Map<String, Object>) raw.get('Due_Date__c')).get('value'); string coverageDate = (string) ((Map<String, Object>) raw.get('Coverage_Month__c')).get('value'); string documentDate = (string) ((Map<String, Object>) raw.get('Document_Date__c')).get('value'); //row.Due_Date__c = dueDate == '' ? null : Date.parse(dueDate); row.Coverage_Month__c = coverageDate == '' ? null : Date.parse(coverageDate); row.Document_Date__c = documentDate == '' ? null : Date.parse(documentDate); invoiceStruct struct = new invoiceStruct(); struct.tmpId = tmpId; struct.record = row; invoiceList.add(struct); } return invoiceList; } public static List<invoiceDetailStruct> parseInvoiceDetails(string invoiceDetails) { List<invoiceDetailStruct> invoiceDetailList = new List<invoiceDetailStruct> (); List<Object> detailJSON = (List<Object>) JSON.deserializeUntyped(invoiceDetails); Id rt = Schema.SObjectType.Invoice_Detail__c.RecordTypeInfosByName.get('Treasury').RecordTypeId; for (Object obj : detailJSON) { Invoice_Detail__c row = new Invoice_Detail__c(); row.RecordTypeId = rt; Map<String, Object> raw = (Map<String, Object>) obj; string tmpId = (string) ((Map<String, Object>) raw.get('tmpId')).get('value'); string invoiceId = (string) ((Map<String, Object>) raw.get('Invoice_Header__c')).get('value'); string vendorBillId = (string) ((Map<String, Object>) raw.get('Vendor_Bill_Id__c')).get('value'); // Lookups row.Item_Id__c = stringId(raw, 'Item_Id__c'); row.Location__c = (string) ((Map<String, Object>) raw.get('Location__c')).get('value'); row.Item_Description__c = (string) ((Map<String, Object>) raw.get('Item_Description__c')).get('value'); row.Policy_Number__c = (string) ((Map<String, Object>) raw.get('Policy_Number__c')).get('value'); row.Memo__c = (string) ((Map<String, Object>) raw.get('Memo__c')).get('value'); row.Ordinal__c = Decimal.valueOf((string) ((Map<String, Object>) raw.get('Ordinal__c')).get('value')); row.Unit_Price__c = Decimal.valueOf((string) ((Map<String, Object>) raw.get('Unit_Price__c')).get('value')); //row.Quantity__c = Decimal.valueOf((String) ((Map<String, Object>) raw.get('Quantity__c')).get('value')); //row.Vendor_Bill_Total__c = Decimal.valueOf((String) ((Map<String, Object>) raw.get('Vendor_Bill_Total__c')).get('value')); //row.Doc_Number__c = (string) ((Map<String, Object>) raw.get('Doc_Number__c')).get('value'); row.Ignore_Vendor_Bill_Amount__c = boolean.valueOf((string) ((Map<String, Object>) raw.get('Ignore_Vendor_Bill_Amount__c')).get('value')); row.Is_Applied__c = boolean.valueOf((string) ((Map<String, Object>) raw.get('Is_Applied__c')).get('value')); invoiceDetailStruct struct = new invoiceDetailStruct(); struct.tmpId = tmpId; struct.tmpIdInvoice = invoiceId; struct.tmpIdVendorBill = vendorBillId; struct.record = row; invoiceDetailList.add(struct); } return invoiceDetailList; } public static List<vendorBillStruct> parseVendorBills(string vendorBills) { List<vendorBillStruct> vendorBillList = new List<vendorBillStruct> (); List<Object> vendorBillJSON = (List<Object>) JSON.deserializeUntyped(vendorBills); //Id rt = Schema.SObjectType.Vendor_Bill__c.RecordTypeInfosByName.get('Treasury').RecordTypeId; for (Object obj : vendorBillJSON) { Vendor_Bill__c row = new Vendor_Bill__c(); Map<String, Object> raw = (Map<String, Object>) obj; string tmpId = (string) ((Map<String, Object>) raw.get('tmpId')).get('value'); // Lookups row.Customer__c = stringId(raw, 'Customer__c'); row.GL_Account_Id__c = stringId(raw, 'GL_Account_Id__c'); row.Vendor_Id__c = stringId(raw, 'Vendor_Id__c'); row.Location__c = (string) ((Map<String, Object>) raw.get('Location__c')).get('value'); //row.Terms__c = (string) ((Map<String, Object>) raw.get('Terms__c')).get('value'); row.Memo__c = (string) ((Map<String, Object>) raw.get('Memo__c')).get('value'); row.Vendor_Description__c = (string) ((Map<String, Object>) raw.get('Vendor_Description__c')).get('value'); row.Ordinal__c = Decimal.valueOf((string) ((Map<String, Object>) raw.get('Ordinal__c')).get('value')); //row.Tran_Id__c = (string) ((Map<String, Object>) raw.get('Tran_Id__c')).get('value'); row.Pay_By_ACH__c = Boolean.valueOf((string) ((Map<String, Object>) raw.get('Pay_By_ACH__c')).get('value')); //string dueDate = (string) ((Map<String, Object>) raw.get('Due_Date__c')).get('value'); //string transDate = (string) ((Map<String, Object>) raw.get('Trans_Date__c')).get('value'); string coverageFromDate = (string) ((Map<String, Object>) raw.get('Coverage_From__c')).get('value'); string coverageToDate = (string) ((Map<String, Object>) raw.get('Coverage_To__c')).get('value'); //row.Due_Date__c = dueDate == '' ? null : Date.parse(dueDate); //row.Trans_Date__c = transDate == '' ? null : Date.parse(transDate); row.Coverage_From__c = coverageFromDate == '' ? null : Date.parse(coverageFromDate); row.Coverage_To__c = coverageToDate == '' ? null : Date.parse(coverageToDate); string paymentDate = (string) ((Map<String, Object>) raw.get('Payment_Date__c')).get('value'); vendorBillStruct struct = new vendorBillStruct(); struct.tmpId = tmpId; struct.record = row; struct.paymentRecord = new Vendor_Payment__c(); struct.paymentRecord.Payment_Date__c = paymentDate == '' ? null : Date.parse(paymentDate); vendorBillList.add(struct); } return vendorBillList; } public static List<vendorBillDetailStruct> parseVendorBillDetails(string vendorBillDetails) { List<vendorBillDetailStruct> vendorBillDetailList = new List<vendorBillDetailStruct> (); List<Object> vendorBillDetailJSON = (List<Object>) JSON.deserializeUntyped(vendorBillDetails); for (Object obj : vendorBillDetailJSON) { Vendor_Bill_Detail__c row = new Vendor_Bill_Detail__c(); Map<String, Object> raw = (Map<String, Object>) obj; string tmpId = (string) ((Map<String, Object>) raw.get('tmpId')).get('value'); string vendorBillId = (string) ((Map<String, Object>) raw.get('Vendor_Bill_Id__c')).get('value'); // Lookups row.Item_Id__c = stringId(raw, 'Item_Id__c'); row.Location__c = (string) ((Map<String, Object>) raw.get('Location__c')).get('value'); row.Is_Billable__c = Boolean.valueOf((string) ((Map<String, Object>) raw.get('Is_Billable__c')).get('value')); row.Description__c = (string) ((Map<String, Object>) raw.get('Description__c')).get('value'); row.Policy_Number__c = (string) ((Map<String, Object>) raw.get('Policy_Number__c')).get('value'); row.Cost_Center__c = (string) ((Map<String, Object>) raw.get('Cost_Center__c')).get('value'); row.Invoice_Number__c = (string) ((Map<String, Object>) raw.get('Invoice_Number__c')).get('value'); row.Rate__c = Decimal.valueOf((string) ((Map<String, Object>) raw.get('Rate__c')).get('value')); row.Quantity__c = Decimal.valueOf((string) ((Map<String, Object>) raw.get('Quantity__c')).get('value')); //if (!vendorBillDetailMap.keySet().contains(tmpId)) { vendorBillDetailMap.add(tmpId, new List<Vendor_Bill_Detail__c> ()); } //vendorBillDetailMap.get(vendorBillId).add(row); vendorBillDetailStruct struct = new vendorBillDetailStruct(); struct.tmpId = tmpId; struct.tmpIdVendorBill = vendorBillId; struct.record = row; vendorBillDetailList.add(struct); } return vendorBillDetailList; } public static string stringId(Map<String, Object> raw, string fieldName) { string itemId = (string) ((Map<String, Object>) raw.get(fieldName)).get('value'); if (itemId.length() == 15 || itemId.length() == 18) { return itemId; } return null; } class invoiceStruct { public Invoice_Header__c record; public string tmpId; } class invoiceDetailStruct { public Invoice_Detail__c record; public string tmpId; public string tmpIdInvoice; public string tmpIdVendorBill; } class vendorBillStruct { public Vendor_Bill__c record; public Vendor_Payment__c paymentRecord; public string tmpId; } class vendorBillDetailStruct { public Vendor_Bill_Detail__c record; public string tmpId; public string tmpIdVendorBill; } class recordStruct { public string tmpId; public Id Id; } }