CN108268456B - Method for establishing invoice database and method for inquiring invoices in database - Google Patents

Abstract

The invention discloses a method for establishing an invoice database and a method for inquiring invoices in the database, belongs to the technical field of database sub-tables, and solves the problems of slow time consumption and low inquiry efficiency when the invoices are inquired for positioning data tables. The invention comprises a method for establishing an invoice database, which comprises the following steps: firstly, setting a threshold value according to an invoice code and an invoice number, and generating a character string; secondly, determining a mapping rule between a threshold value and the character string; judging whether a corresponding data table exists again, and if the corresponding data table does not exist, establishing a new data table; and finally, inserting the invoice into a data table. The invention also comprises a corresponding invoice query method: firstly, inputting an invoice code and an invoice number to be inquired, and generating a character string; then, searching a corresponding data table in the database according to the threshold and the mapping rule; and finally, searching the invoice needing to be inquired through the SQL statement. The invention has the characteristics of simple operation, convenient library building, high query efficiency and the like, and has wide market prospect.

Description

Method for establishing invoice database and method for inquiring invoices in database

Technical Field

The invention relates to an invoice query method, in particular to a method for establishing an invoice database and a method for querying invoices in the database, belonging to the technical field of database sub-tables.

Background

The invoice refers to the business certificate issued and collected by all units and individuals in purchasing and selling goods, providing or receiving services and doing other business activities, is the original basis for accounting, and is also the important basis for law enforcement inspection of auditing agencies and tax authorities. Whether the tax system is a tax authority or an enterprise, a large amount of invoices are issued every moment, and massive tax data is formed. The traditional invoice inquiry system is low in speed and efficiency, and how to quickly inquire invoiced information is the problem to be solved firstly.

In order to increase the query speed, common methods include database optimization, server expansion, and the like. The database optimization includes database structural design optimization, database sub-table division and the like. The database sub-tables are divided into time sub-tables and data quantity sub-tables. The difficulty of the database sub-table in the query process is how to locate the table, and the time for locating the table is long.

Disclosure of Invention

The invention aims to solve the technical problems of slow time consumption and low query efficiency when the invoice queries a positioning data table by providing a method for establishing an invoice database and a method for querying the invoice in the database.

In order to solve the technical problems, the invention provides a method for establishing an invoice database and a method for inquiring invoices in the database; a method of building an invoice database comprising the steps of:

s11: a database is newly established;

s12: setting a threshold value F according to the invoice code and the invoice number of the tax invoice: the length of the invoice code is N bits, the length of the invoice number is M bits, and the threshold value F is a positive integer smaller than N + M;

s13: reading a first invoice, and connecting a first digit of an invoice number after a last digit of an invoice code to generate a character string;

s14: determining a mapping rule between the character string and a threshold value F;

s15: selecting F characters in the character string as data table identifiers according to a mapping rule; judging whether a data table corresponding to the identification exists in the database: if yes, inserting the current invoice into the data table; if not, a data table corresponding to the data table identification is newly established, and the current invoice is inserted into the data table;

s16: judging whether the current invoice is the last invoice: if so, finishing the establishment of the invoice database; if not, the next invoice is read in, the first digit of the invoice number is connected to the last digit of the invoice code, a character string is generated, and the step S15 is executed in a returning way.

The mapping rule is as follows: the first F characters in the string are selected.

N is 12, M is 8, F is 6, and the mapping rule is: the 2 nd to 7 th characters in the character string are selected.

N is 12, M is 8, F is 6, and the mapping rule is: the first 5 and 8 th characters in the character string are selected.

N is 12, M is 8, F is 10, and the mapping rule is: the first 8, 11 and 12 characters in the character string are selected.

N is 10, M is 8, F is 5, and the mapping rule is: the first 4 characters and the 7 th character in the character string are selected.

N is 10, M is 8, F is 8, and the mapping rule is: the first 6, 8 and 10 characters in the character string are selected.

A method of querying invoices in a database built as described above, comprising the steps of:

inputting an invoice code and an invoice number to be inquired, and connecting the first bit of the invoice number after the last bit of the invoice code to generate a character string;

selecting F characters from the character string according to the threshold F and the mapping rule, finding out the data table identification which is the same as the F characters in the invoice database, and further finding out the corresponding data table;

and searching the data table for the invoice needing to be queried through a database search statement.

The database search statement is an SQL statement.

According to the invention, the invoice code and the invoice number are used as the base of the sub-table of the database, so that the sub-table positioning time is shortened, and the invoice query speed is increased; by setting the threshold, a limit on the magnitude of each data table is achieved. And by setting different mapping rules, the limitation on the content of each data table is realized. The invention has the characteristics of simple operation, convenient library building, strong applicability, high query efficiency and the like, and has wide market prospect.

Drawings

FIG. 1 is a flow chart of a method of building an invoice database according to the present invention;

FIG. 2 is a flow chart of a method of querying invoices in a database in accordance with the present invention.

Detailed Description

The present invention is further described below in conjunction with the following figures and specific examples so that those skilled in the art may better understand the present invention and practice it, but the examples are not intended to limit the present invention.

A tax invoice, the invoice code and number are the basic components. The common invoice code is 12 bits, and the value-added tax invoice code is 10 bits (including value-added tax common invoice and value-added tax special invoice); each digit in the invoice code has a specific meaning. The invoice number is 8, and each is the serial number, does not have specific meaning. The invoice code, along with the invoice number, identifies an invoice. The total of the invoice code and the invoice number of the common invoice is 20 bits, and the total of the invoice code and the invoice number of the value-added tax invoice is 18 bits; the 20-bit or 18-bit numeric character string is the basis of the database sub-table of the invention.

The 1 st position of the common invoice is a tax administration code (1-national tax, 2-local tax), the 2 nd to 5 th positions are 4 th positions before an administrative district code, the 6 th to 7 th positions are year codes (the last two digits are taken and are represented by 05 in 2005), the 8 th position is an invoice industry code (1-industry, 2-business, 3-processing, repairing and allocating industry, 4-purchasing industry, 5-water, electricity and gas industry, 6-other), the 9 th position is an invoice category code (1-hand-written invoice with uniform style of tax authority, 2-computer invoice with uniform style of tax authority, 3-enterprise top-name hand-written invoice, 4-enterprise top-name computer invoice, 5-tax control cash register invoice, 6-other invoices except 1 to 5, 7-invoice changing ticket), the 10 th position is an amount version code (0-computer version, 1-ten thousand yuan version, 2-hundred thousand yuan version, 3-million yuan version, 4-ten million yuan version, 5-thousand yuan version, 6-hundred yuan version, 7-ten yuan version, 8-tax controlled cash register, 9-no amount of money limitation), and 11 th to 12 th bits are batch numbers (numbering according to approved printing sequence by taking the year as a unit).

The 1 st to 4 th bits of the value-added tax general invoice are administrative division codes, the 5 th to 6 th bits are years, the 7 th bit is a printing batch, the 8 th bit is an invoice type (the value-added tax general invoice is 6), the 9 th bit is a joint number, and the 10 th bit is an amount version (the value-added tax general invoice is 0).

The 1 st to 4 th bits of the special value-added tax invoice are administrative division codes, the 5 th to 6 th bits are years, the 7 th bit is a printing batch, the 8 th bit is a character version, the 9 th bit is a joint number, and the 10 th bit is an amount version.

When the left number of the threshold value is X bits continuously, the function of limiting the upper limit of the record of a single data table can be achieved, for example, the threshold value is set to be 13, and then the single table stores 999 ten thousand invoices at most. In addition to setting the left consecutive X bits, the mapping rule of the threshold may be set as required: for example, if the mapping rule is set to bits 2-7 of the invoice code (character string), the table stores the invoice data of all invoices in a year under a certain administrative region.

Example 1:

the required equipment of the embodiment is a computer provided with database design software, and the embodiment selects a common invoice, wherein the length of the invoice code is 12 bits.

The working process of the embodiment is as follows:

first, an invoice database is built, see FIG. 1.

Thestep 100 is executed: and a database is newly established.

Step 101 is executed: and setting a threshold value F according to the invoice code and the invoice number of the common invoice. The length of the invoice code is 12 bits (12 is taken by N), the length of the invoice number is 8 bits (8 is taken by M), and the threshold value F is 8.

Executing step 102: reading in the first invoice, connecting the first bit of the invoice number after the last bit of the invoice code, and generating a character string.

Step 103 is executed: determining a mapping rule between the character string and a threshold value F: the first 8 characters in the string are selected.

Step 104 is executed: selecting the first 8 characters of the character string as data table identification according to the mapping rule; judging whether a data table corresponding to the data table identifier exists or not: if yes, go tostep 105; if not, go tostep 106.

Step 105 is executed: the invoice is inserted into the data table andstep 107 is performed.

Step 106 is executed: a corresponding data table is created and the invoice is inserted into the data table, andstep 107 is executed.

Step 107 is executed: judging whether the current invoice is the last invoice, if so, finishing the establishment of the invoice database; if not, go tostep 108.

Step 108 is executed: and reading the next invoice, connecting the first digit of the invoice number after the last digit of the invoice code, generating a character string, and returning to thestep 104.

Referring to fig. 2, the method for querying the tax invoice comprises the following steps:

step 201 is executed: the invoice code and the invoice number to be inquired are input.

Step 202 is executed: the first digit of the invoice number is followed by the last digit of the invoice code, and a character string is generated.

Step 203 is executed: the first 8 characters are selected from the character string according to the threshold and the mapping rule.

Step 204 is executed: and finding the corresponding data table identification according to the 8 characters so as to find the corresponding data table in the database.

Step 205 is executed: and searching the invoice needing to be inquired in the data table through the SQL statement, and finishing the inquiry.

In the embodiment, the first 8 characters of the character string are taken to obtain all the information of the common invoices of a specific administrative division, a specific tax bureau (national tax bureau or local tax bureau), a specific year and a specific industry, so that the operation is simple and the query is efficient.

Example 2:

the required equipment of the embodiment is a computer provided with database design software, and the embodiment selects the value-added tax common invoice, wherein the length of the invoice code is 10 bits.

The working process of the embodiment is as follows:

first, an invoice database is built.

Step 2100 is performed: and a database is newly established.

Step 2101 is performed: and setting a threshold value F according to the invoice code and the invoice number of the value-added tax common invoice. Wherein the length of the invoice code is 10 bits (N takes 10), the length of the invoice number is 8 bits (M takes 8), and the threshold value F takes 5.

Step 2102 is performed: reading in the first invoice, connecting the first bit of the invoice number after the last bit of the invoice code, and generating a character string.

Step 2103 is executed: determining a mapping rule between the threshold value F and the character string: the first 4 characters and the 7 th character of the character string are selected.

Step 2104 is performed: selecting the first 4 characters and the 7 th character of the character string as data table identifiers according to the mapping rule; it is determined whether a data table corresponding to 5 characters (the same data table identification as 5 characters) exists in the database: if yes, go to step 2105; if not, go to step 2106.

Step 2105 is executed: the invoice is inserted into the data table and step 2107 is performed.

Step 2106 is executed: a corresponding data table is created (the above 5 characters are used as data table identification), and the invoice is inserted into the data table, and step 2107 is executed.

Step 2107 is executed: judging whether the current invoice is the last invoice, if so, finishing the establishment of the invoice database; if not, go to step 2108.

Step 2108 is executed: and reading the next invoice, connecting the first digit of the invoice number to the last digit of the invoice code, generating a character string, and returning to the step 2104.

The method for inquiring the tax invoice comprises the following steps:

step 2201 is executed: the invoice code and the invoice number to be inquired are input.

Execution step 2202: the first digit of the invoice number is followed by the last digit of the invoice code, and a character string is generated.

Step 2203 is executed: and selecting the first 4 characters and the 7 th character from the character string according to the threshold value and the mapping rule.

Step 2204 is executed: and finding the corresponding data table (the data table identification same as the 5 characters) in the database according to the 5 characters.

Step 2205 is executed: and searching the invoice needing to be inquired in the data table through the SQL statement, and finishing the inquiry.

In the embodiment, the first 4 characters and the 7 th character of the character string are taken to obtain the common invoice information of all value-added taxes of a specific administrative division and a specific batch, so that the operation is simple and the query is efficient.

Example 3:

the required equipment of the embodiment is a computer provided with database design software, and the embodiment selects a common invoice, wherein the length of the invoice code is 12 bits.

The working process of the embodiment is as follows:

first, a tax invoice database is established.

Step 3100 is executed: and a database is newly established.

Step 3101 is performed: and setting a threshold value F according to the invoice code and the invoice number of the common invoice. The length of the invoice code is 12 bits (12 is taken by N), the length of the invoice number is 8 bits (8 is taken by M), and the threshold value F is 6.

Step 3102 is performed: reading in the first invoice, connecting the first bit of the invoice number after the last bit of the invoice code, and generating a character string.

Step 3103 is performed: determining a mapping rule between the threshold and the character string: the 2 nd to 7 th characters of the character string are selected.

Step 3104 is performed: selecting the 2 nd to 7 th characters of the character string as data table identifiers according to the mapping rule; it is determined whether a data table corresponding to the 2 nd to 7 th characters (the same data table identification as the 2 th to 7 th characters) exists within the database: if yes, go to step 3105; if not, go to step 3106.

Step 3105 is performed: the current invoice is inserted into the data table and step 3107 is performed.

Step 3106 is performed: a corresponding data table is created (the above 6 characters are identified as the data table), and the invoice is inserted into the data table, and step 3107 is performed.

Step 3107 is performed: judging whether the invoice is the last invoice, if so, finishing the establishment of the invoice database; if not, go to step 3108.

Step 3108 is performed: the next invoice is read in, the first digit of the invoice number is connected to the last digit of the invoice code, a character string is generated, and the step 3104 is executed.

The method for inquiring the tax invoice comprises the following steps:

step 3201 is performed: the invoice code and the invoice number to be inquired are input.

Step 3202 is performed: the first digit of the invoice number is followed by the last digit of the invoice code, and a character string is generated.

Execute step 3203: and selecting the 2 nd to 7 th characters from the character string according to the threshold value and the mapping rule.

Execute step 3204: the corresponding data table (the same data table identification as the above-mentioned 5 characters) in the database is found according to the 2 nd to 7 th characters.

Execute step 3205: and searching the invoice needing to be inquired in the data table through the SQL statement, and finishing the inquiry.

The mapping rule of the embodiment takes the 2 nd to 7 th bits of the character string, so that all the common invoice data of a specific year under a specific administrative division can be conveniently inquired, the operation is simple, and the inquiry is efficient.

Example 4:

the required equipment of the embodiment is a computer provided with database design software, and the embodiment selects the value-added tax special invoice, wherein the length of the invoice code is 10 bits.

The working process of the embodiment is as follows:

first, an invoice database is built.

Execute step 4100: and a database is newly established.

Step 4101 is performed: and setting a threshold value F according to the invoice code and the invoice number of the value-added tax special invoice. Wherein the length of the invoice code is 10 bits (10 is taken from N), the length of the invoice number is 8 bits (8 is taken from M), and the threshold value F is 8.

Execute step 4102: reading in the first invoice, connecting the first bit of the invoice number after the last bit of the invoice code, and generating a character string.

Execute step 4103: determining a mapping rule between the threshold value F and the character string: the first 6, 8 and 10 characters of the character string are selected.

Execute step 4104: selecting the first 6 characters, the 8 th character and the 10 th character of the character string as data table identifiers according to the mapping rule; whether a data table corresponding to 8 characters (the same data table identification as the 8 characters) exists in the database is judged: if yes, go to step 4105; if not, go to step 4106.

Execute step 4105: the invoice is inserted into the data table and step 4107 is performed.

Execute step 4106: a corresponding data table is created (the above 8 characters are used as data table identification), and the invoice is inserted into the data table, and step 4107 is executed.

Execute step 4107: judging whether the current invoice is the last invoice, if so, finishing the establishment of the invoice database; if not, go to step 4108.

Execute step 4108: the next invoice is read in, the first digit of the invoice number is connected to the last digit of the invoice code, a character string is generated, and the step 4104 is executed.

The method for inquiring the tax invoice comprises the following steps:

step 4201 is executed: the invoice code and the invoice number to be inquired are input.

Step 4202 is executed: the first digit of the invoice number is followed by the last digit of the invoice code, and a character string is generated.

Step 4203 is executed: and selecting the first 6 characters, the 8 th character and the 10 th character from the character string according to the threshold value and the mapping rule.

Step 4204 is executed: and finding the corresponding data table (the data table identification same as the 8 characters) in the database according to the 5 characters.

Step 4205 is executed: and searching the invoice needing to be inquired in the data table through the SQL statement, and finishing the inquiry.

In the embodiment, the first 6 characters, the 8 th character and the 10 th character of the character string are taken, all the value-added tax special invoice information of the specific administrative division, the specific year, the specific text version and the specific amount version is obtained, the operation is simple, and the query is efficient.

The invention adopts SQL sentences to search the database invoices, and has simple operation.

According to the invention, the invoice code and the invoice number are used as the base of the sub-table of the database, so that the sub-table positioning time is shortened, and the invoice query speed is increased; the limitation on the magnitude of each data table is realized by setting a threshold value; by setting different mapping rules, the limitation of the content of each data table is realized, and common invoices, value-added tax special invoices and the like are supported. The invention is particularly suitable for inquiring massive invoice tax data, has the characteristics of simple operation, convenient library establishment, strong applicability, high inquiring efficiency and the like, and has higher economic and market values.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

The above-mentioned embodiments are merely preferred embodiments for fully illustrating the present invention, and the scope of the present invention is not limited thereto. The equivalent substitution or change made by the technical personnel in the technical field on the basis of the invention is all within the protection scope of the invention. The protection scope of the invention is subject to the claims.

Claims (8)

1. A method of building an invoice database, comprising the steps of:

s11: a database is newly established;

s12: setting a threshold value F according to the invoice code and the invoice number of the tax invoice: the length of the invoice code is N bits, the length of the invoice number is M bits, and the threshold value F is a positive integer smaller than N + M;

s13: reading a first invoice, and connecting a first digit of an invoice number after a last digit of an invoice code to generate a character string;

s14: determining a mapping rule between the character string and a threshold value F, wherein the mapping rule is as follows: selecting the first F characters in the character string;

s15: selecting F characters in the character string as data table identifiers according to a mapping rule; judging whether a data table corresponding to the identification exists in the database: if yes, inserting the current invoice into the data table; if not, a data table corresponding to the data table identification is newly established, and the current invoice is inserted into the data table;

s16: judging whether the current invoice is the last invoice: if so, finishing the establishment of the invoice database; if not, the next invoice is read in, the first digit of the invoice number is connected to the last digit of the invoice code, a character string is generated, and the step S15 is executed in a returning way.

2. The method for building an invoice database according to claim 1, wherein N is 12, M is 8, F is 6, and the mapping rule is: the 2 nd to 7 th characters in the character string are selected.

3. The method for building an invoice database according to claim 1, wherein N is 12, M is 8, F is 6, and the mapping rule is: the first 5 and 8 th characters in the character string are selected.

4. The method for building an invoice database according to claim 1, wherein N is 12, M is 8, F is 10, and the mapping rule is: the first 8, 11 and 12 characters in the character string are selected.

5. The method for building an invoice database according to claim 1, wherein N is 10, M is 8, F is 5, and the mapping rule is: the first 4 characters and the 7 th character in the character string are selected.

6. The method for building an invoice database according to claim 1, wherein N is 10, M is 8, F is 8, and the mapping rule is: the first 6, 8 and 10 characters in the character string are selected.

7. A method of querying invoices within a database created by the method of claim 1 comprising the steps of:

inputting an invoice code and an invoice number to be inquired, and connecting the first bit of the invoice number after the last bit of the invoice code to generate a character string;

selecting F characters from the character string according to the threshold F and the mapping rule, finding out the data table identification which is the same as the F characters in the invoice database, and further finding out the corresponding data table;

and searching the data table for the invoice needing to be queried through a database search statement.

8. The method of claim 7, wherein the database search statement is an SQL statement.

Images