Отвечай на вопросы беря информацию из контекста: declare @basketNumber varchar(max) =…
- 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 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 -- Проверяем правильность обновления данных в таблице ConditionSELECT 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 = 2SELECT [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;
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;
--- Подставить номерсвоей котировки ---Проверка опций риск и франшиз по котировки
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;
Напиши мне как по котировке получить все legalEntity
- Assistant
Для получения всех юридических лиц, связанных с определенной котировкой, необходимо выполнить SQL-запрос, который свяжет таблицы, хранящие информацию о юридических лицах, с таблицами, содержащими данные о котировке.
Однако в предоставленном вами контексте нет прямой информации о том, как юридические лица связаны с котировками. Обычно связь между котировкой и юридическими лицами может осуществляться через такие таблицы, как
Deal,Basketи другие связанные таблицы.Примерный запрос может выглядеть так:
-- Предполагается, что есть таблица LegalEntity, которая хранит информацию о юридических лицах -- и их связь с котировками осуществляется через таблицу Deal или InsuranceObject. SELECT le.* FROM Basket b JOIN Deal d ON b.Id = d.Basket_Id JOIN InsuranceObject io ON d.Id = io.Deal_Id JOIN LegalEntity le ON io.LegalEntity_Id = le.Id WHERE b.Number = @basket_number AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basket_number) AND d.IsInBasket = 1;Обратите внимание, что этот запрос основан на предположении о наличии определенной структуры базы данных. Если структура вашей базы данных отличается, вам нужно будет соответствующим образом изменить связи (JOIN) между таблицами. Обычно информация о юридических лицах может находиться в таблице, такой как
LegalEntity, и связь с котировкой может осуществляться через таблицуInsuranceObjectили аналогичную таблицу, которая имеет связь с таблицейDeal.