def msgInsertField(sSession):
sSession = str(sSession)
sMsg = "Price XX.XX"
dict_sCodes = {}
from dtm_functions import fGetSessionStatus
from dtb_functions import fExecute, fGetSql
iStatus = fGetSessionStatus(sSession)
sFieldName = {
1: 'Shop/Network',
2: 'Product Group',
3: 'product to Product Group',
4: 'Trademark',
5: None
}[iStatus]
sTblName = {
1: 'dicNetwork',
2: 'dicProductGroup',
3: 'dicProductType',
4: 'dicTrademark',
5: None
}[iStatus]
res = fExecute("SELECT MAX(rowid) FROM " + sSession)
iNRows = int(res[0][0]) if len(res) > 0 and res[0][0] is not None else 0
sFilter = ''
if iNRows > 0:
if iStatus == 3:
res = fExecute("SELECT idProductGroup FROM " + sSession +
" WHERE rowid = " + fGetSql(iNRows, 'INT'))
sFilter = 'WHERE idProductGroup = ' + fGetSql(
res[0][0],
'INT') if len(res) > 0 and res[0][0] is not None else ''
if iStatus in range(1, 5):
arr_sValues = fExecute("SELECT rowid, sName FROM " + sTblName + " " +
sFilter)
sMsg = ''
sMsg += '1. Add new ' + sFieldName
dict_sCodes = {"1": "add"}
i = 1
for sValue in arr_sValues:
i = i + 1
sMsg += chr(10) + str(i) + '. ' + sValue[1]
dict_sCodes.update({str(i): sValue[0]})
elif iStatus == 5:
if iNRows > 0:
res = fExecute(
"SELECT a.sParamAmount, a.sParam1, a.sParam2, a.sParam3 FROM dicUnit as a JOIN "
+ sSession +
" as b on a.idProduct = b.idProduct and a.idTradeMark = b.idTrademark"
)
if len(res) > 0:
sMsg += ".."
sMsg += res[0][0] if res[0][0] is not None else ''
sMsg += ".."
sMsg += res[0][1] if res[0][1] is not None else ''
sMsg += ".."
sMsg += res[0][2] if res[0][2] is not None else ''
sMsg += ".."
sMsg += res[0][3] if res[0][3] is not None else ''
dict_sCodes = {}
return {'sMsg': sMsg, 'dict_sCodes': dict_sCodes}
def fSetSessionStatus(sSessionTable, iValue):
from dtb_functions import fExecute, fGetSql
res = fExecute(
"SELECT iStatus FROM tblInsertSessionsStatus WHERE sSessionTable = " +
fGetSql(sSessionTable, 'TEXT'))
if len(res) > 0:
res = fExecute("UPDATE tblInsertSessionsStatus SET iStatus = " +
fGetSql(iValue, 'INT') + " WHERE sSessionTable = " +
fGetSql(sSessionTable, 'TEXT'))
else:
res = fExecute("INSERT INTO tblInsertSessionsStatus VALUES (" +
fGetSql(sSessionTable, 'TEXT') + ", " +
fGetSql(iValue, 'INT') + ")")
return iValue
def fSetDialogStatus(idUser, iStatus):
idUser = int(idUser)
from dtb_functions import fExecute, fGetSql
if len(
fExecute("SELECT iStatus FROM tblDialogStatus WHERE idUser = "******"UPDATE tblDialogStatus SET iStatus = " +
fGetSql(iStatus, 'INT') + " WHERE idUser = "******"INSERT INTO tblDialogStatus VALUES (" +
fGetSql(idUser, 'INT') + ", " +
fGetSql(iStatus, 'INT') + ")")
return
def pDropTempFillTable(sSessionTable):
sSessionTable = str(sSessionTable)
from dtb_functions import fExecute, fDropTable
if len(
fExecute(
"SELECT sCurrentSessionTable FROM tblCurrentInsertSessions WHERE sCurrentSessionTable = '"
+ sSessionTable + "'")) != 0:
res = fExecute(
"UPDATE tblCurrentInsertSessions SET sCurrentSessionTable = NULL WHERE sCurrentSessionTable = '"
+ sSessionTable + "'")
res = fExecute(
"DELETE FROM tblInsertSessionsStatus WHERE sSessionTable = '" +
sSessionTable + "'")
res = fDropTable(sSessionTable)
def fSetCurrentSessionTable(idUser, sSession):
from dtb_functions import fExecute, fGetSql
res = fExecute(
"SELECT sCurrentSessionTable FROM tblCurrentInsertSessions WHERE idUser = "******"UPDATE tblCurrentInsertSessions SET sCurrentSessionTable = " +
fGetSql(sSession, 'TEXT') + " WHERE idUser = "******"INSERT INTO tblCurrentInsertSessions VALUES (" +
fGetSql(idUser, 'INT') + ", " +
fGetSql(sSession, 'TEXT') + ")")
return sSession
def fGetCurrentSessionTable(idUser):
#hypothesis:: if is none, then go to select current session
idUser = int(idUser)
from dtb_functions import fExecute
res = fExecute(
'SELECT sCurrentSessionTable FROM tblCurrentInsertSessions WHERE idUser = '
+ str(idUser))
return str(res[0][0]) if len(res) > 0 and res[0][0] is not None else None
def msgSessions(idUser):
from dtb_functions import fExecute, fGetSql
arr_sSessions = fExecute(
"SELECT name FROM sqlite_master WHERE name LIKE 'temp_TblToFill_" +
str(int(idUser)) + "%'")
sMsg = ''
sMsg += 'Choose your session :' + chr(10)
sMsg += '1. Begin new'
dict_sCodes = {"1": "new"}
i = 1
for sSession in arr_sSessions:
i = i + 1
res = fExecute(
'SELECT MAX(b.sName), MAX(a.dDate) FROM ' +
fGetSql(sSession[0], "TEXT") +
" as a JOIN dicNetwork as b ON a.idNetwork = b.rowid")[0]
sMsg += chr(10) + str(i) + '. ' + str(res[0]) + " [" + str(
res[1]) + "]"
dict_sCodes.update({str(i): sSession[0]})
return {'sMsg': sMsg, 'dict_sCodes': dict_sCodes}
def pPauseTempFillTable(sSessionTable):
from dtb_functions import fExecute
from dtb_functions import fGetSql
iNRows = int(
fExecute("SELECT MAX(rowid) FROM " +
fGetSql(sSessionTable, "TEXT"))[0][0])
if iNRows > 0:
res = fExecute("DELETE FROM " + sSessionTable +
" WHERE fPrice IS NULL AND rowid = " +
fGetSql(iNRows, "INT"))
res = fSetSessionStatus(sSessionTable, None)
res = fExecute(
"UPDATE tblCurrentInsertSessions SET sCurrentSessionTable = NULL WHERE sCurrentSessionTable = "
+ fGetSql(sSessionTable, "TEXT"))
res = fExecute(
"UPDATE tblInsertSessionsStatus SET iStatus = 2 WHERE sSessionTable = "
+ fGetSql(sSessionTable, "TEXT"))
iNRows = int(
fExecute("SELECT COUNT(*) FROM " +
fGetSql(sSessionTable, "TEXT"))[0][0])
if iNRows == 0:
pDropTempFillTable(sSessionTable)
def fCreateTempFillTable(idUser):
idUser = int(idUser)
from dtb_functions import fExecute
sTblName = 'temp_TblToFill_' + str(int(idUser))
arr_records = fExecute("SELECT name FROM sqlite_master WHERE name LIKE '" +
sTblName + "%'")
iTblIndex = len(arr_records)
s_iIndexes = set([0])
s_iIndexes1 = set([1])
i = 0
for arr_record in arr_records:
i = int(arr_record[0].replace(sTblName + '_', ''))
s_iIndexes.add(i)
s_iIndexes1.add(i + 1)
iTblIndex = 0 if iTblIndex == 0 else list(
s_iIndexes1.difference(s_iIndexes))[0]
sTblName = sTblName + '_' + str(iTblIndex)
fExecute('CREATE TABLE ' + sTblName + ' (idUser int' + ', idSession' +
', dDate DATETIME' + ', idNetwork INT' + ', idProductGroup INT' +
', idProduct INT' + ', idTrademark INT' + ', sParam1 TEXT' +
', sParam2 TEXT' + ', sParam3 TEXT' + ', fPrice REAL)')
if len(
fExecute(
'SELECT idUser FROM tblCurrentInsertSessions WHERE idUser = '******'INSERT INTO tblCurrentInsertSessions VALUES (' +
str(idUser) + ', NULL)')
res = fExecute(
"UPDATE tblCurrentInsertSessions SET sCurrentSessionTable = '" +
sTblName + "' WHERE idUser = "******"SELECT sSessionTable FROM tblInsertSessionsStatus WHERE sSessionTable = '"
+ str(sTblName) + "'")) == 0:
res = fExecute("INSERT INTO tblInsertSessionsStatus VALUES ('" +
str(sTblName) + "', NULL)")
res = fExecute(
"UPDATE tblInsertSessionsStatus SET iStatus = 1 WHERE sSessionTable = '"
+ str(sTblName) + "'")
return str(sTblName)
def fFillDictionnary(sSession, sValue):
sSession = str(sSession)
sValue = str(sValue)
from dtm_functions import fGetSessionStatus
from dtb_functions import fExecute, fGetSql
iStatus = fGetSessionStatus(sSession)
sTblName = {
1: 'dicNetwork',
2: 'dicProductGroup',
3: 'dicProductType',
4: 'dicTrademark',
5: 'dicUnit'
}[iStatus]
res = fExecute("SELECT MAX(rowid) FROM " + sSession)
iNRows = int(res[0][0]) if len(res) > 0 and res[0][0] is not None else 0
sAdditionnal = ''
if iNRows > 0:
if iStatus == 3:
res = fExecute("SELECT idProductGroup FROM " + sSession +
" WHERE rowid = " + fGetSql(iNRows, 'INT'))
sAdditionnal = fGetSql(
res[0][0],
'INT') if len(res) > 0 and res[0][0] is not None else 'NULL'
sAdditionnal = ", " + sAdditionnal
elif iStatus == 4:
res = fExecute("SELECT idProduct, idTrademark FROM " + sSession +
" WHERE rowid = " + fGetSql(iNRows, 'INT'))
sAdditionnal5 = ''
sAdditionnal5 = fGetSql(
res[0][0],
'INT') if len(res) > 0 and res[0][0] is not None else 'NULL'
sAdditionnal5 += ", "
sAdditionnal5 += fGetSql(
res[0][1],
'INT') if len(res) > 0 and res[0][1] is not None else 'NULL'
sAdditionnal5 += ", "
if iStatus in range(1, 5):
res = fExecute("INSERT INTO " + sTblName + " VALUES (" +
fGetSql(sValue, 'TEXT') + sAdditionnal + ")")
res = fExecute("SELECT MAX(rowid) FROM " + sTblName)
iNRows = int(
res[0][0]) if len(res) > 0 and res[0][0] is not None else 0
else:
iNRows = 0
if iStatus == 4: # for iStatus = 5's JOIN
print "INSERT INTO dicUnit VALUES (" + sAdditionnal5 + ", NULL, NULL, NULL, NULL)"
res = fExecute("INSERT INTO dicUnit VALUES (" + sAdditionnal5 +
" NULL, NULL, NULL, NULL)")
return iNRows
def fGetSessionStatus(sSessionTable):
from dtb_functions import fExecute
res = fExecute(
"SELECT iStatus FROM tblInsertSessionsStatus WHERE sSessionTable = '" +
str(sSessionTable) + "'")
return int(res[0][0]) if len(res) > 0 and res[0][0] is not None else None
def pFillTempFillTable(sSessionTable, sValue):
sSessionTable = str(sSessionTable)
sValue = str(sValue)
from dtb_functions import fExecute
iStatus = fGetSessionStatus(sSessionTable)
# 0 - таблица дополнена
# 1 - таблица не коректно занесена
# 2
# 3
# 4
# 5 - прочая ошибка
iAnswer = 5
sidUser = sSessionTable.replace("temp_TblToFill_", "")
sidSession = sidUser[sidUser.find("_") + 1:len(sidUser)]
sidUser = sidUser[0:sidUser.find("_")]
if iStatus is None:
iAnswer = 1
elif iStatus == 1:
print type(sSessionTable)
print type(sidUser)
print type(sidSession)
print type(sValue)
res = fExecute("INSERT INTO " + sSessionTable + " VALUES (" + sidUser +
", " + sidSession + ", " + "date('now'), " + "'" +
sValue + "', " + "NULL, " + "NULL, " + "NULL, " +
"NULL, " + "NULL, " + "NULL, " + "NULL)")
elif iStatus == 2:
res = fExecute("SELECT idUser FROM " + sSessionTable +
" WHERE fPrice IS NULL")
iNRows = fExecute("SELECT MAX(rowid) FROM " + sSessionTable)
if len(res) > 0:
res = fExecute("UPDATE " + sSessionTable + " SET " +
"idProductGroup = '" + sValue + "', " +
"idProduct = NULL, " + "idTradeMark = NULL, " +
"sParam1 = NULL, " + "sParam2 = NULL, " +
"sParam3 = NULL" + " WHERE fPrice IS NULL")
else:
iNRows = int(
fExecute("SELECT MAX(rowid) FROM " + sSessionTable)[0][0])
res = fExecute("INSERT INTO " + sSessionTable + " SELECT " +
"idUser, " + "idSession, " + "dDate, " +
"idNetwork, " + "'" + sValue + "', " + "NULL, " +
"NULL, " + "NULL, " + "NULL, " + "NULL, " +
"NULL " + "FROM " + sSessionTable + " " +
"WHERE rowid = " + str(iNRows))
elif iStatus == 3:
res = fExecute("SELECT idUser FROM " + sSessionTable +
" WHERE fPrice IS NULL")
if len(res) > 0:
res = fExecute("UPDATE " + sSessionTable + " SET " +
"idProduct = '" + sValue + "', " +
"idTradeMark = NULL, " + "sParam1 = NULL, " +
"sParam2 = NULL, " + "sParam3 = NULL" +
" WHERE fPrice IS NULL")
else:
iNRows = int(
fExecute("SELECT MAX(rowid) FROM " + sSessionTable)[0][0])
res = fExecute("INSERT INTO " + sSessionTable + " SELECT " +
"idUser, " + "idSession, " + "dDate, " +
"idNetwork, " + "idProductGroup, " + "'" + sValue +
"', " + "NULL, " + "NULL, " + "NULL, " + "NULL, " +
"NULL " + "FROM " + sSessionTable + " " +
"WHERE rowid = " + str(iNRows))
elif iStatus == 4:
res = fExecute("SELECT idUser FROM " + sSessionTable +
" WHERE fPrice IS NULL")
if len(res) > 0:
res = fExecute("UPDATE " + sSessionTable + " SET " +
"idTradeMark = '" + sValue + "', " +
"sParam1 = NULL, " + "sParam2 = NULL, " +
"sParam3 = NULL" + " WHERE fPrice IS NULL")
else:
iNRows = int(
fExecute("SELECT MAX(rowid) FROM " + sSessionTable)[0][0])
res = fExecute("INSERT INTO " + sSessionTable + " SELECT " +
"idUser, " + "idSession, " + "dDate, " +
"idNetwork, " + "idProductGroup, " + "idProduct, " +
"'" + sValue + "', " + "NULL, " + "NULL, " +
"NULL, " + "NULL " + "FROM " + sSessionTable + " " +
"WHERE rowid = " + str(iNRows))
elif iStatus == 5:
res = fExecute("SELECT idUser FROM " + sSessionTable +
" WHERE fPrice IS NULL")
arr_sValue = sValue.split("..")
arr_sValue.extend(["NULL", "NULL", "NULL"])
if len(res) > 0:
res = fExecute("UPDATE " + sSessionTable + " SET " +
"sParam1 = '" + arr_sValue[1] + "', " +
"sParam2 = '" + arr_sValue[2] + "', " +
"sParam3 = '" + arr_sValue[3] + "', " +
"fPrice = replace('" + arr_sValue[0] +
"', ',', '.')" + " WHERE fPrice IS NULL")
else:
iNRows = int(
fExecute("SELECT MAX(rowid) FROM " + sSessionTable)[0][0])
res = fExecute("INSERT INTO " + sSessionTable + " SELECT " +
"idUser, " + "idSession, " + "dDate, " +
"idNetwork, " + "idProductGroup, " + "idProduct, " +
"idTrademark, " + "'" + arr_sValue[1] + "', " +
"'" + arr_sValue[2] + "', " + "'" + arr_sValue[3] +
"', " + "replace('" + arr_sValue[0] +
"', ',', '.')" + "FROM " + sSessionTable + " " +
"WHERE rowid = " + str(iNRows))
res = fSetSessionStatus(sSessionTable, iStatus + 1 if iStatus < 5 else 4)
def fSetIdUpdate(idUpdate):
from dtb_functions import fExecute
idUpdate=str(int(idUpdate))
res=fExecute("UPDATE tblOptionsParam SET sValue = " + idUpdate + " WHERE sParam='idUpdate'")
return
def fGetDialogStatus(idUser):
idUser = int(idUser)
from dtb_functions import fExecute
res = fExecute("SELECT iStatus FROM tblDialogStatus WHERE idUser = " +
str(idUser))
return int(res[0][0]) if len(res) > 0 and res[0][0] is not None else None
# файл dtm_functions - функции, работающие с потоками данных
# таблица с текущими сессиями
from dtb_functions import fExecute, fSelect
# Исходные таблицы
fExecute("CREATE TABLE dicProductGroup (sName TEXT)")
fExecute("CREATE TABLE dicNetwork (sName TEXT)")
fExecute("CREATE TABLE dicProductType (sName TEXT, idProductGroup INTEGER)")
fExecute("CREATE TABLE dicTrademark (sName TEXT)")
fExecute(
"CREATE TABLE dicUnit (idProduct INTEGER, idTrademark INTEGER, sParamAmount TEXT, sParam1 TEXT, sParam2 TEXT, sParam3 TEXT)"
)
fExecute("CREATE TABLE tblOptionsParam (sPAram TEXT, sValue TEXT)")
#наполнение:
fExecute("INSERT INTO tblOptionsPAram VALUES ('idUpdate', '0')")
# текущие сессии
fExecute(
"CREATE TABLE tblCurrentInsertSessions (idUser INT, sCurrentSessionTable TEXT)"
)
# Таблица со статусами сессий:
#
# 0 меню сессии
# 1 запрос: меню сети
# 2 запрос: меню секции
# 3 запрос: меню типа продукта
# 4 запрос: меню брэнда
# 5 запрос: комплектация и запись
fExecute(
'CREATE TABLE tblInsertSessionsStatus (sSessionTable TEXT, iStatus INT)')
print i[1]
pDropTempFillTable(i[1])
from dlg_functions import fGetDialogStatus, fSetDialogStatus
print fGetDialogStatus(104739991)
print fGetDialogStatus(212821593)
fSetDialogStatus(104739991, None)
fSetDialogStatus(212821593, None)
from bot_functions import fGetUpdates, fSetIdUpdate
for i in fGetUpdates(0): print i['idUpdate']
for i in fGetUpdates(0): fSetIdUpdate(i['idUpdate'])
fSetIdUpdate(119910354)
fExecute("DELETE FROM dicNetwork")
fExecute("DELETE FROM dicProductGroup")
fExecute("DELETE FROM dicProductType")
fExecute("DELETE FROM dicTrademark")
fExecute("DELETE FROM dicUnit")
# =====================================================
# garbage
# =====================================================
from dtm_functions import fGetCurrentSessionTable
from msg_generator import msgInsertField
print msgInsertField(fGetCurrentSessionTable(212821593))['sMsg']
from dtb_functions import fSelect
def fGetIdUpdate():
from dtb_functions import fExecute
return int(fExecute("SELECT sValue FROM tblOptionsPAram WHERE sParam='idUpdate'")[0][0])
