bool addEnoceanDevice(char* data) { unsigned char rorg = data[0]; unsigned char id1 = ""; unsigned char id2 = ""; unsigned char id3 = ""; unsigned char id4 = ""; switch (rorg) { case 0xD5: id1 = data[2]; id2 = data[3]; id3 = data[4]; id4 = data[5]; break; case 0xF6: id1 = data[2]; id2 = data[3]; id3 = data[4]; id4 = data[5]; break; case 0xA5: id1 = data[5]; id2 = data[6]; id3 = data[7]; id4 = data[8]; break; case 0xD2: id1 = data[7]; id2 = data[8]; id3 = data[9]; id4 = data[10]; break; default: log_DEBUG("[EnOcean] TYPE: Not Supported"); break; } log_DEBUG("[EnOcean] Record Mode : %d", RECORD); if (RECORD == 1) { log_INFO("[EnOcean] Recording new device type : %02hhX",rorg); mysql_insert("INSERT IGNORE INTO devices (dc_id,last_update,packettype,int_id,id1,id2,id3,id4,com) \ SELECT IFNULL(MAX(dc_id),0)+1,NOW(),'%02hhX',4,'%02hhX','%02hhX','%02hhX','%02hhX','tx' FROM devices ON DUPLICATE KEY UPDATE last_update=NOW();",\ rorg,id1,id2,id3,id4); }
void CReporter::GetSQL( QString &strSql, CommonDataType::ReportType rType, QDateTime& dtStart, QDateTime& dtEnd ) { QString strMonthCard = "月租卡"; QString strTimeCard = "计时卡"; QString strValueCard = "储值卡"; QString strValue = "储值卡充值"; QDate dStart = dtStart.date( ); QDate dEnd = dtEnd.date( ); QString strStart; QString strEnd; QTime time; time.setHMS( 23, 59, 59 ); QDateTime dtTimeEnd = dtEnd; dtEnd.setTime( time ); CCommonFunction::DateTime2String( dtStart, strStart ); CCommonFunction::DateTime2String( dtEnd, strEnd ); QString yBegin = QString::number( dStart.year( ) ) + "-01-01 00:00:00"; QString yEnd = QString::number( dEnd.year( ) + 1 ) + "-01-01 00:00:00"; QString mBegin = QString::number( dStart.year( ) ) + "-" + QString::number( dStart.month( ) ) + "-01 00:00:00"; QString mEnd = QString::number( dEnd.year( ) ) + "-" + QString::number( dEnd.month( ) + 1 ) + "-01 00:00:00"; switch ( rType ) { case CommonDataType::ReportYearly : strSql = "select f1,f2,sum( f3 ),sum(f4), sum(f5), sum(f6), sum(f7), sum(f8) from ( select year( intime ) f1, month( intime ) f2, count( cardno ) as f3, null as f4, null as f5, null as f6, null as f7, null as f8 from stoprd a where ( '"; strSql += yBegin; strSql += "' <= a.intime and a.intime < '"; strSql += yEnd; strSql += "' ) and a.cardkind = '"; strSql += strMonthCard; strSql += "' group by year( a.intime ), month( a.intime ) union all select year( feetime ) f1, month( feetime ) f2, null as f3, sum( feenumb ) f4, null as f5, null as f6, null as f7, null as f8 from feerd where ('"; strSql += yBegin; strSql += "' <= feetime and feetime < '"; strSql += yEnd; strSql += "') and feekind like '"; strSql += strMonthCard; strSql += "%' group by year( feetime ), month( feetime ) union all select year( intime ) f1, month( intime ) f2, count( cardno ) f3, null as f4, null as f5, null as f6, null as f7, null as f8 from stoprd a where ( '"; strSql += yBegin; strSql += "' <= a.intime and a.intime < '"; strSql += yEnd, strSql += "' ) and a.cardkind = '"; strSql += strTimeCard; strSql += "' group by year( a.intime ), month( a.intime ) union all select year( feetime ) f1, month( feetime ) f2, null as f3, sum( feenumb ) f4, null as f5, null as f6, null as f7, null as f8 from feerd where ('"; strSql += yBegin; strSql += "' <= feetime and feetime < '"; strSql += yEnd; strSql += "') and feekind like '"; strSql += strTimeCard; strSql += "%' group by year( feetime ), month( feetime ) union all select year( intime ) f1, month( intime ) f2, count( cardno ) f3, null as f4, null as f5, null as f6, null as f7, null as f8 from stoprd a where ( '"; strSql += yBegin; strSql += "' <= a.intime and a.intime < '"; strSql += yEnd, strSql += "' ) and a.cardkind = '"; strSql += strValueCard; strSql += "' group by year( a.intime ), month( a.intime ) union all select year( feetime ) f1, month( feetime ) f2, null as f3, sum( feenumb ) f4, null as f5, null as f6, null as f7, null as f8 from feerd where ('"; strSql += yBegin; strSql += "' <= feetime and feetime < '"; strSql += yEnd; strSql += "') and feekind like '"; strSql += strValue; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) ) x group by f1, f2"; break; case CommonDataType::ReportMonthly : strSql = "select f1,f2,f3,sum(f4), sum(f5), sum(f6), sum(f7), sum(f8), sum(f9) from ( select year( intime ) f1, month( intime ) f2, day( intime ) f3, count( cardno ) f4, null as f5, null as f6, null as f7, null as f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= a.intime and a.intime < '"; strSql += mEnd; strSql += "' ) and a.cardkind = '"; strSql += strMonthCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all ";/////////// strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, sum( feenumb ) f5, null as f6, null as f7, null as f8, null as f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= feetime and feetime < '"; strSql += mEnd; strSql += "') and feekind like '"; strSql += strMonthCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) "; strSql += "union all ";////////// strSql += "select year( intime ) f1, month( intime ) f2, day( intime ) f3, null as f4, null as f5, count( cardno ) f6, null as f7, null as f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= a.intime and a.intime < '"; strSql += mEnd; strSql += "' ) and a.cardkind = '"; strSql += strTimeCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all "; strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, null as f5, null as f6, sum( feenumb ) f7,null as f8, null as f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= feetime and feetime < '"; strSql += mEnd; strSql += "') and feekind like '"; strSql += strTimeCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) "; strSql += "union all ";////////// strSql += "select year( intime ) f1, month( intime ) f2, day( intime ) f3, null as f4, null as f5, null as f6, null as f7, count( cardno ) f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= a.intime and a.intime < '"; strSql += mEnd; strSql += "' ) and a.cardkind = '"; strSql += strValueCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all "; strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, null as f5, null as f6, null as f7, null as f8, sum( feenumb ) f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += mBegin; strSql += "' <= feetime and feetime < '"; strSql += mEnd; strSql += "') and feekind like '"; strSql += strValueCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) ) x group by f1, f2, f3"; break; case CommonDataType::ReportDaily : strSql = "select f1,f2,f3,sum(f4), sum(f5), sum(f6), sum(f7), sum(f8), sum(f9) from ( select year( intime ) f1, month( intime ) f2, day( intime ) f3, count( cardno ) f4, null as f5, null as f6, null as f7, null as f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += strStart; strSql += "' <= a.intime and a.intime < '"; strSql += strEnd; strSql += "' ) and a.cardkind = '"; strSql += strMonthCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all "; strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, sum( feenumb ) f5, null as f6, null as f7, null as f8, null as f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += strStart; strSql += "' <= feetime and feetime < '"; strSql += strEnd; strSql += "') and feekind like '"; strSql += strMonthCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) "; strSql += "union all "; strSql += "select year( intime ) f1, month( intime ) f2, day( intime ) f3, null as f4, null as f5, count( cardno ) f6, null as f7, null as f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += strStart; strSql += "' <= a.intime and a.intime < '"; strSql += strEnd; strSql += "' ) and a.cardkind = '"; strSql += strTimeCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all "; strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, null as f5, null as f6, sum( feenumb ) f7,null as f8, null as f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += strStart; strSql += "' <= feetime and feetime < '"; strSql += strEnd; strSql += "') and feekind like '"; strSql += strTimeCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) "; strSql += "union all "; strSql += "select year( intime ) f1, month( intime ) f2, day( intime ) f3, null as f4, null as f5, null as f6, null as f7, count( cardno ) f8, null as f9 "; strSql += "from stoprd a "; strSql += "where ( '"; strSql += strStart; strSql += "' <= a.intime and a.intime < '"; strSql + strEnd; strSql += "' ) and a.cardkind = '"; strSql += strValueCard; strSql += "' group by year( a.intime ), month( a.intime ), day( intime ) "; strSql += "union all "; strSql += "select year( feetime ) f1, month( feetime ) f2, day( feetime ) f3, null as f4, null as f5, null as f6, null as f7, null as f8, sum( feenumb ) f9 "; strSql += "from feerd "; strSql += "where ( '"; strSql += strStart; strSql += "' <= feetime and feetime < '"; strSql += strEnd; strSql += "') and feekind like '"; strSql += strValueCard; strSql += "%' group by year( feetime ), month( feetime ), day( feetime ) ) x group by f1, f2, f3"; break; case CommonDataType::ReportPerson : if ( bPersonTime ) { CCommonFunction::DateTime2String( dtTimeEnd, strEnd ); } strSql = "select feeoperator f1, sum( feenumb ) f2 from feerd where feetime between '"; strSql += strStart; strSql += "' and '"; strSql += strEnd; strSql += "' group by feeoperator"; break; case CommonDataType::ReportChannel : #if false ( select date( intime ) a, inshebeiname b , cardkind c, count( cardno ) d from stoprd where intime between '2011-01-01 00:00:00' and '2012-01-01 00:00:00' group by date( intime ), inshebeiname, cardkind order by date( intime ), inshebeiname, cardkind ) t1 -- inner join / cross full inner left right // union //union all union select * from ( select date( outtime ) a, outshebeiname b, cardkind c, count( cardno ) d from stoprd where outtime between '2011-01-01 00:00:00' and '2012-01-01 00:00:00' group by date( outtime ), outshebeiname, cardkind order by date( outtime ), outshebeiname, cardkind ) t2 order by a,b,c; #endif strSql = "select * from "; strSql += "( select date( intime ) a, inshebeiname b , cardkind c, count( cardno ) d from stoprd "; strSql += "where intime between '"; strSql += strStart; strSql += "' and '"; strSql += strEnd; strSql += "' group by date( intime ), inshebeiname, cardkind "; strSql += "order by date( intime ), inshebeiname, cardkind ) t1 "; strSql += "union "; strSql += "select * from ( select date( outtime ) a, outshebeiname b, cardkind c, count( cardno ) d from stoprd "; strSql += "where outtime between '"; strSql += strStart; strSql += "' and '"; strSql += strEnd; strSql += "' group by date( outtime ), outshebeiname, cardkind "; strSql += "order by date( outtime ), outshebeiname, cardkind ) t2 "; strSql += "order by a,b,c"; break; case CommonDataType::ReportTimeCardDetail : CCommonFunction::DateTime2String( dtTimeEnd, strEnd ); strSql = "select date( feetime ) ftime, feeoperator, feekind, feezkyy, sum( feenum ), "; strSql += "sum( feefactnum ), sum( feenum - feefactnum ) from stoprd "; strSql += "where feenum >= feefactnum and cardkind = '"; strSql += strTimeCard; strSql += "' and ( feetime between '"; strSql += strStart; strSql += "' and '"; strSql += strEnd; strSql += "' ) group by date( feetime ), feeoperator, feekind, feezkyy having ( sum( feenum ) + sum( feefactnum ) ) <> 0 "; strSql += "union all "; strSql += "select '合计' ftime, '', '', '', sum( feenum ), sum( feefactnum ), sum( feenum - feefactnum ) from stoprd "; strSql += "where feenum >= feefactnum and feetime between '"; strSql += strStart; strSql += "' and '"; strSql += strEnd; strSql += "' order by ftime desc"; break; case CommonDataType::ReportProvince : strSql = "select case substring( carcp, 1, 1 ) \ when '川' then '四川省' \ when '渝' then '重庆市' \ when '京' then '北京市' \ when '津' then '天津市' \ when '沪' then '上海市' \ when '冀' then '河北省' \ when '豫' then '河南省' \ when '云' then '云南省' \ when '辽' then '辽宁省' \ when '黑' then '黑龙江省' \ when '湘' then '湖南省' \ when '皖' then '安徽省' \ when '鲁' then '山东省' \ when '新' then '新疆自治区' \ when '苏' then '江苏省' \ when '浙' then '浙江省' \ when '赣' then '江西省' \ when '鄂' then '湖北省' \ when '桂' then '广西自治区' \ when '甘' then '甘肃省' \ when '晋' then '山西省' \ when '蒙' then '内蒙古自治区' \ when '陕' then '陕西省' \ when '吉' then '吉林省' \ when '闽' then '福建省' \ when '贵' then '贵州省' \ when '粤' then '广东省' \ when '青' then '青海省' \ when '藏' then '西藏自治区' \ when '宁' then '宁夏自治区' \ when '琼' then '海南省' \ else '未知' end as 车辆所属辖区, \ IFNULL( feekind, '小型车' ) as 车型, \ count( carcp ) as 车辆数 \ from stoprd \ where intime between '" + strStart + "' and '" + strEnd + //"' and cardkind = '计时卡' and feekind is not null group by substring( carcp, 1, 1 ) with rollup"; "' group by substring( carcp, 1, 1 ) with rollup"; break; case CommonDataType::ReportInProvince : strSql = "select case UPPER( substring( carcp, 2, 1 ) ) \ when 'A' then '成都市' \ when 'B' then '绵阳市' \ when 'C' then '自贡市' \ when 'D' then '攀枝花市' \ when 'E' then '泸州市' \ when 'F' then '德阳市' \ when 'H' then '广元市' \ when 'J' then '遂宁市' \ when 'K' then '内江市' \ when 'L' then '乐山市' \ when 'M' then '资阳市' \ when 'Q' then '宜宾市' \ when 'R' then '南充市' \ when 'S' then '达州市' \ when 'T' then '雅安市' \ when 'U' then '阿坝州' \ when 'V' then '甘孜州' \ when 'W' then '凉山州' \ when 'X' then '广安市' \ when 'Y' then '巴中市' \ when 'Z' then '眉山市' \ else '未知' end as 车辆所属辖区, \ IFNULL( feekind, '小型车' ) as 车型, \ count( carcp ) as 车辆数 \ from stoprd \ where intime between '" + strStart + "' and '" + strEnd + //"' and cardkind = '计时卡' and feekind is not null and substring( carcp, 1, 1 ) = '川' "' and substring( carcp, 1, 1 ) = '川' group by substring( carcp, 2, 1 ) with rollup"; break; case CommonDataType::ReportMonth : strSql = "select a.cardno, a.cardselfno , \ case a.EnterMustCard when 0 then '否' when 1 then '是' end as EnterMustCard, \ case a.LeaveMustCard when 0 then '否' when 1 then '是' end as LeaveMustCard, \ case a.MIMO when 0 then '否' when 1 then '是' end as MIMO, \ case a.Inside when 0 then '否' when 1 then '是' end as Inside, \ a.cardstate, a.starttime, a.endtime, b.carcp, c.username, c.userphone, \ IF ( a.starttime > a.endtime, '是', '否' ) as Expire \ from monthcard as a, carinfo as b, userinfo as c \ where a.cardno = b.cardindex and a.cardno = c.cardindex "; if ( 3 <= lstWheres.count( ) ){ int nIndex = lstWheres.at( 0 ).toInt( ); QString strDateRange = ""; switch ( nIndex ) { case 0 : strDateRange = QString( " And StartTime >='%1' And EndTime <= '%2' " ).arg( lstWheres.at( 1 ), lstWheres.at( 2 ) ); break; case 1 : strDateRange = QString( " And StartTime between '%1' And '%2' " ).arg( lstWheres.at( 1 ), lstWheres.at( 2 ) ); break; case 2 : strDateRange = QString( " And EndTime between '%1' And '%2' " ).arg( lstWheres.at( 1 ), lstWheres.at( 2 ) ); break; } strSql += strDateRange; } strSql += " order by Expire"; break; case CommonDataType::ReportMonthInOut : if ( 2 != lstWheres.length( ) ) { CCommonFunction::MsgBox( NULL, "提示", "请选择查询条件。", QMessageBox::Information ); return; } const QString& strWhere1 = lstWheres.at( 0 ); const QString& strWhere2 = lstWheres.at( 1 ); strSql = QString( "select m.cardno, m.cardselfno, m.carcp, m.username, m.userphone, s.counter from \ ( select a.cardno, a.cardselfno, b.carcp, c.username, c.userphone from \ monthcard as a, carinfo as b, userinfo as c \ where a.cardno = b.cardindex and a.cardno = c.cardindex \ %1) m, ( select cardno, count( cardno ) as counter from stoprd where \ %2 intime >= '%3' and outtime <= '%4' group by cardno \ order by cardno ) s where m.cardno = s.cardno" ).arg( strWhere1, strWhere2, strStart, strEnd ); break; } //#ifdef QT_NO_DEBUG QFile file( "d:/sql.txt" ); if ( !file.exists( ) || !file.open( QIODevice::WriteOnly ) ) { return; } // QDataStream QTextStream stream( &file ); stream << strSql; stream.flush( ); file.close( ); //#endif }