/*
Navicat SQLite Data Transfer

Source Server         : PatrolCSDB
Source Server Version : 30808
Source Host           : :0

Target Server Type    : SQLite
Target Server Version : 30000
File Encoding         : 65001

Date: 2016-08-08 10:12:12
*/

PRAGMA foreign_keys = OFF;

-- ----------------------------
-- Table structure for cards
-- ----------------------------
DROP TABLE IF EXISTS "cards";
CREATE TABLE "cards" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"CardID"  varchar (24),
"CardName"  varchar (60),
"Removed"  integer,
"Comments"  memo
);

-- ----------------------------
-- Table structure for checkitemforspot
-- ----------------------------
DROP TABLE IF EXISTS "checkitemforspot";
CREATE TABLE "checkitemforspot" (
"RouteCheckItemID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"RouteID"  integer,
"LocusID"  integer,
"CheckTableID"  integer,
"ItemType"  integer,
"Comments"  memo,
"DefaultValue"  float,
"MaxValue"  float,
"MinValue"  float,
"RouteUnitID"  integer,
"Orders"  integer,
"ReferenceValue"  integer
);

-- ----------------------------
-- Table structure for checkitemsforroute
-- ----------------------------
DROP TABLE IF EXISTS "checkitemsforroute";
CREATE TABLE "checkitemsforroute" (
"CheckTableID"  integer,
"RouteID"  integer,
"ItemID"  integer
);

-- ----------------------------
-- Table structure for crashrecordlist
-- ----------------------------
DROP TABLE IF EXISTS "crashrecordlist";
CREATE TABLE "crashrecordlist" (
"DeviceID"  varchar (50),
"CrashTime"  datetime
);

-- ----------------------------
-- Table structure for devicebasicdatainfo
-- ----------------------------
DROP TABLE IF EXISTS "devicebasicdatainfo";
CREATE TABLE "devicebasicdatainfo" (
"DeviceID"  varchar (150),
"ItemIndex"  integer,
"ItemName"  memo,
"ItemType"  integer
);

-- ----------------------------
-- Table structure for eventcardinfo
-- ----------------------------
DROP TABLE IF EXISTS "eventcardinfo";
CREATE TABLE "eventcardinfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"EventCardNo"  varchar (48),
"EventCardName"  varchar (60),
"DelFlag"  integer,
"Remarks"  memo
);

-- ----------------------------
-- Table structure for exceptiontable
-- ----------------------------
DROP TABLE IF EXISTS "exceptiontable";
CREATE TABLE "exceptiontable" (
"ExceptionID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"ExceptionName"  memo
);

-- ----------------------------
-- Table structure for fingeranduserbindinfo
-- ----------------------------
DROP TABLE IF EXISTS "fingeranduserbindinfo";
CREATE TABLE "fingeranduserbindinfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"FingerIndex"  integer,
"UserID"  integer,
"DeviceID"  memo,
"FingerFileName"  memo
);

-- ----------------------------
-- Table structure for fingerdata
-- ----------------------------
DROP TABLE IF EXISTS "fingerdata";
CREATE TABLE "fingerdata" (
"FingerDataID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"FingerData"  blob,
"MeterID"  memo,
"FingerListID"  integer,
"UserID"  integer,
"FingerHexID"  varchar (60)
);

-- ----------------------------
-- Table structure for global
-- ----------------------------
DROP TABLE IF EXISTS "global";
CREATE TABLE "global" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"UserID"  memo,
"UserName"  memo,
"MeterImg"  memo,
"MeterID"  memo,
"LastLogin"  integer,
"LastTime"  datetime
);

-- ----------------------------
-- Table structure for itemteam
-- ----------------------------
DROP TABLE IF EXISTS "itemteam";
CREATE TABLE "itemteam" (
"TeamID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"TeamName"  memo,
"ParentID"  integer,
"Orders"  integer,
"Remark"  memo
);

-- ----------------------------
-- Table structure for logictable
-- ----------------------------
DROP TABLE IF EXISTS "logictable";
CREATE TABLE "logictable" (
"LogicID"  integer,
"LogicName"  varchar (150),
"TeamID"  integer
);

-- ----------------------------
-- Table structure for logicteam
-- ----------------------------
DROP TABLE IF EXISTS "logicteam";
CREATE TABLE "logicteam" (
"TeamID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"TeamName"  memo,
"ParentID"  integer,
"Orders"  integer,
"Remark"  memo
);

-- ----------------------------
-- Table structure for menufunctioninfo
-- ----------------------------
DROP TABLE IF EXISTS "menufunctioninfo";
CREATE TABLE "menufunctioninfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"FuncName"  varchar (150),
"ButtonID"  varchar (150),
"JSFuncName"  varchar (150),
"MenuID"  integer,
"IconCls"  varchar (60)
);

INSERT INTO [menufunctioninfo]([FuncName], [ButtonID], [JSFuncName], [MenuID], [IconCls]) VALUES
('Edit', NULL, NULL, 2, NULL), ('Delete', NULL, NULL, 2, NULL),('Search', NULL, NULL, 2, NULL),
('From_The_Removed_Card_To_Add', NULL, NULL, 2, NULL),('Export_PDF', NULL, NULL, 2, NULL),
('Export_Excel', NULL, NULL, 2, NULL), ('Import_Excel', NULL, NULL, 2, NULL),('From_The_Device_To_Add', NULL, NULL, 2, NULL),
('Add', NULL, NULL, 3, NULL), ('Edit', NULL, NULL, 3, NULL), ('Delete', NULL, NULL, 3, NULL),
('Search', NULL, NULL, 3, NULL), ('Add', NULL, NULL, 4, NULL), ('Edit', NULL, NULL, 4, NULL),
('Delete', NULL, NULL, 4, NULL), ('Search', NULL, NULL, 4, NULL), ('Add', NULL, NULL, 5, NULL),
('Edit', NULL, NULL, 5, NULL), ('Delete', NULL, NULL, 5, NULL), ('Add_Address', NULL, NULL, 5, NULL),
('Delete_Address', NULL, NULL, 5, NULL), ('Search', NULL, NULL, 5, NULL), 
('Move_Up_Address', NULL, NULL, 5, NULL), ('Move_Down_Address', NULL, NULL, 5, NULL),
('Add_Event', NULL, NULL, 5, NULL), ('Delete_Event', NULL, NULL, 5, NULL), ('Move_Up_Event', NULL, NULL, 5, NULL), 
('Move_Down_Event', NULL, NULL, 5, NULL), ('Add_Global_Event', NULL, NULL, 5, NULL), ('Delete_Global_Event', NULL, NULL, 5, NULL), 
('Move_Up_Global_Event', NULL, NULL, 5, NULL), ('Move_Down_Global_Event', NULL, NULL, 5, NULL), ('Add_Item', NULL, NULL, 5, NULL), 
('Delete_Item', NULL, NULL, 5, NULL), ('Move_Up_Item', NULL, NULL, 5, NULL), ('Move_Down_Item', NULL, NULL, 5, NULL),
('Add_Logical_Options', NULL, NULL, 5, NULL), ('Delete_Logical_Options', NULL, NULL, 5, NULL), ('Move_Up_Logical_Options', NULL, NULL, 5, NULL),
('Move_Down_Logical_Options', NULL, NULL, 5, NULL), ('Edit_Number_Item', NULL, NULL, 5, NULL), ('SetDefault', NULL, NULL, 5, NULL), 
('Add_Team', NULL, NULL, 6, NULL), ('Edit_Team', NULL, NULL, 6, NULL), ('Delete_Team', NULL, NULL, 6, NULL),
('Add', NULL, NULL, 6, NULL), ('Delete', NULL, NULL, 6, NULL), ('Search', NULL, NULL, 6, NULL),
('Add', NULL, NULL, 7, NULL), ('Edit', NULL, NULL, 7, NULL), ('Delete', NULL, NULL, 7, NULL), 
('Search', NULL, NULL, 7, NULL), ('Add', NULL, NULL, 8, NULL), ('Edit', NULL, NULL, 8, NULL), 
('Delete', NULL, NULL, 8, NULL), ('Search', NULL, NULL, 10, NULL), ('Export_PDF', NULL, NULL, 10, NULL),
('Export_Excel', NULL, NULL, 10, NULL), ('Print', NULL, NULL, 10, NULL), ('Search', NULL, NULL, 11, NULL),
('Export_PDF', NULL, NULL, 11, NULL), ('Export_Excel', NULL, NULL, 11, NULL), ('Print', NULL, NULL, 11, NULL),
('Search', NULL, NULL, 12, NULL), ('Export_PDF', NULL, NULL, 12, NULL), ('Export_Excel', NULL, NULL, 12, NULL),
('Print', NULL, NULL, 12, NULL), ('Search', NULL, NULL, 13, NULL), ('Export_PDF', NULL, NULL, 13, NULL),
('Export_Excel', NULL, NULL, 13, NULL), ('Print', NULL, NULL, 13, NULL), ('Add_User', NULL, NULL, 15, NULL), 
('Edit_User', NULL, NULL, 15, NULL), ('Delete_User', NULL, NULL, 15, NULL), ('Search', NULL, NULL, 15, NULL),
('Sure', NULL, NULL, 16, NULL), ('Save', NULL, NULL, 17, NULL), ('Clear', NULL, NULL, 17, NULL), 
('Search', NULL, NULL, 21, NULL), ('Export_PDF', NULL, NULL, 21, NULL), ('Export_Excel', NULL, NULL, 21, NULL),('Search', NULL, NULL, 23, NULL), ('Export_PDF', NULL, NULL, 23, NULL), ('Export_Excel', NULL, NULL, 23, NULL),('UnknowCard', NULL, NULL, 2, NULL);

-- ----------------------------
-- Table structure for menuinfo
-- ----------------------------
DROP TABLE IF EXISTS "menuinfo";
CREATE TABLE "menuinfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"MenuName"  varchar (150),
"MenuType"  integer,
"ParentID"  integer,
"IconCls"  varchar (30),
"Url"  varchar (150)
);

INSERT INTO [menuinfo]([ID], [MenuName], [MenuType], [ParentID], [IconCls], [Url]) VALUES
(1, 'Resource_Management', 0, 0, NULL, NULL), (2, 'Card_Management', 1, 1, NULL, NULL),
(3, 'Project_Management', 1, 1, NULL, NULL), (4, 'Event_Management', 1, 1, NULL, NULL), 
(5, 'Line_Management', 1, 1, NULL, NULL), (6, 'Team_Management', 1, 1, NULL, NULL),
(7, 'Device_Management', 1, 1, NULL, NULL), (8, 'Plan_Management', 1, 1, NULL, NULL), 
(9, 'Report_Management', 0, 0, NULL, NULL), (10, 'Original_Data', 1, 9, NULL, NULL),
(11, 'Data_Query', 1, 9, NULL, NULL), (12, 'Original_Data_Statistics', 1, 9, NULL, NULL), 
(13, 'Data_Summary_Table', 1, 9, NULL, NULL), (14, 'System_Management', 0, 0, NULL, NULL),
(15, 'User_Management', 1, 14, NULL, NULL), (16, 'The_Reset_Operation', 1, 14, NULL, NULL), 
(17, 'Company_Information', 1, 14, NULL, NULL), (18, 'Test_Data', 1, 1, NULL, NULL), 
(19, 'Data_Communication', 0, 0, NULL, NULL), (20, 'Start_The_Communication', 1, 19, NULL, NULL), 
(21, 'Anti-throw_Record', 1, 9, NULL, NULL), (22, 'Data_Backup', 1, 14, NULL, NULL),
(23, 'Data_Batch', 1, 9, NULL, NULL);

-- ----------------------------
-- Table structure for meters
-- ----------------------------
DROP TABLE IF EXISTS "meters";
CREATE TABLE "meters" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"DeviceRealID"  varchar (150),
"MeterID"  varchar (150),
"RouteID"  integer,
"SN"  memo,
"MeterType"  memo,
"Comments"  memo,
"TimeZone"  integer,
"CompanyName"  memo,
"ShortName"  memo,
"MeterImg"  memo,
"UserCardID"  integer
);

-- ----------------------------
-- Table structure for operators
-- ----------------------------
DROP TABLE IF EXISTS "operators";
CREATE TABLE "operators" (
"OperatorID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"OperatorName"  varchar (50),
"PassWord"  varchar (100),
"OperatorCate"  integer,
"CreatorID"  integer,
"Remarks"  varchar (255)
);

INSERT INTO operators([OperatorName], [PassWord], [OperatorCate], [CreatorID]) VALUES('admin', 'B872A7BF894B16B1', 0, 1);

-- ----------------------------
-- Table structure for origin
-- ----------------------------
DROP TABLE IF EXISTS "origin";
CREATE TABLE "origin" (
"UnsionID"  varchar(200),
"OriginID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"CardID"  varchar (60),
"PatrolMan"  varchar (60),
"Time"  datetime,
"ExceptionID"  varchar (1000),
"MeterID"  varchar (150),
"RouteID"  integer,
"MachineIndex"  integer,
"Comments"  memo,
"UserID"  float,
"p1"  varchar (60),
"p2"  varchar (60),
"p3"  varchar (60),
"p4"  varchar (60),
"Au1"  integer,
"Au2"  integer,
"Au3"  integer,
"Au4"  integer,
"isAgent"  integer,
"EventName" varchar(1000),
"BatchNo" INT DEFAULT 0
);

-- ----------------------------
-- Table structure for origindata
-- ----------------------------
DROP TABLE IF EXISTS "origindata";
CREATE TABLE "origindata" (
"UnsionID"  varcahr(200),
"RouteID"  float,
"ItemID"  integer,
"PatrolData"  float
);

-- ----------------------------
-- Table structure for patrolrecordpictures
-- ----------------------------
DROP TABLE IF EXISTS "patrolrecordpictures";
CREATE TABLE "patrolrecordpictures" (
"OriginPicID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"UnsionID"  memo,
"PictureName"  memo
);

-- ----------------------------
-- Table structure for patrolteam
-- ----------------------------
DROP TABLE IF EXISTS "patrolteam";
CREATE TABLE "patrolteam" (
"TeamID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"TeamName"  memo,
"ParentID"  integer,
"Orders"  integer,
"Remark"  memo
);

INSERT INTO [patrolteam] ([TeamName], [ParentID], [Orders], [Remark]) VALUES ('RootNode', 0, NULL, NULL);

-- ----------------------------
-- Table structure for patrolteammember
-- ----------------------------
DROP TABLE IF EXISTS "patrolteammember";
CREATE TABLE "patrolteammember" (
"PatrolTeamMemberID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"PatrolTeamID"  integer,
"Orders"  integer,
"CardID"  integer
);

-- ----------------------------
-- Table structure for planinfo
-- ----------------------------
DROP TABLE IF EXISTS "planinfo";
CREATE TABLE "planinfo" (
"PlanID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"RouteID"  integer,
"PlanName"  memo,
"StartDate"  datetime,
"EndDate"  datetime DEFAULT '=Null',
"PatrolModel"  integer,
"AllowErrorTime"  integer,
"LateErrorTime"  integer,
"PatrolUserModel"  integer
);

-- ----------------------------
-- Table structure for planpatroldaydetail
-- ----------------------------
DROP TABLE IF EXISTS "planpatroldaydetail";
CREATE TABLE "planpatroldaydetail" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"PlanID"  integer,
"DayNo"  integer
);

-- ----------------------------
-- Table structure for planpatrolteaminfo
-- ----------------------------
DROP TABLE IF EXISTS "planpatrolteaminfo";
CREATE TABLE "planpatrolteaminfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"PlanID"  integer,
"TimeSectionID"  integer,
"PatrolTeamID"  integer,
"PatrolTimes"  integer,
"Orders"  integer
);

-- ----------------------------
-- Table structure for planpatroluserinfo
-- ----------------------------
DROP TABLE IF EXISTS "planpatroluserinfo";
CREATE TABLE "planpatroluserinfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"PlanID"  integer,
"TimeSectionID"  integer,
"PatrolUserID"  integer,
"PatrolTimes"  integer,
"Orders"  integer
);

-- ----------------------------
-- Table structure for plantimesectioninfo
-- ----------------------------
DROP TABLE IF EXISTS "plantimesectioninfo";
CREATE TABLE "plantimesectioninfo" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"PlanID"  integer,
"SpecificDayID"  integer,
"StartTime"  varchar (60),
"EndTime"  varchar (60),
"WorkTime"  integer,
"RestTime"  integer
);

-- ----------------------------
-- Table structure for route
-- ----------------------------
DROP TABLE IF EXISTS "route";
CREATE TABLE "route" (
"RouteID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"RouteName"  varchar (186),
"ParentID"  integer,
"Removed"  integer,
"Comments"  memo,
"Version"  integer
);

INSERT INTO route ([RouteName], [ParentID], [Removed], [Comments], [Version]) VALUES ('RootNode', 0, 0, '', 0);

-- ----------------------------
-- Table structure for routeexceptions
-- ----------------------------
DROP TABLE IF EXISTS "routeexceptions";
CREATE TABLE "routeexceptions" (
"RouteExceptionID"  integer,
"RouteID"  integer,
"ExceptionID"  integer
);

-- ----------------------------
-- Table structure for routelogicitems
-- ----------------------------
DROP TABLE IF EXISTS "routelogicitems";
CREATE TABLE "routelogicitems" (
"RouteLogicItemID"  integer,
"RouteID"  integer,
"LogicID"  integer
);

-- ----------------------------
-- Table structure for routesetting
-- ----------------------------
DROP TABLE IF EXISTS "routesetting";
CREATE TABLE "routesetting" (
"SetID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"RouteID"  integer,
"LocusID"  integer,
"Orders"  integer,
"isFingerCheck"  integer
);

-- ----------------------------
-- Table structure for routeunit
-- ----------------------------
DROP TABLE IF EXISTS "routeunit";
CREATE TABLE "routeunit" (
"RouteUnitID"  integer,
"UnitID"  integer,
"Orders"  integer,
"RouteID"  integer
);

-- ----------------------------
-- Table structure for spotexceptions
-- ----------------------------
DROP TABLE IF EXISTS "spotexceptions";
CREATE TABLE "spotexceptions" (
"RouteID"  integer,
"SpotID"  integer,
"RouteExceptionID"  integer,
"Orders"  integer
);

-- ----------------------------
-- Table structure for spotoptionitemgroup
-- ----------------------------
DROP TABLE IF EXISTS "spotoptionitemgroup";
CREATE TABLE "spotoptionitemgroup" (
"SpotOptionItemGroupID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"CheckTableID"  integer,
"SpotID"  integer,
"RouteID"  integer,
"Orders"  integer,
"RouteLogicItemID"  integer
);

-- ----------------------------
-- Table structure for unittable
-- ----------------------------
DROP TABLE IF EXISTS "unittable";
CREATE TABLE "unittable" (
"UnitID"  integer,
"UnitName"  varchar (150),
"TeamID"  integer
);

-- ----------------------------
-- Table structure for unitteam
-- ----------------------------
DROP TABLE IF EXISTS "unitteam";
CREATE TABLE "unitteam" (
"TeamID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"TeamName"  memo,
"ParentID"  integer,
"Orders"  integer,
"Remark"  memo
);

-- ----------------------------
-- Table structure for userfunctionauthority
-- ----------------------------
DROP TABLE IF EXISTS "userfunctionauthority";
CREATE TABLE "userfunctionauthority" (
"ID"  integer PRIMARY KEY AUTOINCREMENT,
"UserID"  integer,
"FuncID"  integer
);

-- ----------------------------
-- Table structure for usermenuauthority
-- ----------------------------
DROP TABLE IF EXISTS "usermenuauthority";
CREATE TABLE "usermenuauthority" (
"ID"  integer PRIMARY KEY AUTOINCREMENT,
"UserID"  integer,
"MenuID"  integer
);

-- ----------------------------
-- Table structure for userpatrolteamauthority
-- ----------------------------
DROP TABLE IF EXISTS "userpatrolteamauthority";
CREATE TABLE "userpatrolteamauthority" (
"ID"  integer PRIMARY KEY AUTOINCREMENT,
"UserID"  integer,
"PatrolTeamID"  integer
);

-- ----------------------------
-- Table structure for userrouteauthority
-- ----------------------------
DROP TABLE IF EXISTS "userrouteauthority";
CREATE TABLE "userrouteauthority" (
"ID"  integer PRIMARY KEY AUTOINCREMENT,
"UserID"  integer,
"RouteID"  integer
);

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS "users";
CREATE TABLE "users" (
"UserID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"CardID"  varchar (24),
"UserName"  varchar (45),
"Remark"  memo,
"Removed"  integer,
"Comments"  memo
);

INSERT INTO users(CardID, UserName, Remark, Removed, Comments) VALUES ('0FFFFFFF', 'admin', '', 0, '');

-- ----------------------------
-- Table structure for usertoken
-- ----------------------------
DROP TABLE IF EXISTS "usertoken";
CREATE TABLE "usertoken" (
"TokenID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"TokenValue"  memo,
"UserName"  memo,
"TokenState"  integer,
"CreateDate"  datetime,
"ClientType"  integer
);

DROP TABLE IF EXISTS "UnknownCards";
CREATE TABLE "UnknownCards" (
"ID"  integer PRIMARY KEY ASC AUTOINCREMENT,
"CardID"  varchar (24)
);

DROP TABLE IF EXISTS "DeviceRouteVersion";
CREATE TABLE "DeviceRouteVersion" (
"ID"  INTEGER PRIMARY KEY ASC AUTOINCREMENT,
"RouteID" INTEGER NOT NULL ,
"MeterID" VARCHAR(50) NOT NULL ,
"OldVersion" INTEGER NOT NULL ,
"NewVersion" INTEGER NOT NULL
)
;

-- ----------------------------
-- Auto increment value for userfunctionauthority
-- ----------------------------
-- UPDATE sqlite_sequence SET seq = 8760 WHERE name = "userfunctionauthority";

-- ----------------------------
-- Auto increment value for usermenuauthority
-- ----------------------------
-- UPDATE sqlite_sequence SET seq = 6 WHERE name = "usermenuauthority";

-- ----------------------------
-- Auto increment value for userpatrolteamauthority
-- ----------------------------
-- UPDATE sqlite_sequence SET seq = 9 WHERE name = "userpatrolteamauthority";

-- ----------------------------
-- Auto increment value for userrouteauthority
-- ----------------------------
-- UPDATE sqlite_sequence SET seq = 13 WHERE name = "userrouteauthority";
