query($statement);
if (!$result) {
// echo $statement.”\n”;
trigger_error(‘Wrong SQL: ‘ . $statement . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
else
{
$updated +=$conn->affected_rows;
}
$conn->commit();
}
function updContest($conn, $orderby, $url, $opendate,$orderby)
{
$today = date(‘Y-m-d H:i’);
$hour = date(‘H:i’);
$weekday =date(‘w’);
// echo “update contest\n”;
if ($today > ‘$opendate’ AND ($hour <'16:50' and $hour>’06:45′ and $weekday>0 and $weekday<7) )
{
$statement ="select member,tick, tradeType, costPrice, curPrice,pctReturn, tick2, tradeType2, costPrice2, curPrice2,pctReturn2,tick3, tradeType3, costPrice3, curPrice3,pctReturn3 from lifan.stkContest where date='$opendate' order by ".$orderby;
$selection_sstk=$conn->query($statement);
if (!$selection_sstk) {
// echo $statement.”\n”;
trigger_error(‘Wrong SQL: ‘ . $statement . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
// echo $selection_sstk->num_rows;
if ($selection_sstk->num_rows <1)
{
$message = "$opendate data not found ";
echo "“;
exit;
}
else
{
while ($row = $selection_sstk->fetch_assoc())
{
$member = $row[‘member’];
$tick = $row[‘tick’];
$costPrice = $row[‘costPrice’];
$tradeType = $row[‘tradeType’];
$tick2 = $row[‘tick2’];
$costPrice2 = $row[‘costPrice2’];
$tradeType2 = $row[‘tradeType2’];
$tick3 = $row[‘tick3’];
$costPrice3 = $row[‘costPrice3’];
$tradeType3 = $row[‘tradeType3′];
if ($costPrice==0)
{
if (NULL != $costPrice )
{
$statement =”update lifan.stkContest inner join tastk using (tick, date) set costprice=open where date=’$opendate'”;
updateDB($conn, $statement);
}
}
if ($costPrice2==0)
{
if (NULL != $costPrice )
{
$statement =”update lifan.stkContest inner join tastk using (date) set costprice2=open where date=’$opendate’ and tastk.tick=tick2″;
updateDB($conn, $statement);
}
}
if ($costPrice3==0)
{
if (NULL != $costPrice )
{
$statement =”update lifan.stkContest inner join tastk using (date) set costprice3=open where date=’$opendate’ and tastk.tick=tick3″;
updateDB($conn, $statement);
}
}
else
{
$statement =”update lifan.stkContest inner join setf using (tick) set curprice=price, pctReturn1=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick=’$tick’ and date=’$opendate'”;
updateDB($conn, $statement);
$statement =”update lifan.stkContest inner join sstk using (tick) set curprice=price, pctReturn1=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick=’$tick’ and date=’$opendate'”;
updateDB($conn, $statement);
$statement =”update lifan.stkContest inner join setf set curprice2=price, pctReturn2=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick2=’$tick2′ and date=’$opendate’ and setf.tick=tick2″;
updateDB($conn, $statement);
$statement =”update lifan.stkContest inner join sstk set curprice2=price, pctReturn2=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick2=’$tick2′ and date=’$opendate’ and sstk.tick=tick2″;
updateDB($conn, $statement);
$statement =”update lifan.stkContest inner join setf set curprice3=price, pctReturn3=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick3=’$tick3′ and date=’$opendate’ and setf.tick=tick3″;
updateDB($conn, $statement);
$statement =”update lifan.stkContest inner join sstk set curprice3=price, pctReturn3=(if (tradeType=’L’, price*100/costPrice -100, 100-price*100/costPrice)) where tick3=’$tick3′ and date=’$opendate’ and sstk.tick=tick3″;
updateDB($conn, $statement);
$statement =”update lifan.stkContest set pctReturn=(pctReturn1+pctReturn2+pctReturn3) where date=’$opendate’ “;
updateDB($conn, $statement);
}
}
$conn->commit();
$selection_sstk->free();
}
}
}
function updScores($conn)
{
$today = date(‘Y-m-d’);
$statement=”UPDATE lifan.stkContest m INNER JOIN
( SELECT a.member, date, sum(IF(a.pctReturn>=b.pctReturn, 1, 0)) as mScore, count(a.member) as totJoined
FROM lifan.stkContest as a inner join lifan.stkContest as b using (date) where date<'$today' group by date,a.member order by date
) s ON s.member = m.member and s.date=m.date
SET m.stdScore = mScore*100/totJoined,m.rank =( totJoined-mScore)+1" ;
updateDB($conn, $statement);
$statement=" UPDATE lifan.memberlist m INNER JOIN
(
SELECT member, SUM(pctReturn) as pctReturn,avg(stdScore) as memberScore,count(member) as totJoined
FROM lifan.stkContest where date<'$today'
GROUP BY member
) s ON s.member = m.member
SET m.totReturnScore = s.pctReturn, m.memberScore=s.memberScore, m.times=totJoined";
updateDB($conn, $statement);
}
function showContest($conn, $orderby, $url, $opendate)
{
$today = date('Y-m-d H:i');
if ($today > $opendate )
{
$table =”stock Contest result week: “.$opendate;
$statement =”select member,tick as ticker1, tradeType as type1, costPrice, curPrice, pctReturn1,tick2 as ticker2, tradeType2 as type2 , costPrice2, curPrice2, pctReturn2,tick3 as ticker3, tradeType3 as type3, costPrice3, curPrice3, pctReturn3,pctReturn as totalReturn from lifan.stkContest inner join lifan.memberlist using(member) where date=’$opendate’ order by “.$orderby;
$ret = queryDB($url,$statement, $table);
if ($ret ==0 )
{
echo “No reult yet \n”;
}
}
else //Not started yet
{
$statement =”select member,tick as ticker1, tradeType as trade1, curPrice as current_price, tick2 as ticker2,tradeType2 as trade2, curPrice2 as cur_price2,tick3 as ticker3, tradeType3 as trade3, curPrice3 as cur_price3 from lifan.stkContest where date=’$opendate’ order by “.$orderby;
$table =”stock Contest week: “.$opendate;
$ret = queryDB($url,$statement, $table);
if ($ret ==0 )
{
echo “Not start yet \n”;
}
}
$statement =”select member, totReturnScore,memberScore,times as ContestJoined from lifan.memberlist where totReturnScore>0 order by totReturnScore desc limit 30 “;
$table =”Top 30 high score members “;
$ret = queryDB($url,$statement, $table);
if ($ret ==0 )
{
echo “Not start yet \n”;
}
else
echo “Note: TotReturnScore means Total percent return a member can win by select and hold his stocks in one week; memberScore means on average, how a member compete other members in same contests in percentile”;
}
function enterContest($conn, $opendate,$today,&$orderby,$url)
{
$tick = $orderby=$wechatID=NULL;
$tick=’SPY’;
$tick2 = ‘QQQ’;
$tick3=’IWM’;
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
if (isset($_POST[‘tick’]))
$tick = test_input($_POST[‘tick’]);
if (isset($_POST[‘tick2’]))
$tick2 = test_input($_POST[‘tick2’]);
if (isset($_POST[‘tick3’]))
$tick3 = test_input($_POST[‘tick3’]);
if (isset($_POST[‘wechatID’]))
$wechatID = test_input($_POST[‘wechatID’]);
if (isset($_POST[‘tradeType’]))
$tradeType = test_input($_POST[‘tradeType’]);
if (isset($_POST[‘tradeType2’]))
$tradeType2 = test_input($_POST[‘tradeType2’]);
if (isset($_POST[‘tradeType3’]))
$tradeType3 = test_input($_POST[‘tradeType3’]);
if (isset($_POST[‘orderby’]))
{
$orderby= test_input($_POST[‘orderby’]);
if ($orderby)
$orderby = $orderby;
}
}
else
{
if (isset($_GET[‘tick’]))
$tick = test_input($_GET[‘tick’]);
if (isset($_GET[‘tick2’]))
$tick2 = test_input($_GET[‘tick2’]);
if (isset($_GET[‘tick3’]))
$tick3 = test_input($_GET[‘tick3’]);
if (isset($_GET[‘wechatID’]))
$wechatID = test_input($_GET[‘wechatID’]);
if (isset($_GET[‘tradeType’]))
$tradeType = test_input($_GET[‘tradeType’]);
if (isset($_GET[‘tradeType2’]))
$tradeType2 = test_input($_GET[‘tradeType2’]);
if (isset($_GET[‘tradeType3’]))
$tradeType3 = test_input($_GET[‘tradeType3’]);
if (isset($_GET[‘orderby’]))
{
$orderby= test_input($_GET[‘orderby’]);
if ($orderby)
$orderby = $orderby;
}
}
//$tick =”YELP”;
if ($orderby)
{
if ($orderby == ‘date’ OR $orderby == ‘pctRetrun’)
$orderby = $orderby.” desc”;
else
{
$myrand = rand ();
if ($myrand % 2 ==1)
$orderby = $orderby.” desc”;
}
}
else
$orderby = ” pctReturn desc, lifan.stkContest.id asc “;
// $opendate = getNextTradingdate($today);
$stopEnter = date(“Y-m-d”,strtotime(“-1 day”, strtotime($opendate)));
$stopEnter = $stopEnter.” 23:59″;
//echo ” $opendatetime $stopEnter “;
// echo “
$tick AND $wechatID $today < $stopEnter and open: $opendate
“;
echo ‘‘;
if ($tick AND $tick2 AND $tick3 AND $wechatID AND ($today <$stopEnter))
{
//Strip whitespace. trim function -- PHP4, PHP5, PHP7;
$tick = trim($tick);
$tick2 = trim($tick2);
$tick3 = trim($tick3);
//Convert into uppercase;
$tick = strtoupper($tick);
$tick2 = strtoupper($tick2);
$tick3 = strtoupper($tick3);
$tradeType = trim($tradeType);
$tradeType2 = trim($tradeType2);
$tradeType3 = trim($tradeType3);
//Convert into uppercase;
$tradeType = strtoupper($tradeType);
$tradeType2 = strtoupper($tradeType2);
$tradeType3 = strtoupper($tradeType3);
// Call function 1. to catch the issue of redundant ticks ;
// 2. to sort the 3 ticks and re-assign them per order as well as associated trade types;
//sortTicks($tick, $tick2, $tick3, $tradeType, $tradeType2, $tradeType3);
$pickArr = array("$tick" => “$tradeType”, “$tick2” => “$tradeType2”, “$tick3” => “$tradeType3”);
ksort($pickArr);
/*foreach ($pickArr as $key => $val) {
echo “$key = $val\n”;
}*/
reset($pickArr);
$tick=key($pickArr);
$tradeType=current($pickArr);
next($pickArr);
$tick2=key($pickArr);
$tradeType2=current($pickArr);
next($pickArr);
$tick3=key($pickArr);
$tradeType3=current($pickArr);
// echo “$tick, $tradeType \n $tick2, $tradeType2 \n $tick3, $tradeType3 \n”;
$num = 0;
$skipped =0;
$skipped = $updated = $deleted = 0;
// $statement= “SELECT tick,price,indexMember FROM sstk WHERE tick=’$tick’ AND (indexMember LIKE ‘%DIA%’ OR indexMember LIKE ‘%QQQ%’ OR indexMember LIKE ‘%SPY%’ )”;
$statement= “SELECT tick, price,indexMember FROM sstk WHERE tick=’$tick’ AND (marketcapm >1000)”;
$statement2= “SELECT tick, price,indexMember FROM sstk WHERE tick= ‘$tick2′ AND (marketcapm >1000)”;
$statement3= “SELECT tick, price,indexMember FROM sstk WHERE tick=’$tick3’ AND (marketcapm >1000)”;
// Retrieve distinct ticks user entered that exist in the database table sstk with enough caps;
$selection_sstk=$conn->query($statement);
if (!$selection_sstk) {
echo $statement.”\n”;
trigger_error(‘Wrong SQL #1: ‘ . $statement . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
$selection_sstk2=$conn->query($statement2);
if (!$selection_sstk2) {
echo $statement2.”\n”;
trigger_error(‘Wrong SQL #2: ‘ . $statement2 . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
$selection_sstk3=$conn->query($statement3);
if (!$selection_sstk3) {
echo $statement3.”\n”;
trigger_error(‘Wrong SQL #3: ‘ . $statement3 . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
else
{
// echo $selection_sstk->num_rows;
if ($selection_sstk->num_rows <1 && $tick!='QQQ' AND $tick!='SPY')
{
// $message = "$tick is not a qualified pick, please choose a stock from DIA, QQQ or SPY component stocks";
$message = "$tick is not a qualified pick, please choose a stock with marketCap >1000M “;
// echo ““;
echo ““;
exit;
}
else
if ($selection_sstk2->num_rows <1 && $tick2!='QQQ' AND $tick2!='SPY')
{
// $message = "$tick2 is not a qualified pick, please choose a stock from DIA, QQQ or SPY component stocks";
$message = "$tick2 is not a qualified pick, please choose a stock with marketCap >1000M “;
echo ““;
exit;
}
else
if ($selection_sstk3->num_rows <1 && $tick3!='QQQ' AND $tick3!='SPY')
{
// $message = "$tick3 is not a qualified pick, please choose a stock from DIA, QQQ or SPY component stocks";
$message = "$tick3 is not a qualified pick, please choose a stock with marketCap >1000M “;
echo ““;
exit;
}
else
{
// Retrieve the price info for each tick user entered from the existing database table sstk;
$row = $selection_sstk->fetch_assoc();
$price = $row[‘price’];
$row2 = $selection_sstk2->fetch_assoc();
$price2 = $row2[‘price’];
$row3 = $selection_sstk3->fetch_assoc();
$price3 = $row3[‘price’];
$statement= “SELECT member, tick,tick2, tick3, tradeType,tradeType2, tradeType3, date FROM lifan.stkContest WHERE tick=’$tick’ AND tick2=’$tick2′ AND tick3 = ‘$tick3′ AND date=’$opendate’ AND tradeType=’$tradeType’ AND tradeType2=’$tradeType2′ AND tradeType3=’$tradeType3′”;
// $statement= “SELECT member, tick,tick2, tick3, tradeType,tradeType2, tradeType3, date FROM lifan.stkContest WHERE tick=’$tick’ AND tick2=’$tick2′ AND tick3 = ‘$tick3′ AND date=’$opendate’ AND tradeType=’$tradeType’ AND tradeType2=’$tradeType2′ AND tradeType3=’$tradeType3′ AND member != ‘$wechatID'”;
//echo “$statement \n”;
$selection_sstk=$conn->query($statement);
if (!$selection_sstk) {
echo $statement.”\n”;
trigger_error(‘Wrong SQL: ‘ . $statement . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
else
{
// echo $selection_sstk->num_rows;
if ($selection_sstk->num_rows >0)
{
$row = $selection_sstk->fetch_assoc();
$member = $row[‘member’];
$message = “$tick $tick2 $tick3 was picked by $member “;
echo ““;
// header(‘Location: http://192.168.0.201/sstk_contest.php’);
exit;
// echo “
“;
}
}
$statement= ” Replace INTO lifan.stkContest (`tick`, `tick2`, `tick3`, `date`, `member`,`curPrice`,`curPrice2`, `curPrice3`, `tradeType`, `tradeType2`, `tradeType3`) VALUES (‘$tick’, ‘$tick2’, ‘$tick3’, ‘$opendate’, ‘$wechatID’, ‘$price’, ‘$price2’, ‘$price3’, ‘$tradeType’, ‘$tradeType2’, ‘$tradeType3’)”;
$result =$conn->query($statement);
if (!$result) {
trigger_error(‘Wrong SQL: ‘ . $result . ‘ Error: ‘ . $conn->error, E_USER_ERROR);
}
if ($conn->affected_rows)
{
$num +=$conn->affected_rows ;
$conn->commit();
echo “Your picks [$tick $tick2 $tick3] entered
“;
}
else
echo “$statement”;
$fromIP= get_the_user_ip();
$statement = “select member from lifan.memberlist where member=’$wechatID'”;
$table = ‘NoDisplay’;
$ret = queryDB($url,$statement, $table);
if ($ret ==0 )
{
echo “Welcome $wechatID joined\n”;
$statement =”insert into lifan.memberlist ( `member`, `times`, `fromIP`) values (‘$wechatID’, ‘1’, ‘$fromIP’) “;
updateDB($conn, $statement);
}
else
{
$statement =”update lifan.memberlist set fromIP= ‘$fromIP’ “;
updateDB($conn, $statement);
}
}
}
}
}
/*
UPDATE memberlist m INNER JOIN
(
SELECT member, SUM(pctReturn) as pctReturn
FROM stkContest
GROUP BY member
) s ON s.member = m.member
SET m.totReturnScore = s.pctReturn*100;
*/
date_default_timezone_set(‘America/Los_Angeles’);
$url = basename(__FILE__);
// $url = $url.”?tick=$tick”;
$opendate = ‘2017-08-14’;
$today = date(“Y-m-d H:i”);
$opendatetime =$opendate.” 13:19″;
$conn=open_db();
if ($today > $opendatetime)
updScores($conn );
else
enterContest($conn,$opendate,$today,$orderby,$url);
echo “
Cuerrnt result:
“;
// $statement =”select member,tick,date as start_date, tradeType, curPrice as current_price, marketcapm,industry from lifan.stkContest inner join sstk using (tick) order by “.$orderby;
// echo $statement;
updContest($conn, $orderby, $url, $opendate,$orderby);
showContest($conn, $orderby,$url,$opendate);
close_db($conn);
?>