DataTableのComputeによる要約計算


//    
            if (active == "initmaindatas")
            {
                #region       ,  initcontractdatas
                string offset = context.Request["offset"] + string.Empty;
                string limit = context.Request["limit"] + string.Empty;
                //string isInit = context.Request["isInit"].ToLower();
                int ioffset = StrHelper.ParseInt(offset, 0);
                int ipagesize = StrHelper.ParseInt(limit, 3);
                int ipageindex = ioffset / ipagesize;

                PagingHelper page = new PagingHelper();
                page.PageIndex = ipageindex;
                page.RowsInPage = ipagesize;

                //    
                TreeListItem[] typeList = CommonTypeCache.ListCommonTypeByDefCode("5101014", false);

                #region     
                List resultList = new List();
                string connectionStr = DBUtil.ConnectionStr;
                //        
                string strsql1 = string.Format(@" 
                SELECT P.NAME PRONAME,
                       P.ID,
                       P.INNER_CODE,
                       P.STATE,
                       P.CODE,
                       P.SIMPLE_NAME
                  FROM PROJECT_INFO     P
                 WHERE parent_id = '"+projectid+@"'  
                   AND LENGTH(P.INNER_CODE)=8 --       ");

                DataSet ds1 = DbHelper.ExecuteDataset(connectionStr, CommandType.Text, strsql1, null);
                DataTable dt1 = new DataTable();
                if (ds1 != null && ds1.Tables.Count > 0)
                {
                    dt1 = ds1.Tables[0];
                }

                //       
                string strsql2 = @" 
                --  :     、      、      ,           
                SELECT distinct T.TASK_NAME,
                        T.PLAN_END_TIME,
                        T.END_TIME,
                        T.PROJECT_ID,
                        P.NAME PRONAME,
                        P.STATE,
                        P.INNER_CODE,
                        C.ID,
                        C.PROJECT_ID
                FROM (SELECT SP.*
                        FROM PROJECT_INFO P, PROJECT_INFO SP
                       WHERE SP.INNER_CODE LIKE P.INNER_CODE || '%'  ) P,       
                    PROJECT_TASKPLAN T,
                    BASE_CONTRACT    C,
                    BASE_WBS         W
                WHERE P.ID = C.PROJECT_ID
                AND W.CONTRACT_ID = C.ID
                AND W.PROJECT_ID = T.PROJECT_ID
                AND W.CODE = T.WBS_CODE
                AND (T.TASK_TYPE = 'TT_Mile' OR T.TASK_TYPE = 'TT_FinMile')";

                DataSet ds2 = DbHelper.ExecuteDataset(connectionStr, CommandType.Text, strsql2, null);
                DataTable dt2 = new DataTable();
                if (ds2 != null && ds2.Tables.Count > 0)
                {
                    dt2 = ds2.Tables[0];
                }

                if (dt1 != null && dt1.Rows.Count > 0)
                {
                    //      
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        #region
                        ProgressItem item = new ProgressItem();
                        item.PROJECT_ID = dr1["ID"].ToString();
                        item.INNER_CODE = dr1["INNER_CODE"].ToString();
                        item.PRONAME = dr1["PRONAME"].ToString();
                        item.JDYJ = !string.IsNullOrEmpty(dr1["STATE"].ToString()) ? typeList.Where(t => t.Id == dr1["STATE"].ToString()).FirstOrDefault().Text : "";   //    

                        //  DataTable Compute    
                        //object obj = dt2.Compute("sum(JHJE)", "PROJECT_ID = '" + item.PROJECT_ID + "' AND INNER_CODE like'" + item.INNER_CODE + "%'");
                        object PLAN_END_TIME = dt2.Compute("count(TASK_NAME)", "INNER_CODE  LIKE '" + dr1["INNER_CODE"].ToString() + "%'");
                        object END_TIME = dt2.Compute("count(TASK_NAME)", "END_TIME IS NOT NULL AND INNER_CODE  LIKE '" + dr1["INNER_CODE"].ToString() + "%'");

                        item.PLAN_END_TIME = PLAN_END_TIME == null ? "0" : PLAN_END_TIME.ToString();  //     
                        item.END_TIME = END_TIME == null ? "0" : END_TIME.ToString();       //     
                        resultList.Add(item);
                        #endregion
                    }
                }
                #endregion

                List list = resultList.OrderBy(t => t.INNER_CODE).Skip((ipageindex) * ipagesize).Take(ipagesize).ToList();
                var FirtProjectId = list.FirstOrDefault().PROJECT_ID;

                PageResult result = new PageResult()
                {
                    pageindex = ipageindex,
                    rows = list,
                    total = resultList.Count
                };
                context.Response.Write((new JavaScriptSerializer()).Serialize(result));
                #endregion
            }