<?php
  

include_once("connect3.php");

    mysqli_query($conexao,"SET NAMES 'utf8'");
    mysqli_query($conexao,'SET character_set_connection=utf8');
    mysqli_query($conexao,'SET character_set_client=utf8');
    mysqli_query($conexao,'SET character_set_results=utf8');


//Funcao para exibir o json
function exibir_json($json){
    $echo_json = json_encode($json);
    $json = array_map('utf8_encode', $json);
  echo $echo_json;
}

//Verificando se a mensagem foi transmitida por POST
if(strcasecmp($_SERVER['REQUEST_METHOD'], 'POST') != 0){  
    $status = array(
        'erro' => 'true',
        'mensagem' => 'O formato recebido não é um POST');
    
    //$status = array_map('utf8_encode', $status);
    //$json = json_encode(array_map('utf8_encode', $status));
    
  exibir_json($status);
    exit(0);
}



  //Comentado Fabio
  $json_fabio = $_POST['dados'];  
  $vendas = json_decode($json_fabio, true);  



//Atribuicao de variaveis e queries padroes
//Variaveis que virão no JSON, mas que para debug precisam ser carregadas
$v_locale             = 'pt';  
$v_address_id         = 11058;
$v_zone_id            = 1692;
$v_comment            = '';
$v_cupom              = ''; //TODO Checar se o cupom eh valido
$v_pontos             = 0;  //Os pontos só podem ser utilizados em produtos, não em frete. Portanto o valor de pontos não deve ser superior a soma dos produtos
$v_custom_field       = '\"\"';
$v_id_forma_entrega   = '11';  //FORMA DE ENVIO: Pagamento na Entrega do Pedido
$v_data_entrega       = '08/09/2020';
$v_hora_entrega       = '16:00 - 18:00';
$v_payment            = '';
$v_company            = '';
$v_invoice_prefix     = 'MOB-2020-21';
$v_store_id           = '0';
$v_store_name         = 'Saúde e Sabor';
$v_store_url          = 'https://jp.saudeesabor.co.jp/';
$customer_group_id    = "(select customer_group_id 
                            from oc_customer_group_description
                           where description = 'Mobile'
                             and language_id = (select language_id 
                                                  from oc_language 
                                                 where locale like concat('%',$v_locale,'%'))";
$v_forwarded_ip       = '';
$v_user_agent         = 'Mobile';
$v_accept_language    = $v_locale;
$v_date_added         = 'now()';
$v_date_modified      = 'now()';
$v_fax                = '';
$v_language_id        = "(select language_id 
                            from oc_language 
                           where locale like concat('%','$v_locale','%'))";
$v_address_format     = '';
// $v_deldttime_dateslot = '05/08/2020';
// $v_deldttime_timeslot = '16:00 - 18:00';


//Campos obrigatórios desnecessários
$v_affiliate_id       = 0;
$v_commission         = 0;
$v_marketing_id       = 0;
$v_tracking           = '';      



  foreach($vendas as $campo=>$valor){


    if ($campo == 'cabecalho'){
        foreach($valor as $cCampo => $cValor){
           //echo $cCampo . "=>" . $cValor . "<br>";

           //fazer atribuição de valores


          //Variaveis que virao no CABEC do JSON


          /*
           if ($cCampo == 'invoice_prefix')             { $v_invoice_prefix           = 'MOB-2020-20';};
           if ($cCampo == 'store_id')                   { $v_store_id                 = '0';};
           if ($cCampo == 'store_name')                 { $v_store_name               = 'Saúde e Sabor';};
           if ($cCampo == 'store_url')                  { $v_store_url                = 'https://jp.saudeesabor.co.jp/';};
           if ($cCampo == 'customer_id')                { $v_customer_id              = $cValor;};
           if ($cCampo == 'customer_group_id')          { $v_customer_group_id        = "(select customer_group_id from oc_customer_group_description
                                                                                           where description = 'Mobile'
                                                                                             and language_id = (select language_id 
                                                                                                                  from oc_language 
                                                                                                                 where locale like concat('%',$v_locale,'%'))
                                                                                          )";};
          */
           if ($cCampo == 'id_forma_entrega')           { $v_id_forma_entrega         = $cValor;}; 
           if ($cCampo == 'customer_id')                { $v_customer_id              = $cValor;}; 
           //if ($cCampo == 'firstname')                  { $v_firstname                = $cValor;};
           //if ($cCampo == 'lastname')                   { $v_lastname                 = $cValor;};
           //if ($cCampo == 'email')                      { $v_email                    = $cValor;};
           //if ($cCampo == 'telephone')                  { $v_telephone                = $cValor;};
           if ($cCampo == 'address_id')                 { $v_address_id               = $cValor;};
           if ($cCampo == 'zone_id')                    { $v_zone_id                  = $cValor;};
           if ($cCampo == 'observacao')                 { $v_comment                  = $cValor;};
           if ($cCampo == 'total')                      { $v_total                    = $cValor;};
           if ($cCampo == 'data_entrega')               { $v_data_entrega             = $cValor;};           
           if ($cCampo == 'hora_entrega')               { $v_hora_entrega             = $cValor;};           
           if ($cCampo == 'ip')                         { $v_ip                       = $cValor;};    


           //adicionar
           if ($cCampo == 'locale')                     { $v_locale                   = $cValor;};               
           if ($cCampo == 'cupom')                      { $v_cupom                    = $cValor;};               
           if ($cCampo == 'pontos')                     { $v_pontos                   = $cValor;};                          

           /*
           if ($cCampo == 'custom_field')               { $v_custom_field             = '\"\"';};
           if ($cCampo == 'payment_firstname')          { $v_payment_firstname        = $v_firstname;};
           if ($cCampo == 'payment_lastname')           { $v_payment_lastname         = $v_lastname;};
           if ($cCampo == 'payment_company')            { $v_payment_company          = '';};
           if ($cCampo == 'payment_address_1')          { $v_payment_address_1        = $v_address_1;};
           if ($cCampo == 'payment_address_2')          { $v_payment_address_2        = $v_address_2;};
           if ($cCampo == 'payment_city')               { $v_payment_city             = $v_city;};
           if ($cCampo == 'payment_postcode')           { $v_payment_postcode         = $v_postcode;};
           if ($cCampo == 'payment_country')            { $v_payment_country          = $v_country;};
           if ($cCampo == 'payment_country_id')         { $v_payment_country_id       = $v_country_id;};
           if ($cCampo == 'payment_zone')               { $v_payment_zone             = $v_zone;};
           if ($cCampo == 'payment_zone_id')            { $v_payment_zone_id          = $v_zone_id;};
           if ($cCampo == 'payment_address_format')     { $v_payment_address_format   = '';};

           
           if ($cCampo == 'payment_custom_field')       { $v_payment_custom_field     = '';};
           if ($cCampo == 'payment_method')             { $v_payment_method           = '';};
           if ($cCampo == 'payment_code')               { $v_payment_code             = $cValor;};
           if ($cCampo == 'shipping_firstname')         { $v_shipping_firstname       = $cValor;};
           if ($cCampo == 'shipping_lastname')          { $v_shipping_lastname        = $cValor;};
           if ($cCampo == 'shipping_company')           { $v_shipping_company         = $cValor;};
           if ($cCampo == 'shipping_address_1')         { $v_shipping_address_1       = $cValor;};
           if ($cCampo == 'shipping_address_2')         { $v_shipping_address_2       = $cValor;};
           if ($cCampo == 'shipping_city')              { $v_shipping_city            = $cValor;};
           if ($cCampo == 'shipping_postcode')          { $v_shipping_postcode        = $cValor;};
           if ($cCampo == 'shipping_country')           { $v_shipping_country         = $cValor;};
           if ($cCampo == 'shipping_country_id')        { $v_shipping_country_id      = $cValor;};
           if ($cCampo == 'shipping_zone')              { $v_shipping_zone            = $cValor;};
           if ($cCampo == 'shipping_zone_id')           { $v_shipping_zone_id         = $cValor;};
           if ($cCampo == 'shipping_address_format')    { $v_shipping_address_format  = $cValor;};
           if ($cCampo == 'shipping_custom_field')      { $v_shipping_custom_field    = $cValor;};
           if ($cCampo == 'shipping_method')            { $v_shipping_method          = $cValor;};
           if ($cCampo == 'shipping_code')              { $v_shipping_code            = $cValor;};
           

           if ($cCampo == 'observacao')                 { $v_comment                  = $cValor;};
           if ($cCampo == 'total')                      { $v_total                    = $cValor;};
           
           
           if ($cCampo == 'affiliate_id')               { $v_affiliate_id             = $cValor;};
           if ($cCampo == 'commission')                 { $v_commission               = $cValor;};
           if ($cCampo == 'marketing_id')               { $v_marketing_id             = $cValor;};


           if ($cCampo == 'tracking')                   { $v_tracking                 = $cValor;};
           if ($cCampo == 'language_id')                { $v_language_id              = $cValor;};
           if ($cCampo == 'currency_id')                { $v_currency_id              = $cValor;};
           if ($cCampo == 'currency_code')              { $v_currency_code            = $cValor;};
           if ($cCampo == 'currency_value')             { $v_currency_value           = $cValor;};
           if ($cCampo == 'ip')                         { $v_ip                       = $cValor;};
           if ($cCampo == 'forwarded_ip')               { $v_forwarded_ip             = $cValor;};
           if ($cCampo == 'user_agent')                 { $v_user_agent               = $cValor;};
           if ($cCampo == 'accept_language')            { $v_accept_language          = $cValor;};
           if ($cCampo == 'date_added')                 { $v_date_added               = $cValor;};
           if ($cCampo == 'date_modified')              { $v_date_modified            = $cValor;};
           if ($cCampo == 'fax')                        { $v_fax                      = $cValor;};
           if ($cCampo == 'deldttime_timeslot')         { $v_deldttime_timeslot       = $cValor;};
          */


//FIM VARIAVEIS QUE VIRAO NO CABEC DO JSON



           //chegar campos obrigatorios e argumentar erro (se ncessario)


           //REGISTRAR O CABEC DA VENDA

        }

          //aqui tenho todas as variaveis carregadas

          //echo "CUSTOMER_ID => " . $v_customer_id;
          //echo "FIRSTNAME   => " . $v_firstname; 


          //primeiro obter os dados de endereco para usar na query de cabec
          // obter dados do endereco para evitar repeticao de custo de subquery
          //execitar e atribior valor as variaveis




          //se estiver informada executar comando abaixo


//OBTENDO VARIAVEIS DO cliente BASEADO NO customer_id (assim a msg fica menor)
$sql_cliente = "select * from oc_customer
                  where customer_id = $v_customer_id";

          $result = mysqli_query($conexao, $sql_cliente) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                   $v_firstname                = $row['firstname'];
                   $v_lastname                 = $row['lastname'];
                   $v_email                    = $row['email'];
                   $v_telephone                = $row['telephone'];            
                }
              }   



//OBTENDO VARIAVEIS DO ENDERECO BASEADO NO ENDERECO ENVIADO NO JSON
$sql_endereco = "select oc_address.*, 
                        oc_country.name as country
                   from oc_address inner join oc_country 
                     on oc_address.country_id = oc_country.country_id 
                  where address_id = $v_address_id";

          $result = mysqli_query($conexao, $sql_endereco) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_address_id         = $row['address_id'];
                    //$v_customer_id        = $row['customer_id'];
                    //$v_firstname          = $row['firstname'];   
                    //$v_lastname           = $row['lastname'];
                    //$v_company            = $row['company'];
                    $v_address_1          = $row['address_1'];
                    $v_address_2          = $row['address_2'];
                    $v_city               = $row['city'];
                    $v_postcode           = $row['postcode'];
                    $v_country_id         = $row['country_id'];
                    $v_country            = $row['country'];
                    $v_zone_id            = $row['zone_id'];
                    $v_custom_field       = $row['custom_field'];
            
                }
              }   


//OBTENDO VARIAVEIS DA FORMA DE ENTREGA BASEADO NA FORMA ENVIADA NO JSON
$sql_forma_entrega = "select   concat('SHIPPROG_', sp.id,'_' ,sn.language_id,'.','SHIPPROG_', sp.id,'_' ,sn.language_id) as code
                              ,sn.name as title
                              ,sp.price
                        from shipping_programation sp inner join shipping_programation_name sn 
                          on sp.id = sn.shipping_programation_id
                       where sn.language_id = (select language_id 
                                                 from oc_language 
                                                where locale like concat('%','$v_locale','%'))
                                                  and sp.id = $v_id_forma_entrega";


          $result = mysqli_query($conexao, $sql_forma_entrega) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_forma_code     = $row['code'];
                    $v_forma_entrega  = $row['title'];
                    $v_forma_preco    = $row['price'];               
                }
              }



//OBTENDO VARIAVEIS DA currency 
$sql_currency = "select * from oc_currency where title = 'Yen'";

          $result = mysqli_query($conexao, $sql_currency) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_currency_id       = $row['currency_id'];
                    $v_currency_code     = $row['code'];
                    $v_currency_value    = $row['value'];               
                }
            }


//Obter dados da zona
$sql_zone = "select * from oc_zone_name
              where zone_id = $v_zone_id 
                and language_id = (select language_id 
                                     from oc_language 
                                    where locale like concat('%','$v_locale','%'))";

          $result = mysqli_query($conexao, $sql_zone) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_zone_name         = $row['name'];
                }
            }



//Obter dados do customer (incluindo o customer_id_group)
$sql_customer = "select * from oc_customer
                  where customer_id = '$v_customer_id'";

          $result = mysqli_query($conexao, $sql_customer) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_customer_group_id         = $row['customer_group_id'];
                }
            }




          // VARIAVEIS NAO TRANSMITIDAS PELO JSON
          $v_custom_field             = $v_custom_field;
          $v_payment_firstname        = $v_firstname;
          $v_payment_lastname         = $v_lastname;
          $v_payment_company          = $v_company;
          $v_payment_address_1        = $v_address_1;
          $v_payment_address_2        = $v_address_2;
          $v_payment_city             = $v_city;
          $v_payment_postcode         = $v_postcode;
          $v_payment_country          = $v_country;
          $v_payment_country_id       = $v_country_id;
          $v_payment_zone             = $v_zone_name;
          $v_payment_zone_id          = $v_zone_id;
          $v_payment_address_format   = $v_address_format;

           
          $v_payment_custom_field     = $v_custom_field;
          $v_payment_method           = $v_forma_entrega;
          $v_payment_code             = $v_forma_code;
          $v_shipping_firstname       = $v_firstname;
          $v_shipping_lastname        = $v_lastname;
          $v_shipping_company         = $v_company;
          $v_shipping_address_1       = $v_address_1;
          $v_shipping_address_2       = $v_address_2;
          $v_shipping_city            = $v_city;
          $v_shipping_postcode        = $v_postcode;
          $v_shipping_country         = $v_country;
          $v_shipping_country_id      = $v_country_id;
          $v_shipping_zone            = $v_zone_name;
          $v_shipping_zone_id         = $v_zone_id;
          $v_shipping_address_format  = $v_address_format;
          $v_shipping_custom_field    = $v_custom_field;
          $v_shipping_method          = $v_forma_entrega;
          $v_shipping_code            = $v_forma_code;

           
           
           /* Obter o indicador */
           // $v_affiliate_id             = $cValor;};
           // $v_commission               = $cValor;};
           // $v_marketing_id             = $cValor;};                   


        

//echo de variaveis
echo  "V_ADDRESS_ID => "    . $v_address_id    . "<BR>";
echo  "V_CUSTOMER_ID => "   . $v_customer_id   . "<BR>";
echo  "V_FIRSTNAME => "     . $v_firstname     . "<BR>";
echo  "V_LASTNAME => "      . $v_lastname      . "<BR>";
echo  "V_COMPANY => "       . $v_company       . "<BR>";
echo  "V_ADDRESS_1 => "     . $v_address_1     . "<BR>";
echo  "V_ADDRESS_2 => "     . $v_address_2     . "<BR>";
echo  "V_CITY => "          . $v_city          . "<BR>";
echo  "V_POSTCODE => "      . $v_postcode      . "<BR>";
echo  "V_COUNTRY_ID => "    . $v_country_id    . "<BR>";
echo  "V_ZONE_ID => "       . $v_zone_id       . "<BR>";
echo  "V_CUSTOM_FIELD => "  . $v_custom_field  . "<BR>";



          /*

          */


          //montar sql stmt
          $sql_registra_cabec = "insert into oc_order (
                                                         invoice_prefix
                                                        ,store_id
                                                        ,store_name
                                                        ,store_url
                                                        ,customer_id
                                                        ,customer_group_id
                                                        ,firstname
                                                        ,lastname
                                                        ,email
                                                        ,telephone
                                                        ,custom_field
                                                        ,payment_firstname
                                                        ,payment_lastname
                                                        ,payment_company
                                                        ,payment_address_1
                                                        ,payment_address_2
                                                        ,payment_city
                                                        ,payment_postcode
                                                        ,payment_country
                                                        ,payment_country_id
                                                        ,payment_zone
                                                        ,payment_zone_id
                                                        ,payment_address_format
                                                        ,payment_custom_field
                                                        ,payment_method
                                                        ,payment_code
                                                        ,shipping_firstname
                                                        ,shipping_lastname
                                                        ,shipping_company
                                                        ,shipping_address_1
                                                        ,shipping_address_2
                                                        ,shipping_city
                                                        ,shipping_postcode
                                                        ,shipping_country
                                                        ,shipping_country_id
                                                        ,shipping_zone
                                                        ,shipping_zone_id
                                                        ,shipping_address_format
                                                        ,shipping_custom_field
                                                        ,shipping_method
                                                        ,shipping_code
                                                        ,comment
                                                        ,total
                                                        ,language_id
                                                        ,currency_id
                                                        ,currency_code
                                                        ,currency_value
                                                        ,ip
                                                        ,forwarded_ip
                                                        ,user_agent
                                                        ,accept_language
                                                        ,date_added
                                                        ,date_modified
                                                        ,fax
                                                        ,deldttime_dateslot
                                                        ,deldttime_timeslot
                                                        ,affiliate_id
                                                        ,commission
                                                        ,marketing_id
                                                        ,tracking
                                                      ) ";

                      $sql_registra_cabec .= "values (                       
                                                       '$v_invoice_prefix'
                                                      ,'$v_store_id'
                                                      ,'$v_store_name'
                                                      ,'$v_store_url'
                                                      ,'$v_customer_id'
                                                      ,'$v_customer_group_id'
                                                      ,'$v_firstname'
                                                      ,'$v_lastname'
                                                      ,'$v_email'
                                                      ,'$v_telephone'
                                                      ,'$v_custom_field'
                                                      ,'$v_payment_firstname'
                                                      ,'$v_payment_lastname'
                                                      ,'$v_payment_company'
                                                      ,'$v_payment_address_1'
                                                      ,'$v_payment_address_2'
                                                      ,'$v_payment_city'
                                                      ,'$v_payment_postcode'
                                                      ,'$v_payment_country'
                                                      ,'$v_payment_country_id'
                                                      ,'$v_payment_zone'
                                                      ,'$v_payment_zone_id'
                                                      ,'$v_payment_address_format'
                                                      ,'$v_payment_custom_field'
                                                      ,'$v_payment_method'
                                                      ,'$v_payment_code'
                                                      ,'$v_shipping_firstname'
                                                      ,'$v_shipping_lastname'
                                                      ,'$v_shipping_company'
                                                      ,'$v_shipping_address_1'
                                                      ,'$v_shipping_address_2'
                                                      ,'$v_shipping_city'
                                                      ,'$v_shipping_postcode'
                                                      ,'$v_shipping_country'
                                                      ,'$v_shipping_country_id'
                                                      ,'$v_shipping_zone'
                                                      ,'$v_shipping_zone_id'
                                                      ,'$v_shipping_address_format'
                                                      ,'$v_shipping_custom_field'
                                                      ,'$v_shipping_method'
                                                      ,'$v_shipping_code'
                                                      ,'$v_comment'
                                                      ,'$v_total'
                                                      ,$v_language_id
                                                      ,'$v_currency_id'
                                                      ,'$v_currency_code'
                                                      ,'$v_currency_value'
                                                      ,'$v_ip'
                                                      ,'$v_forwarded_ip'
                                                      ,'$v_user_agent'
                                                      ,'$v_accept_language'
                                                      ,$v_date_added
                                                      ,$v_date_modified
                                                      ,'$v_fax'
                                                      ,str_to_date('$v_data_entrega','%d/%m/%Y')
                                                      ,'$v_hora_entrega'
                                                      ,'$v_affiliate_id'
                                                      ,'$v_commission'
                                                      ,'$v_marketing_id'
                                                      ,'$v_tracking'
                                                      ) ";
                                                      


//          echo $sql_registra_cabec;



          // exit;

          //TODO EXECUTAR O INSERT E OBTER O ID MY_ID

//          echo "TESTE => " . $teste;

          mysqli_query($conexao, $sql_registra_cabec) or die(mysqli_error($conexao));
         // mysqli_query($conexao, $sql_teste) or die(mysqli_error($conexao));
          $v_order_id = mysqli_insert_id($conexao);

          echo "MEU ID => " . $v_order_id;
          //$teste++;
   
          // exit;
  
} //adicionado Fabio

    }



    if ($campo == 'itens'){
        foreach($valor as $itens){
      //    echo "Item" . $i++ . "<br>";
          foreach ($itens as $iCampo => $iValor){
          //echo $iCampo . "=>" . $iValor . "<br>";
          //checar campos obrigatorios e argumentar erro (se necessario)         
           // if ($iCampo == 'order_id')     { $v_order_id         = $iValor;}; 
           if ($iCampo == 'product_id')   { $v_product_id       = $iValor;};            
           if ($iCampo == 'model')        { $v_model            = $iValor;}; 
           if ($iCampo == 'quantity')     { $v_quantity         = $iValor;}; 
           if ($iCampo == 'price')        { $v_price            = $iValor;}; 
           if ($iCampo == 'total')        { $v_total            = $iValor;}; 
           if ($iCampo == 'tax')          { $v_tax              = $iValor;}; 
           if ($iCampo == 'reward')       { $v_reward           = $iValor;};      //TODO perguntar para Doughan o que eh a reward em produtos.                                                              

          //REGISTRAR OS ITENS COMPRADOS PELO CLIENTE

          //montar sql stmt
        
          }

          //montar sql

        echo "ORDER_ID => "       . $v_order_id   . "<br>";
        echo "PRODUCT_ID => "     . $v_product_id . "<br>";
        echo "MODEL => "          . $v_model      . "<br>";
        echo "QUANTITY => "       . $v_quantity   . "<br>";
        echo "PRICE => "          . $v_price      . "<br>";
        echo "TOTAL => "          . $v_total      . "<br>";
        echo "TAX => "            . $v_tax        . "<br>";
        echo "REWARD => "         . $v_reward     . "<br>";


        $sql_insert_produtos = "insert into oc_order_product(
                                                               order_id
                                                              ,product_id
                                                              ,name
                                                              ,model
                                                              ,quantity
                                                              ,price
                                                              ,total
                                                              ,tax
                                                              ,reward) 
                                                       values (
                                                              $v_order_id
                                                              ,$v_product_id
                                                              ,(select name 
                                                                  from oc_product_description 
                                                                 where product_id = $v_product_id 
                                                                   and language_id = (select language_id 
                                                                                        from oc_language 
                                                                                       where locale like concat('%','$v_locale','%')))
                                                              ,(select model from oc_product where product_id = $v_product_id) 
                                                              ,$v_quantity
                                                              ,$v_price
                                                              ,$v_total
                                                              ,$v_tax
                                                              ,$v_reward
                                                              )";

           //echo "Query de insert de produtos" . $sql_insert_produtos;
           //exit;

          // executar insert 
          mysqli_query($conexao, $sql_insert_produtos) or die(mysqli_error($conexao));

        }

//    }



   //montar os stmt totais

    /*
    sub_total -> soma de todos os produtos
    */

    $sql_sub_total = "insert into oc_order_total (order_id
                                                 ,code
                                                 ,title
                                                 ,value
                                                 ,sort_order) 
                                        values (
                                         $v_order_id  -- order_id
                                        ,'sub_total'  -- code
                                        ,'Produtos'   -- title
                                        ,(select sum(total) 
                                            from oc_order_product
                                           where order_id = $v_order_id) 
                                        ,(select sort_order from code_pedido_ordem where code = 'sub_total') -- sort_order
                                        );";

    //echo "QUERY_TOTAL " . $sql_sub_total;
    //exit;


    // executando o script.
    mysqli_query($conexao, $sql_sub_total) or die(mysqli_error($conexao));

    /*
    SHIPPROG_11_2.SHIPPROG_11_2 -> forma de envio
    */


    $sql_forma_envio = "insert into oc_order_total (order_id
                                                   ,code
                                                   ,title
                                                   ,value
                                                   ,sort_order) 
                                            values ($v_order_id      -- order_id
                                                  ,(select code 
                                                      from vw_code_shipprog  
                                                     where language_id = (select language_id 
                                                                            from oc_language 
                                                                           where locale like concat('%','$v_locale','%')) -- LOCALE
                                                 and id = $v_id_forma_entrega -- ID da Programacao de ENTREGA
                                                   )    -- code
                                                  ,(select title 
                                                      from vw_code_shipprog
                                                     where language_id = (select language_id 
                                                                            from oc_language 
                                                                           where locale like concat('%','$v_locale','%')) -- LOCALE
                                                    and id = $v_id_forma_entrega -- ID da Programacao de ENTREGA
                                                   )    -- title
                                                  ,(select price 
                                                      from vw_code_shipprog
                                                     where language_id = (select language_id 
                                                                            from oc_language 
                                                                           where locale like concat('%','$v_locale','%')) -- LOCALE
                                                       and id = $v_id_forma_entrega -- ID da Programacao de ENTREGA
                                                   )    -- value
                                            ,(select sort_order from code_pedido_ordem where code = 'shipping') -- sort_order
                                            );";

  
     mysqli_query($conexao, $sql_forma_envio) or die(mysqli_error($conexao));


    /*
    sub_total_prod_frete - produtos + forma de envio
    */

    $sql_sub_total_prod_frete = "insert into oc_order_total (order_id
                                                            ,code
                                                            ,title
                                                            ,value
                                                            ,sort_order) 
                                                    values (
                                                     $v_order_id      -- order_id
                                                    ,'sub_total_prod_frete'   -- code
                                                    ,'Total'    -- title
                                                    ,(select sum(total.sub_total_prod_frete) as 'sub_total_prod_frete'
                                                      from (
                                                    select sum(total) as 'sub_total_prod_frete' from oc_order_product
                                                          where order_id = $v_order_id -- order_id
                                                      
                                                     union
                                                     
                                                    select  sp.price as 'sub_total_prod_frete'
                                                       from shipping_programation sp inner join shipping_programation_name sn 
                                                       on sp.id = sn.shipping_programation_id
                                                      where sn.language_id = (select language_id 
                                                                   from oc_language 
                                                                  where locale like concat('%','pt','%')) -- LOCALE
                                                       and sp.id = $v_id_forma_entrega) as total
                                                    ) 
                                                    ,(select sort_order from code_pedido_ordem where code = 'sub_total') -- sort_order
                                                    );";


    mysqli_query($conexao, $sql_sub_total_prod_frete) or die(mysqli_error($conexao));

    /*
    coupon
    TODO -> Variavel de desconto no json e verificar se o cupom eh valido e aplicar a insercao
    VOLTAR AQUI
    */

    $sql_cupom = " select * 
                     from oc_coupon 
                    where date_start <= now() 
                      and date_end   >= now()
                      and name like  '$v_cupom'
                      and status = 1;";

          $result = mysqli_query($conexao, $sql_cupom) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_cupom_id                    = $row['coupon_id'];
                    $v_cupom_name                  = $row['name'];
                    $v_cupom_code                  = $row['code'];
                    $v_cupom_type                  = $row['type'];
                    $v_cupom_discount              = $row['discount'];  
            }

            //verificar se o tipo de desconto é por valor (F) ou por percentual (P)


            if($v_cupom_type == 'P'){
               $sql_coupon = "insert into oc_order_total (order_id
                                                  ,code
                                                  ,title
                                                  ,value
                                                  ,sort_order) 
                                           values (
                                           $v_order_id                 -- order_id
                                          ,'coupon'                    -- code
                                          ,'Cupom ($v_cupom_name)'     -- title concatenado com o nome do cupom entre parentesis
                                          ,(select value * (select discount from oc_coupon where coupon_id = $v_cupom_id)/100 as value 
                                              from oc_order_total 
                                             where order_id = $v_order_id
                                               and code = 'sub_total') -- value (input via mobile) campo pontos
                                          ,(select sort_order from code_pedido_ordem where code = 'coupon')  -- sort_order
                                          );";

            }  

           if($v_cupom_type == 'F'){
               $sql_coupon = "insert into oc_order_total (order_id
                                                  ,code
                                                  ,title
                                                  ,value
                                                  ,sort_order) 
                                           values (
                                           $v_order_id                 -- order_id
                                          ,'coupon'                    -- code
                                          ,'Cupom ($v_cupom_name)'     -- title concatenado com o nome do cupom entre parentesis
                                          ,(select value - (select discount from oc_coupon where coupon_id = $v_cupom_id) as value 
                                              from oc_order_total 
                                             where order_id = $v_order_id
                                               and code = 'sub_total') -- value (input via mobile) campo pontos
                                          ,(select sort_order from code_pedido_ordem where code = 'coupon')  -- sort_order
                                          );";
                  }

        mysqli_query($conexao, $sql_coupon) or die(mysqli_error($conexao));          

        }


    
    /*
    kit_discount - valor de desconto //perguntar ao Doughan sobre kit_discount
    */


    /*
      Pontos de fidelidade
    */
      
      //obter pontos do cliente

      $sql_pontos_disponiveis = "select sum(points) as pontos 
                               from oc_customer_reward
                              where customer_id = $v_customer_id;";


      $result = mysqli_query($conexao, $sql_pontos_disponiveis) or die(mysqli_error($conexao));
            if(mysqli_num_rows($result)>0){
                while($row = mysqli_fetch_assoc($result)){               
                    $v_pontos_disponiveis          = $row['pontos'];
            }
      }


      //checar se os pontos sao suficientes 

      if ($v_pontos < $v_pontos_disponiveis){

        //emitir errro

      }else {

        $sql_pontos_cliente = "insert into oc_order_total (order_id
                                                          ,code
                                                          ,title
                                                          ,value
                                                          ,sort_order) 
                                                  values ($v_order_id           -- order_id
                                                        ,'reward'               -- code
                                                        ,'Pontos de fidelidade ($v_pontos)' 
                                                        ,$v_pontos              -- value (input via mobile) campo pontos
                                                        ,(select sort_order from code_pedido_ordem where code = 'reward')              -- sort_order
                                                        );";

       mysqli_query($conexao, $sql_pontos_cliente) or die(mysqli_error($conexao));

      }






    /*
    total - sub_total_prod_frete - kit_discount
    */


    $sql_total = "insert into oc_order_total (order_id
                                             ,code
                                             ,title
                                             ,value
                                             ,sort_order) 
                                      select '$v_order_id'
                                            ,'total'
                                            ,'Total a ser Pago'
                                            ,sum(total.value)
                                            ,(select sort_order from code_pedido_ordem where code = 'total')
                                       from
                                    (
                                    -- somar
                                    select sum(value) as value 
                                      from oc_order_total
                                     where code in ('sub_total_prod_frete')
                                       and order_id = $v_order_id

                                    union 

                                    -- subtrair   
                                    select sum(value) * -1 as value
                                     from oc_order_total
                                    where code in ('kit_discount','coupon')
                                      and order_id = $v_order_id ) as total";



    mysqli_query($conexao, $sql_total) or die(mysqli_error($conexao));
   
   //executar os stmt totais 
    
  }
    
    
    
// Get as an object
//$json_obj = json_decode($json_str);
  

?>