CREATE TABLE `TS_MopayInvoiceComposition` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `RequestID` int(11) NOT NULL COMMENT '开票ID',

  `CustomerID` int(11) NOT NULL COMMENT '客户ID',

  `ShopID` int(11) NOT NULL COMMENT '门店ID',

  `InvoiceAmount` decimal(10,2) NOT NULL COMMENT '开票金额',

  `Status` tinyint(4) NOT NULL COMMENT '状态，1:初始,3:开票中,4:提交失败,5:开票成功，6:冲销成功，7:作废 8:撤销，9 驳回',

  `AddTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',

  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  `Memo` varchar(255) DEFAULT '' COMMENT '备注',

  PRIMARY KEY (`ID`),

  UNIQUE KEY `UK_RequestID_ShopID` (`RequestID`,`ShopID`),

  KEY `IX_CustomerID_ShopID` (`CustomerID`,`ShopID`),

  KEY `IX_ShopID` (`ShopID`)

) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COMMENT='闪惠开票申请金额组成';

CREATE TABLE `TS_TGInvoiceComposition` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `RequestID` int(11) NOT NULL COMMENT '开票ID',

  `CustomerID` int(11) NOT NULL COMMENT '客户ID',

  `DealGroupID` int(11) NOT NULL COMMENT '团购ID',

  `InvoiceAmount` decimal(10,2) NOT NULL COMMENT '开票金额',

  `Status` tinyint(4) NOT NULL COMMENT '状态，1:初始,3:开票中,4:提交失败,5:开票成功，6:冲销成功，7:作废 8:撤销，9 驳回',

  `AddTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',

  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  `Memo` varchar(255) DEFAULT '' COMMENT '备注',

  PRIMARY KEY (`ID`),

  UNIQUE KEY `UK_RequestID_DealGroupID` (`RequestID`,`DealGroupID`),

  KEY `IX_CustomerID_DealGroupID` (`CustomerID`,`DealGroupID`),

  KEY `IX_DealGroupID` (`DealGroupID`)

) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='团购开票申请金额组成';

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="MopayActivityVoucher">

    <typeAlias alias="mopayActivityVoucherData" type="com.ts.mopay.settle.biz.data.MopayActivityVoucherData"/>

    <resultMap id="mopayActivityVoucherData" class="mopayActivityVoucherData">

        <result column="ID" property="id"/>

        <result column="OrderID" property="orderId"/>

        <result column="ShopID" property="shopId"/>

        <result column="SerialNumber" property="serialNumber"/>

        <result column="OrderAddTime" property="orderAddTime"/>

        <result column="OrderUpdateTime" property="orderUpdateTime"/>

        <result column="OrderStatus" property="orderStatus"/>

        <result column="TradeType" property="tradeType"/>

        <result column="SolutionID" property="solutionId"/>

        <result column="SchemeID" property="schemeId"/>

        <result column="UniCashierOrderID" property="uniCashierOrderId"/>

        <result column="OriginAmount" property="originAmount"/>

        <result column="DiscountAmount" property="discountAmount"/>

        <result column="ActivityAmount" property="activityAmount"/>

        <result column="USerPayAmount" property="userPayAmount"/>

        <result column="NoDiscountAmount" property="noDiscountAmount"/>

        <result column="CouponOfferMessagesJsonStr" property="couponOfferMessagesJsonStr"/>

        <result column="Memo" property="memo"/>

        <result column="SettleStatus" property="settleStatus"/>

        <result column="SnapshotID" property="snapshotId"/>

        <result column="IsOld" property="isOld"/>

        <result column="AddTime" property="addTime"/>

        <result column="UpdateTime" property="updateTime"/>

    </resultMap>

    <sql id="sql_select">

       SELECT

        ID,

        OrderID,

        ShopID,

        SerialNumber,

        SchemeID,

        OrderAddTime,

        OrderUpdateTime,

        OrderStatus,

        TradeType,

        SolutionID,

        SnapshotID,

        UniCashierOrderID,

        OriginAmount,

        DiscountAmount,

        ActivityAmount,

        USerPayAmount,

        NoDiscountAmount,

        CouponOfferMessagesJsonStr,

        SettleStatus,

        IsOld,

        AddTime,

        UpdateTime,

        Memo

        from TS_MopayActivityVoucher

    </sql>

    <insert id="insertMopayActivityVoucherData">

        INSERT INTO TS_MopayActivityVoucher

        (OrderID,ShopID,SerialNumber,SchemeID,OrderAddTime,OrderUpdateTime,OrderStatus,TradeType,SolutionID,SnapshotID,UniCashierOrderID,

        OriginAmount,DiscountAmount,ActivityAmount,USerPayAmount,NoDiscountAmount,

        CouponOfferMessagesJsonStr,SettleStatus,IsOld,AddTime,UpdateTime,Memo)

        VALUES (

         #mopayActivityVoucherData.orderId#,

         #mopayActivityVoucherData.shopId#,

         #mopayActivityVoucherData.serialNumber#,

         #mopayActivityVoucherData.schemeId#,

         #mopayActivityVoucherData.orderAddTime#,

         #mopayActivityVoucherData.orderUpdateTime#,

         #mopayActivityVoucherData.orderStatus#,

         #mopayActivityVoucherData.tradeType#,

         #mopayActivityVoucherData.solutionId#,

         #mopayActivityVoucherData.snapshotId#,

         #mopayActivityVoucherData.uniCashierOrderId#,

         #mopayActivityVoucherData.originAmount#,

         #mopayActivityVoucherData.discountAmount#,

         #mopayActivityVoucherData.activityAmount#,

         #mopayActivityVoucherData.userPayAmount#,

         #mopayActivityVoucherData.noDiscountAmount#,

         #mopayActivityVoucherData.couponOfferMessagesJsonStr#,

         #mopayActivityVoucherData.settleStatus#,

         #mopayActivityVoucherData.isOld#,

         now(),

         now(),

         #mopayActivityVoucherData.memo#

        )

        <selectKey resultClass="java.lang.Integer" keyProperty="Id">

            <![CDATA[

            SELECT @@IDENTITY AS Id

            ]]>

        </selectKey>

    </insert>

    <update id="updateVoucherSettleStatus" parameterClass="map">

        update TS_MopayActivityVoucher

        set SettleStatus = #settleStatus#

        where ID = #id#

    </update>

    <select id="queryVoucherByTimeAndStatus" parameterClass="map" resultClass="mopayActivityVoucherData">

        <include refid="sql_select"/>

        where AddTime &gt;= #beginTime# and AddTime &lt;= #endTime# and SettleStatus = #status#

    </select>

    <select id="queryVoucherById" parameterClass="map" resultClass="mopayActivityVoucherData">

        <include refid="sql_select"/>

        where ID in

        <iterate property="idList" open="(" close=")" conjunction=",">

            #idList[]#

        </iterate>

    </select>

    <select id="loadByOrderIdAndTradeType" parameterClass="map" resultClass="mopayActivityVoucherData">

        <include refid="sql_select"/>

        where OrderId = #orderId#

        and TradeType = #tradeType#

    </select>

    <select id="queryVoucherByTimeAndStatusList" parameterClass="map" resultClass="mopayActivityVoucherData">

        <include refid="sql_select"/>

        where AddTime &gt;= #beginTime#

        and AddTime &lt;= #endTime#

        and SettleStatus in

        <iterate property="statusList" open="(" close=")" conjunction=",">

            #statusList[]#

        </iterate>

    </select>

    <select id="queryVoucherByTimeAndStatusTradeType" parameterClass="map" resultClass="mopayActivityVoucherData">

        <include refid="sql_select"/>

        where

        <![CDATA[AddTime >= #beginTime# and AddTime <= #endTime#  ]]>

        and SettleStatus = #status#

        and TradeType = #tradeType#;

    </select>

</sqlMap>

<update id="updateInfoSort" parameterClass="org.limojfip.domain.FipInfoSort">

    update FIP_InfoSorts

    <dynamic prepend="set">

        <isNotNull prepend="," property="isKey">

            ISKey=#isKey#

        </isNotNull>

        <isNotNull prepend="," property="SName">

            SName=#SName#

        </isNotNull>

        <isNotNull prepend="," property="description">

            Description=#description#

        </isNotNull>

        <isNull prepend="," property="description">

            Description=null

        </isNull>

        <isNotNull prepend="," property="parentKey">

            ParentKey=#parentKey#

        </isNotNull>

        <isNull prepend="," property="parentKey">

            ParentKey=null

        </isNull>

    </dynamic>

    where SKey = #SKey#

</update>