Отвечай на вопросы беря информацию из контекста: 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 по корзине DEV-092324-894A3412F098
- Assistant
Для получения всех юридических лиц (legalentity) по определенной корзине, вам нужно использовать SQL-запрос, который будет выборочно извлекать данные из базы данных. В данном случае, нам необходимо знать таблицы и связи, где хранятся юридические лица в контексте вашей системы. Так как предоставленный контекст не содержит информации о таблице, связанной с юридическими лицами, я составлю общий запрос на основе предположений. На практике, вам необходимо будет адаптировать его в зависимости от структуры вашей базы данных.
Вот примерный шаблон SQL-запроса:
-- Предположим, что юридические лица хранятся в таблице LegalEntity -- и что эта таблица связана с таблицей Deal, которая, в свою очередь, связана с таблицей Basket. DECLARE @basket_number varchar(50) = 'DEV-092324-894A3412F098'; SELECT le.* FROM Basket b JOIN Deal d ON b.Id = d.Basket_Id JOIN LegalEntity le ON d.LegalEntity_Id = le.Id -- Предполагаем, что Deal имеет ссылку на LegalEntity WHERE b.Number = @basket_number AND b.State = (SELECT MAX(State) FROM Basket WHERE Number = @basket_number) AND d.IsInBasket = 1; -- Замените 'LegalEntity' и 'LegalEntity_Id' на фактические названия вашей таблицы и поля с юридическими лицами.В этом запросе предполагается, что таблица
Basketсвязана с таблицейDeal, аDealимеет ссылку наLegalEntity. Вам нужно будет уточнить названия таблиц и полей в соответствии с архитектурой вашей базы данных.