def print_internal_transfer(self): sql = '''select c.name,s.name sname,b.okpo,b.okud,b.chief from client c join sklads s on c.contract=s.enterprise join bank b on b.client=c.id where s.type||s.contract=(select contract from client where id=:pClient)''' client = Executor.exec_cls(sql, pClient=self.header["client_to"], multi=False).as_json() # Номер header.numb_in # Дата tsclad.datatovarheader(header.id) try: datatovarheader = self.func('tsclad.datatovarheader', returnType=cx_Oracle.DATETIME, parameters=[self.head]) ot = datatovarheader.strftime("%d.%m.%Y") except: ot = "" sql = '''select id,name,npack,valume,price,price*valume summ from (select f.id,t.name,p.n_short npack,f.valume,tsclad.GetOurProviderOrPrice(t.id,h.data,1) price from factura f join header h on h.id=f.header join tovar t on t.id=isclad.GetTovarFromModify(f.tovar) left join pack p on p.id=t.pack where h.id=:pHeader) order by name''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) m_summ_words = pytils.numeral.rubles( sum(item.values["summ"] for item in result.data)) loader = template.Loader(TEMPLATE_DIR) output = loader.load("internal_transfer_all.html").generate( data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, client=client, ot=ot, m_summ_words=m_summ_words) output = output.replace('\n', '').replace('\r', '') # f = open('text.html', 'w+') # f.write(output) # f.close() # return {'info':'done'} return { 'cmd': '''self.Incunable(function(doc){ doc.write('%s') })''' % output }
def __init__(self, input=None, rc=None): # .data[0].values self.head = int(input["head"]) m_header = Header.get_item(item_id=self.head, rc=rc) preheader = m_header.as_json( 'id', 'num', 'numb', 'numb_in', 'date', 'client_to', 'client_from', 'client_to_cls.name', 'client_from_cls.name', show={'date': lambda val: val.strftime("%d.%m.%Y")}) # preheader = m_header.as_json(show={'date':lambda val: val.strftime("%d.%m.%Y")}) if not preheader: return self.header = preheader[0] self.client_from = Client.get(pClient=self.header["client_from"]) self.client_to = Client.get(pClient=self.header["client_to"]) sql = "select needre from client where id=:client_to" res = Executor.exec_cls(sql, client_to=self.header["client_to"], multi=False).as_json() if res["needre"] == "*": sql = '''select t.name, t2.nameser||' № '||t2.numser name1, decode(trunc(t2.datas),'01.01.1900','Без срока',trunc(t2.datas)) datas, t2.vidal from tovar t join tovar t2 on t.sert=t2.id join factura f on isclad.GetTovarFromModify(f.tovar)=t.id where f.header=:pHeader union select t.name, t2.nameser||' № '||t2.numser name1, decode(trunc(t2.datas),'01.01.1900','Без срока',trunc(t2.datas)) datas, t2.vidal from factura f join recordt r on r.factura=f.id join tovar t on r.tovar=t.id join tovar t2 on t.sert=t2.id where f.header=:pHeader''' self.needre = Executor.exec_cls(sql, pHeader=self.head, multi=True).as_json() pass
def print_internal_transfer(self): sql = '''select c.name,s.name sname,b.okpo,b.okud,b.chief from client c join sklads s on c.contract=s.enterprise join bank b on b.client=c.id where s.type||s.contract=(select contract from client where id=:pClient)''' client = Executor.exec_cls(sql, pClient=self.header["client_to"], multi=False).as_json() # Номер header.numb_in # Дата tsclad.datatovarheader(header.id) try: datatovarheader = self.func('tsclad.datatovarheader', returnType=cx_Oracle.DATETIME, parameters=[self.head]) ot = datatovarheader.strftime("%d.%m.%Y") except: ot = "" sql = '''select id,name,npack,valume,price,price*valume summ from (select f.id,t.name,p.n_short npack,f.valume,tsclad.GetOurProviderOrPrice(t.id,h.data,1) price from factura f join header h on h.id=f.header join tovar t on t.id=isclad.GetTovarFromModify(f.tovar) left join pack p on p.id=t.pack where h.id=:pHeader) order by name''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) m_summ_words = pytils.numeral.rubles(sum(item.values["summ"] for item in result.data)) loader = template.Loader(TEMPLATE_DIR) output = loader.load("internal_transfer_all.html").generate(data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, client=client, ot=ot, m_summ_words=m_summ_words) output = output.replace('\n', '').replace('\r', '') # f = open('text.html', 'w+') # f.write(output) # f.close() # return {'info':'done'} return {'cmd':'''self.Incunable(function(doc){ doc.write('%s') })''' % output}
def __init__(self, input=None, rc=None): # .data[0].values self.head = int(input["head"]) m_header = Header.get_item(item_id=self.head, rc=rc) preheader = m_header.as_json( 'id', 'num', 'numb', 'numb_in', 'date', 'client_to', 'client_from', 'client_to_cls.name', 'client_from_cls.name', show={'date': lambda val: val.strftime("%d.%m.%Y")}) # preheader = m_header.as_json(show={'date':lambda val: val.strftime("%d.%m.%Y")}) if not preheader: return self.header = preheader[0] self.client_from = Client.get(pClient=self.header["client_from"]) self.client_to = Client.get(pClient=self.header["client_to"]) sql = "select needre from client where id=:client_to" res = Executor.exec_cls(sql, client_to=self.header["client_to"], multi=False).as_json()
def as_print(self): sql = '''select id,tovar,factura,valume,to_char(mesto) mesto,weight,sotrud, sotrudp,code,prim,name,box,part from( select pl.id,pl.tovar,pl.factura,abs(pl.valume) valume, case when m_start=m_end then to_char(m_start) when mesto<>0 then to_char(mesto) else to_char(m_start)||'-'||to_char(m_end) end mesto, pl.weight,pl.sotrud,pl.sotrudp,t.code,pl.prim,t.name,pl.part,pl.box from packlist pl join tovar t on pl.tovar=t.id and pl.factura=:pHeader order by part,m_start,pl.mesto)''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total_valume = sum(d.values["valume"] or 0 for d in result.data) total_weight = sum(d.values["weight"] or 0 for d in result.data) loader = template.Loader(TEMPLATE_DIR) output = loader.load("package.html").generate(data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, total_valume=total_valume, total_weight=total_weight) output = output.replace('\n', '').replace('\r', '') # return output # output = 'hello' return {'cmd':'''self.Incunable(function(doc){ doc.write('%s') })''' % output}
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
def print_way_bill(self): sql = '''SELECT ID,HEADER,TOVAR,VALUME,PRICE,POINT POINT,DISCOUNT,KOEFF,PRICENDS, SFMONEY,SMONEY,DMONEY,PRICEWNDS,SPOINT,KPACK,SPACK,SITOGOWNDS,NDS,SITOGO, PLC,PNMB+P_NEW PNMB,RONMB RONMB,RONPP RONPP,KOMMB KOMMB,SPLC,SPNMB,CODE,NTOVAR, WEIGHT,BUHPRICE,BUHSITOGOWNDS,NO_NDS,CTOVAR,sclad.GetPrice(wf.tovar,'R') pricer, our_provider_price,price_kz FROM tehno.W_Factura wf WHERE Header = :pHeader ORDER BY ntovar''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total = sum(d.values["valume"] for d in result.data) loader = template.Loader(TEMPLATE_DIR) output = loader.load("waybill.html").generate(data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, total=total) output = output.replace('\n', '').replace('\r', '') return {'cmd':'''self.Incunable(function(doc){ doc.write('%s') })''' % output}
def print_way_bill(self): sql = '''SELECT ID,HEADER,TOVAR,VALUME,PRICE,POINT POINT,DISCOUNT,KOEFF,PRICENDS, SFMONEY,SMONEY,DMONEY,PRICEWNDS,SPOINT,KPACK,SPACK,SITOGOWNDS,NDS,SITOGO, PLC,PNMB+P_NEW PNMB,RONMB RONMB,RONPP RONPP,KOMMB KOMMB,SPLC,SPNMB,CODE,NTOVAR, WEIGHT,BUHPRICE,BUHSITOGOWNDS,NO_NDS,CTOVAR,sclad.GetPrice(wf.tovar,'R') pricer, our_provider_price,price_kz FROM tehno.W_Factura wf WHERE Header = :pHeader ORDER BY ntovar''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total = sum(d.values["valume"] for d in result.data) loader = template.Loader(TEMPLATE_DIR) output = loader.load("waybill.html").generate( data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, total=total) output = output.replace('\n', '').replace('\r', '') return { 'cmd': '''self.Incunable(function(doc){ doc.write('%s') })''' % output }
def __init__(self, input=None, rc=None): # .data[0].values self.head = int(input["head"]) m_header = Header.get_item(item_id=self.head, rc=rc) preheader = m_header.as_json('id', 'num', 'numb', 'numb_in', 'date', 'client_to', 'client_from', 'client_to_cls.name', 'client_from_cls.name', show={'date':lambda val: val.strftime("%d.%m.%Y")}) # preheader = m_header.as_json(show={'date':lambda val: val.strftime("%d.%m.%Y")}) if not preheader: return self.header = preheader[0] self.client_from = Client.get(pClient=self.header["client_from"]) self.client_to = Client.get(pClient=self.header["client_to"]) sql = "select needre from client where id=:client_to" res = Executor.exec_cls(sql, client_to=self.header["client_to"], multi=False).as_json() if res["needre"] == "*": sql = '''select t.name, t2.nameser||' № '||t2.numser name1, decode(trunc(t2.datas),'01.01.1900','Без срока',trunc(t2.datas)) datas, t2.vidal from tovar t join tovar t2 on t.sert=t2.id join factura f on isclad.GetTovarFromModify(f.tovar)=t.id where f.header=:pHeader union select t.name, t2.nameser||' № '||t2.numser name1, decode(trunc(t2.datas),'01.01.1900','Без срока',trunc(t2.datas)) datas, t2.vidal from factura f join recordt r on r.factura=f.id join tovar t on r.tovar=t.id join tovar t2 on t.sert=t2.id where f.header=:pHeader''' self.needre = Executor.exec_cls(sql, pHeader=self.head, multi=True).as_json() pass
def print_bill_and_factura(self): client = Client.get(pClient=DEFAULT_CLIENT) # № - header.numb # "от:" - tsclad.datatovarheader(header.id ot = self.func('tsclad.datatovarheader', returnType=cx_Oracle.STRING, parameters=[self.head]) #ot = self.func('tsclad.datatovarheader', returnType=cx_Oracle.DATETIME, parameters=[self.head]).strftime("%d.%m.%Y") sql = '''select id,code,name,valume,round(summ_nds*100/118/valume,2) price,summ_nds,round(summ_nds*18/118,2) nds, round(summ_nds*100/118,2) summ,npack from (select f.id,t.code,t.name,p.n_short npack,f.valume,tsclad.GetFacturaBuhPrice(f.id) price, tsclad.GetFacturaBuhPrice(f.id)*f.valume summ_nds from factura f join tovar t on t.id=isclad.GetTovarFromModify(f.tovar) left join pack p on p.id=t.pack where header=:pHeader) order by name''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total = 0 try: total = sum(d.values["valume"] for d in result.data) except: self.write({'error': 'Ошибка в товарной накладной'}) return loader = template.Loader(TEMPLATE_DIR) m_total = pytils.numeral.in_words(len(result.data)) m_summ_words = pytils.numeral.rubles( sum(item.values["summ_nds"] for item in result.data)) # factura # waybill_product.html output = loader.load("waybill_all.html").generate( data=result.data, needre=self.needre, header=self.header, total=total, client=client, client_from=self.client_from, client_to=self.client_to, ot=ot, m_total=m_total, m_summ_words=m_summ_words) output = output.replace('\n', '').replace('\r', '') return { 'cmd': '''self.Incunable(function(doc){ doc.write('%s') })''' % output }
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)]
def as_print(self, mode=4): # список департаментов sql = '''SELECT DISTINCT r.depart, d.name FROM factura f JOIN recordp r ON f.id = r.factura JOIN depart d ON r.depart=d.depart WHERE f.header=:head AND d.depart_type<>3''' x = Executor.exec_cls(sql, head=self.head, multi=True) result = [ AssemblyListPage(depart_id=v.depart, depart_name=v.name, head=self.head).as_data() for v in x.data ] sql = '''select max(m_end) mend from packlist where factura=:head and palete is not Null''' max_number = Executor.exec_cls(sql, head=self.head, multi=False) if max_number["mend"]: max_number = max_number["mend"] else: max_number = "" loader = template.Loader(TEMPLATE_DIR) output = loader.load("assembly_list.html").generate( data=result, header=self.header, max_number=max_number) # return output output = output.replace('\n', '').replace('\r', '') return { 'cmd': '''self.Incunable(function(doc){ doc.write('%s') })''' % output } """
def as_print(self, mode=4): # список департаментов sql = '''SELECT DISTINCT r.depart, d.name FROM factura f JOIN recordp r ON f.id = r.factura JOIN depart d ON r.depart=d.depart WHERE f.header=:head AND d.depart_type<>3''' x = Executor.exec_cls(sql, head=self.head, multi=True) result = [AssemblyListPage(depart_id=v.depart, depart_name=v.name, head=self.head).as_data() for v in x.data] sql = '''select max(m_end) mend from packlist where factura=:head and palete is not Null''' max_number = Executor.exec_cls(sql, head=self.head, multi=False) if max_number["mend"]: max_number = max_number["mend"] else: max_number = "" loader = template.Loader(TEMPLATE_DIR) output = loader.load("assembly_list.html").generate(data=result, header=self.header, max_number=max_number) # return output output = output.replace('\n', '').replace('\r', '') return {'cmd':'''self.Incunable(function(doc){ doc.write('%s') })''' % output} """
def print_bill_and_factura(self): client = Client.get(pClient=DEFAULT_CLIENT) # № - header.numb # "от:" - tsclad.datatovarheader(header.id ot = self.func('tsclad.datatovarheader', returnType=cx_Oracle.STRING, parameters=[self.head]) #ot = self.func('tsclad.datatovarheader', returnType=cx_Oracle.DATETIME, parameters=[self.head]).strftime("%d.%m.%Y") sql = '''select id,code,name,valume,round(summ_nds*100/118/valume,2) price,summ_nds,round(summ_nds*18/118,2) nds, round(summ_nds*100/118,2) summ,npack from (select f.id,t.code,t.name,p.n_short npack,f.valume,tsclad.GetFacturaBuhPrice(f.id) price, tsclad.GetFacturaBuhPrice(f.id)*f.valume summ_nds from factura f join tovar t on t.id=isclad.GetTovarFromModify(f.tovar) left join pack p on p.id=t.pack where header=:pHeader) order by name''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total = 0 try: total = sum(d.values["valume"] for d in result.data) except: self.write({'error':'Ошибка в товарной накладной'}) return loader = template.Loader(TEMPLATE_DIR) m_total = pytils.numeral.in_words(len(result.data)) m_summ_words = pytils.numeral.rubles(sum(item.values["summ_nds"] for item in result.data)) # factura # waybill_product.html output = loader.load("waybill_all.html").generate(data=result.data, needre=self.needre, header=self.header, total=total, client=client, client_from=self.client_from, client_to=self.client_to, ot=ot, m_total=m_total, m_summ_words=m_summ_words) output = output.replace('\n', '').replace('\r', '') return {'cmd':'''self.Incunable(function(doc){ doc.write('%s') })''' % output}
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) ]
def as_data(self): sql = '''SELECT valume, name, num, code, tpname, FLOOR(valume/inbox) box, valume-inbox*FLOOR(valume/inbox) piece, CASE WHEN inbox > valume THEN NULL WHEN valume/inbox-FLOOR(valume/inbox) = 0 THEN TO_CHAR(FLOOR(valume/inbox))||'*'||TO_CHAR(inbox) ELSE TO_CHAR(FLOOR(valume/inbox))||'*'||TO_CHAR(inbox)||'+'||TO_CHAR(valume-inbox*FLOOR(valume/inbox)) END valume_box FROM ( SELECT ABS(rp.valume) valume, t.name, shiva.GetPartyInBox(party.party) inbox, party.num, t.code, tp.name tpname FROM tehno.recordp rp, tovar t, party, tovar_place tp WHERE t.id=rp.tovar AND rp.party =party.party(+) AND rp.depart=:depart AND isclad.GetNewForOldTovar(t.id)=tp.tovar(+) AND (tp. name=(SELECT (MIN(name)) FROM tovar_place where tovar=isclad.GetNewForOldTovar(t.id) AND depart=rp.depart AND storage=0) ) AND rp.factura in (SELECT id FROM factura WHERE header=:head) AND rp.depart=tp.depart UNION SELECT ABS(rp.valume) valume, t.name, shiva.GetPartyInBox(party.party) inbox, party.num, t.code, '' tpname FROM tehno.recordp rp, tovar t, party, tovar_place tp WHERE t.id=rp.tovar AND rp.party =party.party(+) AND rp.factura IN (SELECT id FROM factura WHERE header=:head) AND rp.depart=:depart AND NOT EXISTS (SELECT * FROM tovar_place WHERE tovar=isclad.GetNewForOldTovar(t.id) AND depart=rp.depart AND storage=0) ) ORDER BY tpname,name ''' # pytilswhere rownum<=2 ORDER BY tpname,name ''' data = Executor.exec_cls(sql, head=self.head, depart=self.depart_id, multi=True).data self.count_i = 0 self.count_b = 0 for j, i in enumerate(data): self.count_i += i.piece self.count_b += i.box result = { 'depart_id': self.depart_id, 'depart_name': self.depart_name, 'head': self.head, 'count_i': self.count_i, 'count_b': self.count_b, 'bar_url': self.bar_url, 'data': data, } return result
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
def __init__(self, input=None, rc=None): # .data[0].values self.head = int(input["head"]) m_header = Header.get_item(item_id=self.head, rc=rc) preheader = m_header.as_json('id', 'num', 'numb', 'numb_in', 'date', 'client_to', 'client_from', 'client_to_cls.name', 'client_from_cls.name', show={'date':lambda val: val.strftime("%d.%m.%Y")}) # preheader = m_header.as_json(show={'date':lambda val: val.strftime("%d.%m.%Y")}) if not preheader: return self.header = preheader[0] self.client_from = Client.get(pClient=self.header["client_from"]) self.client_to = Client.get(pClient=self.header["client_to"]) sql = "select needre from client where id=:client_to" res = Executor.exec_cls(sql, client_to=self.header["client_to"], multi=False).as_json()
def as_print(self): sql = '''select id,tovar,factura,valume,to_char(mesto) mesto,weight,sotrud, sotrudp,code,prim,name,box,part from( select pl.id,pl.tovar,pl.factura,abs(pl.valume) valume, case when m_start=m_end then to_char(m_start) when mesto<>0 then to_char(mesto) else to_char(m_start)||'-'||to_char(m_end) end mesto, pl.weight,pl.sotrud,pl.sotrudp,t.code,pl.prim,t.name,pl.part,pl.box from packlist pl join tovar t on pl.tovar=t.id and pl.factura=:pHeader order by part,m_start,pl.mesto)''' result = Executor.exec_cls(sql, pHeader=self.head, multi=True) total_valume = sum(d.values["valume"] or 0 for d in result.data) total_weight = sum(d.values["weight"] or 0 for d in result.data) loader = template.Loader(TEMPLATE_DIR) output = loader.load("package.html").generate( data=result.data, needre=self.needre, header=self.header, client_from=self.client_from, client_to=self.client_to, total_valume=total_valume, total_weight=total_weight) output = output.replace('\n', '').replace('\r', '') # return output # output = 'hello' return { 'cmd': '''self.Incunable(function(doc){ doc.write('%s') })''' % output }