// 모든 크롤러 정보 로드 하기
public bool SelectAllChannelInfos()
{
try
{
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CrawlerInfoManager.Instance.method_, CrawlerInfoManager.Instance.dbip_, CrawlerInfoManager.Instance.dbport_, CrawlerInfoManager.Instance.dbname_
, CrawlerInfoManager.Instance.dbaccount_, CrawlerInfoManager.Instance.dbpw_, CrawlerInfoManager.Instance.sshhostname_, CrawlerInfoManager.Instance.sshuser_
, CrawlerInfoManager.Instance.sshpw_);
MySqlDataReader datareader = pMySqlDB.call_proc("sp_select_All_Crawler_Info", null);
while (datareader.Read())
{
LQCrawlerInfo pInfo = new LQCrawlerInfo();
pInfo.nIdx_ = Convert.ToInt32(datareader["idx"]);
pInfo.Channel_Idx_ = Convert.ToInt32(datareader["Channel_Idx"]);
pInfo.ChannelName_ = Convert.ToString(datareader["Channel_Name"]);
pInfo.PartnerSeq_ = Convert.ToInt32(datareader["PartnerSeq"]);
pInfo.PartnerName_ = Convert.ToString(datareader["PartnerName"]);
// 권리사 (2014-07-30) . 추가
pInfo.AuthoritySeq_ = Convert.ToInt32(datareader["AuthoritySeq"]);
pInfo.AuthoriryName_ = Convert.ToString(datareader["AuthorityName"]);
pInfo.MainUrl_ = Convert.ToString(datareader["MainUrl"]); // 메인 URL
pInfo.LoginIDTAG_ = Convert.ToString(datareader["LoginIDTAG"]);
pInfo.LoginPWTAG_ = Convert.ToString(datareader["LoginPWTAG"]);
pInfo.LoginUrl_ = Convert.ToString(datareader["LoginUrl"]);
pInfo.LoginParam_ = Convert.ToString(datareader["LoginParam"]); // 로그인 셋팅값
pInfo.LoginID_ = Convert.ToString(datareader["LoginID"]); // 로그인 아이디
pInfo.LoginPW_ = Convert.ToString(datareader["LoginPW"]); // 로그인 암호
pInfo.LoginMethod_ = Convert.ToString(datareader["LoginMethod"]); // 로그인 방식
pInfo.LoginEvent_ = Convert.ToString(datareader["LoginEvent"]); // 로그인 버튼 이벤트
pInfo.LoginCheck_ = Convert.ToString(datareader["LoginCheck"]);
pInfo.LoginType_ = Convert.ToChar(datareader["LoginType"]);
pInfo.ExcelDownUrl_ = Convert.ToString(datareader["ExcelDownUrl"]); // 엑셀 다운로드 URL
pInfo.ExcelDownParameter_ = Convert.ToString(datareader["ExcelDownParameter"]);
pInfo.ExcelDownMethod_ = Convert.ToString(datareader["ExcelDownMethod"]); // 엑셀 다운로드 방식
pInfo.ExcelDownRule_ = Convert.ToString(datareader["ExcelDownRule"]);
pInfo.UseGoodsUrl_ = Convert.ToString(datareader["UseGoodsUrl"]);
pInfo.UseGoodsParam_ = Convert.ToString(datareader["UseGoodsParam"]);
pInfo.UseGoodsCheck_ = Convert.ToString(datareader["UseGoodsCheck"]);
pInfo.UseGoodsRule_ = Convert.ToString(datareader["UseGoodsRule"]);
pInfo.UseUserUrl_ = Convert.ToString(datareader["UseUserUrl"]);
pInfo.UseUserParam_ = Convert.ToString(datareader["UseUserParam"]);
pInfo.UseUserCheck_ = Convert.ToString(datareader["UseUserCheck"]);
pInfo.NUseGoodsUrl_ = Convert.ToString(datareader["NUseGoodsUrl"]);
pInfo.NUseGoodsParam_ = Convert.ToString(datareader["NUseGoodsParam"]);
pInfo.NUseGoodsCheck_ = Convert.ToString(datareader["NUseGoodsCheck"]);
pInfo.NUseGoodsRule_ = Convert.ToString(datareader["NUseGoodsRule"]);
pInfo.NUseUserUrl_ = Convert.ToString(datareader["NUseUserUrl"]);
pInfo.NUseUserParam_ = Convert.ToString(datareader["NUseUserParam"]);
pInfo.NUseUserCheck_ = Convert.ToString(datareader["NUseUserCheck"]);
pInfo.RUseUserUrl_ = Convert.ToString(datareader["RUseUserUrl"]);
pInfo.RUseUserParam_ = Convert.ToString(datareader["RUseUserParam"]);
pInfo.RUseUserCheck_ = Convert.ToString(datareader["RUseUserCheck"]);
pInfo.ExData_Start_ = Convert.ToInt32(datareader["ExData_Start"]);
pInfo.ExData_Coupncode_ = Convert.ToInt32(datareader["ExData_Coupncode"]);
pInfo.ExData_Buydate_ = Convert.ToInt32(datareader["ExData_Buydate"]);
pInfo.ExData_Option_ = Convert.ToInt32(datareader["ExData_Option"]);
pInfo.ExData_Cancel_ = Convert.ToInt32(datareader["ExData_Cancel"]);
pInfo.ExData_Use_ = Convert.ToInt32(datareader["ExData_Use"]);
pInfo.ExData_Buyer_ = Convert.ToInt32(datareader["ExData_Buyer"]);
pInfo.ExData_Count_ = Convert.ToInt32(datareader["ExData_Count"]);
pInfo.ExData_Buyphone_ = Convert.ToInt32(datareader["ExData_Buyphone"]);
pInfo.ExData_Price_ = Convert.ToInt32(datareader["ExData_Price"]);
pInfo.ExData_UseCheck_ = Convert.ToString(datareader["ExData_UseCheck"]);
pInfo.ExData_CancelCheck_ = Convert.ToString(datareader["ExData_CancelCheck"]);
CrawlerInfoList_.Add(pInfo.AuthoritySeq_, pInfo);
}
datareader.Close();
datareader.Dispose();
datareader = null;
}
catch (System.Exception ex)
{
LogManager.Instance.Log(string.Format("채널정보 로드 실패 {0}",ex.Message));
return false;
}
return true;
}
private void button_DBInsert_Click(object sender, EventArgs e)
{
if (comboBox_SelectAuthor.SelectedIndex <= 0)
{
MessageBox.Show("권리사를 선택 해주세요.");
return;
}
if (comboBox_SelectChannel.SelectedIndex <= 0)
{
MessageBox.Show("채널을 선택 해주세요.");
return;
}
if (comboBox_ExcelFrom.SelectedIndex <= 0)
{
MessageBox.Show("엑셀 폼을 선택해 주세요.");
return;
}
LQCrawlerBase pLQCrawlerBase = CrawlerManager.Instance.GetCrawler();
LQCrawlerInfo pLQCrawlerInfo = CrawlerManager.Instance.GetCrawlerInfo();
if (pLQCrawlerBase == null || pLQCrawlerInfo == null)
{
MessageBox.Show("크롤러가 생성되지 않았습니다.");
return;
}
// 상품 정보 읽어오기
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CrawlerInfoManager.Instance.method_, CrawlerInfoManager.Instance.dbip_, CrawlerInfoManager.Instance.dbport_, CrawlerInfoManager.Instance.dbname_
, CrawlerInfoManager.Instance.dbaccount_, CrawlerInfoManager.Instance.dbpw_, CrawlerInfoManager.Instance.sshhostname_
, CrawlerInfoManager.Instance.sshuser_, CrawlerInfoManager.Instance.sshpw_);
DBFunctions.SelectStateTable(pMySqlDB);
DBFunctions.GetGoodsTable(pMySqlDB, pLQCrawlerInfo.Channel_Idx_, pLQCrawlerInfo.AuthoritySeq_);
Dictionary<Int32, ChannelGoodInfo> pGoodInfoList = CrawlerManager.Instance.GetGoodsInfo();
DBFunctions.Select_tblOrder(pMySqlDB, pLQCrawlerInfo.Channel_Idx_);
DBFunctions.Select_tblOrderWr(pMySqlDB, pLQCrawlerInfo.Channel_Idx_);
pLQCrawlerBase.Login();
pLQCrawlerBase.Combine_DB_And_Excel(false);
pLQCrawlerBase.Process_RefundData(pMySqlDB);
pLQCrawlerBase.Process_ExpiredData();
// 채널에 상품처리// 채널에 취소처리 // 채널에 반품처리
pLQCrawlerBase.Process_Use_Cancel_Refund();
// DB 에 처리
SqlHelper pMySqlDB2 = new SqlHelper();
pMySqlDB2.Connect(CrawlerInfoManager.Instance.method_, CrawlerInfoManager.Instance.dbip_, CrawlerInfoManager.Instance.dbport_, CrawlerInfoManager.Instance.dbname_
, CrawlerInfoManager.Instance.dbaccount_, CrawlerInfoManager.Instance.dbpw_, CrawlerInfoManager.Instance.sshhostname_
, CrawlerInfoManager.Instance.sshuser_, CrawlerInfoManager.Instance.sshpw_);
pLQCrawlerBase.Process_DB(pMySqlDB2);
if (pMySqlDB2.Close() == false)
{
LogManager.Instance.Log("<<< 삭제 실패 2 >>>");
}
pMySqlDB2 = null;
MessageBox.Show("크롤링이 끝났습니다.");
}
// 주문정보 잘못된것 정정하는 프로시저
public bool DB_UpdateData_FixUp()
{
try
{
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CINIManager.Instance.method_, CINIManager.Instance.dbip_, CINIManager.Instance.dbport_, CINIManager.Instance.dbname_
, CINIManager.Instance.dbaccount_, CINIManager.Instance.dbpw_, CINIManager.Instance.sshhostname_
, CINIManager.Instance.sshuser_, CINIManager.Instance.sshpw_);
ProcessStateManager.Instance.NeedDBProcessCount_ = DBProccess_List_.Count;
foreach (var pData in DBProccess_List_)
{
COrderData pOrder = pData.Value;
DBInterface.Update_OrderInfo_FixUP(pMySqlDB, (Int32)pOrder.seq_, pOrder.State_);
ProcessStateManager.Instance.CurDBProcessCount_++;
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error DB_UpdateData_FixUp {0}", ex.Message));
return true;
}
return true;
}
// DB 에 데이터 Insert
public bool DB_InsertData()
{
try
{
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CINIManager.Instance.method_, CINIManager.Instance.dbip_, CINIManager.Instance.dbport_, CINIManager.Instance.dbname_
, CINIManager.Instance.dbaccount_, CINIManager.Instance.dbpw_, CINIManager.Instance.sshhostname_
, CINIManager.Instance.sshuser_, CINIManager.Instance.sshpw_);
ProcessStateManager.Instance.NeedDBProcessCount_ = DBProccess_List_.Count;
Int32 nTempSeq = 0;
Int32 nStartSeq = 0;
Int32 nEndSeq = 0;
String nTempPinCode = "";
foreach (var pData in DBProccess_List_)
{
COrderData pOrder = pData.Value;
if (pOrder.goodsPassType == "2")
{
bool bResult = DBInterface.Insert_tblOrder_test(pMySqlDB, pOrder.goodsSeq_, pOrder.channelSeq_
, pOrder.channelOrderCode_, pOrder.orderSettlePrice_, 1, pOrder.orderID_, pOrder.orderName_
, pOrder.orderPhone_, pOrder.State_, pOrder.ExData_Option_, pOrder.ExData_OptionOriginal_
, pOrder.BuyDate_, ref nTempSeq, ref nTempPinCode);
}
else
{
bool bResult = DBInterface.Insert_Order_Channel(pMySqlDB, pOrder.goodsSeq_, pOrder.channelSeq_
, pOrder.channelOrderCode_, pOrder.orderSettlePrice_, pOrder.orderName_
, pOrder.orderPhone_, pOrder.State_, pOrder.BuyDate_, ref nTempSeq, ref nTempPinCode);
}
if (nTempSeq > 0)
{
if(pOrder.goodsSendType_ == 2){
QrCodeImageCreate(nTempPinCode, nTempSeq);
}
else if (pOrder.goodsSendType_ == 3)
{
BarCodeImageCreate(nTempPinCode, nTempSeq);
}
if (nStartSeq == 0)
{
nEndSeq = nStartSeq = nTempSeq;
}
else
{
nEndSeq = nTempSeq;
}
}
ProcessStateManager.Instance.CurDBProcessCount_++;
}
bool bSMSOn = true;
if (nStartSeq > 0 && nEndSeq > 0 && bSMSOn == true)
{
DBInterface.Insert_SMS(pMySqlDB, nStartSeq, nEndSeq);
}
// 매칭 되지 않은 데이터 넣기
foreach (var pData in DBProccess_List_Wrong_)
{
COrderData pOrder = pData.Value;
bool bResult = DBInterface.Insert_tblOrderWr(pMySqlDB, pOrder.goodsSeq_, pOrder.channelSeq_
, pOrder.channelOrderCode_, pOrder.orderSettlePrice_, 1, pOrder.orderID_, pOrder.orderName_
, pOrder.orderPhone_, pOrder.State_, pOrder.ExData_GoodsName_, pOrder.ExData_GoodsNick_, pOrder.ExData_Option_, pOrder.ExData_OptionOriginal_
, pOrder.BuyDate_, ref nTempSeq);
}
pMySqlDB.Close();
pMySqlDB = null;
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error DB_InsertData {0}", ex.Message));
return false;
}
return true;
}
// DB 에서 정보 로드 하기
public bool DB_GetInfos()
{
bool bResult = true;
try
{
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CINIManager.Instance.method_, CINIManager.Instance.dbip_, CINIManager.Instance.dbport_, CINIManager.Instance.dbname_
, CINIManager.Instance.dbaccount_, CINIManager.Instance.dbpw_, CINIManager.Instance.sshhostname_
, CINIManager.Instance.sshuser_, CINIManager.Instance.sshpw_);
bResult = DB_GetChannelInfo(pMySqlDB);
if (bResult)
ProcessStateManager.Instance.ChannelName_ = LQCrawlerInfo_.ChannelName_;
if (bResult)
bResult = DB_GetStateTable(pMySqlDB);
if (bResult)
bResult = DB_GetGoodsInfo(pMySqlDB);
if (bResult)
bResult = DB_SelectData(pMySqlDB);
pMySqlDB.Close();
pMySqlDB = null;
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public bool DB_GetInfos() {0}", ex.Message));
bResult = false;
}
return bResult;
}
// 크롤링
public virtual void Crawling(BackgroundWorker pWorker)
{
LogManager.Instance.Log(string.Format("<<< 크롤링 시작 {0}>>>", CrawlerManager.Instance.CrawlingCount()));
pWorker.ReportProgress(1);
bool bResult = true;
// DB 접속
SqlHelper pMySqlDB = new SqlHelper();
pMySqlDB.Connect(CrawlerInfoManager.Instance.method_, CrawlerInfoManager.Instance.dbip_, CrawlerInfoManager.Instance.dbport_, CrawlerInfoManager.Instance.dbname_
, CrawlerInfoManager.Instance.dbaccount_, CrawlerInfoManager.Instance.dbpw_, CrawlerInfoManager.Instance.sshhostname_
, CrawlerInfoManager.Instance.sshuser_, CrawlerInfoManager.Instance.sshpw_);
// DB에서 채널 정보 로드하기
pWorker.ReportProgress(2);
//bResult = DBFunctions.GetCrawlerInfo_Authority(pMySqlDB, CrawlerInfoManager.Instance.channelidx_, CrawlerInfoManager.Instance.partneridx_);
bResult = DBFunctions.GetCrawlerInfo_Authority(pMySqlDB, CrawlerInfoManager.Instance.channelidx_, CrawlerInfoManager.Instance.partneridx_, CrawlerInfoManager.Instance.authorityseq_);
if (bResult == false)
{
pWorker.ReportProgress(9);
OrderManager.Instance.Init();
LogManager.Instance.Log("<<< 크롤링 실패 : GetCrawlerInfo >>>");
return;
}
// 주문별 상태 로드하기
bResult = DBFunctions.SelectStateTable(pMySqlDB);
if (bResult == false)
{
pWorker.ReportProgress(9);
OrderManager.Instance.Init();
LogManager.Instance.Log("<<< 크롤링 실패 : SelectStateTable >>>");
return;
}
// DB에서 현재 가지고 있는 상품 정보 로드하기
pWorker.ReportProgress(3);
bResult = DBFunctions.GetGoodsTable(pMySqlDB, CrawlerInfoManager.Instance.channelseq_, CrawlerInfoManager.Instance.authorityseq_);
if (bResult == false)
{
pWorker.ReportProgress(9);
OrderManager.Instance.Init();
LogManager.Instance.Log("<<< 크롤링 실패 : GetGoodsTable >>>");
return;
}
// DB에서 현재 Insert된 상품 정보 로드 하기
pWorker.ReportProgress(4);
OrderManager.Instance.Init();
bResult = DBFunctions.Select_tblOrder(pMySqlDB, CrawlerInfoManager.Instance.channelseq_);
bResult = DBFunctions.Select_tblOrderWr(pMySqlDB, CrawlerInfoManager.Instance.channelseq_);
if (bResult == false)
{
pWorker.ReportProgress(9);
OrderManager.Instance.Init();
LogManager.Instance.Log("<<< 크롤링 실패 : Select_tblOrder >>>");
return;
}
CrawlerManager.Instance.GetResultData().DBSelected_ = OrderManager.Instance.GetOrderList().Count;
// 채널 로그인
pWorker.ReportProgress(5);
bResult = Login();
// 채널에서 상품 판매 정보 다운로드 // 데이터 가공
pWorker.ReportProgress(6);
bResult = DownloadExcelAndDataMake();
if (bResult == false)
{
pWorker.ReportProgress(9);
OrderManager.Instance.Init();
LogManager.Instance.Log("<<< 크롤링 실패 >>>");
return;
}
bResult = Combine_DB_And_Excel(false);
//bResult = Process_RefundData(pMySqlDB); // 당일 취소건에 대한것. 일단 막아두자.
bResult = Process_ExpiredData();
// 채널에 상품처리// 채널에 취소처리 // 채널에 반품처리
pWorker.ReportProgress(7);
bResult = Process_Use_Cancel_Refund();
// 다운로드 받은파일 삭제
//deletedownloadfile();
if (pMySqlDB.Close() == false)
{
LogManager.Instance.Log("<<< 삭제 실패 1 >>>");
}
pMySqlDB = null;
// DB 에 Insert or Update
pWorker.ReportProgress(8);
SqlHelper pMySqlDB2 = new SqlHelper();
pMySqlDB2.Connect(CrawlerInfoManager.Instance.method_, CrawlerInfoManager.Instance.dbip_, CrawlerInfoManager.Instance.dbport_, CrawlerInfoManager.Instance.dbname_
, CrawlerInfoManager.Instance.dbaccount_, CrawlerInfoManager.Instance.dbpw_, CrawlerInfoManager.Instance.sshhostname_
, CrawlerInfoManager.Instance.sshuser_, CrawlerInfoManager.Instance.sshpw_);
Process_DB(pMySqlDB2);
if (pMySqlDB2.Close() == false)
{
LogManager.Instance.Log("<<< 삭제 실패 2 >>>");
}
pMySqlDB2 = null;
// 완료
CrawlerManager.Instance.AddCrawlingCount();
pWorker.ReportProgress(9);
LogManager.Instance.Log("<<< 크롤링 완료 >>>");
}