Misc. PHP & PostgreSQL coding on existing website

Website/code location is at: http://173.203.197.133/dmp/

1. PREDIAL HISTORY:

A. Each time a clave number is fetched against the remote PREDIAL data source, any recognized changes need to be written as new/most current, and the previous PREDIAL information in our dB for that clave number needs to be written to the predial_history table. We obtain two main pieces of information from the remote PREDIAL source, and these are OWNER NAME and PROPERTY TAXES. OWNER NAME is just that, whereas PROPERTY TAXES contain a couple of tables, each with 2 or more columns and rows, with $\'s and %\'s and sum totals, as seen on this site, and at the remote PREDIAL website.
As seen in the existing predial_history table, you can see that tax information is being saved, but OWNER NAMES are not, so OWERN NAME needs to be added. For both OWNER NAME and PROPERTY TAXES, their FETCH dates need to be TIME/DATE stamped, and USER NAME needs to be associated. Here is an example of the data as it\'s currently written into the one single column cell of dB dmp and table predial_history and column conceptos: Rezago Predial de 2007 bim 1,2,3,4,5,6, base: 57093.17, tasa: 3.55/1000
When the above is translated to english, it reads like this;
Year: 2007
Bimonthly periods: 1,2,3,4,5,6
Property value/taxable amount: $57093.17 MXN
Rate: 3.55/1000
So, what is currently being written to only one column named conceptos, needs to instead be written to 4 columns named year, bimonthly_period, property_value, and rate. In the dB dmp and table predial_history and column amount, this is what is being written into it;
Recargos por el año 2010, tasa interes anual total: 21%
When the above is translated to english, it reads like this;
Surcharges for Year: 2010
Total annual interest rate: 21%
So, what is currently being written to only one column named predial_history, needs to instead be written to 2 columns named year and interest_rate. All of these these columns need to be associated together as they are related from the same fetch, and of course they are bound in common by a clave number. One clave number will end up with multiple/many of the above groups of fetched data from predial. In summary, all the fields in the predial table need to be added to the predial_history table, and vice versa, so individual PREDIAL fetches may be historically saved per every clave number.

B. Certain clave numbers, when viewed directly on the remote PREDIAL website, like; http://www.eloscabos.gob.mx/CATASTRO/ESTADO_DE_CUENTA.aspx?CLVCAT=402090002001 and when compared with what we have in our dB(search for clave 402090002001), can be recognized as missing either or both the OWNER NAME and PROPERTY TAX information. This means if you push the \'Fetch Data\' button for the above clave number on the dmp.php page, what will happen is the record will end up loosing it\'s OWNER NAME and PROPERTY TAXES. These types of records need to be flagged as being unique, because they may in fact NOT have an OWNER NAME, but they may in fact HAVE tax information.

C. PROPERTY TAXES information change more regularly then do OWNER NAMES. if either a new OWNER NAME or a NEW/CHANGED PROPERTY TAX is fetched, the previous PREDIAL info is moved from the \'predial\' table to the \'predial_history\' table, and the latest/newest PREDIAL fetch is written to the \'predial\' table. If a PREDIAL fetch does not produce any changes in OWNER NAME or PROPERTY TAXES, the current PREDIAL data is marked with as updated/no changes with TIME/DATE stamp, so we can denote how many times PREDIAL info has been fetched(and when) and has remained the same. In other words, we do not want to keep recording the same OWNER NAME and/or PROPERTY TAXES info to the predial_history table as individual historical records, but instead, ONLY if/when the data in the new fetch is different/changed... so the code needs to pay attention and check for this.

=====

2. FETCH & BATCH FETCH PREDIAL DATA:

A. Place \'Fetch Predial Data\' feature on both PREDIAL.PHP and DMP.PHP pages:

B. In the code, make sure any/all 18 digit clave numbers that are entered have a hyphen auto-placed after the 12th and before the 13th characters, e.g.- 401001000001-01AB01 becuase the remote PREDIAL website will fail if the 18 digit clave does not have the dash, e.g.- 40100100000101AB01 is bad and will fail when fetched against remote PREDIAL website.

=====

3. BATCH FETCH PREDIAL DATA AUTOMATICALLY VIA SCHEDULING:

A. Need to use/modify the existing cron script that auto-fetches PREDIAL data from the remote PREDIAL website and add a user interface on the CLAVES.PHP page for use with any/all of the clave lists. Need to be able to set clave groups to fetch every month, or every 2 months, or every 3 months, or every 6 months, or turn off entirely for any clave/group of clave numbers. We need a setting that allows us to enter a value for the number of max clave numbers to be allowed in any give clave group as it relates to a group being grouped for purposes of assigning them a certain day of the month to fetch remote PREDIAL website data. We will set the initial number to 3500, but if we find this number too high, we can then reduce it to 3000, or 2500, or whatever. Also, once a clave group is assigned a day of the month, no other group can use that day. The clave groups that are displayed on the 3 rows, need to have a visual denotation indicating they are scheduled for routine fetching on a certain/specific day, and should show the time/date stamp they were


B. By default, the individual clave groups within the 3 rows of clave number groupings will be assigned a \'day\' of the month to remote fetch data from the remote predial source. Any/all clave number groups with 3500 clave numbers or less will be auto-grouped together accordingly. In the first row, ALL clave number groups have LESS then 3500 claves, except groups 401, 402, and 408. The aforementioned groups will thus be ignored/not assigned a day of the month, and instead, we need to move to the second row and all groups with 3500 claves or less will thus be grouped like the ones in the first row were. In the second row right now(based on the current clave count in each group, which are subject to change), all clave groups qualify except the following clave groups 402002, 402088, 408002, and 408003, and these thus need to be expanded to the third row, because they contain more then 3500 claves each. In the future, as claves are added, and clave groups pass the 3500 count(or whatever the max number of claves is set to in the settings), that group needs to be decommissioned and the next row of clave groupings needs to be used for auto-scheduling of clave groups with less then 3500 claves.

=====

4. TRAMITES FETCH CODE MISSING ONE PIECE OF FETCH INFO:

A. In the Tramites fetch code(cron script), need to add \'Motivo\' column to tramites and tramites_history tables and start fetching that data/info, as that one bit of information was previously overlooked on the remote TRAMITES website.

=====

5. TRAMITES SEARCH/QUERY FILTER ADDITIONS:
A. On the Tramites page, need to add \'Process Type\' and \'Reason\' selections to the pull-down filter menu. They are as follows:
tramites
found in process_type column in tramites table:
PROCESS TYPE: 01 AVALUOS PERICIALES
PROCESS TYPE: 02 SUBDIVISION DE PREDIOS
PROCESS TYPE: 03 FUSION DE PREDIOS
PROCESS TYPE: 04 LOTIFICACION Y RELOTIFICACION
PROCESS TYPE: 05 REGIMEN DE PROPIEDAD EN CONDOMINIO
PROCESS TYPE: 06 CANCELACION DE DOCUMENTOS
PROCESS TYPE: 07 REGISTRO DE TITULOS O POSESION
PROCESS TYPE: 08 MANIFESTACION DE CONSTRUCCIONES
PROCESS TYPE: 09 MANIFESTACION DE ESCRITURAS
PROCESS TYPE: 10 CORRECCION DE MANIFESTACION
PROCESS TYPE: 11 ISABIS
PROCESS TYPE: 12 DESLINDES CATASTRALES
PROCESS TYPE: 13 CERTIFICACION DE DOCTOS.
PROCESS TYPE: 14 EXPEDICION DE COPIAS
PROCESS TYPE: 15 CARTA DE NO PROPIEDAD
PROCESS TYPE: 16 SOLICITUD DE ACLARACIONES
PROCESS TYPE: 18 PROTOCOLIZACION
PROCESS TYPE: 20 PUNTOS UTM
PROCESS TYPE: 21 GENERACION DE PLANOS
PROCESS TYPE: 22 ACTA ACLARATORIA
PROCESS TYPE: 23 PROTOCOLIZACION SUBDIVISION
PROCESS TYPE: 24 REPOSICION MANIFESTACION
PROCESS TYPE: 25 TITULOS CORETT/ RAN
PROCESS TYPE: 26 CSL-MANIFESTACION DE CONSTRUCCION
PROCESS TYPE: 29 CEDULA DE INFORMACION CATASTRAL (or 29 CEDULA CATASTRAL )

found in the reason column in the tramites table:
REASON: ADMINISTRACION
REASON: AVALUOS
REASON: CAPTURA
REASON: CARTOGRAFIA
REASON: JURIDICO
REASON: MANIFESTACIONES
REASON: OPERACIONES CON PREDIOS
REASON: RECHAZADO
REASON: RECEPCION
REASON: TOPOGRAFIA

=====

6. ADD A NEW TABLES.PHP PAGE TO THE MENU:

A. This page allows new tables, and new columns within those tables to be created/modified/deleted. These new tables create table + table_history for each one added, e.g.- architects and architects_history tables for a new grouping/category named \'Architects\'. Again, like the TRAMITES, PREDIAL, IDX, etc., all data history needs to be recorded/saved as newer information is added that replaces said data, like a new TELEPHONE number, or EMAIL address. The tables and columns created on this page are to appear on the CLAVE.PHP(formally DMP.PHP) page, and a little bit also will appear on the CLAVES.PHP page, so user can add info to individual clave number records, or groups of clave numbers.
B. In addition to normal columns, need to also be able to create photo, movie, and document upload forms. These need ot create folders that are inline with the naming conventions used for the tables, so in the case of the \'Architects\' example, the folder would be named /architects/ and this folder would then contain folders named; /pictures and /movies/ and /documents and so forth. The attached/uploaded pictures/movies/etc., will be assigned names like; 401001002003-123ABC-architects-joe_blow-00000001.jpg for example. This makes it easy to associate a given picture or document to the correct clave number, and in this example, to the respective Architect.

B. The CLAVES.PHP will need to have a simple form field selection of the above created tables/columns, that allows the user to select and add any information to a given clave number, using the tables and their respective columns as were previous added in the TABLES.PHP section.

=====

7. CSV IMPORT:

A. Often times we have information that we need to add to the database as it relates to clave numbers. For example, I may have a csv document that contains one row of CLAVE NUMBERS, a second row of OWNER NAMES, and a third row of EMAIL addresses. so, the column names of these csv documents need to match the column names in the tables in the dB. Often times, column names in the csv document will not belong to the same table, but rather, to multiple tables in our dB, so the code needs to account for this. If between all of the tables in our dB there exists duplicate column names, the code needs to announce this to the user upon import, asking the user which table the data is to be added to. Upon importing the data, all effected clave numbers/records need to have the previous versions of said data written to HISTORY and the data in the import needs to represent the latest/most recent time/date stamped data. Also need to associate the user name with said data. All historical data needs to have time/date stamps and user names associated and where the data originated from, so in the case of a csv import, that would be flagged as \'M\' for Manual source, but if the clave number originally came from the remote PREDIAL source, that previous version would need to be represented with \'P\' for Predial.

END OF TASKS