--select * from [Order] where DateOfPayment is not null AND FiscalPrinted =0 and DateTimeCreate> '2017-07-31'


--78:22102:06
--с первого раза заказ не сформировался A3E01F5E-A090-4920-8E22-049A7C2CEB8F
select o.Phone, o.FiscalPrintedDate, o.DateOfPayment, o.ID,RequestNumber, oi.FgisKeyID, oi.DateTimeSendOrder, Region, Cadastral, Status, DateTimeSendOrder, o.Email, sc.Name, oi.OrderPackageID
from OrderItem oi
inner join [Order]  o ON o.ID = OrderID
inner join [Service] s ON s.ID = ServiceID
inner join ServiceCategory sc ON sc.id = s.ServiceCategoryID
where oi.Name not like N'Пакет%' and orderid in (select id from [Order] where (DateOfPayment is not null or oi.OrderPackageID is not null)
AND DateOfEmailSended is null and DateTimeCreate>= '2018-10-25'
)
--and OrderId ='95fa38e9-34e2-4807-8918-87df2fadea4e'
and ResultFileID is null
and OrderID not in('FE0793B1-F2C1-43BA-B762-11B35F4F0364','1476A897-74FC-4C9A-925D-6DB107DFD38B',
'DFC49812-23C9-4C54-BA61-6B19289068A2','4E0B7324-FD91-4813-8C5D-E9F8F0F9DD16',
'0890B5EF-DC1C-4729-A102-5EACC210FCA7','9212FD00-75A6-4FC0-9C70-0731A2758C6A',
'8F18F7E4-B234-4D4F-B8D4-4480F9A951A8','5ADC0747-45DF-4619-A668-023D54E09D0D',
'CDFA1075-0F95-44C8-A297-2FF740A6AEF7',
'7DCBB61E-E86D-4022-A95C-05B0183AD6FE','BC58A83A-85F3-4F0F-A5D2-940DC6B9D17F',
'EF113936-B39F-411E-950D-84652BFDCB5A','FF6B0271-D299-4D26-A20C-1AB4C0B25C66',
'9AE83FDD-FD33-4218-82E6-A0542604A68F','338771C6-E6C5-4ABF-A545-6C8A904A3465',
'EC362329-FB74-483A-A7FE-8645BAC231C8','8D9B42B0-692C-4A8D-AE42-2AB5CFB1D076',
'7DE9F673-6FCB-4C6E-A39A-1E952C2296C3','A57BBA4A-C8E5-44C2-B51F-957016513286',
'36B5FA41-A78E-4D3B-BADE-ABF7630838AA','199F07AC-F315-4C29-AD55-9D68D6734E38',
'4D2691D7-B0C6-400E-BC0A-A2E7891B292A','EDA3E8ED-94F6-41ED-92F9-7EC1A64CEBC5',
'3E8E8537-94BC-4D4F-B1C8-62C1BDE6684D','3E419AF1-4585-4DAE-8473-CCD8DA155086',
'6E1B362F-D961-458D-AF77-E5331822801F','F66A72D0-D69E-4AF6-B81E-B9A651416A60',
'2B182BA7-6307-4D2F-AD9B-262D7263DABC','D3136872-6256-429A-821B-CE4D0E51A31C',
'C7064FD3-CB73-4A8E-927E-E07DF2A67B2A','F99D3CCD-CF70-40A0-973E-7E0EBF7203EB',
'C2C71CC5-CABC-4E09-8EB1-FAF2E4A1F1A4','0A2C3B91-16A2-4D0E-B698-4F2CDEFF962A',
'AA90F27C-38A0-4A06-BC2F-4C629A845BE5','ECA63705-439F-414D-A6E7-2C69C48C8B04',
'3376F29B-9A5C-4414-ABEC-5A795F495BAD','C7C95C2E-1366-45BF-9415-0161CE416748',
'C3129614-25A8-43F8-B541-E50C3D9BF2E2','D3A34429-8540-4114-ABB3-55D94A23951B',
'AE6AD11F-B129-42DC-8BDD-3977FB0C9711','759910FD-489B-49A0-ABFE-9D5CC9D263A0',
'DD698F24-2A41-41AA-A644-EB7E9E4D2DB9','34CC87E7-6563-417A-8575-D2FCD4A1B79B',
'91A3BAEA-710C-4563-A9FB-A23BAFD28766','82E287D5-59AE-4442-8C7A-A0AC0CE44B06',
'4D25EE41-3D38-4F2E-9F59-F4CCA6602263','0BE931F1-2F6A-4B95-AA22-BC601916AC53',
'9D98B3B6-E0A9-44D7-ABE4-677B3B5467D2','9E683A04-DD58-4FF4-BD3A-954A7948A64F',
'A303228E-42FC-4C77-B447-4D11A9E58B2E','27907F6B-F882-4DAB-90EC-CFBCE73CB158',
'1A95BF93-9DA5-4C8A-A508-AB840963C0B4','88311C35-004D-45B6-AB07-6B942551C982',
'936332B6-EF72-43E0-947E-DB3F479252D5','2DA59E41-4E25-4E54-9078-F6CD8AF2AD93',
'00CCD279-270B-4A46-B0A6-281631ED9468','538F4457-F7ED-48DC-9486-60C11A74363C',
'F9F08D28-6D0A-4C99-9670-B8B713E6058F','EBFB7A12-2CCA-4E70-A1B4-AEF3E0DB85F4',
'00E4E394-2176-4EEF-8045-BCAFADDC7784','4B8B6CA8-FC54-45D9-BEEA-A5B4333D46AE',
'57693E15-79CD-42BA-86A6-AC7BB30F0B4D','890F805B-C2E7-4BAC-A421-BF361E8B78EB',
'13990EC0-C42B-4219-9C94-90CD55A99464','9E5ED807-D97D-49A6-90E0-77755C2966BC',
'ED6A4F85-EB49-4A2B-8431-AB75A7A72A2A','8A805EFA-D335-41DF-A869-7FC19DDDF6A2',
'3E49EFB3-A3B4-407B-AA3B-6326ABEEA395','E48D4B8E-0D04-4164-B985-C30C5E72A8CC',
'B27EAC9C-59A2-4ABF-96FA-FB70425228CD','41DC25A5-BCDF-4064-9853-6C0D11D291F5',
'6AF3F788-96B9-44BA-9FE5-3176E6498566','05CE44A9-458C-4C8C-BAD9-4222AE746764',
'BC3924A1-92B1-4DB8-952E-326BDBC389EC','BA8B62C7-92F1-41B5-B72E-82340F9E9789',
'5BA4AFA0-A65E-42C1-BB5C-914B2FBC01C4','C582E7BD-109D-4269-9EF8-D58079F1AB5B',
'7392BD24-F5D5-4E90-95AA-D4DE53F4E4AC','6882BA3D-CC7C-455C-AE0C-AA557ECB4B9F',
'44D92206-259A-4B2F-820E-B7A1DF3D5413','44D92206-259A-4B2F-820E-B7A1DF3D5413',
'353B074B-8212-4346-8798-D85D3F5782F3','5813827E-E4F5-40BD-8043-19E139B2D009',
'BB87D105-7344-4EC5-9522-33B1B9129A1A','0832EFF6-9FD5-43F3-B8E5-6A54B5356474',
'89994A12-10BB-4928-B6FB-D95385D6BE1C',
'490F7AEF-0321-4E0B-AB49-6E8DBFF98F96','95BB30D1-FC5B-48FB-8112-6A581F7791F9',
'D9667006-14B0-4062-B356-882F27DEE0A9','2218E642-3405-4921-8548-0F16D77BB796',
'65CBDF5C-A246-4E8D-AE29-649DB430097E','07BA687D-C2B7-4E34-903A-A752172CF99C',
'41ABD064-AC7B-45FF-BED2-3E7E48619C7A','14612C71-D01E-4BE3-AEDB-1D24D8D2D451',
'CA0A2680-694E-4802-8B68-48F966F1C1FB','87257125-2FD1-4746-A3B1-268F049598AF',
'ED3E0E06-1347-46CB-BB00-10E7D4028196',
'3CC584E8-6EEA-459D-80EF-FA54D87129C8','BEFBBA87-9805-47E5-9F75-E57188681491',
'1477031E-1AE6-42D7-8EB2-74B81B315203','D37C7A10-E84F-497D-B6A5-CC386CB213BC',
'2A05F4D8-4A13-41DF-9E74-B9DDBDABE00E','E60D373F-0FB1-40D1-B282-C1EFB67A5B8F',
'09F01D84-9122-4E3C-8F7D-25A94E49028F','9C2976E1-D518-4566-9ADB-1776E4266B36',
'4D9ECAA2-4AF6-48D0-8644-D3B895AACE96','42391C41-FA9C-4128-971E-208427BCCBA0',
'D6ECFCE3-3044-4F40-875D-92DE56FFFABA','E01C88C3-C103-44BE-AC17-5470340CDC82',
'27B54295-307E-4452-BB96-E6D522F2E665','1DEBC3F5-DDE5-416B-84AA-460362B2F373',
'62CE872B-596E-49DA-A6A1-C5FA68DD9FB2','D3546E38-367B-42E5-B2C2-70C7A31519DD',
'6513BA4D-56D6-4887-A068-56BFE2C20407','2C7B6697-575B-44F0-8500-3F49F05D6261',
'4A747284-2510-4941-943C-F685060DD99B','E5B6D658-E90D-4761-AA57-DBB07AB4B080',
'8AB85C47-9AC2-483F-B931-40E0ADF285FB','FC7DA9CB-21DE-4378-91D2-4524651D3136',
'78B79CE4-E5A6-42B5-A6E0-E10719E2FB38','7573530B-8A08-48F0-B709-EA70000A5E3F',
'5943D6FC-47AE-454A-A9F2-40E8CD787CC1','78497881-9AAC-4C04-968A-DBB6A2EC91D4',
'1DA5D11B-E707-4727-94B3-6F355D24A974','A43F194B-4C29-4CF3-9414-AE8D83D82A58',
'EA33827F-36E7-4CC9-96B0-E396379A2DE4','CCE37C2B-0B48-47E1-BC64-DA276900AAF1',
'79F52952-08C7-45AA-95AA-425B291D6CB1','908C03A3-2DBE-470C-A21E-E60BBD82D67E',
'5A3AF360-B6BC-4037-AEE4-8C1403AE1F2C','0747FB1E-0ECB-4434-B543-6125AD8AF3EF',
'4FA5C298-4D1F-4484-BEA0-B2B5C017A3C5','F194D9DD-0715-4CA1-88C1-BF65BB5910D1',
'44B3B39F-5275-4BAE-9702-50CDD116B029','86D4FF4E-7D77-4FE4-BE8A-96B1539634DE',
'2031D526-8515-4414-A43B-8DF75F481FEF','A0125BCB-F722-4FD1-B6FD-C78873410FD8',
'3FE3449C-F7BE-4E94-AC55-7DE4DC546359','E2CC43F8-0CB2-458C-AB4F-115825BB2A47',
'BF52C5AA-4647-481D-B8F3-69F33F00A5FC','6877C446-5BCC-4C8E-A9B4-FB825913FB3F',
'6C07EE3E-91FA-488D-9D73-24E639C21FCA','23244866-7D2D-4603-A326-C7917E9B2262',
'BA33CBDB-231B-489A-A992-64A9267CAAB5','8E78D47B-CE38-4D3E-8097-E42CD0978F79',
'024BD1FC-E044-4E90-9EDB-C03BA6EACD2E','64DF6BAE-CD35-441A-8870-D4DE4B88C185',
'C62F1778-501C-4781-A199-9E3C78E475CB','DD458ED8-2428-4103-A83A-2DEE151A1B5C',
'381BA70C-FE41-45E8-88B1-007FE5953EE5','836F3FE3-2A78-4665-828C-AA38D4ABCE96',
'7960BB5D-B673-47C9-BFBB-D51AB292B08B','31C79BF7-C05F-41F2-BD9A-24B9660307D8',
'CA9C884D-2541-4143-BAC9-C93FF7285B51','1CFEAD97-F9EF-4584-825E-AE362EDF8C1A',
'E3050EA9-9811-403C-A49F-9D80E5ED6E83','E10817A9-39E7-40F8-9A4A-92ADE655BD3A',
'E33B0E06-E699-4888-96ED-09D8CF22A554','33ED903F-2F43-4C31-AC1B-11AB1CEFE028',
'4FD16EA8-8667-4EC5-8DB5-01CCEED14FBC','01F6021F-57B1-47B5-8522-D54D4B629B61',
'86D2AB71-4F14-4424-94D8-7C14023CCB43','783AD6D1-B08E-4903-8DDB-92AA86DF79B9',
'CA0928E9-DA86-42E9-90B8-85BC80507FAF','3CFAA1A0-AF9D-42A9-B629-804C4D5CC949')
--Ненецкий АО
--удалил  +7 (915) 981-78-11  2017-11-07 11:36:56.7653197 +03:00  9D0ACF06-0AD1-48BC-BFCD-19FEBF67BAB5    NULL    NULL    Республика Коми   11:19:0201000:008:87:422:001:004605210  NULL    ol169@yandex.ru
--d7b607a9-ba68-4709-af7b-8547062f8e44
--ef3d25eb-f3c4-4c07-b73d-6928720a0113


--and Cadastral='74:36:0315003:1708'
--  and o.Email = 'irinasab78@mail.ru'
    --and RequestNumber = '80-12601007'
order by o.DateOfPayment desc

--delete from BasketItemService where BasketItemID in (select id from BasketItem where basketID='332b9b76-af6e-5bf1-1d35-8ba972f6a782')
--delete from BasketItem where basketid = '332b9b76-af6e-5bf1-1d35-8ba972f6a782'

select id, DateOfDownloadedPdf, DateOfEmailSended from [order] where id in (select distinct oi.orderid from OrderItem oi
inner join [Order] as o on o.ID = oi.OrderID
left join [OrderEgrnRegistry] as oer on oer.OrderID = oi.OrderID
where oi.OrderID in (select id from [Order] where DateOfPayment is not null or OrderPackageID is not null)
and (ResultFileID is not null AND o.DateOfEmailSended is null)
and DateOfPayment > '2017-12-20'
and oer.id is null
and Cadastral not in ('16:24:010401:1350','11') and Price != 5 and
 oi.OrderID not in ('C8BC8ADE-D145-42DC-8277-7DB598FAC9A0','FE0793B1-F2C1-43BA-B762-11B35F4F0364'))
 --order by DateTimeSendOrder

 --76:20:000000:0000:00392501:0008
select Region, Count(*) c from OrderItem where OrderID in (select id from [Order] where DateOfPayment is not null or OrderPackageID is not null )
and Cadastral not in ('16:24:010401:1350','11') and Price != 5
group by Region order by c desc
/*/F6F652A6-6B5A-4C6B-BBC6-B701B3BC21FF - заявка не сохранилась, но заявки были созданы
//A843026E-1D4D-4A06-942F-C39D4D0974EA, F3DF3254-1582-43F5-9A27-E05AB1D4403B, 4DD72516-BFBE-440C-8A60-E683A9C71002 - не все заявки были созданы
/*5143DBFE-96E7-45A1-BF9B-B30D52A2402C*/

select o.Phone, o.ID,RequestNumber, oi.DateTimeSendOrder, Region, Cadastral, DateTimeSendOrder, o.Email, sc.Name
from OrderItem oi
inner join [Order]  o ON o.ID = OrderID
inner join [Service] s ON s.ID = ServiceID
inner join ServiceCategory sc ON sc.id = s.ServiceCategoryID
where orderid in (select id from [Order] where DateOfPayment is not null )
and o.Email = 'lana-landysheva@mail.ru'
    --and RequestNumber = '80-12601007'
    --and Cadastral='08:14:030605:212'
order by oi.DateTimeSendOrder desc
*/

--80-12440655   Новосибирская область   54:01:01чё0102:126    2017-08-14 18:51:02.4762660 +03:00  +79656014711    NULL

--cinci555@mail.ru
--mupukdez.tver@gmail.com
--steck08@mail.ru


--непробитые чеки
SELECT TOP (200) ID, Sum, Email, Phone, DateOfPayment, DateTimeCreate, DateTimeLastChange, CreatedUserID, FiscalPrinted, EmailSended, DateOfEmailSended, DateOfSmsSended, FiscalContact, FiscalPrintedDate,
 DateOfDownloadedPdf, PromoCodeID, PaymentType, Notification, OrderTypeID, InvoiceNumber, PayComment, RepeateSendEgrnComment
FROM [Order]
WHERE (FiscalPrintedDate IS NULL) AND (PromoCodeID IS NULL) AND (PayComment IS NULL)
ORDER BY DateOfPayment DESC

Select * from OrderItem Where Status=N'Не найден'