46 lines
3.2 KiB
PL/PgSQL
46 lines
3.2 KiB
PL/PgSQL
-- Tedarikçiden fiyat ve termin isteyen e-postayı bildirim kuyruğuna yazar; fiyat gönderilmez.
|
||
CREATE OR REPLACE PROCEDURE public."Prc_P_PurchaseRfqSend"(p_id uuid, p_user_id uuid DEFAULT NULL)
|
||
LANGUAGE plpgsql AS $$
|
||
DECLARE v_tenant uuid; v_no varchar(32); v_email varchar(256); v_supplier varchar(256); v_deadline timestamp;
|
||
v_subject varchar(256); v_lines text;
|
||
BEGIN
|
||
SELECT r."TenantId", r."RfqNo", c."Email", c."Name", r."OrderDeadline"
|
||
INTO v_tenant, v_no, v_email, v_supplier, v_deadline
|
||
FROM "Prc_T_PurchaseRfq" r LEFT JOIN "Adm_T_Contact" c ON c."Id" = r."SupplierId"
|
||
WHERE r."Id" = p_id;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM "Prc_T_PurchaseRfqLine" WHERE "PurchaseRfqId" = p_id AND "IsDeleted" = false) THEN
|
||
RAISE EXCEPTION '%: kalemi olmayan teklif talebi gönderilemez.', v_no USING ERRCODE = 'P0001', DETAIL = '50165';
|
||
END IF;
|
||
IF COALESCE(v_email, '') = '' THEN
|
||
RAISE EXCEPTION '%: tedarikçinin (%) kontak kartında e-posta adresi yok.', v_no, v_supplier USING ERRCODE = 'P0001', DETAIL = '50166';
|
||
END IF;
|
||
|
||
v_subject := 'Teklif Talebi ' || v_no;
|
||
|
||
SELECT string_agg('<tr><td>' || COALESCE(p."Name", '') || '</td><td>' || COALESCE(l."Description", '') || '</td><td>'
|
||
|| TRIM(TO_CHAR(l."Quantity", 'FM999G999G990D00')) || ' ' || l."UomId" || '</td><td>'
|
||
|| COALESCE(TO_CHAR(l."DeliveryDate", 'DD.MM.YYYY'), '') || '</td></tr>', '' ORDER BY l."CreationTime")
|
||
INTO v_lines
|
||
FROM "Prc_T_PurchaseRfqLine" l LEFT JOIN "Stk_T_Product" p ON p."Id" = l."ProductId"
|
||
WHERE l."PurchaseRfqId" = p_id AND l."IsDeleted" = false;
|
||
|
||
-- Alıcı tedarikçinin e-postasıdır; tipin aktif Custom Mail kuralı gönderimi açar (kural kapatılırsa e-posta kuyruğa girmez).
|
||
INSERT INTO "Adm_T_Notification" ("Id", "TenantId", "NotificationRuleId", "NotificationTypeId", "NotificationChannel", "Identifier", "Subject", "Message",
|
||
"IsSent", "IsRead", "CreationTime", "CreatorId", "IsDeleted")
|
||
SELECT gen_random_uuid(), v_tenant, r."Id", t."Id", r."Channel", v_email, v_subject, LEFT(
|
||
'<p>Sayın ' || COALESCE(v_supplier, '') || ',</p><p>Aşağıdaki ürünler için birim fiyat ve teslim tarihi içeren teklifinizi'
|
||
|| COALESCE(' en geç ' || TO_CHAR(v_deadline, 'DD.MM.YYYY HH24:MI'), '') || ' iletmenizi rica ederiz. Teklif referansı: <b>' || v_no || '</b></p>'
|
||
|| '<table border="1" cellpadding="4" cellspacing="0"><tr><th>Ürün</th><th>Açıklama</th><th>Miktar</th><th>İstenen Termin</th></tr>'
|
||
|| COALESCE(v_lines, '') || '</table>',
|
||
2048), false, false, NOW() AT TIME ZONE 'UTC', p_user_id, false
|
||
FROM "Adm_T_NotificationType" t
|
||
INNER JOIN "Adm_T_NotificationRule" r ON r."NotificationTypeId" = t."Id" AND r."IsActive" = true AND r."IsCustomized" = false AND r."IsDeleted" = false
|
||
AND r."RecipientType" = 'Custom' AND r."RecipientId" IS NULL AND r."Channel" = 'Mail'
|
||
WHERE t."Name" = 'TeklifTalebiGonderildi' AND t."IsDeleted" = false AND t."TenantId" IS NOT DISTINCT FROM v_tenant;
|
||
|
||
UPDATE "Prc_T_PurchaseRfq" SET "LastSentDate" = NOW() AT TIME ZONE 'UTC',
|
||
"Status" = CASE WHEN "Status" = 'Draft' THEN 'Sent' ELSE "Status" END
|
||
WHERE "Id" = p_id;
|
||
END;
|
||
$$;
|