/// <summary>
/// 删除商品关键词
/// </summary>
/// <param name="pid">商品id</param>
public void DeleteProductKeyword(int pid)
{
DbParameter[] parms =
{
GenerateInParam("@pid", SqlDbType.Int, 4, pid)
};
string commandText = string.Format("DELETE FROM [{0}productkeywords] WHERE [pid]=@pid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 更新购物车的用户id
/// </summary>
/// <param name="uid">用户id</param>
/// <param name="sid">用户sid</param>
public void UpdateCartUidBySid(int uid, string sid)
{
DbParameter[] parms =
{
GenerateInParam("@uid", SqlDbType.Int, 4, uid),
GenerateInParam("@sid", SqlDbType.Char, 16, sid)
};
RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
string.Format("{0}updatecartuidbysid", RDBSHelper.RDBSTablePre),
parms);
}
/// <summary>
/// 清空过期购物车
/// </summary>
/// <param name="expireTime">过期时间</param>
public void ClearExpiredCart(DateTime expireTime)
{
DbParameter[] parms =
{
GenerateInParam("@expiretime", SqlDbType.DateTime, 8, expireTime)
};
string commandText = string.Format("DELETE FROM [{0}orderproducts] WHERE [oid]=0 AND [addtime]<@expiretime",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 删除店铺配送费用
/// </summary>
/// <param name="recordId">记录id</param>
public void DeleteStoreShipFeeById(int recordId)
{
DbParameter[] parms =
{
GenerateInParam("@recordid", SqlDbType.Int, 4, recordId)
};
string commandText = string.Format("DELETE FROM [{0}storeshipfees] WHERE [recordid]=@recordId",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 删除店铺配送模板
/// </summary>
/// <param name="storeSTid">店铺配送模板id</param>
public void DeleteStoreShipTemplateById(int storeSTid)
{
DbParameter[] parms =
{
GenerateInParam("@storestid", SqlDbType.Int, 4, storeSTid)
};
string commandText = string.Format("DELETE FROM [{0}storeshipfees] WHERE [storestid]=@storestid;DELETE FROM [{0}storeshiptemplates] WHERE [storestid]=@storestid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 删除店铺行业
/// </summary>
/// <param name="storeIid">店铺行业id</param>
public void DeleteStoreIndustryById(int storeIid)
{
DbParameter[] parms =
{
GenerateInParam("@storeiid", SqlDbType.SmallInt, 2, storeIid)
};
string commandText = String.Format("DELETE FROM [{0}storeindustries] WHERE [storeiid]=@storeiid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 获得店铺数量通过店铺等级id
/// </summary>
/// <param name="storeRid">店铺等级id</param>
/// <returns></returns>
public int GetStoreCountByStoreRid(int storeRid)
{
DbParameter[] parms =
{
GenerateInParam("@storerid", SqlDbType.SmallInt, 2, storeRid)
};
string commandText = string.Format("SELECT COUNT([storeid]) FROM [{0}stores] WHERE [storerid]=@storerid",
RDBSHelper.RDBSTablePre);
return(TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText, parms)));
}
/// <summary>
/// 后台根据店铺名称得到店铺id
/// </summary>
/// <param name="storeName">店铺名称</param>
/// <returns></returns>
public int AdminGetStoreIdByName(string storeName)
{
DbParameter[] parms =
{
GenerateInParam("@name", SqlDbType.NChar, 60, storeName)
};
string commandText = string.Format("SELECT [storeid] FROM [{0}stores] WHERE [name]=@name",
RDBSHelper.RDBSTablePre);
return(TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText, parms)));
}
public System.Data.IDataReader GetJobById(int jobId)
{
DbParameter[] parms =
{
GenerateInParam("@jobId", SqlDbType.Int, 4, jobId)
};
string commandText = string.Format("SELECT {1} FROM [{0}jobs] WHERE [jobId]=@jobId",
RDBSHelper.RDBSTablePre,
RDBSFields.JOBS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 是否存在商品关键词
/// </summary>
/// <param name="pid">商品id</param>
/// <param name="keyword">关键词</param>
/// <returns></returns>
public bool IsExistProductKeyword(int pid, string keyword)
{
DbParameter[] parms =
{
GenerateInParam("@keyword", SqlDbType.NChar, 40, keyword),
GenerateInParam("@pid", SqlDbType.Int, 4, pid)
};
string commandText = string.Format("SELECT [pid] FROM [{0}productkeywords] WHERE [keyword]=@keyword AND [pid]=@pid ",
RDBSHelper.RDBSTablePre);
return(TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText, parms)) > 0);
}
public System.Data.IDataReader GetServiceEvalInfoById(int id)
{
DbParameter[] parms =
{
GenerateInParam("@id", SqlDbType.Int, 4, id)
};
string commandText = string.Format("SELECT {1} FROM [{0}ServiceEval] WHERE [id]=@id",
RDBSHelper.RDBSTablePre,
RDBSFields.SERVICEEVAL);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
public System.Data.IDataReader GetProductFeedbacksInfoById(int id)
{
DbParameter[] parms =
{
GenerateInParam("@id", SqlDbType.Int, 4, id)
};
string commandText = string.Format("SELECT {1} FROM [{0}ProductFeedbacks] WHERE [id]=@id",
RDBSHelper.RDBSTablePre,
RDBSFields.PRODUCTFEEDBACKS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 根据邮箱获取用户基本信息
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public IDataReader GetPartUserByEmail(string email)
{
DbParameter[] parms =
{
GenerateInParam("@Email", SqlDbType.NVarChar, 50, email)
};
string commandText = string.Format("SELECT {1} FROM [{0}User] WHERE [Email]=@Email",
RDBSHelper.RDBSTablePre,
RDBSFields.PARTUSERS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 获取用户详细信息
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public IDataReader GetUserDetailById(int uid)
{
DbParameter[] parms =
{
GenerateInParam("@UserID", SqlDbType.Int, 4, uid)
};
string commandText = string.Format("SELECT {1} FROM [{0}UserDetails] WHERE [UserID]=@UserID",
RDBSHelper.RDBSTablePre,
RDBSFields.USERDETAILS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 获得用户未评价订单列表
/// </summary>
/// <param name="uid">用户id</param>
/// <param name="startAddTime">添加开始时间</param>
/// <param name="endAddTime">添加结束时间</param>
/// <returns></returns>
public int GetUserUnReviewOrderCount(int uid, string startAddTime, string endAddTime)
{
DbParameter[] parms =
{
GenerateInParam("@uid", SqlDbType.Int, 4, uid),
GenerateInParam("@startaddtime", SqlDbType.VarChar, 30, startAddTime.Length > 0? TypeHelper.StringToDateTime(startAddTime).ToString("yyyy-MM-dd HH:mm:ss") : ""),
GenerateInParam("@endaddtime", SqlDbType.VarChar, 30, endAddTime.Length > 0? TypeHelper.StringToDateTime(endAddTime).ToString("yyyy-MM-dd HH:mm:ss") : "")
};
return(TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
string.Format("{0}getuserunreviewordercount", RDBSHelper.RDBSTablePre),
parms)));
}
/// <summary>
/// 更新订单折扣
/// </summary>
/// <param name="oid">订单id</param>
/// <param name="discount">折扣</param>
/// <param name="surplusMoney">剩余金额</param>
public void UpdateOrderDiscount(int oid, decimal discount, decimal surplusMoney)
{
DbParameter[] parms =
{
GenerateInParam("@oid", SqlDbType.Int, 4, oid),
GenerateInParam("@discount", SqlDbType.Decimal, 8, discount),
GenerateInParam("@surplusmoney", SqlDbType.Decimal, 8, surplusMoney)
};
RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
string.Format("{0}updateorderdiscount", RDBSHelper.RDBSTablePre),
parms);
}
/// <summary>
///获取用户转账记录记录(分页)
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize">-1 取全部</param>
/// <param name="condition">没有where</param>
/// <returns></returns>
public DataTable GetUserRemitList(int pageIndex, int pageSize, string condition = "")
{
DbParameter[] parms =
{
GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize),
GenerateInParam("@pageindex", SqlDbType.Int, 4, pageIndex)
};
string commandText = string.Format(@"
begin try
if OBJECT_ID('tempdb..#list') is not null
drop table #list
SELECT ROW_NUMBER() over(order by a.remitid desc) id
,a.[remitid]
,a.[uid]
,a.[type]
,a.[name]
,a.[account]
,a.[money],a.realmoney
,a.[bankname]
,a.[status]
,a.[remark]
,a.[addtime]
,a.[updateuid]
,a.[updatetime],b.mobile mobile,a.chargeremark,b.username
into #list
FROM owzx_userremit a
join owzx_users b on a.uid=b.uid
{0}
declare @total int
select @total=(select count(1) from #list)
if(@pagesize=-1)
begin
select *,@total TotalCount from #list
end
else
begin
select *,@total TotalCount from #list where id>@pagesize*(@pageindex-1) and id <=@pagesize*@pageindex
end
end try
begin catch
select ERROR_MESSAGE() state
end catch
", condition);
return(RDBSHelper.ExecuteTable(CommandType.Text, commandText, parms)[0]);
}
/// <summary>
/// 获得店铺配送费用
/// </summary>
/// <param name="storeSTid">店铺模板id</param>
/// <param name="provinceId">省id</param>
/// <param name="cityId">市id</param>
/// <returns></returns>
public IDataReader GetStoreShipFeeByStoreSTidAndRegion(int storeSTid, int provinceId, int cityId)
{
DbParameter[] parms =
{
GenerateInParam("@storestid", SqlDbType.Int, 4, storeSTid),
GenerateInParam("@provinceid", SqlDbType.SmallInt, 2, provinceId),
GenerateInParam("@cityid", SqlDbType.SmallInt, 2, cityId)
};
return(RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
string.Format("{0}getstoreshipfeebystorestidandregion", RDBSHelper.RDBSTablePre),
parms));
}
/// <summary>
/// 根据ID获取产品信息
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public IDataReader GetProductById(int productId)
{
DbParameter[] parms =
{
GenerateInParam("@productId", SqlDbType.Int, 4, productId)
};
string commandText = string.Format("SELECT {1} FROM [{0}Product] WHERE [productId]=@productId",
RDBSHelper.RDBSTablePre,
RDBSFields.PRODUCT);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 获得店铺配送费用
/// </summary>
/// <param name="recordId">记录id</param>
/// <returns></returns>
public IDataReader GetStoreShipFeeById(int recordId)
{
DbParameter[] parms =
{
GenerateInParam("@recordid", SqlDbType.Int, 4, recordId)
};
string commandText = string.Format("SELECT {1} FROM [{0}storeshipfees] WHERE [recordid]=@recordId",
RDBSHelper.RDBSTablePre,
RDBSFields.STORE_SHIPFEES);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 后台获得店铺配送费用列表
/// </summary>
/// <param name="storeSTid">店铺配送模板id</param>
/// <returns></returns>
public DataTable AdminGetStoreShipFeeList(int storeSTid)
{
DbParameter[] parms =
{
GenerateInParam("@storestid", SqlDbType.Int, 4, storeSTid)
};
string commandText = string.Format("SELECT [temp1].[recordid],[temp1].[storestid],[temp1].[regionid],[temp1].[startvalue],[temp1].[startfee],[temp1].[addvalue],[temp1].[addfee],[temp2].[provincename],[temp2].[cityname],[temp2].[name] FROM (SELECT {1} FROM [{0}storeshipfees] WHERE [storestid]=@storestid) AS [temp1] LEFT JOIN [{0}regions] AS [temp2] ON [temp1].[regionid]=[temp2].[regionid]",
RDBSHelper.RDBSTablePre,
RDBSFields.STORE_SHIPFEES);
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0]);
}
/// <summary>
/// 获得店长
/// </summary>
/// <param name="storeId">店铺id</param>
/// <returns></returns>
public IDataReader GetStoreKeeperById(int storeId)
{
DbParameter[] parms =
{
GenerateInParam("@storeid", SqlDbType.Int, 4, storeId)
};
string commandText = string.Format("SELECT {1} FROM [{0}storekeepers] WHERE [storeid]=@storeid",
RDBSHelper.RDBSTablePre,
RDBSFields.STORE_KEEPERS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 获得店铺配送模板列表
/// </summary>
/// <param name="storeId">店铺id</param>
/// <returns></returns>
public IDataReader GetStoreShipTemplateList(int storeId)
{
DbParameter[] parms =
{
GenerateInParam("@storeid", SqlDbType.Int, 4, storeId)
};
string commandText = string.Format("SELECT {1} FROM [{0}storeshiptemplates] WHERE [storeid]=@storeid",
RDBSHelper.RDBSTablePre,
RDBSFields.STORE_SHIPTEMPLATES);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 创建店铺行业
/// </summary>
public void CreateStoreIndustry(StoreIndustryInfo storeIndustryInfo)
{
DbParameter[] param =
{
GenerateInParam("@title", SqlDbType.NChar, 150, storeIndustryInfo.Title),
GenerateInParam("@displayorder", SqlDbType.Int, 4, storeIndustryInfo.DisplayOrder)
};
string commandText = String.Format("INSERT INTO [{0}storeindustries] ([title],[displayorder]) VALUES(@title,@displayorder)",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, param);
}
public System.Data.IDataReader GetShopById(int shopId)
{
DbParameter[] parms =
{
GenerateInParam("@shopId", SqlDbType.Int, 4, shopId)
};
string commandText = string.Format("SELECT {1} FROM [{0}Shops] WHERE [shopId]=@shopId",
RDBSHelper.RDBSTablePre,
RDBSFields.SHOPS);
return(RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms));
}
/// <summary>
/// 更新店铺状态
/// </summary>
/// <param name="storeId">店铺id</param>
/// <param name="state">状态</param>
/// <param name="stateEndTime">状态结束时间</param>
public void UpdateStoreState(int storeId, StoreState state, DateTime stateEndTime)
{
DbParameter[] parms =
{
GenerateInParam("@state", SqlDbType.TinyInt, 1, (int)state),
GenerateInParam("@stateendtime", SqlDbType.DateTime, 8, stateEndTime),
GenerateInParam("@storeid", SqlDbType.Int, 4, storeId)
};
string commandText = string.Format("UPDATE [{0}stores] SET [state]=@state,[stateendtime]=@stateendtime WHERE [storeid]=@storeid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
}
/// <summary>
/// 更新店铺行业
/// </summary>
public void UpdateStoreIndustry(StoreIndustryInfo storeIndustryInfo)
{
DbParameter[] param =
{
GenerateInParam("@title", SqlDbType.NChar, 150, storeIndustryInfo.Title),
GenerateInParam("@displayorder", SqlDbType.Int, 4, storeIndustryInfo.DisplayOrder),
GenerateInParam("@storeiid", SqlDbType.SmallInt, 2, storeIndustryInfo.StoreIid)
};
string commandText = String.Format("UPDATE [{0}storeindustries] SET [title]=@title,[displayorder]=@displayorder WHERE [storeiid]=@storeiid",
RDBSHelper.RDBSTablePre);
RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, param);
}
/// <summary>
/// 获得订单id列表
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="orderActionType">订单操作类型</param>
/// <returns></returns>
public DataTable GetOrderIdList(DateTime startTime, DateTime endTime, int orderActionType)
{
DbParameter[] parms =
{
GenerateInParam("@starttime", SqlDbType.DateTime, 8, startTime),
GenerateInParam("@endtime", SqlDbType.DateTime, 8, endTime),
GenerateInParam("@orderactiontype", SqlDbType.TinyInt, 1, orderActionType)
};
string commandText = string.Format("SELECT [oid] FROM [{0}orderactions] WHERE [actiontype]=@orderactiontype AND [actiontime]>=@starttime AND [actiontime]<@endtime",
RDBSHelper.RDBSTablePre);
return(RDBSHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0]);
}
/// <summary>
/// 付款
/// </summary>
/// <param name="oid">订单id</param>
/// <param name="orderState">订单状态</param>
/// <param name="paySN">支付单号</param>
/// <param name="payTime">支付时间</param>
public void PayOrder(int oid, OrderState orderState, string paySN, DateTime payTime)
{
DbParameter[] parms =
{
GenerateInParam("@oid", SqlDbType.Int, 4, oid),
GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, (int)orderState),
GenerateInParam("@paysn", SqlDbType.Char, 30, paySN),
GenerateInParam("@paytime", SqlDbType.DateTime, 8, payTime)
};
RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
string.Format("{0}payorder", RDBSHelper.RDBSTablePre),
parms);
}
/// <summary>
/// 发货
/// </summary>
/// <param name="oid">订单id</param>
/// <param name="orderState">订单状态</param>
/// <param name="shipSN">配送单号</param>
/// <param name="shipTime">配送时间</param>
public void SendOrderProduct(int oid, OrderState orderState, string shipSN, DateTime shipTime)
{
DbParameter[] parms =
{
GenerateInParam("@oid", SqlDbType.Int, 4, oid),
GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, (int)orderState),
GenerateInParam("@shipsn", SqlDbType.Char, 30, shipSN),
GenerateInParam("@shiptime", SqlDbType.DateTime, 8, shipTime)
};
RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
string.Format("{0}sendorderproduct", RDBSHelper.RDBSTablePre),
parms);
}