def updateCustomer(data):
now = datetime.datetime.now()
nowDict = {'LAST_CHANGE': now.strftime("%Y-%m-%d %H:%M:%S")}
newData = {
**parseData(data),
**nowDict,
**parseDictFromFlag(data['FLAGFISICA'])
}
dataQuery = ''
for item in newData.items():
if (item[1]):
dataQuery += item[0] + ' = ' + f"'{item[1]}', "
dataQuery = dataQuery[:-2]
cod = data['CODCLI']
query = f"""
UPDATE CLIENTE
SET {dataQuery}
WHERE CODCLI = '{cod}'
"""
con.cursor().execute(query)
con.commit()
def insertNewCustomer(data):
now = datetime.datetime.now()
dates = {
'DATCAD': now.date(),
'LAST_CHANGE': now.strftime("%Y-%m-%d %H:%M:%S")
}
dataWithDate = {
**parseData(data),
**dates,
**parseDictFromFlag(data['FLAGFISICA'])
}
columnsQuery = '('
valuesQuery = '('
for item in dataWithDate.items():
if (item[1]):
columnsQuery += item[0] + ', '
valuesQuery += f"'{item[1]}'" + ', '
columnsQuery = columnsQuery[:-2] + ')'
valuesQuery = valuesQuery[:-2] + ')'
query = f"""
INSERT INTO CLIENTE {columnsQuery}
VALUES {valuesQuery}
"""
con.cursor().execute(query)
con.commit()
def getCount(user, startDate, endDate):
query = f"""
SELECT COUNT(*)
FROM MOVENDA
WHERE DATA >= '{startDate}' AND DATA <= '{endDate}' AND MOVENDA.CODVENDED = '{user.codvend}'
"""
movenda = con.cursor().execute(query).fetchone()
query = f"""
SELECT COUNT(MOVENDA.CODVENDED)
FROM MOVENDADEVOLUCAO
LEFT JOIN MOVENDAPROD ON MOVENDADEVOLUCAO.CODMOVPROD = MOVENDAPROD.CODMOVPROD
LEFT JOIN MOVENDA ON MOVENDAPROD.CODMOVENDA = MOVENDA.CODMOVENDA
WHERE MOVENDADEVOLUCAO.DATA >= '{startDate}' AND MOVENDADEVOLUCAO.DATA <= '{endDate}' AND MOVENDA.CODVENDED = '{user.codvend}'
"""
returns = con.cursor().execute(query).fetchone()
return movenda[0], returns[0]
def getORCAMENTOPRODCodes(quantity):
codes = []
query = """
SELECT NEXT VALUE FOR SEQ_CODORCPROD FROM RDB$DATABASE;
"""
for num in range(quantity):
code = con.cursor().execute(query).fetchone()[0]
codes.append(f'{code:09}')
return codes
def getComission(user, startDate, endDate):
query = f"""
SELECT COALESCE(SUM(CAST(MOVENDAPROD.VALORTOTAL AS DOUBLE PRECISION) * (CAST(PRODUTO.COMISSAO AS DOUBLE PRECISION) / 100)),0) + COALESCE(SUM((COALESCE((CAST(MOVENDA.VALORACRESCIMO AS DOUBLE PRECISION) / NULLIF((CASE WHEN MOVENDA.VALORTOTALPRODUTOS = 0 THEN CAST(MOVENDA.VALORTOTALNOTA AS DOUBLE PRECISION) ELSE CAST(MOVENDA.VALORTOTALPRODUTOS AS DOUBLE PRECISION) END), 0)),0) - COALESCE((CAST(MOVENDA.VALORDESCONTO AS DOUBLE PRECISION) / NULLIF((CASE WHEN MOVENDA.VALORTOTALPRODUTOS = 0 THEN CAST(MOVENDA.VALORTOTALNOTA AS DOUBLE PRECISION) ELSE CAST(MOVENDA.VALORTOTALPRODUTOS AS DOUBLE PRECISION) END), 0)),0) * CAST(MOVENDAPROD.VALORTOTAL AS DOUBLE PRECISION)) * (CAST(PRODUTO.COMISSAO AS DOUBLE PRECISION) / 100)),0) AS saleFinalComission,
COALESCE(SUM(CAST(MOVENDAPROD.VALORTOTAL AS DOUBLE PRECISION)),0) AS saleBruteTotal,
COALESCE(SUM(CASE WHEN CAST(PRODUTO.COMISSAO AS DOUBLE PRECISION) != 0 THEN CAST(MOVENDAPROD.VALORTOTAL AS DOUBLE PRECISION) END),0) AS saleLiquidTotal,
COALESCE(SUM((COALESCE((CAST(MOVENDA.VALORACRESCIMO AS DOUBLE PRECISION) / NULLIF((CASE WHEN MOVENDA.VALORTOTALPRODUTOS = 0 THEN CAST(MOVENDA.VALORTOTALNOTA AS DOUBLE PRECISION) ELSE CAST(MOVENDA.VALORTOTALPRODUTOS AS DOUBLE PRECISION) END), 0)),0) - COALESCE((CAST(MOVENDA.VALORDESCONTO AS DOUBLE PRECISION) / NULLIF((CASE WHEN MOVENDA.VALORTOTALPRODUTOS = 0 THEN CAST(MOVENDA.VALORTOTALNOTA AS DOUBLE PRECISION) ELSE CAST(MOVENDA.VALORTOTALPRODUTOS AS DOUBLE PRECISION) END), 0)),0) * CAST(MOVENDAPROD.VALORTOTAL AS DOUBLE PRECISION)) * (CAST(PRODUTO.COMISSAO AS DOUBLE PRECISION))),0) AS saleBudgetDifer
FROM MOVENDAPROD
INNER JOIN MOVENDA ON MOVENDAPROD.CODMOVENDA = MOVENDA.CODMOVENDA AND DATA >= '{startDate}' AND DATA <= '{endDate}' AND MOVENDA.CODVENDED = '{user.codvend}'
LEFT JOIN PRODUTO ON MOVENDAPROD.CODPROD = PRODUTO.CODPROD
"""
sales = con.cursor().execute(query).fetchone()
query = f"""
SELECT COALESCE(SUM(((MOVENDAPROD.VALORTOTAL / MOVENDAPROD.QUANTIDADE) * MOVENDADEVOLUCAO.QUANTIDADEDEVOLUCAO + (COALESCE((MOVENDA.VALORACRESCIMO / MOVENDA.VALORTOTALPRODUTOS),0) - COALESCE((MOVENDA.VALORDESCONTO / MOVENDA.VALORTOTALPRODUTOS),0) * MOVENDAPROD.VALORTOTAL)) * (PRODUTO.COMISSAO / 100)),0) AS FINALTOTAL,
COALESCE(SUM((MOVENDAPROD.VALORTOTAL / MOVENDAPROD.QUANTIDADE) * MOVENDADEVOLUCAO.QUANTIDADEDEVOLUCAO),0) AS TOTALRETURNS,
COALESCE(SUM(CASE WHEN PRODUTO.COMISSAO != 0 THEN (MOVENDAPROD.VALORTOTAL / MOVENDAPROD.QUANTIDADE) * MOVENDADEVOLUCAO.QUANTIDADEDEVOLUCAO END),0) AS TOTALRETURNSCOMISSION,
COALESCE(SUM((COALESCE((MOVENDA.VALORACRESCIMO / MOVENDA.VALORTOTALPRODUTOS),0) - COALESCE((MOVENDA.VALORDESCONTO / MOVENDA.VALORTOTALPRODUTOS),0) * MOVENDAPROD.VALORTOTAL) * (PRODUTO.COMISSAO)),0) AS BUDGETVALUE
FROM MOVENDADEVOLUCAO
LEFT JOIN MOVENDAPROD ON MOVENDADEVOLUCAO.CODMOVPROD = MOVENDAPROD.CODMOVPROD
LEFT JOIN PRODUTO ON MOVENDAPROD.CODPROD = PRODUTO.CODPROD
LEFT JOIN MOVENDA ON MOVENDAPROD.CODMOVENDA = MOVENDA.CODMOVENDA
WHERE MOVENDADEVOLUCAO.DATA >= '{startDate}'
AND MOVENDADEVOLUCAO.DATA <= '{endDate}'
AND MOVENDA.CODVENDED = '{user.codvend}'
AND MOVENDA.DATA >= '2021-01-01'
"""
returns = con.cursor().execute(query).fetchone()
return {
'saleFinalComission': float(sales[0]),
'saleBruteTotal': float(sales[1]),
'saleLiquidTotal': float(sales[2]),
'saleBudgetDifer': float(sales[3]),
'returnFinalComission': float(returns[0]),
'returnBruteTotal': float(returns[1]),
'returnLiquidTotal': float(returns[2]),
'returnBudgetDifer': float(returns[3]),
'finalComission': float(sales[0]) - float(returns[0])
}
def basicQueryToDict(sqlQuery):
def convertToText(typeClass, num):
if('string' not in str(typeClass)):
try:
return str(num)
except:
return ''
else:
return num
cur = con.cursor()
cur.execute(sqlQuery)
data = cur.fetchall()
return [{desc[0]:convertToText(desc[1],row[index]) for index, desc in enumerate(cur.description)} for row in data]
def getTopItems(codvend):
start, end = getMonthRange()
query = f"""
SELECT FIRST 10 MOVENDAPROD.DESCRICAOPRODUTO
FROM MOVENDAPROD
INNER JOIN MOVENDA ON MOVENDAPROD.CODMOVENDA = MOVENDA.CODMOVENDA AND DATA >= '2020-10-27' AND DATA <= '2020-11-27' AND MOVENDA.CODVENDED = '{codvend}'
GROUP BY MOVENDAPROD.DESCRICAOPRODUTO
ORDER BY COUNT(*) DESC
"""
res = con.cursor().execute(query).fetchall()
res = [item[0] for item in res]
return res
def queryToDict(sqlQuery, flagAdmin):
def convertToText(typeClass, num):
if ('string' not in str(typeClass)):
try:
return str(num)
except:
return ''
else:
return num
def addExtraInfo(productDict):
try:
price = "{:.2f}".format(float(productDict['PRECO']))
except:
price = "0.00"
return {
'CODPROD': productDict['CODPROD'],
'CODIGO': productDict['CODIGO'],
'QUANTIDADE': '1.0',
'VALORUNITARIO': price,
'PRECOTABELA': price,
'ALIQDESCONTOITEM': '0.00',
'VALORDESCONTOITEM': '0.00',
'ALIQACRESCIMOITEM': '0.00',
'VALORACRESCIMOITEM': '0.00',
'VALORTOTAL': price,
'NOMEPROD': productDict['NOMEPROD'],
'UNIDADE': productDict['UNIDADE'],
'DESCMAXIMO':
productDict['DESCMAXIMO'] if not flagAdmin else 100.0,
'ESTATU': productDict['ESTATU']
}
cur = con.cursor()
cur.execute(sqlQuery)
data = cur.fetchall()
return [
addExtraInfo({
desc[0]: convertToText(desc[1], row[index])
for index, desc in enumerate(cur.description)
}) for row in data
]
def addNewBudget(data, codvend):
start = datetime.datetime.now()
buildedData = buildData(data, codvend)
columnsQuery = '('
valuesQuery = '('
for item in buildedData.items():
columnsQuery += item[0] + ', '
valuesQuery += f"'{item[1]}'" + ', '
columnsQuery = columnsQuery[:-2] + ')'
valuesQuery = valuesQuery[:-2] + ')'
query = f"""
INSERT INTO ORCAMENTO {columnsQuery}
VALUES {valuesQuery}
"""
con.cursor().execute(query)
codes = getORCAMENTOPRODCodes(len(data['products']))
for index, budgetProduct in enumerate(data['products']):
CODORCPROD = {'CODORCPROD': codes[index]}
buildedProductData = { **buildBudgetProductData(budgetProduct, index + 1, buildedData), **CODORCPROD}
columnsQuery = '('
valuesQuery = '('
for item in buildedProductData.items():
columnsQuery += item[0] + ', '
valuesQuery += f"'{item[1]}'" + ', '
columnsQuery = columnsQuery[:-2] + ')'
valuesQuery = valuesQuery[:-2] + ')'
query = f"""
INSERT INTO ORCAMENTOPROD {columnsQuery}
VALUES {valuesQuery}
"""
con.cursor().execute(query)
if(data['fromBudget']):
budgetCode = data['fromBudget']
query = f"""
SELECT DATAFATURADO FROM ORCAMENTO WHERE CODORC = '{budgetCode}'
"""
res = con.cursor().execute(query).fetchone()
if not res[0]:
query = f"""
DELETE FROM ORCAMENTOPROD
WHERE CODORC = '{budgetCode}'
"""
con.cursor().execute(query)
query = f"""
UPDATE ORCAMENTO
SET VALORFRETE = '0'
WHERE CODORC = '{budgetCode}'
"""
con.cursor().execute(query)
con.commit()