Skip to content
The Chat Archive
gpt-4o-2024-08-06· OpenAI WildChat-4.8M (AI2)

Отвечай на вопросы, используя следующий контекст: declare @basketNumber varchar(max) =…

  1. User

    Отвечай на вопросы, используя следующий контекст: declare @basketNumber varchar(max) = 'TST-082224-EA17CA527EAD';

    --Auto select d.DateStart, d.DateSign, d.DateEnd, rg.Name as 'Регион использования', p.Name as 'Тип полиса', a.AutoBrandName, a.AutoModelName, a.AutoYearOfIssueValue, a.AutoEnginePowerValue, a.Vin, case a.IsCreditAuto when 1 then sb.Name else null end as 'Банк', a.IsCreditAuto, a.AutoCost as 'Стоимость ТС', ad.IsMultidrive, ts.Series, ts.Number, ts.TSIdentityCardTypeCode, ts.DateOfIssued, a.purposeOfUsingCode from Basket b with(nolock) left join Deal d with(nolock) on b.Id = d.Basket_Id left join Product p with(nolock) on d.Product_Id = p.Id left join InsuranceObject insObj with(nolock) on d.Id = insObj.Deal_Id left join Auto a with(nolock) on insObj.Auto_Id = a.Id left join AutoDeal ad with(nolock) on d.AutoDeal_Id = ad.Id left join TsIdentityCard ts with(nolock) on ad.TsIdentityCard_Id = ts.Id left join RegistrationRegion rg with(nolock) on a.RegionOfUsingCode = rg.Code left join SuggestedBank sb with(nolock) on d.Id = sb.Deal_Id where b.Number= @basketNumber and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber) and d.IsInBasket = 1

    --Insurant select p.LastName, p.MiddleName, p.Name, p.Age, p.DateOfBirth, p.Email1, p.Phone1, id.IssuedBy, id.Series, id.Number, ad.KladrId, p.InnNumber from Basket b with(nolock) left join Deal d with(nolock) on b.Id = d.Basket_Id left join Insurant ins with(nolock) on d.Insurant_Id = ins.Id left join IdentityDocument id with(nolock) on ins.IdentityDocument_Id = id.Id left join Subject sub with(nolock) on ins.Subject_Id = sub.Id left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id left join Person p with(nolock) on ie.Person_Id = p.Id left join Address ad with(nolock) on p.Id = ad.Person_Id where b.Number = @basketNumber and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber) and d.IsInBasket = 1

    --Owner select p.LastName, p.MiddleName, p.Name, p.Age, p.DateOfBirth, p.Email1, p.Phone1, id.IssuedBy, id.Series, id.Number, ad.KladrId, p.InnNumber from Basket b with(nolock) left join Deal d with(nolock) on b.Id = d.Basket_Id left join InsuranceObject insObj on d.Id = insObj.Deal_Id left join [Owner] own on insObj.Id = own.InsuranceObject_Id left join IdentityDocument id with(nolock) on own.IdentityDocument_Id = id.Id left join Subject sub with(nolock) on own.Subject_Id = sub.Id left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id left join Person p with(nolock) on ie.Person_Id = p.Id left join Address ad with(nolock) on p.Id = ad.Person_Id where b.Number = @basketNumber and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber) and d.IsInBasket = 1

    --Driver select p.LastName, p.MiddleName, p.Name, p.Age, p.DriveExperience, p.DateOfBirth, p.Email1, p.Phone1, dr.DrivingExperienceDateStart as 'Дата выдачи прав', dr.FullDrivingExperience as 'Стаж', driv.DriverLicenseSeries, driv.DriverLicenseNumber from Basket b with(nolock) left join Deal d with(nolock) on b.Id = d.Basket_Id left join AutoDeal autodl on d.AutoDeal_Id = autodl.Id left join AutoDealDriver autodealDrv on d.AutoDeal_Id = autodealDrv.AutoDeal_Id left join Driver driv on autodealDrv.Driver_Id = driv.Id left join DrivingExperience dr on driv.Id = dr.Driver_Id left join Subject sub with(nolock) on driv.Subject_Id = sub.Id left join IndividualEntity ie with(nolock) on ie.Id = sub.IndividualEntity_Id left join Person p with(nolock) on ie.Person_Id = p.Id where b.Number = @basketNumber and b.State = (select Max(State) from Basket with(nolock) where Number = @basketNumber) and d.IsInBasket = 1 "Deal.InsuranceObjects.Owners.Subject.LegalEntity"

    -- Получить номер корзины по номеру полиса SELECT b.Number AS BasketNumber FROM Deal d JOIN Basket b ON d.Basket_Id = b.Id WHERE d.PolicyNumber = '035AT-24/0000962-S'; INSERT INTO PriorityProlongationPolicies (PolicyNumber, IsDeleted) VALUES ('XXX0299290199', 0);

    -- Обновление настройки в таблице EntitySetting UPDATE [EntitySetting] SET [SettingValue] = 'true' WHERE [EntitySettingConfigurationCode] = 'UsePriorityProlongationPolicies' AND [EntitySettingDictionariesVersionId] = (SELECT MAX(Id) FROM [EntitySettingDictionariesVersion] WHERE [DateTo] is null) AND [IsDeleted] = 0;

    -- Посмотреть текущее значение настройки SELECT TOP 1 [SettingValue] FROM [EntitySetting] WHERE [EntitySettingConfigurationCode] = 'UsePriorityProlongationPolicies' AND [EntitySettingDictionariesVersionId] = (SELECT MAX(Id) FROM [EntitySettingDictionariesVersion] WHERE [DateTo] is null) AND [IsDeleted] = 0;

    select * from PriorityProlongationPolicies where PolicyNumber = 'XXX0299290199'

    SELECT * FROM AutoProlongationPolicy

    select * from AutoProlongationPackage where Description = 'SRL08006 - Subscription'
    order by CreateDate desc -- VerifyUpdatedConditions.sql -- Проверяем правильность обновления данных в таблице Condition

    SELECT id, Name, Expression FROM Condition WHERE id IN (2010, 2011, 2012);


    -- VerifyUpdatedCalculationFormulas.sql -- Проверяем правильность обновления данных в таблице CalculationComponentSetting

    SELECT StructId, CalculationFormula FROM CalculationComponentSetting WHERE StructId IN (5495, 5496, 5497);


    -- UpdateExpressionConditions.sql -- Обновляем выражения в таблице Condition для заданных ID UPDATE Condition SET Expression = CASE WHEN id = 2010 THEN 'if ({attr_оффер} == 2177) {2990} else if ({attr_оффер} == 2179) {4490} else if ({attr_оффер} == 2180) {5990} else if ({attr_оффер} == 2158) {2519} else {1090}' WHEN id = 2011 THEN 'if ({attr_оффер} == 2184) {2490} else if ({attr_оффер} == 2185) {3990} else if ({attr_оффер} == 2186) {5490} else if ({attr_оффер} == 2159) {2219} else {790}' WHEN id = 2012 THEN 'if ({attr_оффер} == 2181) {1990} else if ({attr_оффер} == 2182) {3490} else if ({attr_оффер} == 2183) {4490} else if ({attr_оффер} == 2160) {1919} else {490}' END WHERE id IN (2010, 2011, 2012);


    --UpdateCalculationFormulas.sql -- Обновляем формулы расчета в таблице CalculationComponentSetting для заданных StructId UPDATE CalculationComponentSetting SET CalculationFormula = 'var result = {coeff_C_DisastrerAndFallingObject}; result;' WHERE StructId = 5497;

    UPDATE CalculationComponentSetting SET CalculationFormula = 'var result = {coeff_C_FallingObject}; result;' WHERE StructId = 5496;

    UPDATE CalculationComponentSetting SET CalculationFormula = 'var result = {coeff_C_Disastrer}; result;' WHERE StructId = 5495; -- Проверка статуса urm по номеру корзины declare @basket_number varchar(50) = 'DEV-081424-683D02057E94'
    select u.URMApprovalStatusCode,u.* from URMApproval u join AutoDeal ad on ad.id = u.AutoDeal_Id join Deal d on d.AutoDeal_Id = ad.Id join basket b on b.id = d.Basket_Id where b.Number = @basket_number AND u.State = 2 -- Включить/отключить страницу "Спасибо" SELECT [Percent] FROM [dbo].[ABTestVariant] WHERE [Code] = 'KaskoFeedbackOn';

    UPDATE [dbo].[ABTestVariant] SET [Percent] = CASE WHEN [Code] = 'KaskoFeedbackOn' THEN 100 WHEN [Code] = 'KaskoFeedbackOff' THEN 0 END WHERE [Code] IN ('KaskoFeedbackOn', 'KaskoFeedbackOff'); -- Все данные по посреднику с внутренним именем 'Yandex', SELECT * FROM [dbo].[Intermediary] WHERE [IntermediaryInternalName] = 'Yandex' AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker';

    -- Проверить какие offerGroup привязаны к Яндекс Intermediary. SELECT iog.Intermediary_Id, og.Code FROM IntermediaryOfferGroup iog JOIN OfferGroup og ON iog.OfferGroup_Id = og.Id WHERE iog.Intermediary_Id = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = 'Yandex' AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker');

    -- Смотреть Product_id = 14 select * from IntermediaryToCostcenter where Intermediary_Id = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = 'Yandex' AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker') AND [State] = 2 AND [IsDeleted] = 0; -- Получить проверки андеррайтинга по номеру корзины DECLARE @basket_number varchar(50) = 'DEV-092424-8D14944541A4'; SELECT dia.StatusCode, dia.* FROM Basket b WITH (NOLOCK) JOIN Deal d WITH (NOLOCK) ON b.Id = d.Basket_Id JOIN AutoDeal ad WITH (NOLOCK) ON d.AutoDeal_Id = ad.Id JOIN DIUnderwriterApproval dia WITH (NOLOCK) ON ad.Id = dia.AutoDeal_Id WHERE b.Number = @basket_number AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basket_number) AND d.IsInBasket = 1; select * from PrintingFormOfferStruct where OfferStruct_Id = 10562 and PrintingForm_Id = 250

    select * from Offer

    SELECT os.Id as OfferStructId FROM OfferStruct os LEFT JOIN ProductConfiguration pc ON os.ProductConfigurationEntityId = pc.EntityId WHERE Offer_Id = 2116 AND pc.State = 2 AND pc.ParentStructId IS NULL AND os.State = 2 AND os.IsDeleted = 0 ORDER BY OfferStructId

    select * from PrintingForm where Code = 'KID_KASKO' DECLARE @IntermediaryInternalName varchar(255) = 'Yandex';

    -- Получение Id посредника DECLARE @IntermediaryId int = (SELECT TOP 1 Id FROM [dbo].Intermediary WHERE [IntermediaryInternalName] = @IntermediaryInternalName AND [State] = 2 AND [IsDeleted] = 0 AND [InteractionTypeCode] = 'Broker');

    -- Обновление значения CostcenterCode на 'SRL08010' для существующего посредника UPDATE [Intermediary] SET CostcenterCode = 'SRL08010', isThemingNeeded = 0, AllowRefByURL = 1 WHERE Id = @IntermediaryId;

    -- Обновляем IntermediaryToCostcenter UPDATE IntermediaryToCostcenter SET CostcenterCode = 'SRL08010' WHERE Intermediary_Id = (SELECT TOP 1 Id FROM Intermediary WHERE Code = 'YAPartners' AND State = 2 AND IsDeleted = 0 ORDER BY IntermediaryStatusCode) AND Product_Id = (SELECT id FROM Product WHERE ProductDictCode = 'KASKO' AND State = 2 AND IsDeleted = 0) AND IsDeleted = 0; -- Посмотреть все коэффициенты по номеру корзины SELECT ccr.CalculationComponentSetting_Id, b.Number, d.BranchCode, ccr.Value, coefcr.CalculationComponentCoefficient_Id, c.Id, c.Name, coefcr.Value FROM ComponentCalculationResult ccr JOIN CoefficientCalculationResult coefcr ON ccr.Id = coefcr.ComponentCalculationResult_Id JOIN CalculationComponentCoefficient ccc ON coefcr.CalculationComponentCoefficient_Id = ccc.Id --JOIN CalculationComponentCoefficientComponentTarifficationAttribute ccccta ON ccc.Id = ccccta.CalculationComponentCoefficient_Id --JOIN ComponentTarifficationAttribute cta ON ccccta.ComponentTarifficationAttribute_Id = cta.Id --JOIN TarifficationAttribute ta ON cta.TarifficationAttribute_Id = ta.Id JOIN Coefficient c ON ccc.Coefficient_Id = c.Id JOIN Proposal p ON ccr.Proposal_Id = p.Id JOIN Deal d ON p.Deal_Id = d.Id JOIN Basket b ON d.Basket_Id = b.Id JOIN InsuranceObject io on io.Deal_Id = d.Id JOIN Auto a on a.Id = io.Auto_Id WHERE b.Number IN ('DEV-070324-CA64BAFB70F1') AND p.IsSelected = 1 AND p.State = 2 AND ccr.State = 2 ORDER BY ccr.CalculationComponentSetting_Id, coefcr.CalculationComponentCoefficient_Id, b.Number --- Подставить номер своей котировки ---Проверка опций риск и франшиз по котировки

    declare @basket_number varchar(50) = 'TST-112123-77210D8AED4E'

    declare @dealId table (dealId int) insert @dealId select d.Id from Basket b with(nolock) join Deal d with(nolock) on d.Basket_Id = b.Id where b.Number = @basket_number --in (Select BasketNumber from AutoProlongationPolicy where AutoProlongationPackage_Id = 389 and Status=50) --= @basket_number

    select distinct b.Price, b.Number, p.Offer_Id, ccr.Value, p.TotalSum as PTotalSum, dc.StructId, pc.CoverageCode, dc.Sum as CovSum, dr.StructId, pr.RiskCode, dr.Sum as RiskSum, dfr.StructId, pfr.Code as RCovFran, dfr.Sum as RCovFranSum, do.StructId, po.OptionCode as CovOption, do.Sum as CovOptionSum, dor.StructId, por.OptionCode as RiskOption, dor.Sum as RiskOptionSum, df.StructId, pf.Code as CovFran, df.Sum as CovFranSum , b.Price, b.State from Basket b with(nolock) join Deal d with(nolock) on d.Basket_Id = b.Id left outer join DealConditionGroup dcg with(nolock) on dcg.Deal_Id = d.Id left outer join InsuranceObjectCondition ioc with(nolock) on ioc.DealConditionGroup_Id = dcg.Id left outer join DealCoverage dc with(nolock) on dc.InsuranceObjectCondition_Id = ioc.Id left outer join ComponentCalculationResult ccr with (nolock) on ccr.Id = dc.ComponentCalculationResult_Id left outer join ProductCoverage pc with(nolock) on pc.Id = dc.ProductCoverage_Id left outer join Proposal p with(nolock) on p.Deal_Id = d.Id left outer join DealRisk dr with(nolock) on dr.DealCoverage_Id = dc.Id left outer join ProductRisk pr with(nolock) on pr.Id = dr.ProductRisk_Id left outer join ConditionBlock cb with(nolock) on cb.Id = dc.ConditionBlock_Id left outer join DealOption do with(nolock) on do.ConditionBlock_Id = cb.Id left outer join ProductOption po with(nolock) on po.Id = do.ProductOption_Id left outer join DealFranchise df with(nolock) on df.ConditionBlock_Id = cb.Id left outer join ProductFranchise pf with(nolock) on pf.Id = df.ProductFranchise_Id left outer join ConditionBlock cbr with(nolock) on cbr.Id = dr.ConditionBlock_Id left outer join DealOption dor with(nolock) on dor.ConditionBlock_Id = cbr.Id left outer join ProductOption por with(nolock) on por.Id = dor.ProductOption_Id left outer join DealFranchise dfr with(nolock) on dfr.ConditionBlock_Id = cbr.Id left outer join ProductFranchise pfr with(nolock) on pfr.Id = dfr.ProductFranchise_Id

    where d.Id in (select * from @dealId) --and d.Product_Id in (14) and d.State in (2) And p.ISSELECTED = 1 --and pr.RiskCode = 'CIVILLIABILITY' order by dc.StructId asc; -- Для поиска полисов для автопролонгации SELECT TOP (10000) *
    FROM [AutoProlongationPolicy] --- where Description = 'SRL08006 - Subscription' where CreateDate >= '20230210'
    ---and AutoProlongationPackage_ID in (5760)
    ---and status in (20, 50, 40)
    order by CreateDate desc --- Пакеты по полисам select TOP (1000) * from AutoProlongationPackage --- where Description = 'SRL08006 - Subscription' order by CreateDate desc -- Получаем номер корзины апсейла по номеру корзины осаго select UpsaleSourceBasketNumber from Basket nolock where Number = 'DEV-100224-F764E7EEF5F5' Что бы сделать запрос к определенной базе данных на ветке, нужно нажать правой кнопкой на сервер: ren-webtestdb01.itlab.renins.com и выбрать "New Query". В самом запросе нужно указать определенную базу с нужным МР, например: USE [IrisInsuranceDB-Kasko-merge-requests-4393]; SELECT * FROM Basket; Как мне посмотреть все сделки в корзине?

  2. Assistant

    Чтобы посмотреть все сделки в корзине, вам нужно выполнить запрос, который соединяет таблицы Basket и Deal по идентификатору корзины (Basket_Id). В вашем контексте уже есть часть запроса, которая выбирает эту информацию:

    SELECT d.* 
    FROM Basket b WITH (NOLOCK)
    JOIN Deal d WITH (NOLOCK) ON b.Id = d.Basket_Id
    WHERE b.Number = @basketNumber
    AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basketNumber)
    AND d.IsInBasket = 1;
    

    Этот запрос извлекает всю информацию о сделках, связанных с заданным номером корзины (@basketNumber). Убедитесь, что переменная @basketNumber инициализирована номером корзины, которую вы хотите проверить. Если вы хотите получить только основные данные по сделкам, можно упростить запрос:

    SELECT d.Id, d.DateStart, d.DateSign, d.DateEnd, d.Product_Id 
    FROM Basket b WITH (NOLOCK)
    JOIN Deal d WITH (NOLOCK) ON b.Id = d.Basket_Id
    WHERE b.Number = @basketNumber
    AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basketNumber)
    AND d.IsInBasket = 1;
    

    Этот вариант выберет только ключевые поля из таблицы Deal для всех сделок в указанной корзине.