public static List<ParamField> getFields()
{
QueryBuilder query = new QueryBuilder(@"
SELECT
rf.rdb$field_name, rf.rdb$description,
f.rdb$field_type, f.rdb$field_length
FROM rdb$relation_fields rf
join rdb$relations r
on rf.rdb$relation_name = r.rdb$relation_name
join rdb$fields f
on f.rdb$field_name = rf.rdb$field_source
left join rdb$types t
on t.rdb$type = f.rdb$field_type
and t.rdb$field_name = 'RDB$FIELD_TYPE'
WHERE
r.rdb$view_blr is null and
(r.rdb$system_flag is null or r.rdb$system_flag=0) and
rf.rdb$relation_name = 'FW_REPORT_PARAMS'
and rf.rdb$field_name <> '" + KEY_FIELD_NAME + "' and 1=1");
DataSet ds = DABase.getDatabase().LoadDataSet(query);
List<ParamField> list = new List<ParamField>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
ParamField field = new ParamField();
field.FIELD_NAME = dr["RDB$FIELD_NAME"].ToString().Trim();
field.DESCRIPTION = dr["RDB$DESCRIPTION"].ToString();
field.FIELD_TYPE = HelpNumber.ParseInt32(dr["RDB$FIELD_TYPE"]);
field.FIELD_LENGTH = HelpNumber.ParseInt64(dr["RDB$FIELD_LENGTH"]);
list.Add(field);
}
return list;
}
public override QueryBuilder PLBuildQueryFilter()
{
QueryBuilder filter = new QueryBuilder(BIEN_MUC.FILTER_STRING);
filter.addSoPhieu(BIEN_MUC.MA_BANG, MaBang.Text);
filter.addLike("LOWER(" + BIEN_MUC.NOI_DUNG + ")", NoiDung.Text.ToLower());
AppCtrl.addID(filter, BIEN_MUC.SO_DKCB_HD, SoDKHDCB);
AppCtrl.addID(filter, BIEN_MUC.TIET_MUC, TietMuc);
AppCtrl.addID(filter, BIEN_MUC.LOAI_LUU_TRU, LoaiBang);
AppCtrl.addID(filter, BIEN_MUC.DON_VI_CUNG_CAP, DonViCungCap);
AppCtrl.addID(filter, BIEN_MUC.DON_VI_SO_HUU, DonViSoHuu);
filter.addIn(BIEN_MUC.POST_MASTER, AppCtrl.GetCheckedValues(TinhTrang, true));
filter.addIn(BIEN_MUC.IS_THANH_LY, AppCtrl.GetCheckedValues(ThanhLy, true));
AppCtrl.addFilterFromTo(filter, BIEN_MUC.NGAY_NHAP, ngayNhap);
AppCtrl.addFilterFromTo(filter, BIEN_MUC.NAM_SAN_XUAT, NamSXTu.Value, NamSXDen.Value);
long[] IDs = QuocGia._getSelectedIDs();
if (IDs.Length > 0 && IDs.Length < QuocGia.Properties.GetItems().Count)
{
string cond = "(";
foreach (long id in IDs)
{
cond += BIEN_MUC.NUOC_SAN_XUAT + " like '%," + id + ",%' or ";
}
cond = cond.TrimEnd(' ', 'r', 'o');
cond = cond += ")";
filter.addCondition(cond);
}
filter.addCondition("1=1");
filter.setDescOrderBy(BIEN_MUC.NGAY_CAP_NHAT);
return filter;
}
public string BaseQuery(bool selectTop, int top)
{
QueryBuilder builder;
try
{
builder = new QueryBuilder();
if (selectTop == true)
builder.SelectTop(top);
else
builder.Select();
builder.AppendColumn(TaskObject.TASK_ID);
builder.AppendColumn(TaskObject.TAG);
builder.AppendColumn(TaskObject.CREATION_DATE);
builder.AppendColumn(TaskObject.UPDATE_DATE);
builder.AppendColumn(TaskObject.DUE_DATE);
builder.AppendColumn(TaskObject.STATUS);
builder.AppendColumn(TaskObject.PRIORITY);
builder.AppendColumn(TaskObject.EFFORT);
builder.AppendFromTable(TaskObject.TABLE);
return builder.Query;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
}
public string BaseQuery(bool selectTop, int top)
{
QueryBuilder builder;
try
{
builder = new QueryBuilder();
if (selectTop == true)
builder.SelectTop(top);
else
builder.Select();
builder.AppendColumn(RevisionObject.REV_ID);
builder.AppendColumn(RevisionObject.MATCAT_ID);
builder.AppendColumn(RevisionObject.MATTYP_ID);
builder.AppendColumn(RevisionObject.MATSTYP_ID);
builder.AppendColumn(RevisionObject.REVISION);
builder.AppendColumn(MaterialCategoryObject.CATEGORY);
builder.AppendColumn(MaterialCategoryObject.MATCAT_ID);
builder.AppendColumn(MaterialSubTypeObject.MATSTYP_ID);
builder.AppendColumn(MaterialSubTypeObject.MATTYP_ID);
builder.AppendColumn(MaterialSubTypeObject.SUBTYPE);
builder.AppendColumn(MaterialTypeObject.MATCAT_ID);
builder.AppendColumn(MaterialTypeObject.MATTYP_ID);
builder.AppendColumn(MaterialTypeObject.TYPE);
builder.AppendFromTable(RevisionObject.TABLE);
builder.AppendLeftJoin(MaterialCategoryObject.TABLE, MaterialCategoryObject.MATCAT_ID, RevisionObject.MATCAT_ID);
builder.AppendLeftJoin(MaterialTypeObject.TABLE, MaterialTypeObject.MATTYP_ID, RevisionObject.MATTYP_ID);
builder.AppendLeftJoin(MaterialSubTypeObject.TABLE, MaterialSubTypeObject.MATSTYP_ID, RevisionObject.MATSTYP_ID);
return builder.Query;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
}
public static void InitChonDienDan(PLComboboxAdd PLDien_Dan,bool? IsAdd)
{
QueryBuilder query = null;
query = new QueryBuilder("SELECT * FROM NHOM_DIEN_DAN where ID_CHA<>0 " + (IsAdd == true ? " AND VISIBLE_BIT='Y'" : "") + " and 1=1");
DataSet ds = HelpDB.getDatabase().LoadDataSet(query);
PLDien_Dan._init(ds.Tables[0], "NAME", "ID");
}
private void btnView_Click(object sender, EventArgs e)
{
if(KenhPhat._getSelectedID()<=1)
{
HelpMsgBox.ShowNotificationMessage("Vui lòng chọn kênh phát");
return;
}
var sql =
string.Format(
@"select ct.time_start,ct.time_end,ct.kenh_phat,iif(c.ct_id is null, tct.noi_dung, c.noi_dung) noi_dung,
c.nuoc_san_xuat, ct.rate_group,datediff(minute,ct.time_start,ct.time_end) timesplit
from tns_programs_grid_scr_ct ct
left join tns_chuong_trinh tct on tct.tct_id=ct.chuong_trinh
left join chuong_trinh c on c.ct_id=tct.ct_id
where 1=1");
var query = new QueryBuilder(sql);
AppCtrl.addID(query, "ct.PGS_id", FileNguon);
query.addID("CT.KENH_PHAT", KenhPhat._getSelectedID());
var dt = NgayPhatSong.DateTime;
query.addDateFromTo("CT.NGAY_PHAT", new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0),
new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 59));
query.setAscOrderBy("ct.time_start, ct.kenh_phat");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
gridControlDetail.DataSource = ds.Tables[0];
gridBand1.Caption = dt.ToString("ddd " + FrameworkParams.option.dateFormat);
}
/// <summary>
/// APU维修成本查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns> APU维修成本DTO集合。</returns>
public IQueryable<ApuMaintainCostDTO> ApuMaintainCostDTOQuery(
QueryBuilder<ApuMaintainCost> query)
{
return
query.ApplyTo(_maintainCostRepository.GetAll().OfType<ApuMaintainCost>())
.Select(p => new ApuMaintainCostDTO
{
Id = p.Id,
NameType = p.NameType,
Type = p.Type,
YearBudgetRate = p.YearBudgetRate,
Rate = p.Rate,
BudgetHour = p.BudgetHour,
HourPercent = p.HourPercent,
Hour = p.Hour,
ContractRepairFeeUsd = p.ContractRepairFeeUsd,
ContractRepairFeeRmb = p.ContractRepairFeeRmb,
CustomRate = p.CustomRate,
TotalTax = p.TotalTax,
AddedValueRate = p.AddedValueRate,
AddedValue = p.AddedValue,
IncludeAddedValue = p.IncludeAddedValue,
MaintainInvoiceId = p.MaintainInvoiceId,
Year = p.Year,
AcutalBudgetAmount = p.MaintainInvoice.PaymentScheduleLineId == null ? 0 : p.MaintainInvoice.PaymentScheduleLine.Amount,
AcutalAmount = p.MaintainInvoice.InvoiceValue
});
}
/// <summary>
/// add key=value into QueryModel.Items
/// </summary>
/// <param name="model">QueryModel</param>
/// <param name="key">the name of Element (input/select/area/radio/checkbox)</param>
/// <param name="val">the value of Element (input/select/area/radio/checkbox)</param>
public static void AddSearchItem(QueryBuilder model, string key, string val)
{
if (model == null)
{
model = new QueryBuilder();
}
string orGroup = "";
var keywords = key.Split("$', ')', '}".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (keywords.Length != 2)
return;
var method = SearchMethodAdapter(keywords[0]);
var field = keywords[1];
if (string.IsNullOrEmpty(method)) return;
if (!string.IsNullOrEmpty(field))
{
var item = new ConditionItem
{
Field = field,
Value = val.Trim(),
OrGroup = orGroup,
Method = (QueryMethod)Enum.Parse(typeof(QueryMethod), method)
};
model.Items.Add(item);
}
}
public IQueryable<EnginePaymentScheduleDTO> EnginePaymentSchedulesQuery(QueryBuilder<PaymentSchedule> query)
{
var dbCurrency = _unitOfWork.CreateSet<Currency>();
return
query.ApplyTo(_unitOfWork.CreateSet<PaymentSchedule>())
.OfType<EnginePaymentSchedule>()
.Select(p => new EnginePaymentScheduleDTO
{
EnginePaymentScheduleId = p.Id,
CreateDate = p.CreateDate,
CurrencyId = p.CurrencyId,
IsCompleted = p.IsCompleted,
SupplierId = p.SupplierId,
SupplierName = p.SupplierName,
ContractEngineId = p.ContractEngineId,
CurrencyName = dbCurrency.FirstOrDefault(c => c.Id == p.CurrencyId).CnName,
PaymentScheduleLines = p.PaymentScheduleLines.Select(c => new PaymentScheduleLineDTO
{
PaymentScheduleLineId = c.Id,
Amount = c.Amount,
Start = c.Start,
End = c.End,
Body = c.Body,
Subject = c.Subject,
PaymentScheduleId = c.PaymentScheduleId,
ScheduleDate = c.ScheduleDate,
Status = (int) c.Status,
Importance = c.Importance,
ProcessStatus = c.Tempo,
InvoiceId = c.InvoiceId,
IsAllDayEvent = c.IsAllDayEvent
}).ToList(),
});
}
/// <summary>
/// FHA维修成本查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns> FHA维修成本DTO集合。</returns>
public IQueryable<FhaMaintainCostDTO> FhaMaintainCostDTOQuery(
QueryBuilder<FhaMaintainCost> query)
{
return
query.ApplyTo(_maintainCostRepository.GetAll().OfType<FhaMaintainCost>())
.Select(p => new FhaMaintainCostDTO
{
Id = p.Id,
EngineProperty = p.EngineProperty,
Jx = p.Jx,
YearBudgetRate = p.YearBudgetRate,
Rate = p.Rate,
AirHour = p.AirHour,
HourPercent = p.HourPercent,
Hour = p.Hour,
FhaFeeUsd = p.FhaFeeUsd,
FhaFeeRmb = p.FhaFeeRmb,
Custom = p.Custom,
CustomAddedRmb = p.CustomAddedRmb,
TotalTax = p.TotalTax,
AddedValueRate = p.AddedValueRate,
AddedValue = p.AddedValue,
IncludeAddedValue = p.IncludeAddedValue,
CustomAdded = p.CustomAdded,
AircraftTypeId = p.AircraftTypeId,
MaintainInvoiceId = p.MaintainInvoiceId,
Year = p.Year,
AcutalBudgetAmount = p.MaintainInvoice.PaymentScheduleLineId == null ? 0 : p.MaintainInvoice.PaymentScheduleLine.Amount,
AcutalAmount = p.MaintainInvoice.InvoiceValue
});
}
/// <summary>
/// 备发计划查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>备发计划DTO集合。</returns>
public IQueryable<EnginePlanDTO> EnginePlanDTOQuery(
QueryBuilder<EnginePlan> query)
{
return query.ApplyTo(_unitOfWork.CreateSet<EnginePlan>()).Select(p => new EnginePlanDTO
{
Id = p.Id,
AirlinesId = p.AirlinesId,
AnnualId = p.AnnualId,
CreateDate = p.CreateDate,
DocName = p.DocName,
DocNumber = p.DocNumber,
DocumentId = p.DocumentId,
IsValid = p.IsValid,
Note = p.Note,
Status = (int)p.Status,
Title = p.Title,
VersionNumber = p.VersionNumber,
EnginePlanHistories = p.EnginePlanHistories.Select(q=>new EnginePlanHistoryDTO
{
Id = q.Id,
ActionCategoryId = q.ActionCategoryId,
EnginePlanId = q.EnginePlanId,
EngineTypeId = q.EngineTypeId,
IsFinished = q.IsFinished,
MaxThrust = q.MaxThrust,
ImportDate = q.ImportDate,
Note = q.Note,
PerformAnnualId = q.PerformAnnualId,
PerformMonth = q.PerformMonth,
PlanEngineId = q.PlanEngineId,
Status = (int)q.Status,
}).ToList(),
}).OrderBy(p=>p.CreateDate);
}
/// <summary>
/// 序号件装机历史查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>序号件装机历史DTO集合。</returns>
public IQueryable<SnHistoryDTO> SnHistoryDTOQuery(
QueryBuilder<SnHistory> query)
{
var dbAircrafts = _unitOfWork.CreateSet<Aircraft>();
return query.ApplyTo(_unitOfWork.CreateSet<SnHistory>()).Select(p => new SnHistoryDTO
{
Id = p.Id,
Sn = p.Sn,
SnRegId = p.SnRegId,
Pn = p.Pn,
PnRegId = p.PnRegId,
CSN = p.CSN,
TSN = p.TSN,
CSN2 = p.CSN2,
TSN2 = p.TSN2,
Status = (int)p.Status,
Position = (int)p.Position,
AircraftId = p.AircraftId,
ActionDate = p.ActionDate,
ActionType = (int)p.ActionType,
ActionNo = p.RemInstRecord.ActionNo,
ActionReason = p.RemInstRecord.Reason,
RemInstRecordId = p.RemInstRecordId,
RegNumber = dbAircrafts.FirstOrDefault(c => c.Id == p.AircraftId).RegNumber,
});
}
/// <summary>CHAUTV : Tính tổng số giờ nhân viên làm được trong khoảng thời gian xác định
/// </summary>
public static double GetSumDaysWorked(DateTime from, DateTime to, long employKey)
{
QueryBuilder query = new QueryBuilder(@"SELECT distinct NV_ID,NGAY,SANG,CHIEU
FROM BANG_CHAM_CONG_AUTO
WHERE 1=1");
query.addID("NV_ID", employKey);
query.addDateFromTo("NGAY", from, to);
DataSet ds = HelpDB.getDatabase().LoadDataSet(query, "BANG_CHAM_CONG_AUTO");
double cntV = 0;
double sumTime = 0;
double cntN = 0;
//Duyệt đếm thời gian, vắng, nghỉ
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["SANG"].ToString() == "V")
cntV++;
if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "V")
cntV++;
if (ds.Tables[0].Rows[i]["SANG"].ToString() == "N")
cntN++;
if (ds.Tables[0].Rows[i]["CHIEU"].ToString() == "N")
cntN++;
if (checkNumber(ds.Tables[0].Rows[i]["SANG"].ToString()))
sumTime += double.Parse(ds.Tables[0].Rows[i]["SANG"].ToString());
if (checkNumber(ds.Tables[0].Rows[i]["CHIEU"].ToString()))
sumTime += double.Parse(ds.Tables[0].Rows[i]["CHIEU"].ToString());
}
//Làm tròn 4 chữ số
return Math.Round(sumTime / 8 - cntV / 2, 4);
}
public override void AddSort(object expr, IComparer comparer)
{
// sort makes sense only when we are dealing with a query that
// returns a nodeset.
Query evalExpr;
string query = expr as string;
if (query != null)
{
evalExpr = new QueryBuilder().Build(query, out _needContext); // this will throw if expr is invalid
}
else
{
CompiledXpathExpr xpathExpr = expr as CompiledXpathExpr;
if (xpathExpr != null)
{
evalExpr = xpathExpr.QueryTree;
}
else
{
throw XPathException.Create(SR.Xp_BadQueryObject);
}
}
SortQuery sortQuery = _query as SortQuery;
if (sortQuery == null)
{
_query = sortQuery = new SortQuery(_query);
}
sortQuery.AddSort(evalExpr, comparer);
}
public void AddToQuery(AuditConfiguration verCfg, String entityName, QueryBuilder qb, Parameters parameters)
{
Parameters opParameters = parameters.AddSubParameters(op);
lhs.AddToQuery(verCfg, entityName, qb, opParameters.AddSubParameters("and"));
rhs.AddToQuery(verCfg, entityName, qb, opParameters.AddSubParameters("and"));
}
/// <summary>
/// 贷项单查询
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>贷项单DTO集合。</returns>
public IQueryable<MaintainCreditNoteDTO> MaintainCreditNoteDTOQuery(
QueryBuilder<MaintainCreditNoteInvoice> query)
{
return
query.ApplyTo(_unitOfWork.CreateSet<MaintainCreditNoteInvoice>())
.Select(p => new MaintainCreditNoteDTO
{
CreditNoteId = p.Id,
InvoiceNumber = p.InvoiceNumber,
InvoideCode = p.InvoideCode,
InvoiceDate = p.InvoiceDate,
SupplierName = p.SupplierName,
SupplierId = p.SupplierId,
InvoiceValue = p.InvoiceValue,
PaidAmount = p.PaidAmount,
OperatorName = p.OperatorName,
Reviewer = p.Reviewer,
CreateDate = p.CreateDate,
ReviewDate = p.ReviewDate,
IsValid = p.IsValid,
IsCompleted = p.IsCompleted,
Status = (int)p.Status,
CurrencyId = p.CurrencyId,
PaymentScheduleLineId = p.PaymentScheduleLineId,
InvoiceLines = p.InvoiceLines.Select(q => new InvoiceLineDTO
{
InvoiceLineId = q.Id,
ItemName = (int)q.ItemName,
Amount = q.Amount,
InvoiceId = q.InvoiceId,
OrderLineId = q.OrderLineId,
Note = q.Note,
}).ToList(),
});
}
public string BaseQuery(bool selectTop, int top)
{
QueryBuilder builder;
try
{
builder = new QueryBuilder();
if (selectTop == true)
builder.SelectTop(top);
else
builder.Select();
builder.AppendColumn(ScheduleObject.SCHED_ID);
builder.AppendColumn(ScheduleObject.FREQUENCY);
builder.AppendColumn(ScheduleObject.MONDAY);
builder.AppendColumn(ScheduleObject.TUESDAY);
builder.AppendColumn(ScheduleObject.WEDNESDAY);
builder.AppendColumn(ScheduleObject.THURSDAY);
builder.AppendColumn(ScheduleObject.FRIDAY);
builder.AppendColumn(ScheduleObject.WEDNESDAY);
builder.AppendColumn(ScheduleObject.SATERDAY);
builder.AppendColumn(ScheduleObject.SUNDAY);
builder.AppendColumn(ScheduleObject.STARTDATE);
builder.AppendColumn(ScheduleObject.TIME);
builder.AppendColumn(ScheduleObject.LAST_SEND);
builder.AppendFromTable(ScheduleObject.TABLE);
return builder.Query;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
}
public IQueryable<MaintainGuaranteeDTO> MaintainGuaranteeQuery(QueryBuilder<Guarantee> query)
{
var dbCurrency = _unitOfWork.CreateSet<Currency>();
var dbMaintainContract = _unitOfWork.CreateSet<MaintainContract>();
return
query.ApplyTo(_unitOfWork.CreateSet<Guarantee>()).OfType<MaintainGuarantee>()
.Select(p => new MaintainGuaranteeDTO
{
GuaranteeId = p.Id,
Amount = p.Amount,
CreateDate = p.CreateDate,
CurrencyId = p.CurrencyId,
EndDate = p.EndDate,
OperatorName = p.OperatorName,
MaintainContractId = p.MaintainContractId,
ReviewDate = p.ReviewDate,
Reviewer = p.Reviewer,
StartDate = p.StartDate,
SupplierId = p.SupplierId,
SupplierName = p.SupplierName,
Status = (int)p.Status,
CurrencyName = dbCurrency.FirstOrDefault(c => c.Id == p.CurrencyId).CnName,
MaintainContractName = dbMaintainContract.FirstOrDefault(c=>c.Id==p.MaintainContractId).Name,
});
}
public static void InitChonKhachHang(PLCombobox PLKhachhang)
{
QueryBuilder query = null;
query = new QueryBuilder("SELECT distinct TEN_KHACH_HANG, KH_ID FROM KHACH_HANG where 1=1");
DataSet ds = DABase.getDatabase().LoadDataSet(query);
PLKhachhang._init(ds.Tables[0], "TEN_KHACH_HANG", "KH_ID");
}
/// <summary>
/// 付款通知查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>付款通知DTO集合。</returns>
public IQueryable<PaymentNoticeDTO> PaymentNoticeDTOQuery(QueryBuilder<PaymentNotice> query)
{
return
query.ApplyTo(_paymentNoticeRepository.GetAll()).Select(p => new PaymentNoticeDTO
{
PaymentNoticeId = p.Id,
NoticeNumber = p.NoticeNumber,
CreateDate = p.CreateDate,
DeadLine = p.DeadLine,
SupplierName = p.SupplierName,
OperatorName = p.OperatorName,
Reviewer = p.Reviewer,
ReviewDate = p.ReviewDate,
Status = (int)p.Status,
SupplierId = p.SupplierId,
CurrencyId = p.CurrencyId,
CurrencyName = p.Currency.CnName,
BankAccountName = p.BankAccount.Account + "/" + p.BankAccount.Bank + p.BankAccount.Branch,
BankAccountId = p.BankAccountId,
IsComplete = p.IsComplete,
PaymentNoticeLines =
p.PaymentNoticeLines.Select(q => new PaymentNoticeLineDTO
{
PaymentNoticeLineId = q.Id,
InvoiceType = (int)q.InvoiceType,
InvoiceId = q.InvoiceId,
InvoiceNumber = q.InvoiceNumber,
Amount = q.Amount,
Note = q.Note
}).ToList(),
});
}
public void AddToQuery(AuditConfiguration auditCfg, String entityName, QueryBuilder qb, Parameters parameters)
{
String propertyName = propertyNameGetter.Get(auditCfg);
CriteriaTools.CheckPropertyNotARelation(auditCfg, entityName, propertyName);
// This will be the aggregated query, containing all the specified conditions
QueryBuilder subQb = qb.NewSubQueryBuilder();
// Adding all specified conditions both to the main query, as well as to the
// aggregated one.
foreach (IAuditCriterion versionsCriteria in criterions) {
versionsCriteria.AddToQuery(auditCfg, entityName, qb, parameters);
versionsCriteria.AddToQuery(auditCfg, entityName, subQb, subQb.RootParameters);
}
// Setting the desired projection of the aggregated query
switch (mode) {
case AggregatedMode.MIN:
subQb.AddProjection("min", propertyName, false);
break;
case AggregatedMode.MAX:
subQb.AddProjection("max", propertyName, false);
break;
}
// Adding the constrain on the result of the aggregated criteria
parameters.AddWhere(propertyName, "=", subQb);
}
public void AddToQuery(AuditConfiguration auditCfg, String entityName, QueryBuilder qb, Parameters parameters)
{
String propertyName = propertyNameGetter.Get(auditCfg);
CriteriaTools.CheckPropertyNotARelation(auditCfg, entityName, propertyName);
parameters.AddWhereWithParam(propertyName, ">=", lo);
parameters.AddWhereWithParam(propertyName, "<=", hi);
}
/// <summary>
/// 航空公司五年规划查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>航空公司五年规划DTO集合。</returns>
public IQueryable<AirProgrammingDTO> AirProgrammingDTOQuery(
QueryBuilder<AirProgramming> query)
{
return query.ApplyTo(_unitOfWork.CreateSet<AirProgramming>()).Select(p => new AirProgrammingDTO
{
Id = p.Id,
CreateDate = p.CreateDate,
DocName = p.DocName,
DocumentId = p.DocumentId,
IssuedDate = p.IssuedDate,
IssuedUnitId = p.IssuedUnitId,
Name = p.Name,
Note = p.Note,
ProgrammingId = p.ProgrammingId,
ProgrammingName = p.Programming.Name,
AirProgrammingLines = p.AirProgrammingLines.Select(q=>new AirProgrammingLineDTO
{
Id = q.Id,
AircraftSeriesId = q.AircraftSeriesId,
AirProgrammingId = q.AirProgrammingId,
BuyNum = q.BuyNum,
ExportNum = q.ExportNum,
LeaseNum = q.LeaseNum,
Year = q.Year,
}).ToList(),
});
}
private void btnView_Click(object sender, EventArgs e)
{
var sql =
string.Format(
@"select right( '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start) +1),2) ||':00' VET,
kp.name kenh_phat,avg(ct.rate_group)
from tns_programs_grid_scr_ct ct
left join tns_kenh_phat kp on ct.kenh_phat=kp.id
where 1=1");
var query = new QueryBuilder(sql);
AppCtrl.addID(query, "ct.PGS_id", FileNguon);
AppCtrl.addID(query, "CT.KENH_PHAT", KenhPhat);
query.addDateFromTo("CT.NGAY_PHAT", TuNgay.DateTime, DenNgay.DateTime);
query.addGroupBy(@"right( '00'||extract(hour from ct.time_start),2) ||':00 '|| ' - ' ||right('00'||(extract(hour from ct.time_start) +1),2) ||':00',
kp.name");
query.setAscOrderBy("ct.time_start, ct.kenh_phat");
var ds = HelpDB.getDBService().LoadDataSet(query, KE_HOACH_LPS_CT.TABLE_NAME);
if (ds != null && ds.Tables.Count > 0)
{
var dt = ds.Tables[0];
var dtS = dt.DefaultView.ToTable(true, "VET");
}
gridControlDetail.DataSource = ds.Tables[0];
}
public string BaseQuery(bool selectTop, int top)
{
QueryBuilder builder;
try
{
builder = new QueryBuilder();
if (selectTop == true)
builder.SelectTop(top);
else
builder.Select();
builder.AppendColumn(PopupUserObject.POPUPUSER_ID);
builder.AppendColumn(PopupUserObject.POPUP_ID);
builder.AppendColumn(PopupUserObject.USER_ID);
builder.AppendColumn(PopupObject.POPUP_ID);
builder.AppendColumn(PopupObject.DESCRIPTION);
builder.AppendColumn(PopupObject.REPEAT);
builder.AppendColumn(PopupObject.ACTIVE);
builder.AppendColumn(PopupObject.CREATIONDATE);
builder.AppendColumn(PopupObject.TIME);
builder.AppendColumn(PopupObject.SHOW_DIRECT);
builder.AppendColumn(UserObject.USR_ID);
builder.AppendFromTable(PopupUserObject.TABLE);
builder.AppendLeftJoin(PopupObject.TABLE, PopupUserObject.POPUP_ID, PopupObject.POPUP_ID);
builder.AppendLeftJoin(UserObject.TABLE, PopupUserObject.USER_ID, UserObject.USR_ID);
return builder.Query;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
}
public static void addID(QueryBuilder filter, string opLeft, PLMultiChoiceGrid input)
{
if (!input._IsNotCheckAnyOrCheckedAll)
{
filter.addID(opLeft, input._GetSelectedIds());
}
}
/// <summary>
/// <see cref="IOrderQuery" />
/// </summary>
/// <param name="query">
/// <see cref="IOrderQuery" />
/// </param>
/// <returns>
/// <see cref="IOrderQuery" />
/// </returns>
public IQueryable<AircraftLeaseOrderDTO> AircraftLeaseOrderQuery(QueryBuilder<Order> query)
{
var trades = _unitOfWork.CreateSet<Trade>();
var result = query.ApplyTo(_orderRepository.GetAll().OfType<AircraftLeaseOrder>().Where(p => p.IsValid))
.Select(o => new AircraftLeaseOrderDTO
{
Id = o.Id,
Name = o.Name,
Version = o.Version,
CurrencyId = o.Currency.Id,
OperatorName = o.OperatorName,
OrderDate = o.OrderDate,
Status = (int) o.Status,
Note = o.Note,
SupplierId = trades.FirstOrDefault(p => p.Id == o.TradeId).SupplierId,
SupplierName = trades.FirstOrDefault(p => p.Id == o.TradeId).Supplier.CnName,
AircraftLeaseOrderLines =
o.OrderLines.OfType<AircraftLeaseOrderLine>().Select(l => new AircraftLeaseOrderLineDTO
{
Id = l.Id,
UnitPrice = l.UnitPrice,
Amount = l.Amount,
Discount = l.Discount,
EstimateDeliveryDate = l.EstimateDeliveryDate,
Note = l.Note,
ContractAircraftId = l.ContractAircraftId,
OrderId = l.OrderId,
TotalLine = (l.UnitPrice*l.Amount)*(1 - (l.Discount/100M)),
}).ToList(),
});
return result;
}
/// <summary>
/// 采购合同飞机查询
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>采购合同飞机DTO集合。</returns>
public IQueryable<PurchaseContractAircraftDTO> PurchaseContractAircraftDTOQuery(
QueryBuilder<PurchaseContractAircraft> query)
{
return
query.ApplyTo(_unitOfWork.CreateSet<ContractAircraft>().OfType<PurchaseContractAircraft>())
.Select(p => new PurchaseContractAircraftDTO
{
PurchaseContractAircraftId = p.Id,
ContractName = p.ContractName,
ContractNumber = p.ContractNumber,
RankNumber = p.RankNumber,
CSCNumber = p.CSCNumber,
SerialNumber = p.SerialNumber,
IsValid = p.IsValid,
ReceivedAmount = p.ReceivedAmount,
AcceptedAmount = p.AcceptedAmount,
AircraftTypeId = p.AircraftTypeId,
AircraftTypeName = p.AircraftType.Name,
PlanAircraftID = p.PlanAircraftID,
ImportCategoryId = p.ImportCategoryId,
ImportType = p.ImportCategory.ActionType,
ImportActionName = p.ImportCategory.ActionName,
SupplierId = p.SupplierId,
});
}
public string BaseQuery(bool selectTop, int top)
{
QueryBuilder builder;
try
{
builder = new QueryBuilder();
if (selectTop == true)
builder.SelectTop(top);
else
builder.Select();
builder.AppendColumn(MaintenanceVehicleObject.MAINTVEH_ID);
builder.AppendColumn(MaintenanceVehicleObject.EMPID_EXECUTOR);
builder.AppendColumn(MaintenanceVehicleObject.REMARK);
builder.AppendColumn(MaintenanceVehicleObject.VEH_ID);
builder.AppendColumn(MaintenanceVehicleObject.START_MAINTENANCE);
builder.AppendFromTable(MaintenanceVehicleObject.TABLE);
builder.AppendInnerJoin(VehicleObject.TABLE, VehicleObject.VEHID, MaintenanceVehicleObject.VEH_ID);
builder.AppendLeftJoin(EmployeeObject.TABLE, EmployeeObject.EMP_ID, MaintenanceVehicleObject.EMPID_EXECUTOR);
return builder.Query;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
}
/// <summary>
/// 飞机配置查询。
/// </summary>
/// <param name="query">查询表达式。</param>
/// <returns>飞机配置DTO集合。</returns>
public IQueryable<AircraftConfigurationDTO> AircraftConfigurationDTOQuery(
QueryBuilder<AircraftConfiguration> query)
{
return query.ApplyTo(_unitOfWork.CreateSet<AircraftConfiguration>()).Select(p => new AircraftConfigurationDTO
{
Id = p.Id,
AircraftSeriesId = p.AircraftSeriesId,
AircraftTypeId = p.AircraftTypeId,
BEW = p.BEW,
BW = p.BW,
BWF = p.BWF,
BWI = p.BWI,
ConfigCode = p.ConfigCode,
Description = p.Description,
FileContent = p.FileContent,
FileName = p.FileName,
MCC = p.MCC,
MLW = p.MLW,
MMFW = p.MMFW,
MTOW = p.MTOW,
MTW = p.MTW,
MZFW = p.MZFW,
AircraftCabins = p.AircraftCabins.Select(q=>new AircraftCabinDTO
{
Id = q.Id,
AircraftCabinTypeId = q.AircraftCabinTypeId,
SeatNumber = q.SeatNumber,
Note = q.Note
}).ToList(),
});
}
public JsonResult GetList(string tmplCode, QueryBuilder qb)
{
var listDef = entities.Set <S_UI_List>().SingleOrDefault(c => c.Code == tmplCode);
SQLHelper sqlHeler = SQLHelper.CreateSqlHelper(listDef.ConnName);
UIFO uiFO = FormulaHelper.CreateFO <UIFO>();
string sql = uiFO.ReplaceString(listDef.SQL);
DataTable dtTmpl = sqlHeler.ExecuteDataTable(string.Format("select * from ({0}) as T where 1=2", sql));
//地址栏参数作为查询条件
foreach (string key in Request.QueryString.Keys)
{
if (string.IsNullOrEmpty(key))
{
continue;
}
if ("ID,FullID,FULLID,TmplCode,IsPreView,_winid,_t".Split(',').Contains(key) || key.StartsWith("$"))
{
continue;
}
if (dtTmpl.Columns.Contains(key))
{
qb.Add(key, QueryMethod.Equal, Request[key], "Group1", null);
}
}
var data = sqlHeler.ExecuteGridData(sql, qb);
#region 计算汇总
var fields = JsonHelper.ToList(listDef.LayoutField);
StringBuilder sb = new StringBuilder();
foreach (var field in fields)
{
if (field.ContainsKey("Settings") == false)
{
continue;
}
var settings = JsonHelper.ToObject(field["Settings"].ToString());
if (settings.ContainsKey("Collect") == false || settings["Collect"].ToString() == "")
{
continue;
}
sb.AppendFormat(",{0}={1}({0})", field["field"], settings["Collect"]);
if (settings["Collect"].ToString() == "sum")
{
data.sumData.Add(field["field"].ToString(), null);
}
else
{
data.avgData.Add(field["field"].ToString(), null);
}
}
if (sb.Length > 0)
{
string collectSql = string.Format("select {0} from (select * from ({1}) as tb where 1=1 {2}) as T"
, sb.ToString().Trim(',')
, sql
, qb.GetWhereString(false) + FormulaHelper.CreateAuthDataFilter().GetWhereString(false)
);
DataTable dtCollect = sqlHeler.ExecuteDataTable(collectSql);
foreach (DataColumn col in dtCollect.Columns)
{
if (data.sumData.ContainsKey(col.ColumnName))
{
data.sumData[col.ColumnName] = dtCollect.Rows[0][col];
}
else
{
data.avgData[col.ColumnName] = dtCollect.Rows[0][col];
}
}
}
#endregion
return(Json(data));
}
public ActionResult Callback(
string encodedRedirect,
[FromQuery(Name = "code")] string code,
[FromQuery(Name = "state")] string state,
[FromQuery(Name = "session_state")] string sessionState,
[FromQuery(Name = "error")] string error,
[FromQuery(Name = "error_description")] string errorDescription)
{
Uri redirectUrl = null;
try
{
redirectUrl = new Uri(Base64UrlEncoder.Decode(encodedRedirect));
}
catch (FormatException ex)
{
throw new AadSmartOnFhirProxyBadRequestException(string.Format(Resources.InvalidRedirectUri, redirectUrl), ex);
}
if (!string.IsNullOrEmpty(error))
{
var errorQueryBuilder = new QueryBuilder
{
{ "error", error },
};
if (!string.IsNullOrEmpty(errorDescription))
{
errorQueryBuilder.Add("error_description", errorDescription);
}
return(Redirect($"{redirectUrl}{errorQueryBuilder}"));
}
string compoundCode;
string newState;
try
{
var launchStateParameters = JObject.Parse(Base64UrlEncoder.Decode(state));
var launchParameters = JObject.Parse(Base64UrlEncoder.Decode(launchStateParameters["l"].ToString()));
launchParameters.Add("code", code);
newState = launchStateParameters["s"].ToString();
compoundCode = Base64UrlEncoder.Encode(launchParameters.ToString(Newtonsoft.Json.Formatting.None));
}
catch (Exception ex)
{
_logger.LogError("Error parsing launch parameters: {message}", ex.Message);
throw new AadSmartOnFhirProxyBadRequestException(Resources.InvalidLaunchContext, ex);
}
var queryBuilder = new QueryBuilder
{
{ "code", compoundCode },
{ "state", newState },
};
if (!string.IsNullOrEmpty(sessionState))
{
queryBuilder.Add("session_state", sessionState);
}
return(Redirect($"{redirectUrl}{queryBuilder}"));
}
public ActionResult Authorize(
[FromQuery(Name = "response_type")] string responseType,
[FromQuery(Name = "client_id")] string clientId,
[FromQuery(Name = "redirect_uri")] Uri redirectUri,
[FromQuery(Name = "launch")] string launch,
[FromQuery(Name = "scope")] string scope,
[FromQuery(Name = "state")] string state,
[FromQuery(Name = "aud")] string aud)
{
if (string.IsNullOrEmpty(launch))
{
launch = Base64UrlEncoder.Encode("{}");
}
var newStateObj = new JObject
{
{ "s", state },
{ "l", launch },
};
var queryBuilder = new QueryBuilder();
if (!string.IsNullOrEmpty(responseType))
{
queryBuilder.Add("response_type", responseType);
}
if (!string.IsNullOrEmpty(clientId))
{
queryBuilder.Add("client_id", clientId);
}
try
{
var callbackUrl = _urlResolver.ResolveRouteNameUrl(RouteNames.AadSmartOnFhirProxyCallback, new RouteValueDictionary {
{ "encodedRedirect", Base64UrlEncoder.Encode(redirectUri.ToString()) }
});
queryBuilder.Add("redirect_uri", callbackUrl.AbsoluteUri);
}
catch (Exception ex)
{
_logger.LogDebug(ex, "Redirect URL passed to Authorize failed to resolve.");
}
if (!_isAadV2 && !string.IsNullOrEmpty(aud))
{
queryBuilder.Add("resource", aud);
}
else if (!string.IsNullOrEmpty(scope))
{
// Azure AD v2.0 uses fully qualified scopes and does not allow '/' (slash)
// We add qualification to scopes and replace '/' -> '$'
var scopes = scope.Split(' ');
var scopesBuilder = new StringBuilder();
string[] wellKnownScopes = { "profile", "openid", "email", "offline_access" };
foreach (var s in scopes)
{
if (wellKnownScopes.Contains(s))
{
scopesBuilder.Append($"{s} ");
}
else
{
scopesBuilder.Append($"{aud}/{s.Replace('/', '$')} ");
}
}
var newScopes = scopesBuilder.ToString().TrimEnd(' ');
queryBuilder.Add("scope", Uri.EscapeDataString(newScopes));
}
if (!string.IsNullOrEmpty(state))
{
string newState = Base64UrlEncoder.Encode(newStateObj.ToString());
queryBuilder.Add("state", newState);
}
return(Redirect($"{_aadAuthorizeEndpoint}{queryBuilder}"));
}
public CityController()
{
context = new deliveryContext();
queryBuider = new QueryBuilder <City>()
.MapMember("StateName", x => x.State.Name);
}
public void Execute()
{
if (_planManager.TargetDatabase == null)
{
throw new Exception("PlanManager has no TargetDatabase set");
}
var memoryRepo = new MemoryCatalogueRepository();
using (_catalogueRepository.BeginNewTransactedConnection())
{
try
{
//for each skipped table
foreach (var skippedTable in _planManager.SkippedTables)
{
//we might have to refactor or port JoinInfos to these tables so we should establish what the parenthood of them was
foreach (ColumnInfo columnInfo in skippedTable.ColumnInfos)
{
GetNewColumnInfoForOld(columnInfo, true);
}
}
//for each table that isn't being skipped
foreach (var oldTableInfo in _planManager.TableInfos.Except(_planManager.SkippedTables))
{
List <DatabaseColumnRequest> columnsToCreate = new List <DatabaseColumnRequest>();
Dictionary <string, ColumnInfo> migratedColumns = new Dictionary <string, ColumnInfo>(StringComparer.CurrentCultureIgnoreCase);
var querybuilderForMigratingTable = new QueryBuilder(null, null);
//for each column we are not skipping (Drop) work out the endpoint datatype (planner knows this)
foreach (ColumnInfo columnInfo in oldTableInfo.ColumnInfos)
{
var columnPlan = _planManager.GetPlanForColumnInfo(columnInfo);
if (columnPlan.Plan != Plan.Drop)
{
//add the column verbatim to the query builder because we know we have to read it from source
querybuilderForMigratingTable.AddColumn(new ColumnInfoToIColumn(memoryRepo, columnInfo));
string colName = columnInfo.GetRuntimeName();
//if it is being ano tabled then give the table name ANO as a prefix
if (columnPlan.Plan == Plan.ANO)
{
colName = "ANO" + colName;
}
migratedColumns.Add(colName, columnInfo);
columnsToCreate.Add(new DatabaseColumnRequest(colName, columnPlan.GetEndpointDataType(), !columnInfo.IsPrimaryKey)
{
IsPrimaryKey = columnInfo.IsPrimaryKey
});
}
}
SelectSQLForMigrations.Add(oldTableInfo, querybuilderForMigratingTable);
//Create the actual table
var tbl = _planManager.TargetDatabase.CreateTable(oldTableInfo.GetRuntimeName(), columnsToCreate.ToArray());
TableInfo newTableInfo;
ColumnInfo[] newColumnInfos;
//import the created table
TableInfoImporter importer = new TableInfoImporter(_catalogueRepository, tbl);
importer.DoImport(out newTableInfo, out newColumnInfos);
//Audit the parenthood of the TableInfo/ColumnInfos
AuditParenthood(oldTableInfo, newTableInfo);
foreach (ColumnInfo newColumnInfo in newColumnInfos)
{
var oldColumnInfo = migratedColumns[newColumnInfo.GetRuntimeName()];
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
if (columnPlan.Plan == Plan.ANO)
{
newColumnInfo.ANOTable_ID = columnPlan.ANOTable.ID;
newColumnInfo.SaveToDatabase();
}
//if there was a dilution configured we need to setup a virtual DLE load only column of the input type (this ensures RAW has a valid datatype)
if (columnPlan.Plan == Plan.Dilute)
{
//Create a discarded (load only) column with name matching the new columninfo
var discard = new PreLoadDiscardedColumn(_catalogueRepository, newTableInfo, newColumnInfo.GetRuntimeName());
//record that it exists to support dilution and that the data type matches the input (old) ColumnInfo (i.e. not the new data type!)
discard.Destination = DiscardedColumnDestination.Dilute;
discard.SqlDataType = oldColumnInfo.Data_type;
discard.SaveToDatabase();
DilutionOperationsForMigrations.Add(discard, columnPlan.Dilution);
}
AuditParenthood(oldColumnInfo, newColumnInfo);
}
if (DilutionOperationsForMigrations.Any())
{
newTableInfo.IdentifierDumpServer_ID = _planManager.GetIdentifierDumpServer().ID;
newTableInfo.SaveToDatabase();
}
}
NewCatalogue = _planManager.Catalogue.ShallowClone();
NewCatalogue.Name = "ANO" + _planManager.Catalogue.Name;
NewCatalogue.Folder = new CatalogueFolder(NewCatalogue, "\\anonymous" + NewCatalogue.Folder.Path);
NewCatalogue.SaveToDatabase();
AuditParenthood(_planManager.Catalogue, NewCatalogue);
//For each of the old ExtractionInformations (95% of the time that's just a reference to a ColumnInfo e.g. '[People].[Height]' but 5% of the time it's some horrible aliased transform e.g. 'dbo.RunMyCoolFunction([People].[Height]) as BigHeight'
foreach (CatalogueItem oldCatalogueItem in _planManager.Catalogue.CatalogueItems)
{
var oldColumnInfo = oldCatalogueItem.ColumnInfo;
//catalogue item is not connected to any ColumnInfo
if (oldColumnInfo == null)
{
continue;
}
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
//we are not migrating it anyway
if (columnPlan.Plan == Plan.Drop)
{
continue;
}
ColumnInfo newColumnInfo = GetNewColumnInfoForOld(oldColumnInfo);
var newCatalogueItem = oldCatalogueItem.ShallowClone(NewCatalogue);
//and rewire it's ColumnInfo to the cloned child one
newCatalogueItem.ColumnInfo_ID = newColumnInfo.ID;
//If the old CatalogueItem had the same name as it's underlying ColumnInfo then we should use the new one otherwise just copy the old name whatever it was
newCatalogueItem.Name = oldCatalogueItem.Name.Equals(oldColumnInfo.Name) ? newColumnInfo.GetRuntimeName() : oldCatalogueItem.Name;
//add ANO to the front if the underlying column was annoed
if (newColumnInfo.GetRuntimeName().StartsWith("ANO") && !newCatalogueItem.Name.StartsWith("ANO"))
{
newCatalogueItem.Name = "ANO" + newCatalogueItem.Name;
}
newCatalogueItem.SaveToDatabase();
var oldExtractionInformation = oldCatalogueItem.ExtractionInformation;
//if the plan is to make the ColumnInfo extractable
if (columnPlan.ExtractionCategoryIfAny != null)
{
//Create a new ExtractionInformation for the new Catalogue
var newExtractionInformation = new ExtractionInformation(_catalogueRepository, newCatalogueItem, newColumnInfo, newColumnInfo.Name);
newExtractionInformation.ExtractionCategory = columnPlan.ExtractionCategoryIfAny.Value;
newExtractionInformation.SaveToDatabase();
//if it was previously extractable
if (oldExtractionInformation != null)
{
var refactorer = new SelectSQLRefactorer();
//restore the old SQL as it existed in the origin table
newExtractionInformation.SelectSQL = oldExtractionInformation.SelectSQL;
//do a refactor on the old column name for the new column name
refactorer.RefactorColumnName(newExtractionInformation, oldColumnInfo, newColumnInfo.Name, true);
//also refactor any other column names that might be referenced by the transform SQL e.g. it could be a combo column name where forename + surname is the value of the ExtractionInformation
foreach (var kvpOtherCols in _parenthoodDictionary.Where(kvp => kvp.Key is ColumnInfo))
{
//if it's one we have already done, dont do it again
if (Equals(kvpOtherCols.Value, newColumnInfo))
{
continue;
}
//otherwise do a non strict refactoring (don't worry if you don't finda ny references)
refactorer.RefactorColumnName(newExtractionInformation, (ColumnInfo)kvpOtherCols.Key, ((ColumnInfo)(kvpOtherCols.Value)).Name, false);
}
//make the new one exactly as extractable
newExtractionInformation.Order = oldExtractionInformation.Order;
newExtractionInformation.Alias = oldExtractionInformation.Alias;
newExtractionInformation.IsExtractionIdentifier = oldExtractionInformation.IsExtractionIdentifier;
newExtractionInformation.HashOnDataRelease = oldExtractionInformation.HashOnDataRelease;
newExtractionInformation.IsPrimaryKey = oldExtractionInformation.IsPrimaryKey;
newExtractionInformation.SaveToDatabase();
}
AuditParenthood(oldCatalogueItem, newCatalogueItem);
if (oldExtractionInformation != null)
{
AuditParenthood(oldExtractionInformation, newExtractionInformation);
}
}
}
var existingJoinInfos = _catalogueRepository.GetAllObjects <JoinInfo>();
var existingLookups = _catalogueRepository.GetAllObjects <Lookup>();
var existingLookupComposites = _catalogueRepository.GetAllObjects <LookupCompositeJoinInfo>();
//migrate join infos
foreach (JoinInfo joinInfo in _planManager.GetJoinInfosRequiredCatalogue())
{
var newFk = GetNewColumnInfoForOld(joinInfo.ForeignKey);
var newPk = GetNewColumnInfoForOld(joinInfo.PrimaryKey);
//already exists
if (!existingJoinInfos.Any(ej => ej.ForeignKey_ID == newFk.ID && ej.PrimaryKey_ID == newPk.ID))
{
new JoinInfo(_catalogueRepository, newFk, newPk, joinInfo.ExtractionJoinType, joinInfo.Collation); //create it
}
}
//migrate Lookups
foreach (Lookup lookup in _planManager.GetLookupsRequiredCatalogue())
{
//Find the new columns in the ANO table that match the old lookup columns
var newDesc = GetNewColumnInfoForOld(lookup.Description);
var newFk = GetNewColumnInfoForOld(lookup.ForeignKey);
var newPk = GetNewColumnInfoForOld(lookup.PrimaryKey);
//see if we already have a Lookup declared for the NEW columns (unlikely)
Lookup newLookup = existingLookups.SingleOrDefault(l => l.Description_ID == newDesc.ID && l.ForeignKey_ID == newFk.ID);
//create new Lookup that mirrors the old but references the ANO columns instead
if (newLookup == null)
{
newLookup = new Lookup(_catalogueRepository, newDesc, newFk, newPk, lookup.ExtractionJoinType, lookup.Collation);
}
//also mirror any composite (secondary, tertiary join column pairs needed for the Lookup to operate correclty e.g. where TestCode 'HAB1' means 2 different things depending on healthboard)
foreach (LookupCompositeJoinInfo compositeJoin in lookup.GetSupplementalJoins().Cast <LookupCompositeJoinInfo>())
{
var newCompositeFk = GetNewColumnInfoForOld(compositeJoin.ForeignKey);
var newCompositePk = GetNewColumnInfoForOld(compositeJoin.PrimaryKey);
if (!existingLookupComposites.Any(c => c.ForeignKey_ID == newCompositeFk.ID && c.PrimaryKey_ID == newCompositePk.ID))
{
new LookupCompositeJoinInfo(_catalogueRepository, newLookup, newCompositeFk, newCompositePk, compositeJoin.Collation);
}
}
}
//create new data load confguration
LoadMetadata = new LoadMetadata(_catalogueRepository, "Anonymising " + NewCatalogue);
LoadMetadata.EnsureLoggingWorksFor(NewCatalogue);
NewCatalogue.LoadMetadata_ID = LoadMetadata.ID;
NewCatalogue.SaveToDatabase();
if (_planManager.DateColumn != null)
{
LoadProgressIfAny = new LoadProgress(_catalogueRepository, LoadMetadata);
LoadProgressIfAny.OriginDate = _planManager.StartDate;
LoadProgressIfAny.SaveToDatabase();
//date column based migration only works for single TableInfo migrations (see Plan Manager checks)
var qb = SelectSQLForMigrations.Single(kvp => !kvp.Key.IsLookupTable()).Value;
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepo, null,
new[]
{
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " >= @startDate", "After batch start date", "", null),
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " <= @endDate", "Before batch end date", "", null),
}
, FilterContainerOperation.AND);
}
try
{
foreach (QueryBuilder qb in SelectSQLForMigrations.Values)
{
Console.WriteLine(qb.SQL);
}
}
catch (Exception e)
{
throw new Exception("Failed to generate migration SQL", e);
}
_catalogueRepository.EndTransactedConnection(true);
}
catch (Exception ex)
{
_catalogueRepository.EndTransactedConnection(false);
throw new Exception("Failed to create ANO version, transaction rolled back succesfully", ex);
}
}
}
static void Main(string[] args)
{
// Get the database (and create it if it doesn't exist)
var database = new Database("mydb");
// Create a new document (i.e. a record) in the database
string id = null;
using (var mutableDoc = new MutableDocument())
{
mutableDoc.SetFloat("version", 2.0f)
.SetString("type", "SDK");
// Save it to the database
database.Save(mutableDoc);
id = mutableDoc.Id;
}
// Update a document
using (var doc = database.GetDocument(id))
using (var mutableDoc = doc.ToMutable())
{
mutableDoc.SetString("language", "python");
database.Save(mutableDoc);
using (var docAgain = database.GetDocument(id))
{
Console.WriteLine($"Document ID :: {docAgain.Id}");
Console.WriteLine($"Learning {docAgain.GetString("language")}");
}
}
// Create a query to fetch documents of type SDK
// i.e. SELECT * FROM database WHERE type = "SDK"
using (var query = QueryBuilder.Select(SelectResult.All())
.From(DataSource.Database(database))
.Where(Expression.Property("type").EqualTo(Expression.String("SDK"))))
{
// Run the query
var result = query.Execute();
Console.WriteLine($"Number of rows :: {result.Count()}");
}
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("language"))
.From(DataSource.Database(database)))
{
foreach (var result in query.Execute())
{
Console.WriteLine($"Document Name :: {result.GetString("language")}");
}
}
// Create replicator to push and pull changes to and from the cloud
var targetEndpoint = new URLEndpoint(new Uri("ws://localhost:4984/getting-started-db"));
var replConfig = new ReplicatorConfiguration(database, targetEndpoint);
// Add authentication
replConfig.Authenticator = new BasicAuthenticator("john", "pass");
// Create replicator (make sure to add an instance or static variable
// named _Replicator)
var _Replicator = new Replicator(replConfig);
_Replicator.AddChangeListener((sender, args) =>
{
if (args.Status.Error != null)
{
Console.WriteLine($"Error :: {args.Status.Error}");
}
});
//Path.Combine(AppContext.BaseDirectory, "CouchbaseLite");
_Replicator.Start();
}
private void QueryResults()
{
if (!string.IsNullOrEmpty(QueryText))
{
var queryTimer = new System.Diagnostics.Stopwatch();
queryTimer.Start();
_updateSource?.Cancel();
var currentUpdateSource = new CancellationTokenSource();
_updateSource = currentUpdateSource;
var currentCancellationToken = _updateSource.Token;
_updateToken = currentCancellationToken;
var queryText = QueryText.Trim();
var pluginQueryPairs = QueryBuilder.Build(ref queryText, PluginManager.NonGlobalPlugins);
if (pluginQueryPairs != null && pluginQueryPairs.Count > 0)
{
_currentQuery = queryText;
Task.Run(
() =>
{
Thread.Sleep(20);
// Keep track of total number of results for telemetry
var numResults = 0;
// Contains all the plugins for which this raw query is valid
var plugins = pluginQueryPairs.Keys.ToList();
try
{
currentCancellationToken.ThrowIfCancellationRequested();
var resultPluginPair = new List <(List <Result>, PluginMetadata)>();
// To execute a query corresponding to each plugin
foreach (KeyValuePair <PluginPair, Query> pluginQueryItem in pluginQueryPairs)
{
var plugin = pluginQueryItem.Key;
var query = pluginQueryItem.Value;
if (!plugin.Metadata.Disabled)
{
var results = PluginManager.QueryForPlugin(plugin, query);
resultPluginPair.Add((results, plugin.Metadata));
currentCancellationToken.ThrowIfCancellationRequested();
}
}
lock (_addResultsLock)
{
// Using CurrentCultureIgnoreCase since this is user facing
if (queryText.Equals(_currentQuery, StringComparison.CurrentCultureIgnoreCase))
{
Results.Clear();
foreach (var p in resultPluginPair)
{
UpdateResultView(p.Item1, queryText, currentCancellationToken);
currentCancellationToken.ThrowIfCancellationRequested();
}
currentCancellationToken.ThrowIfCancellationRequested();
numResults = Results.Results.Count;
Results.Sort();
Results.SelectedItem = Results.Results.FirstOrDefault();
}
}
currentCancellationToken.ThrowIfCancellationRequested();
UpdateResultsListViewAfterQuery(queryText);
// Run the slower query of the DelayedExecution plugins
currentCancellationToken.ThrowIfCancellationRequested();
Parallel.ForEach(plugins, (plugin) =>
{
try
{
if (!plugin.Metadata.Disabled)
{
Query query;
pluginQueryPairs.TryGetValue(plugin, out query);
var results = PluginManager.QueryForPlugin(plugin, query, true);
currentCancellationToken.ThrowIfCancellationRequested();
if ((results?.Count ?? 0) != 0)
{
lock (_addResultsLock)
{
// Using CurrentCultureIgnoreCase since this is user facing
if (queryText.Equals(_currentQuery, StringComparison.CurrentCultureIgnoreCase))
{
currentCancellationToken.ThrowIfCancellationRequested();
// Remove the original results from the plugin
Results.Results.RemoveAll(r => r.Result.PluginID == plugin.Metadata.ID);
currentCancellationToken.ThrowIfCancellationRequested();
// Add the new results from the plugin
UpdateResultView(results, queryText, currentCancellationToken);
currentCancellationToken.ThrowIfCancellationRequested();
numResults = Results.Results.Count;
Results.Sort();
Results.SelectedItem = Results.Results.FirstOrDefault();
}
}
currentCancellationToken.ThrowIfCancellationRequested();
UpdateResultsListViewAfterQuery(queryText, true);
}
}
}
catch (OperationCanceledException)
{
// nothing to do here
}
});
}
catch (OperationCanceledException)
{
// nothing to do here
}
queryTimer.Stop();
var queryEvent = new LauncherQueryEvent()
{
QueryTimeMs = queryTimer.ElapsedMilliseconds,
NumResults = numResults,
QueryLength = queryText.Length,
};
PowerToysTelemetry.Log.WriteEvent(queryEvent);
}, currentCancellationToken);
}
}
else
{
_updateSource?.Cancel();
_currentQuery = _emptyQuery;
Results.SelectedItem = null;
Results.Visibility = Visibility.Hidden;
Task.Run(() =>
{
lock (_addResultsLock)
{
Results.Clear();
}
});
}
}
public async Task <IList <Post> > ListStickyPosts(QueryBuilder builder)
{
// default values
// int page = 1, int per_page = 10, int offset = 0, Post.OrderBy orderby = Post.OrderBy.date
return(await GetRequest <Post[]>(builder.SetRootUrl($"{defaultPath}posts?sticky=true").ToString(), false).ConfigureAwait(false));
}
public async Task <IList <Post> > ListPostsBySearch(string searchTerm, QueryBuilder builder)
{
// default values
// int page = 1, int per_page = 10, int offset = 0, Post.OrderBy orderby = Post.OrderBy.date
return(await GetRequest <Post[]>(builder.SetRootUrl($"{defaultPath}posts?search={searchTerm}").ToString(), false).ConfigureAwait(false));
}
public async Task <IList <Post> > ListPostsByCategory(int categoryId, QueryBuilder builder)
{
// default values
// int page = 1, int per_page = 10, int offset = 0, Post.OrderBy orderby = Post.OrderBy.date
return(await GetRequest <Post[]>(builder.SetRootUrl($"{defaultPath}posts?categories={categoryId}").ToString(), false).ConfigureAwait(false));
}
public void Can_parse_LessThan_on_date()
{
var query = QueryBuilder.BuildQuery("Birthday:{NULL TO 20100515000000000}", new RavenPerFieldAnalyzerWrapper(new StandardAnalyzer(Version.LUCENE_29)));
Assert.Equal("Birthday:{* TO 20100515000000000}", query.ToString());
}
public void Can_parse_disjunctions_within_conjunction_query_with_date_range()
{
var query = QueryBuilder.BuildQuery("(Name:\"Simple Phrase\" OR Name:SingleTerm) AND (Age_Range:3 OR Birthday:[NULL TO 20100515000000000])", new RavenPerFieldAnalyzerWrapper(new StandardAnalyzer(Version.LUCENE_29)));
Assert.Equal("+(Name:\"simple phrase\" Name:singleterm) +(Age_Range:3 Birthday:[* TO 20100515000000000])", query.ToString());
}
public void Can_parse_disjunctions_within_conjunction_query_with_escaped_field()
{
var query = QueryBuilder.BuildQuery("(Name:\"Escaped\\+\\-\\&\\|\\!\\(\\)\\{\\}\\[\\]\\^\\\"\\~\\*\\?\\:\\\\Phrase\" OR Name:SingleTerm) AND (Age_Range:3 OR Birthday:20100515000000000)", new RavenPerFieldAnalyzerWrapper(new StandardAnalyzer(Version.LUCENE_29)));
Assert.Equal("+(Name:\"escaped phrase\" Name:singleterm) +(Age_Range:3 Birthday:20100515000000000)", query.ToString());
}
public void Monitor_Should_Not_Leak_Connections_Test()
{
var presetQueryCassandraObjects = QueryBuilder.GetObjects(where => where.Namespace.Like("Cassandra*")
& where.Namespace.NotLike("Cassandra*Tests"));
var memoryBeforeAll = dotMemory.Check(memory =>
{
TestContext.WriteLine("Before cluster creation");
var cassandraPackage = memory.GetObjects(presetQueryCassandraObjects);
var socketObjects = memory.GetObjects(where => where.Type.Is <Socket>());
TestContext.WriteLine("=== Cassandra driver Object count: " + cassandraPackage.ObjectsCount);
TestContext.WriteLine("=== Number of Socket objects: " + socketObjects.ObjectsCount);
});
var socketOptions = new SocketOptions().SetReadTimeoutMillis(1).SetConnectTimeoutMillis(1);
var builder = Cluster.Builder()
.AddContactPoint(TestHelper.UnreachableHostAddress)
.WithSocketOptions(socketOptions);
const int length = 1000;
using (var cluster = builder.Build())
{
var firstExcepetion = Assert.Throws <NoHostAvailableException>(() => cluster.Connect());
Assert.AreEqual(1, firstExcepetion.Errors.Count);
TestContext.WriteLine("After first attempt");
var memoryBeforeAttempts = dotMemory.Check(memory =>
{
var cassandraPackage = memory.GetObjects(presetQueryCassandraObjects);
var socketObjects = memory.GetObjects(where => where.Type.Is <Socket>());
TestContext.WriteLine("=== Cassandra driver Object count: " + cassandraPackage.ObjectsCount);
TestContext.WriteLine("=== Number of Socket objects: " + socketObjects.ObjectsCount);
});
for (var i = 0; i < length; i++)
{
var ex = Assert.Throws <NoHostAvailableException>(() => cluster.Connect());
Assert.AreEqual(1, ex.Errors.Count);
}
GC.Collect();
dotMemory.Check(memory =>
{
TestContext.WriteLine("After " + length + " more attempts");
var cassandraPackage = memory.GetObjects(presetQueryCassandraObjects);
var socketObjects = memory.GetObjects(where => where.Type.Is <Socket>());
TestContext.WriteLine("=== Cassandra driver Object count: " + cassandraPackage.ObjectsCount);
TestContext.WriteLine("=== Number of Socket objects: " + socketObjects.ObjectsCount);
var leaked = memory.GetDifference(memoryBeforeAttempts)
.GetSurvivedObjects(o => o.LeakedOnEventHandler())
.GetObjects(presetQueryCassandraObjects).ObjectsCount;
TestContext.WriteLine("=== Difference: Leaked: " + leaked);
Assert.That(leaked, Is.EqualTo(0));
var newObjects = memory.GetDifference(memoryBeforeAttempts)
.GetNewObjects().GetObjects(presetQueryCassandraObjects).ObjectsCount;
TestContext.WriteLine("=== Difference: New Objects: " + newObjects);
Assert.That(newObjects, Is.LessThanOrEqualTo(1), "Increased the amount of new objects");
Assert.That(memory.GetDifference(memoryBeforeAll)
.GetNewObjects(o => o.Type.Is <Socket>()).ObjectsCount, Is.LessThanOrEqualTo(1),
"Should have clean all socket objects");
});
}
GC.Collect();
dotMemory.Check(memory =>
{
TestContext.WriteLine("After cluster disposal");
var cassandraPackage = memory.GetObjects(presetQueryCassandraObjects);
var socketObjects = memory.GetObjects(where => where.Type.Is <Socket>());
TestContext.WriteLine("=== Cassandra driver Object count: " + cassandraPackage.ObjectsCount);
TestContext.WriteLine("=== Number of Socket objects: " + socketObjects.ObjectsCount);
var leaked = memory.GetDifference(memoryBeforeAll)
.GetNewObjects(o => o.LeakedOnEventHandler())
.GetObjects(presetQueryCassandraObjects).ObjectsCount;
TestContext.WriteLine("=== Difference: Leaked: " + leaked);
Assert.That(leaked, Is.EqualTo(0));
var newObjects = memory.GetDifference(memoryBeforeAll)
.GetNewObjects().GetObjects(presetQueryCassandraObjects).ObjectsCount;
TestContext.WriteLine("=== Difference: New Objects: " + newObjects);
//Assert.That(newObjects, Is.EqualTo(0), "Should have clean all created objects");
Assert.That(memory.GetDifference(memoryBeforeAll)
.GetSurvivedObjects(o => o.Type.Is <Socket>()).ObjectsCount, Is.LessThanOrEqualTo(4), "");
});
}
public CategoryRepository(QueryBuilder queryBuilder) : base(queryBuilder)
{
}
public TableNameResolver(ISQLTemplate template, QueryBuilder queryBuilder)
{
SqlTemplate = template;
Builder = queryBuilder;
}
public JsonResult GetList(QueryBuilder qb)
{
var data = entities.Set <T_Employee>().Where(c => c.IsDeleted == "0" && c.EmploymentWay == "外聘员工").WhereToGridData(qb);
return(Json(data));
}
public void Can_parse_conjunctions_within_disjunction_query_with_int_range()
{
var query = QueryBuilder.BuildQuery("(Name:\"Simple Phrase\" AND Name:SingleTerm) OR (Age_Range:{0x00000003 TO NULL} AND Birthday:20100515000000000)", new RavenPerFieldAnalyzerWrapper(new StandardAnalyzer(Version.LUCENE_29)));
Assert.Equal("(+Name:\"simple phrase\" +Name:singleterm) (+Age_Range:{3 TO 2147483647} +Birthday:20100515000000000)", query.ToString());
}
/// <summary>
/// Create a URI
/// </summary>
/// <param name="baseUri">Base URI</param>
/// <param name="relativeUri">Relative URI</param>
/// <param name="queryParameters">Collection of query parameters</param>
/// <returns></returns>
public static string Create(string baseUri, string relativeUri, ICollection <KeyValuePair <string, string> > queryParameters)
{
var queryBuilder = new QueryBuilder(queryParameters);
return(Create(baseUri, relativeUri, queryBuilder.ToQueryString().Value));
}
/// <summary>
/// Creates a SQL Statement for merge-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMergeAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
int batchSize = 10,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Check the primary field
if (primaryField == null)
{
throw new PrimaryFieldNotFoundException($"MySql is using the primary key as qualifier for merge operation.");
}
// Check the qualifiers
if (qualifiers?.Any() == true)
{
var others = qualifiers.Where(f => !string.Equals(f.Name, primaryField.Name, StringComparison.OrdinalIgnoreCase));
if (others.Any() == true)
{
throw new InvalidQualifiersException($"MySql is using the primary key as qualifier for merge operation. " +
$"Consider creating 'PrimaryKey' in the {tableName} and set the 'qualifiers' to NULL.");
}
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Set the return value
var result = (string)null;
// Clear the builder
builder.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
// Build the query
builder
.Insert()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(fields, DbSetting)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields, index, DbSetting)
.CloseParen()
.WriteText("ON DUPLICATE KEY")
.Update()
.FieldsAndParametersFrom(fields, index, DbSetting)
.End();
// Check both primary and identity
if (identityField != null && !string.Equals(identityField.Name, primaryField.Name, StringComparison.OrdinalIgnoreCase))
{
result = $"(CASE WHEN {identityField.Name.AsParameter(index, DbSetting)} > 0 THEN " +
$"{identityField.Name.AsParameter(index, DbSetting)} ELSE " +
$"{primaryField.Name.AsParameter(index, DbSetting)} END)";
}
else
{
result = $"COALESCE({primaryField.Name.AsParameter(index, DbSetting)}, LAST_INSERT_ID())";
}
if (!string.IsNullOrEmpty(result))
{
// Set the result
builder
.Select()
.WriteText(string.Concat(result, " AS ", "Id".AsQuoted(DbSetting), ","))
.WriteText(string.Concat($"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index}", " AS ", "OrderColumn".AsQuoted(DbSetting)));
}
// End the builder
builder
.End();
}
// Return the query
return(builder.GetString());
}
public void E04_EstService_GetExportData_Exists_in_BAMService_UpdateDeployedToUser()
{
var updateAssetStatus = EST_HWAssetStatus.Deployed;
var estService = new EST_Service();
var queryBuilder = new QueryBuilder
{
StartDateString = "01/01/2017",
EndDateString = "30/01/2017",
PageCount = 100
};
// Get Records from Esteem System
var dataExport = estService.GetExportData(queryBuilder);
var originalModifiedDate = new DateTime();
var updatedModifiedDate = new DateTime();
Assert.IsNotNull(dataExport, "DataExport model returned null");
Assert.IsNotNull(dataExport.DeployedToBAMUserList, "DeployedToBAMUserList model returned null");
Assert.IsTrue(dataExport.DeployedToBAMUserList.Any(), "DeployedToBAMUserList didn't return any items");
var rand = new Random();
// Select a Random record
var assetList = dataExport.DeployedToBAMUserList;
var asset = assetList[rand.Next(assetList.Count)];
// Get respective record from BAM Api
var hardwareAssetService = new BAM_HardwareAssetServices();
var bamAssetList = hardwareAssetService.GetHardwareAsset_Full(asset.SerialNumber);
Assert.IsNotNull(bamAssetList, "BAM Asset is null");
Assert.IsTrue(bamAssetList.Count == 1, "Get BAM Asset didn't return 1 item " + bamAssetList.Count.ToString());
var bamAsset = bamAssetList.First();
Assert.IsTrue(bamAsset.SerialNumber.Contains(asset.SerialNumber), "BAM and Esteem Asset Serial Numbers do not match");
// Set the BAM record to new AssetStatus
var newHardwareAsset = hardwareAssetService.SetHardwareAssetStatus(bamAsset, updateAssetStatus);
// Get respective User record from BAM Api
IBAM_UserService userService = new BAM_UserService();
var user = userService.GetUser(asset.RequestUser);
Assert.IsNotNull(user, "User item is null");
Assert.IsTrue(user.Name.Contains(asset.RequestUser), "user record is not the same");
newHardwareAsset = hardwareAssetService.SetUser(newHardwareAsset, user);
// Update BAM record on BAM Api
var hardwareAssetList = hardwareAssetService.UpdateTemplate(newHardwareAsset, bamAsset);
Assert.IsNotNull(hardwareAssetList, "Return list is null");
Assert.IsTrue(hardwareAssetList.Count > 1, "Return list doesn't include 2 records");
// Get the newly updated BAM record
var updatedHardwareAsset = hardwareAssetService.GetHardwareAsset_Full(asset.SerialNumber).FirstOrDefault();
Assert.IsNotNull(updatedHardwareAsset, "Updated Asset is null");
Assert.IsTrue(updatedHardwareAsset.SerialNumber == asset.SerialNumber, "SerialNumbers don't match");
updatedModifiedDate = (DateTime)updatedHardwareAsset.LastModified;
// Check Updates worked successfully
Assert.IsTrue(updatedModifiedDate != originalModifiedDate, "Original and Updated LastModified Date are the same");
Assert.IsTrue(updatedModifiedDate > originalModifiedDate, "Updated LastModified Date is not greater that the Original");
Assert.IsTrue(hardwareAssetList[0].HardwareAssetStatus.Name == updateAssetStatus.ToBAMString(), "Updated Asset status doesn't equal BAM AssetStatus Enum");
Assert.IsNotNull(updatedHardwareAsset.Target_HardwareAssetHasPrimaryUser, "User record is null");
Assert.IsTrue(updatedHardwareAsset.Target_HardwareAssetHasPrimaryUser.DisplayName.Contains(asset.RequestUser), "Updated User is not the same :" + updatedHardwareAsset.Target_HardwareAssetHasPrimaryUser.DisplayName + " " + asset.RequestUser);
}
public Business.Models.drugsUnqualificationQuery getDrugsUnqualificationQueryByFlowID(Guid flowID)
{
try
{
var queryBuilder = QueryBuilder.Create <DrugsUnqualication>().Equals(p => p.flowID, flowID)
.Equals(p => p.Deleted, false);
var all = BusinessHandlerFactory.RepositoryProvider.Db.DrugsUnqualications.Where(queryBuilder.Expression);
if (all.Where(r => r.DrugInventoryRecordID.Equals(Guid.Empty)).Count() <= 0)
{
var c = from i in all
join u in RepositoryProvider.Db.Users.Include(r => r.Employee) on i.createUID equals u.Id
join d in RepositoryProvider.Db.DrugInventoryRecords on i.DrugInventoryRecordID equals d.Id
into left
from l in left.DefaultIfEmpty()
join wz in RepositoryProvider.Db.WarehouseZones on l.WarehouseZoneId equals wz.Id
join w in RepositoryProvider.Db.Warehouses on wz.WarehouseId equals w.Id
join pid in RepositoryProvider.Db.PurchaseInInventeryOrderDetails on l.PurchaseInInventeryOrderDetailId equals pid.Id into left2
from ll in left2.DefaultIfEmpty()
select new Business.Models.drugsUnqualificationQuery
{
batchNo = i.batchNo,
Origin = i.Origin,
creater = u.Employee.Name,
createTime = i.createTime,
CurrentInventoryCount = l == null ? 0m : l.CanSaleNum,
Description = i.Description,
Dosage = i.DosageType,
drugName = i.drugName,
FactoryName = i.factoryName,
id = i.Id,
flowID = i.flowID,
InInventoryDate = ll == null ? DateTime.Now : ll.ArrivalDateTime,
InventoryDate = 0,
OutValidDate = i.ExpireDate,
PurchaseOrderDocumentNumber = i.PurchaseOrderDocumentNumber,
quantity = i.quantity,
Specific = i.Specific,
SupplyUnitName = i.Supplyer,
updateTime = i.updateTime,
Warehouse = l == null ? "暂未入库" : w.Name,
WarehouseZone = l == null ? "暂未入库" : wz.Name,
productDate = i.produceDate,
Source = i.source
};
return(c.FirstOrDefault());
}
else
{
var c = from i in all
join u in RepositoryProvider.Db.Users.Include(r => r.Employee) on i.createUID equals u.Id
select new Business.Models.drugsUnqualificationQuery
{
batchNo = i.batchNo,
Origin = i.Origin,
creater = u.Employee.Name,
createTime = i.createTime,
CurrentInventoryCount = 0,
Description = i.Description,
Dosage = i.DosageType,
drugName = i.drugName,
FactoryName = i.factoryName,
id = i.Id,
flowID = i.flowID,
InInventoryDate = DateTime.Now,
InventoryDate = 0,
OutValidDate = i.ExpireDate,
PurchaseOrderDocumentNumber = i.PurchaseOrderDocumentNumber,
quantity = i.quantity,
Specific = i.Specific,
SupplyUnitName = i.Supplyer,
updateTime = i.updateTime,
Warehouse = "验收产生，暂未入库",
WarehouseZone = "验收产生，暂未入库",
productDate = i.produceDate,
Source = i.source
};
return(c.FirstOrDefault());
}
}
catch (Exception ex)
{
return(null);
}
}
public virtual bool Delete(Expression <Func <T, bool> > predicate = null)
{
var session = GetSession();
return(_dapperImplementor.Delete <T>(session, QueryBuilder <T> .FromExpression(predicate), session.Transaction, null));
}
public void SeGerarScriptQueVerificaExistenciaSemDicionarioDeveGerarScriptCorretamente()
{
var builder = new QueryBuilder();
builder.DefinirTabela("Tabela");
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]");
builder.DefinirLimite(15);
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]");
builder.DefinirLimite(20);
builder.AdicionarRelacionamento("inner join Relacionamento on Tabela.campo = Relacionamento.campo");
builder.AdicionarRelacionamento("left join Parente on Tabela.Relacao = Parente.Relacao");
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]" +
"inner join Relacionamento on Tabela.campo = Relacionamento.campo " +
"left join Parente on Tabela.Relacao = Parente.Relacao ");
builder.DefinirLimite(25);
builder.AdicionarCondicao("campoA", (int)Operadores.Igual, 1);
builder.AdicionarCondicao("campoB", (int)Operadores.Igual, 2);
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]" +
"inner join Relacionamento on Tabela.campo = Relacionamento.campo " +
"left join Parente on Tabela.Relacao = Parente.Relacao " +
"where([campoA]=@_p0)and([campoB]=@_p1) ");
builder.DefinirLimite(30);
builder.AdicionarAgrupamento("Tabela.CampoX");
builder.AdicionarAgrupamento("Tabela.CampoY");
builder.AdicionarAgrupamento("Tabela.CampoZ");
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]" +
"inner join Relacionamento on Tabela.campo = Relacionamento.campo " +
"left join Parente on Tabela.Relacao = Parente.Relacao " +
"where([campoA]=@_p0)and([campoB]=@_p1) " +
"group by Tabela.CampoX,Tabela.CampoY,Tabela.CampoZ ");
builder.DefinirLimite(35);
builder.AdicionarCondicaoAgrupamento("Count(*)>0");
builder.AdicionarCondicaoAgrupamento("Max(Tabela.Quantidade)>1000");
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]" +
"inner join Relacionamento on Tabela.campo = Relacionamento.campo " +
"left join Parente on Tabela.Relacao = Parente.Relacao " +
"where([campoA]=@_p0)and([campoB]=@_p1) " +
"group by Tabela.CampoX,Tabela.CampoY,Tabela.CampoZ " +
"having(Count(*)>0)and(Max(Tabela.Quantidade)>1000) ");
builder.AdicionarOrdemDescendente("Tabela.OrdemB");
builder.AdicionarOrdem("Tabela.OrdemA");
builder.GerarScriptExistencia(null)
.Should()
.Be("select top 1 1 from[Tabela]" +
"inner join Relacionamento on Tabela.campo = Relacionamento.campo " +
"left join Parente on Tabela.Relacao = Parente.Relacao " +
"where([campoA]=@_p0)and([campoB]=@_p1) " +
"group by Tabela.CampoX,Tabela.CampoY,Tabela.CampoZ " +
"having(Count(*)>0)and(Max(Tabela.Quantidade)>1000) " +
"order by Tabela.OrdemB DESC,Tabela.OrdemA ");
}
protected override SortBuilder <Blacklist> GetPagedQuery(QueryBuilder <Blacklist> query, PagingSpec <Blacklist> pagingSpec)
{
var baseQuery = query.Join <Blacklist, Movie>(JoinType.Inner, h => h.Movie, (h, s) => h.MovieId == s.Id);
return(base.GetPagedQuery(baseQuery, pagingSpec));
}
private void QueryResults()
{
if (!string.IsNullOrEmpty(QueryText))
{
var queryTimer = new System.Diagnostics.Stopwatch();
queryTimer.Start();
_updateSource?.Cancel();
var currentUpdateSource = new CancellationTokenSource();
_updateSource = currentUpdateSource;
var currentCancellationToken = _updateSource.Token;
_updateToken = currentCancellationToken;
var query = QueryBuilder.Build(QueryText.Trim(), PluginManager.NonGlobalPlugins);
if (query != null)
{
_lastQuery = query;
Task.Run(() =>
{
Thread.Sleep(20);
var plugins = PluginManager.ValidPluginsForQuery(query);
try
{
currentCancellationToken.ThrowIfCancellationRequested();
var resultPluginPair = new List <(List <Result>, PluginMetadata)>();
foreach (PluginPair plugin in plugins)
{
if (!plugin.Metadata.Disabled)
{
var results = PluginManager.QueryForPlugin(plugin, query);
resultPluginPair.Add((results, plugin.Metadata));
currentCancellationToken.ThrowIfCancellationRequested();
}
}
lock (_addResultsLock)
{
RemoveOldQueryResults(query);
foreach (var p in resultPluginPair)
{
UpdateResultView(p.Item1, p.Item2, query, currentCancellationToken);
currentCancellationToken.ThrowIfCancellationRequested();
}
currentCancellationToken.ThrowIfCancellationRequested();
Results.Results.Sort();
}
currentCancellationToken.ThrowIfCancellationRequested();
Application.Current.Dispatcher.BeginInvoke(new Action(() =>
{
if (query.RawQuery == _lastQuery.RawQuery)
{
Results.Results.NotifyChanges();
}
if (Results.Results.Count > 0)
{
Results.Visibility = Visibility.Visible;
Results.SelectedIndex = 0;
}
else
{
Results.Visibility = Visibility.Hidden;
}
}));
}
catch (OperationCanceledException)
{
// nothing to do here
}
queryTimer.Stop();
var queryEvent = new LauncherQueryEvent()
{
QueryTimeMs = queryTimer.ElapsedMilliseconds,
NumResults = Results.Results.Count,
QueryLength = query.RawQuery.Length
};
PowerToysTelemetry.Log.WriteEvent(queryEvent);
}, currentCancellationToken);
}
}
else
{
_updateSource?.Cancel();
_lastQuery = _emptyQuery;
Results.SelectedItem = null;
Results.Visibility = Visibility.Hidden;
Results.Clear();
}
}
private void QueryResults()
{
if (!string.IsNullOrEmpty(QueryText))
{
var queryTimer = new System.Diagnostics.Stopwatch();
queryTimer.Start();
_updateSource?.Cancel();
var currentUpdateSource = new CancellationTokenSource();
_updateSource = currentUpdateSource;
var currentCancellationToken = _updateSource.Token;
_updateToken = currentCancellationToken;
ProgressBarVisibility = Visibility.Hidden;
_isQueryRunning = true;
var query = QueryBuilder.Build(QueryText.Trim(), PluginManager.NonGlobalPlugins);
if (query != null)
{
// handle the exclusiveness of plugin using action keyword
RemoveOldQueryResults(query);
_lastQuery = query;
var plugins = PluginManager.ValidPluginsForQuery(query);
Task.Run(() =>
{
// so looping will stop once it was cancelled
var parallelOptions = new ParallelOptions {
CancellationToken = currentCancellationToken
};
try
{
Parallel.ForEach(plugins, parallelOptions, plugin =>
{
if (!plugin.Metadata.Disabled)
{
var results = PluginManager.QueryForPlugin(plugin, query);
if (Application.Current.Dispatcher.CheckAccess())
{
UpdateResultView(results, plugin.Metadata, query);
}
else
{
Application.Current.Dispatcher.BeginInvoke(new Action(() =>
{
UpdateResultView(results, plugin.Metadata, query);
}));
}
}
});
}
catch (OperationCanceledException)
{
// nothing to do here
}
// this should happen once after all queries are done so progress bar should continue
// until the end of all querying
_isQueryRunning = false;
if (currentUpdateSource == _updateSource)
{ // update to hidden if this is still the current query
ProgressBarVisibility = Visibility.Hidden;
}
queryTimer.Stop();
var queryEvent = new LauncherQueryEvent()
{
QueryTimeMs = queryTimer.ElapsedMilliseconds,
NumResults = Results.Results.Count,
QueryLength = query.RawQuery.Length
};
PowerToysTelemetry.Log.WriteEvent(queryEvent);
}, currentCancellationToken);
}
}
else
{
Results.SelectedItem = null;
Results.Clear();
Results.Visibility = Visibility.Collapsed;
}
}
public GroupByQueryable1(IDbSet db, QueryBody queryBody, QueryBuilder queryBuilder, Expression expression) : base(db, queryBody, queryBuilder, expression)
{
}
public Player GetPlayerByNickName(string nickName)
{
var query = new QueryBuilder <Player>();
return(_mongoRepository.GetRepository <Player>().Collection.Find(query.EQ(x => x.NickName, nickName)).FirstOrDefault());
}
public new IQueryProvider <T> From(QueryBuilder <T> builder)
{
return((IQueryProvider <T>)base.From(builder));
}
