Exemplo n.º 1
0
        public JsonResult FindUseInfo()
        {
            StuInfoDBContext stuContext = new StuInfoDBContext();
            List <useInfo>   stuLis     = stuContext.Database.SqlQuery <useInfo>("SELECT * FROM Bas_WS").ToList();

            return(Json(stuLis));
        }
Exemplo n.º 2
0
        public JsonResult FindSqlList(string where)
        {
            var    cache = CacheHelper.GetCache("mydata");
            object obj   = null;

            if (cache == null)
            {
                StuInfoDBContext stuContext = new StuInfoDBContext();
                StringBuilder    strSql     = new StringBuilder();
                StringBuilder    whereStr   = new StringBuilder();
                for (int i = 0; i < where.Split(',').Length; i++)
                {
                    whereStr.Append(" and  a.WSID<>'" + where.Split(',')[i].ToString() + "'");
                }

                strSql.Append(@"select a.AreaName,a.WSID,a.WSName,
                            min(c.onlineDate) as onlineDate,
							max(c.noLoginWebDay) as noLoginWebDay,
							max(c.noLoginAppDay) as noLoginAppDay,
                            max(f.RecTimestamp) as RecTimestamp,
                            max(g.VisitDate) as VisitDate,
                            max(h.RecTimeStamp) as OrderDate,
                            datediff(day,max(c.noLoginWebDay),GETDATE()) as noLoginWebDayNums,
							datediff(day,max(c.noLoginAppDay),GETDATE()) as noLoginAppDayNums,
                            datediff(day,max(f.RecTimestamp),GETDATE()) as noInputFyDayNums,
                            datediff(day,max(g.VisitDate),GETDATE()) as noBfDayNums,
                            datediff(day,max(h.RecTimeStamp),GETDATE()) as noOrderDayNums,
                            max(c.oneWebDay) as oneWebDay,
							max(c.oneWebWeek) as oneWebWeek,
							max(c.oneWebMonth) as oneWebMonth,
							max(c.oneAppDay) as oneAppDay,
							max(c.oneAppWeek) as oneAppWeek,
							max(c.oneAppMonth) as oneAppMonth,
                            isnull(i.custCount,0) as custCount,
                            isnull(j.newCustCount,0) as newCustCount,
                            d.userCount,
                            isnull(e.vehCount,0) as vehCount,
                            d.xsdbCount,
                            d.xszgCount,
                            d.xsjlCount,
                            d.xszjCount,
                            d.cxywyCount,
                            d.sjCount,
                            d.qtCount,
                            max(h.orderUserCount) as orderUserCount,
							max(g.visitUserCount) as visitUserCount               
                            from 
							(select t1.WSID,t1.WSName,t1.WSType,t1.IsValid,t3.AreaName+'/'+t2.AreaName as AreaName from Bas_WS t1
							left join Bas_Area t2
							on t1.AreaCode=t2.AreaCode
							left join Bas_Area t3
							on t2.ParentCode=t3.AreaCode
							) a
                            left join (select WSID,UserID from sys_user where left(UserName,5)<>'admin') b
                            on b.WSID=a.WSID
                            left join 
                            (
                                select 
                                    userid,
									min(logintime) as onlineDate,
                                    max(case when workstation='Mobile APP' then logintime end) as noLoginAppDay, 
                                    max(case when workstation='DMS WEB' then logintime end) as noLoginWebDay,
                                    count(DISTINCT case when workstation='DMS WEB' and logintime >=  DATEADD(dd,0, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneWebDay,
									count(DISTINCT case when workstation='DMS WEB' and logintime >=  DATEADD(dd,-6, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneWebWeek,
									count(DISTINCT case when workstation='DMS WEB' and logintime >=  DATEADD(dd,-29, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneWebMonth,
									count(DISTINCT case when workstation='Mobile APP' and logintime >=  DATEADD(dd,0, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneAppDay,
									count(DISTINCT case when workstation='Mobile APP' and logintime >=  DATEADD(dd,-6, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneAppWeek,
									count(DISTINCT case when workstation='Mobile APP' and logintime >=  DATEADD(dd,-29, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then Convert(VARCHAR(30),logintime,112) end ) AS oneAppMonth						
                                from SYS_USERLOG group by userid
                            ) c
                            on b.UserID=c.userid
                            left join
							(
								select wsid, 
                                count(UserID) as userCount,
								count(case when UserType='0001' then UserID end) as xsdbCount,
								count(case when UserType='0002' then UserID end) as xszgCount,
								count(case when UserType='0003' then UserID end) as xsjlCount,
								count(case when UserType='0004' then UserID end) as xszjCount,
                                count(case when UserType='0006' then UserID end) as sjCount,
								count(case when UserType='0007' then UserID end) as cxywyCount,
								count(case when isnull(UserType,'')<>'0001' and isnull(UserType,'')<>'0002' and isnull(UserType,'')<>'0003' and isnull(UserType,'')<>'0004' and isnull(UserType,'')<>'0006' and isnull(UserType,'')<>'0007' then UserID end) as qtCount  
								from sys_user where isnull(IsValid,1)=1 and left(UserName,5)<>'admin' group by wsid
							) d
							on a.wsid=d.wsid
                            left join (select WSID,count(VehID) as vehCount from Bas_Vehicle where isnull(IsValid,1)=1 and isnull(GpsID,'')<>'' group by WSID) e
							on a.WSID=e.WSID
                            left join (select WSID,max(RecTimestamp) as RecTimestamp from view_TMS_Car_Cost group by WSID) f
                            on a.WSID=f.WSID
                            left join (
                                select 
							    WSID,
							    count(distinct case when RecTimeStamp >=  DATEADD(dd,-30, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then UserID end) as visitUserCount,
							    max(VisitDate) as VisitDate from SFA_Visit group by WSID
                            ) g
                            on a.WSID=g.WSID
                            left join (
                                select 
								WSID,
								count(distinct case when (SaleSourceID=4 or SaleSourceID=5) and RecTimeStamp >=  DATEADD(dd,-30, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) then RecUserID end) as orderUserCount,
								max(RecTimeStamp) as RecTimeStamp 
								from SFA_Order_Header group by WSID
                            ) h
							on a.WSID=h.WSID
                            left join (select WSID,count(CustomerID) as custCount from Bas_Customer group by WSID) i
							on i.WSID=a.WSID 
                            left join (select WSID,count(CustomerID) as newCustCount from Bas_NewCustomer where AuditStatus=0 group by WSID) j
							on j.WSID=a.WSID 
                            where a.WSType=1 and isnull(a.IsValid,1)=1 and d.userCount is not null 
                            {0}
                            group by a.AreaName,a.WSID,a.WSName,e.vehCount,d.userCount,d.xsdbCount,d.xszgCount,d.xsjlCount,d.xszjCount,d.cxywyCount,d.sjCount,d.qtCount,i.custCount,j.newCustCount
                            order by oneWebMonth desc");

                string str = String.Format(strSql.ToString(), whereStr.ToString());

                var userLis        = stuContext.Database.SqlQuery <useInfo>(str).ToList();
                var totalWsCount   = userLis.Count <useInfo>();
                var totalUserCount = userLis.Sum <useInfo>(t => t.userCount);
                var totalVehCount  = userLis.Sum <useInfo>(t => t.vehCount);

                strSql.Clear();
                strSql.Append(@"select c.ApplicationType,count(distinct a.UserID) as moduleCount
                            from sys_User a
                            left join Sys_user_role b
                            on a.UserID=b.UserID
                            inner join (
                            select r1.RoleID,r1.ModuleID,r3.ApplicationType from Sys_RoleRight r1
                            inner join Sys_Role r2
                            on r1.RoleID=r2.RoleID
                            inner join (select ModuleID,ApplicationType,ModuleName from Sys_Module) r3
                            on r1.ModuleID=r3.ModuleID
                            where isnull(r2.IsValid,1)=1 and (len(r1.ModuleID)=2 or left(r1.ModuleID,2)='53')and r3.ApplicationType is not null and r3.ApplicationType<>'9' and r3.ApplicationType<>'0'
                            ) c
                            on c.RoleID=a.RoleID
                            inner join (select WSID,WSName,WSType,IsValid from Bas_WS) d
                            on a.WSID=d.WSID
                            where d.WSType=1 and isnull(d.IsValid,1)=1 and left(a.UserName,5)<>'admin'
                            {0}
                            group by c.ApplicationType order by moduleCount desc");
                str = String.Format(strSql.ToString(), whereStr.ToString());

                var moduleLis = stuContext.Database.SqlQuery <moduleInfo>(str).ToList();

                obj = new { rows = userLis, totalWsCount = totalWsCount, totalUserCount = totalUserCount, totalVehCount = totalVehCount, moduleLis = moduleLis };

                //插入cache 缓存12小时
                double tt = 12 * 60 * 60;
                CacheHelper.SetCache("mydata", obj, DateTime.Now.AddSeconds(tt), TimeSpan.Zero);
            }
            else
            {
                obj = (object)cache;
            }

            return(Json(obj));
        }
Exemplo n.º 3
0
        private string GetEmailData(bool IsSend)
        {
            var hour = Convert.ToInt16(Request.QueryString["hour"] == null ? "0" : Request.QueryString["hour"].ToString());

            if ((DateTime.Now.Hour == hour && DateTime.Now.Hour != 0) || IsSend == false)
            {
                try
                {
                    string            toMails           = ConfigurationManager.AppSettings["toMails"].ToString();
                    string            smtpEmail         = ConfigurationManager.AppSettings["smtpEmail"].ToString();
                    string            fromEmail         = ConfigurationManager.AppSettings["fromEmail"].ToString();
                    string            fromEmailPassword = ConfigurationManager.AppSettings["fromEmailPassword"].ToString();
                    EmailParameterSet model             = new EmailParameterSet();
                    model.SendEmail        = fromEmail;
                    model.SendPwd          = fromEmailPassword; //密码
                    model.SendSetSmtp      = smtpEmail;         //发送的SMTP服务地址 ,每个邮箱的是不一样的。。根据发件人的邮箱来定
                    model.ConsigneeAddress = toMails;
                    model.ConsigneeTheme   = "EPM-U+经销商使用情况";
                    model.ConsigneeHand    = "实施部门";
                    model.ConsigneeName    = "邹智勇";
                    string thStr    = @"<th style='width:50px;'>序号</th>
                                    <th style='width:60px;'>经销商ID</th>
                                    <th style='width:250px;'>经销商名称</th>
                                    <th style='width:55px;'>客户数量</th>
                                    <th style='width:48px;'>采购订单数量</th>
                                    <th style='width:60px;'>采购退货单数量</th>
                                    <th style='width:55px;'>销售订单<br>(未发货)</th>
                                    <th style='width:100px;'>销售订单数量<br>(有发货)</th>
                                    <th style='width:55px;'>销售退货单数量</th>
                                    <th style='width:75px;'>上线日期</th>
                                    <th style='width:100px;'>状态</th>
                                    <th style='width:48px;'>顾问负责人</th>
                                    <th style='width:200px;'>顾问邮箱</th>
                                    <th style='width:85px;'>顾问手机号</th>";
                    string labelStr = @"<div style=""font-family:'Microsoft Yahei';font-size:12px;text-align:left;"">当天订单数量如下:</div>";
                    string strStyle = @"<style>table{border-collapse:collapse;font-family:'Microsoft Yahei';font-size:12px;}table,th,td{border:1px solid #eee;}th{text-align:center;}th,td{padding:3px;} thead{background-color:#350a4d;color:#fff;}tr:nth-child(even){background-color:#eaf1fb;}</style>";
                    string headStr  = @"<div style=""width:100%;overflow-x:auto"">
                                            <table style=""width:1241px;"">
                                              <thead>
                                                    <tr>
                                                        {0}
                                                    </tr>
                                                </thead>
                                            </table>
                                         </div>";
                    headStr = String.Format(headStr.ToString(), thStr);
                    string bodyStr = @"<div style=""width:100%;overflow-x:auto"">
                                            <table style=""width:1241px;"">
                                                <thead>
                                                    <tr>
                                                        {0}
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                        {1}
                                                </tbody>
                                            </table>
                                         </div>";


                    TestDBContext testContext = new TestDBContext();
                    var           wsList      = testContext.Database.SqlQuery <wsinfo>(@"SELECT 
                                                                            a.wsid as wsid, 
                                                                            a.WSCompany as WSCompany,
                                                                            CONVERT(varchar(100), a.goLiveDate, 23) goLiveDate,
                                                                            a.wsname as wsname,
                                                                            b.T_Name as gwname1, 
                                                                            b.T_Email as gwemail1, 
                                                                            b.T_Mobile as gwmobile1,
		                                                                    c.T_Name as gwname2, 
                                                                            c.T_Email as gwemail2, 
                                                                            c.T_Mobile as gwmobile2,
		                                                                    d.name as status
                                                                            from t_ws a 
		                                                                    left join T_ImplementPersonnel b on a.Personnel1 = b.T_Id and ISNULL(a.IsValid, 1) = 1
		                                                                    left join T_ImplementPersonnel c on a.Personnel2 = c.T_Id and ISNULL(a.IsValid, 1) = 1
		                                                                    left join t_status d on a.status=d.id"        );
                    List <string> list        = new List <string>();
                    foreach (var item in wsList)
                    {
                        list.Add(item.wsid);
                    }
                    var where = string.Join(", ", list.ToArray());


                    StringBuilder whereStr = new StringBuilder();
                    whereStr.Append(where);
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append(@"
                            SELECT 
                            wsid as wsid,
                            WSName as wsname,
                            (select count(0) from Bas_Customer where a.WSID=WSID and isnull(isvalid,1)=1) as custsl,
                            (select count(0) from DMS_pur_bill where a.WSID=WSID and PurType='41' and DateDiff(dd,purdate,getdate())=0 and status>1) as cgddsl,
                            (select count(0) from DMS_pur_bill where a.WSID=WSID and PurType='43' and DateDiff(dd,purdate,getdate())=0 and status>1) as cgddsl,
                            (select count(0) from SFA_Order_Header where a.WSID=WSID and OrderType='51' and DateDiff(dd,OrderDate,getdate())=0 and (status=1 or status=2)) as xsddwfh,
							(select count(0) from SFA_Order_Header where a.WSID=WSID and OrderType='51' and DateDiff(dd,OrderDate,getdate())=0 and (status=3 or status=4)) as xsddsl,
                            (select count(0) from SFA_Order_Header where a.WSID=WSID and OrderType='53' and DateDiff(dd,OrderDate,getdate())=0 and (status=3 or status=4)) as xsthsl
                            FROM Bas_WS a
                            where a.wsid in ({0})
                           ");
                    string           str        = String.Format(strSql.ToString(), whereStr.ToString());
                    StuInfoDBContext stuContext = new StuInfoDBContext();
                    var wsinfoTemp = stuContext.Database.SqlQuery <wsinfo>(str).ToList();
                    //将上线日期赋值后再排序
                    foreach (var item in wsinfoTemp)
                    {
                        var wsItem = wsList.Where(w => w.wsid == item.wsid).FirstOrDefault();
                        item.goLiveDate = (wsItem != null ? wsItem.goLiveDate : "");
                    }
                    var wsinfo = wsinfoTemp.OrderBy(t => t.goLiveDate).ToList();

                    //生成表格内容
                    StringBuilder tempStr = new StringBuilder();
                    for (int i = 0; i < wsinfo.Count; i++)
                    {
                        var wsItem = wsList.Where(w => w.wsid == wsinfo[i].wsid).FirstOrDefault();

                        string _str       = @"<tr>
                                            <td style='text-align:center;'>{0}</td>
                                            <td style='text-align:left;'>{1}</td>
                                            <td style='text-align:left;'>{2}</td>
                                            <td style='text-align:center;'>{3}</td>
                                            <td style='text-align:center;'>{4}</td>
                                            <td style='text-align:center;'>{5}</td>
                                            <td style='text-align:center;background-color:#d9d9d8;'>{6}</td>
                                            <td style='text-align:center;background-color:{8};color:#fff;'>{7}</td>
                                            <td style='text-align:center;'>{9}</td>
                                            <td style='text-align:left;'>{10}</td>
                                            <td style='text-align:left;'>{11}</td>
                                            <td style='text-align:left;'>{12}</td>
                                            <td style='text-align:left;'>{13}</td>
                                            <td style='text-align:left;'>{14}</td>
                                        </tr>";
                        string wsid       = wsinfo[i].wsid;
                        string wsname     = wsList.Where(w => w.WSCompany == wsItem.WSCompany).Count() > 1? wsinfo[i].wsname + "<br>【" + wsItem.WSCompany + "】" : wsinfo[i].wsname;
                        string custsl     = wsinfo[i].custsl == 0 ? "" : wsinfo[i].custsl.ToString();
                        string cgddsl     = wsinfo[i].cgddsl == 0 ? "" : wsinfo[i].cgddsl.ToString();
                        string cgthsl     = wsinfo[i].cgthsl == 0 ? "" : wsinfo[i].cgthsl.ToString();
                        string xsddwfh    = wsinfo[i].xsddwfh == 0 ? "" : wsinfo[i].xsddwfh.ToString();
                        string xsddsl     = wsinfo[i].xsddsl == 0 ? "" : wsinfo[i].xsddsl.ToString();
                        string xsthsl     = wsinfo[i].xsthsl == 0 ? "" : wsinfo[i].xsthsl.ToString();
                        string bgcolor    = wsinfo[i].xsddsl == 0 ? "red" : "#1d6c09";
                        string goLiveDate = (wsItem != null ? wsItem.goLiveDate : "");
                        string status     = (wsItem != null ? wsItem.status : "");
                        string gwname1    = (wsItem != null ? wsItem.gwname1 : "");
                        string gwemail1   = (wsItem != null ? wsItem.gwemail1 : "");
                        string gwmobile1  = (wsItem != null ? wsItem.gwmobile1 : "");
                        string gwname2    = (wsItem != null ? wsItem.gwname2 : "");
                        string gwemail2   = (wsItem != null ? wsItem.gwemail2 : "");
                        string gwmobile2  = (wsItem != null ? wsItem.gwmobile2 : "");

                        string temp = String.Format(_str.ToString(), i + 1, wsid, wsname, custsl, cgddsl, cgthsl, xsddwfh, xsddsl, bgcolor, xsthsl, goLiveDate, status, gwname1 + "<br>" + gwname2, gwemail1 + "<br>" + gwemail2, gwmobile1 + "<br>" + gwmobile2);
                        tempStr.Append(temp);
                    }


                    StringBuilder sqlQueryStr = new StringBuilder();
                    sqlQueryStr.Append(strStyle);
                    sqlQueryStr.Append(labelStr);
                    //sqlQueryStr.Append(headStr);
                    bodyStr = String.Format(bodyStr.ToString(), thStr, tempStr);
                    sqlQueryStr.Append(bodyStr);


                    //是否发送
                    if (IsSend == true)
                    {
                        model.SendContent = sqlQueryStr.ToString();
                        MailSend(model, true);
                    }

                    return(sqlQueryStr.ToString());
                }
                catch (Exception ex)
                {
                    return(ex.Message);
                }
            }
            else
            {
                return(null);
            }
        }
Exemplo n.º 4
0
        public object findWsInfo()
        {
            StuInfoDBContext stuContext  = new StuInfoDBContext();
            TestDBContext    testContext = new TestDBContext();
            var wsList            = testContext.T_WS.Where(t => t.IsValid != 0).OrderByDescending(o => new { o.City, o.WSCompany, o.goLiveDate }).ToList();
            var implementItemList = testContext.T_ImplementPersonnel.Where(t => t.IsValid != 0).ToList();
            var statusItemList    = testContext.T_Status.Where(t => 1 == 1).ToList();

            foreach (var item in wsList)
            {
                var implementItem1 = implementItemList.Where(t => t.T_Id == item.Personnel1).FirstOrDefault();
                var implementItem2 = implementItemList.Where(t => t.T_Id == item.Personnel2).FirstOrDefault();
                var statusItem     = statusItemList.Where(t => t.Id == item.status).FirstOrDefault();

                item.Personnel1Name   = implementItem1 == null ? "" : implementItem1.T_Name;
                item.Personnel1Email  = implementItem1 == null ? "" : implementItem1.T_Email;
                item.Personnel1Mobile = implementItem1 == null ? "" : implementItem1.T_Mobile;
                item.Personnel2Name   = implementItem2 == null ? "" : implementItem2.T_Name;
                item.Personnel2Email  = implementItem2 == null ? "" : implementItem2.T_Email;
                item.Personnel2Mobile = implementItem2 == null ? "" : implementItem2.T_Mobile;
                item.statusName       = statusItem == null ? "" : statusItem.name;
                item.sszStatus        = item.researchBeginDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) <= 0 && item.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) > 0 ? 1 : 0;
                item.ysxStatus        = item.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) <= 0 ? 1 : 0;
                item.wsxStatus        = item.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) > 0 ? 1 : 0;
                item.planStatus       = 1;
                ////获取工作日
                //if(DateTime.Now.ToString("yyyy-MM")== item.goLiveDate.Value.ToString("yyyy-MM"))
                //{
                //    string str1 = DateTime.Now.ToString("yyyy-MM-dd");
                //    string str2 = item.goLiveDate.Value.ToString("yyyy-MM-dd");
                //    DateTime d1 = Convert.ToDateTime(str1);
                //    DateTime d2 = Convert.ToDateTime(str2);
                //    DateTime d3 = Convert.ToDateTime(string.Format("{0}-{1}-{2}", d1.Year, d1.Month, d1.Day));
                //    DateTime d4 = Convert.ToDateTime(string.Format("{0}-{1}-{2}", d2.Year, d2.Month, d2.Day));
                //    int days = (d3 - d4).Days;
                //    item.gzrCount = days;
                //}else
                //{
                //    item.gzrCount = 30;
                //}
            }

            var sszWsNumber  = wsList.Where(t => t.researchBeginDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) <= 0 && t.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) > 0).Count();
            var ysxWsNumber  = wsList.Where(t => t.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) <= 0).Count();
            var wsxWsNumber  = wsList.Where(t => t.goLiveDate.Value.CompareTo(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"))) > 0).Count();
            var planWsNumber = wsList.Count();

            //获取正式库销量数据
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"
                            select 
                            a.WSID,
                            b.WSName,
                            (select count(0) from Bas_Customer where isnull(isvalid,1)=1 and wsid=a.wsid) as custCount,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}01'then CONVERT(char(10),a.OrderDate,120) end) as xsCount1,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}02'then CONVERT(char(10),a.OrderDate,120) end) as xsCount2,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}03'then CONVERT(char(10),a.OrderDate,120) end) as xsCount3,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}04'then CONVERT(char(10),a.OrderDate,120) end) as xsCount4,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}05'then CONVERT(char(10),a.OrderDate,120) end) as xsCount5,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}06'then CONVERT(char(10),a.OrderDate,120) end) as xsCount6,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}07'then CONVERT(char(10),a.OrderDate,120) end) as xsCount7,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}08'then CONVERT(char(10),a.OrderDate,120) end) as xsCount8,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}09'then CONVERT(char(10),a.OrderDate,120) end) as xsCount9,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}10'then CONVERT(char(10),a.OrderDate,120) end) as xsCount10,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}11'then CONVERT(char(10),a.OrderDate,120) end) as xsCount11,
                            count(distinct case when convert(varchar(6),a.OrderDate,112)='{1}12'then CONVERT(char(10),a.OrderDate,120) end) as xsCount12
                            from SFA_Order_Header a 
                            left join Bas_WS b 
                            on a.WSID=b.WSID and isnull(b.isvalid,1)=1 
                            where a.OrderType='51' and  (a.status=3 or a.status=4) and a.wsid in ({0}) 
                            group by a.WSID,b.WSName
                           ");
            List <string> list = new List <string>();

            foreach (var item in wsList)
            {
                list.Add(item.WSID);
            }
            var    whereStr   = string.Join(",", list.ToArray());
            string str        = String.Format(strSql.ToString(), whereStr.ToString(), DateTime.Now.ToString("yyyy"));
            var    wsinfoTemp = stuContext.Database.SqlQuery <T_WS>(str).ToList();

            foreach (var item in wsList)
            {
                var wsItem = wsinfoTemp.Where(w => w.WSID == item.WSID).FirstOrDefault();
                if (wsItem != null)
                {
                    item.WSName        = wsItem.WSName;
                    item.custCount     = wsItem.custCount;
                    item.xsCount1Name  = wsItem.xsCount1 == 0 || item.goLiveDate.Value.Month > 1 ? "-" : wsItem.xsCount1.ToString() + " / " + getDays(item.goLiveDate, 1);
                    item.xsCount2Name  = wsItem.xsCount2 == 0 || item.goLiveDate.Value.Month > 2 ? "-" : wsItem.xsCount2.ToString() + " / " + getDays(item.goLiveDate, 2);
                    item.xsCount3Name  = wsItem.xsCount3 == 0 || item.goLiveDate.Value.Month > 3 ? "-" : wsItem.xsCount3.ToString() + " / " + getDays(item.goLiveDate, 3);
                    item.xsCount4Name  = wsItem.xsCount4 == 0 || item.goLiveDate.Value.Month > 4 ? "-" : wsItem.xsCount4.ToString() + " / " + getDays(item.goLiveDate, 4);
                    item.xsCount5Name  = wsItem.xsCount5 == 0 || item.goLiveDate.Value.Month > 5 ? "-" : wsItem.xsCount5.ToString() + " / " + getDays(item.goLiveDate, 5);
                    item.xsCount6Name  = wsItem.xsCount6 == 0 || item.goLiveDate.Value.Month > 6 ? "-" : wsItem.xsCount6.ToString() + " / " + getDays(item.goLiveDate, 6);
                    item.xsCount7Name  = wsItem.xsCount7 == 0 || item.goLiveDate.Value.Month > 7 ? "-" : wsItem.xsCount7.ToString() + " / " + getDays(item.goLiveDate, 7);
                    item.xsCount8Name  = wsItem.xsCount8 == 0 || item.goLiveDate.Value.Month > 8 ? "-" : wsItem.xsCount8.ToString() + " / " + getDays(item.goLiveDate, 8);
                    item.xsCount9Name  = wsItem.xsCount9 == 0 || item.goLiveDate.Value.Month > 9 ? "-" : wsItem.xsCount9.ToString() + " / " + getDays(item.goLiveDate, 9);
                    item.xsCount10Name = wsItem.xsCount10 == 0 || item.goLiveDate.Value.Month > 10 ? "-" : wsItem.xsCount10.ToString() + " / " + getDays(item.goLiveDate, 10);
                    item.xsCount11Name = wsItem.xsCount11 == 0 || item.goLiveDate.Value.Month > 11 ? "-" : wsItem.xsCount11.ToString() + " / " + getDays(item.goLiveDate, 11);
                    item.xsCount12Name = wsItem.xsCount12 == 0 || item.goLiveDate.Value.Month > 12 ? "-" : wsItem.xsCount12.ToString() + " / " + getDays(item.goLiveDate, 12);
                }
            }

            var obj = new { rows = wsList, sszWsNumber = sszWsNumber, ysxWsNumber = ysxWsNumber, wsxWsNumber = wsxWsNumber, planWsNumber = planWsNumber };

            return(Json(obj));
        }