<?php

namespace App\Models\Shopify;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class Spysummaryrefund extends Model
{
    protected $connection = 'shopify';
    protected $dates = [''];
    protected $table = 'spy_summary_refund';
    protected $rules = array();
    protected $injectUniqueIdentifier = true;

    public static function fetchSpyRefundSingleData($column , $value,$seller_id,$module)
    {
        $SpycredentialObj = Spysummaryrefund::with(['refunds'])->where($column,$value)->where('seller_id',$seller_id)->where('module',$module)->first();

        return $SpycredentialObj;
    }
    public static function fetchSpyRefundData($column , $value,$seller_id)
    {
        $SpycredentialObj = Spysummaryrefund::where($column,$value)->where('seller_id',$seller_id)->get();

        return $SpycredentialObj;
    }
    public static function fetchRefundCountByOrderId($seller_id,$module,$trasid){
       $spyOrder = DB::select(DB::Raw("SELECT * FROM `spy_summary_refund` WHERE `seller_id` = '".$seller_id."' and `module` = '".$module."' and refund_transaction_id in (".implode(',',$trasid).")"));  
       $spyOrder = json_decode(json_encode($spyOrder),true);
       return $spyOrder;
    }
    public static function fetchRefundCountBygateway($seller_id,$date){
       $spyOrder = DB::select(DB::Raw("SELECT gateway,DATE_FORMAT(processed_at, '%Y-%m-%d') as payout_date,SUM(`total_refund`) as total_refund,SUM(`total_refund_tax`) as total_refund_tax,SUM(`total_refund_shipping`) as total_refund_shipping,SUM(`total_refund_shipping_tax`) as total_refund_shipping_tax,SUM(`total_refund_discrepancy`) as total_refund_discrepancy,SUM(`total_refund_discrepancy_tax`) as total_refund_discrepancy_tax,SUM(`total_refund_giftcard`) as total_refund_giftcard FROM `spy_summary_refund` WHERE `seller_id` = '".$seller_id."'  and (gateway!='shopify_payments' OR gateway IS NULL) and processed_at > '".$date."' and processed_at < '".date('Y-m-d')."' group by gateway,DATE_FORMAT(processed_at, '%Y-%m-%d') order by processed_at asc "));  
       $spyOrder = json_decode(json_encode($spyOrder),true);
       return $spyOrder;
    }
     public static function fetchRefundOthergateway($seller_id,$module,$date,$reportenddate,$report_type="",$reCalcParameters){
      $transactionId=(isset($reCalcParameters['transactionId']))?$reCalcParameters['transactionId']:"";
      $reportType=(isset($reCalcParameters['reportType']))?$reCalcParameters['reportType']:"";
      $transaction_type=(isset($reCalcParameters['transaction_type']))?$reCalcParameters['transaction_type']:"";
      $gateway=(isset($reCalcParameters['gateway']))?$reCalcParameters['gateway']:"";
   
      $transactionId =$gatewayCnd= "";
      if ($reportType == "recalculateReport" && $transaction_type == "Individual") {
          $transactionId = "and refund_transaction_id='" . $transactionId . "' ";
      } else if ($reportType == "recalculateReport" && $transaction_type == "Summary") {
          $gateways = config('app.gatewayKey');

          if ((isset($gateways[$gateway])) && is_array($gateways[$gateway])) {
              $commaDoubleQuoteList = '"' . implode('", "', $gateways[$gateway]) . '"';
              $gatewayCnd = "and spy_summary_refund.gateway in (" . $commaDoubleQuoteList . ") ";
          } else if (!empty($gateway)) {
              $gatewayCnd = "and spy_summary_refund.gateway='" . $gateway . "' ";
          } else {
              $gatewayCnd = "and (spy_summary_refund.gateway IS NULL OR spy_summary_refund.gateway ='')";
          }
      } else {
          $gatewayCnd = "and ((spy_summary_refund.gateway != 'shopify_payments' and spy_summary_refund.gateway != 'shopify_installments' and spy_summary_refund.gateway != 'shop_cash') OR spy_summary_refund.gateway IS NULL OR spy_summary_refund.gateway ='')";
      }
      $cDate=($report_type=="monthly")?date('Y-m'):date('Y-m-d');

      $spyOrder = DB::select(DB::Raw("SELECT spy_summary_refund.* FROM `spy_summary_refund`  join  spy_summary_order on `spy_summary_refund`.order_id = `spy_summary_order`.`order_id`  WHERE `spy_summary_refund`.seller_id = '".$seller_id."' and `spy_summary_refund`.module = '".$module."'  " . $gatewayCnd . " and spy_summary_refund.processed_at > '".$date."' and spy_summary_refund.processed_at < '".$reportenddate."' and spy_summary_refund.processed_at <'".$cDate."'  ".$transactionId." and spy_summary_order.sync_flg !='D' order by spy_summary_refund.processed_at asc "));  
      $spyOrder = json_decode(json_encode($spyOrder),true);
       return $spyOrder;
    }
      
     public static function fetchCurrencyConversionByRefundIds($seller_id,$module,$refund_ids){
        $spyOrder = DB::select(DB::Raw("SELECT concat(`spy_summary_refund`.`currency`,'-',`presentment_currency`) as currency,sum(`total_refund_amount`) - sum(ABS(`amount`)) as amount FROM `spy_summary_refund` join  spy_summary_payout_transaction on `spy_summary_refund`.refund_transaction_id = `spy_summary_payout_transaction`.`order_transaction_id` and `spy_summary_refund`.seller_id = `spy_summary_payout_transaction`.`seller_id` and   type = 'refund' WHERE `spy_summary_refund`.`seller_id` = '".$seller_id."' and `spy_summary_refund`.`module` = '".$module."' and `spy_summary_refund`.refund_transaction_id in (".implode(',',$refund_ids).") And presentment_currency != spy_summary_refund.currency group by presentment_currency having sum(total_refund_amount) - sum(ABS(amount)) != 0 "));  
       $spyOrder = json_decode(json_encode($spyOrder),true);
       return $spyOrder;
    }
    public function refunds()
    { 
        return $this->hasMany(Spyrefundlineitems::class, 'refund_id', 'id');
    }
}
