// 모든 크롤러 정보 로드 하기 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("<<< 크롤링 완료 >>>"); }