自动补卡考勤程序

一、项目需求

通过Qt开发一个实现自动在数据库中插入打卡数据的程序

二、背景环境

数据库：Sql Server

编程工具：QtQuick(QML) + C++

三、实现步骤

设置 ODBC 数据源(64 位)-->系统DSN-->添加

CMakeLists.txt，其中

cmake_minimum_required(VERSION 3.16)

project(XSabre VERSION 0.1 LANGUAGES CXX)

set(CMAKE_AUTOMOC ON)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

#增加 sql模块
find_package(Qt6 6.2 COMPONENTS Quick REQUIRED Sql)

#添加源文件和头文件
qt_add_executable(appXSabre
    main.cpp
    sqlddl.h
    sqlddl.cpp

)

qt_add_qml_module(appXSabre
    URI XSabre
    VERSION 1.0
    QML_FILES main.qml 
)

set_target_properties(appXSabre PROPERTIES
    MACOSX_BUNDLE_GUI_IDENTIFIER my.example.com
    MACOSX_BUNDLE_BUNDLE_VERSION ${PROJECT_VERSION}
    MACOSX_BUNDLE_SHORT_VERSION_STRING ${PROJECT_VERSION_MAJOR}.${PROJECT_VERSION_MINOR}
    MACOSX_BUNDLE TRUE
    WIN32_EXECUTABLE TRUE
)

target_compile_definitions(appXSabre
    PRIVATE $<$<OR:$<CONFIG:Debug>,$<CONFIG:RelWithDebInfo>>:QT_QML_DEBUG>)
#添加 Qt6::Sql
target_link_libraries(appXSabre
    PRIVATE Qt6::Quick Qt6::Sql)

添加命名为sqlddl的C++ Class，生成sqlddl.h和sqlddl.cpp两个文件

sqlddl.h

#ifndef SQLDDL_H
#define SQLDDL_H

#include <QObject>

class sqlddl : public QObject
{
    Q_OBJECT
public:
    explicit sqlddl(QObject *parent = nullptr);

    Q_INVOKABLE void execsql();



signals:

};

#endif // SQLDDL_H

sqlddl.cpp，其中敏感信息已经用x代替

#include "sqlddl.h"
//引入库文件
#include <QtSql/QSqlDatabase>
#include<QtSql/QSqlDriver>
#include<QtSql/QSqlError>
#include<QDebug>
#include<QtSql/QSqlQuery>
#include<QString>
#include<sqlopen.h>
sqlddl::sqlddl(QObject *parent)
    : QObject{parent}
{

}

void sqlddl::execsql()
{

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");

    qDebug ()<< "sql driver "<< db.isValid();
    //db.setHostName("192.168.x.x");
    db.setHostName("192.168.x.x,x");
   //设置数据源名称
    db.setDatabaseName("KYSOFT_RenShi");
    db.setUserName("sa");
    //db.setPassword("x");
    db.setPassword("x");
    db.open();

    if(!db.open())
    {
        qDebug()<<db.lastError();
    }
    else
    {
        qDebug()<<"数据库连接成功！";
        //插入语句
         QSqlQuery query;

       
               //旧语句，可以成功执行无工号，作为备份("insert into KQ_KaoQin_MX (BH_EMP,BH_Card,RIQI,Times,Address,ID,Type,BH_Bu,DATA,Trans,strpic,FileName,OK_PIC,RdCardTime)values('读卡成功','0011268861',CONVERT(varchar, GETDATE(), 23),substring( convert(varchar,getdate(),120),1,16),232,0,0,'',CONVERT(varchar, getdate(),120 )+' 0011268861      232','0', ' ', replace(replace(replace(replace(CAST(CONVERT(varchar(100), GETDATE(), 120) as datetime2(2)),'-',''),' ','-'),':',''),'.',''), '0', ' ')"))
                if(!query.exec("insert into KQ_KaoQin_MX (BH_EMP,BH_Card,RIQI,Times,Address,ID,Type,BH_Bu,DATA,Trans,strpic,FileName,OK_PIC,RdCardTime)values('201910004','0011268861',CONVERT(varchar, GETDATE(), 23),substring( convert(varchar,getdate(),120),1,16),232,0,0,'',CONVERT(varchar, getdate(),120 )+' 0011268861      232','0', ' ', replace(replace(replace(replace(CAST(CONVERT(varchar(100), GETDATE(), 120) as datetime2(2)),'-',''),' ','-'),':',''),'.',''), '0', ' ')"))
                {
                      qDebug() << query.lastError();

                  }
                  else
                  {
                      qDebug() << "恭喜，插入语句执行成功！";

                  }

    }

    db.close();
   QSqlDatabase::removeDatabase("QODBC");
}

main.cpp

#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QSqlDatabase>
#include <QSqlError>
#include <sqlddl.h>
#include <QQmlContext>


int main(int argc, char *argv[])
{
    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    const QUrl url(u"qrc:/XSabre/main.qml"_qs);

    //将C++中的类注册到QML中
     qmlRegisterType<sqlddl,1>("Sqlddl",1,0,"Sqlddl");

    QObject::connect(&engine, &QQmlApplicationEngine::objectCreated,
                     &app, [url](QObject *obj, const QUrl &objUrl) {
        if (!obj && url == objUrl)
            QCoreApplication::exit(-1);
    }, Qt::QueuedConnection);
    engine.load(url);



    return app.exec();
}

main.qml

import QtQuick
import QtQuick.Controls
import Sqlddl

Window {
    width: 640
    height: 480
    visible: true
    title: qsTr("自动打卡考勤程序")
    Item {
        Text {
            id: time
            font.pixelSize: 24
        }

        Text {
            id: txtstatus
            x: 300
            font.pixelSize: 24
            text: qsTr("状态")
        }

        Sqlddl {
            id:sqldl
        }


        Button {
            x:50
            y:50
            width: 100
            height: 50
            text: qsTr("插入按钮")
            onClicked: {
                sqldl.execsql()
                }

            }
        }


        Timer {
            interval: 1000; running: true; repeat: true
            onTriggered: {

                time.text = Qt.formatDateTime(new Date(), "yyyy-MM-dd hh:mm:ss")

                var d1 = '07:35:00'
                var d2 = '12:05:00'
                var d3 = '13:05:00'
                var d4 = '17:35:00'
                //var d6 = '22:08:00'

                var d5 = Qt.formatDateTime(new Date(), "hh:mm:ss")
                console.log(d5)
              //console.log(typeof(d5))
              //console.log(typeof(d1))



                if ( d5===d1  || d5===d2 || d5===d3 || d5===d4  ){

                    txtstatus.text="正在插入..."
                    console.log("执行插入语句")

                    //调用数据库插入函数
                    sqldl.execsql()



                }else{
                    txtstatus.text="等待执行插入中..."
                    console.log("等待状态！")

                }

            }

        }


    }