JP4343613B2 - Form creation device, program - Google Patents

Description

  The present invention relates to a form creation method and a form creation apparatus that extract data from a database device and a database file (hereinafter collectively referred to as a database) and create a form by flowing the extraction result as field data in accordance with a predefined field definition. The present invention relates to a computer-executable form creation program.

Conventionally, there has been a form creation system (see, for example, Patent Document 1) that obtains field data and places the obtained field data on a form to create a desired form.
JP2000-222494

  The present applicant considers a form composed of a main form part and a subform part as one form of the form. For example, a quote as shown in FIG. 12 is a slip in which product names and their amounts are listed for each order number, and the main form has fields such as order number, customer information, and order date. The subform is a form having a plurality of detail lines with a product name, number of pieces, unit price, amount of money, etc. as one line, and a total row field summing up the amounts. When creating a form with such a main form and subform, the field data and subform of the main form that are flowed into the fields of the main form based on one query from the database (single table or multiple tables combined) are created. Data that is combined with field data that flows into the field is extracted. Each field data contained in the extracted data is poured into the field of the main form and the field of the subform.

  For example, FIG. 13 shows a system that manages field data using two tables, an order table 1301 and an order detail table 1302. Here, first, a table 1304 generated by combining fields to be extracted from the order table 1301 and the order details table 1302 is obtained. It is a figure explaining the case where a report is created by listing details for each. In this case, as shown in the table 1304, the same value is repeatedly entered in the field data corresponding to the main form of each record ("Order No.", "Order Date", "Customer Name" in FIG. 13). End up. Therefore, the amount of data in the generated table becomes large, and if a large amount of data is extracted at once or connected to a database device via a network, the memory required for the form creation process increases, the database The data transfer time from the computer becomes long, and the processing efficiency deteriorates.

  The present invention has been made in view of the above-described points, and an object of the present invention is to reduce the data amount of the extraction result by extracting data with different queries in the main form and the subform.

In order to solve the above problems, a form creation device of the present application is a form creation device that creates a form by pouring data extracted from a database into a form composed of a first form part and a second form part. ,
Extraction condition setting means for setting a first extraction condition indicating the extraction condition of the first data flowing into the first form portion and a second extraction condition indicating the extraction condition of the second data flowing into the second form portion. When,
A separate query process for extracting data using a separate query for each of the first form part and the second form part, and a single query for both the first form part and the second form part Switching means for switching between single query processing for extracting data using a query,
First data extraction means for extracting the first data including a plurality of records from the database by generating and executing a query of the first form part based on the first extraction condition;
One of the records included in the first data extracted by the first data extraction means is poured into the first form portion of the form, one record poured into the first form portion, the second extraction condition, The second data is extracted from the database by generating and executing the query of the second form part using the second, and the second of the form in which the one record is poured into the first form part. First form creation means for executing a process of creating a form by pouring the extracted second data into the form part;
By generating and executing one query that combines the first extraction condition and the second extraction condition, the third data that flows into the first form part and the second form part is extracted from the database. Third data extraction means for
Second form creation means for executing a process of creating a form by pouring the third data extracted by the third data extraction means into the first form portion and the second form portion;
When the switching means switches to the different query processing, the first data extraction means extracts the first data, and records in the extracted first data are sequentially sorted one by one. And repeatedly executing the processing of the first form creation means until all the records included in the first data are processed,
On the other hand, when the switching unit switches to the single query processing, the third data extraction unit extracts the third data, and further uses the extracted third data to extract the second form. The processing of the creating means is executed.

In order to solve the above problems, a program of the present application causes a computer to create a form by pouring data extracted from a database of a database device into a form composed of a first form part and a second form part. A program,
The computer,
Extraction condition setting means for setting a first extraction condition indicating the extraction condition of the first data flowing into the first form portion and a second extraction condition indicating the extraction condition of the second data flowing into the second form portion. ,
A separate query process for extracting data using a separate query for each of the first form part and the second form part, and a single query for both the first form part and the second form part Switching means for switching between single query processing for extracting data using a query,
First data extraction means for extracting the first data including a plurality of records from the database by generating and executing a query of the first form part based on the first extraction condition;
One of the records included in the first data extracted by the first data extraction means is poured into the first form portion of the form, one record poured into the first form portion, the second extraction condition, The second data is extracted from the database by generating and executing the query of the second form part using the second, and the second of the form in which the one record is poured into the first form part. First form creation means for executing a process of creating a form by flowing the extracted second data into the form part;
By generating and executing one query that combines the first extraction condition and the second extraction condition, the third data that flows into the first form part and the second form part is extracted from the database. Third data extraction means for
A program for causing the third data extracted by the third data extraction means to flow into the first form part and the second form part to function as second form creation means for executing a process for creating a form And
Further, when the switching means switches to the different query processing, the first data extracting means extracts the first data, and further records one by one in the extracted first data. When used in order, repeatedly execute the processing of the first form creation means until all records included in the first data are processed, while the switching means is switched to the single query processing, Extracting the third data by the third data extraction means, and further causing the computer to function so as to execute the processing of the second form creation means by using the extracted third data. Features.

  According to the present invention, the data of the main form is not repeatedly entered in the data of the extraction result from the database, and the overall data amount of the extraction result is reduced. In particular, if a large amount of data is extracted at once, or if it is connected to a database device via a network, the required memory for form creation processing can be reduced, and the time required to transfer data from the database can be minimized. effective.

  In addition, if you use separate query processing, the amount of extracted data can be reduced, but the number of query executions increases, so you can switch between separate query processing (multiple query processing) and single query processing. There is an effect that it is possible to cope with an environment where the number of queries greatly affects the processing speed rather than the data amount of the extraction result, such as when there is a large amount of data or when the database is locally connected.

  Hereinafter, an embodiment of the present invention will be described with reference to the drawings.

  FIG. 1 is a diagram showing the configuration of the entire system to which the form creation device of this embodiment belongs. The form creation device of the present embodiment is assumed to be connected to the database device via the LAN, but the connection method with the database device may be other methods such as WAN or local connection, or form creation. It may be a database file in the device.

  FIG. 2 is a block diagram showing a basic hardware configuration of the form creation device of FIG. The form creation apparatus according to the present embodiment includes a central processing unit 1, a storage device 2, a display device 3, a keyboard 4, a mouse 5, an external storage device 6, a printing device 7, and the like. This can be realized by introducing a program into an information processing apparatus such as a computer.

  The computer program for realizing the present invention can be distributed to computers via the external auxiliary storage device 6 or a network. As a storage medium for storing a computer program or the like, a ROM, a floppy (registered trademark) disk, a CD-ROM, a hard disk, a memory card, a magneto-optical disk, or the like can be used. FIG. 6 shows a configuration example of a form creation program stored in a storage medium. FIG. 7 is an example of a memory map in which the form creation program, DB connection information, query information, form data, and program work memory are stored in the storage device 2 and the program can be executed.

  The DB connection information 704 (FIG. 8) stores a data source name indicating the location of the database in which the field data to be flowed into the form, a user name and a password for accessing the database, and the like are stored.

  Query information 705 (FIG. 9) is information for generating an SQL (query) for extracting field data to be flowed from the database into the form. The query setting for the main form and the subform is different for the main form and the subform. Includes a flag indicating whether data is extracted in the query of, a flag indicating whether records are deleted (not extracted) when records of the same value are consecutive, and setting of quotes of a character string. FIG. 10 is an example of a screen for setting another query flag, duplicate record deletion flag, and quotation marks.

  FIG. 11 is an example of a screen for performing query settings for the main form and the subform. In the upper half area of the screen, a list of tables and fields of the database from which data is extracted is displayed, and table joins are set if necessary. In the lower half area of the screen, fields to be extracted (including aggregation and scalar calculation) in the table, extraction conditions, etc. are set. This setting is performed for each of the main form and the subform. For example, based on an instruction from the user, (order date> 2002/01/01 AND order date <2002/12/31) is set as the extraction condition for the main form, and (number> 5) as the extraction condition for the subform. Can be set.

  When the other query flag is OFF, one query is generated with the query setting that combines the main form query setting and the subform query setting, whereas when the other query flag is ON, the main form query setting and the subform Generate a separate query for each query setting. Details will be described later.

  Form data 706 includes the design and field definition of the fixed part of the form. The field definition includes settings for the main form fields and subform detail lines and summary lines. In addition, form data having field definitions corresponding to the query settings of the query information 705 is read and stored in advance.

  Next, details of processing executed in the present embodiment will be described.

  First, the overall operation will be described based on the flowchart of FIG. This process is executed after the setting in FIG. 10 and FIG. 11 is performed. First, it is determined whether another query flag of the query information 705 is ON (step S301). If it is determined to be OFF, the process proceeds to step S302, and one query is generated with a query setting that combines the main form query setting and the subform query setting of the query information 705. In other words, the extraction target field is the sum of the extraction target field of the main form query setting and the extraction target field of the subform query setting, and the AND of the extraction condition of the main form query setting and the extraction condition of the subform query setting is the extraction condition. One query is generated. Then, the generated query is executed (step S303), and a plurality of records of the extraction results returned by the database are stored in the record variable R on the program work memory 707 (step S304). Then, each record of R is poured into the main form and subform as field data (steps S305 to S308). Since the subform is a tabular format, new records are added as records are flowed sequentially. However, since the main form is a single-sheet format, if the records are flowed sequentially, the same field is overwritten (or ignored) until a page break occurs.

  On the other hand, if it is determined in step S301 that it is ON, the process proceeds to step S309 and another query process is performed. Next, another query process in step S309 will be described in detail with reference to FIG.

  First, a main form query is generated based on the extraction conditions set in the main form query setting of the query information 705 (step S401), the generated query is executed (step S402), and the extraction result returned by the database is returned. A plurality of records are stored in the variable MR for storing the main form record on the program work memory 707, and the number of records is stored in the variable MC (step S403). The loop variable i is initialized to 0 (step S404), and it is determined whether i is smaller than MC (step S405). If it is smaller, the MR's i-th record (0 origin) is poured into the main form (step S406). Sub-form query processing (described later) corresponding to the i-th record of MR is performed (step S407), i is counted up (step S408), and the process returns to step S405, and loops until the MR record is exhausted. When done, exit the process.

  Next, the subform query process S407 will be described in detail with reference to FIG. First, a subform query different from the main form query is generated from the extraction condition set in the subform query setting of the query information 705 and the i-th MR record (step S501). The subform query generates one query based on the AND of the extraction condition set in the subform query setting and the value of the i-th MR record. For example, in the case of a form as shown in FIG. 12, the first MR stores a record of (order number, order date, customer name) = (0011, 2002 / ○ / x, XXX Corporation). Then, (order No, order date, customer name) = (0011, 2002 / o / x, XXX Corporation) is added to the extraction condition of the query setting of the subform by AND. Thereby, only the record of the subform corresponding to the record of the i-th main form can be extracted. When ANDing with the value of the i-th MR record, fields that are not included in the subform table are excluded from the extraction conditions.

  Next, the query generated in S501 is executed (step S502), and a plurality of records of the extraction results returned by the database are stored in the record variable SR on the program work memory 707, and the number of records is stored in the variable SC (step S503). ). The loop variable j is initialized with 0 (step S504), and it is determined whether j is smaller than SC (step S505). If it is smaller, the jth record (0 origin) of SR is poured into the subform (step S506). j is incremented (step S507), the process returns to step S505, and if the process is looped until there is no SR record, the process is exited.

  As described above, according to the present embodiment, the data of the main form is not repeatedly included in the data of the extraction result from the database, and the overall data amount of the extraction result is reduced. In particular, if a large amount of data is extracted at once, or if it is connected to a database device via a network, the required memory for form creation processing can be reduced, and the time required to transfer data from the database can be minimized. effective.

  Also, if you use separate query processing, the amount of data in the extraction result can be reduced, but the number of query executions increases, so if you can switch between separate query processing and single query processing, there is a large amount of available memory There is an effect that it is possible to cope with an environment where the number of queries greatly affects the processing speed rather than the amount of data of the extraction result, such as when the database is connected locally.

1 is a diagram illustrating an overall configuration of a system according to an embodiment of the present invention. It is a block diagram which shows a hardware configuration. It is a flowchart which shows the process sequence in this embodiment. It is a flowchart which shows the procedure of another query process. It is a flowchart which shows the procedure of a subform query process. It is a figure which shows the structure of the form creation program stored in the storage medium. It is a memory map in a state where a program can be executed. It is explanatory drawing of DB connection information. It is explanatory drawing of query information. It is a figure which shows an example of the setting screen of query information, such as another query flag. It is a figure which shows an example of the setting screen of query setting. It is a figure which shows an example of the form containing a subform. It is a figure which shows an example which extracts the field data containing a subform with one query from several tables.

Claims (5)

A form creation device for creating a form by pouring data extracted from a database into a form composed of a first form part and a second form part,
Extraction condition setting means for setting a first extraction condition indicating the extraction condition of the first data flowing into the first form portion and a second extraction condition indicating the extraction condition of the second data flowing into the second form portion. When,
A separate query process for extracting data using a separate query for each of the first form part and the second form part, and a single query for both the first form part and the second form part Switching means for switching between single query processing for extracting data using a query,
First data extraction means for extracting the first data including a plurality of records from the database by generating and executing a query of the first form part based on the first extraction condition;
One of the records included in the first data extracted by the first data extraction means is poured into the first form portion of the form, one record poured into the first form portion, the second extraction condition, The second data is extracted from the database by generating and executing the query of the second form part using the second, and the second of the form in which the one record is poured into the first form part. First form creation means for executing a process of creating a form by pouring the extracted second data into the form part;
By generating and executing one query that combines the first extraction condition and the second extraction condition, the third data that flows into the first form part and the second form part is extracted from the database. Third data extraction means for
Second form creation means for executing a process of creating a form by pouring the third data extracted by the third data extraction means into the first form portion and the second form portion ;
When switched to said further query processing by said switching means, said extracting said first data by the first data extracting means, further, the records contained in the first data the extracted one by one And repeatedly executing the processing of the first form creation means until all the records included in the first data are processed ,
On the other hand, when the switching unit switches to the single query processing, the third data extraction unit extracts the third data, and further uses the extracted third data to extract the second form. A form creation device characterized by executing processing of a creation means . The form creation apparatus according to claim 1, wherein the first form part is a main form, and the second form part is a subform. The computer, to the form composed of a first foam portion and a second foam portion, a program for making creates a form by pouring the data extracted from the database of the database apparatus,
The computer,
Extraction condition setting means for setting a first extraction condition indicating the extraction condition of the first data flowing into the first form portion and a second extraction condition indicating the extraction condition of the second data flowing into the second form portion. ,
A separate query process for extracting data using a separate query for each of the first form part and the second form part, and a single query for both the first form part and the second form part Switching means for switching between single query processing for extracting data using a query,
First data extraction means for extracting the first data including a plurality of records from the database by generating and executing a query of the first form part based on the first extraction condition;
One of the records included in the first data extracted by the first data extraction means is poured into the first form portion of the form, one record poured into the first form portion, the second extraction condition, The second data is extracted from the database by generating and executing the query of the second form part using the second, and the second of the form in which the one record is poured into the first form part. First form creation means for executing a process of creating a form by flowing the extracted second data into the form part;
By generating and executing one query that combines the first extraction condition and the second extraction condition, the third data that flows into the first form part and the second form part is extracted from the database. Third data extraction means for
A program for causing the third data extracted by the third data extraction means to flow into the first form part and the second form part to function as second form creation means for executing a process for creating a form And
Further, when the switching means switches to the different query processing, the first data extracting means extracts the first data , and further records one by one in the extracted first data. When used in order, repeatedly execute the processing of the first form creation means until all records included in the first data are processed , while the switching means is switched to the single query processing, Extracting the third data by the third data extraction means, and further causing the computer to function so as to execute the processing of the second form creation means by using the extracted third data. A featured program. The program according to claim 3 , wherein the first form part is a main form and the second form part is a subform. A computer-readable storage medium storing the program according to claim 3 .

Images