namespace MMDAL.Query.PartLevel
{
    // Indexes: IX_TPARTALLOC_DETAIL, IX_TPARTALLOC_LOT
    public static class PartAllocationQB
    {
        public const string GET_PARTALLOCATION_LIST = @"
            SELECT pa.PARTALLOCATIONID AS PartAllocationId,
                   pa.PARTDETAILID     AS PartDetailId,
                   pa.LOTID            AS LotId,
                   pa.LOTDETAILID      AS LotDetailId,
                   pa.ALLOCATEDQTY     AS AllocatedQty,
                   pa.ISSUEDQTY        AS IssuedQty,
                   pa.CONSUMEDQTY      AS ConsumedQty,
                   pa.STATUS           AS Status,
                   pa.TENANTID,
                   pa.CREATEDBYID      AS CreatedById,
                   pa.CREATEDON        AS CreatedOn,
                   pa.MODIFIEDBYID     AS ModifiedById,
                   pa.MODIFIEDON       AS ModifiedOn
            FROM   TPARTALLOCATION pa
            WHERE  pa.PARTDETAILID = @PartDetailId
            AND    pa.TENANTID     = @TenantId
            ORDER BY pa.PARTALLOCATIONID";

        public const string INSERT_PARTALLOCATION = @"
            INSERT INTO TPARTALLOCATION (
                PARTALLOCATIONID, PARTDETAILID, LOTID, LOTDETAILID,
                ALLOCATEDQTY, ISSUEDQTY, CONSUMEDQTY, STATUS,
                TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES (
                @PartAllocationId, @PartDetailId, @LotId, @LotDetailId,
                @AllocatedQty, 0, 0, 0,
                @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_ISSUED = @"
            UPDATE TPARTALLOCATION
            SET    ISSUEDQTY    = ISSUEDQTY + @Quantity,
                   STATUS       = 1,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  PARTALLOCATIONID = @PartAllocationId
            AND    TENANTID         = @TenantId";

        public const string UPDATE_CONSUMED = @"
            UPDATE TPARTALLOCATION
            SET    CONSUMEDQTY   = CONSUMEDQTY + @Quantity,
                   STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = @ModifiedOn
            WHERE  PARTALLOCATIONID = @PartAllocationId
            AND    TENANTID         = @TenantId";

        public const string UPDATE_RELEASED = @"
            UPDATE TPARTALLOCATION
            SET    STATUS        = 3,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = @ModifiedOn
            WHERE  PARTALLOCATIONID = @PartAllocationId
            AND    TENANTID         = @TenantId";

        public const string GET_PARTALLOCATION = @"
            SELECT pa.PARTALLOCATIONID AS PartAllocationId,
                   pa.PARTDETAILID     AS PartDetailId,
                   pa.LOTID            AS LotId,
                   pa.ALLOCATEDQTY     AS AllocatedQty,
                   pa.ISSUEDQTY        AS IssuedQty,
                   pa.CONSUMEDQTY      AS ConsumedQty,
                   pa.STATUS           AS Status,
                   pa.TENANTID
            FROM   TPARTALLOCATION pa
            WHERE  pa.PARTALLOCATIONID = @PartAllocationId
            AND    pa.TENANTID         = @TenantId";
    }
}