一.pom.xml引入依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.2.0</version>
</dependency>
二.application.properties中数据源配置，使用
druid连接池
spring.datasource.url =jdbc:mysql://10.3.5.46:3306/qumaiyao-new-20160315?autoReconnect=true&failOverReadOnly=false
spring.datasource.username = qumaiyao
spring.datasource.password = xxxxxx
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.filters = stat
spring.datasource.maxActive = 20
spring.datasource.initialSize = 1
spring.datasource.maxWait = 60000
spring.datasource.minIdle = 1
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.minEvictableIdleTimeMillis = 300000
spring.datasource.validationQuery = select 'x'
spring.datasource.testWhileIdle = true
spring.datasource.testOnBorrow = false
spring.datasource.testOnReturn = false
spring.datasource.poolPreparedStatements = true
spring.datasource.maxOpenPreparedStatements = 20
三.集成druid
package com.ehaoyao.payment;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
@Configuration
public class DruidConfig {
private Logger logger = LoggerFactory.getLogger(getClass());
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.filters}")
private String filters;
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("loginUsername", "druid");
reg.addInitParameter("loginPassword", "jiajian123456");
return reg;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
return filterRegistrationBean;
}
@Bean
@Primary
public DataSource druidDataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
return datasource;
}
}
创建数据源
package com.ehaoyao.payment;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
@Configuration
@MapperScan(basePackages = "com.ehaoyao.payment.dao", sqlSessionTemplateRef = "myBatisSqlSessionTemplate")
public class DataSourceConfig {
@Autowired
private DataSource dataSource;
// @Bean(name = "myBatisDataSource")
// @ConfigurationProperties(prefix = "spring.datasource")
// @Primary
// public DataSource testDataSource() {
// //return DataSourceBuilder.create().build();
// }
@Bean(name = "myBatisSqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory()
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return bean.getObject();
}
@Bean(name = "myBatisTransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager() {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "myBatisSqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("myBatisSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
三.dao构建
mapper
OrderMapper.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ehaoyao.payment.dao.OrdersDao">
<resultMap type="com.ehaoyao.payment.entity.Orders" id="resultOrders">
<id column="order_id" property="orderId" />
<result column="member_id" property="memberId" />
<result column="status" property="status" />
<result column="pharmacy_id" property="pharmacyId" />
<result column="pharmacy_name" property="pharmacyName" />
<result column="pharm_short_name" property="pharmShortName" />
<result column="contactmobie" property="contactmobie" />
<result column="ok_pharmacy" property="okPharmacy" />
<result column="pay_status" property="payStatus" />
<result column="is_delivery" property="isDelivery" />
<result column="shipping_id" property="shippingId" />
<result column="shipping" property="shipping" />
<result column="payment" property="payment" />
<result column="weight" property="weight" />
<result column="captcha" property="captcha" />
<result column="itemnum" property="itemnum" />
<result column="delivery_time" property="deliveryTime" />
<result column="createtime" property="createtime" />
<result column="ship_name" property="shipName" />
<result column="ship_area" property="shipArea" />
<result column="province" property="province" />
<result column="city" property="city" />
<result column="country" property="country" />
<result column="street" property="street" />
<result column="ship_addr" property="shipAddr" />
<result column="ship_zip" property="shipZip" />
<result column="ship_tel" property="shipTel" />
<result column="ship_email" property="shipEmail" />
<result column="ship_mobile" property="shipMobile" />
<result column="ship_time" property="shipTime" />
<result column="cost_item" property="costItem" />
<result column="is_tax" property="isTax" />
<result column="tax_company" property="taxCompany" />
<result column="cost_freight" property="costFreight" />
<result column="score_g" property="scoreG" />
<result column="total_amount" property="totalAmount" />
<result column="pmt_amount" property="pmtAmount" />
<result column="final_amount" property="finalAmount" />
<result column="memo" property="memo" />
<result column="last_change_time" property="lastChangeTime" />
<result column="orderType" property="orderType" />
<result column="ordertypeName" property="ordertypeName" />
<!--
<result column="statusIds" property="statusIds" />
-->
<result column="is_group" property="isGroup" />
<result column="reason_text" property="reasonText" />
<result column="is_retention" property="isRetention" />
<result column="activity_id" property="activityId" />
<result column="storeId" property="storeId" jdbcType="VARCHAR" />
<result column="inviteCode" property="inviteCode" jdbcType="VARCHAR" />
<result column="returnId" property="returnId" jdbcType="VARCHAR" />
<result column="returnStatus" property="returnStatus" jdbcType="VARCHAR" />
<result column="return_createTime" property="returnCreateTime" />
<result column="return_createTimeStart" property="returnCreateTimeStart" />
<result column="return_createTimeEnd" property="returnCreateTimeEnd" />
<!--
<result column="pharmacy_ids" property="pharmacyIds" />
-->
<result column="paymethod" property="paymethod" />
<result column="paymentCfg_id" property="paymentCfgId" />
<result column="member_mac" property="memberMac" />
<result column="discount_amount" property="discountAmount" />
<result column="isupdateorder" property="isupdateorder" />
<result column="lastsystime" property="lastsystime" />
<result column="isPrescription" property="isPrescription" />
<result column="gdXcoord" property="gdXcoord" />
<result column="gdYcoord" property="gdYcoord" />
<result column="rev_time" property="rev_time" />
<result column="courier_name" property="courier_name" />
<result column="rev_effect" property="rev_effect" />
<result column="effect_time" property="effect_time" />
<result column="finish_effict" property="finish_effict" />
<result column="courierMobile" property="courierMobile" />
<result column="audit_memo" property="auditMemo"/>
<result column="pushTime" property="pushTime"/>
<result column="logistics_platype" property="logistics_platype"/>
<result column="thirdPlatform_order_id" property="thirdPlatform_order_id"/>
<result column="promote_code" property="promoCode"/>
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.OrderReturnExprot" id="resultReturnOrders">
<id column="order_id" property="orderId" />
<result column="status" property="status" />
<result column="pharm_short_name" property="pharmShortName" />
<result column="contactMobie" property="contactmobie" />
<result column="shipping" property="shipping" />
<result column="ship_name" property="shipName" />
<result column="ship_mobile" property="shipMobile" />
<result column="total_amount" property="totalAmount" />
<result column="returnId" property="returnId" jdbcType="VARCHAR" />
<result column="returnStatus" property="returnStatus" jdbcType="VARCHAR" />
<result column="return_createTime" property="createTime" />
<result column="paymethod" property="paymethod" />
<result column="orderType" property="orderType" />
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.ExpressOrderVO"
id="expressOrderResult" extends="com.ehaoyao.payment.dao.OrdersDao.resultOrders">
<result column="chain_branch_id" property="chainBranchId" />
<collection property="orderDetails" column="order_id"
select="com.ehaoyao.payment.dao.OrderItemsDao.selectPharmacyOrderDetailByOrderId" />
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.ErpOrderVo"
id="erpOrderResult" extends="com.ehaoyao.payment.dao.OrdersDao.resultOrders">
<result column="chain_branch_id" property="chainBranchId" />
<result column="promote_code" property="promoteCode" />
<result column="promCd" property="promCd" />
<result column="systatus" property="systatus" />
<result column="user_id" property="userId" />
<result column="logistics_platype" property="logistics_platype"/>
<collection property="orderDetails" column="order_id"
select="com.ehaoyao.payment.dao.OrderItemsDao.selectPharmacyOrderDetailByOrderId" />
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.Orders" id="resultOrdersActivity">
<id column="order_id" property="orderId" />
<result column="member_id" property="memberId" />
<result column="status" property="status" />
<result column="pharmacy_id" property="pharmacyId" />
<result column="pharmacy_name" property="pharmacyName" />
<result column="contactmobie" property="contactmobie" />
<result column="ok_pharmacy" property="okPharmacy" />
<result column="pay_status" property="payStatus" />
<result column="is_delivery" property="isDelivery" />
<result column="shipping_id" property="shippingId" />
<result column="shipping" property="shipping" />
<result column="payment" property="payment" />
<result column="weight" property="weight" />
<result column="captcha" property="captcha" />
<result column="itemnum" property="itemnum" />
<result column="delivery_time" property="deliveryTime" />
<result column="createtime" property="createtime" />
<result column="ship_name" property="shipName" />
<result column="ship_area" property="shipArea" />
<result column="province" property="province" />
<result column="city" property="city" />
<result column="country" property="country" />
<result column="ship_addr" property="shipAddr" />
<result column="ship_zip" property="shipZip" />
<result column="ship_tel" property="shipTel" />
<result column="ship_email" property="shipEmail" />
<result column="ship_mobile" property="shipMobile" />
<result column="cost_item" property="costItem" />
<result column="is_tax" property="isTax" />
<result column="tax_company" property="taxCompany" />
<result column="cost_freight" property="costFreight" />
<result column="score_g" property="scoreG" />
<result column="total_amount" property="totalAmount" />
<result column="pmt_amount" property="pmtAmount" />
<result column="final_amount" property="finalAmount" />
<result column="memo" property="memo" />
<result column="last_change_time" property="lastChangeTime" />
<result column="orderType" property="orderType" />
<!--
<result column="statusIds" property="statusIds" />
-->
<result column="is_group" property="isGroup" />
<result column="is_retention" property="isRetention" />
<result column="activity_id" property="activityId" />
<result column="member_mac" property="memberMac" />
<result column="discount_amount" property="discountAmount" />
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.OrderMaintenanceVO" id="resultOrderMaintenance">
<result column="order_id" property="ordersId"/>
<result column="member_id" property="memberId"/>
<result column="status" property="status"/>
<result column="mailno" property="mailno"/>
<result column="pharmacy_id" property="pharmacyId"/>
<result column="couponsType" property="couponsType"/>
<result column="returnStatus" property="returnStatus"/>
<result column="activity_id" property="activityId"/>
<result column="member_mac" property="memberMac"/>
<result column="payment" property="payment"/>
<result column="pay_status" property="payStatus"/>
<result column="cost_freight" property="costFreight"/>
<result column="cost_item" property="costItem"/>
<result column="total_amount" property="totalAmount"/>
<result column="pmt_amount" property="pmtAmount"/>
<result column="discount_amount" property="discountAmount"/>
<result column="final_amount" property="finalAmount"/>
</resultMap>
<sql id="Base_Column_List">
o.order_id,o.pharmacy_id,status,shipping,captcha,itemnum,createtime,shipping_id,ship_name,member_id,pay_status,returnStatus,delivery_time,
ship_area,is_retention,
ship_addr, ship_zip, ship_tel, ship_email,street,
ship_mobile,cost_item,is_tax,cost_freight,payment,
tax_company,total_amount,pmt_amount,discount_amount,final_amount,memo,last_change_time,
orderType,pharmacy_name,contactmobie,o.province,o.city,o.country,is_group,activity_id,reason_text,o.member_mac,isPrescription,logistics_platype
</sql>
<insert id="addOrders" parameterType="com.ehaoyao.payment.entity.Orders">
INSERT INTO
o2o_orders(order_id,member_id,status,pharmacy_id,pharmacy_name,contactmobie,ok_pharmacy,pay_status,is_delivery,
delivery_time,shipping_id,shipping,cost_freight,payment,weight,captcha,itemnum,createtime,ship_name,
ship_area,province,city,country,street,ship_addr,ship_zip,ship_tel,ship_email,ship_mobile,ship_time,cost_item,is_tax,tax_company,score_g,
total_amount,pmt_amount,final_amount,memo,last_change_time,orderType,is_group,activity_id,member_mac,discount_amount,couponsType,isPrescription)
VALUES(
#{orderId} ,#{memberId} ,#{status} ,#{pharmacyId}
,#{pharmacyName},#{contactmobie},#{okPharmacy} ,#{payStatus}
,#{isDelivery} ,#{deliveryTime},
#{shippingId} ,#{shipping}
,#{costFreight} ,#{payment} ,#{weight} ,#{captcha} ,#{itemnum} ,
<choose>
<when test="createtime != null and createtime != ''">#{createtime}</when>
<otherwise>now()</otherwise>
</choose>
,
#{shipName} ,#{shipArea},#{province},#{city},#{country},#{street},
#{shipAddr}
,#{shipZip} ,#{shipTel} ,#{shipEmail} ,#{shipMobile} ,#{shipTime} ,
#{costItem}
,#{isTax} ,#{taxCompany} ,#{scoreG} ,#{totalAmount} ,#{pmtAmount}
,#{finalAmount} ,#{memo} ,
<choose>
<when test="lastChangeTime != null and lastChangeTime != ''">#{lastChangeTime}</when>
<otherwise>now()</otherwise>
</choose>
,
#{orderType},#{isGroup},#{activityId},#{memberMac},#{discountAmount},#{couponsType},#{isPrescription}
)
</insert>
<select id="listPageOrders" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="resultOrders">
select
<include refid="Base_Column_List" />
from o2o_orders o where
1=1 and deleteFlag!=1 and (
<foreach item="item" index="index" collection="orders.statusIds"
open="" separator="or" close="">
o.status =#{item}
</foreach>
)
<if test="orders.memberId != null and orders.memberId != 0">
and o.member_id= #{orders.memberId}
</if>
<if test="orders.pharmacyId != null and orders.pharmacyId != ''">
and o.pharmacy_id= #{orders.pharmacyId}
</if>
<if test="orders.isGroup == null">
and o.is_group = 0
</if>
<if test="orders.isGroup!=null">
and o.is_group=#{orders.isGroup}
</if>
order by createtime desc
</select>
<select id="listPageOrders3_0" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="resultOrders">
select
<include refid="Base_Column_List" />,
op.promote_code
from o2o_orders o
left join o2o_order_property op on op.order_id=o.order_id
where
1=1 and deleteFlag!=1 and (
<foreach item="item" index="index" collection="orders.statusIds"
open="" separator="or" close="">
o.status =#{item}
</foreach>
)
<if test="orders.memberId != null and orders.memberId != 0">
and o.member_id= #{orders.memberId}
</if>
<if test="orders.pharmacyId != null and orders.pharmacyId != ''">
and o.pharmacy_id= #{orders.pharmacyId}
</if>
<if test="orders.isGroup == null">
and o.is_group = 0
</if>
<if test="orders.isGroup!=null">
and o.is_group=#{orders.isGroup}
</if>
order by createtime desc
</select>
<select id="getTotalOrders" parameterType="com.ehaoyao.payment.entity.Orders"
resultType="int">
select count(order_id)
from o2o_orders o where 1=1 and deleteFlag!=1
<if test="memberId != null and memberId != 0">
and o.member_id= #{memberId}
</if>
and (
<foreach item="item" index="index" collection="statusIds"
open="" separator="or" close="">
o.status =#{item}
</foreach>
)
<if test="pharmacyId != null and pharmacyId != ''">
and o.pharmacy_id= #{pharmacyId}
</if>
<if test="isGroup!=null">
and o.is_group=#{isGroup}
</if>
</select>
<select id="getOrderById" parameterType="String" resultMap="resultOrders">
select 'true' as QUERYID,op.pharm_short_name,pro.memberAdds_gdXcoord gdXcoord,pro.memberAdds_gdYcoord gdYcoord,
o.COURIER_NAME,o.EFFECT_TIME,o.REV_EFFECT,o.FINISH_EFFICT,o.REV_TIME,o.courierMobile,o.pushTime,pro.thirdPlatform_order_id,
<include refid="Base_Column_List" />
from o2o_orders o
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
LEFT JOIN o2o_order_property pro ON pro.order_id = o.order_id
where o.order_id=#{orderId}
</select>
<select id="getOrderByIdAndUser" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="resultOrders">
select 'true' as QUERYID,
<include refid="Base_Column_List" />
from o2o_orders o
where o.order_id=#{orderId} and
o.member_id=#{memberId}
</select>
<update id="updateStatusByOrderId" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="status!=null">
status= #{status},
</if>
<if test="pharmacyId != null and pharmacyId != 0">
pharmacy_id= #{pharmacyId},
</if>
<if test="pharmacyName != null and pharmacyName != ''">
pharmacy_name= #{pharmacyName},
</if>
<if test="reasonText !=null and reasonText != ''">
reason_text = #{reasonText},
</if>
<if test="payStatus !=null and payStatus != ''">
pay_status = #{payStatus},
</if>
<if test="returnStatus !=null">
returnStatus = #{returnStatus},
</if>
<if test="memo !=null and memo != ''">
memo = #{memo},
</if>
<if test="deleteFlag !=null and deleteFlag != ''">
deleteFlag = #{deleteFlag},
</if>
<choose>
<when test="lastChangeTime != null and lastChangeTime != ''">last_change_time=#{lastChangeTime},</when>
<otherwise>last_change_time=now(),</otherwise>
</choose>
</set>
where
order_id=#{orderId}
<if test="ostatus != null and ostatus!= ''">
and status= #{ostatus}
</if>
</update>
<update id="updateShippingTypeByOrderId" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="shippingId != null and shippingId != ''">
shipping_id= #{shippingId},
</if>
<if test="shipping != null and shipping != ''">
shipping= #{shipping},
</if>
<if test="shipMobile != null and shipMobile != ''">
ship_mobile= #{shipMobile},
</if>
<if test="shipName != null and shipName != ''">
ship_name= #{shipName},
</if>
province= #{province},
city= #{city},
country= #{country},
ship_addr= #{shipAddr},
</set>
where
order_id=#{orderId}
</update>
<!-- 获取某药店 驳回状态下的订单数目 -->
<select id="getRejectedOrderNum" parameterType="java.lang.Long"
resultType="int">
select count(*)
from o2o_orders o
where
o.pharmacy_id=#{pharmacyId} and o.status=3
and o.is_group = 0
</select>
<!-- 获取所有 驳回状态下的订单数目 -->
<select id="getAllRejectedOrderNums" resultType="int">
select count(*)
from o2o_orders o
where o.status=3
and o.is_group = 0
</select>
<select id="queryExpressOrder" parameterType="com.ehaoyao.payment.entity.EDIExpressOrderVO"
resultMap="expressOrderResult">
select a.*,s.syncstatus from(
select o.order_id,o.pharmacy_id,o.status,o.shipping,o.captcha,o.itemnum,o.createtime,o.shipping_id,o.ship_name,o.member_id,
o.ship_area,o.is_retention,o.ship_addr, o.ship_zip,o. ship_tel, o.ship_email,o.ship_mobile,o.cost_item,o.is_tax,o.cost_freight,o.payment,
o.tax_company,o.total_amount,(o.pmt_amount+o.discount_amount) as pmt_amount,o.final_amount,o.memo,g.acttime as last_change_time,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName,o.pharmacy_name,o.contactmobie,
o.province,o.city,o.country,o.is_group,o.activity_id,o.reason_text,e.mailno ,p.external_id as storeId
from o2o_orders o,o2o_pharmacy p,o2o_express_success e,o2o_order_log g,o2o_sys_syscode s
where o.pharmacy_id=p.pharmacy_id and o.order_id=e.orderid and o.orderType = s.`value` and s.catalog = "OrderType"
and o.order_id=g.order_id and g.behavior="完成"
<if test="externalId!=null and externalId != '' ">
and p.external_id=#{externalId}
</if>
<if test="chainBranchId !=null and chainBranchId != '' ">
and p.chain_branch_id=#{chainBranchId}
</if>
and o.status in(2,6)
<if test="shippingIds!=null">
and o.shipping_id in
<foreach item="item" index="index" collection="shippingIds" open="(" separator="," close=")">
#{item}
</foreach>
</if>
order by o.createtime desc
) a left join o2o_order_sync_records s
on a.order_id = s.order_id where
1=1
and s.order_status = 2
<if test="syncStatus==null or syncStatus==''">
and (s.syncstatus= -1 or s.syncstatus =0)
</if>
<if test="syncStatus!=null and syncStatus!=''">
and s.syncstatus=#{syncStatus}
</if>
</select>
<!--自提单： 待处理未同步,待处理已同步但修改配送未同步,待处理已同步做了取消和驳回的定单状态变更后未同步 ;配送单：完单且未同步-->
<select id="listPagequeryExpressOrder2" parameterType="com.ehaoyao.payment.entity.EDIExpressOrderVO" resultMap="erpOrderResult">
select a.* from(
(select o.order_id,o.status,oac.user_id,
h.order_status,h.syncstatus, MAX(h.synctime) as lastsystime,
o.payment,o.createtime,case when o.shipping_id = 1 then 1 else 0 end as shipping_id,
o.is_tax,o.tax_company,o.delivery_time,o.is_delivery,o.memo,o.captcha,o.final_amount,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName,
p.pharm_short_name as pharmacy_name,o.pharmacy_id,p.external_id as storeId,p.chain_branch_id,o.cost_item,o.total_amount,o.cost_freight,
(o.pmt_amount+o.discount_amount) as pmt_amount,o.ship_name,o.ship_mobile,o.ship_addr,o.ship_area,o.logistics_platype,
o.province,o.city,o.country,m.promCd,
case when m.promote_code is null THEN om.inviteCode
when m.promote_code='' THEN om.inviteCode
else m.promote_code end as promote_code,
case when r.order_status is null THEN 0 else 1 end as isupdateorder
from o2o_orders o
LEFT JOIN o2o_order_sync_records h on o.order_id = h.order_id
LEFT JOIN o2o_pharmacy p on o.pharmacy_id=p.pharmacy_id
LEFT JOIN o2o_order_property m on o.order_id = m.order_id
LEFT JOIN o2o_sys_syscode s on o.orderType = s.`value` and s.catalog = "OrderType"
LEFT JOIN o2o_members om on o.member_id = om.member_id
LEFT JOIN o2o_account oa on o.member_id = oa.linked_id
LEFT JOIN o2o_account_center oac on oa.id = oac.account_id
LEFT JOIN (SELECT order_id,order_status,syncstatus from o2o_order_sync_records where order_status = '500' or order_status = '501' GROUP BY order_id) r
on o.order_id = r.order_id
LEFT JOIN (SELECT order_id,order_status,syncstatus from o2o_order_sync_records where order_status in (-1,0) GROUP BY order_id) s
on o.order_id = s.order_id
where o.shipping_id = 1 and o.`status` in (-1,0) and (s.syncstatus != 1 or (s.syncstatus = 1 and r.order_status is not null and r.syncstatus != 1))
GROUP BY o.order_id
)
union (select o.order_id,o.status,oac.user_id,
h.order_status,h.syncstatus, MAX(h.synctime) as lastsystime,
o.payment,o.createtime,case when o.shipping_id = 1 then 1 else 0 end as shipping_id,
o.is_tax,o.tax_company,o.delivery_time,o.is_delivery,o.memo,o.captcha,o.final_amount,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName,
p.pharm_short_name as pharmacy_name,o.pharmacy_id,p.external_id as storeId,p.chain_branch_id,o.cost_item,o.total_amount,o.cost_freight,
(o.pmt_amount+o.discount_amount) as pmt_amount,o.ship_name,o.ship_mobile,o.ship_addr,o.ship_area,o.logistics_platype,
o.province,o.city,o.country,m.promCd,
case when m.promote_code is null THEN om.inviteCode
when m.promote_code='' THEN om.inviteCode
else m.promote_code end as promote_code,
case when r.order_status is null THEN 0 else 1 end as isupdateorder
from o2o_orders o
LEFT JOIN o2o_order_sync_records h on o.order_id = h.order_id
LEFT JOIN o2o_pharmacy p on o.pharmacy_id=p.pharmacy_id
LEFT JOIN o2o_order_property m on o.order_id = m.order_id
LEFT JOIN o2o_sys_syscode s on o.orderType = s.`value` and s.catalog = "OrderType"
LEFT JOIN o2o_members om on o.member_id = om.member_id
LEFT JOIN o2o_account oa on o.member_id = oa.linked_id
LEFT JOIN o2o_account_center oac on oa.id = oac.account_id
LEFT JOIN (SELECT order_id,order_status,syncstatus from o2o_order_sync_records where order_status = '500' or order_status = '501' GROUP BY order_id) r
on o.order_id = r.order_id
LEFT JOIN (SELECT order_id,order_status,syncstatus from o2o_order_sync_records where order_status in (-1,0) GROUP BY order_id) s
on o.order_id = s.order_id
LEFT JOIN (SELECT order_id,order_status,syncstatus from o2o_order_sync_records where order_status in (3,5) GROUP BY order_id) x
on o.order_id = x.order_id
where o.shipping_id = 1 and o.`status` in (3,5) and s.syncstatus = 1 and x.syncstatus != 1
GROUP BY o.order_id
)
union
(select o.order_id,o.status,oac.user_id,
h.order_status,h.syncstatus, MAX(h.synctime) as lastsystime,
o.payment,o.createtime,case when o.shipping_id = 1 then 1 else 0 end as shipping_id,
o.is_tax,o.tax_company,o.delivery_time,o.is_delivery,o.memo,o.captcha,o.final_amount,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName,
p.pharm_short_name as pharmacy_name,o.pharmacy_id,p.external_id as storeId,p.chain_branch_id,o.cost_item,o.total_amount,o.cost_freight,
(o.pmt_amount+o.discount_amount) as pmt_amount,o.ship_name,o.ship_mobile,o.ship_addr,o.ship_area,o.logistics_platype,
o.province,o.city,o.country,m.promCd,
case when m.promote_code is null THEN om.inviteCode
when m.promote_code='' THEN om.inviteCode
else m.promote_code end as promote_code,
case when r.order_status is null THEN 0 else 1 end as isupdateorder
from o2o_orders o
LEFT JOIN o2o_order_sync_records h on o.order_id = h.order_id
LEFT JOIN o2o_pharmacy p on o.pharmacy_id=p.pharmacy_id
LEFT JOIN o2o_order_property m on o.order_id = m.order_id
LEFT JOIN o2o_sys_syscode s on o.orderType = s.`value` and s.catalog = "OrderType"
LEFT JOIN o2o_members om on o.member_id = om.member_id
LEFT JOIN o2o_account oa on o.member_id = oa.linked_id
LEFT JOIN o2o_account_center oac on oa.id = oac.account_id
LEFT JOIN (SELECT order_id,order_status from o2o_order_sync_records where order_status = '500' or order_status = '501' GROUP BY order_id) r
on o.order_id = r.order_id
where h.syncstatus != 1
and o.shipping_id in (3,7) and (o.`status` = 2 or o.`status` = 6)
GROUP BY o.order_id
)
) a where 1=1
<if test="vo.chainBranchIds != null ">
and a.chain_branch_id in
<foreach item="item" index="index" collection="vo.chainBranchIds" open="(" separator="," close=")">
#{item}
</foreach>
</if>
order by a.createtime desc
</select>
<select id="selectOrderByErpRules" parameterType="com.ehaoyao.payment.entity.EDIExpressOrderVO"
resultMap="erpOrderResult">
select a.* from(
select o.order_id,o.status,oac.user_id,
o.payment,o.createtime,case when o.shipping_id = 1 then 1 else 0 end as shipping_id,
o.is_tax,o.tax_company,o.delivery_time,o.is_delivery,o.memo,o.captcha,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName,
p.pharm_short_name as pharmacy_name,o.pharmacy_id,p.external_id as storeId,p.chain_branch_id,o.cost_item,o.total_amount,o.cost_freight,
(o.pmt_amount+o.discount_amount) as pmt_amount,o.ship_name,o.ship_mobile,o.ship_addr,o.ship_area,
o.province,o.city,o.country,m.promCd,
case when m.promote_code is null THEN om.inviteCode
when m.promote_code='' THEN om.inviteCode
else m.promote_code end as promote_code,o.final_amount,
case when r.order_status is null THEN 0 else 1 end as isupdateorder,h.systatus
from o2o_orders o
LEFT JOIN (SELECT a.order_id,COUNT(*) as b,case when MIN(a.syncstatus) < 1 then 0 else 1 end as systatus
from o2o_order_sync_records a GROUP BY a.order_id) h
on o.order_id = h.order_id
LEFT JOIN o2o_pharmacy p on o.pharmacy_id=p.pharmacy_id
LEFT JOIN o2o_order_property m on o.order_id = m.order_id
LEFT JOIN o2o_sys_syscode s on o.orderType = s.`value` and s.catalog = "OrderType"
LEFT JOIN o2o_members om on o.member_id = om.member_id
LEFT JOIN o2o_account oa on o.member_id = oa.linked_id
LEFT JOIN o2o_account_center oac on oa.id = oac.account_id
LEFT JOIN (SELECT order_id,order_status from o2o_order_sync_records where order_status = '500' or order_status = '501' GROUP BY order_id) r
on o.order_id = r.order_id
where 1=1 and o.shipping_id in (1,3,7)
<if test="externalId!=null and externalId != '' ">
and p.external_id=#{externalId}
</if>
<if test="chainBranchId !=null and chainBranchId != '' ">
and p.chain_branch_id=#{chainBranchId}
</if>
<if
test="(start_date != null and start_date != '' ) and (end_date ==null or end_date =='' ) ">
<![CDATA[and o.createtime >= str_to_date(#{start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(start_date == null or start_date == '' ) and (end_date !=null and end_date !='' ) ">
<![CDATA[and o.createtime <= str_to_date(#{end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(start_date != null and start_date != '' ) and (end_date !=null and end_date !='' ) ">
<![CDATA[and o.createtime between str_to_date(#{start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="orderId !=null and orderId !='' ">
and o.order_id = #{orderId}
</if>
<if test="shipName !=null and shipName !='' ">
and o.ship_name = #{shipName}
</if>
<if test="shipMobile !=null and shipMobile !='' ">
and o.ship_mobile = #{shipMobile}
</if>
order by o.createtime desc
) a
where 1=1
<if test="syncStatus !=null and syncStatus !='' ">
and a.systatus = #{syncStatus}
</if>
</select>
<resultMap type="com.ehaoyao.payment.entity.Orders" id="BaseResultMap">
<id column="order_id" property="orderId" />
<result column="member_id" property="memberId" />
<result column="status" property="status" />
<result column="pharmacy_id" property="pharmacyId" />
<result column="pharmacy_name" property="pharmacyName" />
<result column="contactmobie" property="contactmobie" />
<result column="ok_pharmacy" property="okPharmacy" />
<result column="pay_status" property="payStatus" />
<result column="is_delivery" property="isDelivery" />
<result column="shipping_id" property="shippingId" />
<result column="shipping" property="shipping" />
<result column="payment" property="payment" />
<result column="weight" property="weight" />
<result column="captcha" property="captcha" />
<result column="itemnum" property="itemnum" />
<result column="delivery_time" property="deliveryTime" />
<result column="createtime" property="createtime" />
<result column="ship_name" property="shipName" />
<result column="ship_area" property="shipArea" />
<result column="province" property="province" />
<result column="city" property="city" />
<result column="country" property="country" />
<result column="ship_addr" property="shipAddr" />
<result column="ship_zip" property="shipZip" />
<result column="ship_tel" property="shipTel" />
<result column="ship_email" property="shipEmail" />
<result column="ship_mobile" property="shipMobile" />
<result column="cost_item" property="costItem" />
<result column="is_tax" property="isTax" />
<result column="tax_company" property="taxCompany" />
<result column="cost_freight" property="costFreight" />
<result column="score_g" property="scoreG" />
<result column="total_amount" property="totalAmount" />
<result column="pmt_amount" property="pmtAmount" />
<result column="final_amount" property="finalAmount" />
<result column="pmtAount" property="pmtAount"/>
<result column="countAount" property="countAount"/>
<result column="discount_amount" property="discountAmount"/>
<result column="memo" property="memo" />
<result column="last_change_time" property="lastChangeTime" />
<result column="orderType" property="orderType" />
<result column="ordertypeName" property="ordertypeName" />
<!--
<result column="statusIds" property="statusIds" />
-->
<result column="is_group" property="isGroup" />
<result column="activity_id" property="activityId" />
<result column="returnStatus" property="returnStatus" />
<result column="category_id" property="categoryId"/>
<result column="storeId" property="storeId" jdbcType="VARCHAR" />
<result column="inviteCode" property="inviteCode" jdbcType="VARCHAR" />
<result column="isGift" property="isGift"/>
<result column="isGifts" property="isGifts"/>
<result column="member_mac" property="memberMac"/>
<result column="is_retention" property="isRetention"/>
<result column="couponsType" property="couponsType"/>
<result column="isupdateorder" property="isupdateorder"/>
<result column="reason_text" property="reasonText"/>
<collection property="orderDetails" column="order_id"
select="com.ehaoyao.payment.dao.OrderItemsDao.selectPharmacyOrderDetailByOrderId" />
</resultMap>
<resultMap type="com.ehaoyao.payment.entity.OrderVO"
id="BaseResultMapVO" extends="BaseResultMap">
<result column="payTime" property="paymentTime" />
</resultMap>
<resultMap id="ResultOrderVO" type="com.ehaoyao.payment.entity.PharmacyOrderVO"
extends="BaseResultMap">
<result column="name" property="memberName" jdbcType="VARCHAR" />
<result column="shipping_name" property="shippingName" jdbcType="VARCHAR" />
<result column="expressNo" property="expressNo"/>
<result column="expressType" property="expressType"/>
</resultMap>
<!-- /** * resultMerchaindiseStoreVOMap 适用于按订单查询药品 * * @author Justin.Lan
* @date 2013-11-26 下午16:20:22 "src/main/java/com/qumaiyao/pharmacy/vo/MerchaindiseStoreVO.java"
*/ -->
<resultMap type="com.ehaoyao.payment.entity.MerchaindiseStoreVO"
id="resultMerchaindiseStoreVOMap">
<id column="order_id" property="orderId" />
<result column="bn" property="bn" />
<result column="pharmacy_id" property="pharmacyId" />
<result column="nums" property="nums" />
<result column="store" property="store" />
<result column="occ_store" property="occ_store" />
<result column="giftStore" property="giftStore" />
<result column="occ_giftStore" property="occGiftStore" />
<result column="isGift" property="isGift" />
<result column="activity_id" property="activity_id" />
<result column="deflatActivityId" property="deflatActivityId" />
<result column="purchase_id" property="purchaseId" />
</resultMap>
<!--** * resultOrderMemeberPointVO 适用于会员积分操作 * * @author Justin.Lan * @date
2014-08-07 14:20:22 */ -->
<resultMap type="com.ehaoyao.payment.entity.OrderMemeberPointVO"
id="resultOrderMemeberPointVO">
<id column="order_id" property="orderId" />
<result column="status" property="status" />
<result column="score_g" property="score_g" />
<result column="member_id" property="member_id" />
<result column="final_amount" property="finalAmount" />
</resultMap>
<!-- 根据订单编号查询信息 -->
<select id="selectOrderInfo" resultMap="ResultOrderVO"
parameterType="string">
select * from (
select a.order_id, a.status, a.ship_name, a.ship_addr,
a.ship_mobile,a.payment,
b.username, a.orderType,a.ship_tel,a.discount_amount,
a.shipping,a.final_amount,
a.cost_item, a.pmt_amount, (ifnull(a.discount_amount,0) + ifnull(a.pmt_amount,0)) as pmtAount , a.cost_freight,
<![CDATA[if(ifnull(a.final_amount,0) < 0,0,a.final_amount) as countAount]]>
from
o2o_orders a, o2o_account b
where a.member_id = b.linked_id and
a.order_id =
#{orderId,jdbcType=VARCHAR}
and b.user_type = 0
) aa
left join o2o_express_log bb on aa.order_id=bb.orderId group by aa.order_id
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap"
parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from o2o_orders o
where order_id = #{orderId,jdbcType=VARCHAR}
</select>
<select id="selectOrderById" resultMap="BaseResultMap"
parameterType="java.lang.String">
select (select p.external_id from o2o_pharmacy p where
p.pharmacy_id = a.pharmacy_id) as
storeId,a.inviteCode,a.pharmacy_id,a.order_id,a.status,a.shipping,a.itemnum,a.createtime,a.is_retention,
a.cost_item,a.is_tax,a.tax_company,a.total_amount,(a.pmt_amount+a.discount_amount) as pmt_amount,a.final_amount,a.memo,a.cost_freight,a.shipping_id,a.is_delivery,a.delivery_time,a.is_group,
a.last_change_time,
case when m.order_status is null then 0 else 1 end as isupdateorder,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName
,a.ship_name,a.ship_mobile,a.ship_addr,a.captcha,a.payment,a.is_retention
,e.total_mailno as mailno ,(select sum(t.nums) from o2o_order_items t
where t.order_id = a.order_id) as goodsNums from (select
a.*,m.inviteCode from o2o_orders a left join o2o_members m on
a.member_id = m.member_id) a left join o2o_express_success e on
a.order_id = e.orderid
LEFT JOIN (SELECT DISTINCT order_id,order_status from o2o_order_sync_records where (order_status = '500' or order_status = '501') and syncstatus != '1') m on a.order_id = m.order_id
LEFT JOIN (select * from o2o_sys_syscode where catalog = "OrderType") s on a.orderType = s.`value`
where a.order_id = #{order_id,jdbcType=VARCHAR}
</select>
<!-- /** * selectMedStoreByOrderId 适用于按订单ID查询药店药品库存 * * @author Justin.Lan
* @date 2014-08-07 10:20:22 */ -->
<select id="selectMedStoreByOrderId" parameterType="String"
resultMap="resultMerchaindiseStoreVOMap">
select
a.order_id,b.bn,a.pharmacy_id,b.nums,c.store,c.occ_store,c.giftStore,c.occ_giftStore,b.isGift,b.deflatActivityId,b.purchase_id,b.activity_id,b.product_id
as goods_id from o2o_orders a
inner join o2o_order_items b on
a.order_id =b.order_id
left join o2o_pharmacy_goods c on a.pharmacy_id
= c.pharmacy_id and
b.product_id=c.goods_id
where a.order_id =
#{orderId,jdbcType=VARCHAR}
</select>
<!-- /** * selectOrderPointByOrderId 适用于按订单ID查询订单积分 * * @author Justin.Lan
* @date 2014-08-07 10:20:22 */ -->
<select id="selectOrderPointByOrderId" parameterType="String"
resultMap="resultOrderMemeberPointVO">
select a.order_id,status,score_g,member_id,final_amount from
o2o_orders a
where a.order_id = #{orderId,jdbcType=VARCHAR}
</select>
<!-- /** * selectPharmacyIdByOrderId 根据订单查询药店ＩＤ * * @author Justin.Lan *
@date 2014-08-07 10:20:22 */ -->
<select id="selectPharmacyIdByOrderId" resultType="java.lang.Long"
parameterType="String">
select pharmacy_id from o2o_orders where order_id =
#{orderId,jdbcType=VARCHAR}
</select>
<!-- /** * selectBeginOrdersCount适用于统计为初始状态或配货的-1,0 订单数 * * @author Justin.Lan
* @date 2014-08-01 下午15:24:29 */ -->
<select id="selectBeginOrdersCount" parameterType="java.lang.Long"
resultType="java.lang.Integer">
select count(order_id) from o2o_orders a
where 1=1
and
a.status = -1
and (
(
(a.shipping = '客户自提' or a.shipping = '到店自提')
and
(<![CDATA[TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(a.createtime, '%Y-%m-%d %H:%i')),(DATE_FORMAT(now(), '%Y-%m-%d %H:%i')))<= 1 ]]>)
)
or
(a.shipping != '客户自提' and a.shipping != '到店自提')
)
and a.pharmacy_id =
#{pharmacyId,jdbcType=BIGINT}
and a.is_group = 0
</select>
<!-- /** * selectPickingOrdersCount适用于统计客户自提订单数 * * @author Justin.Lan *
@date 2014-08-01 下午15:24:29 */ -->
<select id="selectPickingOrdersCount" parameterType="java.lang.Long"
resultType="java.lang.Integer">
select count(order_id) from o2o_orders a
where 1=1
and
a.status = 1 and (a.shipping ='客户自提' or a.shipping = '到店自提')
and a.pharmacy_id =
#{pharmacyId,jdbcType=BIGINT}
and a.is_group = 0
</select>
<!-- /** * selectDiliveryOrders适用于统计药店配送订单数 * * @author Justin.Lan * @date
2014-08-01 下午15:24:29 */ -->
<select id="selectDiliveryOrdersCount" parameterType="java.lang.Long"
resultType="java.lang.Integer">
select count(order_id) from o2o_orders a
where 1=1
and
a.status = 1 and a.shipping_id in (3,6,7)
and a.pharmacy_id =
#{pharmacyId,jdbcType=BIGINT}
and a.is_group = 0
</select>
<!-- /** * listPageOrderByPharmacy适用于药店订单查询（支持分页） * * @author Justin.Lan
* @date 2014-04-26 上午09:31:22 */ -->
<select id="listPageOrderByPharmacy" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="BaseResultMap">
select GROUP_CONCAT(i.isGift ORDER BY i.isGift ASC) as isGifts,pharmacy_id,a.order_id,status,shipping,itemnum,createtime,
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,cost_freight,shipping_id,is_delivery,delivery_time,is_group,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,a.payment,captcha,status
as operation,a.couponsType,a.discount_amount,(ifnull(a.discount_amount,0) + ifnull(a.pmt_amount,0)) as pmtAount from o2o_orders a
left join o2o_order_items i on i.order_id=a.order_id
where 1=1
<if test="pharmacyOrder.isGift ==1">
and a.order_id in (select order_id from o2o_order_items where isGift=1)
</if>
<if test="pharmacyOrder.isGift ==2">
and a.order_id not in (select order_id from o2o_order_items where isGift=1)
</if>
<if
test="pharmacyOrder.pharmacyId != null and pharmacyOrder.pharmacyId != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacyId}
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId != '' ">
<![CDATA[and a.order_id like CONCAT('%', #{pharmacyOrder.orderId}, '%') ]]>
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.shipMobile != null and pharmacyOrder.shipMobile != '' ">
<![CDATA[and a.ship_mobile like CONCAT('%', #{pharmacyOrder.shipMobile}, '%') ]]>
</if>
<if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if>
<if test="(pharmacyOrder.couponsType != null and pharmacyOrder.couponsType != '')">
<![CDATA[and a.couponsType like CONCAT('%', #{pharmacyOrder.couponsType}, '%')]]>
</if>
<if test="pharmacyOrder.status != null and pharmacyOrder.status > 0 ">
and a.status = #{pharmacyOrder.status}
</if>
<if test="pharmacyOrder.status != null and pharmacyOrder.status < 0 ">
and (a.status = -1 or a.status = 0)
</if>
<if
test="pharmacyOrder.payment ==2">
and a.payment = 0
</if>
<if
test="pharmacyOrder.payment ==1 ">
and a.payment = 1
</if>
<if
test="pharmacyOrder.shippingId != null and pharmacyOrder.shippingId != 0 ">
and a.shipping_id IN(3,6,7)
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
<choose>
<when test="pharmacyOrder.shipping == '药店配送' ">
<![CDATA[and a.shipping != '客户自提' and a.shipping != '到店自提']]>
</when>
<when test="pharmacyOrder.shipping == '药急送' ">
and a.shipping = '药急送'
</when>
<when test="pharmacyOrder.shipping == '隔日达' ">
and a.shipping = '隔日达'
</when>
<when test="pharmacyOrder.shipping == '夜间送' ">
and a.shipping = '夜间送'
</when>
<otherwise>
and (a.shipping = #{pharmacyOrder.shipping} or a.shipping = '到店自提')
</otherwise>
</choose>
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
<![CDATA[and (a.ship_name like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.ship_mobile like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.order_id like CONCAT('%', #{pharmacyOrder.keywords}, '%') ) ]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.isGroup == null">
and a.is_group = 0
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.ordersIds!=null">
and a.order_id in
<foreach item="item" index="index" collection="pharmacyOrder.ordersIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="1==1">
GROUP BY order_id
</if>
<if test="pharmacyOrder.orderBy != null and pharmacyOrder.orderBy != '' ">
order by ${pharmacyOrder.orderBy}
</if>
<if test="pharmacyOrder.sort != null and pharmacyOrder.sort != '' ">
${pharmacyOrder.sort}
</if>
</select>
<!-- 查询药店订单，不含明细 -->
<select id="listPageOrderByPharmacy2" parameterType="com.ehaoyao.payment.entity.ExpressOrderVO"
resultMap="resultOrders">
select
a.reason_text,a.is_retention,a.storeId,a.pharmacy_id,a.inviteCode,a.order_id,a.status,a.shipping,a.itemnum,a.createtime,
a.cost_item,a.is_tax,a.tax_company,a.total_amount,(a.pmt_amount+a.discount_amount) as pmt_amount,a.final_amount,a.memo,a.cost_freight,a.shipping_id,a.is_delivery,a.delivery_time,a.is_group,
a.last_change_time,
case when m.order_status is null then 0 else 1 end as isupdateorder,
case when (s.`value` > 0 and 5 > s.`value` ) THEN '好药师预购单' else s.`code` end as ordertypeName
,a.ship_name,a.ship_mobile,a.ship_addr,a.captcha,a.payment
,e.total_mailno as mailno,(select sum(t.nums) from o2o_order_items t
where t.order_id = a.order_id) as goodsNums from
(select a.*
,p.external_id as storeId ,p.category_id,p.chain_branch_id as chainBranchId from (select a.*,m.inviteCode
from o2o_orders a,o2o_members m where a.member_id = m.member_id
<if test="pharmacyOrder.status != null and pharmacyOrder.status !=-2">
and a.status = #{pharmacyOrder.status}
</if>
<!-- 如果要点传-2（未付款）则不查询任何结果（目前status无-3） -->
<if test="pharmacyOrder.status ==-2">
and a.status ==-3
</if>
<if test="pharmacyOrder.status==null or pharmacyOrder.status == ''">
and a.status !=-2
</if>
<if test="pharmacyOrder.payment != null">
and a.payment = #{pharmacyOrder.payment}
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
) a
left join o2o_pharmacy p on p.pharmacy_id = a.pharmacy_id ) a left
join o2o_express_success e on a.order_id = e.orderid
LEFT JOIN (SELECT DISTINCT order_id,order_status from o2o_order_sync_records where (order_status = '500' or order_status = '501')and syncstatus != '1') m on a.order_id = m.order_id
LEFT JOIN (select * from o2o_sys_syscode where catalog = "OrderType") s on a.orderType = s.`value`
where 1=1
<if test="pharmacyOrder.inviteCode== 1">
and (a.inviteCode is not null and a.inviteCode !=' ')
</if>
<if test="pharmacyOrder.payment != null">
and a.payment = #{pharmacyOrder.payment}
</if>
<if test="pharmacyOrder.chainBranchId !=null and pharmacyOrder.chainBranchId != '' ">
and a.chainBranchId=#{pharmacyOrder.chainBranchId}
</if>
<if test="pharmacyOrder.storeId!=null and pharmacyOrder.storeId != '' ">
and a.storeId=#{pharmacyOrder.storeId}
</if>
<if test="pharmacyOrder.inviteCode== 2">
and (a.inviteCode is null or a.inviteCode =' ')
</if>
<if test="pharmacyOrder.isRetention != null">
and a.is_retention = #{pharmacyOrder.isRetention}
</if>
<if
test="pharmacyOrder.pharmacyId != null and pharmacyOrder.pharmacyId != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacyId}
</if>
<if test="pharmacyOrder.orderIdArray!= null">
and a.order_id in
<foreach item="item" index="index" collection="pharmacyOrder.orderIdArray"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId!=''">
and a.order_id like '%${pharmacyOrder.orderId}%'
</if>
<if test="pharmacyOrder.categoryId != null">
and a.category_id = #{pharmacyOrder.categoryId}
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.shipMobile != null and pharmacyOrder.shipMobile != '' ">
<![CDATA[and a.ship_mobile like CONCAT('%', #{pharmacyOrder.shipMobile}, '%') ]]>
</if>
<!-- <if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if> -->
<if test="pharmacyOrder.orderTypeArray!= null">
and a.orderType in
<foreach item="item" index="index" collection="pharmacyOrder.orderTypeArray"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.status != null and pharmacyOrder.status !=-2">
and a.status = #{pharmacyOrder.status}
</if>
<!-- 如果要点传-2（未付款）则不查询任何结果（目前status无-3） -->
<if test="pharmacyOrder.status ==-2">
and a.status ==-3
</if>
<if test="pharmacyOrder.status==null or pharmacyOrder.status == ''">
and a.status !=-2
</if>
<if test="pharmacyOrder.mailnoArray!=null">
and e.total_mailno in
<foreach item="item" index="index" collection="pharmacyOrder.mailnoArray"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.mailno != null and pharmacyOrder.mailno !=''">
and e.total_mailno like '%${pharmacyOrder.mailno}%'
</if>
<if test="pharmacyOrder.shippingIds!=null">
and a.shipping_id in
<foreach item="item" index="index" collection="pharmacyOrder.shippingIds" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
<choose>
<when test="pharmacyOrder.shipping == '药店配送' ">
<![CDATA[and a.shipping != '客户自提']]>
</when>
<otherwise>
and a.shipping = #{pharmacyOrder.shipping}
</otherwise>
</choose>
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
<![CDATA[and (a.ship_name like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.ship_mobile like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.order_id like CONCAT('%', #{pharmacyOrder.keywords}, '%') ) ]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.orderBy != null and pharmacyOrder.orderBy != '' ">
order by ${pharmacyOrder.orderBy}
</if>
<if test="pharmacyOrder.sort != null and pharmacyOrder.sort != '' ">
${pharmacyOrder.sort}
</if>
</select>
<!-- /** * listPageOrderByPharmacyAndOprationTime适用于药店已守成订单查询（支持分页） */ -->
<select id="listPageOrderByPharmacyAndOprationTime"
parameterType="com.ehaoyao.payment.entity.Orders" resultMap="BaseResultMap">
select a.returnStatus,a.pharmacy_id,GROUP_CONCAT(i.isGift ORDER BY i.isGift ASC) AS isGifts,a.order_id,status,shipping,itemnum,createtime,
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,a.payment,captcha,status
as operation, c.acttime as finishOrderTime,op.category_id,a.couponsType,a.discount_amount,(ifnull(a.discount_amount,0) + ifnull(a.pmt_amount,0)) as pmtAount
from o2o_orders a inner join
o2o_order_log c on a.order_id = c.order_id
LEFT JOIN o2o_order_items i ON i.order_id = a.order_id
inner join o2o_pharmacy op on op.pharmacy_id = a.pharmacy_id
where 1=1
<if test="pharmacyOrder.isGift==1">
and a.order_id in (select order_id from o2o_order_items where isGift=1)
</if>
<if test="pharmacyOrder.isGift==2">
and a.order_id in (select order_id from o2o_order_items where isGift=1)
</if>
<if
test="pharmacyOrder.pharmacyId != null and pharmacyOrder.pharmacyId != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacyId}
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId != '' ">
<![CDATA[and a.order_id like CONCAT('%', #{pharmacyOrder.orderId}, '%') ]]>
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if>
<if test="(pharmacyOrder.couponsType != null and pharmacyOrder.couponsType != '')">
<![CDATA[and a.couponsType like CONCAT('%', #{pharmacyOrder.couponsType}, '%')]]>
</if>
<if
test="pharmacyOrder.status != null and pharmacyOrder.status != 0 and pharmacyOrder.status != 2">
and a.status = #{pharmacyOrder.status}
</if>
<if
test="pharmacyOrder.status != null and pharmacyOrder.status != 0 and pharmacyOrder.status = 2">
and (a.status = #{pharmacyOrder.status} or a.status = 6)
</if>
<if
test="pharmacyOrder.shippingId != null and pharmacyOrder.shippingId != 0 ">
and a.shipping_id = #{pharmacyOrder.shippingId}
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
<choose>
<when test="pharmacyOrder.shipping == '药店配送' ">
<![CDATA[and a.shipping != '客户自提' and a.shipping != '到店自提']]>
</when>
<otherwise>
and (a.shipping = #{pharmacyOrder.shipping} or a.shipping = '到店自提')
</otherwise>
</choose>
</if>
<if
test="pharmacyOrder.payment ==2">
and a.payment = 0
</if>
<if
test="pharmacyOrder.payment ==1 ">
and a.payment = 1
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
<![CDATA[and (a.ship_name like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.ship_mobile like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.order_id like CONCAT('%', #{pharmacyOrder.keywords}, '%') ) ]]>
</if>
<if test="pharmacyOrder.behavior != null and pharmacyOrder.behavior != '' ">
and c.behavior = #{pharmacyOrder.behavior}
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.createBeginTime != null and pharmacyOrder.createBeginTime != '' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.createBeginTime},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.createEndTime != null and pharmacyOrder.createEndTime != '' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.createEndTime},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.acttimeBeginTime != null and pharmacyOrder.acttimeBeginTime != '' ) ">
<![CDATA[and c.acttime >= str_to_date(#{pharmacyOrder.acttimeBeginTime},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.acttimeEndTime != null and pharmacyOrder.acttimeEndTime != '' ) ">
<![CDATA[and c.acttime <= str_to_date(#{pharmacyOrder.acttimeEndTime},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.isGroup == null">
and a.is_group = 0
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="1==1">
GROUP BY order_id
</if>
<if test="pharmacyOrder.orderBy != null and pharmacyOrder.orderBy != '' ">
order by ${pharmacyOrder.orderBy}
</if>
<if test="pharmacyOrder.sort != null and pharmacyOrder.sort != '' ">
${pharmacyOrder.sort}
</if>
</select>
<select id="listPageOrderByPharmacyAddFinishOrderTime"
parameterType="com.ehaoyao.payment.entity.Orders" resultMap="BaseResultMap">
select a.pharmacy_id,a.order_id,status,shipping,itemnum,createtime,
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,captcha,status
as operation,
c.acttime as finishOrderTime from o2o_orders a
inner join
o2o_order_log c on a.order_id = c.order_id and c.behavior = '完成'
where
1=1
<if
test="pharmacyOrder.pharmacyId != null and pharmacyOrder.pharmacyId != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacyId}
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId != '' ">
<![CDATA[and a.order_id like CONCAT('%', #{pharmacyOrder.orderId}, '%') ]]>
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if>
<if
test="pharmacyOrder.status != null and pharmacyOrder.status != 0 and pharmacyOrder.status != 2">
and a.status = #{pharmacyOrder.status}
</if>
<if
test="pharmacyOrder.status != null and pharmacyOrder.status != 0 and pharmacyOrder.status = 2">
and (a.status = #{pharmacyOrder.status} or a.status = 6)
</if>
<if
test="pharmacyOrder.shippingId != null and pharmacyOrder.shippingId != 0 ">
and a.shipping_id = #{pharmacyOrder.shippingId}
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
and a.shipping = #{pharmacyOrder.shipping}
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
and (a.ship_name like '%'||#{pharmacyOrder.keywords}'%' or
a.ship_tel
like '%'||#{pharmacyOrder.keywords}'%' or a.order_id like
'%'||#{pharmacyOrder.keywords}'%' )
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.isGroup == null">
and a.is_group = 0
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.ordersIds!=null">
and a.order_id in
<foreach item="item" index="index" collection="pharmacyOrder.ordersIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.orderBy != null and pharmacyOrder.orderBy != '' ">
order by ${pharmacyOrder.orderBy}
</if>
<if test="pharmacyOrder.sort != null and pharmacyOrder.sort != '' ">
${pharmacyOrder.sort}
</if>
</select>
<select id="listPageCloseOrderByPharmacy" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="BaseResultMap">
<![CDATA[select * from (
select pharmacy_id,GROUP_CONCAT(i.isGift ORDER BY i.isGift ASC) as isGifts,a.order_id,status,shipping,a.payment,itemnum,createtime,orderType
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,a.couponsType,a.discount_amount,
captcha,status as operation ,'' as behavior from o2o_orders a
left join o2o_order_items i on i.order_id=a.order_id
where status >3 and status <6]]>
<if
test="pharmacyOrder.pharmacyId != null and pharmacyOrder.pharmacyId != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacyId}
</if>
<if test="pharmacyOrder.isGroup == null">
and a.is_group = 0
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.ordersIds!=null">
and a.order_id in
<foreach item="item" index="index" collection="pharmacyOrder.ordersIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
<choose>
<when test="pharmacyOrder.shipping == '药店配送' ">
<![CDATA[and a.shipping != '客户自提' and a.shipping != '到店自提']]>
</when>
<otherwise>
and (a.shipping = #{pharmacyOrder.shipping} or a.shipping = '到店自提')
</otherwise>
</choose>
</if>
<if test="1==1">
GROUP BY order_id
</if>
<![CDATA[ union
select l.pharmacy_id,GROUP_CONCAT(i.isGift ORDER BY i.isGift ASC) as isGifts,l.order_id,status,shipping,a.payment,itemnum,createtime,orderType
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,a.couponsType,a.discount_amount,
captcha,status as operation ,l.behavior from o2o_orders a
inner join o2o_order_log l on l.order_id=a.order_id
left join o2o_order_items i on i.order_id=a.order_id
where l.behavior='驳回' and l.order_id=#{pharmacyOrder.pharmacyId} GROUP BY order_id) a where 1=1]]>
<if test="pharmacyOrder.isGift==1">
and a.order_id in (select order_id from o2o_order_items where isGift=1)
</if>
<if test="pharmacyOrder.isGift==2">
and a.order_id in (select order_id from o2o_order_items where isGift=1)
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId != '' ">
<![CDATA[and a.order_id like CONCAT('%', #{pharmacyOrder.orderId}, '%') ]]>
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if>
<if test="(pharmacyOrder.couponsType != null and pharmacyOrder.couponsType != '')">
<![CDATA[and a.couponsType like CONCAT('%', #{pharmacyOrder.couponsType}, '%')]]>
</if>
<if
test="pharmacyOrder.payment ==2">
and a.payment = 0
</if>
<if
test="pharmacyOrder.payment ==1 ">
and a.payment = 1
</if>
<if
test="pharmacyOrder.closedStatus !=null and pharmacyOrder.closedStatus != ''">
and a.status = #{pharmacyOrder.closedStatus}
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
<![CDATA[and (a.ship_name like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.ship_mobile like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.order_id like CONCAT('%', #{pharmacyOrder.keywords}, '%') ) ]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date ==null or pharmacyOrder.end_date =='' ) ">
<![CDATA[and a.createtime >= str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') ]]>
</if>
<if
test="(pharmacyOrder.start_date == null or pharmacyOrder.start_date == '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime <= str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.ordersIds!=null">
and a.order_id in
<foreach item="item" index="index" collection="pharmacyOrder.ordersIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="pharmacyOrder.orderBy != null and pharmacyOrder.orderBy != '' ">
order by ${pharmacyOrder.orderBy}
</if>
<if test="pharmacyOrder.sort != null and pharmacyOrder.sort != '' ">
${pharmacyOrder.sort}
</if>
</select>
<!-- 药店订单高级搜索 -->
<select id="listPageOrderBySeach" parameterType="com.ehaoyao.payment.entity.Orders"
resultMap="BaseResultMap">
select pharmacy_id,order_id,status,shipping,itemnum,createtime,
cost_item,is_tax,tax_company,total_amount,pmt_amount,final_amount,memo,
last_change_time,orderType,a.ship_name,a.ship_mobile,a.ship_addr,captcha,status
as operation from o2o_orders a
where 1=1
<if
test="pharmacyOrder.pharmacy_id != null and pharmacyOrder.pharmacy_id != 0 ">
and a.pharmacy_id = #{pharmacyOrder.pharmacy_id}
</if>
<if test="pharmacyOrder.orderId != null and pharmacyOrder.orderId != '' ">
<![CDATA[and a.order_id like CONCAT('%', #{pharmacyOrder.orderId}, '%') ]]>
</if>
<if test="pharmacyOrder.shipName != null and pharmacyOrder.shipName != '' ">
<![CDATA[and a.ship_name like CONCAT('%', #{pharmacyOrder.shipName}, '%') ]]>
</if>
<if
test="pharmacyOrder.orderType != null and pharmacyOrder.orderType != 0 ">
and a.orderType = #{pharmacyOrder.orderType}
</if>
<if test="pharmacyOrder.status != null">
and a.status = #{pharmacyOrder.status}
</if>
<if
test="pharmacyOrder.shippingId != null and pharmacyOrder.shippingId != 0 ">
and a.shipping_id = #{pharmacyOrder.shippingId}
</if>
<if test="pharmacyOrder.shipping != null and pharmacyOrder.shipping != '' ">
and a.shipping = #{pharmacyOrder.shipping}
</if>
<if test="pharmacyOrder.keywords != null and pharmacyOrder.keywords!= ''">
<![CDATA[and (a.ship_name like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.ship_mobile like CONCAT('%', #{pharmacyOrder.keywords}, '%') or a.order_id like CONCAT('%', #{pharmacyOrder.keywords}, '%') ) ]]>
</if>
<if
test="(pharmacyOrder.start_date != null and pharmacyOrder.start_date != '' ) and (pharmacyOrder.end_date !=null and pharmacyOrder.end_date !='' ) ">
<![CDATA[and a.createtime between str_to_date(#{pharmacyOrder.start_date},'%Y-%m-%d %H:%i:%s') and str_to_date(#{pharmacyOrder.end_date},'%Y-%m-%d %H:%i:%s')]]>
</if>
<if test="pharmacyOrder.isGroup == null">
and a.is_group = 0
</if>
<if test="pharmacyOrder.isGroup!=null">
and a.is_group=#{pharmacyOrder.isGroup}
</if>
<if test="pharmacyOrder.orderBy!=null and pharmacyOrder.sort!=null">
order by #{pharmacyOrder.orderBy} #{pharmacyOrder.sort}
</if>
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from
o2o_orders
where order_id = #{order_id,jdbcType=BIGINT}
</delete>
<update id="updateByPrimaryKeySelective" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="memberId != null">
member_id = #{memberId,jdbcType=BIGINT},
</if>
<if test="status != null">
status = #{status,jdbcType=INTEGER},
</if>
<if test="pharmacyId != null">
pharmacy_id = #{pharmacyId,jdbcType=BIGINT},
</if>
<if test="okPharmacy != null">
ok_pharmacy = #{okPharmacy,jdbcType=INTEGER},
</if>
<if test="payStatus != null">
pay_status = #{payStatus,jdbcType=INTEGER},
</if>
<if test="isDelivery != null">
is_delivery = #{isDelivery,jdbcType=INTEGER},
</if>
<if test="shippingId != null">
shipping_id = #{shippingId,jdbcType=INTEGER},
</if>
<if test="shipping != null">
shipping = #{shipping,jdbcType=VARCHAR},
</if>
<if test="payment != null">
payment = #{payment,jdbcType=INTEGER},
</if>
<if test="weight != null">
weight = #{weight,jdbcType=DECIMAL},
</if>
<if test="captcha != null">
captcha = #{captcha,jdbcType=VARCHAR},
</if>
<if test="itemnum != null">
itemnum = #{itemnum,jdbcType=INTEGER},
</if>
<if test="deliveryTime != null">
delivery_time = #{deliveryTime,jdbcType=TIMESTAMP},
</if>
<if test="createtime != null">
createtime = #{createtime,jdbcType=TIMESTAMP},
</if>
<if test="shipName != null">
ship_name = #{shipName,jdbcType=VARCHAR},
</if>
<if test="shipArea != null">
ship_area = #{shipArea,jdbcType=VARCHAR},
</if>
<if test="shipAddr != null">
ship_addr = #{shipAddr,jdbcType=VARCHAR},
</if>
<if test="shipZip != null">
ship_zip = #{shipZip,jdbcType=VARCHAR},
</if>
<if test="shipTel != null">
ship_tel = #{shipTel,jdbcType=VARCHAR},
</if>
<if test="shipEmail != null">
ship_email = #{shipEmail,jdbcType=VARCHAR},
</if>
<if test="shipMobile != null">
ship_mobile = #{shipMobile,jdbcType=VARCHAR},
</if>
<if test="costItem != null">
cost_item = #{costItem,jdbcType=DECIMAL},
</if>
<if test="isTax != null">
is_tax = #{isTax,jdbcType=INTEGER},
</if>
<if test="taxCompany != null">
tax_company = #{taxCompany,jdbcType=VARCHAR},
</if>
<if test="costFreight != null">
cost_freight = #{costFreight,jdbcType=DECIMAL},
</if>
<if test="scoreG != null">
score_g = #{scoreG,jdbcType=DECIMAL},
</if>
<if test="totalAmount != null">
total_amount = #{totalAmount,jdbcType=DECIMAL},
</if>
<if test="pmtAmount != null">
pmt_amount = #{pmtAmount,jdbcType=DECIMAL},
</if>
<if test="finalAmount != null">
final_amount = #{finalAmount,jdbcType=DECIMAL},
</if>
<if test="memo != null">
memo = #{memo,jdbcType=VARCHAR},
</if>
<if test="lastChangeTime != null">
last_change_time = #{lastChangeTime,jdbcType=TIMESTAMP},
</if>
<if test="orderType != null">
orderType = #{orderType,jdbcType=INTEGER},
</if>
<if test="isNight != null">
isNight=#{isNight,jdbcType=INTEGER},
</if>
<if test="nightTime != null">
nightTime=#{nightTime,jdbcType=TIMESTAMP},
</if>
<if test="isRetention != null" >
is_retention = #{isRetention},
</if>
<if test="isPrescription!=null">
isPrescription = #{isPrescription},
</if>
</set>
where order_id = #{orderId,jdbcType=VARCHAR}
</update>
<!-- /** * updateOrderStatus 更新订单状态 * * @author Justin.Lan * @date 2014-07-09
下午15:20:22 */ -->
<update id="updateOrderStatus" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="status !=null ">
<choose>
<when test="status == -1 ">
status = -1,last_change_time=sysdate()
</when>
<when test="status == 0 ">
status = 0,last_change_time=sysdate()
</when>
<when test="status == 1 ">
status = 1,last_change_time=sysdate()
</when>
<when test="status == 2 ">
status = 2,last_change_time=sysdate()
</when>
<when test="status == 3 ">
status = 3,last_change_time=sysdate()
</when>
<when test="status == 4 ">
status = 4,last_change_time=sysdate()
</when>
<when test="status == 5 ">
status = 5,last_change_time=sysdate()
</when>
</choose>
</if>
</set>
where order_id = #{orderId}
<if test="status !=null ">
<choose>
<when test="status == -1 ">
and (status =-1 or status = 0 or status = 1 or
status = 3)
</when>
<when test="status == 0 ">
and status = -1
</when>
<when test="status == 1 ">
and (status = 0 or status =-1)
</when>
<when test="status == 2 ">
and status = 1
</when>
<when test="status == 3 ">
and (status =-1 or status = 0 or status = 1)
</when>
<when test="status == 4 ">
and status = 1
</when>
<when test="status == 5 ">
and (status = -1 or status = 0 or status = 3)
</when>
</choose>
</if>
</update>
<!-- /** * updateMedStore 更新药店药品库存 * * @author Justin.Lan * @date 2014-08-07
10:20:22 */ -->
<update id="updateMedStore" parameterType="com.ehaoyao.payment.entity.MerchaindiseStoreVO">
update o2o_pharmacy_goods
<set>
<if test="order_status !=null and order_status !='' ">
<choose>
<when test="order_status == 2 ">
store = store - #{nums},occ_store = occ_store -
#{nums},buy_count= buy_count + #{nums}
</when>
<when test="order_status == 3 ">
occ_store = occ_store - #{nums}
</when>
<when test="order_status == 4 ">
occ_store = occ_store - #{nums}
</when>
<when test="order_status == 5 ">
occ_store = occ_store - #{nums}
</when>
</choose>
</if>
</set>
where pharmacy_id=#{pharmacy_id} and bn =#{bn}
</update>
<!-- /** * updateActiveMedStore 更新团购药品库存 * * @author Justin.Lan * @date
2014-08-08 11:20:22 */ -->
<update id="updateActivityMedStore" parameterType="com.ehaoyao.payment.entity.MerchaindiseStoreVO">
update o2o_activity_buying
<set>
<if test="nums !=null and nums !='' ">
store = store + #{nums},
</if>
</set>
where activity_id=#{activity_id}
</update>
<!-- /** * updateMemberPoint 更新会员积分 * * @author Justin.Lan * @date 2014-08-07
11:20:22 */ -->
<update id="updateMemberPoint" parameterType="com.ehaoyao.payment.entity.OrderMemeberPointVO">
update o2o_members m ,o2o_account a
<set>
<if test="score_g !=null ">
m.point = point + #{score_g},
</if>
<if test="finalAmount !=null ">
m.experience = experience + #{finalAmount},
</if>
a.login_time=now(),
</set>
where m.member_id = #{member_id}
and m.member_id=a.linked_id
and
user_type=0
</update>
<!-- /** * insertPointHistory 插入会员历史积记录 * * @author Justin.Lan * @date 2014-08-08
11:20:22 */ -->
<insert id="insertPointHistory" parameterType="com.ehaoyao.payment.entity.OrderMemeberPointVO">
insert into
o2o_point_history(member_id,point,time,reason,related_id,type)
values
(#{member_id,jdbcType=INTEGER}, #{score_g,jdbcType=DECIMAL},
unix_timestamp(),'order_pay_get',#{orderId,jdbcType=BIGINT},2)
</insert>
<!-- 根据会员 id 查找订单（即：查询某个用户所有已完成的订单） -->
<select id="getOrdersByMemberId" parameterType="java.lang.Long"
resultMap="resultOrders">
select 'true' as QUERYID,
<include refid="Base_Column_List" />
from o2o_orders o
where member_id = #{memberId} and (status = 2 or
status = 6)
</select>
<!-- 根据会员 id 查找订单（即：查询某个用户所有在派送的订单） -->
<select id="getOrdersByMemberIdps" parameterType="java.lang.Long"
resultMap="resultOrders">
select *
from o2o_orders o
where member_id = #{memberId} and status = 1 and deleteFlag!=1
</select>
<!-- 根据会员 id,药店ID,商品ID查询订单中此商品的购买数量-->
<select id="getGoodsNumById" parameterType="com.ehaoyao.payment.entity.MerchaindiseStoreVO"
resultType="Integer">
select ifnull(sum(s.nums),0) from o2o_orders o left join o2o_order_items s on
o.order_id=s.order_id and o.`status` not in ('5') and s.isGift=0 where 1 =1 and find_in_set(o.pharmacy_id,
(select ifnull(group_concat(c.pharm_id),0) from o2o_promotion_purchase a
inner join o2o_promotion_scene b on a.scene_id=b.scene_id
inner join o2o_promotion_pharm_store c on a.purchase_id=c.activity_id and c.activity_type=5
where <![CDATA[b.purchase_begin_time<=now() and b.purchase_end_time>=now() and b.status=1
and c.freebie_id=#{goods_id} and a.scene_id=#{sceneId}))
and s.product_id=#{goods_id} and o.createtime >= #{purchaseBeginTime} and o.createtime < #{purchaseEndTime}]]>
<if test="(memberId != null and memberMac == null)">
and o.member_id= #{memberId}
</if>
<if test="(memberId == null and memberMac != null)">
and o.member_mac=#{memberMac}
</if>
<if test="(memberId != null and memberMac != null)">
and (o.member_id= #{memberId} or o.member_mac=#{memberMac})
</if>
</select>
<select id="selectExpressOrder" parameterType="com.ehaoyao.payment.entity.EDIExpressOrderVO"
resultMap="BaseResultMap">
select a.is_retention,a.inviteCode,a.external_id as
storeId,a.pharmacy_id,a.order_id,a.status,a.shipping,a.itemnum,a.createtime,
a.cost_item,a.is_tax,a.tax_company,a.total_amount,a.pmt_amount,a.final_amount,a.memo,a.cost_freight,a.shipping_id,a.is_delivery,a.delivery_time,a.is_group,
a.last_change_time,a.orderType,a.ship_name,a.ship_mobile,a.ship_addr,a.captcha
,a.total_mailno as mailno ,s.syncstatus,(select sum(t.nums) from
o2o_order_items t where t.order_id = a.order_id) as goodsNums from (
select o.* ,e.total_mailno,m.inviteCode,p.external_id from o2o_orders
o,o2o_pharmacy p,o2o_express_success e,o2o_members m where
o.pharmacy_id=p.pharmacy_id
and o.order_id=e.orderid and
m.member_id=o.member_id
<if test="externalId!=null and externalId != '' ">
and p.external_id=#{externalId}
</if>
<if test="chainBranchId !=null and chainBranchId != '' ">
and p.chain_branch_id=#{chainBranchId}
</if>
<if test="orderStatus != null">
and o.status = #{orderStatus}
</if>
<if test="shippingId!=null">
and o.shipping_id=#{shippingId}
</if>
) a left join o2o_order_sync_records s
on a.order_id = s.order_id where
1=1
and s.order_status = 2
<if test="syncStatus==null or syncStatus==''">
and (s.syncstatus= -1 or s.syncstatus =0)
</if>
<if test="syncStatus!=null and syncStatus!=''">
and s.syncstatus=#{syncStatus}
</if>
</select>
<select id="getNightOrderByTime" parameterType="com.ehaoyao.payment.entity.OrderVO"
resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from o2o_orders t where 1=1 and t.status in(-1,0,1,3)
<if test="isRetention!=null">
and t.is_retention=#{isRetention}
</if>
<if test="shippingId!=null">
and t.shipping_id = #{shippingId}
</if>
<if test="beginTime!=null">
and t.createtime >='${beginTime}'
</if>
<if test="endTime!=null">
and t.createtime <'${endTime}'
</if>
</select>
<update id="updateOrderNightTime" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="isNight != null">
isNight=#{isNight,jdbcType=INTEGER},
</if>
<if test="nightTime != null">
nightTime=#{nightTime,jdbcType=TIMESTAMP},
</if>
</set>
where order_id = #{orderId,jdbcType=VARCHAR}
</update>
<select id="getUnevaluateOrdersDayAgo1" resultMap="resultOrders">
select o.order_id, o.member_id, o.pharmacy_id, ol.acttime as
createtime
from o2o_orders o, o2o_order_log ol
<![CDATA[
where o.status = #{status} and o.order_id = ol.order_id and ol.behavior = '完成' and
(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(ol.acttime)) >= #{day} * 24 * 60 * 60
]]>
</select>
<!-- 需要退款订单 -->
<select id="listPageReturnOrder" resultMap="resultOrders">
select oor.createTime as return_createTime,oor.returnId,o.returnStatus,o.order_id,o.`status`,o.shipping,
oop.paymethod,op.pharm_short_name,o.contactMobie,o.ship_mobile,o.ship_name,o.total_amount,o.pharmacy_id,p.audit_memo,o.reason_text
FROM (select o.*,CASE
WHEN t.groupId is null THEN o.order_id
ELSE t.groupId
END as groupId
from o2o_orders o left join (select g.groupId,t.* from o2o_orders t ,o2o_orders_group g where find_in_set(t.order_id, orderIds) ) t
on o.order_id = t.order_id WHERE o.pay_status=1 and o.payment=1 and o.returnStatus=0 and o.orderType!=13 and o.orderType!=16
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.orderStatus != null">
and o.status in
<foreach item="statusItem" index="index" collection="record.orderStatus"
open="(" separator="," close=")">
#{statusItem}
</foreach>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingIds != null">
and o.shipping_id in
<foreach item="shippingItem" index="index" collection="record.shippingIds"
open="(" separator="," close=")">
#{shippingItem}
</foreach>
</if>
) o
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
LEFT JOIN o2o_order_return oor ON o.order_id=oor.orderId
INNER JOIN o2o_payments oop ON o.groupId=oop.order_id
LEFT JOIN o2o_order_property p ON o.order_id = p.order_id
WHERE o.pay_status=1 and o.payment=1 and o.returnStatus=0
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.orderStatus != null">
and o.status in
<foreach item="statusItem" index="index" collection="record.orderStatus"
open="(" separator="," close=")">
#{statusItem}
</foreach>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingIds != null">
and o.shipping_id in
<foreach item="shippingItem" index="index" collection="record.shippingIds"
open="(" separator="," close=")">
#{shippingItem}
</foreach>
</if>
<if test="record.logistics_platype != null">
and o.logistics_platype=#{record.logistics_platype}
</if>
order by return_createTime desc
</select>
<!-- 退款订单查询 -->
<select id="listPageReturnOrderList" resultMap="resultOrders">
select oor.createTime as return_createTime,oor.returnId,o.returnStatus,o.order_id,o.`status`,o.shipping,o.orderType,o.payment,
oop.paymethod,op.pharm_short_name,o.contactMobie,o.ship_mobile,o.ship_name,o.total_amount,o.pharmacy_id,p.audit_memo,o.reason_text
FROM (select o.*,CASE
WHEN t.groupId is null THEN o.order_id
ELSE t.groupId
END as groupId
from o2o_orders o left join (select g.groupId,t.* from o2o_orders t ,o2o_orders_group g where find_in_set(t.order_id, orderIds) ) t
on o.order_id = t.order_id WHERE o.returnStatus!=-1
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.returnStatus !=null">
and o.returnStatus=#{record.returnStatus}
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.status != null">
<if test="record.status!=7">and o.status =#{record.status}
</if>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingId != null">
and o.shipping_id =#{record.shippingId}
</if>
) o
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
LEFT JOIN o2o_order_return oor ON o.order_id=oor.orderId
left JOIN o2o_payments oop ON o.groupId=oop.order_id
LEFT JOIN o2o_order_property p ON o.order_id = p.order_id
WHERE o.returnStatus!=-1
<if test="record.paymentCfgId != null">
<if test="record.paymentCfgId==1">and oop.paymentCfg_id in (1,4,7)
</if>
<if test="record.paymentCfgId==2">and oop.paymentCfg_id in (2,5,8)
and o.orderType not in (6,7,8,11,13)
</if>
<if test="record.paymentCfgId==0">and oop.paymentCfg_id is null
</if>
<if test="record.paymentCfgId==6">and o.orderType = 6
</if>
<if test="record.paymentCfgId==7">and o.orderType = 7
</if>
<if test="record.paymentCfgId==11">and o.orderType =11
</if>
<if test="record.paymentCfgId==13">and o.orderType = 13
</if>
</if>
<if test="record.returnCreateTimeStart != null and record.returnCreateTimeStart != ''" >
and (oor.createTime > #{record.returnCreateTimeStart} or oor.createTime is null)
</if>
<if test="record.returnCreateTimeEnd != null and record.returnCreateTimeEnd != ''" >
and (oor.createTime < date_add(#{record.returnCreateTimeEnd}, INTERVAL 1 day) or oor.createTime is null)
</if>
<if test="record.returnCreateTimeEnd==null and record.returnCreateTimeStart==null">
and (MONTH(oor.createTime)=MONTH(NOW()) and year(oor.createTime)=year(now()) or oor.createTime is null)
</if>
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.returnStatus !=null">
and o.returnStatus=#{record.returnStatus}
</if>
<if test="record.returnId != null and record.returnId != ''">
and oor.returnId like CONCAT('%', #{record.returnId} ,'%')
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.status != null">
<if test="record.status!=7">and o.status =#{record.status}
</if>
<if test="record.status==7">and o.status=2 or o.status=6
</if>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingId != null">
and o.shipping_id =#{record.shippingId}
</if>
group by o.order_id
order by o.returnStatus,return_createTime desc
</select>
<!-- 退款订单查询结果导出excel -->
<select id="ReturnOrderListExport" resultMap="resultReturnOrders">
select oor.createTime as return_createTime,oor.returnId,o.returnStatus,o.order_id,o.`status`,o.shipping,o.orderType,
oop.paymethod,op.pharm_short_name,o.contactMobie,o.ship_mobile,o.ship_name,o.total_amount
FROM (select o.*,CASE
WHEN t.groupId is null THEN o.order_id
ELSE t.groupId
END as groupId
from o2o_orders o left join (select g.groupId,t.* from o2o_orders t ,o2o_orders_group g where find_in_set(t.order_id, orderIds) ) t
on o.order_id = t.order_id WHERE o.returnStatus!=-1
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.returnStatus !=null">
and o.returnStatus=#{record.returnStatus}
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.status != null">
<if test="record.status!=7">and o.status =#{record.status}
</if>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingId != null">
and o.shipping_id =#{record.shippingId}
</if>
) o
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
LEFT JOIN o2o_order_return oor ON o.order_id=oor.orderId
left JOIN o2o_payments oop ON o.groupId=oop.order_id
WHERE o.returnStatus!=-1
<if test="record.paymentCfgId != null">
<if test="record.paymentCfgId==1">and oop.paymentCfg_id in (1,4,7)
</if>
<if test="record.paymentCfgId==2">and oop.paymentCfg_id in (2,5,8)
</if>
<if test="record.paymentCfgId==0">and oop.paymentCfg_id is null
</if>
<if test="record.paymentCfgId==6">and o.orderType = 6
</if>
<if test="record.paymentCfgId==7">and o.orderType = 7
</if>
</if>
<if test="record.returnCreateTimeStart != null and record.returnCreateTimeStart != ''" >
and (oor.createTime > #{record.returnCreateTimeStart} or oor.createTime is null)
</if>
<if test="record.returnCreateTimeEnd != null and record.returnCreateTimeEnd != ''" >
and (oor.createTime < date_add(#{record.returnCreateTimeEnd}, INTERVAL 1 day) or oor.createTime is null)
</if>
<if test="record.returnCreateTimeEnd==null and record.returnCreateTimeStart==null">
and (MONTH(oor.createTime)=MONTH(NOW()) and year(oor.createTime)=year(now()) or oor.createTime is null)
</if>
<if test="record.orderId != null ">
and o.order_id like CONCAT('%', #{record.orderId},'%')
</if>
<if test="record.returnStatus !=null">
and o.returnStatus=#{record.returnStatus}
</if>
<if test="record.returnId != null and record.returnId != ''">
and oor.returnId like CONCAT('%', #{record.returnId} ,'%')
</if>
<if test="record.shipMobile != null and record.shipMobile != '' ">
and o.ship_mobile like CONCAT('%', #{record.shipMobile},'%')
</if>
<if test="record.status != null">
<if test="record.status!=7">and o.status =#{record.status}
</if>
<if test="record.status==7">and o.status=2 or o.status=6
</if>
</if>
<if test="record.pharmacyIds != null">
and o.pharmacy_id in
<foreach item="item" index="index" collection="record.pharmacyIds"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="record.shippingId != null">
and o.shipping_id =#{record.shippingId}
</if>
order by o.returnStatus,return_createTime desc
</select>
<!-- 申请退款 -->
<update id="submitReturn" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders o
<set>
<if test="returnStatus !=null ">
o.returnStatus = #{returnStatus},
</if>
<if test="reasonText !=null">
o.reason_text =#{reasonText}
</if>
</set>
where order_id=#{orderId}
</update>
<select id="selectOrderByreturnId" resultMap="resultOrders" parameterType="String">
select o.*,oor.returnId,oor.returnStatus,oor.createTime return_createTime,op.pharm_short_name
from o2o_orders o left join o2o_order_return oor on o.order_id=oor.orderId
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
where 1=1
<if test="returnId!=null">and oor.returnId=#{returnId}</if>
</select>
<update id="updateReason" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="reasonText !=null ">
reason_text = #{reasonText},
</if>
reason_id= #{reasonId},
</set>
where order_id=#{orderId}
</update>
<select id="getReadyOrders"
resultMap="resultOrders">
select
<include refid="Base_Column_List" />
FROM o2o_orders o where o.createtime between date_add(curdate(), interval -7 day) and curdate()+1
and o.payment=1 and o.pay_status=0 and o.`status`=-2 ORDER BY o.createtime desc
</select>
<select id="getOrdersForB2C" parameterType="com.ehaoyao.payment.entity.OrderVO"
resultMap="BaseResultMapVO">
select a.pharmacy_id,a.order_id,status,shipping,captcha,itemnum,a.createtime,shipping_id,ship_name,a.member_id,pay_status,returnStatus,
ship_area,is_retention,
ship_addr, ship_zip, ship_tel, ship_email,street,
ship_mobile,cost_item,is_tax,cost_freight,payment,
tax_company,total_amount,pmt_amount,final_amount,memo,last_change_time,
orderType,pharmacy_name,contactmobie,province,city,country,is_group,activity_id,reason_text,
b.payTime,a.isPrescription ,a.discount_amount
from o2o_orders a
left join o2o_payments b on a.order_id = b.order_id
where last_change_time >= #{b2cStartTime}
and status = #{status}
and shipping_id in (6,9)
and (pay_status = 1 or (pay_status = 0 and payment = 0))
limit #{start},#{limit}
</select>
<select id="getOrdersCountForB2C" parameterType="com.ehaoyao.payment.entity.OrderVO"
resultType="java.lang.Integer">
select
count(*)
from o2o_orders
where last_change_time >= #{b2cStartTime}
and status = #{status}
and shipping_id in (6,9)
and (pay_status = 1 or (pay_status = 0 and payment = 0))
</select>
<update id="updateByPrimaryKeyForB2C" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
status = #{status,jdbcType=INTEGER},
</set>
where order_id = #{orderId,jdbcType=VARCHAR}
</update>
<update id="batchUpdateByPrimaryKeyForB2C" parameterType="java.lang.String">
update o2o_orders set status = 2
where order_id in
<foreach item="orderIds" index="index" collection="array" open="(" separator="," close=")">
#{orderIds}
</foreach>
</update>
<update id="updateOrderShipping" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="shippingId != null and shippingId != ''">
shipping_id= #{shippingId},
</if>
<if test="shipping != null and shipping != ''">
shipping= #{shipping},
</if>
</set>
where
order_id=#{orderId}
</update>
<!-- 根据订单和订单明细信息查找订单列表 -->
<select id="getOrdersForPurchase" resultMap="resultOrders">
select oo.member_id, oo.member_mac, ooi.nums as itemnum
from o2o_orders oo INNER JOIN o2o_order_items ooi
ON (
<if test="orders.memberId != null and orders.memberMac != null">
(oo.member_id = #{orders.memberId} or oo.member_mac = #{orders.memberMac}) and
</if>
<if test="orders.memberId != null and orders.memberMac == null">
oo.member_id = #{orders.memberId} and
</if>
<if test="orders.memberId == null and orders.memberMac != null">
oo.member_mac = #{orders.memberMac} and
</if>
oo.couponsType = 4 and <![CDATA[oo.`status` <> 5]]> and oo.pharmacy_id = #{orders.pharmacyId}
and ooi.product_id = #{orderItems.productId} and ooi.purchase_id = #{orderItems.purchaseId}
and ooi.isGift = 0 and ooi.order_id = oo.order_id)
</select>
<select id="SynPrescriptionOrdersStatus" resultMap="resultOrders">
select 'true' as QUERYID,op.pharm_short_name,pro.gdXcoord,pro.gdYcoord,
<include refid="Base_Column_List" />
from o2o_orders o
inner JOIN o2o_pharmacy op on o.pharmacy_id=op.pharmacy_id
LEFT JOIN o2o_order_property pro ON pro.order_id = o.order_id
where isPrescription = 1 and o.status=-3 and op.pharm_flag != 2
</select>
<!-- 根据多个订单Id查询查询订单的状态和id -->
<select id="selectOrdersByOrdersId" resultMap="resultOrderMaintenance">
select o.member_id,o.orderType,o.returnStatus,o.member_mac,o.pay_status,
o.cost_freight,o.cost_item,o.total_amount,o.pmt_amount,o.discount_amount,
o.final_amount,o.activity_id,o.couponsType,o.payment,o.order_id,o.`status`
,o.pharmacy_id,e.`status` as expressStatus,e.mailno,e.total_mailno from
(select member_id,returnStatus,orderType,payment,pay_status,cost_freight,cost_item,total_amount,pmt_amount,discount_amount,final_amount,member_mac,activity_id,couponsType,pharmacy_id,order_id,`status` from o2o_orders
where
order_id in
<foreach item="item" index="index" collection="orderIds" open="(" separator="," close=")">
#{item}
</foreach>
)o left join o2o_express_success e on o.order_id = e.orderid
</select>
<update id="updateStatusByOrderIdPrescription" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="status!=null">
status= #{status},
</if>
<if test="pharmacyId != null and pharmacyId != 0">
pharmacy_id= #{pharmacyId},
</if>
<if test="pharmacyName != null and pharmacyName != ''">
pharmacy_name= #{pharmacyName},
</if>
<if test="reasonText !=null and reasonText != ''">
reason_text = #{reasonText},
</if>
<if test="payStatus !=null and payStatus != ''">
pay_status = #{payStatus},
</if>
<if test="returnStatus !=null and returnStatus != ''">
returnStatus = #{returnStatus},
</if>
<if test="memo !=null and memo != ''">
memo = #{memo},
</if>
<choose>
<when test="lastChangeTime != null and lastChangeTime != ''">last_change_time=#{lastChangeTime},</when>
<otherwise>last_change_time=now(),</otherwise>
</choose>
</set>
where
order_id=#{orderId,jdbcType=VARCHAR}
</update>
<!-- jiewang 物流中修改接单时间，接单时效，配送时效、完单时效 -->
<update id="updateOrderDeliveryTime" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="status != null">
status = #{status,jdbcType=INTEGER},
</if>
<if test="deliveryTime != null and deliveryTime != ''">
delivery_time=#{deliveryTime,jdbcType=TIMESTAMP},
</if>
<if test="courier_name !=null and courier_name != ''">
courier_name= #{courier_name},
</if>
<if test="rev_time !=null and rev_time != ''">
rev_time=#{rev_time,jdbcType=TIMESTAMP},
</if>
<if test="rev_effect !=null and rev_effect != ''">
rev_effect= #{rev_effect},
</if>
<if test="effect_time !=null and effect_time != ''">
effect_time= #{effect_time},
</if>
<if test="finish_effict !=null and finish_effict != ''">
finish_effict= #{finish_effict},
</if>
<if test="courierMobile !=null and courierMobile != ''">
courierMobile= #{courierMobile},
</if>
<if test="pushTime !=null and pushTime != ''">
pushTime= #{pushTime,jdbcType=TIMESTAMP},
</if>
<if test="logistics_platype != null">
logistics_platype= #{logistics_platype,jdbcType=INTEGER}
</if>
</set>
where
order_id=#{orderId,jdbcType=VARCHAR}
</update>
<update id="changeOrder" parameterType="com.ehaoyao.payment.entity.Orders">
update o2o_orders
<set>
<if test="status != null">
status = #{status,jdbcType=INTEGER},
</if>
<if test="payment != null">
payment = #{payment,jdbcType=INTEGER},
</if>
</set>
where order_id = #{orderId,jdbcType=VARCHAR}
</update>
<!-- 未处理订单 -->
<select id="synMissOrdersToLogistics" resultMap="resultOrders">
select order_id,status,createtime,shipping_id
from o2o_orders
where status =-1 and shipping_id in (3,7)
</select>
<select id="getAllThirOrders" parameterType="java.lang.Integer" resultType="java.lang.Integer">
select count(*) from o2o_orders where member_id=#{memberId} and orderType in (6,7,8,11,13) and status=2
</select>
<select id="getOrderByThirdPlatformOrderId" parameterType="String" resultMap="resultOrders">
select 'true' as QUERYID,pro.memberAdds_gdXcoord gdXcoord,pro.memberAdds_gdYcoord gdYcoord,
o.COURIER_NAME,o.EFFECT_TIME,o.REV_EFFECT,o.FINISH_EFFICT,o.REV_TIME,
<include refid="Base_Column_List" />
from o2o_orders o
LEFT JOIN o2o_order_property pro ON pro.order_id = o.order_id
where pro.thirdPlatform_order_id=#{thirdPlatformOrderId}
</select>
<select id="getZbOrders" parameterType="java.lang.String" resultMap="resultOrders">
select order_id,`status`,orderType,logistics_platype,pharmacy_id from o2o_orders where orderType=11
and logistics_platype=3 and status=-1 and REV_TIME <=#{endTime} and REV_TIME >#{beginTime} and REV_TIME !=''
</select>
<update id="updateMdsDeli" parameterType="java.lang.String">
update o2o_orders set logistics_platype=0 where order_id=#{orderId}
</update>
<insert id="addThirdOrders" parameterType="com.ehaoyao.payment.entity.Orders">
INSERT INTO
o2o_orders(order_id,member_id,status,pharmacy_id,pharmacy_name,contactmobie,ok_pharmacy,pay_status,is_delivery,
delivery_time,shipping_id,shipping,cost_freight,payment,weight,captcha,itemnum,createtime,ship_name,
ship_area,province,city,country,ship_addr,ship_zip,ship_tel,ship_email,ship_mobile,ship_time,cost_item,is_tax,tax_company,score_g,
total_amount,pmt_amount,final_amount,memo,last_change_time,orderType,is_group,activity_id,member_mac,discount_amount,couponsType,isPrescription,logistics_platype)
VALUES(
#{orderId} ,#{memberId} ,#{status} ,#{pharmacyId}
,#{pharmacyName},#{contactmobie},#{okPharmacy} ,#{payStatus}
,#{isDelivery} ,#{deliveryTime},
#{shippingId} ,#{shipping}
,#{costFreight} ,#{payment} ,#{weight} ,#{captcha} ,#{itemnum} ,
<choose>
<when test="createtime != null and createtime != ''">#{createtime}</when>
<otherwise>now()</otherwise>
</choose>
,
#{shipName} ,#{shipArea},#{province},#{city},#{country},
#{shipAddr}
,#{shipZip} ,#{shipTel} ,#{shipEmail} ,#{shipMobile} ,#{shipTime} ,
#{costItem}
,#{isTax} ,#{taxCompany} ,#{scoreG} ,#{totalAmount} ,#{pmtAmount}
,#{finalAmount} ,#{memo} ,
<choose>
<when test="lastChangeTime != null and lastChangeTime != ''">#{lastChangeTime}</when>
<otherwise>now()</otherwise>
</choose>
,
#{orderType},#{isGroup},#{activityId},#{memberMac},#{discountAmount},#{couponsType},#{isPrescription},#{logistics_platype}
)
</insert>
<select id="getOrdersList" resultMap="BaseResultMap"
parameterType="com.ehaoyao.payment.entity.Orders">
select
<include refid="Base_Column_List" />
from o2o_orders o
<where>
1 = 1
<if test="orderId != null" >
and order_id = #{orderId}
</if>
<if test="status != null" >
and status = #{status}
</if>
<if test="pharmacyId != null and pharmacyId != 0" >
and pharmacy_id = #{pharmacyId}
</if>
<if test="pharmacyName != null" >
and pharmacy_name = #{pharmacyName}
</if>
<if test="contactmobie != null" >
and contactMobie = #{contactmobie}
</if>
<if test="shippingId != null and shippingId != 0">
and shipping_id = #{shippingId}
</if>
<if test="shipping != null" >
and shipping = #{shipping}
</if>
<if test="province != null" >
and province = #{province}
</if>
<if test="city != null" >
and city = #{city}
</if>
<if test="country != null" >
and country = #{country}
</if>
<if test="courier_name != null" >
and COURIER_NAME = #{courier_name}
</if>
<if test="courierMobile != null" >
and courierMobile = #{courierMobile}
</if>
</where>
</select>
</mapper>
OrderDao.java
package com.ehaoyao.payment.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import com.ehaoyao.payment.entity.Pagination;
import com.ehaoyao.payment.entity.ExpressOrderVO;
import com.ehaoyao.payment.entity.EDIExpressOrderVO;
import com.ehaoyao.payment.entity.ErpOrderVo;
import com.ehaoyao.payment.entity.OrderItems;
import com.ehaoyao.payment.entity.OrderMaintenanceVO;
import com.ehaoyao.payment.entity.OrderReturnExprot;
import com.ehaoyao.payment.entity.OrderVO;
import com.ehaoyao.payment.entity.Orders;
import com.ehaoyao.payment.entity.MerchaindiseStoreVO;
import com.ehaoyao.payment.entity.OrderMemeberPointVO;
import com.ehaoyao.payment.entity.PharmacyOrderVO;
/**
* 类OrdersDao.java的实现描述：订单的DAO层
*
* @author 孙勇军 2014-5-30 下午5:04:04
*/
@Repository
public interface OrdersDao extends MybatisMapper {
/**
* 获取订单列表
*
* @param orders
* @param pagination
* @return
*/
public List<Orders> listPageOrders(@Param("orders") Orders orders, @Param("pagination") Pagination pagination);
/**
* 获取订单列表
*
* @param orders
* @param pagination
* @return
*/
public List<Orders> listPageOrders3_0(@Param("orders") Orders orders, @Param("pagination") Pagination pagination);
/**
* 获取订单总数(用户登录后的订单总数显示)
*
* @param orders
* @param pagination
* @return
*/
public int getTotalOrders(Orders orders);
/**
* 根据订单Id修改订单状态
*
* @param orderId
* @return
*/
public int updateStatusByOrderId(Orders orders);
// 修改咨询单状态
public int updateStatusByOrderIdPrescription(Orders orders);
/**
* 添加订单
*
* @param orders
* @return
*/
public int addOrders(Orders orders);
/**
* 通过订单Id获取订单
*
* @param orders
* @return
*/
public Orders getOrderById(String ordersId);
/**
* 获取某药店 驳回状态下的订单数目
*
* @param pharmacyId
* @return
*/
public int getRejectedOrderNum(Long pharmacyId);
/**
* 获取所有 驳回状态下的订单数目
*
* @return
*/
public int getAllRejectedOrderNums();
public Orders getOrderByIdAndUser(Orders orders);
List<ExpressOrderVO> queryExpressOrder(EDIExpressOrderVO vo);
/**
* 连锁ERP获取未同步订单
*
* @param xml
* @return
*/
public List<ErpOrderVo> listPagequeryExpressOrder2(@Param("vo") EDIExpressOrderVO vo,
@Param("pagination") Pagination pagination);
/**
* 连锁ERP查询订单
*
* @param xml
* @return
*/
public List<ErpOrderVo> selectOrderByErpRules(EDIExpressOrderVO vo);
List<Orders> listPageOrderByPharmacy(@Param(value = "pharmacyOrder") Orders pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
/**
* 查询不包含订单明细
*
* @param pharmacyOrder
* @param pagination
* @return
*/
List<Orders> listPageOrderByPharmacy2(@Param(value = "pharmacyOrder") ExpressOrderVO pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
List<Orders> listPageCloseOrderByPharmacy(@Param(value = "pharmacyOrder") Orders pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
int deleteByPrimaryKey(Long order_id);
Orders selectByPrimaryKey(String order_id);
Orders selectOrderById(String order_id);
int updateByPrimaryKeySelective(Orders record);
int updateOrderStatus(Orders record);
int updateOrderNightTime(Orders record);
/**
* 根据订单编号查询信息
*
* @param orderId
* @return
*/
List<PharmacyOrderVO> selectOrderInfo(String orderId);
List<Orders> listPageOrderByPharmacyAddFinishOrderTime(@Param(value = "pharmacyOrder") Orders pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
List<Orders> listPageOrderByPharmacyAndOprationTime(@Param(value = "pharmacyOrder") Orders pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
List<Orders> listPageOrderBySeach(@Param(value = "pharmacyOrder") Orders pharmacyOrder,
@Param(value = "pagination") Pagination pagination);
Integer selectBeginOrdersCount(Long pharmacyId);
Integer selectPickingOrdersCount(Long pharmacyId);
Integer selectDiliveryOrdersCount(Long pharmacyId);
// 更新药店药品库存
void updateMedStore(MerchaindiseStoreVO merchaindiseStoreVO);
// 根据订单ＩＤ查询药品库存
List<MerchaindiseStoreVO> selectMedStoreByOrderId(String orderId);
// 更新团购或其它活动的商品库存
void updateActivityMedStore(MerchaindiseStoreVO merchaindiseStoreVO);
// 查询订单对应的会员可积分数
OrderMemeberPointVO selectOrderPointByOrderId(String order_id);
// 更新会员积分
void updateMemberPoint(OrderMemeberPointVO orderMemeberPointVO);
// 插入会员历史积记录
void insertPointHistory(OrderMemeberPointVO orderMemeberPointVO);
// 根据订单查询药店ＩＤ
Long selectPharmacyIdByOrderId(String order_id);
/**
* 通过ID修改配送方式
*
* @param orderId
* @return
*/
public int updateShippingTypeByOrderId(Orders orders);
/**
* 根据会员 id 查找订单（即：查询某个用户所有已完成的订单）【改：type有值且为all则查全部订单】
*
* @param memberId
* @return
*/
public List<Orders> getOrdersByMemberId(@Param("memberId") Long memberId, @Param("type") String type);
/**
* 根据会员 id 查找订单（即：查询某个用户所有在派送的订单）
*
* @param memberId
* @return
*/
public List<Orders> getOrdersByMemberIdps(@Param("memberId") Long memberId);
/**
* 查询滞留订单
*
* @param orderVO
* @return
*/
public List<Orders> getNightOrderByTime(OrderVO orderVO);
/**
* 查询状态为 status（-1 -- 6）且 已存在 day 天了的订单列表
*
* @param status // 订单状态
* @param day // 天数
* @return
*/
public List<Orders> getUnevaluateOrdersDayAgo1(@Param("status") Long status, @Param("day") Long day);
/**
* 查询已线上支付的已取消和已拒收的订单列表
*/
public List<Orders> listPageReturnOrder(@Param("record") Orders record, @Param("pagination") Pagination pagination);
/**
* 查询已线上支付的已取消和已拒收的订单列表
*/
public List<Orders> listPageReturnOrderList(@Param("record") Orders record,
@Param("pagination") Pagination pagination);
/**
* 查询已线上支付的已取消和已拒收的订单列表导出
*/
public List<OrderReturnExprot> ReturnOrderListExport(@Param("record") Orders record);
/**
* 根据退款单号查询订单
*/
public Orders selectOrderByreturnId(@Param("returnId") String returnId);
public void updateReason(Orders orders);
/**
* 获取订单列表
*/
public List<Orders> getReadyOrders();
/**
* 申请退款
*/
public void submitReturn(Orders orders);
public List<OrderVO> getOrdersForB2C(OrderVO o);
public Integer getOrdersCountForB2C(OrderVO o);
int updateByPrimaryKeyForB2C(Orders record);
int batchUpdateByPrimaryKeyForB2C(String[] orderIds);
public Integer updateOrderShipping(Orders orders);
// 根据会员 id,药店ID,商品ID查询订单中此商品的购买数量
// List<MerchaindiseStoreVO> getGoodsNumById(MerchaindiseStoreVO merchaindiseStoreVO);
public Integer getGoodsNumById(MerchaindiseStoreVO merchaindiseStoreVO);
/**
* 根据订单和订单明细信息查找订单列表
*
* @param orders
* @param orderItems
* @return
*/
public List<Orders> getOrdersForPurchase(@Param("orders") Orders orders, @Param("orderItems") OrderItems orderItems);
/**
* 根据多个订单编号查询出订单的状态和运单号
*
* @param orderIds
* @return
*/
List<OrderMaintenanceVO> selectOrdersByOrdersId(@Param("orderIds") String[] orderIds);
// 定时器，查询出所有状态为-3的订单(-3表示处方药订单)
public List<Orders> SynPrescriptionOrdersStatus();
/**
* @author jiewang 修改配送时间及订单状态
*/
int updateOrderDeliveryTime(Orders record);
/***
* 修改订单为线下支付接口
*
* @param record
* @return
*/
int changeOrder(Orders record);
/**
* 未处理订单
*
* @return
*/
public List<Orders> synMissOrdersToLogistics();
/**
* 通过memberId查询三方订单个数
*/
int getAllThirOrders(@Param("memberId") Long memberId);
/**
* 通过三方平台订单id 查询订单
*
* @param thirdPlatformOrderId
* @return
*/
public List<Orders> getOrderByThirdPlatformOrderId(String thirdPlatformOrderId);
/**
* 根据时间筛选京东到家众包未配送的查询出来
*/
public List<Orders> getZbOrders(@Param("beginTime") String beginTime, @Param("endTime") String endTime);
/**
* 把修改众包配送自配送
*/
int updateMdsDeli(@Param("orderId") String orderId);
/**
* 添加三方订单
*
* @param orders
* @return
*/
public int addThirdOrders(Orders orders);
/**
* 查询订单列表
*
* @param orders
* @return
*/
public List<Orders> getOrdersList(Orders orders);
}
~
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
