def append_seller_identity_contract(db): query = 'select id, seller_id from contracts ' seller_id = pg_query(db, query) for item in seller_id: query ='select identity_num from sellers where id=%s'% (item[1]) ids = pg_query(db, query) if ids[0][0]: match = re.search(r'[\w-]+', ids[0][0]).group() query2 = "update contracts set identity_num='%s' where id=%s" % (match, item[0]) pg_write(db, query2)
def get_sellers_name(db='pyaution'): query = 'select name from sellers where activity_id=1' records = pg_query(db, query) names =[] for name in records: names.append(name[0]) return names
def append_dept_contract(db): query = "select id, num from contracts" id_num = pg_query(db, query) # tuple in list for item in id_num: for dept in dept_mapping: if dept in item[1]: query = "update contracts set dept_no='%s' where id=%s" % (dept_mapping[dept], item[0]) print query pg_write(db, query)
def get_info_antiques(auction_id, activity_id, prefix): """ 导入拍品信息 :param auction_id: :param activity_id: :return: """ query = ( " SELECT a.name,a.name_en,a.lot,c.created_at,c.updated_at,c.num,c.dept_no," # 7 "a.age,a.age_en,a.size,base_price," # 11 " a.author, a.id" " from antiques as a left JOIN contracts as c " " on a.contract_id=c.id " "where a.activity_id=%s") % (activity_id) lots = pg_query(db, query) count = 0 tmp_list = [] for lot in lots: lot_name = lot[0] if "'" not in lot[0] else lot[0].replace( "'", "''") # 拍品中文名字 lot_no = lot[2] # 图录号 create_time = lot[3] # 创建时间 last_update_time = lot[4] # 修改时间 contract_id = lot[5] # 合同关联id role_id = lot[6] or '' # 部门 age = lot[7] or '' # 年代 age_en = lot[8] or '' dimension = lot[9] or '' # 尺寸 bottom_price = lot[10] or 0 # 起步价 author_zh = lot[11] or '' auction_id = auction_id ids = prefix + str(lot[12]) status = 400 tmp_list.append([ lot_name, lot_no, create_time, last_update_time, contract_id, role_id, age, age_en, dimension, bottom_price, author_zh, auction_id, ids, status ]) # query_insert = "insert into lot (lot_name,lot_no,create_time,last_update_time,contract_id,role_id," \ # "age,age_en, o_dimension,bottom_price,author_zh,auction_id,id, status) " \ # " VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % \ # (lot_name, lot_no, create_time, last_update_time, contract_id, role_id, age, # age_en, dimension, bottom_price, author_zh, auction_id, ids, status) query_insert = "insert into lot (lot_name,lot_no,create_time,last_update_time,contract_id,role_id," \ "age,age_en, o_dimension,bottom_price,author_zh,auction_id,id, status) " \ " VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" # print tmp_list my_insert(query_insert, tmp_list) tmp_list = []
def crm_client(): query = "select number from crms_paper where number <>'' " ids_from_pg = pg_query('CRM', query) ids_from_pg = [item[0] for item in ids_from_pg] for num in ids_from_pg: try: # print '->>',num match = re.search(r'[\w-]+', num).group() # print match except re.error as e: print e query = "select identity_number from contract where identity_number LIKE '%s'" % ('%%%s%%' % match) records = my_query(query) if records: print match
def check_identity(): query = "select identity_num from sellers where identity_num <>'' " ids_from_pg = pg_query(db, query) ids_from_pg = [item[0] for item in ids_from_pg] for num in ids_from_pg: try: # print '->>',num match = re.search(r'[\w-]+', num).group() # print match except re.error as e: print e query = "select number from customer_identity where number LIKE '%s'" % ('%%%s%%' % match) records = my_query(query) if records: print match
def handler_contracts(db, prefix, activity_id, auction_id): """ created_at <---> created_at updated_at <---> last_updated_at num # 合同号 <---> electronic_id `seller_id` <---> customer_id activitiy_id <---> action_id seller.num <---> identity_number :return: """ query_contracts = ( " SELECT c.created_at, c.updated_at, c.seller_id, b.identity_num, c.dept_no,c.num,c.id,b.name " " from contracts as c left join sellers as b " " on c.seller_id=b.id where c.activity_id=%s") % activity_id records = pg_query(db, query_contracts) args = [] for record in records: count = 0 create_time = record[0] or ' ' last_update_time = record[1] or ' ' electronic_id = record[5] or ' ' role_id = record[4] or ' ' identitiy_no = record[3] or ' ' ids = prefix + str(record[6]) count += 1 name = record[7] if "'" not in record[7] else record[7].replace( "'", "''") args.append([ids, create_time, last_update_time, electronic_id, role_id, auction_id, identitiy_no, name]) query_insert = "INSERT INTO contract (ID, create_time, last_update_time, electronic_id, role_id, auction_id,identity_number,customer_name)" \ " VALUES (%s, %s, %s,%s,%s,%s,%s,%s)" my_insert(query_insert, args) print '--------------------------------------->' print u'第[%s]届总计完成' % auction_id