API tools faq
paste
Advertisement
Guest User

выгрузка

a guest
Jun 27th, 2019
104
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 25.62 KB | None | 0 0
raw download clone embed print report
  1. /*Нужно отображать данные по одной записи интерфакс через ; в одну ячейку. для ms sql ниже 2017 нет string_agg
  2. пришлось пользоваться STUFF + FOR XML PATH*/
  3.  
  4.  
  5. /*- первый блок выгрузки ЮЛ с ИНН isMain = 1*/
  6. Select Distinct top 2000
  7. [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  8. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSTEMID]
  9. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'person', N'ФЛ' ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'company', N'ЮЛ' ,N'' ) ) as TypeRecord
  10. ,IFINN.VALUE as INN
  11.  
  12. ,myOGRNList as OGRN
  13.  
  14. ,[FULLNAME]
  15. ,myCategorylist
  16.  
  17. ,mySanlist
  18. ,myProgramlist
  19. ,myWatchlist
  20.  
  21.  
  22. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].Id <> '', N'ДА', N'' ) as Rule50
  23.  
  24. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISINFX] = 1, N'ДА', N'' ) as IsInFx
  25. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISSANCTION50] =1 , N'ДА', N'' ) as IsSanction50
  26. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[UPDATEDAT] as UpdatedAt
  27. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSUPDATEDDATE] as SysUpdatedDate
  28. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[STATUS] =1, N'Активна' ,N'Не активна' ) as Status
  29.  
  30. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD]
  31. LEFT JOIN
  32. (Select sanlist_table2.INTERFAXRECORD as mySanListRecordID, mySanlist =
  33. STUFF(
  34. (Select Distinct ';' + sanlist_table1.SanlistStr
  35. from
  36. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME] as SanlistStr
  37. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  38. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table1
  39. where sanlist_table1.INTERFAXRECORD = sanlist_table2.INTERFAXRECORD
  40. FOR XML PATH ('')) , 1, 1, '')
  41. from
  42. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME]
  43. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  44. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table2
  45. group by sanlist_table2.INTERFAXRECORD) as IFSR on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR.mySanListRecordID
  46.  
  47. LEFT JOIN
  48. (Select category_table2.INTERFAXRECORD as mySanListRecordID, myCategorylist =
  49. STUFF(
  50. (Select Distinct ';' + category_table1.CategoryList
  51. from
  52. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  53. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table1
  54.  
  55. where category_table1.INTERFAXRECORD = category_table2.INTERFAXRECORD
  56. FOR XML PATH ('')) , 1, 1, '')
  57. from
  58. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  59. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table2
  60. group by category_table2.INTERFAXRECORD) as IFSR2 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFSR2.mySanListRecordID
  61.  
  62. LEFT JOIN
  63. (Select program_table2.INTERFAXRECORD as mySanListRecordID, myProgramlist =
  64. STUFF(
  65. (Select Distinct ';' + program_table1.ProgramList
  66. from
  67. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  68. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  69. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table1
  70.  
  71. where program_table1.INTERFAXRECORD = program_table2.INTERFAXRECORD
  72. FOR XML PATH ('')) , 1, 1, '')
  73. from
  74. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  75. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  76. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table2
  77. group by program_table2.INTERFAXRECORD) as IFSR3 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR3.mySanListRecordID
  78.  
  79. LEFT JOIN
  80.  
  81. (Select watch_table2.INTERFAXRECORD as mySanListRecordID, myWatchlist =
  82. STUFF(
  83. (Select Distinct ';' + watch_table1.WatchList
  84. from
  85. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  86. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  87. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]) watch_table1
  88.  
  89. where watch_table1.INTERFAXRECORD = watch_table2.INTERFAXRECORD
  90. FOR XML PATH ('')) , 1, 1, '')
  91. from
  92. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  93. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  94. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST] ) watch_table2
  95. group by watch_table2.INTERFAXRECORD) as IFSR4 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR4.mySanListRecordID
  96.  
  97. LEFT JOIN
  98. (Select ogrn_table2.INTERFAXRECORD as mySanListRecordID, myOGRNList =
  99. STUFF(
  100. (Select Distinct ';' + ogrn_table1.OGRNLst
  101. from
  102. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].InterFaxRecord, [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].Value as OGRNLst
  103. from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA]
  104. Where [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].Type = N'ОГРН' ) ogrn_table1
  105.  
  106. where ogrn_table1.INTERFAXRECORD = ogrn_table2.INTERFAXRECORD
  107. FOR XML PATH ('')) , 1, 1, '')
  108. from
  109. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].InterFaxRecord, [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].Value as OGRNLst
  110. from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA]
  111. Where [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].Type = N'ОГРН' ) ogrn_table2
  112. group by ogrn_table2.INTERFAXRECORD) as IFSR5 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR5.mySanListRecordID
  113.  
  114. left join [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].InterFaxRecordChild = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  115. Inner join (Select INTERFAXRECORD, VALUE, isMain from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA] where TYPE = N'ИНН' and ISMAIN = 1) as IFINN on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFINN.INTERFAXRECORD
  116. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  117. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  118. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID]
  119. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  120. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]
  121. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  122. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY]
  123. LEFT JOIN (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[INTERFAXRECORD] as IFRecord, [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[VALUE] as myValue from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA]
  124. where [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE] = N'ОГРН' and [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[ISMAIN] = 0) as IFOGRN
  125. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFOGRN.IFRecord
  126.  
  127.  
  128. /*-добавляем второй блок выгрузки ЮЛ с ИНН isMain <> 1*/
  129.  
  130. UNION
  131.  
  132. SELECT DISTINCT TOP 2000
  133. [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  134. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSTEMID]
  135. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'person', N'ФЛ' ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'company', N'ЮЛ' ,N'' ) ) as TypeRecord
  136. ,IFINN.VALUE as INN
  137. ,'' as OGRN
  138. ,[FULLNAME]
  139. ,myCategorylist
  140. ,mySanlist
  141. ,myProgramlist
  142. ,myWatchlist
  143.  
  144. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].[ID] <> '', N'ДА', N'' ) as Rule50
  145. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISINFX] = 1, N'ДА', N'' ) as IsInFx
  146. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISSANCTION50] =1 , N'ДА', N'' ) as IsSanction50
  147. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[UPDATEDAT] as UpdatedAt
  148. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSUPDATEDDATE] as SysUpdatedDate
  149. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[STATUS] =1, N'Активна' ,N'Не активна' ) as Status
  150.  
  151. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD]
  152. LEFT JOIN
  153. (Select sanlist_table2.INTERFAXRECORD as mySanListRecordID, mySanlist =
  154. STUFF(
  155. (Select Distinct ';' + sanlist_table1.SanlistStr
  156. from
  157. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME] as SanlistStr
  158. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  159. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table1
  160. where sanlist_table1.INTERFAXRECORD = sanlist_table2.INTERFAXRECORD
  161. FOR XML PATH ('')) , 1, 1, '')
  162. from
  163. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME]
  164. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  165. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table2
  166. group by sanlist_table2.INTERFAXRECORD) as IFSR on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR.mySanListRecordID
  167.  
  168. LEFT JOIN
  169. (Select category_table2.INTERFAXRECORD as mySanListRecordID, myCategorylist =
  170. STUFF(
  171. (Select Distinct ';' + category_table1.CategoryList
  172. from
  173. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  174. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table1
  175.  
  176. where category_table1.INTERFAXRECORD = category_table2.INTERFAXRECORD
  177. FOR XML PATH ('')) , 1, 1, '')
  178. from
  179. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  180. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table2
  181. group by category_table2.INTERFAXRECORD) as IFSR2 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR2.mySanListRecordID
  182. LEFT JOIN
  183. (Select program_table2.INTERFAXRECORD as mySanListRecordID, myProgramlist =
  184. STUFF(
  185. (Select Distinct ';' + program_table1.ProgramList
  186. from
  187. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  188. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  189. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table1
  190.  
  191. where program_table1.INTERFAXRECORD = program_table2.INTERFAXRECORD
  192. FOR XML PATH ('')) , 1, 1, '')
  193. from
  194. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  195. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  196. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table2
  197. group by program_table2.INTERFAXRECORD) as IFSR3 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR3.mySanListRecordID
  198. LEFT JOIN
  199.  
  200. (Select watch_table2.INTERFAXRECORD as mySanListRecordID, myWatchlist =
  201. STUFF(
  202. (Select Distinct ';' + watch_table1.WatchList
  203. from
  204. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  205. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  206. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]) watch_table1
  207.  
  208. where watch_table1.INTERFAXRECORD = watch_table2.INTERFAXRECORD
  209. FOR XML PATH ('')) , 1, 1, '')
  210. from
  211. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  212. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  213. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST] ) watch_table2
  214. group by watch_table2.INTERFAXRECORD) as IFSR4 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR4.mySanListRecordID
  215.  
  216. left join [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].InterFaxRecordChild = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  217. /*- если здесь сделать left , то будет выводить по строке с ИНН isMain = 1 и ИНН isMain = 0 запись*/
  218.  
  219. inner join (Select INTERFAXRECORD, VALUE, isMain from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA] where TYPE = N'ИНН' and ISMAIN <>1) as IFINN on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFINN.INTERFAXRECORD
  220.  
  221.  
  222. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  223. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  224. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID]
  225. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  226. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]
  227. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  228. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY]
  229. LEFT JOIN (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[INTERFAXRECORD] as IFRecord, [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[VALUE] as myValue from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA]
  230. where [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE] = N'ОГРН' and [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[ISMAIN] = 0) as IFOGRN
  231. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFOGRN.IFRecord
  232.  
  233.  
  234.  
  235. /*- третий блок - блок выгрузки ФЛ*/
  236. UNION
  237.  
  238. SELECT DISTINCT TOP 2000
  239. [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  240. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSTEMID]
  241. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'person', N'ФЛ' ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'company', N'ЮЛ' ,N'' ) ) as TypeRecord
  242. ,'' as INN
  243. ,'' as OGRN
  244. ,[FULLNAME]
  245. ,myCategorylist
  246. ,mySanlist
  247. ,myProgramlist
  248. ,myWatchlist
  249.  
  250. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].[ID] <> '', N'ДА', N'' ) as Rule50
  251. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISINFX] = 1, N'ДА', N'' ) as IsInFx
  252. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ISSANCTION50] =1 , N'ДА', N'' ) as IsSanction50
  253. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[UPDATEDAT] as UpdatedAt
  254. , [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[SYSUPDATEDDATE] as SysUpdatedDate
  255. ,IIF( [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[STATUS] =1, N'Активна' ,N'Не активна' ) as Status
  256.  
  257. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD]
  258. LEFT JOIN
  259. (Select sanlist_table2.INTERFAXRECORD as mySanListRecordID, mySanlist =
  260. STUFF(
  261. (Select Distinct ';' + sanlist_table1.SanlistStr
  262. from
  263. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME] as SanlistStr
  264. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  265. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table1
  266. where sanlist_table1.INTERFAXRECORD = sanlist_table2.INTERFAXRECORD
  267. FOR XML PATH ('')) , 1, 1, '')
  268. from
  269. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[NAME]
  270. FROM [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] INNER JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST]
  271. ON [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[SANLIST] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANLIST].[ID]) sanlist_table2
  272. group by sanlist_table2.INTERFAXRECORD) as IFSR on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR.mySanListRecordID
  273.  
  274. LEFT JOIN
  275. (Select category_table2.INTERFAXRECORD as mySanListRecordID, myCategorylist =
  276. STUFF(
  277. (Select Distinct ';' + category_table1.CategoryList
  278. from
  279. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  280. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table1
  281.  
  282. where category_table1.INTERFAXRECORD = category_table2.INTERFAXRECORD
  283. FOR XML PATH ('')) , 1, 1, '')
  284. from
  285. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[NAME] as CategoryList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY]
  286. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY] ) category_table2
  287. group by category_table2.INTERFAXRECORD) as IFSR2 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR2.mySanListRecordID
  288. LEFT JOIN
  289. (Select program_table2.INTERFAXRECORD as mySanListRecordID, myProgramlist =
  290. STUFF(
  291. (Select Distinct ';' + program_table1.ProgramList
  292. from
  293. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  294. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  295. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table1
  296.  
  297. where program_table1.INTERFAXRECORD = program_table2.INTERFAXRECORD
  298. FOR XML PATH ('')) , 1, 1, '')
  299. from
  300. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].ProgramTag as ProgramList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION]
  301. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION]
  302. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID] ) program_table2
  303. group by program_table2.INTERFAXRECORD) as IFSR3 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR3.mySanListRecordID
  304. LEFT JOIN
  305.  
  306. (Select watch_table2.INTERFAXRECORD as mySanListRecordID, myWatchlist =
  307. STUFF(
  308. (Select Distinct ';' + watch_table1.WatchList
  309. from
  310. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  311. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  312. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]) watch_table1
  313.  
  314. where watch_table1.INTERFAXRECORD = watch_table2.INTERFAXRECORD
  315. FOR XML PATH ('')) , 1, 1, '')
  316. from
  317. (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].Name as WatchList from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST]
  318. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST]
  319. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST] ) watch_table2
  320. group by watch_table2.INTERFAXRECORD) as IFSR4 on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]= IFSR4.mySanListRecordID
  321.  
  322. left join [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAX50RELATION].InterFaxRecordChild = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  323.  
  324.  
  325. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  326. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  327. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECSANCTION].[SANCTION] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTSANCTION].[ID]
  328. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  329. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTWATHLIST].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECWATHLIST].[WATHLIST]
  330. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[INTERFAXRECORD] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID]
  331. LEFT JOIN [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY] on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXDICTCATEGORY].[ID] = [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXCATEGORY].[CATEGORY]
  332. LEFT JOIN (Select [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[INTERFAXRECORD] as IFRecord, [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE], [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[VALUE] as myValue from [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA]
  333. where [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[TYPE] = N'ОГРН' and [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXREGDATA].[ISMAIN] = 0) as IFOGRN
  334. on [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[ID] = IFOGRN.IFRecord
  335.  
  336.  
  337. WHERE [OUTSYSTEMS].DBO.[OSUSR_JSJ_INTERFAXRECORD].[TYPERECORD] =N'person'
  338.  
  339.  
  340. ORDER by 1 Asc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!