{

if( strText.length() == 0 )

return strText ;

QString myStr;

int index=0;

for( int i=0;i<strText.length();i++)

{

ushort vChar=strText[i].unicode() ;

if( ( vChar >= 'a' && vChar <='z' ) || ( vChar >='A' && vChar <='Z' ) )

myStr.append(strText[i].toUpper() );

if( ( vChar >='0' && vChar <='9' ) )

myStr.append(strText[i]) ;

else

{

index = (int)vChar - 19968 ;

if( index >= 0 && index < strChineseFirstPY.length() )

myStr.append( strChineseFirstPY[index]);

}

}

return myStr ;

QMainWindow(parent),

ui(new Ui::MainWindow)

{

ui->setupUi(this);

sqlitePath = QString("%1/签到数据请勿删除/").arg(QStandardPaths::writableLocation(QStandardPaths::DesktopLocation));

setDatabase();

setAcceptDrops(true);

ui->tableViewDict->hide();

setMainToolBar();

ui->lineEditEname->setFocus();

QtAwesome *awesome = new QtAwesome(this);

awesome->initFontAwesome();

awesome->setDefaultOption( "color", QColor(122, 122, 122) );

ui->pushButtonEsave->setIcon(awesome->icon(fa::database));

ui->pushButtonEsave->setFont(awesome->font(13));

ui->labelBirth->setText(QChar(fa::birthdaycake));

ui->labelBirth->setFont(awesome->font(16));

ui->labelName->setText(QChar(fa::smileo));

ui->labelName->setFont(awesome->font(16));

ui->labelQName->setText(QChar(fa::smileo));

ui->labelQName->setFont(awesome->font(16));

ui->labelPhone->setText(QString("%1|%2").arg(QChar(fa::phone)).arg(QChar(fa::mobilephone)));

ui->labelPhone->setFont(awesome->font(16));

ui->labelQPhone->setText(QString("%1|%2").arg(QChar(fa::phone)).arg(QChar(fa::mobilephone)));

ui->labelQPhone->setFont(awesome->font(16));

ui->labelDate->setText(QChar(fa::calendar));

ui->labelDate->setFont(awesome->font(16));

ui->labelGender->setText(QString("%1|%2").arg(QChar(fa::male)).arg(QChar(fa::female)));

ui->labelGender->setFont(awesome->font(16));

ui->labelQGender->setText(QString("%1|%2").arg(QChar(fa::male)).arg(QChar(fa::female)));

ui->labelQGender->setFont(awesome->font(16));

ui->spinBoxDay->setRange(0, 31);

ui->spinBoxMonth->setRange(0, 12);

ui->actionDict->setText(QChar(fa::refresh));

ui->actionDict->setFont(awesome->font(16));

ui->actionList->setText(QChar(fa::barchart));

ui->actionList->setFont(awesome->font(16));

ui->actionExport->setText(QChar(fa::umbrella));

ui->actionExport->setFont(awesome->font(16));

ui->pushButtonQ->setText(QString("%1 %2").arg(QChar(fa::search)).arg("search"));

ui->pushButtonQ->setFont(awesome->font(14));

ui->toolButton->setText(QChar(fa::search));

ui->toolButton->setFont(awesome->font(14));

ui->dateEditEcurrent->setDate(QDate::currentDate());

ui->dateEditEBirth->setDate(QDate(1900, 1, 1));

ui->dateEditNote->setDate(QDate::currentDate());

ui->actionFirstLetter->setText(QChar(fa::leaf));

ui->actionFirstLetter->setFont(awesome->font(14));

ui->pushButtonSaveNote->setText(QChar(fa::database));

ui->pushButtonSaveNote->setFont(awesome->font(14));

ui->pushButtonSaveNote->setFlat(true);

ui->pushButtonExportCurrentNote->setText(QChar(fa::filetext));

ui->pushButtonExportCurrentNote->setFont(awesome->font(14));

ui->pushButtonExportCurrentNote->setFlat(true);

ui->dateEditDataExportStart->setDate(QDate::currentDate());

ui->dateEditDataExportEnd->setDate(QDate::currentDate());

deleteRowNum = -1;

{

qDebug() << qApp->applicationDirPath();

database = QSqlDatabase::addDatabase("QSQLITE");

QDir dir;

dir.mkpath(sqlitePath);

database.setDatabaseName(QString("%1/sign.db").arg(sqlitePath));

if (!database.open()) {

QMessageBox::information(this, "", "无法连接数据库");

} else {

QSqlQuery query;

QString signSql = QString("Create table sign ("

" name varchar(32), "

" gender varchar(4), "

" phone varchar(32), "

" birthday datetime, "

" logdate datetime,"

" current datetime,"

" ps varchar(64)"

");");

QString signDictSql = QString("Create table sign_dict ( "

" name varchar(32), "

" gender varchar(32),"

" phone varchar(32),"

" birthday datetime,"

" cnt int "

" );");

QString notesSql = QString("Create table notes ("

" name varchar(32), "

" phone varchar(32), "

" logtime datetime, "

" note text)");

QString pinyinSql = QString("Create table pinyin( "

" name varchar(32),"

" pinyin varchar(10) )");

QString pinyinIndex = "CREATE UNIQUE INDEX unique_index_name on pinyin(name)";

query.exec(signSql);

query.exec(signDictSql);

query.exec(notesSql);

query.exec(pinyinSql);

query.exec(pinyinIndex);

database.commit();

QStringList signTableFieldsStringList;

QStringList signDictTableFieldsStringList;

QStringList notesStringList;

signTableFieldsStringList << "姓名" << "性别" << "手机" << "生日" << "日期" << "当前记录时间" << "附注";

signDictTableFieldsStringList << "姓名" << "性别" << "手机" << "生日" << "签到次数";

notesStringList << "姓名" << "手机" << "时间" << "发言记录";

setModel(ui->tableViewSigns, "modelEdit", "sign", &signTableFieldsStringList, "", 0);

setModel(ui->tableViewDict, "modelChoose", "sign_dict", &signDictTableFieldsStringList, "", 4);

setModel(ui->tableViewQDict, "modelQ", "sign_dict", &signDictTableFieldsStringList, "", 4);

setModel(ui->tableView, "modelQE", "sign", &signTableFieldsStringList, "", 0);

setModel(ui->tableViewNoteHuman, "modelNote", "sign", &signTableFieldsStringList, "", 0);

setModel(ui->tableViewNoteList, "modelNotes", "notes", &notesStringList, "", 0);

}

{

QSqlTableModel *model;

if (modelName == "modelEdit") {

modelEdit = new QSqlTableModel;

model = modelEdit;

} else if (modelName == "modelChoose") {

modelChoose = new QSqlTableModel;

model = modelChoose;

} else if (modelName == "modelQ") {

modelQ = new QSqlTableModel;

model = modelQ;

} else if (modelName == "modelQE") {

modelQE = new QSqlTableModel;

model = modelQE;

} else if (modelName == "modelNote") {

modelNote = new QSqlTableModel;

model = modelNote;

} else if (modelName== "modelNotes") {

modelNotes = new QSqlTableModel;

model = modelNotes;

} else {

return;

}

model= new QSqlTableModel(this);

model->setTable(tableName);

model->setEditStrategy(QSqlTableModel::OnFieldChange);

model->setSort(orderNum, Qt::DescendingOrder);

int tableFieldsNum = tableFields->length();

for (int i = 0; i < tableFieldsNum; i++) {

model->setHeaderData(i, Qt::Horizontal, tableFields->at(i));

}

tableViewName->setModel(model);

tableViewName->alternatingRowColors();

tableViewName->horizontalHeader()->setStretchLastSection(true);

model->setFilter(filter);

model->select();

tableViewName->reset();

qDebug() << modelName << model << model->rowCount();

if (modelName == "modelEdit") {

modelEdit = model;

} else if (modelName == "modelChoose") {

modelChoose = model;

} else if (modelName == "modelQ") {

modelQ = model;

} else if (modelName == "modelQE") {

modelQE = model;

} else if (modelName == "modelNote") {

modelNote = model;

} else if (modelName == "modelNotes") {

modelNotes = model;

} else return;

{

qDebug() << index;

switch(index) {

case 0:

ui->lineEditEname->setFocus();

break;

case 1:

ui->lineEditQName->setFocus();

break;

case 2:

break;

case 3:

modelNote->select();

ui->tableViewNoteHuman->reset();

break;

default:

break;

}

{

QSqlQuery query;

query.exec("delete from sign_dict");

qDebug() << "init dict";

QString sql = "insert into sign_dict (name, gender, phone, birthday, cnt) select name, gender, phone, birthday, count(*) as cnt from sign group by name, phone";

query.exec(sql);

qDebug() << query.lastQuery();

database.commit();

{

QSqlQuery query, insertQuery;

query.exec("delete from pinyin");

qDebug() << query.lastError();

database.commit();

query.exec("select name from sign_dict");

while(query.next()) {

QString name = query.value(0).toString();

QString firstLetterName = toChineseSpell(name);

qDebug() << name << firstLetterName;

insertQuery.exec(QString("insert into pinyin(name, pinyin) values ('%1', '%2')").arg(name).arg(firstLetterName));

}

database.commit();

{

initDict();

initFirstLetter();

{

if (ui->tableViewDict->isHidden()) {

modelChoose->setFilter("");

modelChoose->select();

ui->tableViewDict->reset();

ui->tableViewSigns->hide();

ui->tableViewDict->show();

ui->pushButtonEsave->setDisabled(true);

} else {

ui->tableViewDict->hide();

ui->tableViewSigns->show();

ui->pushButtonEsave->setEnabled(true);

}

{

int num = 0;

QSqlQuery query;

query.exec(QString("select name from sign_dict where name = '%1' and gender = '%2' and phone = '%3'").arg(name).arg(gender).arg(phone));

qDebug() << query.lastQuery();

while(query.next()) { num+=1; }

// 如果字典表里有记录，计数器加1

if (num > 0) {

query.exec(QString("update sign_dict set cnt = cnt + 1 where name = '%1' and gender = '%2' and phone = '%3'").arg(name).arg(gender).arg(phone));

qDebug() << query.lastQuery();

return num;

}

// 如果字典表里没有数据，添加到字典表

query.exec(QString("insert into sign_dict (name, gender, phone, birthday, cnt) values ('%1', '%2', '%3', '%4', '%5')").arg(name).arg(gender).arg(phone).arg(birthday).arg(1));

qDebug() << query.lastQuery();

return 0;

{

int cnt = 0;

QSqlQuery query;

query.exec(QString("select * from sign where name = '%1' and gender = '%2' and phone = '%3' and logdate = '%4'").arg(name).arg(gender).arg(phone).arg(logdate));

qDebug() << query.lastQuery();

while(query.next()) {

cnt+=1;

}

return cnt;

{

QString name = ui->lineEditEname->text();

switch(filterType) {

case ORIGIN_NAME:

modelChoose->setFilter(QString("name = '%1'").arg(name));

break;

case PINYIN_NAME:

QString filter = QString("name in (select name from pinyin where pinyin = '%1')").arg(name.toUpper());

qDebug() << name.toUpper() << filter;

modelChoose->setFilter(filter);

qDebug() << "here we are";

break;

}

modelChoose->select();

//ui->tableViewDict->reset();

int rowCount = modelChoose->rowCount();

qDebug() << "rowCount ??" << rowCount << "filter type" << filterType;

if (rowCount == 0) {

return 0;

} else if (rowCount == 1) {

QString gender = modelChoose->record(0).value("gender").toString();

QString phone = modelChoose->record(0).value("phone").toString();

QString birthday = modelChoose->record(0).value("birthday").toString();

ui->lineEditEname->setText(modelChoose->record(0).value("name").toString());

ui->lineEditEphone->setText(phone);

ui->comboBoxEgender->setCurrentText(gender);

ui->dateEditEBirth->setDate(QDate::fromString(birthday, "yyyy-MM-dd"));

} else if (rowCount > 1) {

ui->tableViewDict->show();

}

return rowCount;

{

QString name = ui->lineEditEname->text().trimmed();

QString gender = ui->comboBoxEgender->currentText().trimmed();

QString phone = ui->lineEditEphone->text().trimmed();

phone = phone.remove(" ");

QString birthday = ui->dateEditEBirth->date().toString("yyyy-MM-dd");

QString logdate = ui->dateEditEcurrent->date().toString("yyyy-MM-dd");

if (!ui->tableViewDict->isHidden()) {

ui->tableViewDict->hide();

}

QString pinyin = toChineseSpell(name);

qDebug() << "name = " << pinyin;

if (name.isEmpty()) {

QMessageBox::information(this, "", "贤二，姓名不能为空滴啊！");

return;

}

bool signStat = ifSignedToday(name, gender, phone, logdate);

if (signStat) {

QMessageBox::information(this, "", QString("嘿嘿，%1 %2 今天签到已经录进去啦，我们是一天签到一次哦！").arg(name).arg(phone));

clearEdits();

return;

}

updateDict(name, gender, phone, birthday);

QString current = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");

QSqlQuery query;

query.exec(QString("insert into sign(name, gender, phone, birthday, logdate, current) values ('%1', '%2', '%3', '%4', '%5', '%6')").arg(name).arg(gender).arg(phone).arg(birthday).arg(logdate).arg(current));

query.exec(QString("replace into pinyin(name, pinyin) values ('%1', '%2') ").arg(name).arg(pinyin));

modelEdit->setFilter(QString("logdate = '%1'").arg(logdate));

modelEdit->select();

ui->tableViewSigns->reset();

clearEdits();

{

ui->lineEditEname->clear();

ui->lineEditEphone->clear();

ui->dateEditEBirth->setDate(QDate(1900, 1, 1));

ui->lineEditEname->setFocus();

{

int rowNum = index.row();

QString name = modelChoose->record(rowNum).value("name").toString();

QString phone = modelChoose->record(rowNum).value("phone").toString();

QString gender = modelChoose->record(rowNum).value("gender").toString();

QDate birthday = modelChoose->record(rowNum).value("birthday").toDate();

ui->lineEditEname->setText(name);

ui->lineEditEphone->setText(phone);

ui->comboBoxEgender->setCurrentText(gender);

ui->dateEditEBirth->setDate(birthday);

{

if (ui->tableViewDict->isHidden()) {

choose(0);

}

{

modelEdit->setFilter(QString(" logdate = '%1'").arg(ui->dateEditEcurrent->date().toString("yyyy-MM-dd")));

modelEdit->select();

ui->tableViewSigns->reset();

{

QString sign = QString("%1/sign.db").arg(sqlitePath);

if (!QFile::exists(sign)) return;

QString backup = QString("%1/备份签到数据库位置/%2").arg(QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation)).arg(QDateTime::currentDateTime().toString("yyyy-MM-dd"));

QDir dir;

dir.mkpath(backup);

QString backupSign = QString("%1/sign.db.%2").arg(backup).arg(QDate::currentDate().toString("yyyy-MM-dd"));

QFile::copy(sign, backupSign);

QMessageBox::information(this, "", backupSign);

{

QString name = ui->lineEditQName->text().trimmed();

QString gender = ui->comboBoxQGender->currentText();

QString phone = ui->lineEditQPhone->text().trimmed();

int month = ui->spinBoxMonth->value();

int day = ui->spinBoxDay->value();

QString filter;

QString monthStr;

qDebug() << month << day;

if(month) {

monthStr = QString("%1").arg(month, 2, 10, QChar('0'));

} else {

monthStr = "%";

}

QString dayStr;

if (day) {

dayStr = QString("%1").arg(day, 2, 10, QChar('0'));

qDebug() << "dayStr= " << dayStr;

} else {

dayStr = "%";

}

QString birthday;

if (month && day) {

birthday = QString(" birthday like '%-%2-%3'").arg(monthStr).arg(dayStr);

} else if (month && !day) {

birthday = QString(" birthday like '%-%1-%'").arg(monthStr);

} else if (day && !month) {

birthday = QString(" birthday like '%-%-%1'").arg(dayStr);

} else {

birthday = " birthday like '%' ";

}

filter = birthday;

QMap<QString, QString> map;

map["name"] = name;

map["gender"] = gender;

map["phone"] = phone;

QStringList lst;

lst << "name" << "gender" << "phone";

foreach (QString k, lst) {

if (!map[k].isEmpty()) {

filter.append(QString(" and %1 = '%2'").arg(k).arg(map[k]));

}

}

modelQ->setFilter(filter);

modelQ->select();

qDebug() << "filter: " << modelQ->filter();

ui->tableViewQDict->reset();

{

QString filter;

int rowNum = index.row();

QString name = modelQ->record(rowNum).value("name").toString().trimmed();

QString phone = modelQ->record(rowNum).value("phone").toString().trimmed();

QString gender = modelQ->record(rowNum).value("gender").toString().trimmed();

QMap<QString, QString> map;

map["name"] = name;

map["gender"] = gender;

map["phone"] = phone;

QStringList lst;

lst << "name" << "gender" << "phone";

int i = 0;

foreach (QString k, lst) {

if (!map[k].isEmpty()) {

qDebug() << k << map[k];

if (i >0) {

filter.append(QString(" and %1 = '%2'").arg(k).arg(map[k]));

} else {

filter.append(QString(" %1 = '%2'").arg(k).arg(map[k]));

}

i++;

}

}

modelQE->setFilter(filter);

modelQE->select();

ui->tableView->reset();

{

QString name = ui->lineEditEname->text().trimmed();

QString phone = ui->lineEditEphone->text().trimmed();

QString birthday = ui->dateEditEBirth->dateTime().toString("yyyy-MM-dd");

if (name.isEmpty()) {

QMessageBox::information(this, "", "贤二，人家的名字你没输入，你咋搞的？");

return;

}

QString updatePersonByNameSql = QString("update sign set phone = '%1', birthday = '%2' where name = '%3'").arg(phone).arg(birthday).arg(name);

QString updatePersonByNameSqlDict = QString("update sign_dict set phone = '%1', birthday = '%2' where name = '%3'").arg(phone).arg(birthday).arg(name);

qDebug() << updatePersonByNameSql;

QSqlQuery query;

query.exec(updatePersonByNameSql);

query.exec(updatePersonByNameSqlDict);

database.commit();

{

QString start = ui->dateEditDataExportStart->date().toString("yyyy-MM-dd");

QString end = ui->dateEditDataExportEnd->date().toString("yyyy-MM-dd");

QString exportSql = QString("select name, gender, phone, birthday, logdate, current, ps from sign where logdate >= '%1' and logdate <= '%2'").arg(start).arg(end);

QString savePath = QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);

QString saveFile = QString("%1_%2.data").arg(start).arg(end);

QString saveFilePlain = QString("%1_%2.txt").arg(start).arg(end);

QString saveAbsPath = QString("%1/%2").arg(savePath).arg(saveFile);

QString saveAbsPathPlain = QString("%1/%2").arg(savePath).arg(saveFilePlain);

qDebug() << exportSql;

QFile fileOut(saveAbsPath);

QFile fileOutPlain(saveAbsPathPlain);

if (!fileOut.open(QIODevice::WriteOnly| QIODevice::Text)) {

qDebug() << "open file error";

QMessageBox::information(this, "", "贤二，出错了，文件打不开。你找写代码的那家伙吧，我帮不上你。");

return;

}

if (!fileOutPlain.open(QIODevice::WriteOnly| QIODevice::Text)) {

qDebug() << "open file error";

QMessageBox::information(this, "", "贤二，出错了，文件打不开。你找写代码的那家伙吧，我帮不上你。");

return;

}

QTextStream streamFileOut(&fileOut);

streamFileOut.setCodec("UTF-8");

QTextStream streamFileOutPlain(&fileOutPlain);

streamFileOutPlain.setCodec("UTF-8");

QSqlQuery query;

query.exec(exportSql);

while(query.next()) {

QString name = query.value(0).toString();

QString gender = query.value(1).toString();

QString phone = query.value(2).toString();

QString birthday = query.value(3).toString();

QString logdate = query.value(4).toString();

QString current = query.value(5).toString();

QString ps = query.value(6).toString();

QString recordStr = name + "," + gender + "," + phone + "," + birthday + "," + logdate + "," + current + "," + ps + "\n";

QByteArray base64;

base64.append(recordStr);

QString base64Str = base64.toBase64();

streamFileOut << base64Str << "\n";

streamFileOutPlain << recordStr;

qDebug() << name << gender << phone << birthday << logdate << current;

streamFileOut.flush();

streamFileOutPlain.flush();

}

fileOut.close();

fileOutPlain.close();

{

if (event->mimeData()->hasFormat("text/uri-list")) {

event->acceptProposedAction();

}

{

QList<QUrl> urls = event->mimeData()->urls();

if (urls.isEmpty()) return;

QString fileName = urls.first().toLocalFile();

qDebug() << fileName;

ui->lineEditDataImport->setText(fileName);

{

QString fileName = ui->lineEditDataImport->text().trimmed();

if (fileName.isEmpty()) {

QMessageBox::information(this, "", "贤二，你坑我！你给我个空文件干嘛！");

return;

}

QFile openFile(fileName);

if (!openFile.open(QIODevice::ReadOnly)) {

QMessageBox::information(0, "", openFile.errorString());

return;

}

QSqlQuery query;

QTextStream in(&openFile);

while(!in.atEnd()) {

QString line = in.readLine().trimmed();

QByteArray byteArray = line.toLatin1();

QByteArray text = QByteArray::fromBase64(byteArray);

QString out = text.data();

QString outLine = out.trimmed();

if (outLine.isEmpty()) {

qDebug() << "empty line";

continue;

}

qDebug() << outLine;

query.prepare("insert into sign (name, gender, phone, birthday, logdate, current, ps) values "

" (:name, :gender, :phone, :birthday, :logdate, :current, :ps)");

QStringList recList = outLine.split(",");

QString name = recList.at(0);

QString gender = recList.at(1);

QString phone = recList.at(2);

QString birthday = recList.at(3);

QString logdate = recList.at(4);

QString current = recList.at(5);

QString ps = recList.at(6);

query.bindValue(0, name);

query.bindValue(1, gender);

query.bindValue(2, phone);

query.bindValue(3, birthday);

query.bindValue(4, logdate);

query.bindValue(5, current);

query.bindValue(6, ps);

query.exec();

}

database.commit();

openFile.close();

ui->lineEditDataImport->clear();

qDebug() << "我在后面，你没看到我嘛";

{

QString logdate = date.toString("yyyy-MM-dd");

modelNote->setFilter(QString(" logdate = '%1' ").arg(logdate));

modelNote->select();

ui->tableViewNoteHuman->reset();

{

int rowNum = index.row();

QString name = modelNote->record(rowNum).value("name").toString();

QString gender = modelNote->record(rowNum).value("gender").toString();

QString phone = modelNote->record(rowNum).value("phone").toString();

QDate birthday = modelNote->record(rowNum).value("birthday").toDate();

qDebug() << name << gender << phone << birthday;

ui->lineEditNoteName->setText(name);

ui->lineEditNotePhone->setText(phone);

ui->plainTextEditNote->setFocus();

{

if (ui->lineEditNoteName->text().isEmpty()) return;

QString name = ui->lineEditNoteName->text();