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