1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
| T_Scri=/etc/cron-scri/script_stat_total/temp-scri
T_cpc_total=$T_Scri/temp_cpc_total
T_lprlog=$T_Scri/temp_lprog
T_sql=$T_Scri/temp_sql
T2_res=$T_Scri/temp2_res
T3_res=$T_Scri/temp3_res
rm -fr $T_cpc_total
rm -fr $T_lprog
while read ligne
do lip=""
if [ "$lprog" = "" ]
then lprog="$ligne"
echo "$ligne" >> $T_lprog
else rm -fr $T_sql
echo "
SELECT distinct(client_IP)
FROM Tbl_Affiliate_CPC_Transactions
WHERE program_id IN (\"$lprog\")" >> $T_sql
lprog="$lprog,$ligne"
echo ",$ligne" >> $T_lprog
rm -fr $T2_res
mysql --user=XXX --password=XXX --database=XXX_prod < $T_sql >> $T2_res
if test -r $T2_res
then getIP=""
rm -fr $T3_res
set -x
while read ligneIP
do if [ "$getIP" = "" ]
then read ligneIP
getIP="'$ligneIP'"
else getIP=",'$ligneIP\'"
fi
echo "$getIP" >> $T3_res
done < $T2_res
set +x
if test -r $T3_res
then lip="and client_IP NOT IN (\"$(cat $T3_res)\")"
fi
fi
fi
echo "
INSERT INTO w_CPC_Tech (cpc_id, program_id, login_id, website_id, banner_id,
client_IP, action_amount, total_payable, commission, created_by, created_on,
http_referer, status)
SELECT a.cpc_id, a.program_id, a.login_id, a.website_id, a.banner_id, a.client_IP,
a.action_amount, a.total_payable, a.commission, a.created_by, a.created_on,
a.http_referer, a.status
FROM Tbl_Affiliate_CPC_Transactions a
WHERE a.program_id=
$ligne
$lip
GROUP BY client_ip;
" >> $T_cpc_total
done < /var/www/vhosts/XXX.com/httpdocs/XXX_global/ip_netting
echo "
INSERT INTO w_CPC_Tech (cpc_id, program_id, login_id, website_id, banner_id,
client_IP, action_amount, total_payable, commission, created_by, created_on,
http_referer, status)
SELECT a.cpc_id, a.program_id, a.login_id, a.website_id, a.banner_id, a.client_IP,
a.action_amount, a.total_payable, a.commission, a.created_by, a.created_on,
a.http_referer, a.status
FROM Tbl_Affiliate_CPC_Transactions a
WHERE a.program_id NOT IN ( $(cat $T_lprog) )
GROUP BY concat(a.program_id,a.client_IP);
INSERT INTO CPC_Histo (program_id, login_id, banner_id, website_id, volume, affiliate_amount, commission, advert_amount, created_by, created_on, status )
SELECT a.program_id, a.login_id, a.banner_id, a.website_id, count( a.cpc_id), sum(action_amount), sum(a.commission), sum(a.total_payable), a.created_by, now(), 'np'
FROM w_CPC_Tech a GROUP BY concat(a.website_id,a.banner_id);
delete from w_CPC_Tech;
delete from Tbl_Affiliate_CPC_Transactions;" >> $T_cpc_total
mysql --user=XXX --password=XXX --database=XXX_prod < $T_cpc_total |
