erp/SiteCore/WebCache.cs

using BizCom;
using NPOI.SS.Formula.Functions;
using SQLData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Web.UI.WebControls;
using Utils;

namespace SiteCore
{
    public class WebCache
    {

        #region 缓存方法
        /// <summary>
        /// 添加缓存
        /// </summary>
        /// <param name="cKey"></param>
        /// <param name="cValue"></param>
        public static void AddCache(string cKey, object cValue)
        {
            AddCacheTime(cKey, cValue, 180);
        }


        public static void AddCacheWithTime(string cKey, object cValue, double hour)
        {
            if (HttpContext.Current.Cache[cKey] == null && cValue != null)//HttpContext.Current.Cache.Remove(cKey);
                HttpContext.Current.Cache.Add(cKey, cValue, null, DateTime.Now.AddHours(hour), TimeSpan.Zero, CacheItemPriority.Normal, null);
        }

        /// <summary>
        /// 添加缓存
        /// </summary>
        /// <param name="cKey"></param>
        /// <param name="cValue"></param>
        public static void AddCacheTime(string cKey, object cValue, double cTime)
        {
            if (HttpRuntime.Cache[cKey] == null && cValue != null)//HttpRuntime.Cache.Remove(cKey);
                HttpRuntime.Cache.Add(cKey, cValue, null, DateTime.Now.AddMinutes(cTime), TimeSpan.Zero, CacheItemPriority.Normal, null);
        }

        /// <summary>
        /// 获取缓存
        /// </summary>
        /// <param name="cKey"></param>
        /// <returns></returns>
        public static object GetCache(string cKey)
        {
            return HttpRuntime.Cache[cKey];
        }
        public static void RemoveCache(string cKey)
        {
            CommonHelper.RemoveCache(cKey);
        }


        public static void AddRunCacheTime(string cKey, object cValue, double cTime)
        {
            if (HttpRuntime.Cache[cKey] == null && cValue != null)//HttpRuntime.Cache.Remove(cKey);
                HttpRuntime.Cache.Add(cKey, cValue, null, DateTime.Now.AddMinutes(cTime), TimeSpan.Zero, CacheItemPriority.Normal, null);
        }

        public static object GetRunCache(string cKey)
        {
            return HttpRuntime.Cache[cKey];
        }

        public static void RemoveRunCache(string cKey)
        {
            if (HttpRuntime.Cache[cKey] != null)
                HttpRuntime.Cache.Remove(cKey);
        }

        #endregion


        #region 获取字典表datatable
        /// <summary>
        /// 获取字典数据
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        public static DataTable GetDicData(DicType dicType)
        {
            return GetDicData(dicType, "");
        }
        /// <summary>
        /// 获取字典数据
        /// </summary>
        /// <param name="dicType"></param>
        /// <param name="sWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetDicData(DicType dicType, string sWhere)
        {
            string dicClass = "Dic" + dicType.ToString();
            DicSysBase DicSysBase = (DicSysBase)WebHelper.GetReflectionObject(dicClass);
            return DicSysBase.GetDataTable(sWhere);
        }

        private static readonly object dicObj = new object();
        /// <summary>
        /// 获取带缓存的字典表数据
        /// </summary>
        /// <param name="dicType"></param>
        /// <param name="sWhere"></param>
        /// <param name="cacheId"></param>
        /// <returns></returns>
        public static DataTable GetCacheDicData(DicType dicType, string sWhere, string cacheId)
        {
            if (GetCache(cacheId) == null)
            {
                lock (dicObj)
                {
                    if (GetCache(cacheId) == null)
                    {
                        string dicClass = "Dic" + dicType;
                        DicSysBase DicSysBase = (DicSysBase)WebHelper.GetReflectionObject(dicClass);
                        DataTable dt = DicSysBase.GetDataTable(sWhere);
                        AddCache(dicClass, dt);
                        return dt;
                    }
                }
            }
            return (DataTable)GetCache(cacheId);
        }
        #endregion

        #region 转成DicItemList
        public static List<DicItem> GetDicItemList(string table)
        {
            return GetDicItemList(table, "", "Name", "ID");
        }
        public static List<DicItem> GetDicItemList(string table, string where)
        {
            return GetDicItemList(table, where, "Name", "ID");
        }
        public static List<DicItem> GetDicItemList(string table, string where, string key, string value)
        {
            List<DicItem> list = new List<DicItem>();
            DataTable dt = DicSysBase.GetTable(table, where);
            if (dt != null && dt.Rows.Count > 0)
            {
                bool hasParent = dt.Columns.Contains("ParentID");
                bool hasCode = dt.Columns.Contains("Code");
                bool hasLng = dt.Columns.Contains("Lng");
                bool hasLat = dt.Columns.Contains("Lat");
                bool hasMeal = table.IndexOf("industry", StringComparison.OrdinalIgnoreCase) != -1 ? true : false;
                bool hasMcount = dt.Columns.Contains("Mcount");
                foreach (DataRow row in dt.Rows)
                {
                    DicItem item = new DicItem();
                    item.Name = row[key].ToString();
                    if (hasParent) item.ParentID = Convert.ToInt32(row["ParentID"]);
                    if (hasCode) item.Code = row["Code"].ToString();
                    if (hasLng) item.Lng = row["Lng"].ToString();
                    if (hasLat) item.Lat = row["Lat"].ToString();
                    if (hasMeal) item.IsTakeMeal = Convert.ToBoolean(row["IsTakeMeal"]);
                    if (hasMcount) item.Mcount = Convert.ToInt32(row["Mcount"]);
                    item.ID = Convert.ToInt32(row[value]);
                    list.Add(item);
                }
            }
            return list;
        }
        public static List<DicItem> GetDicItemList(DataTable dt)
        {
            return GetDicItemList(dt, "Name", "ID");
        }
        public static List<DicItem> GetDicItemList(DataTable dt, string Name, string ID)
        {
            List<DicItem> list = new List<DicItem>();
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DicItem item = new DicItem();
                    item.Name = row[Name].ToString();
                    item.ID = Convert.ToInt32(row[ID]);
                    list.Add(item);
                }
            }
            return list;
        }
        #endregion

        #region 绑定字典表DropDownList
        /// <summary>
        /// 绑定DropDownList,插入默认
        /// </summary>
        /// <param name="sel"></param>
        /// <param name="dicType"></param>
        /// <param name="insertSel"></param>
        /// <param name="where">条件</param>
        public static void ddlBindDicData(DropDownList sel, DicType dicType, string insertSel, string where)
        {
            ddlBindDicData(sel, dicType, "Name", "ID", insertSel, where);
        }

        /// <summary>
        /// 绑定DropDownList,插入默认
        /// </summary>
        /// <param name="sel"></param>
        /// <param name="dicType"></param>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="insertSel"></param>
        /// <param name="where">条件</param>
        public static void ddlBindDicData(DropDownList sel, DicType dicType, string name, string value, string insertSel, string where)
        {
            sel.DataSource = GetDicData(dicType, where);
            sel.DataTextField = name;
            sel.DataValueField = value;
            sel.DataBind();
            if (insertSel != "")
            {
                sel.Items.Insert(0, new ListItem(insertSel, "0"));
                sel.SelectedIndex = 0;
            }
        }
        #endregion

        //private static readonly object siteAdv_Falg = new object();
        //public static DataTable GetSiteAdv(string cityCode)
        //{
        //    string key = "siteAdv_" + cityCode;
        //    if (GetCache(key) == null)
        //    {
        //        lock (siteAdv_Falg)
        //        {
        //            if (GetCache(key) == null)
        //            {
        //                DataTable dt=LcIndexAdv.SimpleQuery(getCurrentWhere(cityCode));
        //                AddCache(key, dt);
        //                return dt;
        //            }
        //        }
        //    }
        //    return GetCache(key) as DataTable;
        //}



        //商家分类
        private static readonly object TaskTypeCache_Flag = new object();
        private static List<DicItem> _taskType;
        public static List<DicItem> TaskType
        {
            get
            {
                if (_taskType == null)
                {
                    if (GetCache("taskType") == null)
                    {
                        lock (TaskTypeCache_Flag)
                        {
                            if (GetCache("taskType") == null)
                            {
                                _taskType = GetDicItemList("S_TaskType");
                                AddCache("taskType", _taskType);
                            }
                        }
                    }
                    else
                    {
                        _taskType = GetCache("taskType") as List<DicItem>;
                    }
                }
                return _taskType;
            }
        }





        //商家分类
        private static readonly object ShopTypeCache_Flag = new object();
        private static List<DicItem> _shopType;
        public static List<DicItem> ShopType
        {
            get
            {
                if (_shopType == null)
                {
                    if (GetCache("shopType") == null)
                    {
                        lock (ShopTypeCache_Flag)
                        {
                            if (GetCache("shopType") == null)
                            {
                                _shopType = GetDicItemList("S_ShopType");
                                AddCache("shopType", _shopType);
                            }
                        }
                    }
                    else
                    {
                        _shopType = GetCache("shopType") as List<DicItem>;
                    }
                }
                return _shopType;
            }
        }


        private static readonly object formIdCache_Flag = new object();
        private static DataTable _formTable = null;
        public static DataTable FormTable
        {
            get
            {
                if (_formTable == null)
                {
                    if (GetCache("formTable") == null)
                    {
                        lock (formIdCache_Flag)
                        {
                            if (GetCache("formTable") == null)
                            {
                                string sql = "select userid,COUNT(0) as fc from dbo.S_WxFormId group by UserID";
                                _formTable = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                                AddCacheTime("formTable", _formTable, 480);
                            }
                        }
                    }
                    else
                    {
                        _formTable = GetCache("formTable") as DataTable;
                    }
                }
                return _formTable;
            }
        }

        private static readonly object CopTypeCache_Flag = new object();
        private static List<DicItem> _copType = null;
        public static List<DicItem> CopType
        {
            get
            {
                if (_copType == null)
                {
                    if (GetCache("copType") == null)
                    {
                        lock (CopTypeCache_Flag)
                        {
                            if (GetCache("copType") == null)
                            {
                                _copType = GetDicItemList("S_CopType");
                                AddCache("copType", _shopType);
                            }
                        }
                    }
                    else
                    {
                        _copType = GetCache("copType") as List<DicItem>;
                    }
                }
                return _copType;
            }
        }

        private static readonly object PrizeView_Flag = new object();
        private static DataView _prizeView = null;
        public static DataView GetPrizeView(string item, string date)
        {
            if (_prizeView == null)
            {
                if (GetCache("PrizeView") == null)
                {
                    lock (PrizeView_Flag)
                    {
                        if (GetCache("PrizeView") == null)
                        {
                            string sql = string.Format("select * from s_prize where item='{0}' and datediff(d,termtime,'{1}')=0 ", item, date);
                            DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                            //_prizeView = GetDicItemList("S_CopType");
                            AddCacheTime("PrizeView", new DataView(dt), 5);
                            //AddCache("PrizeView",new DataView(dt));
                        }
                    }
                }
                else
                {
                    _prizeView = GetCache("PrizeView") as DataView;
                }
            }
            return _prizeView;
        }


        //商品分类
        private static readonly object GoodsTypeCache_Flag = new object();
        private static List<DicItem> _goodsType;
        public static List<DicItem> GoodsType
        {
            get
            {
                if (_goodsType == null)
                {
                    if (GetCache("goodsType") == null)
                    {
                        lock (GoodsTypeCache_Flag)
                        {
                            if (GetCache("goodsType") == null)
                            {
                                _goodsType = GetDicItemList("S_GoodsType");
                                AddCache("goodsType", _goodsType);
                            }
                        }
                    }
                    else
                    {
                        _goodsType = GetCache("goodsType") as List<DicItem>;
                    }
                }
                return _goodsType;
            }
        }
        /// <summary>
        /// 根据ID获取商品分类
        /// </summary>
        /// <returns></returns>
        public static List<DicItem> GetGoodsType(string sIds)
        {
            int id = 0;
            string[] sArr = sIds.Split(',');
            if (!sIds.Equals("")) id = Convert.ToInt32(sArr[sArr.Length - 1]);
            return GoodsType.Where(t => t.ParentID == id || sArr.Contains(t.ID.ToString())).OrderBy(t => t.ID).ToList();
        }



        //行业
        public static List<DicItem> IndustryCache
        {
            get
            {
                return DicIndustryCache.Where(t => t.IsTakeMeal == false).ToList();
            }
        }
        public static List<DicItem> MealIndustryCache
        {
            get
            {
                return DicIndustryCache.Where(t => t.ParentID != 0 && t.IsTakeMeal).ToList();
            }
        }

        private static readonly object DicIndustryCache_Flag = new object();
        private static List<DicItem> _dicIndustryCache;
        public static List<DicItem> DicIndustryCache
        {
            get
            {
                if (_dicIndustryCache == null)
                {
                    if (GetCache("dicIndustry") == null)
                    {
                        lock (DicIndustryCache_Flag)
                        {
                            if (GetCache("dicIndustry") == null)
                            {
                                _dicIndustryCache = GetDicItemList("LC_DicIndustry", "");
                                AddCache("dicIndustry", _dicIndustryCache);
                            }
                        }
                    }
                    else
                    {
                        _dicIndustryCache = GetCache("dicIndustry") as List<DicItem>;
                    }
                }
                return _dicIndustryCache;
            }
        }




        /// <summary>
        /// 根据ID获取行业
        /// </summary>
        /// <returns></returns>
        public static List<DicItem> GetIndustry(string sIds)
        {
            int id = 0;
            string[] sArr = sIds.Split(',');
            if (!sIds.Equals("")) id = Convert.ToInt32(sArr[sArr.Length - 1]);
            return IndustryCache.Where(t => t.ParentID == id || sArr.Contains(t.ID.ToString())).OrderBy(t => t.ID).ToList();
        }

        private static readonly object cityZoneFlag = new object();
        public static List<DicItem> GetCityZone(string code)
        {
            string key = "dicCityZone_" + code;
            if (GetCache(key) == null)
            {
                lock (cityZoneFlag)
                {
                    if (GetCache(key) == null)
                    {
                        List<DicItem> list = GetDicItemList("LC_DicRegion",
                            string.Format("Path like (select '%|'+convert(varchar,ID)+'|%' from Lc_Dicregion where Code='{0}')", code));
                        AddCache(key, list);
                        return list;
                    }
                }
            }
            return GetCache(key) as List<DicItem>;
        }


        private static readonly object cityAreaFlag = new object();
        /// <summary>
        /// 获取所有区县
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public static List<DicItem> GetCityArea(string code)
        {
            string key = "dicCityArea_" + code;
            if (GetCache(key) == null)
            {
                lock (cityAreaFlag)
                {
                    if (GetCache(key) == null)
                    {
                        List<DicItem> list = GetDicItemList("LC_DicRegion", "(Code like '" + code.Substring(0, 4) + "%' and Code<>'" + code + "') and IsDel=0", "Name", "Code");
                        AddCache(key, list);
                        return list;
                    }
                }
            }
            return GetCache(key) as List<DicItem>;
        }

        private static readonly object recTasksFlag = new object();
        public static DataTable GetRecTasks()
        {
            string key = "recTasks";
            if (GetCache(key) == null)
            {
                lock (recTasksFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "SELECT TOP 6 id,fee,title,pubtime FROM s_task where State=1 and DATEDIFF(m,pubtime,GETDATE())=0 ORDER BY NEWID()";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 1);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object taskTypeTableFlag = new object();
        public static DataTable GetTaskTypeTable()
        {
            string key = "taskTypeKey";
            if (GetCache(key) == null)
            {
                lock (taskTypeTableFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select id,name,sort from s_tasktype";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object appVerFlag = new object();
        public static DataTable GetAppVer()
        {
            string key = "appVerKey";
            if (GetCache(key) == null)
            {
                lock (appVerFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select appname,appver,downfile from s_appver";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCache(key, dt);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object appWeixinVer = new object();
        public static DataTable GeWeixinVer(string ps)
        {
            string key = "appWeixinVer" + ps;
            if (GetCache(key) == null)
            {
                lock (appWeixinVer)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select top " + ps + " * from s_weixinver order by pubtime desc";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCache(key, dt);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }



        private static readonly object shopTypeTableFlag = new object();
        public static DataTable GetShopTypeTable()
        {
            string key = "shopTypeKey";
            if (GetCache(key) == null)
            {
                lock (shopTypeTableFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select id,name,sort from s_shoptype";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object runRankFlag = new object();
        public static DataTable GetRunRank()
        {
            string key = "runRankFlag";
            if (GetCache(key) == null)
            {
                lock (runRankFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select top 30 nickname,userpic,sex,SUM(step) as allstep from view_RunStep group by nickname,userpic,sex order by allstep desc";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object curRunRankFlag = new object();
        public static DataTable GetCurRunRank(int uid)
        {
            //string key = "curRunRankFlag";
            //if (GetCache(key) == null)
            //{
            //    lock (curRunRankFlag)
            //    {
            //        if (GetCache(key) == null)
            //        {
            //            string sql = "select top 30 id,nickname,userpic,sex,step as allstep,(select COUNT(0) from S_RunStepLikes where StepID=view_RunStep.ID and UserID="+uid+") as cur,likes from view_RunStep where datediff(d,updatetime,GETDATE())=0 order by allstep desc";
            //            DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
            //            //AddRunCacheTime(key, dt, 1);
            //            return dt;
            //        }
            //    }
            //}
            //return GetRunCache(key) as DataTable;
            string sql = "select top 30 id,nickname,userpic,sex,step as allstep,(select COUNT(0) from S_RunStepLikes where StepID=view_RunStep.ID and UserID=" + uid + ") as cur,likes from view_RunStep where datediff(d,updatetime,GETDATE())=0 order by allstep desc";
            DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
            //AddRunCacheTime(key, dt, 1);
            return dt;
        }



        private static readonly object concealtypeFlag = new object();
        public static DataTable GetConcealType()
        {
            string key = "concealtype";
            if (GetCache(key) == null)
            {
                lock (concealtypeFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select id,name from s_concealtype";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }


        private static readonly object ewuTypeTableFlag = new object();
        public static DataTable GetEwuTypeTable()
        {
            string key = "ewuTypeKey";
            if (GetCache(key) == null)
            {
                lock (ewuTypeTableFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select id,name,sort from s_goodstype";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object copTypeTableFlag = new object();
        public static DataTable GetCopTypeTable()
        {
            string key = "copTypeKey";
            if (GetCache(key) == null)
            {
                lock (copTypeTableFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select id,name,sort from s_coptype";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 12);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        //不能预约的
        private static string getNoOrderWhere(string stime, string etime)
        {
            List<string> lst = new List<string>();
            lst.Add(string.Format("not(DATEDIFF(MINUTE,'{1}',StartTime)>=0 or DATEDIFF(MINUTE, '{0}', EndTime)<=0)", stime, etime));//不能预约的,使用中的
            //lst.Add(string.Format("state<2"));//状态没有变化
            lst.Add(" not((state=0 and DATEDIFF(MINUTE,StartTime,GETDATE())>=15) or state>1)");//没有[过期结束的]
            //lst.Add("DATEDIFF(MINUTE, EndTime, GETDATE())<0");//未结束
            return string.Join(" and ", lst.ToArray());
        }


        private static readonly object indexMenuFlag = new object();
        public static DataTable GetIndexMenu(int r)
        {
            string key = "indexMenu";
            if (r == 44 || r == 3899 || r == 3900 || r == 3901) key = "indexMenu_r";
            else if (r == 40 || r == 3163 || r == 78 || r == 495 || r == 34) key = "indexMenu_m";
            if (GetCache(key) == null)
            {
                lock (indexMenuFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select * from s_menu";
                        if (r == 44 || r == 3899 || r == 3900 || r == 3901)
                        {
                            sql += " where tid not in(3,6,1) and isshow=1";
                        }
                        else if (r == 40 || r == 3163 || r == 78 || r == 495 || r == 34)
                        {
                            //sql += "";
                        }
                        else
                        {
                            sql += " where isshow=1";
                        }
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 120);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object labRoomFlag = new object();
        public static DataTable GetLabRoomTable()
        {
            string key = "labRoomKey";
            if (GetCache(key) == null)
            {
                lock (labRoomFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string ctime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        string etime = DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss");
                        //string sql = "select id,name,sort from s_labroom where parentid=0 order by sort";
                        StringBuilder sql = new StringBuilder();
                        sql.Append("select id,name,opentime as ot,'' as opentime,'' as closetime,cols,rows,colfields,holdperson as hperson,sort,code,seatnum,0 as onum from s_labstudyroom where ismeeting=0 order by sort ;");//and state=0and state=0 
                        sql.Append("select * from s_labbreakoff where datediff(d,starttime,getdate())<=0 order by starttime asc;");
                        sql.AppendFormat("select roomid,count(0) as cnum from view_laborder where {1} and datediff(d,starttime,'{0}')=0 group by roomid", ctime, getNoOrderWhere(ctime, etime));
                        DataSet ds = DbHelper.DbConn.ExecuteDataset(sql.ToString());
                        DataTable dt = ds.Tables[0];
                        DataView bDv = new DataView(ds.Tables[1]);
                        DataView orderDv = new DataView(ds.Tables[2]);
                        int w = (int)DateTime.Now.DayOfWeek;
                        if (w == 0) w = 7;
                        string[] sArr = null;
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr["ot"].ToString() != "")
                            {
                                sArr = dr["ot"].ToString().Split('|');
                                if (sArr.Length == 7)
                                    dr["opentime"] = sArr[getWeekDay(w - 1)] + "|" + sArr[getWeekDay(w)] + "|" + sArr[getWeekDay(w + 1)];
                            }
                            bDv.RowFilter = "roomid=" + dr["id"];
                            if (bDv.Count > 0)
                            {
                                dr["closetime"] = bDv[0]["starttime"] + "|" + bDv[0]["endtime"];
                            }
                            orderDv.RowFilter = "roomid=" + dr["id"];
                            if (orderDv.Count > 0)
                            {
                                dr["onum"] = orderDv[0]["cnum"];
                            }
                        }
                        AddCacheTime(key, dt, 25);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object labDiscussFlag = new object();
        public static DataTable GetLabDiscussTable()
        {
            string key = "labDiscussKey";
            if (GetCache(key) == null)
            {
                lock (labDiscussFlag)
                {
                    if (GetCache(key) == null)
                    {
                        //string sql = "select id,name,sort from s_labroom where parentid=0 order by sort";
                        StringBuilder sql = new StringBuilder();
                        sql.AppendFormat("select id,name,sort,hperson,facilities,opentime,'' as closetime,0 as cnum from s_labdiscuss order by name asc ;");
                        sql.AppendFormat("select * from s_labbreakoff where discussid>0 and datediff(d,endtime,getdate())<=0  order by starttime asc;");
                        DataSet ds = DbHelper.DbConn.ExecuteDataset(sql.ToString());
                        DataTable dt = ds.Tables[0];
                        DataView bDv = new DataView(ds.Tables[1]);
                        List<string> lst = new List<string>();
                        foreach (DataRow dr in dt.Rows)
                        {
                            lst = new List<string>();
                            bDv.RowFilter = "discussid=" + dr["id"];
                            if (bDv.Count > 0)
                            {
                                foreach (DataRowView bdrv in bDv)
                                {
                                    lst.Add(bDv[0]["starttime"] + "|" + bDv[0]["endtime"]);
                                }
                                dr["closetime"] = String.Join(",", lst.ToArray());
                            }
                        }
                        AddCacheTime(key, dt, 10);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static int getWeekDay(int w)
        {
            if (w == 7) w = 1;
            else if (w == 8) w = 2;

            return w;
        }


        private static readonly object recGoodsFlag = new object();
        public static DataTable GetRecGoods()
        {
            string key = "recGoods";
            if (GetCache(key) == null)
            {
                lock (recGoodsFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "SELECT TOP 6 * FROM s_goods where State=1 and DATEDIFF(m,GPublishTime,GETDATE())=0 ORDER BY NEWID()";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 1);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object recNewsFlag = new object();
        public static DataTable GetRecNews()
        {
            string key = "recNews";
            if (GetCache(key) == null)
            {
                lock (recNewsFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "SELECT TOP 6 id,title FROM s_news where newstypeid=2 order by addtime desc";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 1);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object lineNewsFlag = new object();
        public static DataTable GetLineNews()
        {
            string key = "lineNews";
            if (GetCache(key) == null)
            {
                lock (lineNewsFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "SELECT TOP 10 id,title FROM s_news order by addtime desc";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        AddCacheTime(key, dt, 1);
                        return dt;
                    }
                }
            }
            return GetCache(key) as DataTable;
        }

        private static readonly object assnFlag = new object();

        public static DataView GetAssn()
        {
            string key = "assn";
            if (GetCache(key) == null)
            {
                lock (lineNewsFlag)
                {
                    if (GetCache(key) == null)
                    {
                        string sql = "select * FROM s_assn ";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        DataView dv = new DataView(dt);
                        AddCache(key, dv);
                        return dv;
                    }
                }
            }
            return GetCache(key) as DataView;
        }


        private static readonly object lyxyUserFlag = new object();

        public static DataTable LyxyUserTable
        {
            get
            {
                string key = "lyxyuser";
                if (GetCache(key) == null)
                {
                    lock (lyxyUserFlag)
                    {
                        if (GetCache(key) == null)
                        {
                            string sql = "select * FROM s_lyxyuser ";
                            DataTable dt = SqlHelper.ExecuteDataSet(sql).Tables[0];
                            AddCache(key, dt);
                            return dt;
                        }
                    }
                }
                return GetCache(key) as DataTable;
            }
        }

        #region 获取表格数据
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dStruct">数据结构</param>
        /// <returns></returns>
        public static DataTable GetData(string tableName, DataStruct dStruct)
        {
            if (dStruct.isExport)
            {
                string where = CommonHelper.CombineWhere(dStruct.MainWhere, dStruct.SecondWhere);
                //string sql;
                StringBuilder sql = new StringBuilder();
                if (dStruct.PageSize != 100000)
                {
                    sql.AppendFormat("select top {0} {1} from {2}", dStruct.PageSize, dStruct.Fileds, tableName);
                }
                else
                {
                    sql.AppendFormat("select {0} from {1}", dStruct.Fileds, tableName);
                }
                if (where.Length > 0) sql.AppendFormat(" where {0}", where);
                if (dStruct.Order.Length > 0) sql.AppendFormat(" order by {0}", dStruct.Order);
                //if (where.Length > 0) sql = string.Format("select top {0} {1} from {2} where {3}", dStruct.PageSize, dStruct.Fileds, tableName, where);
                //else sql = string.Format("select top {0} {1} from {2} ", dStruct.PageSize, dStruct.Fileds, tableName);
                DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                return dt;
                /*int pageSize = 1000;
                int pageIndex = 1;
                DataTable dt = new DataTable();
                while (true)
                {
                    StringBuilder sql = new StringBuilder();
                    if (dStruct.PageSize != 100000)
                    {
                        sql.AppendFormat("select top {0} {1} from {2}", dStruct.PageSize, dStruct.Fileds, tableName);
                    }
                    else
                    {
                        sql.AppendFormat("SELECT TOP {2} * FROM (SELECT row_number()  OVER(ORDER BY ID) AS rownumber, {0} FROM {1} WITH(NOLOCK) ", dStruct.Fileds, tableName, pageSize);
                    }

                    if (where.Length > 0) sql.AppendFormat(" where {0}", where);

                    sql.AppendFormat(" ) temp_row where rownumber > (({0} - 1) * {1}); ", pageIndex, pageSize);
                    DataTable dt_temp = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];
                    if (pageIndex == 1)
                    {
                        dt = dt_temp.Clone();
                    }
                    else
                    {
                        dt.Merge(dt_temp);
                    }
                    if (dt_temp.Rows.Count < pageSize)
                    {
                        break;
                    }
                    pageIndex++;

                }

                //if (dStruct.Order.Length > 0) sql.AppendFormat(" order by {0}", dStruct.Order);
                //if (where.Length > 0) sql = string.Format("select top {0} {1} from {2} where {3}", dStruct.PageSize, dStruct.Fileds, tableName, where);
                //else sql = string.Format("select top {0} {1} from {2} ", dStruct.PageSize, dStruct.Fileds, tableName);

                return dt;*/
            }

            SqlParameter[] sqlParameter ={
                        new SqlParameter("@TableName", SqlDbType.VarChar,8000),
                        new SqlParameter("@Fields", SqlDbType.VarChar, 500),
                        new SqlParameter("@OrderBy", SqlDbType.VarChar, 500),
                        new SqlParameter("@Where", SqlDbType.VarChar, 8000),
                        new SqlParameter("@pageSize", SqlDbType.Int, 4),
                        new SqlParameter("@pageIndex", SqlDbType.Int, 4),
                        new SqlParameter("@totalRecord", SqlDbType.Int, 4)/*,
                        new SqlParameter("@totalPage", SqlDbType.Int, 4)*/
                };
            string key = dStruct.PrimaryKeys.Split(',')[0];
            sqlParameter[0].Value = tableName;
            sqlParameter[1].Value = dStruct.Fileds;
            if (dStruct.Order != "")
                sqlParameter[2].Value = dStruct.Order;
            else
                sqlParameter[2].Value = key + " Asc";
            sqlParameter[3].Value = CommonHelper.CombineWhere(dStruct.MainWhere, dStruct.SecondWhere);
            sqlParameter[4].Value = dStruct.PageSize;
            sqlParameter[5].Value = dStruct.CurrentPage;
            sqlParameter[6].Direction = ParameterDirection.ReturnValue;
            try
            {
                DataTable tmpDt = DbHelper.DbConn.ExecuteDataset(CommandType.StoredProcedure, "sp_PageView", sqlParameter).Tables[0];
                dStruct.TotalCount = (int)sqlParameter[6].Value;
                dStruct.TotalPage = (int)Math.Ceiling((dStruct.TotalCount * 1.0 / dStruct.PageSize));
                if (dStruct.TotalPage == 0 && dStruct.TotalCount > 0) dStruct.TotalPage = 1;
                return tmpDt;
            }
            catch (Exception ex)
            {
                //SyLog.WriteLog(ex);
            }
            return null;
        }

        public static DataTable GetErpData(string tableName, DataStruct dStruct)
        {
            SqlParameter[] sqlParameter ={
                            new SqlParameter("@TableName", SqlDbType.VarChar,8000),
                            new SqlParameter("@Fields", SqlDbType.VarChar, 500),
                            new SqlParameter("@OrderBy", SqlDbType.VarChar, 500),
                            new SqlParameter("@Where", SqlDbType.VarChar, 8000),
                            new SqlParameter("@pageSize", SqlDbType.Int, 4),
                            new SqlParameter("@pageIndex", SqlDbType.Int, 4),
                            new SqlParameter("@totalRecord", SqlDbType.Int, 4)/*,
                            new SqlParameter("@totalPage", SqlDbType.Int, 4)*/
                    };
            string key = dStruct.PrimaryKeys.Split(',')[0];
            sqlParameter[0].Value = tableName;
            sqlParameter[1].Value = dStruct.Fileds;
            if (dStruct.Order != "")
                sqlParameter[2].Value = dStruct.Order;
            else
                sqlParameter[2].Value = key + " Asc";
            sqlParameter[3].Value = CommonHelper.CombineWhere(dStruct.MainWhere, dStruct.SecondWhere);
            sqlParameter[4].Value = dStruct.PageSize;
            sqlParameter[5].Value = dStruct.CurrentPage;
            sqlParameter[6].Direction = ParameterDirection.ReturnValue;
            try
            {
                DataTable tmpDt = ErpSqlHelper.ExecuteDataSet(CommandType.StoredProcedure, "sp_PageView", sqlParameter).Tables[0];
                dStruct.TotalCount = (int)sqlParameter[6].Value;
                dStruct.TotalPage = (int)Math.Ceiling((dStruct.TotalCount * 1.0 / dStruct.PageSize));
                if (dStruct.TotalPage == 0 && dStruct.TotalCount > 0) dStruct.TotalPage = 1;
                return tmpDt;
            }
            catch (Exception ex)
            {
                //SyLog.WriteLog(ex);
            }
            return null;
        }

        //获取所有数据
        public static DataTable GetFullData(string sql, DataStruct dStruct)
        {
            string sWhere = CommonHelper.CombineWhere(dStruct.MainWhere, dStruct.SecondWhere);
            if (sWhere != "") sWhere = " where " + sWhere;
            sql = string.Format("select * from ({0}) as tb {1}", sql, sWhere);
            return DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
        }
        #endregion

        private static object columnsObj = new object();
        public static DataTable GetGridColumns()
        {
            if (GetCache("grid_columns") == null)
            {
                lock (columnsObj)
                {
                    if (GetCache("grid_columns") == null)
                    {
                        string sql = "select * from ce_columns";
                        DataTable dt = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
                        //DataTable dt = CeErpUnit.SimpleQuery(0, "id,name,unittype", "", "");
                        AddCache("grid_columns", dt);
                        return dt;
                    }
                }
            }
            return (DataTable)GetCache("grid_columns");
        }
    }

    public enum AdvPositionEnum
    {
        IndexTop = 1,
        IndexRecommend = 2,
        IndexRight1 = 3,
        IndexRight2 = 5,
        IndexLeft1 = 6
    }


    public enum DicType
    {
        CouponType,
        Industry,
        Region,
        ServiceType,
        TradeType,
        MemberType,
        FriendType,
        ProductType,
        GoodsType,
        CityArea
    }

    //字典表类
    public class DicItem
    {
        public int ID { get; set; }
        public string RegionID { get; set; }
        public int ParentID { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string Lng { get; set; }
        public string Lat { get; set; }
        public bool IsTakeMeal { get; set; }
        public int Mcount { get; set; }
        public DicItem()
        {
        }

        public DicItem(int _id, string _name)
        {
            ID = _id;
            Name = _name;
        }
        public DicItem(int _id, int _parentid, string _name)
        {
            ID = _id;
            ParentID = _parentid;
            Name = _name;
        }

    }
    public class CityItem
    {
        public string RegionName { get; set; }
        public string RegionCode { get; set; }
        public string Prev { get; set; }
        public string LngLat { get; set; }
        public string CityData { get; set; }
        public string IsHot { get; set; }
        public CityItem(string rName, object rCode, object prev, object ishot, object lngLat)
        {
            RegionName = rName;
            RegionCode = rCode.ToString();
            Prev = prev.ToString();
            IsHot = ishot.ToString();
            LngLat = lngLat.ToString();
        }
    }

}