public void WheresMyBuilder()
{
var expectedWhere =
@"WHERE ((Name = @p1) AND (Age < @p2)) AND (Age > @p3) AND (Name != @p4) AND (TheDate = @p5) AND (WhatIsIt = @p6)";
var search = new TestSearch
{
Name = "Person"
};
var builder = new WhereBuilder<TestType>();
var theDate = DateTime.Now.AddDays(-1);
builder.Add(x => x.Name == "test" && x.Age < 3);
builder.Add(x => x.Age > 11);
builder.Add(x => x.Name != search.Name);
builder.Add(x => x.TheDate == theDate);
builder.Add(x => x.WhatIsIt == TestEnum.TheSetting);
builder.AddParam("@pageIndex", 20);
Assert.Contains(expectedWhere, builder.Build());
Assert.Equal(20, builder.GetParamsForTesting()["@pageIndex"]);
Assert.Equal("test", builder.GetParamsForTesting()["@p1"]);
Assert.Equal(3, builder.GetParamsForTesting()["@p2"]);
Assert.Equal(11, builder.GetParamsForTesting()["@p3"]);
Assert.Equal("Person", builder.GetParamsForTesting()["@p4"]);
Assert.Equal(theDate, builder.GetParamsForTesting()["@p5"]);
Assert.Equal(0, builder.GetParamsForTesting()["@p6"]);
}
public void ShouldCreateEqualsWhereQuery()
{
var builder =
new WhereBuilder<Model.Model>()
.Where("Id", new Equals<int>(1))
.ToString();
builder.Should().Be("WHERE [Id] = 1");
}
public void ShouldCreateGreaterThenWhereQuery()
{
var builder =
new WhereBuilder<Model.Model>()
.Where("Id", new GreaterThen<int>(1))
.ToString();
builder.Should().Be("WHERE [Id] > 1");
}
public void ShouldCreateIsNullWhereQuery()
{
var builder =
new WhereBuilder<Model.Model>()
.Where("Id", new IsNull<int>())
.ToString();
builder.Should().Be("WHERE [Id] IS NULL");
}
public void ShouldCreateAndWhereQuery()
{
var builder =
new WhereBuilder<Model.Model>()
.Where("Id", new IsNotNull<int>())
.AndWhere("Id", new IsNull<int>())
.ToString();
builder.Should().Be("WHERE ([Id] IS NOT NULL AND [Id] IS NULL)");
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, true);
int idValue = 7;
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob";
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new UpdateQuery(new SqlServerDialect(), columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE [dbo].[People]"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P4"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[Name] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.AreEqual(command.Parameters["@P4"].Value, 1);
Assert.AreEqual(command.Parameters["@P5"].Value, 7);
Assert.AreEqual(command.Parameters["@P6"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P7"].Value, "Bob");
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, false, true);
int idValue = 7;
var where = new WhereBuilder<Person>(command, p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob", false);
IQuery query = new UpdateQuery(columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE dbo.People"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[ID] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.IsTrue(queryText.Contains("[Name] = @P8"));
Assert.AreEqual(command.Parameters["@P5"].Value, 1);
Assert.AreEqual(command.Parameters["@P6"].Value, 7);
Assert.AreEqual(command.Parameters["@P7"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P8"].Value, "Bob");
}
public IList <ReportHutangPembelianProdukDetail> DetailGetByTanggal(DateTime tanggalMulai, DateTime tanggalSelesai)
{
IList <ReportHutangPembelianProdukDetail> oList = new List <ReportHutangPembelianProdukDetail>();
try
{
var whereBuilder = new WhereBuilder(SQL_TEMPLATE_DETAIL);
whereBuilder.Add("t_beli_produk.tanggal_tempo IS NOT NULL");
whereBuilder.Add("((t_beli_produk.total_nota - t_beli_produk.diskon + t_beli_produk.ppn) - t_beli_produk.total_pelunasan) <> 0");
whereBuilder.Add("t_beli_produk.tanggal BETWEEN @tanggalMulai AND @tanggalSelesai");
oList = _context.db.Query <ReportHutangPembelianProdukDetail>(whereBuilder.ToSql(), new { tanggalMulai, tanggalSelesai })
.ToList();
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(oList);
}
public WhereBuilder GetUserRoleSQL()
{
string sql = "select * from (select b.USER_ROLE_ID,b.USER_ID ,a.role_id,a.role_name,a.role_type,case a.role_type when 0 then '系统管理' when 1 then '普通用户' end as role_type_name,a.remark ";
sql += " from sec_role a left join SEC_USER_ROLE b on a.role_id=b.ROLE_ID ";
string userid = "";
ligerGrid.HasKey("id", ref userid);
if (!string.IsNullOrEmpty(userid))
{
sql += "and b.USER_ID='" + userid + "'";
}
sql += " where 1=1 ";
if (!string.IsNullOrEmpty(systemName))
{
sql += " and a.system_name='" + systemName + "' ";
}
sql += ")tb";
WhereBuilder wb = new WhereBuilder(sql);
return(wb);
}
public virtual bool ExportExcel(LigerGrid grid, ref ExcelConvertHelper.ExcelContext context, ref string Error)
{
SecRoleQuery query = new SecRoleQuery();
query.ligerGrid = grid;
eContract.Common.ExcelConvertHelper.ColumnList columns = new eContract.Common.ExcelConvertHelper.ColumnList();
columns.Add(SecRoleTable.C_ROLE_ID, "角色编号");
columns.Add("role_type_name", "角色类型");
columns.Add(SecRoleTable.C_ROLE_NAME, "角色名称");
columns.Add(SecRoleTable.C_REMARK, "角色描述");
context = new ExcelConvertHelper.ExcelContext();
context.FileName = "RoleList" + ".xls";
context.Title = "角色列表";
WhereBuilder where = query.ParseSQL();
context.Data = DataAccess.Select(where.SQLString, where.Parameters).Tables[0];
if (columns != null)
{
context.Columns.Add(columns);
}
CacheHelper.Instance.Set(WebCaching.UserId + "_" + ExcelHelper.EXPORT_EXCEL_CONTEXT, context);
return(true);
}
public IList <ReportHutangPembelianProdukHeader> GetByBulan(int bulanAwal, int bulanAkhir, int tahun)
{
IList <ReportHutangPembelianProdukHeader> oList = new List <ReportHutangPembelianProdukHeader>();
try
{
var whereBuilder = new WhereBuilder(SQL_TEMPLATE_HEADER);
whereBuilder.Add("t_beli_produk.tanggal_tempo IS NOT NULL");
whereBuilder.Add("(EXTRACT(MONTH FROM t_beli_produk.tanggal) BETWEEN @bulanAwal AND @bulanAkhir)");
whereBuilder.Add("EXTRACT(YEAR FROM t_beli_produk.tanggal) = @tahun");
oList = _context.db.Query <ReportHutangPembelianProdukHeader>(whereBuilder.ToSql(), new { bulanAwal, bulanAkhir, tahun })
.ToList();
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(oList);
}
public override IAI_ORGANIZATION Insert(PRPM_IN401030UV01 addEntity)
{
var data = new IAI_ORGANIZATION
{
organization_id = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.id?.extension,
organization_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.code?.code,
role_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.name,
addr = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.addr,
telecom = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.telecom?.value,
effectivetime_high = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.effectiveTime?.high?.value,
effectivetime_low = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.effectiveTime?.low?.value,
name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.assignedPrincipalOrganization?.name,
scoper2_id = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.assignedPrincipalOrganization?.asAffiliate?.scoper2?.id?.extension,
scoper2_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.assignedEntity?.assignedPrincipalOrganization?.asAffiliate?.scoper2?.name,
};
var wb = new WhereBuilder();
wb.And("organization_id", data.organization_id, true);
if (DBaser.QueryValue <int>("select count(*) from IAI_ORGANIZATION", wb) > 0)
{
throw new Exception($@"主键冲突{data.organization_id}已存在。");
}
DBaser.Insert("IAI_ORGANIZATION");
DBaser["organization_id"] = data.organization_id;
DBaser["organization_code"] = data.organization_code;
DBaser["role_name"] = data.role_name;
DBaser["addr"] = data.addr;
DBaser["telecom"] = data.telecom;
DBaser["effectivetime_high"] = data.effectivetime_high;
DBaser["effectivetime_low"] = data.effectivetime_low;
DBaser["name"] = data.name;
DBaser["scoper2_id"] = data.scoper2_id;
DBaser["scoper2_name"] = data.scoper2_name;
DBaser.AcceptChanges();
return(data);
}
public TEntity SingleOrDefault(Expression <Func <TEntity, bool> > expression, IDbTransaction ts = null)
{
using (_connection)
{
//build ra câu select
var query = new StringBuilder(CommandBuilder.CreateSelectCommand(_entityInfo));
//lấy expression để build ra câu where
var builder = new WhereBuilder <TEntity>();
var wherePart = builder.ToSql(expression);
if (wherePart.Parameters == null)
{
throw new Exception("Can not find any parameter in where part.");
}
query.Append($" WHERE {wherePart.RawSql}");
DynamicParameters parameter = new DynamicParameters();
parameter.AddDynamicParams(wherePart.Parameters);
return(_connection.QuerySingleOrDefault <TEntity>(query.ToString(), parameter, ts));
}
}
public IList <ReportPiutangPenjualanProdukDetail> DetailGetByBulan(int bulan, int tahun)
{
IList <ReportPiutangPenjualanProdukDetail> oList = new List <ReportPiutangPenjualanProdukDetail>();
try
{
var whereBuilder = new WhereBuilder(SQL_TEMPLATE_DETAIL);
whereBuilder.Add("t_jual_produk.tanggal_tempo IS NOT NULL");
whereBuilder.Add("(t_jual_produk.total_nota - t_jual_produk.diskon + t_jual_produk.ppn) - t_jual_produk.total_pelunasan <> 0");
whereBuilder.Add("EXTRACT(MONTH FROM t_jual_produk.tanggal) = @bulan");
whereBuilder.Add("EXTRACT(YEAR FROM t_jual_produk.tanggal) = @tahun");
oList = _context.db.Query <ReportPiutangPenjualanProdukDetail>(whereBuilder.ToSql(), new { bulan, tahun })
.ToList();
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(oList);
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
_selectBuilder = null;
_orderBuilder = null;
_joinBuilder = null;
_whereBuilder = null;
_aliasTableName = string.Empty;
_groupBuilder = null;
_suffix = string.Empty;
TableNameResolver = null;
TableNameAliaser = null;
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
disposedValue = true;
}
}
public IList <ReportPiutangPenjualanProduk> PerProdukGetByBulan(int bulanAwal, int bulanAkhir, int tahun)
{
IList <ReportPiutangPenjualanProduk> oList = new List <ReportPiutangPenjualanProduk>();
try
{
var whereBuilder = new WhereBuilder(SQL_TEMPLATE_PER_PRODUK);
whereBuilder.Add("t_jual_produk.tanggal_tempo IS NOT NULL");
whereBuilder.Add("(t_jual_produk.total_nota - t_jual_produk.diskon + t_jual_produk.ppn + t_jual_produk.ongkos_kirim) - t_jual_produk.total_pelunasan <> 0");
whereBuilder.Add("(EXTRACT(MONTH FROM t_jual_produk.tanggal) BETWEEN @bulanAwal AND @bulanAkhir)");
whereBuilder.Add("EXTRACT(YEAR FROM t_jual_produk.tanggal) = @tahun");
oList = _context.db.Query <ReportPiutangPenjualanProduk>(whereBuilder.ToSql(), new { bulanAwal, bulanAkhir, tahun })
.ToList();
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(oList);
}
public void EvalFail(String exp)
{
var dic = new Dictionary <String, Object>(StringComparer.OrdinalIgnoreCase)
{
["SiteIds"] = new[] { 2, 4, 8, 16 },
["userid"] = 1234
};
var builder = new WhereBuilder
{
Factory = Log.Meta.Factory,
Expression = exp,
};
builder.SetData(dic);
builder.SetData2(dic);
var log = new Log {
LinkID = 17, CreateUserID = 1235
};
var rs = builder.Eval(log);
Assert.False(rs);
}
/// <summary>
/// 已签到未取药 true
/// </summary>
public bool HadCheckedIn(string ptno, string presno)
{
var sql = $@"
SELECT COUNT(*) COUNT
FROM TWOCS_PRSLIP
where BDATE > TRUNC(SYSDATE-3)
and PTNO= '{ptno}'
and PRESNO='{presno}'
and CHECKDATE IS NOT NULL
and CHECKTIME IS NOT NULL";
var wb = new WhereBuilder();
//wb.And("PTNO", ptno, true);
//wb.And("PRESNO", presno, true);
//wb.And("FLAG =1", AnalyzableKind.KeepOriginal);
//wb.And("GBOUT IS NULL", AnalyzableKind.KeepOriginal);
//wb.And("STATUS =0", AnalyzableKind.KeepOriginal);
//wb.And("BDATE > TRUNC(SYSDATE-3)", AnalyzableKind.KeepOriginal);
//wb.And("CHECKDATE IS NOT NULL", AnalyzableKind.KeepOriginal);
//wb.And("CHECKTIME IS NOT NULL", AnalyzableKind.KeepOriginal);
var count = DBaser.QueryValue <string>(sql, wb);
return(int.TryParse(count, out int result) && result > 0);
}
private double GetGajiKaryawan(DateTime tanggalMulai, DateTime tanggalSelesai)
{
double result = 0;
try
{
var whereBuilder = new WhereBuilder(SQL_TEMPLATE_GAJI_KARYAWAN);
whereBuilder.Add("tanggal BETWEEN @tanggalMulai AND @tanggalSelesai");
var obj = _context.db.QuerySingleOrDefault <dynamic>(whereBuilder.ToSql(), new { tanggalMulai, tanggalSelesai });
if (obj != null)
{
result = (double)(obj.gaji_pokok + obj.tunjangan + obj.lembur + obj.bonus - obj.potongan);
}
}
catch (Exception ex)
{
_log.Error("Error:", ex);
}
return(result);
}
/// <summary>
/// 更新医嘱表状态为取药预约
/// </summary>
/// <param name="ptno">患者编号</param>
/// <param name="presno">处方号</param>
public int OrderMedicationAppointment(string ptno, string presno)
{
if (ptno.IsEmpty())
{
throw new Exception("患者编号为空");
}
if (presno.IsEmpty())
{
throw new Exception(".");
}
var wb = new WhereBuilder();
wb.And("PTNO", ptno, true);
wb.And("PRESNO", presno, true);
wb.And($@"BDATE= TRUNC(SYSDATE)", AnalyzableKind.KeepOriginal);
//wb.And("JUPSU", "0", true);
DBaser.Update("TWOCS_OORDER");
DBaser["JUPSU"] = "1";
var count = DBaser.AcceptChanges(wb);
//if (count <= 0)
// throw new Exception($"更新医嘱状态失败", new Exception($"患者编号:{ptno} 处方号:{presno}"));
return(count);
}
public override IAI_PATIENT Insert(PRPA_IN201311UV02 addEntity)
{
var asOtherIDs = addEntity.controlActProcess.subject.registrationRequest.subject1.patient.patientPerson.asOtherIDs;
var data = new IAI_PATIENT()
{
patient_id = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.id?.extension,
id_number = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.id?.extension,
name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.name?.Value,
telecom = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.telecom?.value,
gender_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.administrativeGenderCode?.code,
birth_time = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.birthTime?.value,
street_address_line = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.streetAddressLine?.Value,
state = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.state?.Value,
city = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.city,
county = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.county,
street_name_base = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.streetNameBase,
street_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.streetName,
house_number = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.houseNumber,
postal_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.addr?.postalCode,
marital_status_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.maritalStatusCode?.code,
ethnic_group_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.ethnicGroupCode?.code,
occupation_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.asEmployee?.occupationCode?.code,
employerorganization_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.asEmployee?.employerOrganization?.name,
employerorganization_telecom = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.asEmployee?.employerOrganization?.contactParty?.telecom?.value,
asotherids_id1 = asOtherIDs?.Length >= 1 ? asOtherIDs[0]?.id?.extension : "",
scopingorganization_id1 = asOtherIDs?.Length >= 1 ? asOtherIDs[0]?.scopingOrganization?.id?.extension : "",
asotherids_id2 = asOtherIDs?.Length >= 2 ? asOtherIDs[1]?.id?.extension : "",
scopingorganization_id2 = asOtherIDs?.Length >= 2 ? asOtherIDs[1]?.scopingOrganization?.id?.extension : "",
personalrelationship_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.personalRelationship?.code,
personalrelationship_telecom = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.personalRelationship?.telecom?.value,
relationshipholder1_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.patientPerson?.personalRelationship?.relationshipHolder1?.name,
providerorganization_id = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.providerOrganization?.id?.extension,
providerorganization_name = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.providerOrganization?.name?.Value,
beneficiary_code = addEntity?.controlActProcess?.subject?.registrationRequest?.subject1?.patient?.coveredPartyOf?.coverageRecord?.beneficiary?.beneficiary?.code?.code,
};
var wb = new WhereBuilder();
wb.And("patient_id", data?.patient_id, true);
if (DBaser.QueryValue <int>("select count(*) from IAI_PATIENT", wb) > 0)
{
throw new Exception($@"主键冲突{data.patient_id}已存在。");
}
DBaser.Insert("IAI_PATIENT");
DBaser["patient_id"] = data.patient_id;
DBaser["id_number"] = data.id_number;
DBaser["name"] = data.name;
DBaser["telecom"] = data.telecom;
DBaser["gender_code"] = data.gender_code;
DBaser["birth_time"] = data.birth_time;
DBaser["street_address_line"] = data.street_address_line;
DBaser["state"] = data.state;
DBaser["city"] = data.city;
DBaser["county"] = data.county;
DBaser["street_name_base"] = data.street_name_base;
DBaser["street_name"] = data.street_name;
DBaser["house_number"] = data.house_number;
DBaser["postal_code"] = data.postal_code;
DBaser["marital_status_code"] = data.marital_status_code;
DBaser["ethnic_group_code"] = data.ethnic_group_code;
DBaser["occupation_code"] = data.occupation_code;
DBaser["employerorganization_name"] = data.employerorganization_name;
DBaser["employerorganization_telecom"] = data.employerorganization_telecom;
DBaser["asotherids_id1"] = data.asotherids_id1;
DBaser["scopingorganization_id1"] = data.scopingorganization_id1;
DBaser["asotherids_id2"] = data.asotherids_id2;
DBaser["scopingorganization_id2"] = data.scopingorganization_id2;
DBaser["personalrelationship_code"] = data.personalrelationship_code;
DBaser["personalrelationship_telecom"] = data.personalrelationship_telecom;
DBaser["relationshipholder1_name"] = data.relationshipholder1_name;
DBaser["providerorganization_id"] = data.providerorganization_id;
DBaser["providerorganization_name"] = data.providerorganization_name;
DBaser["beneficiary_code"] = data.beneficiary_code;
DBaser.AcceptChanges();
return(data);
}
public UpdateQueryFluentBuilder1 <TModel> Where(WhereBuilder <TModel> builder)
{
_whereBuilder.AndWith(builder);
return(this);
}
// Where
public UpdateQueryFluentBuilder1 <TModel> Where(Expression <Func <TModel, bool> > exp)
{
_whereBuilder.AndWith(WhereBuilder <TModel> .FromExpression(exp));
return(this);
}
public void ShouldCreateLikeWhereQuery()
{
var builder =
new WhereBuilder<Model.Model>()
.Where(model => model.Name, new Like<string>("%JONY"))
.ToString();
builder.Should().Be("WHERE [Name] LIKE '%JONY'");
}
public DeleteQuery(WhereBuilder <TModel> whereBuilder)
{
_whereBuilder = whereBuilder;
}
public WhereConjuntionBuilder(WhereBuilder whereBuilder, QBuilder qBuilder)
: this(qBuilder)
{
_whereBuilder = whereBuilder;
}
public void enum_as_int()
{
_subject = WhereMetadata(x => x.Status == ArtistStatusType.Continuing);
_subject.ToString().Should().Be($"(\"ArtistMetadata\".\"Status\" = @Clause1_P1)");
}
public virtual string GetWhereSql(Filter filter, int parameterStartIndex)
{
var whereBuilder = new WhereBuilder(this, parameterStartIndex);
return(whereBuilder.Build(filter));
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == 5;
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, false);
IQuery query = new DeleteQuery(new Dialect(), tables[0], where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM [PersonTable]"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
static void Main(string[] args)
{
using (ispDB db = new ispDB())
{
barrio barr = db.barrios.Where(x => x.id == 1).SingleOrDefault();
var registros =
(from op in db.inventarios
from dt in db.detallesinventarios.Where(x => x.idperiodo == op.idperiodo && x.tipo == op.tipo && x.numero == op.numero).DefaultIfEmpty()
group dt by op
into gr
select gr.Key.Relacionar(gr.ToList())
).ToList();
//db.inventarios.GroupJoin(db.detallesinventarios, x => x, y => y.fkinventario, (x, y) => x).ToList();
//(from op in db.inventarios
// join dt in db.detallesinventarios on op equals dt.fkinventario
// //into gr
// join pr in db.productos on dt.fkproducto equals pr
// select gr.ToList()
//).ToList();
}
NameValueCollection conf = (NameValueCollection)ConfigurationManager.AppSettings;
object aaaa = BuscarListaPr.BuscarObjeto(TipoConsulta.ColumnasPorTabla, true, true, "barrios");
using (ispDB db = new ispDB())
{
var sp = db.DataProvider.GetSchemaProvider();
LinqToDB.SchemaProvider.DatabaseSchema aaa = sp.GetSchema(db, new LinqToDB.SchemaProvider.GetSchemaOptions()
{
ExcludedSchemas = new string[] { "empresas" }
});
}
List <prd> prod = new List <prd>()
{
new prd()
{
Codigo = 1, TipoPago = new List <pg>()
{
new pg()
{
Id = 1, Descripcion = ""
}, new pg()
{
Id = 2, Descripcion = ""
}
}
},
new prd()
{
Codigo = 1, TipoPago = new List <pg>()
{
new pg()
{
Id = 1, Descripcion = ""
}
}
}
}
;
object res = prod.Where(x => x.TipoPago.FindIndex(y => y.Id == 2) > -1).ToList();
string a = "Hola";
string b = Reverse(a);
perfil uno = new perfil()
{
id = 0, descripcion = "no se"
};
PerfilPr.Instancia.Grabar(uno);
using (ispDB db = new ispDB())
{
//var lista = (from m in db.perfiles.DefaultIfEmpty()
// join p in db.usuariosperfiles on m.id equals p.idperfil
// into pm
// from a in pm.DefaultIfEmpty()
// where a.idusuario == 1 || a.idusuario == null
// orderby m.descripcion
// select new usuarioperfil()
// {
// fkperfile = m,
// idusuario = 1,
// idperfil = m.id,
// Asignado = (a.idperfil == null ? false : true)
// }).ToList();
//var lista1 = (from m in db.menus
// join p in db.perfilesmenus on m.id equals p.idmenu
// into pm
// from a in pm.DefaultIfEmpty()
// where a.idperfil == 1 || a.idperfil == null
// orderby m.nombre
// select new perfilmenu()
// {
// fkmenu = m,
// editable = (a.editable == null ? false : a.editable),
// idperfil = 1,
// idmenu = m.id,
// Asignado = (a.idmenu == null ? false : true)
// }).ToList();
var lista = (from m in db.menus
from p in db.perfilesmenus
.Where(q => q.idmenu == m.id && (q.idperfil == 1 || q.idperfil == null)).DefaultIfEmpty()
where p.idperfil == 1
select new perfilmenu()
{
fkmenu = m,
editable = (p.editable == null ? false : p.editable),
idperfil = 1,
idmenu = m.id,
Asignado = (p.idmenu == null ? false : true)
}).ToList();
int it = db.GetTable <perfil>().Max(x => ((short?)x.id ?? 0) + 1);
string ax = db.LastQuery;
}
PerfilPr.RegistrosCombo();
BancoPr.Instancia.Borrar(new banco()
{
id = 0
});
DocumentoPr.Instancia.Grabar(new documento()
{
id = 5
});
using (ispDB db = new ispDB())
{
try
{
db.BeginTransaction();
pagoregistrado pagoreg;
pagoreg = db.QueryProc <pagoregistrado>(pagoregistrado.funcion,
new DataParameter("pidentificadorpagos", 0, DataType.Int32),
new DataParameter("pidformapago", 1, DataType.Int16),
new DataParameter("pnotificacion", false, DataType.Boolean),
new DataParameter("pfecha", Sql.DateTime, DataType.DateTime),
new DataParameter("pidusuariocobranza", 1, DataType.Int16),
new DataParameter("pvalor", 100, DataType.Decimal),
new DataParameter("pdetalle", "prueba", DataType.VarChar),
new DataParameter("pidusuarioregistra", 2, DataType.Int16),
new DataParameter("pidusuarioanula", -1, DataType.Int16),
new DataParameter("ttran", 1, DataType.Int16)
).First();
db.CommitTransaction();
List <conveniopago> pago = db.
pagos.Where(x => x.identificadorpagos == pagoreg.identificadorpago).Select(x =>
x.Relacionar(x.fkconveniospago
.Relacionar(x.fkconveniospago.fkidentificadorespagos
).Relacionar(x.fkconveniospago.fkformaspago)
).Relacionar(x.fkusuario
)).ToList().GroupBy(x => x.fkconveniospago, (x, y) => new conveniopago()
{
fkformaspago = x.fkformaspago,
fkidentificadorespagos = x.fkidentificadorespagos,
fkpagos = y.ToList()
}).ToList();
identificadorpago identificador = pago.GroupBy(x => x.fkidentificadorespagos,
(x, y) => new identificadorpago()
{
id = x.id,
fkconveniospago = y.ToList()
}).Single();
}
catch (Exception)
{
db.RollbackTransaction();
throw;
}
}
WhereBuilder f = new WhereBuilder();
string ff = f
.Donde("uno", Comparacion.Igual, 1)
.Y("dos", Comparacion.Contiene, "f")
.O("fkpersona.identificacion", Comparacion.NoContiene, "070").Compilar();
var fff = f.parametros;
LinqToDB.SchemaProvider.GetSchemaOptions GetSchemaOptions =
new LinqToDB.SchemaProvider.GetSchemaOptions();
LinqToDB.SqlProvider.ISqlBuilder SqlBuilder;
using (ispDB db = new ispDB())
{
SqlBuilder = db.DataProvider.CreateSqlBuilder();
var sp = db.DataProvider.GetSchemaProvider();
LinqToDB.SchemaProvider.DatabaseSchema db1 = sp.GetSchema(db, GetSchemaOptions);
}
usuario ls = UsuarioPr.Instancia.RegistroPorLogin("fer fer");
//ClientePr c = new ClientePr();
//Cliente nombre = c.RegistroPorId(3);
//Cliente client;// = new Cliente();
//client = nombre;
//client.Nombre = client.Nombre + " Registro";
//client.Foto = System.Drawing.Image.FromFile("c:\\a.png");
//client.IdApoderado = 2;
//client.IdBarrio = 2;
//client.IdBeneficiarioMortuorio = 2;
//client.IdBeneficiarioSeguroVida = 2;
//client.IdConyugue = 2;
//client.IdEstadoCivil = 2;
//client.IdEstadoPersona = 1;
//client.IdGenero = 1;
//client.IdNivelEstudio = 1;
//client.IdProfesion = 1;
//client.IdTipoSangre = 1;
//client.Modificado = true;
//object resp = c.Grabar(client, null, null);
//bool res = General.ValidarCedula("0704680421");
//res = General.ValidarCedula("0704680420001");
//General.periodoActual = 2014;
//ContablePr a = new ContablePr();
//Contable b = a.RegistroPorId(0, 1, 1);
//Console.WriteLine(b.DetalleContable[0].DescripcionDetalleContable);
//UsuarioSesionActiva usu = new UsuarioSesionActiva(new Usuario(1));
//MenuSistemaPr menu = new MenuSistemaPr();
//General.usuarioActivo = usu;
//Console.WriteLine(usu.CadenaSelect());
//string a = General.CifrarClave("papo 123123");
/*Console.WriteLine(EncodePassword("PRUEBA DE ENCRIPTACION").Length);
* Console.WriteLine(EncodePassword("WALTER PA$$w0rd").Length);
* Console.WriteLine(EncodePassword("DAVID PODE_SERA_MIO").Length);
* Console.WriteLine(EncodePassword("DANIEL EstaEsLa ClaveMas Grande del Mundo"));
*/
//GC.Collect();
Console.ReadLine();
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
var orderBy = MockRepository.GenerateStub<ISortQueryBuilder>();
orderBy.Expect(o => o.BuildQuery()).Return("");
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List<Person> list = new List<Person>();
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.Name.Contains("John") && p.Age > 5;
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new SelectQuery(new SqlServerDialect(), tables, where.ToString(), orderBy, false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}
public void enum_as_int()
{
_subject = Where(x => x.Status == MovieStatusType.Announced);
_subject.ToString().Should().Be($"(\"Movies\".\"Status\" = @Clause1_P1)");
}
public void where_string_is_null()
{
_subject = Where(x => x.CleanTitle == null);
_subject.ToString().Should().Be($"(\"Movies\".\"CleanTitle\" IS NULL)");
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List<Person> list = new List<Person>();
var where = new WhereBuilder<Person>(command, p => p.Name.Contains("John") && p.Age > 5, false);
IQuery query = new SelectQuery(columns, "dbo.People", where.ToString(), "", false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}
public void where_in_string_list_column()
{
_subject = WhereMetadata(x => x.OldForeignArtistIds.Contains("foreignId"));
_subject.ToString().Should().Be($"(\"ArtistMetadata\".\"OldForeignArtistIds\" LIKE '%' || @Clause1_P1 || '%')");
}
//public void Do(Action<RequestEntity> action, RequestEntity p)
//{
// action(p);
//}
public List <TWBAS_PATIENT> FindList(PRPA_IN201305UV02 t, WhereBuilder wb)
{
//var sql = "select * from TWBAS_PATIENT";
//return DBaser.Query<TWBAS_PATIENT>(sql, wb).ToList();
return(null);
}
public void where_string_is_null()
{
_subject = Where(x => x.CleanName == null);
_subject.ToString().Should().Be($"(\"Artists\".\"CleanName\" IS NULL)");
}
public WhereBuilder ParseSQL()
{
var sql = new StringBuilder();
//sql.AppendLine($@" SELECT CON.CONTRACT_ID,CON.STATUS,CON.CONTRACT_NO,CON.CONTRACT_SERIAL_NO,SUPPLIER,CON.CONTRACT_GROUP,CON.CONTRACT_TYPE_ID,CON.CONTRACT_TYPE_NAME, CON.NEED_COMMENT, CON.CONTRACT_TERM, CON.FERRERO_ENTITY,CON.COUNTERPARTY_EN, CON.COUNTERPARTY_CN,CON.EFFECTIVE_DATE,CON.IS_MASTER_AGREEMENT,CON.APPLY_DATE,CON.CREATED_BY,CON.CREATE_TIME,(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_NAME WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_NAME ELSE '' END) CONTRACT_NAME,(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_OWNER WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_OWNER ELSE '' END) CONTRACT_OWNER,(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_INITIATOR WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_INITIATOR ELSE '' END) CONTRACT_INITIATOR FROM CAS_CONTRACT CON INNER JOIN dbo.CAS_PO_APPROVAL_SETTINGS PAS ON CON.CONTRACT_TYPE_ID=PAS.CONTRACT_TYPE_ID INNER JOIN
// dbo.CAS_CONTRACT_FILING CCF ON CON.CONTRACT_ID = CCF.CONTRACT_ID WHERE 1=1 AND NOT_DISPLAY_IN_MY_SUPPORT ='0' AND CCF.STATUS='4' AND PAS.USER_ID='{WebCaching.UserId}'");
//PO页面功能合并至ISupportedContract页面
sql.AppendLine($@"SELECT CON.CONTRACT_ID,CON.STATUS,CON.CONTRACT_NO,CON.CONTRACT_SERIAL_NO,
CON.SUPPLIER,CON.CONTRACT_GROUP,CON.CONTRACT_TYPE_ID,CON.CONTRACT_TYPE_NAME,
CON.NEED_COMMENT, CON.CONTRACT_TERM, CON.FERRERO_ENTITY,
CON.COUNTERPARTY_EN, CON.COUNTERPARTY_CN,CON.EFFECTIVE_DATE,CON.IS_MASTER_AGREEMENT,
CON.APPLY_DATE,CON.CREATED_BY,CON.CREATE_TIME,
(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_NAME
WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_NAME ELSE '' END) CONTRACT_NAME,
(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_OWNER
WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_OWNER ELSE '' END) CONTRACT_OWNER,
(CASE WHEN IS_TEMPLATE_CONTRACT = '0' THEN CONTRACT_INITIATOR
WHEN IS_TEMPLATE_CONTRACT = '1' THEN TEMPLATE_INITIATOR ELSE '' END) CONTRACT_INITIATOR,
(CASE WHEN EXISTS(
SELECT *
FROM CAS_CONTRACT_FILING B
WHERE B.CONTRACT_ID = CON.CONTRACT_ID
AND (B.STATUS = {ContractFilingEnum.Apply.GetHashCode()}
OR B.STATUS = {ContractFilingEnum.POSave.GetHashCode()}))
THEN 1
ELSE 0
END) POAPPLYEXISTS
FROM CAS_CONTRACT CON
WHERE (CON.STATUS = {ContractStatusEnum.HadApproval.GetHashCode()} OR CON.STATUS = {ContractStatusEnum.SignedCompleted.GetHashCode()})");
//过滤没有PR提交的直接查询不出来。PO拒绝的，也能在PO查看中进行查看。
sql.AppendLine($@" AND EXISTS(SELECT CONTRACT_FILING_ID FROM CAS_CONTRACT_FILING T
WHERE T.CONTRACT_ID = CON.CONTRACT_ID AND T.STATUS !={ContractFilingEnum.Save.GetHashCode()}
AND 1=1
)");
sql.AppendLine($@" AND EXISTS(
SELECT * FROM CAS_PO_APPROVAL_SETTINGS C
WHERE CON.CONTRACT_TYPE_ID = C.CONTRACT_TYPE_ID
AND (C.USER_ID = '{WebCaching.UserId}'
OR EXISTS (
SELECT * FROM CAS_PROXY_APPROVAL D
WHERE D.AGENT_USER_ID = '{WebCaching.UserId}'
AND C.USER_ID = D.AUTHORIZED_USER_ID
AND GETDATE() BETWEEN D.BEGIN_TIME AND D.END_TIME AND D.IS_DELETED=0
))
)");
if (keyWord != "")
{
sql.AppendLine($@" AND ( CON.TEMPLATE_NAME LIKE N'%{keyWord}%' OR CON.CONTRACT_NAME LIKE N'%{keyWord}%') ");
}
if (CONTRACT_SERIAL_NO != "")
{
sql.AppendLine($@" AND CON.CONTRACT_SERIAL_NO LIKE N'%{CONTRACT_SERIAL_NO}%'");
}
if (CONTRACT_GROUP != "")
{
sql.AppendLine($@" AND CON.CONTRACT_GROUP ='{CONTRACT_GROUP}'");
}
if (CounterParty != "")
{
sql.AppendLine($@" AND ( COUNTERPARTY_CN LIKE N'%{CounterParty}%' OR COUNTERPARTY_EN LIKE N'%{CounterParty}%') ");
}
if (StatusValue != "")
{
if (StatusValue == "2")
{
sql.AppendLine($@" AND CON.STATUS IN('{ContractStatusEnum.WaitApproval.GetHashCode()}','{ContractStatusEnum.Resubmit.GetHashCode()}') ");
}
else
{
sql.AppendLine($@" AND CON.STATUS IN('{StatusValue}') ");
}
}
if (PO != "")
{
var POQuery = $@"1=1 AND T.PO_NO LIKE N'%{PO}%'";
sql.Replace("1=1", POQuery);
}
if (PR != "")
{
var PRQuery = $@"1=1 AND T.PR_NO LIKE N'%{PR}%'";
sql.Replace("1=1", PRQuery);
}
var wb = new WhereBuilder(sql.ToString());
return(wb);
}
public async Task TestRepositoryAsync()
{
var uow = serviceProvider.GetService <IUnitOfWork>();
var customerRepository = serviceProvider.GetService <ICustomerRepository>();
var orderHeaderRepository = serviceProvider.GetService <IOrderHeaderRepository>();
var orderDetailRepository = serviceProvider.GetService <IOrderDetailRepository>();
//Test insert,update,get,delete
var customer = new Customer()
{
Name = "testCustomer"
};
await customerRepository.InsertAsync(customer);
await customerRepository.Where(it => it.Name == "testCustomer")
.SetValue(it => it.Age, 5)
.SetValue(it => it.TotalConsumptionAmount, 100)
.ExecuteUpdateAsync();
var ccc = customerRepository.Where(it => it.Name == "testCustomer").Distinct().FirstOrDefault();
var age5Customers = customerRepository.Where(it => it.Name == "testCustomer").ToList();
Assert.Single((IEnumerable)age5Customers);
Assert.Equal(5, age5Customers[0].Age);
Assert.Equal(100, age5Customers[0].TotalConsumptionAmount);
var orderHeader = new OrderHeader();
orderHeader.CreateTime = DateTime.UtcNow;
orderHeader.CustomerId = customer.Id;
orderHeader.State = 1;
orderHeader.OrderNo = Guid.NewGuid().ToString("N");
await orderHeaderRepository.InsertAsync(orderHeader);
var orderDetail = new OrderDetail();
orderDetail.OrderHeaderId = orderHeader.Id;
orderDetail.ProductName = "apple";
orderDetail.Quantity = 1;
await orderDetailRepository.InsertAsync(orderDetail);
var orderDetail2 = new OrderDetail();
orderDetail2.OrderHeaderId = orderHeader.Id;
orderDetail2.ProductName = "orange";
orderDetail2.Quantity = 2;
await orderDetailRepository.InsertAsync(orderDetail2);
var result = await customerRepository.QueryAllBuyProductByNameAsync("testCustomer");
Assert.Contains(result, t => t.ProductName == "apple");
Assert.Contains(result, t => t.ProductName == "orange");
orderDetail.Quantity = 2;
await orderDetailRepository.UpdateAsync(orderDetail);
var orderDetailTmp = await orderDetailRepository.GetAsync(orderDetail.Id);
Assert.Equal(2, orderDetailTmp.Quantity);
await orderDetailRepository.DeleteAsync(orderDetail2);
var result2 = await customerRepository.QueryAllBuyProductByNameAsync("testCustomer");
Assert.Single(result2);
Assert.Contains(result2, t => t.ProductName == "apple");
//Test unitOfWork
try
{
uow.BeginTransaction();
await customerRepository.InsertAsync(new Customer()
{
Name = "testCustomer2"
});
var orderDetail3 = new OrderDetail
{
OrderHeaderId = orderHeader.Id,
ProductName = "ball",
Quantity = 3
};
await orderDetailRepository.InsertAsync(orderDetail3);
uow.Commit();
}
catch (Exception e)
{
uow.RollBack();
}
var allCustomer = await customerRepository.GetAllAsync();
Assert.Equal(2, allCustomer.Count);
Assert.Contains(allCustomer, t => t.Name == "testCustomer2");
var allOrderDetails = await orderDetailRepository.GetAllAsync();
Assert.Equal(2, allCustomer.Count);
Assert.Contains(allOrderDetails, t => t.ProductName == "ball");
try
{
uow.BeginTransaction();
await customerRepository.InsertAsync(new Customer()
{
Name = "testCustomer3"
});
throw new Exception("testException");
var orderDetail4 = new OrderDetail();
orderDetail4.OrderHeaderId = orderHeader.Id;
orderDetail4.ProductName = "basketball";
orderDetail4.Quantity = 4;
await orderDetailRepository.InsertAsync(orderDetail4);
uow.Commit();
}
catch (Exception e)
{
uow.RollBack();
}
allCustomer = await customerRepository.GetAllAsync();
Assert.Equal(2, allCustomer.Count);
Assert.Contains(allCustomer, t => t.Name == "testCustomer2");
allOrderDetails = await orderDetailRepository.GetAllAsync();
Assert.Equal(2, allCustomer.Count);
Assert.Contains(allOrderDetails, t => t.ProductName == "ball");
//Test page
var customers = new List <Customer>();
for (int i = 0; i < 100; i++)
{
customers.Add(new Customer()
{
Age = i, Name = "page" + i
});
}
var newCount = await customerRepository.InsertAsync(customers);
Assert.Equal(100, newCount.Count);
var pageable = new Pageable(1, 10);
var page = await customerRepository.GetCustomerByPageAsync(pageable, 5);
//0-99岁，大于5的只有94个
Assert.Equal(94, page.TotalPages);
Assert.Equal(10, page.Data.Count);
var page2 = await customerRepository.Where(it => it.Age > 5).Skip(0).Take(10).ToPageAsync();
Assert.Equal(94, page2.TotalPages);
Assert.Equal(10, page2.Data.Count);
//测试bindWhere构造条件
var nameEmpty = WhereBuilder.Empty <string>();
var ageEmpty = WhereBuilder.Empty <int>();
var nameWhereItem = WhereBuilder.HasValue("page5");
var ageWhereItem = WhereBuilder.HasValue(5);
var bindResult = await customerRepository.GetCustomerByConditionAsync(nameWhereItem, ageEmpty);
Assert.Single(bindResult);
var bindResult2 = await customerRepository.GetCustomerByConditionAsync(nameEmpty, ageEmpty);
Assert.Equal(102, bindResult2.Count);
var bindResult3 = await customerRepository.GetCustomerByConditionAsync(nameEmpty, ageWhereItem);
Assert.Equal(2, bindResult3.Count);
var bindResult4 = await customerRepository.GetCustomerByConditionAsync(nameWhereItem, ageWhereItem);
Assert.Single(bindResult4);
var bindResult5 = await customerRepository.GetCustomerByPageByConditionAsync(pageable, nameWhereItem, ageEmpty);
Assert.Single(bindResult5.Data);
var bindResult6 = await customerRepository.GetCustomerByPageByConditionAsync(pageable, nameEmpty, ageEmpty);
Assert.Equal(10, bindResult6.Data.Count);
var bindResult7 = await customerRepository.GetCustomerByPageByConditionAsync(pageable, nameEmpty, ageWhereItem);
Assert.Equal(2, bindResult7.Data.Count);
var bindResult8 = await customerRepository.GetCustomerByPageByConditionAsync(pageable, nameWhereItem, ageWhereItem);
Assert.Single(bindResult8.Data);
//var customers= customerRepository.Where(it => it.Age > 5).OrderBy(it => it.Id).Take(10).ToList();
//Test update
var newCount2 = await customerRepository.Where(it => it.Age > 5).SetValue(it => it.Name, "a")
.ExecuteUpdateAsync();
Assert.Equal(94, newCount2);
//Test delete
var newCount3 = await customerRepository.DeleteAsync(it => it.Age > 5);
Assert.Equal(94, newCount3);
await customerRepository.DeleteAsync(it => it.Age > 5);
var newCount4 = await customerRepository.GetAllAsync();
Assert.Equal(8, newCount4.Count);
}
protected virtual void AppleCompositeFilter <TSource>(WhereBuilder whereBuilder, ICompositeFilter filter)
{
whereBuilder.SubQuery(subWhereBuilder => filter.Filters.ForEach(subFilter => ApplyFilter <TSource>(subWhereBuilder, subFilter)), filter.And == true);
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var where = new WhereBuilder<Person>(command, p => p.ID == 5, false);
IQuery query = new DeleteQuery("dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM dbo.People"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
public override IAI_OBSERVATION Insert(POOR_IN200901UV addEntity)
{
var data = new IAI_OBSERVATION
{
observation_id = addEntity?.controlActProcess?.subject?.observationRequest?.id?.extension,
observation_type = addEntity?.controlActProcess?.subject?.observationRequest?.code,
observation_text = addEntity?.controlActProcess?.subject?.observationRequest?.text,
effectivetime_high = addEntity.controlActProcess?.subject?.observationRequest?.effectiveTime?.high?.value,
effectivetime_low = addEntity.controlActProcess?.subject?.observationRequest?.effectiveTime?.low?.value,
prioritycode = addEntity?.controlActProcess?.subject?.observationRequest?.priorityCode?.code,
specimen_id = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.id.extension,
specimen_code = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.code,
specimen_desc = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.specimenNatural.desc,
additive3 = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.specimenNatural?.additive?.additive3?.desc?.Value,
productOf_time = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.productOf.time.value,
specimenProcessStep_time = addEntity?.controlActProcess?.subject?.observationRequest?.specimen?.specimen?.productOf?.specimenProcessStep?.effectiveTime?.value,
recordtarget = addEntity?.controlActProcess?.subject?.observationRequest?.recordTarget?.patient?.id[0].extension,
author_time = addEntity?.controlActProcess?.subject?.observationRequest?.author?.time.value,
signaturetext = addEntity?.controlActProcess?.subject?.observationRequest?.author?.signatureText,
author_assignedentity_id = addEntity?.controlActProcess?.subject?.observationRequest?.author?.assignedEntity?.id.extension,
assignedperson_name = addEntity?.controlActProcess?.subject?.observationRequest?.author?.assignedEntity?.assignedPerson?.name.Value,
representedorganization_id = addEntity?.controlActProcess?.subject?.observationRequest?.author?.assignedEntity?.representedOrganization?.id.extension,
representedorganization_name = addEntity?.controlActProcess?.subject?.observationRequest?.author?.assignedEntity?.representedOrganization?.name,
verifier_time = addEntity?.controlActProcess?.subject?.observationRequest?.verifier?.time.value,
verifier_signaturetext = addEntity?.controlActProcess?.subject?.observationRequest?.verifier?.signatureText,
verifier_assignedentity_id = addEntity?.controlActProcess?.subject?.observationRequest?.verifier?.assignedEntity?.id.extension,
verifier_assignedperson_name = addEntity?.controlActProcess?.subject?.observationRequest?.verifier?.assignedEntity?.assignedPerson?.name.Value,
goal_text = addEntity?.controlActProcess?.subject?.observationRequest?.goal?.observationEventCriterion?.text,
goal_reason = addEntity?.controlActProcess?.subject?.observationRequest?.reason[0].observation.value.Value,
annotation_text = addEntity?.controlActProcess?.subject?.observationRequest?.subjectOf6?.annotation?.text,
annotation_author_id = addEntity?.controlActProcess?.subject?.observationRequest?.subjectOf6?.annotation?.author?.assignedEntity?.id.extension,
annotation_author_name = addEntity?.controlActProcess?.subject?.observationRequest?.subjectOf6?.annotation?.author?.assignedEntity?.assignedPerson?.name.Value,
represented_organization_id = addEntity?.controlActProcess?.subject?.observationRequest?.subjectOf6?.annotation?.author?.assignedEntity?.representedOrganization?.id.extension,
represented_organization_name = addEntity?.controlActProcess?.subject?.observationRequest?.subjectOf6?.annotation?.author?.assignedEntity?.representedOrganization?.name,
encounter_id = addEntity.controlActProcess?.subject?.substanceAdministrationRequest?.componentOf1?.encounter?.id,
};
var wb = new WhereBuilder();
wb.And("observation_id", data.observation_id, true);
if (DBaser.QueryValue <int>("select count(*) from IAI_OBSERVATION", wb) > 0)
{
throw new Exception($@"主键冲突{data.observation_id}已存在。");
}
DBaser.Insert("IAI_OBSERVATION");
DBaser["observation_id"] = data.observation_id;
DBaser["observation_type"] = data.observation_type;
DBaser["observation_text"] = data.observation_text;
DBaser["effectivetime_high"] = data.effectivetime_high;
DBaser["effectivetime_low"] = data.effectivetime_low;
DBaser["prioritycode"] = data.prioritycode;
DBaser["specimen_id"] = data.specimen_id;
DBaser["specimen_code"] = data.specimen_code;
DBaser["specimen_desc"] = data.specimen_desc;
DBaser["additive3"] = data.additive3;
DBaser["productOf_time"] = data.productOf_time;
DBaser["specimenProcessStep_time"] = data.specimenProcessStep_time;
DBaser["recordtarget"] = data.recordtarget;
DBaser["author_time"] = data.author_time;
DBaser["signaturetext"] = data.signaturetext;
DBaser["author_assignedentity_id"] = data.author_assignedentity_id;
DBaser["assignedperson_name"] = data.assignedperson_name;
DBaser["representedorganization_id"] = data.representedorganization_id;
DBaser["representedorganization_name"] = data.representedorganization_name;
DBaser["verifier_time"] = data.verifier_time;
DBaser["verifier_signaturetext"] = data.verifier_signaturetext;
DBaser["verifier_assignedentity_id"] = data.verifier_assignedentity_id;
DBaser["verifier_assignedperson_name"] = data.verifier_assignedperson_name;
DBaser["goal_text"] = data.goal_text;
DBaser["goal_reason"] = data.goal_reason;
DBaser["annotation_text"] = data.annotation_text;
DBaser["annotation_author_id"] = data.annotation_author_id;
DBaser["annotation_author_name"] = data.annotation_author_name;
DBaser["represented_organization_id"] = data.represented_organization_id;
DBaser["represented_organization_name"] = data.represented_organization_name;
DBaser.AcceptChanges();
return(data);
}