def update_order_shipment_prepare_time(order_item_process):
print "start...update_order_shipment_prepare_time"
sql = """
UPDATE `order_shipment` SET `goods_prepare_time` = %s, `last_update_by` = '-2' WHERE 1=1 AND `order_id` = %s
"""
temp_data = []
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
for order in order_item_process:
if not order[0]:
continue
if not order[1]:
continue
item = []
item.append(str(order[1]))
item.append(str(order[0]))
temp_data.append(item)
print "flush to mysql"
curr.executemany(sql, temp_data)
conn.commit()
curr.close()
conn.close()
print "end...update_order_shipment_prepare_time"
def update_order_shipment_prepare_time(order_item_process):
print "start...update_order_shipment_prepare_time"
sql="""
UPDATE `order_shipment` SET `goods_prepare_time` = %s, `last_update_by` = '-2' WHERE 1=1 AND `order_id` = %s
"""
temp_data = []
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
for order in order_item_process:
if not order[0]:
continue
if not order[1]:
continue
item = []
item.append(str(order[1]))
item.append(str(order[0]))
temp_data.append(item)
print "flush to mysql"
curr.executemany(sql, temp_data)
conn.commit()
curr.close()
conn.close()
print "end...update_order_shipment_prepare_time"
def get_store_can_online_company_id_list():
print "start...get_store_can_online_company_id_list"
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql = """
SELECT DISTINCT(sc.`comp_id`)
FROM
`m2cchinaerp`.`store$company` sc,
`m2cchinaerp`.`store$banner` head_banner, `m2cchinaerp`.`store$cfg$banner` head_banner_cfg,
`m2cchinaerp`.`store$banner` hot_banner, `m2cchinaerp`.`store$cfg$banner` hot_banner_cfg
WHERE 1=1
AND sc.`status` = 0
AND sc.`logo` IS NOT NULL
AND head_banner.`comp_id` = sc.`comp_id` AND head_banner.`cfg_banner_id` = head_banner_cfg.`cfg_banner_id` and head_banner.`store_company_id` = sc.`store_company_id`
AND head_banner_cfg.`type` = 1 AND head_banner.`status` = 7 AND head_banner.`image` IS NOT NULL and head_banner_cfg.`market_region_code` = sc.`market_region_code`
AND hot_banner.`comp_id` = sc.`comp_id` AND hot_banner.`cfg_banner_id` = hot_banner_cfg.`cfg_banner_id` and hot_banner.`store_company_id` = sc.`store_company_id`
AND hot_banner_cfg.`type` = 2 AND hot_banner.`status` = 7 AND hot_banner.`image` IS NOT NULL AND hot_banner.`link_entity_id` IS NOT NULL and hot_banner_cfg.`market_region_code` = sc.`market_region_code`
AND EXISTS (
SELECT NULL FROM `gmcore`.`pro_product_base` pb
WHERE pb.`status` = 7 AND pb.`comp_id` = sc.`comp_id` and pb.market_region_code = sc.`market_region_code` GROUP BY pb.`comp_id` HAVING(COUNT(0) >= 10)
)
AND EXISTS (
SELECT NULL FROM `m2cchinaerp`.`store$group` sg, `m2cchinaerp`.`store$group_prod` sgp, `gmcore`.`pro_product_base` pb
WHERE sg.`group_id` = sgp.`group_id` AND sgp.`prod_base_id` = pb.`product_base_id` AND pb.`status` = 7
and sg.`store_company_id` = sc.`store_company_id`
and sgp.store_company_id = sc.store_company_id
and pb.market_region_code = sc.market_region_code
AND sg.`status` = 7 AND sg.`show_in_homepage` = 1
AND sg.`comp_id` = sc.`comp_id`
GROUP BY sg.`comp_id` HAVING(COUNT(0) > 3)
)
ORDER BY sc.`comp_id`
"""
comp_id_list = []
curr.execute(sql)
mysqlRs = curr.fetchall()
for rs in mysqlRs:
comp_id_list.append(rs[0])
curr.close()
conn.close()
print "==== ==== ==== ====", current_time, "==== ==== ==== ===="
print current_time, comp_id_list
print "==== ==== ==== ====", current_time, "==== ==== ==== ===="
print "end...get_store_can_online_company_id_list"
return comp_id_list
def get_order_id_by_number(number_list):
print "start...get_order_id_by_number"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql = """
SELECT
id orderId,
number,
status,
acount_name
FROM
gmsales.sales_order so
WHERE
so.number IN %s
"""
temp_data = []
order_id_list = {}
order_number_list = []
for number in number_list:
order_number_list.append(str(number))
temp_data.append(tuple(order_number_list))
print sql % tuple(temp_data)
# for mysqlRs in curr.execute(sql, tuple(temp_data)):
# item_arr=[]
# item_arr.append(mysqlRs[0])
# item_arr.append(mysqlRs[1])
# order_id_list[str(mysqlRs[0])] = item_arr
curr.execute(sql % tuple(temp_data))
mysqlRs = curr.fetchall()
for rs in mysqlRs:
item_arr = []
item_arr.append(rs[0])
item_arr.append(rs[1])
item_arr.append(rs[2])
item_arr.append(rs[3])
order_id_list[str(rs[0])] = item_arr
curr.close()
conn.close()
print "end...get_order_id_by_number"
return order_id_list
def get_store_can_online_company_id_list():
print "start...get_store_can_online_company_id_list"
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql="""
SELECT DISTINCT(sc.`comp_id`)
FROM
`m2cchinaerp`.`store$company` sc,
`m2cchinaerp`.`store$banner` head_banner, `m2cchinaerp`.`store$cfg$banner` head_banner_cfg,
`m2cchinaerp`.`store$banner` hot_banner, `m2cchinaerp`.`store$cfg$banner` hot_banner_cfg
WHERE 1=1
AND sc.`status` = 0
AND sc.`logo` IS NOT NULL
AND head_banner.`comp_id` = sc.`comp_id` AND head_banner.`cfg_banner_id` = head_banner_cfg.`cfg_banner_id` and head_banner.`store_company_id` = sc.`store_company_id`
AND head_banner_cfg.`type` = 1 AND head_banner.`status` = 7 AND head_banner.`image` IS NOT NULL and head_banner_cfg.`market_region_code` = sc.`market_region_code`
AND hot_banner.`comp_id` = sc.`comp_id` AND hot_banner.`cfg_banner_id` = hot_banner_cfg.`cfg_banner_id` and hot_banner.`store_company_id` = sc.`store_company_id`
AND hot_banner_cfg.`type` = 2 AND hot_banner.`status` = 7 AND hot_banner.`image` IS NOT NULL AND hot_banner.`link_entity_id` IS NOT NULL and hot_banner_cfg.`market_region_code` = sc.`market_region_code`
AND EXISTS (
SELECT NULL FROM `gmcore`.`pro_product_base` pb
WHERE pb.`status` = 7 AND pb.`comp_id` = sc.`comp_id` and pb.market_region_code = sc.`market_region_code` GROUP BY pb.`comp_id` HAVING(COUNT(0) >= 10)
)
AND EXISTS (
SELECT NULL FROM `m2cchinaerp`.`store$group` sg, `m2cchinaerp`.`store$group_prod` sgp, `gmcore`.`pro_product_base` pb
WHERE sg.`group_id` = sgp.`group_id` AND sgp.`prod_base_id` = pb.`product_base_id` AND pb.`status` = 7
and sg.`store_company_id` = sc.`store_company_id`
and sgp.store_company_id = sc.store_company_id
and pb.market_region_code = sc.market_region_code
AND sg.`status` = 7 AND sg.`show_in_homepage` = 1
AND sg.`comp_id` = sc.`comp_id`
GROUP BY sg.`comp_id` HAVING(COUNT(0) > 3)
)
ORDER BY sc.`comp_id`
"""
comp_id_list = []
curr.execute(sql)
mysqlRs = curr.fetchall()
for rs in mysqlRs:
comp_id_list.append(rs[0])
curr.close()
conn.close()
print "==== ==== ==== ====",current_time,"==== ==== ==== ===="
print current_time,comp_id_list
print "==== ==== ==== ====",current_time,"==== ==== ==== ===="
print "end...get_store_can_online_company_id_list"
return comp_id_list
def get_order_id_by_number(number_list):
print "start...get_order_id_by_number"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql="""
SELECT
id orderId,
number,
status,
acount_name
FROM
gmsales.sales_order so
WHERE
so.number IN %s
"""
temp_data = []
order_id_list = {}
order_number_list = []
for number in number_list:
order_number_list.append(str(number))
temp_data.append(tuple(order_number_list))
print sql % tuple(temp_data)
# for mysqlRs in curr.execute(sql, tuple(temp_data)):
# item_arr=[]
# item_arr.append(mysqlRs[0])
# item_arr.append(mysqlRs[1])
# order_id_list[str(mysqlRs[0])] = item_arr
curr.execute(sql % tuple(temp_data))
mysqlRs = curr.fetchall()
for rs in mysqlRs:
item_arr=[]
item_arr.append(rs[0])
item_arr.append(rs[1])
item_arr.append(rs[2])
item_arr.append(rs[3])
order_id_list[str(rs[0])] = item_arr
curr.close()
conn.close()
print "end...get_order_id_by_number"
return order_id_list
def online_store_company():
print "start...online_store_company"
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql = """
UPDATE `m2cchinaerp`.`store$company` s, (
SELECT DISTINCT(sc.`comp_id`)
FROM
`m2cchinaerp`.`store$company` sc,
`m2cchinaerp`.`store$banner` head_banner, `m2cchinaerp`.`store$cfg$banner` head_banner_cfg,
`m2cchinaerp`.`store$banner` hot_banner, `m2cchinaerp`.`store$cfg$banner` hot_banner_cfg
WHERE 1=1
AND sc.`status` = 0
AND sc.`logo` IS NOT NULL
AND head_banner.`comp_id` = sc.`comp_id` AND head_banner.`cfg_banner_id` = head_banner_cfg.`cfg_banner_id` and head_banner.`store_company_id` = sc.`store_company_id`
AND head_banner_cfg.`type` = 1 AND head_banner.`status` = 7 AND head_banner.`image` IS NOT NULL and head_banner_cfg.`market_region_code` = sc.`market_region_code`
AND hot_banner.`comp_id` = sc.`comp_id` AND hot_banner.`cfg_banner_id` = hot_banner_cfg.`cfg_banner_id` and hot_banner.`store_company_id` = sc.`store_company_id`
AND hot_banner_cfg.`type` = 2 AND hot_banner.`status` = 7 AND hot_banner.`image` IS NOT NULL AND hot_banner.`link_entity_id` IS NOT NULL and hot_banner_cfg.`market_region_code` = sc.`market_region_code`
AND EXISTS (
SELECT NULL FROM `gmcore`.`pro_product_base` pb
WHERE pb.`status` = 7 AND pb.`comp_id` = sc.`comp_id` and pb.market_region_code = sc.`market_region_code` GROUP BY pb.`comp_id` HAVING(COUNT(0) >= 10)
)
AND EXISTS (
SELECT NULL FROM `m2cchinaerp`.`store$group` sg, `m2cchinaerp`.`store$group_prod` sgp, `gmcore`.`pro_product_base` pb
WHERE sg.`group_id` = sgp.`group_id` AND sgp.`prod_base_id` = pb.`product_base_id` AND pb.`status` = 7
and sg.`store_company_id` = sc.`store_company_id`
and sgp.store_company_id = sc.store_company_id
and pb.market_region_code = sc.market_region_code
AND sg.`status` = 7 AND sg.`show_in_homepage` = 1
AND sg.`comp_id` = sc.`comp_id`
GROUP BY sg.`comp_id` HAVING(COUNT(0) > 3)
)
) ols
SET s.`status` = 7 WHERE s.`comp_id` = ols.comp_id
"""
curr.execute(sql)
conn.commit()
curr.close()
conn.close()
print "end...online_store_company"
def online_store_company():
print "start...online_store_company"
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql="""
UPDATE `m2cchinaerp`.`store$company` s, (
SELECT DISTINCT(sc.`comp_id`)
FROM
`m2cchinaerp`.`store$company` sc,
`m2cchinaerp`.`store$banner` head_banner, `m2cchinaerp`.`store$cfg$banner` head_banner_cfg,
`m2cchinaerp`.`store$banner` hot_banner, `m2cchinaerp`.`store$cfg$banner` hot_banner_cfg
WHERE 1=1
AND sc.`status` = 0
AND sc.`logo` IS NOT NULL
AND head_banner.`comp_id` = sc.`comp_id` AND head_banner.`cfg_banner_id` = head_banner_cfg.`cfg_banner_id` and head_banner.`store_company_id` = sc.`store_company_id`
AND head_banner_cfg.`type` = 1 AND head_banner.`status` = 7 AND head_banner.`image` IS NOT NULL and head_banner_cfg.`market_region_code` = sc.`market_region_code`
AND hot_banner.`comp_id` = sc.`comp_id` AND hot_banner.`cfg_banner_id` = hot_banner_cfg.`cfg_banner_id` and hot_banner.`store_company_id` = sc.`store_company_id`
AND hot_banner_cfg.`type` = 2 AND hot_banner.`status` = 7 AND hot_banner.`image` IS NOT NULL AND hot_banner.`link_entity_id` IS NOT NULL and hot_banner_cfg.`market_region_code` = sc.`market_region_code`
AND EXISTS (
SELECT NULL FROM `gmcore`.`pro_product_base` pb
WHERE pb.`status` = 7 AND pb.`comp_id` = sc.`comp_id` and pb.market_region_code = sc.`market_region_code` GROUP BY pb.`comp_id` HAVING(COUNT(0) >= 10)
)
AND EXISTS (
SELECT NULL FROM `m2cchinaerp`.`store$group` sg, `m2cchinaerp`.`store$group_prod` sgp, `gmcore`.`pro_product_base` pb
WHERE sg.`group_id` = sgp.`group_id` AND sgp.`prod_base_id` = pb.`product_base_id` AND pb.`status` = 7
and sg.`store_company_id` = sc.`store_company_id`
and sgp.store_company_id = sc.store_company_id
and pb.market_region_code = sc.market_region_code
AND sg.`status` = 7 AND sg.`show_in_homepage` = 1
AND sg.`comp_id` = sc.`comp_id`
GROUP BY sg.`comp_id` HAVING(COUNT(0) > 3)
)
) ols
SET s.`status` = 7 WHERE s.`comp_id` = ols.comp_id
"""
curr.execute(sql)
conn.commit()
curr.close()
conn.close()
print "end...online_store_company"
def get_order_item_prepare_max_time():
print "start...get_order_item_prepare_max_time"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql="""
SELECT item.order_id order_id,
MAX(item.`processing_time`) process
FROM
`order_item` item
WHERE 1 = 1
GROUP BY item.order_id
ORDER BY item.order_id
"""
curr.execute(sql)
order_item_process = curr.fetchall()
curr.close()
conn.close()
print "end...get_order_item_prepare_max_time"
return order_item_process
def get_sku_span_prices_info():
print "start...get_sku_span_prices_info"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql="""
SELECT
ps.`product_id`,
ps.product_sku_id,
ps.`comp_id`,
ps.`span_trade_prices`
FROM
`erp$pro$product_sku` ps
"""
curr.execute(sql)
sku_spans = curr.fetchall()
curr.close()
conn.close()
print "end...get_sku_span_prices_info"
return sku_spans
def get_order_item_prepare_max_time():
print "start...get_order_item_prepare_max_time"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql = """
SELECT item.order_id order_id,
MAX(item.`processing_time`) process
FROM
`order_item` item
WHERE 1 = 1
GROUP BY item.order_id
ORDER BY item.order_id
"""
curr.execute(sql)
order_item_process = curr.fetchall()
curr.close()
conn.close()
print "end...get_order_item_prepare_max_time"
return order_item_process
def get_sku_span_prices_info():
print "start...get_sku_span_prices_info"
print MYSQL_USER
conn = mysqlDB.get_mysql_conn(MYSQL_USER, MYSQL_SERVER, MYSQL_PASSWORD,
MYSQL_DB, MYSQL_PORT)
curr = conn.cursor()
sql = """
SELECT
ps.`product_id`,
ps.product_sku_id,
ps.`comp_id`,
ps.`span_trade_prices`
FROM
`erp$pro$product_sku` ps
"""
curr.execute(sql)
sku_spans = curr.fetchall()
curr.close()
conn.close()
print "end...get_sku_span_prices_info"
return sku_spans