def data(self, index, role=None): """return a QVariant saying if exists a payment at index.(row|column)""" if not index.isValid() or role not in (Qt.DisplayRole, Qt.CheckStateRole): return QVariant() #self.update_db_content() # find the month from the row number month_year = QDate().fromString( self.headerData(index.row(), Qt.Vertical, role).toString(), 'MMMM yyyy') month = month_year.month() year = month_year.year() # find the client from the column number header_infos = self.headerData(index.column(), Qt.Horizontal, role).toString().split('\n') client = header_infos[0] machine = header_infos[1] selldate = QDate.fromString(header_infos[2], 'd MMMM yyyy') deltamonth = int(header_infos[3][5:-5]) # [len('Ogni '):-len(' mesi')] anticiped = header_infos[4][10:-6] == 'anti' # 'Pagamento ':-'cipato' query = QSqlQuery( 'SELECT expected_datepayd, effective_datepayd FROM ' 'payments WHERE clients_client = :client AND clients_machine = ' ':machine AND clients_selldate = :selldate AND ' 'expected_datepayd BETWEEN :datebefore AND :dateafter', self._db) query.bindValue(':client', QVariant(client)) query.bindValue(':machine', QVariant(machine)) query.bindValue(':selldate', QVariant(selldate)) # primo giorno del mese d = QDate(year, month, 1) query.bindValue(':datebefore', QVariant(d)) # ultimo giorno del mese query.bindValue(':dateafter', QVariant(d.addMonths(1).addDays(-1))) if not query.exec_(): raise StandardError('SYNTAX ERROR') if not query.first(): return QVariant() expected_datepayd = query.value(0).toDate() payed = not query.isNull(1) effective_datepayd = query.value(1).toDate() if role == Qt.CheckStateRole: return QVariant(Qt.Checked if payed else Qt.Unchecked) else: # DisplayRole date = effective_datepayd if payed else expected_datepayd return QVariant(date.toString('d MMMM yyyy'))
def export_streets(self): """ Exports all streets to CSV :return: """ record_num = 0 language = self.alt_lang sql_template = """ SELECT tblSTREET.*, tlkpLOCALITY.name AS Locality, tlkpLOCALITY.alt_name AS Locality_Alt, tlkpTOWN.name AS Town, tlkpTOWN.alt_name AS Town_Alt, tlkpCOUNTY.name AS County, tlkpCOUNTY.alt_name AS County_Alt FROM ((tblSTREET INNER JOIN tlkpTOWN ON tblSTREET.town_ref = tlkpTOWN.town_ref) INNER JOIN tlkpLOCALITY ON tblSTREET.loc_ref = tlkpLOCALITY.loc_ref) INNER JOIN tlkpCOUNTY ON tblSTREET.county_ref = tlkpCOUNTY.county_ref WHERE tblSTREET.street_ref_type < 5 {extra_where_clauses} ;""" if self.inc_sts: extra_where_clauses = "AND (tblSTREET.currency_flag = 0 OR tblSTREET.Closure_date > 0)" else: extra_where_clauses = "AND tblSTREET.currency_flag = 0" sql = sql_template.format(extra_where_clauses=extra_where_clauses) query = QSqlQuery(self.db) query.exec_(sql) rec = query.record() # This gets all the right indexes for the query results, more flexibility for referencing each result aval = [rec.indexOf("USRN"), rec.indexOf("Version_No"), rec.indexOf("Street_ref_type"), rec.indexOf("description"), rec.indexOf("description_alt"), rec.indexOf("Entry_Date"), rec.indexOf("Update_Date"), rec.indexOf("Start_Date"), rec.indexOf("Authority"), rec.indexOf("Closure_Date"), rec.indexOf("start_xref"), rec.indexOf("start_yref"), rec.indexOf("end_xref"), rec.indexOf("end_yref"), rec.indexOf("tolerance"), rec.indexOf("street_state"), rec.indexOf("state_date"), rec.indexOf("street_class"), rec.indexOf("Locality"), rec.indexOf("Town"), rec.indexOf("County"), rec.indexOf("locality_alt"), rec.indexOf("town_alt"), rec.indexOf("county_alt") ] while query.next(): self.line_count += 1 record_num += 1 if self.version > 6: if query.value(aval[15]) == "" or query.value(aval[15]) == 0: query.value(aval[16]) == "" change = "I" if (not query.isNull(aval[9])) and query.value(aval[9]) > 0: change = "D" opts = { 6: [11, query.value(aval[0]), change, query.value(aval[2]), query.value(aval[3]), query.value(aval[18]), query.value(aval[19]), query.value(aval[20]), "", "", "", "", query.value(aval[8]), query.value(aval[1]), self.format_date(query.value(aval[6])), self.format_date(query.value(aval[9])), query.value(aval[10]), query.value(aval[11]), query.value(aval[12]), query.value(aval[13]), query.value(aval[14]), 1, "", self.format_date(query.value(aval[6])), self.line_count], 7: dict(street=[11, change, self.line_count, query.value(aval[0]), query.value(aval[2]), query.value(aval[8]), query.value(aval[15]), self.format_date(query.value(aval[16])), "", query.value(aval[17]), query.value(aval[1]), self.format_date(query.value(aval[5])), self.format_date(query.value(aval[6])), self.format_date(query.value(aval[7])), self.format_date(query.value(aval[9])), query.value(aval[10]), query.value(aval[11]), query.value(aval[12]), query.value(aval[13]), query.value(aval[14])], streetdesc=[15, change, self.line_count + 1, query.value(aval[0]), query.value(aval[3]), query.value(aval[18]), query.value(aval[19]), query.value(aval[20]), "ENG"], streetalt=[15, change, self.line_count + 2, query.value(aval[0]), query.value(aval[4]), query.value(aval[21]), query.value(aval[22]), query.value(aval[23]), language]), 75: dict(street=[11, change, self.line_count, query.value(aval[0]), query.value(aval[2]), query.value(aval[8]), query.value(aval[15]), self.format_date(query.value(aval[16])), query.value(aval[17]), self.format_date(query.value(aval[5])), self.format_date(query.value(aval[6])), self.format_date(query.value(aval[7])), self.format_date(query.value(aval[9])), query.value(aval[10]), query.value(aval[11]), query.value(aval[12]), query.value(aval[13])], streetdesc=[15, change, self.line_count + 1, query.value(aval[0]), query.value(aval[3]), query.value(aval[18]), query.value(aval[19]), query.value(aval[20]), "ENG"], streetalt=[15, change, self.line_count + 2, query.value(aval[0]), query.value(aval[4]), query.value(aval[21]), query.value(aval[22]), query.value(aval[23]), language]) } if self.version is 6: streets = self.clean_street(opts.get(self.version), query.value(aval[2])) streets = format_floats_and_strings(streets) self.csv.writerow(streets) self.tmp_streets.append(streets) self.xref_sts.append(long(streets[1])) else: streets_first = self.set_state(opts.get(self.version)['street']) streets_first = format_floats_and_strings(streets_first) self.csv.writerow(streets_first) self.tmp_streets.append(streets_first) self.xref_sts.append(long(streets_first[3])) streets = self.clean_street(opts.get(self.version)['streetdesc'], query.value(aval[2])) self.line_count += 1 streets = format_floats_and_strings(streets) self.csv.writerow(streets) self.tmp_streets.append(streets) self.xref_sts.append(streets[3]) if language != "ENG" and query.value(aval[4]) != "": streets_alt = self.clean_street(opts.get(self.version)['streetalt'], query.value(aval[2])) self.line_count += 1 streets_alt = format_floats_and_strings(streets_alt) self.csv.writerow(streets_alt) self.tmp_streets.append(streets_alt) self.xref_sts.append(streets_alt[3])