def store(f_xml):
parser = SaxReader()
xml_dat, _ = parser(f_xml)
q = """select * from ncr_save_xml(
{}::integer,
'{}'::character varying,
'{}'::character varying,
'{}'::character varying,
'{}'::boolean,
{}::integer
) AS result( rc integer, msg text )
""".format( # Store procedure parameters
req.get('ncr_id', None), # _ncr_id
os.path.basename(f_xml), # _file_xml
xml_dat['CFDI_SERIE'], # _serie
xml_dat['CFDI_FOLIO'], # _folio
req.get('saldado', None), # _saldado
req.get('usr_id', None) # _usr_id
)
logger.debug("Performing query: {}".format(q))
try:
res = HelperPg.onfly_query(pt.dbms.pgsql_conn, q, True)
if len(res) != 1:
raise Exception(
'unexpected result regarding execution of store')
rcode, rmsg = res.pop()
if rcode == 0:
return ErrorCode.SUCCESS
raise Exception(rmsg)
except:
logger.error(dump_exception())
return ErrorCode.DBMS_SQL_ISSUES
def run_store():
logger.debug("Performing query: {}".format(q))
r = HelperPg.onfly_query(pgsql_conf, q, True)
# For this case we are just expecting one row
if len(r) != 1:
raise Exception('unexpected result regarding execution of store')
return r
def get_xml_name():
q = """select ref_id as filename
FROM fac_nota_credito
WHERE fac_nota_credito.id = {}""".format(ncr_id)
for row in HelperPg.onfly_query(pt.dbms.pgsql_conn, q, True):
# Just taking first row of query result
return row['filename'] + '.xml'
def get_xml_name():
q = """select ref_id as filename
FROM fac_docs
WHERE fac_docs.id="""
for row in HelperPg.onfly_query("{0}{1}".format(q, fact_id), True):
# Just taking first row of query result
return row['filename'] + '.xml'
def store(f_xmlin, usr_id, prefact_id, no_id):
parser = SaxReader()
xml_dat, _ = parser(f_xmlin)
ref_id = '{}_{}{}'.format(no_id, xml_dat['CFDI_SERIE'],
xml_dat['CFDI_FOLIO'])
q = """select fac_save_xml from fac_save_xml(
'{}'::character varying, {}::integer, {}::integer, '{}'::character varying,
'{}'::character varying, '{}'::character varying, '{}'::character varying,
'{}'::character varying, '{}'::character varying, '{}'::character varying,
'{}'::character varying, '{}'::character varying, '{}'::character varying,
'{}'::character varying, '{}'::character varying, '{}'::character varying,
'{}'::character varying, '{}'::character varying, '{}'::character varying,
{}::double precision, {}::double precision, {}::double precision, {}::boolean,
'{}'::character varying
)""".format( # Store procedure parameters
os.path.basename(f_xmlin), # file_xml
prefact_id, # prefact_id
usr_id, # usr_id
xml_dat['CFDI_DATE'].split('T')[0], # creation_date
ref_id, # no_id_emp
xml_dat['CFDI_SERIE'], # serie
xml_dat['CFDI_FOLIO'], # folio
'THIS FIELD IS DEPRECATED', # items_str
'THIS FIELD IS DEPRECATED', # traslados_str
'THIS FIELD IS DEPRECATED', # retenciones_str
xml_dat['INCEPTOR_REG'], # reg_fiscal
'THIS FIELD IS DEPRECATED', # pay_method
xml_dat['INCEPTOR_CP'], # exp_place
'FACTURA', # proposito - It is obviously hardcoded
'THIS FIELD IS DEPRECATED', # no_aprob
'THIS FIELD IS DEPRECATED', # ano_aprob
xml_dat['RECEPTOR_RFC'], # rfc_custm - RFC customer
xml_dat['RECEPTOR_NAME'], # rs_custm - Razon social customer
'0000', # account_number - An account fake number invented by me
xml_dat['TAXES']['TRAS']['TOTAL'], # total_tras
'0', # subtotal_with_desc
xml_dat['CFDI_TOTAL'], # total
'false', # refact
xml_dat[
'UUID'] # id de documento - It came from SAT timbrado throughout PAC
)
logger.debug("Performing query: {}".format(q))
try:
s_out = None
for row in HelperPg.onfly_query(q, True):
# Just taking first row of query result
s_out = row['fac_save_xml']
break
# here we should parse s_out line
logger.debug(
'store procedure fac_save_xml has returned: {}'.format(s_out))
return ErrorCode.SUCCESS
except:
logger.error(dump_exception())
return ErrorCode.ETL_ISSUES
def __get_emisor_rfc(logger, usr_id, pgsql_conf):
q = """select upper(EMP.rfc) as rfc
FROM gral_suc AS SUC
LEFT JOIN gral_usr_suc AS USR_SUC ON USR_SUC.gral_suc_id = SUC.id
LEFT JOIN gral_emp AS EMP ON EMP.id = SUC.empresa_id
LEFT JOIN cfdi_regimenes AS REG ON REG.numero_control = EMP.regimen_fiscal
WHERE USR_SUC.gral_usr_id = {}""".format(usr_id)
logger.debug("Performing query: {}".format(q))
for row in HelperPg.onfly_query(pgsql_conf, q, True):
# Just taking first row of query result
return row['rfc']
def fetch_empdat(usr_id):
sql = """select upper(EMP.rfc) as rfc, EMP.no_id as no_id
FROM gral_suc AS SUC
LEFT JOIN gral_usr_suc AS USR_SUC ON USR_SUC.gral_suc_id = SUC.id
LEFT JOIN gral_emp AS EMP ON EMP.id = SUC.empresa_id
WHERE USR_SUC.gral_usr_id="""
q = "{0}{1}".format(sql, usr_id)
logger.debug("Performing query: {}".format(q))
try:
for row in HelperPg.onfly_query(pt.dbms.pgsql_conn, q):
return ErrorCode.SUCCESS, dict(rfc=row['rfc'], no_id=row['no_id'])
except:
logger.error(dump_exception())
return ErrorCode.DBMS_SQL_ISSUES, None