Banninger/src/rest/openapi/restcontroller/wechatapp/titem/Titem.java

/**
 *
 */
package openapi.restcontroller.wechatapp.titem;

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.RowsMap;
import openapi.base.data.RowsPool.RowsPool;
import openapi.base.data.db.DBConnect;
import p2.pao.PaoRemote;
import p2.pao.PaoSetRemote;
import p2.util.P2Exception;
import titem.icinvbalMsg;
import titem.titem;

import java.sql.SQLData;
import java.util.Arrays;
import java.util.HashMap;

/**
 * @author Administrator
 *
 */
public class Titem extends Controller {
    /**
     * @param content
     */
    public Titem(JSONObject content) {
        super(content);
    }

    public String query() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        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(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }

            if (whereObject.containsKey("fvalue8") && whereObject.getBoolean("fvalue8")) {
                //如果是中线碟阀
                where = where + " and t1.fvalue8=1";
            }
            if (whereObject.containsKey("fvalue7") && !"".equals(whereObject.getString("fvalue7"))) {
                where = where + " and t1.fvalue7 ='" + whereObject.getString("fvalue7") + "'";
            }
            if (whereObject.containsKey("fvalue6") && !"".equals(whereObject.getString("fvalue6"))) {
                where = where + " and t1.fvalue6 ='" + whereObject.getString("fvalue6") + "'";
            }
            if (whereObject.containsKey("fvalue5") && !"".equals(whereObject.getString("fvalue5"))) {
                where = where + " and t1.fvalue5 ='" + whereObject.getString("fvalue5") + "'";
            }
            if (whereObject.containsKey("fvalue4") && !"".equals(whereObject.getString("fvalue4"))) {
                where = where + " and t1.fvalue4 ='" + whereObject.getString("fvalue4") + "'";
            }
            if (whereObject.containsKey("fvalue3") && !"".equals(whereObject.getString("fvalue3"))) {
                where = where + " and t1.fvalue3 ='" + whereObject.getString("fvalue3") + "'";
            }
            if (whereObject.containsKey("fvalue2") && !"".equals(whereObject.getString("fvalue2"))) {
                where = where + " and t1.fvalue2 ='" + whereObject.getString("fvalue2") + "'";
            }
            if (whereObject.containsKey("fvalue1") && !"".equals(whereObject.getString("fvalue1"))) {
                where = where + " and t1.fvalue1 ='" + whereObject.getString("fvalue1") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "货品档案列表查询", pageSize, pageNumber, sort);
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());

        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");

        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            double foldprice = titem.getCardPriceByAagentnum(fagentnum);
            double fprice = titem.getAgentSalePrice(foldprice, fagentnum);
            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");

            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            //库存数量
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", fprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }
        titemset.close();
        return getReturnObject_suc_page(rows, true, 1, sortmsg).toString();
    }

    public String query_tool() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        if (content.containsKey("sort") && Arrays.asList(sortmsg).contains(content.getString("sort"))) {
            sort = content.getString("sort");
        }

        /**
         * 过滤条件设置
         */
        String where = " t1.fistool=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");

            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                //where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
                where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fproductstandard") && !"".equals(whereObject.getString("fproductstandard"))) {
                where = where + " and t1.fproductstandard ='" + whereObject.getString("fproductstandard") + "'";
            }

            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    // where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "工具档案列表查询", pageSize, pageNumber, sort);
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());
        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");

        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            double foldprice = titem.getCardPriceByAagentnum(fagentnum);
            double fprice = titem.getAgentSalePrice(foldprice, fagentnum);
            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");

            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", fprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }

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


    public String query_toolsaler() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        if (content.containsKey("sort") && Arrays.asList(sortmsg).contains(content.getString("sort"))) {
            sort = content.getString("sort");
        }

        /**
         * 过滤条件设置
         */
        String where = " t1.fistool=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");

            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                // where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
                where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fproductstandard") && !"".equals(whereObject.getString("fproductstandard"))) {
                where = where + " and t1.fproductstandard ='" + whereObject.getString("fproductstandard") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "工具档案列表查询_业务员", pageSize, pageNumber, sort);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());
        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);
        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");


        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            double foldprice = titem.getCardPriceByGrade("1");
            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");
            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", foldprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }

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

    /**
     * 订单商品添加查询
     * @return
     */
    public String query_order() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         *排序条件设置
         */
        String[] sortmsg = {"t6.sequence,t5.fgroupnum,t5.frownum"};
        String sort = sortmsg[0];
        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(t1.fitemno like'%" + whereObject.getString("condition") + "%' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "货品档案列表查询2", pageSize, pageNumber, sort);
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());

        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, rows.toArray("fitemno"));

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", rows.toArray("fitemno"));
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");


        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);
            double foldprice = titem.getCardPriceByGrade("1");
            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");
            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", foldprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }

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

    /**
     * 简单商品查询
     * @return
     */
    public String query_simpleList() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();
        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        if (content.containsKey("sort") && Arrays.asList(sortmsg).contains(content.getString("sort"))) {
            sort = content.getString("sort");
        }
        /**
         * 是否包含价格
         */
        boolean withprice = content.containsKey("withprice") && content.getString("withprice").equals("1");

        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");

            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t1.fitemno like'%" + whereObject.getString("condition") + "%' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec like'%" + whereObject.getString("fspec") + "%'";
            }
            if (whereObject.containsKey("fmodel") && !"".equals(whereObject.getString("fmodel"))) {
                where = where + " and t1.fmodel like'%" + whereObject.getString("fmodel") + "%'";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }

            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "简单货品档案列表查询", pageSize, pageNumber, sort);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());
        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, rows.toArray("fitemno"));

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", rows.toArray("fitemno"));
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");


        HashMap<String, PaoRemote> itemmap = null;
        if (withprice) {
            /**
             * 获取商品相关信息
             */
            PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + rows.getInWhere("fitemno") + " and siteid='" + siteid + "'");
            itemmap = getPaoMap(titemset, "fitemno");
        }

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            if (withprice) {
                titem titem = (titem) itemmap.get(fitemno);
                if (content.containsKey("fprojectnum") && !content.getString("fprojectnum").equals("")) {
                    titem.fprojectnum = content.getString("fprojectnum");
                }
                double foldprice = titem.getCardPriceByAagentnum(fagentnum);
                double fprice = titem.getAgentSalePrice(foldprice, fagentnum);
                //牌价
                itemrow.put("foldprice", foldprice);
                //销售价
                itemrow.put("fprice", fprice);

                //营销分类
                itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
            }
        }
        return getReturnObject_suc_page(rows, true, 1, sortmsg).toString();
    }


    /**
     * 项目订单商品选择
     * @return
     */
    public String query_projectitemList() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();
        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        if (content.containsKey("sort") && Arrays.asList(sortmsg).contains(content.getString("sort"))) {
            sort = content.getString("sort");
        }
        /**
         * 是否包含价格
         */
        boolean withprice = content.containsKey("withprice") && content.getString("withprice").equals("1");

        String fprojectnum = content.getString("fprojectnum");//项目编号
        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");

            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t1.fitemno like'%" + whereObject.getString("condition") + "%' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "项目订单货品档案列表查询", pageSize, pageNumber, sort);
        factory.addParameter("fprojectnum", fprojectnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());
        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, rows.toArray("fitemno"));

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", rows.toArray("fitemno"));
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");


        HashMap<String, PaoRemote> itemmap = null;
        if (withprice) {
            /**
             * 获取商品相关信息
             */
            PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + rows.getInWhere("fitemno") + " and siteid='" + siteid + "'");
            itemmap = getPaoMap(titemset, "fitemno");
        }

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            if (withprice) {
                titem titem = (titem) itemmap.get(fitemno);
                if (content.containsKey("fprojectnum") && !content.getString("fprojectnum").equals("")) {
                    titem.fprojectnum = content.getString("fprojectnum");
                }
                double foldprice = titem.getCardPriceByAagentnum(fagentnum);
                double fprice = titem.getAgentSalePrice(foldprice, fagentnum);
                //牌价
                itemrow.put("foldprice", foldprice);
                //销售价
                itemrow.put("fprice", fprice);

                //营销分类
                itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
            }
        }
        return getReturnObject_suc_page(rows, true, 1, sortmsg).toString();
    }

    public String query_saler() throws Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         *排序条件设置
         */
        String[] sortmsg = {"t1.fitemno", "t1.fitemname", "t1.fspec"};
        String sort = sortmsg[0];
        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(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = drpConnect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }

            if (whereObject.containsKey("fvalue8") && whereObject.getBoolean("fvalue8")) {
                //如果是中线碟阀
                where = where + " and t1.fvalue8=1";
            }
            if (whereObject.containsKey("fvalue7") && !"".equals(whereObject.getString("fvalue7"))) {
                where = where + " and t1.fvalue7 ='" + whereObject.getString("fvalue7") + "'";
            }
            if (whereObject.containsKey("fvalue6") && !"".equals(whereObject.getString("fvalue6"))) {
                where = where + " and t1.fvalue6 ='" + whereObject.getString("fvalue6") + "'";
            }
            if (whereObject.containsKey("fvalue5") && !"".equals(whereObject.getString("fvalue5"))) {
                where = where + " and t1.fvalue5 ='" + whereObject.getString("fvalue5") + "'";
            }
            if (whereObject.containsKey("fvalue4") && !"".equals(whereObject.getString("fvalue4"))) {
                where = where + " and t1.fvalue4 ='" + whereObject.getString("fvalue4") + "'";
            }
            if (whereObject.containsKey("fvalue3") && !"".equals(whereObject.getString("fvalue3"))) {
                where = where + " and t1.fvalue3 ='" + whereObject.getString("fvalue3") + "'";
            }
            if (whereObject.containsKey("fvalue2") && !"".equals(whereObject.getString("fvalue2"))) {
                where = where + " and t1.fvalue2 ='" + whereObject.getString("fvalue2") + "'";
            }
            if (whereObject.containsKey("fvalue1") && !"".equals(whereObject.getString("fvalue1"))) {
                where = where + " and t1.fvalue1 ='" + whereObject.getString("fvalue1") + "'";
            }
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "货品档案列表查询_业务员", pageSize, pageNumber, sort);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());

        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");

        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            double foldprice = titem.getCardPriceByGrade("1");
            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");

            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            //库存数量
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", foldprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }
        titemset.close();
        return getReturnObject_suc_page(rows, true, 1, sortmsg).toString();
    }

    public String queryone() throws P2Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "货品档案列表查询");
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());

        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");

        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");
            double foldprice = 0;
            double fprice = 0;
            if ("业务员".equals(usertype) && ("".equals(fagentnum) || fagentnum == null)) {
                PaoSetRemote titemsaleprice = titem.getPaoSet("titemsaleprice");
                if (!titemsaleprice.isEmpty()) {
                    foldprice = titemsaleprice.getPao(0).getDouble("fprice");
                }
                fprice = foldprice;
            } else {
                foldprice = titem.getCardPriceByAagentnum(fagentnum);
                fprice = titem.getAgentSalePrice(foldprice, fagentnum);
            }

            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", fprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }
        titemset.close();
        return getReturnObject_suc(rows, true).toString();
    }


    public String queryone_saler() throws P2Exception {
        /**
         * 创建数据库连接
         */
        DBConnect drpConnect = new DBConnect();

        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
        }
        /**
         * SQL货品档案查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "货品档案列表查询_业务员");
        factory.addParameter("fagentnum", fagentnum);
        factory.addParameter("siteid", siteid);
        factory.addParameter_SQL("where", where);
        Rows rows = drpConnect.runSqlQuery(factory.getSQL());

        /**
         * 获取品号批查条件
         */
        String itemwhere = rows.getInWhere("fitemno");
        String[] itemarray = rows.toArray("fitemno");

        /**
         *商品图片查询参数设置，并将结果存放置map
         */
        RowsMap picmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_PIC, itemarray);
        /**
         *商品产品样册查询参数设置，并将结果存放置map
         */
        RowsMap samplebookmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_SAMPLEBOOK, itemarray);
        /**
         *商品说明书查询参数设置，并将结果存放置map
         */
        RowsMap instructionsmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTRUCTIONS, itemarray);
        /**
         *商品安装教程查询参数设置，并将结果存放置map
         */
        RowsMap installationtutorialmap = RowsPool.getRowsMap(siteid, RowsPool.GroupName.ITEM_INSTALLATIONTUTORIAL, itemarray);

        /**
         * 商品营销类别
         */
        SQLFactory saleclsnumsqlFactory = new SQLFactory(this, "货品营销类别查询");
        saleclsnumsqlFactory.addParameter("siteid", siteid);
        saleclsnumsqlFactory.addParameter_in("fitemno", itemarray);
        RowsMap saleclsnumRowsMap = drpConnect.runSqlQuery(saleclsnumsqlFactory.getSQL()).toRowsMap("fitemno");

        /**
         *更新即时库存
         */
        new icinvbalMsg().caculate(siteid, rows.toArray("fitemno"), false);

        /**
         * 获取商品相关信息
         */
        PaoSetRemote titemset = getP2ServerPaoSet("titem", hrid, "fitemno in " + itemwhere + " and siteid='" + siteid + "'");
        HashMap<String, PaoRemote> itemmap = getPaoMap(titemset, "fitemno");

        /**
         * 遍历货品档案，设置商品信息
         */
        for (Row itemrow : rows) {
            String fitemno = itemrow.getString("fitemno");
            titem titem = (titem) itemmap.get(fitemno);

            String fsaorderstockstatus = titem.getString("fsaorderstockstatus");
            double ficqty = titem.getDouble("ficqty");
            double foldprice = 0;
            double fprice = 0;
            if ("业务员".equals(usertype) && ("".equals(fagentnum) || fagentnum == null)) {
                PaoSetRemote titemsaleprice = titem.getPaoSet("titemsaleprice");
                if (!titemsaleprice.isEmpty()) {
                    foldprice = titemsaleprice.getPao(0).getDouble("fprice");
                }
                fprice = foldprice;
            } else {
                foldprice = titem.getCardPriceByAagentnum(fagentnum);
                fprice = titem.getAgentSalePrice(foldprice, fagentnum);
            }

            //库存状态
            itemrow.put("fsaorderstockstatus", fsaorderstockstatus);
            itemrow.put("ficqty", ficqty);
            //牌价
            itemrow.put("foldprice", foldprice);
            //销售价
            itemrow.put("fprice", fprice);
            //图片
            itemrow.put("pics", picmap.get(fitemno));
            //产品样册
            itemrow.put("samplebook", samplebookmap.get(fitemno));
            //产品说明书
            itemrow.put("instructions", instructionsmap.get(fitemno));
            //安装教程
            itemrow.put("installationtutorial", installationtutorialmap.get(fitemno));
            //营销分类
            itemrow.put("saleclsnum", saleclsnumRowsMap.get(fitemno));
        }
        titemset.close();
        return getReturnObject_suc(rows, true).toString();
    }

    /**
     * 查询商品图片
     * @return
     */
    public String query_itempic() {
        Rows picrows = RowsPool.getRows(siteid, RowsPool.GroupName.ITEM_PIC, content.getString("fitemno"));
        return getReturnObject_suc(picrows, true).toString();
    }

    /**
     * 商品收藏
     * @return
     */
    public String collection() throws Exception {
        // 商品编号
        String fitemno = content.getString("fitemno");
        //是否收藏
        int fiscollection = content.getInt("fiscollection");

        PaoSetRemote tfavorites = getP2ServerPaoSet("tfavorites", hrid, "fagentnum='" + fagentnum + "' and fitemno='" + fitemno + "'");
        PaoRemote pao = null;
        if (tfavorites.isEmpty()) {
            pao = tfavorites.addAtEnd();
            pao.setValue("fitemno", fitemno, 11L);
            pao.setValue("fagentnum", fagentnum, 11L);
        } else {
            pao = tfavorites.getPao(0);
        }
        pao.setValue("fiscollection", fiscollection == 1, 11L);
        tfavorites.save();
        tfavorites.close();
        return getReturnObject_suc().toString();
    }

    /**
     * 商品定制信息查询
     * @return
     */
    public String query_Customized() {
        /**
         * 创建数据库连接
         */
        DBConnect connect = new DBConnect();

        /**
         * SQL定制信息列表查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "定制信息列表查询");
        factory.addParameter("fitemno", content.getString("fitemno"));
        factory.addParameter("siteid", siteid);
        Rows rows = connect.runSqlQuery(factory.getSQL());

        for (Row row : rows) {
            SQLFactory detailfac = new SQLFactory(this, "定制信息列表选项查询");
            detailfac.addParameter("titem_customizedid", row.getString("titem_customizedid"));
            Rows optionsRows = connect.runSqlQuery(detailfac.getSQL());
            row.put("options", optionsRows.toJsonArray("fvalue"));
        }
        return getReturnObject_suc(rows, true).toString();
    }

    /**
     * 商品品牌查询
     * @return
     */
    public String query_BrandList() {
        /**
         * 创建数据库连接
         */
        DBConnect connect = new DBConnect();

        /**
         * SQL定制信息列表查询参数设置并查询
         */
        SQLFactory factory = new SQLFactory(this, "品牌列表查询");
        factory.addParameter("siteid", siteid);
        Rows rows = connect.runSqlQuery(factory.getSQL());
        return getReturnObject_suc(rows, true).toString();
    }

    public String query_itemvalueclass() {
        DBConnect connect = new DBConnect();
        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = connect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }

            if (whereObject.containsKey("fvalue8") && whereObject.getBoolean("fvalue8")) {
                //如果是中线碟阀
                where = where + " and t1.fvalue8=1";
            }
            if (whereObject.containsKey("fvalue7") && !"".equals(whereObject.getString("fvalue7"))) {
                where = where + " and t1.fvalue7 ='" + whereObject.getString("fvalue7") + "'";
            }
            if (whereObject.containsKey("fvalue6") && !"".equals(whereObject.getString("fvalue6"))) {
                where = where + " and t1.fvalue6 ='" + whereObject.getString("fvalue6") + "'";
            }
            if (whereObject.containsKey("fvalue5") && !"".equals(whereObject.getString("fvalue5"))) {
                where = where + " and t1.fvalue5 ='" + whereObject.getString("fvalue5") + "'";
            }
            if (whereObject.containsKey("fvalue4") && !"".equals(whereObject.getString("fvalue4"))) {
                where = where + " and t1.fvalue4 ='" + whereObject.getString("fvalue4") + "'";
            }
            if (whereObject.containsKey("fvalue3") && !"".equals(whereObject.getString("fvalue3"))) {
                where = where + " and t1.fvalue3 ='" + whereObject.getString("fvalue3") + "'";
            }
            if (whereObject.containsKey("fvalue2") && !"".equals(whereObject.getString("fvalue2"))) {
                where = where + " and t1.fvalue2 ='" + whereObject.getString("fvalue2") + "'";
            }
            if (whereObject.containsKey("fvalue1") && !"".equals(whereObject.getString("fvalue1"))) {
                where = where + " and t1.fvalue1 ='" + whereObject.getString("fvalue1") + "'";
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "商品档案选项查询");
        sqlFactory.addParameter("fagentnum", fagentnum);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        System.err.println(sqlFactory.getSQL());
        RowsMap rowsMap = connect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("field");
        JSONObject fieldobject = new JSONObject();
        for (String field : rowsMap.keySet()) {
            fieldobject.put(field, rowsMap.get(field).toJsonArray("value"));
        }
        return getReturnObject_suc(fieldobject, true).toString();
    }

    public String query_itemvalueclass_saler() {
        DBConnect connect = new DBConnect();
        /**
         * 过滤条件设置
         */
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and(t1.fitemname like'%" + whereObject.getString("condition") + "%' or t1.fitemno='" + whereObject.getString("condition") + "' or t1.fspec like '%" + whereObject.getString("condition") + "%' or t1.fmodel like'%" + whereObject.getString("condition") + "%')";
            }
            if (whereObject.containsKey("fitemno") && !"".equals(whereObject.getString("fitemno"))) {
                where = where + " and t1.fitemno ='" + whereObject.getString("fitemno") + "'";
            }
            if (whereObject.containsKey("fspec") && !"".equals(whereObject.getString("fspec"))) {
                where = where + " and t1.fspec ='" + whereObject.getString("fspec") + "'";
            }
            if (whereObject.containsKey("fsaleclsnum") && !"".equals(whereObject.getString("fsaleclsnum"))) {
                SQLFactory flongsaleslsFactory = new SQLFactory(this, "营销类别全路径查询");
                flongsaleslsFactory.addParameter("fsaleclsnum", whereObject.getString("fsaleclsnum"));
                flongsaleslsFactory.addParameter("siteid", siteid);
                Rows flongsaleslsRows = connect.runSqlQuery(flongsaleslsFactory.getSQL());
                if (flongsaleslsRows.isEmpty()) {
                    where = where + " and 1=2";
                } else {
                    //where = where + " and t1.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%'";
                    where = where + " and exists(select *from titem_saleclsnum where titem_saleclsnum.siteid=t1.siteid and titem_saleclsnum.fitemno=t1.fitemno and titem_saleclsnum.flongsaleclsnum like'" + flongsaleslsRows.get(0).getString("flongsaleslsnum") + "%') ";
                }
            }
            if (whereObject.containsKey("fiscollection") && !"".equals(whereObject.getString("fiscollection"))) {
                where = where + " and isnull(t4.fiscollection,0) ='" + whereObject.getString("fiscollection") + "'";
            }
            if (whereObject.containsKey("fiscustomized") && !"".equals(whereObject.getString("fiscustomized"))) {
                where = where + " and t1.fiscustomized ='" + whereObject.getString("fiscustomized") + "'";
            }
            if (whereObject.containsKey("fiswuliao") && !"".equals(whereObject.getString("fiswuliao"))) {
                where = where + " and t1.fiswuliao ='" + whereObject.getString("fiswuliao") + "'";
            }
            if (whereObject.containsKey("fieldname") && !"".equals(whereObject.getString("fieldname"))) {
                where = where + " and t1.fieldname ='" + whereObject.getString("fieldname") + "'";
            }
            if (whereObject.containsKey("fbrand") && !"".equals(whereObject.getString("fbrand"))) {
                where = where + " and t1.fbrand ='" + whereObject.getString("fbrand") + "'";
            }
            if (whereObject.containsKey("fistool") && !"".equals(whereObject.getString("fistool"))) {
                where = where + " and t1.fistool ='" + whereObject.getString("fistool") + "'";
            }

            if (whereObject.containsKey("fvalue8") && whereObject.getBoolean("fvalue8")) {
                //如果是中线碟阀
                where = where + " and t1.fvalue8=1";
            }
            if (whereObject.containsKey("fvalue7") && !"".equals(whereObject.getString("fvalue7"))) {
                where = where + " and t1.fvalue7 ='" + whereObject.getString("fvalue7") + "'";
            }
            if (whereObject.containsKey("fvalue6") && !"".equals(whereObject.getString("fvalue6"))) {
                where = where + " and t1.fvalue6 ='" + whereObject.getString("fvalue6") + "'";
            }
            if (whereObject.containsKey("fvalue5") && !"".equals(whereObject.getString("fvalue5"))) {
                where = where + " and t1.fvalue5 ='" + whereObject.getString("fvalue5") + "'";
            }
            if (whereObject.containsKey("fvalue4") && !"".equals(whereObject.getString("fvalue4"))) {
                where = where + " and t1.fvalue4 ='" + whereObject.getString("fvalue4") + "'";
            }
            if (whereObject.containsKey("fvalue3") && !"".equals(whereObject.getString("fvalue3"))) {
                where = where + " and t1.fvalue3 ='" + whereObject.getString("fvalue3") + "'";
            }
            if (whereObject.containsKey("fvalue2") && !"".equals(whereObject.getString("fvalue2"))) {
                where = where + " and t1.fvalue2 ='" + whereObject.getString("fvalue2") + "'";
            }
            if (whereObject.containsKey("fvalue1") && !"".equals(whereObject.getString("fvalue1"))) {
                where = where + " and t1.fvalue1 ='" + whereObject.getString("fvalue1") + "'";
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "商品档案选项查询_业务员");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        System.err.println(sqlFactory.getSQL());
        RowsMap rowsMap = connect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("field");
        JSONObject fieldobject = new JSONObject();
        for (String field : rowsMap.keySet()) {
            fieldobject.put(field, rowsMap.get(field).toJsonArray("value"));
        }
        return getReturnObject_suc(fieldobject, true).toString();
    }
}