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
}
