Grassroots unit personnel and transaction management system and method based on VBA platform

Abstract

The invention relates to the technical field of unit management informatization, and particularly discloses a basic-level unit personnel and transaction management system and method based on a VBA platform, and adopts a layered design of a data layer, a function layer and a display layer. The data layer constructs a'human-event-object 'associated data network by using relational databases such as Access and the like; the functional layer realizes data acquisition, associated query, information sharing, statistical analysis and intelligent processing through VBA; and the display layer is provided with a comprehensive information publicity interface and a panoramic view by taking Excel as a carrier. According to the invention, personnel management is taken as a core, static / dynamic / associated data are associated to describe personnel portraits, 'person-event-object 'information is fused, data islands are avoided, information can be automatically pushed, a statistical chart can be generated, and cross-system sharing is supported. According to the method, multi-dimensional management of personnel is deepened, complicated information processing is simplified, communication cost is reduced, closed loop of work is assisted, management convenience, efficiency and fineness of basic units are improved, and the method is suitable for efficient management of personnel and affairs of the basic units.

Description

TECHNICAL FIELD

[0001] The present application relates to the unit management information technology field, and in particular to a grassroots unit personnel and transaction management system and method based on a VBA platform. BACKGROUND

[0002] The management work of many grassroots units is still highly dependent on traditional methods such as paper files, Excel tables, WeChat group notifications, and oral arrangements. These methods have significant drawbacks such as information dispersion, low efficiency, extensive management, and lack of foresight. Although there are professional ERP or OA systems on the market, such systems are generally redundant in function, high in cost, complex to implement, and lack flexibility, making it difficult to adapt to the personalized management processes of small grassroots units.

[0003] Therefore, there is an urgent need for a lightweight, low-cost, high-flexibility, and closely integrated comprehensive management solution that closely integrates the actual management scenarios of the unit. SUMMARY

[0004] The technical problem to be solved by the present application is to provide a grassroots unit personnel and transaction management system and method based on a VBA platform, which adopts a hierarchical design including a data layer, a function layer, and a display layer, and each layer works cooperatively to realize the association and efficient management of "person-transaction-object" information.

[0005] To solve the above technical problems, the technical solution provided by the present application is: a grassroots unit personnel and transaction management system based on a VBA platform, which adopts a hierarchical design including a data layer, a function layer, and a display layer, and each layer works cooperatively to realize the association and efficient management of "person-transaction-object" information.

[0006] The data layer is constructed using a relational database, aiming to establish a "person-transaction-object" data relationship network that can be traced back to each other, providing unified and reliable data support for the entire system.

[0007] Considering the budget and use convenience of grassroots units, the Access database in the Office component is preferred; if the basic conditions allow, mature and stable relational databases such as MySQL and PostgreSQL can also be used.

[0008] The data layer includes a person-entity data table, a transaction-entity data table, and an object-entity data table; specifically as follows:

[0009] The person-entity data table includes static basic attributes such as personnel ID, name, department, and position, as well as extended attributes such as associated organization ID, skill label, and responsibility range, wherein the personnel ID is a globally unique identifier for uniquely identifying each personnel entity.

[0010] Task-Entity data table: covers task table, project table, on-duty table, commendation table, leave table. The task table is provided with a responsible person ID, a participant ID list, and an associated material ID list field; the on-duty table is provided with a duty person ID and a shift ID field; the commendation table is provided with an award winner ID and an associated task ID field; the leave table is provided with an applicant ID, an approver ID, and an extension management responsible person ID field. The IDs in each table are globally unique identifiers and are associated with related entities.

[0011] Material-Entity data table: includes material equipment table, file data table, and site facility table. The material equipment table is provided with a current manager ID / use person ID field; the file data table is provided with a creator ID and an associated matter ID field; and the site facility table is provided with a management responsibility person ID field. The IDs in each table are also globally unique identifiers and are used to associate related personnel or transaction entities.

[0012] The person-entity data table, the task-entity data table, and the material-entity data table are associated through foreign keys or special relationship tables. For example, the responsible person ID in the task table is associated as a foreign key to the personnel ID in the person-entity data table, the current manager ID in the material equipment table is associated to the personnel ID in the person-entity data table, and the associated material ID list in the task table is associated to the material ID in the material-entity data table, finally forming a data relationship network that can be traced back to each other.

[0013] The functional layer is the core processing module of the system, covering five functions of data collection, association query, information sharing, statistical analysis, and intelligent processing. VBA technology is used to realize efficient processing of various management needs.

[0014] The data collection module provides two types of input interfaces: structured Excel templates and UserForm forms. Structured Excel templates are suitable for collecting static basic data (such as personnel basic information and material basic information), with each row corresponding to a record and each column corresponding to a field. The UserForm form input interface is suitable for collecting dynamic transaction data (such as task publishing, on-duty scheduling, and leave application), with the interface design more in line with user operation habits, improving the convenience of input.

[0015] Data writing logic: when the user submits the data, the VBA code traverses the Excel table rows or reads the UserForm form input values, converts the text information into corresponding entity IDs, such as converting the responsible person's name into the personnel ID; constructs an INSERT INTO SQL statement, executes the SQL statement through ADO connection, and writes the data into the database. At the same time, it supports multi-terminal collaborative collection, and different terminal users can collect specific types of data according to their permissions, and the collected data is shared in the system.

[0016] Query logic of the association query module: SQL join query statements are constructed through VBA to realize association query by using the unique IDs in each entity data table. For example, according to the personnel ID, the tasks associated with the personnel (through the task table's responsible person ID or participant ID list), the duty information (through the duty table's duty personnel ID), the commendation records (through the commendation table's award winner ID), and the leave information (through the leave table's applicant ID) can be queried; according to the task ID, the associated personnel (through the task table's responsible person ID and participant ID list), the materials (through the task table's associated material ID list), and the related matters (such as associated projects and pre-task) can be traced back to be queried.

[0017] Query result output of the association query module: the query result can be directly displayed in the interface of the display layer, supporting data filtering and sorting, and facilitating users to quickly obtain the required information.

[0018] The information sharing module establishes a unique data authority source, i.e., a unified data center, all business functions read and write data from this center to eliminate "data islands" and ensure data consistency and accuracy.

[0019] The information sharing module provides a standardized data interface, and other authorized systems, such as the systems of superior units and financial systems, can securely obtain or write data through API to realize cross-system information sharing and data interaction.

[0020] The information sharing module integrates Outlook, and uses VBA to call Outlook functions. When data changes, such as new task publishing, notification updating, and task deadline approaching, the system automatically pushes information to relevant personnel through email to ensure timely information delivery.

[0021] The statistical analysis module realizes the ETL data extraction process through VBA, regularly extracts data from the business database, cleanses and converts the data, and then loads it into the data warehouse to provide high-quality data for statistical analysis.

[0022] The statistical analysis module connects the data in the data warehouse or the data directly obtained from the business database to Excel, and uses Excel pivot tables to generate various statistical analysis charts, including personnel performance dashboards (statistical individual / department task completion rate, work time input, and award situation), work load analysis charts (analysis of task total amount and duty frequency of each department and each time period), and leave trend prediction charts (analysis of the time distribution trend of the number of leave and leave types), supporting managers to intuitively understand the unit operation status and providing data support for decision-making.

[0023] The statistical analysis module realizes the automatic refreshing of statistical analysis charts through VBA code, and can set a timed refresh or manually trigger a refresh to ensure that the chart data is synchronized with the database in real time.

[0024] The intelligent processing module includes a rule-based duty task reminder engine, a personnel comprehensive quantitative evaluation algorithm, and an intelligent scheduling optimization algorithm.

[0025] The duty task reminder engine is an event-driven and state-judgment-based VBA module, and the workflow is as follows:

[0026] After the system is started or triggered at a regular time, the status of all unfinished tasks and related management data are checked, each task is traversed, and it is determined whether it meets the preset rules. If the rules are met, the reminder content is generated, the recipients are determined, and the reminder is pushed to the designated recipients through email, system message or public interface. The preset rules include approaching deadline (within a certain number of days / hours before the task deadline, and the task status is still "not started" or "in progress"), overdue (the task exceeds the deadline and is not completed), state blocking (the task is marked as "blocked" state), pre-task completion (when the pre-task of the task is completed, the task responsible person is reminded to start work), insufficient resources (when the required quantity of the task is insufficient, the task responsible person is reminded), personnel health / task overdue history (when a person with a disease or a person with a recent history of multiple task overruns is assigned a new task, the manager is reminded).

[0027] The personnel comprehensive quantitative evaluation algorithm is as follows:

[0028] Index extraction: Extract multi-dimensional evaluation index data, evaluation dimensions include ideal belief, business literacy, responsibility, and moral character, and index data comes from data tables associated with people in the database, such as democratic evaluation results, commendation / criticism records, education attendance rate, task completion rate, etc.

[0029] Score conversion and weight configuration: Convert the original data into percentage scores according to the preset rules, and configure the weights for each index data. The weights are set according to the management priorities of the unit.

[0030] Score calculation: Calculate the dimension scores and comprehensive scores by writing functions in VBA, where dimension score = Σ(index score x index weight), and comprehensive score = Σ(dimension score x dimension weight).

[0031] Result output and storage: Output the evaluation results in the form of a leaderboard, a radar chart or a PDF report in Excel. The leaderboard displays the comprehensive score ranking of personnel, and the radar chart visually displays the dimension scores. At the same time, the results of each evaluation are stored in a history table for subsequent trend analysis, such as comparing the changes in personnel's comprehensive performance at different times.

[0032] The intelligent scheduling optimization algorithm is as follows:

[0033] Under the premise of meeting various constraints (personnel responsibilities, physical condition, professional level, fairness, scheduling order), the optimal duty roster, on-duty table or task allocation scheme is automatically generated by writing an algorithm in VBA.

[0034] The display layer uses Excel as the front-end carrier to provide users with an intuitive and convenient information viewing and interaction interface, including a comprehensive information display interface and a panoramic view.

[0035] Comprehensive information display interface: The interface is divided into six sections: duty information, on-duty information, personnel dynamics, transaction dynamics, work arrangement, and important notice.

[0036] Duty information section: Displays the names of duty personnel, duty time, duty post, contact information, and other information to facilitate personnel understanding of duty arrangements.

[0037] On-duty information section: Presents the on-duty personnel list, on-duty period, on-duty location, on-duty tasks, and other content to ensure the orderly development of on-duty work.

[0038] Personnel dynamics section: Updates dynamic information such as personnel leave, business trip, training, and award, allowing managers to keep abreast of personnel status in real time.

[0039] Transaction dynamics section: Displays task progress (e.g., not started, in progress, completed), problem handling, and other transaction-related dynamics to facilitate tracking of transaction progress.

[0040] Work arrangement section: Lists recent work tasks, responsible departments / personnel, and completion time limits to clarify work objectives and requirements.

[0041] Important notice section: Publishes important unit notifications, meeting arrangements, activity organization, and other information to ensure that information is accurately conveyed.

[0042] Interaction logic: VBA code is triggered by buttons to pull the latest data from the database and populate the display tables in each section. A search condition input box is provided, and after the user inputs a keyword, VBA constructs a dynamic SQL query based on the input and returns the results. Direct data modification in the display table is supported, and after selecting save, VBA executes the UPDATE SQL statement to write changes back to the database. At the same time, VBA can be triggered to refresh data at regular intervals or manually to ensure the real-time nature of the interface information.

[0043] Panoramic view: Support two query perspectives, one is to click the personnel ID, the system executes the associated query through VBA, shows all the transactions (tasks, on duty, on duty, etc.) responsible by the personnel, the projects participated, the rewards obtained, the current leave state, the equipment and materials used or managed, etc. Information forms a complete personnel portrait; the second is to click the task ID, the system also executes the associated query through VBA, shows all the personnel participating in the task, the equipment and materials required for executing the task, other transactions (such as pre-task, follow-up task, associated commendation record) related to the task, etc. Information realizes the whole chain tracing of the transaction.

[0044] Based on the above system, the application also provides a grassroots unit personnel and transaction management method based on VBA platform, comprising the following steps:

[0045] Step S1: Database construction: Construct a relational database, define the fields and attributes of the person-entity data table, the transaction-entity data table and the material-entity data table according to the management needs of the grassroots unit, assign global unique IDs to each entity, establish the association between the three through foreign keys or special relationship tables, and form a "person-transaction-material" data relationship network.

[0046] Step S2: Data collection: Design Excel input template and UserForm window, Excel input template is used to collect personnel basic information, material basic information and other static data, UserForm window is used to collect task publishing, on duty arrangement, leave application and other dynamic data; through VBA code, realize multi-terminal collaborative collection, when collecting data, automatically complete the conversion of text information to entity ID, construct SQL statement and execute, write data into database.

[0047] Step S3: Associated query development: Write SQL join query statement through VBA, use the unique ID in the entity data table to realize the query of person-transaction-material associated information. Support aggregation query associated transactions and resources according to personnel ID, trace query associated personnel and materials according to task ID, the query result is displayed in the display layer interface, supports filtering and sorting.

[0048] Step S4: Information sharing: Develop information sharing function based on VBA, establish a unified data center to ensure that all business functions read and write data from the center; configure standardized API interface to support data interaction between other authorized systems and the system; integrate Outlook, write VBA code to realize automatic message pushing, when the data changes, send email notification to the relevant personnel in time.

[0049] Step S5: statistical analysis and intelligent processing development: ETL data extraction is realized by VBA, and the data in the business database is extracted, converted and loaded into the data warehouse; combined with Excel data pivot table, personnel performance dashboard, workload analysis chart, leave trend prediction chart and other statistical analysis charts are generated, and automatic refreshing is realized; the code of intelligent processing module based on rules is written, including duty task reminder engine (realizing multi-rule triggered reminder), personnel comprehensive quantitative evaluation algorithm (realizing multi-dimensional score calculation) and intelligent scheduling optimization algorithm (realizing scheduling under multi-constraint condition).

[0050] Step S6: display interface design: design the comprehensive information publicity interface and panoramic view in Excel, divide the comprehensive information publicity interface into six function blocks, write VBA code to realize data pulling, querying, modifying and refreshing functions; the panoramic view realizes the function of displaying associated information by clicking personnel ID or task ID through VBA associated query code, and finally the whole system is built and deployed.

[0051] Compared with the prior art, the present application has the following advantages:

[0052] The system of the present application is centered on personnel management, and around the management core of "person", through collecting and summarizing static, dynamic and associated data information related to "person", from the basic situation of static personnel post, responsibility, skill, to dynamic management education, daily behavior performance, thought dynamics, psychological health, to the responsibility positioning of the individual, major activity task, organizational incentive care, examination and training, file data, commendation and reward, leave and vacation, etc., the personnel portrait is described from multiple aspects and angles, and the management of the person is deepened from single-dimensional work performance management to multi-dimensional comprehensive analysis and comprehensive deepening management.

[0053] Under the idea of taking personnel management as the center, the "person" is used as the subject to associate various aspects of daily operation of the grassroots unit, and the work, transaction / task (activity), material equipment, site equipment, hidden problem and file data management modules strongly related to "person" are established for planning and arranging work affairs, deploying resources and data analysis and use, realizing the information interrelation and fusion of "person", "affair" and "thing".

[0054] The present application realizes comprehensive and efficient information processing of isolated and complicated information in the operation of grass-roots units by means of information sharing, statistical analysis, information screening and extraction, automatic generation and automatic distribution; the administrative interface is designed in the form of comprehensive information publicity, and unit dynamic information is displayed, the interface is divided into on-duty information, on-duty information, personnel dynamic information, transaction dynamic information, work arrangement, important notice and other blocks, not only creating a comprehensive information public window for unit personnel and reducing the communication friction cost caused by personnel information difference, but also providing an effective management means for the work closed-loop procedure of "planning-deployment-implementation-feedback", and the information publicity of on-duty, on-duty, attendance, work progress and the like is also an important means of supervision and driving responsibility implementation; the functions of automatic scheduling, task publishing, work attendance, result feedback, quantitative evaluation, automatic reminding of duty and task, automatic broadcast information, party fee holiday calculation and the like are set, practical tools are provided for transaction information processing, and the management work is more convenient, efficient and fine. BRIEF DESCRIPTION OF DRAWINGS

[0055] Figure 1 is a whole technical architecture flow chart of the grass-roots unit personnel and transaction management system based on the VBA platform.

[0056] Figure 2 is a data layer entity association construction flow chart.

[0057] Figure 3 is a duty and task reminding engine work flow chart.

[0058] Figure 4 is a personnel comprehensive quantitative evaluation algorithm implementation flow chart.

[0059] Figure 5 is a data acquisition module VBA logic flow chart.

[0060] Figure 6 is a grass-roots unit personnel and transaction management method flow chart. DETAILED DESCRIPTION

[0061] Various exemplary embodiments of the present application will be described in detail below with reference to the accompanying drawings. Note that the relative arrangement, numerical expressions and values of components and steps set forth in these embodiments are not limiting to the scope of the present application unless otherwise specifically stated.

[0062] The following description of at least one exemplary embodiment is merely illustrative in nature and is in no way limiting to the scope of the application and its applications or uses.

[0063] Techniques, methods, and devices known to those of ordinary skill in the relevant art can not be discussed in detail herein, but should be considered as part of the specification, where appropriate.

[0064] In all examples shown and discussed herein, any specific values ​​should be interpreted as merely exemplary and not as limitations. Therefore, other examples of exemplary embodiments may have different values.

[0065] The following detailed description, in conjunction with the accompanying drawings, provides a further explanation of the personnel and affairs management system and method for grassroots units based on the VBA platform according to the present invention.

[0066] Technical principle of the invention:

[0067] The core design and logical starting point of this invention system is "people," aiming to build a comprehensive information processing platform centered on the comprehensive management of personnel. Its core logical relationships are as follows:

[0068] 1. Construct a comprehensive personnel profile using multi-dimensional data to achieve deeper management.

[0069] The system first focuses on "people," comprehensively collecting, summarizing, and integrating various related data and information to construct a three-dimensional, dynamically updated database of individuals and organizations. This data system comprises three levels:

[0070] (1) Static basic data: such as the name, place of origin, years of service, position, responsibilities, skills and other basic information of the personnel, which constitute the basic framework for identifying and understanding people.

[0071] (2) Dynamic Process Data: The system extensively tracks dynamic information closely related to personal development, including education and management, daily performance, performance on major tasks, organizational incentives and care, training and assessment, documents and materials, commendations and awards, leave and vacation, etc. This data reflects the individual's growth trajectory, work status and comprehensive contributions in real time.

[0072] (3) Associated transaction data: Strongly associate personnel with specific work tasks and resources.

[0073] Through the integration of data at these three levels, the system successfully transforms the management of people from traditional, one-dimensional "performance" assessment into multi-dimensional comprehensive analysis and in-depth management, providing precise data support for scientific decision-making.

[0074] 2. Connect all matters through people, achieving interconnection between people, things, and events.

[0075] Under the "people-oriented management" approach, the system uses people as a key theme, connecting all aspects of the daily operations of its grassroots units. Every work arrangement, task execution, and duty shift is clearly linked to specific responsible persons and participants, thus establishing a work and affairs management system strongly related to people. This transforms the unit's daily planning and arrangements from abstract lists of tasks into specific, traceable action plans down to the individual level, ultimately achieving the interconnection and deep integration of information about people, tasks, and resources.

[0076] 3. Achieve efficient governance through information technology tools and "intelligent" algorithms.

[0077] Faced with a large amount of isolated and complex information in grassroots units, the system achieves comprehensive and efficient information processing through information sharing mechanisms, statistical analysis tools, and rule-based logical algorithms. This is specifically reflected in two functional levels:

[0078] (1) Comprehensive Information Disclosure Interface (Administrative Management Interface)

[0079] As a centralized information window, it dynamically displays key information such as duty information, on-duty information, personnel dynamics, work arrangements, and important notices.

[0080] Value: It significantly reduces communication friction caused by "information asymmetry" and improves transparency. Simultaneously, it is a key management tool for promoting a closed-loop workflow of "planning-deployment-implementation-feedback." Publicly available information itself serves as an effective form of supervision and motivation for accountability.

[0081] (2) Full-process transaction processing toolset

[0082] The system provides a series of practical tools, including task posting, work attendance, work handover, result feedback, comprehensive quantitative evaluation of personnel, automatic scheduling, automatic reminders for unfinished duties and tasks, automatic information broadcasting, and calculation of party dues and holidays.

[0083] Value: These tools digitize, streamline, and automate daily management tasks, significantly improving the convenience, efficiency, and precision of management work, freeing managers from tedious administrative tasks and allowing them to focus on decision-making and overall planning.

[0084] In summary, the logical relationship of the system of this invention is based on "people" as the core, gathering data to build profiles; based on "people" as the framework, linking and integrating information about people, things and events; and finally, through intelligent information processing and public disclosure tools, realizing the refinement, efficiency and intelligence of grassroots unit management.

[0085] Combined with appendix Figures 1-6 This invention will be described in detail below.

[0086] This invention achieves the integration of "people, events, and things," essentially by establishing a massive, traceable relationship network (Graph) at the data layer. Its core implementation principle is: a relational data model + unique identifier (ID) association.

[0087] This invention digitizes all elements (people, events, and things) in the system into an independent entity and assigns it a globally unique ID. Relationships are recorded and maintained through foreign keys in the database or specially designed relationship tables.

[0088] 1. Data Modeling: Defining Entities and Relationships

[0089] (1) Person-Entity Data Table, which contains static data such as personnel ID, name, department, position, and identity information.

[0090] Extended attributes: associated organization ID (associated to another person - entity data table or independent "organization" entity), skill tags, scope of responsibilities.

[0091] (2) Entity Data Table

[0092] Task: Task ID (primary key), Task Name, Task Content, Creation Time, Deadline, Priority, Status (Not Started, In Progress, Completed)

[0093] Project: A collection of multiple "tasks".

[0094] On-duty / duty: Shift ID, date, time, position, status (planned, in progress, completed).

[0095] Awards and Recognition: Award ID, Award Name, Awarding Organization, and Award Date.

[0096] Leave and Vacation Requests: Leave Request ID, Type (Annual Leave, Sick Leave), Start Time, End Time, Duration, Status (Under Approval, Approved), and Management of Leave and Vacation Periods.

[0097] (3) Object-Entity Data Table

[0098] Materials and Equipment: Item ID, Name, Type, Batch Number, Parameters (Specific Performance Parameters), Quality (New, Ready, Awaiting, Scrap), Status (Idle, In Use, Under Repair), Current Location.

[0099] Document information: Document ID, Document Name, Type, Security Classification, Creator, Related Item ID, Storage Path.

[0100] Site facilities: Site facility ID, site facility name, stored materials, ancillary facilities, current status, and person in charge of management.

[0101] 2. Establish connections: "build" a network using IDs.

[0102] This is a crucial step in achieving integration. Connections are established by storing the IDs of other entities in the entity table.

[0103] (1) Person-to-person relationship:

[0104] The task table has fields for the person in charge ID and a list of participants IDs, which point to the person-entity data table.

[0105] The duty roster includes fields for duty personnel ID and shift ID.

[0106] The award form includes an awardee ID field.

[0107] The leave application form includes fields for Applicant ID, Approver ID, and Extension Management Manager ID.

[0108] Conversely, on the details page of the person-entity data table, you can query the database to list all "events" where the responsible person ID or participant ID is equal to that person ID.

[0109] (2) Event-to-Event Relationship:

[0110] A large "project" can contain multiple sub-"tasks", and the relationship can be achieved by setting the parent task ID or project ID field in the task table.

[0111] For example, a "recognition" might stem from a "major task performance," so the recognition table can be set with a related task ID field.

[0112] (3) Relationship between things

[0113] Executing a certain "task" may require operations on specific "supplies and equipment." This can be achieved by setting up a specific list field for supply IDs in the task table, or by creating a dedicated "task-supplies table."

[0114] For example, a certain "material receiving and dispatching task" (event) requires the receiving and dispatching of a certain material, which is linked through "material receiving and dispatching records".

[0115] (4) Human-object relationship

[0116] The current manager ID or user ID of a certain "equipment and supplies" points to a person - entity data table.

[0117] 3. Implementation Technology and Visualization Approach

[0118] (1) Backend Database: Since the users are grassroots units, to save costs, ensure flexibility and adaptability, and provide general convenience, Access, a commonly used office application, can be used. If basic conditions permit, a mature and stable relational database (such as MySQL or PostgreSQL) can also be used.

[0119] (2) Front-end visualization:

[0120] Personnel overview: Clicking on a person clearly shows all their responsibilities, projects they participate in, awards they have received, current leave status, and assets they use, forming a complete "portrait".

[0121] Transaction traceability view: Clicking on a task will immediately show all related people, things, and other related events (such as previous tasks and subsequent tasks).

[0122] (3) Information processing: sharing, statistics and "intelligent" algorithms

[0123] Information sharing mechanism

[0124] Unified data platform: Establish a single authoritative source of data, and all business functions (attendance, tasks, duty) read and write data from this platform, eliminating "data silos".

[0125] API Interface: Provides a standardized data interface, allowing other authorized systems (such as the systems of higher-level units or financial systems) to securely obtain or write data through the API, thus enabling cross-system information sharing.

[0126] Push notifications and messages: Based on the "publish-subscribe" model, when current data changes (such as the release of new tasks or notification updates), the system automatically notifies users who have subscribed to the information via in-site messages, app push notifications, SMS, etc.

[0127] Statistical analysis

[0128] Data Warehouse and ETL: Data is periodically extracted from business databases into the data warehouse through the ETL (Extract, Transform, Load) process to optimize the structure for complex analytical queries.

[0129] BI Tool Integration: Integrate or build in BI tools. Administrators can easily create integrations via drag-and-drop.

[0130] Personnel Performance Dashboard: Statistics on individual / department task completion rate, working hours, awards, etc., for multi-dimensional quantitative evaluation.

[0131] Workload analysis: Analyze the total workload and shift frequency of each department and time period to provide a basis for scientific planning.

[0132] Trend forecast charts: Analyze leave application trends, changes in task type distribution, etc.

[0133] "Deep Thinking" algorithm

[0134] "Deep thinking" here mainly refers to building model algorithms based on rules and satisfying multiple constraints to extract deeper value from data.

[0135] (4) Natural Language Processing - for automatic information classification

[0136] It automatically parses user-posted task notifications or files, extracts key information (such as time, location, people, and event type), automatically tags them, and categorizes them into the corresponding "event" or "object" categories. For example, it automatically identifies a notification as an "important notification" and associates it with relevant personnel.

[0137] (5) Predictive Models - Used for Risk Warning and Resource Planning

[0138] Application: Building models based on historical data (such as past task completion times, employee performance, and leave records). For example, analyzing recent potential problems and future work plans within the organization to provide risk warnings for the next stage.

[0139] (6) Optimization Algorithm - Used for Intelligent Scheduling and Resource Allocation

[0140] Under the premise of meeting various constraints (personnel responsibilities, physical condition, professional level, fairness, and scheduling order), the optimal duty roster, duty schedule, or task allocation plan is automatically generated.

[0141] (7) Recommendation system - used for responsibility reminders and task assignment

[0142] Similar to the "You May Like" feature in e-commerce, the system intelligently recommends the most suitable person to handle a task based on their skills, past experience, and current availability, providing support for managers' decision-making.

[0143] The specific implementation process of the personnel and affairs management system and method for grassroots units based on the VBA platform of the present invention is as follows:

[0144] Using VBA as the "glue," Office components such as Access, Excel, and Outlook can be integrated to build a lightweight, portable system for standalone or small LAN environments.

[0145] Database: Access is used as a relational database to store all the core data tables related to "people, things, and events" (personnel table, task table, duty table, material table, etc.) and establish the relationships between them (primary key, foreign key).

[0146] User interface: Excel is used as the main interface for data entry, querying, and dashboard display.

[0147] Communication: Use VBA to call Outlook to automatically send notifications, task reminders, and announcements.

[0148] Logic and Control: Write VBA code in Excel to handle business logic, transfer data between components, and respond to button click events.

[0149] 1. Achieve integration and connection between "people, things, and events".

[0150] Data table design (completed in Access)

[0151] Create the main data table for the requirements in Access, and then create a relational view. Example of a personnel table:

[0152] Tblpersonnel (personnel list)

[0153] PersonID (Primary Key, AutoNumber)

[0154] Name (text)

[0155] Department (Text)

[0156] Position (text)

[0157] ...(Other static information)

[0158] Example of task representation:

[0159] Tbltasks (task list)

[0160] Task ID (primary key, automatically generated)

[0161] Taskname(text)

[0162] Task description (Notes)

[0163] Startdate(date / time)

[0164] Deadline (date / time)

[0165] Status (text, such as not started, in progress, completed)

[0166] Personnel ID (foreign key, associated with Tblpersonnel.Person ID) / / Personnel-person association

[0167] Relatedequipment ID (foreign key, associated with Tblequipment.Equipmient ID) / / Item-to-item association

[0168] By using Access's "Relationships" view, these tables can be connected through primary keys and foreign keys to form a data relationship network.

[0169] 2. Data collection and entry

[0170] Adopting the principle of "collaborative collection and multi-site sharing", specific information is collected collaboratively by multiple terminals according to user management permissions, and then shared and referenced throughout the system.

[0171] The data entry interface is designed using a UserForm form and an Access backend database. We design different "entry worksheets" for different types of data.

[0172] Static information collection (one-time or low-frequency updates)

[0173] This includes collecting basic information such as personnel, families, and supplies. Design a structured Excel template worksheet. Each row should contain one record, and each column should contain one field.

[0174] VBA Logic: The user clicks the "Submit" button on the worksheet. The VBA code iterates through each row in the table. For each row, it constructs an SQL statement: "INSERT INTO tblpersonnel(...) VALUES(...)". The SQL statement is executed via ADO connection to write the data to the Access database. Finally, the input rows in the table are cleared, preparing for the next data entry.

[0175] Dynamic information collection (high-frequency, routine tasks)

[0176] Examples of functions include task posting, announcements, duty rosters, work assignments, and leave / vacation requests. A user-friendly form input interface should be designed, which can be integrated with the information disclosure interface.

[0177] VBA Logic: After the user fills out the form and clicks the button, the VBA code reads the value of each cell. It converts the user-selected "Responsible Person's Name" into the corresponding PersonID. It then constructs and executes the INSERT INTO tblTasks(TaskName, PersonInChargeID, Deadline, ...)VALUES(...) statement.

[0178] 3. Implement related queries and displays using VBA

[0179] In Excel, there is a comprehensive information disclosure window (administrative management window). VBA code is written to enable it to display, input, modify, and query related information.

[0180] Execution logic (taking duty scheduling as an example):

[0181] Step s1: Draw out the duty scheduling function area in the display window interface and design the interface style;

[0182] Step s2: Establish a connection to the Access database;

[0183] Step s3: Set the rules or algorithm model and construct the SQL join query statement;

[0184] Step s4: Perform the query operation and input the results into the scheduling function area.

[0185] This feature can integrate "events" (such as leave requests, business trips, hospitalizations, and missions) that are scattered across different tables but are related to the same person, enabling "deep integration" queries.

[0186] 4. Implement information sharing, statistical analysis, and rule-based logical algorithms.

[0187] Information sharing

[0188] Comprehensive Information Disclosure Section (Excel as the front end): In this system, the comprehensive information disclosure section is the face of the entire system and also the most frequently interacted part. In addition to the disclosure function, this interface will also serve as the business window for administrative duty officers. Under their permissions, they can also use this interface to query, modify, and enter information.

[0189] Create an Excel worksheet titled "Administrative Management Information Disclosure Board" (using a UserForm form as the front-end interface). This worksheet does not directly store data; instead, it serves as a dynamic dashboard that interacts with the backend Access database in real time via VBA.

[0190] Display function: Use the button to trigger VBA, pull data from the database, and populate the table for display.

[0191] Query function: Provides search criteria, VBA constructs dynamic SQL statements to query the database and return the results.

[0192] Modification function: After modifying the data in the public table, VBA is triggered via a button to write the changes back to the database.

[0193] Data entry function: Provides a simple form that triggers VBA via a button to insert new data into the database.

[0194] Use VBA to refresh the database periodically or manually, and retrieve the latest data such as duty rosters, notices, personnel updates, and work schedules from the Access database, then display them in tables and graphs.

[0195] Automatic email notifications (Outlook integration): Use VBA to call Outlook to automatically send notifications, task reminders, and announcements.

[0196] Statistical analysis

[0197] Excel PivotTables + VBA Auto-Refresh: Connect an Access table to Excel as the data source and create a pivot table to generate data.

[0198] Daily Shift Handover Summary Report

[0199] Personnel Task Completion Status Dashboard

[0200] Staff Leave Plan Distribution Map

[0201] Employee Leave Statistics Form

[0202] Use VBA code to automatically refresh these pivot tables.

[0203] "Deep Thinking" Algorithm (Rule-Based Logic Algorithm)

[0204] While implementing complex AI algorithms in a VBA environment is impractical, we can implement rule-based logic to simulate intelligence for handling basic administrative tasks.

[0205] Automatic reminders for job duties and tasks (rules engine)

[0206] The core workflow of this event-driven and state-based VBA module is as follows: system startup / timed trigger -- check the status and management data of all incomplete tasks -- iterate through each task -- does it meet the preset rules? -- if yes, generate a reminder -- determine the recipient -- send the reminder via email / message / public interface, etc. If the task status changes or time progresses, this operation is run again.

[0207] Time-based reminders

[0208] Deadline approaching reminder: If the task status is still "not started" or "in progress" 1 day / hour before the task deadline, the person in charge will be reminded.

[0209] Regular reminders: For tasks with a "weekly" cycle, a reminder will be automatically generated every Monday at 08:00 AM.

[0210] Overdue reminder: If a task exceeds the deadline and is not completed, the person in charge will be reminded once every morning.

[0211] Status-based reminders

[0212] When a task is marked as "blocked", the person in charge of the task will be automatically notified.

[0213] Reminders based on relationships

[0214] Prerequisite task reminder: Task B's prerequisite task is Task A. When Task A is completed, the person in charge of Task B will be automatically reminded, "Task B can begin now."

[0215] Task or resource allocation reminder: The task requires A to distribute a certain material, but the system detects that the quantity of materials managed by A is insufficient, reminds the task leader of resource conflict, and provides a reasonable allocation plan.

[0216] Reminders based on human behavior

[0217] The person in charge, A, has recently fallen ill. When assigning him new tasks, the system automatically reminds the manager to pay attention to his health and suggest that someone else be assigned to the task.

[0218] Person in charge B has recently had several tasks overdue. Now, when assigning new tasks to him, the system will automatically remind the manager to pay attention to his physical and mental health, family situation, and daily behavior.

[0219] Comprehensive quantitative evaluation of personnel

[0220] At its core is a configurable, automated scoring algorithm model that extracts data from various related data tables, calculates scores according to predetermined rules, and finally outputs visualized rankings and reports.

[0221] Quantitative model design

[0222] Indicator definition: Clearly define the dimensions from which personnel performance will be measured. Design a sound framework for the evaluation system.

[0223] Data source: Data for each indicator is extracted from data tables associated with people.

[0224] Scoring rules: The raw data is converted into scores using certain formulas and rules.

[0225] Weighting: The degree of importance of each indicator in the total score.

[0226] Implementation steps

[0227] Data preparation and database query

[0228] The score for each metric needs to be calculated using SQL queries. VBA's role is to dynamically construct and execute these queries.

[0229] Score calculation

[0230] Create a main function that iterates through all indicators and all personnel, summarizing and calculating the final equal scores.

[0231] Results visualization output

[0232] The calculated data can be displayed in Excel in several ways:

[0233] Examples include leaderboards, radar charts, bar charts, and generating PDF reports.

[0234] Functionality optimization

[0235] Dynamic time period: Do not hardcode the date. Automatically retrieve the first day of the month.

[0236] Configurable rules: Standard scores, performance level score lines, etc. are stored in a configuration table, which allows administrators to adjust them at any time without modifying the code.

[0237] Historical records: The results of each evaluation are stored in a historical table for trend analysis.

[0238] Data validity verification: Check the validity of the data before calculation. For example, if someone's total number of tasks is 0, the "completion rate" metric should be marked as "no data" or skipped, rather than receiving 0 points, which is fairer.

[0239] For example, a person's comprehensive evaluation index is divided into four evaluation dimensions: "ideals and beliefs, professional competence, sense of responsibility, and moral character." Each dimension is reflected through multiple aspects of performance. The performance of each aspect is quantified through certain scoring rules and formulas. The weight of each quantified data is adjusted, and a function is created to calculate the score for that dimension. After obtaining the score for each dimension, a certain weight is assigned to each dimension, and another function is created to calculate the individual's comprehensive score.

[0240] For example, loyalty is measured by quantifying data related to people in the database, such as obedience and awareness (democratic evaluation results, number of times praised, number of times criticized, number of times commended, attendance rate in educational activities, and departmental / organizational evaluations), using a percentage system. Based on certain rules, weights are assigned to the numerical values ​​of each aspect of performance, and logical operations are performed according to the rules to obtain a quantitative value that reflects a person's loyalty.

[0241] The other dimensions, apart from differing in their selection of human-related expressions and their different operational rules, are used in the same way.

[0242] A quadrilateral radar chart provides a visual representation of an individual's overall situation.

[0243] The present invention and its embodiments have been described above. This description is not restrictive, and the accompanying drawings are only one embodiment of the present invention; the actual structure is not limited thereto. In conclusion, if those skilled in the art are inspired by this description and design similar structures and embodiments without departing from the spirit of the invention, such designs should fall within the protection scope of the present invention.

Claims

1. A VBA platform-based grassroots unit personnel and transaction management system, characterized in that: The system comprises a data layer, a function layer and a display layer; The data layer is built by a relational database, and comprises a person-entity data table, a matter-entity data table and a thing-entity data table, each of which is provided with a global unique identifier (ID), and the three are associated by an external key or a special relationship table to form a data relationship network which can be traced back to each other; The function layer comprises: a data collection module which provides a structured Excel template and a UserForm window entry interface, writes data into the database by executing SQL statements through VBA, and supports multi-terminal collaborative collection; an association query module which builds SQL join table query statements through VBA, aggregates associated tasks, on-duty, commendation and leave information according to the personnel ID, or traces back to the associated personnel, materials and related matters according to the task ID; an information sharing module which establishes a unified data center, provides a standardized API interface, supports cross-system data interaction, and integrates Outlook to realize automatic information pushing; a statistical analysis module which realizes ETL data extraction through VBA, and generates personnel performance dashboard, work load analysis graph and leave trend prediction graph by combining Excel data pivot table; an intelligent processing module which comprises a duty task reminding engine based on rules, a personnel comprehensive quantitative evaluation algorithm and an intelligent scheduling optimization algorithm, the duty task reminding engine supports time triggering, state triggering and association triggering, and the personnel comprehensive quantitative evaluation algorithm configures multi-dimensional index weight and scoring rules; The display layer takes Excel as a front-end carrier, and comprises a comprehensive information publicity interface and a panoramic view, the comprehensive information publicity interface is divided into on-duty information, on-duty information, personnel dynamics, transaction dynamics, work arrangement and important notice board, and the panoramic view supports displaying associated matter-thing information by clicking the personnel ID, or displaying associated personnel and material information by clicking the task ID.

2. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 1, characterized in that: The person-entity data table comprises static basic attributes of personnel ID, name, department and position, and extended attributes of associated organization ID, skill label and responsibility range; The matter-entity data table comprises a task table, a project table, an on-duty / on-duty table, an award table and a leave table, the task table is provided with a responsible person ID, a participant ID list and an associated material ID list, the on-duty / on-duty table is provided with an on-duty personnel ID and a shift ID, the award table is provided with an award winner ID and an associated task ID, and the leave table is provided with an applicant ID, an approver ID and an extension management responsible person ID; The thing-entity data table comprises a material equipment table, a file data table and a site facility table, the material equipment table is provided with a current management person ID / use person ID, the file data table is provided with a creator ID and an associated matter ID, and the site facility table is provided with a management responsibility person ID.

3. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 2, characterized in that: The relational database is an Access database or a MySQL or PostgreSQL database; when the Access database is used, data interaction between Excel and the Access database is realized through VBA; when the MySQL or PostgreSQL is used, a database connection is established through VBA calling of an ODBC interface.

4. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 3, characterized in that: The workflow of the responsibility task reminding engine is as follows: After system startup or timing triggering, the unfinished task state and management data are checked, the tasks are traversed, and it is judged whether the preset rules are met, if yes, the reminding content is generated, and then the reminding content is pushed to the designated receiver through an email, a system message or a public interface; the preset rule content includes a deadline approaching, an overdue period, a state blockage, a preceding task completion, a resource shortage and personnel health / task overdue history.

5. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 4, characterized in that: The implementation steps of the personnel comprehensive quantitative evaluation algorithm include: Step 1: extracting multi-dimensional evaluation index data; the index data includes ideal belief, business literacy, responsibility and moral conduct dimensions, and the index data is derived from democratic evaluation results, commendation / criticism records, education attendance rates and task completion rates; Step 2: converting the original data into a percentage score according to the preset rule, and configuring the weight for each index data; Step 3: calculating the dimension score and comprehensive score through VBA function programming, wherein the dimension score = Σ(index score * index weight), and the comprehensive score = Σ(dimension score * dimension weight); Step 4: outputting the evaluation results in the form of a ranking list, a radar chart or a PDF report in Excel, and storing the results in a history table for trend analysis.

6. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 5, characterized in that: The VBA logic of the data collection module is as follows: When the user submits the data, the VBA traverses the Excel table row or reads the UserForm window input value, converts the text information into the corresponding entity ID, constructs an INSERT INTO SQL statement, executes the SQL through ADO connection and writes into the database, and then clears the input area after completion.

7. The grass-roots unit personnel and transaction management system based on VBA platform according to claim 6, characterized in that: The VBA interaction logic of the comprehensive information public interface includes: The VBA pulls the latest data from the database and fills in the display table through a button; a search condition input box is provided, the VBA constructs a dynamic SQL query according to the input and returns the result; the data in the display table can be directly modified, and after selection and saving, the VBA executes an UPDATE SQL statement to write the changes back to the database; the VBA is triggered to refresh the data at a fixed time or manually, to ensure the real-time nature of the interface information.

8. A method for managing personnel and affairs of a basic unit based on a VBA platform, characterized in that, The steps include: Step S1: constructing a relational database, defining a person-entity data table, a thing-entity data table and a thing-entity data table, and defining the association relationship among the three, and assigning a globally unique ID to each entity; Step S2: designing an Excel input template and a UserForm window, realizing multi-terminal collaborative collection of static basic data and dynamic transaction data through VBA, automatically completing text information to entity ID conversion and writing into the database during collection; Step S3: Write SQL join table query statements through VBA to realize human-transaction-resource association information query, support aggregation of associated transactions and resources according to personnel ID, or trace associated personnel and resources according to task ID; Step S4: Develop information sharing function based on VBA, establish unified data center, integrate Outlook to realize automatic message pushing, and configure API interface to support cross-system data interaction; Step S5: Realize ETL data extraction through VBA, generate statistical analysis charts combined with Excel pivot table, and write intelligent processing modules based on rules, including duty task reminders, personnel quantitative evaluation, and intelligent scheduling; Step S6: Design comprehensive information publicity interface and panoramic view in Excel, realize real-time data refreshing, query, modification, and visual display through VBA.

Images