//package com.example.demo.service.impl;
//
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.ResultSet;
//import java.sql.SQLException;
//import java.sql.Statement;
//import java.util.ArrayList;
//import java.util.List;
//import java.util.UUID;
//import java.util.stream.Collectors;
//
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.beans.factory.annotation.Value;
//import org.springframework.stereotype.Service;
//
//import com.example.demo.dto.Dateduration;
//import com.example.demo.dto.PublisherChartDate;
//import com.example.demo.dto.WidgetDTOChart;
//import com.example.demo.entity.ManageUser;
//import com.example.demo.entity.Publisher;
//import com.example.demo.entity.Roles;
//import com.example.demo.repository.ManageUserRepo;
//import com.example.demo.repository.PublisherRepo;
//import com.example.demo.repository.RolesRepository;
//
//@Service
//public class WidgetsService {
//	@Autowired
//	private ManageUserRepo manageUserRepo;
//	
//	@Autowired
//	private PublisherRepo publisherRepo;
//	
//	@Autowired
//	private RolesRepository roles;
//	
//	@Value("${clickhouse.db}") private String db;
//	
//	public List<WidgetDTOChart> revenue(PublisherChartDate date) throws ClassNotFoundException, SQLException{
//		ManageUser mu=manageUserRepo.findByUserid(date.getUser_id());
//		Roles re=roles.findById(mu.getRoleid()).orElse(null);
//		Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
//		String dbUrl="jdbc:clickhouse://13.56.216.189:8123/"+db;
//		System.out.println("db url is "+dbUrl);
//		List<WidgetDTOChart> list = new ArrayList<>();
//		Connection conn=DriverManager.getConnection(dbUrl, "default", "QWoitybn@hjfZ");
//		//Connection conn=DriverManager.getConnection("jdbc:clickhouse://13.56.216.189:8123/java", "default", "QWoitybn@hjfZ");
//		Statement stmt=conn.createStatement();
//		
//		String query= " Select\n"
//				+ "date_time,\n"
//				+ "sum(imp_count) As imp,\n"
//				+ "sum(req_count) As req_count,\n"
//				+ "sum(res_count) As res_count,\n"
//				+ "multiIf(sum(won_price)=0,0,sum(imp_count)=0,0,trunc((sum(won_price)/sum(imp_count))*1000,2)) As Cpm,\n"
//				+ "trunc(sum(pub_rev),3) AS pub_rev\n"
//				+ "FROM am_stats_hourly\n";
//				
//	    int meet =0;
//		if(re.getRoletype().toString().equalsIgnoreCase("MASTER_ADMIN")) {
//		System.out.println("masterAdmin method called sucessfully");
//		query   =" Select\n"
//				+ "date_time,\n"
//				+ "sum(imp_count) As imp,\n"
//				+ "sum(req_count) As req_count,\n"
//				+ "sum(res_count) As res_count,\n"
//				+ "multiIf(sum(won_price)=0,0,sum(imp_count)=0,0,trunc((sum(won_price)/sum(imp_count))*1000,2)) As Cpm,\n"
//				+ "trunc(sum(admin_rev),3) AS pub_rev\n"
//				+ "FROM am_stats_hourly\n";
//		Dateduration df = date.getRange();
//		if(date.getRange()!=null) {
//			if(df.getFrom().toString().equals(df.getTo().toString())) { 
//				String Date ="'"+df.getFrom().toString()+"'";
//				System.out.println("to date");
//				query += "where 1 AND (toDate(date_time)="+Date+")"
//					    + "GROUP BY date_time\n"
//		    	        + "ORDER BY date_time";
//			}
//			else {
//			String Date3 ="'"+df.getFrom().toString()+"'";
//			String Date1 ="'"+df.getTo().toString()+"'";
//			System.out.println("fromdate to date");
//		    query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//			      + "GROUP BY date_time\n"
//    	          + "ORDER BY date_time";
//			}
//		}
//		meet=1;
//		System.out.println("Query :"+query);
//		}
//		
//		if(mu.getParentid()!=null) {
//			
//			Publisher pub = publisherRepo.findById(mu.getParentid()).orElse(null);
//			 if(pub.getOwner_id()==null) {
//				System.out.println("masss method called sucessfully");
//				List<Publisher> pub3 = publisherRepo.getPublisherList(mu.getParentid());
//				List<UUID> gp=pub3.stream().map(pv->pv.getPublisher_id()).collect(Collectors.toList());
//
//				Dateduration df = date.getRange();
//				   if(date.getRange()!=null) {
//					   if(df.getFrom().toString().equals(df.getTo().toString())) { 
//						    String Date ="'"+df.getFrom().toString()+"'";
//							System.out.println("to date");
//							query += "where 1 AND (toDate(date_time)="+Date+")"
//									+"AND publisher_id IN("+"'"+gp.toString().replace("[", "").replace("]", "").replace(" ", "").replace(",", "','")+"'"+")"
//								    + "GROUP BY date_time\n"
//					    	        + "ORDER BY date_time";
//						}
//				 else {
//						String Date3 ="'"+df.getFrom().toString()+"'";
//						String Date1 ="'"+df.getTo().toString()+"'";
//						System.out.println("fromdate to date");
//					   query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//							+"AND publisher_id IN("+"'"+gp.toString().replace("[", "").replace("]", "").replace(" ", "").replace(",", "','")+"'"+")"
//						    + "GROUP BY date_time\n"
//			    	        + "ORDER BY date_time";
//					}
//				   }
//			    meet=1;	
//			    System.out.println("my query"+query);
//					
//			    }
//			 else if(pub.getOwner_id()!=null) {
//			System.out.println("childpublisher mefhgchgfhfythod called sucessfully");
//			String ud ="'"+ pub.getPublisher_id().toString()+"'";
//			Dateduration df = date.getRange();
//		    if(date.getRange()!=null) {
//		    	
//		    	if(df.getFrom().toString().equals(df.getTo().toString())) { 
//		    		String Date ="'"+df.getFrom().toString()+"'";
//					System.out.println("to date");
//					query += "where 1 AND (toDate(date_time)="+Date+")"
//							+"AND publisher_id = ("+ud+")"
//						    + "GROUP BY date_time\n"
//			    	        + "ORDER BY date_time";
//				}
//		    	else {
//				String Date3 ="'"+df.getFrom().toString()+"'";
//				String Date1 ="'"+df.getTo().toString()+"'";
//				System.out.println("fromdate to date");
//			    query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//				    + "AND publisher_id = ("+ud+")"
//				    + "GROUP BY date_time\n"
//	    	        + "ORDER BY date_time";
//			}
//		    }
//		    System.out.println("my query"+query);
//			meet=1;		
//			}
//			
//			
//		}
//		System.out.println("hsdg");
//		ResultSet rs = stmt.executeQuery(query);
//		System.out.println("hsdg");
//		if(meet==1) {
//			while(rs.next()) {
//				System.out.println("bn z");
//				WidgetDTOChart tile = new WidgetDTOChart();
//				tile.setCpm(rs.getString("Cpm"));
//				System.out.println("Cpm2 :"+rs.getString("Cpm"));
//				String df = rs.getString("date_time");
//				String fv = df.toString();
//				tile.setDatetime(fv);
//				tile .setAdrequestCpm(rs.getString("req_count"));
//				tile.setAvgBidCpm(rs.getString("res_count"));
//				tile.setRevenue(rs.getString("pub_rev"));
//				list.add(tile);
//			}
//			}
//		return list;
//		
//	}
//	
//	
//	public List<WidgetDTOChart> performance(PublisherChartDate date) throws ClassNotFoundException, SQLException{
//		ManageUser mu=manageUserRepo.findByUserid(date.getUser_id());
//		Roles re=roles.findById(mu.getRoleid()).orElse(null);
//		Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
//		String dbUrl="jdbc:clickhouse://13.56.216.189:8123/"+db;
//		System.out.println("db url is "+dbUrl);
//		List<WidgetDTOChart> list = new ArrayList<>();
//		Connection conn=DriverManager.getConnection(dbUrl, "default", "QWoitybn@hjfZ");
//		//Connection conn=DriverManager.getConnection("jdbc:clickhouse://13.56.216.189:8123/java", "default", "QWoitybn@hjfZ");
//		Statement stmt=conn.createStatement();
//		
//		String query= " Select\n"
//				+ "date_time,\n"
//				+ "sum(imp_count) As imp,\n"
//				+ "sum(req_count) As req_count,\n"
//				+ "sum(res_count) As res_count,\n"
//				+ "multiIf(sum(won_price)=0,0,sum(imp_count)=0,0,trunc((sum(won_price)/sum(imp_count))*1000,2)) As Cpm,\n"
//				+ "trunc(sum(pub_rev),3) AS pub_rev\n"
//				+ "FROM am_stats_hourly\n";
//				
//	    int meet =0;
//		if(re.getRoletype().toString().equalsIgnoreCase("MASTER_ADMIN")) {
//		System.out.println("masterAdmin method called sucessfully");
//		query   =" Select\n"
//				+ "date_time,\n"
//				+ "sum(imp_count) As imp,\n"
//				+ "sum(req_count) As req_count,\n"
//				+ "sum(res_count) As res_count,\n"
//				+ "multiIf(sum(won_price)=0,0,sum(imp_count)=0,0,trunc((sum(won_price)/sum(imp_count))*1000,2)) As Cpm,\n"
//				+ "trunc(sum(admin_rev),3) AS pub_rev\n"
//				+ "FROM am_stats_hourly\n";
//		Dateduration df = date.getRange();
//		if(date.getRange()!=null) {
//			if(df.getFrom().toString().equals(df.getTo().toString())) { 
//				String Date ="'"+df.getFrom().toString()+"'";
//				System.out.println("to date");
//				query += "where 1 AND (toDate(date_time)="+Date+")"
//					    + "GROUP BY date_time\n"
//		    	        + "ORDER BY date_time";
//			}
//			else {
//			String Date3 ="'"+df.getFrom().toString()+"'";
//			String Date1 ="'"+df.getTo().toString()+"'";
//			System.out.println("fromdate to date");
//		    query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//			      + "GROUP BY date_time\n"
//    	          + "ORDER BY date_time";
//			}
//		}
//		meet=1;
//		System.out.println("Query :"+query);
//		}
//		
//		if(mu.getParentid()!=null) {
//			
//			Publisher pub = publisherRepo.findById(mu.getParentid()).orElse(null);
//			 if(pub.getOwner_id()==null) {
//				System.out.println("masss method called sucessfully");
//				List<Publisher> pub3 = publisherRepo.getPublisherList(mu.getParentid());
//				List<UUID> gp=pub3.stream().map(pv->pv.getPublisher_id()).collect(Collectors.toList());
//				Dateduration df = date.getRange();
//				   if(date.getRange()!=null) {
//					   if(df.getFrom().toString().equals(df.getTo().toString())) { 
//						    String Date ="'"+df.getFrom().toString()+"'";
//							System.out.println("to date");
//							query += "where 1 AND (toDate(date_time)="+Date+")"
//									+"AND publisher_id IN("+"'"+gp.toString().replace("[", "").replace("]", "").replace(" ", "").replace(",", "','")+"'"+")"
//								    + "GROUP BY date_time\n"
//					    	        + "ORDER BY date_time";
//						}
//				 else {
//						String Date3 ="'"+df.getFrom().toString()+"'";
//						String Date1 ="'"+df.getTo().toString()+"'";
//						System.out.println("fromdate to date");
//					   query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//							+"AND publisher_id IN("+"'"+gp.toString().replace("[", "").replace("]", "").replace(" ", "").replace(",", "','")+"'"+")"
//						    + "GROUP BY date_time\n"
//			    	        + "ORDER BY date_time";
//					}
//				   }
//			    meet=1;	
//			    System.out.println("my query"+query);
//					
//			    }
//			 else if(pub.getOwner_id()!=null) {
//			System.out.println("childpublisher mefhgchgfhfythod called sucessfully");
//			String ud ="'"+ pub.getPublisher_id().toString()+"'";
//			Dateduration df = date.getRange();
//		    if(date.getRange()!=null) {
//		    	
//		    	if(df.getFrom().toString().equals(df.getTo().toString())) { 
//		    		String Date ="'"+df.getFrom().toString()+"'";
//					System.out.println("to date");
//					query += "where 1 AND (toDate(date_time)="+Date+")"
//							+"AND publisher_id = ("+ud+")"
//						    + "GROUP BY date_time\n"
//			    	        + "ORDER BY date_time";
//				}
//		    	else {
//				String Date3 ="'"+df.getFrom().toString()+"'";
//				String Date1 ="'"+df.getTo().toString()+"'";
//				System.out.println("fromdate to date");
//			    query += "where date_time BETWEEN "+Date3+" AND toDate("+Date1+")+1\n"
//				    + "AND publisher_id = ("+ud+")"
//				    + "GROUP BY date_time\n"
//	    	        + "ORDER BY date_time";
//			}
//		    }
//		    System.out.println("my query"+query);
//			meet=1;		
//			}
//			
//			
//		}
//		System.out.println("hsdg");
//		ResultSet rs = stmt.executeQuery(query);
//		System.out.println("hsdg");
//		if(meet==1) {
//			while(rs.next()) {
//				System.out.println("bn z");
//				WidgetDTOChart tile = new WidgetDTOChart();
//				tile.setCpm(rs.getString("Cpm"));
//				System.out.println("Cpm2 :"+rs.getString("Cpm"));
//				String df = rs.getString("date_time");
//				String fv = df.toString();
//				tile.setDatetime(fv);
//				tile .setAdrequestCpm(rs.getString("req_count"));
//				tile.setAvgBidCpm(rs.getString("res_count"));
//				tile.setRevenue(rs.getString("pub_rev"));
//				list.add(tile);
//			}
//			}
//		return list;
//		
//	}
//	
//	
//}