class StorageType(AvtukObject):
'''Доп.информация по сборочным фактурам'''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_storage_type'
id = Col(primary=True)
name = Col(name='name', length=64) # Название
class Role(AvtukObject):
''' Справочник ролей '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_roles'
id = Col(primary=True)
name = Col(length=50)
device = Col() # Наличие терминала сбора данных
def set_modules(self, modules_on, modules_off):
if modules_off:
sql = '''DELETE FROM SW_ROLE_MODULE
WHERE ROLE_ID=%s AND MODULE_ID IN %s''' % (
self.id, tuple(modules_off))
Executor.exec_sql(sql, alias=self.__alias__)
sql = '''MERGE INTO SW_ROLE_MODULE
USING DUAL ON (ROLE_ID=:role AND MODULE_ID=:module)
WHEN NOT matched THEN
INSERT(ROLE_ID,MODULE_ID) VALUES(:role, :module)'''
for i in modules_on:
Executor.exec_sql(sql,
alias=self.__alias__,
role=self.id,
module=i)
class TypeTask(AvtukObject):
''' Типы заданий работникам склада '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_types_task'
id = Col(primary=True, default=Sequence('sqSW_TYPES_TASK'))
name = Col(length=50) # Название
class Status(AvtukObject):
''' Статус Header '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'status'
id = Col(primary=True, default=Sequence('sqHEADER'))
name = Col(length=30)
class ExportCell(AvtukObject):
''' Список изменений ячеек склада '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'vw_export_cell'
str = Col()
begin_date = Col() # Постановка задачи
class Marsh(AvtukObject):
''' Статус Header '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'mess_for_header'
id = Col(primary=True) # , default=Sequence('sqHEADER'))
name = Col(length=30)
class Oper(AvtukObject):
''' Справочник операций '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'oper'
id = Col(name='oper', primary=True, default=Sequence('sqOper'))
name = Col(length=50) # Название
class TypeSmena(AvtukObject):
''' Справочник типов смен'''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'tsmena'
id = Col(name='tsmena', primary=True, default=Sequence('SQTSMENA'))
name = Col(length=30) # Наименование
class Region(AvtukObject):
'''Города клиентов'''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'region'
id = Col(name='region', primary=True)
name = Col(length=30) # Наименование
class Bank(AvtukObject):
''' Справочник отчетов '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'bank'
id = Col(primary=True, default=Sequence('sqBank'))
client = Col()
adress = Col(length=100)
phone = Col(length=50)
class PalletHistory(AvtukObject):
''' История перемещения палет '''
__alias__ = 'tehno'
__live__ = 10
__table__ = 'palet_place'
id = Col(primary=True, default=Sequence('sqPALET_PLACE'))
header = Col() # Фактура
pallet = Col(name='palete') # Палета
pallet_cls = Reference(
'Pallet', condition='PalletHistory.pallet = Pallet.id') # Палеты
class Factura(AvtukObject):
''' Состав заказа '''
__alias__ = 'tehno'
__live__ = 600
__table__ = 'factura'
id = Col(primary=True, default=Sequence('sqFactura'))
header = Col() # Код заголовка
header_cls = Reference(Header, condition='Factura.header = Header.id')
tovar = Col() # Код товара
tovar_cls = Reference(Tovar, condition='Factura.tovar = Tovar.id')
count = Col(name='valume') # Количество
class Pallet(AvtukObject):
''' Список палет '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_palete'
id = Col(primary=True, default=Sequence('sqSW_PALETE'))
party = Col(name='party_id') # партия
party_cls = Reference('Party',
condition='Pallet.party = Party.id') # Партия
count = Col(name='valume', default=0) # Количество товара
depart = Col() # Код отдела
depart_cls = Reference(
Department, condition='Pallet.depart = Department.id') # Подразделение
class Cell(AvtukObject):
''' Справочник мест хранения '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'tovar_place'
__order__ = 'stelag, polka, place'
id = Col(primary=True, default=Sequence('sqTOVAR_PLACE'))
stelag = Col() # стеллаж
polka = Col() # полка в высоту
place = Col() # ячейка
depart = Col() # Код отдела
depart_cls = Reference(
Department, condition='Cell.depart = Department.id') # Подразделение
class RC(AvtukObject):
''' Справочник РЦ '''
__alias__ = 'tehno'
__live__ = 0 #3600
__table__ = 'distribution_center'
id = Col(primary=True)
name = Col(length=250)
depart_cls = Reference('Department',
condition='RC.id = Department.rc',
multi=True)
@classmethod
def get_current(cls, rc):
return cls.get(id=rc)
class ExtraPartyTemp(AvtukObject):
''' распечатанные ID партий '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'extra_party'
id = Col(primary=True, default=Sequence('sqExtra_party'))
class Tovar(AvtukObject):
''' Справочник товаров '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'tovar'
id = Col(primary=True, default=Sequence('sqTovar'))
name = Col(length=80) # Название
typet = Col() # Тип товара
typet_cls = Reference('TypeTovar', condition='Tovar.typet = TypeTovar.id')
code = Reference(None, generator='gen_code')
def gen_code(self):
return 'select SUBSTR(isclad.GetTovarCodeFromModify(:id), 1, 25) from dual', {
'id': self.id,
'alias': self.__alias__
}
class TypeTovar(AvtukObject):
''' Тип товара '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'type_tovar'
id = Col(primary=True, default=Sequence('sqTypeTovar'))
name = Col(length=50)
@classmethod
def select_life(cls):
# sql = 'SELECT id FROM type_tovar WHERE dep IS NOT NULL AND hide=1 AND category not in (5,6)'
sql = '''SELECT id FROM type_tovar WHERE dep IS NOT NULL AND hide=1 AND id not in (8, 54,56,58,60,61, 77,78,79, 110,111, 112, 116, 120,125,127,128,129,130,131,132,133,134,135,136,300000201,300002201)'''
# sql = 'SELECT id FROM type_tovar WHERE hide=1'
return cls.select(sql=sql)
class Department(AvtukObject):
''' Справочник участков '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'depart'
id = Col(name='depart', primary=True, default=Sequence('SQDEPART'))
name = Col(length=50)
rc = Col()
rc_cls = Reference('RC', condition='RC.id = Department.rc') # РЦ
# #all_users_cls = Reference('User', condition='User.depart = Department.id', doc='Все сотрудники подразделения', multi=True)
users_cls = Reference('User', generator='gen_user_cls', multi=True)
def gen_user_cls(self):
return '''SELECT *
FROM sotrud
WHERE sotrud.depart = :dep AND sotrud.VISIBLE=1
ORDER BY name''', {
'dep': self.id
}
class Period(AvtukObject):
''' Список смен '''
__alias__ = 'tehno'
__live__ = 0 # 60
__table__ = 'period'
id = Col(name="period", primary=True, default=Sequence('SQPERIOD'))
date = Col(name='data', default=datetime.datetime.now) # Дата начала смены
date_end = Col(name='data_end') # Дата окончания смены
tsmena = Col(default=1) # Тип смены
tsmena_cls = Reference(
'TypeSmena', condition='Period.tsmena = TypeSmena.id') # Тип смены
rc = Col() # Код РЦ
current_tasks = Reference('Task', generator='gen_tasks_cls',
multi=True) # Текущие задания
def gen_tasks_cls(self):
t2 = (self.date -
datetime.timedelta(hours=1)).strftime('%Y-%m-%d %H:%M:%S')
if config.FOR_TESTING:
return '''SELECT * FROM sw_task
WHERE expose_task >= TO_DATE('%s' ,'YYYY-MM-DD HH24:MI:SS') AND end_task IS NULL AND for_testing = 1
ORDER BY id''' % t2, {}
else:
return '''SELECT * FROM sw_task
WHERE expose_task >= TO_DATE('%s' ,'YYYY-MM-DD HH24:MI:SS') AND end_task IS NULL AND for_testing IS NULL
ORDER BY id''' % t2, {}
@classmethod
def get_last_smen(cls, rc):
sql = '''SELECT * FROM period
WHERE data=(SELECT max(data) FROM period
WHERE data_end IS NULL AND rc=:rc)
AND rc=:rc'''
return cls.get(sql=sql, rc=rc)
class Client(AvtukObject):
''' Справочник клиентов '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'client'
id = Col(primary=True, default=Sequence('sqClient'))
name = Col(length=50) # Наименование
bank_cls = Reference('Bank', condition='Client.id = Bank.client') # адрес
region = Col()
region_cls = Reference('Region',
condition='Client.region = Region.id') # город
sender = Col()
pClient = None
# def __init__(self):
# pass
@classmethod
def get(self, pClient=None):
sql = '''SELECT decode(c.prim,null,c.name,c.prim) name_full,
b.inn,b.account,b.bank,b.korr,b.bic,okpo,b.okonh,b.phone,b.dogovorn,b.dogovork,
b.accountant,b.chief,c.name,c.adress_ur,b.adress
from client c
left join bank b on c.id=b.client
WHERE c.id=:pClient'''
self.pClient = pClient
result = Executor.exec_cls(sql, pClient=self.pClient, multi=False)
if result:
return result.as_json()
return
class Menu(AvtukObject):
''' Список групп модулей '''
__alias__ = 'tehno'
__live__ = 0
__table__ = 'sw_menu'
id = Col(primary=True)
name = Col(length=20) # url меню
caption = Col(length=20) # Заголовок меню
def modules_by_role(self, role):
'''Разрешенные модули для http-меню'''
sql = '''SELECT s.ID, s.CAPTION, s.DESCRIPTION, '/'||m.NAME||'/'||s.NAME url
FROM SW_MODULE s
JOIN sw_menu m ON s.SW_MENU_ID=m.ID AND m.NAME = :menu
WHERE :role = 1 OR
s.id IN ( SELECT MODULE_ID
FROM SW_ROLE_MODULE
WHERE ROLE_ID = :role )'''
return [
k.as_json() for k in Executor.exec_cls(
sql, alias=self.__alias__, menu=self.name, role=role)
]
class Task(AvtukObject):
''' История заданий работникам склада '''
__alias__ = 'tehno'
__live__ = 0 # 5
__table__ = 'sw_task'
id = Col(primary=True, default=Sequence('sqSW_TASK'))
sotrud = Col() # Сотрудник
sotrud_cls = Reference('User',
condition='Task.sotrud = User.id') # Сотрудник
types_task = Col(name='id_types_task') # Тип задачи
types_task_cls = Reference('TypeTask',
condition='Task.types_task = TypeTask.id'
) # Типы заданий работникам склада
expose_task = Col() # Постановка задачи
begin_task = Col() # Начало исполнения
end_task = Col() # Окончание исполнения
delegate_task = Col()
id1 = Col()
id2 = Col()
class Report(AvtukObject):
''' Справочник отчетов '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_reports'
id = Col(primary=True, default=Sequence('sqSW_REPORTS'))
name = Col(length=80) # Название
descript = Col() # (CLOB) Описание отчета
params = Col() # (CLOB) Спек параметров
src = Col() # (CLOB) Исходник отчета
rights = Col() # Права: NULL-все, 0-свой РЦ, 1-свой отдел
class ListAssembly(AvtukObject):
'''Сборочный лист'''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_header_recordp'
id = Col(primary=True, default=Sequence('sqsw_header_recordp'))
header = Col()
depart = Col()
sotrud = Col()
sotrudp = Col()
data = Col()
class RoleHistory(AvtukObject):
''' История ролей по сменам '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_role_history'
id = Col(primary=True, default=Sequence('sqSW_ROLE_HISTORY'))
sotrud = Col()
period = Col()
role_begin = Col(default=datetime.datetime.now) # Время начала роли
role_end = Col() # Время окончания роли
role = Col()
class Party(AvtukObject):
''' Партии товара '''
__alias__ = 'tehno'
__live__ = 10
__table__ = 'party'
id = Col(name='party', primary=True, default=Sequence('sqParty'))
tovar = Col() # Код товара
tovar_cls = Reference('Tovar', condition='Party.tovar = Tovar.id') # Товар
date = Col(name='data') # Срок годности
num = Col()
inbox = Col()
d_iss = Col()
class Module(AvtukObject):
''' Список модулей '''
__alias__ = 'tehno'
__live__ = 3600
__table__ = 'sw_module'
id = Col(primary=True)
name = Col(length=20) # url модуля
caption = Col(length=20) # Заголовок модуля
description = Col(length=50) # Описание модуля
menu = Col(name='sw_menu_id') # Меню
menu_cls = Reference(Menu, condition='Module.menu = Menu.id') # Меню
help = Col()
def use_role(self, role_id):
pass
class Header(AvtukObject):
''' Заголовок фактур '''
__alias__ = 'tehno'
__live__ = 5
__table__ = 'header'
id = Col(primary=True, default=Sequence('sqHEADER'))
oper = Col() # Код операции
oper_cls = Reference('Oper', condition='Header.oper = Oper.id') # Операция
stage = Col() # Стадия фактуры
num = Col(length=10) # Символьный номер
numb = Col() #
numb_in = Col() #
date = Col(name='data') # Дата
status = Col(name='status') # статус
status_cls = Reference('Status', condition='Header.status = Status.id')
client_from = Col(name='client_from') # Код клиента продавца
client_from_cls = Reference(
'Client',
condition='Header.client_from = Client.id') # Клиент продавец
client_to = Col(name='client_to') # Код клиента покупателя
client_to_cls = Reference(
'Client',
condition='Header.client_to = Client.id') # Клиент покупатель
os_numb = Col(name='os_numb') # номер отправки
pallet = Col(name='pallet')
pallet_cls = Reference(
'PastingType', condition='Header.pallet=PastingType.id') # тип оклейки
PalletHistory_cls = Reference(PalletHistory,
condition='PalletHistory.header = Header.id',
multi=True) # Все сотрудники подразделения
marsh = Col() #
marsh_cls = Reference('Marsh', condition='Header.marsh = Marsh.id')
storage = Col()
prim = Col() #
desk = Col() #
skin = Col() #
@classmethod
def get_item(cls, item_id=0, rc=None):
#sql = '''SELECT h.id, h.oper, h.stage, h.num, h.numb, h.numb_in, h.data, h.status, h.client_from, h.client_to,
# h.marsh, h.prim, ms.name marsh_name, h.storage, h.os_numb, h.desk, h.prim, h.skin
# FROM header h
# left join mess_for_header ms on ms.id=h.marsh
# JOIN depart ON depart.depart IN (SELECT depart FROM depart WHERE rc = :rc AND depart_type = 3)
# WHERE depart.depart IN (SELECT depart FROM shema WHERE oper=h.oper) AND h.id =:item_id'''
sql = '''select h.id, h.oper, h.stage, h.num, h.numb, h.numb_in, h.data, h.status, h.client_from, h.client_to,
h.marsh, h.prim, ms.name marsh_name, h.storage, h.os_numb, h.desk, h.prim, h.skin
from header h
left join mess_for_header ms
on ms.id=h.marsh
where h.id =:item_id'''
return cls.select(sql=sql,
item_id=item_id) # , depart_type=depart_type)
@classmethod
def _custom_select(cls, depart_type, opers, rc=None):
'''
:depart_type 1-приемка, 3-сборка, 6-БТК, 31- Участок приемки сырья
:opers дополнительный WHERE
'''
sql = '''SELECT h.id, h.oper, h.stage, h.num, h.numb_in, h.data, h.status, h.pallet, h.os_numb, h.client_from, h.client_to, h.marsh, h.prim, h.storage
FROM header h
JOIN shema ON h.oper=shema.oper AND h.stage=shema.stage
JOIN depart ON depart.depart IN (SELECT depart FROM depart WHERE rc = :rc AND depart_type = :depart_type )
WHERE (shema.depart =depart.depart)'''
if int(depart_type) == 1:
sql = sql + ' AND (SELECT COUNT(*) FROM header_input WHERE headinput=h.id)=0 '
# Без розничных продаж и подарков
if int(opers) == 1:
sql = sql + ' AND h.oper NOT IN (233,245,277,276)'
# Только розничные продажи
elif int(opers) == 2:
sql = sql + ' AND h.oper in (233,245)'
# Только подарки клиентам
elif int(opers) == 3:
sql = sql + ' AND h.oper in (277,276)'
return cls.select(sql=sql, rc=rc, depart_type=depart_type)
@classmethod
def select_BTK(cls, opers, rc=None):
return cls._custom_select(6, opers, rc)
@classmethod
def select_accept(cls, opers, rc=None):
return cls._custom_select(1, opers, rc)
#@classmethod
#def select_assembly(cls, opers, rc=None):
# return cls._custom_select(3, opers, rc)
@classmethod
def select_passport(cls, opers, data_start, data_end, rc=None):
sql = '''SELECT h.id, h.oper, h.stage, h.num, h.data, h.status, h.client_from, h.client_to
FROM header h
JOIN depart ON depart.depart IN (SELECT depart FROM depart WHERE rc = :rc AND depart_type = 3)
WHERE depart.depart IN (SELECT depart FROM shema WHERE oper=h.oper) AND h.data BETWEEN :ds AND :de'''
# AND h.id = '268231899' OR h.id = '268165799'
# Без розничных продаж и подарков
if opers == 1:
sql = sql + ' AND h.oper NOT IN (233,245,277,276)'
# Только розничные продажи
elif opers == 2:
sql = sql + ' AND h.oper in (233,245)'
# Только подарки клиентам
elif opers == 3:
sql = sql + ' AND h.oper in (277,276)'
return cls.select(sql=sql, rc=rc, ds=data_start, de=data_end)
class User(AvtukObject):
''' Список сотрудников '''
__alias__ = 'tehno'
__live__ = 0 #120
__table__ = 'sotrud'
id = Col(primary=True, default=Sequence('SQSOTRUD'))
sotrud = Col(length=20)
name = Col(length=50)
password = Col(length=50)
visible = Col(default=1, show=lambda c, val: '' if val else 'уволен')
depart = Col()
depart_cls = Reference(Department, condition='User.depart = Department.id')
role = Col()
role_cls = Reference('Role',
condition='User.role = Role.id') # Основная роль
current_role_cls = Reference('Role', generator='get_current_role')
role = Col()
def get_current_role(self):
return '''SELECT * FROM sw_roles
WHERE id = ( SELECT UNIQUE CASE
WHEN s.role=1 THEN s.role
WHEN h.role IS NOT NULL THEN h.role
WHEN s.role IS NOT NULL THEN s.role
ELSE NULL
END
FROM sotrud s
LEFT JOIN sw_role_history h ON s.id=h.sotrud AND h.role_end IS NULL
WHERE s.id = :id AND ROWNUM<=1 )''', {
'id': self.id
}
@classmethod
def get_auth(cls, barcode, login='', passw=''):
'''
Авторизация.
Проверяем по login, passw или по barcode и принадлежности к текущему РЦ.
Потом или принадлежность к начальству или к текущей смене
'''
kw = {}
# sql = 'SELECT * FROM sotrud'
# user = Executor.exec_cls(sql)
# print 'hello'
sql = '''SELECT s.id, s.sotrud, s.name, s.depart, s.password, s.visible, s.ip, s.role, d.rc
FROM sotrud s, depart d
WHERE s.visible=1 AND s.depart = d.depart'''# AND d.rc = :crc'''
if login:
sql = sql + ' AND s.sotrud = :sotrud AND s.password = :password'
kw['sotrud'] = str(login).upper()
kw['password'] = md5passw(kw['sotrud'], str(passw))
elif barcode:
barcode = barcode.replace("\n\r", "")
sql = sql + ' AND s.depart = :depart AND s.id = :id'
kw['depart'], kw['id'] = barcode2depart_sid(barcode)
else:
return None
user = Executor.exec_cls(sql, multi=False, cls=cls, **kw)
# юзер не найден
if not user:
raise NotUserException()
return user