· 10 years ago · Aug 26, 2016, 06:42 PM
1Rodrigo Maia [3:12 PM]
2Hey Phil. Let me know when I may ask some questions for you.
3
4Phil Romov [3:12 PM]
5now is a good time
6
7Rodrigo Maia [3:13 PM]
8sweet :-)
9
10[3:13]
11So, I need some directions with Statement Reports.
12
13[3:14]
14Accord I understood, the report will represent a defined period, like a month.
15
16[3:14]
17is that?
18
19Phil Romov [3:14 PM]
20do we have ticket(s) for it?
21
22Rodrigo Maia [3:15 PM]
23Yes, I created some tickets in order to break the big one.
24
25[3:15]
26let me send the link
27
28Phil Romov [3:15 PM]
29thanks
30
31Rodrigo Maia [3:15 PM]
32https://sesac-tech.atlassian.net/browse/SR-18
33
34Phil Romov [3:18 PM]
35no, I think the report is by check number
36
37[3:18]
38and I think some checks can reprsent one period, or more than one period
39
40Rodrigo Maia [3:19 PM]
41I'll need some help to understand this :-S
42
43Phil Romov [3:19 PM]
44uploaded and commented on an image: Pasted image at 2016-08-24, 2:19 PM
451 Comment
46for example, this is the current statement report
47
48Phil Romov [3:20 PM]
49and when I click the first “Check details†button (first row, all the way on the right, here’s what I get)
50```LICENSEE PUBLISHER MFR PUBL. ID. MFR TUNE# SONG TITLE RECORD NO. SALE TYPE UNITS ROYALTY RATE ROYALTY AMT PERIOD END(MMDDYY) LICENSEE PUB NAME LICENSEE NAME PUB NAME MISC ALBUM WRITER(S) HFA SONG PERIOD END(YYYYMMDD) LICENSE # CHECK # MISC-2 GROUP PAY CODE PAYABLE AMT HFA SPLIT MFR SPLIT MEDIA TYPE 3RD PARTY INFO FOREIGN AMOUNT EXCHANGE RATE LABEL CONFIG PUB SONG ID INFO FILLER(MISC) PERIOD CODE ISRC PLAY MINUTES PLAY SECONDS UPC ISWC SERVICE OFFERING ARTIST NAME MICRO PENNY PORTION AMT W/MICRO PENNY DEPT PRODUCT TYPE FRGN.TAX DETAIL FRGN.TAX RATE FOREIGN CURRENCY COMMISSION RATE COMMISSION AMOUNT PAYABLE AMT NET TRANSACTION DESC. GROUPING CRITERIA SALE TYPE DESCR. ADDITIONAL MFR PUB#S SONG FILE CARD HOLDER ORIGINAL TRX # HFA RATE CODE AGREEMENT CODE FLOOR INDICATION FLAG DIR DISTRIBUTION FLAG SONY UNITS UNITS % FOR SONY
51M17712 P61115 PORTRAIT 2 0.091000 0.1800 13114 X5 MUSIC GROUP MASONG MUSIC 010114 201401 TOP SMOOTH JAZZ MASON, RITENOUR P67035 20140131 1213188179 7493910 58244006*Amazon*1 568 0.1800 100.0000 100.0000 DIGITAL MC Amazon 0.0000 0.00000 DP * 010114 USGR19900181 2 35 7340070477030 LEE RITENOUR AND YELLOWJACKETS 0.0000 0.1800 MC Digital 0.0000 0.00000 0.0000 0.0000 0.9000 STANDARD ROYALTY 1 3022245739 S 0 0.0000
52M17712 P61115 AMARETTO 1 0.045500 0.0500 13116 X5 MUSIC GROUP MASONG MUSIC 010116 201601 THE MOST RELAXING JAZZ SAXOPHO MASON, LANG A47020 20160131 1213187003 7493910 61292918*iTunes*1 500 0.0500 50.0000 50.0000 DIGITAL MC iTunes 0.0000 0.00000 DP * 010116 USGR10110408 4 20 7340070474190 TOM SCOTT 0.0000 0.0500 MC Digital 0.0000 0.00000 0.0000 0.0000 1.7100 STANDARD ROYALTY 1 3049836215 S 0 0.0000
53M17712 P61115 PORTRAIT 1 0.091000 0.0900 22814 X5 MUSIC GROUP MASONG MUSIC 010214 201402 TOP SMOOTH JAZZ MASON, RITENOUR P67035 20140228 1213185099 7493910 58340213*Amazon*1 618 0.0900 100.0000 100.0000 DIGITAL MC Amazon 0.0000 0.00000 SP * 010214 USGR19900181 2 35 7340070477030 LEE RITENOUR AND YELLOWJACKETS 0.0000 0.0900 MC Digital 0.0000 0.00000 0.0000 0.0000 0.6300 STANDARD ROYALTY 1 3022246032 S 0 0.0000
54```
55
56[3:20]
57so just more detail, by check number
58
59[3:20]
60does that make sense?
61
62Rodrigo Maia [3:20 PM]
63not yet :-(
64
65[3:21]
66so, who access this report?
67
68[3:21]
69I mean, what is the group level?
70
71[3:21]
72all artists from a company
73
74[3:21]
75all sound_recordings from a catalog?
76
77Phil Romov [3:23 PM]
78I think publishers access this report...
79
80[3:23]
81the group level is a select distinct
82
83[3:23]
84so its “select distinct check_number, net_amount, payee, payer, check cut date"
85
86[3:24]
87and then when you download statement detail for a check, you get “select * where check_num = this check num"
88
89Rodrigo Maia [3:25 PM]
90My fault, my terrible english :-S
91
92[3:25]
93when I said Group Level, I mean what is the entity over all data used on report
94
95[3:26]
96I mean, if is a publisher, all data on report will be related with this publisher for a period.
97
98Phil Romov [3:26 PM]
99oh sorry
100
101[3:26]
102there’s a where clause
103
104[3:26]
105so the user’s account is the entity
106
107[3:26]
108if it begins with “H†(e.g. H00001 or HFA account) then all data is shown
109
110[3:27]
111otherwise, (e.g. P12345 or any publisher P#) then you select “where payee = this user’s P#"
112
113[3:27]
114another global entity is: check_cut_date != 0 and check_number != 0
115
116Rodrigo Maia [3:30 PM]
117It's obscured for a while.
118
119[3:30]
120Where do I get these infos?
121
122Phil Romov [3:31 PM]
123it should be on oracle but I haven’t connected yet
124
125[3:32]
126I got this connection information from Todd, maybe you can try and see if it works for you
127```Your application's credentials are:
128username: apcts_rs
129password: apctsrs400
130
131CTSDEV=
132 (DESCRIPTION=
133 (ADDRESS=
134 (PROTOCOL=TCP)
135 (HOST=actsdadl.sesac.com)
136 (PORT=1527)
137 )
138 (CONNECT_DATA=
139 (SERVICE_NAME=CTSDEV.sesac.com)
140 )
141 )
142
143```
144
145Rodrigo Maia [3:32 PM]
146So, when some data is on Oracle what we used to do?
147
148Phil Romov [3:32 PM]
149we connect to it and query the report :slightly_smiling_face:
150
151Rodrigo Maia [3:32 PM]
152sweet
153
154Phil Romov [3:32 PM]
155table names should be: `mc_statement_details` and `mc_payment_details`
156
157[3:33]
158let me know if you have success connecting to that from ruby
159
160[3:33]
161and any questions remaining
162
163Rodrigo Maia [3:44 PM]
164Thanks :-)
165
166[3:44]
167another question, I want create a table in order to insert reports generated
168
169[3:45]
170I was thinking about `link.statement_reports`
171
172[3:46]
173save the entity (user, publisher, artist), the period (as you said, the check) and the status (ordered, builded, archived etc.)
174
175[3:47]
176if a user ask for the same report twice, we can return the generated report or avoid generate twice the same report.
177
178Phil Romov [3:48 PM]
179hmm, I don’t know if caching it in db is a good idea
180
181[3:48]
182because your report is generated from a select anyway
183
184Rodrigo Maia [3:48 PM]
185no, no. I don't want save the entire report, just the request to create it.
186
187Phil Romov [3:49 PM]
188oh yeah
189
190Rodrigo Maia [3:49 PM]
191The final report, I was considering save on AWS S3
192
193[3:49]
194The goal of this table is: If one user requires to generate one report, we save on it.
195
196[3:49]
197if another user request the same report, we respond with the link of s3 instead generate it again.
198
199Phil Romov [3:50 PM]
200sounds good
201
202[3:50]
203are you also using a queue to schedule report downloads?
204
205Rodrigo Maia [3:50 PM]
206It's planned to use.
207
208[3:50]
209I did't reach this step yet :-P
210
211Phil Romov [3:50 PM]
212i was thinking, maybe instead of saving request to `link.statement_reports` you can save it to the queue
213
214[3:50]
215i’m not sure what you save by also storing it in `link.statement_reports`
216
217Rodrigo Maia [3:51 PM]
218the worker will get the message from queue and check if the report already exists.
219
220[3:51]
221if exists, return the link, else generate it.
222
223Phil Romov [3:52 PM]
224I guess that works
225
226Rodrigo Maia [3:52 PM]
227Looks like I can check on s3 if already exists.
228
229Phil Romov [3:52 PM]
230yeah that’s what I was thinking
231
232[3:52]
233might not need `link.statement_reports` then, keep it simpler
234
235Rodrigo Maia [3:52 PM]
236I'll try on this way, checking if it already exists.
237
238[3:53]
239If I have some trouble, I'll back to this topic.
240
241[3:54]
242thanks for the infos :-)
243
244[3:54]
245I have enought to don't disturb you for today and tomorrow :-P