/// <summary>
/// 获取家庭成员列表
/// </summary>
/// <param name="familyCode"></param>
/// <param name="userId"></param>
/// <returns></returns>
public List <WeChatAccountModel> GetMembersAccount(string familyCode, long userId)
{
if (string.IsNullOrEmpty(familyCode))
{
return(null);
}
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.Query <WeChatAccountModel>(@"SELECT
wa.AccountId,
wa.AvatarUrl,
wf.CreateTime,
wa.FamilyCode,
wa.Gender,
wa.HadBindFamily,
wa.Id,
wa.Id,
wa.IsDel,
wa.IsDel,
wa.NickName,
wa.OpenId,
wa.Remarks,
wf.UnBindTime UpdateTime
FROM
wechatfamily wf
LEFT JOIN wechataccount wa ON wa.AccountId = wf.UserId
WHERE
wf.FamilyCode = @FamilyCode
AND wa.IsDel = @IsDel
AND ( wf.IsDel = @IsDel OR wf.UserId = @CurrentUserId )" , new
{ IsDel = FlagEnum.HadZore.GetHashCode(), FamilyCode = familyCode, CurrentUserId = userId }).ToList());
}
}
/// <summary>
/// 添加新的用户并绑定微信账户
/// </summary>
/// <param name="sysUser"></param>
/// <param name="openId"></param>
/// <returns></returns>
public long InsertAndBindWechatUser(SysUser sysUser, string openId)
{
if (sysUser == null)
{
return(0);
}
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
var wechatUser = conn.GetList <WeChatAccountModel>(new { IsDel = FlagEnum.HadZore.GetHashCode(), OpenId = openId })?.FirstOrDefault();
if (wechatUser == null || wechatUser.AccountId > 0)
{
return(-1);
}
IDbTransaction transaction = conn.BeginTransaction();
try
{
var inId = conn.Insert <long, SysUser>(sysUser, transaction);
wechatUser.AccountId = inId;
wechatUser.UpDateTime = DateTime.Now;
conn.Update(wechatUser, transaction);
transaction.Commit();
return(inId);
}
catch (Exception e)
{
if (Debugger.IsAttached)
{
Trace.WriteLine("事务处理失败：" + e.Message);
}
transaction.Rollback();
return(0);
}
}
}
/// <summary>
/// 解除用户绑定
/// </summary>
/// <param name="familyModel"></param>
/// <param name="weChatAccountModel"></param>
/// <returns></returns>
public bool UnBindFamilyAndUser(WechatFamilyModel familyModel, WeChatAccountModel weChatAccountModel)
{
if (familyModel == null || weChatAccountModel == null)
{
return(false);
}
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
conn.Update(familyModel, transaction);
conn.Update(weChatAccountModel, transaction);
transaction.Commit();
return(true);
}
catch (Exception e)
{
if (Debugger.IsAttached)
{
Trace.WriteLine("事务处理失败：" + e.Message);
}
transaction.Rollback();
return(false);
}
}
}
/// <summary>
/// 获取数据库中的表
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
public List <Table> GetTableList(string connectionstring, string databaseName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionstring))
{
List <Table> tables = new List <Table>();
string sqlCmd = "SELECT table_name,cluster_name FROM user_tables";
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.GetString(0);
string displayName = dr.GetString(0);
string name = dr.GetString(0);
string comment = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
Table table = new Table(id, displayName, name, comment)
{
OriginalName = name
};
tables.Add(table);
}
dr.Close();
return(tables);
}
}
/// <summary>
/// 查询指定用户的类型，名称
/// </summary>
/// <param name="spendType"></param>
/// <param name="userId"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <CostTypeModel> GetModels(int spendType, long userId, string name)
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetList <CostTypeModel>(new { SpendType = spendType, UserId = userId, Name = name, IsDel = FlagEnum.HadZore.GetHashCode() }).ToList());
}
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="id"></param>
public void DelUserModel(int id)
{
if (id < 1)
{
return;
}
var model = GetSysUser(id);
if (model == null)
{
return;
}
model.IsDel = FlagEnum.HadOne;
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
conn.Update(model);
conn.Execute("update sysusermenu set IsDel=@Del where UserId=@UserId and IsDel=@NotDel",
new
{
Del = FlagEnum.HadOne.GetHashCode(),
NotDel = FlagEnum.HadZore.GetHashCode(),
UserId = id
});
}
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="hasDeal"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <CustomercommentModel> GetModels(DateTime beginTime, DateTime endTime, FlagEnum hasDeal, int pageIndex, int pageSize)
{
var where = new StringBuilder(" where HasDeal=@HasDeal and IsDel=@IsDel ");
if (beginTime > new DateTime(1900, 1, 1))
{
where.Append(" and CreateTime>=@BeginTime ");
}
if (endTime > new DateTime(1900, 1, 1))
{
where.Append(" and CreateTime<@EndTime ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
HasDeal = hasDeal.GetHashCode(),
BeginTime = beginTime,
EndTime = endTime
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetListPaged <CustomercommentModel>(pageIndex, pageSize, where.ToString(), " CreateTime desc ", param)?.ToList());
}
}
/// <summary>
/// GetViews
/// </summary>
/// <param name="connectionString"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
public List <View> GetViews(string connectionString, string databaseName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionString))
{
List <View> views = new List <View>();
string sqlCmd = $"select view_name,editioning_view from user_views";
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.GetString(0);
string displayName = dr.GetString(0);
string name = dr.GetString(0);
string comment = string.Empty;
View view = new View(id, displayName, name, comment)
{
OriginalName = name
};
views.Add(view);
}
dr.Close();
return(views);
}
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <returns></returns>
public int GetCount(List <long> userIds, int isValid, string name)
{
var where = new StringBuilder(" where UserId in @UserId ");
where.Append(" and IsDel=@IsDel ");
if (isValid >= 0)
{
where.Append(" and IsValid=@IsValid ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and CostChannelName like @CostChannelName ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
UserId = userIds.ToArray(),
IsValid = isValid,
CostChannelName = "%" + name + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <CostChannelModel>(where.ToString(), param));
}
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="userIds"></param>
/// <param name="isValid"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <CostChannelModel> GetModels(List <long> userIds, int isValid, int pageIndex, int pageSize, string name)
{
var where = new StringBuilder(" where UserId in @UserId ");
where.Append(" and IsDel=@IsDel ");
if (isValid >= 0)
{
where.Append(" and IsValid=@IsValid ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and CostChannelName like @CostChannelName ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
UserId = userIds.ToArray(),
IsValid = isValid,
CostChannelName = "%" + name + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetListPaged <CostChannelModel>(pageIndex, pageSize, where.ToString(), " Sort desc ", param)?.ToList());
}
}
/// <summary>
/// 获取所有的菜单信息
/// </summary>
/// <returns></returns>
public List <SysMenuModel> GetAllMenuModels()
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetList <SysMenuModel>("where IsDel=@IsDel", new { IsDel = FlagEnum.HadZore.GetHashCode() })?.OrderBy(f => f.OrderNo).ToList());
}
}
/// <summary>
/// 获取总数
/// </summary>
/// <param name="preCode"></param>
/// <param name="cardNumber"></param>
/// <param name="name"></param>
/// <returns></returns>
public int GetCount(string preCode, string cardNumber, string name)
{
var where = new StringBuilder(" where 1=1 ");
if (!string.IsNullOrEmpty(preCode))
{
where.Append(" AND CardNumber like @preCardNumber ");
}
if (!string.IsNullOrEmpty(cardNumber))
{
where.Append(" AND CardNumber like @CardNumber ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and Name like @Name ");
}
var param = new
{
Name = "%" + name + "%",
preCardNumber = preCode + "%",
CardNumber = "%" + cardNumber + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <SysIdCardContentModel>(where.ToString(), param));
}
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <returns></returns>
public int GetCount(int spendType, List <long> userIds, string name)
{
var where = new StringBuilder(" where UserId in @UserId ");
where.Append(" and IsDel=@IsDel ");
if (spendType != -1)
{
where.Append(" and SpendType= @Type ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and Name like @Name ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
Type = spendType,
UserId = userIds.ToArray(),
Name = "%" + name + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <CostTypeModel>(where.ToString(), param));
}
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <returns></returns>
public int GetCount(DateTime beginTime, DateTime endTime, FlagEnum hasDeal)
{
var where = new StringBuilder(" where HasDeal=@HasDeal and IsDel=@IsDel ");
if (beginTime > new DateTime(1900, 1, 1))
{
where.Append(" and CreateTime>=@BeginTime ");
}
if (endTime > new DateTime(1900, 1, 1))
{
where.Append(" and CreateTime<@EndTime ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
HasDeal = hasDeal.GetHashCode(),
BeginTime = beginTime,
EndTime = endTime
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <CustomercommentModel>(where.ToString(), param));
}
}
/// <summary>
/// 保存信息
/// </summary>
/// <param name="saveModel"></param>
public void SaveModel(InusekeyinfoModel saveModel)
{
if (saveModel == null)
{
return;
}
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
saveModel.UseYear = saveModel.UseDate.Year;
saveModel.UseMonth = saveModel.UseDate.Month;
saveModel.UseDay = saveModel.UseDate.Day;
if (saveModel.Id < 1)
{
//新增
var newId = conn.Insert(saveModel);
if (newId != null)
{
saveModel.Id = newId.Value;
}
}
//修改
conn.Update(saveModel);
}
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <returns></returns>
public int GetCount(string procedureCode, long userId)
{
var where = new StringBuilder(" where IsDel=@IsDel ");
if (!string.IsNullOrEmpty(procedureCode))
{
where.Append(" and ProcedureCode= @ProcedureCode ");
}
if (userId > -1)
{
where.Append(" and CustomerId=@CustomerId ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
ProcedureCode = procedureCode,
CustomerId = userId
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <ProcedureHistoryModel>(where.ToString(), param));
}
}
/// <summary>
/// 用户登陆
/// </summary>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
public LoginInfoModel UserLogin(string userName, string password)
{
var userInfo = new LoginInfoModel();
List <SysUser> userList;
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
userList = conn.GetList <SysUser>(new { UserName = userName, IsDel = FlagEnum.HadZore.GetHashCode(), Password = password })?.ToList();
}
if (userList == null || userList.Count < 1)
{
userInfo.IsLogin = false;
userInfo.ErrMessage = "用户名或密码错误";
return(userInfo);
}
var u = userList.First();
userInfo.ClientIp = Fetch.UserIp;
userInfo.IsLogin = true;
userInfo.Id = u.Id;
userInfo.UserId = u.Id;
userInfo.LoginName = userName;
userInfo.TrueName = u.TrueName;
userInfo.HeadUrl = u.HeadUrl;
userInfo.DepartName = string.Empty;
userInfo.JobNumber = string.Empty;
return(userInfo);
}
/// <summary>
/// 获取单个用户
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public SysUser GetSysUser(long id)
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.Get <SysUser>(id));
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="preCode"></param>
/// <param name="cardNumber"></param>
/// <param name="name"></param>
/// <param name="indexPage"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <SysIdCardContentModel> GetModels(string preCode, string cardNumber, string name, int indexPage, int pageSize)
{
var where = new StringBuilder(" where 1=1 ");
if (!string.IsNullOrEmpty(preCode))
{
where.Append(" AND CardNumber like @preCardNumber ");
}
if (!string.IsNullOrEmpty(cardNumber))
{
where.Append(" AND CardNumber like @CardNumber ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and Name like @Name ");
}
var param = new
{
Name = "%" + name + "%",
preCardNumber = preCode + "%",
CardNumber = "%" + cardNumber + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetListPaged <SysIdCardContentModel>(indexPage, pageSize, where.ToString(), " Id desc ", param)?.ToList());
}
}
/// <summary>
/// test sql server
/// </summary>
private static void RunTestSqlServer()
{
var stopwatch = Stopwatch.StartNew();
var sqltester = new SimpleCrudTest(SimpleCRUD.Dialect.SQLServer);
foreach (var method in typeof(SimpleCrudTest).GetMethods(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly))
{
var testwatch = Stopwatch.StartNew();
Console.Write($"Running {method.Name} in sql server");
method.Invoke(sqltester, null);
testwatch.Stop();
Console.WriteLine($" -√ {testwatch.ElapsedMilliseconds}'ms ");
}
stopwatch.Stop();
//write result
Console.WriteLine($"Time elapsed :{stopwatch.Elapsed}");
using (var connection = SqlConnectionHelper.GetOpenConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Master;Integrated Security=True"))
{
try
{
connection.Execute(@" alter database DapperSimpleCrudTestDb set single_user with rollback immediate; DROP DATABASE DapperSimpleCrudTestDb; ");
}
catch (Exception)
{
}
}
Console.Write("SQL Server testing complete.");
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <returns></returns>
public int GetSysUserCount()
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.RecordCount <SysUser>(new { IsDel = FlagEnum.HadZore.GetHashCode() }));
}
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="spendType"></param>
/// <param name="userIds"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="name"></param>
/// <returns></returns>
public List <CostTypeModel> GetModels(int spendType, List <long> userIds, int pageIndex, int pageSize, string name)
{
var where = new StringBuilder(" where UserId in @UserId ");
where.Append(" and IsDel=@IsDel ");
if (spendType != -1)
{
where.Append(" and SpendType= @Type ");
}
if (!string.IsNullOrEmpty(name))
{
where.Append(" and Name like @Name ");
}
var param = new
{
IsDel = FlagEnum.HadZore.GetHashCode(),
Type = spendType,
UserId = userIds.ToArray(),
Name = "%" + name + "%"
};
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetListPaged <CostTypeModel>(pageIndex, pageSize, where.ToString(), " Sort desc ", param)?.ToList());
}
}
/// <summary>
/// GetKeys
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sqlCmd"></param>
/// <returns></returns>
private Columns GetKeys(string connectionString, string sqlCmd)
{
Columns columns = new Columns(5);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (var conn = SqlConnectionHelper.GetOpenConnection(connectionString))
{
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string displayName = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string name = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string dataType = "";
string defaultValue = "";
string isNullable = "Y";
string length = "";
string identity = string.Empty;
string comment = "主键";
Column column = new Column(id, displayName, name, dataType, comment);
column.Length = DataTypeConvertHelper.ToInt(length);
column.IsAutoIncremented = false;
column.IsNullable = isNullable.Equals("Y");
column.DefaultValue = defaultValue.ToEmpty();
column.DataType = dataType;
column.OriginalName = name;
columns.Add(id, column);
}
dr.Close();
return(columns);
}
}
/// <summary>
/// 获取列表用户
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <SysUser> GetSysUsers(int pageIndex, int pageSize)
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.GetListPaged <SysUser>(pageIndex, pageSize, " where IsDel=@IsDel ", null,
new { IsDel = FlagEnum.HadZore.GetHashCode() })?.ToList());
}
}
/// <summary>
/// 查询配置文件信息
/// </summary>
/// <returns></returns>
public List <DataBaseLinkConfigModel> GetConfigModels()
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.GetList <DataBaseLinkConfigModel>();
return(result.ToList());
}
public ActionResult Delete(int id)
{
using (_connection = SqlConnectionHelper.GetOpenConnection())
{
_connection.Delete <CarViewModel>(id);
}
return(RedirectToAction("index"));
}
/// <summary>
/// 查询指定条件配置信息
/// </summary>
/// <param name="linkName"></param>
/// <returns></returns>
public DataBaseLinkConfigModel GetConfigModelByLinkName(string linkName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.GetList <DataBaseLinkConfigModel>($" where LinkName='{linkName}' ");
return(result.FirstOrDefault());
}
/// <summary>
/// 更新配置文件
/// </summary>
/// <param name="newModel"></param>
/// <returns></returns>
public bool UpdateConfigModel(DataBaseLinkConfigModel newModel)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.Update(newModel);
return(result > 0);
}
/// <summary>
/// 根据id删除资源信息
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int DeleteResourceById(List <long> ids)
{
var sql = @"update dailystoryresource set IsDel=@IsDel where Id in @Ids and IsDel!=@IsDel";
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(conn.Execute(sql, new { Ids = ids.ToArray(), IsDel = FlagEnum.HadOne.GetHashCode() }));
}
}
/// <summary>
/// 获取所有的类型数据
/// </summary>
/// <param name="keyType">类型</param>
/// <returns></returns>
public List <InusekeyinfoModel> GetAllType(string keyType)
{
using (var conn = SqlConnectionHelper.GetOpenConnection())
{
return(string.IsNullOrEmpty(keyType)
? conn.GetList <InusekeyinfoModel>(new { IsDel = FlagEnum.HadZore.GetHashCode() })?.OrderByDescending(f => f.CreateTime).ToList()
: conn.GetList <InusekeyinfoModel>(new { IsDel = FlagEnum.HadZore.GetHashCode(), KeyType = keyType })?.OrderByDescending(f => f.CreateTime).ToList());
}
}
