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