Codeigniter Query in mysql With JOIN, GroupBy, Select Into Select, IFNULL, SUM, IF Condition

Codeigniter Query

function getitemsofseller($sellers){
       
        //For get total funds of the seller and set flag on item
        $i=0;
        $banne = 0;
        $results = array();
        foreach ($sellers as $seller):
           
            $this->db->select('*');
       
            /*Get homepage banner fee*/
            $this->db->select("SUM(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) )) as mainbannerfee");
           
            /*Get feature banner fee*/
            $this->db->select("SUM(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) )) as featurefee");
           
            /*Get nominal fee*/
            $this->db->select("SUM(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2))) as nominalfee", false);
           
            /*Get Total payment which is payed to seller ((paid_price+shipping_price)*quantity)-mainbannerfee-featurefee-nominalfee*/
            $this->db->select("(SUM((((paid_price+shipping_price)*quantity)"
                    . "-(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2)))))) as sellerpaid", false);
           
            /*Get return product payment (SUM(((paid_price+shipping_price)*quantity)+home_page_banner+featurebanner+nominalfee) */
            $this->db->select("IFNULL((SELECT SUM(((paid_price+shipping_price)*quantity)+(IF(home_page_banner = 1, (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))+(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ), ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))) FROM (".prefix('order_items')." as btoi) WHERE `seller_id` = oi.seller_id AND btoi.lisiting_id = oi.lisiting_id AND `dispute_raised` = '0' AND `ispaidtoseller` = '0' AND `is_paid` = '3' AND `item_status` = 'Completed' GROUP BY `lisiting_id`),0) as refunds", false);
           
            /*Get return product payment (((paid_price+shipping_price)*quantity)-mainbannerfee-featurefee-nominalfee)-returned ((SUM(((paid_price+shipping_price)*quantity)+home_page_banner+featurebanner+nominalfee)) */
            $this->db->select("(SUM((((paid_price+shipping_price)*quantity)"
                    . "-(IF(home_page_banner = 1 , (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100 , ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ) , ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))"
                    . "-(IF(((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) > 0.50 ,((((paid_price+shipping_price)*quantity)*IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0))/100) , (1/2)))))"
                    . "-IFNULL((SELECT SUM(((paid_price+shipping_price)*quantity)+(IF(home_page_banner = 1, (((paid_price+shipping_price)*quantity)*".MAIN_BANNER_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))+(IF(featured_product = 1, IF(is_buytribe = 1, (((paid_price+shipping_price)*quantity)*".FEATURE_BT_FEE.")/100, ((((paid_price+shipping_price)*quantity)*".FEATURE_BIN_FEE.")/100) ), ((((paid_price+shipping_price)*quantity)*".$banne.")/100) ))) FROM (".prefix('order_items')." as btjoi) WHERE `seller_id` = oi.seller_id AND btjoi.lisiting_id = oi.lisiting_id AND `dispute_raised` = '0' AND `ispaidtoseller` = '0' AND `is_paid` = '3' AND `item_status` = 'Completed' GROUP BY `lisiting_id`),0)) as sellerpaidafterfee", false);
           
           
            /*Get Return Qty (if is_paid = 3)*/
            $this->db->select("IFNULL((SELECT SUM(quantity) FROM ".prefix('order_items').' WHERE dispute_raised = 0 && item_status = "Completed" && ispaidtoseller = 0 && is_paid = 3 && `lisiting_id` = oi.lisiting_id LIMIT 1),0) as returnqty', false);
           
            /*Gross Revenue SUM((paid_price+shipping_price)*quantity)*/
            $this->db->select("SUM((paid_price+shipping_price)*quantity) as grossrevenue", false);
           
            /*Get nominal fee in percentage(%) */
            $this->db->select("IFNULL((SELECT `c`.commission FROM ".prefix('category_products')." JOIN ".prefix('categories')." as c ON `c`.`id` = ".prefix('category_products').".`category_id` && c.parent_id = 0 WHERE `product_id` = oi.product_id), 0) as nominalfeeper", false);
           
            /* Get total shipping charge */
            $this->db->select("SUM(shipping_price) as totalshipping", false);
           
            /* Get total product price without fee*/
            $this->db->select("SUM(paid_price)*quantity as totalproductprice", false);
           
            /* Get total quantity without calculate returned*/
            $this->db->select("SUM(quantity) as unitsold");
           
            /* Get total quantity without calculate returned*/
            $this->db->select(MAIN_BANNER_FEE." as mainbannerfeestatic", false);
           
            $this->db->select("IF(is_buytribe = 1 , ".FEATURE_BT_FEE." , ((1*".FEATURE_BIN_FEE.")) ) as featurefeestatic", false);
           
            $this->db->from("order_items as oi");
            $this->db->group_by('lisiting_id');
            $this->db->where('seller_id', $seller->seller_id);
            $this->db->where('dispute_raised', '0');
            $this->db->where('ispaidtoseller', '0');
            $this->db->where('item_status', 'Completed');
           
            $query = $this->db->get();
           
            //echo $this->db->last_query();exit;
            if($query->num_rows() > 0){
                $results[$i] = $query->result();
            }
            $i++;
        endforeach;
       
        return $results;
    }

How to enable / disable edit qty create invoice in magento

How to enable / disable edit qty create invoice in magento.

You can change enable/disable edit qty when create new invoice in magento.

Open you payment method model file.

Ex. You want to enable/disable edit qty when use "Cash On Delivery"

Open file:
/var/www/html/magentostore/app/code/core/Mage/Payment/Model/Method/Cashondelivery.php

class Mage_Payment_Model_Method_Cashondelivery extends Mage_Payment_Model_Method_Abstract
{
    /**
     * Payment method code
     *
     * @var string
     */
    protected $_code  = 'cashondelivery';
    protected $_canCapture = true;
    protected $_canCapturePartial = true;
    .....
    .....
    .....
}

Add  protected $_canCapturePartial = true; in this file.

How to check my url is in iframe or not javascript

Browsers can block access to window.top due to same domain.

Example

<script>
inIframe();
function inIframe () {
    try {
        var isif = window.self !== window.top;
        if(isif == false){
            //not iframe
        }else{
  //in iframe
}
    } catch (e) {
            //not iframe
    }
}
</script>

top and self are both window objects (along with parent), so you're seeing if your window is the top window.

How to replace string with regex in jQuery Javascript in Html with Using nodeType / node Shortcode

How to replace string with regex in jQuery Javascript in Html with Using nodeType / node

Text

[jems-countdown token="4" data-id="20136582"]

Script

jQuery(document).ready(function(){
   
    $('body *').contents().each(function() {
        if(this.nodeType == 3) {
            var u = this.nodeValue;
            var reg = /\[(jems-countdown.*)\]/g;
            $(this).replaceWith(u.replace(reg,'<div class="metcounter" $1>kanasagra</div>'));
        }
    });
  });

Copy text/content when press button (clipboard) Javascript / jQuery.

As of 2016, you can now copy text to the clipboard in most browsers (everywhere except Safari) because most browsers have the ability to pro-grammatically copy a selection of text to the clipboard using document.execCommand("copy") that works off a selection.

As with some other actions in a browser (like opening a new window), the copy to clipboard can only be done via a specific user action (like a mouse click). For example, it cannot be done via a timer.

Here's a code example:

HTML

<div class="showCodeWrapper">
    <btn class="btn btn-default copyMe">
        <i class="fa fa-clipboard"></i>
        Copy
    </btn>
    <textarea data-app-type="countdown-timer" class="form-control embedShortcode showCode selectAll" readonly="">test</textarea>
</div>

jQuery

<script>
    $( "li.third-item" ).siblings().css( "background-color", "green" );
$(document).on("click", ".copyMe", function() {
   
    var e = $(this).siblings("textarea")[0];
    e.select();
    try {
        if (!document.execCommand("copy")) throw "Copy unsuccessful";
        $(this).hide().html('<i class="fa fa-check"></i> Copied').fadeIn("fast")
    } catch (t) {
        $(this).hide().html("Press &#8984;+C to copy.").fadeIn("fast"), debug("Copying text error " + t)
    }
})
</script>

How to Shopify API Product / Order / Collection get sorted when passing in an created_at / updated_at filter

What would be ideal would be to be able to get results in ascending/descending order one way or another, but without having to pass in since_id, because we want to be able to pull in new data on orders on an ongoing basis as they may be updated.

Its possible to sort the Products/Orders/Collection by passing an "order" parameter. The "order" parameter should contain the field to sort by (supported fields are "created_at", "updated_at" and "processed_at"), followed by a space and then by an "asc" or "desc".

Please check following Example for solution.

https://example.myshopify.com/admin/orders.json?limit=2&fields=id,created_at&order=created_at%20asc

Convert PHP, Magento, Joomla, Codeigniter, Cake PHP, Wordpress XML to JSON, XML to Array, JSON to Array

Convert PHP, Magento, Joomla, Codeigniter, Cake PHP, Wordpress XML to JSON, XML to Array, JSON to Array is very difficult task for some people. But, in PHP, believe me it’s very easy. One line of code each! Don’t believe? Just check out the code below and test it yourself!

$xml = simplexml_load_string($xmldata);
$json = json_encode($xml);
$array = json_decode($json,true);