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();
}
protected override void onSchemaLoaded()
{
addTypedSqlHandler()
.setColumns(AppDataName.EOMP_TargetKindName_XX)
.setHandler(
t => t.EOMP_TargetKind.decode("A", "員工", "B", "部門/群組", "C", "職稱", "D", "權限")
//t => t.EOMP_TargetKind.decode("A", "員工", "B", "群組", "C", "職稱", "D", "權限", "E", "機關")
);
addTypedSqlHandler()
.setColumns(AppDataName.EOMP_ViewKindName_XX)
.setHandler(
t => t.EOMP_ViewKind.decode("A", "可檢視", "B", "不可檢視")
);
addTypedSqlHandler()
.setColumns(AppDataName.EOMP_TargetCode_XX)
.setHandler(t1 => {
var ta = t1.tryLink <EO_Employee>(t => t1.EOMP_TargetId == t.EOE_EmployeeId);
var tb = t1.tryLink <EO_Department>(t => t1.EOMP_TargetId == t.EOD_DepartmentId);
var tc = t1.tryLink <EO_EmployeeTitle>(t => t1.EOMP_TargetId == t.EOET_EmployeeTitleId);
var td = t1.tryLink <EO_Permission>(t => t1.EOMP_TargetId == t.EOP_PermissionId);
//var te = t1.tryLink<SY_Organ>(t => t1.EOMP_TargetId == t.SYO_OrganId);
return(NSQL.@case()
.when(ta.PrimaryKey != null, "(員工)" + ta.EOE_EmployeeCode)
.when(tb.PrimaryKey != null, "(" + tb.EOD_DepartmentTypeName_XX + ")" + tb.EOD_DepartmentCode)
.when(tc.PrimaryKey != null, "(職稱)" + tc.EOET_TitleCode)
.when(td.PrimaryKey != null, "(權限)" + td.EOP_PermissionCode)
//.when(te.PrimaryKey != null, "(機關)" + te.SYO_OrganId)
.@else(t1.EOMP_TargetId)
.end());
});
addTypedSqlHandler()
.setColumns(AppDataName.EOMP_TargetName_XX)
.setHandler(t1 => {
var ta = t1.tryLink <EO_Employee>(t => t1.EOMP_TargetId == t.EOE_EmployeeId);
var tb = t1.tryLink <EO_Department>(t => t1.EOMP_TargetId == t.EOD_DepartmentId);
var tc = t1.tryLink <EO_EmployeeTitle>(t => t1.EOMP_TargetId == t.EOET_EmployeeTitleId);
var td = t1.tryLink <EO_Permission>(t => t1.EOMP_TargetId == t.EOP_PermissionId);
//var te = t1.tryLink<SY_Organ>(t => t1.EOMP_TargetId == t.SYO_OrganId);
return(NSQL.@case()
.when(ta.PrimaryKey != null, "(員工)" + ta.EOE_EmployeeName)
.when(tb.PrimaryKey != null, "(" + tb.EOD_DepartmentTypeName_XX + ")" + tb.EOD_DepartmentName)
.when(tc.PrimaryKey != null, "(職稱)" + tc.EOET_TitleName)
.when(td.PrimaryKey != null, "(權限)" + td.EOP_PermissionName)
//.when(te.PrimaryKey != null, "(機關)" + te.SYO_OrganSName)
.@else(t1.EOMP_TargetId)
.end());
});
base.onSchemaLoaded();
}
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));
}
protected override void onSchemaLoaded()
{
addTypedSqlHandler()
.setColumns(AppDataName.EOE_EmployeeFullName_XX)
.setHandler(t1 => (t1.PrimaryKey == null).istrue("", t1.EOE_DepartmentName_XX + "-" + t1.EOE_EmployeeName + " " + t1.EOE_EmployeeTitleName_XX))
//.setHandler(t1 => (t1.PrimaryKey == null).istrue("", t1.EOE_OrganSName_XX + "-" + t1.EOE_EmployeeName + " " + t1.EOE_EmployeeTitleName_XX))
;
addTypedSqlHandler()
.setColumns(AppDataName.EOE_EmployeeStandardName_XX)
.setHandler(t1 => (t1.PrimaryKey == null).istrue("", t1.EOE_EmployeeName + " " + t1.EOE_EmployeeTitleName_XX))
;
addTypedSqlHandler()
.setColumns(AppDataName.EOE_SexName_XX)
.setHandler(t => t.EOE_Sex.decode("A", "男", "B", "女", t.EOE_Sex))
;
addTypedSqlHandler()
.setColumns(AppDataName.EOE_EmployeeSearchName_XX)
.setHandler(t1 => t1.EOE_EmployeeName + NSQL.iif(t1.EOE_EmployeeTitleName_XX.isnull("") != "", " " + t1.EOE_EmployeeTitleName_XX.isnull(""), "") + NSQL.iif(t1.EOE_EmployeeEmail != "", " (" + t1.EOE_EmployeeEmail + ")", ""))
;
addTypedSqlHandler()
.setColumns(AppDataName.EOE_IsLeave_XX)
.setHandler(t1 => NSQL.iif(t1.EOE_LeaveDate != null, "T", "F"))
;
addTypedSqlHandler()
.setColumns(AppDataName.EOE_CU_IsLoginUser_XX)
.setHandler(t => {
var userId = NsQueryContext.getCurrentUserId();
return((t.EOE_EmployeeId == userId).istrue("T", "F"));
});
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();
}