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