/// <summary>
/// 查詢指定使用者的權限清單
/// 如果使用者為人員的話，會包含其所屬部門權限
/// </summary>
public EO_PermissionSettingDataTable queryPermissionSettingsForUserId(string userId, string[] extendFieldNames)
{
//取得人員與其所屬群組代號
List <string> userIds = new List <string>();
var qry = new NsDbQuery();
qry.setSelect(s3 =>
{
var t11 = s3.from <EO_PermissionSetting>();
s3.select(t11.EOPS_PermissionSettingId);
s3.Where = qry.inquery(t11.EOPS_PermissionUserId,
s2 =>
{
s2.fromEmpty(AppDataName.EO);
s2.select(userId.toConst().As("UserId"));
s2.Where = userId.toConstReq1() != "";
s2.union(s =>
{
var t1 = s.from <EO_DeptMember>();
s.select(t1.EODM_DeptId);
s.Where = t1.EODM_MemberId == userId.toConstReq(userId.StartsWith("EOE_"))
;
s.groupBy(t1.EODM_DeptId);
});
});
});
var keys = qry.queryKeys <string>();
var dt = NsDmHelper.EO_PermissionSetting
.selectAll(extendFieldNames)
.where (t0 => t0.EOPS_PermissionSettingId.batchin(keys.toConstReq1()))
.query();
return(dt);
}
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));
}
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
}
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();
}
/// <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);
}
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);
}
/// <summary>
/// 查詢動作者{人、部門、群組}所擁有的權限(不及物)
/// </summary>
public override List <string> queryActorsPremissons(List <string> actor_ids)
{
var qry = new NsDbQuery();
//動作者所擁有的權限
qry.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_PermissionCode);
s.Where =
t1.EOPS_PermissionUserId.batchin(actor_ids.toConstReq1())
& t2.EOP_IsObjectNeed == "N" //免物件
;
s.groupBy(t2.EOP_PermissionCode);
s.unionAll(_s =>
{
var _t1 = _s.from <EO_Permission>();
_s.Where = _t1.EOP_IsEveryOneAllow == "Y";
_s.select(_t1.EOP_PermissionCode);
});
});
var names = qry.queryKeys <string>().Distinct().ToList();
return(names);
}
public override List <string> queryActorRoles(string actor_id)
{
// 查詢指定使用者{動作者}的權限清單
// 如果使用者為動作者的話，會包含其所屬{動作者、部門、群組}權限與動作者所屬部門所屬群組權限
var qry = new NsDbQuery();
var actor_ids = new List <string>();
var rowe = NsDmHelper.EO_Employee.wherepk(actor_id).queryFirst();
if (rowe != null)
{
#region [動作者]
//動作者所屬部門、群組
qry.setSelect(s =>
{
var t1 = s.from <EO_DeptMember>();
s.Where = t1.EODM_MemberId == actor_id.toConstReq1();
s.select(t1.EODM_DeptId);
});
var keys1 = qry.queryKeys <string>();
//部門所屬群組
qry.setSelect(s =>
{
var t1 = s.from <EO_DeptMember>();
s.Where = t1.EODM_MemberId.batchin(keys1.toConstReq1());
s.select(t1.EODM_DeptId);
});
var keys2 = qry.queryKeys <string>();
//得到 部門 群組
actor_ids = keys1.Union(keys2).Distinct().ToList();
#endregion
}
else
{
#region [部門、群組]
//部門所屬群組
qry.setSelect(s =>
{
var t1 = s.from <EO_DeptMember>();
s.Where = t1.EODM_MemberId == actor_id.toConstReq1();
s.select(t1.EODM_DeptId);
});
var keys1 = qry.queryKeys <string>();
keys1.Add(actor_id);
//得到 部門 群組
actor_ids = keys1;
#endregion
}
return(actor_ids);
}
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);
}
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));
}
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));
}
/// <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);
}
}
/// <summary>
/// 修正人員部門為群組者
/// </summary>
public static void step2()
{
var qry = new NsDbQuery();
qry.setSelect(s => {
var t1 = s.from <EO_Employee>();
var t2 = s.join <EO_Department>()
.on(t => t1.EOE_DepartmentId == t.EOD_DepartmentId);
s.Where = t2.EOD_DepartmentType == "B";
s.select(t1.EOE_EmployeeId);
});
var pks = qry.queryKeys <string>();
var dt = NsDmHelper.EO_Employee.wherepks(pks).query();
foreach (var row in dt)
{
row.EOE_DepartmentId = "";
}
dt.ns_update();
}
/// <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();
}
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 ? "使用中" : "未使用";
}
}
/// <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);
}
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
}
/// <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);
}
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();
}
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();
}
