What we need: A MySQL database with a PHP interface to match CDL drivers with local jobs within a predetermined radius of their zip code.
Driver interface fields
- Drive Name
- Distance willing to drive
- City
- State
- Driver Zip Code
- Zip code Array (need to be able to paste in a comma delimited string of zip codes. If zip codes could be automatically populated based on the Drive Zip Code and Distance willing to drive, it's be great, but if not, the Zip Code Array can be obtained through [login to view URL])
- Excluded Carriers
- Notes
Job Interface Fields
- Zip Code of Job
- Carrier
- Job Number
- Estimated Salary
- Description
There should be a Dashboard that shows which Drivers match what Jobs. Basically, the tool will need to check the Driver Zip Code Array and if one of the zip codes matches a Zip Code of a Job, it will display their Driver information along with the details of the job on the Dashboard. There needs to be the ability to add & remove jobs and drivers from the database. If a Carrier is listed in the Driver's "Excluded Carriers", it should not show up in the potential matches for that driver. Also, if a Driver has turned a position down, it needs to be marked off so that it doesn't show up again for that driver as a potential match.
There are 4 carriers that cannot be input into the Job Interface, they have to be manually searched on the carrier's website. There will need to be a place in the Driver Interface to input the last date each of the 4 carriers was searched for each driver.
I have attached an example Excel spreadsheet that does everything needed. The full file is too large to function effectively, hence why we want to switch to a PHP/MySQL database.
If it helps, here are the locations in the example Excel spreadsheet
Formula location: A2
=IF(ISNUMBER(LARGE(S2:AAC2,1)),CONCATENATE(IF(ISNUMBER(LARGE(S2:AAC2,5))=TRUE,VLOOKUP(LARGE(S2:AAC2,5),Jobs!A:E,5,0),""),IF(ISNUMBER(LARGE(S2:AAC2,4))=TRUE,CONCATENATE(VLOOKUP(LARGE(S2:AAC2,4),Jobs!A:E,5,0)," & "),""),IF(ISNUMBER(LARGE(S2:AAC2,3))=TRUE,CONCATENATE(VLOOKUP(LARGE(S2:AAC2,3),Jobs!A:E,5,0)," & "),""),IF(ISNUMBER(LARGE(S2:AAC2,2))=TRUE,CONCATENATE(VLOOKUP(LARGE(S2:AAC2,2),Jobs!A:E,5,0)," & "),""),IF(ISNUMBER(LARGE(S2:AAC2,1))=TRUE,CONCATENATE(VLOOKUP(LARGE(S2:AAC2,1),Jobs!A:E,5,0)," "))),"")
Formula location: S2
=IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$K3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$L3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$M3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$N3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$O3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$P3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$Q3,"",IF(IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,5,0),"")=$R3,"",IFNA(VLOOKUP(MATCH(S3,Jobs!$B:$B,0),Jobs!$A:$E,1,0),"")))))))))