elab_mvp/jianye_data_insert.py

from excel_util import ExcelUtil
import os


class JianYeDataInsert(object):
    brand_id = 13
    file_path = r'E:\elab\建业小程序升级\0330\建业项目卡片信息合档3.29 补2.xlsx'
    sql_1 = """update t_city_management set `order` = {}, coordinate_x = {}, coordinate_y = {} where name = '{}';"""
    sql_1_2 = "insert into t_city_management (brand_id, name, `order`, coordinate_x, coordinate_y, is_recommend, default_city, is_application, status, creator, created) values ({}, '{}', {}, {}, {}, {}, {}, 1, 1, 'binren', now());"

    sql_2 = """insert into t_house_management(is_recommend, brand_id, house_id, `order`, sale_status, description, is_new_open, is_hide, is_application, status, creator, created) values({}, {}, {}, {}, {}, "{}", {}, -1, 1, 1, 'binren', now());"""

    sql_3 = """insert into t_house_image (brand_id, house_id, image_url, image_type, status, creator, created) values({}, {}, '{}', '{}', 1, 'binren', now());"""

    sql_4 = """insert into t_house_parameters (brand_id, house_id, area_min, area_max, open_time, coordinate_x, coordinate_y, status, creator, created) values ({}, {}, {}, {}, {}, {}, {}, 1, 'binren', now());"""

    sql_5 = """insert into t_house_paraments_price_data(brand_id, house_id, value_min, value_max, value_type, house_type, status, creator, created) values ({}, {}, {}, {}, {}, '{}', 1,'binren', now());"""

    sql_6 = """update t_house_management set is_recommend = 1 where brand_id = 13;"""

    def __init__(self):
        self.excel_util = ExcelUtil(file_name=self.file_path)

    # 项目信息
    def house_data(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('项目信息补充')
        rows = [row for row in work_sheet.rows][1:]
        house_data = []
        params_data = []
        for row in rows:
            # 0:项目id	1:省份	2:城市	3:项目	4:项目地址	5:排序	6:最新开盘（1：是，-1：不是）	7:项目描述description
            # 8:销售状态（1：在售，2：待售，3：售完）	9:列表标签	10:推荐	11:状态（是否隐藏）	12:面积低值	13:面积高值
            # 14开盘时间	15:经度	16:纬度
            house_id = row[0].value
            order = row[5].value
            is_new_open = row[6].value
            description = row[7].value
            sale_status = row[8].value
            area_min = row[12].value
            area_max = row[13].value
            coordinate_x = row[15].value
            coordinate_y = row[16].value
            recommend = 'null' if row[10].value is None else row[10].value
            if order is None:
                order = 'null'
            if area_min == '待定' or area_min is None:
                area_min = 'null'
            if area_max == '待定' or area_max is None:
                area_max = 'null'
            if coordinate_x is None or coordinate_x == '待定':
                coordinate_x = 'null'
            if coordinate_y is None or coordinate_y == '待定':
                coordinate_y = 'null'
            if house_id:
                # house_id, `order`, sale_status, description, is_new_open
                house_data.append(self.sql_2.format(recommend, self.brand_id, house_id, order, sale_status, description, is_new_open))
                # house_id, area_min, area_max, open_time, coordinate_x, coordinate_y
                params_data.append(self.sql_4.format(self.brand_id, house_id, area_min, area_max, 'null', coordinate_x, coordinate_y))
        return house_data, params_data

    def params_prices(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('项目参数单价')
        price_data = []
        rows = [row for row in work_sheet.rows][1:]
        for row in rows:
            house_id = row[0].value
            price_min = row[4].value
            if price_min == '待定':
                price_min = 'null'
            price_max = row[5].value
            if price_max == '待定':
                price_max = 'null'
            house_type = row[6].value
            if house_type is None:
                house_type = 'null'
            if house_id:
                price_data.append(self.sql_5.format(self.brand_id, house_id, price_min, price_max, 1, house_type))
        return price_data

    def total_price_data(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('项目参数-总价')
        total_price_data = []
        rows = [row for row in work_sheet.rows][1:]
        for row in rows:
            house_id = row[0].value
            price_min = row[5].value
            if price_min == '待定' or price_min is None:
                price_min = 'null'
            price_max = row[6].value
            if price_max == '待定' or price_max is None:
                price_max = 'null'
            house_type = row[4].value
            if house_type is None:
                house_type = 'null'
            if house_id:
                total_price_data.append(self.sql_5.format(self.brand_id, house_id, price_min, price_max, 2, house_type))
        return total_price_data

    def get_city_data(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('城市信息')
        city_data = []
        rows = [row for row in work_sheet.rows][1:]
        for row in rows:
            name = row[0].value
            order = row[1].value
            x = 'null' if row[4].value is None else row[4].value
            y = 'null' if row[5].value is None else row[5].value
            default = 'null' if row[7].value is None else row[7].value
            recommend = 'null' if row[2].value is None else row[2].value
            # brand_id, name, order, coordinate_x, coordinate_y,
            city_data.append(self.sql_1_2.format(self.brand_id, name, order, x, y, recommend, default))
        return city_data

    def search_image(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('搜索主图')
        rows = [row for row in work_sheet.rows][1:]
        search_data = []
        for row in rows:
            search_data.append(self.sql_3.format(row[0].value, row[1].value, '3'))
        return search_data

    def recommend(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('推荐主图')
        rows = [row for row in work_sheet.rows][1:]
        recommend_data = []
        for row in rows:
            recommend_data.append(self.sql_3.format(row[0].value, row[1].value, '2'))
        return recommend_data

    def list_image(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('列表主图')
        rows = [row for row in work_sheet.rows][1:]
        list_data = []
        for row in rows:
            list_data.append(self.sql_3.format(row[0].value, row[1].value, '1'))
        return list_data

    def house_cards(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('项目卡片')
        rows = [row for row in work_sheet.rows][1:]
        insert_data = []
        for row in rows:
            insert_data.append(self.sql_3.format(row[0].value, row[1].value, '3'))
        return insert_data

    def house_card_image(self):
        card_image = self.excel_util.read_excel_by_ox_name('项目卡片')
        card_rows = [row[0].value for row in card_image.rows]
        house_ids = self.excel_util.read_excel_by_ox_name('项目信息补充')
        ids = [row for row in house_ids]
        result = []
        for cr in card_rows:
            path, file_name = os.path.split(cr)
            house_name = file_name.split('.')[0]
            house_id_end = 0
            for row in ids:
                house_name_1 = row[3].value
                house_id = row[0].value
                if house_name == house_name_1:
                    house_id_end = house_id
                else:
                    pass
            result.append((house_id_end, cr))
        return result

    def update_recommend(self):
        work_sheet = self.excel_util.read_excel_by_ox_name('建业推荐参数3.14')
        rows = [row for row in work_sheet.rows][1:]
        list_data = []
        for row in rows:
            list_data.append(self.sql_6.format(row[1].value, row[0].value))
        return list_data


if __name__ == '__main__':
    jy = JianYeDataInsert()
    house_data, params_data = jy.house_data()
    # price_data = jy.params_prices()
    # total_price_data = jy.total_price_data()
    # city_data = jy.get_city_data()
    # search_data = jy.search_image()
    # recommend_data = jy.recommend()
    # card_datas = jy.house_cards()
    # list_data = jy.list_image()
    # print(len(house_data), len(params_data), len(price_data), len(total_price_data), len(city_data))
    for sql in params_data:
        print(sql)