1 #!/bin/sh
2
3 echo "$0 start at `date +"%F %k:%M:%S"`" 1>&2
4
5 cd /www/scripts/stat/
6 . stat_date.sh
7 . dblist.sh
8
9 . public_func
10
11 stat_date=`riqi $1`
12 instime=`date -d"$stat_date" +'%F'`
13 last_date=`date -d"$stat_date 1 day ago" +'%Y%m%d'`
14
15 channel_id="/tmp/channel_reader.tmp"
16
17 echo "select userid,ch_name from ppoem_user_channel_bak;"|statdb -s > $channel_id
18
19 #此文件对应合作方ID和合作方名称见关系
20 user_list="/tmp/ppoem_user"
21
22 echo "select userid,username from ppoem_user;"|statdb -s >$user_list
23
24 ppoem_table="ppoem_payable_data"
25
26 stat_table="channel_book_reader_kyd"
27
28 data_user="data/reader/reader.userid.$stat_date"
29
30 data_file="data/reader/reader.imei.$stat_date"
31
32 #KYD推广位和合作方ID对应关系
33 channel_id="/tmp/channel_reader.tmp"
34
35 echo "select userid,ch_name from ppoem_user_channel_bak;"|statdb -s > $channel_id
36
37 #在数据库中获取USERID与IMEI对应关系
38
39 echo "select userid,imei from attr_reg_imei2userid where insert_time like '$instime%' and reg_time like '$instime%';" |accountdb -s >/tmp/newuser2imei
40 #echo "select userid from attr_mobile2userid;" |accountdb -s >/tmp/attr_mobile2userid
41
42 #判定访问的imei对应的userid是否是真的是注册会员，不能是准会员
43 #attr_userid2imei 这个表里的userid，不一定是注册会员的ID
44 #all_user_id这个表里的数据是注册会员ID。
45
46
47 #if [ 0 -eq 1 ]
48 #then
49
50 #cat /tmp/attr_mobile2userid|awk '
51 #BEGIN{
52 # while(getline<"/tmp/newuser2imei"==1)
53 # {
54 # imei2userid[$1]=$2
55 # }
56 #}
57 #($1 in imei2userid){
58 #
59 # print $1,imei2userid[$1]
60 #
61 #}' >/tmp/newuser2imei_reg
62
63 #fi
64
65 echo "select userid,paytype from ppoem_user_payable_mapping;"|statdb -s >/tmp/ppoem_user_payable_mapping
66
67 ./kcat.sh $data_file|awk '
68 BEGIN{
69 while(getline<"/www/scripts/stat/config/kyd_limit_day"==1)
70 {
71 lkyd_chname[$1]=$2
72 }
73
74 while(getline<"'$channel_id'"==1)
75 {
76 userid[$2]=$1
77 }
78
79 while(getline<"'$user_list'"==1)
80 {
81 user_id_name[$1]=$2
82 }
83
84 while(getline<"/tmp/newuser2imei"==1)
85 {
86 luser2imei[$2]=$1
87 #print luser2imei[$2]
88 }
89
90 while(getline<"'$channel_id'"==1)
91 {
92 userid[$2]=$1
93 }
94
95 while(getline<"/www/scripts/stat/app_open"==1)
96 {
97 appopen[$1" "$2]
98 }
99
100 while(getline<"/www/scripts/stat/app_close"==1)
101 {
102 appclose[$1" "$2]
103 }
104
105 while(getline<"/www/scripts/stat/app_close_channel"==1)
106 {
107 appclose_channel[$1]
108 }
109
110 while(getline<"/www/scripts/stat/app_open_channel"==1)
111 {
112 app_open_channel[$1" "$2]
113 }
114
115 while(getline<"/tmp/ppoem_user_payable_mapping"==1)
116 {
117 pab_user[$1]=$2
118 }
119
120 }
121 {
122 if($2~/kyd_/)
123 {
124 fr=$2
125 imei=$1
126 }
127 else if($2=="-"&&$3~/9000%26fr/)
128 {
129 fr=gensub(/.*%3D(.+)/,"\\1","g",$3)
130 imei=$1
131 }
132 else
133 next
134
135 #print fr":"imei
136
137 all[fr":"imei]+=$5
138
139 if($7=="true")
140 {
141 j_all[fr":"imei]+=$5
142 }
143
144 #if($1 in lbu5_imei)
145 #new_bu5[fr":"imei]+=$5
146
147 if($6=="0")
148 {
149 new_imei[fr":"imei]+=$5
150
151 if($7=="true")
152 {
153 j_newimei[fr":"imei]
154 }
155
156 if($1 in luser2imei)
157 {
158 lfr_newuser[$2":"luser2imei[$1]]
159 if($7=="true")
160 lj_fr_newuser[$2":"luser2imei[$1]]
161 }
162
163 if($5>3)
164 {
165 bu3[fr":"imei]+=$5
166
167 if($7=="true")
168 {
169 j_bu3[fr":"imei]
170 }
171 }
172 if($5>4)
173 {
174 bu4[fr":"imei]+=$5
175
176 if($7=="true")
177 {
178 j_bu4[fr":"imei]
179 }
180 }
181 if($5>5)
182 {
183 bu5[fr":"imei]+=$5
184
185 if($7=="true")
186 {
187 j_bu5[fr":"imei]
188 }
189 }
190 if($5>7)
191 {
192 bu7[fr":"imei]+=$5
193
194 if($7=="true")
195 {
196 j_bu7[fr":"imei]
197 }
198 }
199 if($5>10)
200 {
201 bu10[fr":"imei]+=$5
202
203 if($7=="true")
204 {
205 j_bu10[fr":"imei]
206 }
207 }
208 if($5>15)
209 {
210 bu15[fr":"imei]+=$5
211
212 if($7=="true")
213 {
214 j_bu15[fr":"imei]
215 }
216 }
217 if($5>20)
218 {
219 bu20[fr":"imei]+=$5
220
221 if($7=="true")
222 {
223 j_bu20[fr":"imei]
224 }
225 }
226 if($5>30)
227 {
228 bu30[fr":"imei]+=$5
229
230 if($7=="true")
231 {
232 j_bu30[fr":"imei]
233 }
234 }
235 if($5>50)
236 {
237 bu50[fr":"imei]+=$5
238
239 if($7=="true")
240 {
241 j_bu50[fr":"imei]
242 }
243 }
244 if($5>70)
245 {
246 bu70[fr":"imei]+=$5
247
248 if($7=="true")
249 {
250 j_bu70[fr":"imei]
251 }
252 }
253 if($5>100)
254 {
255 bu100[fr":"imei]+=$5
256
257 if($7=="true")
258 {
259 j_bu100[fr":"imei]
260 }
261 }
262 }
263 }
264 END{
265 for(i in all)
266 {
267 split(i,key,":")
268 all_u[key[1]]++
269 all_p[key[1]]+=all[i]
270 }
271
272 for(i in j_all)
273 {
274 split(i,key,":")
275 j_all_u[key[1]]++
276 }
277
278 for(i in new_imei)
279 {
280 split(i,key,":")
281 new_imei_u[key[1]]++
282 new_imei_p[key[1]]+=new_imei[i]
283 }
284
285 for(i in j_newimei)
286 {
287 split(i,key,":")
288 j_newimei_u[key[1]]++
289 }
290
291 for(i in bu3)
292 {
293 split(i,key,":")
294 bu3_u[key[1]]++
295 bu3_p[key[1]]+=bu3[i]
296 }
297
298 for(i in j_bu3)
299 {
300 split(i,key,":")
301 j_bu3_u[key[1]]++
302 }
303
304 for(i in bu4)
305 {
306 split(i,key,":")
307 bu4_u[key[1]]++
308 bu4_p[key[1]]+=bu4[i]
309 }
310
311
312 for(i in j_bu4)
313 {
314 split(i,key,":")
315 j_bu4_u[key[1]]++
316 }
317
318 for(i in bu5)
319 {
320 split(i,key,":")
321 bu5_u[key[1]]++
322 bu5_p[key[1]]+=bu5[i]
323 }
324
325 for(i in j_bu5)
326 {
327 split(i,key,":")
328 j_bu5_u[key[1]]++
329 }
330
331 for(i in bu7)
332 {
333 split(i,key,":")
334 bu7_u[key[1]]++
335 bu7_p[key[1]]+=bu7[i]
336 }
337
338 for(i in j_bu7)
339 {
340 split(i,key,":")
341 j_bu7_u[key[1]]++
342 }
343
344 for(i in bu10)
345 {
346 split(i,key,":")
347 bu10_u[key[1]]++
348 bu10_p[key[1]]+=bu10[i]
349 }
350
351 for(i in j_bu10)
352 {
353 split(i,key,":")
354 j_bu10_u[key[1]]++
355 }
356
357 for(i in bu15)
358 {
359 split(i,key,":")
360 bu15_u[key[1]]++
361 bu15_p[key[1]]+=bu15[i]
362 }
363
364 for(i in j_bu15)
365 {
366 split(i,key,":")
367 j_bu15_u[key[1]]++
368 }
369
370 for(i in bu20)
371 {
372 split(i,key,":")
373 bu20_u[key[1]]++
374 bu20_p[key[1]]+=bu20[i]
375 }
376
377
378 for(i in j_bu20)
379 {
380 split(i,key,":")
381 j_bu20_u[key[1]]++
382 }
383
384 for(i in bu30)
385 {
386 split(i,key,":")
387 bu30_u[key[1]]++
388 bu30_p[key[1]]+=bu30[i]
389 }
390
391 for(i in j_bu30)
392 {
393 split(i,key,":")
394 j_bu30_u[key[1]]++
395 }
396
397 for(i in bu50)
398 {
399 split(i,key,":")
400 bu50_u[key[1]]++
401 bu50_p[key[1]]+=bu50[i]
402 }
403
404 for(i in j_bu50)
405 {
406 split(i,key,":")
407 j_bu50_u[key[1]]++
408 }
409
410 for(i in bu70)
411 {
412 split(i,key,":")
413 bu70_u[key[1]]++
414 bu70_p[key[1]]+=bu70[i]
415 }
416
417 for(i in j_bu70)
418 {
419 split(i,key,":")
420 j_bu70_u[key[1]]++
421 }
422
423 for(i in bu100)
424 {
425 split(i,key,":")
426 bu100_u[key[1]]++
427 bu100_p[key[1]]+=bu100[i]
428 }
429
430 for(i in j_bu100)
431 {
432 split(i,key,":")
433 j_bu100_u[key[1]]++
434 }
435
436 for(i in lfr_newuser)
437 {
438 split(i,key,":")
439 lfr_user[key[1]]++
440 }
441
442 for(i in lj_fr_newuser)
443 {
444 split(i,key,":")
445 lj_fr_user[key[1]]++
446 }
447
448 for(i in all_u)
449 {
450 split(i,key," ")
451
452 #MOD guojiadong 2012-11-12
453
454 #pab=int(bu5_u[i])
455
456 #MOD guojiadong 2012-11-20
457 #pab=int(bu7_u[i])
458
459 #MOD guojiadong 2012-12-04
460 #pab=int(bu15_u[i])
461
462 #if(int(lfr_user[i])<int(bu7_u[i]))
463 # pab1=int(lfr_user[i])
464 #else
465
466 pab1=int(new_imei_u[i]*0.8/1.3)
467 if(int(new_imei_u[i])!=0){
468 if(bu7_u[i]*1.3/new_imei_u[i]>0.8){
469 pab=pab1
470 }
471 else{
472 pab=int(bu7_u[i])
473 }
474 }
475 else{
476 pab=0
477 }
478
479 #MOD OVER
480 #MOD OVER
481 #MOD OVER
482
483 #lfr_user
484 #print "update '$stat_table' set imei="int(all_u[i])",new_imei="int(new_imei_u[i])",newuser="int(lfr_user[i])",j_newuser="int(lj_fr_user[i])",Bu3="int(bu3_u[i])",Bp3="int(bu3_p[i])",Bu4="int(bu4_u[i])",Bp4="int(bu4_p[i])",Bu5="int(bu5_u[i])",Bp5="int(bu5_p[i])",Bu7="int(bu7_u[i])",Bp7="int(bu7_p[i])",Bu10="int(bu10_u[i])",Bp10="int(bu10_p[i])",Bu15="int(bu15_u[i])",Bp15="int(bu15_p[i])",Bu20="int(bu20_u[i])",Bu30="int(bu30_u[i])",Bp30="int(bu30_p[i])",Bu50="int(bu50_u[i])",Bp50="int(bu50_p[i])",Bu70="int(bu70_u[i])",Bp70="int(bu70_p[i])",Bu100="int(bu100_u[i])",Bp100="int(bu100_p[i])",pab="pab",j_imei="int(j_all_u[i])",j_newimei="int(j_new_imei_u[i])",j_Bu3="int(j_bu3_u[i])",j_Bu4="int(j_bu4_u[i])",j_Bu5="int(j_bu5_u[i])",j_Bu7="int(j_bu7_u[i])",j_Bu10="int(j_bu10_u[i])",j_Bu15="int(j_bu15_u[i])",j_Bu30="int(j_bu30_u[i])",j_Bu50="int(j_bu50_u[i])",j_Bu70="int(j_bu70_u[i])",j_Bu100="int(j_bu100_u[i])" where stat_date=\"'$instime'\" and ch_name=\""key[1]"\";"
485
486 #print i,int(lfr_user[i])
487 #lishujun edit 2013-4-1
488 #if(user_id_name[userid[i]]" "i in app_open_channel)
489 if(user_id_name[userid[i]]=="UCshuqi" || user_id_name[userid[i]]" "i in app_open_channel)
490 {
491 #print '$stat_date',i,lkyd_chname[i],userid[i]
492 if(i=="kyd_32886"){
493 if(i in lkyd_chname)
494 {
495 if('$stat_date'<=lkyd_chname[i])
496 {
497 print "delete from '$ppoem_table' where stat_date=\"'$instime'\" and ch_name=\""i"\" and userid="int(userid[i])" and ch_type=21 and sv_type=3;"
498 print"insert into '$ppoem_table' set stat_date=\"'$instime'\",ch_name=\""i"\",userid="int(userid[i])",payable="int(pab)",ch_type=21,sv_type=3;"
499 }
500 }
501 else
502 {
503 print "delete from '$ppoem_table' where stat_date=\"'$instime'\" and ch_name=\""i"\" and userid="int(userid[i])" and ch_type=21 and sv_type=3;"
504 print"insert into '$ppoem_table' set stat_date=\"'$instime'\",ch_name=\""i"\",userid="int(userid[i])",payable="int(pab)",ch_type=21,sv_type=3;"
505 }
506 }
507 }
508 }
509 }'
510 # |statdb -f
511
512 echo "$0 end at `date +"%F %k:%M:%S"`" 1>&2
浙公网安备 33010602011771号