File: /home/cafsindia/ntc_cafsinfotech_in_bk/bend/scheduler_model.php
<?php
include('./dbconnect.php');
class scheduler_model extends dbconnect{
protected $post_url = "https://www.quandl.com/api/v3/datasets/NSE/@security_id@?api_key=oDXJMYNbb81cTwmTwzW_";
public function __construct() {
$this->open_db();
}
public function curl($url){
$ch = curl_init(); // Initiate curl
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // Disable SSL verification
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); // Will return the response, if false it print the response
curl_setopt($ch, CURLOPT_URL,$url); // Set the url
$result=curl_exec($ch);// Execute
curl_close($ch);// Closing
return json_decode($result,true);
}
public function real_escape_string($value){
$value = mysql_real_escape_string($value);
if(empty($value)){
$value = 0;
}
return $value;
}
//EQUITY LIST
public function get_equity_list(){
$list_info = $this->runQuery("CALL sp_select_equity");
return $this->result($list_info);
}
//EQUITY DAILY
public function get_equity_daily(){
$equity_list = $this->get_equity_list();
foreach($equity_list as $equity){
$security_code = $equity->security_code;
$security_id = $equity->security_id;
$security_name = $equity->security_name;
$current_day = new DateTime("2019-01-01");
$current_day = $current_day->format("Y-m-d");
$post_url = str_replace("@security_id@",$security_id,$this->post_url)."&start_date=2018-12-31&end_date=$current_day";
echo "post_url :: $post_url<br/>";
$equity_post_rslt = $this->curl($post_url);
$equity_daily_qry = "INSERT INTO cw_equity_data_daily (security_code,security_id,security_name,open,high,low,close,adjusted,volume,dividend,split,date,created_date) values ";
$equity_daily_array = array();
foreach($equity_post_rslt as $key => $equity_array){
$equity_data = $equity_array['data'];
if(count($equity_array['data']) > 0){
foreach($equity_data as $key => $equity_rslt){
$security_code = $security_code;
$security_id = $security_id;
$security_name = $security_name;
$open = $this->real_escape_string($equity_rslt[1]);
$high = $this->real_escape_string($equity_rslt[2]);
$low = $this->real_escape_string($equity_rslt[3]);
$close = $this->real_escape_string($equity_rslt[5]);
$adjusted = $this->real_escape_string($equity_rslt[4]);
$volume = $this->real_escape_string($equity_rslt[6]);
$dividend = $this->real_escape_string($equity_rslt[7]);
$split = $this->real_escape_string($equity_rslt[7]);
$date = $this->real_escape_string($equity_rslt[0]);
$date_time = new DateTime();
$date_time = $date_time->format("Y-m-d H:i:s");
$equity_daily_array[] = '("'.$security_code.'","'.$security_id.'","'.$security_name.'","'.$open.'","'.$high.'","'.$low.'","'.$close.'","'.$adjusted.'","'.$volume.'","'.$dividend.'","'.$split.'","'.$date.'","'.$date_time.'")';
}
}
}
if(count($equity_daily_array)>0){
$equity_daily_qry .= implode(',', $equity_daily_array);
echo "$equity_daily_qry<br/>";
$this->runQuery("SET @insertquery = $equity_daily_qry");
$list_info = $this->runQuery("CALL sp_save_equity_data_daily(@insertquery)");
if(!$list_info){
echo "All Not Inserted";
}else{
echo date("Y-m-d h:i:sa")."daily END";
}
}
}
}
//MUTUAL FUND CODE LIST
public function get_mf_code_list(){
$navs_code_query = 'select * from cw_mutual_fund_master';
$navs_code_info = $this->runQuery("$navs_code_query");
return $this->result($navs_code_info);
}
//USED TO GET ONE YEAR DATA
public function get_all_nav_data(){
//Form 2006 - 01-Apr-2006 :: 30-Jun-2006
$pro_start_date = new DateTime("01-01-2019");
for($i=0;$i<=3;$i++){
if($i > 0){
$pro_start_date = $pro_start_date->modify("+1 day");
}
$start_date = $pro_start_date->format("d-M-Y");
$end_date = $pro_start_date->modify("+3 months");
$end_date = $end_date->modify("-1 day");
$end_date = $end_date->format("t-M-Y");
echo "$start_date :: $end_date<br/>";
//$this->get_mf_nav_data($start_date,$end_date);
}
}
//MUTUAL FUND NAV DATA
public function get_mf_nav_data($from_date,$to_date){
$nav_url = "http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?frmdt=$from_date&todt=$to_date";
$nav_data = file_get_contents($nav_url);
unset($mf_nav_array);
unset($mf_net_price);
$mf_nav_array = array();
$mf_net_price = array();
$count = 0;
$can_process = true;
$message = "";
$master_message = "";
$file_path = "";
if(strpos($nav_data, 'No data found on the basis of selected parameters for this report') !== false) {
$can_process = false;
$message = "No date available";
}else{
$file_path = $this->save_nav_file($nav_data,$from_date,$to_date);
$message = "Processing data";
$nav_data = explode("\n", $nav_data);
foreach ($nav_data as $nav_rslt){
$nav_info = explode(";", $nav_rslt);
if(count($nav_info) === 8){
$nav_info = explode(";", $nav_rslt);
$scheme_code = "0";
if($nav_info[0]){ $scheme_code = $this->real_escape_string($nav_info[0]); }
if($scheme_code !== "Scheme Code"){
$scheme_name = "0";
if($nav_info[1]){ $scheme_name = $this->real_escape_string($nav_info[1]); }
$payout_growth = "0";
if($nav_info[2]){ $payout_growth = $this->real_escape_string($nav_info[2]); }
$reinvestment = "0";
if($nav_info[3]){ $reinvestment = $this->real_escape_string($nav_info[3]); }
$net_asset = "0";
if($nav_info[4]){ $net_asset = $this->real_escape_string($nav_info[4]); }
$repurchase = "0";
if($nav_info[5]){ $repurchase = $this->real_escape_string($nav_info[5]); }
$sale_price = "0";
if($nav_info[6]){ $sale_price = $this->real_escape_string($nav_info[6]); }
$nav_date = "";
if($nav_info[7]){
$nav_date = date_create($nav_info[7]);
$nav_date = date_format($nav_date,"Y-m-d");
}
$date_time = new DateTime();
$date_time = $date_time->format("Y-m-d H:i:s");
$count++;
$mf_nav_array[] = '("'.$scheme_code.'","'.$scheme_name.'","'.$payout_growth.'","'.$reinvestment.'","'.$net_asset.'","'.$repurchase.'","'.$sale_price.'","'.$nav_date.'","'.$date_time.'")';
$mf_net_price[] = '("'.$scheme_code.'","'.$scheme_name.'","'.$payout_growth.'","'.$net_asset.'","'.$nav_date.'","'.$date_time.'")';
}
}
}
}
if($can_process){
if(count($mf_nav_array)>0){
$array_count = count($mf_nav_array);
$mf_nav_query = "INSERT INTO cw_mutual_fund_nav (scheme_code,scheme_name,payout_growth,reinvestment,net_asset,repurchase,sale_price,nav_date,process_time) values ";
$mf_nav_query .= implode(',', $mf_nav_array);
$mf_nav_query_info = $this->runQuery("$mf_nav_query");
if(!$mf_nav_query_info){
$message = "All Not Inserted<br/>";
}else{
$message = "All Records inserted - $array_count";
}
}
if(count($mf_net_price)> 0){
$this->runQuery("CREATE TEMPORARY TABLE IF NOT EXISTS cw_mutual_fund_master_temp (code varchar(25),scheme_nav_name varchar(225),payout_growth longtext,nav_price decimal(15,4),nav_as_on date,process_time datetime)");
$this->runQuery("DELETE FROM cw_mutual_fund_master_temp");
$mf_nav_temp_query = "INSERT INTO cw_mutual_fund_master_temp (code,scheme_nav_name,payout_growth,nav_price,nav_as_on,process_time) values ";
$mf_nav_temp_query .= implode(',', $mf_net_price);
$this->runQuery("$mf_nav_temp_query");
$this->runQuery("UPDATE cw_mutual_fund_master tbl_master INNER JOIN cw_mutual_fund_master_temp tbl_temp ON tbl_master.code = tbl_temp.code SET tbl_master.nav_price = tbl_temp.nav_price,tbl_master.scheme_nav_name = tbl_temp.scheme_nav_name,tbl_master.payout_growth = tbl_temp.payout_growth,tbl_master.nav_as_on = tbl_temp.nav_as_on,tbl_master.process_time = now()");
$info = $this->runQuery("SELECT GROUP_CONCAT(tbl_temp.code) as ids FROM cw_mutual_fund_master_temp tbl_temp LEFT JOIN cw_mutual_fund_master tbl_master ON tbl_temp.code = tbl_master.code WHERE tbl_master.code IS NULL");
$rslt = $this->result($info);
$insert_ids = $rslt[0]->ids;
if($insert_ids){
$insert_ids = '"'.str_replace(",",'","',$insert_ids).'"';
$this->runQuery("INSERT INTO cw_mutual_fund_master(code,scheme_nav_name,payout_growth,nav_price,nav_as_on,process_time) SELECT code,scheme_nav_name,payout_growth,nav_price,nav_as_on,process_time FROM cw_mutual_fund_master_temp WHERE code in ($insert_ids)");
}
$this->runQuery("DELETE FROM cw_mutual_fund_master_temp");
$master_message = "Updated to master";
}
}
$date = new DateTime();
$today_date = $date->format("Y-m-d");
$date_time = $date->format("Y-m-d H:i:s");
$process_sts = "Fail";
if($can_process){
$process_sts = "Success";
}
$this->runQuery("INSERT INTO cw_mutual_fund_nav_log(process_date,process_sts,message,master_message,nav_url,file_path,date_time) values('$today_date','$process_sts','$message','$master_message','$nav_url','$file_path','$date_time')");
return array("can_process"=>$can_process,'message'=>$message,'master_message'=>$master_message,'nav_url'=>$nav_url,'file_path'=>$file_path,'date_time'=>$date_time);
}
public function save_nav_file($nav_data,$from_date,$to_date){
$file_path = "./download_nav";
if(!file_exists($file_path)) {
mkdir($file_path, 0777, true);
}
if($from_date === $to_date){
$file_path = $file_path."/nse_nav_".str_replace("-","",$from_date).".txt";
}else{
$file_path = $file_path."/nse_nav_".str_replace("-","",$from_date)."_".str_replace("-","",$to_date).".txt";
}
echo "file_path : $file_path<br/>";
file_put_contents($file_path, $nav_data);
return $file_path;
}
}
?>