package com.yc.wms.service.impl;

import com.yc.wms.bean.Xuliehao;
import com.yc.wms.service.Xuliehaoqingdan;
import com.yc.wms.until.QueryPageUtil;
import com.yc.wms.until.Result;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class XuliehaoqingdanImpl implements Xuliehaoqingdan {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public Result QueryJson(QueryPageUtil query, Xuliehao xuliehao) {

        int pageNum = query.getPageNum();//当前页
        int pageSize = query.getPageSize();//条数

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        String sql="select Row_number() OVER(ORDER BY inv.id) as Row_Num, inv.id,lc.CODE lc_code,i.CODE i_code,inv.BAR_CODE ,i.NAME i_name,i.UNIT ,\n" +
                "inv.STATUS ,inv.QUANTITY ,inv.QUEUED_QUANTITY ,\n" +
                "ik.PROP_C1 ,ik.PROP_C2 ,ik.PROP_C3 ,ik.PROP_C5 ,ik.PROP_C9 ,ik.PROP_D2 ,\n" +
                "z.NAME z_name,inv.STORAGE_DATE ,case when inv.coun_Plan is null then '否' else '是' end as sfpd\n" +
                " from INVENTORY  inv\n" +
                " left join ITEM_KEY ik on ik.id=inv.ITEM_KEY_ID\n" +
                " left join item i on i.id=ik.ITEM_ID\n" +
                " left join LOCATION lc on lc.id=inv.LOCATION_ID\n" +
                " left join [ZONE] z on z.id=lc.ZONE_ID\n" +
                " WHERE 1=1\n" +
                " and inv.quantity>0\n" +
                " and lc.type not in ('RECEIVE','SHIP')\n" +
                " and i.BE_MARK_WEIGHT=1";



        String sqlCount = "select count(t.id) as nums from (" + sql+") t";
        String sqlFy = "select t.* from(" + sql+") t";

        sqlFy+= " WHERE Row_Num BETWEEN " + (pageNum-1) * pageSize + " AND " + (pageNum) * pageSize;

        //查询单数据
        Map<String,Object> listCount = jdbcTemplate.queryForMap(sqlCount, new HashMap<>());
        //查询多条数据据
        List<Map<String,Object>> maps = jdbcTemplate.queryForList(sqlFy, new HashMap<>());
        return  Result.success(Long.parseLong(listCount.get("nums").toString()),maps);

    }
}
