def get(self): response = {'code': 0, 'data': '', 'errorMsg': ''} df_records = ChartModel.gift_sum_report() data = {'list': json.loads(df_records)} response['data'] = data self.write(json_encode(response))
def export_gift_sum(cls): df_records = ChartModel.gift_sum_report() result_list = json.loads(df_records) data_list = [] for index, item in enumerate(result_list): if len(item['goods_list']) > 0: print(type(item['goods_list'])) for goodsIndex, goods in enumerate(item['goods_list']): goods_item = goods goods_item['gift_id'] = item['gift_id'] goods_item['gift_name'] = item['gift_name'] goods_item['gift_num'] = item['gift_num'] goods_item['index'] = index + 1 goods_item['all_amount'] = item['all_amount'] goods_item['rowspan'] = False goods_item['rowNum'] = 0 print(goods_item) if goodsIndex == 0: goods_item['rowspan'] = True goods_item['rowNum'] = len(item['goods_list']) data_list.append(goods_item) url = cls.write_excel_export_gift_sum(data_list) return url
def export_gift(cls, gift_name, year): total_num, res = ChartModel.gift_record(gift_name, year, '', '') result_list = json.loads(res) data_list = [] for index, item in enumerate(result_list): if len(item['goods_list']) > 0: for goodsIndex, goods in enumerate(item['goods_list']): goods_item = goods goods_item['order_id'] = item['order_id'] goods_item['birth_date'] = item['birth_date'] goods_item['finish_time'] = item['finish_time'] goods_item['index'] = index + 1 goods_item['gift_name'] = item['gift_name'] goods_item['org_name'] = item['org_name'] goods_item['staff_name'] = item['staff_name'] goods_item['staff_no'] = item['staff_no'] goods_item['dept_list'] = item['dept_list'] goods_item['rowspan'] = False goods_item['rowNum'] = 0 if goodsIndex == 0: goods_item['rowspan'] = True goods_item['rowNum'] = len(item['goods_list']) print(goods_item) data_list.append(goods_item) url = cls.write_excel_export_gift(data_list) return url
def get(self): page = self.get_argument('page') page_size = self.get_argument('pageSize') goods_name = self.get_argument('goodsName') begin_date = self.get_argument('beginDate') end_date = self.get_argument('endDate') response = {'code': 0, 'data': '', 'errorMsg': ''} total_num, df_records = ChartModel.goods_stock_out_detail_report( goods_name, begin_date, end_date, page, page_size) if isinstance(total_num, int): total_num = total_num else: total_num = total_num.tolist() data = {'count': total_num, 'list': json.loads(df_records)} response['data'] = data self.write(json_encode(response))
def get(self): try: page = self.get_argument('page') page_size = self.get_argument('pageSize') gift_name = self.get_argument('giftName') year = self.get_argument('year') response = {'code': 0, 'data': '', 'errorMsg': ''} total_num, df_records = ChartModel.gift_record( gift_name, year, page, page_size) if isinstance(total_num, int): total_num = total_num else: total_num = total_num.tolist() data = {'count': total_num, 'list': json.loads(df_records)} response['data'] = data self.write(json_encode(response)) except Exception as e: print(e)
def get_user_list(cls, page, page_size, staff_no, name, get_status, get_year, start_time, end_time): try: template = re.compile(r"(\d{4})-(\d{2})-(\d{2})") re.sub(template, r"\1-01-01", get_year) begin_date = re.sub(template, r"\1-01-01", get_year) end_date = re.sub(template, r"\1-12-31", get_year) year = re.sub(template, r"\1", get_year) print(year) staff_no_sql = '' name_sql = '' get_year_sql = '' start_time_sql = '' end_time_sql = '' if staff_no: staff_no_sql = f'AND ss.code = \'{staff_no}\'' if name: name_sql = f'AND ss.name like \'%{name}%\'' if get_year: get_year_sql = f'and b.zzdate <= \'{get_year}\'' if start_time: start_time_sql = f'AND REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\')>=\'{start_time}\'' if end_time: end_time_sql = f'AND REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\')<=\'{end_time}\'' un_supply_user_list = [] # 未申请 un_confirm_user_list = [] # 已申请待管理员确认 un_got_user_list = [] # 待领取 got_user_list = [] # 已领取 # records_sql = "select ss.pk_psndoc, ss.code, ss.name, ss.birthdate, b.zzdate from (select ss.pk_psndoc from bd_psndoc ss inner join hi_psnjob job on job.pk_psndoc = ss.pk_psndoc inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl where ss.enablestate = 2 and job.endflag = 'N' and job.poststat = 'Y' and jt.name in ('正式员工','全职','车间在职', '试用期员工', '退休返聘') group by ss.pk_psndoc, ss.name having ss.name not like '%测试%') a join ( \ # select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘') group by job.pk_psndoc) b on a.pk_psndoc = b.pk_psndoc \ # join bd_psndoc ss on ss.pk_psndoc = a.pk_psndoc where ss.pk_psndoc not in ( select ss.pk_psndoc from bd_psndoc ss \ # left join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc left join org_dept dp on dp.pk_dept = job.pk_dept left join org_dept fdp on fdp.pk_dept = dp.pk_fatherorg \ # where job.endflag = 'N' and job.ismainjob = 'Y'and (dp.name in ('电商运营部')or fdp.name in ('广州研发中心')or ss.name in ('王署斌', '龚培春'))) " + staff_no_sql + name_sql + get_year_sql + " order by ss.code" records_sql = f'select org.name as orgName, ' \ f'ss.name, ' \ f'ss.code, ' \ f'ss.birthdate,' \ f'REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\') AS thisYearBitth, ' \ f'b.zzdate, ' \ f'dp.name, ' \ f'dp2.name as dp2name,' \ f'{year} - (REGEXP_SUBSTR(ss.birthdate,\'(\d){{{4}}}\')) + 1 as age ,' \ f'ss.mobile, job.pk_dept ' \ f'from bd_psndoc ss ' \ f'inner join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc ' \ f'inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl ' \ f'inner join (select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in (\'正式员工\',\'全职\',\'车间在职\', \'退休返聘\') group by job.pk_psndoc) b on ss.pk_psndoc = b.pk_psndoc ' \ f'left join org_dept dp on dp.pk_dept = job.pk_dept ' \ f'left join org_dept dp2 on dp.pk_fatherorg = dp2.pk_dept ' \ f'left join org_group og on dp.pk_group = og.pk_group ' \ f'left join org_orgs org on dp.pk_org = org.pk_org ' \ f'where job.endflag = \'N\' and job.ismainjob = \'Y\' ' \ f'and job.lastflag = \'Y\' ' \ f'and org.pk_org in (\'0001A31000000002DQ1F\', \'0001A31000000002ETZ6\', \'0001A31000000002FDIG\', \'0001V1100000002GOO1A\', \'0001V1100000002A7G5J\', \'0001A31000000002DQ2O\', \'0001A310000000074BK7\') ' \ f'and (dp2.name not in (\'广州研发中心\') or dp2.name is null ) ' \ f'and (dp.name not in (\'电商运营部\') or dp.name is null) ' \ f'and ss.name not in (\'王署斌\', \'龚培春\', \'施国斌\') ' \ f'and ss.name not like \'%测试%\' ' \ f'and jt.name in (\'正式员工\',\'全职\',\'车间在职\', \'试用期员工\', \'退休返聘\') ' \ f'and b.zzdate <= REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\') ' \ f'and mod({year}- (REGEXP_SUBSTR(ss.birthdate,\'(\\d){{{4}}}\')) + 1 , 10) != 0 ' \ f'{staff_no_sql}' \ f'{name_sql}' \ f'{start_time_sql}' \ f'{end_time_sql}' \ f'order by og.name, org.name, ss.code' print(records_sql) df_records = pd.read_sql(records_sql, con=cls.db_nc) for index, row in df_records.iterrows(): staff_no = row[2] user_item = {} user_item['CODE'] = staff_no user_item['NAME'] = row[1] user_item['BIRTHDATE'] = row[3] user_item['THISYEARBIRTH'] = row[4] user_item['ZZDATE'] = row[5] user_item['mobile'] = row[9] user_item['group'] = row[0] dept_list = ChartModel.get_dept_list(row[10], []) user_item['dept_list'] = dept_list user_item['isOthers'] = '否' # 是否已设置他人代领 # 到other_supply中查询记录 若存在则设置了代领,若不存在则没有设置代领 supply_sql = "SELECT * from others_supply osp WHERE osp.status = 1 AND osp.staff_no = '%s 'AND osp.year = '%s'" % ( staff_no, year) df_supply = pd.read_sql(supply_sql, con=cls.conn_ss) supply_num = len(df_supply) df_supply = df_supply.to_json(orient='records') df_supply = json.loads(df_supply) if supply_num > 0: user_item['isOthers'] = '是' user_item['othersName'] = df_supply[0]['can_supply_name'] user_item['othersStaffNo'] = df_supply[0][ 'can_supply_staff_no'] # 查询某人某年礼包领取状态 order_sql = '''SELECT * from [order] o where o.status != 2 and o.create_time >= '%s' and o.create_time <= '%s' and o.staff_no = '%s' ''' % ( begin_date, end_date, staff_no) df_order = pd.read_sql(order_sql, con=cls.conn_ss) got_num = len(df_order) user_item['GOTNUM'] = got_num if got_num > 0: order_status = df_order['status'][0] user_item['ORDERSTATUS'] = order_status # 已申请待管理员确认 if order_status == 1: un_confirm_user_list.append(user_item) # 待领取 elif order_status == 3: un_got_user_list.append(user_item) # 已领取 elif order_status == 4: got_user_list.append(user_item) # 没有订单代表没有申请 else: un_supply_user_list.append(user_item) user_list = [] if get_status: if '1' in get_status: user_list = user_list + un_supply_user_list if '2' in get_status: user_list = user_list + un_confirm_user_list if '3' in get_status: user_list = user_list + un_got_user_list if '4' in get_status: user_list = user_list + got_user_list else: user_list = un_supply_user_list + un_confirm_user_list + un_got_user_list + got_user_list total_num = len(user_list) if page and page_size: min_top = (int(page) - 1) * int(page_size) max_top = int(page) * int(page_size) user_list = user_list[min_top:max_top] df = pd.DataFrame(user_list) df = df.to_json(orient='records') return total_num, df except Exception as e: print(e)
def hua_ming_ce(cls, page, page_size): try: #获取当前时间 now = datetime.now() print(now) # 获取当前年份 year = now.year print(type(year)) print(year) sql = '''select ss.code,ss.name, org.name as ORGNAME,op.postname, br.name as jiguan, bd.name as minzu, CASE ss.sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '其他' END as sex , bd2.name as hukouxingzhi , br2.name as hukousuozaidi, bd3.name as zhengzhimianmao , bd4.name as hunyinzhuangkuang , ss.mobile,bp.name as zhengjianleixing, ss.id as zhengjianhao , hpc.enddate as youxiaoqi, ss.censusaddr, ss.glbdef1, ss.birthdate,%d - (REGEXP_SUBSTR(ss.birthdate,'(\d){4}')) + 1 as age,d.joindate,hp.begindate as muxianbegindate, c.zcdengji,c.zcname,c.creationtime,bd5.name as xueli,hpe.school,hpe.major,hpe.begindate,hpe.enddate,oj.jobname as zwmc, ojr.jobrankname as zwjb, ojl.name as zhiji,ops.postseriesname,jt.name as jobTypeName,hpct.ifprop, hpct.probegindate , hpct.probenddate,b.zzdate, hpl.linkman as linkman, hpl.mobile as linkmanMobile,job.pk_dept, trunc(months_between(to_date(sysdate),to_date(d.joindate, 'yyyy-mm-dd'))/12) as jtAge, trunc(months_between(to_date(sysdate),to_date(hp.begindate, 'yyyy-mm-dd'))/12) as mxAge from bd_psndoc ss inner join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl left join om_job oj on job.pk_job = oj.pk_job left join om_jobrank ojr on ojr.pk_jobrank = job.pk_jobrank left join om_joblevel ojl on ojl.pk_joblevel = job.pk_jobgrade left join om_post op on op.pk_post = job.pk_post left join om_postseries ops on ops.pk_postseries = job.pk_postseries left join org_dept dp on dp.pk_dept = job.pk_dept left join org_orgs org on dp.pk_org = org.pk_org left join bd_region br on ss.nativeplace = br.pk_region left join bd_region br2 on ss.permanreside = br2.pk_region left join bd_defdoc bd on ss.nationality = bd.pk_defdoc left join bd_defdoc bd2 on ss.characterrpr = bd2.pk_defdoc left join bd_defdoc bd3 on ss.polity = bd3.pk_defdoc left join bd_defdoc bd4 on ss.marital = bd4.pk_defdoc left join bd_psnidtype bp on ss.idtype = bp.pk_identitype left join hi_psndoc_cert hpc on hpc.pk_psndoc = ss.pk_psndoc left join hi_psnorg hp on hp.pk_psndoc = ss.pk_psndoc and hp.endflag = 'N' and hp.lastflag = 'Y' left join ( select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘') group by job.pk_psndoc) b on ss.pk_psndoc = b.pk_psndoc left join ( select ss.pk_psndoc,bd.name as zcname, bd2.name as zcdengji,hpt.creationtime from bd_psndoc ss inner join hi_psndoc_title hpt on ss.pk_psndoc = hpt.pk_psndoc inner join bd_defdoc bd on hpt.pk_techposttitle = bd.pk_defdoc inner join bd_defdoc bd2 on hpt.titlerank = bd2.pk_defdoc where hpt.lastflag = 'Y' ) c on ss.pk_psndoc = c.pk_psndoc left join ( select job.pk_psndoc, min(job.begindate) as joindate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘', '试用期员工') group by job.pk_psndoc) d on ss.pk_psndoc = d.pk_psndoc left join bd_defdoc bd5 on ss.edu = bd5.pk_defdoc left join hi_psndoc_edu hpe on ss.pk_psndoc = hpe.pk_psndoc and hpe.lasteducation = 'Y' left join hi_psndoc_ctrt hpct on ss.pk_psndoc = hpct.pk_psndoc and hpct.lastflag = 'Y' left join hi_psndoc_linkman hpl on ss.pk_psndoc = hpl.pk_psndoc where job.endflag = 'N' and job.ismainjob = 'Y' and job.lastflag = 'Y' and ss.name not like '%%测试%%' and jt.name in ('正式员工','全职','车间在职', '试用期员工', '退休返聘') order by d.joindate desc''' % (year) print(sql) num_sql = '''select count(ss.code) as TOTALNUM from bd_psndoc ss inner join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl left join om_job oj on job.pk_job = oj.pk_job left join om_jobrank ojr on ojr.pk_jobrank = job.pk_jobrank left join om_joblevel ojl on ojl.pk_joblevel = job.pk_jobgrade left join om_post op on op.pk_post = job.pk_post left join om_postseries ops on ops.pk_postseries = job.pk_postseries left join org_dept dp on dp.pk_dept = job.pk_dept left join org_orgs org on dp.pk_org = org.pk_org left join bd_region br on ss.nativeplace = br.pk_region left join bd_region br2 on ss.permanreside = br2.pk_region left join bd_defdoc bd on ss.nationality = bd.pk_defdoc left join bd_defdoc bd2 on ss.characterrpr = bd2.pk_defdoc left join bd_defdoc bd3 on ss.polity = bd3.pk_defdoc left join bd_defdoc bd4 on ss.marital = bd4.pk_defdoc left join bd_psnidtype bp on ss.idtype = bp.pk_identitype left join hi_psndoc_cert hpc on hpc.pk_psndoc = ss.pk_psndoc left join hi_psnorg hp on hp.pk_psndoc = ss.pk_psndoc and hp.endflag = 'N' and hp.lastflag = 'Y' left join ( select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘') group by job.pk_psndoc) b on ss.pk_psndoc = b.pk_psndoc left join ( select ss.pk_psndoc,bd.name as zcname, bd2.name as zcdengji,hpt.creationtime from bd_psndoc ss inner join hi_psndoc_title hpt on ss.pk_psndoc = hpt.pk_psndoc inner join bd_defdoc bd on hpt.pk_techposttitle = bd.pk_defdoc inner join bd_defdoc bd2 on hpt.titlerank = bd2.pk_defdoc where hpt.lastflag = 'Y' ) c on ss.pk_psndoc = c.pk_psndoc left join ( select job.pk_psndoc, min(job.begindate) as joindate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘', '试用期员工') group by job.pk_psndoc) d on ss.pk_psndoc = d.pk_psndoc left join bd_defdoc bd5 on ss.edu = bd5.pk_defdoc left join hi_psndoc_edu hpe on ss.pk_psndoc = hpe.pk_psndoc and hpe.lasteducation = 'Y' left join hi_psndoc_ctrt hpct on ss.pk_psndoc = hpct.pk_psndoc and hpct.lastflag = 'Y' left join hi_psndoc_linkman hpl on ss.pk_psndoc = hpl.pk_psndoc where job.endflag = 'N' and job.ismainjob = 'Y' and job.lastflag = 'Y' and ss.name not like '%测试%' and jt.name in ('正式员工','全职','车间在职', '试用期员工', '退休返聘') order by d.joindate desc''' df_num = pd.read_sql(num_sql, con=cls.db_nc) total_num = df_num['TOTALNUM'][0] if page and page_size: min_top = (int(page) - 1) * int(page_size) + 1 max_top = int(page) * int(page_size) sql = '''SELECT * FROM (SELECT tt.*, ROWNUM AS rowno FROM ( select ss.code,ss.name, org.name as ORGNAME,op.postname, br.name as jiguan, bd.name as minzu, CASE ss.sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '其他' END as sex , bd2.name as hukouxingzhi , br2.name as hukousuozaidi, bd3.name as zhengzhimianmao , bd4.name as hunyinzhuangkuang , ss.mobile,bp.name as zhengjianleixing, ss.id as zhengjianhao , hpc.enddate as youxiaoqi, ss.censusaddr, ss.glbdef1, ss.birthdate,%d - (REGEXP_SUBSTR(ss.birthdate,'(\d){4}')) + 1 as age,d.joindate,hp.begindate as muxianbegindate, c.zcdengji,c.zcname,c.creationtime,bd5.name as xueli,hpe.school,hpe.major,hpe.begindate,hpe.enddate,oj.jobname as zwmc, ojr.jobrankname as zwjb, ojl.name as zhiji,ops.postseriesname,jt.name as jobTypeName,hpct.ifprop, hpct.probegindate , hpct.probenddate,b.zzdate, hpl.linkman as linkman, hpl.mobile as linkmanMobile,job.pk_dept, trunc(months_between(to_date(sysdate),to_date(d.joindate, 'yyyy-mm-dd'))/12) as jtAge, trunc(months_between(to_date(sysdate),to_date(hp.begindate, 'yyyy-mm-dd'))/12) as mxAge from bd_psndoc ss inner join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl left join om_job oj on job.pk_job = oj.pk_job left join om_jobrank ojr on ojr.pk_jobrank = job.pk_jobrank left join om_joblevel ojl on ojl.pk_joblevel = job.pk_jobgrade left join om_post op on op.pk_post = job.pk_post left join om_postseries ops on ops.pk_postseries = job.pk_postseries left join org_dept dp on dp.pk_dept = job.pk_dept left join org_orgs org on dp.pk_org = org.pk_org left join bd_region br on ss.nativeplace = br.pk_region left join bd_region br2 on ss.permanreside = br2.pk_region left join bd_defdoc bd on ss.nationality = bd.pk_defdoc left join bd_defdoc bd2 on ss.characterrpr = bd2.pk_defdoc left join bd_defdoc bd3 on ss.polity = bd3.pk_defdoc left join bd_defdoc bd4 on ss.marital = bd4.pk_defdoc left join bd_psnidtype bp on ss.idtype = bp.pk_identitype left join hi_psndoc_cert hpc on hpc.pk_psndoc = ss.pk_psndoc left join hi_psnorg hp on hp.pk_psndoc = ss.pk_psndoc and hp.endflag = 'N' and hp.lastflag = 'Y' left join ( select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘') group by job.pk_psndoc) b on ss.pk_psndoc = b.pk_psndoc left join ( select ss.pk_psndoc,bd.name as zcname, bd2.name as zcdengji,hpt.creationtime from bd_psndoc ss inner join hi_psndoc_title hpt on ss.pk_psndoc = hpt.pk_psndoc inner join bd_defdoc bd on hpt.pk_techposttitle = bd.pk_defdoc inner join bd_defdoc bd2 on hpt.titlerank = bd2.pk_defdoc where hpt.lastflag = 'Y' ) c on ss.pk_psndoc = c.pk_psndoc left join ( select job.pk_psndoc, min(job.begindate) as joindate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in ('正式员工','全职','车间在职', '退休返聘', '试用期员工') group by job.pk_psndoc) d on ss.pk_psndoc = d.pk_psndoc left join bd_defdoc bd5 on ss.edu = bd5.pk_defdoc left join hi_psndoc_edu hpe on ss.pk_psndoc = hpe.pk_psndoc and hpe.lasteducation = 'Y' left join hi_psndoc_ctrt hpct on ss.pk_psndoc = hpct.pk_psndoc and hpct.lastflag = 'Y' left join hi_psndoc_linkman hpl on ss.pk_psndoc = hpl.pk_psndoc where job.endflag = 'N' and job.ismainjob = 'Y' and job.lastflag = 'Y' and ss.name not like '%%测试%%' and jt.name in ('正式员工','全职','车间在职', '试用期员工', '退休返聘') order by d.joindate desc ) tt WHERE ROWNUM <= %d) table_alias WHERE table_alias.rowno >= %d ''' % (year, max_top, min_top) df_records = pd.read_sql(sql, con=cls.db_nc) user_list = [] for index, row in df_records.iterrows(): dept_list = ChartModel.get_dept_list(row[40], []) row['dept_list'] = dept_list user_list.append(row) df = pd.DataFrame(user_list) df = df.to_json(orient='records') return total_num, df except Exception as e: print(e)
def get_z_birth_user_list(cls, page, page_size, staff_no, name, get_year, start_time, end_time): try: template = re.compile(r"(\d{4})-(\d{2})-(\d{2})") re.sub(template, r"\1-01-01", get_year) year = re.sub(template, r"\1", get_year) print(year) staff_no_sql = '' name_sql = '' start_time_sql = '' end_time_sql = '' if staff_no: staff_no_sql = f'AND ss.code = \'{staff_no}\'' if name: name_sql = f'AND ss.name like \'%{name}%\'' if start_time: start_time_sql = f'AND REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\')>=\'{start_time}\'' if end_time: end_time_sql = f'AND REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\')<=\'{end_time}\'' records_sql = f'select org.name as orgName, ' \ f'ss.name, ' \ f'ss.code, ' \ f'ss.birthdate,' \ f'REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\') AS thisYearBitth, ' \ f'b.zzdate, ' \ f'dp.name, ' \ f'dp2.name as dp2name,' \ f'{year} - (REGEXP_SUBSTR(ss.birthdate,\'(\d){{{4}}}\')) + 1 as age ,' \ f'ss.mobile, job.pk_dept ' \ f'from bd_psndoc ss ' \ f'inner join hi_psnjob job on ss.pk_psndoc = job.pk_psndoc ' \ f'inner join bd_psncl jt on jt.pk_psncl = job.pk_psncl ' \ f'inner join (select job.pk_psndoc, min(job.begindate) as zzdate from hi_psnjob job join bd_psncl jt on job.pk_psncl = jt.pk_psncl where jt.name in (\'正式员工\',\'全职\',\'车间在职\', \'退休返聘\') group by job.pk_psndoc) b on ss.pk_psndoc = b.pk_psndoc ' \ f'left join org_dept dp on dp.pk_dept = job.pk_dept ' \ f'left join org_dept dp2 on dp.pk_fatherorg = dp2.pk_dept ' \ f'left join org_group og on dp.pk_group = og.pk_group ' \ f'left join org_orgs org on dp.pk_org = org.pk_org ' \ f'where job.endflag = \'N\' and job.ismainjob = \'Y\' ' \ f'and job.lastflag = \'Y\' ' \ f'and org.pk_org in (\'0001A31000000002DQ1F\', \'0001A31000000002ETZ6\', \'0001A31000000002FDIG\', \'0001V1100000002GOO1A\', \'0001V1100000002A7G5J\', \'0001A31000000002DQ2O\', \'0001A310000000074BK7\') ' \ f'and (dp2.name not in (\'广州研发中心\') or dp2.name is null ) ' \ f'and (dp.name not in (\'电商运营部\') or dp.name is null) ' \ f'and ss.name not in (\'王署斌\', \'龚培春\', \'施国斌\') ' \ f'and ss.name not like \'%测试%\' ' \ f'and jt.name in (\'正式员工\',\'全职\',\'车间在职\', \'试用期员工\', \'退休返聘\') ' \ f'and b.zzdate <= REGEXP_REPLACE (ss.birthdate,\'(\\d{{{4}}})-(\\d{{{2}}})-(\\d{{{2}}})\',\'{year}-\\2-\\3\') ' \ f'and mod({year}- (REGEXP_SUBSTR(ss.birthdate,\'(\\d){{{4}}}\')) + 1 , 10) = 0 ' \ f'{staff_no_sql}' \ f'{name_sql}' \ f'{start_time_sql}' \ f'{end_time_sql}' \ f'order by og.name, org.name, ss.code' print(records_sql) df_records = pd.read_sql(records_sql, con=cls.db_nc) user_list = [] for index, row in df_records.iterrows(): staff_no = row[2] user_item = {} user_item['CODE'] = staff_no user_item['NAME'] = row[1] user_item['BIRTHDATE'] = row[3] user_item['THISYEARBIRTH'] = row[4] user_item['ZZDATE'] = row[5] user_item['mobile'] = row[9] user_item['group'] = row[0] dept_list = ChartModel.get_dept_list(row[10], []) user_item['dept_list'] = dept_list user_list.append(user_item) total_num = len(user_list) if page and page_size: min_top = (int(page) - 1) * int(page_size) max_top = int(page) * int(page_size) user_list = user_list[min_top:max_top] df = pd.DataFrame(user_list) df = df.to_json(orient='records') return total_num, df except Exception as e: print(e)
def goods_stock_in_detail_report(cls, goods_name, begin_date, end_date): total_num, res = ChartModel.goods_stock_in_detail_report( goods_name, begin_date, end_date, '', '') result_list = json.loads(res) url = cls.write_excel_chart_goods_stock_in(result_list) return url
def export_chart_goods(cls): res = ChartModel.goods_stock_report() result_list = json.loads(res) url = cls.write_excel_chart_goods(result_list) return url