Daily charging method for monthly package
By creating a package rule table and an original call order conversion table, calculating the daily billing value and executing the out-of-scope billing instructions, the problem that the monthly package billing mode cannot be accurately billed by day is solved, and the accuracy of precise billing by day and out-of-scope billing is achieved.
Patent Information
- Application Number
- CN202510572602.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-05-06
- Publication Date
- 2025-07-22
AI Technical Summary
The existing monthly package billing model cannot be accurate to daily billing, resulting in the fact that the excess resource usage cannot be accurately billed out of the package.
By creating a package rule table and an original call order conversion table, calculate the daily billing value, and execute the out-of-scope billing instructions when the package resource usage exceeds the amount, use SQL statements and billing stored procedures to build the daily billing table and the out-of-scope billing table to generate the user call order billing output file.
The monthly package resources are accurately billed on a daily basis, ensuring that the excess is accurately calculated on an external bill, and solving the problem that the current technology cannot be accurately billed on a daily basis.
Smart Images
Figure CN120358305A_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the field of virtual operator billing, and particularly relates to a daily billing method for monthly packages. Background Art
[0002] In the field of mobile communication, the operator provides the virtual operator with the usage details of the numbers. This data is called a call record. We use the call record as the basis to bill users. For the billing of numbers, we have formulated packages. The so-called package is the basis for billing. In the package, there is a type called a monthly package. The monthly package stipulates the package fee that the number needs to pay this month and the resource usage situation that can be used at the same time. However, in this regulation, the stipulated resources can be used at any time this month. After the usage is completed, it will be completely charged according to the out-of-package usage. There is a special case, that is, if you want to evenly distribute the monthly package resources to the days of this month, there will be a fixed amount of resources used every day, and the excess part will be charged out of the package. Summary of the Invention
[0003] The purpose of this embodiment is to provide a daily billing method for monthly packages to solve the problem that the monthly billing mode cannot be accurately billed by day.
[0004] The present invention provides a daily billing method for monthly packages, including:
[0005] Create a package rule table and a raw call record conversion table, including: define the monthly package fee, the monthly package type, and the out-of-package type fee, where the monthly package type includes the monthly number of text messages, the monthly data volume, and the monthly voice call duration; the raw call record conversion table includes the phone number, the call record time, the number of text messages used, the voice call duration used, and the data volume used.
[0006] Execute the raw call record conversion instruction to construct the data of the raw call record conversion table. The raw call record conversion instruction includes converting the call record from the raw call record txt text line to the raw call record table record, and also includes executing the txt text line parsing instruction to obtain the single-line call record data. The txt text line parsing instruction includes obtaining the original user phone number, the call record time, the number of text messages used, the voice call duration used, and the data volume used.
[0007] Execute the billing storage call instruction. The billing storage includes constructing the daily billing table for the current month and starting the billing process; it also includes executing the date calculation instruction. If the current date is the first day of the month, it is determined to start billing; it also includes calculating the daily billing value for the current month according to the monthly package type fee in the package rule table through the monthly days formula, where the monthly days formula is:
[0008] Monthly daily flat-rate billing value = flat-rate fee / number of billing days in the current month; where the number of billing days in the current month is obtained by executing the SQL statement DAY(LAST_DAY(CURRENT_DATE));
[0009] The monthly billing table includes resource type, total amount, usage amount, remaining amount, out-of-package usage amount, effective date, and expiration date;
[0010] Execute the flat-rate package deduction instruction according to the package rule table to obtain the user's out-of-package billing table. If the user's flat-rate type usage value is greater than the flat-rate package defined value, it is determined that the out-of-package billing instruction is executed; where the out-of-package billing instruction includes obtaining the user's out-of-package billing table by executing a comparison instruction between the original call record table and the monthly daily billing table, and the comparison instruction includes select (out-of-package usage amount of the monthly daily billing table) / out-of-package type fee of the package rule table as out-of-package fee from the monthly daily billing table join the package rule table on the flat-rate type of the monthly daily billing table = the flat-rate type of the package rule table where the flat-rate type = (traffic or SMS or voice) and the call record time >= effective date and the call record time <= expiration date and (out-of-package usage amount of the monthly daily billing table) > 0;
[0011] Construct a user call record billing output file according to the user's out-of-package billing table.
[0012] Furthermore, the out-of-package type fee includes out-of-package SMS fee, out-of-package traffic fee, and out-of-package voice fee.
[0013] Furthermore, the original call record conversion instruction also includes the timer to periodically detect if there is a new file in the specified directory. If there is a new file, execute file reading and file content parsing and insert the parsed content into the original call record conversion table.
[0014] Furthermore, the create table instruction includes create table.
[0015] Furthermore, the original call record conversion instruction also includes periodically reading the specified directory. If there is a new file in the directory, execute the original call record conversion table record instruction.
[0016] Furthermore, constructing the monthly daily billing table includes creating it in the way of creating a temporary table during the execution of the billing stored procedure.
[0017] Furthermore, the SQL statement for determining the first day of each month in the date calculation instruction includes:
[0018] SELECT CASE
[0019] WHEN DAY(CURRENT_DATE)=1 THEN 'It is the first day of each month'
[0020] ELSE 'Not the first day of each month' END AS result.
[0021] Furthermore, executing the billing storage call instruction further includes an opening transaction instruction.
[0022] Furthermore, constructing the user call charge output file includes SQL execution statements
[0023] SELECT resource type 1, billing value 1, resource type 2, billing value 2
[0024] FROM the user out-of-package billing table INTO OUTFILE 'path / user number-call charge output file.txt'
[0025] FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n\r'.
[0026] Furthermore, the original call list table is a database table, and the resource type is a monthly package type.
[0027] A daily billing method for a monthly package provided by the present invention solves the problem that the monthly billing mode cannot be accurately billed on a daily basis;
[0028] To make the above objects, features, and advantages of the present invention more obvious and understandable, the following specifically enumerates preferred embodiments and, in conjunction with the accompanying drawings, details are described as follows. BRIEF DESCRIPTION OF THE DRAWINGS
[0029] Figure 1 : A method step diagram of the daily billing method for the monthly package provided by the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS
[0030] The following will describe the technical solutions in the embodiments of the present invention in conjunction with the drawings in the embodiments of the present invention.
[0031] This embodiment provides a daily billing method for a monthly package, including:
[0032] S001: Create a package rule table and an original call record conversion table, including: define the monthly package fee, define the monthly package type value and the out-of-package type fee. The monthly package type value includes the monthly SMS count, the monthly traffic volume, and the monthly voice duration. The out-of-package type fee includes the out-of-package SMS fee, the out-of-package traffic fee, and the out-of-package voice fee. The original call record conversion table includes fields such as phone number, call record time, SMS usage count, voice usage duration, and traffic usage volume. When specifically implemented, use the create table statement in mysql to create the table. The data definition can optionally be to enter the data content through the web terminal on the web page. An example of the package rule table is as follows:
[0033]
[0034]
[0035] In this embodiment, the table is created by a program in the Go language. When the billing program starts, it determines whether the table exists. If the table does not exist, use the table creation instruction to create the package rule table and the original call record conversion table. The code is as follows:
[0036]
[0037]
[0038]
[0039]
[0040] S002: Execute the original call record conversion instruction to construct the data of the original call record conversion table. The original call record conversion instruction includes converting the call record in the original call record txt text line into a record in the original call record table, including: execute the txt text line parsing instruction to obtain the single-line call record data, including the original user phone number, call record time, SMS usage count, voice usage duration, and traffic usage volume. When specifically implemented, use the Go language or Python to construct an independent process to read the file directory. If there is a new txt billing document in the directory, read the file content. In this embodiment, regularly read the specified directory. If there is a new file in the directory, execute the original call record conversion table record instruction. The following code is the Go language implementation to detect whether a new file exists in the directory:
[0041]
[0042]
[0043] The billing document in this embodiment is a txt document. Each line is a record of the user's use of package resources, and the end is ended by a carriage return and line feed. Each line records the user's resource usage and is separated by commas. After obtaining the txt file, the line reading file method is used to parse each line record. The record parsing instruction traverses the txt line record and inserts it into the original call record conversion table through the sql insert statement. The following is the code:
[0044]
[0045]
[0046]
[0047] S003: Execute the billing stored procedure instruction, including constructing the daily billing table for the current month and executing the billing instruction using an independent process;
[0048] In specific implementation, it is optional to use python to build a task execution terminal program, or use the go language to build an independent process, start an independent thread to obtain the local date in a timed manner. The construction of the daily billing table for the current month is created by creating a temporary table during the execution of the billing stored procedure, and its role is to be used as a temporary table for constructing the final out-of-package billing table; In specific implementation, the billing is started on the first day of each month. Every day, the mysql stored procedure is called to obtain the first day of each month. If it is determined to be the first day of each month; Execute the billing calculation to obtain the billing table. The optional go language implementation code in this embodiment is:
[0049]
[0050]
[0051] To determine whether it is the first day of each month, the following sql code can be optionally used:
[0052]
[0053] S004: Calculate the daily billing value for the current month according to the monthly package type fee in the package rule table through the formula of the number of days in the current month,
[0054] The formula for the number of days in the current month is:
[0055] The daily monthly package type billing value = monthly package type fee / number of billing days in the current month; where the number of billing days in the current month is obtained by executing the SQL statement DAY(LAST_DAY(CURRENT_DATE));
[0056] The billing table for the current month includes resource type, total amount, usage amount, remaining amount, out-of-package usage amount, effective date, expiration date; among them, the effective date and expiration date are the dates of each day in the current month; the resource type is the monthly package type;
[0057] S005: Execute the monthly package deduction instruction according to the package rule table to obtain the user's out-of-package billing table. If the user's monthly package type usage value is greater than the defined value of the monthly package, it is determined to execute the out-of-package billing instruction. The out-of-package billing instruction includes executing a comparison instruction between the original call record table and the daily billing table of the current month to obtain the user's out-of-package billing table. The comparison instruction includes select (out-of-package usage of the daily billing table of the current month) / out-of-package type fee of the package rule table as out-of-package fee from the daily billing table of the current month join the package rule table on the monthly package type of the daily billing table of the current month = the monthly package type of the package rule table where the monthly package type = (traffic or SMS or voice) and the call record time >= the effective date and the call record time <= the expiration date and (out-of-package usage of the daily billing table of the current month) > 0; In this embodiment, the user's out-of-package billing table is a temporary table, and before creating the temporary table, a start transaction instruction needs to be executed, including start transaction; to ensure that the original data can be rolled back after the temporary table operation fails.
[0058] S006: Construct a user call record billing output file according to the user's out-of-package billing table. The billing output file includes generating a document with a TXT suffix, and the document content includes the user's resource type and billing fee; In this embodiment, the user call record billing output file is generated by the S003 billing storage procedure, and the SQL statement includes:
[0059] SELECT resource type 1, billing value 1, resource type 2, billing value 2
[0060] FROM user's out-of-package billing table
[0061] INTO OUTFILE 'path / user number - call record billing output file.txt'
[0062] FIELDS TERMINATED BY ','
[0063] ENCLOSED BY '"'
[0064] LINES TERMINATED BY '\n\r';
[0065] A daily billing method for a monthly package provided by the present invention solves the problem that the monthly billing mode cannot be accurately billed on a daily basis.
Claims
1. A daily billing method for a monthly package, characterized in that, Including: Create a package rule table and an original call record conversion table, including: define the monthly package fee, the monthly package type, and the out-of-package type fee, where the monthly package type includes the monthly SMS count, the monthly data volume, and the monthly voice duration; Wherein the original call record conversion table includes the phone number, the call record time, the number of SMS used, the voice duration used, and the data volume used; Execute the original call record conversion instruction to construct the data of the original call record conversion table. The original call record conversion instruction includes converting the call record in the original call record txt text line into the original call record table record, and also includes executing the txt text line parsing instruction to obtain the single-line call record data. The txt text line parsing instruction includes obtaining the original user phone number, the call record time, the number of SMS used, the voice duration used, and the data volume used; Execute the billing storage call instruction. The billing storage includes constructing the daily billing table for the current month and starting the billing process; it also includes executing the date calculation instruction. If the current date is the first day of the month, it is determined that the billing starts; it also includes calculating the daily billing value for the current month according to the monthly package type fee in the package rule table through the monthly days formula, where the monthly days formula is: The daily monthly package type billing value = the monthly package type fee / the monthly billing days; where the monthly billing days are obtained by executing the SQL statement DAY(LAST_DAY(CURRENT_DATE)); The monthly billing table includes the resource type, the total amount, the usage amount, the remaining amount, the out-of-package usage amount, the effective date, and the expiration date; Execute the monthly package deduction instruction according to the package rule table to obtain the user's out-of-package billing table. If the user's monthly package type usage value is greater than the defined value of the monthly package, it is determined that the out-of-package billing instruction is executed; wherein the out-of-package billing instruction includes executing a comparison instruction between the original call record table and the daily billing table for the current month to obtain the user's out-of-package billing table. The comparison instruction includes select (the out-of-package usage amount in the daily billing table for the current month) / the out-of-package type fee in the package rule table as the out-of-package fee from the daily billing table for the current month join the package rule table on the monthly package type in the daily billing table for the current month = the monthly package type in the package rule table where the monthly package type = (data or SMS or voice) and the call record time >= the effective date and the call record time <= the expiration date and (the out-of-package usage amount in the daily billing table for the current month) > 0; Construct a user call record billing output file according to the user's out-of-package billing table.
2. The daily charging method for the monthly package according to claim 1, characterized in that, The out-of-package type fee includes the out-of-package SMS fee, the out-of-package data fee, and the out-of-package voice fee.
3. The daily charging method for the monthly package according to claim 1, characterized in that, The original call record conversion instruction also includes a timer to periodically detect if there is a new file in the specified directory. If there is a new file, execute file reading and file content parsing and insert the parsed content into the original call record conversion table.
4. The daily charging method for the monthly package according to claim 1, characterized in that, The create table instruction includes create table.
5. The daily charging method for the monthly package according to claim 1, characterized in that, The original call record conversion instruction also includes periodically reading the specified directory. If there is a new file in the directory, execute the original call record conversion table record instruction.
6. The daily charging method for the monthly package according to claim 1, characterized in that, The construction of the daily billing table for the current month includes creating it in the way of creating a temporary table during the execution of the billing storage procedure.
7. The daily charging method for the monthly package according to claim 1, characterized in that, The SQL statement for determining the first day of each month in the date calculation instruction includes: SELECT CASE WHEN DAY(CURRENT_DATE)=1 THEN 'It is the first day of each month' ELSE 'It is not the first day of each month' END AS result.
8. The daily charging method for the monthly package according to claim 1, characterized in that, The execution of the billing storage call instruction further includes a transaction start instruction.
9. The daily charging method for the monthly package according to claim 1, characterized in that, The construction of the user call record billing output file includes an SQL execution statement SELECT resource type 1, billing value 1, resource type 2, billing value 2 FROM the user out-of-package billing table INTO OUTFILE 'path / user number-call record billing output file.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n\r'.
10. The daily charging method for the monthly package according to claim 1, characterized in that, The original call record table is a database table, and the resource type is a monthly package type.