def contracts_received_time_series(request, entity_id): """ Computes the time series of number of contracts of entry with entity_id starting with startswith_string. """ query = '''SELECT YEAR(`contracts_contract`.`signing_date`), MONTH(`contracts_contract`.`signing_date`), COUNT(`contracts_contract`.`id`) FROM `contracts_contract` INNER JOIN `contracts_contract_contracted` ON ( `contracts_contract`.`id` = `contracts_contract_contracted`.`contract_id` ) INNER JOIN `contracts_entity` ON ( `contracts_contract_contracted`.`entity_id` = `contracts_entity`.`id` ) WHERE `contracts_entity`.`id` = %s GROUP BY YEAR(`contracts_contract`.`signing_date`), MONTH(`contracts_contract`.`signing_date`) ''' cursor = connection.cursor() cursor.execute(query, entity_id) data = {'values': [], 'key': _('Contracts as hired')} for row in cursor.fetchall(): year, month, value = row if year is None: continue min_date = datetime.date(int(year), int(month), 1) entry = {'month': min_date.strftime('%Y-%m'), 'value': int(value)} data['values'].append(entry) return HttpResponse(json.dumps([data]), content_type="application/json")
def poly_sql_query2(): cursor = connection.cursor() cursor.execute(""" SELECT id, pexp_modela.field1 FROM pexp_modela WHERE pexp_modela.field1=%i ORDER BY pexp_modela.id """ % rnd.randint(0,100) ) #row=cursor.fetchone() return
def poly_sql_query2(): cursor = connection.cursor() cursor.execute(""" SELECT id, pexp_modela.field1 FROM pexp_modela WHERE pexp_modela.field1=%i ORDER BY pexp_modela.id """ % rnd.randint(0,100) ) #row=cursor.fetchone() return
def get_collected_number(request): c = {} collected_count = Favorites.objects.count() cursor = connection.cursor() sql_ = "select count(*) from project_project_target_companies" cursor.execute(sql_) recommond_count = cursor.fetchone()[0] project = Project.target_companies c['collected_count'] = collected_count c['recommond_count'] = recommond_count return render_to_response("analysis/get_collected_number.html", c, context_instance=RequestContext(request))
def poly_sql_query(): cursor = connection.cursor() cursor.execute(""" SELECT id, pexp_modela.field1, pexp_modelb.field2, pexp_modelc.field3 FROM pexp_modela LEFT OUTER JOIN pexp_modelb ON pexp_modela.id = pexp_modelb.modela_ptr_id LEFT OUTER JOIN pexp_modelc ON pexp_modelb.modela_ptr_id = pexp_modelc.modelb_ptr_id WHERE pexp_modela.field1=%i ORDER BY pexp_modela.id """ % rnd.randint(0,100) ) #row=cursor.fetchone() return
def poly_sql_query(): cursor = connection.cursor() cursor.execute(""" SELECT id, pexp_modela.field1, pexp_modelb.field2, pexp_modelc.field3 FROM pexp_modela LEFT OUTER JOIN pexp_modelb ON pexp_modela.id = pexp_modelb.modela_ptr_id LEFT OUTER JOIN pexp_modelc ON pexp_modelb.modela_ptr_id = pexp_modelc.modelb_ptr_id WHERE pexp_modela.field1=%i ORDER BY pexp_modela.id """ % rnd.randint(0,100) ) #row=cursor.fetchone() return
def yc_report(request): ''' 考勤异常明细表 ''' userids, deptids, d1, d2, offset = parse_report_arg(request) dic =(('userid',_(u'用户ID')),('super_dept',_(u'上级部门')),('deptname',_(u'部门名称')),('badgenumber',_(u'人员编号')),('username',_(u'姓名')),('date',_(u'日期')),\ ('late',_(u'迟到分钟')),('early',_(u'早退分钟')),('absent',_(u'旷工时间')),('late_times',_(u'迟到次数')),('early_times',_(u'早退次数')),('absent_times',_(u'旷工次数')),('worktime',_(u'上班时间')),('card_times',_(u'打卡时间'))) calculate = AttCalculateBase(dic) sql = report_sql.get_yc_report_sql(','.join(userids), ','.join(deptids), d1, d2) ret_data = [] try: cur = conn.cursor() cur.execute(sql) ret_data = cur.fetchall() conn._commit() except: import traceback traceback.print_exc() for d in ret_data: r = calculate.NewItem() r["userid"] = d[1] r["super_dept"] = d[2] or " " r["deptname"] = d[3] or " " r["badgenumber"] = d[4] r["username"] = d[5] or " " if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.mysql": r["date"] = d[6].strftime("%Y-%m-%d") else: r["date"] = d[6] r["late"] = d[7] or " " r["early"] = d[8] or " " if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.oracle": r["absent"] = str(d[9]) or " " else: r["absent"] = d[9] r["late_times"] = d[10] or " " r["early_times"] = d[11] or " " r["absent_times"] = d[12] or " " if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.mysql": r["worktime"] = str(d[13]) else: r["worktime"] = d[13] r["card_times"] = d[14] or " " calculate.AddItem(r) return getJSResponse(smart_str(dumps(calculate.ResultDic(offset))))
def makeapps(): cursor = connection.cursor() # Go get all the distinct apps we have records for cursor.execute("SELECT DISTINCT `app_id`, `app_version` FROM `crashLog_crashlog`") app_id_versions = cursor.fetchall() # Go pump those apps into the database app_id_map = [] for app_id_version in app_id_versions: app_id = app_id_version[0] app_version = app_id_version[1] app = recordApp(app_id,None) recordVersion(app_id, app_version)
def attRecAbnormite_report(request): ''' 统计结果详情表 ''' userids, deptids, d1, d2, offset = parse_report_arg(request) dic = (('pin', _(u'人员编号')), ('name', _(u'姓名')), ('checktime', _(u'考勤时间')), ('CheckType', _(u'考勤状态')), ('NewType', _(u'更正状态'))) calculate = AttCalculateBase(dic) sql = report_sql.get_tjjgxq_report_sql(','.join(userids), d1, d2) ret_data = [] try: cur = conn.cursor() cur.execute(sql) ret_data = cur.fetchall() conn._commit() except: import traceback traceback.print_exc() ATTSTATES = (("I", u"上班签到"), ("O", u"下班签退"), ("0", u"上班签到"), ("1", u"下班签退"), ("8", u"就餐开始"), ("9", u"就餐结束"), ("2", u"外出"), ("3", u"外出返回"), ("4", u"加班签到"), ("5", u"加班签退"), ("255", u"未设置状态")) m_dic = {} m_dic.update(ATTSTATES) for d in ret_data: try: r = calculate.NewItem() r["pin"] = d[0] r["name"] = d[1] r["checktime"] = d[2].strftime('%Y-%m-%d %H:%M:%S') if not d[3]: d = list(d) d[3] = "I" d = tuple(d) r["CheckType"] = m_dic[str(d[3])] r["NewType"] = m_dic[str(d[4])] calculate.AddItem(r) except: import traceback traceback.print_exc() return getJSResponse(smart_str(dumps(calculate.ResultDic(offset))))
def recordCrash(crashObj,request): # We grab a raw SQL connection, so we can write our own optimized request for the crash log. cursor = connection.cursor() # Single DB-hit to drop in a crash! That's a promise worth pursuing cursor.execute("INSERT INTO `crashLog_crashlog` (`fingerprint`, `error_signal`, `error_code`, `error_address`, `exception_name`, `exception_reason`, `java_trace`, `trace`, `app_id`, `app_version`, `device_name`, `os_version`, `cpu_arch`, `count`, `symbolicated`, `resolved`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE `count` = `count` + 1", [ crashObj['fingerprint'], # fingerprint crashObj['crash_signal'] if 'crash_signal' in crashObj else '', # error_signal crashObj['code'] if 'code' in crashObj else '', # error_code crashObj['address'] if 'address' in crashObj else '', # error_address crashObj['exception_name'] if 'exception_name' in crashObj else '', # exception_name crashObj['exception_reason'] if 'expection_reason' in crashObj else '', # exception_reason crashObj['java_trace'] if 'java_trace' in crashObj else '', # java_trace json.dumps(crashObj['threads']) if 'threads' in crashObj else '', # trace crashObj['app_id'], # app_id crashObj['app_version'], # app_version crashObj['device'], # device_name crashObj['os_version'], # os_version crashObj['cpu_arch'], # cpu_arch 1, # count False, # symbolicated False, # resolved ]) # Though the docs claim this is unnecessary, the transaction doesn't commit without it transaction.commit_unless_managed() """ # We're going to try not saving ReportTime, to save the DB access reportTime = ReportTime(crash_log=crashLogRecord,report_time=timezone.now(),specific_device=crashObj['specific_device']) reportTime.save() """ print('Saved report')
def view_admin_aggregates_customer(request): if not request.user.is_staff: return HttpResponse('You are no allowed to browse this page!', status=403) active_tab = coerce_int( input=request.GET.get('tab', 1), max_val=3, ) active_timespan = coerce_int( input=request.GET.get('timespan', 5), max_val=5 ) active_rownum = coerce_int( input=request.GET.get('rownum', 1), max_val=7 ) active_redirected = coerce_int( input=request.GET.get('redirected', 1), max_val=3 ) # Use pure ORM to be compaible with Django 1.0.x which doesn't support aggregates cursor = connection.cursor() date_clause = get_query_date_clause(active_timespan) if active_rownum == 7: limit_clause = '' else: limit_clause = "LIMIT %s" % ['25', '50', '100', '200', '500', '1000', 'ALL'][active_rownum-1] redirected_choices = ['All', 'True', 'False'] verbose_redirected_choices = ['All', 'Yes', 'No'] if active_redirected == 1: redirected_clause = '' else: redirected_clause = 'AND redirected = %s' % redirected_choices[active_redirected-1] if active_tab == 1: column_headers = ('Page', 'Num. of errors', 'Fix') sql_statement = r"SELECT url, COUNT(*) AS ct, 0, redirected FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY url ORDER BY ct DESC %s" elif active_tab == 2: column_headers = ('Incoming link', 'Num. of errors') sql_statement = r"SELECT referrer, COUNT(*) AS ct FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY referrer ORDER BY ct DESC %s" else: # if active_tab == 3: column_headers = ('Page', 'Incoming link', 'Num. of errors', 'Fix') sql_statement = r"SELECT url, COUNT(*) AS ct, referrer, redirected FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY url, referrer ORDER BY ct DESC %s" cursor.execute(sql_statement % (redirected_clause, date_clause, limit_clause)) # No SQL-injection problem + we don't need quoting here, so Python formatting is OK timespans = ['Today', 'Yesterday', 'Last 7 days', 'Last 30 days', 'Last 365 days', 'All the time'] return HttpResponse(get_template('admin/aggregate_list.html').render(RequestContext(request, { 'active_tab': active_tab, 'tabs': ['by requested page', 'by requesting page', 'both',], 'active_timespan': active_timespan, 'timespans': timespans, 'active_timespan_description': timespans[active_timespan], 'active_rownum': active_rownum, 'rownums': ['25', '50', '100', '200', '500', '1000', 'ALL'], 'active_redirected': active_redirected, 'redirected_choices':verbose_redirected_choices, 'column_headers': column_headers, 'results': cursor.fetchall(), 'root_path': '/admin/' # To make Django base template happy and 'Change password'/'Logout/ links work })))
def view_admin_aggregates_customer(request): if not request.user.is_staff: return HttpResponse('You are no allowed to browse this page!', status=403) active_tab = coerce_int( input=request.GET.get('tab', 1), max_val=3, ) active_timespan = coerce_int(input=request.GET.get('timespan', 5), max_val=5) active_rownum = coerce_int(input=request.GET.get('rownum', 1), max_val=7) active_redirected = coerce_int(input=request.GET.get('redirected', 1), max_val=3) # Use pure ORM to be compaible with Django 1.0.x which doesn't support aggregates cursor = connection.cursor() date_clause = get_query_date_clause(active_timespan) if active_rownum == 7: limit_clause = '' else: limit_clause = "LIMIT %s" % [ '25', '50', '100', '200', '500', '1000', 'ALL' ][active_rownum - 1] redirected_choices = ['All', 'True', 'False'] verbose_redirected_choices = ['All', 'Yes', 'No'] if active_redirected == 1: redirected_clause = '' else: redirected_clause = 'AND redirected = %s' % redirected_choices[ active_redirected - 1] if active_tab == 1: column_headers = ('Page', 'Num. of errors', 'Fix') sql_statement = r"SELECT url, COUNT(*) AS ct, 0, redirected FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY url ORDER BY ct DESC %s" elif active_tab == 2: column_headers = ('Incoming link', 'Num. of errors') sql_statement = r"SELECT referrer, COUNT(*) AS ct FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY referrer ORDER BY ct DESC %s" else: # if active_tab == 3: column_headers = ('Page', 'Incoming link', 'Num. of errors', 'Fix') sql_statement = r"SELECT url, COUNT(*) AS ct, referrer, redirected FROM djangodblog_error WHERE class_name='Http404' %s %s GROUP BY url, referrer ORDER BY ct DESC %s" cursor.execute( sql_statement % (redirected_clause, date_clause, limit_clause) ) # No SQL-injection problem + we don't need quoting here, so Python formatting is OK timespans = [ 'Today', 'Yesterday', 'Last 7 days', 'Last 30 days', 'Last 365 days', 'All the time' ] return HttpResponse( get_template('admin/aggregate_list.html').render( RequestContext( request, { 'active_tab': active_tab, 'tabs': [ 'by requested page', 'by requesting page', 'both', ], 'active_timespan': active_timespan, 'timespans': timespans, 'active_timespan_description': timespans[active_timespan], 'active_rownum': active_rownum, 'rownums': ['25', '50', '100', '200', '500', '1000', 'ALL'], 'active_redirected': active_redirected, 'redirected_choices': verbose_redirected_choices, 'column_headers': column_headers, 'results': cursor.fetchall(), 'root_path': '/admin/' # To make Django base template happy and 'Change password'/'Logout/ links work })))
def attShifts_report(request): ''' 考勤明细表 ''' userids, deptids, d1, d2, offset = parse_report_arg(request) dic = (('code', _(u'部门编号')), ('DeptName', _(u'部门名称')), ('badgenumber', _(u'人员编号')), ('name', _(u'姓名')), ('AttDate', _(u'日期')), ('SchName', _(u'时段名称')), ('ClockInTime', _(u'上班时间')), ('ClockOutTime', _(u'下班时间')), ('StartTime', _(u'签到时间')), ('EndTime', _(u'签退时间')), ('WorkDay', _(u'应到')), ('RealWorkDay', _(u'实到')), ('MustIn', _(u'应签到')), ('MustOut', _(u'应签退')), ('NoIn', _(u'未签到')), ('NoOut', _(u'未签退')), ('Late', _(u'迟到')), ('Early', _(u'早退')), ('AbsentR', _(u'旷工')), ('WorkTime', _(u'出勤时长')), ('Exception', _(u'例外情况')), ('OverTime_des', _(u'加班时间')), ('AttTime', _(u'时段时间')), ('SSpeDayNormalOT', _(u'平日加班')), ('SSpeDayWeekendOT', _(u'休息日加班')), ('SSpeDayHolidayOT', _(u'节假日加班'))) calculate = AttCalculateBase(dic) sql = report_sql.attShifts_report_sql(','.join(userids), d1, d2) ret_data = [] try: cur = conn.cursor() cur.execute(sql) ret_data = cur.fetchall() conn._commit() except: import traceback traceback.print_exc() for d in ret_data: r = calculate.NewItem() r["code"] = d[0] r["DeptName"] = d[1] r["badgenumber"] = d[2] r["name"] = d[3] r["AttDate"] = d[4].strftime("%Y-%m-%d") r["SchName"] = d[5] r["ClockInTime"] = d[6] and d[6].strftime('%H:%M') or '' r["ClockOutTime"] = d[7] and d[7].strftime('%H:%M') or '' r["StartTime"] = d[8] and d[8].strftime('%H:%M') or '' r["EndTime"] = d[9] and d[9].strftime('%H:%M') or '' r["WorkDay"] = d[10] r["RealWorkDay"] = d[11] r["MustIn"] = d[12] r["MustOut"] = d[13] r["NoIn"] = d[14] r["NoOut"] = d[15] r["Late"] = d[16] r["Early"] = d[17] r["AbsentR"] = d[18] r["WorkTime"] = d[19] r["Exception"] = get_ExceptionID(d[20], d[30], d[4]) r["OverTime_des"] = d[ 21] #r["WorkMins"]=d[22]; r["SSpeDayNormal"]=d[23]; r["SSpeDayWeekend"]=d[24]; r["SSpeDayHoliday"]=d[25] r["AttTime"] = d[26] r["SSpeDayNormalOT"] = d[27] r["SSpeDayWeekendOT"] = d[28] r["SSpeDayHolidayOT"] = d[29] calculate.AddItem(r) return getJSResponse(smart_str(dumps(calculate.ResultDic(offset))))
def cardtime_calculate(request, deptids, userids, st, et, totalall=False): ''' 计算出打卡详情表 ''' from mysite.att.sql import cardtime_calculate_sql1, cardtime_calculate_sql2 total_days = int((et - st).days) + 1 #总天数 Result = {} #定义返回的字典,也就是 传递到前台 re = [] #储存每条记录数 try: #分页 try: offset = int(request.REQUEST.get('p', 1)) #获取分页信息 except: offset = 1 #print "offset:%s"%offset uids = [] #储存人员信息 k = 0 limit = int(request.POST.get('l', settings.PAGE_LIMIT)) if not totalall: sql = cardtime_calculate_sql1(deptids, st, et, userids) count_data = ((0, ), ) try: cur = conn.cursor() cur.execute(sql) count_data = cur.fetchall() conn._commit() except: print sql import traceback traceback.print_exc() item_count = count_data[0][0] #获取记录总数 if item_count % limit == 0: page_count = item_count / limit else: page_count = int(item_count / limit) + 1 if offset > page_count and page_count: offset = page_count # ids=ids[(offset-1)*limit:offset*limit]#分页操作 Result['item_count'] = item_count #记录总数 Result['page'] = offset #第几页 Result['limit'] = limit #每页显示数 Result['from'] = (offset - 1) * limit + 1 # Result['page_count'] = page_count #总页数 # for u in ids: # uids.append(u) r, Fields, Capt = get_card_times_fields() #print "Result['fieldnames']=",Fields Result['fieldnames'] = Fields Result['fieldcaptions'] = Capt Result['datas'] = r date = st.date() sql = cardtime_calculate_sql2(totalall, Result['from'], deptids, st, et, userids, offset, limit) # print sql ret_data = [] try: cur = conn.cursor() cur.execute(sql) ret_data = cur.fetchall() conn._commit() except: print sql import traceback traceback.print_exc() for d in ret_data: r = { 'username': '', 'deptname': '', 'userid': -1, 'badgenumber': '', 'card_times': ' ', 'date': '', 'super_dept': '' } r["userid"] = d[1] r["super_dept"] = d[2] or " " r["deptname"] = d[3] or " " r["badgenumber"] = d[4] r["username"] = d[5] or " " if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.mysql": r["date"] = d[6].strftime("%Y-%m-%d") else: r["date"] = d[6] r["times"] = d[7] r["card_times"] = d[8] or " " re.append(r) Result['datas'] = re return Result except: import traceback traceback.print_exc()