// DBSetData.cpp: implementation of the CDBSetData class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#pragma warning( disable:4244) 
#include "dbconvert.h"
#include "DBSetData.h"
#include "CSPRDalian.h"
#include "AddMaterial.h"
#include "math.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CDBSetData::CDBSetData()
{
	m_pRsDispRange = NULL;

	strPhsManu[iPA]="PA";
	strPhsManu[iPAFIX]="PA";
	strPhsManu[iPART]="PART";
	strPhsManu[iSA]="SA";
	strPhsManu[iSPRING]="SPRING";
	strPhsManu[iCSPRING]="ConstantSPRING";
	strPhsManu[iBOLTSNUTS]="BoltsNuts";
	
	strFDNTableData[iPA]="crudePA";
	strFDNTableData[iPAFIX]="crudePAfix";
	strFDNTableData[iPART]="crudePART";
	strFDNTableData[iSA]="crudeSA";
	strFDNTableData[iSPRING]="Spring_Crude";
	strFDNTableData[iCSPRING]="Spring_Crude";
	strFDNTableData[iBOLTSNUTS]="CrudeBoltsNuts";
	
	strFDNID[iPA]="tbnPAid";
	strFDNID[iPAFIX]="tbnPAid";
	strFDNID[iPART]="tbnPARTid";
	strFDNID[iSA]="tbnSAid";
	strFDNID[iSPRING]="Spring_id";
	strFDNID[iCSPRING]="Spring_id";
	strFDNID[iBOLTSNUTS]="CrudeBoltsNutsID";
	
	strTableData2[iPA]="PipeClampSelData";
	strTableData2[iPAFIX]="PipeFixSelData";
	strTableData2[iPART]="ConnectLoadSize";
	strTableData2[iSA]="SASelData";
	strTableData2[iSPRING]="SpringSelData";
	strTableData2[iCSPRING]="ConstSelData";
	strTableData2[iBOLTSNUTS]="AttachmentSelData";
	
	strTableData3[iPA]="PipeSizeInfo";
	strTableData3[iPAFIX]="PipeSizeInfo";
	strTableData3[iPART]="ConnectSizeInfo";
	strTableData3[iSA]="SASizeInfo";
	strTableData3[iSPRING]="SpringSizeInfo";
	strTableData3[iCSPRING]="ConstSizeInfo";
	strTableData3[iBOLTSNUTS]="";
	
	strFDNID2[iPA]="ComponentID";
	strFDNID2[iPAFIX]="ComponentID";
	strFDNID2[iPART]="ComponentID";
	strFDNID2[iSA]="ComponentID";
	strFDNID2[iSPRING]="ComponentID";
	strFDNID2[iCSPRING]="ComponentID";
	strFDNID2[iBOLTSNUTS]="ComponentID";

	//以下是表格中数据库文件名称
	sourceNM[0] = "西北电力";
	sourceNM[1] = "华东电力";
	sourceNM[2] = "常州电力";
	sourceNM[3] = "大连弹簧";
	sourceNM[4] = "江阴石化";
	sourceNM[5] = "华东石油";
	sourceNM[6] = "华东石油";
	sourceNM[7] = "中石化北京";
	sourceNM[8] = "江苏苏源";
	sourceNM[9] = "D-ZD2010";

	sourceDB[0] = "ZdjcrudeXBDLY.mdb";
	sourceDB[1] = "ZdjcrudeHDDLY.mdb";
	sourceDB[2] = "ZdjcrudeCZDL.mdb";
	sourceDB[3] = "ZdjcrudeDLTH.mdb";
	sourceDB[4] = "ZdjcrudeJYSH.mdb";
	sourceDB[5] = "ZdjcrudeYZDF.mdb";
	sourceDB[6] = "ZdjcrudeYZGJ.mdb";
	sourceDB[7] = "ZdjcrudeZSHBJ.mdb";
	sourceDB[8] = "ZdjcrudeJSSY.mdb";
	sourceDB[9] = "ZdjcrudeD-ZD2010.mdb";
	
	source2DB[0] = "HStandardXBDLY.mdb";
	source2DB[1] = "HStandardHDDLY.mdb";
	source2DB[2] = "HStandardCZDL.mdb";
	source2DB[3] = "HStandardDLTH.mdb";
	source2DB[4] = "HStandardJYSH.mdb";
	source2DB[5] = "HStandardYZDF.mdb";
	source2DB[6] = "HStandardYZGJ.mdb";
	source2DB[7] = "HStandardZSHBJ.mdb";
	source2DB[8] = "HStandardJSSY.mdb";
	source2DB[9] = "HStandardDLGB-2010.mdb";

}

CDBSetData::~CDBSetData()
{

}

bool FileExists(CString filename)
{
	CFileStatus fileStatus;
	if(CFile::GetStatus(filename,fileStatus))
		return TRUE;
	return FALSE;
}

CDBSetData& CDBSetData::Instance()
{
	static CDBSetData s_Inst;
	return s_Inst;
}

void CDBSetData::SetStandard( const CString& strStandard )
{
	ASSERT( !strStandard.IsEmpty() );
	m_strStandard = strStandard;

	m_strHDCrude.Format("HDCrude%s", strStandard );//恒吊数据表
	m_strHDWeight.Format("HD_WEight_%s", strStandard );//恒吊重量表
	m_strHDDisplacementSerial.Format("DisplacementSerial%s", strStandard );//恒吊位移系列表
	m_strHDCustomid.Format("customid" );//恒吊customid-id表
	m_strHDDisplacementRange.Format("DisplacementRange" );//恒吊位移范围表
	m_strHDDIM_Primary.Format("HDDIM_Primary_%s", strStandard );
	m_strHDDIM_PrimaryMode.Format("HDDIM_PrimaryMode_%s", strStandard );
	m_strHDDIM_Primary1.Format("HDDIM_Primary_%s1", strStandard );
	m_strHDLugDiaofCSPR.Format("RODDiaOfCSPR%s", strStandard );
	m_strHDLugDiaofCSPR1.Format("RODDiaOfCSPR%s1", strStandard );
	m_strHDsourceRODDiaOfCSPR.Format("SourceRODDiaOfCSPR%s", strStandard );
	m_strHDCSPR_FieldNameSizeVar.Format("CSPR_%s_FieldNameSizeVar", strStandard );
	m_strHDcrudeid.Format("HDcrude%sid", strStandard );

	m_strHDCustomid = _T("customid");
}

const CString& CDBSetData::GetStandard() const
{
	return m_strStandard;
}

void CDBSetData::SetDbDir( const CString strDbDir )
{
	ASSERT( !strDbDir.IsEmpty() );
	if ( m_strDBDir == strDbDir )
		return;

	m_strDBDir = strDbDir;

}

void CDBSetData::SetDBPath( const CString& strDBFile )
{
	ASSERT( !strDBFile.IsEmpty() );
	if ( m_strDBPathName == strDBFile )
		return;

	m_strDBPathName = strDBFile;
	ConnectionDatamdb();
}

bool CDBSetData::ConnectionDatamdb()
{
	if ( m_strDBPathName.IsEmpty() )
	{
		AfxMessageBox("请设置数据库路径！");
		return false;
	}
	m_pCon.CreateInstance(__uuidof(Connection));
	CString strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + m_strDBPathName;

	try
	{
	   m_pCon->Open(_bstr_t(strCon),"","",-1);

	}
	catch(_com_error e)
	{
		AfxMessageBox(e.ErrorMessage());
		return FALSE;
	}
	return TRUE;
}

//生成恒力弹簧数据库
void CDBSetData::CreateHDCrude() 
{
	_RecordsetPtr pRsA=NULL;//表:HDDIM_Primary_sy
	_RecordsetPtr pRsA1=NULL;//表:HDDIM_Primary_sy的备份表
	_RecordsetPtr pRsB=NULL;//表:HDcrudeSY
	_RecordsetPtr pRsDY=NULL;//CSPR_SY_FieldNameSizeVar
	CString strSQL=_T("");
	int iRsCount=0;
	CString ID=_T("");
	int IsFirst=0;//是否是记录集中的第一条记录
	pRsA1.CreateInstance(__uuidof(Recordset));
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));
	pRsDY.CreateInstance(__uuidof(Recordset));
	long minDH=0;
	long maxDH=0;
	float fBmin=0.0;
	float fBmax=0.0;
	long DH=0;
	CString BH=_T("");
	CString INSTALLtype=_T("");
	CString CustomID=_T("");
	_variant_t key;
	try
	{
		strSQL.Format("drop table %s",m_strHDDIM_Primary1);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
	}
	try
	{
		strSQL.Format("select * into %s from %s",m_strHDDIM_Primary1,m_strHDDIM_Primary);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
		strSQL.Format("生成表 %s 失败!",m_strHDDIM_Primary1);
		AfxMessageBox(strSQL);
		return;
	}
	try
	{
		strSQL.Format("delete * from %s",m_strHDCrude);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL.Format("select * from %s order by CustomID,minDH,maxDH,fBmin,fBmax",m_strHDDIM_Primary);
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL.Format("select * from %s order by CustomID,minDH,maxDH,fBmin,fBmax",m_strHDDIM_Primary1);
		pRsA1->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL.Format("SELECT * from %s",m_strHDCrude);
		pRsB->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL.Format("SELECT * from %s",m_strHDCSPR_FieldNameSizeVar);
		pRsDY->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		iRsCount=pRsA->GetRecordCount();
		if(iRsCount>0)
		{//表:HDDIM_Primary_SY不为空(start)
			for(pRsA->MoveFirst(),pRsA1->MoveFirst();!pRsA->adoEOF;pRsA->MoveNext())
			{//对表(HDDIM_Primary_SY)中的每一条记录进行处理(start)
				if(IsFirst>1)
				{
					pRsA1->MoveNext();
				}
				IsFirst++;
				minDH=vtoi(pRsA->GetCollect(_T("minDH")));
				maxDH=vtoi(pRsA->GetCollect(_T("maxDH")));
				if(maxDH<0||minDH<0||maxDH<minDH)
				{
					AfxMessageBox("输入有误!");
				}
				DH=0;
				CustomID=vtos(pRsA->GetCollect("CustomID"));
				//根据CustomID找与之对应的ID
				ID=HDcrudeSYid(CustomID);

				INSTALLtype=CustomID.Right(1);
				for(DH=minDH;DH<=maxDH;DH++)
				{//对每一条记录进行分解(start)
					pRsB->AddNew();
					BH.Format("%d",DH);
					BH=CustomID.Left(CustomID.GetLength()-1)+"-"+BH+INSTALLtype;
					pRsB->PutCollect(_T("BH"),_variant_t(BH));
					pRsB->PutCollect(_T("DH"),_variant_t(DH));
					pRsB->PutCollect(_T("ID"),_variant_t(ID));
					pRsB->PutCollect(_T("INSTALLtype"),_variant_t(INSTALLtype));
					//在表(CSPR_SY_FieldNameSizeVar)选择当前弹簧需要导入的字段集
					CDBSetData::FilterPRsDY(pRsDY,CustomID);
					if(pRsDY->GetRecordCount()>0)
					{
						for(pRsDY->MoveFirst();!pRsDY->adoEOF;pRsDY->MoveNext())
						{
							key=pRsA->GetCollect(pRsDY->GetCollect("SourceFDName"));
							if(key.vt==VT_NULL||key.vt==VT_EMPTY)
							{
								pRsB->PutCollect(pRsDY->GetCollect("FDName"),pRsA1->GetCollect(pRsDY->GetCollect("sourceFDName")));
							}
							else
							{
								pRsB->PutCollect(pRsDY->GetCollect("FDName"),pRsA->GetCollect(pRsDY->GetCollect("sourceFDName")));
							}
						}
					}
					//确定字段:fBmin和fBmax
					key=pRsA->GetCollect("fBmin");
					if(key.vt==VT_NULL||key.vt==VT_EMPTY)
					{
						fBmin=fBminFBmax(1,DH);
						pRsB->PutCollect(_T("fBmin"),_variant_t(fBmin));
						pRsB->PutCollect(_T("fBmax"),pRsA->GetCollect(_T("fBmax")));
					}
					else
					{
						key=pRsA->GetCollect("fBmax");
						if(key.vt==VT_NULL||key.vt==VT_EMPTY)
						{
							fBmax=fBminFBmax(0,DH);
							pRsB->PutCollect(_T("fBmin"),pRsA->GetCollect(_T("fBmin")));
							pRsB->PutCollect(_T("fBmax"),_variant_t(fBmax));
						}
					}
					pRsB->Update();
				}//对每一条记录进行分解(end)
			}//对表(HDDIM_Primary_SY)中的每一条记录进行处理(end)
		}//表:HDDIM_Primary_SY不为空(end)
		if(pRsA1->State==adStateOpen)
		{
			pRsA1->Close();
		}
		pRsB->Close();
		if ( GetStandard() == _T("SY") || GetStandard() == _T("2010") )
		{
			UpdateCustomIDandBH();
		}
//		WriteWeight();
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
}
//在表(CSPR_SY_FieldNameSizeVar)选择当前弹簧需要导入的字段集
int CDBSetData::FilterPRsDY(_RecordsetPtr &pRs, CString str)
{
	int iPRsCount=0;
	CString strSQL=_T("");
	for(int i=0;i<=11;i++)
	{
		pRs->Filter="";
		if(i>0)
		{
			strSQL.Format("%d",i);
		}
		strSQL="CustomID"+strSQL;
		strSQL+="= '"+str+"'";
		pRs->Filter=_variant_t(strSQL);
		iPRsCount=pRs->GetRecordCount();
		if(iPRsCount>0)
		{
			break;
		}
	}
	return iPRsCount;
}
//根据CustomID找与之对应的ID
CString CDBSetData::HDcrudeSYid(CString CustomID)
{
	CString ID=_T("");
	CString strSQL=_T("");
	static _RecordsetPtr s_pRs=NULL;
	try
	{
		if ( s_pRs == NULL )
		{
			if ( !OpenTable( s_pRs, m_strHDcrudeid ) )
			{
				return  "";
			}
		}
		for ( int i = 0; i < 2; i++ )
		{
			strSQL="CustomID='"+CustomID+"'";
			s_pRs->PutFilter( (long)adFilterNone );
			s_pRs->Filter=_variant_t(strSQL);
			if(s_pRs->GetRecordCount()>0)
			{
				ID = vtos(s_pRs->GetCollect("ID"));
			}
			if ( !ID.IsEmpty() )
			{
				return ID;
			}
			CustomID = ConversionCustomID( CustomID, 0 );
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	return ID;
}
//确定字段:fBmin或者fBmax的值
//当flag=0;val等于已知的fBmin 
//当flag=1;val等于已知的fBmax 
float CDBSetData::fBminFBmax(int flag,long DH)
{
	float value=0.0;
	CString strDH=_T("");
	strDH.Format("%d",DH);
	
	try
	{
		if ( m_pRsDispRange == NULL )
		{
			m_pRsDispRange.CreateInstance( __uuidof(Recordset) );
			m_pRsDispRange->Open( _bstr_t("SELECT * FROM [DisplacementRange]"),
				(IDispatch*)m_pCon, adOpenStatic, adLockOptimistic, adCmdText );
		}
		CString strSQL=_T("");
	//	strSQL="select * from DisplacementRange where ";
		strSQL+="minDH<="+strDH+" and maxDH>="+strDH+"";
		m_pRsDispRange->PutFilter( (long)adFilterNone );
		m_pRsDispRange->PutFilter( _variant_t( strSQL ) );
		int iCount = m_pRsDispRange->GetRecordCount();
		if(iCount>0)
		{
			if(flag)
			{//已知的fBmax
				value=vtof(m_pRsDispRange->GetCollect(_T("fBmin")));
			}
			else
			{
				value=vtof(m_pRsDispRange->GetCollect(_T("fBmax")));
			}
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	return value;
}

//生成表:LugDiaofCSPR
void CDBSetData::CreateRODDiaofCSPR() 
{
	CString strSQL=_T("");
	_RecordsetPtr pRsA=NULL;
	_RecordsetPtr pRsA1=NULL;
	int start=0;//开始位置
	int end=0;//终点位置
	_RecordsetPtr pRsB=NULL;
	_variant_t key;
	_variant_t key1;
	long minDH=0;
	long maxDH=0;
	long Pmax=0;
	long FJmax=0.0;
	long FJmin=0.0;
	CString CustomID1=_T("");
	CString CustomID=_T("");
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));
	pRsA1.CreateInstance(__uuidof(Recordset));
// 	try
// 	{
// 		if ( !ConnectionDatamdb() )
// 			return;
// 	}
// 	catch(_com_error e)
// 	{
// 		AfxMessageBox(e.Description());
// 	}
	try
	{
		strSQL.Format("delete * from %s",m_strHDLugDiaofCSPR);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return;
	}

	try
	{
		strSQL.Format("select * from %s",m_strHDLugDiaofCSPR);
		pRsB->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		strSQL.Format("select * from %s",m_strHDsourceRODDiaOfCSPR);
		pRsA->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		for(pRsA->MoveFirst();!pRsA->adoEOF;pRsA->MoveNext())
		{//对每一条记录进行处理(start)
			start=0;
			end=0;
			CustomID1=vtos(pRsA->GetCollect("CustomID1"));
			CustomID1+=" ";
			minDH=vtoi(pRsA->GetCollect("minDH"));
			maxDH=vtoi(pRsA->GetCollect("maxDH"));
			while(1)
			{//对CustomID1中每项进行分解为多条记录(start)
				end=CustomID1.Find(_T(" "),start);
				if(end!=-1)
				{
					CustomID=CustomID1.Mid(start,end-start);
					start=end+1;
					CustomID.TrimLeft();
					CustomID.TrimRight();
					if(CustomID!="")
					{
						pRsB->AddNew();
						pRsB->PutCollect(_T("Pmax"),pRsA->GetCollect(_T("Pmax")));
						pRsB->PutCollect(_T("PmaxKgf"),_variant_t( vtof(pRsA->GetCollect(_T("Pmax")))/9.8) );
						pRsB->PutCollect(_T("FiJ"),pRsA->GetCollect(_T("FiJ")));
						pRsB->PutCollect(_T("CustomID"),_variant_t(CustomID));
						pRsB->PutCollect(_T("minDH"),pRsA->GetCollect(_T("minDH")));
						pRsB->PutCollect(_T("maxDH"),pRsA->GetCollect(_T("maxDH")));
						if(CustomID=="58VB"||CustomID=="58VC"||CustomID=="58HB"||CustomID=="58HC")
						{//2
							pRsB->PutCollect(_T("FiK"),pRsA->GetCollect(_T("FiK")));
							pRsB->PutCollect(_T("R"),pRsA->GetCollect(_T("R")));
							pRsB->PutCollect(_T("I"),pRsA->GetCollect(_T("I")));
							if(maxDH<=83)
							{
								pRsB->PutCollect(_T("T"),pRsA->GetCollect(_T("T")));
							}
							if(CustomID=="58VC"||CustomID=="58HC")
							{
								pRsB->PutCollect(_T("S"),pRsA->GetCollect(_T("S")));
							}
							if(CustomID=="58HB"||CustomID=="58HC"||((CustomID=="58VB"||CustomID=="58VC")&&minDH>=84) )
							{
								pRsB->PutCollect(_T("W"),pRsA->GetCollect(_T("W")));
							}
						}//2
						pRsB->Update();
					}
				}
				else
				{
					break;
				}
			}//对CustomID1中每项进行分解为多条记录(end)
		}//对每一条记录进行处理(end)
		if(pRsA->State==adStateOpen)
		{
			pRsA->Close();
		}
		if(pRsB->State==adStateOpen)
		{
			pRsB->Close();
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return;
	}
	try
	{
		strSQL.Format("drop table %s", m_strHDLugDiaofCSPR1);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
	}
	try
	{//对同一弹簧的值进行集中(start)
		strSQL.Format( "select * into %s from %s where (CustomID='58VB' OR CustomID='58VC') and T IS NULL",
					m_strHDLugDiaofCSPR1, m_strHDLugDiaofCSPR );
		strSQL+=" and maxDH<=83";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL.Format("delete *  from %s where (CustomID='58VB' OR CustomID='58VC') and T IS NULL",
					m_strHDLugDiaofCSPR );
		strSQL+=" and maxDH<=83";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL.Format("select * from %s where (CustomID='58VB' or CustomID='58VC') and maxDH<=83",
					m_strHDLugDiaofCSPR);
		pRsB->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		strSQL.Format("select * from %s", m_strHDLugDiaofCSPR1 );
		pRsA->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		for(pRsB->MoveFirst();!pRsB->adoEOF;pRsB->MoveNext())
		{
			CustomID=vtos(pRsB->GetCollect(_T("CustomID")));
			minDH=vtoi(pRsB->GetCollect(_T("minDH")));
			maxDH=vtoi(pRsB->GetCollect(_T("maxDH")));
			Pmax=vtoi(pRsB->GetCollect(_T("Pmax")));
			strSQL.Format("CustomID=%s and minDH<=%d and maxDH>=%d and Pmax=%d",CustomID,minDH,maxDH,Pmax);
			pRsA->Filter="";
			pRsA->Filter=_variant_t(strSQL);
			int iCount=pRsA->GetRecordCount();
			if(iCount>0)
			{
				pRsA->MoveFirst();
			}
			for(;!pRsA->adoEOF;pRsA->MoveNext())
			{
				key=pRsA->GetCollect(_T("FiJ"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("FiJ"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("FiJ"),pRsA->GetCollect(_T("FiJ")));
					}
				}
				key=pRsA->GetCollect(_T("FiK"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("Fik"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("FiK"),key);
					}
				}
				key=pRsA->GetCollect(_T("R"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("R"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("R"),key);
					}
				}
				key=pRsA->GetCollect(_T("i"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("i"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("i"),key);
					}
				}
				key=pRsA->GetCollect(_T("S"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("S"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("S"),key);
					}
				}
				key=pRsA->GetCollect(_T("W"));
				if(!(key.vt==VT_NULL||key.vt==VT_EMPTY))
				{
					key1=pRsB->GetCollect(_T("W"));
					if(key1.vt==VT_NULL||key1.vt==VT_EMPTY)
					{
						pRsB->PutCollect(_T("W"),key);
					}
				}
			}
		}
	}//对同一弹簧的值进行集中(end)
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	try
	{//对58HD,58HE,58VEs,58VE的记录重新进行分解同时给Fik赋值(start)
		if(pRsA->State==adStateOpen)
		{
			pRsA->Close();
		}
		if(pRsB->State==adStateOpen)
		{
			pRsB->Close();
		}
		strSQL.Format("drop table %s", m_strHDLugDiaofCSPR1);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL.Format("select * into %s from %s where (CustomID='58VE'",
					m_strHDLugDiaofCSPR1, m_strHDLugDiaofCSPR );
		strSQL+=" or CustomID='58VES' or CustomID='58HE' or CustomID='58HD') and maxDH<=83";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);

		strSQL.Format("delete * from %s where (CustomID='58VE' or CustomID='58VES' or ",
					m_strHDLugDiaofCSPR );
		strSQL+="CustomID='58HE' or CustomID='58HD') and maxDH<=83";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);

		strSQL.Format("select * from %s order by CustomID,Pmax", m_strHDLugDiaofCSPR1 );
		pRsA->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		
		strSQL.Format("select distinct CustomID,minDH,maxDH,K,FJmax,FJmin from %s ", m_strHDDIM_Primary );
		strSQL+=" where ( CustomID='58VE' or CustomID='58VES' or CustomID='58HE' or CustomID='58HD') and ";
		strSQL+=" maxDH<=83 and GL IS NOT NULL order by CustomID,minDH";

		pRsA1->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		strSQL.Format("select * from %s order by CustomID", m_strHDLugDiaofCSPR );
		
		pRsB->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		for(pRsA1->MoveFirst();!pRsA1->adoEOF;pRsA1->MoveNext())
		{
			CustomID=vtos(pRsA1->GetCollect(_T("CustomID")));
			FJmax=vtoi(pRsA1->GetCollect("FJmax"));
			FJmin=vtoi(pRsA1->GetCollect("FJmin"));
			strSQL.Format("CustomID='%s' and FiJ<=%d and FiJ>=%d",CustomID,FJmax,FJmin);
			pRsA->Filter="";
			pRsA->Filter=_variant_t(strSQL);
			int ii=pRsA->GetRecordCount();
			for(pRsA->MoveFirst();!pRsA->adoEOF;pRsA->MoveNext())
			{
				pRsB->AddNew();
				pRsB->PutCollect(_T("CustomID"),pRsA1->GetCollect("CustomID"));
				pRsB->PutCollect(_T("minDH"),pRsA1->GetCollect("minDH"));
				pRsB->PutCollect(_T("maxDH"),pRsA1->GetCollect("maxDH"));
				pRsB->PutCollect(_T("Fik"),pRsA1->GetCollect("K"));

				pRsB->PutCollect(_T("FiJ"),pRsA->GetCollect("FiJ"));
				pRsB->PutCollect(_T("R"),pRsA->GetCollect("R"));
				pRsB->PutCollect(_T("I"),pRsA->GetCollect("I"));
				pRsB->PutCollect(_T("T"),pRsA->GetCollect("T"));
				pRsB->PutCollect(_T("S"),pRsA->GetCollect("S"));
				pRsB->PutCollect(_T("W"),pRsA->GetCollect("W"));
				pRsB->PutCollect(_T("PmaxKgf"),pRsA->GetCollect("PmaxKgf"));
				pRsB->PutCollect(_T("Pmax"),pRsA->GetCollect("Pmax"));
				pRsB->Update();
			}
		}
		if(pRsA->State==adStateOpen)
		{
			pRsA->Close();
		}

		if(pRsA1->State==adStateOpen)
		{
			pRsA1->Close();
		}
		if(pRsB->State==adStateOpen)
		{
			pRsB->Close();
		}
	}//对58HD,58HE,58VEs,58VE的记录重新进行分解同时给Fik赋值(end)
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	AfxMessageBox("成功生成表  "+ m_strHDLugDiaofCSPR );
}

//生成表HDDI_PrimaryModel_sy
void CDBSetData::CreatePrimaryMode() 
{
	CString strFilter=_T("");
	CString strSQL=_T("");
	CString CustomID=_T("");
	_RecordsetPtr pRD=NULL;
	long minDH=0;
	long maxDH=0;
	float fBmin=0.0;
	float fBmax=0.0;
	_variant_t key;
	_variant_t keyCz;

	try
	{
// 		if ( !ConnectionDatamdb() )
// 		{
// 			return;
// 		}
		m_pRes.CreateInstance(__uuidof(Recordset));
		m_pRsy.CreateInstance(__uuidof(Recordset));
		m_pRD.CreateInstance(__uuidof(Recordset));
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	try
	{
		strSQL=_T("drop table HDDIM_PrimaryMode_sy");
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
	}
	try
	{
		strSQL.Format("select * from %s",m_strHDDisplacementRange);
		m_pRD->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);

		strSQL.Format("select * from %s order by CustomID,minDH,maxDH,fBmin,fBmax",m_strHDDIM_Primary);
		m_pRsy->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
				
		strSQL.Format("select * into %s from %s",m_strHDDIM_PrimaryMode,m_strHDDIM_Primary);
		m_pRes->Open(_bstr_t(strSQL),(IDispatch*)m_pCon,adOpenStatic,adLockOptimistic,adCmdText);
		m_pRes->MoveFirst();
		for(;!m_pRes->adoEOF;m_pRes->MoveNext())
		{//对表HDDIM_Primary_sy中的每一条记录进行操作(start)
			CustomID=vtos(m_pRes->GetCollect(_T("CustomID")));
			minDH=vtoi(m_pRes->GetCollect(_T("minDH")));
			maxDH=vtoi(m_pRes->GetCollect(_T("maxDH")));
			fBmin=vtof(m_pRes->GetCollect(_T("fBmin")));
			fBmax=vtof(m_pRes->GetCollect(_T("fBmax")));
			key=m_pRes->GetCollect(_T("fBmax"));
			strFilter.Format("minDH<=%d and maxDH>=%d",minDH,maxDH);
			m_pRD->Filter=_variant_t(strFilter);
			if(key.vt==VT_NULL||key.vt==VT_EMPTY)
			{
				if(!m_pRD->adoEOF)
				{
					m_pRes->PutCollect(_T("fBmax"),m_pRD->GetCollect(_T("fBmax")));
				}
			}
			key=m_pRes->GetCollect(_T("fBmin"));
			if(key.vt==VT_NULL || key.vt==VT_EMPTY)
			{
				if(!m_pRD->adoEOF)
				{
					m_pRes->PutCollect(_T("fBmin"),m_pRD->GetCollect(_T("fBmin")));
				}
			}
			m_pRsy->Filter="";
			strFilter.Format("CustomID='%s' and minDH=%d and maxDH=%d ",CustomID,minDH,maxDH);
			m_pRsy->Filter=_variant_t(strFilter);
			AddData_sy("A");
			AddData_sy("C");
			AddData_sy("D");
			AddData_sy("F");
			AddData_sy("G");
			AddData_sy("H");
			AddData_sy("K");
			AddData_sy("L");
			AddData_sy("M");
			AddData_sy("N");
			AddData_sy("P");
			AddData_sy("PP");
			AddData_sy("S");
			AddData_sy("T");
			AddData_sy("U");
			AddData_sy("W");
			AddData_sy("X");
			AddData_sy("Y");
			AddData_sy("YY");
			AddData_sy("AY");
			AddData_sy("BY");
			AddData_sy("E");
			AddData_sy("sizeH");
			AddData_sy("sizeC");
			AddData_sy("FJmin");
			AddData_sy("FJmax");
			AddData_sy("GL");
		}//对表HDDIM_Primary_sy中的每一条记录进行操作(end)
		if(m_pRes->State==adOpenStatic)
		{
			m_pRes->Close();
		}
		if(m_pRsy->State==adOpenStatic)
		{
			m_pRsy->Close();
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	AfxMessageBox("生成表HDDIM_PrimaryMode_sy成功!");
}

void CDBSetData::AddData_sy(CString strKey)
{
	_variant_t key;
	_variant_t keysy;
	key=m_pRes->GetCollect(_variant_t(strKey));
	if(key.vt==VT_EMPTY||key.vt==VT_NULL)
	{
		m_pRsy->MoveFirst();
		keysy=m_pRsy->GetCollect(_variant_t(strKey));
		if(keysy.vt==VT_NULL||keysy.vt==VT_EMPTY)
		{
			m_pRsy->MoveNext();
		}
		m_pRes->PutCollect(_variant_t(strKey),m_pRsy->GetCollect(_variant_t(strKey)));
		m_pRes->Update();
	}
}
//不处理较复杂，修改始数据
void CDBSetData::WritefBmax()
{
	CString strFilter=_T("");
	_variant_t key;
	long minDH=0;
	long maxDH=0;
	minDH=vtoi(m_pRsy->GetCollect(_T("minDH")));
	maxDH=vtoi(m_pRsy->GetCollect(_T("maxDH")));
	strFilter.Format("minDH<=%d and maxDH>=%d",minDH,maxDH);
	for(int i=0;i<4;i++)
	{
		if(i==1)
		{

		}
		m_pRsy->Filter="";
		m_pRsy->Filter=_variant_t(strFilter);
		if(!m_pRsy->adoEOF)
		{
			key=m_pRes->GetCollect(_T("fBmax"));
			if(key.vt==VT_NULL || key.vt==VT_EMPTY)
			{
				m_pRes->PutCollect(_T("fBmax"),m_pRD->GetCollect(_T("fBmax")));
			}
			key=m_pRes->GetCollect(_T("fBmin"));
			if(key.vt==VT_NULL || key.vt==VT_EMPTY)
			{
				m_pRes->PutCollect(_T("fBmin"),m_pRD->GetCollect(_T("fBmin")));
			}
		}
	}
}

BOOL CDBSetData::OpenTable( _RecordsetPtr& pReRecord, const CString &strTbl, const CString &strOrder) const
{
	ASSERT( !strTbl.IsEmpty() );

	try
	{
		if ( pReRecord == NULL )
		{
			pReRecord.CreateInstance( __uuidof(Recordset) );
		}
		if ( pReRecord->GetState() == adOpenStatic )
		{
			pReRecord->Close();
		}
		CString strSQL;
		strSQL.Format( "select * from %s", strTbl );
		if ( !strOrder.IsEmpty() )
		{
			strSQL += _T(" ORDER BY ") + strOrder;
		}
		pReRecord->Open( _bstr_t(strSQL), (IDispatch *)m_pCon,
					adOpenStatic, adLockOptimistic, adCmdText );
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
		return FALSE;
	}
	return TRUE;
}

//基本思路：
//每次从HD_Crude_SY表中取一个记录，获得CustomID,DH,fBmin,fBmax
//从customid表根据CustomID值查customid1字段，获得对应的customid字段值strCustomID，
//根据这个值strCustomID最右边2位字符，和fBmin的值
//若是立式，从HD_Weight_SY重量表查fBmin_V字段值，获得其值为fBmin_V。
//若fBmin<=fBmin_Weight,则查字段为a列；
//否则应查字段为b列
//获得对应的重量
//更新HD_Crude_SY表的weight字段
void CDBSetData::WriteWeight()
{
	_RecordsetPtr pRs1=NULL;
	_RecordsetPtr pRs3=NULL;//位移值系列表
	_RecordsetPtr pRsWeight=NULL;
	_RecordsetPtr pRsOper =NULL;
	CString strSQL=_T("");
	long DH=0;
	float x=0.0;
	float y=0.0;
	float fBmin=0.0;
	float fBmax=0.0;
	float fBmin_H=0.0;
	float fBmin_V=0.0;
	float fBWeightMin = 0.0;
	float Weight=0.0;
	float sizeH=0.0;
	float pp=0.0;
	float T=0.0;
	float D=0.0;
	CString CustomID=_T("");
	CString strFields=_T("");
	CString strType;
	try
	{
		CString strTmpTbl = _T("tmpTable");
		if ( CopyTableData( strTmpTbl, m_strHDCrude ) ) // 创建一个临时表，
		{
			DeleteTableRecord( strTmpTbl );			// strHDCrude的结构
		}
		else
		{
			//return;
		}

		if ( !OpenTable( pRs1, m_strHDCrude, _T("CustomID, dh, fBmin, fBmax") ) 
		  || !OpenTable( pRsOper, strTmpTbl )
		  || !OpenTable( pRsWeight, m_strHDWeight ) 
		  || !OpenTable( pRs3, m_strHDDisplacementSerial, _T("DisplacementSerial") ) )
		{
			AfxMessageBox( "初始化时，打开数据库失败！" );
			return;
		}

		for( pRs1->MoveFirst(); !pRs1->adoEOF; pRs1->MoveNext() )
		{
			DH=vtoi(pRs1->GetCollect("dh"));	
			CustomID=vtos(pRs1->GetCollect("CustomID"));
			CustomID = ConversionCustomID( CustomID ); // SY的标准要转换

			if ( CustomID.IsEmpty() )
				continue;

			strType = CustomID.Mid( 2, 1 ); // H 或 V 
			if ( strType != _T("H") && strType != _T("V") )
			{
				AfxMessageBox( _T("不认识的CustomID  ") + CustomID );
				ASSERT(NULL);
				continue;
			}
			
			fBmin = vtof(pRs1->GetCollect("fBmin"));
			fBmax = vtof(pRs1->GetCollect("fBmax"));
			for( BOOL bIsNewRecord = TRUE; bIsNewRecord; ) // 是否对记录进行分解(start)
			{
				if(CustomID=="80VF")
				{
					strFields="80v";
				}
				else if(CustomID=="58VES")
				{
					strFields="ve";
				}
				else
				{
					strFields=CustomID.Right(2);//这个位置字符="V?"或"H?"
				}

				pRsOper->AddNew();
				bIsNewRecord = FALSE;
				SetRecordValue( pRsOper, pRs1 ); // 将pRs1的当前记录的所有字段值赋给pRsOper
				pRsOper->PutCollect( _T("fBmin"), fBmin );
				pRsOper->PutCollect( _T("fBmax"), fBmax );

				strSQL.Format("minDH<=%d and maxDH>=%d",DH,DH);
				pRsWeight->Filter="";
				pRsWeight->Filter=_variant_t(strSQL);
				if( pRsWeight->GetRecordCount() > 0 )
				{
					fBmin_H=vtof(pRsWeight->GetCollect("fBmin_H"));
					fBmin_V=vtof(pRsWeight->GetCollect("fBmin_V"));
					fBWeightMin = vtof( pRsWeight->GetCollect( _variant_t( _T("fBmin_") + strType ) ) );
// 					if( CustomID == "80VF") // 这种情况80V不分档
// 					{
// 						strFields="80v";
// 					}
 //					else 
					if ( ( fBmin - fBWeightMin ) <= 1e-6 )
					{
						if ( ( fBmax - fBWeightMin ) > 1e-6 )
						{
							bIsNewRecord = TRUE; // 需要分解记录
							pRsOper->PutCollect( _variant_t("fBmax"), fBWeightMin );
							fBmin = GetNextDisplacement( pRs3, fBWeightMin ); // fBWeightMin 的下一档位移
						}
						strFields += _T("a");
					}
					else
					{
						strFields += _T("b");
					}

					if( CustomID == "80VF") // 这种情况80V分档
					{
						strFields="80v";
					}

					_variant_t fWeight = pRsWeight->GetCollect(_variant_t(strFields));
					pRsOper->PutCollect( _T("Weight"), fWeight );
				}
				//给x,y赋值
				GetXYValue( x, y, CustomID, DH );
				pRsOper->PutCollect( _T("X"), _variant_t(x) );
				pRsOper->PutCollect( _T("Y"), _variant_t(y) );

				//确定sizeH的值(start)
				sizeH=vtof(pRs1->GetCollect("E"));
				pp=vtof(pRs1->GetCollect("pp"));
				T=vtof(pRs1->GetCollect("T"));
				if(CustomID=="58VA"||CustomID=="58HA")
				{
					sizeH=sizeH+pp;
				}
				if((CustomID=="58VB"||CustomID=="58VC")&&DH>=84)
				{
					sizeH=sizeH+T;
				}
				if(CustomID=="58VE"||CustomID=="58VG"||CustomID=="58HD"||CustomID=="58HE")
				{
					sizeH=sizeH;
				}
				if(CustomID=="58VES") 
				{
					D=vtof(pRs1->GetCollect(_T("D")));
					sizeH=-(D-sizeH);
				}
				pRsOper->PutCollect(_T("sizeH"),_variant_t(sizeH));
				//确定sizeH的值(end)

				pRsOper->Update();
			}//是否对记录进行分解(end)
		}
		// 从临时表中的数据拷到strHDCrude表
		pRsOper->Close();
		pRs1->Close();
		DeleteTableRecord( m_strHDCrude );
		CopyTableData( m_strHDCrude, strTmpTbl );
		RemoveTable( strTmpTbl );
		
	}
	catch(_com_error e)
	{
		AfxMessageBox( e.Description() );
	}
}

BOOL CDBSetData::CopyTableData( const CString &strTblTo, const CString &strTblFrom )
{
	if ( RemoveTable( strTblTo ) == FALSE )
	{
		//return FALSE;
	}

	CString strSQL;
	strSQL.Format( _T("SELECT * INTO [%s] FROM [%s]"), strTblTo, strTblFrom );
	return ExecuteSQL( strSQL );		 
}

BOOL CDBSetData::DeleteTableRecord( const CString &strTbl )
{
	CString strSQL;
	strSQL.Format( _T("DELETE * FROM [%s]"), strTbl );
	return ExecuteSQL( strSQL );
}

BOOL CDBSetData::RemoveTable( const CString &strTbl )
{
	CString strSQL;
	strSQL.Format( _T("DROP TABLE [%s]"), strTbl );
	try
	{
		m_pCon->Execute( _bstr_t( strSQL ), NULL, adCmdText );
	}
	catch ( _com_error& e )
	{
		//如果表不存在，也返回出错和FALSE。这导致后面调用的函数不正确退出，无法完成功能。
		AfxMessageBox(e.Description());
		return FALSE;		
	}
	return TRUE;
}

BOOL CDBSetData::ExecuteSQL( const CString& strSQL )
{
	ASSERT( !strSQL.IsEmpty() );
	try
	{
		m_pCon->Execute( _bstr_t( strSQL ),NULL , adCmdText );
	}
	catch ( _com_error& e )
	{
		AfxMessageBox(e.Description());
		return FALSE;		
	}
	return TRUE;
}

void CDBSetData::GetXYValue( float& x, float& y, const CString& CustomID, const long& DH )
{
	x = 0.0;
	y = 0.0;
	if(DH<=83)
	{
		if(  CustomID=="58VA"
		  || CustomID=="58VB"
		  || CustomID=="58VC"
		  || CustomID=="58VE"
		  || CustomID=="58VES"
		  || CustomID=="58VG" )
		{
			x=(DH<=74)?50.0 : 100.0;
			y=(DH<=74)?55.0 : 75.0;
		}
		if(  CustomID=="58HA"
		  || CustomID=="58HB"
		  || CustomID=="58HC"
		  || CustomID=="58HD"
		  || CustomID=="58HE")
		{
			x=(DH<=74)?50.0 : 100.0;
			y=(DH<=74)?55.0 : 75.0;
		}
	}
	else 
	{
		if(	   CustomID=="58VA"
			|| CustomID=="58VB"
			|| CustomID=="58VC")
		{
			x=(DH<=94)?100:200;
			y=95;
		}
		if(	   CustomID=="58HA"
			|| CustomID=="58HB"
			|| CustomID=="58HC"
			|| CustomID=="58HE")
		{
			x=(DH<=94)?100:200;
			y=95;
		}
	}
	
}

void CDBSetData::SetRecordValue( _RecordsetPtr& pRs, const _RecordsetPtr& pRsSource )
{
	long nFieldCount = pRsSource->GetFields()->GetCount();
	if ( nFieldCount != pRs->GetFields()->GetCount() )
	{
		ASSERT( NULL );
		AfxMessageBox( "两个表的结构不相同" );
		return;
	}
	for ( long i = 0; i < nFieldCount; i++ )
	{
		pRs->PutCollect( _variant_t( i ), pRsSource->GetCollect( _variant_t( i ) ) );
	}
}

long CDBSetData::GetNextDisplacement( const _RecordsetPtr& pRs, const long& lCurDisplace )
{
	long lDisp = 0;
	for ( pRs->MoveFirst(); !pRs->adoEOF; pRs->MoveNext() )
	{
		lDisp = pRs->GetCollect( _T("DisplacementSerial") );
		if ( lDisp > lCurDisplace )
		{
			return lDisp;
		}
	}
	return lDisp;
}

bool CDBSetData::UpdateCustomIDandBH()
{
// 	CustomID=vtos(pRs1->GetCollect("CustomID"));
// 	//从customid表查到对应的常州电力恒力弹簧老类别，
// 	//因为重量表的字段名使用的还是常州表的格式，而不是苏源2007版恒吊的类别名称

	try
	{
		CString strSQL;
		_RecordsetPtr pRsCustID = NULL;
		
		strSQL.Format("SELECT * FROM %s",m_strHDCustomid);
		if ( !OpenTable( pRsCustID, m_strHDCustomid ) )
			return false;
		for ( pRsCustID->MoveFirst(); !pRsCustID->adoEOF; pRsCustID->MoveNext() )
		{
			strSQL.Format("UPDATE %s SET customid = '%s' WHERE customid = '%s'",m_strHDCrude,
						vtos( pRsCustID->GetCollect("customid1")), vtos( pRsCustID->GetCollect("customid") ) );
			ExecuteSQL( strSQL );			
		}
		CString strIF;
		//更新BH思路：
		//从m_strHDDIM_Primary选择customid,minDH,maxDH，排升序，查DH在第几组，得到新BH，新BH=customid & 组号 & 组内序号
		//组号=第几组,序号=dh-minDH+1格式化为16进制
		strSQL.Format("SELECT DISTINCT customid1,customid,minDH,maxDH FROM %s as a LEFT JOIN %s AS b ON a.customid=b.customid ORDER BY a.customid,minDH,maxDH",m_strHDDIM_Primary);
		//pRsCustID->Open( _bstr_t(strSQL), (IDispatch *)m_pCon,adOpenStatic, adLockOptimistic, adCmdText );
		

		
		if("SY"==m_strStandard)
		{
		strIF= "IIF(DH<=9,'A'& chr(asc('A')+dh),\
			  IIF(DH<=18,'B'& chr(asc('A')+dh-10),IIF(DH<=34,'C'& chr(asc('A')+dh-19),\
			  IIF(DH<=49,'D'& chr(asc('A')+dh-35),IIF(DH<=63,'E'& chr(asc('A')+dh-50),\
			  IIF(DH<=74,'F'& chr(asc('A')+dh-64),IIF(DH<=83,'G'& chr(asc('A')+dh-75),\
			  IIF(DH<=88,'H'& chr(asc('A')+dh-84),IIF(DH<=94,'I'& chr(asc('A')+dh-89),\
			  IIF(DH<=98,'J'& chr(asc('A')+dh-95),IIF(DH<=102,'K'& chr(asc('A')+dh-99),\
			  IIF(DH<=106,'L'& chr(asc('A')+dh-103),'M'& chr(asc('A')+dh-107)))))))))))))";
		}
		else if("2010"==m_strStandard)
		{
		strIF= "IIF(DH<=9,'1'& HEX(dh),\
			  IIF(DH<=18,'2'& HEX(dh-10),IIF(DH<=34,'3'& HEX(dh-19),\
			  IIF(DH<=49,'4'& HEX(dh-35),IIF(DH<=63,'5'& HEX(dh-50),\
			  IIF(DH<=74,'6'& HEX(dh-64),IIF(DH<=83,'7'& HEX(dh-75),\
			  IIF(DH<=88,'8'& HEX(dh-84),IIF(DH<=94,'9'& HEX(dh-89),\
			  IIF(DH<=98,'A'& HEX(dh-95),IIF(DH<=102,'B'& HEX(dh-99),\
			  IIF(DH<=106,'C'& HEX(dh-103),'D'& HEX(dh-107)))))))))))))";
		}

		strSQL.Format("UPDATE %s SET bh = customid & %s",m_strHDCrude, strIF );
		ExecuteSQL( strSQL );

		strSQL.Format("UPDATE %s SET bh = '609' & %s \
						WHERE trim(CustomID) = '609S'",m_strHDCrude, strIF );
		ExecuteSQL( strSQL );
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
		return false;
	}
	return true;
}

CString CDBSetData::ConversionCustomID( const CString &strCustID, int nInputIDCol ) const
{
	if ( strCustID.IsEmpty() || m_strStandard == "CZ" )
		return strCustID;
	//从customid表查到对应的常州电力恒力弹簧老类别，
	//因为重量表的字段名使用的还是常州表的格式，而不是苏源2007版恒吊的类别名称
	static _RecordsetPtr s_pRs = NULL;
	if ( s_pRs == NULL )
	{
		if ( !OpenTable( s_pRs, m_strHDCustomid ) )
			return strCustID;
	}
	CString strInField = "customid1";
	CString strOutField = "customid";
	if ( nInputIDCol != 1 )
	{
		strInField = "customid";
		strOutField = "customid1";
	}
	CString strSQL;
	strSQL.Format("%s='%s'",strInField, strCustID );
	s_pRs->Filter="";
	s_pRs->Filter = _variant_t(strSQL);
	if( s_pRs->GetRecordCount() > 0 )
	{
		return vtos(s_pRs->GetCollect( _variant_t( strOutField ) ));
	}
	else
	{
		AfxMessageBox(m_strHDCustomid+"表中没找到"+strInField+strCustID);
		ASSERT(NULL);
		return strCustID;
	}

}


bool CDBSetData::FindFieldNameFromEHS(CString strDBPHS,CString strDBEHS,CProgressCtrl &m_progess2)
{
	//数据库版本转换:任意版本=>dVer版本(dbEncrypt,dbVersion10,dbVersion11,dbVersion20,dbVersion3
    BOOL a=FileExists(strDBPHS);
	if ( !a )
		return FALSE;
	a=FileExists(strDBEHS);
	if ( !a )
		return FALSE;
	try
 	{

		SetDBPath(strDBPHS);
		
		a=RemoveTable("FieldNamePHS_EHS");
		a=CreateTable("FieldNamePHS_EHS",strDBPHS);
		a=DeleteTableRecord("FieldNamePHS_EHS");
		

		a=RemoveTable("FieldNamePHS_EHS_SQL");
		CString strSQL;
		strSQL.Format("CREATE TABLE FieldNamePHS_EHS_SQL (CustomID CHAR(20), ID CHAR(20), PHSFdNames CHAR(255),EHSFdNames CHAR(255),PHSDbName CHAR(255))");
		a=ExecuteSQL(strSQL);

		short i=3;
		if(2==i)
		{
			
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iPA,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iPAFIX,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iPART,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iSPRING,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iCSPRING,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iSA,m_progess2);
		a=FindFieldNameFromEHS_Type2(strDBPHS,strDBEHS,iBOLTSNUTS,m_progess2);
		
		}
		else if(3==i)
		{
			
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iPA,m_progess2);
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iPAFIX,m_progess2);
			
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iPART,m_progess2);
			
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iSPRING,m_progess2);
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iCSPRING,m_progess2);
			/*
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iSA,m_progess2);
			a=FindFieldNameFromEHS_Type3(strDBPHS,strDBEHS,iBOLTSNUTS,m_progess2);
			*/
			
		}
		else if(4==i)
		{
			/*
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iPA,m_progess2);
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iPART,m_progess2);
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iSPRING,m_progess2);
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iCSPRING,m_progess2);
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iSA,m_progess2);
			a=FindFieldNameFromEHS_Type4(strDBPHS,strDBEHS,iBOLTSNUTS,m_progess2);
			*/
		}
	}

	catch(_com_error& e)
	{
		AfxMessageBox(e.Description());
	}
	catch(CDaoException* e)
	{
		e->ReportError();
		e->Delete();
	}
	catch(...)
	{
		AfxMessageBox("Exceptoin in FindFieldNameFromEHS()");
	}
	return TRUE;

}

bool CDBSetData::AddMaterialToTableX()
{
	//运行本函数：启动程序后，弹出对话框选择目录，选择Material.mdb所在目录，再点击菜单"原始数据-在material.mdb加入材料特性数据到各表"
	CString strMdb;

	BOOL bResult =FALSE;
	if(m_strDBDir.Right(1)!="\\") m_strDBDir+="\\";
	
	m_strDBPathName.Format(_T("%sMaterial.mdb"),m_strDBDir);
	ConnectionDatamdb();

	CString strTable;
	strTable.Format(_T("最全的溶剂参数表"));
	AddMaterialToMaterialCodeFind(strTable);

	AddMaterialToMaterialCode(strTable);

	AddMaterialToMaterial(strTable);

	AddMaterialToMaterialPropertyTable(strTable);

	return TRUE;

}

short CDBSetData::AddMaterialToMaterialPropertyTable(CString strTable)
{//如果Material表ID字段值等于
	_RecordsetPtr pRsA=NULL;//表:MaterialX
	_RecordsetPtr pRsB=NULL;//表:PropertyInfoN
	_RecordsetPtr pRsC=NULL;//表:
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));
	pRsC.CreateInstance(__uuidof(Recordset));

	CString strSQL=_T("");

	_variant_t key,ID_X;
	short n;

	bool bFlag=FALSE;
	CString strFDNameFrom,strFDNameTo,strDestTable;

	try
	{
		strSQL.Format("SELECT * FROM PropertyInfo%d ORDER BY ID",m_iAutoPSA_ID);
		pRsB->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,\
			adLockPessimistic,adCmdText);
		while( !pRsB->adoEOF)
		{
			strFDNameFrom=vtos(pRsB->GetCollect(_variant_t(_T("FieldNameFrom"))));
			strFDNameTo=vtos(pRsB->GetCollect(_variant_t(_T("FieldName"))));
			strDestTable=vtos(pRsB->GetCollect(_variant_t(_T("TableName"))));
			if(!strFDNameFrom.IsEmpty())
			{//如果源字段不为空，再判断目的字段属性值为空或是不空，为空表示目的表目的字段属性值=MaterialProtID，否则目的表目的字段属性值=Material表ID字段值
				strSQL.Format("SELECT * FROM %s WHERE ID IN (SELECT materialProtID FROM %s ) ORDER BY ID",strDestTable,strTable);
				pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,\
					adLockPessimistic,adCmdText);
				if( !pRsA->adoEOF)
				{	
					pRsA->Close();
					strSQL.Format("DELETE FROM %s WHERE ID IN (SELECT MaterialprotID FROM %s)",strDestTable,strTable);
					bFlag=ExecuteSQL(strSQL);
				}
				else
					pRsA->Close();
				/*strSQL.Format("INSERT INTO %s ( ID, Material, t, Value )\
							  SELECT MaterialProtID, Material, t, [%s]\
								  FROM %s",strDestTable,strFDNameFrom,strTable);
				bFlag=ExecuteSQL(strSQL);*/
				//使用这个SQL语句报告插入语句语法错误，在ACCESS操作也提示Value有问题，怀疑Value是内部关键字，改成用字段对象添加记录。
				strSQL.Format("SELECT * FROM %s ORDER BY ID",strDestTable);
				pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,adLockPessimistic,adCmdText);
				strSQL.Format("SELECT * FROM %s ORDER BY MaterialProtID",strTable);
				pRsC->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,adLockPessimistic,adCmdText);
				while(!pRsC->adoEOF)
				{
					n=AddMaterialXFindMaterial(pRsC,pRsA);
					pRsA->AddNew();
					pRsA->PutCollect(_T("ID"),pRsC->GetCollect(_T("MaterialProtID")));
					pRsA->PutCollect(_T("Material"),pRsC->GetCollect(_T("Material")));
					pRsA->PutCollect(_T("t"),pRsC->GetCollect(_T("t")));
					pRsA->PutCollect(_T("Value"),pRsC->GetCollect(_variant_t(strFDNameFrom)));
					pRsA->Update();
					pRsC->MoveNext();
				}
				pRsC->Close();
				pRsA->Close();
			}
			pRsB->MoveNext();
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	return 0;
}

short CDBSetData::AddMaterialXFindMaterial(_RecordsetPtr pRSrc,_RecordsetPtr pRDest)
{
	CString strSQL=_T("");

	_variant_t key;

	try
	{
		strSQL.Format(_T("Material='%s'"),vtos(pRSrc->GetCollect(_T("Material"))));
		pRDest->Find(_bstr_t(strSQL),0,adSearchForward,"");
		if( !pRDest->adoEOF)
		{
			AfxMessageBox(strSQL);
			return 1;//有重复的材料
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return -1;
	}
	return 0;
}

short CDBSetData::AddMaterialToMaterialCodeFind(CString strTable)
{
	_RecordsetPtr pRsA=NULL;//表:MaterialCodeFind
	_RecordsetPtr pRsB=NULL;//表:MaterialCodeFind
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));

	CString strSQL=_T("");

	_variant_t key;

	try
	{
		//如果MaterialCodeFind表不存在code_find字段值=strTable的记录，则说明strTable没有加入到规范中，取AutoPSA_ID字段的最大值为strTable规范的AutoPSA_ID值。否则，取CODE_ID字段值为规范CODE_ID值。
		strSQL.Format("select * from MaterialCodeFind WHERE CODE_FIND=\'%s\' order by AutoPSA_ID",strTable);
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
			adLockOptimistic,adCmdText);
		if( pRsA->adoEOF)
		{
			strSQL.Format("select * from MaterialCodeFind order by AutoPSA_ID");
			pRsB->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
				adLockOptimistic,adCmdText);
			if( pRsB->adoEOF)
			{
			}
			else
			{
				m_iAutoPSA_ID=pRsB->GetCollect(_T("AutoPSA_ID"));
				pRsA->AddNew();
				pRsA->PutCollect(_T("AutoPSA_ID"),_variant_t(m_iAutoPSA_ID));
				pRsA->PutCollect(_T("CODE_FIND"),_variant_t(strTable));
				pRsA->PutCollect(_T("CODE"),_variant_t(strTable));
				pRsA->PutCollect(_T("CODE_ID"),_variant_t(m_iAutoPSA_ID));
				pRsA->Update();
			}
			pRsB->Close();
		}
		else
		{//规范已经存在
			m_iAutoPSA_ID=pRsA->GetCollect(_T("AutoPSA_ID"));
			m_iCODE_ID=pRsA->GetCollect(_T("CODE_ID"));
		}
		pRsA->Close();
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return -1;
	}
	return 0;
}

short CDBSetData::AddMaterialToMaterialCode(CString strTable)
{
	_RecordsetPtr pRsA=NULL;//表:MaterialCode
	pRsA.CreateInstance(__uuidof(Recordset));

	CString strSQL=_T("");

	_variant_t key;
	bool bFlag=FALSE;

	try
	{
		strSQL.Format("SELECT materialProtID FROM MaterialCode WHERE materialProtID IN (SELECT materialProtID FROM %s )",strTable);
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,\
			adLockPessimistic,adCmdText);
		if( pRsA->adoEOF)
		{
			pRsA->Close();
			//如果MaterialCode表不存在materialProtID字段值=strTable表中materialProtID字段值的记录，则说明strTable表记录没有加入MaterialCode到中

			strSQL.Format("INSERT INTO MaterialCode ( CodeID, Material, MaterialID, MaterialProtID )\
				SELECT CodeID, Material, MaterialID, MaterialProtID\
				FROM %s",strTable);
			bFlag=ExecuteSQL(strSQL);
		}
		else
		{//材料已经存在于MaterialCode表规范中
			pRsA->Close();
			strSQL.Format(_T("DELETE * FROM MaterialCode WHERE materialProtID IN( SELECT materialProtID FROM %s )"),strTable);
			bFlag=ExecuteSQL(strSQL);
			strSQL.Format("INSERT INTO MaterialCode ( CodeID, Material, MaterialID, MaterialProtID )\
						  SELECT CodeID, Material, MaterialID, MaterialProtID\
						  FROM %s",strTable);
			bFlag=ExecuteSQL(strSQL);
		}

	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	return 0;

}

short CDBSetData::AddMaterialToMaterial(CString strTable)
{
	_RecordsetPtr pRsA=NULL;//表:Material
	pRsA.CreateInstance(__uuidof(Recordset));

	CString strSQL=_T("");

	_variant_t key;
	bool bFlag=FALSE;

	try
	{
		strSQL.Format("SELECT ID FROM Material WHERE ID IN (SELECT materialProtID FROM %s )",strTable);
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenDynamic,\
			adLockPessimistic,adCmdText);
		if( pRsA->adoEOF)
		{
			pRsA->Close();
			//如果Material表不存在materialProtID字段值=strTable表中materialProtID字段值的记录，则说明strTable表记录没有加入MaterialCode到中

			strSQL.Format("INSERT INTO Material ( ID ) SELECT MaterialProtID FROM %s",strTable);
			bFlag=ExecuteSQL(strSQL);
		}
		else
		{//材料已经存在于MaterialCode表规范中
			pRsA->Close();
			strSQL.Format(_T("DELETE * FROM Material WHERE ID IN( SELECT materialProtID FROM %s )"),strTable);
			bFlag=ExecuteSQL(strSQL);
			strSQL.Format("INSERT INTO Material ( ID ) SELECT MaterialProtID FROM %s",strTable);
			bFlag=ExecuteSQL(strSQL);
		}

	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	return 0;

}
bool CDBSetData::GetSumFieldSQL(CString strTableName, _ConnectionPtr pConn, CString &strSQLsum,short& iFDN)
{
	CString strSQL,strFdName;
	strSQLsum="";
	
	int iFdType;
	_RecordsetPtr rs=NULL;
	rs=pConn->OpenSchema(adSchemaColumns);
	iFDN=0;
	while(!rs->adoEOF)
	{
		if(strTableName==vtos(rs->GetCollect("TABLE_NAME")))
		{
			iFdType=vtoi(rs->GetCollect("DATA_TYPE"));
			strFdName=vtos(rs->GetCollect("COLUMN_NAME"));
			switch(iFdType)
			{
			case adInteger:
			case adTinyInt:
			case adSmallInt:
			case adBigInt:
			case adUnsignedTinyInt:
			case adUnsignedSmallInt:
			case adUnsignedInt:
			case adUnsignedBigInt:
			case adSingle:
			case adDouble:
			case adDecimal:
			case adNumeric:
			case adBoolean:
				//case adChar:
				strSQL.Format (" SUM(%s) as N%s,",strFdName,strFdName);//把求和字段命名为N+原字段名，以便函数外使用
				strSQLsum+=strSQL;
				iFDN++;
				break;
			default:
				break;
			}
		}
		rs->MoveNext();
	}
	
	return true;
}

bool CDBSetData::GetSumFieldSQL2(_RecordsetPtr& rs, CString &strSQLsum,short& iFDN)
{
	CString strSQL,strFdName;
	strSQLsum="";
	
	int iFdType;
	int iFdNums=0;
	int k=-1;
	iFdNums=rs->GetFields()->GetCount();
	iFDN=0;
	for(short i=0;i<iFdNums;i++)
	{
		iFdType=rs->Fields->GetItem(i)->GetType();
		strFdName=(LPCTSTR)rs->Fields->GetItem(i)->GetName();
		k=strFdName.Find(".");
		if(-1!=k) strFdName=strFdName.Mid(k+1);
		switch(iFdType)
		{
		case adInteger:
		case adTinyInt:
		case adSmallInt:
		case adBigInt:
		case adUnsignedTinyInt:
		case adUnsignedSmallInt:
		case adUnsignedInt:
		case adUnsignedBigInt:
		case adSingle:
		case adDouble:
		case adDecimal:
		case adNumeric:
		case adBoolean:
			//case adChar:
			strSQL.Format (" SUM(%s) as %sN,",strFdName,strFdName);//把求和字段命名为原字段名+N，以便函数外使用
			strSQLsum+=strSQL;
			iFDN++;
			break;
		default:
			break;
		}
	}
	
	return true;
}

bool CDBSetData::WriteFieldName2Table(CString strCustomID, CString strFDNamePHS, CString strFDNameEHS)
{
	return TRUE;
}

 bool CDBSetData::FindFieldNameFromEHS_Type2(CString strDBPHS, CString strDBEHS, short iType, CProgressCtrl &m_progress)
 {
 	//这个函数逐个比较每个字段值，目前对每个CustomID字段只找出了5个数字字段，漏了一些字段
 	
 	_ConnectionPtr pPHS=NULL;
 	_ConnectionPtr pEHS=NULL;
 	pPHS.CreateInstance(__uuidof(Connection));
 	pEHS.CreateInstance(__uuidof(Connection));
 	_RecordsetPtr pRSPhs=NULL,rsID=NULL;
 	_RecordsetPtr pRSEhs=NULL,rs1=NULL,rs2=NULL,rs3=NULL;
 	pRSPhs.CreateInstance(__uuidof(Recordset));
 	pRSEhs.CreateInstance(__uuidof(Recordset));
	rsID.CreateInstance(__uuidof(Recordset));
	rs1.CreateInstance(__uuidof(Recordset));
 	rs2.CreateInstance(__uuidof(Recordset));
 	rs3.CreateInstance(__uuidof(Recordset));
 
 	try
 	{
 		
 		SetDBPath(strDBEHS);
 		pEHS=m_pCon;
 		
 		SetDBPath(strDBPHS);//这个调用必须放在前一个SetDBPath(strDBEHS)后面，为了后面正确执行ExecuteSQL函数
 		pPHS=m_pCon;
 		
 		
 		CString strConn;
 		strConn.Format("SELECT * FROM PhsManu%s",strPhsManu[iType]);
 		pRSPhs->Open (_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		strConn.Format("SELECT * FROM ComponentID");
 		pRSEhs->Open (_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		
 		if(pRSPhs->BOF && pRSPhs->adoEOF)
 			return FALSE;
 		else
 		{
 			//从管理表PhsManuXXXX找原始数据表名称和ID-CustomID对照表名称
 			strTableData[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNTableData[iType]) ));
 			strID[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNID[iType]) ));
 		}
 		CString strSQL,strSQL1,strCSTM;
 		CString strSQL01,strSQL02;//分别记录从EHS提取数据时需要的字段
 		bool b=false;
 		bool bFound=false;//在EHS的第1个表没找到PHS对应的字段，再找EHS的第2个表
 		short iFDCount=0,iFDCount2=0,iFDCount3=0;
 		//打开PHS原始数据表
 		strConn.Format("SELECT * FROM %s ORDER BY BH",strTableData[iType]);
 		rs1->Open(_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		//获得求和的SQL语句
 		//b=GetSumFieldSQL(strTableData[iType],pPHS,strSQL,iFDCount);
		b=GetSumFieldSQL2(rs1,strSQL,iFDCount);
 		if(!b) 
 			return false;
 		strSQL1.Format("SELECT  %s count(*) as iCount,CustomID FROM %s  GROUP BY CustomID ORDER BY CustomID",strSQL,strTableData[iType]);
 		
 		rs1->Close();
 		rs1->Open(_variant_t(strSQL1),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		
 		strConn.Format("SELECT * FROM %s",strTableData2[iType]);
 		rs2->Open(_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		//b=GetSumFieldSQL(strTableData2[iType],pEHS,strSQL,iFDCount2);
		b=GetSumFieldSQL2(rs2,strSQL,iFDCount2);
 		if(!b) 
 			return false;
 		//EHS库strTableData2[iType]表strTableData[iType]表size7字段存储温度，PHS库TJ字段存储温度
 		strSQL1.Format("SELECT  %s count(*) as iCount,CustomID FROM %s GROUP BY CustomID ORDER BY CustomID",strSQL,strTableData2[iType]);
 		
 		rs2->Close();
 		rs2->Open(_variant_t(strSQL1),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
 
 		strConn.Format("SELECT * FROM %s",strTableData3[iType]);
 		rs3->Open(_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		//b=GetSumFieldSQL(strTableData3[iType],pEHS,strSQL,iFDCount3);
		b=GetSumFieldSQL2(rs3,strSQL,iFDCount3);
 		if(!b) 
 			return false;
 		//EHS库size7字段存储温度，PHS库TJ字段存储温度
 		if(0<strDBPHS.Find("XBDLY"))
 		{
 			if(iPA==iType||iPART==iType||iSA==iType||iSPRING==iType||iCSPRING==iType)
 			{
 				strCSTM="LEFT(CompCode,INSTR(CompCode,'.')-1)";
 			}
 		}
 		else if(0<strDBPHS.Find("HDDLY"))
 		{
 			if(iPA==iType||iPART==iType||iSA==iType||iSPRING==iType||iCSPRING==iType)
 			{
 				strCSTM="LEFT(CompCode,INSTR(CompCode,'-')-1)";
 			}
 
 		}
 		else if(0<strDBPHS.Find("LISEGA"))
 		{
 			strCSTM="LEFT(CompCode,2)";
 		}
 		else if(0<strDBPHS.Find("ZD2010"))
 		{
 			if(iPA==iType||iPART==iType||iSPRING==iType||iCSPRING==iType)
 			{
 				strCSTM="LEFT(CompCode,3)";
 			}
 			else if(iSA==iType)
 			{
 				strCSTM="LEFT(CompCode,5)";
 			}
 		}
 		strSQL1.Format("SELECT  %s count(*) as iCount,CustomID FROM (SELECT  *,%s as CustomID FROM %s ) GROUP BY CustomID  ORDER BY CustomID",strSQL,strCSTM,strTableData3[iType]);
 		
 		rs3->Close();
 		rs3->Open(_variant_t(strSQL1),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
 		
 		while(!(rs1->BOF&&rs1->adoEOF))
 		{
 	
 			
 			CString strCustomID,strID3;
 			CString strFdName1,strFdName2,strFdName3;
 			strCustomID=vtos(rs1->GetCollect("CustomID"));
 			strSQL01="";
 			strSQL02="";
 			strFdName1="";
 			strFdName2="";
 			strFdName3="";
 			
 			
 			int ic1=0,ic2=0,ic3=0;
			short i,i1,i2;
 			ic1=vtoi(rs1->GetCollect("iCount"));
 			
 			if(!(rs2->BOF&&rs2->adoEOF))
 			{
 				ic2=vtoi(rs2->GetCollect("iCount"));
 				for( i=0;i<iFDCount;i++)
 				{
 					//逐个比较AutoPHS和EHS的零部件原始数据表各个数值字段和，看哪2个字段存储相同的尺寸
 					bFound=false;
 					if(ic1!=ic2) break;//记录个数不等，不比较了
 					if(0==vtoi(rs1->GetCollect((const _variant_t)(i)))) continue;
 					for( i2=0;i2<iFDCount2;i2++)
 					{
 						//if(0==vtoi(rs2->GetCollect((const _variant_t)(i2)))) continue;
 						if(fabs(vtof(rs1->GetCollect((const _variant_t)(i)))/vtof(rs2->GetCollect((const _variant_t)(i2)))-1)<=1e-3)
 						{
 							//相等，存储字段名
 							bFound=true;
							pRSEhs->Find(_bstr_t("CustomID='"+strCustomID+"'"),0,adSearchForward);
							if(!(pRSEhs->BOF&&pRSEhs->adoEOF))
								strID3=vtos(pRSEhs->GetCollect("ID"));
							else
								strID3="";
 							strFdName1=(LPCTSTR)rs1->Fields->GetItem(i)->GetName();
 							strFdName2=(LPCTSTR)rs2->Fields->GetItem(i2)->GetName();
 							
 							strFdName1=strFdName1.Left(strFdName1.GetLength()-1);
 							strFdName2=strFdName2.Left(strFdName2.GetLength()-1);
 							strSQL01=strSQL01+strFdName1+",";
 							strSQL02=strSQL02+strTableData2[iType]+"."+strFdName2+",";
 							AddNewRecord(strCustomID,strID3,strTableData[iType],(LPCTSTR)strFdName1,strTableData2[iType],(LPCTSTR)strFdName2,pPHS);
 							break;
 						}
 					}
 					if(!bFound && (i2==iFDCount2-1))
 					{
 						//没找到，并且找完了rs2所有列，再找另外一个表的所有列
 						if(!(rs3->BOF&&rs3->adoEOF))
 						{
 							ic3=vtoi(rs3->GetCollect("iCount"));
 							if(ic1!=ic3) break;//记录个数不等，不比较了
 							for(short i3=0;i3<iFDCount3;i3++)
 							{
 								//if(0==vtoi(rs3->GetCollect((const _variant_t)(i3)))) continue;
 								if(fabs(vtof(rs1->GetCollect((const _variant_t)(i)))/vtof(rs3->GetCollect((const _variant_t)(i3)))-1)<=1e-2)
 								{
 									//相等，存储字段名
 									bFound=true;
 									strFdName1=(LPCTSTR)rs1->Fields->GetItem(i)->GetName();
 									strFdName3=(LPCTSTR)rs2->Fields->GetItem(i3)->GetName();
 									
 									strFdName1=strFdName1.Mid(1);
 									strFdName3=strFdName3.Mid(1);
 									strSQL01=strSQL01+strFdName1+",";
 									strSQL02=strSQL02+strTableData3[iType]+"."+strFdName3+",";
 									AddNewRecord(strCustomID,strID3,strTableData[iType],(LPCTSTR)strFdName1,strTableData3[iType],(LPCTSTR)strFdName3,pPHS);
 									break;
 								}
 							}
 						}
 					}
 				}
 				
 			}
 			//把选择数据的字段集合写到数据库
 			strSQL.Format("INSERT INTO FieldNamePHS_EHS_SQL (CustomID , ID , PHSFdNames , EHSFdNames,PHSDbName) VALUES ('%s','%s','%s','%s','%s')",strCustomID,strID3,strSQL01,strSQL02,strDBPHS);
 			ExecuteSQL(strSQL);
 			if(!rs3->adoEOF) rs3->MoveNext();
 			if(!rs2->adoEOF) rs2->MoveNext();
 			if(!rs1->adoEOF) rs1->MoveNext();
 		}
 	}
 	catch ( _com_error& e )
 	{
 		AfxMessageBox( e.Description() );
 		return false;
 	}
 	return true;
 }

bool CDBSetData::FindFieldNameFromEHS_Type3(CString strDBPHS, CString strDBEHS, short iType, CProgressCtrl &m_progress)
{
	//这个函数可以找出文本和数字字段，但到了D14因为记录数分别为228,202，不相等，死循环了。
	
	_ConnectionPtr pPHS=NULL;
	_ConnectionPtr pEHS=NULL;
	pPHS.CreateInstance(__uuidof(Connection));
	pEHS.CreateInstance(__uuidof(Connection));
	_RecordsetPtr pRSPhs=NULL,rsID=NULL;
	_RecordsetPtr pRSEhs=NULL,rs1=NULL,rs2=NULL,rs3=NULL;
	pRSPhs.CreateInstance(__uuidof(Recordset));
	pRSEhs.CreateInstance(__uuidof(Recordset));
	rsID.CreateInstance(__uuidof(Recordset));
	rs1.CreateInstance(__uuidof(Recordset));
	rs2.CreateInstance(__uuidof(Recordset));
	rs3.CreateInstance(__uuidof(Recordset));
	short i,i1,i2;

	try
	{
		
		SetDBPath(strDBEHS);
		pEHS=m_pCon;

		SetDBPath(strDBPHS);//这个调用必须放在前一个SetDBPath(strDBEHS)后面，为了后面正确执行ExecuteSQL函数
		pPHS=m_pCon;
		
		
		CString strConn;
		strConn.Format("SELECT * FROM PhsManu%s",strPhsManu[iType]);
		pRSPhs->Open (_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
		strConn.Format("SELECT * FROM ComponentID");
		pRSEhs->Open (_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
		
		if(pRSPhs->BOF && pRSPhs->adoEOF)
			return FALSE;
		else
		{
			//从管理表PhsManuXXXX找原始数据表名称和ID-CustomID对照表名称
			strTableData[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNTableData[iType]) ));
			strID[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNID[iType]) ));
			strConn.Format("SELECT * FROM %s",strID[iType]);
			rsID->Open (_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
		}
		CString strSQL,strSQL1,strCSTM;
		CString strSQL01,strSQL02;//分别记录从EHS提取数据时需要的字段
		bool b=false;
		bool bFound=false;//在EHS的第1个表没找到PHS对应的字段，再找EHS的第2个表
		short iFDCount1=0,iFDCount2=0,iFDCount3=0;
		//打开PHS原始数据表
		strConn.Format("SELECT * FROM %s ORDER BY BH",strTableData[iType]);
		rs1->Open(_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
		
		strConn.Format("SELECT DISTINCT * FROM %s as a LEFT JOIN %s as b ON a.CompCode=b.CompCode ORDER BY a.CompCode",strTableData2[iType],strTableData3[iType]);
		rs2->Open(_variant_t(strConn),(IDispatch *)pEHS,adOpenKeyset,adLockOptimistic,adCmdText);
		//EHS库size7字段存储温度，PHS库TJ字段存储温度
		
		CString strCustomID,strBH,strCustomID2,strCompCode,strID3;
		CString strFdName1,strFdName2,strFdName3;
		_variant_t v1,v2;
		int iMaxRecordCount1=0,iMaxRecordCount2=0;//存储一个CustomID对应的记录总行数，每个CustomID比较各个字段时不得超过这个记录数
		int iCountCmp=0,iCountEQ=0;//分别存储已经比较了多少行，已经比较行有多少行相等
		iFDCount1=rs1->GetFields()->GetCount();
		iFDCount2=rs2->GetFields()->GetCount();
		while(!rs1->adoEOF)
		{					
			strSQL01="";
			strSQL02="";
			for( i1=0;i1<iFDCount1;i1++)
			{
				//逐个比较AutoPHS和EHS的零部件原始数据表各个数值字段和，看哪2个字段存储相同的尺寸
				bFound=false;
				
				strBH=vtos(rs1->GetCollect("BH"));
				strCompCode=vtos(rs2->GetCollect("a.CompCode"));
				strFdName1="";
				strFdName2="";
				strFdName3="";
				for(short i2=0;i2<iFDCount2;i2++)
				{
					strCustomID=vtos(rs1->GetCollect("CustomID"));					
					if(!(rs2->adoEOF))
					{							
						strCustomID2=vtos(rs2->GetCollect("CustomID"));
						
						v1=rs1->GetCollect((const _variant_t)(i1));
						v2=rs2->GetCollect((const _variant_t)(i2));
						if(i2==0)
						{	
							//iMaxRecordCount(rs1,rs2,strCustomID,iMaxRecordCount1,iType);
							iMaxRecordCount3(rs1,rs2,strCustomID,iMaxRecordCount1,iMaxRecordCount2);
						}
						
						if(iMaxRecordCount1==iMaxRecordCount2)
						{
							bFound=FieldsCompare(rs1,rs2,i1,i2,iCountEQ,iMaxRecordCount1,iMaxRecordCount2);
							if(bFound)
							{
								rsID->Find(_bstr_t("CustomID='"+strCustomID+"'"),0,adSearchForward);
								if(!(rsID->BOF&&rsID->adoEOF))
									strID3=vtos(rsID->GetCollect("ID"));
								else
									strID3="";
								strFdName1=(LPCTSTR)rs1->Fields->GetItem(i1)->GetName();
								strFdName2=(LPCTSTR)rs2->Fields->GetItem(i2)->GetName();
								strSQL01=strSQL01+strFdName1+",";
								strSQL02=strSQL02+strFdName2+",";
								AddNewRecord(strCustomID,strID3,strTableData[iType],(LPCTSTR)strFdName1,strTableData2[iType],(LPCTSTR)strFdName2,pPHS);
								//如果这里不退出for循环，则会从EHS找出与PHS某字段相等的所有字段
								break;
							}
						}						
					}
				}
			}
			if(iMaxRecordCount1==iMaxRecordCount2)
			{
				//把选择数据的字段集合写到数据库
				strSQL.Format("INSERT INTO FieldNamePHS_EHS_SQL (CustomID ,ID, PHSFdNames , EHSFdNames,PHSDbName) VALUES ('%s','%s','%s','%s','%s')",strCustomID,strID3,strSQL01,strSQL02,strDBPHS);
				ExecuteSQL(strSQL);
			}
			
			if(iFDCount1<=i1) 
			{//当2层for循环完了，表示已经找完了某个customid的所有字段，要移动到下一个不同的CustomID。
				//因为在FieldsCompare或iMaxRecordCount函数里已经退回到首个相等记录，故需要往尾移动记录。
				int k=0;
				for( k=0;k<iMaxRecordCount1;k++)
				{
					if(!rs1->adoEOF ) rs1->MoveNext();
				}
				for( k=0;k<iMaxRecordCount2;k++)
				{
					if(!rs2->adoEOF ) rs2->MoveNext();
				}
			}								
		}
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
		return false;
	}
	return true;
}

bool CDBSetData::CreateTable(CString strTableName,CString strDBFile)
{
	try
	{
		SetDBPath(strDBFile);
		ConnectionDatamdb();
		CString strSQL;
		strSQL.Format("CREATE TABLE %s (CustomID CHAR(20),ID CHAR(20), PHSTableDataName CHAR(20), PHSFdName CHAR(20), EHSTableDataName CHAR(20),EHSFdName CHAR(20),PHSDbName CHAR(255),CONSTRAINT pk_cst_PhsFdName UNIQUE (CustomID,PHSFdName)) ",strTableName);
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return false;
	}
	return true;
}

bool CDBSetData::AddNewRecord(CString strCustomID,CString strID2, CString PHSTableDataName, CString FdNamePHS, CString EHSTableDataName, CString FdNameEHS,_ConnectionPtr& pCon)
{
	try
	{
		CString strSQL;
		strSQL.Format("INSERT INTO FieldNamePHS_EHS (CustomID,ID ,PHSTableDataName, PHSFdName ,EHSTableDataName, EHSFdName) VALUES ('%s','%s','%s','%s','%s','%s')",strCustomID,strID2,PHSTableDataName,FdNamePHS,EHSTableDataName,FdNameEHS);
		pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return false;
	}
	return true;

}

bool CDBSetData::FieldsCompare(_RecordsetPtr &rs1, _RecordsetPtr &rs2,short i1,short i2, int &iCountEQ, int &iMaxRecordCount1, int &iMaxRecordCount2)
{
	_variant_t v1,v2;
	CString str1,str2;
	CString strBH,strCompCode,strSQL;
	
	int k1=0,k2=0;
	int iCountNULL=0;
	iCountEQ=0;
	int n1=min(iMaxRecordCount2,iMaxRecordCount1);
	for(k1=1;k1<=n1;k1++)
	{
		strBH=vtos(rs1->GetCollect("BH"));
		if(!rs2->adoEOF)
		{
			v1=rs1->GetCollect((const _variant_t)(i1));
			v2=rs2->GetCollect((const _variant_t)(i2));
			str1=vtos(v1);
			str2=vtos(v2);
			if(v1!=v2)
			{
				short i=str2.GetLength();
				if(v1.vt==VT_BSTR)
				{
					if(i>=6)
					{//字符长度>=6的,去掉后2个字符，值相同，认为字段值相等
						if(str1.Left(i-2)==str2.Left(i-2))
						iCountEQ++;	
					}
				}
				else
				{	
					if(1e-5<fabs(vtof(v2)) && 1e-5>fabs(1-vtof(v1)/vtof(v2)))
						iCountEQ++;	
				}
			}
			else
			{
				if(str1==str2 && str1=="")
				{
					iCountNULL++;
				}
				iCountEQ++;
			}
		}
		rs1->MoveNext();
		rs2->MoveNext();
	}
	for(;k1>1;k1--)
	{
		//退回到进入时位置
		rs1->MovePrevious();
		rs2->MovePrevious();
	}
	if(n1!=iCountEQ||iCountNULL==n1)//排除所有相等且空的字段
		return false;
	else
		return true;
}
bool CDBSetData::iMaxRecordCount(_RecordsetPtr &rs1, _RecordsetPtr &rs2,CString strCustomID,  int &iMaxRecordCountByCustomID,short iType)
{
	_variant_t v1,v2;
	CString strCustomID1,strCustomID2;
	CString strBH,strCompCode,strTJ,strTJ2,strSQL;
	int k1=0,k2=0,i=0,i1;
	int k3=100000;
	iMaxRecordCountByCustomID=0;
	if(!rs1->adoEOF) 
	{
		strCustomID1=vtos(rs1->GetCollect("CustomID"));
		strCustomID2=vtos(rs2->GetCollect("CustomID"));
		if("D14"==strCustomID2)
			short ii=1;
		for(k1=1;(i=strCustomID2.CompareNoCase(strCustomID))==0;k1++)
		{
			strBH=vtos(rs1->GetCollect("BH"));
			strTJ=vtos(rs1->GetCollect("TJ"));
			strCompCode=vtos(rs2->GetCollect("a.CompCode"));
			strTJ2=vtos(rs2->GetCollect("a.size7"));
			if(strBH==strCompCode)
			{
				if(strTJ==strTJ2)
				{
					iMaxRecordCountByCustomID++;
					rs1->MoveNext();
					rs2->MoveNext();
				}
				else
				{
					iMaxRecordCountByCustomID=0;
					rs1->MoveNext();
					rs2->MoveNext();
				}
			}
			else
			{
				strSQL="a.CompCode='"+strBH+"'";
				_variant_t rs2b=rs2->Bookmark;
				rs2->Find(_bstr_t(strSQL),0,adSearchForward);
				if(!(rs2->adoEOF))
				{//如果在rs2找到了与rs1相等的记录，说明rs2找之前不等，把rs2后移一个再比较，就可能相等
					iMaxRecordCountByCustomID=0;
					rs2->Bookmark=rs2b;
					rs2->MoveNext();
				}
				else
				{//如果在rs2没找到与rs1相等的记录，说明rs2没有与rs1当前相等的BH，
					//把rs1后移一个，再比较，就可能相等
					iMaxRecordCountByCustomID=0;
					rs1->MoveNext();
				}
			}
			strCustomID2=vtos(rs1->GetCollect("CustomID"));
		}
	}
	for(i1=1;i1<=iMaxRecordCountByCustomID;i1++)
	{
		//退回到进入时位置
		rs1->MovePrevious();
		rs2->MovePrevious();
	}
	return true;
}
bool CDBSetData::iMaxRecordCount3(_RecordsetPtr &rs1, _RecordsetPtr &rs2,CString strCustomID,  int& iMaxRec1,int& iMaxRec2)
{
	_variant_t v1,v2;
	CString strCustomID1,strCustomID2,strCustomID3;
	CString strBH,strCompCode,strTJ,strTJ2,strSQL;
	float fDW1=0,fDW2=0;
	int i;
	int k1=0,k2=0;
	int k3=100000;
	iMaxRec1=0;
	iMaxRec2=0;
	if(!rs1->adoEOF) 
	{
		strCustomID1=vtos(rs1->GetCollect("CustomID"));
		if("D14"==strCustomID1)
			short ik=0;
		for(k1=1;0==strCustomID1.CompareNoCase(strCustomID);k1++)
		{
			/*
			strBH=vtos(rs1->GetCollect("BH"));
			strTJ=vtos(rs1->GetCollect("TJ"));
			fDW1=vtof(rs1->GetCollect("DW"));
			*/
			strCustomID1=vtos(rs1->GetCollect("CustomID"));
			/*strCompCode=vtos(rs2->GetCollect("a.CompCode"));
			strTJ2=vtos(rs2->GetCollect("a.size7"));
			fDW2=vtof(rs2->GetCollect("a.size1"));
			if(strBH==strCompCode)
			{
				if(strTJ==strTJ2)
				{
					iMaxRec1++;
					iMaxRec2++;
					*iRecNo1[iMaxRec1]=iMaxRec2;
					*iRecNo2[iMaxRec2]=iMaxRec1;
					rs1->MoveNext();
					rs2->MoveNext();
				}
				else
				{
					iMaxRec1++;
					iMaxRec2++;
					rs1->MoveNext();
					rs2->MoveNext();
				}
			}
			else
			{
				iMaxRec1++;
				rs1->MoveNext();
				rs2->MoveNext();
			}*/
			iMaxRec1++;
			rs1->MoveNext();
			
		}
	}
	for( i=1;i<=iMaxRec1;i++)
	{
		//退回到进入时位置
		rs1->MovePrevious();
	}

	if(!rs2->adoEOF) 
	{
		strCustomID2=vtos(rs2->GetCollect("CustomID"));
		for(k1=1;0==strCustomID2.CompareNoCase(strCustomID);k1++)
		{
			strCustomID2=vtos(rs2->GetCollect("CustomID"));
			/*strCompCode=vtos(rs2->GetCollect("a.CompCode"));
			strTJ2=vtos(rs2->GetCollect("a.size7"));
			fDW2=vtof(rs2->GetCollect("a.size1"));
			if(strBH==strCompCode)
			{
				if(strTJ==strTJ2)
				{
					iMaxRec1++;
					iMaxRec2++;
					*iRecNo1[iMaxRec1]=iMaxRec2;
					*iRecNo2[iMaxRec2]=iMaxRec1;
					rs1->MoveNext();
					rs2->MoveNext();
				}
				else
				{
					iMaxRec1++;
					iMaxRec2++;
					rs1->MoveNext();
					rs2->MoveNext();
				}
			}
			else
			{
				iMaxRec1++;
				rs1->MoveNext();
				rs2->MoveNext();
			}*/
			iMaxRec2++;
			rs2->MoveNext();
			
		}
	}
	for(i=1;i<=iMaxRec2;i++)
	{
		//退回到进入时位置
		rs2->MovePrevious();
	}

	return true;
}

//DEL bool CDBSetData::FindFieldNameFromEHS_Type4(CString strDBPHS, CString strDBEHS, short iType, CProgressCtrl &m_progress)
//DEL {
//DEL 	//这个函数逐个比较每个字段值，比FindFieldNameFromEHS_Type2函数更通用，建议用FindFieldNameFromEHS_Type4来取代FindFieldNameFromEHS_Type2
//DEL 	
//DEL 	_ConnectionPtr pPHS=NULL;
//DEL 	_ConnectionPtr pEHS=NULL;
//DEL 	pPHS.CreateInstance(__uuidof(Connection));
//DEL 	pEHS.CreateInstance(__uuidof(Connection));
//DEL 	_RecordsetPtr pRSPhs=NULL,rs1=NULL;
//DEL 	_RecordsetPtr pRSEhs=NULL,rs2=NULL,rs3=NULL;
//DEL 	pRSPhs.CreateInstance(__uuidof(Recordset));
//DEL 	pRSEhs.CreateInstance(__uuidof(Recordset));
//DEL 	rs1.CreateInstance(__uuidof(Recordset));
//DEL 	rs2.CreateInstance(__uuidof(Recordset));
//DEL 	rs3.CreateInstance(__uuidof(Recordset));
//DEL 
//DEL 	try
//DEL 	{
//DEL 		
//DEL 		SetDBPath(strDBEHS);
//DEL 		pEHS=m_pCon;
//DEL 		
//DEL 		SetDBPath(strDBPHS);//这个调用必须放在前一个SetDBPath(strDBEHS)后面，为了后面正确执行ExecuteSQL函数
//DEL 		pPHS=m_pCon;
//DEL 		
//DEL 		
//DEL 		CString strConn;
//DEL 		strConn.Format("SELECT * FROM PhsManu%s",strPhsManu[iType]);
//DEL 		pRSPhs->Open (_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 		strConn.Format("SELECT * FROM ComponentID",strPhsManu[iType]);
//DEL 		pRSEhs->Open (_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 		
//DEL 		if(pRSPhs->BOF && pRSPhs->adoEOF)
//DEL 			return FALSE;
//DEL 		else
//DEL 		{
//DEL 			//从管理表PhsManuXXXX找原始数据表名称和ID-CustomID对照表名称
//DEL 			strTableData[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNTableData[iType]) ));
//DEL 			strID[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNID[iType]) ));
//DEL 		}
//DEL 		CString strSQL,strSQL1,strCSTM;
//DEL 		CString strSQL01,strSQL02;//分别记录从EHS提取数据时需要的字段
//DEL 		bool b=false;
//DEL 		bool bFound=false;//在EHS的第1个表没找到PHS对应的字段，再找EHS的第2个表
//DEL 		short iFDCount=0,iFDCount2=0,iFDCount3=0;
//DEL 		//打开PHS原始数据表
//DEL 		strConn.Format("SELECT * FROM %s ORDER BY BH",strTableData[iType]);
//DEL 		rs1->Open(_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 		//获得求和的SQL语句
//DEL 		b=GetSumFieldSQL2(rs1,strSQL,iFDCount);
//DEL 		if(!b) 
//DEL 			return false;
//DEL 		strSQL1.Format("SELECT  %s count(*) as iCount,CustomID FROM %s  GROUP BY CustomID ORDER BY CustomID",strSQL,strTableData[iType]);
//DEL 		
//DEL 		rs1->Close();
//DEL 		rs1->Open(_variant_t(strSQL1),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 		
//DEL 		strConn.Format("SELECT * FROM %s as a LEFT JOIN %s as b ON a.CompCode=b.CompCode ORDER BY a.CompCode",strTableData2[iType],strTableData3[iType]);
//DEL 		rs2->Open(_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 		//EHS库size7字段存储温度，PHS库TJ字段存储温度
//DEL 		b=GetSumFieldSQL2(rs2,strSQL,iFDCount2);
//DEL 		if(!b) 
//DEL 			return false;
//DEL 		//EHS库strTableData2[iType]表strTableData[iType]表size7字段存储温度，PHS库TJ字段存储温度
//DEL 		strSQL1.Format("SELECT  %s count(*) as iCount,CustomID FROM %s as a LEFT JOIN %s as b ON a.CompCode=b.CompCode GROUP BY CustomID  ORDER BY CustomID",strSQL,strTableData2[iType],strTableData3[iType]);
//DEL 		
//DEL 		//TRACE1("strSQL\n%s",strSQL1);
//DEL 		rs2->Close();
//DEL 		rs2->Open(_variant_t(strSQL1),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
//DEL 
//DEL 		//EHS库size7字段存储温度，PHS库TJ字段存储温度
//DEL 		if(0<strDBPHS.Find("XBDLY"))
//DEL 		{
//DEL 			if(iPA==iType||iPART==iType||iSA==iType||iSPRING==iType||iCSPRING==iType)
//DEL 			{
//DEL 				strCSTM="LEFT(CompCode,INSTR(CompCode,'.')-1)";
//DEL 			}
//DEL 		}
//DEL 		else if(0<strDBPHS.Find("HDDLY"))
//DEL 		{
//DEL 			if(iPA==iType||iPART==iType||iSA==iType||iSPRING==iType||iCSPRING==iType)
//DEL 			{
//DEL 				strCSTM="LEFT(CompCode,INSTR(CompCode,'-')-1)";
//DEL 			}
//DEL 
//DEL 		}
//DEL 		else if(0<strDBPHS.Find("LISEGA"))
//DEL 		{
//DEL 			strCSTM="LEFT(CompCode,2)";
//DEL 		}
//DEL 		else if(0<strDBPHS.Find("ZD2010"))
//DEL 		{
//DEL 			if(iPA==iType||iPART==iType||iSPRING==iType||iCSPRING==iType)
//DEL 			{
//DEL 				strCSTM="LEFT(CompCode,3)";
//DEL 			}
//DEL 			else if(iSA==iType)
//DEL 			{
//DEL 				strCSTM="LEFT(CompCode,5)";
//DEL 			}
//DEL 		}
//DEL         
//DEL         while(!(rs1->BOF&&rs1->adoEOF))
//DEL         {
//DEL                        
//DEL             CString strCustomID,strID3;
//DEL             CString strFdName1,strFdName2,strFdName3;
//DEL 			strCustomID=vtos(rs1->GetCollect("CustomID"));
//DEL             strSQL01="";
//DEL             strSQL02="";
//DEL             strFdName1="";
//DEL             strFdName2="";
//DEL             strFdName3="";
//DEL             
//DEL             
//DEL             int ic1=0,ic2=0,ic3=0;
//DEL             ic1=vtoi(rs1->GetCollect("iCount"));
//DEL             
//DEL             if(!(rs2->BOF&&rs2->adoEOF))
//DEL             {
//DEL                 ic2=vtoi(rs2->GetCollect("iCount"));
//DEL                 for(short i=0;i<iFDCount;i++)
//DEL                 {
//DEL                     //逐个比较AutoPHS和EHS的零部件原始数据表各个数值字段和，看哪2个字段存储相同的尺寸
//DEL                     bFound=false;
//DEL                     if(ic1!=ic2) break;//记录个数不等，不比较了
//DEL                     //if(0==vtof(rs1->GetCollect((const _variant_t)(i)))) continue;
//DEL                     for(short i2=0;i2<iFDCount2;i2++)
//DEL                     {
//DEL                         if(0==vtof(rs2->GetCollect((const _variant_t)(i2)))) continue;
//DEL                         if(fabs(vtof(rs1->GetCollect((const _variant_t)(i)))/vtof(rs2->GetCollect((const _variant_t)(i2)))-1)<=1e-4)
//DEL                         {
//DEL                             //相等，存储字段名
//DEL                             bFound=true;
//DEL 							strID3=vtos(rs2->GetCollect("ID"));
//DEL                             strFdName1=(LPCTSTR)rs1->Fields->GetItem(i)->GetName();
//DEL                             strFdName2=(LPCTSTR)rs2->Fields->GetItem(i2)->GetName();
//DEL                             
//DEL                             strFdName1=strFdName1.Mid(1);
//DEL                             strFdName2=strFdName2.Mid(1);
//DEL                             strSQL01=strSQL01+strFdName1+",";
//DEL                             strSQL02=strSQL02+strTableData2[iType]+"."+strFdName2+",";
//DEL                             AddNewRecord(strCustomID,strID3,strTableData[iType],(LPCTSTR)strFdName1,strTableData2[iType],(LPCTSTR)strFdName2,pPHS);
//DEL                             break;
//DEL                         }
//DEL                     }
//DEL                 }               
//DEL             }
//DEL             //把选择数据的字段集合写到数据库
//DEL             strSQL.Format("INSERT INTO FieldNamePHS_EHS_SQL (CustomID, ID, PHSFdNames , EHSFdNames,PHSDbName) VALUES ('%s','%s','%s','%s','%s')",strCustomID,strID3 ,strSQL01,strSQL02,strDBPHS);
//DEL             ExecuteSQL(strSQL);
//DEL             if(!rs2->adoEOF) rs2->MoveNext();
//DEL             if(!rs1->adoEOF) rs1->MoveNext();
//DEL         }
//DEL         }
//DEL         catch ( _com_error& e )
//DEL         {
//DEL             AfxMessageBox( e.Description() );
//DEL             return false;
//DEL         }
//DEL         return true;
//DEL }

bool CDBSetData::CreateCrudeFromSY2sy0(CString strTableNameTag)
{
	try
	{
		//复制结构
		CString strSQL;
		strSQL.Format("SELECT * INTO crudePA%s0 FROM crudePAsy0 WHERE CustomID='' AND BH=''",strTableNameTag);
		ExecuteSQL(strSQL);

		//复制数据
		CString strFdNames="CustomID,BH,DW,Weight,Material,SIZE11,SIZE12,SIZE13,SIZE14,SIZE2,SIZE26,SIZE3,SIZE4,SIZE5,SIZE7,sizeA,sizeB,sizeB1,sizeC,sizeC1,sizeD,sizeDelta,sizeE,sizeG,sizeH,sizeH1,sizeL";
		strSQL.Format("INSERT INTO crudePA%s0 SELECT DISTINCT %s FROM crudePA%s ",strTableNameTag,strFdNames,strTableNameTag);
		ExecuteSQL(strSQL);
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
		return false;
	}

	catch (CMemoryException* e)
	{
	}
	catch (CFileException* e)
	{
	}
	catch (CException* e)
	{
	}
	return true;
}

bool CDBSetData::CreateCrudeFromEHS2PHS(CString strDBPhs, CString strDBEHS)
{
    BOOL a=FileExists(strDBPhs);
	if ( !a )
		return FALSE;
	a=FileExists(strDBEHS);
	if ( !a )
		return FALSE;
	try
 	{

		SetDBPath(strDBPhs);

		short i=2;
		if(2==i)
		{
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iPA);
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iPAFIX);
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iPART);
			a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iSPRING);
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iCSPRING);
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iSA);
			//a=CreateCrudeFromEHS2PHS_onePart(strDBPhs,strDBEHS,iBOLTSNUTS);
		}
	}
	catch(_com_error& e)
	{
		AfxMessageBox(e.Description());
		return false;
	}
	return true;
}

bool CDBSetData::CreateCrudeFromEHS2PHS_onePart(CString strDBPHS, CString strDBEHS, short iType)
{
	//这个函数逐个比较每个字段值，比FindFieldNameFromEHS_Type2函数更通用，建议用FindFieldNameFromEHS_Type3来取代FindFieldNameFromEHS_Type2
	
	
	_ConnectionPtr pPHS=NULL;
	_ConnectionPtr pEHS=NULL;
	pPHS.CreateInstance(__uuidof(Connection));
	pEHS.CreateInstance(__uuidof(Connection));
	_RecordsetPtr pRSPhs=NULL,rs1=NULL;
	_RecordsetPtr pRSEhs=NULL,rs2=NULL,rs3=NULL,rs4=NULL;
	pRSPhs.CreateInstance(__uuidof(Recordset));
	pRSEhs.CreateInstance(__uuidof(Recordset));
	rs1.CreateInstance(__uuidof(Recordset));
	rs2.CreateInstance(__uuidof(Recordset));
	rs3.CreateInstance(__uuidof(Recordset));
	rs4.CreateInstance(__uuidof(Recordset));

	try
	{
		
		SetDBPath(strDBEHS);
		pEHS=m_pCon;

		bool a=false;
		a=DeleteTableRecord("FieldNamePHS_EHS");

		SetDBPath(strDBPHS);//这个调用必须放在前一个SetDBPath(strDBEHS)后面，为了后面正确执行ExecuteSQL函数
		pPHS=m_pCon;

	
		
		CString strConn;
		strConn.Format("SELECT * FROM PhsManu%s",strPhsManu[iType]);
		pRSPhs->Open (_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
		strConn.Format("SELECT * FROM ComponentID",strPhsManu[iType]);
		pRSEhs->Open (_variant_t(strConn),(IDispatch *)pEHS,adOpenDynamic,adLockOptimistic,adCmdText);
		
		if(pRSPhs->BOF && pRSPhs->adoEOF)
			return FALSE;
		else
		{
			//从管理表PhsManuXXXX找原始数据表名称和ID-CustomID对照表名称
			strTableData[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNTableData[iType]) ));
			strID[iType]=vtos(pRSPhs->GetCollect(_variant_t(strFDNID[iType]) ));
		}
		CString strSQL,strSQL1,strCSTM;

		CString strSQL01,strSQL02;//分别记录从EHS提取数据时需要的字段
		bool b=false;
		bool bFound=false;//在EHS的第1个表没找到PHS对应的字段，再找EHS的第2个表
		short iFDCount1=0,iFDCount2=0,iFDCount3=0;
		//打开PHS原始数据表
		strConn.Format("SELECT * FROM %s ORDER BY BH",strTableData[iType]);
		rs1->Open(_variant_t(strConn),(IDispatch *)pPHS,adOpenDynamic,adLockOptimistic,adCmdText);
		
		strConn.Format("SELECT DISTINCT * FROM %s as a LEFT JOIN %s as b ON a.CompCode=b.CompCode ORDER BY a.CompCode",strTableData2[iType],strTableData3[iType]);
		rs2->Open(_variant_t(strConn),(IDispatch *)pEHS,adOpenKeyset,adLockOptimistic,adCmdText);
		//EHS库size7字段存储温度，PHS库TJ字段存储温度
		
		CString strCustomID,strCustomID1,strCustomID2,strID1;
		CString strPhsFdName,strEhsFdName,strFd1[100],strFd2[100];
		_variant_t v1,v2;
		int iMaxRecordCount1=0,iMaxRecordCount2=0;//存储一个CustomID对应的记录总行数，每个CustomID比较各个字段时不得超过这个记录数
		int iCountCmp=0,iCountEQ=0;//分别存储已经比较了多少行，已经比较行有多少行相等
		int k1=0,i=0,j=0;
		bool tag1=true,tag2=true;
		iFDCount1=rs1->GetFields()->GetCount();
		iFDCount2=rs2->GetFields()->GetCount();
		strConn.Format("SELECT *,b.CustomID FROM FieldNamePHS_EHS as a LEFT JOIN %s as b ON a.id=b.id WHERE b.customid is not NULL ORDER BY PHSTableDataName,a.CustomID,PHSFdName",strID[iType]);
		rs3->Open(_variant_t(strConn),(IDispatch *)pPHS,adOpenKeyset,adLockOptimistic,adCmdText);
	
		//删除以前复制的表
		m_pCon=pEHS;
		strConn.Format("DROP TABLE %sEHS",strTableData[iType]);
		ExecuteSQL(strConn);
		strConn.Format("DROP TABLE %s",strTableData[iType]);
		ExecuteSQL(strConn);

		m_pCon=pPHS;
		//复制表结构从PHS到EHS
		strConn.Format("SELECT * INTO %sEHS IN '%s' FROM %s WHERE BH='' AND Customid=''",strTableData[iType],strDBEHS,strTableData[iType]);
		ExecuteSQL(strConn);

		while(!rs3->adoEOF)
		{
			strCustomID=vtos(rs3->GetCollect("b.CustomID"));
			if(!rs3->adoEOF) 
			{
				strCustomID1=vtos(rs3->GetCollect("b.CustomID"));
				for(k1=1;0==strCustomID1.CompareNoCase(strCustomID);k1++)
				{
					strPhsFdName=vtos(rs3->GetCollect("PhsFdName"));
					strEhsFdName=vtos(rs3->GetCollect("EhsFdName"));
					for (i=0;i<100;i++)
					{
						if (strFd1[i]==strPhsFdName)
						{
							tag1=false;
							break;
						}
						if (strFd1[i]=="")
						{
							strFd1[i]=strPhsFdName;
							break;
						}
					}
					for (j=0;j<100;j++)
					{
						if (strFd2[j]==strEhsFdName)
						{
							tag2=false;
							break;
						}
						if (strFd2[j]=="")
						{
							strFd2[j]=strEhsFdName;
							break;
						}
					}
					if(tag1&&tag2&&!(strPhsFdName=="" ||strEhsFdName==""))
					{
						strSQL01=strSQL01+strPhsFdName+",";
						if(!(strEhsFdName=="CustomID"||strEhsFdName=="ID")&&strEhsFdName.Find(".",0)<0)
							strEhsFdName="a."+strEhsFdName;
						strSQL02=strSQL02+strEhsFdName+",";
					}
					tag1=true;
					tag2=true;
					rs3->MoveNext();					
					if(rs3->adoEOF)
						break;
					strCustomID1=vtos(rs3->GetCollect("b.CustomID"));
				}
				strSQL01=strSQL01.Left(strSQL01.GetLength()-1);//删除末尾逗号
				strSQL02=strSQL02.Left(strSQL02.GetLength()-1);
				strSQL.Format("INSERT INTO %sEHS (%s) SELECT DISTINCT %s FROM %s as a LEFT JOIN %s as b ON a.compcode=b.compcode WHERE CustomID='%s' ",strTableData[iType],strSQL01,strSQL02,strTableData2[iType],strTableData3[iType],strCustomID);
				pEHS->Execute(_bstr_t(strSQL),NULL,adCmdText);
				for (i=0;i<100;i++)
				{
					strFd1[i]="";
					strFd2[i]="";
				}
				strSQL01="";
				strSQL02="";
			}
		}
		//把整理好的数据表从EHS复制到PHS库
		strConn.Format("SELECT * INTO %s FROM %sEHS IN '%s' ORDER BY BH",strTableData[iType],strTableData[iType],strDBEHS);
		ExecuteSQL(strConn);
	}
	catch ( _com_error& e )
	{
		AfxMessageBox( e.Description() );
		return false;
	}
	return true;
}

bool CDBSetData::DBConvertEHS2PHS_SA(CString strDBPHS, CString strDBEHS)
{
    BOOL a=FileExists(strDBPHS);
	if ( !a )
		return FALSE;
	a=FileExists(strDBEHS);
	if ( !a )
		return FALSE;
	
	_RecordsetPtr pRsA=NULL;//表:crudeSA2010
	_RecordsetPtr pRsB=NULL;//表:SASelSubData
	CString strSQL=_T(""),BH,ID;
	int iRsCount=0;
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));
	_ConnectionPtr pPHS=NULL,pEHS=NULL;
	pPHS.CreateInstance(__uuidof(Connection));
	pEHS.CreateInstance(__uuidof(Connection));
	
	try
	{
		
		SetDBPath(strDBEHS);
		pEHS=m_pCon;
		
		SetDBPath(strDBPHS);//这个调用必须放在前一个SetDBPath(strDBEHS)后面，为了后面正确执行ExecuteSQL函数
		pPHS=m_pCon;		

		strSQL="select * from crudeSA2010 order by CustomID,BH";
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)pPHS,adOpenDynamic,\
					adLockOptimistic,adCmdText);
		strSQL="select * from SASelSubData";
		pRsB->Open(_bstr_t(strSQL),(IDispatch *)pEHS,adOpenDynamic,\
					adLockOptimistic,adCmdText);
		iRsCount=pRsB->GetRecordCount();
		if(iRsCount>0)
		{
			//首先把根部子零件清空
			for(int i2=1;i2<=12;i2++)
			{
				strSQL.Format("UPDATE crudeSA2010 SET P%d=NULL, PL%d=NULL, PNUM%d=NULL",i2,i2,i2);
				pPHS->Execute(_bstr_t(strSQL),NULL,adCmdText);
			}
			int i1=0;//指示进度
			BH=vtos(pRsB->GetCollect("ID"));
			
			int iC=1;//标记根部零件号
			while (!(pRsB->adoEOF) )
			{//SASelSubData的多个记录存储了crudeSA2010表中的一个根部记录的多个零件Pi
				i1++;
				ID=vtos(pRsB->GetCollect("ID"));
				if(0!=ID.CompareNoCase(BH))
				{
					iC=1;  //ID<>BH表明是另外一个根部的零件记录了
					BH=ID;//记录这个ID，用于下次判断
				}
				CString strFieldP,strFieldPL,strFieldPNUM;
				strFieldP.Format("P%d",iC);
				strFieldPL.Format("PL%d",iC);
				strFieldPNUM.Format("PNUM%d",iC);
				CString strTmp,strTmp1,strTmp2;
				strTmp=vtos(pRsB->GetCollect("size1"));
				
				short i=0;
				strTmp.MakeUpper();
				i=strTmp.Find("G21L");
				if(-1!=i)
					strTmp=strTmp.Left(i)+"GDW1"+strTmp.Mid(i+4);//把长度表达式中EHS定义的G21L替换成AutoPHS定义的GDW1
				
				i=strTmp.Find("CSHEIGH");
				if(-1!=i)
					strTmp=strTmp.Left(i)+"CHheight"+strTmp.Mid(i+7);//把长度表达式中EHS定义的Csheight替换成AutoPHS定义的CHheight

				strSQL.Format("UPDATE crudeSA2010 SET %s='%s', %s='%s', %s=%d WHERE BH='%s'",strFieldP,vtos(pRsB->GetCollect("SubCompCode")),\
					strFieldPL,strTmp,strFieldPNUM,vtoi(pRsB->GetCollect("size2")),ID);
				pPHS->Execute(_bstr_t(strSQL),NULL,adCmdText);
				/* 下面三行代码无法更新BH重复的记录
				pRsA->PutCollect(_T(_variant_t (strFieldP)),_variant_t(pRsB->GetCollect("SubCompCode")));
				pRsA->PutCollect(_T(_variant_t (strFieldPL)),_variant_t(strTmp));
				pRsA->PutCollect(_T(_variant_t (strFieldPNUM)),_variant_t(pRsB->GetCollect("size2")));
				*/
				iC++;
				pRsB->MoveNext();
			}
		}//表:crudeSA2010不为空(end)
		AfxMessageBox("转换完成了。");
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return false;
	}

	return true;
}


short CDBSetData::AddCHNSTATToFAOSTAT(CString strCHNTable,CString strFAOTable,int iYear,short nFlag)
{
	//ligb on 2019.05.29
	//把中国统计局农业分省统计数据库格式转换为联合国粮农组织统计数据库格式
	//中国农业数据库：播种面积表ALL1，单位千公顷,1949-2017;粮食产量表ALL2,1949-2017，单位万吨
	//联合国粮农组织数据库：播种面积/产量/单产都存储在FAOSTAT_data_3-22-2019表。
	//我们首先存储产量到FAOSTAT_data_3-22-2019china_Production表，存储播种面积到FAOSTAT_data_3-22-2019china，
	//单产=产量/播种面积，最后合并到FAOSTAT_data_3-22-2019表。
	//由于使用ACCESS SQL语句很容易崩溃，不得不一条条添加记录。
	//输入：strCHNTable,中国统计表;strFAOTable,联合国粮农组织统计表;iYear，添加哪一年数据
	_RecordsetPtr pRsA=NULL;//表:strFAOTable
	_RecordsetPtr pRsB=NULL;//表:strCHNTable
	pRsA.CreateInstance(__uuidof(Recordset));
	pRsB.CreateInstance(__uuidof(Recordset));

	CString strSQL=_T("");
	CString strFD;
	strFD.Format(_T("%d"),iYear);
	

	_variant_t key,vt;

	try
	{
		//如果MaterialCodeFind表不存在code_find字段值=strTable的记录，则说明strTable没有加入到规范中，取AutoPSA_ID字段的最大值为strTable规范的AutoPSA_ID值。否则，取CODE_ID字段值为规范CODE_ID值。
		strSQL.Format("select item_cn,[Area Code],Area_cn,[%d] as strYear from %s",iYear,strCHNTable);
		pRsB->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
			adLockOptimistic,adCmdText);

		strSQL.Format("select item_cn,[Area Code],Area_cn,Year,Value,[Element Code],[Element],Unit from [%s] order by ID",strFAOTable);
		pRsA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
			adLockOptimistic,adCmdText);
			while( !pRsB->adoEOF)
			{
				pRsA->AddNew();
				try
				{
					key=pRsB->GetCollect(_T("item_cn"));
					pRsA->PutCollect(_T("item_cn"),key);
					key=pRsB->GetCollect(_T("Area Code"));
					pRsA->PutCollect(_T("Area Code"),key);
					key=pRsB->GetCollect(_T("area_cn"));
					pRsA->PutCollect(_T("area_cn"),key);
					pRsA->PutCollect(_T("Year"),_variant_t(iYear));
					
					key=pRsB->GetCollect(_T("strYear"));
					
					if (1==nFlag)
					{//播种面积 Element Code	5312
						//pRsA->PutCollect(_T("Value"),key);//kha->ha
						if (VT_NULL==key.vt)
						{
						}
						else
							pRsA->PutCollect(_T("Value"),_variant_t(double(key)*1000));//kha->ha
						pRsA->PutCollect(_T("Element"),_variant_t(_T("Area harvested")));
						pRsA->PutCollect(_T("Element Code"),_variant_t(5312));
						pRsA->PutCollect(_T("Unit"),_variant_t(_T("ha")));
						pRsA->Update();
						//加单产记录 Element Code	5419
						pRsA->AddNew();
						key=pRsB->GetCollect(_T("item_cn"));
						pRsA->PutCollect(_T("item_cn"),key);
						key=pRsB->GetCollect(_T("Area Code"));
						pRsA->PutCollect(_T("Area Code"),key);
						key=pRsB->GetCollect(_T("area_cn"));
						pRsA->PutCollect(_T("area_cn"),key);
						pRsA->PutCollect(_T("Year"),_variant_t(iYear));

						pRsA->PutCollect(_T("Element"),_variant_t(_T("Yield")));
						pRsA->PutCollect(_T("Element Code"),_variant_t(5419));
						pRsA->PutCollect(_T("Unit"),_variant_t(_T("hg/ha")));
					}
					else if (2==nFlag)
					{//产量 Element Code	5510	Production
						//pRsA->PutCollect(_T("Value"),key);
						if (VT_NULL==key.vt)
						{
						}
						else
							pRsA->PutCollect(_T("Value"),_variant_t(double(key)*10000));//10ktonnes->tonnes
						pRsA->PutCollect(_T("Element"),_variant_t(_T("Production")));
						pRsA->PutCollect(_T("Element Code"),_variant_t(5510));
						pRsA->PutCollect(_T("Unit"),_variant_t(_T("tonnes")));
					}
				}
				catch(_com_error e)
				{

				}

				pRsA->Update();
				pRsB->MoveNext();
			}
			pRsB->Close();
			pRsA->Close();
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
		return -1;
	}
	return 0;
}


bool CDBSetData::AddCHNToFAO(CProgressCtrl &m_progress1,int nMode)
{
	//运行本函数：启动程序后，弹出对话框选择目录，选择FAOSTAT.mdb所在目录，再点击菜单"原始数据-转换中国统计局数据库到联合国粮农组织统计数据库FAOSTAT"
	int j=0;
	m_progress1.ShowWindow(SW_SHOW);
	m_progress1.SetRange(0, 2017-1949);
	m_progress1.SetPos(0);
	CString strSQL;

	BOOL bResult =FALSE;
	if(m_strDBDir.Right(1)!="\\") m_strDBDir+="\\";

	m_strDBPathName.Format(_T("%sFAOSTAT.mdb"),m_strDBDir);
	ConnectionDatamdb();

	CString strFAOTable,strCHNTable;
	strFAOTable.Format(_T("FAOSTAT_data_3-22-2019china"));
	strCHNTable.Format(_T("ALL1"));
	DeleteTableRecord(strFAOTable);
	int i;
	for (i=1949;i<=2017;i++)
	{
		AddCHNSTATToFAOSTAT(strCHNTable,strFAOTable,i,1);
		m_progress1.SetPos(++j);

	}
	//strSQL.Format(_T("UPDATE %s SET Value=Value*1000 WHERE not ISNULL(item)"),strFAOTable);
	//ExecuteSQL(strSQL);//播种面积单位千公顷转换为公顷
	

	if (1==nMode)
	{//模式1：产量进入单独产量表
		strFAOTable.Format(_T("FAOSTAT_data_3-22-2019china_Production"));
		DeleteTableRecord(strFAOTable);
	}
	else if (2==nMode)
	{//模式2：产量进入面积表，合并到一起

	}
	strCHNTable.Format(_T("ALL2"));


	m_progress1.SetPos(0);
	j=0;
	m_progress1.ShowWindow(SW_SHOW);

	for (i=1949;i<=2017;i++)
	{
		AddCHNSTATToFAOSTAT(strCHNTable,strFAOTable,i,2);
		m_progress1.SetPos(++j);
	}
	//strSQL.Format(_T("UPDATE %s SET Value=Value*10000 WHERE not ISNULL(item)"),strFAOTable);
	//ExecuteSQL(strSQL);//产量单位万吨转换为吨

	AfxMessageBox(strCHNTable+"转换完成!");
	m_progress1.ShowWindow(SW_HIDE);

	return TRUE;

}