Write a Python GUI program which executes MS-SQL commands and posts it to a PHP webpage
Budget: $250 – $750 USD
Please author a program which can be downloaded from my website and run on a Windows computer which completes these functions. The below is a summarized list, a completed spreadsheet of requirements will be sent upon acceptance of the project job.
[PHP]
1. “if Post is Empty”, display instructions to user to download [EXE] and run it. (Text instructions will be provided by me.)
2. “if Post is not Empty and firsttime=yes”, send an email to <useremail> with text “Your report is submitted; review it by clicking this link http://...php?method=post&firsttime=no&name=name&etc=etc”
3. “if Post is not Empty and firsttime=yes”, also save the displayed webpage to a file ~/<useremail><datetime>.txt
4. “if Post is not Empty”, display an HTML report <see Command List> which looks similar to:
<table>
<tr><td>User Name</td><td>$name</td></tr>
<tr><td>User Email</td><td>$email</td></tr>
<tr><td>SQL Version</td><td>$sqlver</td></tr>
<tr><td>Count of Documents</td><td>$doc_cnt</td></tr>
<tr><td>Text1</td><td>$var1</td></tr>
<tr><td>Text2</td><td>$var2</td></tr>
etc…
[EXE]
1. This will be written in Python then made “standalone” using PyInstaller
2. Upon running, program will display a UI which receives input from user for some information: Name, EmailAddress, MS-SQL Server Name, MS-SQL Username, MS-SQL Password.
3. The UI has an "Console" which displays results and errors during running. If a command fails, the console/log will display an error message and pause (e.g. "Failed to log in to SQL Server: Username/Password Incorrect")
4. The UI has a button “Read List of Databases” which can be clicked by the user.
5. When user clicks “Read List of Databases”, program will attempt to connect to SQL and read this SQL command: Select Dbname From Vaultlist
6. Upon success, the UI will display a list of <Dbname> and allow the user to select One Single Dbname. The list may require a vertical scroll depending on quantity displayed.
7. The UI has a button “Generate Report from Selected Database” which can be clicked by the user after user has selected One Single Dbname
8. The program will connect to the SQL server. The program will use a specific database (Use <Dbname>). The program will then execute approximately 40 SQL commands and store the results <see Command List>
9. The program will generate a HTTP Post URL to the [PHP] based on the results of the SQL commands.
10. The UI Console will display the HTTP URL. The UI Console will display “Loading Default Web Browser…”
11. The program will attempt to open a Default Web Browser and launch the HTTP Post URL to the [PHP].
12. The UI has a button "Close Program". When the user clicks this button, the program closes.
[Command List]
You will be provided a list in Excel format which has approximately 40 rows and these columns:
DISPLAY TEXT, VARIABLE NAME, SQL COMMAND
User Name, name, N/A (Manual User Entry)
User Email, email, N/A (Manual User Entry)
SQL Version, sqlver, SELECT @@VERSION
Count of Files, doc_cnt, SELECT COUNT(*) FROM FILES
Text1, var1, SELECT COUNT(*) FROM TABLE
Text2, var2, SELECT AVG(*) FROM TABLE2
etc.
[PHP]
1. “if Post is Empty”, display instructions to user to download [EXE] and run it. (Text instructions will be provided by me.)
2. “if Post is not Empty and firsttime=yes”, send an email to <useremail> with text “Your report is submitted; review it by clicking this link http://...php?method=post&firsttime=no&name=name&etc=etc”
3. “if Post is not Empty and firsttime=yes”, also save the displayed webpage to a file ~/<useremail><datetime>.txt
4. “if Post is not Empty”, display an HTML report <see Command List> which looks similar to:
<table>
<tr><td>User Name</td><td>$name</td></tr>
<tr><td>User Email</td><td>$email</td></tr>
<tr><td>SQL Version</td><td>$sqlver</td></tr>
<tr><td>Count of Documents</td><td>$doc_cnt</td></tr>
<tr><td>Text1</td><td>$var1</td></tr>
<tr><td>Text2</td><td>$var2</td></tr>
etc…
[EXE]
1. This will be written in Python then made “standalone” using PyInstaller
2. Upon running, program will display a UI which receives input from user for some information: Name, EmailAddress, MS-SQL Server Name, MS-SQL Username, MS-SQL Password.
3. The UI has an "Console" which displays results and errors during running. If a command fails, the console/log will display an error message and pause (e.g. "Failed to log in to SQL Server: Username/Password Incorrect")
4. The UI has a button “Read List of Databases” which can be clicked by the user.
5. When user clicks “Read List of Databases”, program will attempt to connect to SQL and read this SQL command: Select Dbname From Vaultlist
6. Upon success, the UI will display a list of <Dbname> and allow the user to select One Single Dbname. The list may require a vertical scroll depending on quantity displayed.
7. The UI has a button “Generate Report from Selected Database” which can be clicked by the user after user has selected One Single Dbname
8. The program will connect to the SQL server. The program will use a specific database (Use <Dbname>). The program will then execute approximately 40 SQL commands and store the results <see Command List>
9. The program will generate a HTTP Post URL to the [PHP] based on the results of the SQL commands.
10. The UI Console will display the HTTP URL. The UI Console will display “Loading Default Web Browser…”
11. The program will attempt to open a Default Web Browser and launch the HTTP Post URL to the [PHP].
12. The UI has a button "Close Program". When the user clicks this button, the program closes.
[Command List]
You will be provided a list in Excel format which has approximately 40 rows and these columns:
DISPLAY TEXT, VARIABLE NAME, SQL COMMAND
User Name, name, N/A (Manual User Entry)
User Email, email, N/A (Manual User Entry)
SQL Version, sqlver, SELECT @@VERSION
Count of Files, doc_cnt, SELECT COUNT(*) FROM FILES
Text1, var1, SELECT COUNT(*) FROM TABLE
Text2, var2, SELECT AVG(*) FROM TABLE2
etc.