<?php

namespace YHMOrders\SqlMap\Orders;

class Order
{
    const COUNT_SOLD_ORDER_BY_STOREID = 'select count(order_code) from orders where store_id=:store_id';
    const COUNT_BUY_ORDER_BY_UID = 'select count(order_code) from orders where buyer_uid=:uid';
    const SELECT_SOLD_ORDER_BY_STOREID = " select order_code,buyer_uid,create_time,order_remark from orders where store_id=:store_id and order_status=:orderStatus order by create_time desc limit :offset,:size";
    
    const SELECT_ORDER_BY_STOREID = 'select order_code,buyer_uid,create_time,order_remark from orders where store_id=:store_id limit :offset,:size';

 	const SELECT_BUYLIST_ORDER_BY_COMMENTS = 'select order_code,seller_uid,create_time,buyer_uid,store_id,last_order_amount,order_remark,is_comments from orders as o where buyer_uid=:buyer_uid and order_status =:order_status  ORDER BY is_comments asc, id desc limit :offset,:size';
    const SELECT_BUYLIST_ORDER_BY_UID = 'select order_code,create_time,seller_uid,buyer_uid,store_id,last_order_amount,order_remark,is_comments from orders as o where buyer_uid=:buyer_uid and order_status=:orderStatus order by id desc limit :offset,:size';
    const SELECT_GOODS_BY_ORDER_CODE = 'select * from orders_goods where order_code=:order_code';
    const SELECT_ONE_GOODS_BY_ORDER_CODE = 'select * from orders_goods where order_code=:order_code limit 1';
    const SELECT_SKC_BY_PRODUCT_SKC = 'select goods_name from goods where product_skc=:product_skc';
    const SELECT_GOODS_IAMGES_BY_PRODUCT_SKC = 'select image_path from goods_images where product_skc=:product_skc';
    const UPDATE_ORDER_STATUS_BY_CODE_AND_STATUS = 'update orders set order_status=:order_status,reason=:reason where order_code=:order_code';
    const SELECT_ORDERS_BY_ORDER_CODE = 'select * from orders where order_code = :order_code';
   
    const COUNT_SOLD_BY_SKC = "select sum(`buy_number`) as sold_number from orders_goods as g LEFT JOIN orders  as o  on  g.order_code=o.order_code  where  `is_payed`='Y' and product_skc = :product_skc";

    const COUNT_SELL_BY_STORE_ID = 'select count(id) from orders where store_id = :store_id ';

    const COUNT_BUYED_BY_BUYER_UID = "select sum(buy_number) from orders as o LEFT JOIN orders_goods as g on o.order_code=g.order_code where buyer_uid = :buyer_uid and `is_payed`='Y' ";
    const GET_CANWITHDRAW_BY_SELLERUID = "select sum(`last_order_amount`) from `orders` where `seller_uid`=:seller_uid and `is_payed`='Y' and `order_status`=600 and `deliver_time`<:deliver_time";

    const GET_FREEZE_BY_SELLERUID = "select sum(`last_order_amount`) from `orders` where `seller_uid`=:seller_uid and `is_payed`='Y'";

    const UPDATE_ORDER_PAY_INFO = "update orders set payment_type=1,payment_id=:payment_id,bank_code=:bank_code,is_payed='Y',alipay_trade_code=:alipay_trade_code,order_status=300,buyer_pay_profile=:buyer_pay_profile where order_code=:order_code";
    
    const SELECT_GOODS_BY_SKC = 'select * from orders_goods where product_skc=:product_skc';
    
   const SELECT_ORDERS_GOODS_INFO = 'select * from orders_goods_info where order_code=:order_code and  product_skc=:product_skc and product_sku=:product_sku';
   
   const UPDATE_ORDER_COMMENTS="update orders set is_comments=:is_comments where order_code=:order_code";
   
   const SELECT_BUY_GOODS_BY_STORE = "select product_skc,sum(buy_number) as buy_number  from (select product_skc,buy_number as buy_number,o.create_time from orders_goods as g LEFT JOIN orders as o on g.order_code=o.order_code where o.is_payed='Y' and g.store_id=:store_id ORDER BY  o.create_time desc  )  as gg  GROUP BY gg.product_skc order by create_time desc  limit :offset,:size";
   const SELECT_BUY_GOODS_BY_STORE_TOTAL = "select count(DISTINCT product_skc) as number from orders_goods as g LEFT JOIN orders as o on g.order_code=o.order_code where o.is_payed='Y' and g.store_id=:store_id";
   
   const SELECT_COUNT_ORDER_BY_STATUS = "select sum(buy_number) from orders as o LEFT JOIN orders_goods as g on g.order_code=o.order_code where o.store_id = :store_id and `is_payed`='Y' ";
   
   const SELECT_BUY_LIST_BY_UID = 'select * from orders where buyer_uid = :uid limit :offset,:num';

   const SELECT_GOODS_LIST="select product_skc,o.order_code,order_status,sale_price,buyer_uid,FROM_UNIXTIME(create_time) as time,buy_number from orders_goods as g LEFT JOIN orders as o on g.order_code=o.order_code where product_skc=:product_skc limit :offset,:num";
   const SELECT_GOODS_LIST_COUNT="select count(product_skc) from orders_goods as g LEFT JOIN orders as o on g.order_code=o.order_code where product_skc=:product_skc ";
   const SELECT_ORDER_LIST_COUNT="select count(g.order_code) from orders as o LEFT JOIN orders_goods as g  on o.order_code=g.order_code where #where#  ";
   const SELECT_ORDER_LIST="select  g.*,o.*,FROM_UNIXTIME(create_time)as  time from orders as o LEFT JOIN orders_goods as g  on o.order_code=g.order_code where #where# #order# limit :offset,:num ";
   const SELECT_GOODS_INFO_BY_ORDER_CODE = 'select * from orders_goods_info where order_code=:order_code';
   const SELECT_GOODS_INFO_BY_IMG="select * from orders_goods_info";
   const UPDATE_GOODS_INFO_BY_IMG="update  orders_goods_info set goods_images=:goods_images where product_skc=:id";
   const GET_SELLER_SOLD_GOODS = "select product_skc,sum(buy_number) as buy_number  from (select product_skc,buy_number as buy_number,o.create_time from orders_goods as g LEFT JOIN orders as o on g.order_code=o.order_code where o.is_payed='Y' and o.store_id=:store_id ORDER BY  o.create_time desc  )  as gg  GROUP BY gg.product_skc order by create_time desc  limit :offset,:num";
   const GET_SELLER_ORDER_BY_SORT_ORDER_STATUS = "SELECT order_code,buyer_uid,seller_uid,create_time,order_remark, order_status,shipping_fee,last_shipping_fee FROM orders WHERE seller_uid=:seller_uid AND order_status IN(#order_status#)  ORDER BY FIELD(order_status, #order_status#), create_time DESC LIMIT :offset, :limit";
   const GET_BUY_ORDER_BY_SORT_ORDER_STATUS = "SELECT order_code,buyer_uid,seller_uid, store_id,order_status,create_time,order_remark, order_status,shipping_fee,last_shipping_fee,last_order_amount FROM orders WHERE buyer_uid=:buyer_uid AND order_status IN(#order_status#)  ORDER BY FIELD(order_status, #order_status#), create_time DESC LIMIT :offset, :limit";
   const GET_BUY_ORDER_TOTAL_BY_SORT_ORDER_STATUS = "SELECT COUNT(*) FROM orders WHERE buyer_uid=:buyer_uid AND order_status IN(#order_status#)";
   const GET_SELLER_ORDER_TOTAL_BY_SORT_ORDER_STATUS = "SELECT COUNT(*) FROM orders WHERE seller_uid=:seller_uid AND order_status IN(#order_status#)";
  
   const UPDATE_ORDER_PRICE = 'UPDATE orders SET order_amount = :order_amount, last_shipping_fee = :last_shipping_fee, shipping_fee = :shipping_fee, last_order_amount = (:order_amount) + (:last_shipping_fee) WHERE order_code = :order_code';
}