def get_column():
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = '''
SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a where c.relname = 't_sales_plan' and a.attrelid = c.oid and a.attnum>0 and col_description(a.attrelid,a.attnum) is not null
'''
cur.execute(sql)
plan_column = pg.fetchall(cur)
sql = '''
SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a where c.relname = 't_sales_plan' and a.attrelid = c.oid and a.attnum>0 and col_description(a.attrelid,a.attnum) is not null
'''
cur.execute(sql)
pos_column = pg.fetchall(cur)
sql = '''
SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a where c.relname = 't_sales_plan' and a.attrelid = c.oid and a.attnum>0 and col_description(a.attrelid,a.attnum) is not null
'''
cur.execute(sql)
saler_column = pg.fetchall(cur)
sql = '''
SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a where c.relname = 't_sales_plan' and a.attrelid = c.oid and a.attnum>0 and col_description(a.attrelid,a.attnum) is not null
'''
cur.execute(sql)
user_column = pg.fetchall(cur)
return plan_column, pos_column, saler_column, user_column
finally:
if cur: cur.close()
if conn: conn.close()
def get_pos_tag():
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (' select t.* from itd.t_sales_pos_tag t where t.deleted=0 ', )
cur.execute(''.join(sql))
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_datas(sql, args=None):
conn, cur = None, None
try:
_config = copy.deepcopy(config.pg_stand)
_config['schema'] ='public'
conn = pg.connect(**_config)
cur = conn.cursor()
cur.execute(sql, args)
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_pos_tag(tags):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (' select * from itd.t_sales_pos_tag t where t.deleted=0 ',
' and tag_id = any(%(tags)s)')
args = {'tags': tags}
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_sales_departs(channel_id, top=True):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = ('''select * from t_sales_depart where channel_id = %s''',
'' if top else ' and parent_id >0')
cur.execute(''.join(sql), (channel_id, ))
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_poi_tag(channel_name):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
' select * from public.t_rp_tag where 1=1 ',
' and tag_label= %(channel_name)s',
)
args = {'channel_name': channel_name}
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_channel_list(channel_id=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
' select c.* from itd.t_sales_channel c ',
' where 1=1 ',
' and channel_id=%(channel_id)s ' if channel_id else '',
)
args = {'channel_id': channel_id}
cur.execute(''.join(sql), args)
channels = pg.fetchall(cur)
return channels
finally:
if cur: cur.close()
if conn: conn.close()
def get_channels(top=False):
u'''
top 控制是否取区分中信息中市公司
'''
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = '''select * from t_sales_channel '''
cur.execute(sql)
rows = pg.fetchall(cur)
# Todo should get in sql
for row in rows:
row['departs'] = get_sales_departs(row['channel_id'], top)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def plan_export(channel_id,
charge_departs,
sales_dates=None,
status_id=None,
sales_depart_id=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
'''
select plan.pos_id 促销点ID, p.pos_name 促销点名称, p.is_charge 是否有租金,
c.channel_name 渠道, d.sales_depart_name 区分,
p.pos_man 责任人, plan.sales_date 促销日期, array_to_string(plan.sale_hour,',') 促销时刻,
plan.create_user_id 排产人ID, plan.saler_cnt 应到人数, array_to_string(plan.saler_mobiles,',') 促销人员 from itd.t_sales_plan plan
''',
' left join itd.t_sales_channel c on plan.channel_id=c.channel_id',
' left join itd.t_sales_depart d on plan.sales_depart_id=d.sales_depart_id',
' left join itd.t_sales_pos p on plan.pos_id=p.pos_id',
' where plan.channel_id=%(channel_id)s ',
' and plan.sales_depart_id=any(%(charge_departs)s) ',
' and plan.sales_date=any(%(sales_dates)s) '
if sales_dates else '',
' and plan.status=%(status_id)s ' if status_id else '',
' and plan.sales_depart_id=%(sales_depart_id)s '
if sales_depart_id else '',
)
args = {
'channel_id': channel_id,
'charge_departs': charge_departs,
'sales_dates': sales_dates,
'status_id': status_id,
'sales_depart_id': sales_depart_id
}
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_users(channel_id=None,
charge_departs=None,
sales_depart_id=None,
query=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = [
'''
select u.*,c.channel_name,d.sales_depart_name from t_sales_user u
left join t_sales_channel c
on u.channel_id=c.channel_id
left join t_sales_depart d
on u.sales_depart_id=d.sales_depart_id
where 1 = 1
''',
' and u.sales_depart_id in %(charge_departs)s'
if charge_departs else ''
' and u.channel_id = %(channel_id)s' if channel_id else '',
' and u.sales_depart_id = %(sales_depart_id)s'
if sales_depart_id else '',
' and (u.user_name like %(query)s or u.mobile like %(query)s) '
if query else '',
]
args = {
'channel_id': channel_id,
'charge_departs': charge_departs,
'sales_depart_id': sales_depart_id,
'query': '%%%s%%' % (query, ) if query else None
}
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
result = rows
return result
finally:
if cur: cur.close()
if conn: conn.close()
def get_user_local_info(user_id):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = '''
select u.*, d.charge_departs , d.charge_departs_info,
dd.sales_depart_name, ch.channel_name
from t_sales_user u
left join v_sales_depart d
on u.sales_depart_id = d.sales_depart_id
left join t_sales_depart dd
on u.sales_depart_id = dd.sales_depart_id
left join t_sales_channel ch
on u.channel_id= ch.channel_id
where user_id = %s
'''
cur.execute(sql, (user_id, ))
rows = pg.fetchall(cur)
return rows[0] if rows else None
finally:
if cur: cur.close()
if conn: conn.close()
def get_depart_list(charge_departs=None, sales_depart_id=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
' select d.* from itd.t_sales_depart d ',
' where 1=1 ',
' and sales_depart_id = any(%(charge_departs)s) '
if charge_departs else '',
' and sales_depart_id = %(sales_depart_id)s '
if sales_depart_id else '',
)
args = {
'charge_departs': charge_departs,
'sales_depart_id': sales_depart_id
}
cur.execute(''.join(sql), args)
departs = pg.fetchall(cur)
return departs
finally:
if cur: cur.close()
if conn: conn.close()
def get_saler_list(q=None,
mobile=None,
mobiles=None,
channel_id=None,
deleted=None,
sales_depart_ids=None,
page=None,
page_size=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
'''
select s.mobile,s.saler_name,s.channel_id,s.sales_depart_id,
s.unit,s.deleted,array_to_string(s.develop_id,','),s.create_user_id,ch.channel_name,d.sales_depart_name
''',
' from t_sales_saler s ',
' left join t_sales_channel ch ',
' on s.channel_id = ch.channel_id ',
' left join t_sales_depart d ',
' on s.sales_depart_id = d.sales_depart_id ',
' where 1 = 1 ',
' and s.mobile = %(mobile)s ' if mobile else '',
' and s.mobile = any(%(mobiles)s) ' if mobiles else '',
' and s.channel_id = %(channel_id)s ' if channel_id else '',
' and s.deleted = %(deleted)s ' if deleted != None else '',
'''
and s.sales_depart_id=any(%(sales_depart_ids)s)
''' if sales_depart_ids else '',
' and (s.saler_name like %(q)s or s.mobile like %(q)s) '
if q else '',
' order by mobile desc ',
' limit %(limit)s offset %(offset)s '
if isinstance(page, int) and isinstance(page_size, int) else '',
)
#print mobiles, 'wtf......'
args = {
'q':
'%%%s%%' % (q, ) if q else None,
'mobile':
mobile,
'channel_id':
channel_id,
'deleted':
deleted,
'sales_depart_ids':
sales_depart_ids,
'limit':
page_size + 1 if isinstance(page_size, int) else None,
'offset': (page - 1) * page_size
if isinstance(page, int) and isinstance(page_size, int) else None,
'mobiles':
mobiles,
}
cur.execute(''.join(sql), args)
# print ''.join(sql) % args
rows = pg.fetchall(cur)
return rows
finally:
if cur: cur.close()
if conn: conn.close()
def get_audit_list(channel_id=None,
charge_departs=None,
pageCurrent=None,
pageSize=None,
sales_depart_id=None,
selectedTag=None,
status_id=None,
queryPoi=None,
queryMan=None):
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = (
' select p.*, ', ' public.st_AsText(p.geo_data) wkt, ',
' s.saler_name,s.mobile,t.tag_label,sta.status_label ',
' from public.t_rp_poi p ', ' left join public.t_rp_sms_user u',
' on p.create_user_id=u.user_id ',
' left join itd.t_sales_saler s ', ' on u.bind_mobile=s.mobile ',
' left join public.t_rp_tag t ', ' on p.reporter_tag=t.tag ',
' left join public.t_rp_status sta ', ' on p.status=sta.status ',
' where 1=1 ',
' and s.channel_id=%(channel_id)s' if channel_id else '',
' and s.sales_depart_id=any(%(charge_departs)s)' if charge_departs
else '', ' and s.sales_depart_id=%(sales_depart_id)s'
if sales_depart_id else '',
' and p.reporter_tag=%(selectedTag)s' if selectedTag else '',
' and p.status=%(status_id)s' if status_id else '',
' and (p.poi_name like %(queryPoi)s or p.address like %(queryPoi)s)'
if queryPoi else '',
' and (u.bind_mobile like %(queryMan)s or u.full_name like %(queryMan)s)'
if queryMan else '', ' order by p.poi_id desc ',
' limit %(limit)s offset %(offset)s ' if isinstance(pageSize, int)
and isinstance(pageCurrent, int) else '')
args = {
'channel_id':
channel_id,
'charge_departs':
charge_departs,
'sales_depart_id':
sales_depart_id,
'selectedTag':
selectedTag,
'status_id':
status_id,
'queryPoi':
'%%%s%%' % queryPoi if queryPoi else '',
'queryMan':
'%%%s%%' % queryMan if queryMan else '',
'limit':
pageSize,
'offset':
(pageCurrent - 1) * pageSize if isinstance(pageSize, int)
and isinstance(pageCurrent, int) else None
}
#print ''.join(sql) % args
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
cur.execute(''.join(sql[:-2]), args)
cnt = len(pg.fetchall(cur))
return rows, cnt
finally:
if cur: cur.close()
if conn: conn.close()
def get_pos_list(q=None,
pos_id=None,
channel_id=None,
pos_type=None,
pos_name=None,
sales_depart_ids=None,
deleted=-1,
located=None,
is_charge=None,
pageCurrent=None,
pageSize=None):
u'''
deleted = -1 全部
'''
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = ('''
select p.* , ch.channel_name, d.sales_depart_name
from t_sales_pos p
left join t_sales_channel ch on p.channel_id = ch.channel_id
left join t_sales_depart d on p.sales_depart_id = d.sales_depart_id
where 1= 1
''', ' ' if deleted == -1 else ' and p.deleted =%(deleted)s ',
'and p.channel_id=%(channel_id)s ' if channel_id else ' ', '''
and p.sales_depart_id=any(%(sales_depart_ids)s)
''' if sales_depart_ids else ' ',
' and p.pos_name=%(pos_name)s ' if pos_name else '',
' and pos_type = %(pos_type)s ' if pos_type else '',
' and p.pos_id = %(pos_id)s ' if pos_id else ' ',
' and p.lng is null or p.lat is null ' if located == 0 else '',
' and p.lng is not null and p.lat is not null'
if located == 1 else '',
" and p.is_charge = %(is_charge)s " if is_charge else '', u'''
and (p.pos_name like %(q)s
or p.pos_address like %(q)s)
''' if q else ' ', ' order by p.pos_id desc ',
' limit %(limit)s offset %(offset)s ' if isinstance(
pageSize, int) and isinstance(pageCurrent, int) else '')
args = {
'q':
'%%%s%%' % q if q else '',
'pos_id':
pos_id,
'pos_name':
pos_name,
'channel_id':
channel_id,
'sales_depart_ids':
sales_depart_ids,
'deleted':
deleted,
'pos_type':
pos_type,
'is_charge':
is_charge,
'limit':
pageSize if isinstance(pageSize, int) else None,
'offset':
(pageCurrent - 1) * pageSize if isinstance(pageCurrent, int)
and isinstance(pageSize, int) else None
}
#print ''.join(sql) % args
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
cur.execute(''.join(sql[:-2]), args)
cnt = len(pg.fetchall(cur))
return rows, cnt
finally:
if cur: cur.close()
if conn: conn.close()
def get_plan_list(channel_id=None,
charge_departs=None,
sales_depart_id=None,
pos_type=None,
is_charge=None,
queryPos=None,
create_user_id=None,
pos_id=None,
sales_date=None,
plan_id=None,
status=None,
page=1,
page_size=100):
u''' status is a list args'''
conn, cur = None, None
try:
conn = pg.connect(**config.pg_main)
cur = conn.cursor()
sql = [
'''
select p.*, ch.channel_name, d.sales_depart_name,pos.pos_name,pos.pos_address,
u.mobile as create_mobile,u.user_name as create_user,pos.lng,pos.lat,
(select array_agg(row_to_json(s))
from t_sales_saler s
where s.mobile = any( p.saler_mobiles))
as salers
from t_sales_plan p
left join t_sales_channel ch
on p.channel_id = ch.channel_id
left join t_sales_depart d
on p.sales_depart_id = d.sales_depart_id
left join t_sales_pos pos
on p.pos_id=pos.pos_id
left join t_sales_user u
on p.create_user_id=u.user_id
where 1=1 and sales_date>=to_char(current_timestamp,'YYYYMMDD')
''', ' and p.channel_id=%(channel_id)s ' if channel_id else '',
' and p.sales_depart_id in %(charge_departs)s' if charge_departs
else '', ' and p.sales_depart_id = %(sales_depart_ids)s'
if sales_depart_id else '',
' and p.plan_id=%(plan_id)s' if plan_id else '',
' and p.status in (1,2,4,5)',
' and p.status=any(%(status)s)' if status else '',
' and p.create_user_id = %(create_user_id)s' if create_user_id else
'', ' and p.pos_id = %(pos_id)s' if pos_id else '',
' and p.sales_date=any(%(sales_date)s)' if sales_date else '',
' and pos.pos_type = %(pos_type)s' if pos_type else '',
' and pos.is_charge= %(is_charge)s' if is_charge else '',
' and pos.pos_name like %(queryPos)s' if queryPos else '',
' order by p.plan_id desc ', ' limit %(limit)s offset %(offset)s '
]
args = {
'channel_id': channel_id,
'charge_departs': charge_departs,
'sales_depart_ids': sales_depart_id,
'pos_type': pos_type,
'is_charge': is_charge,
'queryPos': '%%%s%%' % queryPos if queryPos else '',
'create_user_id': create_user_id,
'plan_id': plan_id,
'status': status,
'pos_id': pos_id,
'sales_date':
sales_date if isinstance(sales_date, list) else '{%s}' %
sales_date,
'limit': page_size,
'offset': (page - 1) * page_size,
}
#print ''.join(sql) % args
cur.execute(''.join(sql), args)
rows = pg.fetchall(cur)
cur.execute(''.join(sql[:-2]), args)
cnt = len(pg.fetchall(cur))
return rows, cnt
finally:
if cur: cur.close()
if conn: conn.close()