tireprice

# ~*~ coding:utf-8 ~*~
#csv是一个读取csv文件的lib
import csv
#itertools生成组合和排列的lib
import itertools
#pyodbc是连接ms sql server的lib
import pyodbc
import pymysql
#cnxn是数据库连接  更改数据库连接方式，账号密码

#cursor是数据库浮标
cursor = cnxn.cursor()


def test(skuset):
    err = False
    for sku in skuset:
        product_id = getIdFromSku(sku)
        if not product_id:
            print ("can't find product id where sku='%s'" % sku)
            err = True
#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("c:/Users/Jenny/Desktop/ski/tierprice for customer/VIP-AU-1.csv")))
    header = reader[0]
    #遍历数据，并处理， tips：跳过第一行
    skuset = set([])
    for i in reader[1:]:
        sku = i[0]
        product_id = getIdFromSku(sku)
        if product_id == 0:
            print ("error sku %s" % sku)
            continue
        prices = i[1:]
        for j in range(len(prices)):
            price = float(prices[j])
            crid = int(header[j+1]) 
            updatetire(product_id)
            if find(product_id,crid):
                update(product_id,crid,price)
            else:
                insert(product_id,price,crid)
def update(product_id,crid,price):
    sql = "update dbo.TierPrice set Price=%f where ProductId=%d and CustomerRoleId=%d" %(price,product_id,crid)
    cursor.execute(sql)
    cnxn.commit()

def find(product_id,crid):
    sql ="select * from dbo.TierPrice where ProductId=%d and CustomerRoleId=%d" % (product_id,crid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows

def updatetire(productid):
    sql = "update dbo.Product set HasTierPrices=1 where Id=%d" % productid
    print (sql)
    cursor.execute(sql)
    cnxn.commit()
def insert(product_id,price,crid):
    sql = "insert into dbo.TierPrice (ProductId,StoreId,CustomerRoleId,Quantity,Price) values(%d,0,%d,1,%f)" % (product_id,crid,price)
    print (sql)
    cursor.execute(sql)
    cnxn.commit()

def getIdFromSku(a):
    sql = "select Id from dbo.Product where Sku='%s' and deleted=0" % a
    cursor.execute(sql)
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0


if __name__ == "__main__":
process()