/// <summary>
/// 获取变化后的商品信息
/// </summary>
/// <returns></returns>
public List <LocalCommodityCode> GetLocalCommodityCodeChange(InventoryChangesApo pageDataApo, out int totalCount)
{
totalCount = 0;
List <LocalCommodityCode> data;
//查询语句
var queryable = SqlSugarHelper.GetInstance().Db.Queryable <LocalCommodityCode>()
.Where((lcc) => lcc.operate_type == pageDataApo.operate_type)
.WhereIF(pageDataApo.startTime.HasValue, (lcc) => lcc.create_time >= pageDataApo.startTime)
.WhereIF(pageDataApo.endTime.HasValue, (lcc) => lcc.create_time <= pageDataApo.endTime)
.WhereIF((!string.IsNullOrEmpty(pageDataApo.name) && !string.IsNullOrWhiteSpace(pageDataApo.name)), (lcc) => lcc.CommodityName.Contains(pageDataApo.name))
.OrderBy((lcc) => lcc.create_time, OrderByType.Desc)
.Select <LocalCommodityCode>();
//如果小于0，默认查全部
if (pageDataApo.PageSize > 0)
{
data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount);
}
else
{
data = queryable.ToList();
totalCount = data.Count();
}
return(data);
}
/// <summary>
/// 批量删除未审批业务员
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int BatchDeleteSalesman(string[] userids)
{
SqlSugarHelper helper = new SqlSugarHelper();
int flag = helper.Db.Deleteable <dt_users>().Where(m => userids.Contains(m.userid) && m.role_type == 3 && m.status == 1).ExecuteCommand();
return(flag);
}
/// <summary>
/// 插入变化后的商品信息
/// </summary>
/// <param name="baseDataCommodityCode">所有数据</param>
/// <param name="sourceBill">业务类型</param>
/// <returns></returns>
public bool InsertLocalCommodityCodeInfo(BaseData <CommodityCode> baseDataCommodityCode, string sourceBill)
{
var result = false;
//校验是否含有数据，如果含有数据，有就继续下一步
baseDataCommodityCode = HttpHelper.GetInstance().ResultCheck(baseDataCommodityCode, out bool isSuccess);
if (isSuccess)
{
List <LocalCommodityCode> localCommodityCodes = baseDataCommodityCode.body.objects.MapToListIgnoreId <CommodityCode, LocalCommodityCode>();
var createTime = DateTime.Now;
var operater = ApplicationState.GetUserInfo().name;
localCommodityCodes.ForEach(it =>
{
it.sourceBill = sourceBill;
it.create_time = createTime;
it.operater = operater;
});
//事务防止多插入产生脏数据
result = SqlSugarHelper.GetInstance().Db.Ado.UseTran(() =>
{
SqlSugarHelper.GetInstance().Db.Insertable(localCommodityCodes).ExecuteCommand();
}).IsSuccess;
}
if (!result)
{
LogUtils.Warn("InsertLocalCommodityCodeInfo" + sourceBill);
}
return(result);
}
/// <summary>
/// 插入扫描的所有商品信息
/// </summary>
/// <param name="currentCommodityEps">当前扫描出来的所有数据</param>
/// <returns></returns>
public bool InsertLocalCommodityEpsInfo(HashSet <CommodityEps> currentCommodityEps)
{
var result = false;
if (currentCommodityEps == null)
{
return(result);
}
//组装最新的扫描结果，一组数据
var localCommodityEps = new LocalCommodityEps
{
commodityEpsList = JsonConvert.SerializeObject(new List <CommodityEps>(currentCommodityEps)),
create_time = DateTime.Now
};
//事务防止多插入产生脏数据
result = SqlSugarHelper.GetInstance().Db.Ado.UseTran(() =>
{
SqlSugarHelper.GetInstance().Db.Insertable(localCommodityEps).ExecuteCommand();
}).IsSuccess;
if (!result)
{
LogUtils.Warn("InsertLocalCommodityEpsInfo 失败" + DateTime.Now);
}
return(result);
}
public static void AddRoleMenu()
{
using (var db = SqlSugarHelper.GetInstance())
{
_funcList = db.Queryable <view_sys_user_role_function>().ToList();
}
}
/// <summary>
/// 判断是否是初次使用本地库存上次，如果是则不上传
/// </summary>
/// <returns></returns>
public bool isInitLocalCommodityEpsInfo()
{
//查询语句
var localCommodityEpsCount = SqlSugarHelper.GetInstance().Db.Queryable <LocalCommodityEps>()
.Select <LocalCommodityEps>().Count();
return(localCommodityEpsCount <= 0);
}
public bool SaveMerchantsDal(string userId, string entIdList, int status)
{
try
{
SqlSugarHelper helper = new SqlSugarHelper();
var entIdDoc = entIdList.Split(',');
string entId = "";
///解除绑定关系
if (status == 0)
{
int count = helper.Db.Ado.ExecuteCommand("update Dt_UserEntDoc set status=0 where entId=@entId and userId=@userId ",
new SugarParameter("@userId", userId),
new SugarParameter("@entId", entId));
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
else
{
///验证数据是否存在
var list = helper.Db.Queryable <Dt_UserEntDoc>().Where(u => u.userId == userId && u.entId == entId).ToList();
if (list != null && list.Count > 0)
{
return(true);
}
///插入数据
Dt_UserEntDoc model = new Dt_UserEntDoc();
model.userId = userId;
model.entId = entId;
model.status = status;
model.addTime = System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
int count = helper.Db.Insertable(model)
.IgnoreColumns(u => new { u.id, u.PriceLevel })
.ExecuteReturnIdentity();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
catch (Exception ex)
{
LogQueue.Write(LogType.Error, "MemberDal/SaveMerchants", ex.Message.ToString());
return(false);
}
}
/// <summary>
/// 读取所有的机构，带分页
/// </summary>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="soustr"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <dt_entdoc> GetEntdocListPager(int pageindex, int pagesize, string soustr, ref int total)
{
SqlSugarHelper sqlSugarHelper = new SqlSugarHelper();
List <dt_entdoc> list = sqlSugarHelper.Db.Queryable <dt_entdoc>()
.Where(m => m.status == 2 && m.entname.Contains(soustr))
.ToPageList(pageindex, pagesize, ref total);
return(list);
}
/// <summary>
/// 获取业务员列表，带分页
/// </summary>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="soustr"></param>
/// <returns></returns>
public object GetSalesmanList(int pageindex, int pagesize, string soustr, ref int total)
{
SqlSugarHelper helper = new SqlSugarHelper();
var data = helper.Db.Queryable <dt_users, dt_entdoc>((u, e) => u.entid == e.entid)
.Where((u, e) => u.role_type == 3 && u.status == 1 && (e.entname.Contains(soustr) || u.username.Contains(soustr) || u.name.Contains(soustr) || u.telphone.Contains(soustr)))
.Select((u, e) => new { entname = e.entname, entid = u.entid, userid = u.userid, username = u.username, telphone = u.telphone, name = u.name, status = u.status, add_time = u.add_time })
.ToPageList(pageindex, pagesize, ref total);
return(data);
}
/// <summary>
/// 获取变化后的商品信息的名称集合
/// </summary>
/// <returns></returns>
public List <string> GetLocalCommodityName()
{
//查询语句
var queryable = SqlSugarHelper.GetInstance().Db.Queryable <LocalCommodityCode>()
.Distinct()
.OrderBy((lcc) => lcc.create_time, OrderByType.Desc)
.Select(it => it.CommodityName).ToList();
return(queryable);
}
public static void Main(string[] args)
{
string _ProjectName = "dotnetframework";
string _TablePath = "dotnetframework.Entities.Table";
//Code First
Assembly assembly = Assembly.Load(_ProjectName);
SqlSugarClient Db = SqlSugarHelper.Instantiate();
Db.CodeFirst.BackupTable().InitTables(assembly.GetTypes().Where(i => i.Namespace == _TablePath).Where(i => i.IsClass).ToArray());
CreateHostBuilder(args).Build().Run();
}
static void Main()
{
log4net.Config.XmlConfigurator.Configure(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "log4net.config"));
ConsoleHelper.WriteLine(ELogCategory.Info, string.Format("SqlSugarDemo.WinForm1 Startup..."), true);
AppDomain.CurrentDomain.UnhandledException += CurrentDomain_UnhandledException;
Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);
Application.ThreadException += Application_ThreadException;
SqlSugarHelper.Init();
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
/// <summary>
/// 获取上次扫描记录的商品信息
/// </summary>
/// <returns></returns>
public HashSet <CommodityEps> GetLastLocalCommodityEpsInfo()
{
var ret = new HashSet <CommodityEps>();
//查询语句
var localCommodityEps = SqlSugarHelper.GetInstance().Db.Queryable <LocalCommodityEps>()
.OrderBy((lcc) => lcc.create_time, OrderByType.Desc)
.Select <LocalCommodityEps>().First();
if (localCommodityEps.commodityEpsList != null)
{
ret = new HashSet <CommodityEps>(JsonConvert.DeserializeObject <List <CommodityEps> >(localCommodityEps.commodityEpsList));
}
return(ret);
}
/// <summary>
/// Get specific page from original SqlSugarQueryable source
/// </summary>
/// <typeparam name="T">element type of your SqlSugarQueryable source</typeparam>
/// <param name="query">original SqlSugarQueryable source</param>
/// <param name="pageNumber">page number</param>
/// <param name="pageSize">page size</param>
/// <returns></returns>
public static async Task <IPage <T> > GetPageAsync <T>(this ISugarQueryable <T> query, int pageNumber, int pageSize)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query), $"{nameof(query)} can not be null.");
}
if (pageNumber < 0)
{
throw new IndexOutOfRangeException($"{nameof(pageNumber)} can not be less than zero");
}
if (pageSize < 0)
{
throw new IndexOutOfRangeException($"{nameof(pageSize)} can not be less than zero");
}
return(new SqlSugarPage <T>(query, pageNumber, pageSize, (await SqlSugarHelper.CountAsync(query))));
}
/// <summary>
/// 分配审核注册业务员
/// </summary>
/// <param name="userid"></param>
/// <param name="entid"></param>
/// <returns>0:此业务员已审核或userid不存在</returns>
public int AuditSalesman(string userid, string entid)
{
SqlSugarHelper helper = new SqlSugarHelper();
var user = helper.Db.Queryable <dt_users>().First(m => m.userid == userid && m.status == 1 && m.role_type == 3);
if (user == null)
{
return(0);
}
int flag = helper.Db.Ado.ExecuteCommand("update dt_users set entid=@entid,[status]=2 where userid=@userid",
new SugarParameter("@entid", entid),
new SugarParameter("@userid", userid)
);
//.Updateable<dt_users>()
// .SetColumns(m => new dt_users() { entid = entid, status = 2 })
// .Where(m=>m.userid== userid)
// .ExecuteCommand();
return(flag);
}
/// <summary>
/// 业务员开放注册
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="name"></param>
/// <param name="telphone"></param>
/// <returns>1:成功,0:失败，-1：用户名已存在</returns>
public int AddSalesman(string username, string password, string name, string telphone)
{
SqlSugarHelper helper = new SqlSugarHelper();
int reun = helper.Db.Queryable <Models.dt_users>().Where(m => m.username == username).Count();
if (reun > 0)
{
return(-1);
}
string maxbh = helper.Db.Ado.GetScalar("select maxbh from dt_Maxbh where BiaoShi='YW'").ToString();
string entid = helper.Db.Ado.GetScalar("select top 1 entid from dt_entdoc").ToString();
string userid = (Convert.ToInt32(maxbh) + 1).ToString();
while (userid.Length < 7)
{
userid = "0" + userid;
}
userid = "YW" + userid;
Models.dt_users model = new dt_users
{
userid = userid,
entid = entid,
username = username,
password = Encryption.GetMD5_16(password),
name = name,
telphone = telphone,
role_type = 3,// 业务员
status = 1,
salt = "2H8H0P",
role_id = 1,
add_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
};
int flag = helper.Db.Insertable(model).ExecuteCommand();
if (flag > 0)
{
int new_maxbh = Convert.ToInt32(maxbh) + 1;
helper.Db.Ado.ExecuteCommand("update dt_Maxbh set [maxbh]=" + new_maxbh + " where BiaoShi='YW'");
}
return(flag);
}
// 定义私有构造函数，使外界不能创建该类实例
private ReplenishDal()
{
Db = SqlSugarHelper.GetInstance().Db;
}
static void Main(string[] args)
{
var option = new ConfigurationOptions();
ConnectionMultiplexer redis = ConnectionMultiplexer.Connect("localhost:6379,password=redis123");
//IDatabase dbr = redis.GetDatabase(0);
//var batch = dbr.CreateBatch();
//sub.Publish("test1", "2222");
ISubscriber sub = redis.GetSubscriber();
sub.Publish("95", "JCZQ1211101950191836176274");
string vs = string.Empty;
string ss = string.Empty;
sub.Subscribe("messages", (channel, message) => {
Console.WriteLine((string)message);
});
Console.ReadKey();
return;
//var db = getdb();
//var list = db.Queryable<blast_count>().ToList();
////var model = list.Find(a => a.id == 1038);
////var model1 = list.Find(a => a.id == 1039);
//blast_count model = new blast_count();
//blast_count model1 = new blast_count();
//model.typeid = 1000;
//model.playedId = 10000;
//model.createdate = DateTime.Now;
//model.betCount = 1;
//model.betAmount = 100;
//model.zjAmount = 100;
//try
//{
// db.Ado.BeginTran();
// model1.id = 1000000;
// db.Insertable<blast_count>(model).ExecuteCommand();
// //db.Insertable<blast_count>(model1).ExecuteCommand();
// //throw new Exception("3333");
// db.Ado.CommitTran();
//}
//catch (Exception exp)
//{
// db.Ado.RollbackTran();
//}
SqlSugarClient db = SqlSugarHelper.GetInstance();
var list = db.Queryable <blast_count>().ToList();
var model = list.Find(a => a.id == 225);
//var result = db.Ado.UseTran(() =>
//{
// model.betCount = 2;
// db.Updateable<blast_count>(model).ExecuteCommand();
// db.Ado.ExecuteCommand("delete student");
// throw new Exception("error haha");
//});
//SqlSugarClient db1 = SqlSugarHelper.GetInstance();
//blast_admin_log model1 = new blast_admin_log();
//SqlSugarHelper.TranInvok(() =>
//{
// model.betCount = 2;
// db.Updateable<blast_count>(model).ExecuteCommand();
// db.Updateable<blast_admin_log>(model1).ExecuteCommand();
// return true;
//}, db, db1);
Console.WriteLine("Hello World!");
Console.ReadKey();
}
public DbContext()
{
Db = SqlSugarHelper.Instantiate();
}
public BaseService()
{
db = SqlSugarHelper.GetClient();
sdb = db.GetSimpleClient();
}
// 定义私有构造函数，使外界不能创建该类实例
private PickingDal()
{
Db = SqlSugarHelper.GetInstance().Db;
}
/// <summary>
/// 权限逻辑
/// </summary>
/// <param name="context"></param>
private void PowerLogic(ActionExecutedContext context)
{
if (MenuID != "Home")
{
//Console.WriteLine(context.HttpContext.Request.Headers["X-Requested-With"].ToString());
if (!(context.HttpContext.Request.Headers["X-Requested-With"].ToString() == "XMLHttpRequest"))
{
using (var db = SqlSugarHelper.GetInstance())
{
var _RouteValues = context.ActionDescriptor.RouteValues;
var _Area = _RouteValues["area"];
var _Controller = _RouteValues["controller"];
var _Action = _RouteValues["action"];
var _func_list = db.Queryable <sys_function>().OrderBy("Function_Num asc").ToList();
var _power_list = new Dictionary <string, object>();
//这里得判断一下是否是查找带回调用页面
string findback = context.HttpContext.Request.Query["findback"];
if (string.IsNullOrEmpty(findback))
{
//dynamic model = new ExpandoObject();
if (string.IsNullOrEmpty(MenuID))
{
throw new MessageBox("区域(" + _Area + "),控制器(" + _Controller + "):的程序中缺少菜单ID");
}
var _Menu = db.Queryable <sys_menu>().First(p => p.Menu_Num == MenuID);
//if (!_Menu.Menu_Url.ToStr().StartsWith("/" + _Area + "/" + _Controller + "/"))
//{
// throw new MessageBox("区域(" + _Area + "),控制器(" + _Controller + "):的程序中缺少菜单ID与该页面不匹配");
//}
if (!account.IsSuperManage)
{
var _role_menu_func_list = db.Queryable <sys_rolemenufunction>().ToList();
_power_list = new Dictionary <string, object>();
_func_list.ForEach(item =>
{
var ispower = _role_menu_func_list.FindAll(x =>
x.RoleMenuFunction_RoleID == account.RoleID &&
x.RoleMenuFunction_MenuID == _Menu.Menu_ID &&
x.RoleMenuFunction_FunctionID == item.Function_ID);
_power_list.Add(item.Function_ByName, (ispower.Count > 0));
});
}
else
{
var _menu_func_list = db.Queryable <sys_menufunction>().ToList();
_func_list.ForEach(item =>
{
//_power_list.Add(item.Function_ByName, true);
var ispower = _menu_func_list.FindAll(x => x.MenuFunction_MenuID == _Menu.Menu_ID && x.MenuFunction_FunctionID == item.Function_ID);
if (ispower.Count > 0)
{
_power_list.Add(item.Function_ByName, true);
}
else
{
_power_list.Add(item.Function_ByName, false);
}
});
}
}
else
{
_power_list = new Dictionary <string, object>();
_func_list.ForEach(item =>
{
_power_list.Add(item.Function_ByName, false);
});
_power_list["Have"] = true;
_power_list["Search"] = true;
}
this.ViewData["PowerModel"] = _power_list.SerializeObject();
}
}
}
}
/// <summary>
/// Action 执行之前 发生
/// </summary>
/// <param name="filterContext"></param>
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
if (IsExecutePowerLogic || MenuID == "Home")
{
Console.WriteLine(filterContext.HttpContext.Request.Path.Value);
var token = string.Empty;
var cktoken = filterContext.HttpContext.Request.Cookies["authtoken"];
var attoken = filterContext.HttpContext.Request.Headers["authtoken"].ToString();
if (!string.IsNullOrEmpty(cktoken))
{
//Console.WriteLine(cktoken);
token = cktoken;
}
if (!string.IsNullOrEmpty(attoken))
{
token = attoken;
}
if (!string.IsNullOrEmpty(token))
{
var userid = string.Empty;
var handers = new JwtSecurityTokenHandler().ReadJwtToken(token.Replace("Bearer ", ""));
foreach (var item in handers.Claims)
{
if (item.Type == "ID")
{
userid = item.Value;
}
//Console.WriteLine(item.Type);
//Console.WriteLine(item.Value);
}
//if (!string.IsNullOrEmpty(Convert.ToString(CacheHelper.Get(userid))))
//{
// CacheHelper.Set(userid, "1", DateTime.Now.AddMinutes(120));
using (var db = SqlSugarHelper.GetInstance())
{
var model = db.Queryable <sys_user, sys_userrole>((u, ur) => new object[] {
JoinType.Inner, u.User_ID == ur.UserRole_UserID
}
).Where((u) => u.User_ID == userid)
.Select((u, ur) => new { u.User_ID, u.User_Name, ur.UserRole_RoleID }).First();
if (model != null)
{
account.RoleID = model.UserRole_RoleID;
account.UserID = model.User_ID;
account.UserName = model.User_Name;
account.IsSuperManage = model.UserRole_RoleID.ToStr().ToLower() == AppConfig.Admin_RoleID;
filterContext.RouteData.Values.Add("account", account);
base.OnActionExecuting(filterContext);
}
else
{
throw new MessageBox("请重新登陆", EMsgStatus.无效权限401);
}
}
}
else
{
throw new MessageBox("请重新登陆", EMsgStatus.无效权限401);
}
}
else
{
base.OnActionExecuting(filterContext);
}
}
// 定义私有构造函数，使外界不能创建该类实例
private FetchOrderDal()
{
Db = SqlSugarHelper.GetInstance().Db;
}
// 定义私有构造函数，使外界不能创建该类实例
private UserDal()
{
Db = SqlSugarHelper.GetInstance().Db;
}
/// <summary>
/// Get Db
/// </summary>
/// <returns></returns>
protected SqlSugarClient GetDb()
{
return(SqlSugarHelper.GetDb());
}
// 定义私有构造函数，使外界不能创建该类实例
private GoodsDal()
{
Db = SqlSugarHelper.GetInstance().Db;
}
public BaseDAL()
{
db = SqlSugarHelper.GetClient();
sdb = db.GetSimpleClient();
}
