# -*- coding: utf-8 -*-
import dingtalk.api
import pymysql
from tqdm import tqdm, trange
import datetime
import time
import pymssql
from log import *
log = getLogger()

#定义通用
appkey = "dingud9rrlmbwk00edvq"
appsecret = "8asCuiBku0t5GByeBEfcByRNep9pr8LeESvFGamJaVATeYPAHV2L--lWZhcDJkCz"
#定义mysql连接
db = pymysql.connect(host='10.99.13.186', user='root', password='Smtgbk_123', db='attendance', charset="utf8")
	#sql服务器名，这里(127.0.0.1)是本地数据库IP
	serverName = '10.99.12.35'
	#登陆用户名和密码
	userName = 'root'
	passWord = 'Smtgbk_123'
	#建立连接并获取cursor
	conn = pymssql.connect(host=serverName,user=userName,password=passWord,database=database)
	conn = pymssql.connect(serverName,userName,passWord,"zkeco20161206")

def getToken():
	log.info("开始获取access_token...")
	request = dingtalk.api.OapiGettokenRequest("https://oapi.dingtalk.com/gettoken")
	request.appkey = appkey
	request.appsecret = appsecret
	access_token = ''
	try:
		resp= request.getResponse()
		access_token = resp.get('access_token')
		log.info("获取access_token成功!")
	except Exception as e:
		log.info(e)
	return access_token


def get_departmentlist(Update=False):
	departmentid = []
	departmentname = []
	departs = []
	# 通过钉钉接口获取departmentid
	if Update:
		access_token = getToken()
		request = dingtalk.api.OapiDepartmentListRequest("https://oapi.dingtalk.com/department/list")
		request.access_token = access_token
		try:
			f = request.getResponse()
			departmentlist = f.get('department')
			log.info("开始发现钉钉部门...")
			count = 0
			for item in departmentlist:
				count = count + 1
				departmentid.append(item.get('id'))
				departmentname.append(item.get('name'))
				departs.append([item.get('id'),item.get('name')])
			log.info("合计发现部门"+str(count)+"个！")
			log.info("开始清理部门数据...")
			sql = 'DELETE FROM wtown_ding_department'
			cursor = db.cursor()
			try:
				cursor.execute(sql)
				db.commit()
			except Exception as e:
				db.rollback()
			cursor.close()
			log.info("完成部门数据清理!")
			count = 0
			log.info("开始添加部门数据...")
			cursor = db.cursor()
			for item in tqdm(departs):
				count = count + 1
				sql = "INSERT INTO wtown_ding_department (`departid`,`departname`) VALUES ('%s','%s')"
				data = (item[0],item[1])
				try:
					cursor.execute(sql % data)
					db.commit()
				except Exception as e:
					db.rollback()
			cursor.close()
			log.info("完成部门数据添加，合计"+str(count)+"个！")
		except Exception as e:
			log.info(e)
	# 通过数据库获取departmentid
	else:
		log.info("通过数据库部门数据...")
		cursor = db.cursor()
		sql = "SELECT departid FROM wtown_ding_department"
		cursor.execute(sql)
		data = cursor.fetchall()
		for each in data:
			departmentid.append(each[0])
		cursor.close()
		log.info("合计发现部门"+str(len(departmentid))+"个！")
	return departmentid

def get_userid(Update=False):
	userid = []
	if Update:
		req=dingtalk.api.OapiUserGetDeptMemberRequest("https://oapi.dingtalk.com/user/getDeptMember")
		access_token = getToken()
		departmentid = get_departmentlist(True)
		log.info("正在通过部门获取userid...")
		for each in tqdm(departmentid):
			req.deptId = each
			req.access_token = access_token
			try:
				resp= req.getResponse()
				userid = sorted(list(set(userid+resp.get('userIds'))))
			except Exception as e:
				log.info(e)
		log.info("完成Userid获取，合计"+str(len(userid))+"条！")
		log.info("开始清理员工数据...")
		cursor = db.cursor()
		sqluser = "DELETE FROM wtown_ding_user"
		try:
			cursor.execute(sqluser)
			db.commit()
		except Exception as e:
			db.rollback()
		log.info("完成员工数据清理!")
		cursor.close()
		log.info("开始写入员工数据...")
		count = 0
		cursor = db.cursor()
		for each in tqdm(userid):
			reqs = dingtalk.api.OapiUserGetRequest("https://oapi.dingtalk.com/user/get")
			reqs.userid = each
			try:
				resps = reqs.getResponse(access_token)
				username = resps.get('name')
				jobnumber = resps.get('jobnumber')
				mobile = resps.get('mobile')
				department = resps.get('department')[0]
				sqluser = "INSERT INTO wtown_ding_user (`userid`,`name`,`jobnumber`,`mobile`,`departid`,`update`)  VALUES ('%s','%s','%s','%s','%s','%s')"
				data = (each,username,jobnumber,mobile,department,datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
				try:
					cursor.execute(sqluser % data)
					db.commit()
				except Exception as e:
					db.rollback()
			except Exception as e:
				log.info(e)
		cursor.close()
		log.info("合计写入钉钉员工数据"+str(len(userid))+"条！")
	else:
		log.info("通过数据库获取员工数据...")
		cursor = db.cursor()
		sqluser = "SELECT userid FROM wtown_ding_user"
		cursor.execute(sqluser)
		data = cursor.fetchall()
		for each in data:
			userid.append(each[0])
		cursor.close()
		log.info("合计发现部门"+str(len(userid))+"个！")
	return userid


def get_attendance():
	access_token = getToken()
	maxnumber = 0
	req = dingtalk.api.OapiAttendanceListRecordRequest("https://oapi.dingtalk.com/attendance/listRecord")
	log.info("开始获取员工数据...")
	cursorF = db.cursor()
	sqluserinfo = 'SELECT userid FROM wtown_ding_user where jobnumber<>""'
	cursorF.execute(sqluserinfo)
	maxnumber = len(cursorF.fetchall())
	log.info("获取有效员工数据"+str(maxnumber)+"条!")
	checkDateFrom = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d") + " 00:00:00"
	checkDateTo = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d") + " 23:59:59"
	log.info("开始获取员工考勤数据...")
	attendanceinfo = []
	useridinfo = {}
	for i in tqdm(range(0,maxnumber,50)):
		time.sleep(1)
		userid = []
		cursor = db.cursor()
		sqluserinfo = 'SELECT * FROM wtown_ding_user where jobnumber<>"" limit ' + str(i) + ',50'
		cursor.execute(sqluserinfo)
		data = cursor.fetchall()
		for each in data:
			userid.append(each[1])
			useridinfo[each[1]]={"name":each[2],"jobnumber":each[3]}
		req.userIds=userid
		req.checkDateFrom=checkDateFrom
		req.checkDateTo=checkDateTo
		req.isI18n=False
		try:
			resp= req.getResponse(access_token)
			datas = resp.get("recordresult")
			for eachs in datas:
				userId = eachs.get('userId')
				userCheckTime = eachs.get('userCheckTime')/1000
				userCheckTime = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(userCheckTime))
				attendanceinfo.append([userId,useridinfo[userId]['name'],useridinfo[userId]['jobnumber'],userCheckTime])
		except Exception as e:
			log.info(e)
		cursor.close()
	#log.info(attendanceinfo)
	log.info("获取考勤数据完成,合计"+str(len(attendanceinfo))+"条!")
	log.info("开始将考勤数据写入本地数据库...")
	addcount = 0
	for atten in tqdm(attendanceinfo):
		cursor1 = db.cursor()
		sql = 'SELECT * FROM wtown_ding_attendance where jobnumber = "%s" and userCheckTime = "%s"'
		data = (atten[2],atten[3])
		cursor1.execute(sql % data)
		if cursor1.rowcount == 0:
			cursor2 = db.cursor()
			addcount = addcount + 1
			sqladd = "INSERT INTO wtown_ding_attendance (`jobnumber`,`name`,`userCheckTime`)  VALUES ('%s','%s','%s')"
			dataadd = (atten[2],atten[1],atten[3])
			try:
				cursor2.execute(sqladd % dataadd)
				db.commit()
			except Exception as e:
				db.rollback()
			cursor2.close()
		cursor1.close()
	log.info("写入本地数据库完成，合计"+str(addcount)+"条！")

def add_into_mssql():
	count = 0
	log.info("开始写入远程考勤数据库(前一日打卡数据)...")
	cursormysql = db.cursor()
	cursormssql = conn.cursor()
	mssql = "INSERT INTO checkinout (pin,checktime,verifycode,sn_name) VALUES ('%s','%s','1','6095161900082')"
	checkDateFrom = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d") + " 00:00:00"
	checkDateTo = (datetime.datetime.now()+datetime.timedelta(days=-1)).strftime("%Y-%m-%d") + " 23:59:59"
	mysql = 'SELECT jobnumber,userCheckTime FROM wtown_ding_attendance WHERE userCheckTime>="' + checkDateFrom + '" and userCheckTime<="' + checkDateTo + '" ORDER BY userCheckTime'
	cursormysql.execute(mysql)
	data = cursormysql.fetchall()
	for each in tqdm(data):
		datamssql = (each[0],each[1])
		try:
			count = count +1
			cursormssql.execute(mssql % datamssql)
			conn.commit()
		except Exception as e:
			db.rollback()
	log.info("远程考勤数据库写入完成，合计"+str(count)+"条！")
	cursormssql.close()
	cursormysql.close()


def main():
	if len(sys.argv) == 1:
		log.info("**********************")
		log.info("【开始执行考勤同步......】")
		get_attendance()
		add_into_mssql()
		log.info("【考勤同步执行完毕！】")
		log.info("**********************")
	else:
		log.info("**********************")
		log.info("【开始更新员工数据......】")
		get_userid(True)
		log.info("【更新员工数据完毕！】")
		log.info("**********************")

if __name__ == '__main__':
	main()


#get_userid(getToken(),get_departmentlist(getToken(),False),True)
#access_token = getToken()

#get_user:id(access_token,departmentid,True)