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