---
# System prepended metadata

title: Received Date

---

# Received Date
Update received date to applications.

No received date column set in the raw data source. Column K in question is labelled as `Licence_Date_Issues` - I made the assumption here that this was the `issue_date` on a `licence` record.
I didn't want to make a bad assumption for a received date based on these columns, so as the received date is a required field, I set this manually to be all the same.
__We can batch update receieved date to be application date__

# Active Licences
All licences came through with an issue date.

## What can we do?
Set active where application date does not equal issue date
SELECT distinct(licence_number), id FROM licences WHERE application_date = issue_date;

# Licence Area is wrong on licences
`Firm Details file`

## What can we do?

>Loop around the file and pull out data to check for the unique record and batch update these to the correct one, or create a new one on the fly.<br/>
>Will need to check all other licence files too in the same manner

## Premises name updates
Grab all premises where the temp licence number field doesn't have a comma and has a dtf location id
- loop around these
- take the temp licence number
- search licences with this licence number
- check if the premise id on the licence = the current premise in the loop
- if it doesn't update it.
- if it does - skip

Grab all premsises where the temp licence number field does have a comma and has a dtf location id
- loop around these
- then loop around the licence numbers on the premises
- take the temp licence number within the loop
- search licences with this licence number
- check if the premise id on the licence = the current premise in the loop
- if it doesn't update it.
- if it does - skip

# Apostrophes on premise names
- Need to cleans the "'" on the names. It's currently "''".

# Vehicle makes not showing
- Data is there, batch update needed
- `vehicle_licence_updates.sql`

# Specific tabs aren't showing in some cases
- Batch update to existing

# Vehicle Licences
- Batch update to add in the details for vehicles - done = 211,0
- Batch update licence area to applicant? contact_id = applicant_id?

# Licences not linked to premises
>Premises with unique contacts (clients) were taken as unique premises. Therefore multiple premises with the same name, take DTS Ltd for example below.

- [ ] We can batch update all licences for example to be the same premise with a placeholder owner (contact) against the premise and remove all duplicated premises as a result if needed.

![](https://i.imgur.com/XB9tQcd.png)

# DTF Locations not linked to premises
- [x] Run the [above fix](#Apostrophes-on-premise-names) for the apostrophes first.
- Temporarily remove all apostrophes from name on premise
-- Run the `premise_dtf_update.sql file` 
**Reason:**	`Apostrophe was stripped out on this update file mistakenly, and it also wasn't ran`.
- Insert all apostrophes back into the names after above has been run

# Vehicle Licences Updates

- [ ] `vehicle_licence_date_update.sql`

# Phone numbers / Mobile numbers
- Update any numbers that don't have a `0` at the start, then update the number to have a 0.
```
Get all numbers from contacts where number is not null
Check if digits is 10 and first digit is not a 0
Add a 0 and update
```

# Licence Number
- Update the licence reference to be the current licence number
- The licence number needs to be numeric only.
- Strip out the characters

# Animal welfare licences
- Need to get the licence activities from licence_activities file and run into the `animal_licence_animal_activity_category` table.
- Mapping needs provided from licence activity types to a CODE.

# Licence Activities
- Needs the licence id updated based on licence numnber
	-	Loop this incase of multiple. Check out the vehicles for example...
-	Note this;
`UPDATE licence_activities SET licence_id = (SELECT id FROM licences WHERE licence_number = licence_activities.licence_number ORDER BY id DESC LIMIT 1);`

### CO2 emissions
- Don't record this on a vehicle licence

# Gambling licences
- Batch updating from licence activities to gambling categories.
- Mapping provided to do this.
- - Only a few licences (6) or so.