public new string CreateTable()
{
DBExtend helper = dbHelper;
AccessControl obj1 = new AccessControl();
string msg = obj1.CreateTable(helper);
Employee obj2 = new Employee();
msg += obj2.CreateTable(helper);
var obj3 = new Menu();
msg += obj3.CreateTable(helper);
var obj4 = new Role();
msg += obj4.CreateTable(helper);
var obj5 = new SystemType();
msg += obj5.CreateTable(helper);
var obj6 = new CRL.Person.LoginLog();
msg += obj6.CreateTable(helper);
SystemTypeBusiness.Instance.Add(new SystemType()
{
Name = "默认项目"
});
return(msg);
}
/// <summary>
/// 创建表脚本
/// </summary>
/// <param name="fields"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public override void CreateTable(DBExtend helper, List <Attribute.FieldAttribute> fields, string tableName)
{
var defaultValues = new List <string>();
string script = string.Format("create table {0}(\r\n", tableName);
List <string> list2 = new List <string>();
foreach (Attribute.FieldAttribute item in fields)
{
string nullStr = item.NotNull ? "NOT NULL" : "";
string str = string.Format("{0} {1} {2} ", item.KeyWordName, item.ColumnType, nullStr);
if (item.IsPrimaryKey)
{
str = " " + item.Name + " int primary key auto_increment";
}
list2.Add(str);
}
script += string.Join(",\r\n", list2.ToArray());
script += ") ";
helper.Execute(script);
foreach (string s in defaultValues)
{
if (!string.IsNullOrEmpty(s))
{
helper.Execute(s);
}
}
}
/// <summary>
/// 添加到购物车
/// </summary>
/// <param name="item"></param>
public static int Add(ICartItem item, bool groupByPrice = false)
{
DBExtend helper = dbHelper;
ICartItem item2;
if (groupByPrice)
{
item2 = helper.QueryItem <ICartItem>(b => b.UserId == item.UserId && b.ProductId == item.ProductId && b.StyleId == item.StyleId && b.CartType == item.CartType && b.Price == item.Price);
}
else
{
item2 = helper.QueryItem <ICartItem>(b => b.UserId == item.UserId && b.ProductId == item.ProductId && b.StyleId == item.StyleId && b.CartType == item.CartType);
}
if (item2 == null)
{
//int n = GetCartCount(item.CartType) + 1;
//SetCartCount(item.CartType,n);
Add <ICartItem>(item);
}
else
{
AddNum(item);
}
return(GetCartCount(item.CartType));
}
/// <summary>
/// 查询购物车,并指定是否按商家分组
/// </summary>
/// <param name="cartType"></param>
/// <param name="userId"></param>
/// <param name="groupBySupplier"></param>
/// <returns></returns>
public static Dictionary <int, List <ICartItem> > Query(int cartType, string userId, bool groupBySupplier)
{
DBExtend helper = dbHelper;
Dictionary <int, List <ICartItem> > list = new Dictionary <int, List <ICartItem> >();
List <ICartItem> all = QueryList <ICartItem>(b => b.UserId == userId && b.CartType == cartType);
if (all.Count == 0)
{
return(list);
}
foreach (ICartItem item in all)
{
int groupId = item.SupplierId;
if (!groupBySupplier)
{
groupId = 1;
}
if (!list.ContainsKey(groupId))
{
list.Add(groupId, new List <ICartItem>());
}
list[groupId].Add(item);
}
return(list);
}
public string MakeNewCode(string parentSequenceCode, TModel category)
{
DBExtend helper = dbHelper;
string newCode = parentSequenceCode + "";
#region 生成新编码
var list = QueryList(b => b.ParentCode == parentSequenceCode && b.DataType == category.DataType).OrderByDescending(b => b.SequenceCode).ToList();
if (list.Count() == 0)
{
newCode += "01";
}
else
{
int len = !string.IsNullOrEmpty(parentSequenceCode) ? parentSequenceCode.Length : 0;
string max = list[0].SequenceCode;
max = max.Substring(len, 2);
int n = int.Parse(max);
if (n >= 99)
{
throw new Exception("子级分类已到最大级99");
}
newCode += (n + 1).ToString().PadLeft(2, '0');
}
#endregion
return(newCode);
}
/// <summary>
/// 提交订单
/// </summary>
/// <typeparam name="TMain"></typeparam>
/// <param name="order"></param>
/// <param name="orderDetail"></param>
/// <returns></returns>
public static bool SubmitOrder <TMain>(TMain order, List <IOrderDetail> orderDetail)
where TMain : IProductOrder, new()
{
DBExtend helper = dbHelper;
order.OrderId = MakeOrderId();//生成订单号
decimal orderAmount = 0;
decimal orderIntegral = 0;
orderDetail.ForEach(b => {
b.OrderId = order.OrderId;
orderAmount += b.Price * b.Num;
orderIntegral += b.Integral * b.Num;
});
//if (UseFreight)
//{
// decimal freightAmount = 0;
// double freight1, freight2;
// Freight.FreightAction<TType>.CalculateFreight(orderDetail, order.AreaId, supplier, out freight1, out freight2);
// freightAmount = order.DeliverType == Freight.DeliverType.物流 ? (decimal)freight1 : (decimal)freight2;
// order.FreightAmount = freightAmount;
//}
order.OriginalAmount = orderAmount;
order.Integral = orderIntegral;
OrderAction <TType> .SubmitOrder <TMain>(order);
//详细
helper.BatchInsert <IOrderDetail>(orderDetail);
return(true);
}
/// <summary>
/// 通过产品详细信息加入购物车
/// </summary>
/// <param name="userId"></param>
/// <param name="product"></param>
/// <param name="num"></param>
/// <param name="styleId"></param>
public static bool Add(string userId, Product.IProduct product, int num, int styleId, string styleName, string spreadInfo)
{
DBExtend helper = dbHelper;
ICartItem c = new ICartItem();
c.Num = num;
c.ProductId = product.Id;
c.Price = product.SoldPrice;
c.ProductName = product.ProductName;
c.StyleId = styleId;
c.StyleName = styleName;
c.Integral = product.Integral;
c.SupplierId = product.SupplierId;
c.UserId = userId;
c.SpreadInfo = spreadInfo;
c.TagData = product.TagData;
c.IncludedFreePost = product.IncludedFreePost;
double totalWeight = 0;
totalWeight = product.Weight * num;
if (product.ExemptFreightCount > 0 && product.ExemptFreightCount <= num)//如果达到多少个,则总重量为0
{
totalWeight = 0;
}
c.TotalWeight = totalWeight;
Add(c);
return(true);
}
/// <summary>
/// 添加到购物车
/// </summary>
/// <param name="item"></param>
/// <param name="groupByPrice">是否按价格分组</param>
/// <returns></returns>
public int Add(TModel item, bool groupByPrice = false)
{
DBExtend helper = DBExtend;
TModel item2;
if (groupByPrice)//按价格分组
{
item2 = helper.QueryItem <TModel>(b => b.UserId == item.UserId && b.ProductId == item.ProductId && b.StyleId == item.StyleId && b.CartType == item.CartType && b.Price == item.Price);
}
else
{
item2 = helper.QueryItem <TModel>(b => b.UserId == item.UserId && b.ProductId == item.ProductId && b.StyleId == item.StyleId && b.CartType == item.CartType);
}
if (item2 == null)
{
//int n = GetCartCount(item.CartType) + 1;
//SetCartCount(item.CartType,n);
base.Add(item);
}
else
{
AddNum(item);
}
return(GetCartCount(item.CartType));
}
/// <summary>
/// 删除一项
/// </summary>
/// <param name="id"></param>
public static void Delete(int id)
{
DBExtend helper = dbHelper;
DeleteById <IDicConfig>(id);
allCache.RemoveAll(b => b.Id == id);
}
/// <summary>
/// 查询用户帐户历史
/// </summary>
/// <param name="account"></param>
/// <param name="transactionType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <ITransaction> QueryAccountHistory(string account, int accountType, int transactionType, int pageIndex, int pageSize, out int count, DateTime?startTime, DateTime?endTime)
{
count = 0;
string condition = " 1 = 1 ";
if (startTime != null)
{
condition += " and AddTime > '" + startTime + "' ";
}
if (endTime != null)
{
condition += " and AddTime < '" + endTime + "' ";
}
List <ITransaction> list = new List <ITransaction>();
int accountId = AccountBusiness <TType> .Instance.GetAccountId(account, accountType, transactionType);
if (accountId == 0)
{
return(list);
}
DBExtend helper = dbHelper;
ParameCollection c = new ParameCollection();
c.SetQueryCondition(condition);
c.SetQueryPageIndex(pageIndex);
c.SetQueryPageSize(pageSize);
c["AccountId"] = accountId;
list = helper.QueryListByPage <ITransaction>(c, out count);
return(list);
}
/// <summary>
/// 按select返回匿名对象
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="resultSelector"></param>
/// <returns></returns>
public List <TResult> ToDynamic <TResult>(Expression <Func <T, TResult> > resultSelector)
{
//只能做到当前对象筛选
var db = new DBExtend(__DbContext);
return(db.QueryDynamic(this, resultSelector));
}
/// <summary>
/// 创建表分区配置
/// </summary>
/// <param name="table"></param>
/// <param name="error"></param>
/// <returns></returns>
public bool Create(Table table, out string error)
{
error = "";
var query = GetLambdaQuery();
query.Where(b => b.DataBaseName == table.DataBaseName && b.TableName == table.TableName);
query.Top(1);
query.OrderBy(b => b.MainDataEndIndex, true);
var part1 = query.ToList().FirstOrDefault();
var start = part1.MainDataEndIndex += 1;
var end = start + table.MaxPartDataTotal - 1;
TablePart part = new TablePart()
{
DataBaseName = table.DataBaseName, TableName = table.TableName, MainDataStartIndex = start, MainDataEndIndex = end, PartIndex = part1.PartIndex + 1
};
if (part.PartIndex == 0)
{
part.PartName = table.TableName;
}
else
{
part.PartName = string.Format("{0}_{1}", table.TableName, part.PartIndex);
}
Add(part);
table.TablePartTotal = part.PartIndex + 1;
DBExtend.Update(table);
return(true);
}
/// <summary>
/// 确认流水,并更改库存
/// </summary>
/// <param name="batchNo"></param>
/// <param name="operateType"></param>
public bool ConfirmSubmit <TStock>(string batchNo, StockOperateType operateType) where TStock : Style, new()
{
DBExtend helper = dbHelper;
helper.BeginTran();
string op = operateType == StockOperateType.出 ? "-" : "+";
string sql = "update $Style set Num=$Style.num" + op + "b.num from $IStockRecord b where $Style.id=b.styleId and b.Handled=0 and b.batchNo=@batchNo";
sql += @"
update $IStockRecord set Handled=1,OperateType=@OperateType,UpdateTime=getdate(),$IStockRecord.num=0" + op + "$IStockRecord.num where batchNo=@batchNo";
//sql = AutoFormat(sql, typeof(TStock), typeof(TRecord));
helper.AddParam("batchNo", batchNo);
helper.AddParam("OperateType", (int)operateType);
try
{
helper.Execute(sql, typeof(TStock), typeof(TModel));
}
catch (Exception ero)
{
helper.RollbackTran();
return(false);
}
helper.CommitTran();
return(true);
}
/// <summary>
/// 返回字典
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TValue"></typeparam>
/// <returns></returns>
public Dictionary <TKey, TValue> ToDictionary <TKey, TValue>()
{
var db = new DBExtend(__DbContext);
var reader = db.GetQueryDynamicReader(this);
return(ObjectConvert.DataReadToDictionary <TKey, TValue>(reader));
}
/// <summary>
/// 解锁锁定金额并确认流水
/// </summary>
/// <param name="item"></param>
/// <param name="lockId"></param>
/// <param name="error"></param>
/// <returns></returns>
public bool SubmitTransactionAndUnlock(ITransaction item, int lockId, out string error)
{
error = "";
if (lockId <= 0)
{
throw new Exception("lockId值不符合");
}
DBExtend helper = dbHelper;
helper.BeginTran();
bool a = UnlockAmount(helper, lockId, out error);
if (!a)
{
helper.RollbackTran();
return(false);
}
bool b = SubmitTransaction(helper, item, out error);
if (!b)
{
helper.RollbackTran();
return(false);
}
helper.CommitTran();
return(true);
}
/// <summary>
/// 查询订单第一个产品
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public static IOrderDetail QueryFirstOrderDetail(string orderId)
{
DBExtend helper = dbHelper;
var orderDetail = helper.QueryItem <IOrderDetail>(b => b.OrderId == orderId);
return(orderDetail);
}
/// <summary>
/// 查询一个订单
/// </summary>
public static TMain Query <TMain>(string orderId) where TMain : IOrder, new()
{
string table = Base.GetTableName(typeof(TMain));
DBExtend helper = dbHelper;
TMain orderMain = helper.QueryItem <TMain>(b => b.OrderId == orderId);
return(orderMain);
}
/// <summary>
/// 填充参数
/// </summary>
/// <param name="db"></param>
internal void FillParames(DBExtend db)
{
//db.ClearParams();
foreach (var n in QueryParames)
{
db.SetParam(n.Key, n.Value);
}
}
/// <summary>
/// 查询订单明细
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public List <IOrderDetail> QueryOrderDetail(string orderId)
{
List <IOrderDetail> orderDetail = new List <IOrderDetail>();
DBExtend helper = dbHelper;
orderDetail = helper.QueryList <IOrderDetail>(b => b.OrderId == orderId);
return(orderDetail);
}
/// <summary>
/// 获取购物车总额
/// </summary>
/// <param name="userId"></param>
/// <param name="cartType"></param>
/// <returns></returns>
public decimal GetTotalAmount(int userId, int cartType = 0)
{
DBExtend helper = DBExtend;
var a = Sum <decimal>(b => b.UserId == userId && b.CartType == cartType && b.Selected == true, b => b.Num * b.Price);
return(a);
}
/// <summary>
/// 删除一个属性
/// </summary>
/// <param name="propertyId"></param>
public void DeleteProperty(int propertyId)
{
DBExtend helper = dbHelper;
Delete(propertyId);
helper.Delete <PropertyValue>(b => b.PropertyId == propertyId);
nameCache.Remove(propertyId);
}
/// <summary>
/// 提交流水不带事务
/// </summary>
/// <param name="helper"></param>
/// <param name="item"></param>
/// <param name="error"></param>
/// <returns></returns>
public bool SubmitTransaction(DBExtend helper, ITransaction item, out string error)
{
//if (item.TradeType is System.Enum)
//{
// item.TradeType = (int)item.TradeType;
//}
error = "";
var account = AccountBusiness <TType> .Instance.GetAccountFromCache(item.AccountId);
item.TransactionType = account.TransactionType;
item.Amount = Math.Abs(item.Amount);
if (item.OperateType == OperateType.支出)
{
item.Amount = 0 - item.Amount;
}
if (string.IsNullOrEmpty(item.TransactionNo))
{
item.TransactionNo = GetSerialNumber(1, item.TradeType, (int)item.OperateType);
}
int transactionId = 0;
try
{
//检测余额
if (item.OperateType == OperateType.支出 && item.CheckBalance)
{
string sql1 = "select CurrentBalance-LockedAmount from $IAccountDetail with (nolock) where id=@AccountId";
//sql1 = FormatTable(sql1);
helper.AddParam("AccountId", item.AccountId);
var balance = helper.AutoExecuteScalar <decimal>(sql1, typeof(IAccountDetail));
//var balance = helper.ExecScalar<decimal>(sql1, typeof(IAccountDetail));
if (balance + item.Amount < 0)
{
error = "对应帐户余额不足";
return(false);
}
}
transactionId = helper.InsertFromObj(item);
string sql = @"
update $ITransaction set CurrentBalance=b.CurrentBalance+Amount,LastBalance=b.CurrentBalance from $IAccountDetail b where b.id=@AccountId and $ITransaction.id=@id
update $IAccountDetail set CurrentBalance=CurrentBalance+@amount where id=@AccountId";
//helper.Clear();
helper.AddParam("id", item.Id);
helper.AddParam("amount", item.Amount);
helper.AddParam("AccountId", item.AccountId);
//sql = FormatTable(sql);
helper.AutoSpUpdate(sql, typeof(ITransaction), typeof(IAccountDetail));
//helper.Execute(sql, typeof(ITransaction), typeof(IAccountDetail));
}
catch (Exception ero)
{
error = ero.Message;
CoreHelper.EventLog.Log("SubmitTransaction 发生错误" + ero, true);
return(false);
}
return(true);
}
/// <summary>
/// 提交订单
/// </summary>
/// <typeparam name="TMain"></typeparam>
/// <returns></returns>
public static bool SubmitOrder <TMain>(TMain order)
where TMain : IOrder, new()
{
DBExtend helper = dbHelper;
int id = helper.InsertFromObj(order);
order.Id = id;
return(true);
}
/// <summary>
/// 获取用户收货地址
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <T> QueryUserAddress <T>(string userId) where T : IAddress, new()
{
DBExtend helper = dbHelper;
LamadaQuery <T> query = new LamadaQuery <T>();
query = query.Select().Where(b => b.UserId == userId).OrderBy(b => b.DefaultAddress, true);
string key;
return(helper.QueryList <T>(query, 0, out key));
}
/// <summary>
/// 返回动态对象
/// 会按GROUP和分页判断
/// </summary>
/// <returns></returns>
public List <dynamic> ToDynamic()
{
var db = new DBExtend(__DbContext);
if (__PageSize > 0)
{
return(db.Page(this));
}
return(db.QueryDynamic(this));
}
public string CreateTable()
{
DBExtend helper = dbHelper;
PropertyName obj1 = new PropertyName();
PropertyValue obj2 = new PropertyValue();
string msg = obj1.CreateTable(helper);
msg += obj2.CreateTable(helper);
return(msg);
}
/// <summary>
/// 更改订单状态
/// </summary>
/// <param name="order"></param>
/// <param name="status"></param>
/// <param name="remark"></param>
/// <returns></returns>
public static bool UpdateOrderStatus <TMain>(TMain order, ProductOrderStatus status, string remark)
where TMain : IProductOrder, new()
{
DBExtend helper = dbHelper;
OrderAction <TType> .UpdateOrderStatus <TMain>(order, (int)status, remark);
order.Status = (int)status;
return(true);
}
/// <summary>
/// 更改数量
/// </summary>
/// <param name="item"></param>
public static void AddNum(ICartItem item)
{
DBExtend helper = dbHelper;
ParameCollection c = new ParameCollection();
c["$Num"] = "Num+" + item.Num;
//int n = GetCartCount(item.CartType) + item.Num;
//SetCartCount(item.CartType, n);
helper.Update <ICartItem>(b => b.UserId == item.UserId && b.ProductId == item.ProductId && b.StyleId == item.StyleId && b.CartType == item.CartType, c);
}
/// <summary>
/// 返回当前类型
/// 会按GROUP和分页判断
/// </summary>
/// <returns></returns>
public List <T> ToList()
{
var db = new DBExtend(__DbContext);
//如果是筛选后的结果,属性可能匹配不上
if (__PageSize > 0)
{
return(db.Page <T, T>(this));
}
return(db.QueryList(this));
}
public string CreateTable <TStock>()
where TStock : Style, new()
{
DBExtend helper = dbHelper;
TStock obj1 = new TStock();
TModel obj2 = new TModel();
string msg = obj1.CreateTable(helper);
msg += obj2.CreateTable(helper);
return(msg);
}
public static void Add(DBExtend dBExtend, Type type)
{
var dbName = dBExtend.dbHelper.DatabaseName;
lock (lockObj)
{
if (!dBExtends.ContainsKey(dbName))
{
dBExtends.Add(dbName, dBExtend);
}
if (!needCheks.ContainsKey(type))
{
needCheks.Add(type, dbName);
}
}
if (thread == null)
{
thread = new Thread(new ThreadStart(DoWatch));
thread.Start();
}
}
/// <summary>
/// 检查索引
/// </summary>
/// <param name="helper"></param>
/// <returns></returns>
public void CheckIndexExists(DBExtend helper)
{
var list = GetIndexScript(helper);
foreach (var item in list)
{
try
{
helper.Execute(item);
}
catch (Exception ero)//出错,
{
CoreHelper.EventLog.Log(string.Format("创建索引失败:{0}\r\n{1}", ero.Message, item));
}
}
}
internal static string CreateColumn(DBExtend helper, Attribute.FieldAttribute item)
{
var dbAdapter = helper._DBAdapter;
string result = "";
if (string.IsNullOrEmpty(item.ColumnType))
{
throw new Exception("ColumnType is null");
}
string str = dbAdapter.GetCreateColumnScript(item);
string indexScript = "";
if (item.FieldIndexType != Attribute.FieldIndexType.无)
{
indexScript = dbAdapter.GetColumnIndexScript(item);
}
try
{
helper.Execute(str);
if (!string.IsNullOrEmpty(indexScript))
{
helper.Execute(indexScript);
}
result = string.Format("创建字段:{0} {1} {2}\r\n", item.TableName, item.Name,item.PropertyType);
CoreHelper.EventLog.Log(result, "", false);
}
catch (Exception ero)
{
//CoreHelper.EventLog.Log("创建字段时发生错误:" + ero.Message);
result = string.Format("创建字段:{0} {1}发生错误:{2}\r\n", item.TableName, item.Name, ero.Message);
}
return result;
}
/// <summary>
/// 检查对应的字段是否存在,不存在则创建
/// </summary>
/// <param name="helper"></param>
public string CheckColumnExists(DBExtend helper)
{
string result = "";
var dbAdapter = helper._DBAdapter;
List<Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
string tableName = TypeCache.GetTableName(this.GetType(),helper.dbContext);
foreach (Attribute.FieldAttribute item in columns)
{
string sql = dbAdapter.GetSelectTop(item.KeyWordName, "from " + tableName, "", 1);
try
{
helper.Execute(sql);
}
catch//出错,按没有字段算
{
result += CreateColumn(helper, item);
}
}
return result;
}
internal List<string> GetIndexScript(DBExtend helper)
{
var dbAdapter = helper._DBAdapter;
List<string> list2 = new List<string>();
List<Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
foreach (Attribute.FieldAttribute item in columns)
{
if (item.FieldIndexType != Attribute.FieldIndexType.无)
{
//string indexScript = string.Format("CREATE {2} NONCLUSTERED INDEX IX_INDEX_{0}_{1} ON dbo.[{0}]({1})", tableName, item.Name, item.FieldIndexType == Attribute.FieldIndexType.非聚集唯一 ? "UNIQUE" : "");
string indexScript = dbAdapter.GetColumnIndexScript(item);
list2.Add(indexScript);
}
}
return list2;
}
/// <summary>
/// 创建表
/// 会检查表是否存在,如果存在则检查字段
/// </summary>
/// <param name="helper"></param>
/// <param name="message"></param>
/// <returns></returns>
public bool CreateTable(DBExtend helper, out string message)
{
var dbAdapter = helper._DBAdapter;
message = "";
//TypeCache.SetDBAdapterCache(GetType(),dbAdapter);
string tableName = TypeCache.GetTableName(GetType(),helper.dbContext);
string sql = dbAdapter.GetSelectTop("0", "from " + tableName, "", 1);
bool needCreate = false;
try
{
//检查表是否存在
helper.Execute(sql);
}
catch
{
needCreate = true;
}
if (needCreate)
{
List<string> list = new List<string>();
try
{
List<Attribute.FieldAttribute> columns = GetColumns(dbAdapter);
dbAdapter.CreateTable(columns, tableName);
message = string.Format("创建表:{0}\r\n", tableName);
CheckIndexExists(helper);
return true;
}
catch (Exception ero)
{
message = "创建表时发生错误 类型{0} {1}\r\n";
message = string.Format(message, GetType(), ero.Message);
throw new Exception(message);
return false;
}
CoreHelper.EventLog.Log(message, "", false);
}
else
{
message = CheckColumnExists(helper);
}
return true;
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="helper"></param>
/// <returns></returns>
public string CreateTable(DBExtend helper)
{
string msg;
CreateTable(helper, out msg);
return msg;
}
