/// <summary>
/// 根据实例主键进行更新
/// </summary>
/// <param name="action">更新动作</param>
/// <param name="conditions">更新的条件</param>
public static bool UpdateWhere <T>(Expression <Action <T> > action, Expression <Func <T, bool> > conditions) where T : OrmBaseModel
{
SqlVisitor visitor = new SqlVisitor();
visitor.Visit(action);
var setStr = visitor.GetSql();
var setPrarmes = visitor.GetParameters();
visitor.Visit(conditions);
var whereStr = visitor.GetSql();
var wherePrarmes = visitor.GetParameters();
var sqlStr = SqlCache <T> .GetSql(SqlType.UpdateWhere) + setStr + " WHERE " + whereStr;
var allParames = new List <MySqlParameter>();
allParames.AddRange(setPrarmes);
allParames.AddRange(wherePrarmes);
return(ExceteSql <bool>(sqlStr, allParames.ToArray(), command =>
{
var result = command.ExecuteNonQuery();
return result >= 1;
}));
}
///<summary>
/// 按照主键进行查找对应的数据库数据
///</summary>
public static T Find <T>(string key) where T : OrmBaseModel
{
if (string.IsNullOrEmpty(key))
{
throw new ArgumentNullException("primary key can't be null");
}
var sqlStr = SqlCache <T> .GetSql(SqlType.Find);
var parameters = SqlCache <T> .GetFindMySqlParameter(key);
return(ExceteSql <T>(sqlStr, parameters, command =>
{
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
Type type = typeof(T);
PropertyInfo[] properies = SqlCache <T> .AllProperties;
T result = Activator.CreateInstance <T>();
foreach (var proerty in properies)
{
var value = reader[proerty.GetMappingName()];
proerty.SetValue(result, value is DBNull ? null : value);
}
return result;
}
return default(T);
}));
}
public override void Update(UserModel model)
{
var Params = new ArrayList()
{
model.loginName,
model.Password,
model.AccessLevel
};
if ((int)Enums.AccessLevel.Admin == model.AccessLevel)
{
Params.Add(DBNull.Value);
Params.Add(DBNull.Value);
}
else if ((int)Enums.AccessLevel.CompanyAdmin == model.AccessLevel)
{
Params.Add(model.CompanyKey);
Params.Add(DBNull.Value);
}
else if ((int)Enums.AccessLevel.PharmacyAdmin == model.AccessLevel || (int)Enums.AccessLevel.Operator == model.AccessLevel)
{
Params.Add(DBNull.Value);
Params.Add(model.CompanyKey);
}
Params.Add(model.ID);
Params.Add(model.UserName);
Params.Add(model.Email);
Params.Add(model.ContactNumber);
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("UpdateUser"));
MessageCollection.copyFrom(sql.Messages);
}
/// <summary> Gets delete statememt. </summary>
/// <param name="type"> The type. </param>
/// <returns> The delete statememt. </returns>
public virtual string GetDeleteStatememt(Type type)
{
var key = SqlCache.TypeKeyPropertiesCache(type).Single();
return
($"DELETE FROM {RenderTableName(type)} WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}");
}
public void FillCompanyDllAction(int accessLevel)
{
SQLHandler sql = new SQLHandler();
if ((int)Enums.AccessLevel.CompanyAdmin == accessLevel)
{
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetUserPharmaCompany"));
}
else if ((int)Enums.AccessLevel.PharmacyAdmin == accessLevel || (int)Enums.AccessLevel.Operator == accessLevel)
{
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetUserPharmacy"));
}
MessageCollection.copyFrom(sql.Messages);
if (dt == null || dt.Rows.Count <= 0)
{
MessageCollection.addMessage(new Message()
{
Context = "UsersHandler",
ErrorCode = ErrorCache.RecordsNotFound,
ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.RecordsNotFound) + " in Company/Pharmacy Table",
isError = true,
LogType = Enums.LogType.Exception,
WebPage = "Users"
});
}
}
public override void DoAction()
{
ArrayList Params = new ArrayList()
{
userName,
password
};
SQLHandler sql = new SQLHandler(Params);
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("UserLogin"));
MessageCollection.copyFrom(sql.Messages);
if (!MessageCollection.isErrorOccured)
{
if (dt != null && dt.Rows.Count > 0)
{
Session[Enums.SessionName.UserDetails.ToString()] = dt;
accessLevel = Convert.ToInt16(dt.Rows[0]["accesslevel"].ToString());
}
else if (dt == null || dt.Rows.Count <= 0)
{
MessageCollection.addMessage(new Message()
{
Context = "LoginHandler",
ErrorCode = ErrorCache.LoginFailed,
ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.LoginFailed),
isError = true,
LogType = Enums.LogType.Exception,
WebPage = "Login"
});
}
}
}
/// <summary> Gets by identifier statement. </summary>
/// <param name="type"> The type. </param>
/// <returns> The by identifier statement. </returns>
public virtual string GetByKeyStatement(Type type)
{
var key = SqlCache.TypeKeyPropertiesCache(type).Single();
return
($"SELECT {RenderPropertyList(SqlCache.TypePropertiesChache(type).ToArray())} FROM {RenderTableName(type)} WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}");
}
public void GetMedicineAgainstQRCode(string qrcode, DataTable viewData, int pharmacyid)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
qrcode
});
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetMedicinesForQRCode"));
MessageCollection.copyFrom(sql.Messages);
if (dt == null || dt.Rows.Count <= 0)
{
MessageCollection.addMessage(new Message()
{
Context = "MedicineHandler",
ErrorCode = ErrorCache.RecordsNotFound,
ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.RecordsNotFound),
isError = true,
LogType = Enums.LogType.Exception,
WebPage = "Medicine"
});
}
Decrementquantity(dt.Rows[0].Field <int>("pharmacyid"), dt.Rows[0].Field <int>("medicineid"));
viewData.Rows.Add(dt.Rows[0]);
dt = viewData;
}
/// <summary>
/// 批量获取符合条件的数据
/// </summary>
/// <param name="conditions">查找的条件</param>
public static IList <T> FindWhere <T>(Expression <Func <T, bool> > conditions) where T : OrmBaseModel
{
SqlVisitor visitor = new SqlVisitor();
visitor.Visit(conditions);
var sqlStr = SqlCache <T> .GetSql(SqlType.FindWhere) + visitor.GetSql();
var parameters = visitor.GetParameters();
return(ExceteSql <IList <T> >(sqlStr, parameters, command =>
{
var result = new List <T>();
var type = typeof(T);
var properies = SqlCache <T> .AllProperties;
var adapter = new MySqlDataAdapter(command);
var dataSet = new DataSet();
var count = adapter.Fill(dataSet);
for (var i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
T t = Activator.CreateInstance <T>();
foreach (var proerty in properies)
{
var value = dataSet.Tables[0].Rows[i][proerty.GetMappingName()];
proerty.SetValue(t, value is DBNull ? null : value);
}
result.Add(t);
}
return result;
}));
}
/// <summary> Gets by filter statement. </summary>
/// <param name="type"> The type. </param>
/// <param name="filterProperties"> The filter properties. </param>
/// <param name="selectFields"> The select fields. </param>
/// <returns> The by filter statement. </returns>
public virtual string GetByFilterStatement(Type type, string[] filterProperties, string[] selectFields)
{
var fProps = SqlCache.TypePropertiesChache(type).Where(pi => filterProperties.Contains(pi.Name)).ToArray();
var sb = new StringBuilder();
for (var i = 0; i < filterProperties.Length; i++)
{
if (i > 0)
{
sb.Append(" AND ");
}
sb.Append($"{RenderPropertyName(fProps[i])} = {RenderParameterProperty(fProps[i])}");
}
var filterSql = sb.ToString();
if (selectFields == null || selectFields.Length < 1)
{
return
($"SELECT {RenderPropertyList(SqlCache.TypePropertiesChache(type).ToArray())} FROM {RenderTableName(type)} WHERE {filterSql}");
}
var sProps = SqlCache.TypePropertiesChache(type).Where(pi => selectFields.Contains(pi.Name)).ToArray();
return
($"SELECT {RenderPropertyList(sProps)} FROM {RenderTableName(type)} WHERE {filterSql}");
}
public string GetSQL(string name)
{
if (SqlCache.TryGetValue(name, out var value))
{
return(value[0]);
}
throw new KeyNotFoundException($"SQL element with name {name} is not found.");
}
/// <summary> Gets the latest. </summary>
/// <returns> The latest. </returns>
public override SigningCredentialEntity GetLatest()
{
var command =
$"SELECT TOP 1 {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(SigningCredentialEntity)).ToArray())} FROM {TableName} ORDER BY {nameof(SigningCredentialEntity.Issued)} DESC";
return(UnitOfWork.Connection.QuerySingleOrDefault <SigningCredentialEntity>(command, null,
UnitOfWork.Transaction));
}
/// <summary> Finds the identifiers in this collection. </summary>
/// <param name="roleIds"> List of identifiers for the roles. </param>
/// <returns>
/// An enumerator that allows foreach to be used to process the identifiers in this collection.
/// </returns>
public override IEnumerable <IdentityRoleEntity> FindByIds(IEnumerable <int> roleIds)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityRoleEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(IdentityUserEntity.Id)}\" = ANY(@Ids)";
return(UnitOfWork.Connection.Query <IdentityRoleEntity>(command, new { Ids = roleIds.ToArray() },
UnitOfWork.Transaction));
}
/// <summary> Gets the scope identifiers in this collection. </summary>
/// <param name="ids"> The identifiers. </param>
/// <returns>
/// An enumerator that allows foreach to be used to process the scope identifiers in this
/// collection.
/// </returns>
public override IEnumerable <IdentityResourceScopeEntity> GetByScopeIds(int[] ids)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityResourceScopeEntity)).ToArray())} FROM {TableName} WHERE {nameof(IdentityResourceScopeEntity.ScopeId)} IN @Ids";
return(UnitOfWork.Connection.Query <IdentityResourceScopeEntity>(command, new { Ids = ids },
UnitOfWork.Transaction));
}
/// <summary> Gets validation valid. </summary>
/// <param name="validSince"> The valid since Date/Time. </param>
/// <returns> The validation valid. </returns>
public override IList <SigningCredentialEntity> GetValidationValid(DateTime validSince)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(SigningCredentialEntity)).ToArray())} FROM {TableName} WHERE {nameof(SigningCredentialEntity.Issued)} > @validSince";
return(UnitOfWork.Connection.Query <SigningCredentialEntity>(command, new { ValidSince = validSince },
UnitOfWork.Transaction).ToList());
}
/// <summary> Gets the names in this collection. </summary>
/// <param name="names"> The names. </param>
/// <returns>
/// An enumerator that allows foreach to be used to process the names in this collection.
/// </returns>
public override IEnumerable <ScopeEntity> GetByNames(string[] names)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(ScopeEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(ScopeEntity.Name)}\" = ANY(@Names)";
return(UnitOfWork.Connection.Query <ScopeEntity>(command, new { Names = names },
UnitOfWork.Transaction));
}
/// <summary> Gets the name compounts in this collection. </summary>
/// <param name="name"> The name. </param>
/// <returns>
/// An enumerator that allows foreach to be used to process the name compounts in this collection.
/// </returns>
public override CompoundApiResource GetByNameCompount(string name)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(ApiResourceEntity)).ToArray())} FROM {TableName} AS aRes" +
$" LEFT JOIN {UnitOfWork.GetRepository<IApiResourceScopeRepository>().TableName} AS aScope" +
$" ON aRes.{nameof(ApiResourceEntity.Id)} = aScope.{nameof(ApiResourceScopeEntity.ApiResourceId)}" +
$" LEFT JOIN {UnitOfWork.GetRepository<IApiResourceClaimRepository>().TableName} AS aClaim" +
$" ON aRes.{nameof(ApiResourceEntity.Id)} = aClaim.{nameof(ApiResourceClaimEntity.ApiResourceId)}" +
$" LEFT JOIN {UnitOfWork.GetRepository<IScopeRepository>().TableName} AS scope" +
$" ON aScope.{nameof(ApiResourceScopeEntity.ScopeId)} = scope.{nameof(ScopeEntity.Id)}" +
$" WHERE aRes.{nameof(ApiResourceEntity.Name)} = @ResName";
var lookup = new Dictionary <int, CompoundApiResource>();
UnitOfWork.Connection
.Query <ApiResourceEntity, ApiResourceScopeEntity, ApiResourceClaimEntity, ScopeEntity,
CompoundApiResource>(command,
(entity, apiScope, apiClaim, scope) =>
{
// make sure the pk exists
if (entity == null || entity.Id == default(int))
{
return(null);
}
// make sure our list contains the pk
if (!lookup.ContainsKey(entity.Id))
{
lookup.Add(entity.Id, new CompoundApiResource
{
ApiResource = entity
});
}
// fetch the real element
var tempElem = lookup[entity.Id];
// add api-scope
if (apiScope != null)
{
tempElem.ApiResourceScopes.Add(apiScope);
}
// add claim
if (apiClaim != null)
{
tempElem.ApiResourceClaims.Add(apiClaim);
}
// add scope
if (scope != null)
{
tempElem.Scopes.Add(scope);
}
return(tempElem);
}, new { ResName = name }, UnitOfWork.Transaction);
return(lookup.Values.SingleOrDefault());
}
/// <summary> Gets insert automatic key statement. </summary>
/// <param name="type"> The type. </param>
/// <returns> The insert automatic key statement. </returns>
public virtual string GetInsertAutoKeyStatement(Type type)
{
var key = SqlCache.TypeKeyPropertiesCache(type).Single();
var columnList = RenderAutoKeyColumnList(type).ToString();
var parameterList = RenderAutoKeyParameterList(type).ToString();
return
($"INSERT INTO {RenderTableName(type)} ({columnList}) VALUES ({parameterList}){GetAutoKeyStatement(key)}");
}
/// <summary> Gets an identity user entity using the given identifier. </summary>
/// <param name="identifier"> The identifier to get. </param>
/// <returns> An IdentityUserEntity. </returns>
public override IdentityUserEntity Get(string identifier)
{
var command =
$"SELECT {SqlBuilder.Adapter.RenderPropertyList(SqlCache.TypePropertiesChache(typeof(IdentityUserEntity)).ToArray())} FROM {TableName} WHERE \"{nameof(IdentityUserEntity.Identifier)}\" = @Identifier";
return(UnitOfWork.Connection.QuerySingleOrDefault <IdentityUserEntity>(command,
new { Identifier = Guid.Parse(identifier) },
UnitOfWork.Transaction));
}
public void Init(SqlCache sqlCache)
{
var name = GetServerName();
sqlCache.AddSqlType(name);
sqlCache.AddProvider(name, () => new SqliteCommonProvider());
sqlCache.AddProvider(name, () => new SqliteFunctionProvider());
sqlCache.AddProvider(name, () => new SqliteProcedureProvider());
sqlCache.AddProvider(name, () => new SqliteViewProvider());
}
public void Init(SqlCache sqlCache)
{
var name = GetServerName();
sqlCache.AddSqlType(name);
sqlCache.AddProvider(name, () => new PostgreSQLCommonProvider());
sqlCache.AddProvider(name, () => new PostgreSQLFunctionProvider());
sqlCache.AddProvider(name, () => new PostgreSQLProcedureProvider());
sqlCache.AddProvider(name, () => new PostgreSQLViewProvider());
}
/// <summary> Gets update statement. </summary>
/// <param name="type"> The type. </param>
/// <returns> The update statement. </returns>
public virtual string GetUpdateStatement(Type type)
{
var key = SqlCache.TypeKeyPropertiesCache(type).Single();
var setClauses = RenderSetStatements(type).ToString();
return
($"UPDATE {RenderTableName(type)} " +
$"SET {setClauses} " +
$"WHERE {RenderPropertyName(key)} = {RenderParameterProperty(key)}");
}
public override void Update(PharmacyInventoryModel Model)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
Model.ID
});
sql.ExecuteNonQuery(SqlCache.GetSql("UpdatePharmacyPOSInventory"));
MessageCollection.copyFrom(sql.Messages);
}
public static List <InvoiceCatalogModel> FindInvoices(DSModel db, InvoiceCatalogFilter filter)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (filter == null)
{
throw new ArgumentNullException("filter");
}
string sql = SqlCache.Get(db, "invoice catalog load");
List <MySqlParameter> par = new List <MySqlParameter>();
if (!string.IsNullOrWhiteSpace(filter.InvoiceNumber))
{
sql = sql.Replace("#InvoiceNumber", string.Empty);
par.Add(new MySqlParameter("InvoiceNumber", filter.InvoiceNumber + "%"));
}
if (filter.IssuedFrom.HasValue && filter.IssuedFrom.Value != DateTime.MinValue)
{
sql = sql.Replace("#IssuedFrom", string.Empty);
par.Add(new MySqlParameter("IssuedFrom", filter.IssuedFrom.Value.Date));
}
if (filter.IssuedTo.HasValue && filter.IssuedTo.Value != DateTime.MinValue)
{
sql = sql.Replace("#IssuedTo", string.Empty);
par.Add(new MySqlParameter("IssuedTo", filter.IssuedTo.Value.Date));
}
if (filter.PeriodFrom.HasValue && filter.PeriodFrom.Value != DateTime.MinValue)
{
sql = sql.Replace("#PeriodFrom", string.Empty);
par.Add(new MySqlParameter("PeriodFrom", filter.PeriodFrom.Value.Date));
}
if (filter.PeriodTo.HasValue && filter.PeriodTo.Value != DateTime.MinValue)
{
sql = sql.Replace("#PeriodTo", string.Empty);
par.Add(new MySqlParameter("PeriodTo", filter.PeriodTo.Value.Date));
}
if (!string.IsNullOrWhiteSpace(filter.CompanyID))
{
sql = sql.Replace("#CompanyID", string.Empty);
sql = sql.Replace("@CompanyID", filter.CompanyID);
}
if (!string.IsNullOrWhiteSpace(filter.LocationID))
{
sql = sql.Replace("#LocationID", string.Empty);
sql = sql.Replace("@LocationID", filter.LocationID);
}
return(db.ExecuteQuery <InvoiceCatalogModel>(sql, par.ToArray()).ToList());
}
public void AddAndGetTest()
{
var cache = new SqlCache();
cache.Add(typeof(TestModel), "SELECT", null, null, "Test");
Assert.AreEqual(1, cache.Count);
var sql = cache.Get(typeof(TestModel), "SELECT", null, null);
Assert.AreEqual("Test", sql.SqlString);
}
public (string CountSQL, string QuerySQL) GetPagingSQL(string name)
{
if (!SqlCache.TryGetValue(name, out var value))
{
throw new KeyNotFoundException($"SQL element with name {name} is not found.");
}
if (value.Length != 2)
{
throw new ArgumentException("Wrong paging SQL configuration.");
}
return(value[0], value[1]);
}
public void GetItem()
{
string key = "GetItem";
string data = "data";
SqlCache cache = new SqlCache(connectionString);
cache.Add(key, data, DateTime.Now.AddMinutes(30));
var getData = cache.Get(key);
Assert.AreEqual(data, getData);
cache.Remove(key); // Clean DB for further testing
}
public void Decrementquantity(int pharmID, int medid)
{
var Params = new ArrayList()
{
pharmID, medid
};
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("DecrementPharmacyInventory"));
MessageCollection.copyFrom(sql.Messages);
}
public override void Delete(int ID)
{
var Params = new ArrayList()
{
ID
};
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("DeleteMedicine"));
MessageCollection.copyFrom(sql.Messages);
}
public void AddItem()
{
string key = "AddItem";
string data = "data";
SqlCache cache = new SqlCache(connectionString);
cache.Add(key, data, DateTime.Now.AddMinutes(30));
var result = cache.Contains(key);
Assert.AreEqual(result, true);
cache.Remove(key); // Clean DB for further testing
}
public void RemoveItem()
{
var key = "RemoveItem";
var data = "data";
var cache = new SqlCache(connectionString, serializer);
cache.Add(key, data, DateTime.Now.AddMinutes(30));
cache.Remove(key);
var result = cache.Contains(key);
Assert.AreEqual(result, false);
}
public void RemoveItem()
{
string key = "RemoveItem";
string data = "data";
SqlCache cache = new SqlCache(connectionString);
cache.Add(key, data, DateTime.Now.AddMinutes(30));
cache.Remove(key);
var result = cache.Contains(key);
Assert.AreEqual(result, false);
}
public void GetItem()
{
var key = "GetItem";
var data = "data";
var cache = new SqlCache(connectionString, serializer);
cache.Add(key, data, DateTime.Now.AddMinutes(30));
var getData = cache.Get(key);
Assert.AreEqual(data, getData);
cache.Remove(key); // Clean DB for further testing
}
public void AddOrGetExisting()
{
string key = "AddOrGetExisting";
string data = "data";
SqlCache cache = new SqlCache(connectionString);
// Try to get data
var getData = cache.Get(key);
Assert.IsNull(getData);
// Add new entry
getData = cache.AddOrGetExisting(key, data, DateTime.Now.AddMinutes(30));
Assert.IsNull(getData);
// Retrieve added entry
getData = cache.AddOrGetExisting(key, data, DateTime.Now.AddMinutes(30));
Assert.IsNotNull(getData);
cache.Remove(key); // Clean DB for further testing
}
