class DeviceDetection(Enum):
SHOWS_LEAK = FormCheckBox("Device is currently displaying a leak.")
ALERT_PRESENT = FormCheckBox("Device is showing an alert.")
AUDIO_AT_DEVICE = FormCheckBox("Audio at Device")
VISUAL_AT_DEVICE = FormCheckBox("Visual at Device")
PUSH_NOTIFY = FormCheckBox("Push Notification")
IN_APP_ALERT = FormCheckBox("In-App-Alert")
TEXT_MSG = FormCheckBox("Text Message")
EMAIL = FormCheckBox("Email")
AUTO_CALL = FormCheckBox("Auto Call")
CALL_BY_RETAILER = FormCheckBox("Called by Retailer/Mfr")
OTHER = FormEntry()
LEAK_VOL = FormEntry(double_only=True)
UNIT = FormEntry()
LEAK_LOC = FormTextBox()
LEAK_DISCOVERED = FormCheckBox(
"Leak discovered when device was installed?")
LEAK_DIS_VOL = FormEntry(double_only=True)
LEAK_DIS_UNIT = FormEntry()
LEAK_DIS_LOC = FormTextBox()
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class HomeInfo(Enum):
PARCEL = FormEntry()
REDFIN_VAL = FormEntry(double_only=True)
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class Economic(Enum):
POC = FormEntry()
HOME_VAL = FormEntry(double_only=True)
INCOME = FormEntry(double_only=True)
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class Device(Enum):
NAME = FormEntry()
MODEL = FormEntry()
SERIAL = FormEntry()
LOCATION = FormEntry()
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
def __init__(self, title, labels=[], data=[], table_name="", field_name="", parent=None):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
y_pos = np.arange(len(data))
self.ax.barh(y_pos, data, align="center", color='lightskyblue', alpha=0.5)
self.ax.set_xlabel(labels[len(labels)-1])
#self.ax.set_ylabel(labels[1])
rects = self.ax.patches
low_rect = rects[0]
high_rect = rects[len(rects)-1]
width = low_rect.get_width()
self.ax.text(low_rect.get_x()+width, low_rect.get_y()+1, "Lowest: $"+str(data[0]))
width = high_rect.get_width()
self.ax.text(high_rect.get_x(), high_rect.get_y()+1, "Highest: $"+str(data[len(data)-1]))
self.setCentralWidget(self._canvas)
class PrevLeak(Enum):
INFLUENCE = FormCheckBox("Previous Leak Influenced in purchasing Device")
COST_TO_REPAIR = FormEntry(double_only=True)
LOC = FormTextBox()
VOL = FormEntry(double_only=True)
UNIT = FormEntry()
CLAIM_FILED = FormCheckBox("Claim Filed")
CLAIM_AMT = FormEntry(double_only=True)
DEDUCT = FormEntry(double_only=True)
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
def __init__(self, title, labels=[], data=[], table_name="", field_name="", x_label=None, y_label=None, parent=None, already_sorted=False):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self._x_label = x_label
self._y_label = y_label
self._already_sorted = already_sorted
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.ax.bar(self._labels, self._data)
if self._x_label != None:
self.ax.set_xlabel(self._x_label)
if self._y_label != None:
self.ax.set_ylabel(self._y_label)
self.setCentralWidget(self._canvas)
class Installation(Enum):
SELF_INSTALLED = FormCheckBox("Self Installed?", reverse=True)
OTHER_INSTALLER = FormEntry()
INSTALL_COST = FormEntry(double_only=True)
SUBSCRIPTION = FormCheckBox("Subscription?")
SUBSCRIPTION_COST = FormEntry(double_only=True)
FUNCTIONAL = FormCheckBox("Device is Functional?")
SETUP_ON_PHONE = FormCheckBox("Device is setup on the phone.")
EASE_OF_PHYS_INSTALL = FormSpinBox()
EASE_OF_APP_INSTALL = FormSpinBox()
EASE_OF_APP_USE = FormSpinBox()
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class People(Enum):
PEOPLE_COUNT = FormSpinBox(default_value=1, min_value=1, max_value=99)
AGES = FormEntry(comma_sort=True)
HIGHEST_EDU = FormCombo(default_values=[
"-- None Selected --", "High School or (G.E.D)", "Technical Degree",
"Bachelor's Degree", "Graduate School"
])
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class Client(Enum):
ADDRESS = FormEntry()
DATE = DateEntry()
FIRST_NAME = FormEntry()
LAST_NAME = FormEntry()
OTHER_NAME = FormCheckBox("Different person than above met onsite?")
OTHER_FIRST_NAME = FormEntry()
OTHER_LAST_NAME = FormEntry()
SNWA_REP_FIRST_NAME = FormEntry()
SNWA_REP_LAST_NAME = FormEntry()
AP_PHASE_ID = FormEntry()
@property
def data_type(self):
return self.value.getType()
class RepLeak(Enum):
LEAK_SHOWING = FormCheckBox("Leak Showing at Meter")
VOL = FormEntry(double_only=True)
UNIT = FormCombo(default_values=["gpm", "cfm"])
LOCS = FormTextBox()
MET_DEV_AGREE = FormCheckBox("Meter and Device Show the Same Leak Amount")
MET_OR_DEV_HIGHER = FormCombo(default_values=["Meter", "Device"])
CUS_INFORM = FormCheckBox("Customer Informed of Leak")
REA_CUS_NOT_INFORM = FormTextBox()
RESOLUTION = FormTextBox()
AP_PHASE_ID = Client.AP_PHASE_ID.value
@property
def data_type(self):
return self.value.getType()
class PlotBarWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", x_label=None, y_label=None, parent=None, already_sorted=False):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self._x_label = x_label
self._y_label = y_label
self._already_sorted = already_sorted
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.ax.bar(self._labels, self._data)
if self._x_label != None:
self.ax.set_xlabel(self._x_label)
if self._y_label != None:
self.ax.set_ylabel(self._y_label)
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
field_name = self._field_name.text()
if file_name != "":
title = self.ax.title.get_text()
last_row = len(self._data)+2
labels = sorted(self._labels)
if self._already_sorted:
sorted_data = {label:[self._data[i]] for i, label in enumerate(self._labels)}
else:
data = {label:[self._data[i]] for i, label in enumerate(self._labels)}
sorted_data = {label:data[label] for label in labels}
df = pd.DataFrame(data=sorted_data)
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name=field_name, index=False)
workbook = writer.book
worksheet = writer.sheets[field_name]
chart = workbook.add_chart({"type": 'column'})
chart.set_title({"name": title})
if self._x_label != None:
chart.set_x_axis({'name': self._x_label})
if self._y_label != None:
chart.set_y_axis({'name': self._y_label})
chart.add_series({"categories": "={fn}!$A$1:${lc}$1".format(lc=self._last_column, fn=field_name),
"values": "={fn}!$A$2:${lc}$2".format(lc=self._last_column, fn=field_name),
"fill": {'color': '#0000CC'}})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()
class PlotHBarWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", parent=None):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
y_pos = np.arange(len(data))
self.ax.barh(y_pos, data, align="center", color='lightskyblue', alpha=0.5)
self.ax.set_xlabel(labels[len(labels)-1])
#self.ax.set_ylabel(labels[1])
rects = self.ax.patches
low_rect = rects[0]
high_rect = rects[len(rects)-1]
width = low_rect.get_width()
self.ax.text(low_rect.get_x()+width, low_rect.get_y()+1, "Lowest: $"+str(data[0]))
width = high_rect.get_width()
self.ax.text(high_rect.get_x(), high_rect.get_y()+1, "Highest: $"+str(data[len(data)-1]))
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
field_name = self._field_name.text()
if file_name != "":
title = self.ax.title.get_text()
last_row = len(self._data)+2
col_1 = ["" for data in self._data]
col_2 = col_1.copy()
col_1[0] = self._data[0]
col_2[0] = self._data[len(self._data)-1]
data = {
self._labels[0]: self._data,
self._labels[1]: col_1,
self._labels[2]: col_2
}
df = pd.DataFrame(data=data)
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name=field_name, index=False)
workbook = writer.book
worksheet = writer.sheets[field_name]
chart = workbook.add_chart({"type": 'bar'})
chart.set_title({"name": title})
chart.set_x_axis({'name': self._labels[0]})
chart.add_series({"values": "={fn}!$A$2:$A${lr}".format(lr=last_row, fn=field_name), 'fill': {'color': '#0000CC'}, 'border': {'color': '#0000CC'}})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()
def __init__(self, tables, database, parent=None):
super().__init__(parent)
try:
self._tables = tables
self._database = database
self.setWindowTitle("Search for Records")
self._main_widget = QWidget(self)
self._bas_search_tab = QWidget(self)
self._bas_search_lay = QVBoxLayout(self._bas_search_tab)
self._adv_search_tab = QWidget(self)
self._adv_search_lay = QVBoxLayout(self._adv_search_tab)
self._search_tabs = QTabWidget(self)
self._search_tabs.addTab(self._bas_search_tab, "Search")
self._search_tabs.addTab(self._adv_search_tab, "Advance Search")
layout = QVBoxLayout(self._main_widget)
layout.setContentsMargins(0,0,0,0)
layout.addWidget(self._search_tabs)
# ----------------------------------------------------------------------------------------
# ADVANCE SEARCH
sql_form_box = FormGroupBox("Enter SQL Command:")
self._sql_cmd_box = SQLCommandBox()
self._highlighter = Highlighter(self._sql_cmd_box.document())
self._field_names = {}
self._field_names = {table.name:getFieldNames(table.name) for table in Tables}
for table in ExclusiveDbTables:
self._field_names[table.name] = table.fields
self._adv_search_button = QPushButton("Run SQL")
self._adv_search_button.clicked.connect(self.sqlSearch)
search_button_row = QWidget(self)
search_button_lay = QHBoxLayout(search_button_row)
search_button_lay.setContentsMargins(0,0,0,0)
search_button_lay.addWidget(HorizontalFiller())
search_button_lay.addWidget(self._adv_search_button)
sql_results_box = FormGroupBox("Results:")
self._adv_table_label = QLabel("Number of Results:")
self._adv_results_table = DataTable(use_max_height=False, stretch=False)
self._export_results_button = QPushButton("Export Table (CSV)")
self._export_results_button.clicked.connect(self.exportTable)
self._export_results_button.setDisabled(True)
res_row = QWidget(self)
res_lay = QHBoxLayout(res_row)
res_lay.setContentsMargins(0,0,0,0)
res_lay.addWidget(HorizontalFiller())
res_lay.addWidget(self._export_results_button)
sql_form_box.frame_layout.addRow(self._sql_cmd_box)
sql_form_box.frame_layout.addRow(search_button_row)
self._adv_search_lay.addWidget(sql_form_box)
sql_results_box.frame_layout.addRow(self._adv_table_label)
sql_results_box.frame_layout.addRow(self._adv_results_table)
sql_results_box.frame_layout.addRow(res_row)
self._adv_search_lay.addWidget(sql_results_box)
# ----------------------------------------------------------------------------------------
# BASIC SEARCH
form_box = FormGroupBox("Search for Record by Value", self)
results_box = FormGroupBox("Results", self)
self._table_combo = FormCombo(self)
table_names = [table.name for table in self._tables]
table_names.extend([table.name for table in ExclusiveDbTables])
self._table_combo.addItems(table_names)
self._field_combo = FormCombo(self)
self._condition_combo = FormCombo(self)
self._condition_combo.setMinimumWidth(150)
self._value_entry = ExtendedComboBox(self)
self._value_entry.setMinimumWidth(250)
self._range_entry = FormEntry(self)
self._range_entry.setMinimumWidth(250)
self._table_label = QLabel("Number of Results:")
self._result_table = DataTable(use_max_height=False, stretch=False, parent=self)
self._search_button = QPushButton(" Search ")
self._search_button.clicked.connect(self.searchForData)
self._export_button = QPushButton("Export CSV")
self._export_button.clicked.connect(self.exportCSV)
self._export_button.setEnabled(False)
self._value_type = None
form_box.frame_layout.addRow("Table:", self._table_combo)
condition_row = QWidget(self)
condition_lay = QGridLayout(condition_row)
condition_lay.addWidget(QLabel("Field:"), 0,0)
condition_lay.addWidget(QLabel("Condition:"), 0,1)
condition_lay.addWidget(QLabel("Value:"), 0,2)
condition_lay.addWidget(QLabel("Value 2 (Range Between Value 1 and Value 2):"), 0, 3)
condition_lay.addWidget(self._field_combo, 1,0)
condition_lay.addWidget(self._condition_combo, 1,1)
condition_lay.addWidget(self._value_entry, 1,2)
condition_lay.addWidget(self._range_entry, 1,3)
form_box.frame_layout.addRow(condition_row)
search_row = QWidget(self)
search_lay = QHBoxLayout(search_row)
search_lay.addWidget(HorizontalFiller(self))
search_lay.addWidget(self._search_button)
form_box.frame_layout.addRow(search_row)
search_lay.setContentsMargins(0,0,0,0)
form_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.addRow(self._table_label)
results_box.frame_layout.addRow(self._result_table)
export_row = QWidget(self)
export_lay = QHBoxLayout(export_row)
export_lay.addWidget(HorizontalFiller(self))
export_lay.addWidget(self._export_button)
results_box.frame_layout.addRow(export_row)
export_lay.setContentsMargins(0,0,0,0)
self._current_table = None
self._setTableFieldsCombo()
self._table_combo.currentTextChanged.connect(self._setTableFieldsCombo)
self._setConditionsCombo()
self._field_combo.currentTextChanged.connect(self._setConditionsCombo)
self._conditionChange()
self._condition_combo.currentTextChanged.connect(self._conditionChange)
self._bas_search_lay.addWidget(form_box)
self._bas_search_lay.addWidget(results_box)
self.setCentralWidget(self._main_widget)
except Exception as e:
print("Window failed", e)
class SearchWindow(QMainWindow):
def __init__(self, tables, database, parent=None):
super().__init__(parent)
try:
self._tables = tables
self._database = database
self.setWindowTitle("Search for Records")
self._main_widget = QWidget(self)
self._bas_search_tab = QWidget(self)
self._bas_search_lay = QVBoxLayout(self._bas_search_tab)
self._adv_search_tab = QWidget(self)
self._adv_search_lay = QVBoxLayout(self._adv_search_tab)
self._search_tabs = QTabWidget(self)
self._search_tabs.addTab(self._bas_search_tab, "Search")
self._search_tabs.addTab(self._adv_search_tab, "Advance Search")
layout = QVBoxLayout(self._main_widget)
layout.setContentsMargins(0,0,0,0)
layout.addWidget(self._search_tabs)
# ----------------------------------------------------------------------------------------
# ADVANCE SEARCH
sql_form_box = FormGroupBox("Enter SQL Command:")
self._sql_cmd_box = SQLCommandBox()
self._highlighter = Highlighter(self._sql_cmd_box.document())
self._field_names = {}
self._field_names = {table.name:getFieldNames(table.name) for table in Tables}
for table in ExclusiveDbTables:
self._field_names[table.name] = table.fields
self._adv_search_button = QPushButton("Run SQL")
self._adv_search_button.clicked.connect(self.sqlSearch)
search_button_row = QWidget(self)
search_button_lay = QHBoxLayout(search_button_row)
search_button_lay.setContentsMargins(0,0,0,0)
search_button_lay.addWidget(HorizontalFiller())
search_button_lay.addWidget(self._adv_search_button)
sql_results_box = FormGroupBox("Results:")
self._adv_table_label = QLabel("Number of Results:")
self._adv_results_table = DataTable(use_max_height=False, stretch=False)
self._export_results_button = QPushButton("Export Table (CSV)")
self._export_results_button.clicked.connect(self.exportTable)
self._export_results_button.setDisabled(True)
res_row = QWidget(self)
res_lay = QHBoxLayout(res_row)
res_lay.setContentsMargins(0,0,0,0)
res_lay.addWidget(HorizontalFiller())
res_lay.addWidget(self._export_results_button)
sql_form_box.frame_layout.addRow(self._sql_cmd_box)
sql_form_box.frame_layout.addRow(search_button_row)
self._adv_search_lay.addWidget(sql_form_box)
sql_results_box.frame_layout.addRow(self._adv_table_label)
sql_results_box.frame_layout.addRow(self._adv_results_table)
sql_results_box.frame_layout.addRow(res_row)
self._adv_search_lay.addWidget(sql_results_box)
# ----------------------------------------------------------------------------------------
# BASIC SEARCH
form_box = FormGroupBox("Search for Record by Value", self)
results_box = FormGroupBox("Results", self)
self._table_combo = FormCombo(self)
table_names = [table.name for table in self._tables]
table_names.extend([table.name for table in ExclusiveDbTables])
self._table_combo.addItems(table_names)
self._field_combo = FormCombo(self)
self._condition_combo = FormCombo(self)
self._condition_combo.setMinimumWidth(150)
self._value_entry = ExtendedComboBox(self)
self._value_entry.setMinimumWidth(250)
self._range_entry = FormEntry(self)
self._range_entry.setMinimumWidth(250)
self._table_label = QLabel("Number of Results:")
self._result_table = DataTable(use_max_height=False, stretch=False, parent=self)
self._search_button = QPushButton(" Search ")
self._search_button.clicked.connect(self.searchForData)
self._export_button = QPushButton("Export CSV")
self._export_button.clicked.connect(self.exportCSV)
self._export_button.setEnabled(False)
self._value_type = None
form_box.frame_layout.addRow("Table:", self._table_combo)
condition_row = QWidget(self)
condition_lay = QGridLayout(condition_row)
condition_lay.addWidget(QLabel("Field:"), 0,0)
condition_lay.addWidget(QLabel("Condition:"), 0,1)
condition_lay.addWidget(QLabel("Value:"), 0,2)
condition_lay.addWidget(QLabel("Value 2 (Range Between Value 1 and Value 2):"), 0, 3)
condition_lay.addWidget(self._field_combo, 1,0)
condition_lay.addWidget(self._condition_combo, 1,1)
condition_lay.addWidget(self._value_entry, 1,2)
condition_lay.addWidget(self._range_entry, 1,3)
form_box.frame_layout.addRow(condition_row)
search_row = QWidget(self)
search_lay = QHBoxLayout(search_row)
search_lay.addWidget(HorizontalFiller(self))
search_lay.addWidget(self._search_button)
form_box.frame_layout.addRow(search_row)
search_lay.setContentsMargins(0,0,0,0)
form_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.setContentsMargins(0,0,0,0)
results_box.frame_layout.addRow(self._table_label)
results_box.frame_layout.addRow(self._result_table)
export_row = QWidget(self)
export_lay = QHBoxLayout(export_row)
export_lay.addWidget(HorizontalFiller(self))
export_lay.addWidget(self._export_button)
results_box.frame_layout.addRow(export_row)
export_lay.setContentsMargins(0,0,0,0)
self._current_table = None
self._setTableFieldsCombo()
self._table_combo.currentTextChanged.connect(self._setTableFieldsCombo)
self._setConditionsCombo()
self._field_combo.currentTextChanged.connect(self._setConditionsCombo)
self._conditionChange()
self._condition_combo.currentTextChanged.connect(self._conditionChange)
self._bas_search_lay.addWidget(form_box)
self._bas_search_lay.addWidget(results_box)
self.setCentralWidget(self._main_widget)
except Exception as e:
print("Window failed", e)
def _setTableFieldsCombo(self):
self._field_combo.clear()
table_name = self._table_combo.getValue()
if table_name in [table.name for table in Tables]:
for table in self._tables:
if table.name == table_name:
fields = table.fields
if table_name != "CLIENT":
fields.pop(fields.index("AP_PHASE_ID"))
self._field_combo.addItems(fields)
self._current_table = table
self._setConditionsCombo()
return
else:
for table in ExclusiveDbTables:
if table_name == table.name:
fields = table.fields
fields.pop(fields.index("AP_PHASE_ID"))
self._field_combo.addItems(fields)
self._current_table = table
self._setConditionsCombo()
return
def _setConditionsCombo(self):
table_name = self._table_combo.getValue()
field_name = self._field_combo.getValue()
number_conds = ["=", ">", "<", ">=", "<=", "Range"]
bool_conds = ["True", "False"]
str_conds = ["="]
self._value_entry.clear()
self._range_entry.clear()
self._condition_combo.clear()
field_name = self._field_combo.getValue()
for field in self._current_table.table_fields:
if field.name == field_name:
if field.data_type == Types.TEXT:
self._condition_combo.addItems(str_conds)
self._value_entry.clear()
self._database.openDatabase()
items = list(set(self._database.getValuesFromField(table_name, field_name)))
self._database.closeDatabase()
self._value_entry.addItems(items)
self._value_entry.setCurrentText("")
self._value_entry.setEnabled(True)
self._value_type = Types.TEXT
elif field.data_type == Types.BOOLEAN:
self._condition_combo.addItems(bool_conds)
self._value_entry.clear()
self._value_entry.setEnabled(False)
self._value_type = Types.BOOLEAN
else:
self._condition_combo.addItems(number_conds)
self._value_entry.clear()
self._value_entry.setEnabled(True)
self._value_type = Types.NUMBER
return
def _conditionChange(self):
if self._condition_combo.getValue() == "Range":
self._range_entry.setEnabled(True)
else:
self._range_entry.setEnabled(False)
self._range_entry.clear()
def getBoolValue(self, bool_string):
if bool_string == "False":
return 0
return 1
def getBoolString(self, bool_value):
return str(bool(bool_value))
def updateTable(self, data, field_name, is_bool):
headers = ["AP_PHASE_ID", "ADDRESS"]
if field_name != "ADDRESS" and field_name != "AP_PHASE_ID":
headers.append(field_name)
data_count = len(data)
self._table_label.setText("Number of Results: "+str(data_count))
self._result_table.setRowCount(0)
self._result_table.setColumnCount(len(headers))
self._result_table.setRowCount(data_count)
self._export_button.setEnabled((self._result_table.rowCount() >= 1))
for col, header_text in enumerate(headers):
header = QTableWidgetItem(header_text)
self._result_table.setHorizontalHeaderItem(col, header)
for row, (ap_phase_id, field_row) in enumerate(data.items()):
item = QTableWidgetItem(ap_phase_id)
self._result_table.setItem(row, 0, item)
for col, field in enumerate(field_row):
if is_bool and field != "ADDRESS":
item = QTableWidgetItem(self.getBoolString(field_row[field]))
else:
item = QTableWidgetItem(str(field_row[field]))
self._result_table.setItem(row, col+1, item)
self._result_table.horizontalHeader().setStretchLastSection(True)
def searchForData(self):
table_name = self._table_combo.getValue()
field_name = self._field_combo.getValue()
condition = self._condition_combo.getValue()
value_1 = self._value_entry.getValue()
value_2 = self._range_entry.getValue()
is_bool = False
if condition == "True" or condition == "False":
value_1 = self.getBoolValue(condition)
is_bool = True
condition = '='
self._database.openDatabase()
if condition == "Range":
data = {ap_phase_id[0]:{"ADDRESS": "", field_name: ""} for ap_phase_id in self._database.getApPhaseIDFromRange(table_name, field_name, float(value_1), float(value_2))}
else:
if self._value_type == Types.TEXT:
data = {ap_phase_id[0]:{"ADDRESS": "", field_name: ""} for ap_phase_id in self._database.getApPhaseIDFromValue(table_name, field_name, value_1, condition)}
elif self._value_type == Types.INTEGER:
data = {ap_phase_id[0]:{"ADDRESS": "", field_name: ""} for ap_phase_id in self._database.getApPhaseIDFromValue(table_name, field_name, int(value_1), condition)}
else:
data = {ap_phase_id[0]:{"ADDRESS": "", field_name: ""} for ap_phase_id in self._database.getApPhaseIDFromValue(table_name, field_name, float(value_1), condition)}
for ap_phase_id in data:
data[ap_phase_id]["ADDRESS"] = self._database.readValueFromApPhaseID("CLIENT", ap_phase_id, "ADDRESS")
if field_name != "ADDRESS" and field_name != "AP_PHASE_ID":
data[ap_phase_id][field_name] = self._database.readValueFromApPhaseID(table_name, ap_phase_id, field_name)
self._database.closeDatabase()
self.updateTable(data, field_name, is_bool)
def exportCSV(self):
file_name = self.exportCSVDialog()
temp_row = []
headers = []
for col in range(self._result_table.columnCount()):
headers.append(self._result_table.horizontalHeaderItem(col).text())
if file_name:
with open(file_name, 'w', newline='') as n_f:
writer = csv.writer(n_f)
writer.writerow(headers)
for row in range(self._result_table.rowCount()):
for col in range(self._result_table.columnCount()):
temp_row.append(self._result_table.item(row, col).text())
writer.writerow(temp_row)
temp_row.clear()
def exportCSVDialog(self):
file_dialog = QFileDialog()
file_name, ext = file_dialog.getSaveFileName(self, 'Export CSV File', "", "CSV (*.csv)")
return file_name
# ADVANCE METHODS
# ----------------------------------------------------------------------------------------
def sqlSearch(self):
values = []
try:
connection = sqlite3.connect(DB_LOC)
cursor = connection.cursor()
sql = self._sql_cmd_box.toPlainText()
cursor.execute(sql)
values = cursor.fetchall()
if values:
self.updateAdvTable(values, sql)
else:
connection.commit()
except Exception as error:
error_text = "\n(!) Search Error - {e}\n\n".format(e=error)
self.searchErrorMsgBox(error_text)
finally:
connection.close()
def clearFeedBack(self):
self._feedback_box.clear()
def updateAdvTable(self, data, sql):
data_count = len(data)
field_names_ordered = []
self._adv_table_label.setText("Number of Results: "+str(data_count))
self._adv_results_table.setRowCount(0)
if data_count:
table_names = list(self._field_names.keys())
table_names = [table_name for table_name in table_names if re.search(r'\b{tn}\b'.format(tn = table_name), sql)]
all_field_names = {}
for table_name in table_names:
for field_name in self._field_names[table_name]:
all_field_names[field_name] = ''
if len(all_field_names):
field_names_unordered = list(all_field_names.keys())
field_names_unordered = [field_name for field_name in all_field_names if re.search(r'\b{fn}\b'.format(fn = field_name), sql)]
field_names_ordered = sorted(field_names_unordered, key=sql.find)
if not len(field_names_ordered):
for table_name in table_names:
field_names_ordered.extend(getAllFieldNames(table_name))
self._adv_results_table.setColumnCount(len(field_names_ordered))
self._adv_results_table.setRowCount(data_count)
for col, field_name in enumerate(field_names_ordered):
header = QTableWidgetItem(field_name)
self._adv_results_table.setHorizontalHeaderItem(col, header)
for row, data_row in enumerate(data):
for col, data_value in enumerate(data_row):
item = QTableWidgetItem(str(data_value))
self._adv_results_table.setItem(row, col, item)
self._export_results_button.setDisabled(False)
else:
self._export_results_button.setDisabled(True)
def exportTable(self):
file_name = self.exportCSVDialog()
temp_row = []
headers = []
for col in range(self._adv_results_table.columnCount()):
headers.append(self._adv_results_table.horizontalHeaderItem(col).text())
if file_name:
with open(file_name, 'w', newline='') as n_f:
writer = csv.writer(n_f)
writer.writerow(headers)
for row in range(self._adv_results_table.rowCount()):
for col in range(self._adv_results_table.columnCount()):
temp_row.append(self._adv_results_table.item(row, col).text())
writer.writerow(temp_row)
temp_row.clear()
def searchErrorMsgBox(self, error_text):
msg = QMessageBox(self)
msg.setWindowTitle("SQL Entry Error")
msg.setText(error_text)
msg.setIcon(msg.Warning)
msg.setStandardButtons(msg.Ok)
msg.exec_()
def __init__(self, title, labels=[], data=[], table_name="", field_name="", random_colors=False, has_explode=True, parent=None):
super().__init__(parent)
self._main_widget = QWidget(self)
self._layout = QVBoxLayout(self._main_widget)
self._title = title
self._data = data
self._df_data = {k:v for (k,v) in zip(labels, data)}
self.setWindowTitle(title)
# Pie chart, where the slices will be ordered and plotted counter-clockwise:
self._labels = labels
self._sizes = data
self._legend_labels = [label+" - "+"{:.1f}".format(value)+"%" for label, value in zip(self._labels, self._sizes)]
self._last_column = excelColumnFromNumber(len(self._labels))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
explode = [0 for value in data]
explode[1] = 0.1
explode = tuple(explode)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self._canvas = FigureCanvas(self._figure)
colors = ['yellowgreen', 'lightskyblue']
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
if random_colors:
if has_explode:
self.ax.pie(self._sizes, explode=explode, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
if has_explode:
self.ax.pie(self._sizes, explode=explode, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
self.ax.legend(labels=self._legend_labels, loc="best")
# Equal aspect ratio ensures that pie is drawn as a circle.
self.ax.axis("equal")
self.ax.set_title(title)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.setCentralWidget(self._canvas)
class PlotPieWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", random_colors=False, has_explode=True, parent=None):
super().__init__(parent)
self._main_widget = QWidget(self)
self._layout = QVBoxLayout(self._main_widget)
self._title = title
self._data = data
self._df_data = {k:v for (k,v) in zip(labels, data)}
self.setWindowTitle(title)
# Pie chart, where the slices will be ordered and plotted counter-clockwise:
self._labels = labels
self._sizes = data
self._legend_labels = [label+" - "+"{:.1f}".format(value)+"%" for label, value in zip(self._labels, self._sizes)]
self._last_column = excelColumnFromNumber(len(self._labels))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
explode = [0 for value in data]
explode[1] = 0.1
explode = tuple(explode)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self._canvas = FigureCanvas(self._figure)
colors = ['yellowgreen', 'lightskyblue']
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
if random_colors:
if has_explode:
self.ax.pie(self._sizes, explode=explode, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
if has_explode:
self.ax.pie(self._sizes, explode=explode, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
self.ax.legend(labels=self._legend_labels, loc="best")
# Equal aspect ratio ensures that pie is drawn as a circle.
self.ax.axis("equal")
self.ax.set_title(title)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
if file_name != "":
title = self.ax.title.get_text()
df = pd.DataFrame(data=[self._df_data])
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Pie_Chart', index=False)
workbook = writer.book
worksheet = writer.sheets["Pie_Chart"]
chart = workbook.add_chart({"type": 'pie'})
chart.set_title({"name": title})
chart.add_series({"categories": "=Pie_Chart!$A$1:${lc}$1".format(lc = self._last_column), "values": "=Pie_Chart!$A$2:${lc}$2".format(lc = self._last_column)})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()
