Esempio n. 1
0
        public override void scheduleMain(ThreadStart doEvent)
        {
            #region [SessionTable逾期2日的資料刪除]
            {
                var str     = Db_PrimaryKey;
                var stables = FdmService.Instance.AllTables.Where(x => x.Value.IsSessionEnable).Select(x => x.Value.SessionTable).ToArray();
                //小於此日者刪除之。
                var the_date = DateTime.Today.AddDays(-2).ToString("yyyyMMdd.HHmmss", CultureInfo.InvariantCulture);
                foreach (var stable in stables)
                {
                    var qry = new NsDbQuery();
                    qry.setSelect(s =>
                    {
                        var t1  = s.from(stable.TableName);
                        s.Where = t1[stable.NsSessionId.ColumnName] < the_date;
                        s.select(t1.All);
                    });

                    var dt = qry.queryData();
                    foreach (DataRow row in dt.Rows)
                    {
                        row.Delete();
                    }
                    dt.TableName = stable.TableName;

                    dt.ns_update_import_mode();

                    doEvent();
                }
            }
            #endregion
        }
Esempio n. 2
0
        /// <summary>
        /// 取得日期內的所有機台ID的資料
        /// </summary>
        /// <param name="iDate">日期(哪一天)</param>
        /// <param name="iMCIDList">要查的機台ID</param>
        /// <returns>機台該日期所有資料</returns>
        public DataTable AlAssmblingDetail_getDayTotalByMCID(string iDate, List <string> iMCIDList)
        {
            DateTime dteTmp   = DateTime.Today;
            DateTime dteDateS = DateTime.MinValue;
            DateTime dteDateE = DateTime.Today;

            HryDataService.Instance.getOneDayForAssemblingDetail(iDate, ref dteDateS, ref dteDateE);

            var qry = new NsDbQuery();

            qry.setSelect(s =>
            {
                var t1 = s.from <AL_Assmbling>();
                var t2 = s.leftJoin <AL_AssmblingDetail>().on(t => t.ALAD_MCID == t1.ALA_MCID);
                s.select(t1.ALA_MCID, t1.ALA_MCCode, t1.ALA_MCName, t2.ALAD_DATE, t2.ALAD_ITEM, t2.ALAD_QTY);
                s.Where = t1.ALA_MCID.@in(iMCIDList)
                          & t2.ALAD_DATE >= dteDateS
                          & t2.ALAD_DATE <= dteDateE;
                s.groupBy(t1.ALA_MCID, t1.ALA_MCCode, t1.ALA_MCName, t2.ALAD_DATE, t2.ALAD_ITEM, t2.ALAD_QTY);
                s.orderBy(new[] { t1.ALA_MCID.Asc, t2.ALAD_DATE.Asc });
            });
            var dt = qry.queryData();

            return(dt);
        }
Esempio n. 3
0
        private void link_ZZO_OrderAmount_XX(ZZ_OrderDataTable dt)
        {
            var pks = dt.getPrimaryKeys();

            var qrydb = new NsDbQuery();

            qrydb.setSelect(s =>
            {
                var t1 = s.from <ZZ_OrderDetail>();

                s.select(
                    t1.ZZOD_OrderId,
                    (t1.ZZOD_Qty.isnull(0) * t1.ZZOD_UnitPrice.isnull(0)).sum().As("Amt")
                    );

                s.Where = t1.ZZOD_OrderId.batchin(pks.toConstReq1());

                s.groupBy(s.Selects[0]);
            });
            var dt2 = qrydb.queryData();

            dt2.Constraints.Add("PK", dt2.Columns[0], true);

            FtdDataHelper.linkTable(dt, dt2, new FtdDataHelper.LinkInfo(AppDataName.ZZO_OrderId, AppDataName.ZZO_OrderAmount_XX, "Amt", 0));
        }
Esempio n. 4
0
        protected override void onSchemaLoaded()
        {
            addTypedCalcHandler()
            .setColumns(AppDataName.EOET_InUse_XX, AppDataName.EOET_InUseName_XX)
            .setReferences(AppDataName.EOET_EmployeeTitleId)
            .setHandler(dt => {
                link_EOET_InUse_XX(dt);
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOET_UserCount_XX)
            .setHandler(dt => {
                var keys = dt.getPrimaryKeys();
                var qry  = new NsDbQuery();
                qry.setSelect(s => {
                    var t1  = s.from <EO_Employee>();
                    s.Where = t1.EOE_EmployeeTitleId.batchin(keys.toConstReq1());
                    s.groupBy(t1.EOE_EmployeeTitleId);
                    s.select(t1.EOE_EmployeeTitleId, NSQL.count().As("TCount"));
                });
                var dt1 = qry.queryData();
                dt1.Constraints.Add("pk", dt1.Columns[0], true);
                foreach (var row in dt)
                {
                    row.EOET_UserCount_XX = 0;
                    var row1 = dt1.Rows.Find(row.EOET_EmployeeTitleId);
                    if (row1 != null)
                    {
                        row.EOET_UserCount_XX = row1.getInt("TCount");
                    }
                }
            });

            base.onSchemaLoaded();
        }
Esempio n. 5
0
        public DataTable AlAssmblingDetail_getDayList(AlAssmblingDetailQryModel qm)
        {
            DateTime dteTmp   = DateTime.Today;
            DateTime dteDateS = DateTime.MinValue;
            DateTime dteDateE = DateTime.Today;

            HryDataService.Instance.getOneDayForAssemblingDetail(qm.Q_Date, ref dteDateS, ref dteDateE);

            var qry = new NsDbQuery();

            qry.setSelect(s =>
            {
                var t1 = s.from <AL_Assmbling>();
                var t2 = s.leftJoin <AL_AssmblingDetail>().on(t => t.ALAD_MCID == t1.ALA_MCID);
                s.select(t1.ALA_MCID, t1.ALA_MCCode, t1.ALA_MCName, t2.ALAD_DATE, t2.ALAD_ITEM, t2.ALAD_QTY);
                s.Where = t1.ALA_MCCode.contains(qm.Q_MCCode.toConstOpt1())
                          & t2.ALAD_DATE >= dteDateS
                          & t2.ALAD_DATE <= dteDateE;
                s.groupBy(t1.ALA_MCID, t1.ALA_MCCode, t1.ALA_MCName, t2.ALAD_DATE, t2.ALAD_ITEM, t2.ALAD_QTY);
                s.orderBy(new[] { t1.ALA_MCName.Asc, t2.ALAD_DATE.Asc });
            });

            var dt = qry.queryData();

            return(dt);
        }
Esempio n. 6
0
        private void link_EOD_DepartmentFullName_XX(EO_DepartmentDataTable dt)
        {
            var qry = new NsDbQuery();
            var col = dt.Columns.Add("LevelId_XX", typeof(string));

            //初始資料
            foreach (var row in dt)
            {
                row[col] = row.EOD_ParentId;
                row.EOD_DepartmentFullName_XX = row.EOD_DepartmentName;
            }

            //逐階計算
            while (true)
            {
                var levelpks = FtdDataHelper.getDistinctArray <string>(dt, col);
                qry.setSelect(s => {
                    var t1 = s.from <EO_Department>();
                    s.select(t1.EOD_DepartmentId, t1.EOD_DepartmentName, t1.EOD_ParentId);
                    s.Where = t1.EOD_DepartmentId.batchin(levelpks.toConstReq1());
                });
                var dt2 = qry.queryData <EO_DepartmentDataTable>();
                if (dt2.Count == 0)
                {
                    break;
                }
                FtdDataHelper.linkTable(
                    dt.Rows
                    , dt2
                    , col
                    , (row, row2, isLink) =>
                {
                    if (isLink)
                    {
                        row[AppDataName.EOD_DepartmentFullName_XX] = string.Concat(row2[AppDataName.EOD_DepartmentName], @"\", row[AppDataName.EOD_DepartmentFullName_XX]);
                        row[col] = row2[AppDataName.EOD_ParentId];
                    }
                    else
                    {
                        row[col] = "";
                    }
                }
                    );
                dt2.Clear();
            }

            dt.Columns.Remove(col);
        }
Esempio n. 7
0
        private void link_EOD_EmployeeCount_XX(EO_DepartmentDataTable dt)
        {
            var pks = dt.getPrimaryKeys();

            var qrydb = new NsDbQuery();

            qrydb.setSelect(s => {
                var t1 = s.from <EO_DeptMember>();
                s.select(t1.EODM_DeptId, NSQL.count().As("EmpCount"));
                s.Where = t1.EODM_DeptId.batchin(pks.toConstReq1());
                s.groupBy(s.Selects[0]);
            });

            var dt2 = qrydb.queryData();

            dt2.Constraints.Add("PK", dt2.Columns[0], true);

            FtdDataHelper.linkTable(dt, dt2, new FtdDataHelper.LinkInfo(AppDataName.EOD_DepartmentId, AppDataName.EOD_EmployeeCount_XX, "EmpCount", 0));
        }
Esempio n. 8
0
        private void link_CRCL_RegisterQty_XX(CR_ClassDataTable dt)
        {
            var pks = dt.getPrimaryKeys();

            var qrydb = new NsDbQuery();

            qrydb.setSelect(s =>
            {
                var t1 = s.from <CR_Registration>();
                s.select(t1.CRR_ClassId, NSQL.count().As("UserCount"));
                s.Where = t1.CRR_ClassId.batchin(pks.toConstReq1());
                s.groupBy(s.Selects[0]);
            });

            var dt2 = qrydb.queryData();

            dt2.Constraints.Add("PK", dt2.Columns[0], true);

            FtdDataHelper.linkTable(dt, dt2, new FtdDataHelper.LinkInfo(AppDataName.CRCL_ClassId, AppDataName.CRCL_RegisterQty_XX, "UserCount", 0));
        }
Esempio n. 9
0
        /// <summary>
        /// 取得給使用者的權限
        /// </summary>
        public EO_PermissionDataTable queryPermissionForUsers(string[] userIds, string[] extendFieldNames)
        {
            var       qrydb = new NsDbQuery();
            DataTable dt1   = null;
            {
                //人員所擁有的權限
                qrydb.setSelect(s =>
                {
                    var t1 = s.from <EO_PermissionSetting>();
                    var t2 = s.join <EO_Permission>()
                             .on(t => t.EOP_PermissionId == t1.EOPS_PermissionId);
                    s.select(t2.EOP_PermissionId);
                    s.Where = t1.EOPS_PermissionUserId.batchin(userIds.toConstReq1())
                              & t2.EOP_IsObjectNeed == "N"
                              & t2.EOP_IsEveryOneAllow == "N";
                });
                dt1 = qrydb.queryData();
            }

            {
                //EveryOne都可以有的權限
                qrydb.setSelect(s =>
                {
                    var t1 = s.from <EO_Permission>();
                    s.select(t1.EOP_PermissionId);
                    s.Where = t1.EOP_IsObjectNeed == "N"
                              & t1.EOP_IsEveryOneAllow == "Y";
                });
                qrydb.fillData(dt1);
            }

            {
                var pks = FtdDataHelper.getDistinctArray <string>(dt1, dt1.Columns[0]);
                var qry = new NsDmQuery();
                var t1  = qry.from <EO_Permission>();
                qry.selectAll(extendFieldNames);
                qry.Where = t1.EOP_PermissionId.batchin(pks.toConstReq1());
                var dt = (EO_PermissionDataTable)qry.queryData();
                return(dt);
            }
        }
Esempio n. 10
0
        /// <summary>
        /// 移除無人的群組清單
        /// </summary>
        public static void step3()
        {
            var qry = new NsDbQuery();

            qry.setSelect(s =>
            {
                var t1 = s.from <EO_DeptMember>();
                var t2 = s.leftJoin <EO_Employee>()
                         .on(t => t1.EODM_MemberId == t.EOE_EmployeeId);
                var t3 = s.leftJoin <EO_Department>()
                         .on(t => t1.EODM_DeptId == t.EOD_DepartmentId);
                s.Where = t2.EOE_EmployeeId == null | t3.EOD_DepartmentId == null;
                s.select(t1.AllPhysical);
            });
            var dt = qry.queryData <EO_DeptMemberDataTable>();

            foreach (DataRow row in dt.Rows)
            {
                row.Delete();
            }
            dt.ns_update();
        }
Esempio n. 11
0
        private void link_EOET_InUse_XX(EO_EmployeeTitleDataTable dt)
        {
            var pks = dt.getPrimaryKeys();

            var qrydb = new NsDbQuery();

            qrydb.setSelect(s => {
                var t1 = s.from <EO_Employee>();
                s.select(t1.EOE_EmployeeTitleId);
                s.Where = t1.EOE_EmployeeTitleId.batchin(pks.toConstReq1());
                s.groupBy(s.Selects[0]);
            });
            var dt2 = qrydb.queryData();

            dt2.Constraints.Add("PK", dt2.Columns[0], true);

            foreach (var row in dt)
            {
                var isUse = (dt2.Rows.Find(row.EOET_EmployeeTitleId) != null);
                row.EOET_InUse_XX     = isUse ? "T" : "F";
                row.EOET_InUseName_XX = isUse ? "使用中" : "未使用";
            }
        }
Esempio n. 12
0
        public static void testAll()
        {
            var system_name   = "EO";
            var long_col_name = "A".PadRight(300, '0');

            //#region [1]
            //{

            //    var dts_1 = new DataTable();
            //    dts_1.Columns.Add("ID", typeof(string));
            //    dts_1.Columns.Add(long_col_name, typeof(string));
            //    dts_1.Columns.Add("Money", typeof(int));
            //    dts_1.Columns.Add("EntryDate", typeof(DateTime));
            //    dts_1.Columns.Add("Percent", typeof(Decimal));
            //    dts_1.Rows.Add("ID001", @"Jack''s Name", null, "2009/10/1 12:30:40", 10.21m);
            //    dts_1.Rows.Add("ID002", "Mary\"'or''='", 200, null, 22.45m);
            //    dts_1.Rows.Add("\"or\"=\" -- select node", "David--;&12345;", 300, "2009/10/3 12:30:40", null);
            //    dts_1.Rows.Add("ID004", null, 300, "2009/10/4 12:30:40", 19234.8m);

            //    Console.WriteLine("Test 1");
            //    var qry = new NsDbQuery();
            //    qry.setSelect(s =>
            //    {
            //        var t = s.fromSelect(get_datatable_sql(system_name, dts_1))
            //            .typed(t => new { ID = t["ID"], sName = t[long_col_name], iMoney = t["Money"], dEntryDate = t["EntryDate"], mPercent = t["Percent"] });
            //        s.select(
            //            t.Cols.ID
            //            , t.Cols.sName
            //            , t.Cols.sName.As("C1")
            //            , t.Cols.dEntryDate
            //            , t.Cols.mPercent
            //            , t.Cols.iMoney
            //            , t.Cols.sName.substr(1, 2).As("C2")
            //            , t.Cols.sName.substr(1, 100).As("C3")
            //            , t.Cols.sName.left(2).As("C4")
            //            , t.Cols.sName.left(100).As("C5")
            //            , t.Cols.sName.isnull("Ken").As("C6")
            //            , t.Cols.sName.right(1).As("C7")
            //            , t.Cols.sName.right(100).As("C8")
            //            , t.Cols.sName.upper().As("C9")
            //            , t.Cols.sName.lower().As("C10")
            //            , t.Cols.iMoney.tostring().As("C12")
            //            , t.Cols.iMoney.todecimal(1).As("C13")
            //            , t.Cols.iMoney.isnull(100).As("C14")
            //        );
            //    });
            //    var BatchDataTable = qry.queryData();

            //    Console.WriteLine("Test 2");
            //    qry.setSelect(s =>
            //    {
            //        var t = s.fromSelect(get_datatable_sql(system_name, dts_1))
            //            .typed(t => new { ID = t["ID"], sName = t[long_col_name], iMoney = t["Money"], dEntryDate = t["EntryDate"], mPercent = t["Percent"] });
            //        s.select(
            //            t.Cols.mPercent.tostring().As("C1")
            //            , t.Cols.mPercent.tointeger().As("C2")
            //            , t.Cols.mPercent.round(0).As("C3")
            //            , t.Cols.mPercent.round(1).As("C4")
            //            , t.Cols.mPercent.round(3).As("C5")
            //            , t.Cols.mPercent.isnull(10.5m).As("C6")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_EDate).As("C7")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_EDateTime).As("C71")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_EDateTime16).As("C72")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_MSSQL_120).As("C73")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_YYYYMMDD).As("C74")
            //            , t.Cols.dEntryDate.tostring(NsDataFormat.DATE_YYYYMMDDHHMMSS).As("C75")
            //            , t.Cols.dEntryDate.tostring().As("C76")
            //        );
            //    });
            //    BatchDataTable = qry.queryData();
            //}
            //#endregion

            #region [DateTime Test]
            {
                Console.WriteLine("DateTime Test");
                var dts = new DataTable();
                dts.Columns.Add("Date1", typeof(string));
                dts.Columns.Add("Date2", typeof(string));
                dts.Columns.Add("Date3", typeof(string));
                dts.Columns.Add("Date4", typeof(string));
                dts.Rows.Add("20080911", "235959", "2009-12-30 23:59:59", "2009/12/30 23:59:59");
                dts.Rows.Add(null, null, null, null);

                var qry = new NsDbQuery();
                qry.setSelect(s =>
                {
                    var t1 = s.fromSelect(get_datatable_sql(system_name, dts))
                             .typed(t => new { Date1 = t["Date1"], Date2 = t["Date2"], Date3 = t["Date3"], Date4 = t["Date4"] });
                    s.select(
                        t1.Cols.Date1.todatetime(NsDataFormat.DATE_YYYYMMDD).As("C1")
                        , (t1.Cols.Date1 + t1.Cols.Date2).todatetime(NsDataFormat.DATE_YYYYMMDDHHMMSS).As("C2")
                        , t1.Cols.Date3.todatetime(NsDataFormat.DATE_MSSQL_120).As("C3")
                        , t1.Cols.Date4.todatetime(NsDataFormat.DATE_EDateTime).As("C4")
                        , t1.Cols.Date4.left(10).todatetime(NsDataFormat.DATE_EDate).As("C5")
                        , t1.Cols.Date4.left(16).todatetime(NsDataFormat.DATE_EDateTime16).As("C7")
                        );
                });
                var dt = qry.queryData();
            }
            #endregion

            #region [JoinTest]
            {
                Console.WriteLine("JoinTest");
                var dts1 = new DataTable();
                dts1.Columns.Add("ID", typeof(string));
                dts1.Rows.Add("A");
                dts1.Rows.Add("B");
                dts1.Rows.Add("C");
                dts1.Rows.Add("D");

                var dts2 = new DataTable();
                dts2.Columns.Add("ID", typeof(string));
                dts2.Rows.Add("A1");
                dts2.Rows.Add("B");
                dts2.Rows.Add("C1");
                dts2.Rows.Add("D");

                var qry = new NsDbQuery();
                qry.setSelect(s =>
                {
                    var t1 = s.fromSelect(get_datatable_sql(system_name, dts1))
                             .typed(t => new { ID = t["ID"] });
                    var t2 = s.joinSelect(get_datatable_sql(system_name, dts2))
                             .on(t => t1.Cols.ID == t["ID"])
                             .typed(t => new { ID = t["ID"] });
                    s.select(
                        t1.Cols.ID.isnull("#").As("ID1")
                        , t2.Cols.ID.isnull("#").As("ID2")
                        );
                    s.orderBy(s.Selects[0].Asc, s.Selects[1].Asc);
                });
                var dt = qry.queryData();

                qry.setSelect(s =>
                {
                    var t1 = s.fromSelect(get_datatable_sql(system_name, dts1))
                             .typed(t => new { ID = t["ID"] });
                    var t2 = s.leftJoinSelect(get_datatable_sql(system_name, dts2))
                             .on(t => t1.Cols.ID == t["ID"])
                             .typed(t => new { ID = t["ID"] });
                    s.select(
                        t1.Cols.ID.isnull("#").As("ID1")
                        , t2.Cols.ID.isnull("#").As("ID2")
                        );
                    s.orderBy(s.Selects[0].Asc, s.Selects[1].Asc);
                });
                var dt2 = qry.queryData();

                qry.setSelect(s =>
                {
                    var t1 = s.fromSelect(get_datatable_sql(system_name, dts1))
                             .typed(t => new { ID = t["ID"] });
                    var t2 = s.rightJoinSelect(get_datatable_sql(system_name, dts2))
                             .on(t => t1.Cols.ID == t["ID"])
                             .typed(t => new { ID = t["ID"] });
                    s.select(
                        t1.Cols.ID.isnull("#").As("ID1")
                        , t2.Cols.ID.isnull("#").As("ID2")
                        );
                    s.orderBy(s.Selects[0].Asc, s.Selects[1].Asc);
                });
                var dt3 = qry.queryData();

                qry.setSelect(s =>
                {
                    var t1 = s.fromSelect(get_datatable_sql(system_name, dts1))
                             .typed(t => new { ID = t["ID"] });
                    var t2 = s.fullJoinSelect(get_datatable_sql(system_name, dts2))
                             .on(t => t1.Cols.ID == t["ID"])
                             .typed(t => new { ID = t["ID"] });
                    s.select(
                        t1.Cols.ID.isnull("#").As("ID1")
                        , t2.Cols.ID.isnull("#").As("ID2")
                        );
                    s.orderBy(s.Selects[0].Asc, s.Selects[1].Asc);
                });
                var dt4 = qry.queryData();
            }
            #endregion
        }
Esempio n. 13
0
        protected override void onSchemaLoaded()
        {
            addTypedCalcHandler()
            .setColumns(AppDataName.EOMS_NodeType_XX)
            .setReferences(AppDataName.EOMS_ParentId)
            .setHandler(dt => {
                foreach (var row in dt)
                {
                    if (row.EOMS_ParentId.isNullOrEmpty())
                    {
                        row.EOMS_NodeType_XX = "A";
                    }
                }
                var rows2 = dt.Where(x => x.EOMS_NodeType_XX.isNullOrEmpty()).Select(x => x).ToArray();
                var pks   = rows2.Select(x => x.EOMS_NodeId).ToArray();
                var qry   = new NsDbQuery();
                qry.setSelect(s => {
                    var t1 = s.from <EO_MenuStruct>();
                    s.select(t1.EOMS_ParentId);
                    s.Where = t1.EOMS_ParentId.batchin(pks.toConstReq1());
                    s.groupBy(t1.EOMS_ParentId);
                });

                //有子的節點
                var pks2 = qry.queryKeys <string>().ToDictionary(x => x);

                foreach (var row2 in rows2)
                {
                    row2.EOMS_NodeType_XX = pks2.ContainsKey(row2.EOMS_NodeId) ? "B" : "C";
                }
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOMS_MatchSiteId_XX)
            .setReferences(AppDataName.EOMS_ClickMode, AppDataName.EOMS_Url)
            .setHandler(dt => {
                //var qry = new NsDbQuery();
                //qry.setSelect(s => {
                //    var t1 = s.from<EO_SignSite>();
                //    s.select(t1.EOSS_SiteId, t1.EOSS_FilterUrl1, t1.EOSS_FilterUrl2);
                //    s.Where = t1.EOSS_FilterUrl1 != "" | t1.EOSS_FilterUrl2 != "";
                //});
                //var dt1 = qry.queryData<EO_SignSiteDataTable>();

                //foreach (var row in dt)
                //{
                //    if (row.EOMS_ClickMode != "U" || row.EOMS_Url.isNullOrEmpty())
                //        continue;

                //    foreach (var row1 in dt1)
                //    {
                //        if (!row1.EOSS_FilterUrl1.isNullOrEmpty())
                //        {
                //            if (row.EOMS_Url.StartsWith(row1.EOSS_FilterUrl1, true, CultureInfo.InvariantCulture))
                //            {
                //                row.EOMS_MatchSiteId_XX = row1.EOSS_SiteId;
                //                break;
                //            }
                //        }

                //        if (!row1.EOSS_FilterUrl2.isNullOrEmpty())
                //        {
                //            if (row.EOMS_Url.StartsWith(row1.EOSS_FilterUrl2, true, CultureInfo.InvariantCulture))
                //            {
                //                row.EOMS_MatchSiteId_XX = row1.EOSS_SiteId;
                //                break;
                //            }
                //        }
                //    }
                //}
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOMS_ChildCount_XX
                        , AppDataName.EOMS_BrotherCount_XX
                        , AppDataName.EOMS_RootId_XX
                        , AppDataName.EOMS_LevelNo_XX
                        , AppDataName.EOMS_TreeLeftNo_XX
                        , AppDataName.EOMS_TreeRightNo_XX
                        )
            .setReferences(AppDataName.EOMS_ParentId)
            .setHandler(dt => {
                var qry = new NsDbQuery();
                //調出全部的結構
                qry.setSelect(s => {
                    var t1 = s.from <EO_MenuStruct>();
                    s.select(t1.EOMS_NodeId, t1.EOMS_ParentId, t1.EOMS_SortNo);
                    //s.Where = t1.EOMS_Viewable == "Y";
                });
                var dt1 = qry.queryData <EO_MenuStructDataTable>();

                //計算樹結構
                FtdDataHelper.TableTree.calcTreeNo(dt1, "X_TreeNo1", "X_TreeNo2", "X_Level",
                                                   dt11 => dt11.Where(x => x.EOMS_ParentId.isNullOrEmpty()).Select(x => x)
                                                   , (dt11, row11) =>
                {
                    var pk = ((EO_MenuStructRow)row11).EOMS_NodeId;
                    return(dt11.Where(x => x.EOMS_ParentId == pk));
                }
                                                   );

                var keys = dt1
                           .GroupBy(x => x.EOMS_ParentId.nullOrEmptyAs("@"))
                           .Select(x => new { ID = x.Key, ChildCount = x.Count() })
                           .ToDictionary(x => x.ID);

                var roots = dt1.Where(x => x.EOMS_ParentId.isNullOrEmpty()).Select(x => x).ToArray();

                //計算結點的資訊
                foreach (var row in dt)
                {
                    //if (!row.EOMS_Viewable.equalIgnoreCase("Y"))
                    //{
                    //    //row.EOMS_RootId_XX = root.EOMS_NodeId;
                    //    //row.EOMS_LevelNo_XX = level;
                    //    //row.EOMS_TreeLeftNo_XX = no1;
                    //    //row.EOMS_TreeRightNo_XX = no2;
                    //    continue;
                    //}

                    var key = keys.findKey(row.EOMS_NodeId);
                    //1
                    if (row.EOMS_ParentId.isNullOrEmpty())
                    {
                        row.EOMS_NodeType_XX = "A";
                    }
                    else
                    {
                        row.EOMS_NodeType_XX = key != null ? "B" : "C";
                    }

                    //2
                    var pkey = keys.findKey(row.EOMS_ParentId.nullOrEmptyAs("@"));
                    row.EOMS_BrotherCount_XX = (pkey == null) ? 0 : pkey.ChildCount;

                    //3
                    if (key != null)
                    {
                        row.EOMS_ChildCount_XX = key.ChildCount;
                    }
                    else
                    {
                        row.EOMS_ChildCount_XX = 0;
                    }

                    //4
                    var row1                = dt1.findByPrimaryKey(row.EOMS_NodeId);
                    var no1                 = row1.getInt("X_TreeNo1").Value;
                    var no2                 = row1.getInt("X_TreeNo2").Value;
                    var level               = row1.getInt("X_Level").Value;
                    var root                = roots.Where(x => no1 >= x.getInt("X_TreeNo1").Value&& no2 <= x.getInt("X_TreeNo2").Value).First();
                    row.EOMS_RootId_XX      = root.EOMS_NodeId;
                    row.EOMS_LevelNo_XX     = level;
                    row.EOMS_TreeLeftNo_XX  = no1;
                    row.EOMS_TreeRightNo_XX = no2;
                }
            });

            base.onSchemaLoaded();
        }
Esempio n. 14
0
        protected override void onSchemaLoaded()
        {
            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_ChildCount_XX, AppDataName.EOD_BrotherCount_XX)
            .setReferences(AppDataName.EOD_DepartmentId, AppDataName.EOD_ParentId)
            .setHandler(dt =>
            {
                var pks = dt.Select(x => x.EOD_DepartmentId).Union(dt.Select(x => x.EOD_ParentId)).Distinct().ToArray();
                var qry = new NsDbQuery();
                qry.setSelect(s =>
                {
                    var t1  = s.from <EO_Department>();
                    s.Where = t1.EOD_ParentId.batchin(pks.toConstReq1());
                    s.select(t1.EOD_ParentId.isnull("@").As("ID"), NSQL.count().As("TheCount"));
                    s.groupBy(t1.EOD_ParentId);
                });
                var dt1  = qry.queryData();
                var keys = dt1.Rows.Cast <DataRow>().Select(x => new { ID = x.getString("ID"), TheCount = x.getInt("TheCount").Value }).ToDictionary(x => x.ID);

                foreach (var row in dt)
                {
                    //ChildCount
                    {
                        var row1 = keys.findKey(row.EOD_DepartmentId);
                        if (row1 != null)
                        {
                            row.EOD_ChildCount_XX = row1.TheCount;
                        }
                        else
                        {
                            row.EOD_ChildCount_XX = 0;
                        }
                    }
                    //BrotherCount
                    {
                        var row1 = keys.findKey(row.EOD_ParentId.nullOrEmptyAs("@"));
                        if (row1 != null)
                        {
                            row.EOD_BrotherCount_XX = row1.TheCount;
                        }
                        else
                        {
                            row.EOD_BrotherCount_XX = 0;
                        }
                    }
                }
            });

            addTypedSqlHandler()
            .setColumns(AppDataName.EOD_DepartmentTypeName_XX)
            .setHandler(
                t1 => t1.EOD_DepartmentType.decode("A", "部門", "B", "群組", t1.EOD_DepartmentType)
                );

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_EmployeeCount_XX)
            .setHandler(dt => {
                link_EOD_EmployeeCount_XX(dt);
            });

            addTypedSqlHandler()
            .setColumns(AppDataName.EOD_DepartmentObjectName_XX)
            .setHandler(
                t1 => (t1.PrimaryKey == null).istrue("", "[" + t1.EOD_DepartmentTypeName_XX + "]" + t1.EOD_DepartmentName)
                );

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_CU_IsVirtualVisible_XX)
            .setReferences(AppDataName.EOD_DepartmentType, AppDataName.EOD_VirtualType)
            .setHandler(dt => {
                link_EOD_CU_IsVirtualVisible_XX(dt);
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_DepartmentFullName_XX)
            .setReferences(AppDataName.EOD_ParentId, AppDataName.EOD_DepartmentName)
            .setHandler(dt => {
                link_EOD_DepartmentFullName_XX(dt);
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_DepartmentFullNameII_XX)
            .setReferences(AppDataName.EOD_DepartmentName, AppDataName.EOD_DepartmentFullName_XX)
            .setHandler(dt => {
                foreach (var row in dt)
                {
                    row.EOD_DepartmentFullNameII_XX = row.EOD_DepartmentCode.isNullOrEmpty() ? row.EOD_DepartmentFullName_XX : row.EOD_DepartmentCode + "-" + row.EOD_DepartmentFullName_XX;
                }
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_IsDeleteable_XX)
            .setReferences(AppDataName.EOD_DepartmentType, AppDataName.EOD_ChildCount_XX, AppDataName.EOD_EmployeeCount_XX)
            .setHandler(dt => {
                foreach (var row in dt)
                {
                    if (row.EOD_DepartmentType == "A")
                    {
                        row.EOD_IsDeleteable_XX = (row.EOD_ChildCount_XX == 0 && row.EOD_EmployeeCount_XX == 0) ? "T" : "F";
                    }
                    if (row.EOD_DepartmentType == "B")
                    {
                        row.EOD_IsDeleteable_XX = (row.EOD_ChildCount_XX == 0) ? "T" : "F";
                    }
                }
            });

            addTypedCalcHandler()
            .setColumns(AppDataName.EOD_ScopeLevelNo_XX, AppDataName.EOD_ScopeTreeLeftNo_XX, AppDataName.EOD_ScopeTreeRightNo_XX)
            .setReferences(AppDataName.EOD_ParentId)
            .setHandler(dt => {
                //向上展開樹
                var dta = FtdDataHelper.TableTree.getTreeUpExpand(
                    dt,
                    dt1 =>
                {
                    //Find All Parent
                    var pks = dt1.Select(x => x.EOD_ParentId).Distinct().ToArray();
                    var dt2 = NsDmHelper.EO_Department.wherepks(pks).query();
                    return(dt2);
                }
                    , (dt1, dt2) =>
                {
                    var rows = dt1.Where(x => dt2.findByPrimaryKey(x.EOD_DepartmentId) != null).ToArray();
                    rows.forEach(x => dt1.Rows.Remove(x));
                }
                    );

                //樹系代號計算
                FtdDataHelper.TableTree.calcTreeNo(dta, "X_TreeNo1", "X_TreeNo2", "X_Level"
                                                   , dt1 => dt1.Where(x => x.EOD_ParentId.isNullOrEmpty()).OrderBy(x => x.EOD_SortNo)
                                                   , (dt1, row1) =>
                {
                    var pk = ((EO_DepartmentRow)row1).EOD_DepartmentId;
                    return(dt1.Where(x => x.EOD_ParentId == pk).OrderBy(x => x.EOD_SortNo));
                }
                                                   );

                foreach (var row in dt)
                {
                    var rowa = dta.findByPrimaryKey(row.EOD_DepartmentId);
                    if (rowa == null)
                    {
                        row.EOD_ScopeLevelNo_XX     = null;
                        row.EOD_ScopeTreeLeftNo_XX  = null;
                        row.EOD_ScopeTreeRightNo_XX = null;
                    }
                    else
                    {
                        row.EOD_ScopeLevelNo_XX     = rowa.getInt("X_Level").Value;
                        row.EOD_ScopeTreeLeftNo_XX  = rowa.getInt("X_TreeNo1").Value;
                        row.EOD_ScopeTreeRightNo_XX = rowa.getInt("X_TreeNo2").Value;
                    }
                }
            });

            base.onSchemaLoaded();
        }
Esempio n. 15
0
        /// <summary>
        /// 查詢指定的使用者可以使用的權限清單
        /// </summary>
        //private  Dictionary<string, Dictionary<string, string>> queryUserFunCmds(string usrId, string menuId)
        public Dictionary <string, Dictionary <string, string> > queryUserFunPermSet(string usrId, string menuId)
        {
            var targetIds = new List <string>();

            #region //Find Use's All Target
            {
                var usrperm   = FtdPermissionService.Instance.getActorPermission(usrId);
                var permCodes = usrperm.PermissionNames;
                var permIds   = NsDmHelper.EO_Permission
                                .where (t => t.EOP_PermissionCode.batchin(permCodes.toConstReq1()))
                                .query()
                                .Select(x => x.EOP_PermissionId)
                                .ToArray();

                //權限Id
                targetIds.AddRange(permIds);

                var rowe = NsDmHelper.EO_Employee.wherepk(usrId).queryFirst();
                if (rowe != null)
                {
                    //人員Id
                    targetIds.Add(rowe.EOE_EmployeeId);
                    //職稱Id
                    if (!rowe.EOE_EmployeeTitleId.isNullOrEmpty())
                    {
                        targetIds.Add(rowe.EOE_EmployeeTitleId);
                    }
                    //部門Id
                    if (!rowe.EOE_DepartmentId.isNullOrEmpty())
                    {
                        targetIds.Add(rowe.EOE_DepartmentId);
                    }
                    //群組Id
                    var dt2 = NsDmHelper.EO_DeptMember.where (t => t.EODM_MemberId == usrId.toConstReq1()).query();
                    dt2.forEach(x => targetIds.Add(x.EODM_DeptId));
                }
            }
            #endregion

            //Find Target's All Viewable Function
            var qry = new NsDbQuery();
            qry.setSelect(s =>
            {
                var t1 = s.from <EO_MenuPerm>();
                var t2 = s.join <EO_MenuPermSet>()
                         .on(t => t1.EOMP_MenuPermId == t.EOMPS_MenuPermId);

                s.Where = t1.EOMP_MenuId == menuId.toConstReq1()
                          & t1.EOMP_TargetId.batchin(targetIds.toConstReq1());

                s.select(
                    t2.EOMPS_MenuPermSetId.As("MenuPermSetId"),
                    t1.EOMP_ViewKind.decode("A", 0, "B", 1).sum().As("Viewable") //Value == 0 代表可檢視
                    );

                s.groupBy(
                    s.Selects[0]
                    );

                s.Having = s.Selects[1] == 0;//Value == 0 代表可檢視
            });
            var dt             = qry.queryData();
            var menuPermSetIds = dt.Rows.Cast <DataRow>().Select(x => x.getString("MenuPermSetId")).ToList();

            var dtFunPermSet = NsDmHelper.EO_FunPermSet
                               .selectAll(t => t.AllExt)
                               .where (t => t.EOFPS_MenuPermSetId.batchin(menuPermSetIds.toConstReq1()))
                               .orderby(t => new[] {
                t.EOFPS_MenuItemNo_XX.Asc,
                t.EOFPS_FunctionSeqNo_XX.Asc
            })
                               .query();

            //var cmds = dtFunPermSet.GroupBy(x =>
            //        x.EOFPS_MenuItemNo_XX,
            //        y => new
            //        {
            //            Code = y.EOFPS_FunctionCode_XX,
            //            Name = y.EOFPS_FunctionName_XX
            //        }
            //    )
            //    .ToDictionary(x =>
            //        x.Key,
            //        y => y.ToDictionary(a => a.Code, b => b.Name)
            //    );

            Dictionary <string, Dictionary <string, string> > dicCmds = new Dictionary <string, Dictionary <string, string> >();
            Dictionary <string, string> dicFuns = null;
            var itemNos = dtFunPermSet.Select(x => x.EOFPS_MenuItemNo_XX).Distinct().ToArray();
            foreach (var item in itemNos)
            {
                if (!dicCmds.Keys.Contains(item))
                {
                    dicCmds.Add(item, new Dictionary <string, string>());
                }
                dicFuns = dicCmds[item];

                var funs = dtFunPermSet.Where(x => x.EOFPS_MenuItemNo_XX == item).OrderBy(x => x.EOFPS_FunctionSeqNo_XX).ToArray();
                foreach (var fun in funs)
                {
                    if (dicFuns.Keys.Contains(fun.EOFPS_FunctionCode_XX))
                    {
                        dicFuns[fun.EOFPS_FunctionCode_XX] = fun.EOFPS_FunctionName_XX;
                    }
                    else
                    {
                        dicFuns.Add(fun.EOFPS_FunctionCode_XX, fun.EOFPS_FunctionName_XX);
                    }
                }
            }
            return(dicCmds);
        }
Esempio n. 16
0
        /// <summary>
        /// 查詢指定的使用者可以使用的權限清單
        /// </summary>
        public List <string> queryUserFunctions(string usrId, string menuId)
        {
            var targetIds = new List <string>();
            //Find Use's All Target
            {
                var usrperm   = FtdPermissionService.Instance.getActorPermission(usrId);
                var permCodes = usrperm.PermissionNames;
                var permIds   = NsDmHelper.EO_Permission
                                .where (t => t.EOP_PermissionCode.batchin(permCodes.toConstReq1()))
                                .query()
                                .Select(x => x.EOP_PermissionId)
                                .ToArray();

                //權限Id
                targetIds.AddRange(permIds);

                var rowe = NsDmHelper.EO_Employee.wherepk(usrId).queryFirst();
                if (rowe != null)
                {
                    //人員Id
                    targetIds.Add(rowe.EOE_EmployeeId);
                    //職稱Id
                    if (!rowe.EOE_EmployeeTitleId.isNullOrEmpty())
                    {
                        targetIds.Add(rowe.EOE_EmployeeTitleId);
                    }
                    //部門Id
                    if (!rowe.EOE_DepartmentId.isNullOrEmpty())
                    {
                        targetIds.Add(rowe.EOE_DepartmentId);
                    }
                    ////機關id
                    //if (!rowe.EOE_OrganId.isNullOrEmpty())
                    //    targetIds.Add(rowe.EOE_OrganId);
                    //群組Id
                    var dt2 = NsDmHelper.EO_DeptMember.where (t => t.EODM_MemberId == usrId.toConstReq1()).query();
                    dt2.forEach(x => targetIds.Add(x.EODM_DeptId));
                }
            }

            //Find Target's All Viewable Function
            var qry = new NsDbQuery();

            qry.setSelect(s =>
            {
                var t1 = s.from <EO_MenuPerm>();
                var t2 = s.join <EO_MenuPermSet>()
                         .on(t => t1.EOMP_MenuPermId == t.EOMPS_MenuPermId);

                s.Where = t1.EOMP_MenuId == menuId.toConstReq1()
                          & t1.EOMP_TargetId.batchin(targetIds.toConstReq1());

                s.select(
                    t2.EOMPS_MenuItemNo.As("ItemNo")
                    , t1.EOMP_ViewKind.decode("A", 0, "B", 1).sum().As("Viewable") //Value == 0 代表可檢視
                    );
                s.groupBy(s.Selects[0]);
                s.Having = s.Selects[1] == 0;//Value == 0 代表可檢視
            });
            var dt   = qry.queryData();
            var funs = dt.Rows.Cast <DataRow>().Select(x => x.getString("ItemNo")).ToList();

            return(funs);
        }