def _init_combobox_completer(self):
# 增加自动补全
items_list = settings.value(Configs.first_classifier)
_add_completer(items_list, self.comboBox)
items_list_2 = settings.value(Configs.second_classifier)
_add_completer(items_list_2, self.comboBox_2)
def use_setting_date_value(self):
"""
将日期恢复到原值
"""
start_time = settings.value(Configs.start_time)
self.dateEdit.setDate(QDate.fromString(start_time, "yyyy-MM-dd"))
end_time = settings.value(Configs.end_time)
self.dateEdit_2.setDate(QDate.fromString(end_time, "yyyy-MM-dd"))
def get_conn(self, *args, **kwargs):
conn = pymysql.connect(host=settings.value(Configs.mysql_host),
user=settings.value(Configs.mysql_user),
password=settings.value(Configs.mysql_password),
database=settings.value(Configs.mysql_database),
port=int(settings.value(Configs.mysql_port)))
cursor = conn.cursor()
conn.ping()
return conn, cursor
def _init_radio_frame(self):
"""
初始化界面显示哪个数据库信息
"""
if settings.value(Configs.db_used) == DBField.db_type[0]:
self.radioButton.setChecked(True)
self.stackedWidget.setCurrentIndex(0)
elif settings.value(Configs.db_used) == DBField.db_type[1]:
self.radioButton_2.setChecked(True)
self.stackedWidget.setCurrentIndex(1)
def use_different_windows(self):
# 使用不同的窗口
if self.radioButton.isChecked():
self.page.lineEdit.setText(settings.value(Configs.db_path))
self.stackedWidget.setCurrentIndex(0)
elif self.radioButton_2.isChecked():
self.stackedWidget.setCurrentIndex(1)
def delete_one(self, id=1):
try:
table_name = settings.value(Configs.mysql_table)
self.cursor.execute("delete from {} where id={}".format(
table_name, id))
except Exception as e:
pass
def search_all(self):
try:
table_name = settings.value(Configs.mysql_table)
sql = "select * from {}".format(table_name)
df = pd.read_sql(sql, self.conn)
return df
except Exception as e:
return self._panda_df()
def __init__(self, update_signal):
super().__init__()
self.setupUi(self)
self.buttonBox.button(QDialogButtonBox.Cancel).setText("取消")
self.buttonBox.button(QDialogButtonBox.Ok).setText("确定")
self.comboBox_3.addItems(DBField.inOutSelect)
self.signal = update_signal
self.first_classifier_items = settings.value(Configs.first_classifier)
if self.first_classifier_items is None:
self.first_classifier_items = []
self.comboBox.addItems(self.first_classifier_items)
self.second_classifier_items = settings.value(
Configs.second_classifier)
if self.second_classifier_items is None:
self.second_classifier_items = []
self.comboBox_2.addItems(self.second_classifier_items)
self._init_combobox_completer()
def search_more(self, dateInput='2020-10-04'):
try:
table_name = settings.value(Configs.mysql_table)
sql = "select * from {} where dateInput='{}'".format(
table_name, dateInput)
df = pd.read_sql(sql, self.conn)
return df
except Exception as e:
return self._panda_df()
def search_range(self, start, end):
try:
table_name = settings.value(Configs.mysql_table)
sql = "select * from {} where dateInput>='{}' and dateInput<='{}' order by dateInput".format(
table_name, start, end)
df = pd.read_sql(sql, self.conn)
return df
except Exception as e:
return self._panda_df()
def load_data_from_db(self):
"""
从数据库中获取数据
:return:
"""
# db_path = settings.value(Configs.db_path)
# db = DBOperation.get_instance(db_path)
db = DBOperation.get_instance()
# df: pd.DataFrame = db.search_all()
start_time = settings.value(Configs.start_time)
end_time = settings.value(Configs.end_time)
df: pd.DataFrame = db.search_range(start_time, end_time)
row_num, col_num = df.shape
self.setRowCount(row_num)
self.setColumnCount(col_num)
classifier = ['id', '日期', '收入\支出', '大类', '小类', '金额']
self.setHorizontalHeaderLabels(classifier)
# 隐藏id
self.setColumnHidden(0, True)
for i in range(row_num):
input_table_rows_values = df.iloc[[i]]
input_table_rows_values_array = np.array(input_table_rows_values)
input_table_rows_values_list = input_table_rows_values_array.tolist(
)[0]
for j in range(col_num):
input_table_items_list = input_table_rows_values_list[j]
input_table_items = str(input_table_items_list)
newItem = QTableWidgetItem(input_table_items)
newItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)
if input_table_rows_values_list[2] == "收入":
newItem.setBackground(QColor("#C2FF68"))
self.setItem(i, j, newItem)
self.not_start = False
return df
def insert_one(self,
dateInput='2020-12-05',
inOutClassifier="支出",
firstClass='衣',
secondClass='唯品会',
price=300):
try:
table_name = settings.value(Configs.mysql_table)
self.cursor.execute(
"insert into {} values(null,'{}','{}', '{}','{}',{})".format(
table_name, dateInput, inOutClassifier, firstClass,
secondClass, price))
except Exception as e:
pass
def update_one(self,
id=2,
dateInput='2020-10-04',
inOutClassifier="支出",
firstClass='衣',
secondClass='唯品会',
price=4000):
try:
table_name = settings.value(Configs.mysql_table)
self.cursor.execute(
"update {} set dateInput='{}', inOutClassifier='{}', firstClassifier='{}', secondClassifier='{}', `inOut`={} where id={}"
.format(table_name, dateInput, inOutClassifier, firstClass,
secondClass, price, id))
except Exception as e:
pass
def get_mysql_info(self):
"""
将MySQL数据库信息恢复的原信息
"""
self.page_2.lineEdit.setText(settings.value(Configs.mysql_host))
self.page_2.lineEdit_2.setText(settings.value(Configs.mysql_port))
self.page_2.lineEdit_3.setText(settings.value(Configs.mysql_user))
self.page_2.lineEdit_4.setText(settings.value(Configs.mysql_password))
self.page_2.lineEdit_5.setText(settings.value(Configs.mysql_database))
self.page_2.lineEdit_6.setText(settings.value(Configs.mysql_table))
def syncImages():
"""
Run the sync over the images
returns -- Returns a tuple of (state, message). state can be 'Pass' or
'Fail'
"""
images = os.path.join(pardir, "data")
server = settings.value("syncing/server_image_location").toString()
if server.isEmpty():
return ('Fail', "No server image location found in settings.ini")
if not os.path.exists(images):
# Don't return a fail if there is no data directory
return ('Pass', 'Images uploaded: %s' % str(0))
cmd = 'xcopy "%s" "%s" /Q /D /S /E /K /C /H /R /Y' % (images, server)
p = Popen(cmd, stdout=PIPE, stderr=PIPE, stdin=PIPE, shell = True)
stdout, stderr = p.communicate()
if not stderr == "":
return ('Fail', stderr)
else:
return ('Pass', stdout)
def get_instance(cls, *args, **kwargs):
ops = DBOps[settings.value(Configs.db_used)]
return ops.get_instance(*args, **kwargs)
def get_instance(cls, *args, **kwargs) -> DBOperation:
return cls(settings.value(Configs.db_path))
def use_settings_line_edit(self):
"""
设置数据库的相关信息
"""
self.page.lineEdit.setText(settings.value(Configs.db_path))
self.get_mysql_info()
def _rollback_radio(self):
if settings.value(Configs.db_used) == DBField.db_type[0]:
self.radioButton.setChecked(True)
else:
self.radioButton_2.setChecked(True)
