///<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);
}));
}
/// <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;
}));
}
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);
}
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 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="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;
}));
}
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"
});
}
}
}
public override void Update(PharmacyInventoryModel Model)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
Model.ID
});
sql.ExecuteNonQuery(SqlCache.GetSql("UpdatePharmacyPOSInventory"));
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 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 Insert(PharmacyInventoryModel model)
{
var Params = new ArrayList()
{
model.MedicineID,
model.PharmacyID,
model.Quantity,
};
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("InsertPharmacyInventory"));
MessageCollection.copyFrom(sql.Messages);
}
internal void DoSqlAction(MailingModel request)
{
Token = Guid.NewGuid().ToString();
ArrayList Params = new ArrayList()
{
request.ID,
request.EmailTo,
request.Hours,
Token,
false
};
SQLHandler sqlHandler = new SQLHandler(Params);
sqlHandler.ExecuteNonQuery(SqlCache.GetSql("InsertMail"));
}
public override void Insert(PharmaCompanyModel model)
{
var Params = new ArrayList()
{
model.Name,
model.Email,
model.ContactNumber,
model.Description,
model.Address
};
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("InsertPharmaCompany"));
MessageCollection.copyFrom(sql.Messages);
}
public void InsertCustomer(PharmacyInventoryModel Model)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
Model.CustomerName
});
sql.ExecuteNonQuery(SqlCache.GetSql("InsertCustomer"));
MessageCollection.copyFrom(sql.Messages);
sql = new SQLHandler(new ArrayList()
{
Model.CustomerName, Model.Amount
});
sql.ExecuteNonQuery(SqlCache.GetSql("InsertPurchaseDetails"));
MessageCollection.copyFrom(sql.Messages);
}
/// <summary>
/// 根据实例主键进行更新
/// </summary>
public static bool Update <T>(T t) where T : OrmBaseModel
{
if (null == t)
{
throw new ArgumentNullException("instance can't be null");
}
var parameters = SqlCache <T> .GetUpdateMySqlParameters(t);
var sqlStr = SqlCache <T> .GetSql(SqlType.Update);
return(ExceteSql <bool>(sqlStr, parameters, command =>
{
var result = command.ExecuteNonQuery();
return result == 1;
}));
}
/// <summary>
/// 按主键值进行删除
/// </summary>
public static bool Deleate <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.Deleate);
var parameters = SqlCache <T> .GetDelMySqlParameters(key);
return(ExceteSql <bool>(sqlStr, parameters, command =>
{
var result = command.ExecuteNonQuery();
return result == 1;
}));
}
public override void Insert(MedicineModel model)
{
var Params = new ArrayList()
{
model.Name,
model.QRCode,
model.ExpiryDate,
model.MfgDate,
model.BatchNo,
model.Price,
model.PharmaCompanyID
};
SQLHandler sql = new SQLHandler(Params);
sql.ExecuteNonQuery(SqlCache.GetSql("InsertMedicine"));
MessageCollection.copyFrom(sql.Messages);
}
internal void DoSqlUpdateAction(string token, string EmailFrom)
{
ArrayList Params = new ArrayList()
{
token,
EmailFrom
};
SQLHandler sqlHandler = new SQLHandler(Params);
DataTable dt = sqlHandler.ExecuteSqlReterieve(SqlCache.GetSql("CheckToken"));
if (dt != null && dt.Rows.Count > 0)
{
if (!Convert.ToBoolean(dt.Rows[0]["isUsed"]))
{
sqlHandler = new SQLHandler(Params);
sqlHandler.ExecuteNonQuery(SqlCache.GetSql("UpdateToken"));
}
else
{
messageCollection.addMessage(
new Message()
{
Context = "MailingHandler",
ErrorCode = 1,
ErrorMessage = "The Token is already Used",
isError = true,
LogType = Enums.LogType.Exception
});
}
}
else
{
messageCollection.addMessage(
new Message()
{
Context = "MailingHandler",
ErrorCode = 1,
ErrorMessage = "There is no token with that signature",
isError = true,
LogType = Enums.LogType.Exception
});
}
}
public override void DoFillGridAction()
{
SQLHandler sql = new SQLHandler();
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetMedicines"));
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"
});
}
}
/// <summary>
/// 根据表达式目录树，批量的删除
/// </summary>
/// <param name="conditions">删除的条件</param>
public static bool DeleateWhere <T>(Expression <Func <T, bool> > conditions) where T : OrmBaseModel
{
if (null == conditions)
{
return(false);
}
SqlVisitor visitor = new SqlVisitor();
visitor.Visit(conditions);
var sqlStr = SqlCache <T> .GetSql(SqlType.DeleateWhere) + visitor.GetSql();
var parameters = visitor.GetParameters();
return(ExceteSql <bool>(sqlStr, parameters, command =>
{
var result = command.ExecuteNonQuery();
return result >= 1;
}));
}
public override void DoFillBackPanelAction(int ID)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
ID
});
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetUser"));
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),
isError = true,
LogType = Enums.LogType.Exception,
WebPage = "Users"
});
}
}
public DataTable GetMedicine(PharmacyInventoryModel model)
{
SQLHandler sql = new SQLHandler(new ArrayList()
{
model.PharmacyID, model.MedicineID
});
dt = sql.ExecuteSqlReterieve(SqlCache.GetSql("GetPOSMedicine"));
MessageCollection.copyFrom(sql.Messages);
if (dt == null || dt.Rows.Count <= 0)
{
MessageCollection.addMessage(new Message()
{
Context = "PharmacyPOSHandler",
ErrorCode = ErrorCache.RecordsNotFound,
ErrorMessage = ErrorCache.getErrorMessage(ErrorCache.RecordsNotFound),
isError = true,
LogType = Enums.LogType.Exception,
WebPage = "PharmacyPOS"
});
}
return(dt);
}