def toDB(self):
q = QtSql.QSqlQuery()
try:
confdate = self.confdate.date().toPyDate()
query = """UPDATE LIABILITY SET CONFIRM_DATE='%s' WHERE SUB_CODE='%s'""" % (
confdate, self.subcode)
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
q = QtSql.QSqlQuery(
"""SELECT AMOUNT FROM LIABILITY WHERE SUB_CODE='%s'""" %
self.subcode)
while q.next():
amount = q.value(0).toDouble()[0]
ct = CashTrade(book=0,
trader=self.user.id,
tradeDateTime=datetime.datetime.fromordinal(
confdate.toordinal()),
amount=amount,
comment=u'申购%s' % self.subcode)
ct.toDB()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def getTypeData(code):
q = QtSql.QSqlQuery(
"""SELECT SEC_NAME, SEC_TYPE, EXCHANGE FROM SECINFO WHERE SEC_CODE='%s'"""
% code)
while q.next():
name = q.value(0).toString()
insttype = q.value(1).toString()
exchange = q.value(2).toString()
return name, insttype, exchange
infolist = ['sec_name', 'sec_type', 'exch_city']
result = w.wss(
unicode(code), infolist,
'tradeDate={0}'.format(format(datetime.datetime.today(),
'%Y%m%d')))
if result:
if result.ErrorCode == 0:
name = result.Data[0][0]
insttype = result.Data[1][0]
exchange = result.Data[2][0]
q = QtSql.QSqlQuery()
try:
q.exec_(
"""INSERT INTO SECINFO VALUES ('%s','%s','%s','%s')"""
% (code, name, insttype, exchange))
QtSql.QSqlDatabase().commit()
return name, insttype, exchange
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def bookToDB(self):
q = QtSql.QSqlQuery()
try:
q.exec_(
"""INSERT INTO DEALS VALUES (%s,%s,%s,%s)""" %
(self.dealID, self.book.id, self.events[0].timestamp, None))
QtSql.QSqlDatabase().commit()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def expsettle(self, trader):
q = QtSql.QSqlQuery()
try:
query = """UPDATE TRADES SET EXP_SETTLED_BY='%s' WHERE TRADE_ID='%s'""" % (
self.settledBy, self.tradeID)
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
self.settledBy = trader
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def newHol(self):
nh = NewHolPanel()
if nh.exec_():
date = nh.date.date()
name = nh.nameList.currentText()
status = nh.statusList.currentText()
q = QtSql.QSqlQuery()
try:
q.exec_("""INSERT INTO HOLIDAYS VALUES ('%s','%s','%s')""" % (date.toPyDate(), name, status))
QtSql.QSqlDatabase().commit()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def deleteTrade(self):
rowIndex = self.currentIndex().row()
tradeID = self.model().index(rowIndex, 14).data().toString()
q = QtSql.QSqlQuery()
try:
query = """DELETE FROM TRADES WHERE TRADE_ID='%s'""" % tradeID
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
self.refresh()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def bookToDB(self):
try:
q = QtSql.QSqlQuery()
q.exec_(
'INSERT INTO EVENTS VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
%
(self.eventID, self.dealID, self.instID, self.timestamp,
self.typeID(), self.by, self.at, self.cancelled, self.comment,
self.refDate, self.refAmount, self.refPrice, self.refYield))
QtSql.QSqlDatabase().commit()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def toDB(self):
q = QtSql.QSqlQuery()
try:
query = """INSERT INTO TRADES VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',NULL,NULL)""" % (
self.tradeID, self.book, self.trader, self.tradeDateTime,
self.settleDate, self.instCode, self.amount, self.price,
int(self.collateralized), self.refTrade, self.refYield,
self.settledBy, self.comment, self.maturityDate)
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def __init__(self,
ticketTableName,
queueTypeId,
db=QtSql.QSqlDatabase(),
parent=None):
super(CEQTicketModel, self).__init__(parent, db)
self._queueTypeId = queueTypeId
self._currentDate = None # для смены фильтра запросов к базе при смене дня (если класс живет на протяж неск. дней)
self._queueId = None
self._queueName = u''
self._queueOffice = u''
self._ticketPrefix = u''
self._baseOrgStructureId = None
self.setTable(ticketTableName)
self.setSort([(u'status', QtCore.Qt.DescendingOrder),
(u'summonDatetime', QtCore.Qt.AscendingOrder),
(u'idx', QtCore.Qt.AscendingOrder)])
self.setEditStrategy(self.OnManualSubmit)
self._statusColumn = self.fieldIndex(u'status')
self._summonColumn = self.fieldIndex(u'summonDatetime')
self._orgStructureColumn = self.fieldIndex(u'orgStructure_id')
self.updateForCurrentDate()
def resetPwd(self, newpwd):
hex = hashlib.sha1(newpwd).hexdigest()
q = QtSql.QSqlQuery()
query = """UPDATE USERS SET PWD='%s', PWD_TEMP=0 WHERE ID=%s""" % (hex, self.id)
print query
q.exec_(query)
QtSql.QSqlDatabase().commit()
self.pwd = hex
def cancel(self, user, comment=''):
self.by = user.name
self.at = datetime.datetime.now()
self.comment = comment
self.cancelled = True
q = QtSql.QSqlQuery()
query = """UPDATE EVENTS SET CANCEL='%s' WHERE ID=%s""" % (
1, self.eventID)
q.exec_(query)
QtSql.QSqlDatabase().commit()
def sign(self, user, comment=''):
self.by = user.name
self.at = datetime.datetime.now()
if comment:
self.comment = comment + ' | ' + self.comment
q = QtSql.QSqlQuery()
query = """UPDATE EVENTS SET SIGNER='%s', SIGNED_AT='%s', COMMENT='%s' WHERE ID='%s'""" % (
user.name, datetime.datetime.now(), self.comment, self.eventID)
q.exec_(query)
QtSql.QSqlDatabase().commit()
def removeRow(self):
rowIndex = self.currentIndex().row()
subcode = self.model().index(rowIndex, 6).data().toString()
confdate = self.model().index(rowIndex, 12).data().toString()
if confdate.isEmpty():
reply = QtGui.QMessageBox.question(
self, u'删除本条记录', u'申请书编号：%s' % subcode,
QtGui.QMessageBox.Yes | QtGui.QMessageBox.No,
QtGui.QMessageBox.No)
if reply == QtGui.QMessageBox.Yes:
q = QtSql.QSqlQuery()
try:
q.exec_("""DELETE FROM LIABILITY WHERE SUB_CODE='%s'""" %
subcode)
QtSql.QSqlDatabase().commit()
self.model().removeRow(rowIndex)
self.refresh()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def initPwd(self):
import random
import string
import utils
initpwd = ''.join(random.choice(string.ascii_letters) for i in range(6))
hex = hashlib.sha1(initpwd).hexdigest()
utils.sendmail('*****@*****.**', [self.email], u'重设CAMP密码', initpwd)
q = QtSql.QSqlQuery()
query = """UPDATE USERS SET PWD='%s', PWD_TEMP=1 WHERE ID=%s""" % (hex, self.id)
q.exec_(query)
QtSql.QSqlDatabase().commit()
self.pwd = hex
def settle(self, trader):
q = QtSql.QSqlQuery()
try:
query = """UPDATE TRADES SET SETTLED_BY='%s' WHERE TRADE_ID='%s'""" % (
trader, self.tradeID)
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
self.settledBy = trader
ct = CashTrade(
self.book,
self.trader,
datetime.datetime.fromordinal(self.settleDate.toordinal()),
-self.amount * self.price,
self.instCode[-2:] == 'IB' and 'CASH_IB' or 'CASH_EX',
refTrade=self.tradeID,
comment=u'交割现金流')
ct.toDB()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def toDB(self):
code = self.subcode.text()
client_type = self.clientType.currentIndex()
client_name = self.clientname.currentText()
sale_type = self.rbDirect.isChecked() and 'A' or 'B'
amount, _ = self.amount.text().toDouble()
rtn, _ = self.rtn.text().toDouble()
subdate = self.subdate.date().toPyDate()
settledate = self.settledate.date().toPyDate()
expdate = self.expdate.date().toPyDate()
expops = self.expops.currentIndex() + 1
comment = self.comment.text()
q = QtSql.QSqlQuery()
try:
query = """INSERT INTO LIABILITY VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s', '%s')""" % (
code, client_type, client_name, sale_type, amount, rtn,
subdate, settledate, expdate, expops, comment, '')
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
def initDB(self,
host='caitcfid.mysql.rds.aliyuncs.com',
port=3306,
dbname='secs'):
self.db = QtSql.QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName(host)
self.db.setPort(port)
self.db.setDatabaseName(dbname)
self.db.setUserName('hewei')
self.db.setPassword('wehea1984')
if not self.db.open():
raise Exception(u'无法连接数据库')
QtSql.QSqlDatabase().setConnectOptions('MYSQL_OPT_RECONNECT=1')
self.oss = OSS()
def updateInfo(self):
c = self.code.text()
if c in self.secinfo:
n, t, e = self.secinfo[c]
self.secname.setText(n)
self.prdtype.setText(t)
self.exchange.setText(e)
else:
gotinfo = False
infolist = ['sec_name', 'sec_type', 'exch_city']
result = w.wss(
unicode(c), infolist,
'tradeDate={0}'.format(format(self.sysdate, '%Y%m%d')))
if result:
if result.ErrorCode == 0:
self.secname.setText(result.Data[0][0])
self.prdtype.setText(result.Data[1][0])
self.exchange.setText(result.Data[2][0])
self.secinfo[c] = (self.secname.text(),
self.prdtype.text(),
self.exchange.text())
q = QtSql.QSqlQuery()
try:
q.exec_(
"""INSERT INTO SECINFO VALUES ('%s','%s','%s','%s')"""
% (c, self.secname.text(), self.prdtype.text(),
self.exchange.text()))
QtSql.QSqlDatabase().commit()
gotinfo = True
except Exception, e:
print e.message
QtSql.QSqlDatabase().rollback()
if not gotinfo:
self.secname.setText(u'未知')
self.prdtype.setText(u'未知')
self.exchange.setText(u'未知')
def uploadOrder(self):
fn = QtGui.QFileDialog.getOpenFileName(self, u'选取指令文件', '.')
if not fn.isEmpty():
rowIndex = self.currentIndex().row()
tradeID = self.model().index(rowIndex, 14).data().toString()
s = self.oss.upload_trade_order(str(tradeID), unicode(fn))
if s:
q = QtSql.QSqlQuery()
query = """UPDATE TRADES SET ORDER_SAVED='%s' WHERE TRADE_ID='%s'""" % (
datetime.datetime.now(), tradeID)
q.exec_(query)
#print query
QtSql.QSqlDatabase().commit()
QtGui.QMessageBox.about(self, u'交易指令上传', u'上传成功')
else:
QtGui.QMessageBox.warning(self, u'交易指令上传', u'上传失败，请联系开发人员解决问题')
def __init__(self, queueTypeId, db=QtSql.QSqlDatabase(), parent=None):
super(CEQTicketControlModel, self).__init__(u'EQueueTicket',
queueTypeId, db, parent)
# self.setEditStrategy(self.OnFieldChange)
self._currentIdList = []
self._nextId = QtCore.QVariant()
self._prevIdStack = []
# self._summonTimerId = None
# self._summonTimeout = 5.0
self._ticketIdToValue = {
} #FIXME: atronah: возможно, лучше не хранить дубликаты данных и получать их через rowById
self._noUpdateMutex = QtCore.QMutex()
def __init__(self):
# Вызов конструктора базового класса
QtGui.QDialog.__init__(self)
# Подключение описания интерфейса
self.__ui = Ui_Dialog()
self.__ui.setupUi(self)
# Подключение драйвера MySQL
self.__db = QtSql.QSqlDatabase("QMYSQL")
# Создание пяти моделей представления результатов выборки
self.__model = {
"host_list": CMyModel(),
"file_list": CMyModel(),
"sea_res": CMyModel(),
"down_list": CMyModel(),
"down_finish": CMyModel()
}
# Создание пяти таблиц графического интерфейса,
# каждая из которых связывается со своей
# моделью представления результатов выборки
self.__ui.host_list.setModel(self.__model["host_list"])
self.__ui.file_list.setModel(self.__model["file_list"])
self.__ui.sea_res.setModel(self.__model["sea_res"])
self.__ui.down_list.setModel(self.__model["down_list"])
self.__ui.down_finish.setModel(self.__model["down_finish"])
self.__server = {}
self.__filelist = None
# Считывание параметров подключения из конфигурационного файла
self.first_config_host = config.host
self.first_config_mysql_port = str(config.mysql_port)
self.first_config_route_port = str(config.route_port)
self.first_config_upload_port = str(config.upload_port)
self.first_config_db = config.db
self.first_config_user = config.user
# Обновление интерфейса
self.conf_reset_click()
def __init__(self,
queueTypeId,
orgStructureId=None,
db=QtSql.QSqlDatabase(),
parent=None):
self._orgStructureId = orgStructureId
self._isInProgressDisplay = True
self._isWaitersDisplay = True
super(CEQTicketViewModel, self).__init__(u'vEQueueTicket', queueTypeId,
db, parent)
self._valueColumn = self.fieldIndex('value')
self._rowLimit = 0
self._baseFont = QtGui.QFont()
self._baseFont.setPointSize(64)
self._inProgressBrush = QtGui.QBrush(
QtGui.QColor(
QtCore.Qt.green).lighter()) #, QtCore.Qt.Dense5Pattern)
self._summonRowList = []
self._lastInProgressRow = -1
self._isBlink = False
self._blinkTimerId = None
self._blinkTimeout = 0.5 # seconds
self._maxElapsedSummon = QtCore.QTime(
0, # hours
0, # minutes
5, # seconds
0) # mseconds
self._alreadySommoningList = []
def __init__(self, user, db, host="localhost", port=None):
self.__QByteArray_type = type(QtCore.QByteArray())
self.__db = []
# Создаются два подключения:
# один для главного потока программы,
# другой для возможно существующего дочернего потока программы
for x in range(0, 2):
# Загрузка драйвера для целевой СУБД
next_db = QtSql.QSqlDatabase("QMYSQL")
# Установка параметров подключения к целевому серверу БД
next_db.setHostName(host)
if port != None:
next_db.setPort(port)
next_db.setUserName(user)
next_db.setDatabaseName(db)
# Подключение к целевому серверу БД
if not next_db.open():
raise Exception("Ошибка при открытии базы данных")
self.__db.append(next_db)
# Инициализация объектов класса QSqlQuery,
# с помощью которых будут выполнятся различные запросы к БД
self.__query = [
QtSql.QSqlQuery(db=self.__db[0]),
QtSql.QSqlQuery(db=self.__db[1])
]
def __init__(self, parent=None, db=QtSql.QSqlDatabase()):
super(CSqlTableModel, self).__init__(parent, db)
self._sortRuleList = []
def refresh(self):
#self.dataModel.query().exec_()
if not QtSql.QSqlDatabase().isOpen():
print 'Lost Connection!!!!'
self.dataModel.setQuery(self.query)
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
import datetime
from PyQt4 import QtSql
db = QtSql.QSqlDatabase("QSQLITE")
db.setDatabaseName("/home/user/.rtcom-eventlogger/el-v1.db")
def insert_sms_in_history(sms):
global db
# Pre
num = sms['num']
if not sms['message']:
return
msg = sms['message'].encode('utf-8').decode('utf-8')
group_uid = str(num[-7:])
now = int(datetime.datetime.now().strftime("%s"))
db.open()
def closeOnDB(self):
q = QtSql.QSqlQuery()
query = """UPDATE DEALS SET CLOSE='%s' WHERE ID='%s'""" % (
self.events[-1].timestamp)
q.exec_(query)
QtSql.QSqlDatabase().commit()
def __init__(self):
QMainWindow.__init__(self)
self.db = QtSql.QSqlDatabase() #kalau ini perlu, karena mungkin akan sering dipakai oleh method lain di kelas ini
self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
self.model = None #sebenernya ini ga perlu dijadikan variabel object. But its okay
self.filename = None
