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;
}
