Irfan Khan
Reputation: 11
ZK Time in PHP with mysql database
Is there a way to access ZK time attendance machine database which is in mdb format in php using mysql db ? Im trying to create a web page that will display a custom attendance report.
Upvotes: 1
Views: 6632
Answers (2)
joshua
Reputation: 21
Hi i used zk machines to connect to a database and i retrieve data using my php web pages. copy this script, its an sql script it will create a duplicate of that mdb file.Then install the zk time and attendance software and make the machine point to this database. boom you good to go.
CREATE TABLE CHECKEXACT (
EXACTID INT IDENTITY(1,1) NOT NULL ,
USERID INT NULL DEFAULT 0,
CHECKTIME DATETIME NULL DEFAULT 0,
CHECKTYPE VARCHAR (2) NULL DEFAULT 0,
ISADD SMALLINT NULL DEFAULT 0,
YUYIN VARCHAR (25) NULL ,
ISMODIFY SMALLINT NULL DEFAULT 0,
ISDELETE SMALLINT NULL DEFAULT 0,
INCOUNT SMALLINT NULL DEFAULT 0,
ISCOUNT SMALLINT NULL DEFAULT 0,
MODIFYBY VARCHAR (20) NULL,
[DATE] DATETIME NULL,
CONSTRAINT EXACTID PRIMARY KEY (EXACTID)
)
;
CREATE TABLE CHECKINOUT (
USERID INT NOT NULL ,
CHECKTIME DATETIME NOT NULL DEFAULT GETDATE(),
CHECKTYPE VARCHAR (1) NULL DEFAULT 'I',
VERIFYCODE INT NULL DEFAULT 0,
SENSORID VARCHAR (5) NULL,
CONSTRAINT USERCHECKTIME PRIMARY KEY (USERID, CHECKTIME)
)
;
CREATE TABLE DEPARTMENTS (
DEPTID INT IDENTITY(1,1) NOT NULL ,
DEPTNAME VARCHAR (30) NULL ,
SUPDEPTID INT NOT NULL DEFAULT 1,
CONSTRAINT DEPTID PRIMARY KEY (DEPTID)
)
;
CREATE TABLE EXCNOTES (
USERID INT NULL ,
ATTDATE DATETIME NULL ,
NOTES VARCHAR (200) NULL
)
;
CREATE TABLE HOLIDAYS (
HOLIDAYID INT IDENTITY(1,1) NOT NULL ,
HOLIDAYNAME VARCHAR (20) NULL ,
HOLIDAYYEAR SMALLINT NULL ,
HOLIDAYMONTH SMALLINT NULL ,
HOLIDAYDAY SMALLINT NULL DEFAULT 1,
STARTTIME DATETIME NULL ,
DURATION SMALLINT NULL ,
HOLIDAYTYPE SMALLINT NULL ,
XINBIE VARCHAR (4) NULL ,
MINZU VARCHAR (50) NULL,
CONSTRAINT HOLID PRIMARY KEY (HOLIDAYID)
)
;
CREATE TABLE NUM_RUN (
NUM_RUNID INT IDENTITY(1,1) NOT NULL ,
OLDID INT NULL DEFAULT -1,
NAME VARCHAR (30) NOT NULL ,
STARTDATE DATETIME NULL DEFAULT '1900-1-1',
ENDDATE DATETIME NULL DEFAULT '2099-12-31',
CYLE SMALLINT NULL DEFAULT 1,
UNITS SMALLINT NULL DEFAULT 1,
CONSTRAINT NUMID PRIMARY KEY (NUM_RUNID)
)
;
CREATE TABLE NUM_RUN_DEIL (
NUM_RUNID SMALLINT NOT NULL ,
STARTTIME DATETIME NOT NULL ,
ENDTIME DATETIME NULL ,
SDAYS SMALLINT NOT NULL ,
EDAYS SMALLINT NULL ,
SCHCLASSID INT NULL DEFAULT -1,
CONSTRAINT NUMID2 PRIMARY KEY (NUM_RUNID, SDAYS, STARTTIME)
)
;
CREATE TABLE SECURITYDETAILS (
SECURITYDETAILID INT IDENTITY(1,1) NOT NULL ,
USERID SMALLINT NULL ,
DEPTID SMALLINT NULL ,
SCHEDULE SMALLINT NULL ,
USERINFO SMALLINT NULL ,
ENROLLFINGERS SMALLINT NULL ,
REPORTVIEW SMALLINT NULL ,
REPORT VARCHAR (10) NULL,
CONSTRAINT NAMEID2 PRIMARY KEY (SECURITYDETAILID)
)
;
CREATE TABLE SHIFT (
SHIFTID INT IDENTITY(1,1) NOT NULL ,
NAME VARCHAR (20) NULL ,
USHIFTID INT NULL DEFAULT -1,
STARTDATE DATETIME NOT NULL DEFAULT '1900-1-1',
ENDDATE DATETIME NULL DEFAULT '1900-12-31',
RUNNUM SMALLINT NULL DEFAULT 0,
SCH1 INT NULL DEFAULT 0,
SCH2 INT NULL DEFAULT 0,
SCH3 INT NULL DEFAULT 0,
SCH4 INT NULL DEFAULT 0,
SCH5 INT NULL DEFAULT 0,
SCH6 INT NULL DEFAULT 0,
SCH7 INT NULL DEFAULT 0,
SCH8 INT NULL DEFAULT 0,
SCH9 INT NULL DEFAULT 0,
SCH10 INT NULL DEFAULT 0,
SCH11 INT NULL DEFAULT 0,
SCH12 INT NULL DEFAULT 0,
CYCLE SMALLINT NULL DEFAULT 0,
UNITS SMALLINT NULL DEFAULT 0 ,
CONSTRAINT SHIFTS PRIMARY KEY (SHIFTID)
)
;
CREATE TABLE TEMPLATE (
TEMPLATEID INT IDENTITY(1,1) NOT NULL ,
USERID INT NOT NULL ,
FINGERID INT NOT NULL ,
TEMPLATE image NOT NULL ,
TEMPLATE2 image NULL ,
TEMPLATE3 image NULL ,
BITMAPPICTURE image NULL ,
BITMAPPICTURE2 image NULL ,
BITMAPPICTURE3 image NULL ,
BITMAPPICTURE4 image NULL ,
USETYPE SMALLINT NULL ,
CONSTRAINT TEMPLATED PRIMARY KEY (TEMPLATEID)
)
;
CREATE TABLE USER_OF_RUN (
USERID INT NOT NULL ,
NUM_OF_RUN_ID INT not NULL ,
STARTDATE DATETIME not NULL DEFAULT '1900-1-1',
ENDDATE DATETIME not NULL DEFAULT '2099-12-31',
ISNOTOF_RUN INT NULL DEFAULT 0,
ORDER_RUN INT NULL ,
CONSTRAINT USER_ST_NUM PRIMARY KEY (USERID, NUM_OF_RUN_ID, STARTDATE, ENDDATE)
)
;
CREATE TABLE USER_SPEDAY (
USERID INT NOT NULL ,
STARTSPECDAY DATETIME NOT NULL DEFAULT '1900-1-1',
ENDSPECDAY DATETIME NULL DEFAULT '2099-12-31',
DATEID SMALLINT not NULL DEFAULT -1,
YUANYING VARCHAR (200) NULL ,
[DATE] DATETIME NULL ,
CONSTRAINT USER_SEP PRIMARY KEY (USERID, STARTSPECDAY, DATEID)
)
;
CREATE TABLE USER_TEMP_SCH (
USERID INT not NULL ,
COMETIME DATETIME not NULL ,
LEAVETIME DATETIME not NULL ,
OVERTIME INT not NULL DEFAULT 0,
[TYPE] SMALLINT NULL DEFAULT 0,
FLAG SMALLINT NULL DEFAULT 1,
SCHCLASSID INT NULL DEFAULT -1,
CONSTRAINT USER_TEMP PRIMARY KEY (USERID, COMETIME, LEAVETIME)
)
;
CREATE TABLE USERINFO (
USERID INT IDENTITY(1,1) NOT NULL ,
BADGENUMBER VARCHAR (12) NOT NULL ,
SSN VARCHAR (20) NULL ,
NAME VARCHAR (20) NULL ,
GENDER VARCHAR (2) NULL ,
TITLE VARCHAR (20) NULL ,
PAGER VARCHAR (20) NULL ,
BIRTHDAY DATETIME NULL ,
HIREDDAY DATETIME NULL ,
STREET VARCHAR (40) NULL ,
CITY VARCHAR (2) NULL ,
STATE VARCHAR (2) NULL ,
ZIP VARCHAR (12) NULL ,
OPHONE VARCHAR (20) NULL ,
FPHONE VARCHAR (20) NULL ,
VERIFICATIONMETHOD SMALLINT NULL ,
DEFAULTDEPTID SMALLINT NULL DEFAULT 1,
SECURITYFLAGS SMALLINT NULL ,
ATT SMALLINT NOT NULL DEFAULT 1,
INLATE SMALLINT NOT NULL DEFAULT 1,
OUTEARLY SMALLINT NOT NULL DEFAULT 1,
OVERTIME SMALLINT NOT NULL DEFAULT 1,
SEP SMALLINT NOT NULL DEFAULT 1,
HOLIDAY SMALLINT NOT NULL DEFAULT 1,
MINZU VARCHAR (8) NULL ,
[PASSWORD] VARCHAR (20) NULL ,
LUNCHDURATION SMALLINT NOT NULL DEFAULT 1,
MVerifyPass VARCHAR(10) NULL,
PHOTO Image NULL,
CONSTRAINT USERIDS PRIMARY KEY (USERID)
)
;
CREATE UNIQUE INDEX USERFINGER ON TEMPLATE(USERID, FINGERID)
;
CREATE UNIQUE INDEX HOLIDAYNAME ON HOLIDAYS(HOLIDAYNAME)
;
CREATE INDEX DEPTNAME ON DEPARTMENTS(DEPTNAME)
;
CREATE UNIQUE INDEX EXCNOTE ON EXCNOTES(USERID, ATTDATE)
;
CREATE UNIQUE INDEX BADGENUMBER ON USERINFO(BADGENUMBER)
;
Create Table LeaveClass(
LeaveId INT Identity(1,1) not null primary key,
LeaveName VARCHAR(20) not null,
MinUnit float not null default 1,
Unit smallint not null default 1,
RemaindProc smallint not null default 1,
RemaindCount smallint not null default 1,
ReportSymbol varchar(4) not null default '-',
Deduct float not null default 0,
Color int not null default 0,
Classify SMALLINT NOT null default 0)
;
Create Table LeaveClass1(
LeaveId INT Identity(999,1) not null primary key,
LeaveName VARCHAR(20) not null,
MinUnit float not null default 1,
Unit smallint not null default 0,
RemaindProc smallint not null default 2,
RemaindCount smallint not null default 1,
ReportSymbol varchar(4) not null default '-',
Deduct float not null default 0,
LeaveType SMALLINT not null default 0,
Color int not null default 0,
Classify SMALLINT not null default 0,
Calc text null)
;
CREATE TABLE SchClass(
schClassid INT identity(1,1) NOT NULL PRIMARY KEY,
schName VARCHAR(20) NOT null,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
LateMinutes int null,
EarlyMinutes int null,
CheckIn int null default 1,
CheckOut int null default 1,
CheckInTime1 datetime NULL,
CheckInTime2 datetime NULL,
CheckOutTime1 datetime NULL,
CheckOutTime2 datetime NULL,
Color Int NULL default 16715535,
AutoBind SMALLINT NULL DEFAULT 1)
;
Create Table AttParam(
PARANAME VARCHAR (20) NOT NULL Primary key,
PARATYPE VARCHAR (2) NULL ,
PARAVALUE VARCHAR(100) NOT NULL)
;
INSERT INTO DEPARTMENTS (DEPTNAME, SUPDEPTID) VALUES('Our Company',0);
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color)
values('Sick', 1, 'B', 3398744);
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color)
values('Vacation', 1, 'S', 8421631);
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color)
values('Other', 1, 'T', 16744576);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('BLeave', 0.5, 3, 1, 1, 'G', 3, 'if(AttItem(LeaveType1)=999,AttItem(LeaveTime1),0)+if(AttItem(LeaveType2)=999,AttItem(LeaveTime2),0)+if(AttItem(LeaveType3)=999,AttItem(LeaveTime3),0)+if(AttItem(LeaveType4)=999,AttItem(LeaveTime4),0)+if(AttItem(LeaveType5)=999,AttItem(LeaveTime5),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('Normal', 0.5, 3, 1, 0, ' ', 3);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('Late', 10, 2, 2, 1, '>', 3, 'AttItem(minLater)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('Early', 10, 2, 2, 1, '<', 3, 'AttItem(minEarly)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('AfL', 1, 1, 1, 1, 'L', 3,
'if((AttItem(LeaveType1)>0) and (AttItem(LeaveType1)<999),AttItem(LeaveTime1),0)+if((AttItem(LeaveType2)>0) and (AttItem(LeaveType2)<999),AttItem(LeaveTime2),0)+if((AttItem(LeaveType3)>0) and (AttItem(LeaveType3)<999),AttItem(LeaveTime3),0)+if((AttItem(LeaveType4)>0) and (AttItem(LeaveType4)<999),AttItem(LeaveTime4),0)+if((AttItem(LeaveType5)>0) and (AttItem(LeaveType5)<999),AttItem(LeaveTime5),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('Absent', 0.5, 3, 1, 0, 'A', 3, 'AttItem(MinAbsent)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('OT', 1, 1, 1, 1, '+', 3, 'AttItem(MinOverTime)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('OT2', 1, 1, 0, 1, '=', 0, 'if(HolidayId(1)=1, AttItem(MinOverTime),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('Rest', 0.5, 3, 2, 1, '-', 2);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('N/In', 1, 4, 2, 1, '[', 2,
'If(AttItem(CheckIn)=null,If(AttItem(OnDuty)=null,0,if(((AttItem(LeaveStart1)=null) or (AttItem(LeaveStart1)>AttItem(OnDuty))) and AttItem(DutyOn),1,0)),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType, Calc)
values('N/Out', 1, 4, 2, 1, ']', 2,
'If(AttItem(CheckOut)=null,If(AttItem(OffDuty)=null,0,if((AttItem(LeaveEnd1)=null) or (AttItem(LeaveEnd1)<AttItem(OffDuty)),if((AttItem(LeaveEnd2)=null) or (AttItem(LeaveEnd2)<AttItem(OffDuty)),if(((AttItem(LeaveEnd3)=null) or (AttItem(LeaveEnd3)<AttItem(OffDuty))) and AttItem(DutyOff),1,0),0),0)),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('ROT', 1, 4, 2, 1, '{', 3);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('BOUT', 1, 4, 2, 1, '}', 3);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('OUT', 1, 1, 2, 1, 'L', 3);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
RemaindCount, ReportSymbol, LeaveType)
values('FOT', 1, 1, 2, 1, 'F', 3);
insert into AttParam(ParaName,ParaValue) values('MinsEarly',5);
insert into AttParam(ParaName,ParaValue) values('MinsLate',10);
insert into AttParam(ParaName,ParaValue) values('MinsNoBreakIn',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoBreakOut',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoIn',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoLeave',60);
insert into AttParam(ParaName,ParaValue) values('MinsNotOverTime',60);
insert into AttParam(ParaName,ParaValue) values('MinsWorkDay',420);
insert into AttParam(ParaName,ParaValue) values('NoBreakIn',1012);
insert into AttParam(ParaName,ParaValue) values('NoBreakOut',1012);
insert into AttParam(ParaName,ParaValue) values('NoIn',1001);
insert into AttParam(ParaName,ParaValue) values('NoLeave',1002);
insert into AttParam(ParaName,ParaValue) values('OutOverTime',0);
insert into AttParam(ParaName,ParaValue) values('TwoDay',0);
insert into AttParam(ParaName,ParaValue) values('CheckInColor',16777151);
insert into AttParam(ParaName,ParaValue) values('CheckOutColor',12910591);
insert into AttParam(ParaName,ParaValue) values('DBVersion',167);
Upvotes: 2
Ave
Reputation: 4430
In PHP you can do that.
Something like this:
$dbName = $_SERVER["DOCUMENT_ROOT"] . "zksoftware\zksoftware.mdb";
if (!file_exists($dbName)) {
die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");
A successful connection will allow SQL commands to be executed from PHP to read or write the database. If, however, you get the error message “PDOException Could not find driver” then it’s likely that the PDO ODBC driver is not installed. Use the phpinfo() function to check your installation for references to PDO.
If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache, and then try to connect to the database again.
In your case, I believe it would work if the .mdb the file is on an external server, but you'd have to be able to hit it from where you're serving the page.
Not sure if this is an intranet or across the public internet, but if it's public internet you might want to rework your workflow a bit, as generally making your database accessible that way is not something you'd want to do.
Reference answer at here.
Upvotes: 0