def getByContentType(cls,idContentType,lang='en',active=1):
return DBSession.query(cls).join(cls.language).\
filter( func.upper( FixLanguage.code) == func.upper(lang ) ).\
filter(cls.active == str(active).decode('utf-8'), cls.id_content_type == str(idContentType ).decode('utf-8'))\
.union( DBSession.query(cls).join(cls.language).\
filter( func.upper( FixLanguage.code) == func.upper('en' ) ).\
filter(cls.active == str(active).decode('utf-8'), cls.id_content_type == str(idContentType ).decode('utf-8'))).first()
def getInvitationNumberByPublicId(cls,idpublic,page=0, page_size=None):
result = [];
try:
param = {};
sql = """select
sur_voter.id_voter,
sur_voter.email,
CONCAT(sur_voter.firstname , ' ' , sur_voter.lastname) as name,
count(sur_send_mail.id_send_mail) as no_send,
MAX(sur_send_mail.send_date) as last_create_date
from
sur_voter LEFT JOIN sur_respondents on sur_voter.id_voter = sur_respondents.id_voter
LEFT OUTER JOIN sur_send_mail on (sur_respondents.id_voter = sur_send_mail.id_voter and sur_respondents.id_question_option = sur_send_mail.id_question_option)
where sur_respondents.id_question_option = :idpublic
GROUP BY
sur_voter.id_voter
""" ;
param['idpublic'] = idpublic;
colunmResult = DBSession.execute(text(sql), param );
count= str(colunmResult.rowcount);
if page_size:
sql = sql + """ limit :limit """;
param['limit'] = page_size;
if page:
page = 0 if page < 0 else page;
sql = sql + """ offset :offset """;
param['offset'] = str(page*page_size) ;
colunmResult = DBSession.execute(text(sql), param );
for row in colunmResult :
if row[0]:
result.append({'id_voter':row[0],'email': row[1],'name': row[2],'no_send': row[3],'last_create_date': row[4]})
else:
count =0;
return True, result, count;
except Exception as e:
print e;
return False, result, 0;#e.__str__();
def getScoreViewHtml(cls):
# print "contextual_connect : %s" %( 'contextual_connect' in DBSession)
env = DBSession.query(cls).filter(cls.environment_key == str('SCORE_VIEW_HTML').decode('utf-8') , cls.active == str('1').decode('utf-8')).first();
return env.description;
def updateStatusSended(cls,id):
try:
#sql = "update sur_send_mail set status= 'F', sended_date='" + str(datetime.now()) + "' where id_send_mail = " + str(id);
#result = DBSession.execute(sql);
#DBSession.flush() ;
#DBSession.execute("UNLOCK TABLES");
#email = DBSession.query(cls).filter(cls.id_send_mail == id).first();
#email.status = 'F';
DBSession.query(cls).filter(cls.id_send_mail == id).update({"status": 'F',"sended_date" : datetime.now() }) ;
DBSession.flush() ;
return True, 'success';
except Exception as e:
print "Exception----------> : "
print e;
return False, 'Cannot delete.';#e.__str__();
def updateStatusReaded(cls,code):
sendmail = DBSession.query(cls).filter(cls.gen_code == code).first() ;
if sendmail :
sendmail.total_read = sendmail.total_read +1
if sendmail.readed_date :
sendmail.update_readed_date = datetime.now()
else:
sendmail.readed_date = datetime.now()
return True, 'success'
return False, 'Not Found'
def querySendMail(cls,page=0,page_size=None):
query = DBSession.query(cls).filter(cls.status == str('W') );
if page_size:
query = query.limit(page_size)
if page:
query = query.offset(page*page_size)
value = query.all();
return value;
def getBySearch(cls, search="", page=0, page_size=None, act=1):
query = DBSession.query(cls).filter(
cls.message.like('%' + str(search) + '%'), cls.active == str(act))
query_total = query
if page_size:
query = query.limit(page_size)
if page:
page = 0 if page < 0 else page
query = query.offset(page * page_size)
values = query.all()
total = query_total.count()
#data = [];
#for v in values:
# data.append(v.to_json());
return values, total
def currentSessionAuthen(cls,session):
sql = """ select ( expire_date > NOW() ) as result from sur_user_session_authen where session_value = :sessionKey limit 1 """;
colunmResult = DBSession.execute(text(sql), {'sessionKey' : session} );
result = -1;
for row in colunmResult :
result = row[0];
print "result value : " + str(result);
if (result <=0):
return False;
else:
return True;
pass;
def getUserActivated(cls,user_id):
return DBSession.query(cls).filter(cls.id_gen_code_type == str('2').decode('utf-8'),cls.success == str("1").decode('utf-8'),cls.user_id == str(user_id).decode('utf-8')).order_by(desc( cls.create_date )).first();
def getByDeactivateCode(cls,code):
return DBSession.query(cls).filter(cls.id_gen_code_type == str('6').decode('utf-8'),cls.success == str("1").decode('utf-8') ,cls.code == str(code).decode('utf-8')).order_by(desc( cls.create_date )).first();
def getUserLinkVoterBy(cls, idLink, idUserRef):
return (
DBSession.query(cls)
.filter(cls.id_use_external_link == str(idLink), cls.id_user_ref == str(idUserRef))
.first()
)
def updateall(self):
print "update"
DBSession.merge(self,load=True)
def getByPageandZone(cls,pageName,ZoneNumber,active=1):
return DBSession.query(cls).filter( cls.page_name.ilike("%"+pageName+"%") , cls.zone_number == str(ZoneNumber), cls.active == str(active).decode('utf-8')).first();
def getByid(cls,id,act):
return DBSession.query(cls).filter( cls.id_social_type == str(id).decode('utf-8') , cls.active == str(act).decode('utf-8') ).first();
def getEmailTemplate(cls):
env = DBSession.query(cls).filter(cls.environment_key == str('EMAIL_TEMPLATE').decode('utf-8') , cls.active == str('1').decode('utf-8')).first();
return env.description;
def getByUserIdAndSocialId(cls,user_id,socialId):
return DBSession.query(cls).filter(cls.user_id == str(user_id).decode('utf-8'), cls.id_social_type == str(socialId).decode('utf-8') ).first();
def getbyKey(cls,key):
return DBSession.query(cls).filter(cls.environment_key == str(key).decode('utf-8') ,cls.active == str('1').decode('utf-8')).first();
def getServerUrl(cls):
env = DBSession.query(cls).filter(cls.environment_key == str('SERVER_URL').decode('utf-8') , cls.active == str('1').decode('utf-8')).first();
return env.description;
def getAll(cls,active):
return DBSession.query(cls).filter(cls.active == str(active).decode('utf-8')).all();
def getServerName(cls,serverName):
return DBSession.query(cls).filter(cls.server_name == str(serverName).decode('utf-8') ).first()
def getAll(cls, act=1):
return DBSession.query(cls).filter(
cls.active == str(act).decode('utf-8')).all()
def getSocialByUserId(cls,user_id):
return DBSession.query(SocialType,cls).outerjoin(cls, and_(SocialType.id_social_type == cls.id_social_type, cls.user_id == str(user_id).decode('utf-8') ) ).all();
def deleteById(cls,id):
result = DBSession.query(cls).filter(cls.id_user_social_network == id).delete()
DBSession.flush()
return result
def getByUserId(cls,user_id):
return DBSession.query(cls).filter(cls.user_id == str(user_id).decode('utf-8') ).all();
def save (self):
DBSession.add(self);
DBSession.flush() ;
def getBySocial(cls,social_id, socialtype_id):
return DBSession.query(cls).filter(cls.provider_user_id == str(social_id).decode('utf-8'), cls.id_social_type == str(socialtype_id).decode('utf-8') ).first();
def getUserLinkBy(cls, idUser, idTest, idPublic):
return (
DBSession.query(cls)
.filter(cls.id_user == str(idUser), cls.id_test == str(idTest), cls.id_question_option == str(idPublic))
.first()
)
def getUserClientAuthen(cls,client_id,client_secret):
return DBSession.query(cls).filter(cls.client_id == str(client_id).decode('utf-8'),cls.client_secret == str(client_secret).decode('utf-8'),cls.active == str('1').decode('utf-8')).first();
def getByUserId(cls,user_id):
return DBSession.query(User).filter(User.user_id == str(user_id).decode('utf-8') ).first();
def updateGroupUserVoter(self):
result = DBSession.execute('insert into tg_user_group values(:user_id,:group_id)', {'user_id': self.user_id,'group_id':2})
#DBSession.execute(s, user_id= self.user_id ).fetchall()
DBSession.flush() ;
def updateGroupUserCreator(self):
result = DBSession.execute('insert into tg_user_group values(:user_id,:group_id)', {'user_id': self.user_id,'group_id':4})
DBSession.flush() ;
def getAll(cls,act):
if act is not None:
return DBSession.query(cls).filter(cls.active == str(act).decode('utf-8')).all();
#return DBSession.query(cls).get(act);
else:
return DBSession.query(cls) .all();
def getById(cls, id_detail_report=0, act=1):
return DBSession.query(cls).filter(
cls.id_detail_report == str(id_detail_report),
cls.active == str(act)).first()