akhilrex/hammond

Feature Request: GasBuddy import

camden-bock opened this issue · 2 comments

camden-bock commented

GasBuddy offers a fuel log tied to transactions from GasBuddy's WEX payment card. GasBuddy's log exports as a CSV with many of the same fields that hammond relies on.

Because GasBuddy provides integration with the fuel purchase through Wex, it is convenient to collect fuel log data through GasBuddy. It would be really great to be able to import this in batches to Hammond!

Column Headers:

  • Date (UTC): YYYY-MM-DD HH:MM:SS
  • Location: string, human-friendly name
  • Station Link: string, url
  • Address: string, street address
  • City: string, municipality
  • State: string, 2 char state abbreviation
  • Country: string, 2 char country abbreviation
  • Total Cost: number, up to 2 decimal points
  • Currency: string, 3 digit abreviation (USD)
  • Fuel Type: string, (e.g. Regular, Midgrade, Diesel)
  • Quantity: number, up to 3 decimal points
  • Unit: string, (e.g. gallons)
  • Vehicle: vehicle nickname
  • Unit price: number, currency per quantity unit
  • Odometer: number, mileage
  • Fuel Economy: string: number (calcualted fuel economy, OR 'missingPrevious', 'noFillPrevious')
  • Fuel Economy Unit: string, abbreviation (e.
  • Fillup: string, YES or NO (complete fill)

ExampleGasBuddyExport.csv
g., MPG)

This looks like it would only require small modificatinos from the Fuelly import function, as well as a new view.

func GasBuddyImport(content []byte, userId string) []string {
   stream := bytes.New(content)
   reader := csv.NewReader(stream)
   records, err := reader.ReadAll()

   var errors []string
   if err != nil {
   	errors = append(errors, err.Error())
   	return errors
   }

   vehicles, err := GetUserVehicles(userId)
   if err != nil {
   	errors = append(errors, err.Error())
   	return errors
   }
   user, err := GetUserById(userId)

   if err != nil {
   	errors = append(errors, err.Error())
   	return errors
   }

   var vehicleMap map[string]db.Vehicle = make(map[string]db.Vehicle)
   for _, vehicle := range *vehicles {
   	vehicleMap[vehicle.Nickname] = vehicle
   }

   var fillups []db.Fillup
   var expenses []db.Expense
   # layout YYYY-MM-DD HH:MM:SS
   layout := "2006-01-02 15:04:05"

   for index, record := range records {
   	if index == 0 {
   		continue
   	}

   	var vehicle db.Vehicle
   	var ok bool
   	// vehicle appears in the 13th column
   	if vehicle, ok = vehicleMap[record[12]]; !ok {
   		errors = append(errors, "Found an unmapped vehicle entry at row "+strconv.Itoa(index+1))
   	}
   	// date appears in column 1, no alt layout
   	dateStr := record[0]
   	date, err := time.Parse(layout, dateStr)
   	if err != nil {
   		errors = append(errors, "Found an invalid date/time at row "+strconv.Itoa(index+1))
   	}
   	// total cost appears in column 8; Currency can be pulled from column 9
   	totalCostStr := record[7]
   	totalCost64, err := strconv.ParseFloat(totalCostStr, 32)
   	if err != nil {
   		errors = append(errors, "Found an invalid total cost at row "+strconv.Itoa(index+1))
   	}

   	totalCost := float32(totalCost64)
   	// odometer reading is in column 15; not sure what user.Currency does here. odometer reading is simply presented as a number
   	odoStr := record[14]
   	odoreading, err := strconv.Atoi(odoStr)
   	if err != nil {
   		errors = append(errors, "Found an invalid odo reading at row "+strconv.Itoa(index+1))
   	}
   	// location could be pulled from a short name (e.g. CITGO) from column 2 or a long name.
   	
   	location := record[1] + " at " + record[2] + ", " + record[3] + ", " + record[4] + ", " + record[5]

   	//Create Fillup: only fuel records, no service records
   	// unit price in column 14; not sure what user.Currency does here.
   	rateStr := record[13]
   	ratet64, err := strconv.ParseFloat(rateStr, 32)
   	if err != nil {
   		errors = append(errors, "Found an invalid cost per gallon at row "+strconv.Itoa(index+1))
   	}
   	rate := float32(ratet64)
   	//quantity is in column 11
   	quantity64, err := strconv.ParseFloat(record[10], 32)
   	if err != nil {
   		errors = append(errors, "Found an invalid quantity at row "+strconv.Itoa(index+1))
   	}
   	quantity := float32(quantity64)
   	//pull station link as notes, from column 3
   	notes := record[2]
   	
   	//fillup in column 18
   	isTankFull := record[17] == "Yes"
   	
   	fal := false
   	//this entry does not include Fuel Type (record[9]), which could map to Hammond's Fuel Subtype. However, this would not capture differences in Diesel (e.g., in NorthEast US, we can get higher grade Diesel sourced from New Brunswick - all listed under "diesel" in gasbuddy).
   	fillups = append(fillups, db.Fillup{
   		VehicleID:       vehicle.ID,
   		FuelUnit:        vehicle.FuelUnit, //this could be pulled from record[11]
   		FuelQuantity:    quantity,
   		PerUnitPrice:    rate,
   		TotalAmount:     totalCost,
   		OdoReading:      odoreading,
   		IsTankFull:      &isTankFull,
   		Comments:        notes,
   		FillingStation:  location,
   		HasMissedFillup: &fal,
   		UserID:          userId,
   		Date:            date,
   		Currency:        user.Currency, //this could be pulled from record[8] 
   		DistanceUnit:    user.DistanceUnit, //odometer units must be mapped correctly between fuely profile and gasbuddy profile
   		Source:          "GasBuddy",
   	})

   }
   if len(errors) != 0 {
   	return errors
   }

   tx := db.DB.Begin()
   defer func() {
   	if r := recover(); r != nil {
   		tx.Rollback()
   	}
   }()
   if err := tx.Error; err != nil {
   	errors = append(errors, err.Error())
   	return errors
   }
   if err := tx.Create(&fillups).Error; err != nil {
   	tx.Rollback()
   	errors = append(errors, err.Error())
   	return errors
   }
   if err := tx.Create(&expenses).Error; err != nil {
   	tx.Rollback()
   	errors = append(errors, err.Error())
   	return errors
   }
   err = tx.Commit().Error
   if err != nil {
   	errors = append(errors, err.Error())
   }
   return errors
}

I would imagine that similar instructions would apply to gasbuddy.

      <div class="column">
        <p class="subtitle"> Steps to import data from GasBuddy</p>
        <ol>
          <li
            >Export your data from GasBuddy in the CSV format. Steps to do that can be found
            <a href="https://help.gasbuddy.com/hc/en-us/articles/9224647595543-Export-Fuel-Logs">here</a>.</li
          >
          <li>Make sure that you have already created the vehicles in Hammond platform.</li>
          <li>Make sure that the Vehicle nickname in Hammond is exactly the same as the name on GasBuddy CSV (Check Vehicle column) or the import will not work.</li>
          <li
            >Make sure that the <u>Currency</u> and <u>Distance Unit</u> are set correctly in Hammond. Import will not autodetect Currency from the
            CSV but use the one set for the user.</li>
          <li>Similiarly, make sure that the <u>Fuel Unit</u> and <u>Fuel Type</u> are correctly set in the Vehicle.</li>
          <li>Once you have checked all these points,just import the CSV below.</li>
          <li><b>Make sure that you do not import the file again and that will create repeat entries.</b></li>
        </ol>
      </div>

GasBuddy has the appropriate currency and fuel unit data if #103 is implemented.

I am not familiar with Go, but I will try to test this in a fork to see if I can get it working.

camden-bock commented

I made an initial attempt at an implementation.

For both Fuelly and GasBuddy uploads, I have run into #106. This includes when using sample data from #11 sample2. I wasn't able to find any special characters to remove to help resolve this (as suggested in #106). It looks like the content upload is a text/csv, where multipart/form-data is expected?

Response:

{"ErrorString":"request Content-Type isn't multipart/form-data"}

Request:

XHRPOSThttp://localhost:8080/api/import/fuelly
[HTTP/1.1 422 Unprocessable Entity 10ms]

-----------------------------412623039826418261423771155141
Content-Disposition: form-data; name="file"; filename="Fuelly.Export.2.csv"
Content-Type: text/csv
Type,MPG,Date,Time,Vehicle,Odometer,Filled Up,Cost/Gallon,Gallons,Total Cost,Octane,Gas Brand,Location,Tags,Payment Type,Tire Pressure,Notes,Services
Gas,0,2020-01-17,3:03 PM,2010 Honda Element,15021,Full,$3.559,9.781,$34.81,Premium [Octane: 92],,,,,0,,
Gas,22.9,2020-01-21,10:12 AM,2010 Honda Element,15183,Full,$4.099,7.088,$29.05,Premium [Octane: 92],Chevron,chevron,,,0,,
Gas,21.4,2020-01-23,1:06 PM,2010 Honda Element,15403,Full,$3.579,10.298,$36.86,Premium [Octane: 92],Chevron,,,MasterCard,0,,
[...]
-----------------------------412623039826418261423771155141-
akhilrex commented