ESIID PHP program notes
use php to write a program to go a website and download files each week.
use mysql to create table to import these zips with data flat files (cvs) or updates
use php/mysql to let user search these tables for their address record.
show them the found records and let them select which one is theirs
write two web pages one for user search and one for me as admin
Web 1 page for [url removed, login to view]
user search for ESIID meter number
simple capture of TEXT in a picture to verify not bot
Web 2 page for admin (user and password protected)
admin (me) to view status on last updates done and processed
view number of searches and search data with IP info for blocking use
manually force file download and update process of mysql data
modify cron date and hour of job and which files to download and process
view new signups for electricity
Purpose of project
I market Electricity Customers in Texas, which is regulated in Texas and governed by ERCOT.
ERCOT uploads data files every week to their website for our use
These files have the Electric Service ID Number (ESIID) and
address info for every electricity customer in TEXAS.
This list comes in about 15 files from the electric transmission companies (TDSP)
On the first Tuesday of every month we can get the full file from each TDSP.
On all other Tuesdays we can get an update file from each TDSP.
1. write a program to go to ERCOT's site and download these files after midnight CST
This needs to be a CRON job also
Send an email alert when job is done and or an email of job errors.
(attached documents tells the date and times that these files are available)
2. write a program so when I go to admin page and I can request job to start manually.
(in case of error or they process file after our scheduled time and files were not there.)
3. create MySQL database to import full file loads or update with update data
I have attached the sql create table file for structure
There will be several million records in complete database table
We only use about 9 million records of these.
ERCOT's filename is long but the last 16 characters is the name of TDSP + New or [url removed, login to view]
4. I need a table that list all TDSP's, last download, new/update, success/fail with field for False or True.
IF false cron job will not download this TDSP file. I want to download all in beginning,
but I will not update all of them all of the time.
We only use about 8 TDSP's all of the time every week.
5. I need a table that tracks logins, and searches. The search table tracks who did the search,
date time, search data entered, how many seconds search took, user browser info, cookie,
ip address. I want the ability to block a machine or ip address or robots. and capture
any other info you think is good to know.
6. create program to search mysql esiid TDSP table.
we search by Address and sometimes on ESIID number. Most people do not know their ESIID number.
7. create table to save date,time, ESIID detail records when customer selects to sign up for electricity service.
write programs to export these into flat file and email them to me
8. create program to strip excess blanks out of Address, City, ZIP fields and repopulate data.
parse the address field into smaller field segments so we can search by parts of the address.
Reference files included in zip file
search site - Address Search - Apollo Power &... Adobe Acrobat ...
data files to download - ERCOT TDSP Esiid [url removed, login to view] Adobe Acrobat ...
search site - [url removed, login to view] Screen [url removed, login to view] Adobe Acrobat ...
ercot file info - [url removed, login to view] Microsoft Offic...
Mysql table - [url removed, login to view] SQL File
download site - ERCOT [url removed, login to view] source vi... Text Document
this file my notes - ESIID PHP program [url removed, login to view] Text Document
sample download data -TDSP sample DATA ONCOR_E... Text Document
search site =[url removed, login to view] demo_esiids_city_se... Web php
9 file(s) 512,923