System Analysis of Data room charges 2

In the initial analysis of the data center charging system, the relationship between the table and the table is briefly described.

 

 

The following describes how to analyze the functions.

 

 

 

 

The first thing is getting on and off the machine.

 

When you are on the machine, first determine whether to register. If you are not registered, first register;

 

If you have already registered, you are checking whether you are on the machine;

 

When the machine is not on, the machine can be successfully connected only when the balance in the card is determined to be greater than the minimum balance;

 

When the machine is on, the machine information is written into the machine table;

 

Calculate the consumption amount and update the amount in the basic information table of the student;

 

Add the computer information to the computer information record and delete the records in the computer card table;

Note: The minimum balance in the card cannot be too small. If the student spends a long time on the computer and the amount is consumed, the balance will be negative.

 

 

 

Second, register.

 

At the time of registration, add information to the registration card information and student basic information table;

 

Determine whether to register. You can obtain information directly in the student information table.

 

 

 

 

Again, log on

 

Get the username in the login form box, and write it with the time and date to the in-duty table information;

 

When you exit the system, you can write the username, logon time, date, exit time, and date into the duty record table.

Specific functions:

 

1. Calculate the time period difference

 

There are machine time, machine time, calculation Machine Time

 

Obviously, it is impossible to directly subtract the time on the machine from the time on the machine. In code implementation, I first convert both the time into minutes. In this case, the time difference can be obtained.

 

This calculation is correct on the premise of the same date, but the calculation fails the next day. If you write your own code, it will be especially troublesome. It is quite easy to use the date functions written by others directly.

 

Dim A1 as string dim A2 as string a1 = txtstartdate & "" & txtstarttime 'machine time a2 = txtenddate & "& txtendtime 'machine time txtshow. TEXT = datediff ("N", a1, a2) 'display time difference, represented by minutes

It should be noted that the calculated result is the number of minutes of the time difference between A2 and A1; the date is large before and the date is small.

 

By changing the parameter n, you can also return variables such as year, month, and day of the difference between the two time periods.

 

(Yyyy, Q, M, Y, D, W, W, WW, H, n minutes, S)

 

 

 

2. Export to excel

 

There are several forms in the system that need to export the data in the table to excel. The function code can be encapsulated in the process, and the call process can be used directly. Duplicate code is written every time.

 

Code:

 

'*************************************** * ************************************ Number of letters: toexcel ** input: mygrid (msflexgrid)-'** output: None ** Function Description: Export to Excel sheet' ** Author: li Shuang Kai '** Date:' ** revised by: '** Date:' ** version: v1.0.0 '************************************** * ********************************** sub toexcel (mygrid msflexgrid) dim xlapp as Excel. application dim xlbook as Excel. workbook dim xlsheet as Excel. worksheet dim I as integer dim J as Integer Set xlapp = Createobject ("Excel. application ") set xlbook = xlapp. workbooks. add set xlsheet = xlbook. worksheets (1) for I = 0 to mygrid. rows-1 for J = 0 to mygrid. cols-1 xlsheet. cells (I + 1, J + 1) = mygrid. textmatrix (I, j) next J doevents next xlapp. visible = true end sub

 

3, ListBox

 

When the style attribute of the list box is set to 2, it can only be selected. When content cannot be added to the list box, we want to display our specific values in the program, an error occurs when values are directly assigned.

Solution: define a function in the module and return the index value of the corresponding string in the control.

 

 

Definition:

'*************************************** * ************************************ Number of letters: getindex' ** input: Combo (ComboBox)-'**: byval strvalue (string)-' ** output: (integer)-'** function description: return the index value of the corresponding string in the combo control. *** OPERATOR: Li Shuang yao' ** Date: 2012-08-21 '** modifier:' ** Date: '** version: v1.0.0 '************************************** * ********************************** public function getindex (combo as ComboBox, byval strvalue as string) as integer dim index as integer if combo. listcount <= 0 then getindex =-1 exit function end if for Index = 0 to combo. listcount-1 If trim (strvalue) = trim (combo. list (INDEX) Then getindex = index exit function end if nextend Function

When assigning values:

 cmbGrade.ListIndex = GetIndex(cmbGrade, m_rstclassinfo.Fields(1).Value)

 

 

 

4. Conditional Query

 

 

 

 

In the first line, if the first one is selected, the following two cannot be blank, and A is true.

 

In the second row, if the first one is selected, the following two values are not blank and B is true.

 

In the third row, if the first one is selected and the two following are not empty, C is true.

A B C uses the first line, the second line, and the third line respectively.

 

Query with a: dd (0) = true

 

Query with B: A dd (1) = true is not required

 

Use a DD (2) = true

 

Query with C: No A, B No AB dd (3) = true

 

Use one or two of the DD (4) = true

 

When dd (0) dd (1) is true, it is not necessary to judge whether the first composite is null or not,

 

When dd (2) is true, the first composite link must be determined to ensure that it is not empty,

 

When dd (4), make sure that the second combination is not empty,

The Code is as follows:

 

Dim objrs as ADODB. recordset dim STR as string dim TXT as string dim AA as string, BB as string dim dd (5) as Boolean, guanxi as string STR = "select * From oncard where" If cmbname1.text <> "" then' if the first line is selected, 'the first line is not empty, A is true if testtxt (cmbcrl1.text) then' control operator is not empty msgbox "operator cannot be blank, please select operator! ", Vbokonly + vbinformation," prompt "cmbcrl1.setfocus exit sub end if testtxt (txtmsg1.text) then" control query content is empty msgbox "the content to be queried cannot be blank, enter the content to query! ", Vbokonly + vbinformation, "prompt" txtmsg1.setfocus exit sub end if a = true end if cmbname2.text <> "" then' if you select the first line of the second line '', the second line is not empty, B is true if testtxt (cmbcrl2.text) then' control operator is not empty msgbox "operator cannot be blank, please select operator! ", Vbokonly + vbinformation," prompt "cmbcrl2.setfocus exit sub end if testtxt (txtmsg2.text) then" control query content is empty msgbox "the content to be queried cannot be blank, enter the content to query! ", Vbokonly + vbinformation, "prompt" txtmsg2.setfocus exit sub end if B = true end if cmbname3.text <> "" then' if you select the first ''of the third row, the third row is not empty, C is true if testtxt (cmbcrl3.text) then' control operator is not empty msgbox "operator cannot be blank, please select operator! ", Vbokonly + vbinformation," prompt "cmbcrl3.setfocus exit sub end if testtxt (txtmsg3.text) then" control query content is empty msgbox "the content to be queried cannot be blank, enter the content to query! ", Vbokonly + vbinformation," prompt "txtmsg3.setfocus exit sub end if C = true end if a then' if select a, DD (0) true if cmbname1.text = "card number" then aa = "card_id" BB = trim $ (txtmsg1.text) end if cmbname1.text = "" then aa = "date" BB = format $ (trim $ (txtmsg1.text), "yyyy/mm/DD ") end if STR = STR & trim $ (AA) & "" & trim $ (cmbcrl1.text) & "'" & BB & "'" DD (0) = true end if B then' select B, and then select a, DD (1) is true If cmbname2.text = "card number" then aa = "card_id" BB = trim $ (txtmsg2.text) end if cmbname2.text = "" then aa = "date" BB = format $ (trim $ (txtmsg2.text), "yyyy/mm/DD ") end if not dd (0) then' if you select B, select a STR = STR & trim $ (AA) & "" & trim $ (cmbcrl2.text) & "'" & BB & "'" DD (1) = true else ": select a, and select B if testtxt (cmb0.text) Then msgbox. The composite link cannot be empty, select the combination link ", vbokonly + vbinformation," prompt "cmb0.setfocu S exit sub end if cmb0.text = "or" then Guanxi = "or" end if cmb0.text = "and" then Guanxi = "and" end if STR = STR &""& guanxi & "" & aa & "" & trim $ (cmbcrl2.text) & "'" & BB & "'" DD (2) = true end if C then if cmbname3.text = "card number" then aa = "card_id" BB = trim $ (txtmsg3.text) end if cmbname3.text = "" then aa = "date" BB = format $ (trim $ (txtmsg3.text), "yyyy/mm/DD") end if If not (DD (0) or DD (1) or DD (2) then' if both A and B do not select STR = STR & trim $ (aa) & "" & trim $ (cmbcrl3.text) & "'" & BB & "'" DD (3) = true else 'select either or both of them if testtxt (cmb1.text) then msgbox "the composite link cannot be blank. Select a composite link! ", Vbokonly + vbinformation, "prompt" cmb1.setfocus exit sub end if cmb1.text = "or" then Guanxi = "or" end if cmb1.text = "and" then Guanxi = "and" end if STR = STR & "" & Guanxi & "" & trim $ (aa) & "" & trim $ (cmbcrl3.text) & "'" & BB & "'" DD (4) = true end if not (DD (0) or dd (1) or DD (2) or DD (3) or DD (4) Then msgbox "select a query method", vbokonly + vbinformation, "prompt" cmbname1.setfocus exit sub end if

 

 

 

Rows A, B, and C can be queried separately or in combination.

One problem is that when two conditions are queried, the combination relationship between A and C must be the second one, and the first one does not work ..

 

When querying three statements, the composite relationship must be used ..

 

The entire judgment process is to determine the SQL statement.

When the conditions are determined, SQL statement concatenation is normal, and the query results are naturally not wrong.