34,838
社区成员




void CEGraph2Doc::GenGrpTbl(const UINT CntTotalRc,const UINT CntRcPerGrp)
{
_RecordsetPtr pRs;
_variant_t var;
CString SqlCmd;
CString TmpTblName;
COleDateTime LastDateTime;
float FirstVolt;
float LastVolt;
float MinVolt;
float MaxVolt;
UINT i;
pRs.CreateInstance(__uuidof(Recordset));
TmpTblName.Format("tmp_%s_%u",m_TableName,CntRcPerGrp);
try {
//try to create temporary table if it doesn't exist
SqlCmd.Format("CREATE TABLE %s "
"(ID int PRIMARY KEY,"
"时刻 datetime,"
"type char(12),"
"grprc int,"
"first real,last real,min real,max real,"
"ma5 real,ma10 real,ma12 real,ma20 real,ma26 real,ma30 real,"
"a real,n real,"
"rsv real,z real,j real,y real)",
TmpTblName);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
//fill it
SqlCmd.Format("SELECT 时刻,电伏,规格 FROM %s ORDER BY 时刻",m_TableName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;
UINT _CntRcPerGrp=CntRcPerGrp;
for (i=0;i<CntTotalGrp;++i) {//groups that have enough records
LastDateTime=pRs->GetCollect("时刻");
FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));
if (i==CntTotalGrp-1 && CntLastGrpRc>0) {
_CntRcPerGrp=CntLastGrpRc;
}
for (UINT j=1;j<_CntRcPerGrp;++j) {
pRs->MoveNext();
LastVolt=float(pRs->GetCollect("电伏"));
MaxVolt=max(MaxVolt,LastVolt);
MinVolt=min(MinVolt,LastVolt);
}
CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));
SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "
"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",
TmpTblName,i,LastDateTime.Format(),
sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
pRs->MoveNext();
}
pRs->Close();
FillMA(TmpTblName,5,CntTotalGrp,0);
FillMA(TmpTblName,10,CntTotalGrp,0);
FillMA(TmpTblName,12,CntTotalGrp,0);
FillMA(TmpTblName,20,CntTotalGrp,0);
FillMA(TmpTblName,26,CntTotalGrp,0);
FillMA(TmpTblName,30,CntTotalGrp,0);
FillA(TmpTblName,CntTotalGrp,0);
FillN(TmpTblName,CntTotalGrp,0);
FillRSV(TmpTblName,CntTotalGrp,0);
FillZ(TmpTblName,CntTotalGrp,0);
FillJ(TmpTblName,CntTotalGrp,0);
FillY(TmpTblName,CntTotalGrp,0);
} catch (_com_error e) {
//already created, or hardly other error
SqlCmd.Format("SELECT SUM(grprc) as TotalRc FROM %s",TmpTblName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
var=pRs->GetCollect("TotalRc");
pRs->Close();
UINT n=var.uintVal;
if (CntTotalRc>n) {
int LastID=(n+CntRcPerGrp-1)/CntRcPerGrp-1;
UINT NewCntGrp=(CntTotalRc-LastID*CntRcPerGrp+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;
UINT _CntRcPerGrp=CntRcPerGrp;
SqlCmd.Format("DELETE FROM %s WHERE ID=%d",TmpTblName,LastID);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
SqlCmd.Format("SELECT 时刻,电伏,规格 FROM "
"(SELECT TOP %d 时刻,电伏,规格 FROM %s ORDER BY 时刻 DESC) as t1 ORDER BY 时刻",
CntTotalRc-LastID*CntRcPerGrp,m_TableName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
for (i=0;i<NewCntGrp;++i) {//groups that have enough records
LastDateTime=pRs->GetCollect("时刻");
FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));
if (i==NewCntGrp-1 && CntLastGrpRc>0) {
_CntRcPerGrp=CntLastGrpRc;
}
for (UINT j=1;j<_CntRcPerGrp;++j) {
pRs->MoveNext();
LastVolt=float(pRs->GetCollect("电伏"));
MaxVolt=max(MaxVolt,LastVolt);
MinVolt=min(MinVolt,LastVolt);
}
CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));
SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "
"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",
TmpTblName,LastID+i,LastDateTime.Format(),
sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
pRs->MoveNext();
}
pRs->Close();
FillMA(TmpTblName,5,CntTotalGrp,LastID);
FillMA(TmpTblName,10,CntTotalGrp,LastID);
FillMA(TmpTblName,12,CntTotalGrp,LastID);
FillMA(TmpTblName,20,CntTotalGrp,LastID);
FillMA(TmpTblName,26,CntTotalGrp,LastID);
FillMA(TmpTblName,30,CntTotalGrp,LastID);
FillA(TmpTblName,CntTotalGrp,LastID);
FillN(TmpTblName,CntTotalGrp,LastID);
FillRSV(TmpTblName,CntTotalGrp,LastID);
FillZ(TmpTblName,CntTotalGrp,LastID);
FillJ(TmpTblName,CntTotalGrp,LastID);
FillY(TmpTblName,CntTotalGrp,LastID);
}
}
if (pRs->State==adStateOpen) {
pRs->Close();
}
pRs.Release();
}
void CEGraph2Doc::GenGrpTbl(const UINT CntTotalRc,const UINT CntRcPerGrp)
{
_RecordsetPtr pRs;
_variant_t var;
CString SqlCmd;
CString TmpTblName;
COleDateTime LastDateTime;
float FirstVolt;
float LastVolt;
float MinVolt;
float MaxVolt;
UINT i;
pRs.CreateInstance(__uuidof(Recordset));
TmpTblName.Format("tmp_%s_%u",m_TableName,CntRcPerGrp);
try {
//try to create temporary table if it doesn't exist
SqlCmd.Format("CREATE TABLE %s "
"(ID int PRIMARY KEY,"
"时刻 datetime,"
"type char(12),"
"grprc int,"
"first real,last real,min real,max real,"
"ma5 real,ma10 real,ma12 real,ma20 real,ma26 real,ma30 real,"
"a real,n real,"
"rsv real,z real,j real,y real)",
TmpTblName);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
//fill it
SqlCmd.Format("SELECT 时刻,电伏,规格 FROM %s ORDER BY 时刻",m_TableName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;
UINT _CntRcPerGrp=CntRcPerGrp;
for (i=0;i<CntTotalGrp;++i) {//groups that have enough records
LastDateTime=pRs->GetCollect("时刻");
FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));
if (i==CntTotalGrp-1 && CntLastGrpRc>0) {
_CntRcPerGrp=CntLastGrpRc;
}
for (UINT j=1;j<_CntRcPerGrp;++j) {
pRs->MoveNext();
LastVolt=float(pRs->GetCollect("电伏"));
MaxVolt=max(MaxVolt,LastVolt);
MinVolt=min(MinVolt,LastVolt);
}
CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));
SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "
"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",
TmpTblName,i,LastDateTime.Format(),
sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
pRs->MoveNext();
}
pRs->Close();
FillMA(TmpTblName,5,CntTotalGrp,0);
FillMA(TmpTblName,10,CntTotalGrp,0);
FillMA(TmpTblName,12,CntTotalGrp,0);
FillMA(TmpTblName,20,CntTotalGrp,0);
FillMA(TmpTblName,26,CntTotalGrp,0);
FillMA(TmpTblName,30,CntTotalGrp,0);
FillA(TmpTblName,CntTotalGrp,0);
FillN(TmpTblName,CntTotalGrp,0);
FillRSV(TmpTblName,CntTotalGrp,0);
FillZ(TmpTblName,CntTotalGrp,0);
FillJ(TmpTblName,CntTotalGrp,0);
FillY(TmpTblName,CntTotalGrp,0);
} catch (_com_error e) {
//already created, or hardly other error
SqlCmd.Format("SELECT SUM(grprc) as TotalRc FROM %s",TmpTblName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
var=pRs->GetCollect("TotalRc");
pRs->Close();
UINT n=var.uintVal;
if (CntTotalRc>n) {
int LastID=(n+CntRcPerGrp-1)/CntRcPerGrp-1;
UINT NewCntGrp=(CntTotalRc-LastID*CntRcPerGrp+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;
UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;
UINT _CntRcPerGrp=CntRcPerGrp;
SqlCmd.Format("DELETE FROM %s WHERE ID=%d",TmpTblName,LastID);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
SqlCmd.Format("SELECT 时刻,电伏,规格 FROM "
"(SELECT TOP %d 时刻,电伏,规格 FROM %s ORDER BY 时刻 DESC) as t1 ORDER BY 时刻",
CntTotalRc-LastID*CntRcPerGrp,m_TableName);
pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);
for (i=0;i<NewCntGrp;++i) {//groups that have enough records
LastDateTime=pRs->GetCollect("时刻");
FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));
if (i==NewCntGrp-1 && CntLastGrpRc>0) {
_CntRcPerGrp=CntLastGrpRc;
}
for (UINT j=1;j<_CntRcPerGrp;++j) {
pRs->MoveNext();
LastVolt=float(pRs->GetCollect("电伏"));
MaxVolt=max(MaxVolt,LastVolt);
MinVolt=min(MinVolt,LastVolt);
}
CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));
SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "
"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",
TmpTblName,LastID+i,LastDateTime.Format(),
sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);
m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);
pRs->MoveNext();
}
pRs->Close();
FillMA(TmpTblName,5,CntTotalGrp,LastID);
FillMA(TmpTblName,10,CntTotalGrp,LastID);
FillMA(TmpTblName,12,CntTotalGrp,LastID);
FillMA(TmpTblName,20,CntTotalGrp,LastID);
FillMA(TmpTblName,26,CntTotalGrp,LastID);
FillMA(TmpTblName,30,CntTotalGrp,LastID);
FillA(TmpTblName,CntTotalGrp,LastID);
FillN(TmpTblName,CntTotalGrp,LastID);
FillRSV(TmpTblName,CntTotalGrp,LastID);
FillZ(TmpTblName,CntTotalGrp,LastID);
FillJ(TmpTblName,CntTotalGrp,LastID);
FillY(TmpTblName,CntTotalGrp,LastID);
}
}
if (pRs->State==adStateOpen) {
pRs->Close();
}
pRs.Release();
}