def normalize(string, repl=None, convert_numerals=True, convert_possessive=True, unidecode=True):
""" Normalize string """
# Convert superscript/subscript numerals (Alien³ -> Alien 3):
string = re.sub(r'%s' % '|'.join(constants.NUMERALS.get('superscript') + constants.NUMERALS.get('subscript')), lambda m: ' ' + m.group(), string, flags=re.I)
# Convert possessive:
if convert_possessive:
string = re.sub(r'\'(s([\s]|$))', lambda m: m.group(1), string, flags=re.I)
# Remove any accentuated characters:
if unidecode:
string = _unidecode(string)
# Normalize word delimiters:
for i in [r'([\._]|\s\-\s)', r'\s{2,}']:
string = re.sub(i, ' ', string)
# Normalize special characters:
REPLACEMENT_DICT = {':': ' ', '!': ' ', ',': ' ', '?': ' ', "'": ' ', '-': ' ', '&': 'and', '*': ' ', '"':''}
# Update dict with user inputs:
REPLACEMENT_DICT.update(repl or {})
# Replace special characters:
for k in sorted(REPLACEMENT_DICT, reverse=True):
if REPLACEMENT_DICT[k] is not None:
string = re.sub(re.escape(k), REPLACEMENT_DICT[k], string, flags=re.I)
# Remove successive delimiters:
string = re.sub(r'\s{2,}', ' ', string)
# Convert roman numerals with "episode" indicators (part, episode, etc.):
if convert_numerals:
for v in constants.NUMERALS.values():
string = re.sub(r'(?P<episode>%s)\s?(?P<number>%s)' % ('|'.join(constants.get('video.episode')), '|'.join(reversed(v))), lambda m: m.group('episode') + ' ' + str(to_int(m.group('number'))), string, flags=re.X|re.I)
return string.lower().strip()
def processar(self, entrada):
entradaAux = unidecode._unidecode(entrada)
palavras = entradaAux.split(
' ') # criar array com as palavras separadas por espaço
numeros = []
indiceHora = 0
indiceDia = 0
countAux = 0
count = 0
i = 0
for p in palavras:
palavras[i] = p.lower()
i = i + 1
for word in palavras:
if re.findall(
r'-?\d+\.?\d*', word
): # reconhecer somente numeros no contains. && pegar o primeiro num da frase
if (palavras[count - 1] == 'as'
and countAux == 0) or countAux > 0:
countAux = countAux + 1
if countAux == 1:
indiceHora = count
if countAux == 2:
if palavras[count - 1] != 'dia':
indiceMinuto = count
else:
indiceMinuto = 0
indiceDia = count
break
if countAux == 3 and palavras[count - 1] == 'dia':
indiceDia = count
count = count + 1
if countAux == 0: # entrada invalida (nao possui numeros)
print('Entrada inválida!')
return None
if countAux == 1: # se count for == 1 significa que só tem um numero no o que indica provavelmente que
datap = datetime.datetime.today(
) # o usuario só falou a hora logo atribuir data atual
datap = datetime.datetime(datap.year, datap.month, datap.day,
int(palavras[indiceHora]), 0, 0)
if count >= 1: # Encontrando a descricao de uma entrada valida
descricaop = self.pdescricao(indiceHora, palavras)
if countAux > 1:
datap = self.pdata(indiceHora, indiceMinuto, indiceDia, countAux,
palavras)
categoria = self.idfcategoria(palavras)
atributos = [descricaop, datap, categoria]
return atributos
def save_tweepy_for_users(proxy=True, thld=400):
if proxy:
from data.proxy.tor import TorProxy
tor = TorProxy()
tor.start_tor()
tor.proxy_requests()
user_names_tweepy = tweepyClient.get_user_names()
users_db = [
ud._unidecode(user.username)
for user in session.query(TwitterUser).all()
]
usernames = user_names_tweepy + list(
set(users_db) - set(user_names_tweepy))
#Remove party users
for party in partiesTP.parties:
usernames = [
x for x in usernames if x != ud._unidecode(party.pusername)
]
for user in party.pusers:
usernames = [
x for x in usernames if x != ud._unidecode(user.username)
]
print("Users: " + str(len(usernames)))
startdate, enddate = date(2017, 8, 28), date(2017, 10, 1)
for user in usernames:
print("user => [ " + str(user) + " ]")
tweets = tweepyClient.get_user_timeline_tweets(user,
startdate,
enddate,
threshold=thld)
print("Attempting to save: " + str(len(tweets)) + " tweets.")
for t in tweets:
twitterClient.saveTweet(t, table=Tweet)
if proxy:
tor.stop_tor()
def clean(label):
"""Cleans the label by doing the following:
- Convert unicode characters their ascii equivalents.
- Strip leading and trailing whitespace.
- Convert to lower case.
- Strip all punctuation except for the apostrophe (').
"""
label = _unidecode(label)
label = label.strip()
label = label.lower()
label = label.translate(_remove_table)
return label
def get_info():
user_info_dict = {'user': '******', 'passwd': '*****', 'api_type': 'json'}
sess = requests.Session()
sess.headers.update({'User-Agent': 'I am testing Alexa: Sentdex'})
sess.post('https://ssl.reddit.com/api/login', data=user_info_dict)
time.sleep(1)
url = 'https://google.com'
html = sess.get(url)
data = json.loads(html.content.decode('utf-8'))
titles = [
unidecode._unidecode(listing['data'][''])
for listing in data['data']['children']
]
titles = '... '.join([i for i in titles])
return titles
def get_user_names(self): #remove duplicates list(set(t))
user_ids = []
for dist in self.locations:
print("District: " + dist['Name'])
i = 0
while True:
try:
users = self.api.search_users(
q="geocode:" + dist['lat'] + "," + dist['lon'] + "," +
dist['radius'] + "km AND lang:pt",
page=i)
except Exception:
print("Exception caught.")
break
if not len(users) > 0:
break
for user in users:
user_ids.append(ud._unidecode(user.screen_name))
i += 1
total_users = list(set(user_ids))
print("Total users: " + str(len(total_users)))
return total_users
def main():
print "Start"
query = '#' + sys.argv[1]
print(query)
start = "2017-09-01"
stop = "2017-12-20"
#Creo il dizionario per il filtraggio degli hashtag
# dizionario_hashtag = {}
# with open('../utils/ListaHashtag.txt', 'r') as fileHashtags:
#
# for line in fileHashtags:
# if not dizionario_hashtag.has_key(line.strip()):
# dizionario_hashtag[line] = '#'+line.strip().lower()
# for x in dizionario_hashtag:
# print(len(dizionario_hashtag))
# print(dizionario_hashtag[x])
if not os.path.exists('pickle/' + query + 'TestAWS'):
os.makedirs('pickle/' + query + 'TestAWS')
tweetCriteria = got.manager.TweetCriteria().setQuerySearch(query).setSince(
start).setUntil(stop)
tweet = got.manager.TweetManager.getTweets(tweetCriteria)
list = []
listRed = []
listBlue = []
listYellow = []
#creo il dizionario con l'oggetto tweetOcc
dizionario_tweet = {}
dizionario_tweet_Blue = {}
dizionario_tweet_Red = {}
dizionario_tweet_Yellow = {}
#for t in range(len(tweet)):
for t in range(len(tweet)):
# print (tweet[t].hashtags)
# list_hash = tweet[t].hashtags.strip().lower().split(" ")
#
# b3 = [val for val in list_hash if val in dizionario_hashtag.values()]
#
# if len(b3)==0:
# continue
# else:
list.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet.has_key(tweet[t].username):
dizionario_tweet[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
# dizionario_tweet.update[tweet[t].username](countOccTweet(tweet[t].username,countOccTweet.count+1))
dizionario_tweet[tweet[t].username].count += 1
if (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "ForzaItalia"):
listBlue.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Blue.has_key(tweet[t].username):
dizionario_tweet_Blue[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Blue[tweet[t].username].count += 1
elif (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "5stelle"):
listRed.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Red.has_key(tweet[t].username):
dizionario_tweet_Red[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Red[tweet[t].username].count += 1
elif (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "Altro"):
listYellow.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Yellow.has_key(tweet[t].username):
dizionario_tweet_Yellow[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Yellow[tweet[t].username].count += 1
with open(
'./pickle/' + query + 'TestAWS/tweetBlue' + query + '_' + start +
'_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listBlue, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetBlue' + query + '_' + start +
'_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Blue, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetRed' + query + '_' + start +
'_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listRed, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetRed' + query + '_' + start +
'_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Red, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetYellow' + query + '_' + start +
'_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listYellow, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetYellow' + query + '_' + start +
'_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Yellow, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetAll' + query + '_' + start +
'_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(list, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/tweetAll' + query + '_' + start +
'_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet, output, pickle.HIGHEST_PROTOCOL)
#for x in dizionario_tweet:
# print (dizionario_tweet[x])
# print('Dopo Scritturea PICKLE')
# with open('./pickle/tweet' + query + '_' + start + '_' + stop + '_data.pkl', 'rb') as input:
# readList = pickle.load(input)
#
# for i in range(0, len(readList)):
# print(len(readList))
# print(readList[i].username,readList[i].numRetweet,readList[i].text,readList[i].hashtags)
retweetmain(listRed, query, start, stop, dizionario_tweet_Red, "Red")
retweetmain(listBlue, query, start, stop, dizionario_tweet_Blue, "Blue")
retweetmain(listYellow, query, start, stop, dizionario_tweet_Yellow,
"Yellow")
retweetmain(list, query, start, stop, dizionario_tweet, "All")
with open('./pickle/' + query + 'TestAWS/Finito' + str(dt.datetime.now()),
"wb") as output:
pickle.dump(str(dt.datetime.now), output, pickle.HIGHEST_PROTOCOL)
print "Finish"
def main():
print "Start"
query = '#' + sys.argv[1]
print(query)
start = "2017-09-01"
stop = "2017-11-05"
if not os.path.exists('pickle/' + query + 'TestAWS/5Novembre/'):
os.makedirs('pickle/' + query + 'TestAWS/5Novembre/')
#collect tweet using Get Old Tweet, query is the hashtag to search, start and stop the time of research
tweetCriteria = got.manager.TweetCriteria().setQuerySearch(query).setSince(
start).setUntil(stop)
tweet = got.manager.TweetManager.getTweets(tweetCriteria)
list = []
listRed = []
listBlue = []
listYellow = []
#creo il dizionario con l'oggetto tweetOcc
dizionario_tweet = {}
dizionario_tweet_Blue = {}
dizionario_tweet_Red = {}
dizionario_tweet_Yellow = {}
#for t in range(len(tweet)):
for t in range(len(tweet)):
list.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
#Use the sentiment Analysis to divide the tweet and save it on list
if (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "ForzaItalia"):
listBlue.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Blue.has_key(tweet[t].username):
dizionario_tweet_Blue[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Blue[tweet[t].username].count += 1
elif (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "5stelle"):
listRed.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Red.has_key(tweet[t].username):
dizionario_tweet_Red[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Red[tweet[t].username].count += 1
elif (sentiment.checkPartition(u._unidecode(
tweet[t].text).lower()) == "Altro"):
listYellow.append(
Tweet(tweet[t].username, tweet[t].id, tweet[t].retweets,
tweet[t].text, tweet[t].mentions, tweet[t].hashtags,
tweet[t].date, tweet[t].permalink))
if not dizionario_tweet_Yellow.has_key(tweet[t].username):
dizionario_tweet_Yellow[tweet[t].username] = (countOccTweet(
tweet[t].username, 1, tweet[t].date))
else:
dizionario_tweet_Yellow[tweet[t].username].count += 1
#Save all tweet with pickle
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetBlue' + query + '_' +
start + '_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listBlue, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetBlue' + query + '_' +
start + '_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Blue, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetRed' + query + '_' +
start + '_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listRed, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetRed' + query + '_' +
start + '_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Red, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetYellow' + query +
'_' + start + '_' + stop + '_data.pkl', 'wb') as output:
pickle.dump(listYellow, output, pickle.HIGHEST_PROTOCOL)
with open(
'./pickle/' + query + 'TestAWS/5Novembre/tweetYellow' + query +
'_' + start + '_' + stop + '_dictionaryTweet.pkl', 'wb') as output:
pickle.dump(dizionario_tweet_Yellow, output, pickle.HIGHEST_PROTOCOL)
retweetmain(listRed, query, start, stop, dizionario_tweet_Red, "Red")
retweetmain(listBlue, query, start, stop, dizionario_tweet_Blue, "Blue")
retweetmain(listYellow, query, start, stop, dizionario_tweet_Yellow,
"Yellow")
#retweetmain(list, query, start, stop, dizionario_tweet, "All")
with open(
'./pickle/' + query + 'TestAWS/5Novembre/Finito' +
str(dt.datetime.now()), "wb") as output:
pickle.dump(str(dt.datetime.now), output, pickle.HIGHEST_PROTOCOL)
print "Finish"
def unidecode(x):
if not isinstance(x, _unicode):
x = six.u(x)
return _unidecode(x)
def unidecode(x):
if not isinstance(x, _unicode):
x = six.u(x)
return _unidecode(x)
def get_bot_response():
#userText = unidecode._unidecode(request.args.get('msg')).strip().lower() request.args.get('msg')
#print(userText)
userText = unidecode._unidecode(request.args.get('msg')).strip().lower()
msg = userText
response = englishBot.get_response(userText)
if float(response.confidence) > 0.8:
return str(englishBot.get_response(userText))
elif float(response.confidence) > 0.4:
wikipedia.set_lang("pt")
a = msg
result = wikipedia.search(a, results=1)
page = wikipedia.summary(result, sentences=5)
content = page
# print(content)
return str(content)
elif float(response.confidence) == 0:
entrada = str(msg).lower()
p1 = 'http://receita.economia.gov.br/@@busca?advanced_search=False&sort_on=&SearchableText='
p2 = '&portal_type%3Alist=Document&created.query%3Arecord%3Alist%3Adate=1970-01-02&created.range%3Arecord=min'
html = str(p1 + entrada + p2)
stop2 = nltk.corpus.stopwords.words('portuguese')
stop2.append('faço')
stop2.append('um')
stop2.append('gostaria')
stop2.append('fazer')
stop2.append('saber')
stop2.append('posso')
stop2.append('como')
splitter = re.compile('\\W+')
lista_palavras = []
lista = [p for p in splitter.split(entrada) if p != '']
for p in lista:
if p not in stop2:
if len(p) > 1:
lista_palavras.append(p)
ar = len(lista_palavras)
ax = str(lista_palavras[0:ar])
e = str(ax).replace(',', ' ').strip('[]')
e.strip("'")
page = requests.get(html, verify=False, stream=False)
soup = BeautifulSoup(page.content, 'lxml')
cla = soup.find(class_='searchResults')
links = cla.find_all('a')
namess = soup.find_all('a')
ra = (lista_palavras)
# CRIAR A LISTA DE LINKS SITE RFB
listr = []
for link in links:
texto = str(link.get_text()).lower().replace('ã', 'a').replace(
'-', ' ').replace('ç', 'c').split()
# print(len(texto))
url = str(link.get('href'))
# print(len(url))
urls = str(link.get('href')).lower().replace('/', ' ').replace(
'-', ' ').replace('.', ' ').split()
# print(len(urls))
if entrada in texto:
listr.append(url)
for i in range(0, ar):
if lista_palavras[i] in texto:
listr.append(url)
elif lista_palavras[i] in urls:
listr.append(url)
listag = []
rec = 'site:receita.economia.gov.br intext:' + msg + " -filetype:pdf -.pdf"
for urla in search(rec, tld='com.br', lang='pt-br', stop=3, pause=2):
listag.append(urla)
g = int(len(listag))
#print(g)
listago = []
for z in range(0, g):
ur = str(listag[z])
listago.append(ur)
# print(listago)
# print(len(listago))
qo = int(len(listago))
# print(listr)
# print(len(listr))
listaunida = listago + listr
conj = list(set(listaunida))
# print(conj)
# print(len(conj))
# print(type(conj))
# print(p)
# print(len(p))
j = len(conj)
reports2 = []
news_pool.set(reports2, threads_per_source=2)
news_pool.join()
for r in range(0, j):
ia = str(conj[r])
article = Article(ia, language="pt")
try:
article.download()
article.parse()
article.text
article.nlp()
article.summary
except:
pass
reports2.append(str(article.summary).replace('\n', ' '))
# print(len(reports2))
resposta_finalc = set(reports2)
resposta_final = (str(resposta_finalc).replace('\n', ' ').replace(
'[', ' ').replace(']',
' ').replace(',', ' ').replace("'", ' ').replace(
'{', ' ').replace("}", ' '))
f = csv.writer(open('chats.txt', 'a', encoding='utf-8'))
f.writerow([msg + '\n' + resposta_final])
return str(resposta_final)
def unidecode(string):
# last replace is unnecessary, but, for example, in links symbol ' looks awful
return _unidecode(string.lower().replace(' ', '_')).replace("'", "")
def create_contacts_xls(self):
obj = None
apartments = Apartment.objects.all().order_by('entrance__position')
if self.id=='0':
self.id = None
if self.id:
obj = Entrance.objects.get(id=self.id)
apartments = apartments.filter(entrance=obj)
wb = xlwt.Workbook()
font_header = xlwt.Font() # Create the Font
font_header.name = 'Times New Roman'
font_header.height = 14 * 20 # for 16 point
header_style = xlwt.XFStyle()
header_style.font = font_header
horz_style = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;')
horz_style_green = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_green')
horz_style_yellow = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_yellow')
horz_style_left = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;')
horz_style_left_green = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_green')
horz_style_left_yellow = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_yellow')
horz_style_not_paid = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour silver_ega;')
horz_style_no_border = xlwt.easyxf('font: height 320, name Arial, bold true; align: wrap on, vert centre, horiz center;')
sheet = wb.add_sheet(_('Contacts'))
sheet.col(0).width = 256 * 50 # 50 characters
start_row = 1
start_column = 1
sheet.write(start_row, start_column, _('Entrance'), horz_style)
sheet.write(start_row, start_column+1, _('Floor'), horz_style)
sheet.write(start_row, start_column+2, _('Apartment'), horz_style)
sheet.write(start_row, start_column+3, _('Common area'), horz_style)
sheet.write(start_row, start_column+4, _('Monthly fee'), horz_style)
sheet.write(start_row, start_column+5, _('Contact person'), horz_style)
sheet.write(start_row, start_column+6, _('Contact phone'), horz_style)
sheet.write(start_row, start_column+7, _('Contact email'), horz_style)
sheet.write(start_row, start_column+8, _('Number of occupants'), horz_style)
sheet.write(start_row, start_column+9, _('Has pet'), horz_style)
sheet.write(start_row, start_column+10, _('Easypay Id'), horz_style)
sheet.col(0).width = 256 * 7
sheet.col(start_column).width = 256 * 40
sheet.col(start_column+1).width = 256 * 7
sheet.col(start_column+2).width = 256 * 12
sheet.col(start_column+3).width = 256 * 12
sheet.col(start_column+4).width = 256 * 15
sheet.col(start_column+5).width = 256 * 26
sheet.col(start_column+6).width = 256 * 20
sheet.col(start_column+7).width = 256 * 26
sheet.row(start_row).height = 20 * 40
for a in apartments:
start_row+=1
sheet.write(start_row, start_column, a.entrance.title, horz_style)
sheet.write(start_row, start_column+1, a.floor, horz_style)
sheet.write(start_row, start_column+2, a.apartment, horz_style)
sheet.write(start_row, start_column+3, a.common_area if a.common_area else 0, horz_style)
sheet.write(start_row, start_column+4, a.monthly_fee if a.monthly_fee else 0, horz_style)
sheet.write(start_row, start_column+5, a.contact_person, horz_style)
sheet.write(start_row, start_column+6, a.contact_phone, horz_style)
sheet.write(start_row, start_column+7, a.contact_email, horz_style)
sheet.write(start_row, start_column+8, int(a.number_of_occupants) if a.number_of_occupants else 0, horz_style)
sheet.write(start_row, start_column+9, _('Yes') if a.has_pet else _('No'), horz_style)
sheet.write(start_row, start_column+10, a.easypay_id(), horz_style)
sheet.row(start_row).height = 20 * 26
sheet.write(start_row+1, start_column+8, xlwt.Formula("SUM(J3:J%s)" % (start_row+1)), horz_style)
if self.id:
filename = '%s/tmp/Apartments-%s.xls' % (MEDIA_ROOT, slugify(unicode(_unidecode(obj.title))))
else:
filename = '%s/tmp/Apartments-%s.xls' % (MEDIA_ROOT, 'all')
wb.save(filename)
return filename
def create_xls(self):
obj = Entrance.objects.get(id=self.id)
dynamic_costs = DynamicCost.objects.filter(entrance=obj, active=1, task_type__in=TaskType.objects.filter(can_pay=True))
apartments = Apartment.objects.filter(entrance=obj)
wb = xlwt.Workbook()
font_header = xlwt.Font() # Create the Font
font_header.name = 'Times New Roman'
font_header.height = 14 * 20 # for 16 point
header_style = xlwt.XFStyle()
header_style.font = font_header
horz_style = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;')
horz_style_green = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_green')
horz_style_yellow = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_yellow')
horz_style_left = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;')
horz_style_left_green = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_green')
horz_style_left_yellow = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz left;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour light_yellow')
horz_style_not_paid = xlwt.easyxf('font: height 320, name Arial; align: wrap on, vert centre, horiz center;borders: top medium, bottom medium, left medium, right medium;pattern: pattern solid, fore_colour silver_ega;')
horz_style_no_border = xlwt.easyxf('font: height 320, name Arial, bold true; align: wrap on, vert centre, horiz center;')
#############################
# ## PRIHODI ######
#############################
sheet = wb.add_sheet(_('kasa'))
sheet.col(0).width = 256 * 50 # 50 characters
years = Task.objects.filter(resolved_by_admin=True).extra(select={"year": """strftime('%%Y', to_date)"""}).order_by('-year').values_list('year', flat=True).distinct()
start_row = 2
years_cells = {}
for year in years:
start_row += 1
sheet.write(start_row, 0, '%s %s' % (_('kasa'), year), horz_style)
for i in range(1, 13):
sheet.write(start_row, i, '%s.%s' % (i, year), horz_style)
sheet.col(i).width = 256 * 9
sheet.write(start_row, 13, _('Price'), horz_style)
fake_start_row = 0
if int(year) > 2015:
start_row += 1
fake_start_row = start_row + 1
sheet.write(start_row, 0, '%s %s' % (_('kasa'), int(year)-1), horz_style_left_green)
years_cells[year] = {'start_row': start_row}
month_sums = {}
task_types = Task.objects.filter(entrance=obj, resolved_by_admin=True, to_date__year=year, task_type__in=TaskType.objects.filter(can_pay=True)).values_list('task_type_id', flat=True)
task_types = list(set(task_types))
for t in task_types:
start_row += 1
task_type = TaskType.objects.get(id=t)
sheet.write(start_row, 0, task_type.title, horz_style_left_green)
for i in range(1, 13):
# tt = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=True, to_date__year=year, to_date__month=i, apartment_dynamic_cost_id__isnull=True).aggregate(Sum('price'))
tt = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=True, to_date__year=year, to_date__month=i).aggregate(Sum('price'))
tt1 = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=False, to_date__year=year, to_date__month=i).aggregate(Sum('partial_paid_total'))
price_total = 0
if tt['price__sum'] is None:
tt['price__sum'] = 0
if tt1['partial_paid_total__sum'] is None:
tt1['partial_paid_total__sum'] = 0
price_total = price_total + tt['price__sum'] + tt1['partial_paid_total__sum']
sheet.write(start_row, i, price_total, horz_style_green)
if i not in month_sums:
month_sums[i] = 0
month_sums[i] += price_total
sheet.col(i).width = 256 * 12
sheet.row(start_row).height = 20 * 16
rng = (start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1)
sheet.write(start_row, 13, xlwt.Formula("SUM(B%s+C%s+D%s+E%s+F%s+G%s+H%s+I%s+J%s+K%s+L%s+M%s)" % rng), horz_style_green)
start_row += 1
sheet.write(start_row, 0, _('Total income'), horz_style_left_green)
for i in range(1, 13):
if fake_start_row != 0 and i == 1:
sheet.write(start_row, i, xlwt.Formula("SUM(B%s:B%s)" % (fake_start_row, start_row)), horz_style_green)
else:
sheet.write(start_row, i, month_sums.get(i), horz_style_green)
rng = (start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1)
sheet.write(start_row, 13, xlwt.Formula("SUM(B%s+C%s+D%s+E%s+F%s+G%s+H%s+I%s+J%s+K%s+L%s+M%s)" % rng), horz_style_green)
total_income_cell = "N%s" % (int(start_row) + 1)
# ############################ RAZHODI
month_sums = {}
task_types = Task.objects.filter(entrance=obj, resolved_by_admin=True, to_date__year=year, task_type__in=TaskType.objects.filter(can_pay=False)).values_list('task_type_id', flat=True)
task_types = list(set(task_types))
for t in task_types:
start_row += 1
task_type = TaskType.objects.get(id=t)
sheet.write(start_row, 0, task_type.title, horz_style_left)
for i in range(1, 13):
# tt = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=True, to_date__year=year, to_date__month=i, apartment_dynamic_cost_id__isnull=True).aggregate(Sum('price'))
tt = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=True, to_date__year=year, to_date__month=i).aggregate(Sum('price'))
tt1 = Task.objects.filter(task_type=task_type, entrance=obj, resolved_by_admin=False, to_date__year=year, to_date__month=i).aggregate(Sum('partial_paid_total'))
price_total = 0
if tt['price__sum'] is None:
tt['price__sum'] = 0
if tt1['partial_paid_total__sum'] is None:
tt1['partial_paid_total__sum'] = 0
price_total = price_total + tt['price__sum'] + tt1['partial_paid_total__sum']
sheet.write(start_row, i, price_total, horz_style)
if i not in month_sums:
month_sums[i] = 0
month_sums[i] += price_total
sheet.col(i).width = 256 * 12
sheet.row(start_row).height = 20 * 16
rng = (start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1)
sheet.write(start_row, 13, xlwt.Formula("SUM(B%s+C%s+D%s+E%s+F%s+G%s+H%s+I%s+J%s+K%s+L%s+M%s)" % rng), horz_style)
start_row += 1
sheet.write(start_row, 0, _('Total outcome'), horz_style_left_yellow)
for i in range(1, 13):
sheet.write(start_row, i, month_sums.get(i), horz_style_yellow)
rng = (start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1, start_row + 1)
sheet.write(start_row, 13, xlwt.Formula("SUM(B%s+C%s+D%s+E%s+F%s+G%s+H%s+I%s+J%s+K%s+L%s+M%s)" % rng), horz_style_yellow)
total_outcome_cell = "N%s" % (int(start_row) + 1)
start_row += 1
sheet.write(start_row, 12, _('kasa'), horz_style_left)
sheet.write(start_row, 13, xlwt.Formula("%s-%s" % (total_income_cell, total_outcome_cell)), horz_style)
years_cells[year]['value'] = "N%s" % (start_row + 1)
start_row += 3
# STARI KASI
for k in years_cells:
k = int(k)
if k > 2015:
sheet.write(years_cells[str(k)]['start_row'], 1, xlwt.Formula(years_cells[str(k-1)]['value']), horz_style_green)
sheet.write(years_cells[str(k)]['start_row'], 13, xlwt.Formula(years_cells[str(k-1)]['value']), horz_style_green)
for i in range(2, 13):
sheet.write(years_cells[str(k)]['start_row'], i, 0, horz_style_green)
#############################
# ## TABOVE ######
#############################
years = Task.objects.filter(task_type=TaskType.objects.get(default_fee=True), entrance=obj).extra(select={"year": """strftime('%%Y', to_date)"""}).order_by('-year').values_list('year').distinct()
for year in years:
year = year[0]
tasks = Task.objects.filter(task_type=TaskType.objects.get(default_fee=True), to_date__year=year, entrance=obj)
sheet = wb.add_sheet('%s' % year)
sheet.write(0, 1, '%s: %s (%s)' % (_('Tax at address'), obj.title, year), header_style) # sheet.write(top_row, left_column, 'Long Cell')
sheet.merge(0, 0, 1, 17) # sheet.merge(top_row, bottom_row, left_column, right_column)
sheet.row(0).height = 24 * 16
start_row = 2
sheet.write(start_row, 1, _('Floor'), horz_style)
sheet.write(start_row, 2, _('Apartment'), horz_style)
sheet.write(start_row, 3, _('Number of occupants'), horz_style)
sheet.write(start_row, 4, _('Contact person'), horz_style)
sheet.write(start_row, 5, _('Monthly payment'), horz_style)
sheet.write(start_row, 6, _('Due payment'), horz_style)
sheet.write(start_row, 19, _('Collected'), horz_style)
sheet.row(start_row).height = 20 * 16
sheet.col(1).width = 256 * 6 # 7 characters
sheet.col(2).width = 256 * 12 # 14 characters
sheet.col(3).width = 256 * 14 # 18 characters
sheet.col(4).width = 256 * 22 # 24 characters
sheet.col(5).width = 256 * 10 # 12 characters
sheet.col(6).width = 256 * 12 # 12 characters
sheet.col(19).width = 256 * 10 # 12 characters
for i in range(1, 13):
sheet.write(start_row, 6 + i, '%s.%s' % (i, year), horz_style)
sheet.col(5 + i).width = 256 * 9
cells = {}
month_payments = {}
for a in apartments:
start_row += 1
sheet.row(start_row).height = 20 * 16
sheet.write(start_row, 1, a.floor, horz_style)
sheet.write(start_row, 2, a.apartment, horz_style)
sheet.write(start_row, 3, a.number_of_occupants, horz_style)
sheet.write(start_row, 4, a.contact_person, horz_style)
sheet.write(start_row, 5, a.monthly_fee, horz_style)
fees = tasks.filter(apartment=a)
due_payment = 0
collected = 0
cells[start_row] = {}
for f in fees:
cells[start_row][f.to_date.month] = f.price
try:
sheet.write(start_row, 6 + f.to_date.month, f.price or 0, horz_style if f.resolved_by_admin else horz_style_not_paid)
except:
pass
if f.resolved_by_admin:
if f.to_date.month not in month_payments:
month_payments[f.to_date.month] = 0
month_payments[f.to_date.month] += f.price
collected += f.price
else:
if f.price:
due_payment += f.price
sheet.write(start_row, 6, due_payment or 0, horz_style)
sheet.write(start_row, 19, collected or 0, horz_style)
start_row += 1
sheet.write(start_row, 6, _('Total'), horz_style)
for i in range(1, 13):
sheet.write(start_row, 6 + i, month_payments.get(i, 0), horz_style)
for c in cells:
for i in range(1, 13):
val = cells[c].get(i)
if not val:
try:
sheet.write(c, 6 + i, val, horz_style)
except:
pass
for d in dynamic_costs:
tasks = Task.objects.filter(apartment_dynamic_cost_id__in=ApartmentDynamicCost.objects.filter(cost=d).values_list('id', flat=True))
if tasks:
try:
sheet = wb.add_sheet(d.title[:30])
except:
sheet = wb.add_sheet('%s-%s' % (d.title[:27], d.id))
total_price = 0
total_partial_price = 0
sheet.write(0, 1, obj.title.strip(), header_style) # sheet.write(top_row, left_column, 'Long Cell')
sheet.merge(0, 0, 1, 7) # sheet.merge(top_row, bottom_row, left_column, right_column)
sheet.row(0).height = 24 * 16
sheet.write(1, 1, d.title.strip(), header_style) # sheet.write(top_row, left_column, 'Long Cell')
sheet.merge(1, 1, 1, 7) # sheet.merge(top_row, bottom_row, left_column, right_column)
sheet.row(1).height = 24 * 16
info = strip_tags(d.common_information).strip()
if info:
sheet.write(2, 1, strip_tags(d.common_information).strip(), header_style) # sheet.write(top_row, left_column, 'Long Cell')
sheet.merge(2, 2, 1, 7) # sheet.merge(top_row, bottom_row, left_column, right_column)
sheet.row(2).height = 24 * 16
start_row = 3
sheet.write(start_row, 1, _('Floor'), horz_style)
sheet.write(start_row, 2, _('Apartment'), horz_style)
sheet.write(start_row, 3, _('Number of occupants'), horz_style)
sheet.write(start_row, 4, _('Contact person'), horz_style)
sheet.write(start_row, 5, _('Price'), horz_style)
sheet.write(start_row, 6, _('Sum paid'), horz_style)
sheet.col(1).width = 256 * 7 # 7 characters
sheet.col(2).width = 256 * 16 # 16 characters
sheet.col(3).width = 256 * 18 # 18 characters
sheet.col(4).width = 256 * 24 # 24 characters
sheet.col(5).width = 256 * 12 # 12 characters
sheet.col(6).width = 256 * 12 # 12 characters
sheet.row(start_row).height = 20 * 16
sheet.write(start_row, 7, _('For Payment'), horz_style)
sheet.col(7).width = 256 * 25 # 25 characters
for t in tasks:
start_row += 1
sheet.write(start_row, 1, t.apartment.floor, horz_style)
sheet.write(start_row, 2, t.apartment.apartment, horz_style)
sheet.write(start_row, 3, t.apartment.number_of_occupants, horz_style)
sheet.write(start_row, 4, t.apartment.contact_person, horz_style)
sheet.write(start_row, 5, t.price or 0, horz_style)
if t.price:
total_price += t.price
if t.resolved_by_admin:
if t.price:
total_partial_price += t.price
sheet.write(start_row, 6, t.price, horz_style)
else:
if t.partial_paid_total:
total_partial_price += t.partial_paid_total
sheet.write(start_row, 6, t.partial_paid_total or 0, horz_style)
sheet.write(start_row, 7, xlwt.Formula("SUM(F%s-G%s)" % (start_row+1, start_row+1)), horz_style)
sheet.row(start_row).height = 20 * 16
start_row += 1
sheet.write(start_row, 5, total_price or 0, horz_style_no_border)
sheet.write(start_row, 6, total_partial_price or 0, horz_style_no_border)
sheet.write(start_row, 7, xlwt.Formula("SUM(F%s-G%s)" % (start_row+1, start_row+1)), horz_style_no_border)
administrative_tasks = Task.objects.filter(task_type__in=TaskType.objects.filter(is_administrative=True), entrance=obj).order_by('-date')
if administrative_tasks:
start_row = 2
sheet = wb.add_sheet('%s' % _('Administrative Tasks'))
sheet.write(start_row, 1, _('Month'), horz_style)
sheet.write(start_row, 2, _('User'), horz_style)
sheet.write(start_row, 3, _('task'), horz_style)
sheet.write(start_row, 4, _('Common information'), horz_style)
sheet.col(1).width = 256 * 7 # 7 characters
sheet.col(2).width = 256 * 24 # 24 characters
sheet.col(3).width = 256 * 60 # 300 characters
sheet.col(4).width = 256 * 60 # 300 characters
sheet.row(start_row).height = 20 * 16
for a in administrative_tasks:
start_row+=1
sheet.write(start_row, 1, a.get_date(), horz_style)
sheet.write(start_row, 2, a.assignee.get_full_name() if a.assignee_id else '-', horz_style)
sheet.write(start_row, 3, a.title, horz_style)
sheet.write(start_row, 4, strip_tags(a.common_information) if a.common_information else '', horz_style)
filename = '%s/tmp/%s-%s.xls' % (MEDIA_ROOT, slugify(unicode(_unidecode(obj.title))), obj.id)
wb.save(filename)
return filename
