def handle(self, *args, **options):
import time,os
from base.sync_api import SYNC_MODEL, get_att_record_file
if SYNC_MODEL:
from base.backup import get_attsite_file
from mysite.iclock.device_http.sync_conv_att import line_to_log
batch_lens = 100 # 最多插入的批次，根据服务器的配置以及数据库的性能来配置条数的多少
cnts = get_attsite_file()["SYS"]["SQL_BATCH_CNTS"]
if cnts:
batch_lens = int(cnts)
while True:
dev,lines,file = get_att_record_file()
if lines:
lens = len(lines)
times = lens%batch_lens == 0 and lens/batch_lens or (lens/batch_lens + 1)
for i in range(times):
m_lines = lines[i*batch_lens:(i+1)*batch_lens]
succcess = True
while succcess:
ret = line_to_log(dev,m_lines,event="files") # ret =False <数据库断开了>
if not ret:
time.sleep(60*2)
else:
succcess = False
time.sleep(0.1)
if file:
os.remove(file)
time.sleep(0.1)
else:
from django.db import connection as conn
import time
while True:
start =time.time()
while True:
try:
process_data()
except:
import traceback;traceback.print_exc()
time.sleep(5)
end=time.time()
if end-start>60*30:#半个小时重启一下连接
break
try:
#退出的时候防止是断网，不断开连接而不能处理数据，所以关闭该连接
cur=conn.cursor()
cur.close()
conn.close()
except:
pass
def backup_db():
logger = logging.getLogger()
database_user = settings.DATABASES["default"]["USER"]
database_password = settings.DATABASES["default"]["PASSWORD"]
database_engine = settings.DATABASES["default"]["ENGINE"]
database_name = settings.DATABASES["default"]["NAME"]
database_host = settings.DATABASES["default"]["HOST"]
database_port = settings.DATABASES["default"]["PORT"]
backup_file = ""
dict = get_attsite_file()
path = dict["Options"]["BACKUP_PATH"]#.encode('gbk')
#print type(path)
#print path
if path == "":
path = settings.APP_HOME+"/tmp"
if not os.path.exists(path):
os.mkdir(path)
if database_engine == "django.db.backends.mysql":
backup_file = path+"\\db_upgrade_"+datetime.datetime.now().strftime("%Y%m%d%H%M%S") +".sql"
#backup_file = "python manage.pyc dumpdata >\"%s\""%backup_file
if database_password != "":
backup_file = "mysqldump --hex-blob -l --opt -q -R --default-character-set=utf8 -h %s -u %s -p%s --port %s --database %s >%s"%(database_host, database_user, database_password, database_port, database_name, backup_file)
else:
backup_file = "mysqldump --hex-blob -l --opt -q -R --default-character-set=utf8 -h %s -u %s --port %s --database %s >%s"%(database_host, database_user, database_port, database_name, backup_file)
elif database_engine == "sqlserver_ado":
database_name='[%s]'%database_name
backup_file = path+"\\db_upgrade_"+datetime.datetime.now().strftime("%Y%m%d%H%M%S") +".bak"
backup_file = '''sqlcmd -U %s -P %s -S %s -Q "backup database %s to disk='%s'"'''%(database_user,database_password,database_host,database_name,backup_file)
elif database_engine == "django.db.backends.oracle":
path = os.environ["path"]
list = path.split(";")
oracle_path = ""
for i in list:
if "oraclexe" in i:
oralce_path = i
backup_file = path+"\\db_upgrade_"+datetime.datetime.now().strftime("%Y%m%d%H%M%S") +".dmp"
backup_file = "%s\\exp %s/%s@%s file='%s'"%(oracle_path,database_user,database_password,database_name,backup_file)
p = subprocess.Popen(backup_file.encode('gbk'), shell=True, stderr=subprocess.PIPE)
p.wait()
stderrdata = p.communicate()
if p.returncode != 0:
logger.error(stderrdata)
def __init__(self, req, input_name="import_data"):
u"初始化基础类"
self.input_name = "import_data" #前段上传文件控件的名称
self.valid_format = ["xls", "csv"] #导入的文件格式
self.request = req #请求
self.head = None #模型头
self.records = [] #记录
self.format = None #上传文件的格式
self.upload_file = None #上传的文件
self.app_label = None #应用名称
self.model_name = None #模型名称
self.model_cls = None #模型类
self.error_info = [] #错误信息
self.valid_head_indexs = [] #导入文件中合法字段的索引
self.valid_model_fields = [] #导入文件中包含的模型字段
self.other_fields = [] #其他需要没有在文档中的字段，使用默认值插入到数据
self.calculate_fields_verbose = [
] # {u"%s"%_(u"部门编号"):""} 用来给其他列初始化的列，例如人员中的部门编号，就是为了初始化，人员中的部门字段
self.calculate_fields_index = {} # 在文档头中的index
#self.map_field_head = {} #模型字段的名称与导入文件的列的序号对应关系
self.must_fields = [] #必须要用户填的字段，如员工的PIN号。。。
self.current_dbtype = "sqlserver_ado" #当前连接数据库的驱动名称
self.input_name = input_name
self.need_read_data = True #默认需要从文件中读取数据
self.need_update_old_record = self.request.POST.get(
"duplicate_pin", False) #已经存在的记录是否需要更新
self.sql_batch_cnts = 80
if self.need_update_old_record == u"0":
self.need_update_old_record = False
else:
self.need_update_old_record = bool(self.need_update_old_record)
app_label = self.request.POST.get("app_label", None)
model_name = self.request.POST.get("model_name", None)
#print "app_label:%s,model_name:%s\n"%(app_label,model_name)
if app_label and model_name:
self.model_cls = models.get_model(app_label, model_name)
else:
self.error_info.append(u"%s" % _(u"模块参数错误"))
cnts = get_attsite_file()["SYS"]["SQL_BATCH_CNTS"]
if cnts:
self.sql_batch_cnts = int(cnts)
def backup_db_validate(request, type):
from dbapp.models import DbBackupLog
from base.backup import get_attsite_file
dict = get_attsite_file()
path = dict["Options"]["BACKUP_PATH"]
if path == "":
return getJSResponse('null')
if not os.path.exists(path):
return getJSResponse('notexist')
#type = int(request.REQUEST.get("type", 0))
if int(type) == BACKUP_IMMEDIATELY:
end_dt = datetime.datetime.now()
start_dt = (end_dt +
datetime.timedelta(hours=-settings.DB_DBCKUP_STEPTIME))
cc = DbBackupLog.objects.filter(starttime__range=(start_dt, end_dt))
if cc:
return getJSResponse('fail')
else:
return getJSResponse(path)
else:
return getJSResponse(path)
def __init__(self, req=None, input_name="import_data"):
u"初始化基础类"
self.input_name = "import_data" #前段上传文件控件的名称
self.valid_format = ["xls", "csv"] #导入的文件格式
self.request = req #请求
self.head = None #模型头
self.records = [] #记录
self.format = None #上传文件的格式
self.upload_file = None #上传的文件
self.app_label = None #应用名称
self.model_name = None #模型名称
self.model_cls = None #模型类
self.error_info = [] #错误信息
self.valid_head_indexs = [] #导入文件中合法字段的索引
self.valid_model_fields = [] #导入文件中包含的模型字段
self.other_fields = [] #其他需要没有在文档中的字段，使用默认值插入到数据
self.calculate_fields_verbose = [
] # {u"%s"%_(u"部门编号"):""} 用来给其他列初始化的列，例如人员中的部门编号，就是为了初始化，人员中的部门字段
self.calculate_fields_index = {} # 在文档头中的index
self.must_fields = [] #必须要用户填的字段，如员工的PIN号。。。
self.current_dbtype = "sqlserver_ado" #当前连接数据库的驱动名称
self.input_name = input_name
self.need_read_data = False #不需要验证
self.need_update_old_record = True
self.sql_batch_cnts = 80
cnts = get_attsite_file()["SYS"]["SQL_BATCH_CNTS"]
if cnts:
self.sql_batch_cnts = int(cnts)
app_label = "personnel"
model_name = "IssueCard"
self.model_cls = models.get_model(app_label, model_name)
self.calculate_fields_verbose = [u"%s" % _(u"人员编号"), "user_pk"]
self.must_fields = [
u"%s" % _(u"人员编号"),
u"%s" % _(u"卡号"),
]
def get_videolinkage_picture_savepath():
from base.backup import get_attsite_file
db_dict = get_attsite_file()
save_path = db_dict["Options"]["VIDEOLINKAGE_PICTURE_PATH"]
#print"______-----save_path=",save_path,"---type=",type(save_path)
return save_path
# -*- coding: utf-8 -*-
#*****一些静态的常量，根据不同的系统自己写一份不同的参数，
#****不在通过mysite.att in ...这种方式来做复杂的判断
#如果能够区分默写操作一定是某子系统的，可以通过ATT,POST,ACCESS...这些个参数来控制，
#否则使用新的自定义参数来配置
#默认为ZKECO的配置
import const
from django.utils.translation import ugettext_lazy as _
import settings
from base.backup import get_attsite_file
att_file = get_attsite_file()
v_pos_id = False
v_pos_ic = False
op_pos_id = att_file["Options"]["POS_ID"]
op_pos_ic = att_file["Options"]["POS_IC"]
if op_pos_id.lower() == "true":
v_pos_id = True
if op_pos_ic.lower() == "true":
v_pos_ic = True
#-----------------------------------------app控制参数-------------------------------
const.ATT = False
const.IACCESS = False
const.POS = False
const.POS_ID = False #ID消费
const.POS_IC = False #IC消费
#-----------------------------------------app控制参数-------------------------------
#控制帮助文档
# -*- coding: utf-8 -*-
import os
import traceback
from lxml import etree
from django.core.cache import cache
import settings
#from django.db import connection as conn
from dbutils_pool import getConn, reConn
from dbutils_pool import OperationalError, InternalError, ProgrammingError
from base.backup import get_attsite_file
TIMEOUT = 7 * 24 * 3600 #7天
develop_model = False
if get_attsite_file()["Options"]["SQL_PRINT"].lower() == "true":
develop_model = True
def get_curr_db_engine_name():
u"""
获取当前使用的数据库类型
"""
db_name = ""
if settings.DATABASES["default"]["ENGINE"] == "sqlserver_ado":
db_name = "sqlserver"
elif settings.DATABASES["default"]["ENGINE"] == "django.db.backends.mysql":
db_name = "mysql"
elif settings.DATABASES["default"][
"ENGINE"] == "django.db.backends.sqlite3":
db_name = "sqlite"
def sync_db():
try:
backup_db()
except:
print_exc()
pass
os.system("python manage.pyc syncdb")
conn = connections['default']
cursor = conn.cursor()
db_dict = get_attsite_file()["DATABASE"]
database_engine = db_dict["ENGINE"]
try:
if database_engine == 'sqlserver_ado':
# print 'sqlserver_ado'
sql="alter table Departments alter COLUMN code nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table Departments alter COLUMN DeptName nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table personnel_empchange alter column newvalue nvarchar(MAX)"
cursor.execute(sql)
conn._commit()
sql="alter table personnel_empchange alter column oldvalue nvarchar(MAX)"
cursor.execute(sql)
conn._commit()
#
sql="alter table checkinout alter column checktype nvarchar(5)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo alter column street nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo alter column cuser1 nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo alter column cuser2 nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql = "select length from syscolumns where id=object_id('userinfo') and name='password';"
cursor.execute(sql)
qets = cursor.fetchall()
if qets and qets[0][0] == 16:#判断password长度，如果是8则未加过密
sql="alter table userinfo alter column Password nvarchar(16)"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door alter column force_pwd nvarchar(18)"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door alter column supper_pwd nvarchar(18)"
cursor.execute(sql)
conn._commit()
sql="alter table iclock alter column comm_pwd nvarchar(32)"
cursor.execute(sql)
conn._commit()
process_pwd()
print 'sqlserver_ado'
print 'create tmp table and copy data'
sql='SELECT DISTINCT log_tag,time,pin,card_no,device_id,device_sn,device_name, door_id, door_name, in_address,out_address,verified,state,event_type,trigger_opt \
INTO acc_monitor_log_tmp1 FROM acc_monitor_log'
cursor = connection.cursor()
cursor.execute(sql)
connection._commit()
print 'TRUNCATE TABLE acc_monitor_log'
sql='TRUNCATE TABLE acc_monitor_log'
cursor.execute(sql)
connection._commit()
try:
sql = 'update acc_monitor_log_tmp1 set out_address=-1 where out_address is null'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'alter table acc_monitor_log add constraint tri_def default -1 for trigger_opt'
sql='alter table acc_monitor_log add constraint tri_def default -1 for trigger_opt'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'alter table acc_monitor_log add constraint in_address_def default -1 for in_address'
sql='alter table acc_monitor_log add constraint in_address_def default -1 for in_address'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'ALTER TABLE acc_monitor_log ADD CONSTRAINT time_pin UNIQUE (TIME, pin,card_no,device_id,door_id,in_address,out_address,verified,state,event_type,trigger_opt)'
sql='ALTER TABLE acc_monitor_log ADD CONSTRAINT time_pin UNIQUE (TIME, pin,card_no,device_id,door_id,in_address,out_address,verified,state,event_type,trigger_opt)'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'copy date from tmp'
sql='INSERT INTO acc_monitor_log(change_time, create_time , STATUS , log_tag,TIME , pin , card_no , device_id , device_sn, device_name, door_id, door_name, in_address ,out_address, verified , state , event_type , trigger_opt ) \
SELECT getdate(), getdate(),0, log_tag,TIME , pin , card_no , device_id , device_sn,device_name, door_id, door_name, in_address ,out_address, verified , state , event_type , trigger_opt FROM acc_monitor_log_tmp1'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
print 'DROP table acc_monitor_log_tmp1'
sql='DROP table acc_monitor_log_tmp1'
cursor.execute(sql)
connection._commit()
elif database_engine=='mysql':
# print "personnel empchange"
sql="alter table personnel_empchange modify oldvalue longtext"
cursor.execute(sql)
conn._commit()
sql="alter table personnel_empchange modify newvalue longtext"
cursor.execute(sql)
conn._commit()
sql="alter table Departments modify DeptName nvarchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table Departments modify code nvarchar(100)"
cursor.execute(sql)
conn._commit()
#
sql="alter table checkinout modify checktype varchar(5)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify street varchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify cuser1 varchar(100)"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify cuser2 varchar(100)"
cursor.execute(sql)
conn._commit()
sql = 'desc userinfo'
cursor.execute(sql)
qets = cursor.fetchall()
for q in qets:#判断password长度，如果是8则未加过密
if q[0].lower() == 'password' and q[1] == 'varchar(8)':
sql="alter table userinfo modify Password varchar(16)"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door modify force_pwd varchar(18)"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door modify supper_pwd varchar(18)"
cursor.execute(sql)
conn._commit()
sql="alter table iclock modify comm_pwd varchar(32)"
cursor.execute(sql)
conn._commit()
process_pwd()
break
print 'mysql'
print 'create tmp table and copy data'
sql='CREATE TABLE acc_monitor_log_tmp1 SELECT DISTINCT log_tag,TIME,pin,card_no,device_id,device_sn,device_name, door_id, door_name, in_address,out_address,verified,state,event_type,trigger_opt \
FROM acc_monitor_log '
cursor = connection.cursor()
cursor.execute(sql)
connection._commit()
print 'TRUNCATE TABLE acc_monitor_log'
sql='TRUNCATE TABLE acc_monitor_log'
cursor.execute(sql)
connection._commit()
try:
sql = 'update acc_monitor_log_tmp1 set out_address=-1 where out_address is null'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'ALTER TABLE acc_monitor_log MODIFY trigger_opt INT(6) DEFAULT -1'
sql='ALTER TABLE acc_monitor_log MODIFY trigger_opt INT(6) DEFAULT -1'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'ALTER TABLE acc_monitor_log MODIFY in_address INT(6) DEFAULT -1'
sql='ALTER TABLE acc_monitor_log MODIFY in_address INT(6) DEFAULT -1'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'ALTER TABLE acc_monitor_log ADD CONSTRAINT time_pin UNIQUE'
sql='ALTER TABLE acc_monitor_log ADD CONSTRAINT time_pin UNIQUE (TIME, pin,card_no,device_id,door_id,in_address,out_address,verified,state,event_type,trigger_opt)'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
try:
print 'copy date from tmp'
sql='INSERT INTO acc_monitor_log(change_time, create_time , STATUS , log_tag,TIME , pin , card_no , device_id ,device_sn, device_name, door_id, door_name, in_address , out_address,verified , state , event_type , trigger_opt ) \
SELECT NOW(), NOW(),0, log_tag,TIME , pin , card_no , device_id ,device_sn, device_name, door_id, door_name, in_address , out_address,verified , state , event_type , trigger_opt FROM acc_monitor_log_tmp1'
cursor.execute(sql)
connection._commit()
except:
print_exc()
pass
print 'drop table acc_monitor_log_tmp1'
sql='drop table acc_monitor_log_tmp1'
cursor.execute(sql)
connection._commit()
elif database_engine=='oracle':
# sql="alter table personnel_empchange modify oldvalue longtext"
# cursor.execute(sql)
# conn._commit()
#
# sql="alter table personnel_empchange modify newvalue longtext"
# cursor.execute(sql)
# conn._commit()
sql="alter table Departments modify (DeptName nvarchar2(100))"
cursor.execute(sql)
conn._commit()
sql="alter table Departments modify (code nvarchar2(100))"
cursor.execute(sql)
conn._commit()
#
sql="alter table checkinout modify (checktype nvarchar2(5))"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify (street nvarchar2(100))"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify (cuser1 nvarchar2(100))"
cursor.execute(sql)
conn._commit()
sql="alter table userinfo modify (cuser2 nvarchar2(100))"
cursor.execute(sql)
conn._commit()
sql="select DATA_LENGTH from user_tab_columns where table_name='USERINFO' and column_name='PASSWORD'"
cursor.execute(sql)
qets = cursor.fetchall()
if qets and qets[0][0] == '16':
sql="alter table userinfo modify (Password nvarchar2(16))"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door modify (force_pwd nvarchar2(18))"
cursor.execute(sql)
conn._commit()
sql="alter table acc_door modify (SUPPER_PWD nvarchar2(18))"
cursor.execute(sql)
conn._commit()
sql="alter table iclock modify (comm_pwd nvarchar2(32))"
cursor.execute(sql)
conn._commit()
process_pwd()
try:
sql="alter table acc_monitor_log add constraint uq_id unique(time, pin,card_no,device_id,door_id,in_address,verified,state,event_type,trigger_opt)"
cursor.execute(sql)
conn._commit()
except:
pass
cursor = connection.cursor()
#sql = 'update userinfo set isatt=1'
#cursor.execute(sql)
#员工自助补签卡添加审核
sql = "UPDATE checkexact SET audit_status =2 WHERE audit_status is NULL"
cursor.execute(sql)
connection._commit()
#员工自助请假单添加审核
sql = "UPDATE user_speday SET audit_status =2 WHERE audit_status is NULL"
cursor.execute(sql)
connection._commit()
except:
import traceback;traceback.print_exc()
pass
# print u'update database complie...'
finally:
connection.close()
except:
supper_pwd = ''
sql = u"update acc_door set force_pwd='%s', supper_pwd='%s' where id=%s"%(encryption(force_pwd), encryption(supper_pwd), q[0])
#cursor = connection.cursor()
cursor.execute(sql)
connection._commit()
connection._commit()
connection.close()
except:
import traceback;traceback.print_exc()
connection.close()
pass
try:
os.environ['DJANGO_SETTINGS_MODULE'] = 'mysite.settings'
dict = get_attsite_file()
version = None
version_file = str(dict["Options"]["Version"])
version_db = version_file.split('.')[0] #attsite中的数据库版本号
new_svn_version = version_file.split('.')[1] #attsite 中的svn 版本号
app_option = AppOption.objects.filter(optname='dbversion')
if app_option:
version = app_option[0].value.split('.')[0] #数据库中的数据库版本号
svn_version = app_option[0].value.split('.')[1] #数据库中的svn 的版本号
if not version:
super(AppOption,AppOption(optname="dbversion", value=version_file, discribe=u"%s" % _(u'版本'))).save()
sync_db()
elif int(version_db) > int(version) or int(new_svn_version) >int(svn_version) :
app_option[0].value = version_file
super(AppOption,app_option[0]).save()
sync_db()