CHINT_test_autoupdateMaterial/CHINT_test/update.cpp

#include "util.h"
#include "test.h"
#include "SQLUtil.cpp"
#include "SQLAPI.h"
#include "updateMaterial.h"



using namespace libxl;
using namespace std;

void getClassNo(set<string> &ids,map<string,string> &maps,vector<string> &vecs)
{
	//db db;
	
	SAConnection conn;
	SACommand command;
	int pref_cnt;
	char **pref_vals;
	PREF_ask_char_values("database_tc", &pref_cnt, &pref_vals);
	try{
		stringstream ss;
		ss << pref_vals[0] << ':' << pref_vals[1] << '/' << pref_vals[2];		
		conn.Connect(ss.str().c_str(), pref_vals[3], pref_vals[4],
			SA_Oracle_Client);

		//ss << "10.128.155.88" << ':' << "1521" << '/' << "testdb";
		//conn.Connect(ss.str().c_str(), "infodba", "infodba",
		//	SA_Oracle_Client);
		command.setConnection(&conn);
		command.setCommandText("select nvl(\"PmpcCode\",'') from chint_material where \"GoodsCode\"=:1");
		for(auto it=ids.begin();it!=ids.end();it++)
		{
			command<<(*it).c_str();
			command.Execute();
			if(command.FetchNext())
			{
				string PmpcCode = command.Field(1).asString().GetMultiByteChars();
				if(PmpcCode.empty())
				{
					vecs.push_back(*it);
				}else
				{
					maps[*it] = PmpcCode;
				}
			}

		}		
	conn.Disconnect();
	}catch(SAException e)
	{
		printf("异常\n");
		e.ErrMessage();
		LINFO<<e.ErrMessage().GetMultiByteChars();
		printf(e.ErrMessage());
	}



}
//从SQLServer数据库中获取物料信息
void getClassNo2(set<string> &ids,map<string,string> &maps,vector<string> &vecs)
{
	//db db;
	SAConnection conn;
	SACommand command;
	int pref_cnt;
	char **pref_vals;
	PREF_ask_char_values("database_tc", &pref_cnt, &pref_vals);
	try{
		stringstream ss;
		ss << "10.128.20.35:1433@BDP2020";		
		conn.Connect(ss.str().c_str(), "PLMUser", "PLMUser",
			SA_SQLServer_Client);

		//ss << "10.128.155.88" << ':' << "1521" << '/' << "testdb";
		//conn.Connect(ss.str().c_str(), "infodba", "infodba",
		//	SA_Oracle_Client);
		command.setConnection(&conn);
		//command.setCommandText("select nvl(\"PmpcCode\",'') from chint_material where \"GoodsCode\"=:1");


		command.setCommandText("select \"GoodsPmpcCode\" from \"CcemVW_Cgd\" where \"GoodsCode\"=:1 ");
		for(auto it=ids.begin();it!=ids.end();it++)
		{
			command<<(*it).c_str();
			command.Execute();
			if(command.FetchNext())
			{
				string PmpcCode = command.Field(1).asString().GetMultiByteChars();
				if(PmpcCode.empty())
				{
					vecs.push_back(*it);
				}else
				{
					maps[*it] = PmpcCode;
				}
			}

		}		
	conn.Disconnect();
	}catch(SAException e)
	{
		printf("异常\n");
		e.ErrMessage();
		LINFO<<e.ErrMessage().GetMultiByteChars();
		printf("%s\r\n",e.ErrMessage());
	}



}

//从主数据获取数据
vector<string> searchSQLServer(vector<string> vecs,map<string,MaterialBean> &materials)
{
	SAConnection conn;
	SACommand command;
	stringstream ss;
	vector<string> vec_not_founds;
	try{
		ss << "10.128.20.35@BDP2020";	
		conn.Connect(ss.str().c_str(), "PLMUser", "PLMUser",
		SA_SQLServer_Client);
		command.setConnection(&conn);
		command.setCommandText("select \"GoodsPmpcCode\",\"GoodsCode\",\"GoodsName\",\"GoodsUnitCode\",\"GoodsFullInfo\",\"GoodsBpNo\",\"GoodsTeRe\"   from \"CcemVW_Cgd\" where \"GoodsCode\"=:1 ");
		//
		for(auto it=vecs.begin();it!=vecs.end();it++)
		{
			command<<(*it).c_str();
			command.Execute();
			if(command.FetchNext())
			{

				MaterialBean bean;
				bean.pmpcCode = command.Field(1).asString().GetMultiByteChars();
				bean.goodsCode = command.Field(2).asString().GetMultiByteChars();
				bean.goodsName = command.Field(3).asString().GetMultiByteChars();
				bean.unitCode = command.Field(4).asString().GetMultiByteChars();
				bean.spec = command.Field(5).asString().GetMultiByteChars();
				bean.bpNo = command.Field(6).asString().GetMultiByteChars();
				bean.state = "";	
				bean.teRe = command.Field(7).asString().GetMultiByteChars();
				bean.companyCode = "";	
				bean.userID = "";
				bean.puid = "";
				bean.code=0;
				if(bean.userID.empty())
					bean.userID="";
				materials[*it] = bean;
			}else
			{
				vec_not_founds.push_back(*it);
			}
		}

		
	conn.Disconnect();
	}catch(SAException e)
	{
		LINFO<<"连接主数据库异常:"<<e.ErrMessage();
		printf("%s\r\n",e.ErrMessage());
	}
	return vec_not_founds;
}

int test(string &excelPath)
{
	Book *book;
	book = xlCreateXMLBook();
	book->setKey(L"Halil Kural", L"windows-2723210a07c4e90162b26966a8jcdboe");
	map<string,MaterialBean> materials;
	set<string> ids;
	vector<string> vecs;
	string process_name2="TCM Release Process";
	if (book->load(StringToWString(excelPath.c_str()).c_str()))
	{
		int sheet_count=book->sheetCount();
		Sheet *sheet=book->getSheet(0);
		int rowNum = sheet->lastRow();
		int colNum=sheet->lastCol();
		for(auto i=1;i<rowNum;i++)
		{
			string id = get_excel_data(book,sheet,i,0);			
			ids.insert(id);
			tag_t rev = NULLTAG;
			queryRevByItemID(id,&rev);
			//printfMaterial(bean);
			if(rev!=NULLTAG)
			{
				LINFO<<"更新物料-->"<<id.c_str();
				//updatePart2(rev,bean,false);
			}else
			{
				LINFO<<"未查询到对象ID=["<<id.c_str()<<"],开始创建";
				rev = createPart(id);
				proProcessCreate(rev,process_name2);
			}
		}
	}
	return 0;





	LINFO<<"物料更新---------->"<<getTime().c_str();
	db db;
	SAConnection conn;
	SACommand command;
	int pref_cnt;
	char **pref_vals;
	PREF_ask_char_values("database_tc", &pref_cnt, &pref_vals);
	try{
		stringstream ss;
		ss << pref_vals[0] << ':' << pref_vals[1] << '/' << pref_vals[2];		
		conn.Connect(ss.str().c_str(), pref_vals[3], pref_vals[4],
			SA_Oracle_Client);
		command.setConnection(&conn);
		db.command = &command;
		printf("sss\r\n");
		db.getMaterialsByGoodsCode(ids,materials,vecs);
		conn.Disconnect();
	}catch(SAException e)
	{
		printf("异常:%s\r\n",e.ErrMessage());
		e.ErrMessage();
		LINFO<<e.ErrMessage().GetMultiByteChars();
	}
	string process_name="TCM Release Process";
	LINFO<<"TC数据库查询结束，物料更新开始------>";
	for(auto it=materials.begin();it!=materials.end();it++)
	{
		string id = it->first.c_str();
		MaterialBean bean= it->second;
		tag_t rev = NULLTAG;
		queryRevByItemID(id,&rev);
		printfMaterial(bean);
		if(rev!=NULLTAG)
		{
			LINFO<<"更新物料-->"<<id.c_str();
			updatePart(rev,bean,false);
		}else
		{
			LINFO<<"未查询到对象ID=["<<id.c_str()<<"],开始创建";
			rev = createPart(bean);
			proProcessCreate(rev,process_name);
		}
	}
	materials.clear();
	vector<string> vec_not_founds;
	LINFO<<"TC数据库更新结束，开始查询主数据物料";
	vec_not_founds = searchSQLServer(vecs,materials);
	LINFO<<"开始更新主数据物料";
	for(auto it=materials.begin();it!=materials.end();it++)
	{
		string id = it->first.c_str();
		MaterialBean bean= it->second;
		tag_t rev = NULLTAG;
		queryRevByItemID(id,&rev);
		printfMaterial(bean);
		if(rev!=NULLTAG)
		{
			LINFO<<"更新物料-->"<<id.c_str();
			updatePart2(rev,bean,false);
		}else
		{
			LINFO<<"未查询到对象ID=["<<id.c_str()<<"],开始创建";
			rev = createPart(bean);
			proProcessCreate(rev,process_name);
		}
	}



	LINFO<<"数据库未搜索到物料------>";
	for(auto it=vec_not_founds.begin();it!=vec_not_founds.end();it++)
	{
		string id = (*it).c_str();
		LINFO<<"物料编码:"<<id.c_str()<<"";
	}
	LINFO<<"<--------数据库未搜索到物料";
	LINFO<<"结束--------";

	return 0;
}