// 엑셀 다운로드
public override bool Web_DownLoadExcel()
{
try
{
ProcessStateManager.Instance.NeedDownLoadCount_ = GoodsInfoList_.Count;
DateTime dtNow = DateTime.Now;
// 하위 폴더 만들기
string makefolder = HKLibrary.UTIL.HKFileHelper.GetCurrentDirectory();
makefolder += "\\";
makefolder += CINIManager.Instance.channelseq_;
makefolder += "\\";
makefolder += dtNow.ToShortDateString();
HKLibrary.UTIL.HKFileHelper.MakeFolder(makefolder);
foreach (var pData in GoodsInfoList_)
{
ChannelGoodInfo pGoodInfo = pData.Value;
pGoodInfo.Goods_Code_ = "0000";
string downString = makefolder;
downString += "\\";
downString += pGoodInfo.Goods_Code_;
downString += "_";
downString += Convert.ToString(dtNow.Ticks);
downString += ".xls";
// 이미 다운로드가 끝난 파일이라면 다시 다운로드 하지 않는다.
if (GoodsDownInfo_.ContainsKey(pGoodInfo.Goods_Code_) == false)
{
try
{
string method = LQCrawlerInfo_.ExcelDownMethod_;
string url = "";
if (LQCrawlerInfo_.AuthoritySeq_ == 17)
{
url = @"https://soffice.11st.co.kr/escrow/OrderingLogisticsAction.tmall?method=getLogisticsForExcel&isItalyAgencyYn=&isAbrdSellerYn=&listType=orderingLogistics";
}
else
{
url = @"https://partner.11st.co.kr/escrow/OrderingLogisticsAction.tmall?method=getLogisticsForExcel&isItalyAgencyYn=&isAbrdSellerYn=&listType=orderingLogistics";
}
string sendparameter = @"excelColumnList=0/1/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34/35/36/37/38/39/40/41/42/43/44/45/46/47/48/49/50/51/52/53/54/56/57/58/59/60/61/62/63/64/65&excelDownType=oldExcel&abrdOrdPrdStat=&excelShGblDlv=N&shBuyerType=&shBuyerText=&shErrYN=&shProductStat=202&abrdOrdPrdStat420=&abrdOrdPrdStat301=&abrdOrdPrdStat401=&shOrderType=on&addrSeq=&shDateType=01&shDateFrom={sDate}&shDateTo={eDate}&searchDt=8&shDelayReport=&shPurchaseConfirm=&shGblDlv=&dlvMthdCd=%B9%E8%BC%DB%C7%CA%BF%E4%BE%F8%C0%BD&dlvCd=00&pagePerSize=100&listType=orderingConfirm&delaySendDt=&delaySendRsnCd=&delaySendRsn=&orderConfrim=&shStckNo=&prdNo=&hiddenStatusOrder=&hiddenShProductStat=&hiddenCheck=&hiddenprdNo=&hiddenshStckNo=";
string eDate = "";
string sData = "";
if (pGoodInfo.eDateFormat_ != null)
{
DateTime beforeData = dtNow.AddDays(-7);
eDate = string.Format("{0:D4}{1:D2}{2:D2}", dtNow.Year, dtNow.Month, dtNow.Day);
sData = string.Format("{0:D4}{1:D2}{2:D2}", beforeData.Year, beforeData.Month, beforeData.Day);
}
sendparameter = sendparameter.Replace("{GoodsCode}", pGoodInfo.Goods_Code_);
sendparameter = sendparameter.Replace("{sDate}", sData);
sendparameter = sendparameter.Replace("{eDate}", eDate);
HttpWebResponse pResponse = HKHttpWebRequest.ReqHttpRequest(method, url, sendparameter, Cookie_, null, null, 180000);
if (pResponse.CharacterSet == "" || pResponse.CharacterSet == "euc-kr" || pResponse.CharacterSet == "EUC-KR")
{
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
else
{
TextReader textReader = (TextReader) new StreamReader(pResponse.GetResponseStream(), Encoding.GetEncoding(pResponse.CharacterSet));
string htmlBuffer = textReader.ReadToEnd();
HKLibrary.UTIL.HKFileHelper.SaveToFile(downString, htmlBuffer);
textReader.Close();
textReader.Dispose();
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(ex.Message);
continue;
}
GoodsDownInfo_.Add(pGoodInfo.Goods_Code_, downString);
ProcessStateManager.Instance.CurDownLoadCount_++;
}
else
{
ProcessStateManager.Instance.PassDownLoadCount_++;
}
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Web_DownLoadExcel() - {0}", ex.Message));
return(false);
}
return(true);
}
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo)
{
try
{
if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false)
{
NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_));
return(false);
}
string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_];
Microsoft.Office.Interop.Excel.Application ap = null;
Workbook wb = null;
Worksheet ws = null;
HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws);
Range tRange = null;
string tempString = "";
string comparesitename = "";
Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_;
Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_;
Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_;
Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_;
Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_;
Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_;
Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_;
Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_;
Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_;
Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_;
Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_;
Int32 ExData_Coupncode2 = 4; // 11번가만 가지고 있는 주문 순번 쿠폰 코드가 고유하지 않아서 이것과 합쳐야만 고유해진다.
ProcessStateManager.Instance.NeedParsingCount_ += ws.UsedRange.Rows.Count;
while (true)
{
try
{
tRange = ws.Cells[nCurrentRow, 1];
comparesitename = Convert.ToString(tRange.Value2);
tRange = ws.Cells[nCurrentRow, ExData_Option];
if (tRange == null)
{
break;
}
tempString = tRange.Value2;
if (tempString == null)
{
break;
}
COrderData pExcelData = new COrderData();
pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_;
pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_;
pExcelData.ExData_Option_ = tempString;
pExcelData.ExData_OptionOriginal_ = tempString;
tRange = ws.Cells[nCurrentRow, ExData_GoodsName];
pExcelData.ExData_GoodsName_ = tRange.Value2;
pExcelData.ExData_GoodsNick_ = Regex.Replace(pExcelData.ExData_GoodsName_, @"[^a-zA-Z0-9가-힣]", "");
tRange = ws.Cells[nCurrentRow, ExData_Coupncode];
if (tRange == null)
{
break;
}
pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2);
if (pExcelData.channelOrderCode_ == null)
{
break;
}
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", "");
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거
tRange = ws.Cells[nCurrentRow, ExData_Coupncode2];
if (tRange == null)
{
break;
}
pExcelData.channelOrderCode_ = string.Format("{0}_{1}", pExcelData.channelOrderCode_, Convert.ToString(tRange.Value2));
tRange = ws.Cells[nCurrentRow, ExData_Buyer];
pExcelData.orderName_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderName_ == null)
{
pExcelData.orderName_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Cancel];
pExcelData.ExData_Cancel_ = tRange.Value2;
if (pExcelData.ExData_Cancel_ == null)
{
pExcelData.ExData_Cancel_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Use];
pExcelData.ExData_Use_ = tRange.Value2;
if (pExcelData.ExData_Use_ == null)
{
pExcelData.ExData_Use_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Buyphone];
pExcelData.orderPhone_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderPhone_ == null)
{
pExcelData.orderPhone_ = "";
}
pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", "");
if (ExData_Price != 0)
{
tRange = ws.Cells[nCurrentRow, ExData_Price];
if (tRange.Value2 != null)
{// 돈에 , 가 있으면 제거하자.
tempString = Convert.ToString(tRange.Value2);
tempString = tempString.Replace(",", "");
pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString);
}
}
tRange = ws.Cells[nCurrentRow, ExData_BuyDate];
if (LQCrawlerInfo_.Channel_Idx_ == 9 || LQCrawlerInfo_.Channel_Idx_ == 14 || LQCrawlerInfo_.Channel_Idx_ == 15 || LQCrawlerInfo_.Channel_Idx_ == 18)
{
double temp = Convert.ToDouble(tRange.Value2);
DateTime dta = DateTime.FromOADate(temp);
pExcelData.BuyDate_ = dta.ToString("u");
pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", "");
}
else
{
pExcelData.BuyDate_ = Convert.ToString(tRange.Value2);
}
pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace('/', '-');
if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만
{
tRange = ws.Cells[nCurrentRow, ExData_BuyCount];
pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2);
}
SplitDealAndInsertExcelData(pExcelData, comparesitename);
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message));
break;
//nCurrentRow++;
//continue;
}
ProcessStateManager.Instance.CurParsingCount_++;
nCurrentRow++;
}
wb.Close(false, Type.Missing, Type.Missing);
ap.Quit();
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(ap);
ws = null;
wb = null;
ap = null;
GC.Collect();
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error override bool Internal_Excel_Parsing - {0}", ex.Message));
return(false);
}
return(true);
}
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo)
{
try
{
if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false)
{
NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_));
return(false);
}
string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_];
Microsoft.Office.Interop.Excel.Application ap = null;
Workbook wb = null;
Worksheet ws = null;
HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws);
Range tRange = null;
string tempString = "";
string comparesitename = "";
Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_;
Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_;
Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_;
Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_;
Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_;
Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_;
Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_;
Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_;
Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_;
Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_;
Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_;
if (nCurrentRow > 0)
{
ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1));
}
while (true)
{
try
{
tRange = ws.Cells[nCurrentRow, 1];
comparesitename = Convert.ToString(tRange.Value2);
tRange = ws.Cells[nCurrentRow, ExData_Option];
if (tRange == null)
{
break;
}
tempString = tRange.Value2;
if (tempString == null)
{
break;
}
COrderData pExcelData = new COrderData();
pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_;
pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_;
pExcelData.ExData_Option_ = tempString;
pExcelData.ExData_OptionOriginal_ = tempString;
tRange = ws.Cells[nCurrentRow, ExData_GoodsName];
pExcelData.ExData_GoodsName_ = tRange.Value2;
pExcelData.goodsCode_ = pChannelGoodInfo.Goods_Code_;
tRange = ws.Cells[nCurrentRow, ExData_Coupncode];
if (tRange == null)
{
break;
}
pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2);
if (pExcelData.channelOrderCode_ == null)
{
break;
}
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", "");
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거
tRange = ws.Cells[nCurrentRow, ExData_Buyer];
pExcelData.orderName_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderName_ == null)
{
pExcelData.orderName_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Cancel];
pExcelData.ExData_Cancel_ = tRange.Value2;
if (pExcelData.ExData_Cancel_ == null)
{
pExcelData.ExData_Cancel_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Use];
pExcelData.ExData_Use_ = tRange.Value2;
if (pExcelData.ExData_Use_ == null)
{
pExcelData.ExData_Use_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Buyphone];
pExcelData.orderPhone_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderPhone_ == null)
{
pExcelData.orderPhone_ = "";
}
pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", "");
pExcelData.orderPhone_ = Regex.Replace(pExcelData.orderPhone_, @"^(01[016789]{1}|02|0[3-9]{1}[0-9]{1})-?([0-9]{3,4})-?([0-9]{4})$", @"$1-$2-$3");
if (ExData_Price != 0)
{
tRange = ws.Cells[nCurrentRow, ExData_Price];
if (tRange.Value2 != null)
{// 돈에 , 가 있으면 제거하자.
tempString = Convert.ToString(tRange.Value2);
tempString = tempString.Replace(",", "");
pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString);
}
}
tRange = ws.Cells[nCurrentRow, ExData_BuyDate];
pExcelData.BuyDate_ = Convert.ToString(tRange.Value2);
pExcelData.BuyDate_ = pExcelData.BuyDate_.Substring(0, pExcelData.BuyDate_.Length - 2);
if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만
{
tRange = ws.Cells[nCurrentRow, ExData_BuyCount];
pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2);
}
SplitDealAndInsertExcelData(pExcelData, comparesitename);
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message));
break;
//nCurrentRow++;
//continue;
}
ProcessStateManager.Instance.CurParsingCount_++;
nCurrentRow++;
}
wb.Close(false, Type.Missing, Type.Missing);
ap.Quit();
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(ap);
ws = null;
wb = null;
ap = null;
GC.Collect();
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error protected override bool Internal_Excel_Parsing - {0}", ex.Message));
return(false);
}
return(true);
}
// 엑셀 다운로드
public override bool Web_DownLoadExcel()
{
try
{
ProcessStateManager.Instance.NeedDownLoadCount_ = GoodsInfoList_.Count;
DateTime dtNow = DateTime.Now;
// 하위 폴더 만들기
string makefolder = HKLibrary.UTIL.HKFileHelper.GetCurrentDirectory();
makefolder += "\\";
makefolder += CINIManager.Instance.channelseq_;
makefolder += "\\";
makefolder += dtNow.ToShortDateString();
HKLibrary.UTIL.HKFileHelper.MakeFolder(makefolder);
foreach (var pData in GoodsInfoList_)
{
ChannelGoodInfo pGoodInfo = pData.Value;
string downString = makefolder;
downString += "\\";
downString += pGoodInfo.Goods_Code_;
downString += "_";
downString += Convert.ToString(dtNow.Ticks);
downString += ".xls";
// 이미 다운로드가 끝난 파일이라면 다시 다운로드 하지 않는다.
if (GoodsDownInfo_.ContainsKey(pGoodInfo.Goods_Code_) == false)
{
try
{
string method = LQCrawlerInfo_.ExcelDownMethod_;
string url = LQCrawlerInfo_.ExcelDownUrl_;
url = url.Replace("{GoodsCode}", pGoodInfo.Goods_Code_);
string sendparameter = LQCrawlerInfo_.ExcelDownParameter_;
string eDate = "";
string sData = "";
if (pGoodInfo.eDateFormat_ != null)
{
DateTime beforeData = dtNow.AddDays(-7); // 이지웰 건수가 많으면 데이터를 못들고옴, 10일전 건수만 들고오게 함
eDate = string.Format("{0:D4}-{1:D2}-{2:D2}", dtNow.Year, dtNow.Month, dtNow.Day);
sData = string.Format("{0:D4}-{1:D2}-{2:D2}", beforeData.Year, beforeData.Month, beforeData.Day);
}
sendparameter = sendparameter.Replace("{sDate}", sData);
sendparameter = sendparameter.Replace("{eDate}", eDate);
HttpWebResponse pResponse = HKHttpWebRequest.ReqHttpRequest(method, url, sendparameter, Cookie_, null, null, 180000);
if (pResponse.CharacterSet == "" || pResponse.CharacterSet == "euc-kr" || pResponse.CharacterSet == "EUC-KR")
{
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
else
{
TextReader textReader = (TextReader) new StreamReader(pResponse.GetResponseStream(), Encoding.GetEncoding(pResponse.CharacterSet));
string htmlBuffer = textReader.ReadToEnd();
string FileName = "";
htmlBuffer = htmlBuffer.Replace(" ", " ");
string regstring = @"{""result"":""SUCCESS"",""fileName"":""(?<FileName>\S+)""}";
Regex re = new Regex(regstring, RegexOptions.IgnoreCase | RegexOptions.Singleline);
MatchCollection oe = re.Matches(htmlBuffer);
foreach (Match mat in oe)
{
GroupCollection group = mat.Groups;
FileName = Convert.ToString(group["FileName"].Value);
FileName = FileName.Replace(" ", " ");
}
if (FileName != "")
{
url = "http://www.saletonight.com/excel/excelDown";
sendparameter = "fileName=" + System.Web.HttpUtility.UrlEncode(FileName);
pResponse = HKHttpWebRequest.ReqHttpRequest("GET", url, sendparameter, Cookie_, null, null, 180000);
if (pResponse.CharacterSet == "" || pResponse.CharacterSet == "euc-kr" || pResponse.CharacterSet == "EUC-KR")
{
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
else
{
textReader = (TextReader) new StreamReader(pResponse.GetResponseStream(), Encoding.GetEncoding(pResponse.CharacterSet));
htmlBuffer = textReader.ReadToEnd();
}
}
textReader.Close();
textReader.Dispose();
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(ex.Message);
continue;
}
GoodsDownInfo_.Add(pGoodInfo.Goods_Code_, downString);
ProcessStateManager.Instance.CurDownLoadCount_++;
}
else
{
ProcessStateManager.Instance.PassDownLoadCount_++;
}
GC.Collect();
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Web_DownLoadExcel() - {0}", ex.Message));
return(false);
}
return(true);
}
// 엑셀 다운로드
public override bool Web_DownLoadExcel()
{
try
{
//ChannelGoodInfo p = new ChannelGoodInfo();
//p.Goods_Code_ = "test";
//GoodsInfoList_.Add(9999,p);
ProcessStateManager.Instance.NeedDownLoadCount_ = GoodsInfoList_.Count;
DateTime dtNow = DateTime.Now;
// 하위 폴더 만들기
string makefolder = HKLibrary.UTIL.HKFileHelper.GetCurrentDirectory();
makefolder += "\\";
makefolder += CINIManager.Instance.channelseq_;
makefolder += "\\";
makefolder += dtNow.ToShortDateString();
HKLibrary.UTIL.HKFileHelper.MakeFolder(makefolder);
foreach (var pData in GoodsInfoList_)
{
ChannelGoodInfo pGoodInfo = pData.Value;
string downString = makefolder;
downString += "\\";
downString += pGoodInfo.Goods_Code_;
downString += "_";
downString += Convert.ToString(dtNow.Ticks);
downString += ".xls";
// 이미 다운로드가 끝난 파일이라면 다시 다운로드 하지 않는다.
if (GoodsDownInfo_.ContainsKey(pGoodInfo.Goods_Code_) == false)
{
try
{
string method = LQCrawlerInfo_.ExcelDownMethod_;
string url = LQCrawlerInfo_.ExcelDownUrl_;
url = url.Replace("{GoodsCode}", pGoodInfo.Goods_Code_);
string sendparameter = LQCrawlerInfo_.ExcelDownParameter_;
DateTime beforeData = dtNow.AddDays(-30);
string eDate_year = string.Format("{0:D4}", dtNow.Year);
string eDate_month = string.Format("{0:D2}", dtNow.Month);
string eDate_day = string.Format("{0:D2}", dtNow.Day);
string sDate_year = string.Format("{0:D4}", beforeData.Year);
string sDate_month = string.Format("{0:D2}", beforeData.Month);
string sDate_day = string.Format("{0:D2}", beforeData.Day);
sendparameter = sendparameter.Replace("{sDay_Y}", sDate_year);
sendparameter = sendparameter.Replace("{sDay_M}", sDate_month);
sendparameter = sendparameter.Replace("{sDay_D}", sDate_day);
sendparameter = sendparameter.Replace("{eDay_Y}", eDate_year);
sendparameter = sendparameter.Replace("{eDay_M}", eDate_month);
sendparameter = sendparameter.Replace("{eDay_D}", eDate_day);
HttpWebResponse pResponse = HKHttpWebRequest.ReqHttpRequest(method, url, sendparameter, Cookie_, null, null, 180000);
if (pResponse.CharacterSet == "" || pResponse.CharacterSet == "euc-kr" || pResponse.CharacterSet == "EUC-KR")
{
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
else
{
TextReader textReader = (TextReader) new StreamReader(pResponse.GetResponseStream(), Encoding.GetEncoding(pResponse.CharacterSet));
string htmlBuffer = textReader.ReadToEnd();
HKLibrary.UTIL.HKFileHelper.SaveToFile(downString, htmlBuffer);
textReader.Close();
textReader.Dispose();
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(ex.Message);
continue;
}
GoodsDownInfo_.Add(pGoodInfo.Goods_Code_, downString);
ProcessStateManager.Instance.CurDownLoadCount_++;
}
else
{
ProcessStateManager.Instance.PassDownLoadCount_++;
}
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Web_DownLoadExcel() - {0}", ex.Message));
return(false);
}
return(true);
}
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo)
{
try
{
if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false)
{
NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_));
return(false);
}
string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_];
Microsoft.Office.Interop.Excel.Application ap = null;
Workbook wb = null;
Worksheet ws = null;
HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws);
Range tRange = null;
string tempString = "";
Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_;
Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_;
Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_;
Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_;
Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_;
Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_;
Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_;
Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_;
Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_;
Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_;
Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_;
if (nCurrentRow > 0)
{
ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1));
}
string beforeOrderName = ""; // 이전 주문자명
string beforeOrderPhone = ""; // 이전 주문 전화번호
string beforeOrderDate = ""; // 이전 주문 날짜
while (true)
{
try
{
tRange = ws.Cells[nCurrentRow, ExData_Option];
if (tRange == null)
{
break;
}
tempString = tRange.Value2;
if (tempString == null)
{
break;
}
COrderData pExcelData = new COrderData();
pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_; // 채널 시퀀스
pExcelData.goodsSeq_ = -1; // 상품 시퀀스
pExcelData.ExData_Option_ = tempString; // 옵션명
pExcelData.ExData_OptionOriginal_ = tempString; // 원래 옵션명
tRange = ws.Cells[nCurrentRow, ExData_GoodsName];
pExcelData.ExData_GoodsName_ = tRange.Value2;
pExcelData.ExData_GoodsNick_ = Regex.Replace(pExcelData.ExData_GoodsName_, @"[^a-zA-Z0-9가-힣]", "");
tRange = ws.Cells[nCurrentRow, ExData_Coupncode];
if (tRange == null)
{
break;
}
pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2);
if (pExcelData.channelOrderCode_ == null)
{
break;
}
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", "");
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim();
tRange = ws.Cells[nCurrentRow, ExData_Buyer];
pExcelData.orderName_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderName_ == null)
{
pExcelData.orderName_ = beforeOrderName;
}
else
{
beforeOrderName = pExcelData.orderName_;
}
tRange = ws.Cells[nCurrentRow, ExData_Cancel];
pExcelData.ExData_Cancel_ = tRange.Value2;
if (pExcelData.ExData_Cancel_ == null)
{
pExcelData.ExData_Cancel_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Use];
pExcelData.ExData_Use_ = tRange.Value2;
if (pExcelData.ExData_Use_ == null)
{
pExcelData.ExData_Use_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Buyphone];
pExcelData.orderPhone_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderPhone_ == null)
{
pExcelData.orderPhone_ = beforeOrderPhone;
}
else
{
beforeOrderPhone = pExcelData.orderPhone_;
}
pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", "");
if (ExData_Price != 0)
{
tRange = ws.Cells[nCurrentRow, ExData_Price];
if (tRange.Value2 != null)
{
tempString = Convert.ToString(tRange.Value2);
tempString = tempString.Replace(",", "");
pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString);
}
}
tRange = ws.Cells[nCurrentRow, ExData_BuyDate];
if (tRange.Value2 == null)
{
pExcelData.BuyDate_ = beforeOrderDate;
}
else
{
double temp = Convert.ToDouble(tRange.Value2);
DateTime dta = DateTime.FromOADate(temp);
pExcelData.BuyDate_ = dta.ToString("u");
pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", "");
beforeOrderDate = pExcelData.BuyDate_;
}
if (ExData_BuyCount != 0)
{
tRange = ws.Cells[nCurrentRow, ExData_BuyCount];
pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2);
}
SplitDealAndInsertExcelData(pExcelData);
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message));
break;
//nCurrentRow++;
//continue;
}
ProcessStateManager.Instance.CurParsingCount_++;
nCurrentRow++;
}
wb.Close(false, Type.Missing, Type.Missing);
ap.Quit();
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(ap);
ws = null;
wb = null;
ap = null;
GC.Collect();
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Internal_Excel_Parsing - {0}", ex.Message));
return(false);
}
return(true);
}
// 엑셀 다운로드
public override bool Web_DownLoadExcel()
{
try
{
ProcessStateManager.Instance.NeedDownLoadCount_ = GoodsInfoList_.Count;
DateTime dtNow = DateTime.Now;
// 하위 폴더 만들기
string makefolder = HKLibrary.UTIL.HKFileHelper.GetCurrentDirectory();
makefolder += "\\";
makefolder += CINIManager.Instance.channelseq_;
makefolder += "\\";
makefolder += dtNow.ToShortDateString();
HKLibrary.UTIL.HKFileHelper.MakeFolder(makefolder);
foreach (var pData in GoodsInfoList_)
{
ChannelGoodInfo pGoodInfo = pData.Value;
string downString = makefolder;
downString += "\\";
downString += pGoodInfo.Goods_Code_;
downString += "_";
downString += Convert.ToString(dtNow.Ticks);
downString += ".xls";
// 이미 다운로드가 끝난 파일이라면 다시 다운로드 하지 않는다.
if (GoodsDownInfo_.ContainsKey(pGoodInfo.Goods_Code_) == false)
{
try
{
string method = LQCrawlerInfo_.ExcelDownMethod_;
string url = LQCrawlerInfo_.ExcelDownUrl_;
string sendparameter = LQCrawlerInfo_.ExcelDownParameter_;
// DB 의 값이 달라서 임시로 넣어둔값
//string method = "GET";
//string url = @"https://withgs.gsshop.com/dlv/mobilCpnAutoRfnManulProc/filedown";
//string sendparameter = @"flag=&ecDirdlvOboxYn=&dirdlvRelsInfoImprovTgtYn=N&downPsblYn=null&intgSrchLinkOrdNo=&dateTime={eDate}+12%3A56%3A50&soldOut=&prdCd=&itemCd=&chkOrgSupCd=1027095&chkSupCd=1027095&userDownLoadYn=0&mobilCpnAutoRfnSupYn=&qryTerm=B&fromSearchDtm={sDate}&toSearchDtm={eDate}&srchCond=A&srchText=&ordSt=1&useYn=0&fileDownGbn=";
string useragent = @"User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36";
string eDate = "";
string sData = "";
if (pGoodInfo.eDateFormat_ != null)
{
DateTime beforeData = dtNow.AddDays(-6);
eDate = string.Format("{0:D4}-{1:D2}-{2:D2}", dtNow.Year, dtNow.Month, dtNow.Day);
sData = string.Format("{0:D4}-{1:D2}-{2:D2}", beforeData.Year, beforeData.Month, beforeData.Day);
}
sendparameter = sendparameter.Replace("{sDate}", sData);
sendparameter = sendparameter.Replace("{eDate}", eDate);
HttpWebResponse pResponse = HKHttpWebRequest.ReqHttpRequest(method, url, sendparameter, Cookie_, null, useragent, 180000);
if (pResponse.CharacterSet == "" || pResponse.CharacterSet == "euc-kr" || pResponse.CharacterSet == "EUC-KR")
{
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
else
{
TextReader textReader = (TextReader) new StreamReader(pResponse.GetResponseStream(), Encoding.GetEncoding(pResponse.CharacterSet));
string htmlBuffer = textReader.ReadToEnd();
HKLibrary.UTIL.HKFileHelper.SaveToFile(downString, htmlBuffer);
textReader.Close();
textReader.Dispose();
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(ex.Message);
continue;
}
GoodsDownInfo_.Add(pGoodInfo.Goods_Code_, downString);
ProcessStateManager.Instance.CurDownLoadCount_++;
}
else
{
ProcessStateManager.Instance.PassDownLoadCount_++;
}
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Web_DownLoadExcel() - {0}", ex.Message));
return(false);
}
return(true);
}
// 엑셀 다운로드
public override bool Web_DownLoadExcel()
{
try
{
ProcessStateManager.Instance.NeedDownLoadCount_ = GoodsInfoList_.Count;
DateTime dtNow = DateTime.Now;
// 하위 폴더 만들기
string makefolder = HKLibrary.UTIL.HKFileHelper.GetCurrentDirectory();
makefolder += "\\";
makefolder += CINIManager.Instance.channelseq_;
makefolder += "\\";
makefolder += dtNow.ToShortDateString();
HKLibrary.UTIL.HKFileHelper.MakeFolder(makefolder);
foreach (var pData in GoodsInfoList_)
{
ChannelGoodInfo pGoodInfo = pData.Value;
pGoodInfo.Goods_Code_ = "0000";
string downString = makefolder;
downString += "\\";
downString += pGoodInfo.Goods_Code_;
downString += "_";
downString += Convert.ToString(dtNow.Ticks);
downString += ".xls";
// 이미 다운로드가 끝난 파일이라면 다시 다운로드 하지 않는다.
if (GoodsDownInfo_.ContainsKey(pGoodInfo.Goods_Code_) == false)
{
try
{
string method = LQCrawlerInfo_.ExcelDownMethod_;
string url = LQCrawlerInfo_.ExcelDownUrl_;
url = url.Replace("{GoodsCode}", pGoodInfo.Goods_Code_);
string sendparameter = LQCrawlerInfo_.ExcelDownParameter_;
string eDate = "";
string sData = "";
if (pGoodInfo.eDateFormat_ != null)
{
DateTime beforeData = dtNow.AddDays(-7); // 이지웰 건수가 많으면 데이터를 못들고옴, 10일전 건수만 들고오게 함
eDate = string.Format(pGoodInfo.eDateFormat_, dtNow.Year, dtNow.Month, dtNow.Day);
sData = string.Format(pGoodInfo.eDateFormat_, beforeData.Year, beforeData.Month, beforeData.Day);
}
sendparameter = sendparameter.Replace("{GoodsCode}", pGoodInfo.Goods_Code_);
sendparameter = sendparameter.Replace("{sDate}", sData);
sendparameter = sendparameter.Replace("{eDate}", eDate);
HttpWebResponse pResponse = HKHttpWebRequest.ReqHttpRequest(method, url, sendparameter, Cookie_);
FileStream fs = File.OpenWrite(downString);
string d = pResponse.CharacterSet;
Stream responsestream = pResponse.GetResponseStream();
byte[] buffer = new byte[2048];
long totalBytesRead = 0;
int bytesRead;
while ((bytesRead = responsestream.Read(buffer, 0, buffer.Length)) > 0)
{
totalBytesRead += bytesRead;
fs.Write(buffer, 0, bytesRead);
}
fs.Close();
fs.Dispose();
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(ex.Message);
continue;
}
GoodsDownInfo_.Add(pGoodInfo.Goods_Code_, downString);
ProcessStateManager.Instance.CurDownLoadCount_++;
}
else
{
ProcessStateManager.Instance.PassDownLoadCount_++;
}
}
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error public override bool Web_DownLoadExcel() - {0}", ex.Message));
return(false);
}
return(true);
}
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo)
{
try
{
if (actionType != "insert")
{
return(true);
}
if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false)
{
NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_));
return(false);
}
DateTime dtNow = BaseChannel.dtnow;
DateTime beforeData = dtNow.AddDays(-2);
string sData = string.Format("{0}-{1}-{2} {3}:{4}:{5}", beforeData.Year, beforeData.Month, beforeData.Day, "00", "00", "00");
string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_];
Microsoft.Office.Interop.Excel.Application ap = null;
Workbook wb = null;
Worksheet ws = null;
HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws);
Range tRange = null;
string tempString = "";
string comparesitename = "";
Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_;
Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_;
Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_;
Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_;
Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_;
Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_;
Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_;
Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_;
Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_;
Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_;
if (nCurrentRow > 0)
{
ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1));
}
while (true)
{
try
{
tRange = ws.Cells[nCurrentRow, 1];
comparesitename = Convert.ToString(tRange.Value2);
tRange = ws.Cells[nCurrentRow, ExData_Option];
if (tRange == null)
{
break;
}
tempString = tRange.Value2;
if (tempString == null)
{
break;
}
COrderData pExcelData = new COrderData();
pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_;
pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_;
pExcelData.ExData_Option_ = tempString;
pExcelData.ExData_OptionOriginal_ = tempString;
pExcelData.ExData_GoodsName_ = pExcelData.ExData_GoodsNick_ = pChannelGoodInfo.GoodsName_;
pExcelData.goodsCode_ = pChannelGoodInfo.Goods_Code_;
tRange = ws.Cells[nCurrentRow, ExData_BuyDate];
pExcelData.BuyDate_ = Convert.ToString(tRange.Value2);
pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace('.', '-');
pExcelData.BuyDate_ = Regex.Replace(pExcelData.BuyDate_, @"^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$", @"$1-$2-$3 $4:$5:$6");
DateTime excelDate = DateTime.Parse(pExcelData.BuyDate_);
int checkTime = DateTime.Compare(beforeData, excelDate);
//비교날짜보다 더 크면 리스트에 추가하지 말고 넘어간다
if (checkTime > 0)
{
ProcessStateManager.Instance.PassParsingCount_++;
nCurrentRow++;
continue;
}
tRange = ws.Cells[nCurrentRow, ExData_Coupncode];
if (tRange == null)
{
break;
}
pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2);
if (pExcelData.channelOrderCode_ == null)
{
break;
}
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", "");
pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거
tRange = ws.Cells[nCurrentRow, ExData_Buyer];
pExcelData.orderName_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderName_ == null)
{
pExcelData.orderName_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Cancel];
pExcelData.ExData_Cancel_ = tRange.Value2;
if (pExcelData.ExData_Cancel_ == null)
{
pExcelData.ExData_Cancel_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Use];
pExcelData.ExData_Use_ = tRange.Value2;
if (pExcelData.ExData_Use_ == null)
{
pExcelData.ExData_Use_ = "";
}
tRange = ws.Cells[nCurrentRow, ExData_Buyphone];
pExcelData.orderPhone_ = Convert.ToString(tRange.Value2);
if (pExcelData.orderPhone_ == null)
{
pExcelData.orderPhone_ = "";
}
pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", "");
if (ExData_Price != 0)
{
tRange = ws.Cells[nCurrentRow, ExData_Price];
if (tRange.Value2 != null && tRange.Value2 != "")
{// 돈에 , 가 있으면 제거하자.
tempString = Convert.ToString(tRange.Value2);
tempString = tempString.Replace(",", "");
pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString);
}
}
if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만
{
tRange = ws.Cells[nCurrentRow, ExData_BuyCount];
pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2);
}
SplitDealAndInsertExcelData(pExcelData, comparesitename);
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message));
break;
//nCurrentRow++;
//continue;
}
ProcessStateManager.Instance.CurParsingCount_++;
nCurrentRow++;
}
wb.Close(false, filepath, Type.Missing);
ap.Quit();
Marshal.FinalReleaseComObject(tRange);
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(ap);
tRange = null;
ws = null;
wb = null;
ap = null;
GC.Collect();
}
catch (System.Exception ex)
{
NewLogManager2.Instance.Log(string.Format("Error protected override bool Internal_Excel_Parsing - {0}", ex.Message));
return(false);
}
return(true);
}
