53 lines
3.2 KiB
Transact-SQL
53 lines
3.2 KiB
Transact-SQL
-- Tedarikçiden fiyat ve termin isteyen e-postayı bildirim kuyruğuna yazar; fiyat gönderilmez.
|
||
CREATE OR ALTER PROCEDURE [dbo].[Prc_P_PurchaseRfqSend]
|
||
@Id uniqueidentifier,
|
||
@UserId uniqueidentifier = NULL
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
DECLARE @Tenant uniqueidentifier, @No nvarchar(32), @Email nvarchar(256), @Supplier nvarchar(256), @Deadline datetime,
|
||
@Subject nvarchar(256), @Lines nvarchar(MAX), @Msg nvarchar(512);
|
||
|
||
SELECT @Tenant = r.TenantId, @No = r.RfqNo, @Email = c.Email, @Supplier = c.Name, @Deadline = r.OrderDeadline
|
||
FROM Prc_T_PurchaseRfq r LEFT JOIN Adm_T_Contact c ON c.Id = r.SupplierId
|
||
WHERE r.Id = @Id;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM Prc_T_PurchaseRfqLine WHERE PurchaseRfqId = @Id AND IsDeleted = 0)
|
||
BEGIN
|
||
SET @Msg = CONCAT(@No, N': kalemi olmayan teklif talebi gönderilemez.');
|
||
THROW 50165, @Msg, 1;
|
||
END
|
||
IF ISNULL(@Email, N'') = N''
|
||
BEGIN
|
||
SET @Msg = CONCAT(@No, N': tedarikçinin (', @Supplier, N') kontak kartında e-posta adresi yok.');
|
||
THROW 50166, @Msg, 1;
|
||
END
|
||
|
||
SET @Subject = N'Teklif Talebi ' + @No;
|
||
|
||
SELECT @Lines = (SELECT N'<tr><td>' + ISNULL(p.Name, N'') + N'</td><td>' + ISNULL(l.Description, N'') + N'</td><td>'
|
||
+ FORMAT(l.Quantity, 'N2', 'tr-TR') + N' ' + l.UomId + N'</td><td>'
|
||
+ ISNULL(FORMAT(l.DeliveryDate, 'dd.MM.yyyy'), N'') + N'</td></tr>'
|
||
FROM Prc_T_PurchaseRfqLine l LEFT JOIN Stk_T_Product p ON p.Id = l.ProductId
|
||
WHERE l.PurchaseRfqId = @Id AND l.IsDeleted = 0
|
||
ORDER BY l.CreationTime
|
||
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)');
|
||
|
||
-- 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 NEWID(), @Tenant, r.Id, t.Id, r.Channel, @Email, @Subject, LEFT(
|
||
N'<p>Sayın ' + ISNULL(@Supplier, N'') + N',</p><p>Aşağıdaki ürünler için birim fiyat ve teslim tarihi içeren teklifinizi'
|
||
+ ISNULL(N' en geç ' + FORMAT(@Deadline, 'dd.MM.yyyy HH:mm'), N'') + N' iletmenizi rica ederiz. Teklif referansı: <b>' + @No + N'</b></p>'
|
||
+ N'<table border="1" cellpadding="4" cellspacing="0"><tr><th>Ürün</th><th>Açıklama</th><th>Miktar</th><th>İstenen Termin</th></tr>'
|
||
+ ISNULL(@Lines, N'') + N'</table>',
|
||
2048), 0, 0, GETUTCDATE(), @UserId, 0
|
||
FROM Adm_T_NotificationType t
|
||
INNER JOIN Adm_T_NotificationRule r ON r.NotificationTypeId = t.Id AND r.IsActive = 1 AND r.IsCustomized = 0 AND r.IsDeleted = 0
|
||
AND r.RecipientType = N'Custom' AND r.RecipientId IS NULL AND r.Channel = N'Mail'
|
||
WHERE t.Name = N'TeklifTalebiGonderildi' AND t.IsDeleted = 0 AND (t.TenantId = @Tenant OR (t.TenantId IS NULL AND @Tenant IS NULL));
|
||
|
||
UPDATE Prc_T_PurchaseRfq
|
||
SET LastSentDate = GETUTCDATE(), Status = CASE WHEN Status = 'Draft' THEN 'Sent' ELSE Status END
|
||
WHERE Id = @Id;
|
||
END
|