where('requiredFor',$attr)->lists('datatypeName');*/
$data = DB::table('tbl_stats_datatype_header_list')
->where('requiredFor',$attr)
->distinct()
->orderBy('f_orderBy')
->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0 ){
$finalArray = array();
foreach ($data as $key => $value) {
$finalArray[$value['tableName']] = $value['datatypeName'];
}
return $finalArray;
}else{
return null;
}
}
public function checkDatatypeExist($attr){
$check = 'true';
if(DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr)->count() < 1 || DB::table('tbl_stats_left_toolbox_landing')->where('datatypeName',$attr)->count() < 1){
$check="false";
}
return $check;
}
public function getCommAndRegion($attr, $memid=0, $siteName = 'SM'){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr)->take(1)->get();
$colomnNameofRegion = '';
$stats = json_decode(json_encode($stats), true);
if( $siteName == 'CM' ){
$data = DB::table('tbl_stats_left_toolbox_landing')
->where('datatypeName',$attr . "_not_reqd");
} else {
$data = DB::table('tbl_stats_left_toolbox_landing')
->where('datatypeName',$attr);
if( $memid == 116707 || $memid == 120030 || $memid == 120954 ){
$data = $data -> orWhere('datatypeName',$attr . "_not_reqd");
}
}
$data = $data
->select('region','startDate','toDate','nextParentsList','commodity','col_name_commodity','subCommodity','col_name_subcommodity','col_name_region')
-> orderByRaw("
case subCommodity
when 'Fines/Lumps' then 1
when 'Pellet/Concentrate' then 2
when 'Non Coking Coal' then 3
when 'Coking Coal' then 4
when 'Met Coke' then 5
when 'Sponge Iron' then 6
when 'Pig Iron' then 7
when 'Ferrous Scrap' then 8
when 'Crude Steel' then 9
when 'Semi Finish' then 10
when 'Finish Long' then 11
when 'Finish Flat' then 12
when 'Manganese Ore' then 13
when 'Chrome Ore' then 14
when 'Ferro Silico Manganese' then 15
when 'Ferro Manganese' then 16
when 'Ferro Chrome' then 17
when 'Finish Long Stainless Steel' then 18
when 'Finish Flat Stainless Steel' then 19
when 'Graphite Electrode' then 20
when 'Ship Breaking' then 21
else 99
end
")
->get();
//DB::enableQueryLog(); dd(DB::getQueryLog());
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
//$user = new User($memid=1318, $appType='SM');
//echo 'test'.$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
//$value['startDate'] = $sectionFromDate;
//$value['endDate'] = $sectionTodate;
}else{
$sectionFromDate = $GLOBALS['currDate'];
$sectionTodate = $GLOBALS['currDate'];
}
$communityArray = array();
//$globalRegionArray = array();
foreach ($data as $key => $value) {
if($colomnNameofRegion==''){
$colomnNameofRegion= $value['col_name_region'];
}
$sFromDate = strtotime($value['startDate']) <= strtotime($sectionFromDate) ?$sectionFromDate:$value['startDate'];
$sTodate = strtotime($value['toDate']) <= strtotime($sectionTodate) ?$value['toDate']:$sectionTodate;
$communityArray[$value['commodity']]['columnNameOfCommodity'] = $value['col_name_commodity'];
//$communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']] = $value['subCommodity'];
$communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']]['columnNameOfCommodity'] = $value['col_name_subcommodity'];
$communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']]['region'][] = array(
'region' => $value['region'],
'startDate' => $sFromDate,
'endDate' => $sTodate,
'nextParentsList' => $value['nextParentsList'],
'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
);
$communityArray[$value['commodity']]['subCommodity']['All']['columnNameOfCommodity'] = 'commodity_new';
$a = $communityArray[$value['commodity']]['subCommodity']['All']['region'];
if( count($a) == 0 ){
$communityArray[$value['commodity']]['subCommodity']['All']['region'][] = array(
'region' => $value['region'],
'startDate' => $sFromDate,
'endDate' => $sTodate,
'nextParentsList' => $value['nextParentsList'],
'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
);
} else {
$flag = 0;
foreach($a as $key => $val){
if( $val['region'] == $value['region'] ){
$flag = 1;
if(strtotime($val['endDate']) < strtotime($value['toDate'])){
$communityArray[$value['commodity']]['subCommodity']['All']['region'][$key]['endDate'] = $sTodate;
}
if( $memid == 116707 && 'Steel'==$value['commodity']){
//echo '',$key, $value['region'],'---',$value['commodity'],'---' , $value['subCommodity'],$val['endDate'],'---' ,$value['toDate'], '--',$aa , '
';
}
if(strtotime($val['startDate']) > strtotime($sFromDate)){
$communityArray[$value['commodity']]['subCommodity']['All']['region'][$key]['startDate'] = $sFromDate;
}
}
}
if( $flag == 0 ){
$communityArray[$value['commodity']]['subCommodity']['All']['region'][] = array(
'region' => $value['region'],
'startDate' => $sFromDate,
'endDate' => $sTodate,
'nextParentsList' => $value['nextParentsList'],
'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
);
}
}
}
}
// echo "";
// print_r($globalRegionArray);
//$regionColname = $stats[0]['col_name_region_stats_toolbox'];
$regionColname = $colomnNameofRegion;
$periodFormat = $stats[0]['periodFormat'];
$dataArray = array(
'periodFormat' => $periodFormat,
'regionColname' => $regionColname,
'commodity' => $communityArray
);
return $dataArray;
}
public function getCommAndRegion_19_5_2020($attr, $memid=0){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr)->take(1)->get();
$colomnNameofRegion = '';
$stats = json_decode(json_encode($stats), true);
$data = DB::table('tbl_stats_left_toolbox_landing')
->where('datatypeName',$attr)
->select('region','startDate','nextParentsList','commodity','col_name_commodity','col_name_region')
-> orderByRaw("
case commodity
when 'Iron Ore' then 1
when 'Coal' then 2
when 'Scrap & Metallics' then 3
when 'Ferrous Scrap' then 4
when 'Crude Steel' then 5
when 'Semi Finish' then 6
when 'Finish Long' then 7
when 'Finish Flat' then 8
when 'Ferro Alloy' then 9
when 'Finish Flat Stainless Steel' then 10
when 'Finish Long Stainless Steel' then 11
when 'Graphite Electrode' then 12
when 'Ship Breaking' then 13
else 99
end
")
->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
//$user = new User($memid=1318, $appType='SM');
//echo 'test'.$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
$value['startDate'] = $sectionFromDate;
$value['endDate'] = $sectionTodate;
}else{
$sectionFromDate = $GLOBALS['currDate'];
$sectionTodate = $GLOBALS['currDate'];
}
$communityArray = array();
foreach ($data as $key => $value) {
if($colomnNameofRegion==''){
$colomnNameofRegion= $value['col_name_region'];
}
$communityArray[$value['commodity']]['columnNameOfCommodity'] = $value['col_name_commodity'];
$communityArray[$value['commodity']]['region'][] = array(
'region' => $value['region'],
'startDate' => $sectionFromDate,
'endDate' => $sectionTodate,
'nextParentsList' => $value['nextParentsList'],
'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
);
}
}
//$regionColname = $stats[0]['col_name_region_stats_toolbox'];
$regionColname = $colomnNameofRegion;
$periodFormat = $stats[0]['periodFormat'];
$dataArray = array(
'periodFormat' => $periodFormat,
'regionColname' => $regionColname,
'commodity' => $communityArray
);
return $dataArray;
}
public function getGraphData($attr){
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr['dataType'])->value('tableName');
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
if($attr['debugMode'] == true){
echo "
memid:" . $memid;
}
$user = new User($memid, $appType='SM');
if( isset($attr['requiredFor']) && $attr['requiredFor']=='comparison') {
$isAccessible = $user->isAccessible($resourceType='Web', $section='comparison');
}else{
$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
}
if($attr['debugMode'] == true){
echo "
isAccessible:" . $isAccessible;
}
$valDivideBy = 1;
if($isAccessible>=0 || $attr['source']=='home'){//visitor or upgrade
/* $periodFormat = 'yearly';
$attr['fromDate'] = '2017';
$attr['toDate'] = date('Y'); */
$periodFormat = 'monthly';
$attr['fromDate'] = 'jul 2019';
$attr['toDate'] = "dec 2019";//date('Y');
$valDivideBy = 1;
}else{
if($attr['source']=='tabular'){
if(strtolower($periodFormat)=='monthly'){
$sectionFromDate = date('M Y',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('M Y',strtotime(Session::get('sectionTodate')));
} elseif(strtolower($periodFormat)=='yearly'){
$sectionFromDate = date('Y',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('Y',strtotime(Session::get('sectionTodate')));
}else{
$sectionFromDate = date('m',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('m',strtotime(Session::get('sectionTodate')));
$yearQuarterFrom = ceil($sectionFromDate / 3);
$sectionFromDate = "q".$yearQuarterFrom. ' '.date('Y',strtotime(Session::get('sectionFromDate')));
$yearQuarterto = ceil($sectionTodate / 3);
$sectionTodate = "q".$yearQuarterto. ' '.date('Y',strtotime(Session::get('sectionTodate')));
}
$attr['fromDate'] = $sectionFromDate;
$attr['toDate'] = $sectionTodate;
}
}
$extraCol = "qtrYear";
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$time1 = strtotime($fromDate);
$time2 = strtotime($toDate);
$fromDate = date('Y-m-d', $time1);
$toDate = date('Y-m-d', $time2);
$grpBy = "formattedDate";
} elseif(strtolower($periodFormat)=='yearly'){
$fromDate =trim($attr['fromDate']).'-01'.'-01';
$toDate = trim($attr['toDate']).'-12'.'-31';
$grpBy = $yearColumnName;
} elseif(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth;
$time1 = strtotime($fromDate);
$time2 = strtotime($toDate);
$fromDate = date('Y-m-d', $time1);
$toDate = date('Y-m-31', $time2);
$grpBy = "qtrYear";
}
//echo $fromDate . " -- " . $toDate;
//query formation - START
$data = DB::table($getTableByDataType)
->select($extraCol, 'formattedDate', DB::raw('SUM(' . $quantityColumnName . ') as total_quantity'));
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
} elseif(empty($attr['columnNameOfCommodity'])) {
if(!empty($attr['commodity'])){
$commColName = DB::table("tbl_stats_selection_compare")
-> select("commodity", 'subCommodity', 'subSubCommodity')
-> where('commodity', '=', $attr['commodity'])
-> orWhere('subCommodity', '=', $attr['commodity'])
-> orWhere('subSubCommodity', '=', $attr['commodity'])
-> get();
if( count($commColName) > 0 ){
if( $commColName[0]['commodity'] == $attr['commodity'])
$commodityColumn = 'commodity';
elseif( $commColName[0]['subCommodity'] == $attr['commodity'])
$commodityColumn = 'subCommodity';
elseif( $commColName[0]['subSubCommodity'] == $attr['commodity'])
$commodityColumn = 'subSubCommodity';
}
$data = $data->where($commodityColumn,$commodity);
}
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data ->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region);
}
if(!empty($attr['filters']['app_type'])){
if( $attr['filters']['app_type'] == 'CM' )
$siteFilter = "coalmint";
else
$siteFilter = "steelmint";
$data = $data -> where('website_access', 'like', '%' . $siteFilter . '%');
}else{
$siteFilter = "steelmint";
$data = $data -> where('website_access', 'like', '%' . $siteFilter . '%');
}
$data = $data -> where('formattedDate', '>=', $fromDate)
-> where('formattedDate', '<=', $toDate)
-> groupBy($grpBy);
if($attr['debugMode'] == true){
//$data= $data->orderBy('formattedDate','ASC')->toSql();
echo "";
echo "" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region;
//print_r($data);
//dd();
}
$data= $data->orderBy('formattedDate','ASC')->get();
/* echo "" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region;
print_r($data);
dd(); */
/* print_r($data);
dd(); */
if($attr['debugMode'] == true){
echo "";
print_r($data);
//dd();
}
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
for($i = 0; $i < count($data); $i++){
$yValue = '';
if(strtolower($periodFormat)=='monthly'){
$time=strtotime($data[$i]["formattedDate"]);
$month=date("F",$time);
$year=date("Y",$time);
$yValue = $month . ' ' . $year;
}
if(strtolower($periodFormat)=='yearly'){
$time=strtotime($data[$i]["formattedDate"]);
$year=date("Y",$time);
$yValue = $year;
}
if(strtolower($periodFormat)=='quarterly'){
/* $time=strtotime($data[$i]["formattedDate"]);
//Get the month number of the date
//in question.
$month = date("n", strtotime($time));
//Divide that month number by 3 and round up
//using ceil.
$yearQuarter = ceil($month / 3);
//Print it out
$yValue = $yearQuarter . " " . date("Y", strtotime($dateStr)); */
$yValue = $data[$i]["qtrYear"];
}
if($data[$i]["total_quantity"]>0){
if($yValue==date('Y')){
$yValue .= ' till';
}
$responceArray[] = array(
'x' => trim($yValue),
'y' => number_format(round($data[$i]["total_quantity"]/1000000, 2),2)
);
}
}
return $responceArray;
} else {
return 'false';
}
//query formation - END
//$data = $data ->toSql();
/* echo "";
print_r($data);
dd(); */
/* $data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $monthsArray) {
foreach ($monthsArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val),2)
);
}
}
return $responceArray;
}else{
return 'false';
} */
// }
/* if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange);
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
}
$data= $data->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $val) {
$responceArray[] = array(
'x' => $keys,
'y' => $val>1 ? number_format(array_sum($val)/$valDivideBy,2):number_format(array_sum($val),2)
);
}
return $responceArray;
}else{
return 'false';
}
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
}
$data= $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
$arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $yearArray) {
foreach ($yearArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val),2)
);
}
}
return $responceArray;
}else{
return 'false';
}
} */
}else{
return 'false';
}
}
public function getGraphData_16_4_2020($attr){
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr['dataType'])->value('tableName');
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
/*if($memid<=0){
$status = array(
'status' => 2001,
'message' => 'Unauthorized action'
);
return response()->json($status);
}*/
$user = new User($memid, $appType='SM');
if( isset($attr['requiredFor']) && $attr['requiredFor']=='comparison') {
$isAccessible = $user->isAccessible($resourceType='Web', $section='comparison');
}else{
$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
}
$valDivideBy = 1;
if($isAccessible>=0){//visitor or upgrade
$periodFormat = 'yearly';
$attr['fromDate'] = '2017';
$attr['toDate'] = date('Y');
$valDivideBy = 3.7;
}else{
//isset($attr['fromDate'])?$attr['fromDate']:;
//isset($attr['toDate'])?$attr['fromDate']:;
if($attr['source']=='tabular'){
if(strtolower($periodFormat)=='monthly'){
$sectionFromDate = date('M Y',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('M Y',strtotime(Session::get('sectionTodate')));
} elseif(strtolower($periodFormat)=='yearly'){
$sectionFromDate = date('Y',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('Y',strtotime(Session::get('sectionTodate')));
}else{
$sectionFromDate = date('m',strtotime(Session::get('sectionFromDate')));
$sectionTodate = date('m',strtotime(Session::get('sectionTodate')));
$yearQuarterFrom = ceil($sectionFromDate / 3);
$sectionFromDate = "q".$yearQuarterFrom. ' '.date('Y',strtotime(Session::get('sectionFromDate')));
$yearQuarterto = ceil($sectionTodate / 3);
$sectionTodate = "q".$yearQuarterto. ' '.date('Y',strtotime(Session::get('sectionTodate')));
}
$attr['fromDate'] = $sectionFromDate;
$attr['toDate'] = $sectionTodate;
//if(!isset($attr['periodFormat']))
// $periodFormat = 'monthly';
}
//$attr['fromDate'] = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
//$attr['toDate'] = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
}
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
//print_r($explodeFrom);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
}
$data= $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $monthsArray) {
foreach ($monthsArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val),2)
);
}
}
return $responceArray;
}else{
return 'false';
}
}
//return $responceArray;
if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
//DB::enableQueryLog();
$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange);
//dd(DB::getQueryLog());
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
}
$data= $data->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $val) {
$responceArray[] = array(
'x' => $keys,
'y' => $val>1 ? number_format(array_sum($val)/$valDivideBy,2):number_format(array_sum($val),2)
);
}
return $responceArray;
}else{
return 'false';
}
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
if($attr['source']=='tabular'){
if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
$data = $data->where($commodityColumn,$commodity);
}
if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
$data = $data->where($columnNameOfRegion,$region);
}
}else{
$data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
}
$data= $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();
// $data= $data->orderBy('YYMM','ASC')->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$arragedArray = array();
foreach ($data as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
$arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $yearArray) {
foreach ($yearArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val),2)
);
}
}
return $responceArray;
}else{
return 'false';
}
}
}else{
return 'false';
}
}
public function getHeaderNameAndColumns($attr){
$getDataTypeId = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr['dataType'])->value('id');
if($getDataTypeId){
$getHeaderFromTable = DB::table('tbl_stats_datatype_header_dtls')->where('datatypeID',$getDataTypeId)->get();
$getHeaderFromTable = json_decode(json_encode($getHeaderFromTable), true);
if(count($getHeaderFromTable) > 0){
$headerArray = array();
foreach ($getHeaderFromTable as $key => $value) {
$headerArray[] = array(
'headerName' => $value['headerName_display'],
'headerColumn' => $value['tbl_column_name']
);
}
return $headerArray;
}else{
return 'false';
}
}else{
return 'false';
}
}
public function getChildrenGraphPoint($attr, $siteName = 'SM', $title){
if( $siteName == 'CM' ){
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')
->where('datatypeName',$attr['dataType'] . "_not_reqd")
->value('tableName');
} else {
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')
->where('datatypeName',$attr['dataType'])
->value('tableName');
}
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$headerColumn = trim($attr['headerColumn']);
if($headerColumn == 'subCommodity'){
if( $commodityColumn == 'commodity_new' )
$headerColumn = 'subCommodity';
else
$headerColumn = 'subSubCommodity';
}
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
$user = new User($memid, $appType='SM');
$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
$valDivideBy = 1;
if($isAccessible>=0){//visitor or upgrade
$periodFormat = 'monthly';
$attr['fromDate'] = 'jul 2019';
$attr['toDate'] = "dec 2019";//date('Y');
$valDivideBy = 1;
}
$extraCol = "qtrYear";
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$time1 = strtotime($fromDate);
$time2 = strtotime($toDate);
$fromDate = date('Y-m-d', $time1);
$toDate = date('Y-m-d', $time2);
$grpBy = "formattedDate";
} elseif(strtolower($periodFormat)=='yearly'){
$fromDate =trim($attr['fromDate']).'-01'.'-01';
$toDate = trim($attr['toDate']).'-12'.'-31';
$grpBy = $yearColumnName;
} elseif(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth;
$time1 = strtotime($fromDate);
$time2 = strtotime($toDate);
$fromDate = date('Y-m-d', $time1);
$toDate = date('Y-m-31', $time2);
$grpBy = "qtrYear";
}
if(!empty($attr['filters']['app_type'])){
if( $attr['filters']['app_type'] == 'CM' )
$siteFilter = "coalmint";
else
$siteFilter = "steelmint";
}else{
$siteFilter = "steelmint";
}
if($memid==56350000)
DB::enableQueryLog();
$data = DB::table($getTableByDataType)
-> select($headerColumn, $extraCol, 'formattedDate', DB::raw('SUM(' . $quantityColumnName . ') as total_quantity'))
-> whereNotIn($headerColumn,['','-'])
-> where($commodityColumn,$commodity)
-> where($columnNameOfRegion,$region)
-> where('formattedDate', '>=', $fromDate)
-> where('formattedDate', '<=', $toDate)
-> where('website_access', 'like', '%' . $siteFilter . '%')
-> groupBy($grpBy, $headerColumn)
-> orderBy($grpBy, 'ASC')
-> orderBy('total_quantity','DESC')
-> get();
if($memid==56350000)
dd(DB::getQueryLog());
/* echo "" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region . " -- " . $headerColumn . " -- " . $grpBy . " -- " . $extraCol . " -- " . $quantityColumnName . " -- " . $fromDate . " -- " . $toDate;
print_r($data);
dd(); */
/* if ($attr['memid'] == 116707 ){
echo "";
print_r($data);
} */
$data = json_decode(json_encode($data), true);
/* echo "";
print_r($data);
dd(); */
$minDateArr = explode('-',$fromDate);
$maxDateArr = explode('-',$toDate);
$atcdds = array();
$properOrderArr = array();
$assessmentArr = array();
$icnt = 0;
if(count($data) > 0){
$start = $month = strtotime($fromDate);
$end = strtotime($toDate);
while($month <= $end)
{
if(strtolower($periodFormat)=='quarterly'){
$monthn = date("n", $month);
$yearQuarter = ceil($monthn / 3);
////echo "Q$yearQuarter " . date("Y",$month), PHP_EOL;
if(in_array("Q$yearQuarter " . date("Y",$month), $assessmentArr)===false)
$assessmentArr[] = "Q$yearQuarter " . date("Y",$month);
}elseif(strtolower($periodFormat)=='monthly'){
$assessmentArr[] = date('F Y', $month);
// date('F Y', $month), PHP_EOL;
}else{
if(in_array(date("Y",$month), $assessmentArr)===false)
$assessmentArr[] = date('Y', $month);
}
$month = strtotime("+1 month", $month);
}
for($i = 0; $i < count($data); $i++){
$yValue = '';
$year=date("Y",$time);
$newHeaderColumn = $data[$i][$headerColumn];
if(strtolower($periodFormat)=='monthly'){
$time=strtotime($data[$i]["formattedDate"]);
$month=date("F",$time);
$year=date("Y",$time);
$yValue = $month . ' ' . $year;
}
if(strtolower($periodFormat)=='yearly'){
$time=strtotime($data[$i]["formattedDate"]);
$year=date("Y",$time);
$yValue = $year;
$asArrYr2[$newHeaderColumn] += $data[$i]["total_quantity"];
}
if(strtolower($periodFormat)=='quarterly'){
$yValue = $data[$i]["qtrYear"];
$year = end(explode(' ',$yValue));
}
$asArr[$newHeaderColumn][$yValue] = $data[$i]["total_quantity"];
$asArr1[$yValue][$newHeaderColumn] = $data[$i]["total_quantity"];
$asArrYr[$year][$newHeaderColumn] += $data[$i]["total_quantity"];
}
if ($attr['memid'] == 56350 ){
//print_r($assessmentArr);
//echo '', print_r($asArrYr), print_r($asArr1),'
';
//echo '', print_r($asArr),'
';
}
if(strtolower($periodFormat)=='yearly'){
arsort($asArrYr2);
}
$asArrYrFin=array();
$asArrYrTbl=array();
ksort($asArrYr);
foreach($asArrYr as $yr=>$colArr){
arsort($colArr);
$asArrYr[$yr] = $colArr;
/* $asArrYr[$yr] = array_keys(array_slice($colArr,0,5));
$asArrYrTbl[$yr] = array_keys(array_slice($colArr,0,10)); */
//$asArrYrFin = array_merge($asArrYrFin,array_slice($colArr,0,5));
}
$asArr2 = array();
foreach($asArrYr as $yr=>$colNameArr):
$o=0;
foreach($colNameArr as $colName=>$colVal):
$colValArr = $asArr[$colName];
//foreach($asArr as $colName=>$colValArr):
$o++;
$_colName = $o>5?'Others':$colName;
if ($attr['memid'] == 56350 ){
//print_r($assessmentArr);
//echo '',$colName,print_r($colValArr), $_colName,'
';die;
}
foreach($colValArr as $mYKey=>$mYValue):
$yrMY = end(explode(' ',$mYKey));
if($yrMY==$yr){
//$_colName = in_array($colName, $asArrYr[$yrMY])!==false?$colName:'Others';
if(isset($asArr2[$_colName][$mYKey]))
if($_colName=='Others')
$asArr2[$_colName][$mYKey] += $mYValue;
else
$asArr2[$_colName][$mYKey] = $mYValue;
else
$asArr2[$_colName][$mYKey] = $mYValue;
}
endforeach;
endforeach;
endforeach;
if ($attr['memid'] == 56350 ){
//print_r($assessmentArr);
///echo '', print_r($asArr2),'
';die;
}
foreach($asArr2 AS $colName=>$colValArr):
foreach($assessmentArr as $mntYr):
if(isset($colValArr[$mntYr])){
$atcdds[$colName]['data'][] = array(
'key' => trim($mntYr),
'value' => $colValArr[$mntYr]
);
}else{
$atcdds[$colName]['data'][] = array(
'key' => trim($mntYr),
'value' => ''
);
}
endforeach;
endforeach;
$asArr2Tbl=array();
foreach($asArrYr as $yr=>$colNameArr):
$o=0;
foreach($colNameArr as $colName=>$colVal):
$colValArr = $asArr[$colName];
//foreach($asArr as $colName=>$colValArr):
$o++;
$_colName = $o>10?'Others':$colName;
foreach($colValArr as $mYKey=>$mYValue):
$yrMY = end(explode(' ',$mYKey));
if($yrMY==$yr){
if(isset($asArr2Tbl[$yr][$_colName][$mYKey]))
if($_colName=='Others')
$asArr2Tbl[$yr][$_colName][$mYKey] += $mYValue;
else
$asArr2Tbl[$yr][$_colName][$mYKey] = $mYValue;
else
$asArr2Tbl[$yr][$_colName][$mYKey] = $mYValue;
}
endforeach;
if(strtolower($periodFormat)!='yearly'){
$asArr2Tbl[$yr][$_colName]['Total'] += $colVal;
}
endforeach;
endforeach;
if ($attr['memid'] == 56350 ){
///print_r($assessmentArr);
///echo '', $title, print_r($asArr2Tbl),'
';
}
///if(strtolower($periodFormat)=='yearly'){
if(strtolower($periodFormat)=='yearly'){
$colspan=count($asArrYr)+1;
$monthsArray=array();
$childGraph='
|
|
';
$minYear1 = $minDateArr[0];
$maxYear1 = $maxDateArr[0];
$childGraph.='| '.$title.' - '.$minYear1.' - '.$maxYear1.' | |
|---|
| |
|---|
';
$childGraph.='| '.ucfirst(strtolower(str_replace('_', ' ',$headerColumn))).' | ';
foreach($asArrYr as $yr=>$val):
$childGraph.=''.$yr.' | ';
endforeach;
$childGraph.='
|---|
';
$o=0;
$othersTotal=array();
foreach($asArrYr2 AS $colName=>$totalVal):
$o++;
if($colName){
if($o<=10)
$childGraph.='| '.$colName.' | ';
foreach($asArrYr as $yr=>$val):
$yrVal = $val[$colName];
if($o>10){
if($yrVal)
$othersTotal[$yr]+=$yrVal;
else
$othersTotal[$yr]+=0;
$mntYrTotal[$yr]+=$yrVal;
}else{
if($yrVal){
$childGraph.=''.number_format($yrVal).' | ';
$mntYrTotal[$yr]+=$yrVal;
}else
$childGraph.=' | ';
}
endforeach;
$childGraph.='
|---|
';
}
endforeach;
if(!empty($othersTotal)){
$childGraph.='| Others | ';
foreach($asArrYr as $yr=>$val):
$yrVal = $othersTotal[$yr];
if($yrVal){
$childGraph.=''.number_format($yrVal).' | ';
}else
$childGraph.=' | ';
endforeach;
$childGraph.='
|---|
';
}
$GrandTotal=0;
$childGraph.='';
$childGraph.='| Grand Total | ';
foreach($asArrYr as $yr=>$val):
if($mntYrTotal[$yr]){
$childGraph.=''.number_format($mntYrTotal[$yr]).' | ';
$GrandTotal+=$mntYrTotal[$yr];
}else
$childGraph.=' | ';
endforeach;
///$childGraph.=''.number_format($GrandTotal).' | ';
$childGraph.='
';
$childGraph.='| All Quantity in MT |
';
}else{
if(strtolower($periodFormat)=='monthly'){
$colspan=14;
$monthsArray = array(
'Jan' => 'January',
'Feb' => 'February',
'Mar' => 'March',
'Apr' => 'April',
'May' => 'May',
'Jun' => 'June',
'Jul' => 'July',
'Aug' => 'August',
'Sep' => 'September',
'Oct' => 'October',
'Nov' => 'November',
'Dec' => 'December'
);
}elseif(strtolower($periodFormat)=='quarterly'){
$colspan=6;
$monthsArray = array(
'Q1' => 'Q1',
'Q2' => 'Q2',
'Q3' => 'Q3',
'Q4' => 'Q4'
);
}
$childGraph=' |
|
';
foreach($asArr2Tbl AS $yr=>$colValArr1):
$childGraph.='| '.$title.' - '.$yr.' | |
|---|
| |
|---|
';
$childGraph.=' | ';
foreach($monthsArray as $mnt):
$childGraph.=''.$mnt.' | ';
endforeach;
$childGraph.='Total | ';
$childGraph.='
|---|
';
$mntYrTotal=array();
foreach($colValArr1 AS $colName=>$colValArr):
$childGraph.='';
$childGraph.='| '.$colName.' | ';
foreach($monthsArray as $mnt):
$mntYr = $mnt.' '.$yr;
$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr]):'';
if($colValArr[$mntYr])
$mntYrTotal[$mnt]+=$colValArr[$mntYr];
//$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr],2,'.',''):'-';
$childGraph.=''.$val1.' | ';
if(isset($colValArr[$mntYr])){
$atcddsTbl[$yr][$colName]['data'][] = array(
'key' => trim($mntYr),
'value' => $colValArr[$mntYr]
);
}else{
$atcddsTbl[$yr][$colName]['data'][] = array(
'key' => trim($mntYr),
'value' => ''
);
}
endforeach;
$total1 = $asArr2Tbl[$yr][$colName]['Total'];
$childGraph.=''.number_format($total1).' | ';
$childGraph.='
';
//$atcddsTbl[$yr][$colName]['data'][]= array(
// 'key' => 'Total',
// 'value' => $colValArr['Total']
// );
endforeach;
$GrandTotal=0;
$childGraph.='';
$childGraph.='| Grand Total | ';
foreach($monthsArray as $mnt):
if($mntYrTotal[$mnt]){
$childGraph.=''.number_format($mntYrTotal[$mnt]).' | ';
$GrandTotal+=$mntYrTotal[$mnt];
}else{
$childGraph.=' | ';
}
endforeach;
$childGraph.=''.number_format($GrandTotal).' | ';
$childGraph.='
';
$childGraph.=' |
|
';
endforeach;
$childGraph.='| All Quantity in MT |
';
}
if ($attr['memid'] == 56350 ){
//print_r($assessmentArr);
//echo '', print_r($asArr2), print_r($asArr),'
';
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
$responceArray['tblvalue'] = ($attr['memid'] == 5635000)?$atcddsTbl:$childGraph;
return $responceArray;
} else {
return 'false';
}
$firstColHeaderColumnName='';
if(count($data) > 0){
for($i = 0; $i < count($data); $i++){
$yValue = '';
if(strtolower($periodFormat)=='monthly'){
$time=strtotime($data[$i]["formattedDate"]);
$month=date("F",$time);
$year=date("Y",$time);
$yValue = $month . ' ' . $year;
}
if(strtolower($periodFormat)=='yearly'){
$time=strtotime($data[$i]["formattedDate"]);
$year=date("Y",$time);
$yValue = $year;
}
if(strtolower($periodFormat)=='quarterly'){
$yValue = $data[$i]["qtrYear"];
}
if( empty($properOrderArr[$yValue]) ){
$properOrderArr[$yValue] = 0;
$icnt += 1;
}
if($data[$i]["total_quantity"]>0){
$properOrderArr[$yValue] += 1;
if( $properOrderArr[$yValue] == 6 ){
$newHeaderColumn = "Others";
$flag = 0;
foreach($atcdds[$newHeaderColumn]['data'] as $key => $value){
if( $value['key'] == $yValue ){
$flag = 1;
$atcdds[$newHeaderColumn]['data'][$key]['value'] += $data[$i]["total_quantity"];
}
}
if( $flag == 0 ){
$atcdds[$newHeaderColumn]['data'][] = array(
'key' => trim($yValue),
'value' => $data[$i]["total_quantity"]
);
}
} elseif( $properOrderArr[$yValue] > 6 ){
$newHeaderColumn = "Others";
foreach($atcdds[$newHeaderColumn]['data'] as $key => $value){
if( $value['key'] == $yValue )
$atcdds[$newHeaderColumn]['data'][$key]['value'] += $data[$i]["total_quantity"];
}
} else {
$newHeaderColumn = $data[$i][$headerColumn];
if($firstColHeaderColumnName==''){
$firstColHeaderColumnName = $newHeaderColumn;
}
if( $newHeaderColumn == 'Others' ){
$newHeaderColumn = "Others";
$atcdds[$newHeaderColumn]['data'][] = array(
'key' => trim($yValue),
'value' => $data[$i]["total_quantity"]
);
$properOrderArr[$yValue] -= 1;
} else {
$newqty = $data[$i]["total_quantity"];
$atcdds[$newHeaderColumn]['data'][] = array(
'key' => trim($yValue),
'value' => number_format($newqty,2)
);
}
}
}
}
if ($attr['memid'] == 56350 ){
//echo "";
/* print_r($properOrderArr);
foreach($properOrderArr as $k1=>$v1){
$atcdds[$newHeaderColumn]['data'] =
} */
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
} else {
return 'false';
}
}
/* if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
if($getTableByDataType=='tbl_capacity'){
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->where($yearColumnName, '>=', $fromYear)
->where($yearColumnName, '<=', $toYear)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$headerColumn)
->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();
} else {
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$monthColumName,$headerColumn)
->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();
}
if($attr['debugMode'] == true){
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->where($yearColumnName, '>=', $fromYear)
->where($yearColumnName, '<=', $toYear)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$headerColumn)
->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC');
echo "";
print_r($data->getBindings() );
die;
}
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
$qtySumArray = array();
foreach ($variable as $key => $value) {
$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];
}
arsort($qtySumArray);
$qtySumArrayKeys = array_chunk(array_keys($qtySumArray),4);
foreach ($variable as $key => $value) {
$newkey = $value[$headerColumn];
if(in_array($newkey, $qtySumArrayKeys[0])===false){
$newkey = 'Others';
}
if(!isset($categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$categoryArrayOther=[];
if(isset($categoryArray['Others'])){
$categoryArrayOther = $categoryArray['Others'];
unset($categoryArray['Others']);
$categoryArray['Others'] = $categoryArrayOther;
}
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($monthList as $ky => $val) {
if(array_key_exists($val, $value['data'])){
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
//return $responceArray;
if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
if($getTableByDataType=='tbl_capacity'){
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
}else{
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
}
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($getYearRange as $ky => $val) {
if(array_key_exists($val, $value['data'])){
//$value['data'][$val] = $value['data'][$val]/1000000;
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$quarterArray = array();
foreach ($monthList as $kesdsdy => $sdsddsds) {
$explode = explode(' ', $sdsddsds);
$qter = ceil(date("n", strtotime($explode[0]))/3);
$vaknhm = 'Q'.$qter.' '.$explode[1];
if(!in_array($vaknhm,$quarterArray)){
$quarterArray[] = $vaknhm;
}
}
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($quarterArray as $ky => $val) {
if(array_key_exists($val, $value['data'])){
//$value['data'][$val] = $value['data'][$val]/1000000;
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
}else{
return 'false';
} */
}
public function getChildrenGraphPoint_20_5_2020($attr){
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr['dataType'])->value('tableName');
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$headerColumn = trim($attr['headerColumn']);
if($headerColumn == 'subCommodity'){
if( $commodityColumn == 'commodity_new' )
$headerColumn = 'subCommodity';
else
$headerColumn = 'subSubCommodity';
}
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
$user = new User($memid, $appType='SM');
$isAccessible = $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
$valDivideBy = 1;
if($isAccessible>=0){//visitor or upgrade
/* $periodFormat = 'yearly';
$attr['fromDate'] = '2017';
$attr['toDate'] = date('Y');
$valDivideBy = 3.7; */
$periodFormat = 'monthly';
$attr['fromDate'] = 'jul 2019';
$attr['toDate'] = "dec 2019";//date('Y');
$valDivideBy = 1;
}
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
if($getTableByDataType=='tbl_capacity'){
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->where($yearColumnName, '>=', $fromYear)
->where($yearColumnName, '<=', $toYear)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$headerColumn)
->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();
} else {
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$monthColumName,$headerColumn)
->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();
}
if($attr['debugMode'] == true){
$data = DB::table($getTableByDataType)
->whereNotIn($headerColumn,['','-'])
->where($yearColumnName, '>=', $fromYear)
->where($yearColumnName, '<=', $toYear)
->where($commodityColumn,$commodity)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$headerColumn)
->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC');
echo "";
print_r($data->getBindings() );
die;
}
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
$qtySumArray = array();
foreach ($variable as $key => $value) {
$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];
}
arsort($qtySumArray);
$qtySumArrayKeys = array_chunk(array_keys($qtySumArray),4);
foreach ($variable as $key => $value) {
$newkey = $value[$headerColumn];
if(in_array($newkey, $qtySumArrayKeys[0])===false){
$newkey = 'Others';
}
if(!isset($categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
//$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];
}
$categoryArrayOther=[];
if(isset($categoryArray['Others'])){
$categoryArrayOther = $categoryArray['Others'];
unset($categoryArray['Others']);
$categoryArray['Others'] = $categoryArrayOther;
}
/* echo "";
print_r($categoryArray); print_r($qtySumArrayKeys);
*/
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($monthList as $ky => $val) {
if(array_key_exists($val, $value['data'])){
//$value['data'][$val] = $value['data'][$val]/1000000;
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
//return $responceArray;
if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
if($getTableByDataType=='tbl_capacity'){
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
}else{
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
}
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
/* echo "";
print_r($variable);
die();*/
foreach ($variable as $key => $value) {
if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($getYearRange as $ky => $val) {
if(array_key_exists($val, $value['data'])){
//$value['data'][$val] = $value['data'][$val]/1000000;
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}/* else{
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => 0
);
} */
}
}
/*$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($value as $ky => $val) {
foreach ($val as $k => $vae) {
foreach ($getYearRange as $keysds => $sdsd) {
if(array_key_exists($sdsd, $val)){
$atcdds[$key][$ky][] = array(
'key' => $k,
'value' => number_format($vae,2)
);
}else{
$atcdds[$key][$ky][] = array(
'key' => $sdsd,
'value' => 0
);
}
}
}
}
}*/
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
/*foreach ($variable as $key => $value) {
$categoryArray[$value[$yearColumnName]][][$value[$headerColumn]] = number_format($value[$quantityColumnName],2);
}
return $categoryArray;*/
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
/*
if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_capacity'){
$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
}else{
}*/
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$quarterArray = array();
foreach ($monthList as $kesdsdy => $sdsddsds) {
$explode = explode(' ', $sdsddsds);
$qter = ceil(date("n", strtotime($explode[0]))/3);
$vaknhm = 'Q'.$qter.' '.$explode[1];
if(!in_array($vaknhm,$quarterArray)){
$quarterArray[] = $vaknhm;
}
}
/* echo "";
print_r($quarterArray);
die();*/
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($quarterArray as $ky => $val) {
if(array_key_exists($val, $value['data'])){
//$value['data'][$val] = $value['data'][$val]/1000000;
$value['data'][$val] = $value['data'][$val];
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
);
}/* else{
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => 0
);
} */
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
}else{
return 'false';
}
}
//chnages on 18Dec19
public function getChildrenGraphPointBak($attr){
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr['dataType'])->value('tableName');
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$headerColumn = trim($attr['headerColumn']);
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
/*echo "";
print_r($monthList);
die();*/
$data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
/*$categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = number_format($value[$quantityColumnName],2);*/
if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
//echo "";
//print_r($categoryArray);
//die();
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($monthList as $ky => $val) {
if(array_key_exists($val, $value['data'])){
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => number_format($value['data'][$val],2)
);
}else{
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => 0
);
}
}
}
//die();
/* foreach ($categoryArray as $key => $value) {
foreach ($value as $ky => $val) {
foreach ($monthList as $ksds => $sdsddsd) {
foreach ($val as $k => $vae) {
$checkExist = array_search($k,$monthList);
if($checkExist){
$atcdds[$key][$ky][] = array(
'key' => $k,
'value' => number_format($vae,2)
);
}else{
$atcdds[$key][$ky][] = array(
'key' => $monthList[$checkExist],
'value' => 0
);
}
}
}
}
}*/
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
//return $responceArray;
if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
//DB::enableQueryLog();
$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
//dd(DB::getQueryLog()); // Show results of log
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
/*$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] =number_format($value[$quantityColumnName],2);*/
if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
/* echo "";
print_r($categoryArray);
die();*/
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($getYearRange as $ky => $val) {
if(array_key_exists($val, $value['data'])){
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => number_format($value['data'][$val],2)
);
}else{
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => 0
);
}
}
}
/*$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($value as $ky => $val) {
foreach ($val as $k => $vae) {
foreach ($getYearRange as $keysds => $sdsd) {
if(array_key_exists($sdsd, $val)){
$atcdds[$key][$ky][] = array(
'key' => $k,
'value' => number_format($vae,2)
);
}else{
$atcdds[$key][$ky][] = array(
'key' => $sdsd,
'value' => 0
);
}
}
}
}
}*/
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
/*foreach ($variable as $key => $value) {
$categoryArray[$value[$yearColumnName]][][$value[$headerColumn]] = number_format($value[$quantityColumnName],2);
}
return $categoryArray;*/
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
$variable = json_decode(json_encode($data), true);
$responceArray = array();
$categoryArray = array();
foreach ($variable as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
}else{
$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
}
}
$quarterArray = array();
foreach ($monthList as $kesdsdy => $sdsddsds) {
$explode = explode(' ', $sdsddsds);
$qter = ceil(date("n", strtotime($explode[0]))/3);
$vaknhm = 'Q'.$qter.' '.$explode[1];
if(!in_array($vaknhm,$quarterArray)){
$quarterArray[] = $vaknhm;
}
}
/* echo "";
print_r($quarterArray);
die();*/
$atcdds = array();
foreach ($categoryArray as $key => $value) {
foreach ($quarterArray as $ky => $val) {
if(array_key_exists($val, $value['data'])){
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => number_format($value['data'][$val],2)
);
}else{
$atcdds[$key]['data'][] = array(
'key' => $val,
'value' => 0
);
}
}
}
$responceArray['headerColumn'] = $headerColumn;
$responceArray['value'] = $atcdds;
return $responceArray;
}
}else{
return 'false';
}
}
public function getQuater($quater){
$fromMonth = '';
if($quater=='Q1'){
$fromMonth = 'January';
}if($quater=='Q2'){
$fromMonth = 'April';
}if($quater=='Q3'){
$fromMonth = 'July';
}if($quater=='Q4'){
$fromMonth = 'October';
}
return $fromMonth;
}
public function getQuaterAfter($quater){
$fromMonth = '';
if($quater=='Q1'){
$fromMonth = 'March';
}if($quater=='Q2'){
$fromMonth = 'June';
}if($quater=='Q3'){
$fromMonth = 'September';
}if($quater=='Q4'){
$fromMonth = 'December';
}
return $fromMonth;
}
public function getMonthBetweenTwoDates($date1,$date2){
$time1 = strtotime($date1);
$time2 = strtotime($date2);
$my = date('mY', $time2);
$months = array(date('F Y', $time1));
$f = '';
while($time1 < $time2) {
$time1 = strtotime((date('Y-m-d', $time1).' +15days'));
if(date('F', $time1) != $f) {
$f = date('F', $time1);
if(date('mY', $time1) != $my && ($time1 < $time2))
$months[] = date('F Y', $time1);
}
}
$months[] = date('F Y', $time2);
array_shift($months);
return $months;
}
public function addtoPortfolio($request){
if($request['graphType']=='parent' || $request['graphType']=='child'){
if(empty($request['childGraphType']))
$cgt = '';
else
$cgt = $request['childGraphType'];
$id = DB::table('portfolio')->insertGetId([
'memid' => $request['memid'],
'dataType' => $request['dataType'],
'commodity' => $request['commodity'],
'region' => $request['region'],
'fromDate' => $request['fromDate'],
'toDate' => $request['toDate'],
'periodFormat' => $request['periodFormat'],
'columnNameOfRegion' => $request['columnNameOfRegion'],
'columnNameOfCommodity' => $request['columnNameOfCommodity'],
'type' => $request['graphType'],
'childGraphType' => $cgt,
'excel_card' => (isset($request['excel_card']) && $request['excel_card'] != null)?$request['excel_card']:"n"
]);
return $id;
}else{
return 'false';
}
}
public function removeFromPortfolio($request){
if(DB::table('portfolio')->where('id',$request['portFolioId'])->where('memid',$request['memid'])->count() > 0){
DB::table('portfolio')->where('id',$request['portFolioId'])->delete();
return $request['portFolioId'];
}else{
return 'false';
}
}
public function getRecentView($request){
$type = trim($request['type']);
$memid = trim($request['memid']);
if(DB::table('recent_activities')->where('type',$type)->where('memid',$memid)->count() > 0){
$data = DB::table('recent_activities')
->where('type',$type)
->where('memid',$memid)
//->groupBy('dataType', 'commodity', 'region', 'fromDate', 'toDate', 'periodFormat', 'columnNameOfRegion', 'columnNameOfCommodity')
->orderBy('id','DESC')
->limit(5)
->get();
return $data = json_decode(json_encode($data), true);
}else{
return 'false';
}
}
public function checkPortfolio($request,$type){
/* if($request['debugMode']=='true')
DB::enableQueryLog(); */
if($request['source']=='home'){
$request['periodFormat'] = 'monthly';
$request['fromDate'] = 'jul 2019';
$request['toDate'] = "dec 2019";
}
$check_portfolio = DB::table('portfolio')
->where('memid',$request['memid'])
->where('dataType',$request['dataType'])
->where('commodity',$request['commodity'])
->where('region',trim($request['region']))
->where('fromDate',$request['fromDate'])
->where('toDate',$request['toDate'])
->where('periodFormat',$request['periodFormat'])
->where('columnNameOfRegion',$request['columnNameOfRegion'])
->where('columnNameOfCommodity',$request['columnNameOfCommodity'])
->where('type',$type);
if($type == "child"){
if( $request['headerColumn'] == 'subCommodity' ){
$check_portfolio = $check_portfolio ->where('childGraphType',$request['headerColumn']);
$check_portfolio = $check_portfolio ->orWhere('childGraphType','subSubCommodity');
} else
$check_portfolio = $check_portfolio ->where('childGraphType',$request['headerColumn']);
}
$check_portfolio = $check_portfolio ->orderBy("id",'DESC');
$check_portfolio = $check_portfolio->first();
//if($request['debugMode']=='true')
//dd(DB::getQueryLog());
if($request['debugMode']=='true'){
echo "check_portfolio:";
print_r($check_portfolio);
//echo "check_portfolio:" . $check_portfolio['id'];
}
/* echo "";
print_r($check_portfolio); */
if($type == "parent"){
$post = DB::table('recent_activities')->insert([
'memid' => $request['memid'],
'dataType' => $request['dataType'],
'commodity' => $request['commodity'],
'region' => $request['region'],
'fromDate' => $request['fromDate'],
'toDate' => $request['toDate'],
'periodFormat' => $request['periodFormat'],
'columnNameOfRegion' => $request['columnNameOfRegion'],
'columnNameOfCommodity' => $request['columnNameOfCommodity'],
'f_datetime' => date("Y-m-d H:i:s")
]);
}
if(count($check_portfolio) > 0){
return $check_portfolio['id'];
} else {
return "false";
}
}
public function getUserStatsPortfolioV2019($user_id, $appType='SM')
{
$res = array();
//->where('type', '=' , $type)->where('dataType', '=' , $appType)
$temp = DB::table('portfolio')
->select('*')
->where('memid', '=' , $user_id)
->orderBy("id",'DESC')
->get();
foreach($temp as $k=>$v){
$res[] = $v;
}
return $res;
}
/* Historical data Api */
public function historicalGetData($attr, $memid, $appType='SM'){
$type = strtolower($attr);
/// echo $appType.'appType';die;
if($appType=='SM')
$dataCom = DB::table('tbl_historical_landing')
->where('section',$type)
->whereNOTIn('subCommodity', array('Non Coking', 'Non Coking Coal', 'Pet Coke', 'Anthracite', 'Others', 'Bituminous Coal', 'Lignite'))
->get();
else
$dataCom = DB::table('tbl_historical_landing')->where('section',$type)->get();
$dataCom = json_decode(json_encode($dataCom), true);
//for commodity
array_multisort(array_column($dataCom, 'commodity_orderby_1st_level'), SORT_ASC,
//array_column($dataCom, 'commodity_orderby_2nd_level'), SORT_ASC,
array_column($dataCom, 'country'), SORT_ASC,
$dataCom);
if(count($dataCom) > 0){
$responceArray = array();
$responceArrays = array();
$sortCommodity = array();
foreach ($dataCom as $key => $value) {
if(strtotime($value['dataAvailableFrom']) >= strtotime('2008-01-01')){
if(!empty($value['commodity'])){
$responceArray[$value['commodity']][] = array(
'id' => $value['id'],
'subCommodity' => $value['subCommodity'],
'subSubCommodity' => $value['subSubCommodity'],
'country' => $value['country'],
'tradeType' => $value['tradeType'],
'dataAvailableFrom' => $value['dataAvailableFrom']
);
}
/* if(!empty($value['country'])){
$responceArrays[$value['country']][] = array(
'id' => $value['id'],
'commodity' => $value['commodity'],
'subCommodity' => $value['subCommodity'],
'subSubCommodity' => $value['subSubCommodity'],
'tradeType' => $value['tradeType'],
'dataAvailableFrom' => $value['dataAvailableFrom']
);
} */
}
}
}
//for commodity
array_multisort(array_column($dataCom, 'region_orderby_1st_level'), SORT_ASC,
array_column($dataCom, 'region_orderby_2nd_level'), SORT_ASC,
array_column($dataCom, 'commodity_orderby_1st_level'), SORT_ASC,
array_column($dataCom, 'commodity_orderby_2nd_level'), SORT_ASC,
$dataCom);
if(count($dataCom) > 0){
//$responceArray = array();
$responceArrays = array();
foreach ($dataCom as $key => $value) {
if(strtotime($value['dataAvailableFrom']) >= strtotime('2008-01-01')){
/* if(!empty($value['commodity'])){
$responceArray[$value['commodity']][] = array(
'id' => $value['id'],
'subCommodity' => $value['subCommodity'],
'subSubCommodity' => $value['subSubCommodity'],
'country' => $value['country'],
'tradeType' => $value['tradeType'],
'dataAvailableFrom' => $value['dataAvailableFrom']
);
} */
if(!empty($value['country'])){
$responceArrays[$value['country']][] = array(
'id' => $value['id'],
'commodity' => $value['commodity'],
'subCommodity' => $value['subCommodity'],
'subSubCommodity' => $value['subSubCommodity'],
'tradeType' => $value['tradeType'],
'dataAvailableFrom' => $value['dataAvailableFrom']
);
}
}
}
}
$responce['commodityWise'] = $responceArray;
$responce['regionWise'] = $responceArrays;
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
$responce['startDate'] = $sectionFromDate;
$responce['endDate'] = $sectionTodate;
}else{
$responce['startDate'] = '1990-01-01';//$GLOBALS['currDate'];
$responce['endDate'] = '1990-01-01';//$GLOBALS['currDate'];
}
return $responce;
/* }else{
return 'false';
} */
}
public function checkListNameExist($attr){
$listName = trim($attr['listName']);
$memid = trim($attr['memid']);
$check = DB::table('tb_historical_data_saved_list')->where('memid',$memid)->where('listName',$listName)->count();
if($check < 1){
return 'true';
}else{
return 'false';
}
}
public function saveHistoricalDataList($attr){
//print_r($attr);
$listName = trim($attr['listName']);
$memid = trim($attr['memid']);
$type = trim($attr['type']);
$requiredFor = trim($attr['requiredFor']);
$fromDates = trim($attr['fromDate']);
$date = str_replace('/', '-', $fromDates);
$fromDate = date("Y-m-d", strtotime($date));
$toDated = trim($attr['toDate']);
$date = str_replace('/', '-', $toDated);
$toDate = date("Y-m-d", strtotime($date));
$getIds = DB::table('tbl_historical_landing')->lists('id');
$checkedlistid = rtrim($attr['checkedIdList'],',');
$explode = explode(',', $checkedlistid);
if(count($explode) > 0){
$str = '';
$check = 'true';
foreach ($explode as $key => $value) {
if(in_array($value,$getIds)){
$str.=$value.',';
}else{
$check = 'false';
}
}
if($check=='true'){
if(strlen($checkedlistid) > 0){
DB::table('tb_historical_data_saved_list')->insert([
'listName' => $listName,
'type' => $type,
'requiredFor' => $requiredFor,
'parent_id' => rtrim($str,','),
'fromDate' => $fromDate,
'toDate' => $toDate,
'memid'=> $memid
]);
return 'Record has been Added!';
}else{
return 'false';
}
}else{
return 'false';
}
}else{
return 'false';
}
}
public function getSavedHistoricaldataList($attr){
$memid = trim($attr['memid']);
//$type = trim($attr['type']);
$getParentData = DB::table('tb_historical_data_saved_list')->where('memid',$memid)->orderBy('id','DESC')->get();
$getParentData = json_decode(json_encode($getParentData), true);
if(count($getParentData) > 0){
$responceArray = array();
foreach ($getParentData as $key => $value) {
$explode = explode(',', $value['parent_id']);
$dataFromParent = DB::table('tbl_historical_landing')->whereIn('id',$explode)->get();
$dataFromParent = json_decode(json_encode($dataFromParent), true);
$responceArray[] = array(
'id' => $value['id'],
'name' => $value['listName'],
'type' => $value['type'],
'fromDate' => $value['fromDate'],
'toDate' => $value['toDate'],
'point' => $dataFromParent
);
}
return $responceArray;
}else{
return 'false';
}
}
public function downloadHistoricaldataList($attr){
$requiredFor = ucfirst(trim($attr['requiredFor']));
$type = trim($attr['type']);
$explode = explode(',', trim($attr['checkedIdList']));
$fromDates = trim($attr['fromDate']);
$date = str_replace('/', '-', $fromDates);
$fromDate = date("Y-m-d", strtotime($date));
$toDated = trim($attr['toDate']);
$date = str_replace('/', '-', $toDated);
$toDate = date("Y-m-d", strtotime($date));
$section = 'Prices_Historical';
if(strtolower($requiredFor)=='price' || strtolower($requiredFor)=='prices')
$section = 'Prices_Historical';
elseif(strtolower($requiredFor)=='statistic' || strtolower($requiredFor)=='statistics')
$section = 'Statistics_Historical';
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
$user = new User($memid, $appType='SM');
$isAccessible = $user->isAccessible($resourceType='Web', $section);
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$fromDate = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
$toDate = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
}else{
$fromDate = '1990-01-01';//$GLOBALS['currDate'];
$toDate = '1990-01-01';//$GLOBALS['currDate'];
}
$dataFromParent = DB::table('tbl_historical_landing')->where('section',$requiredFor)->whereIn('id',$explode)->get();
$dataFromParent = json_decode(json_encode($dataFromParent), true);
$responceArray = array();
foreach ($dataFromParent as $key => $value) {
/* $responceArray[] = array(
'id' => $value['id'],
'tradeType' => $value['tradeType'],
'commodity' => $value['commodity'],
'subCommodity' => $value['subCommodity'],
'country' => $value['country'],
'dataAvailableFrom' => $value['dataAvailableFrom'],
'fromDate' => $fromDate,
'toDate' => $toDate
); */
$responceArray['filePath'] = BASEURL . "excel_download/_intraday_20181026_091049.zip";
}
return $responceArray;
}
public function removeItemFromList($attr){
if(DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->count() > 0){
DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->delete();
return 'true';
}else{
return 'false';
}
}
public function getItemFromList($attr){
if(DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->count() > 0){
$data = DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->first();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$explode = explode(',', $data['parent_id']);
$dataFromParent = DB::table('tbl_historical_landing')->whereIn('id',$explode)->get();
$dataFromParent = json_decode(json_encode($dataFromParent), true);
$responceArray = array();
$dataArray['itemId'] = $data['id'];
$dataArray['parent_id'] = $data['parent_id'];
foreach ($dataFromParent as $key => $value) {
$responceArray[] = array(
'id' => $value['id'],
'tradeType' => $value['tradeType'],
'commodity' => $value['commodity'],
'subCommodity' => $value['subCommodity'],
'country' => $value['country'],
'dataAvailableFrom' => $value['dataAvailableFrom'],
'fromDate' => $data['fromDate'],
'toDate' => $data['toDate']
);
}
$dataArray['selectedItem'] = $responceArray;
return $dataArray;
}else{
return 'false';
}
}else{
return 'false';
}
}
public function editSaveList($attr){
$fromDates = trim($attr['fromDate']);
$date = str_replace('/', '-', $fromDates);
$fromDate = date("Y-m-d", strtotime($date));
$toDated = trim($attr['toDate']);
$date = str_replace('/', '-', $toDated);
$toDate = date("Y-m-d", strtotime($date));
$parent = rtrim($attr['parent_id'],',');
$explode = explode(',', $parent);
$getIds = DB::table('tbl_historical_landing')->lists('id');
if(count($explode) > 0){
$str = '';
$check = 'true';
foreach ($explode as $key => $value) {
if(in_array($value,$getIds)){
$str.=$value.',';
}else{
$check = 'false';
}
}
if($check=='true'){
$updated = DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->update(['parent_id' => rtrim($str,','),'fromDate' => $fromDate,'toDate' => $toDate]);
if($updated){
return 'true';
}else{
return 'false';
}
}else{
return 'false';
}
}else{
return 'false';
}
}
/* Compare Controller Functions*/
public function comGetCommodityRegion($attr){
$requiredFor = trim($attr['requiredFor']);
$dataType = trim($attr['dataType']);
if(DB::table('tbl_stats_datatype_header_list')->where('requiredFor',$requiredFor)->where('datatypeName',$dataType)->count() < 1){
return 'false';
}
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$dataType)->take(1)->get();
$stats = json_decode(json_encode($stats), true);
$data = DB::table('tbl_stats_selection_compare')
->where('datatypeName',$dataType)
->where('commodity','!=','-')
->where('subCommodity','!=','-')
->where('subSubCommodity','!=','-')
->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$communityArray = array();
$tempArray = array();
$tempSubArray = array();
$regionArray = array();
$tempsubsubArray = array();
$col_name_region = '';
foreach ($data as $key => $value) {
if($col_name_region==''){
$col_name_region = $value['col_name_region'];
}
$temps = $value['commodity'].'-'.$value['subCommodity'].'-'.$value['subSubCommodity'];
if(!in_array($temps,$tempsubsubArray)){
if($value['subSubCommodity']!=''){
$communityArray[$value['commodity']][$value['subCommodity']][] = $value['subSubCommodity'];
}else{
$communityArray[$value['commodity']][] = $value['subCommodity'];
}
$tempsubsubArray[] = $temps;
}
/* $communityArray[$value['commodity']][$value['subCommodity']] = $value['subSubCommodity']; */
$cont = $value['commodity'].'-'.$value['region'];
$conts = $value['subCommodity'].'-'.$value['region'];
$contss = $value['subSubCommodity'].'-'.$value['region'];
if(!in_array($cont, $tempArray)){
$regionArray[$value['commodity']][] = $value['region'];
$tempArray[] = trim($cont);
}
if(!in_array($conts,$tempSubArray)){
if($value['commodity']!=$value['subCommodity']){
$regionArray[$value['subCommodity']][] = $value['region'];
$tempSubArray[] = $conts;
}
if(!in_array($contss, $tempArray)){
$regionArray[$value['subSubCommodity']][] = $value['region'];
$tempSubSubArray[] = trim($contss);
}
}
}
}
$regionColname = $col_name_region;
$periodFormat = $stats[0]['periodFormat'];
$dataArray = array(
'columnNameOfRegion' => $regionColname,
'columnNameOfCommodity' => $stats[0]['col_name_commodity_compare_toolbox'],
'commodity' => $communityArray,
'region' => $regionArray
);
return $dataArray;
}
public function formatCompareHeaderData($attr){
$array = array();
foreach ($attr as $key => $variable) {
foreach ($variable as $ke => $val) {
$array[] = $val;
}
}
return $array;
}
public function getHeaderList($attr){
$datatype = trim($attr['dataType']);
$columnNameOfCommodity = trim($attr['columnNameOfCommodity']);
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$commodity = trim($attr['commodity']);
$region = trim(ucfirst($attr['region']));
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->take(1)->get();
$getTableByDataType = json_decode(json_encode($getTableByDataType), true);
if(count($getTableByDataType) > 0){
$getHeaderscolumns = DB::table('tbl_stats_datatype_header_dtls')->where('datatypeID',$getTableByDataType[0]['id'])->get();
$getHeaderscolumns = json_decode(json_encode($getHeaderscolumns), true);
if(count($getHeaderscolumns) > 0){
$responceArray = array();
foreach ($getHeaderscolumns as $key => $value) {
$responceArray['headers'][$value['headerName_display']]['headerColumn'] = $value['tbl_column_name'];
$getvalues = DB::table($getTableByDataType[0]['tableName'])->select($value['tbl_column_name'])
->where($columnNameOfCommodity,$commodity)
->where($columnNameOfRegion,$region)
->distinct()->get();
$heaedtr = $this->formatCompareHeaderData($getvalues);
$responceArray['headers'][$value['headerName_display']]['values'] = $heaedtr;
}
return $responceArray;
}else{
return 'false';
}
}else{
return 'false';
}
}
/****************************/
public function getDataForCompare($attr){
$datatype = trim($attr['dataType']);
$getTableByDataType = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->value('tableName');
if($getTableByDataType){
$periodFormat = trim($attr['periodFormat']);
$commodityColumn = trim($attr['columnNameOfCommodity']);
$commodity = trim($attr['commodity']);
$columnNameOfRegion = trim($attr['columnNameOfRegion']);
$region = trim(ucfirst($attr['region']));
$monthColumName = '';
$yearColumnName = '';
$quantityColumnName = '';
if($getTableByDataType=='tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
}else{
$monthColumName= 'month_of_loading';
$yearColumnName='year_of_loading';
$quantityColumnName = 'quantity';
}
if(strtolower($periodFormat)=='monthly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$fromMonth = date('m',strtotime($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$explodeTo = explode(' ', $attr['toDate']);
$toMonth = date('m',strtotime($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
/* $data = DB::table($getTableByDataType)
->whereIn(DB::raw("CONCAT(`".$yearColumnName."`, ' ', `".$monthColumName."`)"),$monthList)
->where($commodityColumn,$commodity)
->where($headerColumn,$headerValue)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$monthColumName,$headerColumn)
->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();*/
$data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
if(isset($attr['headerColumn']) && isset($attr['headerValue'])){
$headerColumn = trim($attr['headerColumn']);
$headerValue = trim($attr['headerValue']);
$data->where($headerColumn,$headerValue);
}
$data = $data->get();
$variable = json_decode(json_encode($data), true);
if(count($variable) > 0){
$arragedArray = array();
foreach ($variable as $key => $value) {
$arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $monthsArray) {
foreach ($monthsArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val)/1000000,2)
);
}
}
return $responceArray;
}else{
return 'false';
}
}
//return $responceArray;
if(strtolower($periodFormat)=='yearly'){
$fromYear =trim($attr['fromDate']);
$toYear = trim($attr['toDate']);
$getYearRange = range($fromYear,$toYear);
/* $data = DB::table($getTableByDataType)
->whereIn($yearColumnName,$getYearRange)
->where($commodityColumn,$commodity)
->where($headerColumn,$headerValue)
->where($columnNameOfRegion,$region)
->groupBy($yearColumnName,$headerColumn)
->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
->orderBy($quantityColumnName,'DESC')
->get();*/
$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
if(isset($attr['headerColumn']) && isset($attr['headerValue'])){
$headerColumn = trim($attr['headerColumn']);
$headerValue = trim($attr['headerValue']);
$data->where($headerColumn,$headerValue);
}
$data = $data->get();
$variable = json_decode(json_encode($data), true);
if(count($variable) > 0){
$arragedArray = array();
foreach ($variable as $key => $value) {
$arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $val) {
$responceArray[] = array(
'x' => $keys,
'y' => number_format(array_sum($val)/1000000,2)
);
}
return $responceArray;
}else{
return 'false';
}
}
if(strtolower($periodFormat)=='quarterly'){
$explodeFrom = explode(' ', $attr['fromDate']);
$explodeTo = explode(' ', $attr['toDate']);
$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
$fromYear = $explodeFrom[1];
$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
$toYear = $explodeTo[1];
$fromDate = $fromYear.'-'.$fromMonth.'-01';
$toDate = $toYear.'-'.$toMonth.'-01';
$monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
$data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
if(isset($attr['headerColumn']) && isset($attr['headerValue'])){
$headerColumn = trim($attr['headerColumn']);
$headerValue = trim($attr['headerValue']);
$data->where($headerColumn,$headerValue);
}
$data = $data->get();
$variable = json_decode(json_encode($data), true);
$arragedArray = array();
foreach ($variable as $key => $value) {
$qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
$arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
}
foreach ($arragedArray as $keys => $yearArray) {
foreach ($yearArray as $key => $val) {
$responceArray[] = array(
'x' => $key.' '.$keys,
'y' => number_format(array_sum($val)/1000000,2)
);
}
}
return $responceArray;
}
}else{
return 'false';
}
}
/***************************/
public function getTabularData($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$memid){
/*===if user select all fild it show only one recourd ===*/
$post = DB::table('recent_activities')->insert([
'memid' => $memid,
'type' => "tabular",
'dataType' => $datatype,
'commodity' => $commodity,
'region' => $region,
'columnNameOfRegion' => $columnNameOfRegion,
'columnNameOfCommodity' => $commodityColumn,
'requiredFor' => $requiredFor,
'f_datetime' => date("Y-m-d H:i:s")
]);
if($commodity != 'ALL' && $region != 'ALL'){
$tmpCommodityColumn = $commodityColumn;
if( $commodityColumn == "commodity_new" )
$tmpCommodityColumn = 'commodity';
$frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity)->pluck('frequency');
// $frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
if(!$frequency){
$frequency = 'monthly';
}
$data = $this->getTabularDataCase1($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$frequency);
}
/*if user select all fild expect region then show all recourd accourdingly different region*/
else if($commodity != 'ALL' && $region == 'ALL') {
$data = $this->getTabularDataCase2($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion);
}
else if($commodity == 'ALL' && $region != 'ALL') {
$data = $this->getTabularDataCase3($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion);
}
return $data;
}
/*Case 1: when user select all requried field, like-comdity-sub comodity,region*/
public function getTabularDataCase1($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$frequency){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->where('requiredFor',$requiredFor)->take(1)->get();
$stats = json_decode(json_encode($stats), true);
$stat_table_name = $stats[0]['tableName'];
/*It used to manage column name of year and column name of month*/
$monthColumName = 'month_of_loading';
$yearColumnName = 'year_of_loading';
$quantityColumnName = 'quantity';
$unitColumnName = 'quantity_unit';
if($stat_table_name == 'tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
$unitColumnName = 'qty_unit';
$frequency = 'yearly';
}
else if($stat_table_name=='tbl_production' || $stat_table_name=='tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
$unitColumnName = 'unit';
}
$get_year = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->orderBy($yearColumnName,'desc')->take(2)->groupBy($yearColumnName)->lists($yearColumnName);
$get_data = array();
if($get_year){
if($frequency == 'yearly'){
if(count($get_year) >= 2){
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
if($tbl_data){
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] ="MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
}
} else {
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if($tbl_data){
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] ="MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
}
}
} else {
$get_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->take(2)->groupBy($monthColumName)->lists($monthColumName);
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[0])->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if(count($get_months) >= 2){
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] ="MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
// DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
} else if(count($get_months) == 1 && count($get_year) >= 2) {
$get_last_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->groupBy($monthColumName)->pluck($monthColumName);
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->where($monthColumName,$get_last_months)->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] ="MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
} else {
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] ="MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
}
}
$get_data['unit'] ="MnT";
//$get_data['unitText'] = 'Unit showing in MT';
return $get_data;
} else {
return $data = array();
}
}
/*Case 1: when user select all requried field, like-comdity-sub comodity,region*/
public function getTabularDataCase2($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->where('requiredFor',$requiredFor)->take(1)->get();
$stats = json_decode(json_encode($stats), true);
$stat_table_name = $stats[0]['tableName'];
$monthColumName = 'month_of_loading';
$yearColumnName = 'year_of_loading';
$quantityColumnName = 'quantity';
$unitColumnName = 'quantity_unit';
if($stat_table_name == 'tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
$unitColumnName = 'qty_unit';
$frequency = 'yearly';
}
else if($stat_table_name=='tbl_production' || $stat_table_name == 'tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
$unitColumnName = 'unit';
}
$tmpCommodityColumn = $commodityColumn;
if( $commodityColumn == "commodity_new" )
$tmpCommodityColumn = 'commodity';
$all_regions = DB::table("tbl_stats_selection_compare")
->where('datatypeName',$datatype)
->where($tmpCommodityColumn,$commodity)
->groupBy('region')
->select('region','col_name_region')
->get();
$all_regions = json_decode(json_encode($all_regions), true);
$dd = array();
foreach ($all_regions as $key => $r) {
$region = $r['region'];
$columnNameOfRegion = $r['col_name_region'];
if($stat_table_name == 'tbl_capacity'){
$frequency = 'yearly';
}
else
{
//$frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
$frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity)->pluck('frequency');
if(!$frequency){
$frequency = 'monthly';
}
}
$get_year = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->orderBy($yearColumnName,'desc')->take(2)->groupBy($yearColumnName)->lists($yearColumnName);
if($get_year){
if($frequency == 'yearly'){
if(count($get_year) >= 2){
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
if($tbl_data){
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
} else {
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if($tbl_data){
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
}
} else {
$get_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->take(2)->groupBy($monthColumName)->lists($monthColumName);
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[0])->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if(count($get_months) >= 2){
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
// DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
} else if(count($get_months) == 1 && count($get_year) >= 2) {
$get_last_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->groupBy($monthColumName)->pluck($monthColumName);
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->where($monthColumName,$get_last_months)->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
} else {
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
}
}
}
return $dd;
}
/*Case 3: when user select all requried field, like-comdity-sub comodity,region*/
public function getTabularDataCase3($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->where('requiredFor',$requiredFor)->take(1)->get();
$stats = json_decode(json_encode($stats), true);
$stat_table_name = $stats[0]['tableName'];
$monthColumName = 'month_of_loading';
$yearColumnName = 'year_of_loading';
$quantityColumnName = 'quantity';
$unitColumnName = 'quantity_unit';
if($stat_table_name == 'tbl_capacity'){
$yearColumnName='capacity_year';
$quantityColumnName='capacity';
$unitColumnName = 'qty_unit';
$frequency = 'yearly';
}
else if($stat_table_name=='tbl_production' || $stat_table_name == 'tbl_consumption'){
$monthColumName= 'month';
$yearColumnName='year';
$quantityColumnName = 'quantity';
$unitColumnName = 'unit';
}
/// echo "b4w:";
$sub_sub_commodity = DB::table("tbl_stats_selection_compare")->where('datatypeName',$datatype)->where("region",$region)->where("subSubCommodity",'!=','')->select('subSubCommodity')->get();
$sub_sub_commodity_ids = DB::table("tbl_stats_selection_compare")->where('datatypeName',$datatype)->where("region",$region)->where("subSubCommodity",'!=','')->lists('id');
$sub_commodity = DB::table("tbl_stats_selection_compare")->where('datatypeName',$datatype)->where("region",$region)->whereNotIn('id',$sub_sub_commodity_ids)->select('subCommodity')->get();
$commodity = array_merge($sub_sub_commodity,$sub_commodity);
$commodities = json_decode(json_encode($commodity), true);
// echo "b4:";
$dd = array();
foreach ($commodities as $key => $r) {
if(isset($r['subSubCommodity'])){
$commodityColumn = 'subSubCommodity';
$commodity = $r['subSubCommodity'];
} else {
$commodityColumn = 'subCommodity';
$commodity = $r['subCommodity'];
}
if($stat_table_name == 'tbl_capacity'){
$frequency = 'yearly';
}
else
{
$tmpCommodityColumn = $commodityColumn;
if( $commodityColumn == "commodity_new" )
$tmpCommodityColumn = 'commodity';
//$frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
$frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity)->pluck('frequency');
if(!$frequency){
$frequency = 'monthly';
}
}
$get_year = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->orderBy($yearColumnName,'desc')->take(2)->groupBy($yearColumnName)->lists($yearColumnName);
if($get_year){
if($frequency == 'yearly'){
if(count($get_year) >= 2){
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
if($tbl_data){
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
} else {
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if($tbl_data){
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = $get_year[0];
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
}
} else {
$get_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->take(2)->groupBy($monthColumName)->lists($monthColumName);
$tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[0])->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
$tbl_data = json_decode(json_encode($tbl_data), true);
$tbl_data = $tbl_data[0];
if(count($get_months) >= 2){
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
// DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
} else if(count($get_months) == 1 && count($get_year) >= 2) {
$get_last_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->groupBy($monthColumName)->pluck($monthColumName);
$get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->where($monthColumName,$get_last_months)->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
if($get_old_quantity == 0){
$get_data['previous_quantity'] = $get_old_quantity;
} else {
$get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
}
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
} else {
$get_old_quantity = 0;
$get_data['id'] = $tbl_data['id'];
$get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
$get_data['previous_quantity'] = $get_old_quantity;
$get_data['region'] = $region;
$get_data['commodity'] = $commodity;
//$get_data['unit'] = $tbl_data[$unitColumnName];
$get_data['unit'] = "MnT";
$get_data['frequency'] = $frequency;
$get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
$get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
$dd[] = $get_data;
}
}
}
}
return $dd;
}
public function getTabularFilter($datatype){
$stats = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$datatype)->take(1)->get();
$stats = json_decode(json_encode($stats), true);
$data = DB::table('tbl_stats_selection_compare')->where('datatypeName',$datatype)->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$communityArray = array();
$tempArray = array();
$tempSubArray = array();
$tempSubSubArray = array();
$regionArray = array();
$tempsubsubArray = array();
$regions = array();
$columnNameOfRegion = '';
$l = 0;
/* echo "";
print_r($data); */
foreach ($data as $key => $value) {
if($columnNameOfRegion==''){
$columnNameOfRegion = $value['col_name_region'];
}
$temps = $value['commodity'].'-'.$value['subCommodity'].'-'.$value['subSubCommodity'];
if(!in_array($temps,$tempsubsubArray)){
if($value['subSubCommodity']!=''){
$communityArray[$value['commodity']][$value['subCommodity']][] = $value['subSubCommodity'];
}else{
$communityArray[$value['commodity']][] = $value['subCommodity'];
}
$tempsubsubArray[] = $temps;
}
$cont = $value['commodity'].'-'.$value['region'];
$conts = $value['subCommodity'].'-'.$value['region'];
$contss = $value['subSubCommodity'].'-'.$value['region'];
if(!in_array($cont, $tempArray)){
if(!in_array($value['region'],$regionArray[$value['commodity']]))
$regionArray[$value['commodity']][] = $value['region'];
$tempArray[] = $cont;
}
if(!in_array($contss,$tempSubSubArray) && $value['subSubCommodity'] != ''){
if(!in_array($value['region'],$regionArray[$value['subSubCommodity']]))
$regionArray[$value['subSubCommodity']][] = $value['region'];
$tempSubSubArray[] = $contss;
}
if(!in_array($conts,$tempSubArray)){
if(!in_array($value['region'],$regionArray[$value['subCommodity']]))
$regionArray[$value['subCommodity']][] = $value['region'];
$tempSubArray[] = $conts;
}
if($l == 0 && $value['region'] != ''){
$regions[] = $value['region'];
$l = 1;
}
if(isset($regions) && !in_array($value['region'],$regions)){
$regions[] = $value['region'];
}
}
}
$regionColname = $columnNameOfRegion;
$dataArray = array(
'commodity' => $communityArray,
'regionColname' => $regionColname,
'commodityRegion' => $regionArray,
'allRegions' => $regions
);
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
$dataArray['startDate'] = $sectionFromDate;
$dataArray['endDate'] = $sectionTodate;
}else{
$dataArray['startDate'] = $GLOBALS['currDate'];
$dataArray['endDate'] = $GLOBALS['currDate'];
}
return $dataArray;
}
public function getMom($latest_quantity,$old_quantity){
$mom = $latest_quantity - $old_quantity;
$mom = number_format((float)$mom, 2, '.', '');
if($old_quantity == 0){
$mom_per = "100";
} else {
$mom_per = (1 - $old_quantity / $latest_quantity) * 100;
}
return $mom.'('.number_format((float)$mom_per, 2, '.', '').'%)';
}
public function checkDatatypeExistTabular($attr,$requiredFor){
$check = 'true';
if(DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$attr)->where('requiredFor',$requiredFor)->count() < 1 || DB::table('tbl_stats_selection_compare')->where('datatypeName',$attr)->count() < 1){
$check="false";
}
return $check;
}
/* Map APi */
public function getComandRegionList($attr){
$datatype = strtolower(trim($attr['dataType']));
if($datatype){
$data = DB::table('tbl_map_left_toolbox')->where('datatypeName',$datatype)->get();
$data = json_decode(json_encode($data), true);
if(count($data) > 0){
$responceArray = array();
$checked = array();
foreach ($data as $key => $value) {
$responceArray[$value['commodity']][] = array(
'col_name_commodity' => $value['col_name_commodity'],
'region' => $value['region'],
'commodityID' => $value['commodityID'],
'regionID' => $value['regionID']
);
if(!in_array($value['region'], $checked)){
if( $value['region'] != '' )
$checked[$value['regionID']] = $value['region'];
}
}
$responceArray['allRegion'] = $checked;
return $responceArray;
}else{
return 'false';
}
}else{
return 'false';
}
}
public function checkSelectionNameexist($attr){
$memid = trim($attr['memid']);
$name = trim($attr['name']);
if(DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->where('name',$name)->count() > 0){
return 'false';
}else{
return 'true';
}
}
public function uploadMapFromBase64($attr){
$image_parts = explode(";base64,", $attr);
$image_type_aux = explode("image/", $image_parts[0]);
$image_type = $image_type_aux[1];
$folderUrl = public_path().'/uploads/map/';//image
$file = base64_decode($image_parts[1]);
$safeName = rand(1000000,9999999).'.'.'png';
if (!File::exists($folderUrl)) {
File::makeDirectory($folderUrl, $mode = 0777, true, true);
}
if(file_put_contents($folderUrl.$safeName, $file)){
return $safeName;
}else{
return 'false';
}
}
public function saveMapSelectionList($attr){
$memid = trim($attr['memid']);
$name = trim($attr['name']);
$dataType = trim($attr['dataType']);
$commodity = trim($attr['commodity']);
$columnNameOfCommodity = trim($attr['columnNameOfCommodity']);
if(isset($attr['image'])){
$imageBase64 = $attr['image'];
$imageName = $this->uploadMapFromBase64($imageBase64);
}else{
$imageName=NULL;
}
$lastId = DB::table('tbl_map_selection_saved_list')->insertGetId([
'memid' => $memid,
'name' => $name,
'datatype' => $dataType,
'commodity' => $commodity,
'image' => $imageName,
'col_name_commodity' => $columnNameOfCommodity,
'region' => isset($attr['region']) ? trim($attr['region']) :NULL,
'added_on'=> date('Y-m-d')
]);
$responceArray = array(
'lastInsertedId' => $lastId,
'message' => 'Record Has been Saved!'
);
return $lastId;
}
public function getSelectionById($attr){
$memid = trim($attr['memid']);
$data = DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->orderBy('id','DESC')->get();
$data = json_decode(json_encode($data), true);
$responceArray = array();
foreach ($data as $key => $value) {
if($value['image']!=NULL){
$responceArray[] = array(
'id' => $value['id'],
'memid' => $value['memid'],
'name' => $value['name'],
'dataType' => $value['datatype'],
'commodity' => $value['commodity'],
'columnNameOfCommodity' => $value['col_name_commodity'],
'image' => url('/uploads/map',$value['image']),
'region' => $value['region'],
'added_on' => $value['added_on']
);
}else{
$responceArray[] = array(
'id' => $value['id'],
'memid' => $value['memid'],
'name' => $value['name'],
'dataType' => $value['datatype'],
'commodity' => $value['commodity'],
'columnNameOfCommodity' => $value['col_name_commodity'],
'image' => $value['image'],
'region' => $value['region'],
'added_on' => $value['added_on']
);
}
}
return $responceArray;
if(count($data) > 0){
return $data;
}else{
return 'false';
}
}
public function removeSelectionById($attr){
$memid = trim($attr['memid']);
$id = trim($attr['selectionId']);
if(DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->where('id',$id)->delete()){
$responceArray = array(
'removedId' => $id,
'message' => 'Selection Has been Removed!'
);
return $id;
}else{
return 'false';
}
}
public function downloadHistoricaldataListTest($attr){
$requiredFor = ucfirst(trim($attr['requiredFor']));
$type = trim($attr['type']);
$explode = explode(',', trim($attr['checkedIdList']));
$fromDates = trim($attr['fromDate']);
$date = str_replace('/', '-', $fromDates);
$fromDate = date("Y-m-d", strtotime($date));
$toDated = trim($attr['toDate']);
$date = str_replace('/', '-', $toDated);
$toDate = date("Y-m-d", strtotime($date));
$section = 'Prices_Historical';
if(strtolower($requiredFor)=='price' || strtolower($requiredFor)=='prices')
$section = 'Prices_Historical';
elseif(strtolower($requiredFor)=='statistic' || strtolower($requiredFor)=='statistics')
$section = 'Statistics_Historical';
$memid = 0;
//update memId if getting in POST request
if(isset($attr['memid']) && $attr['memid']>0){
$memid = trim($attr['memid']);
}
$user = new User($memid, $appType='SM');
$isAccessible = $user->isAccessible($resourceType='Web', $section);
if(!empty(Session::get('sectionFromDate'))){
$sectionFromDate = Session::get('sectionFromDate');
$sectionTodate = Session::get('sectionTodate');
$fromDate = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
$toDate = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
}else{
$fromDate =$attr['fromDate']; // '2019-07-01'; //$GLOBALS['currDate'];
$toDate = $attr['toDate']; //'2019-12-31'; //$GLOBALS['currDate'];
}
$dataFromParent = DB::table('tbl_historical_landing')->where('section',$requiredFor)->whereIn('id',$explode)->get();
if(count($dataFromParent)>0){
$dataFromParent = json_decode(json_encode($dataFromParent), true);
$responceArray = array();
if($section == 'Prices_Historical')
{
foreach ($dataFromParent as $key => $value) {
if($value['tradeType']=='import'){
$section_val = 'global';
$type_cnf_fob = 'cnf';
}
elseif($value['tradeType']=='export'){
$section_val = 'global';
$type_cnf_fob = 'fob';
}
else{
//$section_val = 'domestic';
$section_val = $value['tradeType'];
$type_cnf_fob = '';
}
$item='';
if(strtolower($value['commodity']) == 'iron ore'){
if($value['subCommodity']=='Pellets')
$category=$value['subCommodity'];
else
$category = $value['commodity'];
}
elseif(strtolower($value['commodity']) == 'coal' && $value['subCommodity']!=''){
$item=$value['subCommodity'];
$category = $value['commodity'];
}
elseif(strtolower($value['commodity']) == 'steel' )
$category = $value['subSubCommodity'];
else
$category = $value['subCommodity'];
if($value['country']!='' && $category!=''){
/*if($category=='Billet/Bloom/Ingot')
{
$responceArray[$section_val]['Billet'][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
$responceArray[$section_val]['Ingot'][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
}
else*/
$responceArray[$section_val][$category][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
}
}
$responceArray['from_date']=date('Y-m-d', strtotime($fromDate));
$responceArray['to_date']=date('Y-m-d', strtotime($toDate));
}
else{
foreach ($dataFromParent as $key => $value) {
if(trim($value['subSubCommodity']) != '' ){
$category = $value['subSubCommodity'];
$colType='subSubCommodity';
}else{
$category = $value['subCommodity'];
$colType='subCommodity';
}
$responceArray[ucfirst($value['tradeType'])][$value['country']][] = array('colType'=>$colType, 'colValue'=>$category); ///$responceArray[ucfirst($value['tradeType'])][$value['country']][] = $value['subCommodity'];
}
}
$responceArray['statistics_from_year'] = date('Y', strtotime($fromDate));
$responceArray['statistics_from_month'] = date('m', strtotime($fromDate));
$responceArray['statistics_to_year'] = date('Y', strtotime($toDate));
$responceArray['statistics_to_month'] = date('m', strtotime($toDate));
$responceArray['websiteSrc'] = 'steelmint';
$responceArray['sourceFrom'] = 'newWebsite';
$responceArray['uid'] = $memid;
$_REQUEST = $responceArray;
//print_r($responceArray);
if($section == 'Prices_Historical'){
include("/home/steelmin/public_html/excel_download/xls_products_new.php");
//$file_path= BASEURL . "excel_download/_20200323_050315.zip";
}
else{
include("/home/steelmin/public_html/excel_download/xls_statistics_csv_v2019.php");
}
$resValue['filePath'] = $file_path;
//print_r($_REQUEST);
return $resValue;
//return $responceArray['filePath'] = $file_path;
//return $responceArray['filePath'] = BASEURL . "excel_download/_intraday_20181026_091049.zip";
}else{
return 'No Data Found';
}
}
public function nextParentDetailHandler($attr){
$nextParentDataType = explode(',', $attr);
$nextParentsListDetail = array();
if(count($nextParentDataType) > 0){
foreach ($nextParentDataType as $key => $value) {
$data = DB::table('tbl_stats_datatype_header_list')->where('datatypeName',$value)->select('col_name_region_stats_toolbox','periodFormat')->get();
$data = json_decode(json_encode($data), true);
$nextParentsListDetail[$value] = array(
'colomnNameofRegion' => $data[0]['col_name_region_stats_toolbox'],
'periodFormat' => $data[0]['periodFormat']
);
}
}
return $nextParentsListDetail;
}
}
