// CSPRDalian.cpp : implementation file
//

#include "stdafx.h"
#include "dbconvert.h"
#include "CSPRDalian.h"

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

/////////////////////////////////////////////////////////////////////////////
// CCSPRDalian dialog


CCSPRDalian::CCSPRDalian(CWnd* pParent /*=NULL*/)
	: CDialog(CCSPRDalian::IDD, pParent)
{
	//{{AFX_DATA_INIT(CCSPRDalian)
	m_pathName = _T("");
	//}}AFX_DATA_INIT
}


void CCSPRDalian::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CCSPRDalian)
	DDX_Text(pDX, IDC_EDIT1, m_pathName);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CCSPRDalian, CDialog)
	//{{AFX_MSG_MAP(CCSPRDalian)
	ON_BN_CLICKED(IDC_H1_Button, OnH1Button)
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CCSPRDalian message handlers

bool CCSPRDalian::ConnectionDatamdb(_ConnectionPtr &pCon, CString strMdbName)
{
	
	pCon.CreateInstance(__uuidof(Connection));
	CString conPRJDBSql = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source='"+m_pathName+"';PerSist Security Info=False";
	try
	{
	   pCon->Open(_bstr_t(conPRJDBSql),"","",-1);
	}
	catch(_com_error e)
	{
		return FALSE;
	}
	return TRUE;
}


float vtof(_variant_t &v)
{
	float ret=0;
	CString tmps;
	switch(v.vt)
	{
	case VT_R4:
		ret = V_R4(&v);
		break;
	case VT_R8:
		ret=(float)V_R8(&v);
		break;
	case VT_I2:
		ret= (float)V_I2(&v);
		break;
	case VT_I4:
		ret= (float)V_I4(&v);
		break;
	case VT_BSTR:
		tmps=(char*)_bstr_t(&v);
		ret=atof((LPCSTR)tmps);
		break;

	}
	return ret;
}


CString vtos(_variant_t& v)
{
	CString ret;
	switch(v.vt)
	{
	case VT_NULL:
	case VT_EMPTY:
		ret="";
		break;
	case VT_BSTR:
		ret=V_BSTR(&v);
		break;
	case VT_R4:
		ret.Format("%g", (double)V_R4(&v));
		break;
	case VT_R8:
		ret.Format("%g",V_R8(&v));
		break;
	case VT_I2:
		ret.Format("%d",(int)V_I2(&v));
		break;
	case VT_I4:
		ret.Format("%d",(int)V_I4(&v));
		break;
	case VT_BOOL:
		ret=(V_BOOL(&v) ? "True" : "False");
		break;
	}
	ret.TrimLeft();ret.TrimRight();
	return ret;
}


int  vtoi(_variant_t & v)
{
	CString tmps;
	int ret=0;
	switch(v.vt)
	{
	case VT_I2:
		ret= (int)V_I2(&v);
		break;
	case VT_I4:
		ret= V_I4(&v);
		break;
	case VT_R4:
		ret= (int)V_R4(&v);
		break;
	case VT_R8:
		ret =(int)V_R8(&v);
		break;
	case VT_BSTR:
		tmps=(char*)_bstr_t(&v);
		ret=atoi((LPCSTR)tmps);
		break;
	}
	return ret;
}
BOOL CCSPRDalian::OnInitDialog()
{
	CDialog::OnInitDialog();
	return true;
}

void CCSPRDalian::OnOK() 
{
	UpdateData(true);
	::CoInitialize(NULL);
	CString strTmp="";
	_RecordsetPtr ARs;//HDcrudeDL
	_RecordsetPtr BRs;//crudeTurnBuckleHDdl
	_RecordsetPtr ARs1;//pfg
	ARs.CreateInstance(__uuidof(Recordset));
	ARs1.CreateInstance(__uuidof(Recordset));
	BRs.CreateInstance(__uuidof(Recordset));
	CString strSQL=_T("");
	long Adh=0;
	CString ACustomID=_T("");
	float AfBmin=0.0;
	float AfBmax=0.0;
	float wAfBmin=0.0;
	float wAfBmax=0.0;
	float p=0.0;
	float App=0.0;
	CString strAdh=_T("");
	
	long BfBmin=0;
	long BfBmax=0;
	float BSize2=0.0;

	float e=0.0;
	float fNewE=0.0;
	float sizeh=0.0;

   if(1)
   {//打开数据库成功(start)
	   //删除临时表：HDcrudeDL1 (start)
	   try
	   {
			strSQL="drop table HDcrudeDL1";
			m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	   }
	   catch(_com_error e)
	   {
	   }
	   //删除临时表：HDcrudeDL1 (end)
		try
		{
			strSQL="select * into HDcrudeDL1 from HDcrudeDL";
			m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
			strSQL="delete * from HDcrudeDL";
			m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
			strSQL="select * from HDcrudeDL";
			ARs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
			strSQL="select * from HDcrudeDL1";
			ARs1->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
						adLockOptimistic,adCmdText);
			strSQL="select * from crudeTurnBuckleHDdl order by CustomID,minDH,fBmin ";
			BRs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
						adLockOptimistic,adCmdText);
			int iRsCount=ARs1->GetRecordCount();
			if(iRsCount>0)
			{
				ARs1->MoveFirst();
				while(!ARs1->adoEOF)
				{//对表HDcrudeDL中的每一条记录进行处理(start)
					Adh=vtoi(ARs1->GetCollect(_T("dh")));
					ACustomID=vtos(ARs1->GetCollect(_T("CustomID")));
					AfBmin=vtof(ARs1->GetCollect(_T("fBmin")));
					AfBmax=vtof(ARs1->GetCollect(_T("fBmax")));
					strAdh.Format("%d",Adh);

					strSQL="minDH<="+strAdh+" and maxDH>="+strAdh+" and customid='"+ACustomID+"'";
					BRs->Filter="";
					BRs->Filter=_variant_t(strSQL);
					iRsCount=BRs->GetRecordCount();
					if(iRsCount>0)
					{
						e=vtof(ARs1->GetCollect(_T("e")));
						fNewE=e;
						App=vtof(ARs1->GetCollect(_T("pp")));
						for(BRs->MoveFirst();!BRs->adoEOF;BRs->MoveNext())
						{//对表:crudeTurnBuckleHDdl循环(start)
							BfBmin=vtof(BRs->GetCollect(_T("fBmin")));
							BfBmax=vtof(BRs->GetCollect(_T("fBmax")));
							if(AfBmin>=BfBmin && AfBmax<=BfBmax)
							{//在指定的区间内(start) 第1种情况
								ARs->AddNew();
								wAfBmin=AfBmin;
								wAfBmax=AfBmax;
							}//在指定的区间内(end)
							else
							{//不在指定的区间内(start)
								if(AfBmin>=BfBmax)
								{//第2种情况
									continue;
								}
								if(AfBmax<=BfBmin)
								{//第3种情况
									break;
								}
								if(AfBmin<BfBmin && AfBmax<=BfBmax && AfBmax>BfBmin)
								{//第5种情况
									ARs->AddNew();
									wAfBmin=BfBmin;
									wAfBmax=AfBmax;
									if(ACustomID=="ZHA"||ACustomID=="ZHB")
									{
										fNewE=e+(BfBmin-AfBmin)/2;
									}
									else
									{
										fNewE=e-(BfBmin-AfBmin)/2;
									}
								}
								if(AfBmin>=BfBmin && AfBmax>BfBmax && AfBmin<BfBmax )
								{//第6种情况
									ARs->AddNew();
									wAfBmax=BfBmax;
									wAfBmin=AfBmin;
								}
								if(AfBmin<BfBmin && AfBmax>BfBmax)
								{//第4种情况
									ARs->AddNew();
									wAfBmax=BfBmax;
									wAfBmin=BfBmin;
									if(ACustomID=="ZHA"||ACustomID=="ZHB")
									{
										fNewE=e+(BfBmin-AfBmin)/2;
									}
									else
									{
										fNewE=e-(BfBmin-AfBmin)/2;
									}
								}
							}//不在指定的区间内(end)
							BSize2=vtof(BRs->GetCollect(_T("size2")));
							p=App+BSize2*1.5;
							if(App!=0)
							{
								ARs->PutCollect(_T("p"),_variant_t(p));
							}
							ARs->PutCollect(_T("fBmin"),_variant_t(wAfBmin));
							ARs->PutCollect(_T("fBmax"),_variant_t(wAfBmax));
							ARs->PutCollect(_T("dh"),_variant_t(Adh));
							ARs->PutCollect(_T("customid"),_variant_t(ACustomID));

							ARs->PutCollect(_T("ID"),ARs1->GetCollect("ID"));
							ARs->PutCollect(_T("Group"),ARs1->GetCollect("Group"));
							ARs->PutCollect(_T("INSTALLtype"),ARs1->GetCollect("INSTALLtype"));
							ARs->PutCollect(_T("BH"),ARs1->GetCollect("BH"));
							ARs->PutCollect(_T("A"),ARs1->GetCollect("A"));
							ARs->PutCollect(_T("C"),ARs1->GetCollect("C"));
							ARs->PutCollect(_T("D"),ARs1->GetCollect("D"));
							ARs->PutCollect(_T("F"),ARs1->GetCollect("F"));
							ARs->PutCollect(_T("G"),ARs1->GetCollect("G"));
							ARs->PutCollect(_T("H"),ARs1->GetCollect("H"));
							ARs->PutCollect(_T("K"),ARs1->GetCollect("K"));
							ARs->PutCollect(_T("FJmin"),_variant_t(BSize2));//拉杆直径
							ARs->PutCollect(_T("N"),ARs1->GetCollect("N"));
							ARs->PutCollect(_T("pp"),ARs1->GetCollect("pp"));
							ARs->PutCollect(_T("s"),ARs1->GetCollect("s"));
							ARs->PutCollect(_T("t"),ARs1->GetCollect("t"));
							ARs->PutCollect(_T("s"),ARs1->GetCollect("s"));
							ARs->PutCollect(_T("u"),ARs1->GetCollect("u"));
							ARs->PutCollect(_T("w"),ARs1->GetCollect("w"));
							ARs->PutCollect(_T("x"),ARs1->GetCollect("x"));
							ARs->PutCollect(_T("y"),ARs1->GetCollect("y"));
							ARs->PutCollect(_T("yy"),ARs1->GetCollect("yy"));
							ARs->PutCollect(_T("ay"),ARs1->GetCollect("ay"));
							ARs->PutCollect(_T("by"),ARs1->GetCollect("by"));
							ARs->PutCollect(_T("e"),_variant_t(fNewE));
							ARs->PutCollect(_T("sizeH"),_variant_t(fNewE));
							if(ACustomID=="LHA"||ACustomID=="LHB"||ACustomID=="PHA"||ACustomID=="PHB")
							{
								sizeh=App+fNewE;
								ARs->PutCollect(_T("sizeH"),_variant_t(sizeh));//只有LHA LHB PHA PHB sizeH=E+pp
							}
	//						else
	//						{
	//							ARs->PutCollect(_T("sizeH"),ARs1->GetCollect(_variant_t("e")));//只有LHA LHB PHA PHB sizeH=E+pp
	//						}
							ARs->PutCollect(_T("sizeC"),ARs1->GetCollect("sizeC"));
							ARs->PutCollect(_T("FJmax"),ARs1->GetCollect("FJmax"));
							ARs->PutCollect(_T("GL"),ARs1->GetCollect("GL"));
							ARs->PutCollect(_T("Weight"),ARs1->GetCollect("Weight"));
							ARs->Update();
						}//对表:crudeTurnBuckleHDdl循环(end)
					}
					ARs1->MoveNext();
				}//对表HDcrudeDL中的每一条记录进行处理(end)
			}
		}
		catch(_com_error e)
		{
			AfxMessageBox(e.Description());
		}
   }//打开数据库成功(end)
   else
   {//打开数据库失败(start)
 		AfxMessageBox("连接数据库失败!");
		return;
   }//打开数据库失败(end)
	UpdateData(false);
   try
   {
		if(ARs->State==adStateOpen)
		{
			ARs->Close();
		}
		if(ARs1->State==adStateOpen)
		{
			ARs1->Close();
		}
		if(BRs->State==adStateOpen)
		{
			BRs->Close();
		}
		strSQL="drop table HDcrudeDL1";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
   }
   catch(_com_error e)
   {
   }
	CDialog::OnOK();
}


//根据表:CSPR_DL_FieldNameSizeVar、HDDIM_Primary_DL、HDDIM_H1_DL
//生成表:HDcrudeDL
void CCSPRDalian::OnH1Button() 
{
	UpdateData(true);
	::CoInitialize(NULL);
	_RecordsetPtr HDcrudeDLRs=NULL;
	_RecordsetPtr H1Rs=NULL;
	_RecordsetPtr HPDLRs=NULL;
	_RecordsetPtr CSPRRs=NULL;
	CString CustomID=_T("");
	CString strBH=_T("");
	CString strDH=_T("");
	long dh=0;
	HDcrudeDLRs.CreateInstance(__uuidof(Recordset));
	H1Rs.CreateInstance(__uuidof(Recordset));
	HPDLRs.CreateInstance(__uuidof(Recordset));
	CSPRRs.CreateInstance(__uuidof(Recordset));
	CString strSQL="";
	try
	{
		ConnectionDatamdb(m_pCon,"");
		strSQL="delete * from HDcrudeDL";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL="select * from HDcrudeDL";
		HDcrudeDLRs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL="select * from HDDIM_H1_DL order by customID,dh,fBmin";
		H1Rs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL="select * from HDDIM_Primary_DL";
		HPDLRs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL="select * from cspr_dl_fieldnamesizevar";
		CSPRRs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		for(H1Rs->MoveFirst();!H1Rs->adoEOF;H1Rs->MoveNext())
		{//处理表(HDDIM_H1_DL)中的每一条记录(start)
			for(int i=0;i<2;i++)
			{
				HDcrudeDLRs->AddNew();
				HDcrudeDLRs->PutCollect(_T("dh"),H1Rs->GetCollect(_T("dh")));
				HDcrudeDLRs->PutCollect(_T("fBmin"),H1Rs->GetCollect("fBmin"));
				HDcrudeDLRs->PutCollect(_T("fBmax"),H1Rs->GetCollect("fBMax"));
				HDcrudeDLRs->PutCollect(_T("e"),H1Rs->GetCollect("e"));
				CustomID=vtos(H1Rs->GetCollect(_T("customID")));
				if(i==1)
				{
					if(CustomID=="PHA")
					{
						CustomID="PHB";
					}
					else if(CustomID=="PHC")
					{
						CustomID="PHE";
					}
					else if(CustomID=="LHA")
					{
						CustomID="LHB";
					}
					else if(CustomID=="LHC")
					{
						CustomID="LHE";
					}
				}
				else if(CustomID!="LHC"&&CustomID!="LHA"&&CustomID!="PHC"&&CustomID!="PHA")
				{
					i++;
				}
				HDcrudeDLRs->PutCollect(_T("ID"),_variant_t(CustomID));
				HDcrudeDLRs->PutCollect(_T("customID"),_variant_t(CustomID));
				dh=vtoi(H1Rs->GetCollect("dh"));
				strBH.Format("%d",dh);
				strDH=strBH;
				if(dh<10)
				{
					strBH="0"+strBH;	
				}
				strBH=CustomID+strBH;
				HDcrudeDLRs->PutCollect(_T("bh"),_variant_t(strBH));
				HDcrudeDLRs->PutCollect(_T("Installtype"),_variant_t(CustomID.Right(1)));
				//写支吊架主要外形尺寸(start)
				CSPRRs->Filter="";
				CSPRRs->Filter=_variant_t(_T("CustomID='"+CustomID+"'"));
				int ii=CSPRRs->GetRecordCount();
				if(ii<1)
				{
					CSPRRs->Filter="";
					CSPRRs->Filter=_variant_t(_T("CustomID1='"+CustomID+"'"));
					ii=CSPRRs->GetRecordCount();
				}
				if(ii>0)
				{
					CSPRRs->MoveFirst();
					CustomID=vtos(CSPRRs->GetCollect(_T("ID")));
					HPDLRs->Filter="";
					HPDLRs->Filter=_variant_t(_T("CustomID='"+CustomID+"' and minDH<="+strDH+" and maxDH>="+strDH+""));
					while(!CSPRRs->adoEOF)
					{
						HDcrudeDLRs->PutCollect(CSPRRs->GetCollect("FDName"),HPDLRs->GetCollect(CSPRRs->GetCollect("SourceFDName")));
						CSPRRs->MoveNext();
					}
				}
				//写支吊架主要外形尺寸(end)
				HDcrudeDLRs->Update();
			}
		}//处理表(HDDIM_H1_DL)中的每一条记录(end)
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	UpdateData(false);
	AfxMessageBox("预处理成功!");
}
//生成表:CrudeTurnBuckleHDdl
void CCSPRDalian::OnButton2() 
{
	_RecordsetPtr HDcrudeDLRs=NULL;
	::CoInitialize(NULL);
	CString strSQL;
	CString strCustomID="";
	float Size2=0.0;
	_variant_t key;
	HDcrudeDLRs.CreateInstance(__uuidof(Recordset));
	_RecordsetPtr pRsTD=NULL;
	pRsTD.CreateInstance(__uuidof(Recordset));
	_RecordsetPtr pRsLHA=NULL;
	pRsLHA.CreateInstance(__uuidof(Recordset));
	strSQL="select * from CrudeTurnBuckleHDdl_LHA";
	//修改size7(start)
	try
	{
		pRsLHA->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		strSQL="select * from crudeTurnbuckleTD";
		pRsTD->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		for(pRsLHA->MoveFirst();!pRsLHA->adoEOF;pRsLHA->MoveNext())
		{
			key=pRsLHA->GetCollect("size2");
			if(key.vt==VT_NULL||key.vt==VT_EMPTY)
			{
				//pRsLHA->PutCollect(_T("size7"),_variant_t(long(0)));
			}
			else
			{
				Size2=vtof(key);
				strSQL.Format("%f",Size2);
				strSQL="size2="+strSQL;
				pRsTD->Filter="";
				pRsTD->Filter=_variant_t(strSQL);
				if(pRsTD->GetRecordCount()>0)
				{
					pRsLHA->PutCollect(_T("size7"),pRsTD->GetCollect("size7"));
				}
			}
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	//修改size7(end)
	try
	{
		strSQL="UPDATE CrudeTurnBuckleHDdl_LHA SET size3 = sizeH+2*size4";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL="UPDATE CrudeTurnBuckleHDdl_LHA SET size5 = (size3-size7)/2";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
		strSQL="delete * from CrudeTurnBuckleHDdl";
		m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	try
	{
		strSQL="select distinct CustomID from HDcrudeDL order By CustomID";
		HDcrudeDLRs->Open(_bstr_t(strSQL),(IDispatch *)m_pCon,adOpenStatic,\
					adLockOptimistic,adCmdText);
		int ii=HDcrudeDLRs->GetRecordCount();
		if(ii>0)
		{
			for(HDcrudeDLRs->MoveFirst();!HDcrudeDLRs->adoEOF;HDcrudeDLRs->MoveNext())
			{
				strCustomID=vtos(HDcrudeDLRs->GetCollect("customid"));
				strSQL="update CrudeTurnBuckleHDdl_LHA set customID='"+strCustomID+"' ";
				m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
				strSQL="insert into CrudeTurnBuckleHDdl(CustomID,minDH,maxDH,fBmin,fBmax,size3,size4,sizeH,size2,size5,size7)";
				strSQL+=" select CustomID,minDH,maxDH,fBmin,fBmax,size3,size4,sizeH,size2,size5,size7 from CrudeTurnBuckleHDdl_LHA";
				m_pCon->Execute(_bstr_t(strSQL),NULL,adCmdText);
			}
		}
	
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}
	AfxMessageBox("生成表(CrudeTurnBuckleHDdl)成功!");
}