Untitled
unknown
plain_text
2 years ago
73 kB
5
Indexable
CREATE OR REPLACE PROCEDURE public.create_tickets(raffleid integer, limitclints integer)
LANGUAGE plpgsql
AS $procedure$
declare
contestId int := 0;
contest contests."name"%type := '';
raffleName raffle."name"%type := '';
startDate raffle."startDate"%type;
endDate raffle."endDate"%type;
participationForm vw_part_form_by_raffle[];
depositoParticipation vw_part_form_by_raffle[];
promedioParticipation vw_part_form_by_raffle[];
consumoParticipation vw_part_form_by_raffle[];
participation_form_additional vw_part_form_by_raffle[];
deposito_participation_additional vw_part_form_by_raffle[];
consumo_participation_additional vw_part_form_by_raffle[];
counter int := 0;
limitQuery int := limitClints;
offsetQuery int := 0;
clientLists record;
startBalance accounts_by_clients."startBalance"%type := 0;
balanceDeposit accounts_by_clients."balanceDeposit"%type := 0;
endBalance accounts_by_clients."endBalance"%type := 0;
balanceAvg accounts_by_clients."avgBalance"%type := 0;
running boolean := true;
account record;
diffBalance decimal;
totalClient int := 0;
percentProccess decimal := 0;
tickets int := 0;
countTicket int := 0;
seq_ticket int := 0;
ticketAudit int := 0;
sec_Temp int := 0;
ticketProcessId int := 0;
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;
textError text;
clientsProcessed int := 0;
enableTicket boolean := false;
enableTicketTCB boolean := false;
average int := 0;
participantes int := 0;
excluidos int := 0;
accounts text := '';
existAdditional boolean := false;
clientCode accounts_by_clients."clientCode"%type;
seqQuantityOut int := 0;
seqTicketOut int := 0;
ticketAuditOut int := 0;
giveAdditional boolean := false;
transactionsClient record;
secret text;
tbSeqTicket int := 0;
group_dynamic_count int := 0;
ind_to_delete vw_part_form_by_raffle[];
group_categories record;
group_dynamic_temp vw_part_form_by_raffle;
balances_json json := '{}';
balance_group_tmp int := 0;
group_cat_max_ticket json := '{}';
group_cat_give_ticket boolean := false;
group_balance_record record;
group_cat_k text;
begin
-- Validate sorteo
select r."name", r."contestId", c."name" as concurso, r."startDate", r."endDate", r."endDate" - r."startDate"
into raffleName, contestId, contest, startDate, endDate, average
from raffle r
inner join contests c on (c.id = r."contestId" and c."deletedAt" is null and c."isActived" = true)
where r."deletedAt" is null
and r."isActived" = true
and r."raffleProcessStatusId" in (3, 6, 7, 8) -- Estados en los cuales se puede generar boletas
and r.id = raffleId;
-- Fin validar sorteo
if contestId > 0 then
ticketProcessId = nextval('ticket_process_id_seq'::regclass);
select convert_from(decode(st."secret", 'base64'), 'LATIN1') into secret from secret_ticket st where st.id = 1;
-- Anular todos posibles sorteos que no se completaron correctamente
update ticket_process set status = 'error', details = 'Se ha corrido un nuevo proceso para este sorteo' where "raffleId" = raffleId;
-- Normalizacion del consumo de tarjetas
call ps_remove_duplicate_consume(limitclints);
-- Mover ticket incompletos a la tabla de logs --
-- Mover ticket incompletos a la tabla de logs --
-- Validar si existe participacion adicional
select vw.additional into existAdditional
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw.additional = true
limit 1;
if existAdditional is not null and existAdditional = true then
-- Forma de participacion adicional
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into participation_form_additional
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" not in (4, 8)
and vw.additional = true;
-- Fin forma de participacion adicional
-- Forma de participacion depositos adicional
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into deposito_participation_additional
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" = 4
and vw.additional = true;
-- Find forma de participacion depositos adicional
-- Forma de participacion consumo tarjetas adicional
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into consumo_participation_additional
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" = 8
and vw.additional = true;
-- Find forma de participacion consumo tarjetas adicional
end if;
-- Fin Validar si existe participacion adicional
-- Cantidad de clientes a procesar
select count(distinct abc."clientCode") into totalClient
from product_by_raffle pbr
inner join product_by_raffle_product_list pbrpl on (pbrpl."ProductByRaffleId" = pbr.id and pbrpl."deletedAt" is null)
inner join products p on (p.id = pbrpl."productId")
inner join accounts_by_clients abc on (abc."accountType" = p.code)
where pbr."raffleId" = raffleId
and pbr."deletedAt" is null
and p."deletedAt" is null
and p.status = 'Active';
-- Fin Cantidad de clientes a procesar
-- Estado del proceso actual del sorteo inicio del proceso => 3 - tabla de raffle_proccess_status
update raffle set "raffleProcessStatusId" = 4 where id = raffleId;
commit;
-- Fin estado del proceso actual del sorteo inicio del proceso => 3 - tabla de raffle_proccess_status
-- Clientes a procesar
raise notice 'Total de clientes a procesar %', totalClient;
-- Fin Clientes a procesar
-- Secuencias
select st.id, st.seq into tbSeqTicket, seq_ticket from seq_tickets st where st."contestId" = contestId and st."raffleId" = raffleId;
if seq_ticket is null then
seq_ticket = 0;
tbSeqTicket = nextval('seq_tickets_id_seq'::regclass);
insert into seq_tickets ("id", "contestId", "raffleId", "seq") values(tbSeqTicket, contestId, raffleId, seq_ticket);
end if;
-- Fin secuencia
-- Inicio de proceso
raise notice 'Saving PROCESSID: %', ticketProcessId;
insert into ticket_process (id, "contestId", "raffleId", "clientTotal", process, details, "seqStart")
values (ticketProcessId, contestId, raffleId, totalClient, percentProccess, 'Generando boletas, por favor espere.', concat(contestId, '', raffleId, '', (seq_ticket + 1)));
commit;
-- Fin inicio del proceso
-- Forma de participacion
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into participationForm
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" not in (4, 8)
and vw.additional = false;
-- Fin forma de participacion
-- Forma de participacion depositos
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into depositoParticipation
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" = 4
and vw.additional = false;
-- Find forma de participacion depositos
-- Forma de participacion consumo tarjetas
select array_agg('(' || vw."raffleId" || ',' || vw."participationFormId" || ',' || vw."participationForm" || ',' || vw."productCategoryId" || ',' || vw."category" || ',' || vw."incrementAmount" || ',' || vw."minTicket" || ',' || vw."maxTicket" || ',' || vw.additional || ',' || vw."accountTypeClient" || ',' || (vw."productCategoriesIds")::text || ',' || (vw."minTickets")::text || ',' || (vw."maxTickets")::text || ')') into consumoParticipation
from vw_part_form_by_raffle vw
where vw."raffleId" = raffleId
and vw."participationFormId" = 8
and vw.additional = false;
-- Find forma de participacion consumo tarjetas
if participationForm is null then
raise notice 'Forma de participacion invalida';
update ticket_process set details = 'Forma de participacion invalida', status = 'error' where id = ticketProcessId;
update raffle set "raffleProcessStatusId" = 8 where id = raffleId;
return ;
end if;
-- Limpiar tabla de agrupamiento de totalidad de cuentas
truncate table md_participation_form;
-- Proceso de agrupacion
for i in 1..CARDINALITY(participationForm) loop
if participationForm[i]."accountTypeClient" = 1 then
insert into md_participation_form("raffleId","participationFormId","accountTypeClient","productCategoryIds","incrementAmount","minTicket","maxTicket")
values (participationForm[i]."raffleId", participationForm[i]."participationFormId", participationForm[i]."accountTypeClient", participationForm[i]."productCategoriesIds", participationForm[i]."incrementAmount", array[participationForm[i]."minTicket"], array[participationForm[i]."maxTicket"]);
group_dynamic_count = group_dynamic_count + 1;
if group_dynamic_count > 1 then
ind_to_delete = array_append(ind_to_delete, participationForm[i]);
end if;
end if;
end loop;
-- Valor a eliminar
for i in 1..CARDINALITY(ind_to_delete) loop
participationForm = array_remove(participationForm, ind_to_delete[i]);
end loop;
-- Valor a eliminar
-- Actualizar valores
for i in 1..CARDINALITY(participationForm) loop
for group_categories in(
select "raffleId","participationFormId","accountTypeClient", "incrementAmount", array_agg("productCategoryIds"[1]) as "productCategoryIds",array_agg("minTicket"[1]) as "minTickets", array_agg("maxTicket"[1]) as "maxTickets"
from md_participation_form
where "participationFormId" = participationForm[i]."participationFormId"
and "incrementAmount" = participationForm[i]."incrementAmount"
group by "raffleId","participationFormId","accountTypeClient", "incrementAmount"
) loop
group_dynamic_temp = participationForm[i];
group_dynamic_temp."productCategoriesIds" = group_categories."productCategoryIds";
group_dynamic_temp."minTickets" = group_categories."minTickets";
group_dynamic_temp."maxTickets" = group_categories."maxTickets";
participationForm[i] = group_dynamic_temp;
end loop;
-- Validar que categoria da mayor cantidad de boletas
/*if array_length(participationForm[i]."productCategoriesIds", 1) > 1 then
for y in 1..CARDINALITY(participationForm[i]."productCategoriesIds") loop
if participationForm[i]."minTickets"[y]::int > balance_group_tmp then
balance_group_tmp = participationForm[i]."minTickets"[y];
group_cat_max_ticket = jsonb_set(group_cat_max_ticket::jsonb, '{category}', concat('{"categoryId":', participationForm[i]."productCategoriesIds"[y], ', "min": ', balance_group_tmp, ', "max": ', participationForm[i]."maxTickets"[y], '}')::jsonb, true);
end if;
end loop;
end if;*/
-- Fin Validar que categoria da mayor cantidad de boletas
end loop;
-- Fin Actualizar valores
-- Fin Proceso de agrupacion
-- Procedimiento de las transacciones
call sp_tranfer_amount(raffleId, participationForm);
-- Fin procedimiento de las transacciones
-- While 1er loop
while running loop
-- 2do loop de clientes
for clientLists in (select abc."clientCode"
from product_by_raffle pbr
inner join product_by_raffle_product_list pbrpl on (pbrpl."ProductByRaffleId" = pbr.id and pbrpl."deletedAt" is null)
inner join products p on (p.id = pbrpl."productId")
inner join accounts_by_clients abc on (abc."accountType" = p.code)
where pbr."raffleId" = raffleId
and pbr."deletedAt" is null
and pbr.additional = false
and p."deletedAt" is null
and p.status = 'Active'
group by abc."clientCode"
limit limitQuery
offset offsetQuery) loop
-- Actualizacion de la tabla de procesos
raise notice 'Cliente: %', clientLists."clientCode";
update ticket_process set process = percentProccess,
created = sec_Temp,
"auditTickets" = ticketAudit,
"seqEnd" = concat(contestId, '', raffleId, '', seq_ticket)
where id = ticketProcessId;
commit;
-- Fin actualizacion de la tabla de procesos
giveAdditional = false;
-- 3er loop forma de participacion
for i in 1..CARDINALITY(participationForm) loop
-- 4to loop Cuentas del clientes
-- Rebajar en el balance final (endBalance) los depositos de los cuantas que probienen de otras categorias de menor a categorias que dan mayor boletas => (endBalance - transfer_amount) as "endBalance"
for account in (select abc."clientCode", abc."accountNumber", abc."startBalance", coalesce(abc."balanceDeposit", 0) as "balanceDeposit", (abc."endBalance" - abc.transfer_amount + abc.transfered_source) as "endBalance", abc."openingDate", coalesce(abc."avgBalance", 0) as "avgBalance", abc."accountType", abc.status, pbr."productCategoryId", pbr."accountTypeClient"
from product_by_raffle pbr
inner join product_by_raffle_product_list pbrpl on (pbrpl."ProductByRaffleId" = pbr.id and pbrpl."deletedAt" is null)
inner join products p on (p.id = pbrpl."productId")
inner join accounts_by_clients abc on (abc."accountType" = p.code)
where pbr."raffleId" = raffleId
and pbr."productCategoryId" = any(participationForm[i]."productCategoriesIds")
and abc."clientCode" = clientLists."clientCode"
and pbr."deletedAt" is null
and pbr.additional = false
and p."deletedAt" is null
and p.status = 'Active'
group by abc."accountNumber", abc."clientCode", abc."startBalance", abc."balanceDeposit", abc."endBalance", abc.transfer_amount, abc.transfered_source, abc."openingDate", abc."avgBalance", abc."accountType", abc.status, pbr."productCategoryId", pbr."accountTypeClient"
) loop
enableTicket = false;
enableTicketTCB = false;
raise notice 'cueeenta: %', account;
/*Forma de participacion de cuentas 1 => Todas las cuentas, 2 => Individual*/
if account."accountTypeClient" = 1 then -- Totalidad de cuentas por cliente
accounts = concat(accounts, account."accountNumber", ',');
startBalance = startBalance + account."startBalance";
balanceDeposit = balanceDeposit + account."balanceDeposit";
endBalance = endBalance + account."endBalance";
balanceAvg = balanceAvg + account."avgBalance";
if array_length(participationForm[i]."productCategoriesIds", 1) > 1 then
for x in 1..CARDINALITY(participationForm[i]."productCategoriesIds") loop
if participationForm[i]."productCategoriesIds"[x] = account."productCategoryId" then
balances_json = jsonb_set(balances_json::jsonb, concat('{', account."productCategoryId", '}')::text[], concat('{"inicial":', coalesce(balances_json->account."productCategoryId"::text->>'inicial', '0')::numeric + account."startBalance", ', "final": ', coalesce(balances_json->account."productCategoryId"::text->>'final', '0')::numeric + account."endBalance", ', "min": ', participationForm[i]."minTickets"[x], ', "max": ', participationForm[i]."maxTickets"[x], ', "deposito": ', coalesce(balances_json->account."productCategoryId"::text->>'deposito', '0')::numeric + account."balanceDeposit", ', "avg": ', coalesce(balances_json->account."productCategoryId"::text->>'avg', '0')::numeric + account."avgBalance",'}')::jsonb, true);
-- raise notice 'Tmp JSON %', balances_json;
end if;
end loop;
end if;
else -- Individual
if participationForm[i]."participationFormId" = 1 then -- Mantenimiento
if account."endBalance" >= account."startBalance" and account."endBalance" >= participationForm[i]."incrementAmount" then
tickets = trunc(account."endBalance"::decimal / participationForm[i]."incrementAmount"::decimal, 0);
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
enableTicket = true;
giveAdditional = true;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Balance no mantenido', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
elsif participationForm[i]."participationFormId" = 2 then -- Activos
-- Certificados
if participationForm[i]."productCategoryId" >= 1 and participationForm[i]."productCategoryId" <= 2 then -- Certificados
if account."startBalance" >= participationForm[i]."incrementAmount" then -- Generar boletas
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
enableTicket = true;
giveAdditional = true;
else -- excluido
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Monto de certificado insuficiente', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
-- Fin certificados
elsif participationForm[i]."productCategoryId" >= 3 and participationForm[i]."productCategoryId" <= 6 then -- Prestamos
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
elsif participationForm[i]."productCategoryId" = 7 then -- Tarjetas de credito
if account.status = participationForm[i]."participationForm" then
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
enableTicketTCB = true;
giveAdditional = true;
end if;
elsif participationForm[i]."productCategoryId" = 8 then -- Tarjetas de debito
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
enableTicketTCB = true;
giveAdditional = true;
end if;
elsif participationForm[i]."participationFormId" = 3 then -- Incremento
diffBalance := trunc(account."endBalance" - account."startBalance", 0);
-- raise notice 'Account % Balance Inicial % Balance Final % Diferencia => %', account."clientCode", account."startBalance", account."endBalance", diffBalance;
if diffBalance > 0 and account."endBalance" > account."startBalance" and diffBalance >= participationForm[i]."incrementAmount" then
tickets = trunc(diffBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
-- raise info '--> % / % = %', diffBalance, participationForm."incrementAmount", tickets;
-- Generar ticket
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else
-- raise warning 'X monto insuficiente %', diffBalance;
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Balance no incrementado', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
elseif participationForm[i]."participationFormId" = 5 then -- Balance promedio
if account."avgBalance"::decimal >= participationForm[i]."incrementAmount" then
tickets = trunc(account."avgBalance"::decimal / participationForm[i]."incrementAmount"::decimal, 0);
-- Generar ticket
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else
-- raise warning 'X monto insuficiente %', diffBalance;
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Balance promedio insuficiente', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
elsif participationForm[i]."participationFormId" = 6 then -- Apertura
-- Certificados
if participationForm[i]."productCategoryId" >= 1 and participationForm[i]."productCategoryId" <= 2 then -- Certificados
if account."openingDate" >= startDate and account."openingDate" <= endDate then -- Validacion de apertura
if account."startBalance" >= participationForm[i]."incrementAmount" then -- Generar boletas
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else -- excluido
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Monto de certificado insuficiente', participationForm[i]."participationFormId", account."startBalance", account."endBalance", 0, participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'El certificado no esta dentro de la fecha de apertura', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
-- Fin certificados
end if;
elsif participationForm[i]."participationFormId" = 7 then -- Monto minimo
if account."startBalance" >= participationForm[i]."incrementAmount" and account."openingDate" >= startDate and account."openingDate" <= endDate then
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'El monto del prestamo es insuficiente o la fecha de apertura no esta dentro del sorto', participationForm[i]."participationFormId", account."startBalance", account."endBalance", account."balanceDeposit", participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
elsif participationForm[i]."participationFormId" >= 10 and participationForm[i]."participationFormId" <= 17 and (account."accountType" = 'BM' or account."accountType" = 'ARC') = true then -- Canales digitales
-- Validar transacciones
for transactionsClient in (
select ct."accountCode", ct.amount
from clients_transactions ct
where ct."accountCode" = account."accountNumber"
and ct."transactionChannel" = account."accountType"
and ct."participationFormId" = participationForm[i]."participationFormId"
) loop
if transactionsClient.amount >= participationForm[i]."incrementAmount" then
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Monto insuficiente', participationForm[i]."participationFormId", transactionsClient.amount, 0, 0, participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end loop;
-- Fin Validar transacciones
elsif participationForm[i]."participationFormId" >= 19 and participationForm[i]."participationFormId" <= 21 then -- Tarjetas de credito
if account.status = participationForm[i]."participationForm" then
tickets = participationForm[i]."minTicket"::int;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
end if;
end if;
-- Si tiene deposito
if depositoParticipation is not null and enableTicket = true and account."productCategoryId" = depositoParticipation[1]."productCategoryId" then
if account."balanceDeposit"::decimal >= depositoParticipation[1]."incrementAmount" then
tickets = trunc(account."balanceDeposit"::decimal / depositoParticipation[1]."incrementAmount"::decimal, 0);
tickets = tickets * depositoParticipation[1]."minTicket";
if depositoParticipation[1]."maxTicket"::int > 0 and tickets::int > depositoParticipation[1]."maxTicket"::int then
tickets = depositoParticipation[1]."maxTicket"::int;
end if;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId", "participationFormIdPrincipal")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), depositoParticipation[1]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), depositoParticipation[1]."productCategoryId", participationForm[i]."participationFormId");
end loop;
-- Fin generar ticket
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Balance de deposito insuficiente', depositoParticipation[1]."participationFormId", account."startBalance", account."endBalance", 0, depositoParticipation[1]."incrementAmount", depositoParticipation[1]."minTicket", depositoParticipation[1]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end if;
-- Fin deposito
-- Consumo con tarjetas
if consumoParticipation is not null and enableTicketTCB = true then
-- Loop de consumo
for x in 1..CARDINALITY(consumoParticipation) loop
if account."productCategoryId" = consumoParticipation[x]."productCategoryId" then
if account."startBalance"::decimal >= consumoParticipation[x]."incrementAmount" then
tickets = trunc(account."startBalance"::decimal / consumoParticipation[x]."incrementAmount"::decimal, 0);
tickets = tickets * consumoParticipation[x]."minTicket";
if consumoParticipation[x]."maxTicket"::int > 0 and tickets::int > consumoParticipation[x]."maxTicket"::int then
tickets = consumoParticipation[x]."maxTicket"::int;
end if;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), consumoParticipation[x]."participationFormId", account."accountNumber", account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), consumoParticipation[x]."productCategoryId");
end loop;
-- Fin generar ticket
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, account."accountNumber", account."clientCode", 'Balance de consumo insuficiente', consumoParticipation[x]."participationFormId", account."startBalance", account."endBalance", 0, consumoParticipation[x]."incrementAmount", consumoParticipation[x]."minTicket", consumoParticipation[x]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end if;
end loop;
-- Loop de consumo
end if;
-- Fin consumo con tarjetas
end if;
/*Fin forma de participacion de las cuentas*/
end loop;
-- Fin 4to loop cuentas del clientes
-- Totalidad de las cuentas
if account."accountTypeClient" = 1 then
enableTicket = false;
if participationForm[i]."participationFormId" = 1 then -- Mantenimiento
if endBalance >= startBalance and endBalance >= participationForm[i]."incrementAmount" then
if array_length(participationForm[i]."productCategoriesIds", 1) > 1 then
group_cat_give_ticket = false;
group_cat_give_ticket = false;
balance_group_tmp = 0;
group_cat_max_ticket = '{}';
for group_balance_record in (select json_object_keys(balances_json) as k) loop -- balances por las categorias que tiene el cliente
group_cat_k = group_balance_record.k;
-- Validar cual de las categorias que tiene el cliente genera mas boletas
if coalesce(balances_json->group_cat_k->>'min', '0')::int > balance_group_tmp then
balance_group_tmp = coalesce(balances_json->group_cat_k->>'min', '0')::int;
group_cat_max_ticket = jsonb_set(group_cat_max_ticket::jsonb, '{category}', concat('{"categoryId":', group_cat_k, ', "min": ', balance_group_tmp, ', "max": ', coalesce(balances_json->group_cat_k->>'max', '0')::int, '}')::jsonb, true);
-- raise notice 'Grupo maximo %', group_cat_max_ticket;
end if;
-- Validar cual de las categorias que tiene el cliente genera mas boletas
if coalesce(balances_json->group_cat_k->>'final', '0')::numeric >= coalesce(balances_json->group_cat_k->>'inicial', '0')::numeric and coalesce(balances_json->group_cat_k->>'final', '0')::numeric >= participationForm[i]."incrementAmount" then
-- Generar ticket
tickets = trunc(coalesce(balances_json->group_cat_k->>'final', '0')::decimal / participationForm[i]."incrementAmount"::decimal, 0);
tickets = tickets * (balances_json->group_cat_k->>'min')::numeric;
if (balances_json->group_cat_k->>'max')::int > 0 and tickets::int > (balances_json->group_cat_k->>'max')::int then
tickets = (balances_json->group_cat_k->>'max')::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), group_cat_k::int);
end loop;
-- Fin Generar tickets
group_cat_give_ticket = true;
enableTicket = true;
giveAdditional = true;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance no incrementado', participationForm[i]."participationFormId", coalesce(balances_json->group_cat_k->>'inicial', '0')::numeric, coalesce(balances_json->group_cat_k->>'final', '0')::numeric, coalesce(balances_json->group_cat_k->>'deposito', '0')::numeric, participationForm[i]."incrementAmount", coalesce(balances_json->group_cat_k->>'min', '0')::int, coalesce(balances_json->group_cat_k->>'max', '0')::int, ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end loop;
-- Fin Balances por categoria
-- En caso de que las categorias por separadas no generaron boletas darselas al que mayor cantidad de boletas genera
if group_cat_give_ticket = false then
-- Generar ticket
tickets = trunc(endBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
tickets = tickets * (group_cat_max_ticket->'category'->>'min')::numeric;
if (group_cat_max_ticket->'category'->>'max')::int > 0 and tickets::int > (group_cat_max_ticket->'category'->>'max')::int then
tickets = (group_cat_max_ticket->'category'->>'max')::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), (group_cat_max_ticket->'category'->>'categoryId')::int);
end loop;
-- Fin generar ticket
enableTicket = true;
giveAdditional = true;
-- Generar ticket
end if;
-- Fin En caso de que las categorias por separadas no generaron boletas darselas al que mayor cantidad de boletas genera
else
-- Generar ticket
tickets = trunc(endBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
enableTicket = true;
giveAdditional = true;
end if;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance no mantenido', participationForm[i]."participationFormId", startBalance, endBalance, balanceDeposit, participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
elsif participationForm[i]."participationFormId" = 3 then -- Incremento
-- Mejoras en la totalidad de cuentas de cuentas regulares y demas que se agrupen como es el caso de las cuentas digitales
diffBalance := trunc(endBalance - startBalance, 0);
if diffBalance > 0 and endBalance > startBalance and diffBalance >= participationForm[i]."incrementAmount" then
-- Si la forma de participacion tiene varias categorias
if array_length(participationForm[i]."productCategoriesIds", 1) > 1 then
-- Comparar balances
group_cat_give_ticket = false;
balance_group_tmp = 0;
group_cat_max_ticket = '{}';
raise notice 'Detalle %', balances_json;
for group_balance_record in (select json_object_keys(balances_json) as k) loop -- balances por las categorias que tiene el cliente
group_cat_k = group_balance_record.k;
-- Validar cual de las categorias que tiene el cliente genera mas boletas
if coalesce(balances_json->group_cat_k->>'min', '0')::int > balance_group_tmp then
balance_group_tmp = coalesce(balances_json->group_cat_k->>'min', '0')::int;
group_cat_max_ticket = jsonb_set(group_cat_max_ticket::jsonb, '{category}', concat('{"categoryId":', group_cat_k, ', "min": ', balance_group_tmp, ', "max": ', coalesce(balances_json->group_cat_k->>'max', '0')::int, '}')::jsonb, true);
-- raise notice 'Grupo maximo %', group_cat_max_ticket;
end if;
-- Validar cual de las categorias que tiene el cliente genera mas boletas
diffBalance = trunc(coalesce(balances_json->group_cat_k->>'final', '0')::numeric - coalesce(balances_json->group_cat_k->>'inicial', '0')::numeric, 0);
if diffBalance > 0 and coalesce(balances_json->group_cat_k->>'final', '0')::numeric > coalesce(balances_json->group_cat_k->>'inicial', '0')::numeric and diffBalance >= participationForm[i]."incrementAmount" then
tickets = trunc(diffBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
-- Generar ticket
tickets = tickets * (balances_json->group_cat_k->>'min')::numeric;
if (balances_json->group_cat_k->>'max')::int > 0 and tickets::int > (balances_json->group_cat_k->>'max')::int then
tickets = (balances_json->group_cat_k->>'max')::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), group_cat_k::int);
end loop;
-- Fin generar ticket
group_cat_give_ticket = true;
giveAdditional = true;
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance no incrementado', participationForm[i]."participationFormId", coalesce(balances_json->group_cat_k->>'inicial', '0')::numeric, coalesce(balances_json->group_cat_k->>'final', '0')::numeric, coalesce(balances_json->group_cat_k->>'deposito', '0')::numeric, participationForm[i]."incrementAmount", coalesce(balances_json->group_cat_k->>'min', '0')::int, coalesce(balances_json->group_cat_k->>'max', '0')::int, ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end loop;
-- Fin de balances por categoria
-- En caso de que las categorias por separadas no generaron boletas darselas al que mayor cantidad de boletas genera
if group_cat_give_ticket = false then
diffBalance := trunc(endBalance - startBalance, 0);
tickets = trunc(diffBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
tickets = tickets * (group_cat_max_ticket->'category'->>'min')::numeric;
if (group_cat_max_ticket->'category'->>'max')::int > 0 and tickets::int > (group_cat_max_ticket->'category'->>'max')::int then
tickets = (group_cat_max_ticket->'category'->>'max')::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), (group_cat_max_ticket->'category'->>'categoryId')::int);
end loop;
giveAdditional = true;
end if;
-- Fin En caso de que las categorias por separadas no generaron boletas darselas al que mayor cantidad de boletas genera
-- Fin comparar balances
else -- Cuando la forma de participacion solo tiene una categoria
tickets = trunc(diffBalance::decimal / participationForm[i]."incrementAmount"::decimal, 0);
-- Generar ticket
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
end if;
else
-- raise warning 'X monto insuficiente %', diffBalance;
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance no incrementado', participationForm[i]."participationFormId", startBalance, endBalance, balanceDeposit, participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
-- Mejoras en la totalidad de cuentas de cuentas regulares y demas que se agrupen como es el caso de las cuentas digitales
elsif participationForm[i]."participationFormId" = 5 then -- Balance promedio
if balanceAvg::decimal > participationForm[i]."incrementAmount" then
tickets = trunc(balanceAvg::decimal / participationForm[i]."incrementAmount"::decimal, 0);
-- Generar ticket
tickets = tickets * participationForm[i]."minTicket";
if participationForm[i]."maxTicket"::int > 0 and tickets::int > participationForm[i]."maxTicket"::int then
tickets = participationForm[i]."maxTicket"::int;
end if;
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), participationForm[i]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), participationForm[i]."productCategoryId");
end loop;
-- Fin generar ticket
giveAdditional = true;
else
-- raise warning 'X monto insuficiente %', diffBalance;
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance promedio insuficiente', participationForm[i]."participationFormId", startBalance, endBalance, balanceDeposit, participationForm[i]."incrementAmount", participationForm[i]."minTicket", participationForm[i]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end if;
-- Si tiene deposito
if depositoParticipation is not null and enableTicket = true and account."productCategoryId" = depositoParticipation[1]."productCategoryId" then
if balanceDeposit::decimal >= depositoParticipation[1]."incrementAmount" then
tickets = trunc(balanceDeposit::decimal / depositoParticipation[1]."incrementAmount"::decimal, 0);
tickets = tickets * depositoParticipation[1]."minTicket";
if depositoParticipation[1]."maxTicket"::int > 0 and tickets::int > depositoParticipation[1]."maxTicket"::int then
tickets = depositoParticipation[1]."maxTicket"::int;
end if;
-- Generar ticket
for countTicket in 1..tickets loop
seq_ticket := seq_ticket + 1;
sec_Temp = sec_Temp + 1;
insert into ticket_by_client ("raffleId", "seqCode", "participationFormId", "accountNumber", "clientCode", "ticketProcessId", "hashCode", "productCategoryId", "participationFormIdPrincipal")
values(raffleId, concat(contestId, '', raffleId, '', seq_ticket), depositoParticipation[1]."participationFormId", TRIM(BOTH ',' FROM accounts), account."clientCode", ticketProcessId, encode(encrypt(sha256(convert_to(contestId || ',' || raffleId || ',' || account."clientCode" || ',' || concat(contestId, '', raffleId, '', seq_ticket), 'LATIN1')), convert_to(secret, 'LATIN1'), 'bf'), 'hex'), depositoParticipation[1]."productCategoryId", participationForm[i]."participationFormId");
end loop;
-- Fin generar ticket
else
insert into audit_excluded ("raffleId", "accountNumber", "clientCode", reason, "participationFormId", "startBalance", "endBalance", "depositBalance", "baseAmount", "minTicket", "maxTicket", "ticketProcessId")
values (raffleId, TRIM(BOTH ',' FROM accounts), account."clientCode", 'Balance de depositos para el promedio es insuficiente', depositoParticipation[1]."participationFormId", startBalance, endBalance, balanceDeposit, depositoParticipation[1]."incrementAmount", depositoParticipation[1]."minTicket", depositoParticipation[1]."maxTicket", ticketProcessId);
ticketAudit = ticketAudit + 1;
end if;
end if;
-- Fin deposito
end if;
-- Fin totalidad de las cuentas
-- Reset balance de cliente
startBalance = 0;
balanceDeposit = 0;
endBalance = 0;
accounts = '';
balanceAvg = 0;
balances_json = '{}';
group_cat_give_ticket = false;
-- Fin Reset balance de cliente
-- Actualizar datos del proceso 1ra vez
update seq_tickets set "seq" = seq_ticket where "id" = tbSeqTicket;
-- Fin actualizar datos del proceso 1ra vez
end loop;
-- Fin 3er loop forma de participacion
-- Validar si tiene adicional
if existAdditional is not null and existAdditional = true and giveAdditional then
select vw."clientCode" into clientCode
from vw_clte_additional vw
where vw."raffleId" = raffleId
and vw."clientCode" = clientLists."clientCode";
if clientCode is not null then
call create_tickets_additional(contestId, raffleId, startDate, endDate, average, secret, participation_form_additional, deposito_participation_additional, consumo_participation_additional, clientCode, ticketProcessId, seq_ticket, sec_Temp, ticketAudit, seqQuantityOut, seqTicketOut, ticketAuditOut);
sec_Temp = seqQuantityOut;
seq_ticket = seqTicketOut;
ticketAudit = ticketAuditOut;
end if;
end if;
-- Validar si tiene adicional
-- Calculo de porcentaje
clientsProcessed = clientsProcessed + 1;
percentProccess = round((clientsProcessed::decimal / totalClient::decimal) * 100, 0);
if percentProccess > 100 then
percentProccess = 100;
end if;
--
end loop;
-- Fin 2do loop de clientes
-- Saber cuando detener el while
if clientLists is null then
raise info 'Proceso completado';
percentProccess = 100;
running := false;
end if;
-- Saber cuando detener el while
-- Actualizar offset
offsetQuery := offsetQuery + limitQuery;
end loop;
-- Fin while 1er loop
raise info 'Boletas generadass %', sec_Temp;
raise info 'Boletas no generadas %', ticketAudit;
-- Actualizar datos del proceso 2da vez
update seq_tickets set "seq" = seq_ticket where "id" = tbSeqTicket;
-- Fin actualizar datos del proceso 2da vez
-- Clientes participantes --
select count(distinct tbc."clientCode") into participantes
from ticket_by_client tbc
where tbc."ticketProcessId" = ticketProcessId;
-- Fin clientes participantes --
-- Clientes excluidos --
select count(distinct ae."clientCode") into excluidos
from audit_excluded ae
where ae."ticketProcessId" = ticketProcessId;
-- Fin clientes excluidos --
-- Actualizacion de la tabla de procesos
update ticket_process set process = percentProccess, "endAt" = now(),
details = 'Proceso terminado',
created = sec_Temp,
"auditTickets" = ticketAudit,
status = 'completed',
"seqEnd" = concat(contestId, '', raffleId, '', seq_ticket),
"participantsClient" = participantes,
"participantsExcluded" = excluidos
where id = ticketProcessId;
-- Fin actualizacion de la tabla de procesos
-- Estado del proceso actual del sorteo ya completado => 4 - tabla de raffle_proccess_status
update raffle set "raffleProcessStatusId" = 5 where id = raffleId;
-- Fin estado del proceso actual del sorteo ya completado => 4 - tabla de raffle_proccess_status
commit;
-- Historial
-- Historil de balance de cuentas
insert into hist_accounts_by_clients ("clientCode", "accountType", "accountNumber", "startBalance", "endBalance", "fromDate", "toDate","balanceDeposit", "raffleId", "transactionCode", "ticketProcessId", "status", "openingDate", "avgBalance", "transfer_amount")
select "clientCode", "accountType", "accountNumber", "startBalance", "endBalance", "fromDate", "toDate","balanceDeposit", raffleId as "raffleId", "transactionCode", ticketProcessId, "status", "openingDate", "avgBalance", "transfer_amount"
from accounts_by_clients;
-- Fin Historil de balance de cuentas
-- Historial de clientes
insert into hist_clients("clientCode", name, surname, email, "idType", "identificationNumber", "branchCode", "raffleId", "ticketProcessId")
select "clientCode", name, surname, email, "idType", "identificationNumber", "branchCode", raffleId as "raffleId", ticketProcessId as "ticketProcessId"
from clients;
-- Fin historial de clientes
-- Historial de las transacciones de canales
insert into hist_clients_transactions ("transactionChannel", "transactionCode", "accountCode", "transactionDate", "raffleId", amount, "transactionType", "participationFormId", "ticketProcessId")
select "transactionChannel", "transactionCode", "accountCode", "transactionDate", "raffleId", amount, "transactionType", "participationFormId", ticketProcessId as "ticketProcessId"
from clients_transactions;
-- Historial de las transacciones de canales
commit;
/*
-- Limpiar tablas de cargas
truncate table accounts_by_clients;
truncate table clients;
truncate table clients_transactions;
-- Limpiar tabla de agrupamiento de totalidad de cuentas
truncate table md_participation_form;
-- Fin limpiar tablas de cargas
*/
-- Fin de historial
else
raise notice 'Este sorteo no es valido';
end if;
end;
$procedure$
;
Editor is loading...