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()
