<repo_name>tranthaibinh111/MySqlToSqlServer<file_sep>/controllers/__init__.py
from .excel_controller import ExcelController
from .category_controller import CategoryController
from .in_out_product_variable_controller import InOutProductVariableController
from .product_controller import ProductController
from .product_image_controller import ProductImageController
from .product_variable_controller import ProductVariableController
from .product_variable_value_controller import ProductVariableValueController
from .variable_value_controller import VariableValueController
<file_sep>/__main__.py
import os
from controllers import CategoryController, \
    InOutProductVariableController, \
    ProductController, \
    ProductImageController, \
    ProductVariableController, \
    ProductVariableValueController, \
    VariableValueController

if __name__ == "__main__":
    root_path = os.getcwd()

    # Lấy thông tin các danh mục cần thiết
    print("Lấy thông tin các danh mục cần thiết")
    category_file = {
        "path_full": root_path + "\\import_excel\\20180619_category.xlsx",
        "sheet": "category"
    }

    variable_value_file = {
        "path_full": root_path + "\\import_excel\\20180619_variable-value.xlsx",
        "sheet": "variable-value"
    }

    category_controller = CategoryController(category_file["path_full"], category_file["sheet"])
    variable_value_controller = VariableValueController(variable_value_file["path_full"], variable_value_file["sheet"])

    # Đọc thông tin từ file excel cần import
    print("Đọc thông tin từ file excel cần import")
    product_file = {
        "path_full": root_path + "\\import_excel\\20180619_san-pham.xlsx",
        "sheet": "san-pham"
    }
    product_variable_file = {
        "path_full": root_path + "\\import_excel\\20180619_bien-the.xlsx",
        "sheet": "bien-the"
    }

    product_controller = ProductController(product_file["path_full"], product_file["sheet"], category_controller)
    product_image_controller = ProductImageController(product_controller)
    product_variable_controller = ProductVariableController(product_variable_file["path_full"],
                                                            product_variable_file["sheet"],
                                                            product_controller)
    product_variable_value_controller = ProductVariableValueController(product_variable_controller,
                                                                       variable_value_controller)
    in_out_product_variable_controller = InOutProductVariableController(product_controller,
                                                                        product_variable_controller,
                                                                        variable_value_controller)

    # Update thông tin table chính từ table phụ
    print("Update thông tin table chính từ table phụ")
    product_controller.update_product_variable(product_variable_controller.product_parents)

    # Xuất file sql import
    print("Xuất file sql import")
    category_controller.export_sql()
    variable_value_controller.export_sql()
    product_controller.export_sql()
    product_image_controller.export_sql()
    product_variable_controller.export_sql()
    product_variable_value_controller.export_sql()
    in_out_product_variable_controller.export_sql()

    print("Ket thuc chuong trinh")<file_sep>/controllers/product_variable_controller.py
import os
from common.sql import CommonSql
from models import ProductVariableEntity
from controllers import ExcelController


class ProductVariableController:
    def __init__(self, file_name, sheet_name, product_controller):
        self.__file_name = file_name
        self.__sheet_name = sheet_name
        self.product_variables = []
        self.product_parents = []

        self.__mapping(product_controller)

    def __mapping(self, product_controller):
        data = ExcelController.get_data(self.__file_name, self.__sheet_name)

        index = 0

        for row in data:
            index += 1
            product_variable = ProductVariableEntity()

            product_variable.id = index
            product_variable.parent_sku = row[0]
            product_variable.sku = row[4]

            product_variable.product_id = product_controller.get_product_variable_id(product_variable.parent_sku)
            if product_variable.product_id is None:
                print(product_variable.parent_sku)
                raise Exception("ParentSku khong ton tai trong table tbl_Product")

            if row[5] is None:
                    product_variable.stock = 0
            else:
                # Do file excel co van de format nen de float
                if float(str(row[5]).replace("'", "")) < 0:
                    product_variable.stock = 0
                else:
                    product_variable.stock = row[5]

            if product_variable.stock > 0:
                product_variable.stock_status = 1
            else:
                product_variable.stock_status = 0

            product_variable.regular_price = row[7]
            product_variable.cost_of_good = row[8]
            product_variable.image = row[9]
            product_variable.color = row[12]
            product_variable.size = row[13]
            product_variable.retail_price = row[14]
            product_variable.minimum_inventory_level = 2
            product_variable.maximum_inventory_level = 10

            self.product_variables.append(product_variable)
            self.__calculator_product(product_variable.parent_sku,
                                      product_variable.stock,
                                      product_variable.regular_price)

    # Su ly lay thong tin stock  cho table product
    def __calculator_product(self, parent_sku, stock, regular_price):
        check_exist = False
        min_regular_price = regular_price

        for product_stock in self.product_parents:
            if product_stock["parent_sku"] == parent_sku:
                check_exist = True
                product_stock["stock"] += stock

                if product_stock["regular_price"] > min_regular_price:
                    product_stock["regular_price"] = min_regular_price

        if not check_exist:
            self.product_parents.append({"parent_sku": parent_sku, "stock": stock, "regular_price": regular_price})

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\product_variable.sql"

        with open(file_name, mode="w+", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductVariable ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_ProductVariable;\n")
            wf.write("\n")

            index = 0
            for product_variable in self.product_variables:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_ProductVariable("
                sql_text += "     ID"
                sql_text += ",    ProductID"
                sql_text += ",    ParentSKU"
                sql_text += ",    SKU"
                sql_text += ",    Stock"
                sql_text += ",    StockStatus"
                sql_text += ",    Regular_Price"
                sql_text += ",    CostOfGood"
                sql_text += ",    Image"
                sql_text += ",    ManageStock"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ",    color"
                sql_text += ",    size"
                sql_text += ",    RetailPrice"
                sql_text += ",    MinimumInventoryLevel"
                sql_text += ",    MaximumInventoryLevel"
                sql_text += ",    SupplierID"
                sql_text += ",    SupplierName"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(product_variable.id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.product_id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.parent_sku)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.sku)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.stock)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.stock_status)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.regular_price)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.cost_of_good)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.image)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.manage_stock)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.created_date)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.created_by)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.modified_by)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.color)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.size)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.retail_price)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.minimum_inventory_level)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.maximum_inventory_level)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.supplier_id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable.supplier_name)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductVariable OFF;\n")
<file_sep>/controllers/product_variable_value_controller.py
import os
from common.sql import CommonSql
from models import ProductVariableValueEntity


class ProductVariableValueController:
    def __init__(self, product_variable_controller, variable_value_controller):
        self.product_variable_values = []
        self.__mapping(product_variable_controller, variable_value_controller)

    def __mapping(self, product_variable_controller, variable_value_controller):

        index = 0

        for product_variable in product_variable_controller.product_variables:
            product_variable_column = []

            if product_variable.color is not None and product_variable.size is not None:
                product_variable_column = ["Màu", "Size"]
            elif product_variable.color is not None:
                product_variable_column = ["Màu"]
            elif product_variable.size is not None:
                product_variable_column = ["Size"]

            for column in product_variable_column:
                index += 1
                product_variable_value = ProductVariableValueEntity()

                product_variable_value.id = index
                product_variable_value.product_variable_id = product_variable.id

                if column == "Màu":
                    info_variable_value = variable_value_controller.get_variable_value_info(column,
                                                                                            product_variable.color)
                else:
                    info_variable_value = variable_value_controller.get_variable_value_info(column,
                                                                                            product_variable.size)

                if info_variable_value is None:
                    print(column, " ", product_variable.color, " ", product_variable.size)
                    raise Exception("Color or Size khong ton tai trong table tbl_VariableValue")

                product_variable_value.product_variable_sku = product_variable.sku
                product_variable_value.variable_value_id = info_variable_value["id"]
                product_variable_value.variable_name = info_variable_value["variable_name"]
                product_variable_value.variable_value = info_variable_value["variable_value"]
                product_variable_value.is_hidden = product_variable.is_hidden
                product_variable_value.created_date = product_variable.created_date
                product_variable_value.created_by = product_variable.created_by
                product_variable_value.modified_date = product_variable.modified_date
                product_variable_value.modified_by = product_variable.modified_by

                self.product_variable_values.append(product_variable_value)

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\product_variable_value.sql"

        with open(file_name, mode="w+", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductVariableValue ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_ProductVariableValue;\n")
            wf.write("\n")

            index = 0
            for product_variable_value in self.product_variable_values:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_ProductVariableValue("
                sql_text += "     ID"
                sql_text += ",    ProductVariableID"
                sql_text += ",    ProductvariableSKU"
                sql_text += ",    VariableValueID"
                sql_text += ",    VariableName"
                sql_text += ",    VariableValue"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(product_variable_value.id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.product_variable_id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.product_variable_sku)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.variable_value_id)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.variable_name)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.variable_value)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.created_date)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.created_by)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(product_variable_value.modified_by)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductVariableValue OFF;\n")
<file_sep>/models/product_image_entity.py
from datetime import datetime


class ProductImageEntity:
    def __init__(self):
        self.id = None
        self.product_id = None
        self.product_image = None
        self.is_hidden = 0
        self.created_date = datetime.now()
        self.created_by = "admin"
        self.modified_date = None
        self.modified_by = None
<file_sep>/common/sql/__init__.py
from .common_sql import CommonSql
<file_sep>/models/product_variable_entity.py
from datetime import datetime


class ProductVariableEntity:
    def __init__(self):
        self.id = None
        self.product_id = None
        self.parent_sku = None
        self.stock = 0
        self.stock_status = 0
        self.regular_price = 0
        self.cost_of_good = 0
        self.image = None
        self.manage_stock = 1
        self.is_hidden = 0
        self.created_date = datetime.now()
        self.created_by = "admin"
        self.modified_date = None
        self.modified_by = None
        self.color = None
        self.size = None
        self.retail_price = 0
        self.minimum_inventory_level = None
        self.maximum_inventory_level = None
        self.supplier_id = None
        self.supplier_name = None
<file_sep>/models/in_out_product_variable_entity.py
from datetime import datetime


class InOutProductVariableEntity:
    def __init__(self):
        self.id = None
        self.agent_id = 1
        self.product_id = None
        self.product_variable_id = None
        self.product_variable_name = None
        self.product_variable_value = None
        self.quantity = None
        self.quantity_current = 0
        self.type = 1
        self.is_hidden = 0
        self.created_date = datetime.now()
        self.created_by = "admin"
        self.modified_date = None
        self.modified_by = None
        self.product_type = None
        self.note = None
        self.order_id = 0
        self.session_in_out_id = 0
        self.status = 1
        self.product_name = None
        self.sku = None
        self.product_image = None
        self.product_variable = None
        self.move_pro_id = 0
        self.parent_id = None
<file_sep>/models/__init__.py
from .category_entity import CategoryEntity
from .in_out_product_variable_entity import InOutProductVariableEntity
from .product_entity import ProductEntity
from .product_image_entity import ProductImageEntity
from .product_variable_entity import ProductVariableEntity
from .product_variable_value_entity import ProductVariableValueEntity
from .variable_value_entity import VariableValueEntity
<file_sep>/models/category_entity.py
from datetime import datetime


class CategoryEntity:
    def __init__(self):
        self.id = None
        self.category_name = None
        self.category_description = None
        self.category_level = None
        self.parent_id = None
        self.is_hidden = None
        self.create_date = datetime.now()
        self.create_by = "admin"
        self.modified_date = None
        self.modified_by = None
<file_sep>/controllers/variable_value_controller.py
import os
from common.sql import CommonSql
from models import VariableValueEntity
from controllers import ExcelController


class VariableValueController:
    def __init__(self, file_name, sheet_name):
        self.__file_name = file_name
        self.__sheet_name = sheet_name
        self.variable_values = []

        self.__mapping()

    def __mapping(self):
        data = ExcelController.get_data(self.__file_name, self.__sheet_name)

        for row in data:
            variable_value = VariableValueEntity()

            variable_value.id = row[0]
            variable_value.variable_id = row[1]
            variable_value.variable_name = row[2]
            variable_value.variable_value = row[3]
            variable_value.variable_value_text = row[9]
            variable_value.sku_text = row[10]

            self.variable_values.append(variable_value)

    def get_variable_value_info(self, variable_name, variable_value_text):
        result = None

        for variable_value in self.variable_values:
            if variable_value.variable_name.upper() == variable_name.upper() \
                    and str(variable_value.variable_value_text).upper() == str(variable_value_text).upper():
                result = {
                            'id': variable_value.id,
                            'variable_name': variable_value.variable_name,
                            'variable_value': variable_value.variable_value,
                            'sku_text': variable_value.sku_text
                        }
                break

        return result

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\variable_value.sql"

        with open(file_name, mode="w", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_VariableValue ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_VariableValue;\n")
            wf.write("\n")

            index = 0
            for variable_value in self.variable_values:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_VariableValue("
                sql_text += "     ID"
                sql_text += ",    VariableID"
                sql_text += ",    VariableName"
                sql_text += ",    VariableValue"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ",    VariableValueText"
                sql_text += ",    SKUText"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(variable_value.id)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.variable_id)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.variable_name)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.variable_value)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.create_date)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.create_by)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.modified_by)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.variable_value_text)
                sql_text += ",    " + CommonSql.f_str_value(variable_value.sku_text)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_VariableValue OFF;\n")
<file_sep>/controllers/in_out_product_variable_controller.py
import os
from common.sql import CommonSql
from models import InOutProductVariableEntity


class InOutProductVariableController:
    def __init__(self, product_controller, product_variable_controller, variable_value_controller):
        self.in_out_product_variable = []

        self.__mapping(product_controller, product_variable_controller, variable_value_controller)

    def __mapping(self, product_controller, product_variable_controller, variable_value_controller):
        index = 0

        for data in product_controller.product_simples:
            index += 1
            product = InOutProductVariableEntity()

            product.id = index
            product.product_id = data.id
            product.product_variable_id = 0
            product.product_variable_name = None
            product.product_variable_value = None
            product.quantity = data.product_stock
            product.product_type = 1
            product.sku = data.product_sku
            product.product_variable = None
            product.parent_id = data.id

            self.in_out_product_variable.append(product)

        for data in product_variable_controller.product_variables:
            index += 1
            product = InOutProductVariableEntity()

            product.id = index
            product.product_id = 0
            product.product_variable_id = data.id

            if data.color is not None and data.size:
                product_variable_name = "Màu|Size"

                color = variable_value_controller.get_variable_value_info("Màu", data.color)
                size = variable_value_controller.get_variable_value_info("Size", data.size)
                product_variable_value = color["variable_value"] + "|" + str(size["variable_value"])
            else:
                if data.color is not None:
                    product_variable_name = "Màu|"

                    color = variable_value_controller.get_variable_value_info("Màu", data.color)
                    product_variable_value = color["variable_value"] + "|"
                elif data.size is not None:
                    product_variable_name = "Size|"

                    size = variable_value_controller.get_variable_value_info("Size", data.size)
                    product_variable_value = str(size["variable_value"]) + "|"
                else:
                    product_variable_name = None
                    product_variable_value = None

            product.product_variable_name = product_variable_name
            product.product_variable_value = product_variable_value
            product.quantity = data.stock
            product.product_type = 2
            product.sku = data.sku
            product.product_variable = product_variable_value
            product.parent_id = data.product_id

            self.in_out_product_variable.append(product)

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\in_out_product_variable.sql"

        with open(file_name, mode="w+", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_StockManager ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_StockManager;\n")
            wf.write("\n")

            index = 0
            for product in self.in_out_product_variable:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_StockManager("
                sql_text += "     ID"
                sql_text += ",    AgentID"
                sql_text += ",    ProductID"
                sql_text += ",    ProductVariableID"
                # sql_text += ",    ProductVariableName"
                # sql_text += ",    ProductVariableValue"
                sql_text += ",    Quantity"
                sql_text += ",    QuantityCurrent"
                sql_text += ",    Type"
                # sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                # sql_text += ",    ProductType"
                sql_text += ",    NoteID"
                sql_text += ",    OrderID"
                # sql_text += ",    SessionInOutID"
                sql_text += ",    Status"
                # sql_text += ",    ProductName"
                sql_text += ",    SKU"
                # sql_text += ",    ProductImage"
                # sql_text += ",    ProductVariable"
                sql_text += ",    MoveProID"
                sql_text += ",    ParentID"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(product.id)
                sql_text += ",    " + CommonSql.f_str_value(product.agent_id)
                sql_text += ",    " + CommonSql.f_str_value(product.product_id)
                sql_text += ",    " + CommonSql.f_str_value(product.product_variable_id)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_variable_name)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_variable_value)
                sql_text += ",    " + CommonSql.f_str_value(product.quantity)
                sql_text += ",    " + CommonSql.f_str_value(product.quantity_current)
                sql_text += ",    " + CommonSql.f_str_value(product.type)
                # sql_text += ",    " + CommonSql.f_str_value(product.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(product.created_date)
                sql_text += ",    " + CommonSql.f_str_value(product.created_by)
                sql_text += ",    " + CommonSql.f_str_value(product.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(product.modified_by)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_type)
                sql_text += ",    " + CommonSql.f_str_value(product.note)
                sql_text += ",    " + CommonSql.f_str_value(product.order_id)
                # sql_text += ",    " + CommonSql.f_str_value(product.session_in_out_id)
                sql_text += ",    " + CommonSql.f_str_value(product.status)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_name)
                sql_text += ",    " + CommonSql.f_str_value(product.sku)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_image)
                # sql_text += ",    " + CommonSql.f_str_value(product.product_variable)
                sql_text += ",    " + CommonSql.f_str_value(product.move_pro_id)
                sql_text += ",    " + CommonSql.f_str_value(product.parent_id)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_InOutProductVariable OFF;\n")<file_sep>/common/sql/common_sql.py
from datetime import datetime


class CommonSql:
    @staticmethod
    def f_str_value(parameter):
        result = None

        if parameter is None:
            result = "NULL"
        else:
            if type(parameter) is str:
                result = "N'" + parameter + "'"
            elif type(parameter) is int:
                result = str(parameter)
            elif type(parameter) is float:
                result = str(parameter)
            elif type(parameter) is datetime:
                result = "CAST('" + str(parameter) + "' AS DATETIME2)"
            else:
                result = None

        if result is None:
            print(str(parameter) + ": " + str(type(parameter)))
            raise Exception("Kieu parameter khong ton tai trong dinh nghia ham common")
        else:
            return result
<file_sep>/controllers/product_controller.py
import os
from common.sql import CommonSql
from models import ProductEntity
from controllers import ExcelController


class ProductController:
    def __init__(self, file_name, sheet_name, category_controller):
        self.__file_name = file_name
        self.__sheet_name = sheet_name
        self.product_simples = []
        self.product_variables = []

        self.__mapping(category_controller)

    def __mapping(self, category_controller):
        data = ExcelController.get_data(self.__file_name, self.__sheet_name)

        index = 0

        for row in data:
            index += 1
            product = ProductEntity()

            product.id = index

            if row[14] is not None:
                categories = row[14].split(" > ")
                category_name = categories[len(categories) - 1]
                product.category_id = category_controller.get_category_id(category_name)

                if product.category_id is None:
                    print(category_name)
                    raise Exception("CategoryName khong ton tai trong table tbl_category")

            product.product_title = row[0]
            product.product_content = row[3]
            product.product_sku = row[5]

            if row[13] is not None and row[13].upper() == "VARIABLE":
                product.product_stock = 0
                product.manage_stock = 0
                product.product_style = 2
            else:
                if row[6] is not None:
                    if int(str(row[6]).replace("'", "")) > 0:
                        product.product_stock = row[6]
                    else:
                        product.product_stock = 0

                if product.product_stock > 0:
                    # in stock
                    product.stock_status = 1
                else:
                    # out stock
                    product.stock_status = 0

                product.manage_stock = 1
                product.regular_price = row[9]
                product.product_style = 1
                product.minimum_inventory_level = 2
                product.maximum_inventory_level = 10

            product.cost_of_good = row[11]
            product.retail_price = row[10]

            if row[12] is not None:
                image = row[12].split(" | ")
                product.product_images = image
                product.product_image = product.product_images[0]

            if product.manage_stock == 1:
                # simple
                self.product_simples.append(product)
            else:
                # variable
                self.product_variables.append(product)

    def get_product_list(self):
        return self.product_simples + self.product_variables

    def get_product_variable_id(self, product_sku):
        result = None

        for product in self.product_variables:
            if product.product_sku.upper() == product_sku.upper():
                result = product.id
                break

        return result

    def update_product_variable(self, product_parents):
        for product in self.product_variables:
            for group in product_parents:
                if product.product_sku == group["parent_sku"]:
                    product.regular_price = group["regular_price"]

                    if group["stock"] > 0:
                        # in stock
                        product.stock_status = 1
                    else:
                        # out stock
                        product.stock_status = 0

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\product.sql"

        with open(file_name, mode="w", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_Product ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_Product;\n")
            wf.write("\n")

            index = 0
            for product in self.get_product_list():
                index += 1

                if product.product_title is not None:
                    product.product_title = product.product_title.replace("'", "''")

                if product.product_content is not None:
                    product.product_content = product.product_content.replace("\n", "").replace("'", "''")

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_Product("
                sql_text += "     ID"
                sql_text += ",    CategoryID"
                sql_text += ",    ProductOldID"
                sql_text += ",    ProductTitle"
                sql_text += ",    ProductContent"
                sql_text += ",    ProductSKU"
                sql_text += ",    ProductStock"
                sql_text += ",    StockStatus"
                sql_text += ",    ManageStock"
                sql_text += ",    Regular_Price"
                sql_text += ",    CostOfGood"
                sql_text += ",    Retail_Price"
                sql_text += ",    ProductImage"
                sql_text += ",    ProductType"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ",    Materials"
                sql_text += ",    MinimumInventoryLevel"
                sql_text += ",    MaximumInventoryLevel"
                sql_text += ",    SupplierID"
                sql_text += ",    SupplierName"
                sql_text += ",    ProductStyle"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(product.id)
                sql_text += ",    " + CommonSql.f_str_value(product.category_id)
                sql_text += ",    " + CommonSql.f_str_value(product.product_old_id)
                sql_text += ",    " + CommonSql.f_str_value(product.product_title)
                sql_text += ",    " + CommonSql.f_str_value(product.product_content)
                sql_text += ",    " + CommonSql.f_str_value(product.product_sku)
                sql_text += ",    " + CommonSql.f_str_value(product.product_stock)
                sql_text += ",    " + CommonSql.f_str_value(product.stock_status)
                sql_text += ",    " + CommonSql.f_str_value(product.manage_stock)
                sql_text += ",    " + CommonSql.f_str_value(product.regular_price)
                sql_text += ",    " + CommonSql.f_str_value(product.cost_of_good)
                sql_text += ",    " + CommonSql.f_str_value(product.retail_price)
                sql_text += ",    " + CommonSql.f_str_value(product.product_image)
                sql_text += ",    " + CommonSql.f_str_value(product.product_type)
                sql_text += ",    " + CommonSql.f_str_value(product.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(product.created_date)
                sql_text += ",    " + CommonSql.f_str_value(product.created_by)
                sql_text += ",    " + CommonSql.f_str_value(product.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(product.modified_by)
                sql_text += ",    " + CommonSql.f_str_value(product.materials)
                sql_text += ",    " + CommonSql.f_str_value(product.minimum_inventory_level)
                sql_text += ",    " + CommonSql.f_str_value(product.maximum_inventory_level)
                sql_text += ",    " + CommonSql.f_str_value(product.supplier_id)
                sql_text += ",    " + CommonSql.f_str_value(product.supplier_name)
                sql_text += ",    " + CommonSql.f_str_value(product.product_style)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_Product OFF;\n")
<file_sep>/controllers/excel_controller.py
from openpyxl import load_workbook


class ExcelController:
    def __init__(self):
        raise Exception("Class ExcelController khong the khoi tao")

    @staticmethod
    def get_data(file_name, sheet_name):
        if file_name == '':
            raise Exception("Chua cho thong tin file excel")

        try:
            wb = load_workbook(file_name, read_only=True)
            ws = wb[sheet_name]

            index = 0
            data = []

            for row in ws.rows:
                index += 1

                # Khong doc dong title
                if index == 1:
                    continue

                data_row = []
                for cell in row:
                    data_row.append(cell.value)

                data.append(data_row)

            return data

        except Exception as e:
            print(e)
<file_sep>/controllers/product_image_controller.py
import os
from common.sql import CommonSql
from models import ProductImageEntity


class ProductImageController:
    def __init__(self, product_controller):
        self.product_images = []
        self.__mapping(product_controller)

    def __mapping(self, product_controller):

        index = 0

        for product in product_controller.get_product_list():
            if product.product_images is not None:
                for image in product.product_images:

                    index += 1
                    product_image = ProductImageEntity()

                    product_image.id = index
                    product_image.product_id = product.id
                    product_image.product_image = image
                    product_image.is_hidden = product.is_hidden
                    product_image.created_date = product.created_date
                    product_image.created_by = product.created_by
                    product_image.modified_date = product.modified_date
                    product_image.modified_by = product.modified_by

                    self.product_images.append(product_image)

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\product_image.sql"

        with open(file_name, mode="w+", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductImage ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_ProductImage;\n")
            wf.write("\n")

            index = 0
            for product_image in self.product_images:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_ProductImage("
                sql_text += "     ID"
                sql_text += ",    ProductID"
                sql_text += ",    ProductImage"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(product_image.id)
                sql_text += ",    " + CommonSql.f_str_value(product_image.product_id)
                sql_text += ",    " + CommonSql.f_str_value(product_image.product_image)
                sql_text += ",    " + CommonSql.f_str_value(product_image.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(product_image.created_date)
                sql_text += ",    " + CommonSql.f_str_value(product_image.created_by)
                sql_text += ",    " + CommonSql.f_str_value(product_image.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(product_image.modified_by)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_ProductImage OFF;\n")
<file_sep>/models/product_entity.py
from datetime import datetime


class ProductEntity:
    def __init__(self):
        self.id = None
        self.category_id = None
        self.product_old_id = None
        self.product_title = None
        self.product_content = None
        self.product_sku = None
        self.product_stock = 0
        self.stock_status = 0
        self.manage_stock = 1
        self.regular_price = 0
        self.cost_of_good = 0
        self.retail_price = 0
        self.product_image = None
        self.product_type = 0
        self.is_hidden = 0
        self.created_date = datetime.now()
        self.created_by = "admin"
        self.modified_date = None
        self.modified_by = None
        self.materials = None
        self.minimum_inventory_level = None
        self.maximum_inventory_level = None
        self.supplier_id = None
        self.supplier_name = None
        self.product_style = None

        # Dùng cho insert tabel ProductImage
        self.product_images = None<file_sep>/controllers/category_controller.py
import os
from common.sql import CommonSql
from controllers import ExcelController
from models import CategoryEntity


class CategoryController:
    def __init__(self, file_name, sheet_name):
        self.__file_name = file_name
        self.__sheet_name = sheet_name
        self.categories = []

        self.__mapping()

    def __mapping(self):
        data = ExcelController.get_data(self.__file_name, self.__sheet_name)

        for row in data:
            category = CategoryEntity()

            category.id = row[0]
            category.category_name = row[1]
            category.category_description = row[2]
            category.category_level = row[3]
            category.parent_id = row[4]
            category.is_hidden = row[5]

            self.categories.append(category)

    def get_category_id(self, category_name):
        result = None

        for category in self.categories:
            if category.category_name.upper() == category_name.upper():
                result = category.id
                break

        return result

    def export_sql(self):
        file_name = os.getcwd() + "\\export_sql\\category.sql"

        with open(file_name, mode="w", encoding="utf-8") as wf:
            wf.write("SET IDENTITY_INSERT dbo.tbl_Category ON;\n")
            wf.write("\n")
            wf.write("DELETE FROM dbo.tbl_Category;\n")
            wf.write("\n")

            index = 0
            for category in self.categories:
                index += 1

                sql_text = ""
                sql_text += "INSERT INTO dbo.tbl_Category("
                sql_text += "     ID"
                sql_text += ",    CategoryName"
                sql_text += ",    CategoryDescription"
                sql_text += ",    CategoryLevel"
                sql_text += ",    ParentID"
                sql_text += ",    IsHidden"
                sql_text += ",    CreatedDate"
                sql_text += ",    CreatedBy"
                sql_text += ",    ModifiedDate"
                sql_text += ",    ModifiedBy"
                sql_text += ") VALUES("
                sql_text += "     " + CommonSql.f_str_value(category.id)
                sql_text += ",    " + CommonSql.f_str_value(category.category_name)
                sql_text += ",    " + CommonSql.f_str_value(category.category_description)
                sql_text += ",    " + CommonSql.f_str_value(category.category_level)
                sql_text += ",    " + CommonSql.f_str_value(category.parent_id)
                sql_text += ",    " + CommonSql.f_str_value(category.is_hidden)
                sql_text += ",    " + CommonSql.f_str_value(category.create_date)
                sql_text += ",    " + CommonSql.f_str_value(category.create_by)
                sql_text += ",    " + CommonSql.f_str_value(category.modified_date)
                sql_text += ",    " + CommonSql.f_str_value(category.modified_by)
                sql_text += ");\n"

                wf.write(sql_text)

                if index > 100:
                    wf.write("GO\n")
                    index = 0

            wf.write("SET IDENTITY_INSERT dbo.tbl_Category OFF;\n")
<file_sep>/models/product_variable_value_entity.py
from datetime import datetime


class ProductVariableValueEntity:
    def __init__(self):
        self.id = None
        self.product_variable_id = None
        self.product_variable_sku = None
        self.variable_value_id = None
        self.variable_name = None
        self.variable_value = None
        self.is_hidden = 0
        self.created_date = datetime.now()
        self.created_by = "admin"
        self.modified_date = None
        self.modified_by = None
<file_sep>/models/variable_value_entity.py
from datetime import datetime


class VariableValueEntity:
    def __init__(self):
        self.id = None
        self.variable_id = None
        self.variable_name = None
        self.variable_value = None
        self.is_hidden = 0
        self.create_date = datetime.now()
        self.create_by = "admin"
        self.modified_date = None
        self.modified_by = None
        self.variable_value_text = None
        self.sku_text = None
