int CMyDatabase::getProvinceid(CString provinceName)
{
CString SqlString2 = _T("SELECT * FROM Province WHERE Province='")+provinceName+_T("' ");
CRecordset *record;
record= new CRecordset(this->m_nDatabase);
record->Open(CRecordset::dynaset,SqlString2);
CString tempStr=_T("0");
int PID=0;
if (!record->IsEOF())
{
record->MoveFirst();
record->GetFieldValue(_T("ProvinceID"),tempStr);
}
PID = _ttoi(tempStr) ;
return PID;
}
int CMyDatabase::getTypesID(CString TypesName)
{
CString SqlString2 = _T("SELECT * FROM types WHERE Name='")+TypesName+_T("' ");
CRecordset *record;
record= new CRecordset(this->m_nDatabase);
record->Open(CRecordset::dynaset,SqlString2);
CString tempStr=_T("0");
int PID=0;
if (!record->IsEOF())
{
record->MoveFirst();
record->GetFieldValue(_T("CID"),tempStr);
}
PID = _ttoi(tempStr) ;
return PID;
}
void CMyDatabase::addTypeName(CString TypeName,CString PTypeName)
{
CString SqlString2 = _T("SELECT * FROM types WHERE Name='")+PTypeName+_T("' ");
CRecordset *record;
record= new CRecordset(this->m_nDatabase);
record->Open(CRecordset::dynaset,SqlString2);
CString tempStr=_T("0");
if (!record->IsEOF())
{
record->MoveFirst();
record->GetFieldValue(_T("CID"),tempStr);
}
CString SqlString;
SqlString.Format(_T("INSERT INTO types (Name,PID) VALUES('%s',%s)"),TypeName,tempStr);
this->m_nDatabase->ExecuteSQL(SqlString);
}
UINT ThreadUpdatePLSModel(LPVOID lpParam)
{
double coeff=2;//系数
CDBManageTrueDlg* pCDBManageTrueDlg=(CDBManageTrueDlg*)lpParam;
CTrueWineLib TrueWineLib;
CWineTypeIndex WineTypeIndex;
CAlcoholTypeIndex AlcoholTypeIndex;
CFlavourTypeIndex FlavourTypeIndex;
CBrandTypeIndex BrandTypeIndex;
CPLSModel PLSModel;
map<CString,int> map_TrueWineIndex;
map<CString, int> map_AlcoholContent;
map<CString,int> map_Flavour;
map<CString,int> map_Brand;
if(!PLSModel.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开PLS模型库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(!PLSModel.IsEOF())
{
PLSModel.MoveFirst();
}
while(!PLSModel.IsEOF())
{
PLSModel.Delete();
PLSModel.MoveNext();
}
//打开索引库
if(!WineTypeIndex.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开真酒索引库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(0==WineTypeIndex.GetRecordCount())
{
pCDBManageTrueDlg->MessageBox(L"光谱管理信息出现错误，请尝试更新！",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(!AlcoholTypeIndex.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开酒精度索引库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(0==AlcoholTypeIndex.GetRecordCount())
{
pCDBManageTrueDlg->MessageBox(L"光谱管理信息出现错误，请尝试更新！",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(!FlavourTypeIndex.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开香型索引库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(0==FlavourTypeIndex.GetRecordCount())
{
pCDBManageTrueDlg->MessageBox(L"光谱管理信息出现错误，请尝试更新！",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(!BrandTypeIndex.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开品牌索引库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(0==BrandTypeIndex.GetRecordCount())
{
pCDBManageTrueDlg->MessageBox(L"光谱管理信息出现错误，请尝试更新！",L"更新模型失败",MB_ICONERROR);
return -1;
}
/////////////////////////
WineTypeIndex.MoveFirst();
while(!WineTypeIndex.IsEOF())
{
map_TrueWineIndex[WineTypeIndex.m_WineName]=WineTypeIndex.m_TypeIndex;
WineTypeIndex.MoveNext();
}
AlcoholTypeIndex.MoveFirst();
while(!AlcoholTypeIndex.IsEOF())
{
map_AlcoholContent[AlcoholTypeIndex.m_AlcoholContent]=AlcoholTypeIndex.m_AlcoholIndex;
AlcoholTypeIndex.MoveNext();
}
AlcoholTypeIndex.MoveFirst();
FlavourTypeIndex.MoveFirst();
while(!FlavourTypeIndex.IsEOF())
{
map_Flavour[FlavourTypeIndex.m_Flavour]=FlavourTypeIndex.m_FlavourIndex;
FlavourTypeIndex.MoveNext();
}
FlavourTypeIndex.MoveFirst();
BrandTypeIndex.MoveFirst();
while(!BrandTypeIndex.IsEOF())
{
map_Brand[BrandTypeIndex.m_Brand]=BrandTypeIndex.m_BrandIndex;
BrandTypeIndex.MoveNext();
}
BrandTypeIndex.MoveFirst();
if(!TrueWineLib.Open())
{
pCDBManageTrueDlg->MessageBox(L"打开真酒库失败",L"更新模型失败",MB_ICONERROR);
return -1;
}
if(TrueWineLib.IsEOF())
{
pCDBManageTrueDlg->MessageBox(L"真酒库中无光谱记录",L"更新模型失败",MB_ICONERROR);
return -1;
}
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
//酒精度分类模型
int Num_of_AlcoholContent=AlcoholTypeIndex.GetRecordCount();
if(1!=Num_of_AlcoholContent)
{
CMatrix MNum_EachLabel(1,Num_of_AlcoholContent);
CMatrix MLabels(1,Num_of_AlcoholContent);
CMatrix X_1(TrueWineLib.GetRecordCount(),869);
CMatrix Y_1(TrueWineLib.GetRecordCount(),1);
int AlcoholTypeCount=0;
//CSVMobj tempsvmobj1(869);
if(!AlcoholTypeIndex.IsEOF())
{
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
int Num_inX_1=0;
AlcoholTypeIndex.MoveFirst();
while(!AlcoholTypeIndex.IsEOF())
{
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s'",AlcoholTypeIndex.m_AlcoholContent);
TrueWineLib.m_strSort=L"WineName";
if(!TrueWineLib.Requery())
{
pCDBManageTrueDlg->MessageBox(L"查询真酒库失败",L"更新酒精度模型失败",MB_ICONERROR);
return -1;
}
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
int Num_of_EachAlcoholContent=TrueWineLib.GetRecordCount();
TrueWineLib.MoveFirst();
CMatrix tempMatrix(1,869);
int m=1;
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
CString2Matrix(tempMatrix,TrueWineLib.m_SpectrumData,L",");
X_1.SetRow(Num_inX_1+m,tempMatrix);
Y_1.SetItem(Num_inX_1+m,1,map_AlcoholContent[TrueWineLib.m_AlcoholContent]);
TrueWineLib.MoveNext();
m++;
}
//TrueWineLib.Close();
AlcoholTypeCount++;
Num_inX_1+=Num_of_EachAlcoholContent;
MLabels.SetItem(1,AlcoholTypeCount,map_AlcoholContent[TrueWineLib.m_AlcoholContent]);
MNum_EachLabel.SetItem(1,AlcoholTypeCount,Num_of_EachAlcoholContent);
AlcoholTypeIndex.MoveNext();
}
}
//训练酒精度分类模型
CMatrix Y_1map=BuildMapLabel(Y_1);
//CMatrix X_1_mean=X_1.Mean();
CMatrix CV=Plscvfold(X_1,Y_1map,X_1.m_row,"center");
CMatrixIndex CV_indexmin;
CV.FindMin(CV_indexmin);
int bestPC=CV_indexmin.column;
CMatrix W(X_1.m_column,bestPC);
CMatrix T(X_1.m_row,bestPC);
CMatrix P(X_1.m_column,bestPC);
CMatrix Q(bestPC,1);
CMatrix para2(1,X_1.m_column);//=X_1.Mean();
CMatrix para1(1,X_1.m_column);//=X_1.Mean();
CMatrix para2_X(1,X_1.m_column);//=X_1.Mean();
CMatrix para1_X(1,X_1.m_column);//=X_1.Mean();
Pretreat(X_1,"center",para1_X,para2_X);
CMatrix X_1_mean=para1_X;
//map<int,int> Label_map;
CMatrix para2_Y(1,Y_1map.m_column);
CMatrix para1_Y(1,Y_1map.m_column);
Pretreat(Y_1map,"center",para1_Y,para2_Y);
CMatrix Y_1_mean=para1_Y;
CMatrix B=Pls_nipals(X_1,Y_1map,W,T,P,Q,bestPC);
CMatrix StandardPC(AlcoholTypeCount,bestPC);
int begin=1;
for(int i=1;i<=AlcoholTypeCount;i++)
{
StandardPC.SetRow(i,(T.GetRows(begin,begin+MNum_EachLabel(1,i)-1)).Mean());
begin+=MNum_EachLabel(1,i);
}
//计算阈值
CMatrix Threshold(1,AlcoholTypeCount);//计算样本与标准值之间的最大距离,样本与标准值之间的平均距离的coeff倍，取较大的那个
//CMatrix Threshold2(1,AlcoholTypeCount);//计算
begin=1;
for(int i=1;i<=AlcoholTypeCount;i++)
{
CMatrix tempdist(MNum_EachLabel(1,i),1);
CMatrix tempPC=T.GetRows(begin,begin+MNum_EachLabel(1,i)-1);
CMatrix covPC=tempPC.covariance();
covPC.Inv();
int k=1;
for(int j=begin;j<=begin+MNum_EachLabel(1,i)-1;j++)
{
CMatrix diff=T.GetRow(j)-StandardPC.GetRow(i);
tempdist.SetItem(k,1,(diff*covPC*diff.Transposition())(1,1));
k++;
}
Threshold.SetItem(1,i,Max(tempdist.GetMax(),tempdist.GetMean()*coeff));
begin=begin+MNum_EachLabel(1,i);
}
CString Weight;
CString Labels;
CString Num_EachLabel;
CString standardPC;
CString TrainsetPC;
CString TrainsetMean;
CString threshold;
//Weight
W.Matrix2CString(Weight,L",");
//Labels
MLabels.Matrix2CString(Labels,L",");
//Num_EachLabel
MNum_EachLabel.Matrix2CString(Num_EachLabel,L",");
//standardPC
StandardPC.Matrix2CString(standardPC,L",");
//TrainsetPC
T.Matrix2CString(TrainsetPC,L",");
//TrainsetMean
X_1_mean.Matrix2CString(TrainsetMean,L",");
//阈值
Threshold.Matrix2CString(threshold,L",");
//保存模型
PLSModel.AddNew();
PLSModel.m_FactorNum=bestPC;
PLSModel.m_ClassCount=AlcoholTypeCount;
PLSModel.m_Weight=Weight;
PLSModel.m_Labels=Labels;
PLSModel.m_Num_EachLabel=Num_EachLabel;
PLSModel.m_StandardPC=standardPC;
PLSModel.m_TrainsetPC=TrainsetPC;
PLSModel.m_TrainsetMean=TrainsetMean;
PLSModel.m_Threshold=threshold;
PLSModel.Update();
}
//开始香型
AlcoholTypeIndex.MoveFirst();
while(!AlcoholTypeIndex.IsEOF())
{
CString command=_T("SELECT DISTINCT Flavour FROM TrueWineLib");
CString tempFlavourInAlconhol;
CDatabase m_db;
CRecordset rsforFlavour;
rsforFlavour.m_strFilter.Format(L"AlcoholContent='%s'",AlcoholTypeIndex.m_AlcoholContent);
m_db.OpenEx(_T("DSN=白酒鉴定与溯源数据库;"),CDatabase::noOdbcDialog);
rsforFlavour.m_pDatabase = &m_db;
if (!rsforFlavour.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
CString str;
str.Format(L"更新%s度香型分类模型失败！",AlcoholTypeIndex.m_AlcoholContent);
pCDBManageTrueDlg->MessageBox(str,L"打开数据库失败",MB_ICONERROR);
return -1;
}
if(!rsforFlavour.IsEOF())
{
rsforFlavour.MoveFirst();
while(!rsforFlavour.IsEOF())
{
rsforFlavour.MoveNext();
}
}
int Num_of_FlavourInAlcoholContent=rsforFlavour.GetRecordCount();
if(1!=Num_of_FlavourInAlcoholContent)
{
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s'",AlcoholTypeIndex.m_AlcoholContent);
TrueWineLib.Requery();
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
CMatrix MNum_EachLabel(1,Num_of_FlavourInAlcoholContent);
CMatrix MLabels(1,Num_of_FlavourInAlcoholContent);
CMatrix X_2(TrueWineLib.GetRecordCount(),869);
CMatrix Y_2(TrueWineLib.GetRecordCount(),1);
int FlavourTypeCount=0;
int Num_inX_2=0;
rsforFlavour.MoveFirst();
while(!rsforFlavour.IsEOF())
{
rsforFlavour.GetFieldValue(L"Flavour",tempFlavourInAlconhol);
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s'AND Flavour='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavourInAlconhol);
if(!TrueWineLib.Requery())
{
pCDBManageTrueDlg->MessageBox(L"查询真酒库失败",L"更新香型模型失败",MB_ICONERROR);
return -1;
}
if(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
}
int Num_of_EachFlavorInAlcoholContent=TrueWineLib.GetRecordCount();
CMatrix tempMatrix(1,869);
int m=1;
while(!TrueWineLib.IsEOF())
{
CString2Matrix(tempMatrix,TrueWineLib.m_SpectrumData,L",");
X_2.SetRow(Num_inX_2+m,tempMatrix);
Y_2.SetItem(Num_inX_2+m,1,map_Flavour[TrueWineLib.m_Flavour]);
TrueWineLib.MoveNext();
m++;
}
FlavourTypeCount++;
Num_inX_2+=Num_of_EachFlavorInAlcoholContent;
MLabels.SetItem(1,FlavourTypeCount,map_Flavour[TrueWineLib.m_Flavour]);
MNum_EachLabel.SetItem(1,FlavourTypeCount,Num_of_EachFlavorInAlcoholContent);
rsforFlavour.MoveNext();
}
//训练香型分类模型
//CMatrix X_2_mean=X_2.Mean();
CMatrix Y_2map=BuildMapLabel(Y_2);
CMatrix CV=Plscvfold(X_2,Y_2map,X_2.m_row,"center");
CMatrixIndex CV_indexmin;
CV.FindMin(CV_indexmin);
int bestPC=CV_indexmin.column;
CMatrix W(X_2.m_column,bestPC);
CMatrix T(X_2.m_row,bestPC);
CMatrix P(X_2.m_column,bestPC);
CMatrix Q(bestPC,1);
CMatrix para2_X(1,X_2.m_column);//=X_1.Mean();
CMatrix para1_X(1,X_2.m_column);//=X_1.Mean();
Pretreat(X_2,"center",para1_X,para2_X);
CMatrix X_2_mean=para1_X;
CMatrix para2_Y(1,Y_2map.m_column);//=X_1.Mean();
CMatrix para1_Y(1,Y_2map.m_column);//=X_1.Mean();
Pretreat(Y_2map,"center",para1_Y,para2_Y);
CMatrix Y_2_mean=para1_Y;
CMatrix B=Pls_nipals(X_2,Y_2map,W,T,P,Q,bestPC);
CMatrix StandardPC(FlavourTypeCount,bestPC);
int begin=1;
for(int i=1;i<=FlavourTypeCount;i++)
{
StandardPC.SetRow(i,(T.GetRows(begin,begin+MNum_EachLabel(1,i)-1)).Mean());
begin+=MNum_EachLabel(1,i);
}
//计算阈值
CMatrix Threshold(1,FlavourTypeCount);//计算样本与标准值之间的最大距离,样本与标准值之间的平均距离的coeff倍，取较大的那个
begin=1;
for(int i=1;i<=FlavourTypeCount;i++)
{
CMatrix tempdist(MNum_EachLabel(1,i),1);
CMatrix tempPC=T.GetRows(begin,begin+MNum_EachLabel(1,i)-1);
CMatrix covPC=tempPC.covariance();
covPC.Inv();
int k=1;
for(int j=begin;j<=begin+MNum_EachLabel(1,i)-1;j++)
{
CMatrix diff=T.GetRow(j)-StandardPC.GetRow(i);
tempdist.SetItem(k,1,(diff*covPC*diff.Transposition())(1,1));
k++;
}
Threshold.SetItem(1,i,Max(tempdist.GetMax(),tempdist.GetMean()*coeff));
begin=begin+MNum_EachLabel(1,i);
}
CString Weight;
CString Labels;
CString Num_EachLabel;
CString standardPC;
CString TrainsetPC;
CString TrainsetMean;
CString threshold;
//Weight
W.Matrix2CString(Weight,L",");
//Labels
MLabels.Matrix2CString(Labels,L",");
//Num_EachLabel
MNum_EachLabel.Matrix2CString(Num_EachLabel,L",");
//standardPC
StandardPC.Matrix2CString(standardPC,L",");
//TrainsetPC
T.Matrix2CString(TrainsetPC,L",");
//TrainsetMean
X_2_mean.Matrix2CString(TrainsetMean,L",");
//阈值
Threshold.Matrix2CString(threshold,L",");
//保存模型
PLSModel.AddNew();
PLSModel.m_AlcoholContent=AlcoholTypeIndex.m_AlcoholContent;
PLSModel.m_FactorNum=bestPC;
PLSModel.m_ClassCount=FlavourTypeCount;
PLSModel.m_Weight=Weight;
PLSModel.m_Labels=Labels;
PLSModel.m_Num_EachLabel=Num_EachLabel;
PLSModel.m_StandardPC=standardPC;
PLSModel.m_TrainsetPC=TrainsetPC;
PLSModel.m_TrainsetMean=TrainsetMean;
PLSModel.m_Threshold=threshold;
PLSModel.Update();
}
/////////////////////////////////////////////
//开始品牌
rsforFlavour.MoveFirst();
while(!rsforFlavour.IsEOF())
{
CString tempFlavour;
rsforFlavour.GetFieldValue(L"Flavour",tempFlavour);
CString command=_T("SELECT DISTINCT Brand FROM TrueWineLib");
CDatabase m_db;
CRecordset rsforBrand;
rsforBrand.m_strFilter.Format(L"AlcoholContent='%s' AND Flavour='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavour);
m_db.OpenEx(_T("DSN=白酒鉴定与溯源数据库;"),CDatabase::noOdbcDialog);
rsforBrand.m_pDatabase = &m_db;
if (!rsforBrand.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
CString str;
str.Format(L"更新%s度%s分类模型失败！",AlcoholTypeIndex.m_AlcoholContent,tempFlavour);
pCDBManageTrueDlg->MessageBox(str,L"打开数据库失败",MB_ICONERROR);
return -1;
}
if(!rsforBrand.IsEOF())
{
rsforBrand.MoveFirst();
}
while(!rsforBrand.IsEOF())
{
rsforBrand.MoveNext();
}
int Num_of_BrandInFlavour=rsforBrand.GetRecordCount();
if(1!=Num_of_BrandInFlavour)
{
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s' AND Flavour='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavour);
TrueWineLib.Requery();
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
CMatrix MNum_EachLabel(1,Num_of_BrandInFlavour);
CMatrix MLabels(1,Num_of_BrandInFlavour);
CMatrix X_3(TrueWineLib.GetRecordCount(),869);
CMatrix Y_3(TrueWineLib.GetRecordCount(),1);
int BrandTypeCount=0;
int Num_inX_3=0;
rsforBrand.MoveFirst();
while(!rsforBrand.IsEOF())
{
CString tempBrandInFlavour;
rsforBrand.GetFieldValue(L"Brand",tempBrandInFlavour);
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s'AND Flavour='%s' AND Brand='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavour,tempBrandInFlavour);
if(!TrueWineLib.Requery())
{
pCDBManageTrueDlg->MessageBox(L"打开真酒库失败",L"更新酒精度模型失败",MB_ICONERROR);
return -1;
}
if(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
}
int Num_of_EachBrandInFlavour=TrueWineLib.GetRecordCount();
CMatrix tempMatrix(1,869);
int m=1;
//TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
CString2Matrix(tempMatrix,TrueWineLib.m_SpectrumData,L",");
X_3.SetRow(Num_inX_3+m,tempMatrix);
Y_3.SetItem(Num_inX_3+m,1,map_Brand[TrueWineLib.m_Brand]);
TrueWineLib.MoveNext();
m++;
}
BrandTypeCount++;
Num_inX_3+=Num_of_EachBrandInFlavour;
MLabels.SetItem(1,BrandTypeCount,map_Brand[TrueWineLib.m_Brand]);
MNum_EachLabel.SetItem(1,BrandTypeCount,Num_of_EachBrandInFlavour);
rsforBrand.MoveNext();
}
//
// CMatrix X_3_mean=X_3.Mean();
CMatrix Y_3map=BuildMapLabel(Y_3);
CMatrix CV=Plscvfold(X_3,Y_3map,X_3.m_row,"center");
CMatrixIndex CV_indexmin;
CV.FindMin(CV_indexmin);
int bestPC=CV_indexmin.column;
CMatrix W(X_3.m_column,bestPC);
CMatrix T(X_3.m_row,bestPC);
CMatrix P(X_3.m_column,bestPC);
CMatrix Q(bestPC,1);
CMatrix para2_X(1,X_3.m_column);//=X_1.Mean();
CMatrix para1_X(1,X_3.m_column);//=X_1.Mean();
Pretreat(X_3,"center",para1_X,para2_X);
CMatrix X_3_mean=para1_X;
CMatrix para2_Y(1,Y_3map.m_column);//=X_1.Mean();
CMatrix para1_Y(1,Y_3map.m_column);//=X_1.Mean();
Pretreat(Y_3map,"center",para1_Y,para2_Y);
CMatrix Y_3_mean=para1_Y;
CMatrix B=Pls_nipals(X_3,Y_3map,W,T,P,Q,bestPC);
CMatrix StandardPC(BrandTypeCount,bestPC);
int begin=1;
for(int i=1;i<=BrandTypeCount;i++)
{
StandardPC.SetRow(i,(T.GetRows(begin,begin+MNum_EachLabel(1,i)-1)).Mean());
begin+=MNum_EachLabel(1,i);
}
//计算阈值
CMatrix Threshold(1,BrandTypeCount);//计算样本与标准值之间的最大距离,样本与标准值之间的平均距离的coeff倍，取较大的那个
begin=1;
for(int i=1;i<=BrandTypeCount;i++)
{
CMatrix tempdist(MNum_EachLabel(1,i),1);
CMatrix tempPC=T.GetRows(begin,begin+MNum_EachLabel(1,i)-1);
CMatrix covPC=tempPC.covariance();
covPC.Inv();
int k=1;
for(int j=begin;j<=begin+MNum_EachLabel(1,i)-1;j++)
{
CMatrix diff=T.GetRow(j)-StandardPC.GetRow(i);
tempdist.SetItem(k,1,(diff*covPC*diff.Transposition())(1,1));
k++;
}
Threshold.SetItem(1,i,Max(tempdist.GetMax(),tempdist.GetMean()*coeff));
begin=begin+MNum_EachLabel(1,i);
}
CString Weight;
CString Labels;
CString Num_EachLabel;
CString standardPC;
CString TrainsetPC;
CString TrainsetMean;
CString threshold;
//Weight
W.Matrix2CString(Weight,L",");
//Labels
MLabels.Matrix2CString(Labels,L",");
//Num_EachLabel
MNum_EachLabel.Matrix2CString(Num_EachLabel,L",");
//standardPC
StandardPC.Matrix2CString(standardPC,L",");
//TrainsetPC
T.Matrix2CString(TrainsetPC,L",");
//TrainsetMean
X_3_mean.Matrix2CString(TrainsetMean,L",");
//阈值
Threshold.Matrix2CString(threshold,L",");
//保存模型
PLSModel.AddNew();
PLSModel.m_AlcoholContent=AlcoholTypeIndex.m_AlcoholContent;
PLSModel.m_Flavour=tempFlavour;
PLSModel.m_FactorNum=bestPC;
PLSModel.m_ClassCount=BrandTypeCount;
PLSModel.m_Weight=Weight;
PLSModel.m_Labels=Labels;
PLSModel.m_Num_EachLabel=Num_EachLabel;
PLSModel.m_StandardPC=standardPC;
PLSModel.m_TrainsetPC=TrainsetPC;
PLSModel.m_TrainsetMean=TrainsetMean;
PLSModel.m_Threshold=threshold;
PLSModel.Update();
}
/////////////////////////////////////////
//开始品牌内
rsforBrand.MoveFirst();
while(!rsforBrand.IsEOF())
{
CString tempBrand;
rsforBrand.GetFieldValue(L"Brand",tempBrand);
CString command=_T("SELECT DISTINCT WineName FROM TrueWineLib");
CString tempInBrand;
CDatabase m_db;
CRecordset rsforInBrand;
rsforInBrand.m_strFilter.Format(L"AlcoholContent='%s' AND Flavour='%s' AND Brand='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavour,tempBrand);
m_db.OpenEx(_T("DSN=白酒鉴定与溯源数据库;"),CDatabase::noOdbcDialog);
rsforInBrand.m_pDatabase = &m_db;
if (!rsforInBrand.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
CString str;
str.Format(L"更新%s度%s%s分类模型失败！",AlcoholTypeIndex.m_AlcoholContent,tempFlavour,tempBrand);
pCDBManageTrueDlg->MessageBox(str,L"打开数据库失败",MB_ICONERROR);
return -1;
}
if(!rsforInBrand.IsEOF())
{
rsforInBrand.MoveFirst();
}
while(!rsforInBrand.IsEOF())
{
rsforInBrand.MoveNext();
}
int Num_of_InBrand=rsforInBrand.GetRecordCount();
if(1!=Num_of_InBrand)
{
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s' AND Flavour='%s' AND Brand='%s'",AlcoholTypeIndex.m_AlcoholContent,tempFlavour,tempBrand);
TrueWineLib.Requery();
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
CMatrix MNum_EachLabel(1,Num_of_InBrand);
CMatrix MLabels(1,Num_of_InBrand);
CMatrix X_4(TrueWineLib.GetRecordCount(),869);
CMatrix Y_4(TrueWineLib.GetRecordCount(),1);
int InBrandTypeCount=0;
int Num_inX_4=0;
rsforInBrand.MoveFirst();
while(!rsforInBrand.IsEOF())
{
rsforInBrand.GetFieldValue(L"WineName",tempInBrand);
TrueWineLib.m_strFilter.Format(L"AlcoholContent='%s'AND Flavour='%s' AND Brand='%s' AND WineName='%s'",
AlcoholTypeIndex.m_AlcoholContent,tempFlavour,tempBrand,tempInBrand);
if(!TrueWineLib.Requery())
{
pCDBManageTrueDlg->MessageBox(L"打开真酒库失败",L"更新品牌内模型失败",MB_ICONERROR);
return -1;
}
if(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveFirst();
while(!TrueWineLib.IsEOF())
{
TrueWineLib.MoveNext();
}
TrueWineLib.MoveFirst();
}
int Num_of_EachTypeInBrand=TrueWineLib.GetRecordCount();
CMatrix tempMatrix(1,869);
int m=1;
//TrueWineLib.MoveFirst();
int DataStrlenth=TrueWineLib.m_SpectrumData.GetLength();
while(!TrueWineLib.IsEOF())
{
CString2Matrix(tempMatrix,TrueWineLib.m_SpectrumData,L",");
X_4.SetRow(Num_inX_4+m,tempMatrix);
Y_4.SetItem(Num_inX_4+m,1,map_TrueWineIndex[TrueWineLib.m_WineName]);
TrueWineLib.MoveNext();
m++;
}
InBrandTypeCount++;
Num_inX_4+=Num_of_EachTypeInBrand;
MLabels.SetItem(1,InBrandTypeCount,map_TrueWineIndex[TrueWineLib.m_WineName]);
MNum_EachLabel.SetItem(1,InBrandTypeCount,Num_of_EachTypeInBrand);
rsforInBrand.MoveNext();
}
//
// CMatrix X_4_mean=X_4.Mean();
CMatrix Y_4map=BuildMapLabel(Y_4);
CMatrix CV=Plscvfold(X_4,Y_4map,X_4.m_row,"center");
CMatrixIndex CV_indexmin;
CV.FindMin(CV_indexmin);
int bestPC=CV_indexmin.column;
CMatrix W(X_4.m_column,bestPC);
CMatrix T(X_4.m_row,bestPC);
CMatrix P(X_4.m_column,bestPC);
CMatrix Q(bestPC,1);
CMatrix para2_X(1,X_4.m_column);//=X_1.Mean();
CMatrix para1_X(1,X_4.m_column);//=X_1.Mean();
Pretreat(X_4,"center",para1_X,para2_X);
CMatrix X_4_mean=para1_X;
//map<int,int> Label_map;
CMatrix para2_Y(1,Y_4map.m_column);//=X_1.Mean();
CMatrix para1_Y(1,Y_4map.m_column);//=X_1.Mean();
Pretreat(Y_4map,"center",para1_Y,para2_Y);
CMatrix Y_4_mean=para1_Y;
CMatrix B=Pls_nipals(X_4,Y_4map,W,T,P,Q,bestPC);
CMatrix StandardPC(InBrandTypeCount,bestPC);
int begin=1;
for(int i=1;i<=InBrandTypeCount;i++)
{
StandardPC.SetRow(i,(T.GetRows(begin,begin+MNum_EachLabel(1,i)-1)).Mean());
begin+=MNum_EachLabel(1,i);
}
CMatrix Threshold(1,InBrandTypeCount);//计算样本与标准值之间的最大距离,样本与标准值之间的平均距离的coeff倍，取较大的那个
begin=1;
for(int i=1;i<=InBrandTypeCount;i++)
{
CMatrix tempdist(MNum_EachLabel(1,i),1);
CMatrix tempPC=T.GetRows(begin,begin+MNum_EachLabel(1,i)-1);
CMatrix covPC=tempPC.covariance();
covPC.Inv();
int k=1;
for(int j=begin;j<=begin+MNum_EachLabel(1,i)-1;j++)
{
CMatrix diff=T.GetRow(j)-StandardPC.GetRow(i);
tempdist.SetItem(k,1,(diff*covPC*diff.Transposition())(1,1));
k++;
}
Threshold.SetItem(1,i,Max(tempdist.GetMax(),tempdist.GetMean()*coeff));
begin=begin+MNum_EachLabel(1,i);
}
CString Weight;
CString Labels;
CString Num_EachLabel;
CString standardPC;
CString TrainsetPC;
CString TrainsetMean;
CString threshold;
//Weight
W.Matrix2CString(Weight,L",");
//Labels
MLabels.Matrix2CString(Labels,L",");
//Num_EachLabel
MNum_EachLabel.Matrix2CString(Num_EachLabel,L",");
//standardPC
StandardPC.Matrix2CString(standardPC,L",");
//TrainsetPC
T.Matrix2CString(TrainsetPC,L",");
//TrainsetMean
X_4_mean.Matrix2CString(TrainsetMean,L",");
//阈值
Threshold.Matrix2CString(threshold,L",");
//保存模型
PLSModel.AddNew();
PLSModel.m_AlcoholContent=AlcoholTypeIndex.m_AlcoholContent;
PLSModel.m_Flavour=tempFlavour;
PLSModel.m_Brand=tempBrand;
PLSModel.m_FactorNum=bestPC;
PLSModel.m_ClassCount=InBrandTypeCount;
PLSModel.m_Weight=Weight;
PLSModel.m_Labels=Labels;
PLSModel.m_Num_EachLabel=Num_EachLabel;
PLSModel.m_StandardPC=standardPC;
PLSModel.m_TrainsetPC=TrainsetPC;
PLSModel.m_TrainsetMean=TrainsetMean;
PLSModel.m_Threshold=threshold;
PLSModel.Update();
}
rsforBrand.MoveNext();
}
///////////////////////////////
rsforFlavour.MoveNext();
}
AlcoholTypeIndex.MoveNext();
}
TrueWineLib.Close();
WineTypeIndex.Close();
AlcoholTypeIndex.Close();
FlavourTypeIndex.Close();
BrandTypeIndex.Close();
pCDBManageTrueDlg->MessageBox(L"模型更新成功！",L"模型更新",MB_ICONINFORMATION);
//删除不需要的模型文件
}
void CDBManageTrueDlg::UpdateIndexTable(void)
{
//CTrueWineLib TrueWineLib;
CDatabase m_db;
CRecordset rs;
m_db.OpenEx(_T("DSN=白酒鉴定与溯源数据库;"),CDatabase::noOdbcDialog);
rs.m_pDatabase = &m_db;
CString WineName;
CString AlcoholContent;
CString Flavour;
CString Brand;
CString ProductionBatchNo;
//更新白酒类型索引
CWineTypeIndex WineTypeIndex;
if(!WineTypeIndex.Open())
{
MessageBox(L"打开光谱索引库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!WineTypeIndex.IsEOF())
{
WineTypeIndex.MoveFirst();
while(!WineTypeIndex.IsEOF())
{
WineTypeIndex.Delete();
WineTypeIndex.MoveNext();
}
}
CString command=_T("SELECT DISTINCT WineName,AlcoholContent,Flavour,Brand,ProductionBatchNo FROM TrueWineLib");
if(!rs.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
MessageBox(L"打开真酒库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!rs.IsEOF())
{
int count=1;
rs.MoveFirst();
while(!rs.IsEOF())
{
rs.GetFieldValue(L"WineName",WineName);
rs.GetFieldValue(L"AlcoholContent",AlcoholContent);
rs.GetFieldValue(L"Flavour",Flavour);
rs.GetFieldValue(L"Brand",Brand);
rs.GetFieldValue(L"ProductionBatchNo",ProductionBatchNo);
WineTypeIndex.AddNew();
WineTypeIndex.m_WineName=WineName; //不知道为什么使用了select distinct 得到某几列记录时，自动把这一列分配给最前面的类型相同的列
WineTypeIndex.m_AlcoholContent=AlcoholContent;
WineTypeIndex.m_Flavour=Flavour;
WineTypeIndex.m_Brand=Brand;
WineTypeIndex.m_ProductionBatchNo=ProductionBatchNo;
WineTypeIndex.m_TypeIndex=count;
count++;
rs.MoveNext();
WineTypeIndex.Update();
}
}
rs.Close();
//更新酒精度索引
CAlcoholTypeIndex AlcoholTypeIndex;
if(!AlcoholTypeIndex.Open())
{
MessageBox(L"打开光谱索引库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!AlcoholTypeIndex.IsEOF())
{
AlcoholTypeIndex.MoveFirst();
while(!AlcoholTypeIndex.IsEOF())
{
AlcoholTypeIndex.Delete();
AlcoholTypeIndex.MoveNext();
}
}
command=_T("SELECT DISTINCT AlcoholContent FROM TrueWineLib");
if(!rs.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
MessageBox(L"打开真酒库失败",L"更新失败",MB_ICONERROR);
return;
}
CString alcohol;
if(!rs.IsEOF())
{
rs.MoveFirst();
int i=1;
while(!rs.IsEOF())
{
rs.GetFieldValue(_T("AlcoholContent"),alcohol);
AlcoholTypeIndex.AddNew();
AlcoholTypeIndex.m_AlcoholContent=alcohol;
AlcoholTypeIndex.m_AlcoholIndex=i;
i++;
AlcoholTypeIndex.Update();
rs.MoveNext();
}
}
AlcoholTypeIndex.Close();
rs.Close();
//更新香型索引
CFlavourTypeIndex FlavourTypeIndex;
if(!FlavourTypeIndex.Open())
{
MessageBox(L"打开光谱索引库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!FlavourTypeIndex.IsEOF())
{
FlavourTypeIndex.MoveFirst();
while(!FlavourTypeIndex.IsEOF())
{
FlavourTypeIndex.Delete();
FlavourTypeIndex.MoveNext();
}
}
command=_T("SELECT DISTINCT Flavour FROM TrueWineLib");
if(!rs.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
MessageBox(L"打开真酒库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!rs.IsEOF())
{
int count=1;
rs.MoveFirst();
while(!rs.IsEOF())
{
rs.GetFieldValue(L"Flavour",Flavour);
FlavourTypeIndex.AddNew();
FlavourTypeIndex.m_Flavour=Flavour;
FlavourTypeIndex.m_FlavourIndex=count;
count++;
rs.MoveNext();
FlavourTypeIndex.Update();
}
}
rs.Close();
//更新品牌索引
CBrandTypeIndex BrandTypeIndex;
if(!BrandTypeIndex.Open())
{
MessageBox(L"打开光谱索引库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!BrandTypeIndex.IsEOF())
{
BrandTypeIndex.MoveFirst();
while(!BrandTypeIndex.IsEOF())
{
BrandTypeIndex.Delete();
BrandTypeIndex.MoveNext();
}
}
command=_T("SELECT DISTINCT Brand FROM TrueWineLib");
if(!rs.Open(AFX_DB_USE_DEFAULT_TYPE,command))
{
MessageBox(L"打开真酒库失败",L"更新失败",MB_ICONERROR);
return;
}
if(!rs.IsEOF())
{
int count=1;
rs.MoveFirst();
while(!rs.IsEOF())
{
rs.GetFieldValue(L"Brand",Brand);
BrandTypeIndex.AddNew();
BrandTypeIndex.m_Brand=Brand;
BrandTypeIndex.m_BrandIndex=count;
count++;
rs.MoveNext();
BrandTypeIndex.Update();
}
}
rs.Close();
}
BOOL CFieldContentsDlg::OnInitDialog()
{
CDialog::OnInitDialog();
try
{
// prüfe Parameter
ASSERT (NULL != m_pDatabase && m_pDatabase -> IsOpen ());
ASSERT (!m_strField.IsEmpty ());
ASSERT (!m_strTable.IsEmpty ());
// Listbox füllen
CWaitCursor wc;
// zuerst SelectString zusammenbauen
CString strSelect;
AfxFormatString2 (strSelect, IDS_SELECT_FIELD, m_strField, m_strTable);
CRecordset rs (m_pDatabase);
VERIFY (rs.Open (CRecordset::forwardOnly, strSelect, CRecordset::readOnly));
CString strVal;
int iMax = 0;
while (!rs.IsEOF ())
{
rs.GetFieldValue (m_strField, strVal);
if (!strVal.IsEmpty ())
{
if (m_lbFields.AddString (strVal) < 0)
AfxThrowMemoryException ();
iMax = max (iMax, strVal.GetLength ());
}
rs.MoveNext ();
}
// HorizontalExtent setzen
if (iMax > 0)
{
WORD wUnits = LOWORD (:: GetDialogBaseUnits ());
m_lbFields.SetHorizontalExtent (wUnits * iMax);
}
// TypInfo ausgeben
CODBCFieldInfo FieldInfo;
rs.GetODBCFieldInfo (m_strField, FieldInfo);
switch (FieldInfo.m_nSQLType)
{
case SQL_DATE: // Zeitformate
case SQL_TIME:
case SQL_TIMESTAMP:
{
m_uiResID = IDS_DATETIME_FORMAT;
VERIFY (m_strFieldType.LoadString (IDS_DATE_TIME));
}
break;
case SQL_DECIMAL:
case SQL_NUMERIC:
case SQL_BIGINT:
case SQL_CHAR:
case SQL_VARCHAR:
{
m_uiResID = IDS_STRING_FORMAT;
VERIFY (m_strFieldType.LoadString (IDS_TEXT));
}
break;
default: // alle Zahlenformate
VERIFY (m_strFieldType.LoadString (IDS_ZAHL));
break;
}
// Caption setzen
CString strFormat;
GetWindowText (strFormat);
strVal.Format (strFormat, m_strTable);
SetWindowText (strVal);
// Feldname setzen
m_strFieldName = FieldInfo.m_strName;
// Controls setzen
m_lbFields.EnableWindow (m_lbFields.GetCount () > 0);
m_btOk.EnableWindow (FALSE);
rs.Close ();
UpdateData (FALSE);
}
catch (CException *e)
{
e -> ReportError ();
e -> Delete ();
}
return TRUE; // return TRUE unless you set the focus to a control
// EXCEPTION: OCX Property Pages should return FALSE
}