def getCashMovement(fromDate, toDate): query = """SELECT ID, amount, in_out, custody_oid, movement_date, comment, external_id FROM cash_movement WHERE movement_date BETWEEN %s AND %s order by movement_date desc""" resultSet = DbConnector().doQuery(query, (fromDate, toDate)) return resultSet
def getCustodyNameList(self): query = "SELECT NAME FROM CUSTODY" resultSet = DbConnector().doQuery(query, "") returnList = [] for (row) in resultSet: returnList.append(row[0]) returnList.append('ALL') return sorted(returnList)
def getLastPrice(assetName): query = """SELECT p.last_price, p.date_ FROM PRICE p INNER JOIN ASSET A ON P.ASSET_OID = A.ID WHERE A.NAME = %s order by p.DATE_ desc""" resultSet = DbConnector().doQuery(query, (assetName, )) return resultSet
def getCurrencyValueByDate(currencyName, date): query = """SELECT cv.value, cv.date_ FROM CURRENCY_VALUE cv INNER JOIN CURRENCY C ON C.ID = CV.CURRENCY_ID WHERE C.NAME = %s AND cv.DATE_ = %s""" resultSet = DbConnector().doQuery(query, (currencyName, date)) return resultSet
def getAssetNames(): query = """SELECT DISTINCT NAME FROM ASSET""" resultSet = DbConnector().doQuery(query, "") returnList = [] for (row) in resultSet: returnList.append(row[0]) returnList.append('ALL') return sorted(returnList)
def getAssetTypes(self): query = '''SELECT DISTINCT ASSET_TYPE FROM ASSET''' resultSet = DbConnector().doQuery(query, "") returnList = [] for (row) in resultSet: returnList.append(row[0]) return returnList
def insert(cashMovement): insertSentence = """insert cash_movement(amount, in_out, custody_oid, movement_date, asset_oid,comment, external_id) values (%s,%s,%s,%s,%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (cashMovement.amount, cashMovement.inOut, cashMovement.custody.OID, cashMovement.movementDate, cashMovement.asset.OID, cashMovement.comment, cashMovement.externalID))
def getMovementsByMaturityDate(maturityDate): query = '''SELECT m.id, m.gross_amount FROM movement as m left join tax as t on t.origin_oid = m.id and t.origin_type = 'MOVEMENT' WHERE m.maturity_date = %s and t.id is null order by gross_amount desc''' resultSet = DbConnector().doQuery(query, (maturityDate, )) return resultSet
def insert(corporateEvent): insertSentence = """insert corporate_event(corporate_event_type_oid, asset_oid, payment_date, gross_amount, custody_oid, net_amount, comment, external_id) values (%s,%s,%s,%s,%s,%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (corporateEvent.corporateEventType.OID, corporateEvent.asset.OID, corporateEvent.paymentDate, corporateEvent.grossAmount, corporateEvent.custody.OID, corporateEvent.netAmount, corporateEvent.comment, corporateEvent.externalID))
def getPriceByDate(assetName, date): query = """SELECT p.last_price, p.date_ FROM PRICE p INNER JOIN ASSET A ON P.ASSET_OID = A.ID WHERE ((A.NAME = %s and a.ORIGIN_NAME IS NULL) OR (A.ORIGIN_NAME = %s)) AND p.DATE_ = %s""" resultSet = DbConnector().doQuery(query, (assetName, assetName, date)) return resultSet
def getMovementType(): query = '''SELECT DISTINCT ASSET_TYPE FROM ASSET''' resultSet = DbConnector().doQuery(query, "") returnList = [] for (row) in resultSet: returnList.append(row[0]) returnList.append('CASH') returnList.append('ALL') return sorted(returnList)
def getCorporateEventByDateAndAsset(date, assetOID): query = '''SELECT ce.ID, ce.gross_amount FROM corporate_event as ce WHERE ce.payment_date = %s and ce.asset_oid = %s''' resultSet = DbConnector().doQuery(query, ( date, assetOID, )) return resultSet
def getMovementByOID(movementOID): query = '''SELECT m.ID, m.asset_oid, m.buy_sell, m.ACQUISITION_DATE, m.quantity, m.price, m.rate, m.GROSS_AMOUNT, m.NET_AMOUNT, m.COMMISSION_PERCENTAGE, m.COMMISSION_AMOUNT, m.COMMISSION_IVA_AMOUNT, m.TENOR, c.ID, m.MATURITY_DATE, IFNULL(t.tax_amount, 0), m.external_id, m.comment FROM movement as m inner join asset as a on m.asset_oid = a.id inner join custody as c on c.ID = m.CUSTODY_OID left join tax as t on t.origin_oid = m.id and t.origin_type = 'MOVEMENT' WHERE m.ID = %s''' resultSet = DbConnector().doQuery(query, (movementOID, )) return resultSet
def getMovementsByDate(assetName, fromDate, toDate): query = '''SELECT m.ID, m.asset_oid, m.buy_sell, m.ACQUISITION_DATE, m.quantity, m.price, m.rate, m.GROSS_AMOUNT, m.NET_AMOUNT, m.COMMISSION_PERCENTAGE, m.COMMISSION_AMOUNT, m.COMMISSION_IVA_AMOUNT, m.TENOR, c.ID, m.MATURITY_DATE, IFNULL(t.tax_amount, 0), m.external_id, m.comment FROM movement as m inner join asset as a on m.asset_oid = a.id inner join custody as c on c.ID = m.CUSTODY_OID left join tax as t on t.origin_oid = m.id and t.origin_type = 'MOVEMENT' WHERE ACQUISITION_DATE >= %s AND ACQUISITION_DATE <= %s AND (a.NAME = %s or %s is null) ORDER BY m.asset_oid,ACQUISITION_DATE''' resultSet = DbConnector().doQuery( query, (fromDate, toDate, assetName, assetName)) return resultSet
def insertMovement(movement): insertSentence = """insert movement(asset_oid, buy_sell,acquisition_date, quantity, price, rate, gross_amount, net_amount, commission_percentage, commission_amount, commission_iva_amount, tenor, maturity_date, custody_oid, external_id, comment) values (%s,%s,%s,%s, %s,%s,%s,%s, %s,%s,%s,%s, %s,%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (movement.asset.OID, movement.buySell, movement.acquisitionDate, movement.quantity, movement.price, movement.rate, movement.grossAmount, movement.netAmount, movement.commissionPercentage, movement.commissionAmount, movement.commissionVATAmount, movement.tenor, movement.maturityDate, movement.custody.OID, movement.externalID, movement.comment))
def deleteMovement(movementOID): deleteSentence = """delete from movement where id =%s""" return DbConnector().doDelete(deleteSentence, (movementOID, ))
def getCurrencyByName(currencyName): query = """SELECT c.id FROM CURRENCY C WHERE C.NAME = %s""" resultSet = DbConnector().doQuery(query, (currencyName, )) return resultSet
def insertCurrencyValue(currencyValue): insertSentence = """insert currency_value(currency_id, date_, value) values (%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (currencyValue.currencyOID, currencyValue.date, currencyValue.value))
def deleteCorporateEvent(movementOID): deleteSentence = """delete from corporate_event where id =%s""" return DbConnector().doDelete(deleteSentence, (movementOID, ))
def getTaxByExternalID(externalID): query = '''SELECT t.external_id FROM tax as t WHERE t.external_id = %s''' resultSet = DbConnector().doQuery(query, (externalID, )) return resultSet
def insert(price): insertSentence = """insert price(asset_oid, last_price, date_) values (%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (price.assetOID, price.lastPrice, price.date))
def getMovements(fromDate, toDate, movementType, assetName, custodyName): paramns = { 'fromdate': fromDate, 'toDate': toDate, 'movementType': movementType, 'assetName': assetName, 'custodyName': custodyName } query = '''SELECT m.ID as EVENT_ID, 'MOVEMENT' as EVENT_TYPE, 'TRX' AS EVENT_SUB_TYPE, BUY_SELL as EVENT_DIRECTION, a.name as ASSET_NAME, ACQUISITION_DATE AS EVENT_DATE, QUANTITY AS QUANTITY, PRICE AS PRICE, RATE AS RATE, GROSS_AMOUNT AS GROSS_AMOUNT, NET_AMOUNT AS NET_AMOUNT, COMMISSION_PERCENTAGE AS COMMISSION_PERCENTAGE, COMMISSION_AMOUNT AS COMMISSION_AMOUNT, COMMISSION_IVA_AMOUNT AS COMMISSION_IVA_AMOUNT, TENOR AS TENOR, MATURITY_DATE AS MATURITY_DATE, c.name AS CUSTODY_NAME, t.ID AS TAX_ID, t.TAX_AMOUNT as TAX_AMOUNT, COMMENT AS COMMENT, m.EXTERNAL_ID AS EXTERNAL_ID FROM movement m left join asset as a on m.asset_oid = a.id left join custody as c on c.id = m.custody_oid left join tax as t on t.origin_oid = m.id and t.origin_type = 'MOVEMENT' WHERE ACQUISITION_DATE >= %(fromdate)s AND ACQUISITION_DATE <= %(toDate)s AND (a.asset_type = %(movementType)s or %(movementType)s = 'ALL') AND (a.name = %(assetName)s or %(assetName)s = 'ALL') AND (c.name = %(custodyName)s or %(custodyName)s = 'ALL') UNION ALL SELECT ce.ID as EVENT_ID, 'CORP EVENT' as EVENT_TYPE, cet.name AS EVENT_SUB_TYPE, null as EVENT_DIRECTION, a.name as ASSET_NAME, payment_date AS EVENT_DATE, NULL AS QUANTITY, NULL AS PRICE, NULL AS RATE, gross_amount AS GROSS_AMOUNT, NET_AMOUNT AS NET_AMOUNT, NULL AS COMMISSION_PERCENTAGE, NULL AS COMMISSION_AMOUNT, NULL AS COMMISSION_IVA_AMOUNT, NULL AS TENOR, NULL AS MATURITY_DATE, c.name AS CUSTODY_NAME, t.ID AS TAX_ID, t.TAX_AMOUNT as TAX_AMOUNT, COMMENT AS COMMENT, ce.EXTERNAL_ID AS EXTERNAL_ID FROM corporate_event ce left join asset as a on ce.asset_oid = a.id left join custody as c on c.id = ce.custody_oid left join corporate_event_type as cet on cet.id = ce.corporate_event_type_oid left join tax as t on t.origin_oid = ce.id and t.origin_type = 'CORPORATE_EVENT' WHERE payment_date >= %(fromdate)s AND payment_date <= %(toDate)s AND (a.asset_type = %(movementType)s or %(movementType)s = 'ALL') AND (a.name = %(assetName)s or %(assetName)s = 'ALL') AND (c.name = %(custodyName)s or %(custodyName)s = 'ALL') UNION ALL SELECT cm.ID as EVENT_ID, 'MOVEMENT' as EVENT_TYPE, 'CASH' AS EVENT_SUB_TYPE, in_out as EVENT_DIRECTION, a.name as ASSET_NAME, movement_date AS EVENT_DATE, NULL AS QUANTITY, NULL AS PRICE, NULL AS RATE, amount AS GROSS_AMOUNT, amount AS NET_AMOUNT, NULL AS COMMISSION_PERCENTAGE, NULL AS COMMISSION_AMOUNT, NULL AS COMMISSION_IVA_AMOUNT, NULL AS TENOR, NULL AS MATURITY_DATE, c.name AS CUSTODY_NAME, null AS TAX_ID, null as TAX_AMOUNT, COMMENT AS COMMENT, EXTERNAL_ID AS EXTERNAL_ID FROM cash_movement CM left join custody as c on c.id = CM.custody_oid left join asset as a on a.id = CM.asset_oid WHERE movement_date >= %(fromdate)s AND movement_date <= %(toDate)s AND ('CASH' = %(movementType)s or %(movementType)s = 'ALL') AND (a.name = %(assetName)s or %(assetName)s = 'ALL') AND (c.name = %(custodyName)s or %(custodyName)s = 'ALL') ''' resultSet = DbConnector().doQuery(query, paramns) return resultSet
def getTaxByOriginID(originType, originOID): query = '''SELECT t.ID, t.origin_type, origin_oid, tax_amount, external_id FROM tax as t WHERE t.origin_type = %s and t.origin_oid = %s ''' resultSet = DbConnector().doQuery(query, (originType, originOID)) return resultSet
def deleteCashMovement(cashMovementOID): deleteSentence = """delete from cash_movement where id =%s""" return DbConnector().doDelete(deleteSentence, (cashMovementOID, ))
def getCashMovementsByExternalID(externalID): query = '''SELECT cm.external_id FROM cash_movement as cm WHERE cm.external_id = %s''' resultSet = DbConnector().doQuery(query, (externalID, )) return resultSet
def deleteTax(taxOID): deleteSentence = """delete from tax where id =%s""" return DbConnector().doDelete(deleteSentence, (taxOID, ))
def updateNetAmount(OID, netAmount): insertSentence = """update corporate_event set net_amount = %s where ID = %s""" return DbConnector().doInsert(insertSentence, (netAmount, OID))
def insert(tax): insertSentence = """insert tax(origin_type, origin_oid, tax_amount, external_id) values (%s,%s,%s,%s)""" return DbConnector().doInsert( insertSentence, (tax.originType, tax.originOID, tax.taxAmount, tax.externalID))
def getAssetNames(self, assetType): query = """SELECT ID, NAME FROM ASSET WHERE ASSET_TYPE = %s""" resultSet = DbConnector().doQuery(query, (assetType, )) return resultSet
def getMovementsByExternalID(externalID): query = '''SELECT m.external_id FROM movement as m WHERE m.external_id = %s''' resultSet = DbConnector().doQuery(query, (externalID, )) return resultSet