package com.seeyon.apps.src_hxinvoice.dao.impl;

import com.seeyon.apps.src_hxinvoice.dao.IHxinvoiceDao;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.util.JDBCAgent;
import www.seeyon.com.utils.StringUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class HxinvoiceDaoImpl implements IHxinvoiceDao {
//    测试环境
//    private String getInvoiceIdByCode = "select id from formmain_3963 where field0078 = ? and field0054 = ?";
//    private String getInvoiceDYFlowIdByCode = "select id from formmain_3969 where field0078 = ? and field0054 = ?";
//    private String getInvoicePLFlowIdByCode = "select id from formmain_3972 where field0078 = ? and field0054 = ?";
//    private String updateInvoiceByGoodsCode = "update formson_3964 set field0073 = ? where  formmain_id = ? and field0080 = ?";
//    private String updateInvoiceDataByNoCode = "update formmain_3963 set field0050 = ?  , field0085 = TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') ,field0086 = ?,field0084 = ?,field0087 = ? ,field0005 = ? where id = ?";
//    private String getCtpEnumItemsByEnumId = "select id,ref_enumid,showvalue,enumvalue from ctp_enum_item where ref_enumid = ?";
//    private String getInvoicesByState =
//            "SELECT f1.id,to_char( f1.field0039, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqq,to_char( f1.field0039 + 1, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqz,f1.field0078,f1.field0054,e1.showvalue FROM formmain_3963 f1 LEFT JOIN ctp_enum_item e1 ON f1.field0084 = e1.id WHERE (e1.showvalue != '订单开票成功' and e1.showvalue !='订单开票失败') or e1.showvalue is null";
//    private String getGoodsByCode = "select field0084,field0094,field0093,field0120,field0121 from formmain_3960 where field0083 = ?";
//    private String queryFastRedInvoice = "select field0047,field0059,field0058,field0064,field0057 from formmain_3958 where field0064 not like '%订单开票成功%' or field0064 is null";
//    private String getTaxRateByTaxItemCode = "select field0084,field0094 from formmain_3960 where field0083  = ?";

//  正式环境
    private String getInvoiceIdByCode = "select id from formmain_3969 where field0078 = ? and field0054 = ?";
    private String getInvoiceDYFlowIdByCode = "select id from formmain_3967 where field0078 = ? and field0054 = ?";
    private String updateInvoiceDYFlowIdByCode = "update formmain_3967 set field0076 = ? where  field0078 = ? and field0054 = ?";
    private String getInvoicePLFlowIdByCode = "select id,field0076 as resdata from formmain_3972 where field0078 = ? and field0115 = ?";
    private String updateInvoicePLFlowByCode = "update formmain_3972 set field0076 = ? where  field0078 = ? and field0115 = ?";
    private String updateInvoiceByGoodsCode = "update formson_3970 set field0073 = ? where  formmain_id = ? and field0080 = ?";
    private String updateInvoiceDataByNoCode = "update formmain_3969 set field0050 = ?  , field0085 = TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') ,field0086 = ?,field0084 = ?,field0087 = ? ,field0005 = ? where id = ?";
    private String getCtpEnumItemsByEnumId = "select id,ref_enumid,showvalue,enumvalue from ctp_enum_item where ref_enumid = ?";
    private String getInvoicesByState =
        "SELECT f1.id,to_char( f1.field0039, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqq,to_char( f1.field0039 + 1, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqz,f1.field0078 as ddqqlsh,f1.field0054 as shuihao,e1.showvalue FROM formmain_3969 f1 LEFT JOIN ctp_enum_item e1 ON f1.field0084 = e1.id WHERE (e1.showvalue != '订单开票成功' and e1.showvalue !='订单开票失败') or e1.showvalue is null";
//        "SELECT f1.id,to_char( f1.field0039, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqq,to_char( f1.field0039 + 1, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqz,f1.field0078 as ddqqlsh,f1.field0054 as shuihao,e1.showvalue FROM formmain_3969 f1 LEFT JOIN ctp_enum_item e1 ON f1.field0084 = e1.id ";
    private String getGoodsByCode = "select field0084,field0094,field0093,field0120,field0121 from formmain_3966 where field0083 = ?";
    private String queryFastRedInvoice = "select field0047,field0059,field0058,field0064,field0057 from formmain_3982 where field0064 not like '%订单开票成功%' or field0064 is null";
    private String getTaxRateByTaxItemCode = "select field0084,field0094 from formmain_3966 where field0083  = ?";

//    select id from formmain_3969 where field0078 = ? and field0054 = ?
    @Override
    public String getInvoiceIdByCode(String code,String shuihao) {
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getInvoiceIdByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(code);
            p.add(shuihao);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String ,Object> map = list.get(0);
                res = map.get("id").toString();
            }else{
                res = "0";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    select id from formmain_3967 where field0078 = ? and field0054 = ?
    @Override
    public String getInvoiceDYFlowIdByCode(String code,String shuihao) {
        String res = "";
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getInvoiceDYFlowIdByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(code);
            p.add(shuihao);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String ,Object> map = list.get(0);
                res = map.get("id").toString();
            }else{
                res = "0";
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    update formmain_3967 set field0076 = ? where  field0078 = ? and field0054 = ?
    @Override
    public int updateInvoiceDYFlowByCode(String msg,String code,String shuihao) {
        int i = 0 ;
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(updateInvoiceDYFlowIdByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(msg);
            p.add(code);
            p.add(shuihao);
            i=agent.execute(sql.toString(), p);
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return i;
    }

    //    update formmain_3967 set field0076 = ? where  field0078 = ? and field0054 = ?
    @Override
    public int updateInvoicePLFlowByCode(String msg,String code,String shuihao) {
        int i = 0;
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(updateInvoicePLFlowByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(msg);
            p.add(code);
            p.add(shuihao);
            i = agent.execute(sql.toString(), p);
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return i;
    }

//    select id,field0076 as resdata from formmain_3972 where field0078 = ? and field0115 = ?
        @Override
    public Map<String,String> getInvoicePLFlowIdByCode(String code,String shuihao) {
        Map<String,String> res = new HashMap<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getInvoicePLFlowIdByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(code);
            p.add(shuihao);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();
            if (list != null && list.size() > 0) {
                Map<String ,Object> map = list.get(0);
                res.put("id",map.get("id").toString());
                res.put("resdata",map.get("resdata").toString());
            }else{
                res.put("id","0");
                res.put("resdata","");
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    update formson_3970 set field0073 = ? where  formmain_id = ? and field0080 = ?
    @Override
    public int updateInvoiceByGoodsCode(String formmainId, String goodsCode, String taxAmount) {
        int i = 0 ;
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(updateInvoiceByGoodsCode);
            List<Object> p = new ArrayList<Object>();
            p.add(taxAmount);
            p.add(formmainId);
            p.add(goodsCode);
            i=agent.execute(sql.toString(), p);
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return i;
    }

//    update formmain_3969 set field0050 = ?  , field0085 = TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') ,field0086 = ?,field0084 = ?,field0087 = ? ,field0005 = ? where id = ?
    @Override
    public int updateInvoiceDataByNoCode(String noCode, Map<String, String> param) {
        int i = 0 ;
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(updateInvoiceDataByNoCode);
            List<Object> p = new ArrayList<Object>();
            p.add(param.get("发票号码"));
//            p.add(param.get("发票代码"));
            p.add(param.get("实际开票日期"));
//            p.add(param.get("订单状态"));
            p.add(param.get("订单状态描述"));
            p.add(param.get("订单状态"));
            p.add(param.get("数电发票附件"));
            p.add(param.get("开票事项说明"));
            p.add(noCode);
            i=agent.execute(sql.toString(), p);
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return i;
    }

//    select id,ref_enumid,showvalue,enumvalue from ctp_enum_item where ref_enumid = ?
    @Override
    public Map<String, String> getCtpEnumItemsByEnumId(String refEnumId) {
        Map<String ,String> res = new HashMap<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getCtpEnumItemsByEnumId);
            List<Object> p = new ArrayList<Object>();
            p.add(refEnumId);
            agent.execute(sql.toString(), p);
            List<Map> list = agent.resultSetToList();

            for(int i = 0 ; i < list.size() ; i++){
                Map<String ,Object> map = list.get(i);
                res.put(map.get("showvalue").toString(),map.get("id").toString());

            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    SELECT f1.id,to_char( f1.field0039, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqq,to_char( f1.field0039 + 1, 'YYYY-MM-DD HH24:MI:SS' ) AS ddrqz,f1.field0078,f1.field0054,e1.showvalue FROM formmain_3969 f1 LEFT JOIN ctp_enum_item e1 ON f1.field0084 = e1.id WHERE (e1.showvalue != '订单开票成功' and e1.showvalue !='订单开票失败') or e1.showvalue is null
    @Override
    public List<Map<String, String>> getInvoicesByState() {
        List<Map<String,String>> res = new ArrayList<Map<String,String>>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getInvoicesByState);
            List<Object> p = new ArrayList<Object>();
            agent.execute(sql.toString(), p);
            List<Map> lists = agent.resultSetToList();
            if (lists != null && lists.size() > 0) {
                for(int i = 0 ; i < lists.size() ; i ++) {
                    Map<String,String> map = new HashMap<>();
                    Map<String,String> list = lists.get(i);
                    map.put("id",list.get("id"));
                    map.put("ddqqlsh",list.get("ddqqlsh"));
                    map.put("shuihao",list.get("shuihao"));
//                    map.put("dingdan",list.get("field0088"));
//                    map.put("kaipiaodate",list.get("field0039"));
                    map.put("ddrqq",list.get("ddrqq"));
                    map.put("ddrqz",list.get("ddrqz"));
                    res.add(map);
//                    System.out.println(map.toString());
                }
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    select field0084,field0094,field0093,field0120,field0121 from formmain_3966 where field0083 = ?
    @Override
    public Map<String, Object> getGoodsByCode(String goodsCode) {
        Map<String,Object> res = new HashMap<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getGoodsByCode);
            List<Object> p = new ArrayList<Object>();
            p.add(goodsCode);
            agent.execute(sql.toString(), p);
            List<Map> lists = agent.resultSetToList();
            if (lists != null && lists.size() > 0) {
                Map<String,String> list = lists.get(0);
                res.put("spmc",list.get("field0084"));
                res.put("sl",list.get("field0094"));
                res.put("lsbs",list.get("field0120"));
                res.put("zzstsgl",list.get("field0093"));
                res.put("yhzcbs",list.get("field0121"));
//                System.out.println(res.toString());
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    select field0047,field0059,field0058,field0064,field0057 from formmain_3982 where field0064 !='订单开票成功' or field0064 is null
    @Override
    public List<Map<String, String>> queryFastRedInvoice() {
        List<Map<String,String>> res = new ArrayList<Map<String,String>>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(queryFastRedInvoice);
            List<Object> p = new ArrayList<Object>();
            agent.execute(sql.toString(), p);
            List<Map> lists = agent.resultSetToList();
            if (lists != null && lists.size() > 0) {
                for(int i = 0 ; i < lists.size() ; i ++) {
                    Map<String,String> map = new HashMap<>();
                    Map<String,String> list = lists.get(i);
                    map.put("uuid",list.get("field0059"));
                    map.put("hzfpxxqrdbh",list.get("field0058"));
                    map.put("nsrsbh",list.get("field0047"));
                    map.put("token",list.get("field0057"));
                    res.add(map);
//                    System.out.println(map.toString());
                }
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }

//    select field0084,field0094 from formmain_3966 where field0083  = ?
    @Override
    public List<String> getTaxRateByTaxItemCode(String taxItemCode) {
        List<String> res = new ArrayList<>();
        JDBCAgent agent = new JDBCAgent();
        try {
            StringBuilder sql = new StringBuilder(getTaxRateByTaxItemCode);
            List<Object> p = new ArrayList<Object>();
            p.add(taxItemCode);
            agent.execute(sql.toString(), p);
            List<Map> lists = agent.resultSetToList();
            if (lists != null && lists.size() > 0) {
                for(int i = 0 ; i < lists.size() ; i ++) {
                    String taxRate = "0";
                    Map<String,Object> list = lists.get(i);
                    if(list.get("field0094")!=null){
                        taxRate = list.get("field0094").toString();
                    }
                    res.add(taxRate);
                }
            }
        } catch (BusinessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (agent != null) {
                agent.close();
            }
        }
        return res;
    }


}
