I have CSV files which contain SMS delivery reports for SMS messages been sent by our platform. These CSV are downloaded nightly, sometimes there are duplicates SMS's in the downloaded files which I would like to avoid in the database. We would need to be able to check what exists in the database and if it matches the message ID or has the same sending timestamp that it would not be duplicated in the DB.
I would like a continuous log file of any Duplicates not written to the database as a duplicate log file for audit purposes.
The messageID for each record is unique! what can change is it can go from Pending, Delivered, Undelivered etc... we should store pending and Undeliverable in separate table
The format is as follows: the header remains the same each time:
msgid, accountid, groupid, destaddr, srcadd,received,status, completed, error, data, mcc, sendid,
18D005DB, 13247, 0, 447937555666, OilGuard, 2016-09-17 20:10:22+02:00, Delivered, 2016-09-17 20:10:28+02:00,0,THIS IS THE BODY OF SMS, 234, null,
I would also like you to create a master OUTPUT MONTHLY CSV output that will incrementally added the data to so I can have a CSV that would show all SMS sent for September_2016.csv as example, in the utility folder.
I would like a windows based Utility that will incrementally add those CSV to a Table which you will create in a existing mySQL data base on our server. (I have the access required to create a new table etc... and I can organise for Teamviewer access to create the required columns and setup the system).
I would like an option to search this database via a webpage, by date selectors, either in combination with AccountID, GroupID, DestationAddress, Status(pending, delivered, undelivered), error or free text contained in DATA the text body of the message or just by date selectors , sometimes we can search by key text such as a site name or a particular alert type such as TAMPER Alert Active etc....... (The attached file has sample data for September ).
The ability to download the Search result file to CSV or save in PDF format that works in landscape mode if necessary to make the information easily legible for the user. (don't want to squint to read the text, nor do I want massive font, normal font size 10-12 is fine)
The ability to give access to a certain Account ID Only is desirable if easy to do. I can embed this into our platform which used iframes and does all the user account logins etc...... If I can set a filter only to a specific account ID and then embed that URL into a site on one of our platforms, this maybe a searchable view. (needs further discussion and for me to show you the platforms we have)
The web page should be formatted and created to be aesthetically pleasing and look very modern and professional to the users. A modern look and feel is expected. no engineering boxes etc......
I would like to be able to serve this out publicly using a subdomain, if we have some basic SSL/AES encryption on it (self signed certificate) then I can re-enforce the security by deploying it under our CDN secure server which will strengthen the AES SSL security. (this part to be discussed/ shown)
There is potential for another project here to add an API to call data record for a mobile network. Separate project!! based on SOAP api call on a secure token based system.
There may be some clarifications to this specification before it's final, I will prompt the responders to re-read it if any major changes are made prior to hire.
This is a cost sensitive project!