Subversion Repositories SmartDukaan

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">



<mapper namespace="in.shop2020.warehouse.persistence.TransferLotMapper">
  <resultMap type="transferLot" id="transferLotResult">
    <id property="id" column="id"/>
  </resultMap>

  <insert id="createTransferLot"  parameterType="transferLot" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO transferLot
    (id, originWarehouseId, destinationWarehouseId, status, transferDate, transitCompletionReferenceNumber, referenceUpdationDate, remarks)
    VALUES
    (#{id}, #{originWarehouseId}, #{destinationWarehouseId}, #{status}, #{transferDate}, #{transitCompletionReferenceNumber}, #{referenceUpdationDate}, #{remarks})
  </insert>
  
  <select id="getTransferLot" parameterType="java.lang.Long" resultType="transferLot">
        SELECT * from transferLot 
        WHERE id = #{transferLotId}
  </select>  


  <select id="getTransferLotsByDate" parameterType="map" resultType="transferLot">
        SELECT * from transferLot 
        WHERE transferDate between #{fromDate} and #{toDate} 
  </select>  
  
  <select id="getItemsInTransferLot" parameterType="java.lang.Long" resultType="map">
        SELECT i.itemId as itemId, sum(s.quantity) as count 
        FROM inventoryItem i 
        JOIN scanNew s 
        ON i.id = s.inventoryItemId 
        WHERE s.transferLotId = #{transferLotId}
        AND s.type = 'WAREHOUSE_TRANSFER_OUT' 
        group by i.itemId
  </select>
  
  <update id="markTransferLotAsReceived" parameterType = "map">
        UPDATE transferLot 
        SET 
        status = 'TRANSFER_COMPLETE',
        transitCompletionReferenceNumber = #{remoteTransferRefNumber},
        referenceUpdationDate = NOW()
        WHERE id = #{id}
  </update> 

  <select id="getAllowedDestinationWarehousesForTransfer" parameterType="java.lang.Long" resultType="java.lang.Long">
        SELECT toWarehouseId 
        FROM possibleWarehouseMovement 
        WHERE fromWarehouseId = #{originWarehouseId} 
  </select>
  
  <update id = "updateTransferLotStatus" parameterType = "map">
        UPDATE transferLot
        SET
        status = #{status}
        WHERE id = #{id} 
  </update>
  
  <update id="markTransferLotAsReceivedPartial" parameterType = "map">
        UPDATE transferLot 
        SET 
        status = 'PARTIAL_TRANSFER',
        transitCompletionReferenceNumber = #{remoteTransferRefNumber},
        referenceUpdationDate = NOW()
        WHERE id = #{id}
  </update>
  
  <insert id="createRemovalStockShipmentReference" parameterType="removalStockShipmentReference" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO removalStockShipmentReference 
    (id, shipmentReference, createdOn, source, inventoryType, quantity, unfulfilQuantity, completed)
    VALUES
    (#{id}, #{shipmentReference}, #{createdOn}, #{source}, #{inventoryType}, #{quantity}, #{unfulfilQuantity}, #{completed})
  </insert>
  
  <update id="updateStockShipment" parameterType="map" >
    UPDATE removalStockShipmentReference
    SET 
    unfulfilQuantity = unfulfilQuantity - #{lineItemQuantity} 
    WHERE id= #{id}
  </update>
  
  <update id="markStockShipmentComplete" parameterType= "java.lang.Long" >
        UPDATE removalStockShipmentReference
        SET 
        completed =1 
        WHERE id= #{id}
  </update>
  
  <select id="getAllUnCompletedStockShipments" parameterType="java.lang.String" resultType= "removalStockShipmentReference">
        SELECT * FROM 
        removalStockShipmentReference 
        WHERE source= #{source} 
        AND completed = 0 
  </select>
  
  <update id="updateShipmentReferenceTransferLot" parameterType="map">
        UPDATE transferLot
        SET 
        shipmentReference = #{shipmentReference}
        WHERE id= #{id}
  </update>
  
  <select id="getRemovalStockShipmentReferenceById" parameterType="java.lang.Long" resultType= "removalStockShipmentReference">
        SELECT * FROM
        removalStockShipmentReference 
        WHERE id= #{id}
  </select>
</mapper>