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));
}
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);
}
}
