def add(bom_name, bom_code, com_name, com_code, com_cnt, com_unit, com_unit_price, com_rate): print(com_unit_price) print(com_cnt) conn = get_conn() cur = conn.cursor() res = dict() if bom_code == com_code: res["status"] = 0 res["msg"] = u"不能组成自身" elif check_add_exist(cur, bom_code, com_code): res["status"] = 0 res["msg"] = u"组件已在该物料组成中" else: sql = "insert into " + table_name \ + "(bom_name, bom_code, com_name, com_code, com_cnt, com_unit, " \ "com_unit_price, com_rate) " \ "VALUES ('%s','%s','%s','%s',%f,'%s',%f,%d)" % \ (bom_name, bom_code, com_name, com_code, float(com_cnt), com_unit, float(com_unit_price), int(com_rate)) cur.execute(sql) conn.commit() total = cur.rowcount # cur.close() # conn.close() res = get_res_dict(total) return res
def get_page(page_number, page_size, bom_name=None, bom_code=None): count_sql = "select count(1) AS num from %s where 1>0 " % (table_name, ) if int(page_number) <= 1: pager_sql = "select top %d * from %s WHERE bom_id>0 " % ( int(page_size), table_name) else: pager_sql = "select top %d * from %s WHERE bom_id not in(select top %d bom_id from %s) " %\ (int(page_size), table_name, int(page_number-1) * int(page_size), table_name) if is_not_blank(bom_name): pager_sql += " and bom_name like '%%%s%%'" % (bom_name, ) count_sql += " and bom_name like '%%%s%%'" % (bom_name, ) if is_not_blank(bom_code): pager_sql += " and bom_code like '%%%s%%'" % (bom_code, ) count_sql += " and bom_code like '%%%s%%'" % (bom_code, ) # select top 分页条数 * from 表名 where 主键 not in(select top 20 主键 from 表名) res = dict() cur = get_conn().cursor() cur.execute(count_sql) res["total"] = cur.fetchone()["num"] print(cur.rowcount) print(pager_sql) cur.execute(pager_sql) data = get_result_dict(cur) cur.close() res["pageNumber"] = page_number res["pageSize"] = page_size res["data"] = data return res
def get_rate_s(): sql = "select distinct bom_rate from %s " % (table_name, ) res = dict() cur = get_conn().cursor() cur.execute(sql) data = get_result_dict(cur) cur.close() return data
def get_all_for_tree(): sql = "select * from %s " % (table_name, ) res = dict() cur = get_conn().cursor() cur.execute(sql) data = get_result_dict(cur) cur.close() return data
def get_by_id(bom_id): conn = get_conn() cur = conn.cursor() sql = "select * from " + table_name + " where bom_id=%d" % (int(bom_id), ) cur.execute(sql) res = cur.fetchone() cur.close() # conn.close() return res
def get_by_bom_code(bom_code): conn = get_conn() cur = conn.cursor() sql = "select * from " + table_name + " where bom_code='%s'" % (bom_code,) cur.execute(sql) res = get_result_dict(cur) cur.close() # conn.close() return res
def get_by_code(bom_code=None): sql = "select * from %s " % (table_name, ) if is_not_blank(bom_code): sql += " where bom_code = '%s'" % (bom_code, ) res = dict() cur = get_conn().cursor() cur.execute(sql) data = get_result_dict(cur) cur.close() return data
def delete(bom_id): res = dict() conn = get_conn() cur = conn.cursor() sql = "delete from " + table_name + " where bom_id=%d" % (int(bom_id), ) cur.execute(sql) conn.commit() total = cur.rowcount res = get_res_dict(total) return res
def delete_by_bom_code(bom_code): res = dict() conn = get_conn() cur = conn.cursor() sql = "delete from " + table_name + " where bom_code='%s'" % (bom_code,) print(sql) cur.execute(sql) conn.commit() total = cur.rowcount res = get_res_dict(total) return res
def calculate_all_cost(bom_name=None, bom_code=None): product_s = bom_product_mgr.get_page(1, sys.maxint, bom_name, bom_code).get("data") counter = 0 conn = get_conn() cur = conn.cursor() for product in product_s: bom_code = product.get("bom_code") bom_cost = calculate_product_cost(bom_code) sql = "update " + table_name + " set bom_cost=%f where bom_code='%s'" \ % (bom_cost, bom_code) cur.execute(sql) if counter == 100: conn.commit() counter = 0 conn.commit()
def get_tree(): # 以后每次需要数据库连接就是用connection()函数获取连接就好了 conn = db_util.get_conn() cur = conn.cursor() sql = "select * from t_menu WHERE pid=%d AND display=1 ORDER BY disorder" % ( 0, ) cur.execute(sql) result_list = db_util.get_result_dict(cur) for result_dict in result_list: sql = "select * from t_menu WHERE pid=%d AND display=1 ORDER BY disorder" % ( result_dict['id'], ) cur.execute(sql) r_2 = db_util.get_result_dict(cur) result_dict['children'] = r_2 # cur.close() # conn.close() return result_list
def add(bom_name, bom_code, factory_code): conn = get_conn() cur = conn.cursor() res = dict() if check_add_exist(cur, table_name, "bom_name", bom_name): res["status"] = 0 res["msg"] = u"物料名称已存在" elif check_add_exist(cur, table_name, "bom_code", bom_code): res["status"] = 0 res["msg"] = u"物料编码已存在" else: sql = "insert into " + table_name + "(bom_name, bom_code, factory_code) " \ "VALUES ('%s','%s',%d)" % (bom_name, bom_code, int(factory_code)) cur.execute(sql) conn.commit() total = cur.rowcount # cur.close() # conn.close() res = get_res_dict(total) return res
def add(bom_name, bom_code, bom_rate, bom_unit, bom_unit_price, is_produce): conn = get_conn() cur = conn.cursor() res = dict() if check_add_exist(cur, table_name, "bom_name", bom_name): res["status"] = 0 res["msg"] = u"用料名称已存在" elif check_add_exist(cur, table_name, "bom_code", bom_code): res["status"] = 0 res["msg"] = u"用料编码已存在" else: sql = "insert into " + table_name + "(bom_name, bom_code, bom_rate, bom_unit, bom_unit_price, " \ "is_produce) VALUES ('%s','%s',%d,'%s',%f,%d)" % \ (bom_name, bom_code, int(bom_rate), bom_unit, float(bom_unit_price), int(is_produce)) cur.execute(sql) conn.commit() total = cur.rowcount # cur.close() # conn.close() res = get_res_dict(total) return res
def update(c_id, bom_name, bom_code, com_name, com_code, com_cnt, com_unit, com_unit_price, com_rate, factory_code): conn = get_conn() cur = conn.cursor() res = dict() # def check_update_exist(cur, c_id, bom_code, com_code): if check_update_exist(cur, c_id, bom_code, com_code): res["status"] = 0 res["msg"] = u"组件已在该物料组成中" else: sql = "update " + table_name + " set bom_name='%s', bom_code='%s',com_name='%s', com_code='%s'," \ "com_cnt=%f, com_unit='%s', com_unit_price=%f, com_rate=%d,factory_code=%d where id=%d" \ % (bom_name, bom_code, com_name, com_code, float(com_cnt), com_unit, float(com_unit_price), int(com_rate), int(factory_code), int(c_id)) print(sql) cur.execute(sql) conn.commit() total = cur.rowcount cur.close() # conn.close() res = get_res_dict(total) return res
def update(bom_id, bom_name, bom_code, factory_code): conn = get_conn() cur = conn.cursor() res = dict() if check_update_exist(cur, table_name, "bom_name", bom_name, "bom_id", int(bom_id)): res["status"] = 0 res["msg"] = u"物料名称已存在" elif check_update_exist(cur, table_name, "bom_code", bom_code, "bom_id", int(bom_id)): res["status"] = 0 res["msg"] = u"物料编码已存在" else: sql = "update " + table_name + " set bom_name='%s', bom_code='%s', factory_code=%d " \ "where bom_id=%d" \ % (bom_name, bom_code, int(factory_code), int(bom_id)) cur.execute(sql) conn.commit() total = cur.rowcount cur.close() # conn.close() res = get_res_dict(total) return res
def update(bom_id, bom_name, bom_code, bom_rate, bom_unit, bom_unit_price, is_produce): conn = get_conn() cur = conn.cursor() res = dict() if check_update_exist(cur, table_name, "bom_name", bom_name, "bom_id", int(bom_id)): res["status"] = 0 res["msg"] = u"用料名称已存在" elif check_update_exist(cur, table_name, "bom_code", bom_code, "bom_id", int(bom_id)): res["status"] = 0 res["msg"] = u"用料编码已存在" else: sql = "update " + table_name + " set bom_name='%s', bom_code='%s', bom_rate=%d " \ " , bom_unit='%s',bom_unit_price=%f, is_produce=%d where bom_id=%d" \ % (bom_name, bom_code, int(bom_rate), bom_unit, float(bom_unit_price),int(is_produce), int(bom_id)) cur.execute(sql) conn.commit() total = cur.rowcount cur.close() # conn.close() res = get_res_dict(total) return res