132 lines
6.9 KiB
Python
132 lines
6.9 KiB
Python
import pandas, os, random
|
|
from datetime import date, timedelta, datetime
|
|
|
|
class mergeNewOrders:
|
|
def __init__(self):
|
|
self.queuedir = "/Users/maddiekorea/PycharmProjects/automations/new_orders"
|
|
self.queuefiles = self.getQueue()
|
|
self.currentfile = "/Users/maddiekorea/PycharmProjects/automations/now/current.xlsx"
|
|
self.proccessed = "/Users/maddiekorea/PycharmProjects/automations/proccessed"
|
|
self.objfiles = {'smartstore':self.getSmartStoreOrderfile(),'playauto':self.getPlayAutoOrderfile(),'artbox':self.getArtBoxOrderfile()}
|
|
self.resfileformat = ['상품주문번호','주문번호','배송방법','택배사','송장번호','발송일','구매자명','수취인명','주문상태','결제일','상품번호','상품명','옵션정보','수량','옵션가격','상품가격','상품별 할인액','판매자 부담 할인액','상품별 총 주문금액','발주확인일','발송기한','발송처리일','송장출력일','배송비 형태','배송비 묶음번호','배송비 유형','배송비 합계','제주/도서 추가배송비','배송비 할인액','판매자 상품코드','판매자 내부코드1','판매자 내부코드2','수취인연락처1','수취인연락처2','통합배송지','구매자연락처','우편번호','배송메세지','출고지','결제수단','수수료 과금구분','수수료결제방식','네이버페이 주문관리 수수료','매출연동 수수료','매출연동 수수료 구분','정산예정금액','개인통관고유부호','주문일시','마지막 유입경로','유입채널','원주문코드','원주문GR시퀀스','작가코드','SKU코드','쇼핑몰상품코드','공급사']
|
|
self.ordernumCount = 0
|
|
|
|
def getQueue(self):
|
|
xlsx_list = [file for file in os.listdir(self.queuedir) if file.endswith(r'.xlsx') or file.endswith(r'.xls')]
|
|
return xlsx_list
|
|
|
|
def getSmartStoreOrderfile(self):
|
|
smstorelist = [item for item in self.queuefiles if "스마트스토어" in item]
|
|
if len(smstorelist) != 1:
|
|
return ''
|
|
else:
|
|
return self.queuedir + "/" + smstorelist[0]
|
|
|
|
def getPlayAutoOrderfile(self):
|
|
plAutolist = [item for item in self.queuefiles if "플레이오토" in item]
|
|
if len(plAutolist) != 1:
|
|
return ''
|
|
else:
|
|
return self.queuedir + "/" + plAutolist[0]
|
|
|
|
def getArtBoxOrderfile(self):
|
|
artBoxlist = [item for item in self.queuefiles if "아트박스" in item]
|
|
if len(artBoxlist) != 1:
|
|
return ''
|
|
else:
|
|
return self.queuedir + "/" + artBoxlist[0]
|
|
|
|
def smartstorehandle(self):
|
|
file = self.objfiles['smartstore']
|
|
df = pandas.read_excel(file,header=1)
|
|
df['유입채널'] = "네이버"
|
|
df['원주문코드'] = df['주문번호']
|
|
df['원주문GR시퀀스'] = df['상품주문번호']
|
|
df['작가코드'] = df['판매자 상품코드']
|
|
df['SKU코드'] = ''
|
|
df['쇼핑몰상품코드'] = df['상품번호']
|
|
df['공급사'] = ''
|
|
df = df.astype({'상품주문번호':'str','주문번호':'str','상품번호':'str','배송비 묶음번호':'str','우편번호':'str','원주문코드':'str','원주문GR시퀀스':'str','쇼핑몰상품코드':'str'})
|
|
df['우편번호'] = df.apply(lambda x: x['우편번호'].zfill(5), axis=1)
|
|
return df
|
|
|
|
def playautohandle(self):
|
|
file = self.objfiles['playauto']
|
|
df = pandas.read_excel(file)
|
|
rdf = pandas.DataFrame(columns = self.resfileformat)
|
|
rdf['주문번호'] = df['묶음번호']
|
|
rdf['배송방법'] = '택배,등기,소포'
|
|
rdf['결제일'] = df['결제완료일']
|
|
df['유입채널'] = df.apply(lambda x: self.makeChannelname(x['쇼핑몰(계정)']), axis=1)
|
|
rdf['유입채널'] = df["유입채널"]
|
|
rdf['쇼핑몰상품코드'] = df['쇼핑몰 상품코드']
|
|
rdf['상품명'] = df['온라인 상품명']
|
|
rdf['옵션정보'] = df['옵션']
|
|
rdf['수량'] = df['주문수량']
|
|
df['_주문자명'] = df.apply(lambda x: self.makeChannelname(x['주문자(ID)']), axis=1)
|
|
rdf['구매자명'] = df['_주문자명']
|
|
rdf['수취인명'] = df['수령자명']
|
|
df = df.astype({'배송메세지':'str','기타메세지':'str'})
|
|
df['_배송메세지'] = df[['배송메세지', '기타메세지']].apply(' '.join, axis=1)
|
|
rdf['배송메세지'] = df['_배송메세지']
|
|
rdf['배송메세지'] = rdf.apply(lambda x: x['배송메세지'].replace("nan",""), axis=1)
|
|
df['우편번호'] = df.apply(lambda x: self.postcodeNormal(x['우편번호']), axis=1)
|
|
rdf['우편번호'] = df['우편번호']
|
|
rdf['통합배송지'] = df['주소']
|
|
rdf['상품별 총 주문금액'] = df['금액']
|
|
rdf['배송비 합계'] = df['배송비']
|
|
rdf['주문일시'] = df['주문일']
|
|
rdf['발송일'] = df['발송예정일']
|
|
rdf['송장번호'] = df['배송번호']
|
|
rdf['주문상태'] = df['주문상태']
|
|
rdf['배송비 유형'] = df['배송방법']
|
|
rdf['수취인연락처1'] = df['수령자 휴대폰번호']
|
|
rdf['구매자연락처'] = df['주문자 휴대폰번호']
|
|
rdf['출고지'] = df['배송처']
|
|
rdf['원주문GR시퀀스'] = df['쇼핑몰 주문번호']
|
|
rdf['원주문코드'] = rdf.apply(lambda x: self.getOrgOrdNum(x['원주문GR시퀀스']), axis=1)
|
|
rdf['상품주문번호'] = rdf.apply(lambda x: self.fakeOrderNo(), axis=1)
|
|
rdf = rdf.astype({'쇼핑몰상품코드': 'str','상품주문번호':'str','주문번호':'str'})
|
|
rdf['결제일'] = pandas.to_datetime(rdf['결제일'], format='%Y-%m-%d %H:%M:%S', errors='raise')
|
|
rdf['주문일시'] = pandas.to_datetime(rdf['주문일시'], format='%Y-%m-%d %H:%M:%S', errors='raise')
|
|
return rdf
|
|
|
|
def makeChannelname(self,chstring):
|
|
strs = chstring.split('/')
|
|
res = strs[0]
|
|
return res
|
|
|
|
def postcodeNormal(self,postcodes):
|
|
res = str(postcodes).replace("-","")
|
|
return res
|
|
|
|
def merge(self):
|
|
df = self.smartstorehandle()
|
|
df2 = self.playautohandle()
|
|
df = pandas.concat([df,df2])
|
|
return df
|
|
|
|
def getOrgOrdNum(self,noStr):
|
|
tmp = noStr.split(" ")
|
|
res = tmp[0]
|
|
return res
|
|
|
|
def fakeOrderNo(self):
|
|
now = datetime.now()
|
|
mo = str(now.month).zfill(2)
|
|
y = now.year + 7000
|
|
y = str(y).zfill(4)
|
|
d = str(now.day).zfill(2)
|
|
h = str(now.hour).zfill(2)
|
|
m = str(now.minute).zfill(2)
|
|
ordernum = self.ordernumCount
|
|
end = str(ordernum).zfill(4)
|
|
self.ordernumCount = self.ordernumCount + 1
|
|
res = str(y)+str(mo)+str(d)+str(h)+str(m)+str(end)
|
|
return res
|
|
|
|
if __name__ == '__main__':
|
|
mergeOrders = mergeNewOrders()
|
|
df = mergeOrders.merge()
|
|
df.to_excel("./test.xlsx")
|
|
#print(mergeOrders.fakeOrderNo()) |