telsale-management/代码/cpp/telsale_aux_kit/source/DataManipulation.cpp

600 lines
13 KiB
C++
Raw Permalink Normal View History

#include <SQLAPI.h>
#include <string>
#include <sstream>
#include <exception>
#include <stdlib.h>
#include <libxl.h>
#include "DataManipulation.h"
#include "SystemDataQuery.h"
#include "StringCodeConverter.h"
2018-06-04 12:54:29 +00:00
//#pragma comment( lib, "user32.lib")
//#pragma comment( lib, "version.lib")
//#pragma comment( lib, "oleaut32.lib")
//#pragma comment( lib, "ole32.lib")
//#pragma comment( lib, "libxl.lib")
// #ifdef _DEBUG
// #pragma comment( lib, "sqlapisd.lib")
// #else
// #pragma comment( lib, "sqlapis.lib")
// #endif
const char g_cszConnectStringIDS6[] =
"DRIVER={};"
"PROTOCOL=onsoctcp;"
"SERVICE=16191;"
"SERVER=xmcx1;"
"HOST=10.39.0.91;"
"DATABASE=ids6;"
"DB_LOCALE=en_US.819;";
const static char g_szUserNameIDS6[] = "ccx99";
const static char g_szPasswordIDS6[] = "c91IT09";
const static char g_cszConnstringYwgl[] =
2018-06-04 12:54:29 +00:00
"DRIVER={};"
"PROTOCOL=onsoctcp;"
"SERVICE=16192;"
"SERVER=xmcx2;"
"HOST=10.39.0.92;"
"DATABASE=ywgl_xm;"
"DB_LOCALE=en_US.819;";
const static char g_cszUserNameYwgl[] = "ccx99";
const static char g_cszPasswordYwgl[] = "c92IT09";
2018-06-20 09:56:06 +00:00
// void queryTelsalePolicyGifts(vector<TelSalePolicyGift>& giftList)
// {
// SAConnection connection;
// SACommand command;
//
// string strSQL = "select id, trim(name), default_price from w_dx_gifts";
//
// command.setConnection(&connection);
// command.setCommandText(strSQL.c_str());
//
// try
// {
// connection.Connect(g_cszConnstringYwgl,
// g_cszUserNameYwgl,
// g_cszPasswordYwgl,
// SA_Informix_Client);
//
// command.Execute();
// }
// catch (SAException& error)
// {
// throw runtime_error(error.ErrText());
// }
//
// while (command.FetchNext())
// {
// TelSalePolicyGift gift;
//
// gift.Id(command.Field(1).asLong());
// gift.GiftName((const char *)(command.Field(2).asString()));
// gift.GiftDefaultPrice((const char *)(command.Field(3).asString()));
//
// giftList.push_back(gift);
// }
//
// connection.Disconnect();
// }
2018-06-04 12:54:29 +00:00
void SaveTelSalePolicyInfo(const SPolicyRecord& policy, const vector<TelSalePolicyGift>& giftList)
{
2018-06-04 12:54:29 +00:00
if (policy.strPolicySerial.empty() == true)
{
2018-06-04 12:54:29 +00:00
throw invalid_argument("保单号为空!");
}
2018-06-04 12:54:29 +00:00
if (policy.strSalerCode.empty() == true)
{
2018-06-04 12:54:29 +00:00
throw invalid_argument("保单号为空!");
}
ostringstream ostrCommand;
2018-06-04 12:54:29 +00:00
string strAutotraderCall;
2018-06-01 10:21:26 +00:00
//车店联呼
2018-06-04 12:54:29 +00:00
if (policy.bIsAutotraderCall == true)
{
strAutotraderCall = "1";
}
else
{
strAutotraderCall = "0";
}
2018-06-04 12:54:29 +00:00
ostrCommand <<
"insert into w_dxbd_i ( jjbj, khjl, khjllx, bdh, zhjywy, zhjywymc, zhjywybm, zhjywybmm, kshdm, kshmc, czydm, czrq , chdlh, chshdm, chshmc, khmc, chph, chjh, fdjh ) "
"values( 1, 0, 0, "
<< "trim('" << policy.strPolicySerial << "'), "
<< "trim('" << policy.strSalerCode << "'), "
<< "trim('" << policy.strSalerName << "'), "
<< "trim('" << policy.strSalerDeptCode << "'), "
<< "trim('" << policy.strSalerDeptName << "'), "
<< "trim('" << policy.strSalerOfficeCode << "'), "
<< "trim('" << policy.strSalerOfficeName << "'), "
<< "trim('" << policy.strOperatorCode << "'), "
<< "today, "
<< "'" << strAutotraderCall << "'";
2018-06-04 12:54:29 +00:00
if (policy.strAutoTraderCode.empty() == false)
{
ostrCommand << ", '" << policy.strAutoTraderCode << "'";
ostrCommand << ", '" << policy.strAutoTraderName << "'";
}
else
{
ostrCommand << ", NULL, NULL";
}
2018-06-04 12:54:29 +00:00
if (policy.strCustomerName.empty() == false)
{
ostrCommand << ", '" << policy.strCustomerName << "'";
}
else
{
ostrCommand << ", NULL";
}
2018-06-04 12:54:29 +00:00
if (policy.strPlateSerial.empty() == false)
{
ostrCommand << ", '" << policy.strPlateSerial << "'";
}
else
{
ostrCommand << ", NULL";
}
2018-06-04 12:54:29 +00:00
if (policy.strFrameSerial.empty() == false)
{
ostrCommand << ", '" << policy.strFrameSerial << "'";
}
else
{
ostrCommand << ", NULL";
}
2018-06-04 12:54:29 +00:00
if (policy.strEngineSerial.empty() == false)
{
ostrCommand << ", '" << policy.strEngineSerial << "'";
}
else
{
ostrCommand << ", NULL";
}
ostrCommand << " )";
string strCommand = ostrCommand.str();
SAConnection conn;
2018-06-04 12:54:29 +00:00
SACommand command;
2018-06-04 12:54:29 +00:00
command.setConnection(&conn);
command.setCommandText(strCommand.c_str());
try
{
conn.Connect(
2018-06-04 12:54:29 +00:00
g_cszConnstringYwgl,
g_cszUserNameYwgl,
g_cszPasswordYwgl,
SA_Informix_Client);
2018-06-01 10:21:26 +00:00
//conn.setAutoCommit( SA_AutoCommitOff ); //导致锁表
2018-06-01 10:21:26 +00:00
//保存保单
command.Execute();
2018-06-01 10:21:26 +00:00
//保存礼品列表,先删除旧数据
2018-06-04 12:54:29 +00:00
command.setCommandText("delete w_dxbd_gift_i where bdh = :1 ");
command.Param(1).setAsString() = policy.strPolicySerial.c_str();
command.Execute();
2018-06-04 12:54:29 +00:00
command.setCommandText("insert into w_dxbd_gift_i ( bdh, gift_name, gift_price ) values ( :1, :2, :3 )");
2018-06-04 12:54:29 +00:00
for (vector<TelSalePolicyGift>::const_iterator iter = giftList.begin(); iter != giftList.end(); ++iter)
{
2018-06-04 12:54:29 +00:00
command.Param(1).setAsString() = policy.strPolicySerial.c_str();
command.Param(2).setAsString() = iter->GiftName().c_str();
command.Param(3).setAsString() = iter->GiftDefaultPrice().c_str();
command.Execute();
}
conn.Commit();
}
2018-06-04 12:54:29 +00:00
catch (SAException& error)
{
conn.Rollback();
conn.Disconnect();
2018-06-04 12:54:29 +00:00
throw runtime_error(error.ErrText());
}
conn.Disconnect();
}
2018-06-04 12:54:29 +00:00
void QueryTelSalePolicyInfo(const string& strPolicyNo,
const string& strOperatorCode,
const string& strStartDate,
const string& strEndDate,
vector<SPolicyQuery>& vPolicyInfo)
{
2018-06-04 12:54:29 +00:00
SACommand command;
SAConnection connection;
SPolicyQuery policy;
2018-06-04 12:54:29 +00:00
string strSQL =
"select trim(bdh) bdh, trim(zhjywy) zhjywy, to_char(czrq, '%Y年%m月%d日') czrq, trim(czydm) czydm, chdlh, zhjywybmm, kshmc, chshdm, chshmc "
" from w_dxbd_i "
" where 1=1 ";
2018-06-04 12:54:29 +00:00
if (!strPolicyNo.empty())
{
2018-06-04 12:54:29 +00:00
strSQL.append("and bdh = '");
strSQL.append(strPolicyNo);
strSQL.append("' ");
}
2018-06-04 12:54:29 +00:00
if (!strOperatorCode.empty())
{
2018-06-04 12:54:29 +00:00
strSQL.append("and czydm = '");
strSQL.append(strOperatorCode);
strSQL.append("' ");
}
2018-06-04 12:54:29 +00:00
if (!strStartDate.empty())
{
2018-06-04 12:54:29 +00:00
strSQL.append("and czrq >= '");
strSQL.append(strStartDate);
strSQL.append("' ");
}
2018-06-04 12:54:29 +00:00
if (!strEndDate.empty())
{
2018-06-04 12:54:29 +00:00
strSQL.append("and czrq <= '");
strSQL.append(strEndDate);
strSQL.append("' ");
}
try
{
2018-06-04 12:54:29 +00:00
command.setCommandText(strSQL.c_str());
command.setConnection(&connection);
connection.Connect(
2018-06-04 12:54:29 +00:00
g_cszConnstringYwgl,
g_cszUserNameYwgl,
g_cszPasswordYwgl,
SA_Informix_Client);
command.Execute();
2018-06-04 12:54:29 +00:00
while (command.FetchNext())
{
2018-06-04 12:54:29 +00:00
policy.strPolicySerial = command.Field("bdh").asString();
policy.strSalerCode = command.Field("zhjywy").asString();
policy.strDeptName = command.Field("zhjywybmm").asString();
policy.strOfficeName = command.Field("kshmc").asString();
policy.strAutoTraderCode = command.Field("chshdm").asString();
policy.strAutoTraderName = command.Field("chshmc").asString();
2018-06-04 12:54:29 +00:00
policy.strInputDate = command.Field("czrq").asString();
policy.strCDLH = command.Field("chdlh").asString();
2018-06-20 09:56:06 +00:00
//QueryPolicyGifts(policy.strPolicySerial, policy.strGifts, policy.dGiftPriceSum);
2018-06-04 12:54:29 +00:00
vPolicyInfo.push_back(policy);
}
connection.Disconnect();
}
2018-06-04 12:54:29 +00:00
catch (SAException& error)
{
2018-06-04 12:54:29 +00:00
throw runtime_error(error.ErrText());
}
}
2018-06-20 09:56:06 +00:00
// void QueryPolicyGifts(const string& strPolicyNo, string& strGifts, double dGiftPriceSum)
// {
// SAConnection connection;
// SACommand command;
//
// string strSQL =
// "select gift_name, gift_price "
// " from w_dxbd_gift_i "
// " where bdh = '" + strPolicyNo + "'";
//
// string strSQLSum =
// "select sum( gift_price ) "
// " from w_dxbd_gift_i "
// " where bdh = '" + strPolicyNo + "'";
//
// try
// {
// command.setConnection(&connection);
// command.setCommandText(strSQL.c_str());
//
// connection.Connect(g_cszConnstringYwgl,
// g_cszUserNameYwgl,
// g_cszPasswordYwgl,
// SA_Informix_Client);
//
// command.Execute();
//
// strGifts.clear();
// dGiftPriceSum = 0.0;
//
// while (command.FetchNext())
// {
// if (!strGifts.empty())
// {
// strGifts.append("; ");
// }
//
// strGifts.append(command.Field("gift_name").asString());
// strGifts.append(" ");
// strGifts.append(command.Field("gift_price").asString());
// strGifts.append("元");
//
// dGiftPriceSum += command.Field("gift_price").asDouble();
// }
//
// /*command.setCommandText( strSQLSum.c_str() );
// command.Execute();
//
// if ( command.FetchNext() )
// {
// strGiftPriceSum = command.Field( 0 ).asString();
// }*/
//
// connection.Disconnect();
// }
// catch (SAException& error)
// {
// throw runtime_error(error.ErrText());
// }
// }
2018-06-02 09:47:28 +00:00
/*void readTelsaleXlsFile(const string strFilePath, vector<SPolicyRecord> & listPolicy, bool hasTitle, bool isXML )
{
2018-06-02 09:47:28 +00:00
Book * pBook = NULL;
Sheet * pSheet = NULL;
int iRowCount = 0;
int iRowIndex = 0;
if ( isXML == true )
{
pBook = xlCreateXMLBook();
}
else
{
pBook = xlCreateBook();
}
if ( pBook == NULL )
{
throw string("");
}
if ( pBook->load(strFilePath.c_str()) == NULL )
{
string strMessage = pBook->errorMessage();
throw string( strMessage );
}
pSheet = pBook->getSheet( 0 );
if ( pSheet == NULL )
{
2018-06-01 10:21:26 +00:00
throw string( "读取sheet失败" );
}
iRowCount = pSheet->lastRow();
if ( hasTitle == true )
{
iRowIndex = 1;
}
2018-06-01 10:21:26 +00:00
//读取数据
while ( iRowIndex < iRowCount )
{
SPolicyRecord policy;
CellType type = pSheet->cellType( iRowIndex, 4 );
2018-06-02 09:47:28 +00:00
policy.strSignDate = readXlsxCell( pSheet, iRowIndex, 0 );
policy.strPolicySerial = readXlsxCell( pSheet, iRowIndex, 1 );
policy.strPlateSerial = readXlsxCell( pSheet, iRowIndex, 2 );
policy.strCustomerName = readXlsxCell( pSheet, iRowIndex, 3 );
policy.strSalerCode = readXlsxCell( pSheet, iRowIndex, 4 );
//policy.strAutoTraderName = readXlsCell( pSheet, iRowIndex, 5 );
2018-06-02 09:47:28 +00:00
policy.strAutoTraderCode = readXlsxCell( pSheet, iRowIndex, 6 );
2018-06-01 10:21:26 +00:00
//补齐工号
if ( policy.strSalerCode.length() == 1 )
{
policy.strSalerCode = string("00") + policy.strSalerCode;
}
if ( policy.strSalerCode.length() == 2 )
{
policy.strSalerCode = string("0") + policy.strSalerCode;
}
policy.strOperatorCode = getUserCode();
2018-06-15 07:32:41 +00:00
query_auto_trader_info( policy.strAutoTraderCode, policy.strAutoTraderName );
2018-06-15 07:32:41 +00:00
query_staff_info( policy.strSalerCode,
policy.strSalerName,
policy.strSalerDeptCode,
policy.strSalerDeptName,
policy.strSalerOfficeCode,
policy.strSalerOfficeName );
listPolicy.push_back( policy );
iRowIndex++;
}
pBook->release();
2018-06-02 09:47:28 +00:00
}*/
2018-06-04 12:54:29 +00:00
string readXlsCell(ISheetT<char>* pSheet, int iRowIndex, int iColIndex)
{
string strCell;
2018-06-04 12:54:29 +00:00
char szNum[40];
int iCell;
2018-06-04 12:54:29 +00:00
if (pSheet == NULL)
{
throw string("");
}
2018-06-04 12:54:29 +00:00
CellType cellType = pSheet->cellType(iRowIndex, iColIndex);
2018-06-04 12:54:29 +00:00
switch (cellType)
{
2018-06-04 12:54:29 +00:00
case libxl::CELLTYPE_STRING:
strCell = pSheet->readStr(iRowIndex, iColIndex);
break;
case libxl::CELLTYPE_NUMBER:
memset(szNum, NULL, 40);
2018-06-04 12:54:29 +00:00
iCell = pSheet->readNum(iRowIndex, iColIndex);
sprintf_s(szNum, "%d", iCell);
strCell = szNum;
2018-06-04 12:54:29 +00:00
break;
default:
strCell = "";
}
return strCell;
}
2018-06-04 12:54:29 +00:00
string readXlsxCell(ISheetT<wchar_t>* pSheet, int iRowIndex, int iColIndex)
{
wstring strCell;
2018-06-04 12:54:29 +00:00
string strReturn;
char szNum[40];
2018-06-04 12:54:29 +00:00
int iCell;
2018-06-04 12:54:29 +00:00
if (pSheet == NULL)
{
throw string("");
}
2018-06-04 12:54:29 +00:00
CellType cellType = pSheet->cellType(iRowIndex, iColIndex);
2018-06-04 12:54:29 +00:00
switch (cellType)
{
2018-06-04 12:54:29 +00:00
case libxl::CELLTYPE_STRING:
strCell = pSheet->readStr(iRowIndex, iColIndex);
break;
case libxl::CELLTYPE_NUMBER:
memset(szNum, NULL, 40);
2018-06-04 12:54:29 +00:00
iCell = pSheet->readNum(iRowIndex, iColIndex);
sprintf_s(szNum, "%d", iCell);
strReturn = szNum;
2018-06-04 12:54:29 +00:00
break;
default:
strReturn = "";
}
2018-06-04 12:54:29 +00:00
StringCodeConverter::unicode2mbs(strCell, strReturn);
return strReturn;
}
2018-06-04 12:54:29 +00:00
void readTelsaleXlsxFile(const wstring& filePath, vector<SPolicyRecord>& listPolicy, bool hasTitle)
{
2018-06-04 12:54:29 +00:00
IBookT<wchar_t>* pBook = NULL;
ISheetT<wchar_t>* pSheet = NULL;
int iRowCount = 0;
int iRowIndex = 0;
2018-06-04 12:54:29 +00:00
pBook = xlCreateXMLBookW();
2018-06-04 12:54:29 +00:00
if (pBook == NULL)
{
throw string("");
}
2018-06-04 12:54:29 +00:00
if (pBook->load(filePath.data()) == false)
{
2018-06-04 12:54:29 +00:00
throw string("打开文件失败!");
}
2018-06-04 12:54:29 +00:00
pSheet = pBook->getSheet(0);
2018-06-04 12:54:29 +00:00
if (pSheet == NULL)
{
2018-06-04 12:54:29 +00:00
throw string("读取sheet失败");
}
iRowCount = pSheet->lastRow();
2018-06-04 12:54:29 +00:00
if (hasTitle == true)
{
iRowIndex = 1;
}
2018-06-01 10:21:26 +00:00
//读取数据
2018-06-04 12:54:29 +00:00
while (iRowIndex < iRowCount)
{
SPolicyRecord policy;
2018-06-04 12:54:29 +00:00
CellType type = pSheet->cellType(iRowIndex, 4);
2018-06-04 12:54:29 +00:00
policy.strSignDate = readXlsxCell(pSheet, iRowIndex, 0);
policy.strPolicySerial = readXlsxCell(pSheet, iRowIndex, 1);
policy.strPlateSerial = readXlsxCell(pSheet, iRowIndex, 2);
policy.strCustomerName = readXlsxCell(pSheet, iRowIndex, 3);
policy.strSalerCode = readXlsxCell(pSheet, iRowIndex, 4);
policy.strAutoTraderName = readXlsxCell(pSheet, iRowIndex, 6);
2018-06-01 10:21:26 +00:00
//补齐工号
2018-06-04 12:54:29 +00:00
if (policy.strSalerCode.length() == 1)
{
policy.strSalerCode = string("00") + policy.strSalerCode;
}
2018-06-04 12:54:29 +00:00
if (policy.strSalerCode.length() == 2)
{
policy.strSalerCode = string("0") + policy.strSalerCode;
}
policy.strOperatorCode = getUserCode();
2018-06-15 07:32:41 +00:00
query_staff_info(
2018-06-04 12:54:29 +00:00
policy.strSalerCode,
policy.strSalerName,
policy.strSalerDeptCode,
policy.strSalerDeptName,
policy.strSalerOfficeCode,
policy.strSalerOfficeName);
2018-06-04 12:54:29 +00:00
listPolicy.push_back(policy);
iRowIndex++;
}
pBook->release();
}