MYSQL to SQLite
#include "stdafx.h"
#include <stdio.h>
#include <atltime.h>
#include <stdio.h>
#include <atltime.h>
#include "Homi.h"
#include "CFGApp.h"
#include <common.h>
#include <BizDataCenter.h>
#include <BizCommDB.h>
#include "MySQLDB.h"
#include "DBPool.h"
BizCommDB::BizCommDB()
{
}
BizCommDB::~BizCommDB()
{
}
#if 0
int BizCommDB::InsertDailySise(CMySQLDB *pDB2,char *pgmname, char*shtcode, STDSItem hmps)
{
CMySQLDB *pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
char szquery[1024] = { 0, };
int rows = 0;
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
if (hmps.ndate > 0) {
// When a duplicate value is found, then MySQL will perform an update instead of an insert.
// ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.
sprintf(szquery, "INSERT INTO homidb.t_dailysise (pgm_id,shtcode,sdate, "
"open,high,low,close,dsign,dchange,drate,volume,diff_vol,chdegree,sojinrate, "
"changerate,fpvolume,covolume,ppvolume,krwamt,update_ts)\r\n"
"VALUES(\'%s\',\'%s\',\'%08d\', "
"\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%s\',\'%ld\',\'%4.2f\', "
"\'%ld\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%ld\',\'%ld\',\'%ld\',\'%ld\', \r\n" //volume
"FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H%%i')))) \r\n"
"ON DUPLICATE KEY UPDATE pgm_id = \'%s\', update_ts = NOW(), \r\n"
"open =\'%ld\', high=\'%ld\', low=\'%ld\', close=\'%ld\',\r\n"
"dsign=\'%s\', dchange=\'%ld\', drate=\'%4.2f\', volume =\'%ld\',\r\n"
"diff_vol=\'%3.2f\', chdegree=\'%3.2f\', sojinrate=\'%3.2f\',\r\n"
"changerate=\'%3.2f\',fpvolume=\'%ld\',covolume=\'%ld\',ppvolume=\'%ld\',krwamt=\'%ld\';\r\n"
,
pgmname, shtcode, hmps.ndate,
hmps.open, hmps.high, hmps.low, hmps.close, hmps.sign, hmps.change, hmps.drate,
hmps.volume, hmps.diff_vol, hmps.chdegree, hmps.sojinrate, hmps.changerate,
hmps.fpvolume, hmps.covolume, hmps.ppvolume, hmps.krwamt, hmps.update_dt,
pgmname, hmps.open, hmps.high, hmps.low, hmps.close, hmps.sign, hmps.change, hmps.drate,
hmps.volume, hmps.diff_vol, hmps.chdegree, hmps.sojinrate, hmps.changerate,
hmps.fpvolume, hmps.covolume, hmps.ppvolume, hmps.krwamt);// MultiByteToUtf8("접속"));
rows += pDB->ExecuteQuery(szquery);
pDB->Commit();
}
}
if(!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
#endif
int BizCommDB::DeletePastDailySise(CMySQLDB *pDB2)
{
int rows = 0;
int qlen;
char szquery[1024] = { 0, };
CMySQLDB *pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
qlen = sprintf(szquery, //무조건 지우고 61일치 새로받기
"DELETE FROM homidb.t_dailysise \r\n"
"WHERE sdate <= DATE_FORMAT(DATE_SUB(\'%08d\', INTERVAL %d DAY), '%%Y%%m%%d'); \r\n"
, g_conf.m_today, MAX_DAILYSISE + 40);
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
#if 0
int BizCommDB::SaveDailySise(CMySQLDB *pDB2, HMStockInfo *psm,int daycount,char *szupdatedt)
{
STDSItem hmps;
char *pgmname = g_conf.m_lastname;
char*shtcode = psm->shtcode;
char szquery[2048] = { 0, };
int rows = 0;
int slen = 0;
if (daycount < 1) return 0;
CMySQLDB *pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
for (int i = 0; i < min(psm->ds.daycount, daycount); i++) {
//float memevol = abs(psm->ds.days[i].fpvolume)
// + abs(psm->ds.days[i].ppvolume) + abs(psm->ds.days[i].covolume);
hmps = psm->ds.days[i];
if (hmps.ndate > 0) {
// When a duplicate value is found, then MySQL will perform an update instead of an insert.
// ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.
slen = sprintf(szquery, "INSERT INTO homidb.t_dailysise (pgm_id,shtcode,sdate, "
"open,high,low,close,dsign,dchange,drate,volume,diff_vol,chdegree,sojinrate, "
"changerate,fpvolume,covolume,ppvolume,krwamt,update_ts)\r\n"
"VALUES(\'%s\',\'%s\',\'%08d\', "
"\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%s\',\'%ld\',\'%4.2f\', "
"\'%ld\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%ld\',\'%ld\',\'%ld\',\'%ld\', \r\n" //volume
"FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H%%i')))) \r\n"
//"ON DUPLICATE KEY UPDATE pgm_id = \'%s\', update_ts = NOW(), \r\n"
"ON DUPLICATE KEY UPDATE pgm_id = \'%s\', \r\n"
"update_ts = FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H%%i'))), \r\n"
"open =\'%ld\', high=\'%ld\', low=\'%ld\', close=\'%ld\',\r\n"
"dsign=\'%s\', dchange=\'%ld\', drate=\'%4.2f\', volume =\'%ld\',\r\n"
"diff_vol=\'%3.2f\', chdegree=\'%3.2f\', sojinrate=\'%3.2f\',\r\n"
"changerate=\'%3.2f\',fpvolume=\'%ld\',covolume=\'%ld\',ppvolume=\'%ld\',krwamt=\'%ld\';\r\n"
,pgmname, shtcode, hmps.ndate,
hmps.open, hmps.high, hmps.low, hmps.close, hmps.sign, hmps.change, hmps.drate,
hmps.volume, hmps.diff_vol, hmps.chdegree, hmps.sojinrate, hmps.changerate,
hmps.fpvolume, hmps.covolume, hmps.ppvolume, hmps.krwamt, szupdatedt,
pgmname, szupdatedt, hmps.open, hmps.high, hmps.low, hmps.close, hmps.sign, hmps.change, hmps.drate,
hmps.volume, hmps.diff_vol, hmps.chdegree, hmps.sojinrate, hmps.changerate,
hmps.fpvolume, hmps.covolume, hmps.ppvolume, hmps.krwamt);// MultiByteToUtf8("접속"));
rows += pDB->ExecuteQuery(szquery);
}
}
pDB->Commit();
if(rows > 0)
strncpy(psm->di.lastdbdate, szupdatedt, sizeof(psm->ds.lastdbdate)); //db저장 최근날짜
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
#else
int BizCommDB::SaveDailySise(CMySQLDB* pDB2, HMStockInfo* psm, int daycount, char* szupdatedt)
{
STDSItem hmps;
char* pgmname = g_conf.m_lastname;
char* shtcode = psm->shtcode;
char* szquery = NULL;
char* szvalue = NULL;
int rows = 0;
int slen = 0;
int vlen = 0, vlen1 = 0;
if (daycount < 1) return 0;
CMySQLDB* pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
int count = min(psm->ds.daycount, daycount);
szquery = (char*)malloc(10000);
if (!szquery) goto exit_save;
szquery[0] = NULL;
szvalue = (char*)malloc(6096);
if (!szvalue) goto exit_save;
szvalue[0] = NULL;
for (int i = 0; i < count; i++) {
//float memevol = abs(psm->ds.days[i].fpvolume)
// + abs(psm->ds.days[i].ppvolume) + abs(psm->ds.days[i].covolume);
hmps = psm->ds.days[i];
if (hmps.ndate > 0 && hmps.open > 0) {
// When a duplicate value is found, then MySQL will perform an update instead of an insert.
// ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.
vlen1 = sprintf(szvalue + vlen,
"%s(\'%s\',\'%s\',\'%08d\', "
"\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%s\',\'%ld\',\'%4.2f\',"
"\'%ld\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%3.2f\',\'%ld\',\'%ld\',\'%ld\',\'%ld\',\r\n" //volume
"FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H%%i'))))",
vlen > 0 ? ",\r\n":"",pgmname, shtcode, hmps.ndate,
hmps.open, hmps.high, hmps.low, hmps.close, hmps.sign, hmps.change, hmps.drate,
hmps.volume, hmps.diff_vol, hmps.chdegree, hmps.sojinrate, hmps.changerate,
hmps.fpvolume, hmps.covolume, hmps.ppvolume, hmps.krwamt, szupdatedt);
vlen += vlen1;
if ((i+1) % 10 == 0 || i == count - 1 || vlen >= 4800) {
slen = sprintf(szquery, "INSERT INTO homidb.t_dailysise (pgm_id,shtcode,sdate, "
"open,high,low,close,dsign,dchange,drate,volume,diff_vol,chdegree,sojinrate, "
"changerate,fpvolume,covolume,ppvolume,krwamt,update_ts)\r\n"
"VALUES\r\n"
"%s\r\n"
"ON DUPLICATE KEY UPDATE\r\n"
"pgm_id = VALUES(pgm_id),update_ts = VALUES(update_ts),\r\n" //FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H%%i'))), \r\n"
"open =VALUES(open), high=VALUES(high), low=VALUES(low), close=VALUES(close),\r\n"
"dsign=VALUES(dsign), dchange=VALUES(dchange), drate=VALUES(drate), volume =VALUES(volume),\r\n"
"diff_vol=VALUES(diff_vol), chdegree=VALUES(chdegree), sojinrate=VALUES(sojinrate),\r\n"
"changerate=VALUES(changerate),fpvolume=VALUES(fpvolume),covolume=VALUES(covolume),ppvolume=VALUES(ppvolume),krwamt=VALUES(krwamt);\r\n",
szvalue);
rows += pDB->ExecuteQuery(szquery);
//memset(szvalue, 0, sizeof(szvalue));
szquery[0] = NULL;
szvalue[0] = NULL;
vlen = 0;
}
}
}
pDB->Commit();
if (rows > 0)
strncpy(psm->ds.lastdbdate, szupdatedt, sizeof(psm->ds.lastdbdate)); //db저장 최근날짜
exit_save:
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
if (szquery)
free(szquery);
if (szvalue)
free(szvalue);
return rows;
}
#endif
//int BizCommDB::SaveDailySise(int index, int count, int onlytoday, int *saved)
//{
// int rows = 0;
// int rowindex = index;
// int rowcount = 0;
// int qlen;
// char szquery[1024] = { 0, };
// HMStockInfo *psm = NULL;
// //MYSTOCK_MASTER::iterator it;
// STDSItem hmps;
// CMySQLDB *pDB = CDBPool::GetLiveDB();
// string shtcode;
// HMSiseInfo *pSItem;
// MYSTOCK_LIST *pStockList = &g_datacenter.m_stocklist;
// MYSTOCK_LIST::HASHMAP *pMap = pStockList->GetHashMap();
// MYSTOCK_LIST::HASHMAP::iterator it;
// //100일 경과는 삭제하고 현재상태 저장
// //if (index >= g_datacenter.m_stockmaster.size() - 1)
// // return 0;
// if (index >= pStockList->Count() - 1)
// return 0;
// if (!pDB || !pDB->IsConnected())
// return 0;
// if (index == 0) {
// qlen = sprintf(szquery, //무조건 지우고 61일치 새로받기
// "DELETE FROM homidb.t_dailysise \r\n"
// "WHERE sdate <= DATE_FORMAT(DATE_SUB(\'%s\', INTERVAL %d DAY), '%%Y%%m%%d'); \r\n"
// , g_conf.m_sztoday, MAX_DAILYSISE + 40);
// rows = pDB->ExecuteQuery(szquery);
// pDB->Commit();
// }
// int tosave = 0;
// char szupdate_dt[13];
// sprintf(szupdate_dt, "%s%02d%02d", g_conf.m_sztoday, g_conf.m_watchtime.wHour, g_conf.m_watchtime.wMinute);
// //for (it = std::next(g_datacenter.m_stockmaster.begin(), index); it != g_datacenter.m_stockmaster.end(); ++it) {
// for (it = std::next(pMap->begin(), index); it != pMap->end(); ++it) {
// shtcode = it->first;
// pSItem = &it->second;
// //shtcode = it->first;
// //psm = &it->second;
// psm = pSItem->psm;
// tosave = psm->ds.daycount;
// if (psm->ds.daycount > 0) {
// if (psm->ds.diffdbdays >= 3)
// tosave = min(psm->ds.daycount, psm->ds.diffdbdays + 5);
// else if (!strnicmp(psm->ds.days[0].date, g_conf.m_szexeday,8))
// tosave = max(psm->ds.diffdbdays,2);
// if (psm->ds.dbloadcount < MAX_DAILYSISE) {
// tosave = psm->ds.daycount;
// psm->ds.daycount = psm->ds.daycount;
// }
// if (onlytoday)
// tosave = 1;
// int saved = 0;
// for (int i = 0; i < tosave; i++) {
// strncpy(psm->ds.days[i].update_dt, szupdate_dt, sizeof(psm->ds.days[i].update_dt)-1);
// if (i == 0 && psm->ds.days[0].drate != pSItem->market.drate) {
// psm->ds.days[0].open = pSItem->market.open;
// psm->ds.days[0].high = pSItem->market.high;
// psm->ds.days[0].low = pSItem->market.low;
// psm->ds.days[0].close = pSItem->market.price;
// psm->ds.days[0].sign[0] = pSItem->market.sign[0]; //전일대비 구분 char(1) 1: 상한, 2: 상승, 3: 보합, 4: 하한, 5: 하락
// psm->ds.days[0].change = pSItem->market.change;//전일대비 long(8) 기호는 안들어옴 위에 sign으로 판단해야함
// psm->ds.days[0].drate = pSItem->market.drate; //등락율
// psm->ds.days[0].volume = pSItem->market.volume;
// psm->ds.days[0].krwamt = pSItem->market.value;//거래대금(백만)
// }
// float memevol = abs(psm->ds.days[i].fpvolume)
// + abs(psm->ds.days[i].ppvolume) + abs(psm->ds.days[i].covolume);
// if (memevol == 0 || psm->ds.days[i].krwamt == 0) {
// BizCommDB::InsertDailySise(pDB, g_conf.m_lastname, psm->shtcode, psm->ds.days[i]);
// saved++;
// }
// else
// break;
// }
// if(saved)
// rowcount++;
// }
// if (count > 0 && rowcount >= count)
// break;
// rowindex++;
// }
// if (saved)
// *saved = rowcount;
//
// CDBPool::FreeLiveDB(pDB);
// return rowindex;
//}
//20220413
//sprintf 등에 인자가 한꺼번에 들어가니까 같은 메모리가리키는 static 쓰면안된다.
int MBS2UTF8(char *src,char *dest,int maxlen)
{
int len = 0;
wstring strUni = CA2W(src); //ANSI -> UNICODE
string strUTF8 = CW2A(strUni.c_str(), CP_UTF8).m_psz; // UNICODE -> UTF8
*dest = 0x00;
len = strUTF8.size();
strncpy(dest, strUTF8.c_str(), min(maxlen,len));
*(dest + min(maxlen, len)) = 0x00;
return strUTF8.size();
}
int UTF8MBS(char *src, char *dest, int maxlen)
{
int len = 0;
wstring strUni = CA2W(src, CP_UTF8);//UTF-8을 UNICODE(16bit)로
string strAnsi = CW2A(strUni.c_str()).m_psz; // UNICODE -> ANSI
*dest = 0x00;
len = strAnsi.size();
strncpy(dest, strAnsi.c_str(), min(maxlen, len));
*(dest + min(maxlen, len)) = 0x00;
return strAnsi.size();
}
#if 0
//20240904
//먼저 아래종목정보 호출전에 당일업데이트한 시간과 종목갯수를 알아본후 아래 개별
//LoadMasterInfo 종목정보 호출할것!!
//쿼리참고 C:\Util\HeidiSQL_9.5_64_Portable\당일시세저장 최대갯수와 마지막 저장시간쿼리.sql
int BizCommDB::LoadMasterInfo(char* shtcode, HMStockInfo* psm, CMySQLDB* pDB2)
{
//--
//-- 2024-09-03 20:48:48 20240903204848 642
SELECT MAX(update_ts), DATE_FORMAT(MAX(update_ts), '%Y%m%d%H%i%s') AS recent_dt, COUNT(*)
-- ((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(update_ts)) AS diffsecs
FROM `homidb`.`t_stockinfo`
WHERE sdate = DATE_FORMAT(update_ts, '%Y%m%d')
}
#endif
int BizCommDB::LoadMasterInfo(char* shtcode, HMStockInfo* psm, CMySQLDB* pDB2)
{
struct _tempmaster {
char szshtcode[8+1] = { 0, };
char szhname[86] = { 0, };
char szupname[86] = { 0, };
char szmarketgb[20] = { 0, };
char szlocks[40] = { 0, };
char szmanage[40] = { 0, };
char szstop[40] = { 0, };
char szwarn[40] = { 0, };
char szcare[40] = { 0, };
char shterm_text[40] = { 0, };
char lend_text[40] = { 0, };
char insert_id[30] = { 0, };
char update_id[30] = { 0, };
char update_dt[30] = { 0, };
};
int rows = 0;
CMySQLDB* pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
char szquery[4096] = { 0, };
int qlen = 0;
int col;
BOOL bLoadAll = FALSE;
const char* szerror = 0;
struct _tempmaster *tempinfo;
SYSTEMTIME cur_time;
GetLocalTime(&cur_time);
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
//2시간미만이면 DB에서 로드하고 현재시세 서버요청 안함(EBEST만)
//szquery = (char*)malloc(4096);
if (szquery == NULL)
return -1;
tempinfo = new _tempmaster;
if(tempinfo == NULL)
return -2;
memset(tempinfo, 0, sizeof(_tempmaster));
//qlen = sprintf(szquery,
// "SELECT COUNT(*) FROM `homidb`.`t_stockinfo` \r\n");
//if (shtcode && *shtcode)
// sprintf(szquery + qlen, "WHERE shtcode = \'%s\';\r\n", shtcode);
//rows = pDB->QuerySelect(NULL, szquery, qlen);//%포맷열 때문에 SelectEx 사용금지(오류남)
qlen = sprintf(szquery,
"SELECT shtcode,UNIX_TIMESTAMP() - UNIX_TIMESTAMP(update_ts) AS diffsecs,sdate,\r\n"
" hname,upname,marketid, marketgb,capital, sigatotal, listing, abscnt,etf, \r\n"
" category, bu12gubun, spac_gubun, per, pbrx, t_per, epsrt, bfeps, \r\n"
" bfoperatingincome, bfoperatingincome2, high52w, low52w, \r\n"
" high52wdate, low52wdate, listdate, uplmtprice, dnlmtprice, openprice, high, low, closeprice, \r\n"
" dsign, dchange, drate, jnilvolume, volume, `locks`, manage, stops, warn, care, shterm_text, lend_text, investatt, \r\n"
" fprevolumerate, fprevolumelistrate, `insert_id`, update_id, \r\n"
" DATE_FORMAT(update_ts, \'%%Y%%m%%d\') update_dt\r\n"
"FROM `homidb`.`t_stockinfo`\r\n");
if (shtcode && *shtcode)
sprintf(szquery + qlen, "WHERE shtcode = \'%s\';\r\n", shtcode);
else {
sprintf(szquery + qlen, "ORDER BY shtcode ASC;\r\n");
bLoadAll = TRUE;
}
//if (within_secs > 0)
// qlen += sprintf(szquery + qlen,
// " AND (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(update_ts)) <= %d;\r\n", within_secs);
//else
// qlen += sprintf(szquery + qlen,";\r\n");
//쿼리결과가 이상함 20240816
rows = pDB->QuerySelect(NULL, szquery, qlen);//%포맷열 때문에 SelectEx 사용금지(오류남)
if (rows > 0) {
for (register int i = 0; i < rows; i++) {
HMStockInfo mi;
//HMSiseInfo siseitem;
memset(tempinfo, 0, sizeof(_tempmaster));
//memset(szhname, 0, sizeof(szhname));
//memset(szupname, 0, sizeof(szupname));
//memset(szmarketgb, 0, sizeof(szmarketgb));
//memset(szlocks ,0, sizeof(szlocks));
//memset(szmanage, 0, sizeof(szmanage));
//memset(szstop, 0, sizeof(szstop));
//memset(szwarn, 0, sizeof(szwarn));
//memset(szcare, 0, sizeof(szcare));
//memset(shterm_text, 0, sizeof(shterm_text));
//memset(lend_text, 0, sizeof(lend_text));
col = 0;
pDB->Row2Str(NULL, col++, tempinfo->szshtcode, sizeof(tempinfo->szshtcode) - 1);
if (bLoadAll) {
mi.ncode = atoi(tempinfo->szshtcode);
STRCOPYDATASIZE(mi.shtcode, tempinfo->szshtcode);
psm = g_datacenter.GetStockMaster(tempinfo->szshtcode);
if (!psm) {
g_datacenter.m_stockmaster.insert(MYSTOCK_MASTER::value_type(tempinfo->szshtcode, mi));
psm = g_datacenter.GetStockMaster(tempinfo->szshtcode);
psm->uptime = cur_time;
}
}
psm->upitme_diffsecs = pDB->Row2Int(NULL, col++);
psm->si.getcount++;
//if (psm->upitme_diffsecs <= within_secs) {
// rc = 1;
//}
pDB->Row2Str(NULL, col++, psm->lastupdate, sizeof(psm->lastupdate) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szhname, sizeof(tempinfo->szhname) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szupname, sizeof(tempinfo->szupname) - 1);
//UTF8MBS(szupname, psm->upname, sizeof(szupname) - 1);
psm->marketid = pDB->Row2Int(NULL, col++);
pDB->Row2Str(NULL, col++, tempinfo->szmarketgb, sizeof(tempinfo->szmarketgb) - 1);
//UTF8MBS(szmarketgb, psm->marketgb, sizeof(szmarketgb) - 1);
psm->si.capital = pDB->Row2Int(NULL, col++);
psm->si.total = pDB->Row2Int(NULL, col++);
psm->si.listing = pDB->Row2Int64(NULL, col++);
psm->si.abscnt = pDB->Row2Int64(NULL, col++);
psm->si.etf = pDB->Row2Int(NULL, col++);
//서로 마스터에 정보가 없는것 끼리 반대로 로드
pDB->Row2Str(NULL, col++, psm->category, sizeof(psm->category) - 1);
pDB->Row2Str(NULL, col++, psm->si.bu12gubun, sizeof(psm->si.bu12gubun) - 1);
pDB->Row2Str(NULL, col++, psm->si.spac_gubun, sizeof(psm->si.spac_gubun) - 1);
psm->si.per = pDB->Row2Double(NULL, col++);
psm->si.pbrx = pDB->Row2Double(NULL, col++);
psm->si.t_per = pDB->Row2Double(NULL, col++);
psm->si.epsrt = pDB->Row2Double(NULL, col++);
psm->si.bfeps = pDB->Row2Double(NULL, col++);
psm->si.bfoperatingincome = pDB->Row2Int(NULL, col++);
psm->si.bfoperatingincome2 = pDB->Row2Int(NULL, col++);
psm->si.high52w = pDB->Row2Int(NULL, col++);
psm->si.low52w = pDB->Row2Int(NULL, col++);
pDB->Row2Str(NULL, col++, psm->si.high52wdate, sizeof(psm->si.high52wdate) - 1);
pDB->Row2Str(NULL, col++, psm->si.low52wdate, sizeof(psm->si.low52wdate) - 1);
pDB->Row2Str(NULL, col++, psm->si.listdate, sizeof(psm->si.listdate) - 1);
psm->si.daysfromlist = GetDiffDays(atoi(psm->si.listdate), g_conf.m_today);
psm->si.uplmtprice = pDB->Row2Int(NULL, col++);
psm->si.dnlmtprice = pDB->Row2Int(NULL, col++);
psm->si.open = pDB->Row2Int(NULL, col++);
psm->si.high = pDB->Row2Int(NULL, col++);
psm->si.low = pDB->Row2Int(NULL, col++);
psm->si.price = pDB->Row2Int(NULL, col++);
if(psm->si.preclose == 0)
psm->si.preclose = psm->si.preclose1 = psm->si.price;
pDB->Row2Str(NULL, col++, psm->si.dsign, sizeof(psm->si.dsign) - 1);
psm->si.dchange = pDB->Row2Int(NULL, col++);
psm->si.drate = pDB->Row2Double(NULL, col++);
psm->si.jnilvolume = pDB->Row2Int(NULL, col++);
psm->si.todayvolume = pDB->Row2Int(NULL, col++);
pDB->Row2Str(NULL, col++, tempinfo->szlocks , sizeof(tempinfo->szlocks) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szmanage , sizeof(tempinfo->szmanage) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szstop , sizeof(tempinfo->szstop) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szwarn , sizeof(tempinfo->szwarn) - 1);
pDB->Row2Str(NULL, col++, tempinfo->szcare , sizeof(tempinfo->szcare) - 1);
pDB->Row2Str(NULL, col++, tempinfo->shterm_text , sizeof(tempinfo->shterm_text) - 1);
pDB->Row2Str(NULL, col++, tempinfo->lend_text , sizeof(tempinfo->lend_text) - 1);
pDB->Row2Str(NULL, col++, psm->si.szinvestatt, sizeof(psm->si.szinvestatt) - 1);
psm->si.fabsvolrate = pDB->Row2Double(NULL, col++);
psm->si.flistonvolrate = pDB->Row2Double(NULL, col++);
pDB->Row2Str(NULL, col++, tempinfo->insert_id, sizeof(tempinfo->insert_id) - 1);
pDB->Row2Str(NULL, col++, tempinfo->update_id, sizeof(tempinfo->update_id) - 1);
pDB->Row2Str(NULL, col++, tempinfo->update_dt, sizeof(tempinfo->update_dt) - 1);
if (!psm->hname[0]) {
UTF8MBS(tempinfo->szhname, psm->hname, sizeof(tempinfo->szhname) - 1);
mbcscopy(psm->shtname, psm->hname, sizeof(psm->shtname) - 1);
}
if (!psm->upname[0])
UTF8MBS(tempinfo->szupname, psm->upname, sizeof(tempinfo->szupname) - 1);
UTF8MBS(tempinfo->szlocks , psm->si.lock, sizeof(tempinfo->szlocks) - 1);
UTF8MBS(tempinfo->szmanage , psm->si.manage, sizeof(tempinfo->szmanage) - 1);
UTF8MBS(tempinfo->szstop , psm->si.stop, sizeof(tempinfo->szstop) - 1);
UTF8MBS(tempinfo->szwarn , psm->si.warn, sizeof(tempinfo->szwarn) - 1);
UTF8MBS(tempinfo->szcare , psm->si.care, sizeof(tempinfo->szcare) - 1);
UTF8MBS(tempinfo->shterm_text, psm->si.shterm_text, sizeof(tempinfo->shterm_text) - 1);
UTF8MBS(tempinfo->lend_text, psm->si.lend_text, sizeof(tempinfo->lend_text) - 1);
pDB->FastNext(NULL);//m_pDB->MoveNext();
}
}
//free(szquery);
}
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::SaveMasterInfo()
{
int rc = 0;
int rows = 0;
int qlen = 0;
const char* szerror = 0;
char* szquery = NULL;
char* szvalue = NULL;
char* szvalue2 = NULL;
LONG64 dwSaveBegTick = GetTickCount64();
string strlog;
CMySQLDB* pDB = CDBPool::GetLiveDB();
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
MYSTOCK_LIST* pStockList = &g_datacenter.m_stocklist;
MYSTOCK_LIST::HASHMAP* pMap = pStockList->GetHashMap();
MYSTOCK_LIST::HASHMAP::iterator it;
HMSiseInfo* pSItem;
HMStockInfo* psm;
int doit = 0;
char szhname[86] = { 0, };
char szupname[86] = { 0, };
char szmarketgb[20] = { 0, };
char szlocks[40] = { 0, };
char szmanage[40] = { 0, };
char szstop[40] = { 0, };
char szwarn[40] = { 0, };
char szcare[40] = { 0, };
char shterm_text[40] = { 0, };
char lend_text[40] = { 0, };
char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm
int vlen = 0, vlen1 = 0;
int vlen2 = 0, vlen3 = 0;
int count = pMap->size();
sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday,16);
szquery = (char*)malloc(10000);
if (!szquery) goto exit_save;
szquery[0] = NULL;
szvalue = (char*)malloc(6096);
if (!szvalue) goto exit_save;
szvalue[0] = NULL;
szvalue2 = (char*)malloc(6096);
if (!szvalue2) goto exit_save;
szvalue2[0] = NULL;
qlen = sprintf(szquery, //당일시간보다 이전이면 삭제
"DELETE FROM homidb.t_stockinfo "
"WHERE update_ts < FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(\'%s\', '%%Y%%m%%d%%H')));", szupdate_dt);
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
#if 1
SQLiteCpp sqlite;
bool rc2;
int qlen;
//char szquery[2048];
//char szupdate_dt[12 + 1];//갱신시간 yyyymmddhhmm
string strquery;
CHAR szdatafile[MAX_PATH] = { 0, };
sprintf(szdatafile, "%s\\data\\Homi.db", g_conf.m_commondir);
strlog = (string)szdatafile;
rc2 = sqlite.OpenDB(szdatafile);
//sprintf(szupdate_dt, "%08d%02d", g_conf.m_bizday, 16);
qlen = sprintf(szquery, //당일시간보다 이전이면 삭제
"DELETE FROM t_stockinfo "
"WHERE STRFTIME('%%Y%%m%%d',update_ts) < \'%s\';\0", szupdate_dt);
rc2 = sqlite.queryexec(szquery);
#endif
pStockList->Lock();
for (it = pMap->begin(); it != pMap->end(); it++)
{
pSItem = &it->second;
psm = pSItem->psm;
//모두저장 20231019
doit = 1;
#if 0
wstring strUni = CA2W(psm->hname);
string strUTF8 = CW2A(strUni.c_str(), CP_UTF8).m_psz;
#endif
//빠른디버깅을 위해 종목마스터 선별 통과한 것만(시세오류있어도)
if ((psm->noerror == 0 || psm->noerror > (int)ENUM_SISEERROR::TRASHSTOCK)
&& psm->si.abscnt > 0) {
doit = 1;
}
if (doit == 0)
continue;
//////////////////////////////////////////////////////////////////////////
MBS2UTF8(psm->hname, szhname, sizeof(szhname) - 1);
MBS2UTF8(psm->upname, szupname, sizeof(szupname) - 1);
MBS2UTF8(psm->marketgb, szmarketgb, sizeof(szmarketgb) - 1);
MBS2UTF8(psm->si.lock, szlocks, sizeof(szlocks) - 1);
MBS2UTF8(psm->si.manage, szmanage, sizeof(szmanage) - 1);
MBS2UTF8(psm->si.stop, szstop, sizeof(szstop) - 1);
MBS2UTF8(psm->si.warn, szwarn, sizeof(szwarn) - 1);
MBS2UTF8(psm->si.care, szcare, sizeof(szcare) - 1);
MBS2UTF8(psm->si.shterm_text, shterm_text, sizeof(shterm_text) - 1);
MBS2UTF8(psm->si.lend_text, lend_text, sizeof(lend_text) - 1);
// When a duplicate value is found, then MySQL will perform an update instead of an insert.
// ON DUPLICATE KEY UPDATE 데이터 추가되면 응답 값을 1로 리턴하고,업데이트되면 응답 값을 2로 리턴한다.
vlen1 = sprintf(szvalue + vlen,
"%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"
" \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"
" \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"
" \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"
" \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',NOW())\r\n",
vlen > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,
psm->marketid, szmarketgb, psm->si.etf, psm->category,
psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,
psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,
psm->si.t_per, psm->si.epsrt, psm->si.bfeps,
psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,
psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,
psm->si.uplmtprice, psm->si.dnlmtprice,
psm->si.open, psm->si.high, psm->si.low, psm->si.price,
psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,
szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,
psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);
vlen += vlen1;
vlen3 = sprintf(szvalue2 + vlen2,
//"%s(?,? ,? ,?,?,?,?,?,?,\r\n"
//" ?,?,?,?,?,? ,?,?,?,?,\r\n"
//" ?,?,?,?,?,?,?,?,?,\r\n"
//" ? ,? ? ? ? ? ? ? ? ? ?,\r\n"
//" ?, ?, ?,? ,? ,? ,? ,? ,? ,? ,?,NOW())\r\n",
"%s(\'%08d\',\'%s\' ,\'%s\' ,\'%s\',\'%s\' ,\'%d\' ,\'%s\' ,\'%d\',\'%s\',\r\n"
" \'%s\',\'%s\' ,\'%ld\' ,\'%ld\' ,\'%lld\' ,\'%lld\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\' ,\'%3.2f\',\r\n"
" \'%3.2f\',\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%ld\',\r\n"
" \'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%ld\' ,\'%s\' ,\'%ld\' ,\'%3.2f\' ,\'%lu\' ,\'%lu\',\r\n"
" \'%s\', \'%s\', \'%s\',\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%s\' ,\'%3.2f\' ,\'%3.2f\' , \'%s\',datetime('now','localtime'))\r\n",
vlen2 > 0 ? ",\r\n" : "", g_conf.m_today, psm->shtcode, psm->excode, szhname, szupname,
psm->marketid, szmarketgb, psm->si.etf, psm->category,
psm->si.bu12gubun, psm->si.spac_gubun, psm->si.capital, psm->si.total,
psm->si.listing, psm->si.abscnt, psm->si.per, psm->si.pbrx,
psm->si.t_per, psm->si.epsrt, psm->si.bfeps,
psm->si.bfoperatingincome, psm->si.bfoperatingincome2, psm->si.high52w, psm->si.low52w,
psm->si.high52wdate, psm->si.low52wdate, psm->si.listdate, psm->si.preclose,
psm->si.uplmtprice, psm->si.dnlmtprice,
psm->si.open, psm->si.high, psm->si.low, psm->si.price,
psm->si.dsign, psm->si.dchange, psm->si.drate, psm->si.jnilvolume, psm->si.todayvolume,
szlocks, szmanage, szstop, szwarn, szcare, shterm_text, lend_text, psm->si.szinvestatt,
psm->si.fabsvolrate, psm->si.flistonvolrate, g_conf.m_lastname);
vlen2 += vlen3;
if ((rc + 1) % 10 == 0 || rc == count - 1 || vlen >= 5800) {
qlen = sprintf(szquery,
"INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
" bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
" bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
" uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
" `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
" VALUES\r\n"
"%s\r\n"
"ON DUPLICATE KEY UPDATE\r\n"
"sdate = VALUES(sdate),shtcode = VALUES(shtcode),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"
"marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"
"bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"
"low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"
"uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"
"closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"
"volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"
"lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"
"update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);
rows += pDB->ExecuteQuery(szquery);
//memset(szvalue, 0, sizeof(szvalue));
#if 1 //simple SQL
qlen = sprintf(szquery,
"INSERT INTO t_stockinfo (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
" bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
" bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
" uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
" `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
" VALUES\r\n"
"%s\r\n", szvalue2);
#else
//https://stackoverflow.com/questions/2717590/sqlite-insert-on-duplicate-key-update-upsert
qlen = sprintf(szquery,
"INSERT INTO `homidb`.`t_stockinfo` (sdate, shtcode, excode, hname, upname,marketid, marketgb, etf, category,\r\n"
" bu12gubun, spac_gubun, capital, sigatotal, listing, abscnt, per, pbrx, t_per, epsrt,\r\n"
" bfeps, bfoperatingincome, bfoperatingincome2, high52w, low52w, high52wdate, low52wdate, listdate, preclose,\r\n"
" uplmtprice, dnlmtprice, openprice, high, low, closeprice, dsign, dchange, drate, jnilvolume, volume,\r\n"
" `locks`, manage, stops, warn, care, shterm_text, lend_text,investatt, fprevolumerate, fprevolumelistrate,insert_id, update_ts) \r\n"
" VALUES\r\n"
"%s\r\n"
"ON CONFLICT(shtcode) DO UPDATE SET \r\n"
"sdate = VALUES(sdate),excode = VALUES(excode),hname = VALUES(hname),upname = VALUES(upname),\r\n"
"marketid =VALUES(marketid), etf=VALUES(etf), category=VALUES(category),\r\n"
"bfeps=VALUES(bfeps), bfoperatingincome=VALUES(bfoperatingincome), bfoperatingincome2=VALUES(bfoperatingincome2), high52w =VALUES(high52w),\r\n"
"low52w=VALUES(low52w), high52wdate=VALUES(high52wdate), low52wdate=VALUES(low52wdate),listdate = VALUES(listdate),preclose = VALUES(preclose),\r\n"
"uplmtprice=VALUES(uplmtprice),dnlmtprice=VALUES(dnlmtprice),openprice=VALUES(openprice),high=VALUES(high),low=VALUES(low),\r\n"
"closeprice=VALUES(closeprice),dsign=VALUES(dsign),dchange=VALUES(dchange),drate=VALUES(drate),jnilvolume=VALUES(jnilvolume),\r\n"
"volume=VALUES(volume),locks=VALUES(locks),manage=VALUES(manage),stops=VALUES(stops),warn=VALUES(warn),care=VALUES(care),shterm_text=VALUES(shterm_text),\r\n"
"lend_text=VALUES(lend_text),investatt=VALUES(investatt),fprevolumerate=VALUES(fprevolumerate),fprevolumelistrate=VALUES(fprevolumelistrate),\r\n"
"update_id=\'%s\',update_ts=VALUES(update_ts);\r\n", szvalue, g_conf.m_lastname);
#endif
int errcode = 0;
rc2 = sqlite.queryexec(szquery);
if (rc2 == 0)
errcode = sqlite.errcode();
szvalue2[0] = NULL;
vlen = 0;
vlen2 = 0;
}
//////////////////////////////////////////////////////////////////////////
rc++;
}
pStockList->Unlock();
if(rc > 0)
pDB->Commit();
#if 1
rc = sqlite.affected_rows();
rc2 = sqlite.CloseDB();
#endif
}
exit_save:
if (pDB)
CDBPool::FreeLiveDB(pDB);
if(szquery)
free(szquery);
if (szvalue)
free(szvalue);
if (rc > 0) {
CString strmsg;
strmsg.Format("종목정보|저장(%ld) SQLite>%s(%d초)",
rc, strlog.c_str(), (GetTickCount64() - dwSaveBegTick) / 1000);
AddToMainLog(strmsg, 0x03);
}
return rc;
}
//업종명갱신
int BizCommDB::UpdateMasterInfo(HMStockInfo* psm, CMySQLDB* pDB2)
{
char szquery[4096] = { 0, };
int rows = 0;
int qlen = 0;
const char* szerror = 0;
CMySQLDB* pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
wstring strUni = CA2W(psm->hname);
string strUTF8 = CW2A(strUni.c_str(), CP_UTF8).m_psz;
char szupname[86] = { 0, };
char szlocks[40];
char szmanage[40];
char szstop[40];
char szwarn[40];
char szcare[40];
char shterm_text[40];
char lend_text[40];
MBS2UTF8(psm->upname, szupname, sizeof(szupname) - 1);
MBS2UTF8(psm->si.lock, szlocks, sizeof(szlocks) - 1);
MBS2UTF8(psm->si.manage, szmanage, sizeof(szmanage) - 1);
MBS2UTF8(psm->si.stop, szstop, sizeof(szstop) - 1);
MBS2UTF8(psm->si.warn, szwarn, sizeof(szwarn) - 1);
MBS2UTF8(psm->si.care, szcare, sizeof(szcare) - 1);
MBS2UTF8(psm->si.shterm_text, shterm_text, sizeof(shterm_text) - 1);
MBS2UTF8(psm->si.lend_text, lend_text, sizeof(lend_text) - 1);
//최근 업데이트 일자 얻어오기
qlen = sprintf(szquery,
"SELECT COUNT(shtcode) FROM `homidb`.`t_stockinfo` \r\n"
"WHERE shtcode = \'%s\';",
psm->shtcode);
int found = 0;
rows = pDB->QuerySelect(NULL, szquery, qlen);//%포맷열 때문에 SelectEx 사용금지(오류남)
if (rows > 0) {
//pDB->Row2Str(NULL, col++, szdbupday, sizeof(szdbupday));
found = pDB->Row2Int(NULL, 0);
}
if (found > 0) {
qlen = sprintf(szquery,
"UPDATE `homidb`.`t_stockinfo` SET \r\n"
" sdate = \'%08d\',upname = \'%s\', \r\n"
" `locks` = \'%s\',manage = \'%s\',stops = \'%s\',warn = \'%s\',care = \'%s\', \r\n"
" update_id = \'%s\',update_ts = FROM_UNIXTIME(unix_timestamp()) \r\n"
"WHERE shtcode = \'%s\';\r\n",
g_conf.m_today, szupname,
szlocks, szmanage, szstop, szwarn, szcare,
g_conf.m_lastname, psm->shtcode);
rows = pDB->ExecuteQuery(szquery);
if (rows < 0) {
szerror = pDB->geterrmsg();
if (szerror) {
OutputDebugString(szerror);
OutputDebugString("\r\n");
}
}
#ifdef USE_EBEST_API
qlen = sprintf(szquery,
"UPDATE `homidb`.`t_stockinfo` SET \r\n"
" bu12gubun = \'%s\',spac_gubun = \'%s\',shterm_text = \'%s\',lend_text = \'%s\', \r\n"
" update_id = \'%s\',update_ts = FROM_UNIXTIME(unix_timestamp()) \r\n"
"WHERE shtcode = \'%s\';\r\n",
psm->si.bu12gubun, psm->si.spac_gubun, shterm_text, lend_text,
g_conf.m_lastname, psm->shtcode);
#else
//category[3]; //코스피 200 1 'A'
//szinvestatt[2]; //Y = 투자유의 (케이프만)
qlen = sprintf(szquery,
"UPDATE `homidb`.`t_stockinfo` SET \r\n"
" category = \'%s\', investatt = \'%s\', \r\n"
" update_id = \'%s\',update_ts = FROM_UNIXTIME(unix_timestamp()) \r\n"
"WHERE shtcode = \'%s\';\r\n",
psm->category, psm->si.szinvestatt,
g_conf.m_lastname, psm->shtcode);
#endif
rows = pDB->ExecuteQuery(szquery);
if (rows < 0) {
szerror = pDB->geterrmsg();
if (szerror) {
OutputDebugString(szerror);
OutputDebugString("\r\n");
}
}
pDB->Commit();
}
}
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
//업데이트 시간을 1일전으로 갱신-종목선별시 새로 서버에서 받게
int BizCommDB::ResetMasterInfo(CMySQLDB *pDB2)
{
char szquery[4096] = { 0, };
int rows = 0;
int qlen = 0;
const char *szerror = 0;
CMySQLDB *pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
qlen = sprintf(szquery,
"UPDATE `homidb`.`t_stockinfo` SET \r\n"
" update_ts = TIMESTAMP(DATE_SUB(update_ts,INTERVAL 1 DAY)) \r\n");
rows = pDB->ExecuteQuery(szquery);
if (rows < 0) {
szerror = pDB->geterrmsg();
if (szerror) {
OutputDebugString(szerror);
OutputDebugString("\r\n");
}
}
pDB->Commit();
}
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::DeleteSonIkHist(int yyyymmdd)
{
int rows = 0;
int qlen;
char szquery[1024] = { 0, };
CMySQLDB* pDB = CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
qlen = sprintf(szquery, //무조건 지우고 61일치 새로받기
"DELETE FROM homidb.t_dailysonik \r\n"
" WHERE sdate = DATE_FORMAT(\'%08d\', '%%Y%%m%%d'); \r\n", yyyymmdd);
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::InsertSonIkHist(STSonIkInfo *pSI, bool update)
{
int rows = 0;
int qlen;
int seqno = 1;
char szquery[2048] = { 0, };
char szmarketid[8] = "";
char szbiddate[9];
char szbidtime[9];
char szasktime[9];
char szbidmode[2] = "M";
char shtname[86] = { 0, };
//CDBPool *pDBExec = CDBPool::GetInstance();
CMySQLDB *pDB = CDBPool::GetLiveDB();
if (pDB && pDB->IsConnected() && CDBPool::GetConnected()) {
if (update) {
if (pSI->qty >= 5) {
qlen = sprintf(szquery,
"UPDATE homidb.t_dailysonik \r\n"
"SET qty=\'%ld\',incomingkrw=\'%ld\',\'%4.2f\' \r\n"
"WHERE sdate=\'%08d\' AND pgm_id=\'%s\' AND user_id=\'%s\' AND shtcode=\'%s\' AND seqno=\'%ld\';\r\n",
pSI->qty, pSI->shsonik, pSI->sonikrate,
g_conf.m_today, g_conf.m_lastname, g_conf.account.userid, pSI->shtcode, pSI->dblastseqno);
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
if (rows > 0) {
}
}
}
else {
if (pSI->bidwho <= (byte)ENUM_BIDWHO::BIDWHO_HTS)
szbidmode[0] = 'H';
else if (pSI->bidwho == (byte)ENUM_BIDWHO::BIDWHO_MAN)
szbidmode[0] = 'C';
else if (pSI->bidwho == (byte)ENUM_BIDWHO::BIDWHO_APP)
szbidmode[0] = 'A';
if (pSI->marketid == (int)ENUM_MARKETID::KOSDAQ)
sprintf(szmarketid, "KSD");
else if (pSI->marketid == (int)ENUM_MARKETID::KOSPI)
sprintf(szmarketid, "KSP");
else if (pSI->marketid == (int)ENUM_MARKETID::KOSPI200)
sprintf(szmarketid, "KSP200");
sprintf(szbiddate, "%04d%02d%02d",
pSI->bidtime.wYear, pSI->bidtime.wMonth, pSI->bidtime.wDay);
sprintf(szbidtime, "%02d%02d%02d",
pSI->bidtime.wHour, pSI->bidtime.wMinute, pSI->bidtime.wSecond);
sprintf(szasktime, "%02d%02d%02d", pSI->asktime.wHour, pSI->asktime.wMinute, pSI->asktime.wSecond);
qlen = sprintf(szquery,
"SELECT IFNULL(MAX(seqno),0)+1 AS nextseq FROM t_dailysonik \r\n"
"WHERE sdate = \'%08d\' AND pgm_id=\'%s\' AND user_id=\'%s\' AND account=\'%s\';\r\n",
g_conf.m_today, g_conf.m_lastname, g_conf.account.userid, g_conf.account.account);
rows = pDB->QuerySelect(NULL, szquery, qlen);//%포맷열 때문에 SelectEx 사용금지(오류남)
if (rows > 0) {
//pDB->Row2Str(NULL, col++, szdbupday, sizeof(szdbupday));
seqno = pDB->Row2Int(NULL, 0);
}
//wstring strUni = CA2W(pSI->shtname); //CA2W("멀티바이트를 유니코드로 변환");
//string strMulti = CW2A(L"접속");//유니코드를 멀티바이트로 변환");
// //string strUTF8 = CW2A(L"유니코드를 UTF8로변환", CP_UTF8);
//string strUTF8 = CW2A(strUni, CP_UTF8).m_psz;
//string strhname = MultiByteToUtf8(pSI->shtname);
char hostname[128] = { 0, };
int result;
result = gethostname(hostname, 127);
MBS2UTF8(pSI->shtname, shtname, sizeof(shtname) - 1);
// When a duplicate value is found, then MySQL will perform an update instead of an insert.
int slen = sprintf(szquery, "INSERT INTO homidb.t_dailysonik (sdate,shtcode,shtname,marketid,\r\n"
"ask_ordno,biddate,bidtime,asktime,qty,bidprice,askprice,curprice,preprice,bidamt,incomingkrw,incomingrate, \r\n"
"fsrate,drate,bidmode,hps,pgm_id,user_id,account,hostname,update_ts)\r\n"
"VALUES(\'%08d\',\'%s\',\'%s\',\'%s\', \r\n"
"\'%ld\',\'%s\',\'%s\',\'%s\',\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%ld\',\'%4.2f\', \r\n"
"\'%4.2f\',\'%5.2f\',\'%s\',\'%ld\',\'%s\',\'%s\',\'%s\',\'%s\', NOW()) \r\n",
g_conf.m_today, pSI->shtcode, shtname, szmarketid,
pSI->ordno, szbiddate, szbidtime, szasktime, pSI->qty, pSI->bidprice, pSI->askprice, pSI->curprice,
pSI->preprice, pSI->mamt, pSI->shsonik, pSI->sonikrate,
pSI->fsrate, pSI->drate, szbidmode, pSI->hps, g_conf.m_lastname,
g_conf.account.userid, g_conf.account.account, hostname);// MultiByteToUtf8("접속"));
rows = pDB->ExecuteQuery(szquery);
if (rows > 0)
pSI->dblastseqno = seqno;
else {
const char *perror = pDB->geterrmsg();
OutputDebugString(perror);
}
pDB->Commit();
}
}
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::SaveTimeLine()
{
int rows = 0;
int qlen;
char szquery[1024] = { 0, };
CMySQLDB* pDB = CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
long jungsankrw = g_datacenter.m_dashboard.tot_jungsan;
qlen = sprintf(szquery,
"INSERT INTO homidb.t_timeline (sdate,stime,pname,uid,tot_invest,cur_asset,tot_bidamt,\n"
" tot_deposit,drawamt,tot_shsonik,jango_evlamt,tot_jungsan,jango_count,tot_bidreqtr,tot_ordertr)\n"
" VALUES(\'%08d\',\'%06d\',\'%s\',\'%s\',\'%lu\',\'%lu\',\'%lu\',\'%d\',\'%d\',\'%d\',\'%d\',\'%d\',\'%d\',\'%d\',\'%d\')",
g_conf.m_today, g_conf.m_hhmmss, g_conf.m_lastname, g_conf.account.userid,
g_datacenter.m_dashboard.tot_invest, g_datacenter.m_dashboard.cur_asset,
g_datacenter.m_dashboard.tot_bidamt,//총매입금액
g_datacenter.m_dashboard.cashableamt,//예수금(증거금)
g_datacenter.m_dashboard.drawamt,//출금가능
g_datacenter.m_dashboard.shsonik,//실현손익
g_datacenter.m_dashboard.jango_evlamt,//잔고 평가손익
g_datacenter.m_dashboard.tot_jungsan,//정산
g_datacenter.m_dashboard.jango_count,
g_datacenter.m_dashboard.tot_bidreqcount,
g_datacenter.m_dashboard.tot_ordercount);// "connect");// MultiByteToUtf8("접속"));
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::SaveChatting(char *msg,char *szfile)
{
int rows = 0;
int qlen;
char szalias[80];
char szmsg[256];
char szquery[1024] = { 0, };
CMySQLDB* pDB = CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
long jungsankrw = g_datacenter.m_dashboard.tot_jungsan;
wstring strUni = CA2W(g_conf.account.alias); //CA2W("멀티바이트를 유니코드로 변환");
string strMulti = CW2A(L"접속");//유니코드를 멀티바이트로 변환");
//string strUTF8 = CW2A(L"유니코드를 UTF8로변환", CP_UTF8);
//string strUTF8 = CW2A(L"접속", CP_UTF8).m_psz;
MBS2UTF8(g_conf.account.alias, szalias, sizeof(szalias) - 1);
MBS2UTF8(msg, szmsg, sizeof(szmsg) - 1);
qlen = sprintf(szquery,
"INSERT INTO homidb.t_minichat (sdate,stime,pname,uid,alias,msg,attach)\n"
" VALUES(\'%08d\',\'%06d\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')",
g_conf.m_today, g_conf.m_hhmmss, g_conf.m_lastname, g_conf.account.userid,
szalias, szmsg, szfile);// "connect");// MultiByteToUtf8("접속"));
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
CDBPool::FreeLiveDB(pDB);
return rows;
}
int BizCommDB::ReadChatting(ST_MINICHAT *pchat,int yyyymmdd,int hhmmss,int maxcount)
{
int rows = 0,col = 0;
int reads = 0;
int qlen;
char szquery[1024] = { 0, };
ST_MINICHAT readchat;
CMySQLDB* pDB = CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
long jungsankrw = g_datacenter.m_dashboard.tot_jungsan;
qlen = sprintf(szquery,
"SELECT sdate,stime,pname,uid,alias,msg,attach FROM homidb.t_minichat\n"
" WHERE sdate >=\'%08d\' AND stime > \'%06d\'",
yyyymmdd, hhmmss);// "connect");// MultiByteToUtf8("접속"));
rows = pDB->QuerySelect(NULL, szquery, qlen);//%포맷열 때문에 SelectEx 사용금지(오류남)
if (rows > 0) {
char szalias[80];
char szmsg[256];
for (register int i = 0; i < rows; i++) {
memset(&readchat, 0, sizeof(readchat));
szalias[0] = NULL;
szmsg[0] = NULL;
col = 0;
readchat.yyyymmdd = pDB->Row2Int(NULL, col++);
readchat.hhmmss = pDB->Row2Int(NULL, col++);
pDB->Row2Str(NULL, col++, readchat.pname, sizeof(readchat.pname) - 1);
pDB->Row2Str(NULL, col++, readchat.uid, sizeof(readchat.uid) - 1);
pDB->Row2Str(NULL, col++, readchat.alias, sizeof(readchat.alias) - 1);
pDB->Row2Str(NULL, col++, readchat.msg, sizeof(readchat.msg) - 1);
pDB->Row2Str(NULL, col++, readchat.attach, sizeof(readchat.attach) - 1);
UTF8MBS(readchat.alias, szalias, sizeof(szalias) - 1);
UTF8MBS(readchat.msg, szmsg, sizeof(szmsg) - 1);
strncpy(readchat.alias, szalias, sizeof(readchat.alias) - 1);
strncpy(readchat.msg, szmsg, sizeof(readchat.msg) - 1);
pDB->FastNext(NULL);
memcpy(pchat++, &readchat, sizeof(readchat));
reads++;
if (reads >= maxcount || i >= maxcount-1)
break;
}
}
CDBPool::FreeLiveDB(pDB);
return reads;
}
int BizCommDB::DeleteTimeLine(CMySQLDB* pDB2,int pastdays)
{
int rows = 0;
int qlen;
char szquery[1024] = { 0, };
CMySQLDB* pDB = (pDB2) ? pDB2 : CDBPool::GetLiveDB();
if (!pDB || !pDB->IsConnected())
return 0;
qlen = sprintf(szquery, //pastdays일치 무조건 삭제
"DELETE FROM homidb.t_timeline \r\n"
"WHERE sdate <= DATE_FORMAT(DATE_SUB(\'%08d\', INTERVAL %d DAY), '%%Y%%m%%d'); \r\n"
, g_conf.m_today, pastdays);
rows = pDB->ExecuteQuery(szquery);
pDB->Commit();
if (!pDB2)
CDBPool::FreeLiveDB(pDB);
return rows;
}