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]=$2118 }119 120 }121 {122 if($2~/kyd_/)123 {124 fr=$2125 imei=$1126 }127 else if($2=="-"&&$3~/9000%26fr/)128 {129 fr=gensub(/.*%3D(.+)/,"\\1","g",$3)130 imei=$1131 }132 else133 next134 135 #print fr":"imei136 137 all[fr":"imei]+=$5138 139 if($7=="true")140 {141 j_all[fr":"imei]+=$5142 }143 144 #if($1 in lbu5_imei)145 #new_bu5[fr":"imei]+=$5146 147 if($6=="0")148 {149 new_imei[fr":"imei]+=$5150 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]+=$5166 167 if($7=="true")168 {169 j_bu3[fr":"imei]170 }171 }172 if($5>4)173 {174 bu4[fr":"imei]+=$5175 176 if($7=="true")177 {178 j_bu4[fr":"imei]179 }180 }181 if($5>5)182 {183 bu5[fr":"imei]+=$5184 185 if($7=="true")186 {187 j_bu5[fr":"imei]188 }189 }190 if($5>7)191 {192 bu7[fr":"imei]+=$5193 194 if($7=="true")195 {196 j_bu7[fr":"imei]197 }198 }199 if($5>10)200 {201 bu10[fr":"imei]+=$5202 203 if($7=="true")204 {205 j_bu10[fr":"imei]206 }207 }208 if($5>15)209 {210 bu15[fr":"imei]+=$5211 212 if($7=="true")213 {214 j_bu15[fr":"imei]215 }216 }217 if($5>20)218 {219 bu20[fr":"imei]+=$5220 221 if($7=="true")222 {223 j_bu20[fr":"imei]224 }225 }226 if($5>30)227 {228 bu30[fr":"imei]+=$5229 230 if($7=="true")231 {232 j_bu30[fr":"imei]233 }234 }235 if($5>50)236 {237 bu50[fr":"imei]+=$5238 239 if($7=="true")240 {241 j_bu50[fr":"imei]242 }243 }244 if($5>70)245 {246 bu70[fr":"imei]+=$5247 248 if($7=="true")249 {250 j_bu70[fr":"imei]251 }252 }253 if($5>100)254 {255 bu100[fr":"imei]+=$5256 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-12453 454 #pab=int(bu5_u[i])455 456 #MOD guojiadong 2012-11-20457 #pab=int(bu7_u[i])458 459 #MOD guojiadong 2012-12-04460 #pab=int(bu15_u[i])461 462 #if(int(lfr_user[i]) 0.8){469 pab=pab1470 }471 else{472 pab=int(bu7_u[i])473 }474 }475 else{476 pab=0477 }478 479 #MOD OVER480 #MOD OVER481 #MOD OVER482 483 #lfr_user484 #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-1488 #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 else502 {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 -f511 512 echo "$0 end at `date +"%F %k:%M:%S"`" 1>&2