コード例 #1
0
        protected WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.AssetsNo) != "")
            {
                wcd.Sql += @" and T1.assetsNo  like '%'+@assetsNo+'%'";
                wcd.DBPara.Add("assetsNo", model.AssetsNo);
            }
            if (DataConvert.ToString(model.AssetsName) != "")
            {
                wcd.Sql += @" and T1.assetsName  like '%'+@assetsName+'%'";
                wcd.DBPara.Add("assetsName", model.AssetsName);
            }
            if (DataConvert.ToString(model.BorrowDate1) != "")
            {
                wcd.Sql += @" and T1.borrowDate>=@borrowDate1";
                wcd.DBPara.Add("borrowDate1", DataConvert.ToString(model.BorrowDate1) + " 00:00:00");
            }
            if (DataConvert.ToString(model.BorrowDate2) != "")
            {
                wcd.Sql += @" and T1.borrowDate<=@borrowDate2";
                wcd.DBPara.Add("borrowDate2", DataConvert.ToString(model.BorrowDate2) + " 23:59:59");
            }
            return(wcd);
        }
コード例 #2
0
        protected override WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            ApproveListCondition acondition = condition as ApproveListCondition;

            if (DataConvert.ToString(acondition.ListMode) != "")
            {
                wcd.DBPara.Add("approver", acondition.Approver);
            }
            ListModel model = JsonHelper.Deserialize <ListModel>(acondition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.AssetsMaintainNo) != "")
            {
                wcd.Sql += @" and AssetsMaintain.assetsMaintainNo like '%'+@assetsMaintainNo+'%'";
                wcd.DBPara.Add("assetsMaintainNo", model.AssetsMaintainNo);
            }
            if (DataConvert.ToString(model.AssetsMaintainName) != "")
            {
                wcd.Sql += @" and AssetsMaintain.assetsMaintainName like '%'+@assetsMaintainName+'%'";
                wcd.DBPara.Add("assetsMaintainName", model.AssetsMaintainName);
            }
            return(wcd);
        }
コード例 #3
0
        protected WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.AssetsMergeNo) != "")
            {
                wcd.Sql += @" and AssetsMerge.assetsMergeNo  like '%'+@assetsMergeNo+'%'";
                wcd.DBPara.Add("assetsMergeNo", model.AssetsMergeNo);
            }
            if (DataConvert.ToString(model.AssetsMergeName) != "")
            {
                wcd.Sql += @" and AssetsMerge.assetsMergeName  like '%'+@assetsMergeName+'%'";
                wcd.DBPara.Add("assetsMergeName", model.AssetsMergeName);
            }
            if (DataConvert.ToString(model.AssetsNo) != "")
            {
                wcd.Sql += @" and A1.assetsNo  like '%'+@assetsNo+'%'";
                wcd.DBPara.Add("assetsNo", model.AssetsNo);
            }
            if (DataConvert.ToString(model.AssetsName) != "")
            {
                wcd.Sql += @" and A1.assetsName  like '%'+@assetsName+'%'";
                wcd.DBPara.Add("assetsName", model.AssetsName);
            }
            return(wcd);
        }
コード例 #4
0
        protected override string ListSql(ListCondition condition)
        {
            string subViewSql = string.Format(@"select  AppUser.userId userId,
                                 AppUser.userNo userNo,
                                 AppUser.userName userName,
                                 AppUser.groupIdDisplay groupId ,  
                                 AppUser.departmentDisplay departmentId,
                                (select departmentName from AppDepartment C where D.companyId=C.departmentId ) companyId,
                                 AppPost.postName postId,
                                 (select CodeTable.codeName from CodeTable where AppUser.sex=CodeTable.codeNo and CodeTable.codeType='Sex' and CodeTable.languageVer='{0}' ) sex,
                                 AppUser.tel tel,
                                 AppUser.email email,
                                 AppUser.address address,
                                (select CodeTable.codeName from CodeTable where AppUser.isFixed=CodeTable.codeNo and CodeTable.codeType='BoolVal' and CodeTable.languageVer='{0}' ) isFixed,
                                 U1.userName createId,
                                 AppUser.createTime,
                                 U2.userName updateId,
                                 AppUser.updateTime  updateTime,
                                 AppUser.updatePro updatePro
                              from AppUser left join AppGroup on AppUser.groupId=AppGroup.groupId   
                              left join AppDepartment D on AppUser.departmentId=D.departmentId 
                              left join AppPost on AppUser.postId=AppPost.postId 
                              left join AppUser U1 on AppUser.createId=U1.userId
                              left join AppUser U2 on AppUser.updateId=U2.userId  
                          where 1=1 ", AppMember.AppLanguage.ToString());

            return(subViewSql);
        }
コード例 #5
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            int    rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
            string sql     = string.Format(@"select  Assets.assetsId assetsId,
                        Assets.assetsNo assetsNo,
                        Assets.assetsName assetsName,
                        AssetsMaintain.assetsMaintainNo assetsMaintainNo,
                        AssetsMaintain.assetsMaintainName assetsMaintainName,
                        convert(nvarchar(100),  AssetsMaintainDetail.maintainDate  ,23) maintainDate,
                        AssetsMaintainDetail.maintainCompany maintainCompany,
                        AssetsMaintainDetail.maintainAmount maintainAmount,
                        AssetsMaintainDetail.maintainActualCompany maintainActualCompany,
                        AssetsMaintainDetail.maintainActualAmount maintainActualAmount,
                       convert(nvarchar(100),AssetsMaintainDetail.maintainActualDate  ,23) maintainActualDate,
                        AssetsMaintainDetail.remark Remark
                       from AssetsMaintainDetail,Assets,AssetsMaintain 
                        where AssetsMaintainDetail.assetsId=Assets.assetsId  
                        and AssetsMaintainDetail.assetsMaintainId=AssetsMaintain.assetsMaintainId 
                        and (AssetsMaintainDetail.approveState='E' or AssetsMaintainDetail.approveState is null or AssetsMaintainDetail.approveState='') 
                        {0} {1} ", ListWhereSql(condition).Sql,
                                           " order by  AssetsMaintain.assetsMaintainNo,Assets.assetsNo ");
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #6
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            string sql = string.Format(@"select  row_number() over(order by AssetsCheck.assetsCheckNo,Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
                        Assets.assetsNo assetsNo,
                        Assets.assetsName assetsName,
                        AssetsCheck.assetsCheckNo assetsCheckNo,
                        AssetsCheck.assetsCheckName assetsCheckName,
                       (select userName from AppUser where AssetsCheck.checkPeople=AppUser.userId) checkPeople,
                         convert(nvarchar(100),   AssetsCheckDetail.checkDate ,23)  checkDate,
                      convert(nvarchar(100),    AssetsCheckDetail.actualCheckDate ,23)  actualCheckDate,
                        (select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.storeSiteId) storeSiteId,  
                        (select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.actualStoreSiteId) actualStoreSiteId,                       
                        (select CodeTable.codeName from CodeTable where AssetsCheckDetail.checkResult=CodeTable.codeNo and CodeTable.codeType='CheckResult' and CodeTable.languageVer='{0}' ) checkResult,
                        AssetsCheckDetail.isFinished isFinished,
                        AssetsCheckDetail.remark Remark
                       from AssetsCheckDetail,Assets,AssetsCheck 
                      where AssetsCheckDetail.assetsId=Assets.assetsId  
                        and AssetsCheckDetail.assetsCheckId=AssetsCheck.assetsCheckId 
                        and (AssetsCheckDetail.approveState='E' or AssetsCheckDetail.approveState is null or AssetsCheckDetail.approveState='') 
                        {1}  ", AppMember.AppLanguage.ToString(), ListWhereSql(condition).Sql);

            if (needPaging)
            {
                sql = string.Format("select top {0} *  from ({1}) A where rownumber > {2} order by assetsCheckNo,assetsNo ", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
            }
            else
            {
                sql += " order by AssetsCheck.assetsCheckNo,Assets.assetsNo ";
            }
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #7
0
ファイル: QueryController.cs プロジェクト: ktz-tec/WebApp_STD
        public ActionResult ExcelPrint(string formvar)
        {
            //ListCondition condition = new ListCondition();
            //condition.SortField = DataConvert.ToString(Request.Params["sidx"]);
            //condition.SortType = DataConvert.ToString(Request.Params["sord"]);
            //condition.PageIndex = DataConvert.ToInt32(Request.Params["page"]);
            //condition.PageRowNum = DataConvert.ToInt32(Request.Params["rows"]);
            //condition.ListModelString = formvar;
            //DataTable dt = Repository.GetReportGridDataTable(condition);
            //QueryEntryViewModel model = JsonHelper.Deserialize<QueryEntryViewModel>(formvar);
            //ExcelHelper.PrintExcel(dt, EntryGridLayout(model.FormMode), "main");
            //return Content("1", "text/html");
            ListCondition condition = new ListCondition();

            condition.SortField       = DataConvert.ToString(Request.Params["sidx"]);
            condition.SortType        = DataConvert.ToString(Request.Params["sord"]);
            condition.PageIndex       = DataConvert.ToInt32(Request.Params["page"]);
            condition.PageRowNum      = DataConvert.ToInt32(Request.Params["rows"]);
            condition.ListModelString = formvar;
            DataTable           dt    = Repository.GetReportGridDataTable(condition, false);
            QueryEntryViewModel model = JsonHelper.Deserialize <QueryEntryViewModel>(formvar);

            model.EntryGridData   = dt;
            model.EntryGridLayout = EntryGridLayout(model.FormMode);
            model.EntryGridId     = "EntryGrid";
            model.FormId          = "PrintForm";
            model.GridHeight      = 375;
            return(View("ReportPrint", model));
        }
コード例 #8
0
        private static IResultAccessor <T> CreateCondition(Func <T, int, bool> condition, IListItemSetterManager <T> manager)
        {
            var c = new ListCondition <T>(condition);

            manager.Add(c);
            return(c);
        }
コード例 #9
0
        protected override WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            if (condition.SysUser.UserNo != "sa" && condition.SysUser.IsHeaderOffice != "Y")
            {
                wcd.Sql += @" and D.companyId=@companyId";
                wcd.DBPara.Add("companyId", condition.SysUser.CompanyId);
            }
            ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.StoreSiteNo) != "")
            {
                wcd.Sql += @" and StoreSite.storeSiteNo like '%'+@storeSiteNo+'%'";
                wcd.DBPara.Add("storeSiteNo", model.StoreSiteNo);
            }
            if (DataConvert.ToString(model.StoreSiteName) != "")
            {
                wcd.Sql += @" and StoreSite.storeSiteName like '%'+@storeSiteName+'%'";
                wcd.DBPara.Add("storeSiteName", model.StoreSiteName);
            }
            return(wcd);
        }
コード例 #10
0
        private IResultAccessor <T> CreateAddInsertCondition(Func <T, int, bool> condition, IListItemSetterManager <T> manager)
        {
            var c = new ListCondition <T>(condition);

            // Go through all of the existing items to see if this condition
            // has already been met. Otherwise the caller could potentially
            // wait forever.
            bool conditionNotMet = true;

            for (int index = 0; index < list.Count; index++)
            {
                if (c.TrySetResult(list[index], index))
                {
                    conditionNotMet = false;
                }
            }

            // The condition was not met by any of the items in the list.
            // Add the condition to the manager to be monitored.
            if (conditionNotMet)
            {
                manager.Add(c);
            }

            return(c);
        }
コード例 #11
0
 protected override string ListSql(ListCondition condition)
 {
     ApproveListCondition acondition = condition as ApproveListCondition;
     string lsql = " where 1=1";
     if (DataConvert.ToString(acondition.ListMode) != "")
     {
         if (DataConvert.ToString(acondition.ListMode) == "approve")
         {
             lsql = @",AppApprove
              where AppApprove.tableName='AssetsSplit' and AppApprove.approveState='O'
               and AssetsSplit.AssetsSplitId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
         }
         else if (DataConvert.ToString(acondition.ListMode) == "reapply")
         {
             lsql = @" where AssetsSplit.createId=@approver and AssetsSplit.approveState='R' ";
         }
     }
     string subViewSql = string.Format(@"select AssetsSplit.AssetsSplitId AssetsSplitId,
                 AssetsSplit.AssetsSplitNo AssetsSplitNo,
                 AssetsSplit.AssetsSplitName AssetsSplitName,
                 (select userName from AppUser where AssetsSplit.createId=AppUser.userId) createId,
                 AssetsSplit.createTime createTime ,
                 (select userName from AppUser where AssetsSplit.updateId=AppUser.userId) updateId,
                 AssetsSplit.updateTime updateTime ,
                 AssetsSplit.updatePro updatePro
         from AssetsSplit  {0}  ",  lsql);
     return subViewSql;
 }
コード例 #12
0
ファイル: UnitRepository.cs プロジェクト: ktz-tec/WebApp_STD
        protected override WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.UnitNo) != "")
            {
                wcd.Sql += @" and Unit.unitNo like '%'+@unitNo+'%'";
                wcd.DBPara.Add("unitNo", model.UnitNo);
            }
            if (DataConvert.ToString(model.UnitName) != "")
            {
                wcd.Sql += @" and Unit.unitName like '%'+@unitName+'%'";
                wcd.DBPara.Add("unitName", model.UnitName);
            }
            if (DataConvert.ToString(model.UnitType) != "")
            {
                wcd.Sql += @" and Unit.unitType =@unitType ";
                wcd.DBPara.Add("unitType", model.UnitType);
            }
            return(wcd);
        }
コード例 #13
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            int       rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
            string    sql     = string.Format(@"select AssetsMergeDetail.newAssetsId,
                                A1.assetsNo newAssetsNo,
                                A1.assetsName newAssetsName,
                                A1.assetsValue newAssetsValue,
                                AssetsMerge.assetsMergeNo,
                                AssetsMerge.assetsMergeName,
                                A2.assetsNo originalAssetsNo,
                                A2.assetsName originalAssetsName,
                                A2.assetsValue originalAssetsValue,
                                AssetsMergeDetail.originalAssetsId,
                                AssetsMergeDetail.remark
                                from AssetsMergeDetail,
                                AssetsMerge ,
                                Assets A1,
                                Assets A2
                                where AssetsMergeDetail.assetsMergeId=AssetsMerge.assetsMergeId 
                                and AssetsMergeDetail.newAssetsId=A1.assetsId
                                and AssetsMergeDetail.originalAssetsId=A2.assetsId  
                                 and (AssetsMergeDetail.approveState='E' or AssetsMergeDetail.approveState is null) 
                                {0} {1}", ListWhereSql(condition).Sql, " order by AssetsMerge.assetsMergeNo,A1.assetsNo ");
            DataTable dtGrid  = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #14
0
ファイル: QueryController.cs プロジェクト: ktz-tec/WebApp_STD
        public virtual JsonResult EntryGridData(string formVar)
        {
            ListCondition condition = new ListCondition();

            condition.SortField       = DataConvert.ToString(Request.Form["sidx"]);
            condition.SortType        = DataConvert.ToString(Request.Params["sord"]);
            condition.PageIndex       = DataConvert.ToInt32(Request.Params["page"]);
            condition.PageRowNum      = DataConvert.ToInt32(Request.Params["rows"]);
            condition.ListModelString = formVar;
            var rows = new object[0];
            int cnt  = 0;

            if (DataConvert.ToString(formVar) != "")
            {
                cnt = Repository.GetReportGridDataTable(condition, false).Rows.Count;
                condition.TotalRowNum = cnt;
                QueryEntryViewModel model = JsonHelper.Deserialize <QueryEntryViewModel>(formVar);
                DataTable           dt    = Repository.GetReportGridDataTable(condition, true);
                rows = DataTable2Object.Data(dt, EntryGridLayout(model.FormMode).GridLayouts);
            }
            double aa      = (double)cnt / condition.PageRowNum;
            double pageCnt = Math.Ceiling(aa);
            var    result  = new JsonResult();

            result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            result.Data = new { page = 1, records = cnt, total = pageCnt, rows = rows };
            return(result);
        }
コード例 #15
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            string sql = string.Format(@"select  row_number() over(order by  Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
                        Assets.assetsNo assetsNo,
                        Assets.assetsName assetsName,
                        (select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
	                (select departmentName from AppDepartment where Assets.departmentId=AppDepartment.departmentId) departmentId,
                   (select storeSiteName from StoreSite where Assets.storeSiteId=StoreSite.storeSiteId) storeSiteId,
	                  Assets.durableYears durableYears,
	                convert(nvarchar(100),  Assets.purchaseDate ,23) purchaseDate,
                    convert(nvarchar(100),  DATEADD(yy,Assets.durableYears,Assets.purchaseDate) ,23)  endDate,
	                (select CodeTable.codeName from CodeTable where Assets.assetsState=CodeTable.codeNo and CodeTable.codeType='AssetsState' and CodeTable.languageVer='{0}' ) assetsState
                from Assets where 1=1 {1}  ", AppMember.AppLanguage.ToString(), ListWhereSql(condition).Sql);

            if (needPaging)
            {
                sql = string.Format("select top {0} *  from ({1}) A where rownumber > {2} order by   assetsNo ", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
            }
            else
            {
                sql += " order by   Assets.assetsNo";
            }
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #16
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="paras"></param>
        /// <param name="isInnerOrder">内部的排序用相反排序,外部正常排序</param>
        /// <returns></returns>
        private string OrderBy(ListCondition condition, int layer, string defaultSort)
        {
            string sql       = defaultSort;
            string sortField = condition.SortField;
            string sortType  = condition.SortType;

            if (sortField != "")
            {
                if (SafeSql.ProcessSqlStr(sortField, 0) && SafeSql.ProcessSqlStr(sortType, 0))
                {
                    if (layer == 2)
                    {
                        if (sortType == "asc")
                        {
                            sql = string.Format(" order by  {0} desc ", sortField);
                        }
                        else
                        {
                            sql = string.Format(" order by  {0}  ", sortField);
                        }
                    }
                    else
                    {
                        sql = string.Format(" order by  {0} {1} ", sortField, sortType);
                    }
                }
            }
            return(sql);
        }
コード例 #17
0
        public virtual DataTable GetGridDataTable(ListCondition condition)
        {
            int topNum = 0; //当前页显示的条数

            if (condition.PageIndex * condition.PageRowNum > condition.TotalRowNum)
            {
                topNum = condition.TotalRowNum - (condition.PageIndex - 1) * condition.PageRowNum;
            }
            else
            {
                topNum = condition.PageRowNum;
            }
            int    rowSize = condition.PageIndex * condition.PageRowNum; //当前视图需要的尺寸
            string sql     = string.Format(@" select * from 
                                            (select top {0} * from
                                               (select top {1} * from ({2} {3} )P {4})T 
                                             {5})F {6}  ", DataConvert.ToString(topNum),
                                           rowSize,
                                           ListSql(condition),
                                           ListWhereSql(condition).Sql,
                                           OrderBy(condition, 1, string.Format(@"order by {0} desc ", DefaulteGridSortField)),
                                           OrderBy(condition, 2, string.Format(@"order by {0} ", DefaulteGridSortField)),
                                           OrderBy(condition, 3, string.Format(@"order by {0} desc", DefaulteGridSortField)));
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #18
0
        protected virtual DataTable GetStoreSiteTable(ListCondition condition)
        {
            string    sql   = string.Format(@"select MonthRecord.*,
            StoreSite.storeSitePath,
            StoreSite.storeSiteName 
            from MonthRecord,StoreSite 
            where  MonthRecord.storeSiteId=StoreSite.storeSiteId {0}
            order by StoreSite.storeSiteNo ", ListWhereSql(condition).Sql);
            DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlGroup = string.Format(@"select distinct StoreSite.storeSiteId, 
            StoreSite.storeSiteNo,
            StoreSite.storeSiteName ,
            StoreSite.storeSitePath
            from MonthRecord,StoreSite 
            where  MonthRecord.storeSiteId=StoreSite.storeSiteId {0}
            order by StoreSite.storeSiteNo ", ListWhereSql(condition).Sql);
            DataTable dtGroup  = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlView = @"select MonthRecord.* ,
                              StoreSite.storeSiteName 
                              from MonthRecord,StoreSite 
                             where MonthRecord.storeSiteId=StoreSite.storeSiteId and 1<>1 ";
            DataTable dtView  = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];

            SetDataView(dtView, dtAll, dtGroup, "storeSite");
            return(dtView);
        }
コード例 #19
0
        protected virtual DataTable GetDepartmentTable(ListCondition condition)
        {
            string    sql   = string.Format(@"select MonthRecord.*,
            AppDepartment.departmentPath,
            AppDepartment.departmentName 
            from MonthRecord,AppDepartment 
            where  MonthRecord.departmentId=AppDepartment.departmentId {0}
            order by AppDepartment.departmentNo ", ListWhereSql(condition).Sql);
            DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlGroup = string.Format(@"select distinct AppDepartment.departmentId, 
            AppDepartment.departmentNo,
            AppDepartment.departmentName ,
            AppDepartment.departmentPath
            from MonthRecord,AppDepartment 
            where  MonthRecord.departmentId=AppDepartment.departmentId {0}
            order by AppDepartment.departmentNo ", ListWhereSql(condition).Sql);
            DataTable dtGroup  = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlView = @"select MonthRecord.* ,
                              AppDepartment.departmentName 
                              from MonthRecord,AppDepartment 
                             where MonthRecord.departmentId=AppDepartment.departmentId and 1<>1 ";
            DataTable dtView  = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];

            SetDataView(dtView, dtAll, dtGroup, "department");
            return(dtView);
        }
コード例 #20
0
        protected virtual DataTable GetAssetsClassTable(ListCondition condition)
        {
            string    sql   = string.Format(@"select MonthRecord.*,
            AssetsClass.assetsClassPath,
            AssetsClass.assetsClassName 
            from MonthRecord,AssetsClass 
            where  MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
            order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
            DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlGroup = string.Format(@"select distinct AssetsClass.assetsClassId,
            AssetsClass.assetsClassNo,
            AssetsClass.assetsClassName ,
            AssetsClass.assetsClassPath
            from MonthRecord,AssetsClass 
            where  MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
            order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
            DataTable dtGroup  = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];

            string    sqlView = @"select MonthRecord.* ,
                              AssetsClass.assetsClassName 
                              from MonthRecord,AssetsClass 
                             where MonthRecord.assetsClassId=AssetsClass.assetsClassId and 1<>1 ";
            DataTable dtView  = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];

            SetDataView(dtView, dtAll, dtGroup, "assetsClass");
            return(dtView);
        }
コード例 #21
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition)
        {
            int       rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
            string    sql     = string.Format(@"select T1.*,
                                    T2.assetsReturnNo,
                                    T2.assetsReturnName,
                                    T2.returnPeople,
                                    T2.returnDate
                                    from 
                                    (select AssetsBorrowDetail.assetsId,
                                    Assets.assetsNo,
                                    Assets.assetsName,
                                    AssetsBorrow.assetsBorrowNo,
                                    AssetsBorrow.assetsBorrowName,
                                    AssetsBorrowDetail.borrowPeople,
                                    AssetsBorrowDetail.borrowDepartmentId,
                                    AssetsBorrowDetail.borrowDate
                                    from AssetsBorrow,AssetsBorrowDetail,Assets
                                    where AssetsBorrow.assetsBorrowId=AssetsBorrowDetail.assetsBorrowId
                                    and AssetsBorrowDetail.assetsId=Assets.assetsId) T1
                                    left join 
                                    (select AssetsReturnDetail.assetsId,
                                    AssetsReturn.assetsReturnNo,
                                    AssetsReturn.assetsReturnName,
                                    AssetsReturnDetail.returnPeople,
                                    AssetsReturnDetail.returnDate
                                    from AssetsReturn,AssetsReturnDetail
                                    where AssetsReturn.assetsReturnId=AssetsReturnDetail.assetsReturnId) T2
                                    on T1.assetsId=T2.assetsId {0} ", ListWhereSql(condition).Sql);
            DataTable dtGrid  = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #22
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            string sql = string.Format(@"select  row_number() over(order by AssetsScrap.assetsScrapNo,Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
                        Assets.assetsNo assetsNo,
                        Assets.assetsName assetsName,
                        AssetsScrap.assetsScrapNo assetsScrapNo,
                        AssetsScrap.assetsScrapName assetsScrapName,
                        (select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
                 (select assetsTypeName from AssetsType where Assets.assetsTypeId=AssetsType.assetsTypeId) assetsTypeId,
                (select departmentName from AppDepartment where Assets.departmentId=AppDepartment.departmentId) departmentId,
                  (select scrapTypeName from ScrapType where AssetsScrapDetail.scrapTypeId=ScrapType.scrapTypeId)   ScrapTypeId,
                 convert(nvarchar(100),  AssetsScrapDetail.scrapDate,23) ScrapDate,
                    AssetsScrapDetail.remark Remark
                from AssetsScrapDetail,Assets,AssetsScrap 
                where AssetsScrapDetail.assetsId=Assets.assetsId  
                and AssetsScrapDetail.assetsScrapId=AssetsScrap.assetsScrapId 
                and (AssetsScrapDetail.approveState='E' or AssetsScrapDetail.approveState is null  or AssetsScrapDetail.approveState='') 
                {0} ", ListWhereSql(condition).Sql);

            if (needPaging)
            {
                sql = string.Format("select top {0} *  from ({1}) A where rownumber > {2}  order by   assetsScrapNo  ,assetsNo", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
            }
            else
            {
                sql += " order by   AssetsScrap.assetsScrapNo  ,Assets.assetsNo";
            }
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #23
0
        public virtual int GetGridDataCount(ListCondition condition)
        {
//            string sql = string.Format(@"select count(*) cnt
//                          from {0}  where 1=1 {1}", MasterTable,ListWhereSql(condition).Sql);
            string    sql    = ListSql(condition) + ListWhereSql(condition).Sql;
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid.Rows.Count);
        }
コード例 #24
0
        public virtual DataTable GetAllGridDataTable(ListCondition condition)
        {
            string sql = string.Format(@" {0} {1} {2} ",
                                       ListSql(condition),
                                       ListWhereSql(condition).Sql,
                                       OrderBy(condition, 1, string.Format(@"order by {0} ", DefaulteGridSortField)));
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #25
0
        protected override string ListSql(ListCondition condition)
        {
            ApproveListCondition acondition = condition as ApproveListCondition;

            string subViewSql = @"select distinct AssetsTransfer.assetsTransferId assetsTransferId,
                        AssetsTransfer.assetsTransferNo assetsTransferNo,
                        AssetsTransfer.assetsTransferName assetsTransferName,
                         convert(nvarchar(100), (select top 1 transferDate from AssetsTransferDetail where assetsTransferId=AssetsTransfer.assetsTransferId),23)  transferDate,
   AssetsTransfer.approveState ApproveState_val,
(select top 1 codename from CodeTable where codetype='ApproveState' and  AssetsTransfer.approveState=codeno) approveState,                       
(select top 1 AssetsClass.assetsClassName from AssetsTransferDetail,Assets,AssetsClass where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId and Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
                       (select top 1 Assets.assetsNo from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsNo,
                       (select top 1 Assets.assetsName from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsName,
                       (select top 1 Assets.spec from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) spec,
                       (select top 1 Assets.assetsQty from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsQty,
                        (select userName from AppUser where AssetsTransfer.createId=AppUser.userId) createId,
                        AssetsTransfer.createTime createTime ,
                        (select userName from AppUser where AssetsTransfer.updateId=AppUser.userId) updateId,
                        AssetsTransfer.updateTime updateTime ,
                        AssetsTransfer.updatePro updatePro
                from AssetsTransfer  ";


            string    lsql  = " where 1=1";
            ListModel model = JsonHelper.Deserialize <ListModel>(acondition.ListModelString);

            if (model == null || (model != null && !model.QueryAllApproveRecord))
            {
                if (DataConvert.ToString(acondition.ListMode) != "")
                {
                    if (DataConvert.ToString(acondition.ListMode) == "approve")
                    {
                        lsql = @",AppApprove
                     where AppApprove.tableName='AssetsTransfer' and AppApprove.approveState in ('O','B')
                      and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
                    }
                    else if (DataConvert.ToString(acondition.ListMode) == "reapply")
                    {
                        lsql = @" ,AppApprove
                     where AppApprove.tableName='AssetsTransfer' and AppApprove.approveState in ('B')
                      and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
                    }
                }
            }
            else
            {
                lsql = @",AppApprove
                     where AppApprove.tableName='AssetsTransfer' 
                      and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and (isValid='Y' or Approvetime is not null ) ";
            }
            subViewSql = subViewSql + lsql;
            subViewSql = string.Format(" select * from ({0}) M where 1=1 ", subViewSql);
            return(subViewSql);
        }
コード例 #26
0
        protected WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.AssetsNo) != "")
            {
                wcd.Sql += @" and Assets.assetsNo  like '%'+@assetsNo+'%'";
                wcd.DBPara.Add("assetsNo", model.AssetsNo);
            }
            if (DataConvert.ToString(model.AssetsName) != "")
            {
                wcd.Sql += @" and Assets.assetsName  like '%'+@assetsName+'%'";
                wcd.DBPara.Add("assetsName", model.AssetsName);
            }
            if (DataConvert.ToString(model.AssetsClassId) != "")
            {
                wcd.Sql += @" and  Assets.assetsClassId=@assetsClassId";
                wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
            }
            if (DataConvert.ToString(model.DepartmentId) != "")
            {
                wcd.Sql += @" and Assets.departmentId=@departmentId";
                wcd.DBPara.Add("departmentId", model.DepartmentId);
            }
            if (DataConvert.ToString(model.StoreSiteId) != "")
            {
                wcd.Sql += @" and Assets.storeSiteId=@storeSiteId";
                wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
            }
            if (DataConvert.ToString(model.AssetsState) != "")
            {
                wcd.Sql += @" and Assets.assetsState=@assetsState";
                wcd.DBPara.Add("assetsState", model.AssetsState);
            }
            if (DataConvert.ToString(model.DepreciationEndDate1) != "")
            {
                wcd.Sql += @" and DATEADD(yy,Assets.durableYears,Assets.purchaseDate)>=@depreciationEndDate1 ";
                wcd.DBPara.Add("depreciationEndDate1", DataConvert.ToString(model.DepreciationEndDate1) + " 00:00:00");
            }
            if (DataConvert.ToString(model.DepreciationEndDate2) != "")
            {
                wcd.Sql += @" and DATEADD(yy,Assets.durableYears,Assets.purchaseDate)<=@depreciationEndDate2 ";
                wcd.DBPara.Add("depreciationEndDate2", DataConvert.ToString(model.DepreciationEndDate2) + " 23:59:59");
            }
            return(wcd);
        }
コード例 #27
0
        protected WhereConditon ListWhereSql(ListCondition condition)
        {
            WhereConditon wcd = new WhereConditon();

            wcd.DBPara = new Dictionary <string, object>();
            EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);

            if (model == null)
            {
                return(wcd);
            }
            if (DataConvert.ToString(model.AssetsScrapNo) != "")
            {
                wcd.Sql += @" and AssetsScrap.assetsScrapNo  like '%'+@assetsScrapNo+'%'";
                wcd.DBPara.Add("assetsScrapNo", model.AssetsScrapNo);
            }
            if (DataConvert.ToString(model.AssetsScrapName) != "")
            {
                wcd.Sql += @" and AssetsScrap.assetsScrapName  like '%'+@assetsScrapName+'%'";
                wcd.DBPara.Add("assetsScrapName", model.AssetsScrapName);
            }
            if (DataConvert.ToString(model.AssetsNo) != "")
            {
                wcd.Sql += @" and Assets.assetsNo  like '%'+@assetsNo+'%'";
                wcd.DBPara.Add("assetsNo", model.AssetsNo);
            }
            if (DataConvert.ToString(model.AssetsName) != "")
            {
                wcd.Sql += @" and Assets.assetsName  like '%'+@assetsName+'%'";
                wcd.DBPara.Add("assetsName", model.AssetsName);
            }
            if (DataConvert.ToString(model.AssetsTypeId) != "")
            {
                wcd.Sql += @" and Assets.assetsTypeId  like '%'+@assetsTypeId+'%'";
                wcd.DBPara.Add("assetsTypeId", model.AssetsTypeId);
            }
            if (DataConvert.ToString(model.ScrapTypeId) != "")
            {
                wcd.Sql += @" and AssetsScrapDetail.scrapTypeId  like '%'+@scrapTypeId+'%'";
                wcd.DBPara.Add("scrapTypeId", model.ScrapTypeId);
            }
            if (DataConvert.ToString(model.AssetsClassId) != "")
            {
                wcd.Sql += @" and  Assets.assetsClassId  like '%'+@assetsClassId+'%'";
                wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
            }
            if (DataConvert.ToString(model.DepartmentId) != "")
            {
                wcd.Sql += @" and Assets.departmentId  like '%'+@departmentId+'%'";
                wcd.DBPara.Add("departmentId", model.DepartmentId);
            }
            return(wcd);
        }
コード例 #28
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
        {
            int    rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
            string sql     = string.Format(@"select top 500 LogDate,
                        UserId,
                        Logger,
                        Message
                from AppLog where 1=1 {0} order by LogDate desc ", ListWhereSql(condition).Sql,
                                           " order by   AssetsScrap.assetsScrapNo  ,Assets.assetsNo");
            DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];

            return(dtGrid);
        }
コード例 #29
0
 public override (IQueryable <Continent> entity, ExecutionState executionState, string message) List(
     QueryOptions <Continent> queryOptions = null,
     ListCondition listCondition           = ListCondition.Normal)
 {
     try
     {
         IQueryable <Continent> continent = ReadOnlyContext.Set <Continent>().Include(x => x.TableMappingDatas).ThenInclude(x => x.TableConfiguration).AsQueryable();
         return(entity : continent, executionState : (continent != null) ? ExecutionState.Success : ExecutionState.Failure, message : (continent != null) ? "Country found." : "Country not found.");
     }
     catch (Exception ex)
     {
         return(entity: null, executionState: ExecutionState.Failure, message : ex.Message);
     }
 }
コード例 #30
0
        public virtual DataTable GetReportGridDataTable(ListCondition condition)
        {
            int       rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
            string    sql     = string.Format(@"select MonthAssetsClassRecord.*,AssetsClass.assetsClassPath,AssetsClass.assetsClassName 
            from MonthAssetsClassRecord,AssetsClass 
            where  MonthAssetsClassRecord.assetsClassId=AssetsClass.assetsClassId {0}
            order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
            DataTable dt      = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
            string    sqlRpt  = string.Format(@"select distinct AssetsClass.assetsClassId, AssetsClass.assetsClassNo,
            AssetsClass.assetsClassName ,AssetsClass.assetsClassPath
            from MonthAssetsClassRecord,AssetsClass 
            where  MonthAssetsClassRecord.assetsClassId=AssetsClass.assetsClassId {0}
            order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
            DataTable dtRpt   = AppMember.DbHelper.GetDataSet(sqlRpt, ListWhereSql(condition).DBPara).Tables[0];
            string    sqlView = @"select * from MonthAssetsClassRecord where 1<>1 ";
            DataTable dtView  = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];

            foreach (DataRow dr in dtRpt.Rows)
            {
                DataRow drHeader = dtView.NewRow();
                drHeader["assetsClassId"]   = DataConvert.ToString(dr["assetsClassId"]);
                drHeader["assetsClassName"] = DataConvert.ToString(dr["assetsClassName"]);
                if (DataConvert.ToString(dr["assetsClassPath"]) == "" ||
                    DataConvert.ToString(dr["assetsClassPath"]) == DataConvert.ToString(dr["assetsClassId"]))
                {
                    drHeader["beginPeriodNum"]         = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["beginPeriodAmt"]         = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["currentPeriodAddNum"]    = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["currentPeriodAddAmt"]    = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["currentPeriodReduceNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["currentPeriodReduceAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["endPeriodNum"]           = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                    drHeader["endPeriodAmt"]           = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
                }
                else
                {
                    drHeader["beginPeriodNum"]         = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["beginPeriodAmt"]         = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["currentPeriodAddNum"]    = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["currentPeriodAddAmt"]    = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["currentPeriodReduceNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["currentPeriodReduceAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["endPeriodNum"]           = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                    drHeader["endPeriodAmt"]           = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
                }
                dtView.Rows.Add(drHeader);
            }
            return(dtView);
        }
コード例 #31
0
        public StringCondition(StringQueryField Field, ListCondition Condition, IEnumerable<string> Values)
        {
            if (Values == null)
            {
                queryPart = "";
                return;
            }

            string values = string.Join(", ", Values.Select(val => $"'{val.Replace("'", "''")}'"));

            if (string.IsNullOrWhiteSpace(values))
            {
                queryPart = "";
            }
            else
            {
                queryPart = $"{Field.GetQueryPart()} {(Condition == ListCondition.In ? "in" : "not in")} ({values})";
            }
        }