import pandas, pymysql, json, re, os

def makejson(text):
    textlist = text.split("\n")
    return textlist

def escapestring(text):
    res = re.sub(r"\'","\\'",text)
    return res

def makeOptContent(optgroup,options):
    optlist = options.split("\n")
    optgrplist = optgroup.split("\n")
    res = []
    for i in range(len(optlist)):
        valuelist = optlist[i].split(",")
        optCont = {}
        for j in range(len(optgrplist)):
            optCont[optgrplist[j]] = re.sub(r"\'", "’", valuelist[j])
            #optCont[optgrplist[j]] = valuelist[j]
        res.append(optCont)
    return res

def makeOPtPrice(price,optprice):
    opp = optprice.split("\n")
    res = []
    for i in range(len(opp)):
        _opp = int(opp[i]) + int(price)
        res.append(_opp)
    return res

def getXlsfiles():
    fpath = "/Users/maddiekorea/Desktop/스마트스토어/"
    xlslist = os.listdir(fpath)
    xlslist.sort()
    for i in range(len(xlslist)):
        xlslist[i] = fpath + xlslist[i]
    return xlslist

if __name__ == '__main__':
    files = getXlsfiles()
    for index in range(len(files)):
        df = pandas.read_excel(files[index], header=1, skiprows=[2, 3, 4], usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 18, 19, 20])
        df = df.fillna("")
        for row in df.iterrows():
            try:
                db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
                                     db='conteenew_catalogue', charset='utf8', unix_socket="/tmp/mysql.sock")
                cursor = db.cursor()
                dispSql = """INSERT INTO `conteenew_catalogue`.`dispPrds` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')
                    """.format(row[1]['상품번호'], row[1]['카테고리코드'], row[1]['판매자 상품코드'], row[1]['브랜드'],
                               escapestring(row[1]['상품명']), row[1]['상품상태'], row[1]['판매가'], row[1]['부가세'],
                               row[1]['재고수량'], row[1]['옵션형태'], row[1]['대표이미지'],
                               json.dumps(makejson(row[1]['추가이미지']), ensure_ascii=False))
                cursor.execute(dispSql)
                db.commit()
                db.close()
                if row[1]['옵션형태'] == "조합형":
                    optcontent = makeOptContent(row[1]['옵션명'], row[1]['옵션값'])
                    optprice = makeOPtPrice(row[1]['판매가'], row[1]['옵션가'])
                    stock = makejson(row[1]['옵션 재고수량'])
                    for i in range(len(optcontent)):
                        try:
                            db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
                                                 db='conteenew_catalogue', charset='utf8',
                                                 unix_socket="/tmp/mysql.sock")
                            cursor = db.cursor()
                            skuSql = """INSERT INTO `conteenew_catalogue`.`skuEntries` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}')
                                """.format(row[1]['상품번호'], row[1]['판매자 상품코드'], row[1]['브랜드'],
                                           escapestring(row[1]['상품명']), json.dumps(optcontent[i], ensure_ascii=False),
                                           optprice[i], stock[i])
                            cursor.execute(skuSql)
                            db.commit()
                            db.close()
                        except:
                            print(skuSql)
                else:
                    try:
                        db = pymysql.connect(host='127.0.0.1', port=3306, user='maddiekorea', password='mad(#lin',
                                             db='conteenew_catalogue', charset='utf8', unix_socket="/tmp/mysql.sock")
                        cursor = db.cursor()
                        skuSql = """INSERT INTO `conteenew_catalogue`.`skuEntries` VALUES (0,'{0}','','','{1}','{2}','{3}','{4}','{5}','{6}')
                            """.format(row[1]['상품번호'], row[1]['판매자 상품코드'], row[1]['브랜드'], escapestring(row[1]['상품명']),
                                       json.dumps({'원래없음': re.sub(r"\'", "’", row[1]['상품명'])}, ensure_ascii=False),
                                       row[1]['판매가'], row[1]['재고수량'])
                        cursor.execute(skuSql)
                        db.commit()
                        db.close()
                    except:
                        print(skuSql)
            except:
                print(dispSql)