API tools faq
paste
Advertisement
bytecoded

Top 5 Query (1C)

bytecoded
Nov 9th, 2019
404
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.64 KB | None | 0 0
raw download clone embed print report
  1. SELECT ALLOWED
  2. CASE
  3. WHEN SalesTurnovers.Recorder = UNDEFINED
  4. THEN NULL
  5. ELSE SalesTurnovers.Recorder
  6. END AS Recorder,
  7. SalesTurnovers.Item AS Item,
  8. SalesTurnovers.Item AS ItemWithSKU,
  9. SalesTurnovers.Item.Description AS ItemPresentation,
  10. CASE
  11. WHEN SalesTurnovers.Characteristic = VALUE(Catalog.ItemCharacteristics.EmptyRef)
  12. THEN CASE
  13. WHEN SalesTurnovers.Batch <> VALUE(Catalog.ItemBatches.EmptyRef)
  14. THEN "N/A"
  15. ELSE NULL
  16. END
  17. ELSE SalesTurnovers.Characteristic
  18. END AS Characteristic,
  19. SalesTurnovers.Characteristic AS CharacteristicGrossProfit,
  20. CASE
  21. WHEN SalesTurnovers.Batch = VALUE(Catalog.ItemBatches.EmptyRef)
  22. THEN NULL
  23. ELSE SalesTurnovers.Batch
  24. END AS Batch,
  25. SalesTurnovers.Batch AS BatchGrossProfit,
  26. SalesTurnovers.Document AS Document,
  27. SalesTurnovers.VATRate AS VATRate,
  28. SalesTurnovers.Entity AS Entity,
  29. SalesTurnovers.CustomerOrder AS CustomerOrder,
  30. SalesTurnovers.QuantityTurnover AS Quantity,
  31. SalesTurnovers.AmountTurnover AS Amount,
  32. SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover AS AmountWithoutVAT,
  33. SalesTurnovers.VATAmountTurnover AS VATAmount,
  34. SalesTurnovers.CostPriceTurnover AS CostPrice,
  35. SalesTurnovers.Item.UnitOfMeasure AS ItemUnitOfMeasure,
  36. SalesTurnovers.Item.SKU AS ItemSKU,
  37. Companies.Ref AS Company,
  38. SalesTurnovers.Project AS Project,
  39. SalesTurnovers.Document.Date AS DocumentDate,
  40. SalesTurnovers.Document.Number AS DocumentNumber,
  41. SalesTurnovers.CustomerOrder.Date AS CustomerOrderDate,
  42. SalesTurnovers.CustomerOrder.Number AS CustomerOrderNumber,
  43. SalesTurnovers.WeekPeriod AS WeekPeriod,
  44. SalesTurnovers.MonthPeriod AS MonthPeriod,
  45. SalesTurnovers.QuarterPeriod AS QuarterPeriod,
  46. SalesTurnovers.HalfYearPeriod AS HalfYearPeriod,
  47. SalesTurnovers.YearPeriod AS YearPeriod,
  48. CASE
  49. WHEN SalesTurnovers.Item.Commissionable
  50. THEN SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover
  51. ELSE 0
  52. END AS AmountWithoutVATCI,
  53. CASE
  54. WHEN NOT SalesTurnovers.Item.Commissionable
  55. THEN SalesTurnovers.AmountTurnover - SalesTurnovers.VATAmountTurnover
  56. ELSE 0
  57. END AS AmountWithoutVATNCI,
  58. 0 AS Payment,
  59. SalesTurnovers.Document.Department AS Department,
  60. NULL AS AmountCancelled
  61. INTO Sales
  62. FROM
  63. AccumulationRegister.Sales.Turnovers(, , Auto, ) AS SalesTurnovers
  64. LEFT JOIN Catalog.Companies AS Companies
  65. ON SalesTurnovers.Document.Company = Companies.Ref
  66.  
  67. UNION ALL
  68.  
  69. SELECT
  70. AccountsReceivableDeferredTurnovers.Recorder,
  71. NULL,
  72. NULL,
  73. NULL,
  74. NULL,
  75. NULL,
  76. NULL,
  77. NULL,
  78. AccountsReceivableDeferredTurnovers.Document,
  79. NULL,
  80. AccountsReceivableDeferredTurnovers.Entity,
  81. NULL,
  82. NULL,
  83. NULL,
  84. NULL,
  85. NULL,
  86. NULL,
  87. NULL,
  88. NULL,
  89. AccountsReceivableDeferredTurnovers.Company,
  90. NULL,
  91. NULL,
  92. NULL,
  93. NULL,
  94. NULL,
  95. NULL,
  96. NULL,
  97. NULL,
  98. NULL,
  99. NULL,
  100. NULL,
  101. NULL,
  102. AccountsReceivableDeferredTurnovers.AmountExpense,
  103. AccountsReceivableDeferredTurnovers.Document.Department,
  104. NULL
  105. FROM
  106. AccumulationRegister.AccountsReceivableDeferred.Turnovers(, , Auto, &IncludingCheques) AS AccountsReceivableDeferredTurnovers
  107. WHERE
  108. AccountsReceivableDeferredTurnovers.Recorder REFS Document.ChequeReceipt
  109.  
  110. UNION ALL
  111.  
  112. SELECT
  113. CustomerAccountsTurnovers.Recorder,
  114. NULL,
  115. NULL,
  116. NULL,
  117. NULL,
  118. NULL,
  119. NULL,
  120. NULL,
  121. CustomerAccountsTurnovers.Document,
  122. NULL,
  123. CustomerAccountsTurnovers.Entity,
  124. NULL,
  125. NULL,
  126. NULL,
  127. NULL,
  128. NULL,
  129. NULL,
  130. NULL,
  131. NULL,
  132. CustomerAccountsTurnovers.Company,
  133. NULL,
  134. NULL,
  135. NULL,
  136. NULL,
  137. NULL,
  138. NULL,
  139. NULL,
  140. NULL,
  141. NULL,
  142. NULL,
  143. NULL,
  144. NULL,
  145. CustomerAccountsTurnovers.AmountExpense,
  146. CustomerAccountsTurnovers.Document.Department,
  147. NULL
  148. FROM
  149. AccumulationRegister.CustomerAccounts.Turnovers(, , Auto, ) AS CustomerAccountsTurnovers
  150. WHERE
  151. CASE
  152. WHEN &IncludingCheques
  153. THEN NOT CustomerAccountsTurnovers.Recorder.DocumentBasis REFS Document.ChequeReceipt
  154. ELSE TRUE
  155. END
  156. AND (CustomerAccountsTurnovers.Recorder REFS Document.PettyCashReceipt
  157. OR CustomerAccountsTurnovers.Recorder REFS Document.PaymentReceipt)
  158.  
  159. UNION ALL
  160.  
  161. SELECT
  162. AccountsReceivableDeferredTurnovers.Recorder,
  163. NULL,
  164. NULL,
  165. NULL,
  166. NULL,
  167. NULL,
  168. NULL,
  169. NULL,
  170. AccountsReceivableDeferredTurnovers.Document,
  171. NULL,
  172. AccountsReceivableDeferredTurnovers.Entity,
  173. NULL,
  174. NULL,
  175. NULL,
  176. NULL,
  177. NULL,
  178. NULL,
  179. NULL,
  180. NULL,
  181. AccountsReceivableDeferredTurnovers.Company,
  182. NULL,
  183. NULL,
  184. NULL,
  185. NULL,
  186. NULL,
  187. NULL,
  188. NULL,
  189. NULL,
  190. NULL,
  191. NULL,
  192. NULL,
  193. NULL,
  194. NULL,
  195. AccountsReceivableDeferredTurnovers.Document.Department,
  196. AccountsReceivableDeferredTurnovers.AmountReceipt
  197. FROM
  198. AccumulationRegister.AccountsReceivableDeferred.Turnovers(, , Auto, &IncludingCheques) AS AccountsReceivableDeferredTurnovers
  199. WHERE
  200. AccountsReceivableDeferredTurnovers.Recorder REFS Document.ChequeCancellation
  201. ;
  202.  
  203. ////////////////////////////////////////////////////////////////////////////////
  204. SELECT ALLOWED
  205. CustomerAccountsBalance.Company AS Company,
  206. CustomerAccountsBalance.Entity AS Entity,
  207. SUM(CustomerAccountsBalance.AmountBalance) AS AmountBalance,
  208. CustomerAccountsBalance.Document.Department AS Department,
  209. CustomerAccountsBalance.Document AS Document
  210. INTO CustomerBalance
  211. FROM
  212. AccumulationRegister.CustomerAccounts.Balance(
  213. ,
  214. (Company, Entity) IN
  215. (SELECT
  216. Sales.Company AS Company,
  217. Sales.Entity AS Entity
  218. FROM
  219. Sales AS Sales)) AS CustomerAccountsBalance
  220.  
  221. GROUP BY
  222. CustomerAccountsBalance.Entity,
  223. CustomerAccountsBalance.Company,
  224. CustomerAccountsBalance.Document,
  225. CustomerAccountsBalance.Document.Department
  226.  
  227. UNION ALL
  228.  
  229. SELECT
  230. AccountsReceivableDeferredBalance.Company,
  231. AccountsReceivableDeferredBalance.Entity,
  232. SUM(AccountsReceivableDeferredBalance.AmountBalance),
  233. AccountsReceivableDeferredBalance.Document.Department,
  234. AccountsReceivableDeferredBalance.Document
  235. FROM
  236. AccumulationRegister.AccountsReceivableDeferred.Balance(
  237. ,
  238. (Company, Entity) IN
  239. (SELECT
  240. Sales.Company AS Company,
  241. Sales.Entity AS Entity
  242. FROM
  243. Sales AS Sales)) AS AccountsReceivableDeferredBalance
  244. WHERE
  245. &IncludingCheques
  246.  
  247. GROUP BY
  248. AccountsReceivableDeferredBalance.Entity,
  249. AccountsReceivableDeferredBalance.Company,
  250. AccountsReceivableDeferredBalance.Document,
  251. AccountsReceivableDeferredBalance.Document.Department
  252. ;
  253.  
  254. ////////////////////////////////////////////////////////////////////////////////
  255. SELECT ALLOWED
  256. Sales.Entity AS Entity,
  257. SUM(Sales.Amount) AS Amount,
  258. SUM(Sales.AmountWithoutVAT) AS AmountWithoutVAT,
  259. Sales.Document.Responsible AS Responsible,
  260. Sales.Document.SalesExecutive AS SalesExecutive,
  261. Sales.Company AS Company,
  262. Sales.MonthPeriod AS MonthPeriod,
  263. Sales.QuarterPeriod AS QuarterPeriod,
  264. Sales.HalfYearPeriod AS HalfYearPeriod,
  265. Sales.YearPeriod AS YearPeriod,
  266. SUM(Sales.AmountWithoutVATCI) AS AmountWithoutVATCI,
  267. SUM(Sales.AmountWithoutVATNCI) AS AmountWithoutVATNCI,
  268. SUM(Sales.Payment) AS Payment,
  269. NULL AS ARBalance,
  270. Sales.Department AS Department,
  271. Sales.Document AS Document,
  272. Sales.Document.Date AS DocumentDate,
  273. Sales.Document.Number AS DocumentNumber,
  274. SUM(CASE
  275. WHEN Sales.Document.Date < DATETIME(2018, 1, 1)
  276. THEN Sales.Payment
  277. ELSE 0
  278. END) AS PaymentOB,
  279. SUM(CASE
  280. WHEN Sales.Document.Date >= DATETIME(2018, 1, 1)
  281. OR Sales.Document.Date IS NULL
  282. THEN Sales.Payment
  283. ELSE 0
  284. END) AS PaymentWOB,
  285. SUM(Sales.AmountCancelled) AS AmountCancelled,
  286. SUM(CASE
  287. WHEN Sales.Document.Date < DATETIME(2018, 1, 1)
  288. THEN Sales.Payment
  289. ELSE Sales.Payment / (1 + &VATRateByDefault / 100)
  290. END) AS PaymentExclVAT
  291. FROM
  292. Sales AS Sales
  293. WHERE
  294. CASE
  295. WHEN &RespType = 0
  296. THEN Sales.Document.Responsible = &Employee
  297. OR Sales.Document.SalesExecutive = &Employee
  298. WHEN &RespType = 1
  299. THEN Sales.Document.Responsible = &Employee
  300. AND Sales.Document.SalesExecutive <> &Employee
  301. WHEN &RespType = 2
  302. THEN Sales.Document.Responsible <> &Employee
  303. AND Sales.Document.SalesExecutive = &Employee
  304. WHEN &RespType = 3
  305. THEN Sales.Document.Responsible = &Employee
  306. AND Sales.Document.SalesExecutive = &Employee
  307. ELSE TRUE
  308. END
  309.  
  310. GROUP BY
  311. Sales.Company,
  312. Sales.YearPeriod,
  313. Sales.HalfYearPeriod,
  314. Sales.MonthPeriod,
  315. Sales.Entity,
  316. Sales.QuarterPeriod,
  317. Sales.Department,
  318. Sales.Document,
  319. Sales.Document.Date,
  320. Sales.Document.Number,
  321. Sales.Document.Responsible,
  322. Sales.Document.SalesExecutive
  323.  
  324. UNION ALL
  325.  
  326. SELECT
  327. CustomerBalance.Entity,
  328. NULL,
  329. NULL,
  330. CustomerBalance.Document.Responsible,
  331. CustomerBalance.Document.SalesExecutive,
  332. CustomerBalance.Company,
  333. NULL,
  334. NULL,
  335. NULL,
  336. NULL,
  337. NULL,
  338. NULL,
  339. NULL,
  340. SUM(CustomerBalance.AmountBalance),
  341. CustomerBalance.Department,
  342. CustomerBalance.Document,
  343. CustomerBalance.Document.Date,
  344. CustomerBalance.Document.Number,
  345. NULL,
  346. NULL,
  347. NULL,
  348. NULL
  349. FROM
  350. CustomerBalance AS CustomerBalance
  351. WHERE
  352. CASE
  353. WHEN &RespType = 0
  354. THEN CustomerBalance.Document.Responsible = &Employee
  355. OR CustomerBalance.Document.SalesExecutive = &Employee
  356. WHEN &RespType = 1
  357. THEN CustomerBalance.Document.Responsible = &Employee
  358. AND CustomerBalance.Document.SalesExecutive <> &Employee
  359. WHEN &RespType = 2
  360. THEN CustomerBalance.Document.Responsible <> &Employee
  361. AND CustomerBalance.Document.SalesExecutive = &Employee
  362. WHEN &RespType = 3
  363. THEN CustomerBalance.Document.Responsible = &Employee
  364. AND CustomerBalance.Document.SalesExecutive = &Employee
  365. ELSE TRUE
  366. END
  367.  
  368. GROUP BY
  369. CustomerBalance.Company,
  370. CustomerBalance.Document,
  371. CustomerBalance.Entity,
  372. CustomerBalance.Department,
  373. CustomerBalance.Document.Date,
  374. CustomerBalance.Document.Number,
  375. CustomerBalance.Document.Responsible,
  376. CustomerBalance.Document.SalesExecutive
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!