package Reports;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class ProcessedDigSales {

	private Connection conn = null;
	

	public ProcessedDigSales(Connection connection) throws Exception{
		DriverManager.registerDriver (new com.mysql.jdbc.Driver());
		conn = connection;
	}
	
	
	public Map<String, Object> getRecordsPerThread(Map<String, Object> whereClauseParams, int num_of_threads){
        ResultSet     rs   = null;
        Statement  stmt = null;
        Long recordsPerThread;
        Map<String, Object> parameters = new HashMap<String, Object>();
        String period_id = (String)whereClauseParams.get("period_id");
        try
        {    
        	  System.out.println("Getting min & max id....");
              stmt = conn.createStatement (java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
              //String sql = "SELECT (SELECT MIN(statement_detail_id) FROM dig_sales_detail WHERE statement_id = (SELECT MIN(statement_id) FROM dig_sales WHERE period_id = "+period_id+")) AS min_id,(SELECT MAX(statement_detail_id) FROM dig_sales_detail WHERE statement_id = (SELECT MAX(statement_id) FROM dig_sales WHERE period_id = "+period_id+")) AS max_id";
              String sql = "SELECT MIN(statement_detail_id) as min_id, MAX(statement_detail_id) as max_id FROM processed_dig_sales WHERE period_id = "+period_id;
              rs = stmt.executeQuery (sql);
               
              if (rs.next()){
            	  	parameters.put("min_id", rs.getLong("min_id"));
             	 	parameters.put("max_id", rs.getLong("max_id"));
                	parameters.put("recordsPerThread",(rs.getLong("max_id") - rs.getLong("min_id")) / num_of_threads);
              }
              rs.close();
              stmt.close();
              if (conn != null)
                  conn.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            parameters.put("recordsPerThread", new Long(-1));
        }
        return parameters;
    }
   
    public static String getSQL(Map<String, Object> whereClauseParams, long min, long max){
    	String sql = "SELECT pd.period_id, " + 
          		 "     pd.upc, " + 
          		 "     IF(pd.trans_type IN ('DA','UA','VR'), 0, pd.cd) AS cd, " + 
          		 "     IF(pd.trans_type IN ('DA','UA','VR'), 0, pd.track_id) AS track_id, " + 
          		 "     CASE " + 
          		 "       WHEN pd.trans_type IN ('DA','UA','VR') OR ((pd.trans_type IS NULL OR pd.trans_type = '') AND (dd.isrc = '' OR dd.isrc IS NULL)) THEN CONCAT(pd.upc, '_ISRC') " + 
          		 "       WHEN dd.isrc = ''  OR dd.isrc IS NULL THEN CONCAT_WS('_',pd.upc, pd.cd, pd.track_id) " + 
          		 "       ELSE UPPER(dd.isrc) " + 
          		 "     END AS isrc,   " + 
          		 "     CASE WHEN pd.trans_type IS NULL OR pd.trans_type = '' THEN 'N/A' ELSE UPPER(pd.trans_type) END AS trans_type, " + 
          		 "     LPAD(EXTRACT(MONTH FROM pd.date),2,0) AS activityMonth, " + 
          		 "     EXTRACT(YEAR FROM pd.date) AS activityYear, " + 
          		 "     c.abbrivation AS country_code, " + 
          		 "     NULL AS vendor_catalog_number, " + 
          		 "     NULL AS imprint, " + 
          		 "     dd.track_name, " + 
          		 "     pd.statement_detail_id, " + 
          		 "     NULL AS artistId, " + 
          		 "     pd.period_id AS accountingPeriodId, " + 
          		 "     NULL AS activityPeriodId, " + 
          		 "     NULL AS labelId, " + 
          		 "     cm.customer_master_master_id AS storeId,  " + 
          		 "     NULL AS genreId,  " + 
          		 "     pd.upc AS releaseId,  " + 
          		 "     NULL AS trackId,  " + 
          		 "     NULL AS countryId,  " + 
          		 "     NULL AS transactionTypeId,  " + 
          		 "     NULL AS catalogId,  " + 
          		 "     NULL AS isrcId,  " + 
          		 "     NULL AS imprintId, " + 
          		 "     pd.gross, " + 
          		 "     pd.qty as sales, " + 
          		 "     pd.actual_net, " + 
          		 "     pd.adjusted_gross, " + 
          		 "     pd.distribution_fees, " + 
          		 "     pd.dpd_publishing, " + 
          		 "     pd.net_receipt, " + 
          		 "     pd.oms_fees, " + 
          		 "     pd.partner_share, " + 
          		 "     pd.ringtone_publishing,              " + 
          		 "     dd.retail_price, " + 
          		 "     NULL AS subaccountId, " + 
          		 "     (SELECT ISO_4217_code FROM currencies WHERE id = ds.original_currency_id) AS original_currency_code, " + 
          		 "     pd.fx_spread_fee, " + 
          		 "     pd.activity_rate AS activity_fx_rate, " + 
          		 "     (SELECT ISO_4217_code FROM currencies WHERE id = pd.payout_currency_id) AS payout_currency_code, " + 
          		 "     pd.fx_adjusted_exchange_rate, " +
          		 "     pd.gross * pd.fx_adjusted_exchange_rate AS fx_gross, " + 
          		 "     pd.actual_net * pd.fx_adjusted_exchange_rate AS fx_actual_net, " +
          		 "     pd.adjusted_gross * pd.fx_adjusted_exchange_rate AS fx_adjusted_gross, " +
          		 "     pd.distribution_fees * pd.fx_adjusted_exchange_rate AS fx_distribution_fees, " + 
          		 "     pd.dpd_publishing * pd.fx_adjusted_exchange_rate AS fx_dpd_publishing, " + 
          		 "     pd.net_receipt * pd.fx_adjusted_exchange_rate AS fx_net_receipt, " + 
          		 "     pd.oms_fees * pd.fx_adjusted_exchange_rate AS fx_oms_fees, " + 
          		 "     pd.ringtone_publishing * pd.fx_adjusted_exchange_rate AS fx_ringtone_publishing,     " +
          		 "     NULL AS payout_currency_id,     " +
          		 "     NULL AS original_currency_id     " +
          		 "     FROM processed_dig_sales pd " + 
          		 "     INNER JOIN customer_master cm ON pd.dms_customer_id = cm.customer_id " + 
          		 "     INNER JOIN country c ON cm.territory = c.id " + 
          		 "     INNER JOIN dig_sales_detail dd ON dd.statement_detail_id = pd.statement_detail_id " + 
          		 "     INNER JOIN dig_sales ds ON ds.statement_id = dd.statement_id  ";
       	
       	sql += " where pd.period_id = "+(String)whereClauseParams.get("period_id")+" and pd.statement_detail_id between "+min+" and "+max;
       	
       	return sql;
    }
    
    public String getMaxAccountingPeriodId(){
        ResultSet     rs   = null;
        Statement  stmt = null;
        try
        {    
        	  System.out.println("Getting max period id....");
              stmt = conn.createStatement (java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
              rs = stmt.executeQuery ("select max(period_id) as period_id from processed_dig_sales");
               
              if (rs.next()){
            	 return rs.getString("period_id");
              }
              rs.close();
              stmt.close();
              if (conn != null)
                  conn.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        
        return "";
    }
}