Fixed issue #137

[kakaye-mkubwa]

Updated the naming of regions and provinces to match ISO-3166 standards for Spain.

[levogirar]

@kakaye-mkubwa Thank you for the PR, few things:

1. Could we show the company with the ID 'ES' for Spain when we did not detect a postal code?
2. For the postal code: instead of assigning 1 to ES-VI, can we do 01 to ES-VI, instead? In ES-VI, their postal code starts with 01 (postal code example: 01001)

This is from me. Perhaps @Nils85 has more comments.

[Nils85]

2. For the postal code: instead of assigning 1 to ES-VI, can we do 01 to ES-VI, instead? In ES-VI, their postal code starts with 01 (postal code example: 01001)

@levogirar Are you talking about a specific part in the source code or this ID (1 for example) is explicitly visible somewhere in the user interface?

[levogirar]

@Nils85 I planned to use the first two digits of the postal code to assign the province to a company. I feared that a postal code like 01345 won't be assigned to the right province. The code itself shouldn't be displayed anywhere.

[Nils85]

@levogirar So are you talking about the file Models/DataAccess/default data/postalcode.tsv when you said "assigning 1 to ES-VI"?

[levogirar]

@Nils85 yes

[kakaye-mkubwa]

@kakaye-mkubwa Thank you for the PR, few things:

1. Could we show the company with the ID 'ES' for Spain when we did not detect a postal code?

2. For the postal code: instead of assigning 1 to ES-VI, can we do 01 to ES-VI, instead? In ES-VI, their postal code starts with 01 (postal code example: 01001)

This is from me. Perhaps @Nils85 has more comments.

As for (1) the way we can sort it out is by creating a record for holding data in the case we did not find a postal code. Since just assigning ES will lead to issues with foreign key checks.

As for (2) lemme change the structure of the tsv. As for the table structure, I have set the postal code column to varchar so as to support values with leading zeros.

[kakaye-mkubwa]

@kakaye-mkubwa Thank you for the PR, few things:

1. Could we show the company with the ID 'ES' for Spain when we did not detect a postal code?

2. For the postal code: instead of assigning 1 to ES-VI, can we do 01 to ES-VI, instead? In ES-VI, their postal code starts with 01 (postal code example: 01001)

This is from me. Perhaps @Nils85 has more comments.

As for (1) the way we can sort it out is by creating a record for holding data in the case we did not find a postal code. Since just assigning ES will lead to issues with foreign key checks.

As for (2) lemme change the structure of the tsv. As for the table structure, I have set the postal code column to varchar so as to support values with leading zeros.

Made a commit that sorts out the second (2) concern that was raised by @levogirar and a fix for the first one (1), since 'ES' is displayed but not stored on the database due to foreign key checks.

[levogirar]

@kakaye-mkubwa I have a question about the CompanyData.php. In it, we have two variables $region and $postalCode. These two are not connected, no? If I change the $postalCode of a company, it will not change the $region, correct?

What do you think if we simplify the CompanyData.php? Let's say we remove $region and we use $postalCode to filter by region and to display by province? For example: we assign Wuwana with $postalCode = 28001. In the view, we will see that Wuwana belongs to the province of Madrid and in the region Community of Madrid. The scraper will detect the postal code and store it in the database without having to assign a $region.

What do you think? is it a good idea?

[kakaye-mkubwa]

@kakaye-mkubwa I have a question about the CompanyData.php. In it, we have two variables $region and $postalCode. These two are not connected, no? If I change the $postalCode of a company, it will not change the $region, correct?

What do you think if we simplify the CompanyData.php? Let's say we remove $region and we use $postalCode to filter by region and to display by province? For example: we assign Wuwana with $postalCode = 28001. In the view, we will see that Wuwana belongs to the province of Madrid and in the region Community of Madrid. The scraper will detect the postal code and store it in the database without having to assign a $region.

What do you think? is it a good idea?

The two are linked, that is, $region and $postalCode. This is how I have gone about it. Since the postal code is scrapped from the site, I have set its value in Scraper/Scraper.php. As for the region variable, it is processed in Company.php. Check out this method is Company.php public function fetchProvinceID($postalCode){

return $this->fetchQuery("select Province.ProvinceID from Province inner join PostalCode on PostalCode.ProvinceID = Province.ProvinceID where PostalCode.Code ='".$postalCode."'");

`}`

This method is called before an insert operation and should be called before an update operation(noticed I did not capture the update part). Check the insert method in `Company.php

public function insert(CompanyData $company) { $otherTags = implode(self::VALUES_DELIMITER, $company->otherTags); $i = count($company->otherTags); $company->region = empty($company->postalCode) ? $this->fetchProvinceID(null) : $this->fetchProvinceID(substr($company->postalCode,0,2)); ... I chose to go about it this way rather than the method you mentioned, since this involves less refactoring of the code. You can test this feature using a website that has a postal code present.

[levogirar]

@kakaye-mkubwa Noted, thanks.

[Nils85]

Before merging this branch on the server it is necessary to create new tables and columns in the MySQL database:

create table Region (
            RegionID varchar(6),
            EN varchar(100),
            ES varchar(100),
            FR varchar(100),
            ZH varchar(100),
            primary key(RegionID));

create table Province (
            ProvinceID varchar(6),
            EN varchar(100),
            ES varchar(100),
            FR varchar(100),
            ZH varchar(100),
            RegionID varchar(6),
            primary key(ProvinceID),
            foreign key(RegionID) references Region(RegionID));

create table PostalCode(
            Code varchar(2) not null primary key,
            ProvinceID varchar(6),
            foreign key(ProvinceID) references Province(ProvinceID));

drop table Location;
create table Location (
            CountryCode char(2) not null,
            ProvinceID varchar(6),
            foreign key(ProvinceID) references Province(ProvinceID));

alter table Company change LocationID ProvinceID varchar(6) not null;
update Company set ProvinceID='ES-M';
alter table Company add PostalCode varchar(255);
alter table Company add foreign key(ProvinceID) references Province(ProvinceID);

Then insert data into these new tables (see TSV files) @kakaye-mkubwa Can you tell me if I forgot something else to change in the DB?

[kakaye-mkubwa]

@Nils85 I have highlighted all the changes that are related to the database.