System and method for data capture and reporting
View Patent ↗A database system includes an intermediary program that provides a variety of functions. Logging on to the intermediary program results in log-in information being transmitted from an encrypted file to the database system via the intermediary program. The database system includes the ability for the user to enter criteria for a query in a variety of different manners, including the ability to directly modify generated SQL statements and the ability to use input lists in creating queries. In addition, the queries entered by the user can be stored in a separate database, such that the efficiency and operation of the database system can be improved. In addition, the delivery of query results and reports allows for delivery of files that are associated with a data period in any format and also allows the user to receive multiple reports in a single, compressed file.
1. A method for facilitating execution of a query on a database comprising:
receiving an input list from a user, wherein said input list includes query criteria relating to a field in said database;
storing said input list with a profile corresponding to said user;
receiving a selection of a query type from said user, wherein said query type corresponds to a query template;
receiving a request from said user to retrieve said input list;
providing said user with said input list;
receiving a query field and query criteria from a user, wherein said query criteria is selected from said input list by said user;
creating a Structured Query Language (SQL) statement based on said query field, said query criteria, and said query type;
executing said SQL statement to add said query field and said query criteria to said query template;
saving said query template within a first record of a query database to create a stored query;
receiving a selection of said stored query;
executing said stored query against said database;
saving said stored query in a second record of said query database;
performing an analysis of said query database, wherein said analysis is based on performance data resulting from execution of said stored query on said database, and wherein said analysis determines at least one of: database fields that are queried least often and database fields that are queried most often.
2. The method of claim 1 wherein said receiving step comprises receiving a graphical selection of said query field upon which said stored query is executed; and receiving said query criteria for said query field.
3. The method of claim 2 further comprising selecting an operator for said query field.
4. The method of claim 1 , wherein said step of receiving said query field and said query criteria from said user comprises receiving said query field and said query criteria from a secure system.
5. The method of claim 1 , wherein said step of receiving said query field and said query criteria from a user comprises accessing a secure system by receiving log-on information on an intermediary system, validating said log-on information by said intermediary system, obtaining access data from a separate file wherein said access data contains information used to locate and access said secure system, transmitting said access data to said secure system, validating said access data by said secure system, and allowing access to said secure system via said intermediary system.
6. The method of claim 5 , wherein said separate file is encrypted and stored on a terminal, wherein said terminal receives said log-on information.
7. The method of claim 1 further comprising delivering information.
8. The method of claim 1 further comprising delivering information by generating a first file using data from a specific data period within said database, associating said first file with said data period, categorizing said first file such that said first file is available to a pre-determined group of users, and placing said first file in a location accessible to said pre-determined group of users.
9. The method of claim 8 , wherein said location comprises a location accessible via an Internet connection.
10. The method of claim 1 , further comprising delivering information by generating a file using said stored query and data from a specific data period within said database.
11. The method of claim 1 further comprising delivering information by generating a first file using data from a specific data period within said database, associating said first file with said data period, categorizing said first file such that said first file is available to a pre-determined group of users, placing said first file in a location accessible to said pre-determined group of users, generating a second file, compressing said first file and said second file, placing a result of said compressing step into a third file, categorizing said third file such that said third file is available to a pre-determined group of users, and placing said third file in a location accessible to said pre-determined group of users.
12. The method of claim 1 , wherein said step of performing an analysis includes at least one of determining performance of said stored query, determining most frequently queried tables, and determining most frequently queried fields.
13. A machine-readable medium having stored thereon a plurality of instructions, said plurality of instructions when executed by a processor, cause said processor to perform a method comprising:
receiving an input list from a user, wherein said input list includes query criteria relating to a field in said database;
storing said input list with a profile corresponding to said user;
receiving a selection of a query type from said user, wherein said query type corresponds to a query template;
receiving a request from said user to retrieve said input list;
providing said user with said input list;
receiving a query field and query criteria from a user, wherein said query criteria is selected from said input list by said user;
creating a Structured Query Language (SQL) statement based on said query field, said query criteria, and said query type;
executing said SQL statement to add said query field and said query criteria to said query template;
saving said query template within a first record of a query database to create a stored query;
receiving a selection of said stored query;
executing said stored query against said database;
saving said stored query in a second record of said query database;
performing an analysis of said query database, wherein said analysis is based on performance data resulting from execution of said stored query on said database, and wherein said analysis determines at least one of: database fields that are queried least often and database fields that are queried most often.