Пример #1
0
        /// <summary>
        /// 获取黄页模型
        /// </summary>
        /// <returns></returns>
        ///
        DataTable ListPageModel()
        {
            string    strSql = "Select * from ZL_Model Where TableName like 'ZL_Reg_%' and ModelType=3";
            DataTable dt     = DBCenter.ExecuteTable(strSql, null);

            return(dt);
        }
Пример #2
0
        DataTable ListModel()
        {
            string    strSql = "Select * from ZL_Model Where ModelType=1 or ModelType=5 or ModelType=2 order by ModelID ";
            DataTable dt     = DBCenter.ExecuteTable(strSql, null);

            return(dt);
        }
Пример #3
0
        /// <summary>
        /// 获取互动模型
        /// </summary>
        /// <returns></returns>
        ///
        DataTable ListPubModel()
        {
            string    strSql = "Select * from ZL_Model Where ModelType=7";
            DataTable dt     = DBCenter.ExecuteTable(strSql, null);

            return(dt);
        }
Пример #4
0
        /// <summary>
        /// 读取用户商城模型列表
        /// </summary>
        /// <returns></returns>
        ///
        DataTable ListUserShopModel()
        {
            string    strSql = "Select * from ZL_Model Where ModelType=5";
            DataTable dt     = DBCenter.ExecuteTable(strSql);

            return(dt);
        }
Пример #5
0
        /// <summary>
        ///获取子节点列表,不包含父节点信息
        /// </summary>
        /// <param name="pid">父节点ID</param>
        /// <param name="isall">true所有子节点信息,false:仅下一级</param>
        public DataTable SelByPid(int pid, bool isall = false)
        {
            DataTable dt = new DataTable();

            if (isall)
            {
                //string sql = "with Tree as(SELECT *,cast(1 as int) as [level] FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.*,b.[level]+1 FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
                //DataTable dt = Sel();
                //string nids = "";
                //RecursionByPid(dt, pid, ref nids);
                //nids = StrHelper.PureIDSForDB(nids);
                //dt.DefaultView.RowFilter = "NodeID IN(" + nids + ")";
                //return dt.DefaultView.ToTable();
                //WITH Tree AS(
                //SELECT * FROM ZL_Node WHERE ParentID=0 UNION ALL SELECT A.* FROM ZL_Node A JOIN Tree B on A.ParentID=B.NodeID)
                //SELECT * FROM Tree ORDER BY OrderID,NodeID ASC
                string sql = "with Tree as(SELECT * FROM ZL_Node WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM ZL_Node a JOIN Tree b on a.ParentID=b.NodeID) SELECT *,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID AND ZStatus=99) ChildCount FROM Tree AS A ORDER BY OrderID,NodeID ASC";
                //获取父节点下子级后,再去除父级
                string oracle = "SELECT * FROM(SELECT * FROM ZL_Node START WITH NODEID IN(" + pid + ") CONNECT BY nocycle prior NodeID=ParentID )WHERE NodeID!=" + pid;
                dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));
            }
            else
            {
                string fields = "A.*,(SELECT Count(NodeID) From ZL_Node WHERE A.NodeID=ParentID) ChildCount ";
                //string sql = "SELECT " + fields + " FROM " + TbName + " A WHERE ParentID=" + pid+" ORDER BY OrderID,NodeID ASC";
                dt = DBCenter.SelWithField(TbName, fields, "ParentID=" + pid, defOrder);
            }
            dt.DefaultView.RowFilter = "ZStatus IS NULL OR ZStatus NOT IN ('" + (int)ZLEnum.ConStatus.Recycle + "')";
            dt = dt.DefaultView.ToTable();
            return(dt);
        }
Пример #6
0
        public static int SelFirstNodeID(string TbName, string PK, int nodeid, ref string nodeTree)
        {
            int firstNodeID = 0;

            if (nodeid < 1)
            {
                return(firstNodeID);
            }
            string    sql    = "with f as(SELECT * FROM " + TbName + " WHERE " + PK + "=" + nodeid + " UNION ALL SELECT A.* FROM " + TbName + " A, f WHERE a." + PK + "=f.ParentID) SELECT * FROM " + TbName + " WHERE " + PK + " IN(SELECT " + PK + " FROM f)";
            string    oracle = "SELECT * FROM " + TbName + " Where " + PK + "=" + nodeid;//[need deal]
            DataTable dt     = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));

            if (dt.Rows.Count < 1)
            {
                return(firstNodeID);
            }
            //顺序无法确定,首位可能是第一,也可能是最后???
            foreach (DataRow dr in dt.Rows)
            {
                if (DataConvert.CLng(dr["ParentID"]) == 0)
                {
                    firstNodeID = Convert.ToInt32(dr[PK]);
                }
                nodeTree += dr[PK] + ",";
            }
            nodeTree = nodeTree.Trim(',');
            return(firstNodeID);
        }
Пример #7
0
        /// <summary>
        /// 选取最近几次的不重复的发票信息(根据Head)
        /// </summary>
        /// <returns></returns>
        public DataTable SelForTop(int uid)
        {
            string    childSql = "SELECT max(a.ID) FROM ZL_Order_Invoice A LEFT JOIN ZL_Orderinfo B ON A.ORDERID = B.ID WHERE B.UserID=" + uid + " GROUP BY A.InvHead, b.Userid";
            string    sql      = "SELECT TOP 5 * FROM ZL_Order_Invoice WHERE ID IN (" + childSql + ")";
            DataTable dt       = DBCenter.ExecuteTable(sql);

            return(dt);
        }
Пример #8
0
    public DataTable SelByJS(Com_Filter filter)
    {
        List <SqlParameter> sp = new List <SqlParameter>();

        string where = GetWhere(filter, sp);
        return(DBCenter.ExecuteTable("SELECT A.Attribute,SUM(CAST(A.AllMoney_Json AS MONEY)) AllMoney_Json "
                                     + " FROM ZL_CartPro A LEFT JOIN ZL_OrderInfo B ON A.OrderListID=B.ID "
                                     + " WHERE A.Attribute!='' AND " + where + " GROUP BY Attribute", sp));
    }
Пример #9
0
        public static DataTable SelByPid(string TbName, string PK, int pid)
        {
            string sql = "with Tree as(SELECT * FROM {0} WHERE ParentID=" + pid + " UNION ALL SELECT a.* FROM {0} a JOIN Tree b on a.ParentID=b.{1}) SELECT * FROM Tree AS A ORDER {1} ASC";
            //获取父节点下子级后,再去除父级
            string oracle = "SELECT * FROM(SELECT * FROM {0} START WITH {1} IN(" + pid + ") CONNECT BY nocycle prior {1}=ParentID )WHERE {1}!=" + pid;

            sql    = string.Format(sql, TbName, PK);
            oracle = string.Format(oracle, TbName, PK);
            DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));

            return(dt);
        }
Пример #10
0
    /// <summary>
    /// 获取子级链,包含起始ID
    /// </summary>
    private string SelChildTree(string tbname, string pk, string pfield, int startID)
    {
        string ids = "";
        string sql = "WITH TREE as(SELECT * FROM {ZL_Node} WHERE {ParentID}=" + startID + " UNION ALL SELECT a.* FROM {ZL_Node} a JOIN Tree b on a.{ParentID}=b.{NodeID}) SELECT {NodeID} FROM Tree AS A";

        sql = sql.Replace("{ZL_Node}", tbname).Replace("{NodeID}", pk).Replace("{ParentID}", pfield);
        DataTable dt = DBCenter.ExecuteTable(sql);

        foreach (DataRow dr in dt.Rows)
        {
            ids += dr[pk] + ",";
        }
        return(ids.Trim(','));
    }
Пример #11
0
        public DataTable SelOrderCount()
        {
            string tbname = "ZL_Orderinfo";
            string sql    = "SELECT COUNT(ID) AS [all]";

            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend + ") unexp";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay + ") unpaid";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend + ") exped";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + ") finished";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund + ") unrefund";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ") refunded";
            sql += ",(SELECT COUNT(ID) FROM " + tbname + " WHERE Aside=1) recycle";
            sql += " FROM " + tbname + " WHERE Aside=0";
            return(DBCenter.ExecuteTable(sql));
        }
Пример #12
0
    /// <summary>
    /// 返回父级链,包含起始ID
    /// </summary>
    /// <param name="tbname">ZL_Node</param>
    /// <param name="pk">NodeID</param>
    /// <param name="pfield">示例:ParentID</param>
    /// <param name="startid">起始的ID值,如UserID的值</param>
    /// <param name="ids">返回的层级IDS</param>
    /// <returns></returns>
    private string SelParentTree(string tbname, string pk, string pfield, int startID)
    {
        string ids = "";

        if (startID < 1)
        {
            return(ids);
        }
        string sql    = "WITH f AS(SELECT * FROM {ZL_Node} WHERE {NodeID}=" + startID + " UNION ALL SELECT A.* FROM {ZL_Node} A, f WHERE a.{NodeID}=f.{ParentID}) SELECT * FROM {ZL_Node} WHERE {NodeID} IN(SELECT {NodeID} FROM f)";
        string oracle = "SELECT * FROM {tbname} START WITH {NodeID} =" + startID + " CONNECT BY PRIOR {ParentID} = {NodeID}";

        sql = sql.Replace("{ZL_Node}", tbname).Replace("{NodeID}", pk).Replace("{ParentID}", pfield);
        DataTable dt = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));

        foreach (DataRow dr in dt.Rows)
        {
            ids += dr[pk] + ",";
        }
        return(ids.Trim(','));
    }
Пример #13
0
        public int SelFirstID(int id, ref string nodeTree)
        {
            if (id < 1)
            {
                return(0);
            }
            string    sql = "with f as(SELECT * FROM " + TbName + " WHERE ID=" + id + " UNION ALL SELECT A.* FROM " + TbName + " A, f WHERE a.ID=f.PID) SELECT * FROM " + TbName + " WHERE ID IN(SELECT ID FROM f)";
            DataTable dt  = DBCenter.ExecuteTable(sql);

            if (dt.Rows.Count < 1)
            {
                return(0);
            }
            foreach (DataRow dr in dt.Rows)
            {
                nodeTree += dr[PK] + ",";
            }
            nodeTree = nodeTree.Trim(',');
            return(DataConvert.CLng(dt.Rows[0][PK]));
        }
Пример #14
0
        /// <summary>
        /// 获取指定节点的起始父节点,并生成节点树,不包含0
        /// 注意节点不要存在循环(如NodeID=ParentID,或圆形)
        /// </summary>
        public int SelFirstNodeID(int nodeid, ref string nodeTree)
        {
            if (nodeid < 1)
            {
                return(0);
            }
            string sql = "with f as(SELECT * FROM ZL_Node WHERE NodeID=" + nodeid + " UNION ALL SELECT A.* FROM ZL_Node A, f WHERE a.NodeID=f.ParentID) SELECT * FROM ZL_Node WHERE NodeID IN(SELECT NodeID FROM f)";
            //string oracle = "SELECT * FROM ZL_Node START WITH NODEID =" + nodeid + " CONNECT BY PRIOR ParentID = NodeID";
            string    oracle = "SELECT * FROM ZL_Node Where NodeID=" + nodeid;//[need deal]
            DataTable dt     = DBCenter.ExecuteTable(DBCenter.GetSqlByDB(sql, oracle));

            if (dt.Rows.Count < 1)
            {
                return(0);
            }
            foreach (DataRow dr in dt.Rows)
            {
                nodeTree += dr["NodeID"] + ",";
            }
            nodeTree = nodeTree.Trim(',');
            return(DataConvert.CLng(dt.Rows[0]["NodeID"]));
        }
Пример #15
0
        public IActionResult Default_Result()
        {
            string    SQL = EncryptHelper.Base64Decrypt(Request.Form["sql"]);
            DataTable dt  = null;

            try
            {
                if (!string.IsNullOrEmpty(SQL))
                {
                    dt = DBCenter.ExecuteTable(SQL);
                }
                else
                {
                    throw new Exception("未指定需要查询的参数");
                }
            }
            catch (Exception ex)
            {
                dt          = null;
                ViewBag.err = ex.Message;
            }
            return(PartialView());
        }
Пример #16
0
        public PageSetting SelForOrderList(Filter_Order filter)
        {
            string where = "OrderType!=" + (int)M_OrderList.OrderEnum.Hide;
            string whereCount      = " WHERE OrderType!=" + (int)M_OrderList.OrderEnum.Hide;
            List <SqlParameter> sp = new List <SqlParameter>();

            //是否包含回收站订单
            if (filter.aside != -100)
            {
                where += " AND Aside=" + filter.aside;
            }
            #region 用户中心快速筛选
            switch (filter.fast)
            {
            case "all":    //全部(不含回收站)
                where += " AND Aside=0";
                break;

            case "unpaid":    //待付款==状态为未付款的
                where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay;
                break;

            case "prepay":    //已预付款(尚未支付尾款的订单)
                where += " AND Aside=0 AND Delivery=1 AND IsCount=0";
                break;

            case "paid":    //已支付(只支付了预付款的不在此列)
                where += " AND Aside=0 AND ((PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1)) ";
                break;

            case "needpay":    //需付款
                where += " AND Aside=0 AND PaymentStatus=0";
                break;

            case "receive":    //需确认收货
                where += " AND Aside=0 AND StateLogistics=1";
                break;

            case "comment":    //已付款未评价
            //where += " AND (OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")";
            //where += " AND (SELECT COUNT(*) FROM ZL_CartPro WHERE Orderlistid=ID AND (AddStatus IS NULL OR AddStatus=''))>0";//AddStatus中会有退货记录,所以筛选为必须为空
            //break;
            case "finish":    //客户已付款收货 ||客户已完成退货
            {
                where += string.Format(" AND ({0} OR {1})",
                                       "(OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")",
                                       "(PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ")");
            }
            break;

            case "issure":
                where += " AND IsSure=0 ";
                break;

            case "recycle":    //订单回收站
                where = "OrderType!=" + (int)M_OrderList.OrderEnum.Hide + " AND Aside=1";
                break;
            }
            #endregion
            #region 后台快速筛选
            switch (filter.addon)
            {
            case "unpaid":    //待付款==状态为未付款的
                where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay;
                break;

            case "prepay":    //已预付款(尚未支付尾款的订单)
                where += " AND Delivery=1 AND IsCount=0";
                break;

            case "paid":
                where += " AND ((PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1)) ";
                //where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed;
                break;

            case "unexp":    //待发货==已付款+未发货
                where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend;
                break;

            case "exped":    //已发货==大于未发货状态的订单
                where += " AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend;
                break;

            case "finished":
                //where += " AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived;
                //后期移除,暂时支持预付
                where += " AND (OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ")";
                break;

            case "unrefund":
                where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund;
                break;

            case "refunded":
                where += " AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded;
                break;

            case "recycle":    //订单回收站==已关闭
                where = " Aside=1 ";
                break;

            case "all":    //全部(不含回收站)
            default:
                break;
            }
            #endregion
            //店铺类型
            switch (filter.storeType)
            {
            case "all":
                break;

            case "store":
                where      += " AND StoreID>0";
                whereCount += " AND StoreID>0";
                break;

            case "shop":
                where      += " AND StoreID=0 ";
                whereCount += " AND StoreID=0";
                break;

            default:
                if (DataConvert.CLng(filter.storeType) > 0)
                {
                    where      += " AND StoreID=" + DataConvert.CLng(filter.storeType) + " ";
                    whereCount += " AND StoreID=" + DataConvert.CLng(filter.storeType) + " ";
                }
                break;
            }
            if (filter.isSure != -100)
            {
                where += " AND IsSure=" + filter.isSure;
            }
            //订单类型,未指定则抽出常规订单
            if (string.IsNullOrEmpty(filter.orderType))
            {
                where      += " AND OrderType IN (0,1,4)";
                whereCount += " AND OrderType IN (0,1,4)";
            }
            else if (filter.orderType.Equals("-100") || filter.orderType.Equals("-1"))
            {
            }
            else
            {
                SafeSC.CheckIDSEx(filter.orderType);
                where      += " AND OrderType IN (" + filter.orderType + ")";
                whereCount += " AND OrderType IN (" + filter.orderType + ")";
            }
            //商品名,订单号,用户名,手机号,用户ids
            if (!string.IsNullOrEmpty(filter.proname))
            {
                where += " AND ProName LIKE @proname"; sp.Add(new SqlParameter("proname", "%" + filter.proname + "%"));
            }
            if (!string.IsNullOrEmpty(filter.orderno))
            {
                where += " AND OrderNo LIKE @orderno"; sp.Add(new SqlParameter("orderno", "%" + filter.orderno + "%"));
            }
            if (!string.IsNullOrEmpty(filter.reuser))
            {
                where += " AND (Rename LIKE @reuser OR Receiver LIKE @reuser)"; sp.Add(new SqlParameter("reuser", "%" + filter.reuser + "%"));
            }
            if (!string.IsNullOrEmpty(filter.mobile))
            {
                where += " AND MobileNum LIKE @mobile"; sp.Add(new SqlParameter("mobile", "%" + filter.mobile + "%"));
            }
            if (!string.IsNullOrEmpty(filter.uids) && SafeSC.CheckIDS(filter.uids))
            {
                where += " AND UserID IN (" + filter.uids + ")";
            }
            //下单日期
            if (!string.IsNullOrEmpty(filter.stime))
            {
                DateTime result = DateTime.Now;
                if (DateTime.TryParse(filter.stime, out result))
                {
                    where += " AND AddTime>=@stime"; sp.Add(new SqlParameter("stime", result.ToString("yyyy/MM/dd 00:00:00")));
                }
            }
            if (!string.IsNullOrEmpty(filter.etime))
            {
                DateTime result = DateTime.Now;
                if (DateTime.TryParse(filter.etime, out result))
                {
                    where += " AND AddTime<=@etime"; sp.Add(new SqlParameter("etime", result.ToString("yyyy/MM/dd 23:59:59")));
                }
            }
            //发货时间
            if (!string.IsNullOrEmpty(filter.expstime) || !string.IsNullOrEmpty(filter.expetime))
            {
                where += " AND ExpSTime IS NOT NULL ";
            }
            if (!string.IsNullOrEmpty(filter.expstime))//按发货日期筛选
            {
                DateTime result = DateTime.Now;
                if (DateTime.TryParse(filter.expstime, out result))
                {
                    where += " AND ExpSTime>=@expstime"; sp.Add(new SqlParameter("expstime", result.ToString("yyyy/MM/dd 00:00:00")));
                }
            }
            if (!string.IsNullOrEmpty(filter.expetime))
            {
                DateTime result = DateTime.Now;
                if (DateTime.TryParse(filter.expetime, out result))
                {
                    where += " AND ExpSTime<=@expetime"; sp.Add(new SqlParameter("expetime", result.ToString("yyyy/MM/dd 23:59:59")));
                }
            }
            //搜索,支持指定条件
            if (!string.IsNullOrEmpty(filter.skey))
            {
                sp.Add(new SqlParameter("skey", "%" + filter.skey + "%"));
                switch (filter.stype)
                {
                case "exp":
                    where += " AND ExpressDelivery LIKE @skey";
                    break;

                case "oid":
                    where += " AND ID= " + DataConvert.CLng(filter.skey);
                    break;
                }
            }
            if (!String.IsNullOrEmpty(filter.oids))
            {
                SafeSC.CheckIDSEx(filter.oids);
                where += " AND ID IN (" + filter.oids + ")";
            }
            if (filter.payType != -100)
            {
                where += " AND PayType=" + filter.payType;
            }
            ////商品信息
            //string mtbname = "(SELECT *,LinPrice,Thumbnails,GuessXML,ProCode FROM ZL_CartPro A LEFT JOIN ZL_Commodities B ON ProID=ID)";
            ////订单与用户信息
            //string stbname = "(SELECT *,ParentUserID,HoneyName,ZL_Order_Exp.CDate AS ExpSTime FROM ZL_OrderInfo A LEFT JOIN ZL_User B ON UserID=UserID LEFT JOIN ZL_Order_Exp ON ExpressNum=ZL_Order_Exp.ID)";
            //string fields = "ID AS CartID,PClass,ProID,ProName,Shijia,Pronum,AddStatus,AllMoney,StoreID,Thumbnails,GuessXML"//ZL_CartPro
            //             + ",Freight,ID,OrderNo,AddUser,AddTime,OrderStatus,PaymentStatus,OrdersAmount,StateLogistics,Aside,Delivery,Service_charge"//ZL_OrderInfo
            //             + ",OrderType,OrderMessage,Receivablesamount,ExpressNum,PaymentNo" //ZL_OrderInfo
            //             + ",Shengfen,Jiedao,ZipCode,MobileNum,Phone,Email,Receiver,Rename,ExpressDelivery"//地址信息 ZL_OrderInfo
            //             + ",UserID,ParentUserID,HoneyName"//User
            //             + ",ExpSTime";//ZL_Order_Exp
            //PageSetting setting=PageSetting.Double(filter.cpage,filter.psize,mtbname,stbname,"ID","OrderListID=ID",where,"ID DESC",sp,fields);
            //return DBCenter.JoinQuery(fields, mtbname, stbname, "OrderListID=ID", where, "ID DESC", sp.ToArray());

            string view = "ZL_CartProView";
            //只取订单的ID
            PageSetting setting = PageSetting.Single(filter.cpage, filter.psize, view, "ID", where, " GROUP BY ID ORDER BY ID DESC", sp, "ID");
            DBCenter.SelPage(setting);
            string ids = "";
            foreach (DataRow dr in setting.dt.Rows)
            {
                ids += dr["id"] + ",";
            }
            ids = ids.TrimEnd(',');
            setting.itemCount = DataConvert.CLng(DBCenter.Count("(SELECT ID FROM ZL_CartProView WHERE " + where + " GROUP BY ID) A", "", sp));
            setting.pageCount = SqlBase.GetPageCount(setting.itemCount, setting.psize);
            //根据订单ID取出购物车中的数据,需要进行名称等筛选
            if (!string.IsNullOrEmpty(ids))
            {
                sp.Clear();
                string cartWhere = "ID IN (" + ids + ") ";
                if (!string.IsNullOrEmpty(filter.proname))
                {
                    cartWhere += " AND ProName LIKE @proname"; sp.Add(new SqlParameter("proname", "%" + filter.proname + "%"));
                }
                setting.dt = DBCenter.Sel(view, cartWhere, "ID DESC", sp);
            }
            if (filter.needCount)
            {
                string tbname = view;
                filter.countMod = new OrderCount();
                string sql = "SELECT COUNT(ID) AS [all]";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.HasPayed + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.NoSend + ") unexp";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.NoPay + ") unpaid";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND Delivery=1 AND IsCount=0) prepay";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND ((PaymentStatus=1 AND Delivery=0) OR (Delivery=1 AND IsCount=1 AND Settle=1))) paid";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND StateLogistics>" + (int)M_OrderList.ExpEnum.NoSend + ") exped";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND OrderStatus=" + (int)M_OrderList.StatusEnum.OrderFinish + " AND StateLogistics=" + (int)M_OrderList.ExpEnum.HasReceived + ") finished";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.RequestRefund + ") unrefund";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=0 AND PaymentStatus=" + (int)M_OrderList.PayEnum.Refunded + ") refunded";
                sql += ",(SELECT COUNT(ID) FROM " + tbname + whereCount + " AND Aside=1) recycle";
                sql += " FROM " + tbname + whereCount + " AND Aside=0";
                DataTable dt = DBCenter.ExecuteTable(sql);
                filter.countMod.all      = DataConvert.CLng(dt.Rows[0]["all"]);
                filter.countMod.unexp    = DataConvert.CLng(dt.Rows[0]["unexp"]);
                filter.countMod.unpaid   = DataConvert.CLng(dt.Rows[0]["unpaid"]);
                filter.countMod.prepay   = DataConvert.CLng(dt.Rows[0]["prepay"]);
                filter.countMod.paid     = DataConvert.CLng(dt.Rows[0]["paid"]);
                filter.countMod.exped    = DataConvert.CLng(dt.Rows[0]["exped"]);
                filter.countMod.finished = DataConvert.CLng(dt.Rows[0]["finished"]);
                filter.countMod.unrefund = DataConvert.CLng(dt.Rows[0]["unrefund"]);
                filter.countMod.refunded = DataConvert.CLng(dt.Rows[0]["refunded"]);
                filter.countMod.recycle  = DataConvert.CLng(dt.Rows[0]["recycle"]);
            }
            return(setting);
        }
Пример #17
0
 public DataTable SelByType(string ids)
 {
     SafeSC.CheckIDSEx(ids);
     return(DBCenter.ExecuteTable("SELECT * FROM " + TbName + " WHERE ModelType IN(" + ids + ") "));
 }
Пример #18
0
 /// <summary>
 /// 已有多少人参加了活动
 /// </summary>
 /// <param name="luckid"></param>
 /// <returns></returns>
 public int Count(int luckid)
 {
     return(DataConvert.CLng(
                DBCenter.ExecuteTable("SELECT COUNT(USERID) FROM (SELECT UserID FROM ZL_WX_LuckDrawList GROUP BY UserID) AS A").Rows[0][0]
                ));
 }