技术领域Technical field
本发明涉及电子标签技术领域，具体为一种基于RFID的虚拟公物仓电子标签识别系统。The invention relates to the technical field of electronic tags, specifically an RFID-based electronic tag identification system for virtual public property warehouses.
背景技术Background technique
射频识别(RFID)是Radio Frequency Identification的缩写。Radio Frequency Identification (RFID) is the abbreviation of Radio Frequency Identification.
其原理为阅读器与标签之间进行非接触式的数据通信，达到识别目标的目的。RFID的应用非常广泛，典型应用有动物晶片、汽车晶片防盗器、门禁管制、停车场管制、生产线自动化、物料管理；The principle is to conduct non-contact data communication between the reader and the tag to achieve the purpose of identifying the target. RFID is widely used, and typical applications include animal chips, car chip anti-theft devices, access control, parking lot control, production line automation, and material management;
参考公开号为“CN105160365A”的“基于射频识别技术的仓库物资管理系统”可知，该专利只需将第一耦合器串行接入读写器系统即可完成对仓库物资的盘点和监控，无需人工持手持机对遗漏的物资进行二次盘点，排除了人的主观因素，降低了人力成本和设备成本，提高了系统的可靠性，且第一耦合器的布点灵活可变，能够严防盘点死角；Referring to the "Warehouse Material Management System Based on Radio Frequency Identification Technology" with the publication number "CN105160365A", it can be seen that this patent only needs to serially connect the first coupler to the reader system to complete the inventory and monitoring of warehouse materials, without Manually holding a handheld device to conduct a secondary inventory of missing materials eliminates human subjective factors, reduces labor costs and equipment costs, and improves the reliability of the system. Moreover, the layout of the first coupler is flexible and variable, which can prevent blind spots in inventory ;
但是该专利申请人在应用于虚拟公物仓电子标签应用的过程中，由于缺乏对数据的便捷录入、安全上锁等的配合设计，导致整体系统的应用安全系数降低，因此，需要对以上问题提出一种新的解决方案。However, during the application of electronic tags in virtual public warehouses, the patent applicant lacked convenient design for data entry, safe locking, etc., which resulted in a reduction in the application safety factor of the overall system. Therefore, the above issues need to be addressed. A new solution.
发明内容Contents of the invention
本发明的目的在于提供一种基于RFID的虚拟公物仓电子标签识别系统，以解决背景技术中提出的问题。The purpose of the present invention is to provide an RFID-based virtual public property warehouse electronic tag identification system to solve the problems raised in the background technology.
为实现上述目的，本发明提供如下技术方案：一种基于RFID的虚拟公物仓电子标签识别系统，至少包括以下模块：In order to achieve the above objectives, the present invention provides the following technical solution: an RFID-based virtual public warehouse electronic tag identification system, which at least includes the following modules:
通过在客户端、中间服务器、主服务器构成三层架构，在三层架构的基础上采用C/S架构，数据库至少包括SQLServer2000与DB2中的一个，在三层架构的中间服务器上以后台方式运行负责与客户端进行数据交换的模块和主服务器进行交换的模块，从而构成虚拟公物仓；By forming a three-tier architecture on the client, intermediate server, and main server, a C/S architecture is adopted based on the three-tier architecture. The database includes at least one of SQLServer2000 and DB2, and runs in the background on the intermediate server of the three-tier architecture. The module responsible for exchanging data with the client and the main server, thus forming a virtual public property warehouse;
RFID构成模块，用于对收入所述虚拟公物仓的入仓物品进行标识；An RFID module is used to identify the items stored in the virtual public property warehouse;
所述RFID构成模块包括存储器，所述存储器包括编码器存储器，所述编码器存储器用于对存储的信息进行第一层的编码上锁；The RFID component module includes a memory, and the memory includes an encoder memory, and the encoder memory is used to perform first-level coding and locking of stored information;
RFID读写器模块，用于对存储器内容的写入和读取；RFID reader/writer module, used to write and read memory contents;
RFID读写器接口模块，RFID读写器接口模块用于中间服务器与RFID读写器模块的数据通信，主要有获取RFID数据与虚拟公物仓进行连接；RFID reader interface module, the RFID reader interface module is used for data communication between the intermediate server and the RFID reader module, mainly to obtain RFID data and connect to the virtual public property warehouse;
逻辑读写器映射模块，所述逻辑读写器映射模块用于将多个物理读写器或者读写器的多条天线映射成为一个逻辑读写器；A logical reader/writer mapping module, which is used to map multiple physical readers/writers or multiple antennas of the readers/writers into one logical reader/writer;
RFID数据过滤模块，所述RFID数据过滤模块用于将逻辑读写器映射模块进行初步过滤后的数据进行二次过滤；RFID data filtering module, the RFID data filtering module is used to perform secondary filtering on the data after preliminary filtering by the logical reader-writer mapping module;
数据加密设置模块，所述数据加密设置模块用于完成对过滤后的数据模块进行加密后传入虚拟公物仓，并将导出的数据进行同步加密，避免了登录查看端的获取源数据；Data encryption setting module, the data encryption setting module is used to complete the encryption of the filtered data module and then transfer it to the virtual public warehouse, and to synchronously encrypt the exported data, avoiding the need to log in to the viewing terminal to obtain the source data;
业务系统接口模块和业务系统；Business system interface module and business system;
在虚拟公物仓中，针对仓储物品监控模块的应用需求，采用Zigbee无线传感器网络和有线网络相结合并与局域网、互联网相连的设计思路实现整个虚拟公物仓的入仓物品监控。In the virtual public warehouse, in response to the application needs of the storage item monitoring module, the design idea of combining Zigbee wireless sensor network and wired network and connecting with local area network and Internet is adopted to realize the monitoring of the incoming items in the entire virtual public warehouse.
逻辑读写器映射模块，所述逻辑读写器映射模块用于将多个物理读写器或者读写器的多条天线映射成为一个逻辑读写器，一个逻辑读写器代表了一个有具体含义的数据采集点，而不管该采集点在物理上由多少个读写器和天线组成。它屏蔽了数据采集点的具体实现方式，减少了数据过滤等上层模块与下层数据采集部分的软件耦合度。对于上层业务系统来说，可见的只有逻辑读写器，所以，逻辑读写器映射模块对RFID数据有初步过滤的功能；Logical reader mapping module. The logical reader mapping module is used to map multiple physical readers or multiple antennas of the readers into a logical reader. A logical reader represents a specific meaning data collection point, regardless of how many readers and antennas the collection point physically consists of. It shields the specific implementation of data collection points and reduces the software coupling between upper-layer modules such as data filtering and the lower-layer data collection part. For the upper-layer business system, only logical readers and writers are visible, so the logical reader-writer mapping module has a preliminary filtering function for RFID data;
RFID数据过滤模块，所述RFID数据过滤模块用于将逻辑读写器映射模块进行初步过滤后的数据进行二次过滤，RFID采集的原始数据量非常大，在实际应用中，根据具体的配置不同，每台读写器每秒可以上报数个至数十个不等的电子标签数据，如重复多次扫描同一个电子标签，但其中只有少部分是对用户有意义的、非重复性的数据，这样大量的数据如果不经过去冗等处理而直接上传，将会给整个RFID系统带来很大的负担。因此，系统采用数据采集事件编码的方法对RFID采集的数据进行过滤处理。首先，对电子标签状态的改变进行编码，定义标签出现的状态编码为0，标签状态消失的编码为1；其次，加入计时器机制，对计时器有效时间内的同一标签的状态跳变进行忽略，从而在状态定义和时间维度两个方面对数据进行去重化。该方法能够很好地消除冗余数据，减少上层系统的负荷；RFID data filtering module. The RFID data filtering module is used to perform secondary filtering on the data after preliminary filtering by the logical reader-writer mapping module. The amount of original data collected by RFID is very large. In practical applications, it varies according to the specific configuration. , each reader can report several to dozens of electronic tag data per second, such as scanning the same electronic tag multiple times, but only a small part of it is meaningful and non-repetitive data to the user. , if such a large amount of data is uploaded directly without deduplication and other processing, it will bring a great burden to the entire RFID system. Therefore, the system uses the data collection event encoding method to filter the data collected by RFID. First, code the change in the status of the electronic tag, defining the status code of the tag to appear as 0, and the code of the tag status to disappear as 1; secondly, add a timer mechanism to ignore the status transition of the same tag within the valid time of the timer. , thereby deduplicating the data in terms of state definition and time dimension. This method can effectively eliminate redundant data and reduce the load on the upper-layer system;
数据加密设置模块，所述数据加密设置模块用于完成对过滤后的数据模块进行加密后传入虚拟公物仓，并将导出的数据进行同步加密，避免了登录查看端的获取源数据；Data encryption setting module, the data encryption setting module is used to complete the encryption of the filtered data module and then transfer it to the virtual public warehouse, and to synchronously encrypt the exported data, avoiding the need to log in to the viewing terminal to obtain the source data;
业务系统接口模块和业务系统，用于构成为录入、输出构成独立应用的通道，避免了多个功能间的互相影响；The business system interface module and business system are used to form independent application channels for input and output, avoiding the mutual influence between multiple functions;
在虚拟公物仓中，针对仓储物品监控模块的应用需求，采用Zigbee无线传感器网络和有线网络相结合并与局域网、互联网相连的设计思路实现整个虚拟公物仓的物品监控。。In the virtual public warehouse, in response to the application requirements of the storage item monitoring module, the design idea of combining Zigbee wireless sensor network and wired network and connecting to the local area network and the Internet is used to realize the item monitoring of the entire virtual public warehouse. .
进一步地，所述RFID构成模块还包括天线匹配网络、电源恢复电路、电源稳压电路、反向散射调制电路、解调电路、时钟恢复产生电路、启动信号产生电路和参考源产生电路；Furthermore, the RFID component module also includes an antenna matching network, a power recovery circuit, a power voltage stabilization circuit, a backscatter modulation circuit, a demodulation circuit, a clock recovery generation circuit, a start signal generation circuit and a reference source generation circuit;
所述天线匹配网络为L型匹配；The antenna matching network is L-shaped matching;
所述反向散射调制电路和解调电路至少包括PSK和ASK，PSK用于改变电容值也就是阻抗的虚部，ASK用于改变电阻值也就是阻抗的实部中的一种；The backscatter modulation circuit and demodulation circuit at least include PSK and ASK, PSK is used to change the capacitance value, which is the imaginary part of the impedance, and ASK is used to change the resistance value, which is one of the real parts of the impedance;
所述电源稳压电路用于电源稳压电路主要是用来稳定电荷泵输出的电压，为电路的其他模块以及数字电路提供稳定的电源电压。The power supply voltage stabilizing circuit is mainly used to stabilize the voltage output by the charge pump and provide stable power supply voltage for other modules of the circuit and digital circuits.
进一步地，所述数据加密设置模块至少包括以下模块：加密信息检索模块、线性搜索算法模块、基于关键词的公钥搜索模块、安全索引模块、加锁模块和对应解锁模块。Further, the data encryption setting module at least includes the following modules: an encrypted information retrieval module, a linear search algorithm module, a keyword-based public key search module, a security index module, a locking module and a corresponding unlocking module.
进一步地，所述虚拟公物仓包括系统管理模块、资料存储模块、计量管理模块、三维仓库模块、仓库管理模块和查询统计模块。Further, the virtual public property warehouse includes a system management module, a data storage module, a measurement management module, a three-dimensional warehouse module, a warehouse management module and a query statistics module.
进一步地，所述系统管理模块至少包括单位架构、角色管理、用户管理、数据管理、更改密码、条码设置数据导入和登录退出系统。Further, the system management module at least includes unit structure, role management, user management, data management, changing passwords, barcode setting data import and logging in and out of the system.
进一步地，所述资料存储模块至少包括往来单位、仓库资料、货架资料、存储类别、存储资料和计量单位。Furthermore, the data storage module at least includes business units, warehouse data, shelf data, storage categories, storage data and measurement units.
进一步地，所述计量管理模块至少包括仪器台账、检定计划和送检记录。Further, the measurement management module at least includes an instrument ledger, a calibration plan, and an inspection record.
进一步地，所述三维仓库模块至少包括仓库模型、货架模型和物件虚拟模型。Further, the three-dimensional warehouse module includes at least a warehouse model, a shelf model and an object virtual model.
进一步地，所述仓库管理模块至少包括入库单、借用出库单、借出还入单和货品出库单。Further, the warehouse management module at least includes a warehouse entry note, a borrowing issue note, a loan return note and a goods issue note.
与现有技术相比，本发明的有益效果是：Compared with the prior art, the present invention has the following beneficial effects:
本发明通过在RFID的基础上，通过在标签识别的过程中，完成对虚拟公物仓中的物体存放进行进一步的虚拟录入，构成多端口接入查看的虚拟公物仓，且便于从中进行便捷筛选入仓物品，实现线上线下物品的同步动态调配，并通过在系统中接入对应的存储扫描加密，使得数据被录入和输出的过程中形成加密，从而提高系统的加密性能，大大提高了整体系统的安全系数。On the basis of RFID, the present invention completes further virtual entry of objects stored in the virtual public property warehouse in the process of tag identification, forming a multi-port access and viewing virtual public property warehouse, and facilitates convenient filtering and entry. Warehouse items realize the synchronous and dynamic allocation of online and offline items, and by accessing the corresponding storage scanning encryption in the system, encryption is formed during the data entry and output process, thus improving the encryption performance of the system and greatly improving the overall system. safety factor.
附图说明Description of the drawings
为了更清楚地说明本发明实施例的技术方案，下面将对实施例描述所需要使用的附图作简单地介绍，下面描述中的附图仅仅是本发明的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for describing the embodiments are briefly introduced below. The drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.
图1为本发明RFID标签的示意图；Figure 1 is a schematic diagram of the RFID tag of the present invention;
图2为本发明整体系统的运行示意图。FIG. 2 is a schematic diagram of the operation of the overall system of the present invention.
具体实施方式Detailed ways
下面将结合本发明实施例中的附图，对本发明实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本发明一部分实施例，而不是全部的实施例。The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only some of the embodiments of the present invention, rather than all the embodiments.
请参阅图1和图2，本发明公开了一种基于RFID的虚拟公物仓电子标签识别系统，基于RFID的虚拟公物仓电子标签识别系统至少包括以下模块：Please refer to Figures 1 and 2. The present invention discloses an RFID-based electronic label identification system for virtual public property warehouses. The RFID-based electronic label identification system for virtual public property warehouses at least includes the following modules:
通过在客户端、中间服务器、主服务器构成三层架构，在三层架构的基础上采用C/S架构，数据库至少包括SQLServer2000与DB2中的一个，在三层架构的中间服务器上以后台方式运行负责与客户端进行数据交换的模块和主服务器进行交换的模块，从而构成虚拟公物仓；By forming a three-tier architecture on the client, intermediate server, and main server, a C/S architecture is adopted based on the three-tier architecture. The database includes at least one of SQLServer2000 and DB2, and runs in the background on the intermediate server of the three-tier architecture. The module responsible for exchanging data with the client and the main server exchange modules, thus forming a virtual public warehouse;
RFID构成模块，用于对收入虚拟公物仓的入仓物品进行标识；The RFID module is used to identify the items entering the virtual public warehouse;
RFID构成模块包括存储器，存储器包括编码器存储器，编码器存储器用于对存储的信息进行第一层的编码上锁；The RFID component module includes a memory, and the memory includes an encoder memory. The encoder memory is used to perform first-level coding and locking of stored information;
RFID读写器模块，用于对存储器内容的写入和读取；RFID reader/writer module, used to write and read memory contents;
RFID读写器接口模块，RFID读写器接口模块用于中间服务器与RFID读写器模块的数据通信，主要有获取RFID数据与虚拟公物仓进行连接；RFID reader interface module, the RFID reader interface module is used for data communication between the intermediate server and the RFID reader module, mainly to obtain RFID data and connect to the virtual public property warehouse;
逻辑读写器映射模块，逻辑读写器映射模块用于将多个物理读写器或者读写器的多条天线映射成为一个逻辑读写器，一个逻辑读写器代表了一个有具体含义的数据采集点，而不管该采集点在物理上由多少个读写器和天线组成。它屏蔽了数据采集点的具体实现方式，减少了数据过滤等上层模块与下层数据采集部分的软件耦合度。对于上层业务系统来说，可见的只有逻辑读写器，所以逻辑读写器映射模块对RFID数据有初步过滤的功能；Logical reader mapping module. The logical reader mapping module is used to map multiple physical readers or multiple antennas of a reader into a logical reader. A logical reader represents a specific meaning. A data collection point, regardless of how many readers and antennas the collection point physically consists of. It shields the specific implementation of data collection points and reduces the software coupling between upper-layer modules such as data filtering and the lower-layer data collection part. For the upper-layer business system, only logical readers and writers are visible, so the logical reader-writer mapping module has a preliminary filtering function for RFID data;
RFID数据过滤模块，RFID数据过滤模块用于将逻辑读写器映射模块进行初步过滤后的数据进行二次过滤，RFID采集的原始数据量非常大，在实际应用中，根据具体的配置不同，每台读写器每秒可以上报数个至数十个不等的电子标签数据，如重复多次扫描同一个电子标签，但其中只有少部分是对用户有意义的、非重复性的数据，这样大量的数据如果不经过去冗等处理而直接上传，将会给整个RFID系统带来很大的负担。因此，系统采用数据采集事件编码的方法对RFID采集的数据进行过滤处理。首先，对电子标签状态的改变进行编码，定义标签出现的状态编码为0，标签状态消失的编码为1；其次，加入计时器机制，对计时器有效时间内的同一标签的状态跳变进行忽略，从而，在状态定义和时间维度两个方面对数据进行去重化，该方法能够很好地消除冗余数据，减少上层系统的负荷；RFID data filtering module. The RFID data filtering module is used to perform secondary filtering on the data after preliminary filtering by the logical reader-writer mapping module. The amount of original data collected by RFID is very large. In practical applications, depending on the specific configuration, each Each reader/writer can report several to dozens of electronic tag data per second. For example, the same electronic tag is scanned multiple times, but only a small part of it is meaningful and non-repetitive data to the user. In this way If a large amount of data is uploaded directly without deduplication and other processing, it will bring a great burden to the entire RFID system. Therefore, the system uses the data collection event encoding method to filter the data collected by RFID. First, code the change in the status of the electronic tag, defining the status code of the tag to appear as 0, and the code of the tag status to disappear as 1; secondly, add a timer mechanism to ignore the status transition of the same tag within the valid time of the timer. , thereby deduplicating data in both the status definition and time dimensions. This method can effectively eliminate redundant data and reduce the load on the upper-layer system;
数据加密设置模块，数据加密设置模块用于完成对过滤后的数据模块进行加密后传入虚拟公物仓，并将导出的数据进行同步加密，避免了登录查看端的获取源数据；Data encryption setting module. The data encryption setting module is used to complete the encryption of the filtered data module and then transfer it to the virtual public warehouse, and to synchronously encrypt the exported data, avoiding the need to log in to the viewing terminal to obtain the source data;
业务系统接口模块和业务系统，用于构成为录入、输出构成独立应用的通道，避免了多个功能间的互相影响；The business system interface module and business system are used to form independent application channels for input and output, avoiding the mutual influence between multiple functions;
在虚拟公物仓中，针对仓储物品监控模块的应用需求，采用Zigbee无线传感器网络和有线网络相结合并与局域网、互联网相连的设计思路实现整个虚拟公物仓的物品监控。Zigbee技术具有功耗极低、系统简单、组网方式灵活、成本低、低等待时间等性质，适用于此类监控系统的设计。出于节能的考虑，仓储物品监控模块的数据采集应要求传感器体积小、低功耗、外围电路简单，最好采用不需要信号调理电路的数字式传感器。主控单元在实施例中采用Atmega16L单片机。无线通信模块在实施例中采用CC1000芯片与微控器及一些外围无源元件一起构成；In the virtual public warehouse, in response to the application requirements of the storage item monitoring module, the design idea of combining Zigbee wireless sensor network and wired network and connecting to the local area network and the Internet is used to realize the item monitoring of the entire virtual public warehouse. Zigbee technology has the characteristics of extremely low power consumption, simple system, flexible networking method, low cost, and low waiting time, and is suitable for the design of this type of monitoring system. For energy-saving reasons, the data collection of the warehouse item monitoring module should require sensors with small size, low power consumption, and simple peripheral circuits. It is best to use digital sensors that do not require signal conditioning circuits. The main control unit adopts Atmega16L microcontroller in the embodiment. In the embodiment, the wireless communication module is composed of a CC1000 chip, a microcontroller and some peripheral passive components;
RFID构成模块还包括天线匹配网络、电源恢复电路、电源稳压电路、反向散射调制电路、解调电路、时钟恢复产生电路、启动信号产生电路和参考源产生电路；The RFID component module also includes an antenna matching network, a power recovery circuit, a power supply voltage stabilizing circuit, a backscatter modulation circuit, a demodulation circuit, a clock recovery generation circuit, a startup signal generation circuit and a reference source generation circuit;
天线匹配网络为L型匹配；The antenna matching network is L-shaped matching;
反向散射调制电路和解调电路至少包括PSK和ASK，PSK用于改变电容值也就是阻抗的虚部，ASK用于改变电阻值也就是阻抗的实部中的一种；The backscatter modulation circuit and demodulation circuit at least include PSK and ASK. PSK is used to change the capacitance value, which is the imaginary part of the impedance, and ASK is used to change the resistance value, which is one of the real parts of the impedance;
电源稳压电路用于电源稳压电路主要是用来稳定电荷泵输出的电压，为电路的其他模块以及数字电路提供稳定的电源电压；The power supply voltage regulator circuit is mainly used to stabilize the voltage output by the charge pump and provide a stable power supply voltage for other modules of the circuit and the digital circuit;
数据加密设置模块至少包括以下模块：加密信息检索模块、线性搜索算法模块、基于关键词的公钥搜索模块、安全索引模块、加锁模块和对应解锁模块。The data encryption setting module at least includes the following modules: encrypted information retrieval module, linear search algorithm module, keyword-based public key search module, security index module, locking module and corresponding unlocking module.
加锁模块运行过程为“USE[master]The lock module runs as "USE [master]
GOGO
if object_ID('[sp_EncryptObject]')is not nullif object_ID('[sp_EncryptObject]')is not null
Drop Procedure[sp_EncryptObject]Drop Procedure[sp_EncryptObject]
GoGo
SET ANSI_NULLS ONSET ANSI_NULLS ON
GOGO
SET QUOTED_IDENTIFIER ONSET QUOTED_IDENTIFIER ON
GOGO
CREATE procedure[dbo].[sp_EncryptObject]CREATE procedure[dbo].[sp_EncryptObject]
((
@Object sysname＝'All'@Object sysname='All'
))
asas
beginbegin
set nocount onset nocount on
if@Object<>'All'if@Object<>'All'
beginbegin
if not exists(select 1from sys.objects a where a.object_id＝object_id(@Object)And a.type in('P','V','TR','FN','IF','TF'))beginif not exists(select 1from sys.objects a where a.object_id＝object_id(@Object)And a.type in('P','V','TR','FN','IF','TF') )begin
--SQL Server 2008--SQL Server 2008
raiserror 50001N'无有效的加密对象！加密对象必须是函数，存储过程，视图或触发器。'raiseerror 50001N'No valid encryption object! The encrypted object must be a function, stored procedure, view or trigger. '
--SQL Server 2012--SQL Server 2012
--throw 50001,N'无有效的加密对象！加密对象必须是函数，存储过程，视图或触发器。',1--throw 50001,N'No valid encryption object! The encryption object must be a function, stored procedure, view or trigger. ',1
returnreturn
endend
if exists(select 1from sys.sql_modules a where a.object_id＝object_id(@Object)and a.definition is null)if exists(select 1from sys.sql_modules a where a.object_id＝object_id(@Object)and a.definition is null)
beginbegin
--SQL Server 2008--SQL Server 2008
raiserror 50001N'对象已经加密！'raiserror 50001N'The object has been encrypted! '
--SQL Server 2012--SQL Server 2012
--throw 50001,N'对象已经加密！',1--throw 50001,N' object has been encrypted! ',1
returnreturn
endend
endend
declare@sql nvarchar(max),@C1 nchar(1),@C2 nchar(1),@typedeclare@sql nvarchar(max),@C1 nchar(1),@C2 nchar(1),@type
nvarchar(50),@Replace nvarchar(50)nvarchar(50),@Replace nvarchar(50)
set@C1＝nchar(13)set@C1＝nchar(13)
set@C2＝nchar(10)set@C2＝nchar(10)
declare cur_Objectdeclare cur_Object
cursor forcursor for
select object_name(a.object_id)As ObjectName,a.definitionselect object_name(a.object_id)As ObjectName,a.definition
from sys.sql_modules afrom sys.sql_modules a
inner join sys.objects b on b.object_id＝a.object_idinner join sys.objects b on b.object_id=a.object_id
and b.is_ms_shipped＝0and b.is_ms_shipped=0
and not exists(select 1and not exists(select 1
from sys.extended_properties xfrom sys.extended_properties x
where x.major_id＝b.object_idwhere x.major_id = b.object_id
and x.minor_id＝0and x.minor_id=0
and x.class＝1and x.class=1
and x.name＝'microsoft_database_tools_support'and x.name = 'microsoft_database_tools_support'
))
where b.type in('P','V','TR','FN','IF','TF')where b.type in('P','V','TR','FN','IF','TF')
and(b.name＝@Object or@Object＝'All')and(b.name=@Object or@Object='All')
and b.name<>'sp_EncryptObject'and b.name<>'sp_EncryptObject'
and a.definition is not nulland a.definition is not null
order by Caseorder by Case
when b.type＝'V'then 1when b.type='V' then 1
when b.type＝'TR'then 2when b.type='TR'then 2
when b.type in('FN','IF','TF')then 3when b.type in('FN','IF','TF')then 3
else 4 end,b.create_date,b.object_idelse 4 end,b.create_date,b.object_id
open cur_Objectopen cur_Object
fetch next from cur_Object into@Object,@sqlfetch next from cur_Object into@Object,@sql
while@@fetch_status＝0while@@fetch_status＝0
beginbegin
Begin TryBegin Try
if objectproperty(object_id(@Object),'ExecIsAfterTrigger')＝0 set@Replace＝'As'；else set@Replace＝'For'；if(patindex('％'+@C1+@C2+@Replace+@C1+@C2+'％',@sql)>0)if objectproperty(object_id(@Object),'ExecIsAfterTrigger')＝0 set@Replace＝'As'; else set@Replace＝'For'; if (patindex('%'+@C1+@C2+@Replace+@C1+@C2+ '%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C1+@C2+@Replace+@C1+@C2,@C1+@C2+'Withset@sql＝Replace(@sql,@C1+@C2+@Replace+@C1+@C2,@C1+@C2+'With
Encryption'+@C1+@C2+@Replace+@C1+@C2)Encryption'+@C1+@C2+@Replace+@C1+@C2)
endend
else if(patindex('％'+@C1+@Replace+@C1+'％',@sql)>0)else if(patindex('%'+@C1+@Replace+@C1+'%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C1+@Replace+@C1,@C1+'With Encryption'+@C1+@Replace+@C1)set@sql＝Replace(@sql,@C1+@Replace+@C1,@C1+'With Encryption'+@C1+@Replace+@C1)
endend
else if(patindex('％'+@C2+@Replace+@C2+'％',@sql)>0)else if(patindex('%'+@C2+@Replace+@C2+'%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C2+@Replace+@C2,@C2+'WithEncryption'+@C2+@Replace+@C2)set@sql＝Replace(@sql,@C2+@Replace+@C2,@C2+'WithEncryption'+@C2+@Replace+@C2)
endend
else if(patindex('％'+@C2+@Replace+@C1+'％',@sql)>0)else if(patindex('%'+@C2+@Replace+@C1+'%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C2+@Replace+@C1,@C1+'WithEncryption'+@C2+@Replace+@C1)set@sql＝Replace(@sql,@C2+@Replace+@C1,@C1+'WithEncryption'+@C2+@Replace+@C1)
endend
else if(patindex('％'+@C1+@C2+@Replace+'％',@sql)>0)beginelse if(patindex('%'+@C1+@C2+@Replace+'%',@sql)>0)begin
set@sql＝Replace(@sql,@C1+@C2+@Replace,@C1+@C2+'WithEncryption'+@C1+@C2+@Replace)endset@sql＝Replace(@sql,@C1+@C2+@Replace,@C1+@C2+'WithEncryption'+@C1+@C2+@Replace)end
else if(patindex('％'+@C1+@Replace+'％',@sql)>0)else if(patindex('%'+@C1+@Replace+'%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C1+@Replace,@C1+'With Encryption'+@C1+@Replace)set@sql＝Replace(@sql,@C1+@Replace,@C1+'With Encryption'+@C1+@Replace)
endend
else if(patindex('％'+@C2+@Replace+'％',@sql)>0)else if(patindex('%'+@C2+@Replace+'%',@sql)>0)
beginbegin
set@sql＝Replace(@sql,@C2+@Replace,@C2+'With Encryption'+@C2+@Replace)set@sql＝Replace(@sql,@C2+@Replace,@C2+'With Encryption'+@C2+@Replace)
endend
set@type＝set@type=
casecase
when object_id(@Object,'P')>0then'Proc'when object_id(@Object,'P')>0then'Proc'
when object_id(@Object,'V')>0then'View'when object_id(@Object,'V')>0then'View'
when object_id(@Object,'TR')>0then'Trigger'when object_id(@Object,'TR')>0then'Trigger'
when object_id(@Object,'FN')>0 or object_id(@Object,'IF')>0 orobject_id(@Object,'TF')>0then'Function'endwhen object_id(@Object,'FN')>0 or object_id(@Object,'IF')>0 orobject_id(@Object,'TF')>0then'Function'end
set@sql＝Replace(@sql,'Create'+@type,'Alter'+@type)set@sql＝Replace(@sql,'Create'+@type,'Alter'+@type)
Begin TransactionBegin Transaction
exec(@sql)exec(@sql)
print N'已完成加密对象('+@type+')：'+@Objectprint N'Complete encrypted object ('+@type+'): '+@Object
Commit TransactionCommit Transaction
End TryEnd Try
Begin CatchBegin Catch
Declare@Error nvarchar(2047)Declare@Error nvarchar(2047)
Set@Error＝'Object:'+@Object+@C1+@C2+'Error:'+Error_message()Set@Error＝'Object:'+@Object+@C1+@C2+'Error:'+Error_message()
Rollback TransactionRollback Transaction
print@Errorprint@Error
print@sqlprint@sql
End CatchEnd Catch
fetch next from cur_Object into@Object,@sqlfetch next from cur_Object into@Object,@sql
endend
close cur_Objectclose cur_Object
deallocate cur_Objectdeallocate cur_Object
endend
GoGo
exec sp_ms_marksystemobject'sp_EncryptObject'--标识为系统对象exec sp_ms_marksystemobject'sp_EncryptObject'--identified as a system object
go”go”
所述对应解锁模块的运行过程为“USE[master]The running process of the corresponding unlocking module is "USE[master]
GOGO
if object_ID('[sp_DecryptObject]')is not nullif object_ID('[sp_DecryptObject]')is not null
Drop Procedure[sp_DecryptObject]Drop Procedure [sp_DecryptObject]
GoGo
SET ANSI_NULLS ONSET ANSI_NULLS ON
GOGO
SET QUOTED_IDENTIFIER ONSET QUOTED_IDENTIFIER ON
GOGO
CREATE procedure[dbo].[sp_DecryptObject]CREATE procedure[dbo].[sp_DecryptObject]
((
@Object sysname,--要解密的对象名：函数，存储过程，视图或触发器@Object sysname,--The name of the object to be decrypted: function, stored procedure, view or trigger
@MaxLength int＝4000--评估内容的长度@MaxLength int=4000 - the length of the evaluation content
))
asas
set nocount onset nocount on
if not exists(select 1from sys.objects a where a.object_id＝object_id(@Object)And a.type in('P','V','TR','FN','IF','TF'))beginif not exists(select 1from sys.objects a where a.object_id＝object_id(@Object)And a.type in('P','V','TR','FN','IF','TF') )begin
--SQL Server 2008--SQL Server 2008
raiserror 50001N'有效的对象！要解密的对象必须是函数，存储过程，视图或触发器。'raiserror 50001N'Valid object! The object to be decrypted must be a function, stored procedure, view or trigger. '
--SQL Server 2012--SQL Server 2012
--throw 50001,N'有效的对象！要解密的对象必须是函数，存储过程，视图或触发器。',1--throw 50001,N'valid objects! The object to be decrypted must be a function, stored procedure, view or trigger. ',1
returnreturn
endend
if exists(select 1from sys.sql_modules a where a.object_id＝object_id(@Object)and a.definition is not null)if exists(select 1from sys.sql_modules a where a.object_id＝object_id(@Object)and a.definition is not null)
beginbegin
--SQL Server 2008--SQL Server 2008
raiserror 50001N'对象没有加密！'raiserror 50001N 'Object is not encrypted!'
--SQL Server 2012--SQL Server 2012
--throw 50001,N'有效的对象！要解密的对象必须是函数，存储过程，视图或触发器。',1--throw 50001,N'valid objects! The object to be decrypted must be a function, stored procedure, view or trigger. ',1
returnreturn
endend
declare@sql nvarchar(max)--解密出来的SQL语句declare@sql nvarchar(max)--Decrypted SQL statement
,@imageval nvarchar(max)--加密字符串,@imageval nvarchar(max)--encrypted string
,@tmpStr nvarchar(max)--临时SQL语句,@tmpStr nvarchar(max)--temporary SQL statement
,@tmpStr_imageval nvarchar(max)--临时SQL语句(加密后),@tmpStr_imageval nvarchar(max)--temporary SQL statement (after encryption)
,@type char(2)--对象类型('P','V','TR','FN','IF','TF'),@type char(2)--Object type ('P','V','TR','FN','IF','TF')
,@objectID int--对象ID,@objectID int--object ID
,@i int--While循环使用,@i int--While loop use
,@Oject1 nvarchar(1000),@Oject1 nvarchar(1000)
set@objectID＝object_id(@Object)set@objectID=object_id(@Object)
set@type＝(select a.type from sys.objects a where a.object_id＝@objectID)set@type=(select a.type from sys.objects a where a.object_id=@objectID)
declare@Space4000 nchar(4000)declare@Space4000 nchar(4000)
set@Space4000＝replicate('-',4000)set@Space4000＝replicate('-',4000)
----------------------------------------------------------------------------------------------------------------------- -------------------
--@tmpStr会构造下面的SQL语句--@tmpStr will construct the following SQL statement
--alter trigger Tr_Name on Table_Name with encryption for update asreturn--alter proc Proc_Name with encryption as select 1as col--alter trigger Tr_Name on Table_Name with encryption for update asreturn --alter proc Proc_Name with encryption as select 1as col
--alter view View_Name with encryption as select 1as col--alter view View_Name with encryption as select 1as col
--alter function Fn_Name()returns int with encryption as begin return(0)end--alter function Fn_Name()returns int with encryption as begin return(0)end
------------------------------------------------------------------set------------------------------------------------------------------set
@Oject1＝quotename(object_schema_name(@objectID))+'.'+quotename(@Object)@Oject1＝quotename(object_schema_name(@objectID))+'.'+quotename(@Object)
set@tmpStr＝set@tmpStr＝
casecase
when@type＝'P'then N'Alter Procedure'+@Oject1+'with encryption asselect 1 as column1'when@type＝'P'then N'Alter Procedure'+@Oject1+'with encryption asselect 1 as column1'
when@type＝'V'then N'Alter View'+@Oject1+'with encryption as select 1as column1'when@type＝'V'then N'Alter View'+@Oject1+'with encryption as select 1as column1'
when@type＝'FN'then N'Alter Function'+@Oject1+'()returns int withencryption as begin return(0)end'when@type＝'FN'then N'Alter Function'+@Oject1+'()returns int withencryption as begin return(0)end'
when@type＝'IF'then N'Alter Function'+@Oject1+'()returns table withencryption as return(Select a.name fromwhen@type＝'IF'then N'Alter Function'+@Oject1+'()returns table withencryption as return(Select a.name from
sys.types a)'sys.types a)'
when@type＝'TF'then N'Alter Function'+@Oject1+'()returns@t table(namenvarchar(50))with encryption as begin return end'when@type＝'TF'then N'Alter Function'+@Oject1+'()returns@t table(namenvarchar(50))with encryption as begin return end'
else'Alter Trigger'+@Oject1+'onelse'Alter Trigger'+@Oject1+'on
'+quotename(object_schema_name(@objectID))+'.'+(select Top(1)'+quotename(object_schema_name(@objectID))+'.'+(select Top(1)
quotename(object_name(parent_id))from sys.triggers a where a.object_id＝@objectID)+'with encryption for update as return'。quotename(object_name(parent_id))from sys.triggers a where a.object_id=@objectID)+'with encryption for update as return'.
虚拟公物仓包括系统管理模块、资料存储模块、计量管理模块、三维仓库模块、仓库管理模块和查询统计模块。The virtual public property warehouse includes system management module, data storage module, measurement management module, three-dimensional warehouse module, warehouse management module and query statistics module.
系统管理模块至少包括单位架构、角色管理、用户管理、数据管理、更改密码、条码设置数据导入和登录退出系统。The system management module at least includes unit structure, role management, user management, data management, changing passwords, barcode setting data import and logging in and out of the system.
单位架构用于对公司内部的各个部门进行管理，可以增加部门或对已有部门进行更改等操作。The unit structure is used to manage various departments within the company. You can add departments or make changes to existing departments.
角色管理用于管理员对角色的权限进行管理和对具体的用户的权限进行管理。Role management is used by administrators to manage the permissions of roles and the permissions of specific users.
用户管理用于针对系统中涉及的人员进行管理，分为三类角色：国有资产管理人员、供应商管理人员，政府工作管理人员。User management is used to manage the personnel involved in the system and is divided into three roles: state-owned assets managers, supplier managers, and government work managers.
数据管理用于进行数据备份，将系统中的数据备份到指定路径文件夹中，以防一些人为或者不可预知的原因而造成数据丢失。Data management is used to back up data in the system to a specified path folder to prevent data loss due to human or unpredictable reasons.
更改密码用于主要针对系统使用人员的登录密码进行维护，增强系统的安全性。Password change is used to maintain the login passwords of system users and enhance system security.
条码设置用于通过数据库建立条码与商品之间一一对应的关系，当条码的数据传到计算机上时，由计算机上的应用程序对数据进行操作和处理。Barcode settings are used to establish a one-to-one correspondence between barcodes and commodities through the database. When the barcode data is transmitted to the computer, the data is operated and processed by the application program on the computer.
资料存储模块至少包括往来单位、仓库资料、货架资料、存储类别、存储资料和计量单位。The data storage module at least includes transaction units, warehouse data, shelf data, storage categories, storage data and measurement units.
检定部门主要是对单位的计量设备定期进行检定的部门信息进行操作。The calibration department mainly operates the department information for regular calibration of the unit's measuring equipment.
往来单位用于记录客户、供应商的基础信息。The corresponding unit is used to record basic information of customers and suppliers.
仓库资料用于记录仓库的名称和位置。Warehouse information is used to record the name and location of the warehouse.
货架资料用于辅助构成系统需要的模拟三维仓库，货架资料的设置就是为了确定设备在仓库中的存放位置。The shelf data is used to assist in constructing the simulated three-dimensional warehouse required by the system. The setting of the shelf data is to determine the storage location of the equipment in the warehouse.
存储类别用于记录存储类别的基本资料。The storage class is used to record the basic information of the storage class.
存储资料用于记录设备的品名、规格、型号、类别等有关资料，便于在以后发生入库、借用时直接引用。The storage data is used to record the name, specification, model, category and other relevant information of the equipment, so as to facilitate direct reference when it is put into storage or borrowed in the future.
计量单位用于对货品的计量单位进行设置或管理。Unit of measurement is used to set or manage the unit of measurement of goods.
计量管理模块至少包括仪器台帐、检定计划和送检记录。The measurement management module at least includes instrument ledger, calibration plan and inspection record.
检定计划用于设定仓库中的存放物体需要定期或不定期进行检定，系统提供三种计划方式：年度计划、季度计划和月度计划。The verification plan is used to set the objects stored in the warehouse to be verified regularly or irregularly. The system provides three planning methods: annual plan, quarterly plan and monthly plan.
送检记录用于需要检定的存储物品清单通过管理员审批后到相关的检定部门进行检定，检定结束后必须将存放物体的检定情况进行登记操作，以便于查询和管理。Inspection submission records are used to submit the list of stored items that need to be verified to the relevant verification department for verification after being approved by the administrator. After the verification is completed, the verification status of the stored objects must be registered for easy query and management.
计量器具台帐用于对所有的计量仪器设备信息进行查询，系统提供较多的组合方便用户操作The measuring instrument ledger is used to query information on all measuring instruments and equipment. The system provides more combinations for user convenience.
三维仓库模块至少包括仓库模型、货架模型和物件虚拟模型。The three-dimensional warehouse module at least includes warehouse models, shelf models and object virtual models.
仓库管理模块至少包括入库单、借用出库单、借出还入单和货品出库单。The warehouse management module at least includes the warehouse receipt note, the borrowing issue note, the loan return note and the goods issue note.
对于本领域技术人员而言，显然本发明不限于上述示范性实施例的细节，而且在不背离本发明的精神或基本特征的情况下，能够以其他的具体形式实现本发明。因此，无论从哪一点来看，均应将实施例看作是示范性的，而且是非限制性的，本发明的范围由所附权利要求而不是上述说明限定，因此旨在将落在权利要求的等同要件的含义和范围内的所有变化囊括在本发明内。不应将权利要求中的任何附图标记视为限制所涉及的权利要求。It is obvious to those skilled in the art that the present invention is not limited to the details of the above-described exemplary embodiments, and that the present invention can be implemented in other specific forms without departing from the spirit or essential characteristics of the present invention. Therefore, the embodiments should be regarded as illustrative and non-restrictive from any point of view, and the scope of the present invention is defined by the appended claims rather than the above description, and it is therefore intended that all claims falling within the claims All changes within the meaning and scope of equivalent elements are included in the present invention. Any reference signs in the claims shall not be construed as limiting the claim in question.
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310379634.1ACN117787316A (en) | 2023-04-11 | 2023-04-11 | RFID-based virtual public warehouse electronic tag identification system |
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310379634.1ACN117787316A (en) | 2023-04-11 | 2023-04-11 | RFID-based virtual public warehouse electronic tag identification system |
| Publication Number | Publication Date |
|---|---|
| CN117787316Atrue CN117787316A (en) | 2024-03-29 |
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310379634.1APendingCN117787316A (en) | 2023-04-11 | 2023-04-11 | RFID-based virtual public warehouse electronic tag identification system |
| Country | Link |
|---|---|
| CN (1) | CN117787316A (en) |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100289627A1 (en)* | 2005-08-19 | 2010-11-18 | Adasa Inc. | Fully Secure Item-Level Tagging |
| CN202433964U (en)* | 2012-01-18 | 2012-09-12 | 沈海峰 | Enterprise intelligent logistic management system |
| CN103136703A (en)* | 2012-12-27 | 2013-06-05 | 中州大学 | Method and system for statistics and process of tourist density distribution |
| CN103489081A (en)* | 2013-10-14 | 2014-01-01 | 深圳市腾邦物流股份有限公司 | Intelligent wine cellar management system |
| CN206497508U (en)* | 2017-01-23 | 2017-09-15 | 西安工程大学 | A Campus Safety Supervision System Based on RFID Technology |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20100289627A1 (en)* | 2005-08-19 | 2010-11-18 | Adasa Inc. | Fully Secure Item-Level Tagging |
| CN202433964U (en)* | 2012-01-18 | 2012-09-12 | 沈海峰 | Enterprise intelligent logistic management system |
| CN103136703A (en)* | 2012-12-27 | 2013-06-05 | 中州大学 | Method and system for statistics and process of tourist density distribution |
| CN103489081A (en)* | 2013-10-14 | 2014-01-01 | 深圳市腾邦物流股份有限公司 | Intelligent wine cellar management system |
| CN206497508U (en)* | 2017-01-23 | 2017-09-15 | 西安工程大学 | A Campus Safety Supervision System Based on RFID Technology |
| Title |
|---|
| ENDV；: ""sql存储过程加密和解密(MSSQL)"", pages 1 - 13, Retrieved from the Internet <URL:https://www.cnblogs.com/endv/p/8336635.html>* |
| Publication | Publication Date | Title |
|---|---|---|
| CN1955998B (en) | For the system and method for visualizing auto-id data | |
| CN104200245B (en) | A kind of electric power asset whole life-cycle assets quick counting method | |
| CN102903033A (en) | File automation management system based on RFID electronic tags | |
| Liu et al. | Internet of Things based acquisition system of industrial intelligent bar code for smart city applications | |
| CN101957938A (en) | Article management system and article management method | |
| CN102693440A (en) | Electronized dormitory antitheft system base on RFID | |
| CN112634547A (en) | RFID intelligent compact shelf management system based on Internet of things | |
| CN111539677A (en) | RFID fixed asset management system and method based on block chain | |
| Jing et al. | Research and design of the intelligent inventory management system based on RFID | |
| CN106886738A (en) | Substation equipment intelligent management system based on Quick Response Code identification | |
| Du | Logistics and warehousing intelligent management and optimization based on radio frequency identification technology | |
| CN204463200U (en) | A safety tool management system | |
| CN113850553A (en) | An RFID-based item data collection and intelligent warehouse management system | |
| CN211654321U (en) | RFID asset management system | |
| CN202720682U (en) | Inventory Material Management System of Electric Power Department Based on Radio Frequency Identification Technology | |
| Liu et al. | Drug distribution management system based on IoT. | |
| KR20200088003A (en) | Inventory control system based on nfc and mobile app and its method | |
| CN117787316A (en) | RFID-based virtual public warehouse electronic tag identification system | |
| Zhang et al. | Safety Traceability System of Characteristic Food Based on RFID and EPC Internet of Things. | |
| Wang et al. | Logistics management system based on permissioned blockchains and RFID technology | |
| CN118570926A (en) | Article tool intelligent borrowing and returning device and management system based on RFID technology | |
| Liu et al. | [Retracted] Design of Logistics Intelligent Warehouse Management System Based on Radio Frequency Identification Technology for 5G | |
| CN109658313A (en) | A kind of E-government's purchasing management platform based on research and development control technology | |
| CN116631119A (en) | An intelligent tool management system for RFID Internet of Things | |
| CN115238842A (en) | Method for making coding rule of radio frequency technology electronic tag |
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication | ||
| WD01 | Invention patent application deemed withdrawn after publication | Application publication date:20240329 |