/// <summary>
/// 添加入库单
/// </summary>
/// <param name="stockInfo"></param>
/// <param name="stockGoodsList"></param>
/// <returns>入库单编号 与 入库单号</returns>
public string AddStockInfo(StockStoreInfoModel stockInfo, List <StStockGoodsInfoModel> stockGoodsList)
{
//入库单表的列名
string cols = "StoreId,DealPerson,Remark,StockNo,Creator,CreateTime";
//插入期初库存商品列表的列名
string sgCols = "StockId,GoodsId,StCount,StPrice,StAmount,Remark";
//生成入库单号---保存入库单信息--保存入库商品明细列表
return(SqlHelper.ExecuteTrans <string>(cmd =>
{
try
{
//获取生成的入库单号
cmd.CommandText = "makeStockNo";
cmd.CommandType = CommandType.StoredProcedure;
object stockNo = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (stockNo != null && stockNo.ToString() != "")
{
//保存入库单
stockInfo.StockNo = stockNo.ToString();
SqlModel inStock = CreateSql.GetInsertSqlAndParas <StockStoreInfoModel>(stockInfo, cols, 1);
cmd.CommandText = inStock.Sql;
foreach (var p in inStock.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.CommandType = CommandType.Text;
object oStockId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oStockId != null && oStockId.GetInt() > 0)
{
//入库商品明细保存
foreach (StStockGoodsInfoModel si in stockGoodsList)
{
si.StockId = oStockId.GetInt();
SqlModel inStockGoods = CreateSql.GetInsertSqlAndParas <StStockGoodsInfoModel>(si, sgCols, 0);
cmd.CommandText = inStockGoods.Sql;
cmd.CommandType = CommandType.Text;
foreach (var p in inStockGoods.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
cmd.Transaction.Commit();
return oStockId.ToString() + "," + stockNo.ToString();
}
return "";
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("执行添加入库单异常！", ex);
}
}));
}
/// <summary>
/// 添加实体信息
/// </summary>
/// <param name="t"></param>
/// <param name="strCols">插入列名字符串，若为空，则全插入</param>
/// <returns></returns>
public int Add(T t, string strCols, int isReturn)
{
if (t == null)
{
return(0);
}
//获取生成的sql和参数列表
SqlModel insert = CreateSql.GetInsertSqlAndParas <T>(t, strCols, isReturn);
//执行sql命令
if (isReturn == 0)
{
return(SqlHelper.ExecuteNonQuery(insert.Sql, 1, insert.SqlParaArray));
}
else
{
object oId = SqlHelper.ExecuteScalar(insert.Sql, 1, insert.SqlParaArray);
if (oId != null && oId.ToString() != "")
{
return(oId.GetInt());
}
else
{
return(0);
}
}
}
/// <summary>
/// 添加商品
/// </summary>
/// <param name="goodsInfo"></param>
/// <returns></returns>
public bool AddGoodsInfo(GoodsInfoModel goodsInfo)
{
string cols = "GoodsName,GoodsNo,GoodsPYNo,GoodsSName,GoodsTXNo,GUnit,GTypeId,GProperties,IsStopped,RetailPrice,LowPrice,PrePrice,DisCount,BidPrice,Remark,GoodsPic,Creator";
SqlModel insert = CreateSql.GetInsertSqlAndParas <GoodsInfoModel>(goodsInfo, cols, 1);
StoreDAL storeDAL = new StoreDAL();
List <int> storeIds = storeDAL.GetAllStoreIds();
return(SqlHelper.ExecuteTrans <bool>(cmd =>
{
try
{
cmd.CommandText = insert.Sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
foreach (var p in insert.SqlParaArray)
{
cmd.Parameters.Add(p);
}
object oId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oId != null && oId.ToString() != "")
{
int goodsId = oId.GetInt();
string sql = "insert into StoreGoodsStockInfos (StoreId,GoodsId,Creator) values(@storeId,@goodsId,@creator)";
foreach (int storeId in storeIds)
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@storeId", storeId));
cmd.Parameters.Add(new SqlParameter("@goodsId", goodsId));
cmd.Parameters.Add(new SqlParameter("@creator", goodsInfo.Creator));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
//添加总库存记录
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
int sId = 0;
cmd.Parameters.Add(new SqlParameter("@storeId", sId));
cmd.Parameters.Add(new SqlParameter("@goodsId", goodsId));
cmd.Parameters.Add(new SqlParameter("@creator", goodsInfo.Creator));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
cmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("添加商品执行异常！", ex);
}
}));
}
/// <summary>
/// 添加用户信息
/// </summary>
/// <param name="userInfo"></param>
/// <param name="urList"></param>
/// <returns></returns>
public bool AddUserInfo(UserInfoModel userInfo, List <UserRoleInfoModel> urList)
{
string colUsers = "UserName,UserPwd,UserState,Creator";
SqlModel inUser = CreateSql.GetInsertSqlAndParas(userInfo, colUsers, 1);
return(SqlHelper.ExecuteTrans <bool>(cmd =>
{
try
{
cmd.CommandText = inUser.Sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
foreach (var p in inUser.SqlParaArray)
{
cmd.Parameters.Add(p);
}
object oId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oId != null && oId.ToString() != "")
{
int userId = oId.GetInt();
if (urList.Count > 0)
{
string colsUserRole = "UserId,RoleId,Creator";
foreach (var ur in urList)
{
ur.UserId = userId;
SqlModel inUserRole = CreateSql.GetInsertSqlAndParas <UserRoleInfoModel>(ur, colsUserRole, 0);
cmd.CommandText = inUserRole.Sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
foreach (var p in inUserRole.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
}
cmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("添加用户执行异常！", ex);
}
}));
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="list"></param>
/// <param name="strCols"></param>
/// <returns></returns>
public bool AddList(List <T> list, string strCols)
{
if (list == null || list.Count == 0)
{
return(false);
}
List <CommandInfo> comList = new List <CommandInfo>();
foreach (T t in list)
{
SqlModel insert = CreateSql.GetInsertSqlAndParas <T>(t, strCols, 0);
CommandInfo com = new CommandInfo(insert.Sql, false, insert.SqlParaArray);
comList.Add(com);
}
return(SqlHelper.ExecuteTrans(comList));
}
/// <summary>
/// 修改用户信息
/// </summary>
/// <param name="userInfo"></param>
/// <param name="urList"></param>
/// <param name="urListNew"></param>
/// <returns></returns>
public bool UpdateUserInfo(UserInfoModel userInfo, List <UserRoleInfoModel> urList, List <UserRoleInfoModel> urListNew)
{
List <CommandInfo> comList = new List <CommandInfo>();
string colsUser = "******";
if (!string.IsNullOrEmpty(userInfo.UserPwd))
{
colsUser += ",UserPwd";
}
colsUser += ",UserState";
SqlModel upUser = CreateSql.GetUpdateSqlAndParas(userInfo, colsUser, "");
//修改用户信息
comList.Add(new CommandInfo()
{
CommandText = upUser.Sql,
IsProc = false,
Paras = upUser.SqlParaArray
});
if (urList.Count > 0)
{
//删除取消的角色关系数据
string roleIds = string.Join(",", urList.Select(ur => ur.RoleId));
comList.Add(new CommandInfo()
{
CommandText = $"delete from UserRoleInfos where RoleId not in ({roleIds}) and UserId={userInfo.UserId}",
IsProc = false
});
}
if (urListNew.Count > 0)
{
//新增新设置的角色关系
string colsUserRole = "UserId,RoleId,Creator";
foreach (var ur in urListNew)
{
SqlModel inUserRole = CreateSql.GetInsertSqlAndParas <UserRoleInfoModel>(ur, colsUserRole, 0);
comList.Add(new CommandInfo()
{
CommandText = inUserRole.Sql,
IsProc = false,
Paras = inUserRole.SqlParaArray
});
}
}
return(SqlHelper.ExecuteTrans(comList));
}
/// <summary>
/// 添加仓库信息
/// </summary>
/// <param name="storeInfo"></param>
/// <param name="goodsIds">所有商品的编号集合</param>
/// <returns></returns>
public bool AddStoreInfo(StoreInfoModel storeInfo, List <int> goodsIds)
{
string cols = "StoreNo,StoreName,STypeId,StorePYNo,StoreOrder,StoreRemark,Creator";
SqlModel insert = CreateSql.GetInsertSqlAndParas <StoreInfoModel>(storeInfo, cols, 1);
GoodsDAL goodsDAL = new GoodsDAL();
return(SqlHelper.ExecuteTrans <bool>(cmd =>
{
try
{
cmd.CommandText = insert.Sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
foreach (var p in insert.SqlParaArray)
{
cmd.Parameters.Add(p);
}
object oId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oId != null && oId.ToString() != "")
{
int storeId = oId.GetInt();
string sql = "insert into StoreGoodsStockInfos (StoreId,GoodsId,Creator) values(@storeId,@goodsId,@creator)";
foreach (int goodsId in goodsIds)
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@storeId", storeId));
cmd.Parameters.Add(new SqlParameter("@goodsId", goodsId));
cmd.Parameters.Add(new SqlParameter("@creator", storeInfo.Creator));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
cmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("添加仓库执行异常！", ex);
}
}));
}
/// <summary>
/// 更新入库单信息
/// </summary>
/// <param name="stockInfo"></param>
/// <param name="stockGoodsList"></param>
/// <returns></returns>
public bool UpdateStockInfo(StockStoreInfoModel stockInfo, List <StStockGoodsInfoModel> stockGoodsList)
{
string cols = "StockId,StoreId,DealPerson,Remark";
string sgCols = "StockId,GoodsId,StCount,StPrice,StAmount,Remark";
List <CommandInfo> comList = new List <CommandInfo>();
SqlModel upStock = CreateSql.GetUpdateSqlAndParas <StockStoreInfoModel>(stockInfo, cols, "");
//修改入库单信息
comList.Add(new CommandInfo()
{
CommandText = upStock.Sql,
IsProc = false,
Paras = upStock.SqlParaArray
});
//删除已移除的商品明细数据
//商品明细编号集合字符串
string goodsIds = string.Join(",", stockGoodsList.Select(g => g.GoodsId));
comList.Add(new CommandInfo()
{
CommandText = $"delete from StStockGoodsInfos where StockId ={stockInfo.StockId} and GoodsId not in ({goodsIds})",
IsProc = false
});
//明细商品列表保存：修改、添加 两种
foreach (StStockGoodsInfoModel sg in stockGoodsList)
{
if (sgDAL.ExistsGoods(stockInfo.StockId, sg.GoodsId))
{
string upSql = "update StStockGoodsInfos set StCount=@count,StPrice=@stPrice,StAmount=@stAmount,Remark=@remark where StockId=@stockId and GoodsId=@goodsId";
SqlParameter[] paras =
{
new SqlParameter("@stockId", stockInfo.StockId),
new SqlParameter("@goodsId", sg.GoodsId),
new SqlParameter("@count", sg.StCount),
new SqlParameter("@stPrice", sg.StPrice),
new SqlParameter("@remark", sg.Remark),
new SqlParameter("@stAmount", sg.StAmount)
};
comList.Add(new CommandInfo()
{
CommandText = upSql,
IsProc = false,
Paras = paras
});
}
else//新增处理
{
sg.StockId = stockInfo.StockId;
SqlModel inStStockGoods = CreateSql.GetInsertSqlAndParas <StStockGoodsInfoModel>(sg, sgCols, 0);
comList.Add(new CommandInfo()
{
CommandText = inStStockGoods.Sql,
IsProc = false,
Paras = inStStockGoods.SqlParaArray
});
}
}
return(SqlHelper.ExecuteTrans(comList));
}
/// <summary>
/// 更新采购单信息
/// </summary>
/// <param name="perchaseInfo"></param>
/// <param name="perGoodsList"></param>
/// <returns></returns>
public bool UpdatePerchaseInfo(PerchaseInStoreInfoModel perchaseInfo, List <PerchaseGoodsInfoModel> perGoodsList)
{
string cols = "UnitId,StoreId,DealPerson,PayDesp,ThisAmount,Remark,TotalAmount,YHAmount,PerId";
if (perchaseInfo.PayTime != null)
{
cols += ",PayTime,IsPayed";
}
if (perchaseInfo.IsPayFull == 1)
{
cols += ",IsPayFull";
}
//插入采购商品列表的列名
string pgCols = "PerId,GoodsId,GUnit,Count,PerPrice,Amount,Remark";
List <CommandInfo> comList = new List <CommandInfo>();
SqlModel upStock = CreateSql.GetUpdateSqlAndParas(perchaseInfo, cols, "");
//修改采购单信息
comList.Add(new CommandInfo()
{
CommandText = upStock.Sql,
IsProc = false,
Paras = upStock.SqlParaArray
});
//删除已移除的商品明细数据
//商品明细编号集合字符串
string goodsIds = string.Join(",", perGoodsList.Select(g => g.GoodsId));
comList.Add(new CommandInfo()
{
CommandText = $"delete from PerchaseGoodsInfos where PerId ={perchaseInfo.PerId} and GoodsId not in ({goodsIds})",
IsProc = false
});
//明细商品列表保存：修改、添加 两种
foreach (PerchaseGoodsInfoModel sg in perGoodsList)
{
if (pgDAL.ExistsGoods(perchaseInfo.PerId, sg.GoodsId))
{
string upSql = "update PerchaseGoodsInfos set Count=@count,PerPrice=@perPrice,Amount=@Amount,Remark=@remark where PerId=@perId and GoodsId=@goodsId";
SqlParameter[] paras =
{
new SqlParameter("@perId", perchaseInfo.PerId),
new SqlParameter("@goodsId", sg.GoodsId),
new SqlParameter("@count", sg.Count),
new SqlParameter("@perPrice", sg.PerPrice),
new SqlParameter("@remark", sg.Remark),
new SqlParameter("@Amount", sg.Amount)
};
comList.Add(new CommandInfo()
{
CommandText = upSql,
IsProc = false,
Paras = paras
});
}
else//新增处理
{
sg.PerId = perchaseInfo.PerId;
SqlModel inStStockGoods = CreateSql.GetInsertSqlAndParas(sg, pgCols, 0);
comList.Add(new CommandInfo()
{
CommandText = inStStockGoods.Sql,
IsProc = false,
Paras = inStStockGoods.SqlParaArray
});
}
}
return(SqlHelper.ExecuteTrans(comList));
}
/// <summary>
/// 添加采购单
/// </summary>
/// <param name="perchaseInfo"></param>
/// <param name="perGoodsList"></param>
/// <returns>采购单编号 与 采购单号</returns>
public string AddPerchaseInfo(PerchaseInStoreInfoModel perchaseInfo, List <PerchaseGoodsInfoModel> perGoodsList)
{
//采购单表的列名
string cols = "UnitId,StoreId,DealPerson,PayDesp,ThisAmount,Remark,TotalAmount,YHAmount,PerchaseNo,Creator,CreateTime";
if (perchaseInfo.PayTime != null)
{
cols += ",PayTime,IsPayed,IsPayFull";
}
//插入采购商品列表的列名
string sgCols = "PerId,GoodsId,GUnit,Count,PerPrice,Amount,Remark";
//生成采购单号---保存采购单信息--保存采购商品明细列表
return(SqlHelper.ExecuteTrans <string>(cmd =>
{
try
{
//获取生成的采购单号
cmd.CommandText = "makePerNo";
cmd.CommandType = CommandType.StoredProcedure;
object perNo = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (perNo != null && perNo.ToString() != "")
{
//保存入库单
perchaseInfo.PerchaseNo = perNo.ToString();
SqlModel inStock = CreateSql.GetInsertSqlAndParas <PerchaseInStoreInfoModel>(perchaseInfo, cols, 1);
cmd.CommandText = inStock.Sql;
foreach (var p in inStock.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.CommandType = CommandType.Text;
object oPerId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oPerId != null && oPerId.GetInt() > 0)
{
//入库商品明细保存
foreach (PerchaseGoodsInfoModel si in perGoodsList)
{
si.PerId = oPerId.GetInt();
SqlModel inPerGoods = CreateSql.GetInsertSqlAndParas <PerchaseGoodsInfoModel>(si, sgCols, 0);
cmd.CommandText = inPerGoods.Sql;
cmd.CommandType = CommandType.Text;
foreach (var p in inPerGoods.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
cmd.Transaction.Commit();
return oPerId.ToString() + "," + perNo.ToString();
}
return "";
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("执行添加采购单异常！", ex);
}
}));
}
/// <summary>
/// 保存权限数据
/// </summary>
/// <param name="roleId"></param>
/// <param name="rmList"></param>
/// <param name="rtmList"></param>
/// <returns></returns>
public bool SetRoleRight(int roleId, List <RoleMenuInfoModel> rmList, List <RoleTMenuInfoModel> rtmList)
{
//先删除角色相关的权限数据 检查 ---是否存在这么一条关系数据，否---插入 不插入
return(SqlHelper.ExecuteTrans <bool>(cmd =>
{
try
{
if (rmList.Count > 0)
{
string noIds = string.Join(",", rmList.Select(rm => rm.MId));
cmd.CommandText = $"delete from RoleMenuInfos where MId not in ({noIds}) and RoleId={roleId} and IsDeleted=0";
cmd.ExecuteNonQuery();
foreach (var rm in rmList)
{
cmd.CommandText = $"select count(1) from RoleMenuInfos where RoleId={rm.RoleId} and MId = {rm.MId} and IsDeleted=0";
object oCount = cmd.ExecuteScalar();
if (oCount != null && oCount.ToString() != "")
{
int count = oCount.GetInt();
if (count == 0)
{
SqlModel insertModel = CreateSql.GetInsertSqlAndParas(rm, "MId,RoleId,Creator", 0);
cmd.CommandText = insertModel.Sql;
foreach (var p in insertModel.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
else
{
continue;
}
}
}
}
if (rtmList.Count > 0)
{
string noIds = string.Join(",", rtmList.Select(rtm => rtm.TMenuId));
cmd.CommandText = $"delete from RoleTMenuInfos where TMenuId not in ({noIds}) and RoleId={roleId} and IsDeleted=0";
cmd.ExecuteNonQuery();
foreach (var rtm in rtmList)
{
cmd.CommandText = $"select count(1) from RoleTMenuInfos where RoleId={rtm.RoleId} and TMenuId = {rtm.TMenuId} and IsDeleted=0";
object oCount = cmd.ExecuteScalar();
if (oCount != null && oCount.ToString() != "")
{
int count = oCount.GetInt();
if (count == 0)
{
SqlModel insertModel = CreateSql.GetInsertSqlAndParas(rtm, "TMenuId,RoleId,Creator", 0);
cmd.CommandText = insertModel.Sql;
foreach (var p in insertModel.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
else
{
continue;
}
}
}
}
cmd.Transaction.Commit();
return true;
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("保存权限数据，执行异常！", ex);
}
}));
//保存权限数据
}
/// <summary>
/// 添加销售单
/// </summary>
/// <param name="saleInfo"></param>
/// <param name="saleGoodsList"></param>
/// <returns>销售单编号 与 销售单号</returns>
public string AddSaleOutStoreInfo(SaleOutStoreInfoModel saleInfo, List <SaleGoodsInfoModel> saleGoodsList)
{
//销售单表的列名
string cols = "UnitId,StoreId,DealPerson,PayDesp,ThisAmount,Remark,TotalAmount,YHAmount,SaleOutNo,Creator,CreateTime";
if (saleInfo.PayTime != null)
{
cols += ",PayTime,IsPayed,IsPayFull";
}
//插入销售商品列表的列名
string sgCols = "SaleId,GoodsId,GUnit,Count,SalePrice,Amount,Remark";
//生成销售单号---保存销售单信息--保存销售商品明细列表
return(SqlHelper.ExecuteTrans <string>(cmd =>
{
try
{
//获取生成的销售单号
cmd.CommandText = "makeSaleNo";
cmd.CommandType = CommandType.StoredProcedure;
object saleNo = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (saleNo != null && saleNo.ToString() != "")
{
//保存销售单
saleInfo.SaleOutNo = saleNo.ToString();
SqlModel inStock = CreateSql.GetInsertSqlAndParas <SaleOutStoreInfoModel>(saleInfo, cols, 1);
cmd.CommandText = inStock.Sql;
foreach (var p in inStock.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.CommandType = CommandType.Text;
object oSaleId = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (oSaleId != null && oSaleId.GetInt() > 0)
{
//出库商品明细保存
foreach (SaleGoodsInfoModel si in saleGoodsList)
{
si.SaleId = oSaleId.GetInt();
SqlModel inSaleGoods = CreateSql.GetInsertSqlAndParas <SaleGoodsInfoModel>(si, sgCols, 0);
cmd.CommandText = inSaleGoods.Sql;
cmd.CommandType = CommandType.Text;
foreach (var p in inSaleGoods.SqlParaArray)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
cmd.Transaction.Commit();
return oSaleId.ToString() + "," + saleNo.ToString();
}
return "";
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw new Exception("执行添加销售单异常！", ex);
}
}));
}