Banninger/src/rest/openapi/restcontroller/wechatapp/tinvoiceapply/Tinvoiceapply.java

package openapi.restcontroller.wechatapp.tinvoiceapply;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import openapi.base.Controller;
import openapi.base.SQLFactory;
import openapi.base.data.Row;
import openapi.base.data.Rows;
import openapi.base.data.db.DBConnect;
import p2.pao.PaoRemote;
import p2.pao.PaoSetRemote;
import tinvoiceapply.tinvoiceapply;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import org.apache.commons.lang.StringUtils;

public class Tinvoiceapply extends Controller {

    public Tinvoiceapply(JSONObject content) {
        super(content);
    }

    public String queryInvoiceApplyList() {
        String[] sortmsg = {"case when t1.fstatus='新建'then 1 when t1.fstatus='提交'then 2 when t1.fstatus='审核'then 3 else 4 end,t1.createdate desc "};
        String sort = "case when t1.fstatus='新建'then 1 when t1.fstatus='提交'then 2 when t1.fstatus='审核'then 3 else 4 end,t1.createdate desc ";
        if (content.containsKey("sort") && Arrays.asList(sortmsg).contains(content.getString("sort"))) {
            sort = content.getString("sort");
        }
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and (t3.fagentname like'%" + whereObject.getString("condition") + "%' or t3.fagentshortname like'%" + whereObject.getString("condition") + "%' or t1.finvoicename like'%" + whereObject.getString("condition") + "%' " +
                        "or exists(select tinvoicebillid from tinvoicebill where siteid=t1.siteid and tinvoiceapplyid=t1.tinvoiceapplyid and (invoicecode like'%" + whereObject.getString("condition") + "%' or invoiceno like'%" + whereObject.getString("condition") +
                        "%')))";
            }
            if (whereObject.containsKey("fbegdate") && !"".equals(whereObject.getString("fbegdate"))) {
                where = where + " and t1.checkdate >='" + whereObject.getString("fbegdate") + "'";
            }
            if (whereObject.containsKey("fenddate") && !"".equals(whereObject.getString("fenddate"))) {
                where = where + " and t1.checkdate <='" + whereObject.getString("fenddate") + "'";
            }
        }
        SQLFactory factory = new SQLFactory(this, "经销商发票列表查询", pageSize, pageNumber, sort);
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);

        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery(factory.getSQL());

        return getReturnObject_suc_page(rows, true, 1, sortmsg).toString();
    }

    /**
     * 发票详情查询
     *
     * @return
     */
    public String queryInvoiceApplyMain() {
        long tinvoiceapplyid = content.getLong("tinvoiceapplyid");

        SQLFactory factory = new SQLFactory(this, "经销商发票详情查询");
        factory.addParameter("tinvoiceapplyid", tinvoiceapplyid);
        factory.addParameter("siteid", siteid);
        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery(factory.getSQL());

        for (Row row : rows) {
            SQLFactory orderFactory = new SQLFactory(this, "经销商发票详情查询_订单行");
            orderFactory.addParameter("tinvoiceapplyid", tinvoiceapplyid);
            orderFactory.addParameter("siteid", siteid);
            row.put("orderdetails", connect.runSqlQuery(orderFactory.getSQL()));

            SQLFactory invoiceFactory = new SQLFactory(this, "经销商发票详情查询_发票");
            invoiceFactory.addParameter("tinvoiceapplyid", tinvoiceapplyid);
            invoiceFactory.addParameter("siteid", siteid);
            row.put("invoices", connect.runSqlQuery(invoiceFactory.getSQL()));
        }
        return getReturnObject_suc(rows, false).toString();
    }

    /**
     * 发票新增修改
     *
     * @return
     */
    public String insertorModify() {
        long tinvoiceapplyid = content.getLong("tinvoiceapplyid");//新建为0
        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery("select * from tinvoiceapply where tinvoiceapplyid='" + tinvoiceapplyid + "'");
        if (!rows.isEmpty() && !"新建".equals(rows.get(0).getString("fstatus"))) {
            return getReturnObject_err("当前状态不可修改").toString();
        }
        String finvoicename = content.getString("finvoicename");//开票单位
        String finvoiceaddress = content.getString("finvoiceaddress");//开票单位地址
        String finvoicetaxnumber = content.getString("finvoicetaxnumber");//开票单位税号
        String fnotes = nullToStr(content.getString("fnotes"));//备注
        JSONArray orderdetailsArray = content.getJSONArray("orderdetails");
        PaoSetRemote tinvoiceapplySet = null;
        try {
            tinvoiceapplySet = getP2ServerPaoSet("tinvoiceapply", hrid, "siteid='" + siteid + "' and tinvoiceapplyid='" + tinvoiceapplyid + "'");
            tinvoiceapplySet.setApp("INVOICEAPP");
            PaoRemote tinvoiceapply = null;
            if (tinvoiceapplyid <= 0 || tinvoiceapplySet.isEmpty()) {
                tinvoiceapply = tinvoiceapplySet.addAtEnd();
                tinvoiceapply.setValue("fagentnum", fagentnum, 11L);
                content.put("tinvoiceapplyid", tinvoiceapply.getUniqueIDValue());
            } else {
                tinvoiceapply = tinvoiceapplySet.getPao(0);
            }
            tinvoiceapply.setValue("finvoicename", finvoicename, 11L);
            tinvoiceapply.setValue("finvoiceaddress", finvoiceaddress, 11L);
            tinvoiceapply.setValue("finvoicetaxnumber", finvoicetaxnumber, 2L);
            tinvoiceapply.setValue("fnotes", fnotes, 11L);

            PaoSetRemote tinvoiceapplyorderset = tinvoiceapply.getPaoSet("tinvoiceapplyorder");
            HashMap<String, PaoRemote> tinvoiceapplyordermap = getPaoMap(tinvoiceapplyorderset, "saorderdetailid");

            ArrayList<String> donotdeleteid = new ArrayList<>();

            for (Object o : orderdetailsArray) {
                JSONObject orderdetailObject = (JSONObject) o;
                String saorderid = orderdetailObject.getString("saorderid");
                String saorderdetailid = orderdetailObject.getString("saorderdetailid");
                //double finvoiceqty = orderdetailObject.getDouble("finvoiceqty");//开票数量
                double finvoiceamount = orderdetailObject.getDouble("finvoiceamount");//开票金额

                PaoRemote detailPao = null;
                if (tinvoiceapplyordermap.containsKey(saorderdetailid)) {
                    detailPao = tinvoiceapplyordermap.get(saorderdetailid);
                } else {
                    /**
                     * 如果id不为0表示该订单行本身不存在，需要增加行
                     */
                    detailPao = tinvoiceapplyorderset.addAtEnd();
                    detailPao.setValue("siteid", siteid, 11L);
                    detailPao.setValue("tinvoiceapplyid", tinvoiceapply.getUniqueIDValue(), 11L);
                    detailPao.setValue("saorderid", saorderid, 2L);
                    detailPao.setValue("saorderdetailid", saorderdetailid, 2L);
                }
                if (detailPao != null) {
                    //detailPao.setValue("finvoiceqty", finvoiceqty, 2L);
                    detailPao.setValue("finvoiceamount", finvoiceamount, 2L);
                }
                donotdeleteid.add(saorderdetailid);
            }
            /**
             * 删除多余的行
             */
            for (String id : tinvoiceapplyordermap.keySet()) {
                if (!donotdeleteid.contains(id)) {
                    if (!tinvoiceapplyordermap.get(id).isModified()) {
                        tinvoiceapplyordermap.get(id).delete();
                    }
                }
            }
            tinvoiceapplySet.save();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                tinvoiceapplySet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return queryInvoiceApplyMain();
    }

    public String invoiceApplysubmit() {
        long tinvoiceapplyid = content.getLong("tinvoiceapplyid");//新建为0
        PaoSetRemote tinvoiceapplySet = null;
        try {
            tinvoiceapplySet = getP2ServerPaoSet("tinvoiceapply", hrid, "siteid='" + siteid + "' and tinvoiceapplyid='" + tinvoiceapplyid + "'");
            tinvoiceapplySet.setApp("INVOICEAPP");
            tinvoiceapply tinvoiceapply = (tinvoiceapply) tinvoiceapplySet.getPao(0);
            tinvoiceapply.submitvalidate(true);
            tinvoiceapply.submit(true);
            tinvoiceapplySet.save();
        } catch (Exception e) {
            return getReturnObject_err(e.getMessage()).toString();
        } finally {
            try {
                tinvoiceapplySet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return getReturnObject_suc().toString();
    }

    public String invoiceApplydelete() {
        long tinvoiceapplyid = content.getLong("tinvoiceapplyid");//新建为0
        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery("select * from tinvoiceapply where tinvoiceapplyid='" + tinvoiceapplyid + "'");
        if (rows.isEmpty() || !"新建".equals(rows.get(0).getString("fstatus"))) {
            return getReturnObject_err("非新建状态下不可删除").toString();
        }
        String status = connect.runSqlUpdate("delete from tinvoiceapply where tinvoiceapplyid='" + tinvoiceapplyid + "'");
        if ("true".equals(status)) {
            return getReturnObject_suc().toString();
        } else {
            return getReturnObject_err("删除失败！").toString();
        }
    }

    /**
     * 开票单位选择
     *
     * @return
     */
    public String queryInvoiceName() {
        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery("select * from tinvoiceapply_taxview where siteid='" + siteid + "' and fpayagentnum='" + fagentnum + "'");
        return getReturnObject_suc(rows, false).toString();
    }

    /**
     * 开票订单行选择
     *
     * @return
     * @throws ParseException 
     */
    public String querySaorderDetail() {
        //String finvoicetaxnumber = content.getString("finvoicetaxnumber");
        long tinvoiceapplyid = content.getLong("tinvoiceapplyid");
        String fbegdate = content.getString("fbegdate");
        String fenddate = content.getString("fenddate");
        String fprojectname = content.getString("fprojectname");
        SQLFactory factory = new SQLFactory(this, "开票订单行选择查询");
        factory.addParameter("siteid", siteid);
        factory.addParameter("fagentnum", fagentnum);
        if(!"".equals(fbegdate)){
            String wherestr1 = " and sdate.closedate1 >='"+fbegdate+"' and sdate.closedate1 <='"+fenddate+"'";
            factory.addParameter_SQL("where1", wherestr1);
        }else{
            factory.addParameter_SQL("where1", "");
        }
        if(!"".equals(fbegdate)){
            String wherestr2 = " and tp.fprojectname like '%"+fprojectname+"%'";
            factory.addParameter_SQL("where2", wherestr2);
        }else{
            factory.addParameter_SQL("where2", "");
        }

        //factory.addParameter("finvoicetaxnumber", finvoicetaxnumber);
        factory.addParameter("tinvoiceapplyid", tinvoiceapplyid);
        System.out.println("factory.getSQL()==="+factory.getSQL());

        DBConnect connect = new DBConnect();
        Rows rows = connect.runSqlQuery(factory.getSQL());
        return getReturnObject_suc(rows, false).toString();

    }
}