def set_user_base_info(user_info): u''' 登入接口更新本地用户基本信息, 存在则更新, 不存在则插入 user_id 为集团邮箱 不应该手动去修改或调用改接口 ''' conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ''' insert into t_sales_user (user_id, user_name, mobile,privs ) select %(user_id)s, %(user_name)s, %(mobile)s, %(privs)s where not exists (select 1 from t_sales_user where user_id=%(user_id)s) ''' user_info['privs'] = [] cur.execute(sql, user_info) if cur.rowcount != 1: sql = ''' update t_sales_user set user_name = %(user_name)s , mobile = %(mobile)s, last_sync_time = current_timestamp where user_id = %(user_id)s ''' cur.execute(sql, user_info) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
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 plan_audit(status_id, status, channel_id, charge_departs, selected_plan): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ( ' update itd.t_sales_plan ', ' set status=%(status_id)s ', ' where 1=1 ', ' and status=any(%(status)s) ', ' and channel_id=%(channel_id)s ', ' and sales_depart_id = any(%(charge_departs)s) ', ' and plan_id=any(%(selected_plan)s) ', ) args = { 'status_id': status_id, 'status': status, 'channel_id': channel_id, 'charge_departs': charge_departs, 'selected_plan': selected_plan } cur.execute(''.join(sql), args) conn.commit() return cur.rowcount finally: if cur: cur.close() if conn: conn.close()
def update_pos(pos_info): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() keys = ( 'pos_type', 'deleted', 'sales_id', 'pos_name', 'pos_address', 'channel_id', 'sales_depart_id', 'pos_unit', 'pos_code', 'geo_data', 'pos_man', 'pos_man_mobile', 'update_user_id', ) items = [] for key in pos_info: if key in keys: items.append(' %s=%%(%s)s ' % (key, key)) sql = [ ''' update t_sales_pos set update_time=current_timestamp,''', ','.join(items), ''' where pos_id = %(pos_id)s ''' ] cur.execute(''.join(sql), pos_info) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
def pos_import(rows): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ''' insert into t_sales_pos ( pos_id, pos_type, sales_id, pos_name, pos_address, channel_id, sales_depart_id, pos_unit, pos_code, pos_man, pos_man_mobile, create_user_id,is_charge ) values( nextval('seq_t_sales_pos'), %(pos_type)s, %(sales_id)s, %(pos_name)s, %(pos_address)s, %(channel_id)s, %(sales_depart_id)s, %(pos_unit)s, %(pos_code)s, %(pos_man)s, %(pos_man_mobile)s, %(create_user_id)s,%(is_charge)s ); ''' cur.executemany(sql, rows) result = cur.rowcount == len(rows) if result: conn.commit() return result finally: if cur: cur.close() if conn: conn.close()
def update_plan(plan_info): u''' 使用最多的是更新status ''' conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() plan_id = plan_info.get('plan_id') keys = ( #'plan_id', #'channel_id', 'sales_depart_id', 'pos_id', #'saler_mobiles', 'sales_date', 'saler_cnt', 'remark' , 'audit_user_id', 'status') args = {} args.update(plan_info) items = [] for key in args: if key not in keys: continue items.append(' %s=%%(%s)s ' % (key, key)) sql = [ ''' update t_sales_plan set update_time = current_timestamp, ''', ','.join(items), 'where plan_id = %(plan_id)s ' ] #print ''.join(sql) % args cur.execute(''.join(sql), args) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
def add_saler(saler): conn, cur = None, None try: keys = ('mobile', 'saler_name', 'channel_id', 'sales_depart_id', 'unit', 'create_user_id') conn = pg.connect(**config.pg_main) cur = conn.cursor() args = {} args.update(saler) for k in keys: if k not in args: args[k] = None sql = ''' insert into t_sales_saler ( mobile, saler_name, channel_id, sales_depart_id, unit, create_user_id )values( %(mobile)s, %(saler_name)s, %(channel_id)s, %(sales_depart_id)s, %(unit)s, %(create_user_id)s ) ''' cur.execute(sql, args) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
def get_pos(): conn, cur = None, None try: conn = pg.connect(**pg.main) cur = conn.cursor() finally: if cur: cur.close() if conn: conn.close()
def add_plans(channel_id, sales_depart_id, create_user_id, plans): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() keys = ('pos_id', 'saler_mobiles', 'sales_date', 'saler_cnt', 'remark', 'sale_hour') rows = [] for p in plans: args = { 'channel_id': channel_id, 'sales_depart_id': sales_depart_id, 'create_user_id': create_user_id, } for key in keys: args[key] = p.get(key, None) args['sale_hour'] = '{' + args['sale_hour'] + '}' rows.append(args) # 更新 未审核的一个点同一个日期只能有一个 sql = ''' update t_sales_plan set status = 6 where status in (1, 2) and pos_id = %(pos_id)s and sales_date = %(sales_date)s ''' cur.executemany(sql, rows) sql = ''' insert into t_sales_plan( plan_id, channel_id, sales_depart_id, pos_id, saler_mobiles, sales_date, saler_cnt, remark, create_user_id, sale_hour )values( nextval('seq_t_sales_plan'), %(channel_id)s, %(sales_depart_id)s, %(pos_id)s, %(saler_mobiles)s, %(sales_date)s, %(saler_cnt)s, %(remark)s, %(create_user_id)s, %(sale_hour)s ) ''' cur.executemany(sql, rows) conn.commit() return cur.rowcount 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 update_saler(mobile, channel_id=None, sales_depart_id=None, saler_name=None, unit=None, deleted=None, update_user_id=None): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() cr = conn.cursor() # print deleted # print mobile # print channel_id sql = (''' update t_sales_saler set update_time = current_timestamp, ''', ' channel_id=%(channel_id)s, ' if channel_id else '', ' sales_depart_id=%(sales_depart_id)s, ' if sales_depart_id else '', ' saler_name=%(saler_name)s, ' if saler_name else '', ' unit=%(unit)s, ' if unit else '', ' deleted=%(deleted)s, ' if deleted else '', ' last_update_user_id = %(update_user_id)s ' if update_user_id else '', ' where mobile = %(mobile)s') sqll="update itd.t_sales_saler set deleted ='%s' " \ "where mobile ='%s'"%(deleted,mobile) args = { 'mobile': mobile, 'channel_id': channel_id, 'sales_depart_id': sales_depart_id, 'saler_name': saler_name, 'unit': unit, 'deleted': deleted, 'update_user_id': update_user_id } cur.execute(''.join(sql), args) conn.commit() cr.execute(sqll) conn.commit() cr.close() return cur.rowcount == 1 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 pos_audit(selectedPoi, status, queryStatus): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ( ' update public.t_rp_poi set status=%(status)s where status=any(%(queryStatus)s) and poi_id=any(%(selectedPoi)s)' ) args = { 'selectedPoi': selectedPoi, 'status': status, 'queryStatus': queryStatus } cur.execute(sql, args) conn.commit() return cur.rowcount finally: if cur: cur.close() if conn: conn.close()
def del_pos(pos_id, user_id=None): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ''' update t_sales_pos set deleted = 1 , update_time = current_timestamp, update_user_id = %s where deleted = 0 and pos_id = %s ''' cur.execute(sql, ( user_id, pos_id, )) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
def add_plan(plan_info): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() cur.execute("select nextval('seq_t_sales_plan')") plan_id = cur.fetchone()[0] args = {'plan_id': plan_id} keys = ('channel_id', 'sales_depart_id', 'pos_id', 'saler_mobiles', 'sales_date', 'saler_cnt', 'remark', 'create_user_id') for key in keys: args[key] = plan_info.get(key, None) sql = ''' insert into t_sales_plan( plan_id, channel_id, sales_depart_id, pos_id, saler_mobiles, sales_date, saler_cnt, remark, create_user_id )values( %(plan_id)s, %(channel_id)s, %(sales_depart_id)s, %(pos_id)s, %(saler_mobiles)s, %(sales_date)s, %(saler_cnt)s, %(remark)s, %(create_user_id)s ) ''' cur.execute(sql, args) conn.commit() return plan_id if cur.rowcount == 1 else None 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 saler_import(rows): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ''' insert into t_sales_saler ( mobile, saler_name, channel_id, sales_depart_id, unit, create_user_id,develop_id )values( %(mobile)s, %(saler_name)s, %(channel_id)s, %(sales_depart_id)s, %(unit)s, %(create_user_id)s, %(develop_id)s ) ''' cur.executemany(sql, rows) if cur.rowcount == len(rows): conn.commit() return cur.rowcount == len(rows) finally: if cur: cur.close() if conn: conn.close()
def sms_user_import(rows): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ( ' insert into public.t_rp_sms_user', ' (user_id,bind_mobile,full_name,reg_date,status)', ' select ', " nextval('public.seq_rp_sms_user_id'),'%(mobile)s',%(saler_name)s,current_timestamp,1 ", ' where not exists (select 1', ' from public.t_rp_sms_user', " where bind_mobile='%(mobile)s')", ) cur.executemany(''.join(sql), rows) result = cur.rowcount if result > 0: conn.commit() return result finally: if cur: cur.close() if conn: conn.close()
def set_user_sales_info(user_id, channel_id, sales_depart_id, op_user_id=None): u''' 管理人员可以在本地没有改用户信息的时候设置 用户的 信息 ''' conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() args = { 'user_id': user_id, 'channel_id': channel_id, 'sales_depart_id': sales_depart_id, 'last_update_user_id': op_user_id } sql = ''' insert into t_sales_user (user_id, channel_id, sales_depart_id, last_update_time, last_update_user_id) select %(user_id)s, %(channel_id)s, %(sales_depart_id)s, current_timestamp, %(last_update_user_id)s where not exists (select 1 from t_sales_user where user_id=%(user_id)s ) ''' cur.execute(sql, args) if cur.rowcount != 1: sql = ''' update t_sales_user set channel_id = %(channel_id)s , sales_depart_id = %(sales_depart_id)s, last_update_time = current_timestamp, last_update_user_id = %(last_update_user_id)s where user_id = %(user_id)s ''' cur.execute(sql, args) conn.commit() return cur.rowcount == 1 finally: if cur: cur.close() if conn: conn.close()
def set_user_all(user_id=None, adminuser_id=None, channel_id=None, sales_depart_id=None, user_name=None, privs=None, tags=None): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() sql = ( ' update itd.t_sales_user set ', ' channel_id = %(channel_id)s, ' if channel_id else '', ' sales_depart_id = %(sales_depart_id)s, ' if sales_depart_id else '', ' user_name = %(user_name)s, ' if user_name else '' ' privs = %(privs)s ,' if privs else '', ' tags = %(tags)s ,' if tags else '', ' last_update_user_id = %(adminuser_id)s,', ' last_update_time = current_timestamp ' ' where user_id=%(user_id)s ', ) args = { 'user_id': user_id, 'channel_id': channel_id, 'sales_depart_id': sales_depart_id, 'user_name': user_name, 'privs': privs, 'tags': tags, 'adminuser_id': adminuser_id, } cur.execute(''.join(sql), args) conn.commit() return cur.rowcount == 1 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_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_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_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 add_pos(pos_info): conn, cur = None, None try: conn = pg.connect(**config.pg_main) cur = conn.cursor() keys = ( 'pos_type', 'sales_id', 'pos_name', 'pos_address', 'channel_id', 'sales_depart_id', 'pos_unit', 'pos_code', 'pos_man', 'pos_man_mobile', 'geo_data', 'create_user_id', ) args = {} args.update(pos_info) for k in keys: if k not in pos_info: args[k] = None sql = "select nextval('seq_t_sales_pos')" cur.execute(sql) args['pos_id'] = cur.fetchone()[0] sql = ''' insert into t_sales_pos ( pos_id, pos_type, sales_id, pos_name, pos_address, channel_id, sales_depart_id, pos_unit, pos_code, pos_man, pos_man_mobile, ---geo_data, create_user_id ) values( %(pos_id)s, %(pos_type)s, %(sales_id)s, %(pos_name)s, %(pos_address)s, %(channel_id)s, %(sales_depart_id)s, %(pos_unit)s, %(pos_code)s, %(pos_man)s, %(pos_man_mobile)s, ---geo_data, %(create_user_id)s ) ''' cur.execute(sql, args) conn.commit() return args['pos_id'] if cur.rowcount == 1 else None finally: if cur: cur.close() if conn: conn.close()